函數(shù)的分類(lèi)：
1缘回，單行函數(shù)：對(duì)每一條記錄輸入值進(jìn)行計(jì)算吆视，得到相應(yīng)的計(jì)算結(jié)果，返回給用戶(hù)酥宴，也就是說(shuō)啦吧，每條記錄作為一個(gè)輸入?yún)?shù)，經(jīng)過(guò)函數(shù)計(jì)算得到每條記錄的計(jì)算結(jié)果拙寡。
2授滓，多行函數(shù)：對(duì)多條記錄輸入值進(jìn)行計(jì)算，得到多條記錄對(duì)應(yīng)的單個(gè)結(jié)果。
單行函數(shù)細(xì)分
1般堆、字符函數(shù)
2在孝、數(shù)學(xué)函數(shù)
3、日期函數(shù)
4淮摔、其他函數(shù)
5私沮、流程控制函數(shù)
單行函數(shù) - 字符函數(shù)
一、字符函數(shù)
1. length 獲取參數(shù)的字節(jié)長(zhǎng)度
SELECT LENGTH('john');
2. concat 拼接字符
SELECT CONCAT(last_name,'_',first_name) FROM employees;
3. upper,lower
SELECT UPPER('john');
SELECT LOWER('JOHN');
案例：將姓變大寫(xiě)和橙，將名變小寫(xiě)仔燕，然后拼接
SELECT CONCAT(UPPER(first_name), LOWER(first_name)) AS "姓名" FROM employees;
4. substr
4個(gè)重載的方法
注意：SQL語(yǔ)言的索引從1開(kāi)始
作用：截取從pos開(kāi)始的所有字符，包括pos
SELECT SUBSTR('zhangjin', 6); # jin
作用：截取從pos開(kāi)始, 字符長(zhǎng)度為len的子串
SELECT SUBSTR('zhangjin',1,5); # zhang
案例：姓名中首字母大寫(xiě)魔招，其他字符小寫(xiě)
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)), LOWER(SUBSTR(last_name,2)))
AS "姓"
FROM employees;
5. instr 返回子串第一次出現(xiàn)的起始索引晰搀，沒(méi)有出現(xiàn)則返回0
SELECT INSTR('zhangjin','shay'); # 0
SELECT INSTR('zhangjin','jin'); # 6
SELECT INSTR('zhangjinjinjinjinjinjin','jin'); # 6
6、trim
默認(rèn)是trim左右兩邊的空格
SELECT TRIM(' zhangjin ');
可以指定要trim的字符
SELECT TRIM('a' FROM 'aaaaaazhangjinaaaa');
7办斑、lpad
使用指定字符進(jìn)行左填充外恕，填充后的字符個(gè)數(shù)為len
len，字符個(gè)數(shù)俄周，并不是字節(jié)長(zhǎng)度
SELECT LPAD('張',3,'*'); # **張
最終長(zhǎng)度由len決定
SELECT LPAD('張三豐',2,'*'); # 張三
8吁讨、rpad
使用指定字符進(jìn)行右填充，填充后的字符個(gè)數(shù)為len
len峦朗，字符個(gè)數(shù)建丧，并不是字節(jié)長(zhǎng)度
SELECT RPAD('張',3,''); # 張*
最終長(zhǎng)度由len決定
SELECT RPAD('張三豐',2,'*'); # 張三
9、replace
replace(str,from_str,to_str) 全部替換
SELECT REPLACE('zhangjin', 'zhang', 'shay');
SELECT REPLACE('zhangjinzhangjinzhangjin', 'zhang', 'shay');
一些練習(xí)
-- 寫(xiě)一個(gè)查詢(xún),用首字母大寫(xiě)波势，其它字母小寫(xiě)顯示雇員的 ename翎朱，顯示名字的長(zhǎng)度，并給每列一個(gè)適當(dāng)?shù)臉?biāo)簽尺铣，條件是滿(mǎn)足所有雇員名字的開(kāi)始字母是J拴曲、A 或 M 的雇員，并對(duì)查詢(xún)結(jié)果按雇員的ename升序排序凛忿。（提示：使用initcap澈灼、length、substr）
select concat(SUBSTR(ename,1,1),LOWER(SUBSTR(ename,2,LENGTH(ename)-1))),LENGTH(ename) from emp
where SUBSTR(ename,1,1) in ('J','A','M')
order by ename
-- 查詢(xún)員工姓名中中包含大寫(xiě)或小寫(xiě)字母A的員工姓名店溢。
select ename from emp
where INSTR(ename,'A') > 0
2.查詢(xún)部門(mén)編號(hào)為10或20叁熔，入職日期在81年5月1日之后，并且姓名中包含大寫(xiě)字母A的員工姓名床牧，員工姓名長(zhǎng)度（提示荣回，要求使用INSTR函數(shù)，不能使用like進(jìn)行判斷)
3.查詢(xún)每個(gè)職工的編號(hào),姓名戈咳，工資
要求將查詢(xún)到的數(shù)據(jù)按照一定的格式合并成一個(gè)字符串.
前10位：編號(hào),不足部分用*填充,左對(duì)齊
中間10位：姓名心软，不足部分用*填充,左對(duì)齊
后10位：工資壕吹，不足部分用*填充,右對(duì)齊
二、數(shù)學(xué)函數(shù)
round 四舍五入
SELECT ROUND(1.45);
SELECT ROUND(-1.65);
SELECT ROUND(1.567,2); #保留2位小數(shù)
ceil 向上取整, 返回大于等于該參數(shù)的最小整數(shù)
SELECT CEIL(1.002); #2
SELECT CEIL(1.00); #1
floor 向下取整删铃，返回小于等于該參數(shù)的最大整數(shù)
SELECT FLOOR(1.002); #1
truncate 截?cái)?/h1>
SELECT TRUNCATE(1.65,1); #保留1位小數(shù)
mod 取余數(shù)
SELECT MOD(10,3); # 10%3=1
SELECT MOD(-10,-3); # -1, 被除數(shù)為負(fù)數(shù)耳贬，結(jié)果為負(fù)數(shù)
一些練習(xí)
1.寫(xiě)一個(gè)查詢(xún)，分別計(jì)算100.456 四舍五入到小數(shù)點(diǎn)后第2位泳姐，第1位效拭，整數(shù)位的值暂吉。
2.寫(xiě)一個(gè)查詢(xún)胖秒，分別計(jì)算100.456 從小數(shù)點(diǎn)后第2位，第1位慕的，整數(shù)位截?cái)嗟闹怠?
三阎肝、日期函數(shù)
now 返回當(dāng)前系統(tǒng)日期+時(shí)間
select now();
curdate 返回當(dāng)前系統(tǒng)日期，不包括日期
select curdate();
curtime 返回當(dāng)前時(shí)間肮街，不包括日期
select curtime();
可以獲取指定的部分：年风题，月，日嫉父，時(shí)沛硅，分，秒
獲取年
select year(now()) as 年;
select year('1986-1-1');
select year(hiredate) from employees;
獲取月
select month(now()) as 月; # 11
select monthname(now()) as 月; # November
獲取日
select day(now()) as 日; # 24
select dayname(now()) as 星期; # Friday
其他一些日期函數(shù)
-- datediff(date1,date2)：兩個(gè)日期相減 date1 - date2绕辖，返回天數(shù)
SELECT DATEDIFF('2017-06-05','2017-05-29');-- 7
SELECT LAST_DAY('2016-02-01');-- 2016-02-29 (返回月份中最后一天)
SELECT LAST_DAY('2016-05-01');-- 2016-05-31
-- DATE_ADD(date,INTERVAL expr type) 從日期加上指定的時(shí)間間隔
-- type參數(shù)可參考：http://www.w3school.com.cn/sql/func_date_sub.asp
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 YEAR);-- 表示：2018-05-15 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 QUARTER);-- 表示：2017-08-15 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MONTH);-- 表示：2017-06-15 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 WEEK);-- 表示：2017-05-22 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 DAY);-- 表示：2017-05-16 10:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 HOUR);-- 表示：2017-05-15 11:37:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MINUTE);-- 表示：2017-05-15 10:38:14.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 SECOND);-- 表示：2017-05-15 10:37:15.123456
SELECT DATE_ADD('2017-05-15 10:37:14.123456',INTERVAL 1 MICROSECOND);-- 表示：2017-05-15 10:37:14.123457
-- DATE_SUB(date,INTERVAL expr type) 從日期減去指定的時(shí)間間隔
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 YEAR);-- 表示：2016-05-15 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 QUARTER);-- 表示：2017-02-15 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MONTH);-- 表示：2017-04-15 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 WEEK);-- 表示：2017-05-08 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 DAY);-- 表示：2017-05-14 10:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 HOUR);-- 表示：2017-05-15 09:37:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MINUTE);-- 表示：2017-05-15 10:36:14.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 SECOND);-- 表示：2017-05-15 10:37:13.123456
SELECT DATE_SUB('2017-05-15 10:37:14.123456',INTERVAL 1 MICROSECOND);-- 表示：2017-05-15 10:37:14.123455
SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);-- 前一天：2017-05-11
SELECT DATE_SUB(CURDATE(),INTERVAL -1 DAY);-- 后一天：2017-05-13
SELECT DATE_SUB(CURDATE(),INTERVAL 1 MONTH);-- 一個(gè)月前日期：2017-04-12
SELECT DATE_SUB(CURDATE(),INTERVAL -1 MONTH);-- 一個(gè)月后日期：2017-06-12
SELECT DATE_SUB(CURDATE(),INTERVAL 1 YEAR);-- 一年前日期：2016-05-12
SELECT DATE_SUB(CURDATE(),INTERVAL -1 YEAR);-- 一年后日期：20178-06-12
一些練習(xí)
0.查詢(xún)每個(gè)員工截止到現(xiàn)在一共入職多少天摇肌？
1.查詢(xún)服務(wù)器當(dāng)前時(shí)間
-- 查詢(xún)部門(mén)10,20的員工截止到2000年1月1日，工作了多少個(gè)月仪际，入職的月份围小。
select ename,month(hiredate), (year('2000-01-01') -year(hiredate))*12 + month('2000-01-01') -month(hiredate) from emp;
-- 如果員工試用期6個(gè)月，查詢(xún)職位不是MANAGER的員工姓名树碱，入職日期肯适，轉(zhuǎn)正日期，入職日期后的第一個(gè)星期一,入職當(dāng)月的最后一天日期成榜。
select ename,hiredate,DATE_ADD(hiredate,INTERVAL 6 MONTH),DATE_ADD(hiredate,INTERVAL 7-DAYOFWEEK(hiredate)+2 DAY),LAST_DAY(hiredate)from emp
類(lèi)型轉(zhuǎn)換函數(shù)
將日期格式的字符串 -> 日期
str_to_date('24-11-2017', '%d-%m-%Y');
select str_to_date('24-11-2017', '%d-%m-%Y');
案例：將用戶(hù)輸入的4-3 1992作為查詢(xún)依據(jù)框舔，找出在1992年4月3日入職的員工
select * from emp
where hiredate = str_to_date('12-17 1980', '%m-%d %Y');
日期 -> 字符
date_format('2018/6/6', '%Y年%m月%d日')
select date_format('2018/6/6', '%Y年%m月%d日');
案例：查詢(xún)有獎(jiǎng)金的員工名和入職日期（XX月/XX日 XX年）
select ename,date_format(hiredate, '%m月/%d日 %y年') as 入職日期
from emp
where comm is not null;
mysql 字符串?dāng)?shù)字轉(zhuǎn)換
//字符串轉(zhuǎn)數(shù)字
1 方法一：SELECT CAST('123' AS SIGNED);
2 方法二：SELECT CONVERT('123',SIGNED);
3 方法三：SELECT '123'+0;
//數(shù)字轉(zhuǎn)字符串
CONCAT()
FORMAT函數(shù)
FORMAT函數(shù)在mysql中是數(shù)據(jù)內(nèi)容格式化的，格式化后得到結(jié)果：＃＃＃赎婚，＃＃＃刘绣，＃＃＃＃＃。
SELECT FORMAT(100000,2);//100,000.00
一些練習(xí)
1.顯示服務(wù)器系統(tǒng)當(dāng)前時(shí)間惑淳，格式為2007-10-12 17:11:11
2.顯示ename额港、hiredate 和 雇員開(kāi)始工作日是星期幾，列標(biāo)簽DAY
3.查詢(xún)員工姓名歧焦，工資移斩，格式化的工資（￥999,999.99）
4.把字符串2015-3月-18 13:13:13 轉(zhuǎn)換成日期格式肚医，并計(jì)算和系統(tǒng)當(dāng)前時(shí)間間隔多少天。
case控制結(jié)構(gòu)的使用一：switch case的效果
/*
適合：等值判斷
mysql中case控制結(jié)構(gòu)：
case 要判斷的字段或表達(dá)式
when 常量1 then 要顯示的值1或語(yǔ)句1;
when 常量2 then 要顯示的值2或語(yǔ)句2;
when 常量3 then 要顯示的值3或語(yǔ)句3;
when 常量4 then 要顯示的值4或語(yǔ)句4;
else 要顯示的值n或語(yǔ)句n;
end
*/
/案例：查詢(xún)員工的工資向瓷，要求
部門(mén)號(hào)=30肠套，顯示的工資為1.1倍
部門(mén)號(hào)=40，顯示的工資為1.2倍
部門(mén)號(hào)=50猖任，顯示的工資為1.3倍
其他部門(mén)你稚，顯示工資為原工資
/
SELECT salary,department_id,
CASE department_id
WHEN 30 THEN 1.1salary
WHEN 40 THEN 1.2salary
WHEN 50 THEN 1.3*salary
ELSE salary
END
AS "新工資"
FROM employees;
case控制結(jié)構(gòu)的使用二：類(lèi)似多重if
/*
mysql中case控制結(jié)構(gòu)：
適合：區(qū)間判斷（大小）
case
when 條件1(true,false) 要顯示的值1（尾部不加分號(hào)）或語(yǔ)句1（尾部加分號(hào)）
when 條件2(true,false) 要顯示的值2（尾部不加分號(hào)）或語(yǔ)句2（尾部加分號(hào)）
....
else 要顯示的值n(尾部不加分號(hào)）或語(yǔ)句n(尾部加分號(hào)）
end
*/
案例：查詢(xún)員工的工資情況
/*
如果工資大于2萬(wàn)朱躺，顯示A級(jí)別
如果工資大于1萬(wàn)5刁赖，顯示B級(jí)別
如果工資大于1萬(wàn)，顯示C級(jí)別
否則长搀，顯示D級(jí)別
*/
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END
AS "工資級(jí)別"
FROM employees;
課后作業(yè)
1.計(jì)算2000年1月1日到現(xiàn)在有多少月宇弛，多少周（四舍五入）。
2.查詢(xún)員工ENAME的第三個(gè)字母是A的員工的信息(使用2個(gè)函數(shù))源请。
3.使用trim函數(shù)將字符串‘hello’枪芒、‘ Hello ’、‘bllb’谁尸、‘ hello ’分別處理得到下列字符串ello舅踪、Hello、ll良蛮、hello抽碌。
4.將員工工資按如下格式顯示：123,234.00 RMB 。
5.查詢(xún)員工的姓名及其經(jīng)理編號(hào)背镇，要求對(duì)于沒(méi)有經(jīng)理的顯示“No Manager”字符串咬展。
6.將員工的參加工作日期按如下格式顯示：月份/年份。
7.在員工表中查詢(xún)出員工的工資瞒斩，并計(jì)算應(yīng)交稅款：如果工資小于1000,稅率為0破婆，如果工資大于等于1000并小于2000，稅率為10％胸囱，如果工資大于等于2000并小于3000祷舀，稅率為15％，如果工資大于等于3000烹笔，稅率為20％裳扯。
8.創(chuàng)建一個(gè)查詢(xún)顯示所有雇員的 ename和 sal。格式化sal為 15 個(gè)字符長(zhǎng)度谤职，用 $ 左填充饰豺，列標(biāo)簽 SALARY。
