單行函數(shù)

• 操作數(shù)據(jù)對(duì)象
• 接收參數(shù)返回一個(gè)結(jié)果
• 只對(duì)一行進(jìn)行變換
• 每行返回一個(gè)結(jié)果
• 可以嵌套
• 參數(shù)可以是一列或一個(gè)值

數(shù)值函數(shù)

ABS(x) 返回x的絕對(duì)值
SIGN(X) 返回X的符號(hào)。正數(shù)返回1，負(fù)數(shù)返回-1，0返回0 PI() 返回圓周率的值
CEIL(x)，CEILING(x) 返回大于或等于某個(gè)值的最小整數(shù)
FLOOR(x) 返回小于或等于某個(gè)值的最大整數(shù)
LEAST(e1,e2,e3…) 返回列表中的最小值
GREATEST(e1,e2,e3…) 返回列表中的最大值
MOD(x,y) 返回X除以Y后的余數(shù)
RAND() 返回0~1的隨機(jī)值
RAND(x)
返回0~1的隨機(jī)值，其中x的值用作種子值，相同的X值會(huì)產(chǎn)生相同的隨機(jī)
數(shù) ROUND(x) 返回一個(gè)對(duì)x的值進(jìn)行四舍五入后，最接近于X的整數(shù)
ROUND(x,y) 返回一個(gè)對(duì)x的值進(jìn)行四舍五入后最接近X的值，并保留到小數(shù)點(diǎn)后面Y位 TRUNCATE(x,y) 返回?cái)?shù)字x截?cái)酁閥位小數(shù)的結(jié)果
SQRT(x) 返回x的平方根。當(dāng)X的值為負(fù)數(shù)時(shí)，返回NULL

• 示例
  SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32), FLOOR(-43.23),MOD(12,5) FROM DUAL;

  
  
  image.png
  
  

  SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1) FROM DUAL;

  
  
  image.png
  
  SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1) FROM DUAL;
  
  image.png

BIN(x) 返回x的二進(jìn)制編碼
HEX(x) 返回x的十六進(jìn)制編碼
OCT(x) 返回x的八進(jìn)制編碼
CONV(x,f1,f2) 返回f1進(jìn)制數(shù)變成f2進(jìn)制數(shù)

字符串函數(shù)

ASCII(S) 返回字符串S中的第一個(gè)字符的ASCII碼值
CHAR_LENGTH(s) 返回字符串s的字符數(shù)。作用與CHARACTER_LENGTH(s)相同
LENGTH(s) 返回字符串s的字節(jié)數(shù)，和字符集有關(guān)
CONCAT(s1,s2,......,sn) 連接s1,s2,......,sn為一個(gè)字符串
CONCAT_WS(x, s1,s2,......,sn) 同CONCAT(s1,s2,...)函數(shù)，但是每個(gè)字符串之間要加上x(chóng)
INSERT(str, idx, len, replacestr)將字符串str從第idx位置開(kāi)始，len個(gè)字符長(zhǎng)的子串替換為字符串replacestr
REPLACE(str, a, b) 用字符串b替換字符串str中所有出現(xiàn)的字符串a(chǎn)
UPPER(s) 或 UCASE(s) 將字符串s的所有字母轉(zhuǎn)成大寫(xiě)字母
LOWER(s) 或LCASE(s) 將字符串s的所有字母轉(zhuǎn)成小寫(xiě)字母
LEFT(str,n) 返回字符串str最左邊的n個(gè)字符
RIGHT(str,n) 返回字符串str最右邊的n個(gè)字符
LPAD(str, len, pad) 用字符串pad對(duì)str最左邊進(jìn)行填充，直到str的長(zhǎng)度為len個(gè)字符
RPAD(str ,len, pad) 用字符串pad對(duì)str最右邊進(jìn)行填充，直到str的長(zhǎng)度為len個(gè)字符
LTRIM(s) 去掉字符串s左側(cè)的空格
RTRIM(s) 去掉字符串s右側(cè)的空格
TRIM(s) 去掉字符串s開(kāi)始與結(jié)尾的空格
TRIM(s1 FROM s) 去掉字符串s開(kāi)始與結(jié)尾的s1
TRIM(LEADING s1 FROM s)去掉字符串s開(kāi)始處的s1
TRIM(TRAILING s1 FROM s)去掉字符串s結(jié)尾處的s1
REPEAT(str, n) 返回str重復(fù)n次的結(jié)果
SPACE(n) 返回n個(gè)空格
STRCMP(s1,s2) 比較字符串s1,s2的ASCII碼值的大小
SUBSTR(s,index,len)返回從字符串s的index位置其len個(gè)字符，作用與SUBSTRING(s,n,len)、 MID(s,n,len)相同
LOCATE(substr,str)返回字符串substr在字符串str中首次出現(xiàn)的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn)返回指定位置的字符串，如果m=1，則返回s1，如果m=2，則返回s2，如果m=n，則返回sn FIELD(s,s1,s2,…,sn) 返回字符串s在字符串列表中第一次出現(xiàn)的位置
注意：MySQL中，字符串的位置是從1開(kāi)始的。

