聚合函數

聚合函數通常會跟分組查詢結合使用

• SUM(字段)：求和
• AVG(字段)：求平均值
• MIN(字段)：求最小值
• MAX(字段)：求最大值
• COUNT(*)：統計符合條件的記錄總條數

COUNT()函數的多種用法

count(1)、count(*)、count(常量)、count(主鍵)、count(非主鍵)、count(distinct(字段)) 等多個函數

COUNT(*)

• 對于 MyISAM 引擎，會把表的總行數存在了磁盤上（存放在 information_schema 庫中的 PARTITIONS 表中），在不加 where 條件時，執行 count(*) 時會直接返回這個總數，因此效率很高，但是在加 where 限定語句的時候 MySQL 需要對全表進行檢索從而得出 count 的總數。

• 而 InnoDB 引擎并沒有像 MyISAM 那樣把表的總行數存儲在磁盤，而是在執行 count(* )時，在不加 where 限定語句時，MySQL Server 層需要把數據從引擎里面讀出來，然后逐行累加得出總數；如果加了 where 限定語句，需要根據 where 條件從引擎里面篩選出數據，然后累加得出總數。

COUNT(主鍵)

• 對于 MyISAM 引擎，不加 where 條件時，直接返回的表中保存的數據總行數值；加 where 條件時，需要走主鍵索引篩選出值后再統計；

• 對于 InnoDB 引擎，不加 where 條件時，count(id) 和 count(*) 的處理方式一樣，MySQL優化器會選擇最小樹索引age 索引進行遍歷統計；加 where 條件時，需要走主鍵索引篩選出值后再統計

COUNT(1)

對于 MyISAM 引擎，count(1) 和 count(*) 的 邏輯是一樣的。

對于 InnoDB 引擎，按照官方文檔，count(1) 和 count(*) 的處理方式一樣，無性能差別

COUNT(常量)

count(常量)的執行邏輯和 count(1) 的邏輯是一樣的，比如：count(5)、count(‘abc’)、count(‘.’)

COUNT(非主鍵)

只統計非主鍵字段值不為NULL的總數，不管在 InnoDB 引擎 還是在 MyISAM引擎中執行，非主鍵這個字段是否添加了索引直接影響了count() 統計掃描表的行數，從而影響統計的性能

COUNT(distinct(字段))

count(distinct(字段)) 其實是 count(字段) + distinct 的結果集，統計字段不為NULL，并且在字段值重復的情況下只統計一次

COUNT()函數小結

1. count(expr)函數的參數 expr可以是任意的表達式，該函數用于統計在符合搜索條件的記錄總數；
2. count(expr)函數執行效率從低到高排序為：count(非主鍵字段) < count(主鍵) < count(1) ≈ count(*) ；
3. 對于 count(1) 和 count() ，效率相當，建議盡量使用 count()，因為 MySQL 優化器會選擇最小的索引樹進行統計，把優化的問題交給 MySQL 優化器去解決就可以了；
4. count(列字段) 只統計不為 NULL 的總行數，比如，count(name)，當name字段值為NUll時，就不會被count；而其他的count，它返回檢索到的行數，無論它們是否包含 NULL值；
5. count(NULL) 總是返回 0；
6. count(expr) 聚合函數在統計時，帶與不帶 where 條件實現原理具有差異性，性能也存在差異性；
7. 在生產中，對于InnoDB 引擎，如果對數據總量不要求特別精確，可以使用 “show table status” 方式獲取總行數；
8. 因為 MySQL一直在快速發展，所以不同的版本實現可能存在差異，所以在研究技術的時候一定要注意版本差異；
9. 在生產環境中，應盡量避免 count(expr)這種耗時操作，如果一定要進行統計，可以根據統計數據的精確度來區分采用什么方式統計；也可以在專業 DBA 的指導下進行，或者通過 explain 執行計劃宏觀看下count(expr) 操作會掃描多少數據行，如果對性能影響比較大，可以選擇在離線庫或者只讀庫中進行

字符函數

length(str)函數：獲取參數值的字節個數

對于utf-8字符集來說，一個英文占1個字節；一個中文占3個字節；
對于gbk字符集來說，一個英文占1個字節；一個中文占2個字節；

select length('蘇生Susheng') as 長度 from dual;

concat(str1,str2,…)函數：字符串拼接

