MYSQL函數使用實例分析

MYSQL函數

1:聚合函數

聚合函數主要由:count,sum,mi​​n,max,avg,group_count()

#重點說一下group_count( )函數,首先根據group by指定的列進行分組,並且用分隔符分隔,將同一個分組中的值連接起來,返回一個字符串結果. 

格式: group_count([distinct ]欄位名稱[order by 排序欄位asc/desc] [separator '分隔符號'])

說明:

      1:使用distinct可以排除重複值.
      1:使用distinct可以排除重複值.
      2 :如果需要對結果值進行排序,可以使用order by 子句.

      3:separator是一個字串值,預設為逗號.

##2:數學函數

1:ABS(x) 傳回x的絕對值
2:CEIL(x)傳回大於等於x的最小整數(向上取整)
3:FLOOR(x) 傳回小於等於x的最大整數(向下取整)
4:GREATEST(expr1,expr2...)傳回清單中的最大值
5:LEAST(expr1,expr2.....)傳回清單的最小值
6 :MAX(x) 傳回欄位x的最大值
7:MIN(x) 傳回欄位x的最小值
8:MOD(x,y) 傳回x除以y以後的餘數
9: PI()傳回圓周率(3.141593)
10:POW(x,y) 傳回x的y次方
11:RAND() 傳回0到1的隨機數
12:ROUND(x) 返回離x最近的整數(遵循四捨五入)
13:ROUND(x,y) 傳回指定位數的小數(遵循四捨五入)
14:TRUNCATE(x,y) 傳回數值x保留到小數點後y位的值,(與ROUND最大的去區別是不會進行四捨五入)

2:字串函數

1:char_length(s) 傳回字串s的字元數
2:character_length 傳回字串s的字元數
3:concat(s1,s2,s3) 字串s1,s2等多個字串合併為一個字串
4:concat_ws( x,s1,s2..) 同concat(s1,s2,s3)函數,但是每個字串之間要加上x,x可以是分隔符號
5:field(s,s1,s2) 返回第一個字串s在字串列表(s1,s2..)中的位置
6:length() 回傳的是位元組數mysql中utf-8的編碼一個中文漢字是三個位元組
7:ltrim(s) 去掉字串s開始處的空格去除左邊的空格 rtrim()去除右邊的空格trim()去除兩邊的空格
8:mid(s,n,len) 從字串s的n位置截取長度為len的子字串同substring(s,n,len)
9:position(s1,in,s) 從字串s中取得s1的起始位置
10:replcae (s,s1,s2) 將字串s2取代字串s中的字串s1
11:reverse(s) 將字串s的順序反過來
12:right(s,n)傳回字符串s的後n個字元(從右取n個字元)
13:strcmp(s1,s2) 比較字串s1和s2,如果s1與s2相等回傳0,如果s1>s2回傳1 如果s1小於s2返回-1
14:substr(s,start,length) 從字串s的start位置截取長度為length的子字串
15:ucase(s) upper(s)將字串轉換為大寫
16:lcase(s) lower(s) 將字串轉換為小寫

3:日期函數

1:unix_timestamp() 傳回1970- 01-01 00:00:00 到目前毫秒值
2:unix_timestamp(date_string) 將指定日期轉換為毫秒值時間戳
3:from_unixtime(bigint unixtime,string-format) 將毫秒值戳時間轉為指定格式日期
4:curdate() 傳回目前日期
5:current_date() 傳回目前日期
6:current_timestamp() 傳回目前日期和時間
7:datediff(d1,d2)計算日期d1>d2之間間隔的天數 eg:datediff('2022-01-01','2022-02-01')
8:currtime() 返回目前時間 
9:date_format(d, f) 依照表達式f的要求顯示日期d

4:控制流程函數

1:if(expr,v1,v2) 如果表達式expr成立,​​返回結果v1,否則回傳結果v2
2:ifnull(v1,v2) 如果v1的值為null則回傳v1,否則回傳v2 
3:isnull(expression) 判斷表達式是否為null 
4 :nullif(expr1,expr2)  比較兩個字串如果字串expr1和expr2相等回傳null 否則回傳expr1
5:case expression when condition1 then result1  when condition2 then result2 else result end 表示函數開始表示函數結束,如果condition1成立則返回result1,如果condition2成立則返回result2,當全部不成立則返回result,而當有一個成立之後,後面的就不執行了.

5:窗口函數

mysql8.0新增的視窗函數又稱為開窗函數,非聚合視窗函數是相對於聚合函數來說,聚合函數是對一組資料計算後傳回單一值(即分組),非聚合函數一次只會處理一行資料,視窗聚合函數在行記錄上計算某個欄位的結果時,可將視窗範圍內的資料輸入到聚合函數,並不會改變行數

#5.1序號函數

可以實現分組排序,並新增序號

1: row_number()
2: rank()
3: dense_rank() 

寫入法:select id,...,dense_rank() over(partition by dname order by salary desc) as rn from employee;

#注意: 不加partition by表示全域排序

5.2 分佈函數

1: percent_rank()
  用途:每行依照公式(rank-1)/(row- 1)進行計算.其中rank為rank()函數產生的序號,row為目前視窗的記錄總行數
2: cume_dist()
  用途:分組內小於,等於目前rank值的行數/分組內總行數
  應用場景:查詢小於等於當前薪資的比例

寫法:select dname,ename,salary,cume_dist() over(order by salary) as rn1,
cume_dist() over(partition by dname order by salary) as rn2 from employe;

5.3 前後函數

1: lag(expr,n)
2: lead(expr,n)

#用途:傳回位於目前行的前n行(lag(exor,n))或後n行(lead(expr,n))的expr的值

應用場景:查詢前1名同學的成績和當前同學成績的差值(當前行內可以有個上一行資料的某一字段值)

5.4 頭尾函數

1: first_value(expr)
2: last_value(expr)

用途:傳回第一個(first_value(expr))或最後一個(last_value(expr)) expr的值
應用程式場景:截止到目前,依照日期排序查詢第1個入職和最後一個入職員工的薪資

#5.5 其他函數

1: nth_value(expr,n)
2: ntile(n)

用途:返回視窗中第n個expr的值,expr可以是表達式,也可以是列名
應用場景:截止到目前薪資,顯示每位員工的薪資中排名第二或第三的薪資

#5.6 開窗聚合函數

#1: sum()
2: avg()
3: min()
4: max()

寫法:select id,.. .,sum(salary) over(partition by dname order by hiredate desc) as rn from employee;

每一行rn的資料是當期行和之前每一行的salary之和
如果沒有order by排序語句,預設把分組內的所有資料做sum操作

#

以上是MYSQL函數使用實例分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！