日期和時(shí)間函數(shù)

CURDATE() ，CURRENT_DATE() 返回當(dāng)前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME() 返回當(dāng)前時(shí)間，只包含時(shí)、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() 返回當(dāng)前系統(tǒng)日期和時(shí)間
UTC_DATE() 返回UTC（世界標(biāo)準(zhǔn)時(shí)間）日期
UTC_TIME() 返回UTC（世界標(biāo)準(zhǔn)時(shí)間）時(shí)間

日期與時(shí)間戳的轉(zhuǎn)換

UNIX_TIMESTAMP() 以UNIX時(shí)間戳的形式返回當(dāng)前時(shí)間。SELECT UNIX_TIMESTAMP() - >1634348884 UNIX_TIMESTAMP(date) 將時(shí)間date以UNIX時(shí)間戳的形式返回。
FROM_UNIXTIME(timestamp) 將UNIX時(shí)間戳的時(shí)間轉(zhuǎn)換為普通格式的時(shí)間

獲取月份、星期、星期數(shù)、天數(shù)等函數(shù)

YEAR(date) / MONTH(date) / DAY(date) 返回具體的日期值
HOUR(time) / MINUTE(time) / SECOND(time) 返回具體的時(shí)間值
MONTHNAME(date) 返回月份：January，...
DAYNAME(date) 返回星期幾：MONDAY，TUESDAY.....SUNDAY
WEEKDAY(date) 返回周幾，注意，周1是0，周2是1，。。。周日是6
QUARTER(date) 返回日期對(duì)應(yīng)的季度，范圍為1～4 WEEK(date) ，
WEEKOFYEAR(date) 返回一年中的第幾周
DAYOFYEAR(date) 返回日期是一年中的第幾天
DAYOFMONTH(date) 返回日期位于所在月份的第幾天
DAYOFWEEK(date)返回周幾，注意：周日是1，周一是2，。。。周六是7

計(jì)算時(shí)間和日期的函數(shù)

DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type) 返回與給定日期時(shí)間相差I(lǐng)NTERVAL時(shí)
間段的日期時(shí)間
DATE_SUB(date,INTERVAL expr type)， SUBDATE(date,INTERVAL expr type) 返回與date相差I(lǐng)NTERVAL時(shí)間間隔的
日期
ADDTIME(time1,time2) 返回time1加上time2的時(shí)間。當(dāng)time2為一個(gè)數(shù)字時(shí)，代表的是秒 ，可以為負(fù)數(shù)
SUBTIME(time1,time2) 返回time1減去time2后的時(shí)間。當(dāng)time2為一個(gè)數(shù)字時(shí)，代表的是 秒 ，可以為負(fù)數(shù)
DATEDIFF(date1,date2) 返回date1 - date2的日期間隔天數(shù)
TIMEDIFF(time1, time2) 返回time1 - time2的時(shí)間間隔
FROM_DAYS(N) 返回從0000年1月1日起，N天以后的日期
TO_DAYS(date) 返回日期date距離0000年1月1日的天數(shù)
LAST_DAY(date) 返回date所在月份的最后一天的日期
MAKEDATE(year,n) 針對(duì)給定年份與所在年份中的天數(shù)返回一個(gè)日期
MAKETIME(hour,minute,second) 將給定的小時(shí)、分鐘和秒組合成時(shí)間并返回
PERIOD_ADD(time,n) 返回time加上n后的時(shí)間