通過輸入的參數str1、str2等，將他們拼接成一個字符串。

select concat('蘇生','-','Susheng') as 作者信息 from dual;

upper(str)、lower(str)函數:大小寫轉換

upper(str):將字符中的所有字母變為大寫
lower(str)將字符中的所有字母變成小寫
lcase(str)是 lower()的同義詞,用法一樣。

select upper('susheng') as 大寫, lower('SUSHENG') as 小寫 from dual;

substr(str,start,len)函數:截取字符串

str為輸入字符串，從start位置開始截取字符串，len表示要截取的長度； 沒有指定len長度：表示從start開始起，截取到字符串末尾。指定了len長度：表示從start開始起，截取len個長度。
mid(str,pos,len)是 substring(str,pos,len)的同義詞，截取字符串功能，用法一樣

select substr('我是CSDN的蘇生，低調學習',8,2) as 截取輸出 from dual;

注：8是開始位置，此處起始位置，2是開始到結束位置的長度，并不是索引的結束位置

substring_index(str,delim,count)

在定界符 delim 以及count 出現前，從字符串str返回自字符串。若count為正值,則返回最終定界符(從左邊開始)左邊的一切內容。若count為負值，則返回定界符（從右邊開始）右邊的一切內容。

 select substring_index('pro,file,admin',',',-1) from dual;

instr(str,要查找的子串)函數

• 返回子串第一次出現的索引，如果找不到，返回0；
• 當查找的子串存在于字符串中：返回該子串在字符串中【第一次】出現的索引。
• 當查找的子串不在字符串中：返回0

select instr('我是CSDN的蘇生，低調學習','蘇生') as 第一次出現 from dual;
select instr('我是CSDN的蘇生，低調學習','書生') as 第一次出現 from dual;

trim(str)函數

去掉字符串前后的空格； 該函數只能去掉字符串前后的空格，不能去掉字符串中間的空格。

select trim('   我是CSDN的蘇生    低調學習   ') as 空格去除 from dual;

lpad(str,len,填充字符)、rpad(str,len,填充字符)函數

lpad（左填充）：用指定的字符，實現對字符串左填充指定長度
rpad（右填充）：用指定的字符，實現對字符串右填充指定長度

select lpad('1',3,'0') as out_put from dual;
select rpad('1.0',5,'0') as out_put from dual;

注：這里的填充len指的是用填充字符填充后的總長度，也就是若你的len選擇5你的字符串含有位置為4則只能填充一個字符，也就是填充字符的第一個字符

replace(str,子串,另一個字符串)函數

將字符串str中的字串，替換為另一個字符串

select username as 替換之前,replace(username,'liubei','liuxuande') as 替換之后 from user;

concat_ws(separator,str1,str2,…)

是concat()的特殊形式。第一個參數是其它參數的分隔符。
分隔符的位置放在要連接的兩個字符串之間。
分隔符可以是一個字符串，也可以是其它參數。
如果分隔符為 null，則結果為 null。
函數會忽略任何分隔符參數后的 null 值。

select concat_ws(':',username,userpass,role) from user;

elt(n,str1,str2,str3,…)

若n=1，則返回值為str1若n=2，則返回值為 str2 ，以此類推。若n小于1或大于參數的數目，則返回值為 null 。

select username,elt(role,'管理員','版主','會員','游客') as 身份 from user;

select username,elt(role,'管理員','版主','會員','游客') as 身份 from user;

insert(str,pos,len,newstr)

• 返回字符串 str, 其子字符串起始于 pos 位置和長期被字符串 newstr取代的len 字符。
• 如果pos 超過字符串長度，則返回值為原始字符串。假如len的長度大于其它字符串的長度，則從位置pos開始替換。若任何一個參數為null，則返回值為null。

select insert('susheng',1,2,'shu') from dual;

reverse(str)翻轉字符串

select 'admin' as 翻轉前,reverse('admin') as 翻轉后 from dual;

數學函數

round(x,保留位數)函數

四舍五入； 當對正數進行四舍五入：按照正常的計算方式，四舍五入即可。當對負數進行四舍五入：先把符號丟到一邊，對去掉負號后的正數進行四舍五入，完成以后，再把這個負號，補上即可。

select round(1.595658,3) as out_put from dual;

ceil(x)函數：向上取整

向上取整，返回>=該參數的最小整數。求的是大于等于這個數字的最小整數

