mysql常見函數及其用例

函數調用：select 函數名(實參列表) 【from 表】;

函數分類：

1、單行函數

如 concat、length、ifnull等。

2、分組函數 

功能：做統計使用，又稱為統計函數、聚合函數、組函數。

資料庫結構圖：

常見函數：

一、單行函數

字元函數：

length:獲取位元組個數(utf-8一個漢字代表3個位元組,gbk為2個位元組)
concat
substr
instr
trim
upper
lower
lpad
rpad
replace

數學函數：

round
ceil
floor
truncate
mod

日期函數：

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

其他函數： 

version
database
user

 控制函數：

if
case

一、字元函數

1.length 獲取參數值的位元組個數

SELECT LENGTH('john');

SELECT LENGTH('張三豐hahaha'); 

2.concat 拼接字元串

SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

3.upper、lower

SELECT UPPER('john');

SELECT LOWER('joHn');

示例：將姓變大寫，名變小寫，然後拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM employees;

4.substr、substring

註意：索引從1開始

截取從指定索引處後面所有字元

SELECT SUBSTR('重中之重做做做',7)  out_put;

截取從指定索引處指定字元長度的字元

SELECT SUBSTR('中中中中轉組織',1,3) out_put;

案例：姓名中首字元大寫，其他字元小寫然後用_拼接，顯示出來

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put FROM employees;

5.instr 返回子串第一次出現的索引，如果找不到返回0

SELECT INSTR('富家大室可憐中國人','殷八俠') AS out_put;

6.trim

SELECT LENGTH(TRIM('    中國人    ')) AS out_put; SELECT TRIM('aa' FROM 'aaaaaaaaa中aaaaaaaaaaaa國人aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;

7.lpad 用指定的字元實現左填充指定長度

SELECT LPAD('中國人',2,'!') AS out_put;

8.rpad 用指定的字元實現右填充指定長度

SELECT RPAD('中國人',12,'aa') AS out_put; 

9.replace 替換

SELECT REPLACE('今天我在中國','今天','前天') AS out_put; 

二、數學函數

round 四捨五入

SELECT ROUND(-1.55); SELECT ROUND(1.567,2);

ceil 向上取整,返回>=該參數的最小整數

SELECT CEIL(-1.02);

floor 向下取整，返回<=該參數的最大整數

SELECT FLOOR(-9.99);

truncate 截斷

SELECT TRUNCATE(1.69999,1);

mod取餘

SELECT MOD(10,-3); 

SELECT 10%3;

三、日期函數

now 返回當前系統日期+時間

SELECT NOW();

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

SELECT CURDATE();

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

SELECT CURTIME();

可以獲取指定的部分，年、月、日、小時、分鐘、秒

SELECT YEAR(NOW()) 年; SELECT YEAR('1998-1-1') 年; SELECT  YEAR(hiredate) 年 FROM employees; SELECT MONTH(NOW()) 月; SELECT MONTHNAME(NOW()) 月;

str_to_date 將字元通過指定的格式轉換成日期

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

查詢入職日期為1992--4-3的員工信息

SELECT * FROM employees WHERE hiredate = '1992-4-3'; SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

date_format 將日期轉換成字元

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;

查詢有獎金的員工名和入職日期(xx月/xx日 xx年)

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入職日期 FROM employees WHERE commission_pct IS NOT NULL;

 四、其他函數

SELECT VERSION(); SELECT DATABASE(); 

SELECT USER();

五、流程式控制制函數

1.if函數： if else 的效果

SELECT IF(10<5,'大','小'); SELECT last_name,commission_pct,IF(commission_pct IS NULL,'沒獎金','有獎金') 備註 FROM employees;

2.case函數的使用一： 

case 要判斷的欄位或表達式

when 常量1 then 要顯示的值1或語句1;

when 常量2 then 要顯示的值2或語句2;

...

else 要顯示的值n或語句n;

end

*/

 

/*案例：查詢員工的工資，要求

 

部門號=30，顯示的工資為1.1倍

部門號=40，顯示的工資為1.2倍

部門號=50，顯示的工資為1.3倍

其他部門，顯示的工資為原工資

 

*/

SELECT salary 原始工資,department_id, CASE department_id WHEN 30 THEN salary*1.1 WHEN 40 THEN salary*1.2 WHEN 50 THEN salary*1.3 ELSE salary END AS 新工資 FROM employees;

3.case 函數的使用二：類似於 多重if 

case

when 條件1 then 要顯示的值1或語句1

when 條件2 then 要顯示的值2或語句2

。。。

else 要顯示的值n或語句n

end

*/

 

案例：查詢員工的工資的情況

如果工資>20000,顯示A級別

如果工資>15000,顯示B級別

如果工資>10000，顯示C級別

否則，顯示D級別

SELECT salary, CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE 'D' END AS 工資級別 FROM employees;