1. 聚合函數(shù)

count , sum , min , max ,avg , group_concat()

-- 將所有員工的名字合并成一行 
select group_concat(emp_name) from emp;
-- 指定分隔符合并 
select department,group_concat(emp_name separator ';' ) from emp group by department; 
-- 指定排序方式和分隔符 
select department,group_concat(emp_name order by salary desc separator ';' ) from emp group by department;
-- 附上 sql server 的寫法  比較
select department,STRING_AGG(emp_name ,';') from emp group by department

2. 數(shù)學(xué)函數(shù)

-- 絕對值
select abs(-10)  --返回10
-- 向上取整
select ceil(1.1);  --返回2
select ceil(1.0);  --返回1
-- 向下取整
select floor(1.1);  --返回1
select floor(1.9);  --返回1
-- 返回列表中的最大值
select greatest(1,2,3);  --3
select least(1,2,3);  --1
-- 取模 求余數(shù)
select mod(5,2) --1
-- x的y次方
select power(2,3)  --8
-- 取隨機數(shù)
select rand()  --隨機數(shù)(0到1)
select floor(rand() * 100 ) --100以內(nèi)隨機數(shù)
-- 將小數(shù)四舍五入取整
select round(3.5345)  --4
select round(3.5345,3)  --3.535
-- 將小數(shù)直接截取到指定位數(shù)
select truncate(3.1415,3)  --3.141

3. 字符串函數(shù)

-- 獲取字符串長度
select char_length('hello'); --5
select char_length('你好嗎');  --3
select length('hello'); --5
select length('你好嗎');  --9  length取長度返回的是字節(jié)
-- 字符串合并
select concat('hello','world');  -- helloworld 無分隔符
select concat_ws('-','hello','world'); -- hello-world 有分隔符
-- 返回字符串在列表中第一次出現(xiàn)的位置
select field('aa','aa','bb','cc');  --1
select field('bb','aa','bb','cc');  --2
-- 去除字符串空格
select ltrim('  aaaa');
select rtrim('aaaa    ');
select trim('  aaaa    ');
-- 字符串截取
select mid('helloworld',2,3);  --ell
select substr('helloworld',2,3);  --ell
select substring('helloworld',2,3);  --ell
-- 獲取字符串a(chǎn)在字符串b中的位置
select position('abc' in 'habchelloworld') --2
-- 字符串替換
select replace('habchelloworld','habc','') --helloworld
-- 字符串翻轉(zhuǎn)
select reverse('hello') --olleh
-- 返回字符串的后幾個字符
select right('hello',2)  --lo
-- 小寫轉(zhuǎn)大寫
select ucase('hello');
select upper('hello');
-- 大寫轉(zhuǎn)小寫
select lcase('Hello');
select lower('Hello');

4. 日期函數(shù)

-- 獲取時間戳(毫秒值) 返回從1970-01-01 00:00:00到當(dāng)前毫秒值
select unix_timestamp()
-- 將指定的時間轉(zhuǎn)為毫秒時間戳
select unix_timestamp('2023-11-11 11:11:11')
-- 將時間戳毫秒值轉(zhuǎn)為指定的時間格式
select from_unixtime(1598079966,'%Y-%m-%d %H:%i:%s');
-- 獲取當(dāng)前日期
select curdate() --年月日
select current_date()  --年月日
select current_time()  --時分秒
select curtime()  --時分秒
select current_timestamp() ;-- 年月日時分秒
select now() -- 年月日時分秒
-- 從具體時間中獲取年月日
select date('2023-11-11 11:11:11')  --2023-11-11
-- 獲取日期之間的差值
select datediff('2023-11-11','2023-11-1')  --10
-- 獲取時間的差值(秒級)
select timediff('8:40:00','12:00:00') 
-- 日期格式化
select date_format('2023-1-1 1:1:1','%Y-%m-%d %H:%i:%s') --2023-01-01 01:01:01
-- 將字符串轉(zhuǎn)為日期
select str_to_date('2023-1-1 1:1:1','%Y-%m-%d %H:%i:%s'); --2023-01-01 01:01:01
SELECT str_to_date("August 10 2017", "%M %d %Y") --2017-08-10
-- 將日期進行加減
select date_sub('2023-11-11',interval 2 day) --2023-11-09
select date_add('2023-11-11',interval 2 month) --2024-01-11
-- 從日期中獲取 年|月|日|時|分|秒..
select extract(year from '2023-11-11')
select day('2023-05-01 11:22:33')
select month('2023-05-01 11:22:33')
select quarter('2023-05-01 11:22:33')  --2 季度
select monthname('2023-05-01 11:22:33') -- may
select dayname('2023-05-01 11:22:33') -- Monday 周幾
select dayofweek('2023-05-01 11:22:33') -- 2 這周的第幾天
select dayofmonth('2023-05-01 11:22:33') -- 1 這個月的第幾天
select dayofyear('2023-05-01 11:22:33') -- 121 這年的第幾天
select week('2023-05-01 11:22:33') -- 18 這年的第幾周
-- 獲取給定日期所在月的最后一天
select last_day('2023-11-11') --2023-11-30
--獲取指定年份和天數(shù)的日期
select makedate('2023',53) --2023-02-22

