MySQL數(shù)據(jù)庫：第五章：常見函數(shù)

回退至Mysql數(shù)據(jù)庫理論與實(shí)戰(zhàn)
#進(jìn)階4：常見函數(shù)

函數(shù)：類似于java中的”方法“，將完成某個(gè)功能的一系列步驟封裝起來，對外暴露一個(gè)名字，供外界調(diào)用
當(dāng)我們學(xué)習(xí)別人定義好的方法（函數(shù)），只需要關(guān)心兩件事：
①叫什么（函數(shù)名）
②干什么（函數(shù)功能）
sql中的函數(shù)：肯定有，并且僅有一個(gè)返回值
調(diào)用語法：
select 函數(shù)名(實(shí)參列表);
一、數(shù)學(xué)函數(shù)：
abs絕對值
mod取余
floor向下取整
truncate 截?cái)?br>ceil向上取整
round四舍五入
注意：
sql中的round支持兩個(gè)重載，規(guī)則：先按絕對值四舍五入，然后再添加正負(fù)
round(x):只保留整數(shù)部位
round(x,d):保留小數(shù)點(diǎn)后一位
java中Math.round只支持一個(gè)參數(shù)，規(guī)則：Math.round(x)等價(jià)于Math.floor(x+0.5)
Math.round(x)
rand隨機(jī)數(shù)
二、字符函數(shù)
upper轉(zhuǎn)換成大寫
lower轉(zhuǎn)換成小寫
length獲取字節(jié)長度
char_length獲取字符長度
substr截取子串
trim去掉前后空格或字符
concat拼接
strcmp比較兩個(gè)字符串
instr獲取子串第一次出現(xiàn)的索引，如果找不到，返回0
注意：sql中起始索引，一般從1開始！
三、日期函數(shù)
now當(dāng)前日期+時(shí)間
curdate當(dāng)前日期
curtime當(dāng)前時(shí)間
datediff兩個(gè)日期天數(shù)差
date_format日期——>字符
str_to_date字符——>日期
四、流程控制函數(shù)
1、if函數(shù)
2、case結(jié)構(gòu)
形式1：類似于switch
形式2：類似于多重if

#一、數(shù)學(xué)函數(shù)
#abs絕對值
SELECT ABS(-1.5);
#floor 向下取整，返回<=該參數(shù)的最大整數(shù)
SELECT FLOOR(-1.5);
#ceil 向上取整，返回>=該參數(shù)的最小整數(shù)
SELECT CEIL(-1.5);
#round 四舍五入
SELECT ROUND(-1.56);
SELECT ROUND(-1.56,1);
#truncate 截?cái)?br>SELECT TRUNCATE(1.67,1);
#rand隨機(jī)數(shù) 0——1之間的小數(shù)[0,1)
SELECT RAND();
#mod取余
SELECT MOD(10,3);
SELECT 10%3;
#二、字符函數(shù)
#length字節(jié)長度
SELECT LENGTH(‘john你好’);
#char_length字符長度
SELECT CHAR_LENGTH(‘john你好’);
#upper轉(zhuǎn)大寫
SELECT UPPER(last_name) FROM employees;
#lower轉(zhuǎn)小寫
SELECT LOWER(last_name) FROM employees;
#concat拼接
SELECT CONCAT(first_name,last_name) FROM employees;
#substr截取子串

注意：起始索引從1開始
包含起始索引
substr(str,起始索引，截取字符的長度)：截取從起始索引（包含）開始，指定長度的子串
substr(str,起始索引)：截取從起始索引開始，一直到后面所有的子串

SELECT SUBSTR(‘hello,郭襄愛上了楊過’,1,5);
SELECT SUBSTR(‘hello,郭襄愛上了楊過’,12,2);
SELECT SUBSTR(‘hello,郭襄愛上了楊過’,12);
#練習(xí)：查詢員工的姓名，其中姓首字符大寫，其他字符小寫。
#名前兩個(gè)字符大寫，去其他字符小寫，中間用.拼接，最終起別名 ：姓名
SELECT UPPER(SUBSTR(last_name,1,2))
SELECT LOWER(SUBSTR(last_name,3))
SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),’.’,UPPER(SUBSTR(last_name,1,2)),LOWER(SUBSTR(last_name,3))) 姓名
FROM employees;
#instr