select ceil(1.9),ceil(1.1) from dual;

floor(x)函數：向下取整

向下取整，返回<=該參數的最大整數，求的是小于等于這個數字的最大整數。

select floor(1.9),floor(1.1) from dual;

truncate(x,D)函數

• 此函數叫截斷函數，顧名思義就是就是截取不要的部分，然后刪掉（斷掉）它。在小數點的D位置處，截取數字直接刪去數字，若在左邊就是位置取整不使用任何法則。

• 把truncate當作小數點（.）x是要截取的數字。D為正數時是小數點的右側部分，D為0時則不要小數部分，D為負數時是小數點左邊部分，具體使用看例子演示。

select truncate(314159.2673525,5) as 截取之后 from dual;
select truncate(314159.2673525,0) as 截取之后 from dual;
select truncate(314159.2673525,-4) as 截取之后 from dual;

mod(被除數,除數)函數

取余； 當被除數為正數，結果就是正數。當被除數為負數，結果就是負數。

select mod(10,3) as out_put from dual;

pow(x,D)函數

此函數是用于計算指數函數，x為底，D為指數
select pow(5,2) as 平方運算 from dual;

select pow(5,2) as 平方運算 from dual;

絕對值函數：ABS()

select abs(-99) from dual;

圓周率函數：PI()

獲取隨機數：RAND()

每次調用都會得到一個0-1之間的浮點數

select RAND(),RAND(),RAND(),RAND() from dual;

時間/日期函數

• DATE_FORMAT(“20000101”, ‘%Y-%m-%d’) – 2020-01-01
• DATE_FORMAT(“2000-01-01”, ‘%Y-%m-%d’) – 2020-01-01
• DATE_FORMAT(‘2000-05-07 05:06:07’, ‘%H:%i:%s’) – 05:06:07 (24小時制)
• DATE_FORMAT(‘2000-05-07 05:06:07’, ‘%h:%i:%s’) – 05:06:07 (12小時制)
• DATE_FORMAT(‘2000-05-07 05:06:07’, ‘%Y-%m-%d %H:%i:%s’) – 2000-05-07 05:06:07
  

now()函數

select now() from dual;

curdate()函數

只返回系統當前的日期，不包含時間

select curdate() from dual;

curtime()函數

只返回當前的時間，不包含日期

select curtime() from dual;

獲取日期和時間中的年、月、日、時、分、秒

• 獲取年份：year()
• 獲取月份:month()
• 獲取日:day()
• 獲取小時:hour()
• 獲取分鐘:minute()
• 獲取秒數:second()

select year(now()) from dual;

weekofyear()函數：獲取當前時刻所屬周數

select weekofyear(now()) from dual;

quarter()函數：獲取當前時刻所屬的季度

select quarter(curdate()) from dual;

str_to_date()函數：字符串轉日期

select str_to_date('2024,3,1','%Y,%c,%d');

date_format()函數：日期轉字符串

select date_format(now(),'%Y年%m月%d日') from dual;

date_add(日期,interval num 時間)函數

向前、向后偏移日期和時間，正號為向后，負號為向前，除此之外還有hour（小時），minute（分鐘），second（秒）

select curdate() as '當前時間',date_add(curdate(),interval 1 year) as '一年后',date_add(curdate(),interval 1 month) as '一個月后',date_add(curdate(),interval 1 day) as '一天后' 
from dual;

last_day()函數：提取某個月最后一天的日期

select last_day(now()) from dual;

datediff(end_date,start_date)函數：計算兩個時間相差的天數

select datediff(curdate(),'2000-1-1') from dual;

timestampdiff(unit,start_date,end_date)函數： 計算兩個時間返回的年/月/天數；

unit參數是確定(start_date,end_date)結果的單位，表示為整數，以下是有效單位：
year：年份、month：月份、day：天、hour：小時、minute 分鐘、second：秒、microsecond：微秒、week：周數、quarter：季度

select timestampdiff(year,'2000-1-1',curdate()) from dual;

ADDDATE(d,n)

計算起始日期 d 加上 n 天的日期

SELECT  ADDDATE("2017-06-15",  INTERVAL 10 DAY);

ADDTIME(t,n)

n 是一個時間表達式，時間 t 加上時間表達式 n

# 加5秒
SELECT  ADDTIME('2011-11-11  11:11:11',5);

