ceil函數_【筆記】MySQL-常見函數

一、單行函數

——字元函數

length、concat、upper、lower、substr、instr、trim、lpad、rpad、replace

——數學函數

round、ceil、floor、truncate、mod、rand

——日期函數

now、curdate、curtime、year、month、monthname、day、hour、minute、second、str_to_date、date_format、datediff

——其他函數

version、database

——控制函數

if、case

二、分組函數 sum、avg、max、min、count

概念：類似Python的函數，将一組邏輯語句封裝在函數體中，對外暴露函數名。此處學習的是系統定義好的内置函數，直接調用即可。

好處：1、隐藏了實作細節，直接使用，不用關注内部實作；2、提高了代碼的重用性

調用方法：

select 函數名(實參清單) ；

分類：1、單行函數；如concat、length。給一個值，傳回一個值。

2、分組函數；做統計使用，又稱為統計函數、聚合函數、組函數。給一組值，經過統計或處理傳回一個值。

單行函數

1.字元函數

（1）length(str)：擷取參數值的

位元組

個數

SELECT LENGTH('join')
》4
           

（2）concat(str1,str2,...)：拼接字元串

SELECT CONCAT(studentId,'_',studentName) FROM student;
           

（3）upper(str)，lower(str)：将字元串改為大寫/小寫

SELECT CONCAT(UPPER(studentName),'+',LOWER(studentName)) FROM student;
           

（4）substr/substring：分割字元串

注意：MySQL中索引從1開始

SUBSTR(str,pos)：截取pos開始後面所有字元，包含pos

SELECT SUBSTR('一二三四五六七',3)
-》三四五六七
           

SELECT SUBSTR(str,pos,len)：截取pos處開始指定len長度的字元

-- 從第三位開始取3個字元
           

（5）INSTR(str,substr)：傳回子字元串substr在字元串str中的起始索引；多個傳回第一個索引，找不到傳回0；

SELECT INSTR('一二三四五六七','二');
-》2
           

（6）trim([remstr FROM] str)：去掉字元串首尾remstr字元串，省略則去掉首尾空格，類似Python中的strip；

SELECT TRIM('   nihao     ');
-》nihao
SELECT TRIM('a' FROM 'aaaabbbbbccccaaaa');
-》bbbbbcccc
SELECT TRIM('1112' FROM '111234');
-》34
           

（7）lpad(str,len,padstr)/rpad(str,len,padstr)：左邊/右邊填充指定字元padstr達到總長度為len；若str長度超過len，則右側部分截斷（無論lpad還是rpad都是截斷右側）；

SELECT LPAD('一二三四五六七',10,'*');
-》***一二三四五六七
SELECT LPAD('一二三四五六七',5,'*');
-》一二三四五
SELECT rPAD('一二三四五六七',10,'ab');
-》一二三四五六七aba
           

（9）replace(str,str1,str2)：把str字元串中的str1全部替換為str2；

SELECT REPLACE('大寶大寶愛上了小寶小寶','大寶','二寶');
-》二寶二寶愛上了小寶小寶
           

2.數學函數

（1）round(X,D)：四舍五入函數，把X四舍五入，保留D位小數，D省略則四舍五入為整數，負數也可以四舍五入，按照絕對值四舍五入後取負數；

SELECT ROUND(1.23);
-》1
SELECT ROUND(1.56);
-》2
SELECT ROUND(1.5655,2);
-》1.57
SELECT ROUND(-1.5655,2);
-》1.57
           

（2）ceil(X)： 向上取整，傳回>=該參數的最小整數，負數也是同樣；

SELECT CEIL(1.5);
-》2
SELECT CEIL(1.00);
-》1
SELECT CEIL(-1.50);
-》-1
           

（3）floor(X)：向下取整，傳回<=該參數的最小整數，負數也同樣；

SELECT FLOOR(-1.50);
-》-2
SELECT FLOOR(1.50);
-》1
           

（4）truncate(X,D)：截斷X，小數點後保留D位；

SELECT TRUNCATE(1.699,1);
-》1.6
SELECT TRUNCATE(-1.699,1);
-》-1.6
           

（5）mod(N,M)：取餘數，同N%M；

SELECT MOD(10,3);
-》1
SELECT 10%3;
-》1
SELECT MOD(-10,3);
-》-1
           