5. 控制流函數(shù)

(1). if邏輯判斷

-- if(expr,v1,v2)  表達式expr成立返回v1,否則返回v2
select if(score>80,'優(yōu)秀','及格') flag ,* from score 
-- ifnull 如果表達式時null，轉(zhuǎn)換顯示為指定值
select ifnull(5,0); -- 5
select ifnull(null,0); -- 0
-- isnull 判斷表達式是否為null
select isnull(5); -- 0
select isnull(null); -- 1
-- nullif(expr1,expr2) 判斷兩個字符串是否相同,相同返回null,不同返回expr1
select nullif(12,12); -- null
select nullif(12,13); -- 12

注意:在sql server 中isnull()的用法與mysql中的ifnull用法一致,沒有ifnull

(2). case when

6. 窗口函數(shù)

mysql 8.0之后增加的,也稱為開窗函數(shù)文章來源地址http://www.zghlxwxcb.cn/news/detail-806876.html

(1). 序號函數(shù)

• row_number( ) --排序 1,2,3
• rank( ) --排序 1,1,3
• dense_rank( ) --排序 1,1,2
• 另外還有開窗聚合函數(shù):sum avg min max

-- 格式
row_number()|rank()|dense_rank() over ( 
  partition by ... 
  order by ... 
) 

(2). 分布函數(shù)

• cume_dist()
  用途：分組內(nèi)小于、等于當(dāng)前rank值的行數(shù) / 分組內(nèi)總行數(shù)

-- 查詢小于等于當(dāng)前薪資
select  dname,ename,salary,
cume_dist() over(order by salary) as rn1, -- 沒有partition語句 所有的數(shù)據(jù)位于一組
cume_dist() over(partition by dept order by salary) as rn2 
from employee;

• percent-rank()
  用途：每行按照公式(rank-1) / (rows-1)進行計算。其中，rank為RANK()函數(shù)產(chǎn)生的序號，rows為當(dāng)前窗口的記錄總行數(shù)

-- 
select dname,ename,salary,
rank() over(partition by dname order by salary desc ) as rn,
percent_rank() over(partition by dname order by salary desc ) as rn2
from employee;

(3). 前后函數(shù)

• lag(expr,n,x)
  用途：返回位于當(dāng)前行的前n行（lag(expr,n)）或后n行（LEAD(expr,n)）的expr的值

-- last_1_time 查詢排序前1名職員的入職時間
-- last_2_time 查詢排序前2名職員的入職時間
-- '2000-01-01'分組的第一個值沒有前一行,所以設(shè)置一個默認值,可不寫,返回null
select dname,ename,hiredate,salary,
lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
from employee;

• lead(expr,n,x)
  用途：返回位于當(dāng)前行的后n行

-- last_1_time 查詢排序后1名職員的入職時間
-- last_2_time 查詢排序后2名職員的入職時間
-- '2000-01-01'分組的第一個值沒有前一行,所以設(shè)置一個默認值,可不寫,返回null
select dname,ename,hiredate,salary,
lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
lead(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
from employee;

(4). 頭尾函數(shù)

• first_value | last_value
  用途：first_value(expr) 到目前為止的排序第一的
  last_value(expr) 到目前為止的最后一個,實際上就是本行的值

-- 截止到當(dāng)前，按照日期排序查詢第1個入職和最后1個入職員工的薪資
-- 注意,  如果不指定ORDER BY，則進行排序混亂，會出現(xiàn)錯誤的結(jié)果
selectdname,ename,hiredate,salary,
first_value(salary) over(partition by dname order by hiredate) as first,
last_value(salary) over(partition by dname order by  hiredate) as last 
from  employee;

(5). 其他函數(shù)

• nth_value(expr,n)
  用途：返回窗口中第n個expr的值。expr可以是表達式，也可以是列名

--截止到當(dāng)前薪資，顯示每個員工的薪資中排名第2或者第3的薪資
select  dname,ename,hiredate,salary,
nth_value(salary,2) over(partition by dname order by hiredate) as second_score,
nth_value(salary,3) over(partition by dname order by hiredate) as third_score
from employee

• ntile(n)
  用途：將分區(qū)中的有序數(shù)據(jù)分為n個等級，記錄等級數(shù)

-- 根據(jù)入職日期將每個部門的員工分成3組
select dname,ename,hiredate,salary,
ntile(3) over(partition by dname order by  hiredate  ) as rn 
from employee;

到了這里，關(guān)于學(xué)習(xí)筆記-mysql-各種函數(shù)的基本使用的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！