DATE_SUB(date,INTERVAL expr type)

函數從日期減去指定的時間間隔。

SELECT DATE_SUB('2024-3-1',INTERVAL  2 DAY) AS OrderPayDate FROM dual;

DAYNAME(d):返回日期 d 是星期幾，如Monday,Tuesday

SELECT  DAYNAME('2011-11-11  11:11:11') from dual;

DAYOFMONTH(d)、DAYOFWEEK(d)等

DAYOFMONTH(d)：計算日期 d 是本月的第幾天
DAYOFWEEK(d)：日期 d 今天是星期幾，1 星期日，2 星期一，以此類推

NULLIF(expr1, expr2)

比較兩個字符串，如果字符串 expr1 與 expr2 相等返回 NULL，否則返回 expr1

SELECT  NULLIF(25,25),nullif(24,25);

流程操作函數

if(expr,v1,v2)函數

實現if-else的效果，如果expr是true，返回v1。如果expr是false，返回v2

select studentresult,if(studentresult<60,'不及格',studentresult) as 成績 from result where subjectno=1;

ifnull()函數

判斷值是否為null，是null用指定值填充；如果v1不為NULL，返回v2。否則返回v1

select studentno,studentname,ifnull(address,'地址不詳') from student order by studentno desc limit 5;

case…when函數的三種用法

1.等值判斷：可以實現多條件的查詢值賽選；

case 要判斷的字段或表達式
when 常量1 then 要顯示的值1或語句1
when 常量2 then 要顯示的值2或語句2
…
else 要顯示的值n或語句n
end

select studentname,gradeid,case gradeid when 1 then '一年級' when 2 then '二年級' when 3 then '三年級' else '四年級' end as 年級 from student order by studentno desc limit 5;

2.區間判斷：類似于python中if-elif-else的效果；

case
when 條件1 then 要顯示的值1或語句1
when 條件2 then 要顯示的值2或語句2
…
else 要顯示的值n或語句n
end

select studentresult ,case when studentresult <60 then '不及格' when studentresult >= 60 and studentresult < 80 then '及格' else '優秀' end as 檔次  from result where subjectno = 1;

case … when和聚合函數聯用

select subjectno,max(studentresult) 最高分,min(studentresult) 最低分,avg(studentresult) 平均分, sum(case when studentresult>=60 then 1 else 0 end)/count(*) 及格率,sum(case when studentresult>=80 then 1 else 0 end)/count(*) 優秀率,sum(case when studentresult < 60 then 1 else 0 end)/count(*) 不及格率 
from result group by subjectno;

系統信息函數

version()函數：查看MySQL系統版本信息號

select version() from dual;

connection_id()函數：查看當前登入用戶的連接次數數

直接調用CONNECTION_ID()函數–不需任何參數–就可以看到當下連接MySQL服務器的連接次數，不同時間段該函數返回值可能是不一樣的

select connection_id();

processlist：查看用戶的連接信息

show processlist;

• Id列：登錄MySQL的用戶標識，是系統自動分配的CONNECTION ID；
• User列：顯示當前的“用戶名”；
• Host列：顯示執行這個語句的IP，用來追蹤出現問題語句的用戶；
• db列：顯示這個進程目前連接的是哪個數據庫；
• Command列：顯示當前連接執行的命令，一般是休眠（Sleep）、查詢（Query）、連接（Connect）；
• Time列：顯示這個狀態持續的時間，單位是秒；
• State列：顯示使用當前連接的SQL語句的狀態，包含有：Copying to tmptable、Sorting result、Sending data等狀態；
• Info列：顯示當前SQL的內容，如果語句過長可能無法顯示完全。

database(),schema()函數

查看當前使用的數據庫

select database(),schema();

user(),current_user(),system_user()函數

獲取當前用戶

select user(),current_user(),system_user();

其它函數

md5(str)函數 加密函數;

• 參數為字符串，該函數為字符串算出一個MD5 128比特校驗和
• 返回值以32位16進制數字的二進制字符串形式返回
• str為NULL，返回NULL
• select md5(‘Susheng’);
  

encode(str，pswd_str)、decode(加密的字符串,pswd_str)函數

加密：encode(被加密的密碼，密碼);
解密：decode(encode(被加密的密碼，密碼),密碼); //也可以用上面返回的二進制字符串

uuid()：生成唯一序列UUID

select uuid();