（6）rand()：擷取随機數，傳回0-1之間的小數

3.日期函數

（1）now()：傳回目前的系統日期+時間

SELECT NOW()
-》2020-03-04 11:22:51
           

（2）curdata()：傳回目前日期，不包含時間

SELECT CURDATE()
-》2020-03-04
           

（3）curtime()：傳回目前時間，不包含日期

SELECT CURTIME()
-》11:24:38
           

（4）擷取指定的部分，年、月、日、小時等

SELECT YEAR(CURDATE());# 擷取年份
-》2020
SELECT YEAR(NOW());
-》2020
SELECT YEAR('1998-1-5');
-》1998
SELECT MONTH('1998-1-5');# 擷取月份
-》1
SELECT MONTHNAME('1998-1-5');#擷取英文月份
-》January
SELECT DAY('1998-1-5');# 擷取日期
-》5
SELECT DAYNAME('2020-3-6');# 擷取日期的星期幾
-》Friday
SELECT HOUR(NOW());#擷取小時
-》11
SELECT MINUTE(NOW());#擷取分鐘
SELECT SECOND(NOW());#擷取秒
           

（5）（常用）str_to_data(str,format)：将日期格式的字元轉換成指定格式的日期

SELECT STR_TO_DATE('9-13-98','%m-%d-%y');
-》1998-09-13
           

（6）data_format(date,format)：将日期轉換成字元

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日')
-》20年03月04日
           

（7）datediff(expr1,expr2)：傳回expr1日期和expr2的日期差的天數；

SELECT DATEDIFF('20170509','20180509');
-》-365
SELECT DATEDIFF(NOW(),'1995-1-1');
->>9194
           

4.其他函數（使用較少）

（1）version()：檢視版本号

SELECT VERSION();
-》8.0.17
           

（2）database()：檢視目前使用資料庫

SELECT DATABASE();
-》school
           

（3）user()：檢視目前使用者

SELECT USER();
->[email protected]
           

5.流程控制函數

（1）if(expr1,expr2,expr3)函數：if else效果，條件1expr1為true，傳回expr2，False傳回expr3；

SELECT IF(10>5,'大','小');
-》大
           

（2）case函數使用1：适用于

等值

判斷；case可以作為一個表達式使用，也可以作為一個語句

#case文法
case 要判斷的字段或表達式 
when 常量1 then 要顯示的值1或語句1;#值後無需加;
when 常量2 then 要顯示的值2或語句2;
...
else 要顯示的值或語句;
end
           

案例：(case作為一個表達式）

#查詢員工的工資，要求部門号=30，顯示工資為1.1倍
#部門号=40，工資為1.2倍
#其他部門為原工資

select salary as 原始工資,department_id,
-----
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
else salary
end as 新工資
-----
from employees;
           

case函數使用2：适用于

區間

判斷；類似多重if，與使用1差別是case後面不加内容

#case文法
case #後面不加内容
when 條件1 then 要顯示的值1 或語句1;
when 條件2 then 要顯示的值2 或語句2;
...
else 要顯示的值n 或語句n;
end
           

案例：

#如果工資>20000，顯示A級别；>15000，顯示B級别；其他顯示C級别
------
select salary,
case
when salary>20000 then 'A'
when salary>15000 then 'B'
else 'C'
end as 工資級别
------
from employees;
           

分組函數

——參數支援哪些類型？

sum、avg數值型；（字元、日期均不建議，計算無意義）

max、min數值、字元、日期均支援，因為可以進行排序，取排序後的最大最小值；

count也是任何類型均可，計算的非null值

——是否支援null？

（null加任意值結果均為null）

sum、avg、count、max、min均計算時忽略null值

——和關鍵字distinct搭配 sum、avg、count均支援distinct搭配使用，代表去重後再計算 ——count函數詳細介紹 count(字段/*/1); *統計所有行數。1加了一列1，統計1的個數，也可以寫2。效率的話和存儲引擎有關系，count(字段)最慢，因為中間加了判斷過程，需要看列是否為null。 ——和分組函數一同查詢的字段需要是group by後的，其他的因為分組傳回傳回一個值，未分組的字段顯示無參考意義。

1.sum求和

2.avg求平均數

3.max求最大值

4.min求最小值

5.count計算個數

SELECT