類似于java中indexOf
功能：獲取子串第一次出現(xiàn)的索引，如果找不到，返回0！

SELECT INSTR(‘郭襄張三豐郭襄愛上了郭襄’,‘郭小襄’);
#trim

trim(【substr from 】str)：去掉str前后的指定的子串。如果substr from省略，默認(rèn)去掉空格

SELECT TRIM('哈' FROM '哈哈哈哈he哈llo張 三 豐哈哈哈哈') AS 備注;
#strcmp

功能：比較兩個(gè)字符串大小，
如果前者比后者大，則返回1，
如果前者比后者小，則返回-1
如果相等，則返回0

SELECT STRCMP('hillo','hillo');

#三、日期函數(shù)

#獲取當(dāng)前日期+時(shí)間
SELECT NOW();
#只獲取當(dāng)前日期，不包含時(shí)間
SELECT CURDATE();
#只獲取當(dāng)前時(shí)間，不包含日期
SELECT CURTIME();
#獲取兩個(gè)日期之差(前面-后面的天數(shù)差)
SELECT DATEDIFF('2018-8-18','2018-8-9');
#將日期轉(zhuǎn)換成指定格式的字符串
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s %W') 日期;
#將字符串解析成日期
SELECT STR_TO_DATE('2018-10-19 10:40:09 Friday','%Y-%m-%d %H:%i:%s %W') 日期;
#案例：將入職日期早于'09-09/2000'的員工信息
SELECT * FROM employees WHERE hiredate<STR_TO_DATE('09-09/2000','%m-%d/%Y');

#四、流程控制函數(shù)
#1、if函數(shù)
類似于java的三元運(yùn)算符。
參數(shù)1：條件
參數(shù)2：條件成立，返回的值
參數(shù)3：條件不成立，返回的值

SELECT IF(LENGTH(last_name)>LENGTH(first_name),'名大','姓大') 備注,
last_name,
first_name
FROM employees;

#2、case結(jié)構(gòu)
回顧java的switch case語句
switch(判斷的變量){
case 常量值1:語句1；break;
case 常量值2:語句1；break;
case 常量值3:語句1；break;
…
default:語句n;break
}
特點(diǎn)：做等值判斷

語法1：類似于switch case，實(shí)現(xiàn)等值判斷
case 判斷的字段或表達(dá)式
when 值1 then 顯示的值1
when 值2 then 顯示的值2
…
【else 顯示的值n】
end

#案例：顯示不同職位的新工資。AD_VP 3倍  IT_PROG 4倍 FI_MGR 5倍
SELECT salary 原工資,job_id 職位,
CASE job_id
WHEN 'AD_VP' THEN SALARY*3
WHEN 'IT_PROG' THEN SALARY*4
WHEN 'FI_MGR' THEN SALARY*5
#ELSE SALARY
END  新工資
FROM employees;

語法2：類似于多重if的效果，實(shí)現(xiàn)區(qū)間判斷
case
when 條件1 then 顯示的值1
when 條件2 then 顯示的值2
…
【else 顯示的值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 級別
FROM employees;

#1. 顯示系統(tǒng)時(shí)間(注：日期+時(shí)間)
SELECT NOW();
#2. 查詢員工號，姓名，工資，以及工資提高百分之 20%后的結(jié)果（new salary）
SELECT employee_id,last_name,salary,salary1.2 ‘new salary’
FROM employees;
#3. 將員工的姓名按首字母排序，并寫出姓名的長度（length）
SELECT last_name,LENGTH(last_name) 長度
FROM employees
ORDER BY SUBSTR(last_name,1,1);
#4. 做一個(gè)查詢，產(chǎn)生下面的結(jié)果
<last_name> earns monthly but wants <salary3>
Dream Salary
King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) 'Dream Salary'
FROM employees
WHERE salary=24000;

#5. 使用 case-when，按照下面的條件：
job                      grade
AD_PRES            A
ST_MAN              B
IT_PROG             C
SA_REP               D
ST_CLERK           E
產(chǎn)生下面的結(jié)果
Last_name    Job_id       Grade
king               AD_PRES       A

SELECT last_name,job_id,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END Grade
FROM employees
WHERE last_name = 'k_ing';