日期的格式化與解析

DATE_FORMAT(date,fmt) 按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt) 按照字符串fmt格式化時(shí)間time值
GET_FORMAT(date_type,format_type) 返回日期字符串的顯示格式
STR_TO_DATE(str, fmt) 按照字符串fmt對(duì)str進(jìn)行解析，解析為一個(gè)日期

流程函數(shù)

IF(value,value1,value2) 如果value的值為T(mén)RUE，返回value1，否則返回value2 IFNULL(value1, value2)如果value1不為NULL，返回value1，否則返回value2
CASE WHEN 條件1 THEN 結(jié)果1 WHEN 條件2 THEN 結(jié)果2 .... [ELSE resultn] END相當(dāng)于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END
相當(dāng)于Java的switch...case...

加解密函數(shù)

PASSWORD(str)返回字符串str的加密版本，41位長(zhǎng)的字符串。加密結(jié)果 不可 逆 ，常用于用戶(hù)的密碼加密
MD5(str)返回字符串str的md5加密后的值，也是一種加密方式。若參數(shù)為NULL，則會(huì)返回NULL SHA(str)
從原明文密碼str計(jì)算并返回加密后的密碼字符串，當(dāng)參數(shù)為NULL時(shí)，返回NULL。 SHA加密算法比MD5更加安全 。
ENCODE(value,password_seed) 返回使用password_seed作為加密密碼加密value
DECODE(value,password_seed) 返回使用password_seed作為加密密碼解密value

mysql信息函數(shù)

VERSION() 返回當(dāng)前MySQL的版本號(hào)
CONNECTION_ID() 返回當(dāng)前MySQL服務(wù)器的連接數(shù)
DATABASE()，SCHEMA() 返回MySQL命令行當(dāng)前所在的數(shù)據(jù)庫(kù)
USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER()
返回當(dāng)前連接MySQL的用戶(hù)名，返回結(jié)果格式為 “主機(jī)名@用戶(hù)名” CHARSET(value) 返回字符串value自變量的字符集
COLLATION(value) 返回字符串value的比較規(guī)則

聚合函數(shù)

AVG() SUM() MAX() MIN() COUNT()
問(wèn)題：用count()，count(1)，count(列名)誰(shuí)好呢?
其實(shí)，對(duì)于MyISAM引擎的表是沒(méi)有區(qū)別的。這種引擎內(nèi)部有一計(jì)數(shù)器在維護(hù)著行數(shù)。
Innodb引擎的表用count(),count(1)直接讀行數(shù)，復(fù)雜度是O(n)，因?yàn)閕nnodb真的要去數(shù)一遍。但好
于具體的count(列名)。
問(wèn)題：能不能使用count(列名)替換count()?
不要使用 count(列名)來(lái)替代 count() ， count() 是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語(yǔ)法，跟數(shù)
據(jù)庫(kù)無(wú)關(guān)，跟 NULL 和非 NULL 無(wú)關(guān)。
說(shuō)明：count()會(huì)統(tǒng)計(jì)值為 NULL 的行，而 count(列名)不會(huì)統(tǒng)計(jì)此列為 NULL 值的行。
使用 WITH ROLLUP 關(guān)鍵字之后，在所有查詢(xún)出的分組記錄之后增加一條記錄，該記錄計(jì)算查詢(xún)出的所
有記錄的總和，即統(tǒng)計(jì)記錄數(shù)量。
SELECT department_id,AVG(salary) FROM employees WHERE department_id > 80 GROUP BY department_id WITH ROLLUP;
注意：
當(dāng)使用ROLLUP時(shí)，不能同時(shí)使用ORDER BY子句進(jìn)行結(jié)果排序，即ROLLUP和ORDER BY是互相排斥
的。
非法使用聚合函數(shù) ： 不能在 WHERE 子句中使用聚合函數(shù)。如下：
SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id;

image.png