概述
《SQL.Cookbook》是迄今為止，筆者遇到過(guò)sql系列最全、案例最精簡(jiǎn)的一本書(shū)。
許多真實(shí)環(huán)境遇到的棘手問(wèn)題捡偏，本書(shū)都提供了解決方案怖糊。許多面試的sql題目(無(wú)論是Oracle帅容、MySQL還是HIVE)，本書(shū)也提供了解決方案伍伤。如果想提升自己的sql編寫(xiě)能力并徘，本書(shū)是不二之選。
筆者陸陸續(xù)續(xù)的看過(guò)《SQL.Cookbook》多次扰魂，也從中收獲頗多麦乞，近段時(shí)間，筆者將書(shū)中涉及的MySQL的章節(jié)劝评，用MySQL 8.0 的窗口函數(shù)及with語(yǔ)句進(jìn)行編寫(xiě)姐直，也形成了系列博客，特此整理為系列博客蒋畜。
一.檢索記錄
1.1 從表中檢索所有行和列
需要檢索所有列声畏，一般是直接用"*"來(lái)代表
需要注意如果是查看數(shù)據(jù)庫(kù)中的數(shù)據(jù)可以用 星號(hào)來(lái)代替，如果是代碼中姻成，最好將每個(gè)列都列明插龄，方便后面引用列。
代碼:
select * from emp
1.2 從表中檢索部分行
使用where子句可以指定要保留哪些行科展。
例如要查看部門(mén)編號(hào)為10的所有員工:
select * from emp where deptno = 10
1.3 查找滿足多個(gè)條件的行
使用 where子句以及 OR 和 AND 子句辫狼。
例如，如果要查找部門(mén)10中所有員工辛润，且有提成的員工
select *
from emp
where deptno = 20
and comm is not null
1.4 從表中檢索部分列
指定感興趣的列膨处。
例如，如果只查看員工的名字砂竖、部門(mén)號(hào)和工資
select ename,deptno,sal
from emp
1.5 為列取有意義的名稱
要改變查詢結(jié)果列名,可以按這種格式使用AS關(guān)鍵字: 原名 AS 新名(一些數(shù)據(jù)庫(kù)可以省略AS)
select sal as salary, comm as commission
from emp
1.6 在where子句中引用取別名的列
1.7 連接列值
1.8 在select語(yǔ)句中使用條件邏輯
使用CASE 表達(dá)式 直接在 SELECT 語(yǔ)句中執(zhí)行條件邏輯真椿。
需求: 要產(chǎn)生一個(gè)結(jié)果集如果一個(gè)員工工資小于2000美金，就返回消息"UNDERPAID"乎澄，如果大于等于4000美金突硝，就返回消息"OVERPAID",如果在這兩者之間，就返回"OK".
select ename, sal,
case when sal <= 2000 then 'UNDERPAID'
when sal >= 4000 then 'OVERPAID'
else 'OK'
end as status
from emp
1.9 限制返回行數(shù)
限制查詢中返回的行數(shù)置济，這里不關(guān)心順序解恰，返回任何n行都行。
select * from emp limit 5
1.10 從表中隨機(jī)返回n行數(shù)據(jù)
1.11 查找空值
要查找某列值為空的所有行浙于。
要確定值是否為空护盈，必須使用 IS NULL
select * from emp where comm is null
1.12 將空值轉(zhuǎn)為實(shí)際值
在一些行中包含空值，需要使用非空值來(lái)代替這些空值羞酗。
使用coalesce函數(shù)用實(shí)際的值替換控制,coalesce函數(shù)有1個(gè)或多個(gè)參數(shù)腐宋，改函數(shù)返回第一個(gè)非空值。
select coalesce(comm,0) from emp
1.13 按模式搜索
需求: 在部門(mén)10和部門(mén)20，需要返回名字中有一個(gè) "I" 或者職務(wù)(job title)中帶有 "ER" 的員工胸竞。
使用LIKE運(yùn)算符和SQL通配符"%"
select ename, job
from emp
where deptno in (10,20)
and (ename like '%I' or job like '%ER')
二.查詢結(jié)果排序
2.1 以指定的次序返回查詢結(jié)果
需求: 顯示部門(mén)10中員工名字欺嗤、職位和工資，并按照工資的升序排列卫枝。
使用ORDER BY 子句可以對(duì)結(jié)果集進(jìn)行排序煎饼。解決方案按SAL升序?qū)π羞M(jìn)行排列。
默認(rèn)情況下校赤，ORDER BY以升序方式排列吆玖，因此ASC子句是可選的。DESC表示降序排列痒谴。
select ename, job, sal
from emp
where deptno = 10
order by sal asc;
-- 等同于
select ename, job, sal
from emp
where deptno = 10
order by 3 asc;
2.2 按多個(gè)字段排序
需求: 在EMP表中衰伯，首先按照DEPTNO的升序排序行铡羡，然后按照工資的降序排列积蔚。
在ORDER BY子句中列出不同的排序列，使用逗號(hào)分隔:
select empno,deptno,sal,ename,job
from emp
order by deptno, sal desc
2.3 按子串排序
需求: 按字符串的某一部分對(duì)查詢結(jié)果集排序烦周，要從EMP表中返回員工名字和職位尽爆，并且按照職位字段的最后兩個(gè)字符排序。
在ORDER BY子句中使用SUBSTR函數(shù):
select ename,job
from emp
order by substr(job,length(job)-2)
2.4 處理排序的空值
需求: 在EMP中根據(jù)COMM排序結(jié)果读慎。但是漱贱，這個(gè)字段可以有空值，需要指定是否將空值排在最后夭委。
select ename,sal,comm
from (
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp
) x
order by is_null,comm desc
2.5 根據(jù)數(shù)據(jù)項(xiàng)的鍵排序
MySQL根據(jù)數(shù)據(jù)項(xiàng)的鍵排序
三.操作多個(gè)表
3.1 記錄集的疊加
需求: 要將來(lái)自多個(gè)表的數(shù)據(jù)組織到一起幅狮，就想將一個(gè)結(jié)果集疊加到另一個(gè)上面一樣。這些表不必有相同的關(guān)機(jī)子株灸，但是崇摄，他們對(duì)應(yīng)累的數(shù)據(jù)類型應(yīng)相同。
例如慌烧，要顯示EMP表部門(mén)10中員工的名字和部門(mén)編號(hào)逐抑，以及DEPT表中每個(gè)部門(mén)的名字和部門(mén)編號(hào)。
使用集合操作 UNION ALL把多個(gè)表中的行組合到一起
select ename as ename_and_dname, deptno
from emp
where deptno = 10
union all
select '----------', null
union all
select dname,deptno
from dept
3.2 組合相關(guān)的行
需求: 多個(gè)表有一些相同的列屹蚊，或有些列的值相同厕氨，要通過(guò)連接這些列得到結(jié)果。
例如:要顯示部門(mén)10中所有員工的名字汹粤，以及每個(gè)員工所在部門(mén)的工作地點(diǎn)命斧，這些數(shù)據(jù)存儲(chǔ)在兩個(gè)獨(dú)立的表中
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno
and e.deptno = 10;
-- 等同于
select e.ename, d.loc
from emp e
inner join dept d
on e.deptno = d.deptno
where e.deptno = 10;
討論
該解決方案是連接的一種，更準(zhǔn)確地說(shuō)是等值連接,這是內(nèi)連接(inner join)的一種類型嘱兼。
連接操作會(huì)將來(lái)自兩個(gè)表的行組合到一個(gè)表中冯丙。等值連接的嵌套條件是相同條件。
從概念上來(lái)說(shuō)，要得到連接的結(jié)果集胃惜，首先要?jiǎng)?chuàng)建from子句后列出的表的笛卡爾積(所有可能的情況)
select e.ename, d.loc,
e.deptno as emp_deptno,
d.deptno as dept_deptno
from emp e, dept d
where e.deptno = 10
笛卡爾積:
mysql> select e.ename, d.loc,
-> e.deptno as emp_deptno,
-> d.deptno as dept_deptno
-> from emp e, dept d
-> where e.deptno = 10;
+--------+----------+------------+-------------+
| ename | loc | emp_deptno | dept_deptno |
+--------+----------+------------+-------------+
| CLARK | NEW YORK | 10 | 10 |
| KING | NEW YORK | 10 | 10 |
| MILLER | NEW YORK | 10 | 10 |
| CLARK | DALLAS | 10 | 20 |
| KING | DALLAS | 10 | 20 |
| MILLER | DALLAS | 10 | 20 |
| CLARK | CHICAGO | 10 | 30 |
| KING | CHICAGO | 10 | 30 |
| MILLER | CHICAGO | 10 | 30 |
| CLARK | BOSTON | 10 | 40 |
| KING | BOSTON | 10 | 40 |
| MILLER | BOSTON | 10 | 40 |
+--------+----------+------------+-------------+
12 rows in set (0.01 sec)
這將返回表EMP部門(mén)10中所有員工泞莉，以及DEPT的所有部門(mén)。
然后船殉，在WHERE子句的表達(dá)式中使用 e.deptno 和 d.deptno 來(lái)限制結(jié)果集鲫趁，只返回EMP.DEPTNo和DEPT.DEPTNO相等的那些行。
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno
and e.deptno = 10;
測(cè)試記錄:
mysql> select e.ename, d.loc
-> from emp e, dept d
-> where e.deptno = d.deptno
-> and e.deptno = 10;
+--------+----------+
| ename | loc |
+--------+----------+
| CLARK | NEW YORK |
| KING | NEW YORK |
| MILLER | NEW YORK |
+--------+----------+
3 rows in set (0.00 sec)
mysql>
3.3 在兩個(gè)表中查找相同的行
需求:
查找兩個(gè)表中共同行利虫，但有多列可以用來(lái)連接這兩個(gè)表挨厚。
create view v_test2
as
select ename,job,sal
from emp
where job = 'CLERK'
要返回正確的結(jié)果，必須按所有必要的列進(jìn)行連接糠惫∫咛辏或者，如果不想進(jìn)行連接硼讽，也可以使用子查詢 in 巢价。
select e.ename,e.job,e.sal
from emp e,v_test2 v
where e.ename = v.ename
and e.job = v.job
and e.sal = v.sal;
select ename,job,sal
from emp
where (ename,job,sal) in
(select ename,job,sal from v_test2);
3.4 從一個(gè)表中找到另一個(gè)表中沒(méi)有的值
需求:
要從一個(gè)表(稱為源表)中查找在另一目標(biāo)表中不存在的值。
例如固阁，要從表DEPT中查找在表EMP中不存在的數(shù)據(jù)的所有部門(mén)壤躲。
在示例數(shù)據(jù)庫(kù)中，DEPTNO值為40的記錄在表EMP中不存在备燃。
MySQL可以使用not in
select deptno
from dept
where deptno not in (select deptno from emp);
不過(guò)not in 存在一個(gè)問(wèn)題
如果not in 后面的結(jié)果集中有null的話碉克，前面的查詢結(jié)果會(huì)返回所有的值。
select deptno
from dept
where deptno not in (10 ,20, null);
測(cè)試記錄:
mysql> select deptno
-> from dept
-> where deptno not in (10 ,20, null);
Empty set (0.00 sec)
mysql>
deptno not in (10,20,null)等同于:
not (deptno = 10 or deptno = 50 or deptno = null)
(false or fasle or null) 最后的結(jié)果為null并齐，所以不會(huì)有什么輸出
此時(shí)可以把not in 改為not exists可以避免上述問(wèn)題
select deptno
from dept d
where not exists (select 1 from emp e where e.deptno = d.deptno );
3.5 在一個(gè)表中查找與其它表不匹配的記錄
需求:
對(duì)于具有相同關(guān)鍵字的兩個(gè)表漏麦，要在一個(gè)表中查找與另外一個(gè)表中不匹配的行。
例如: 要查找沒(méi)有支援的部門(mén)
解決方案:
首先返回一個(gè)表中的所有行,以及另一個(gè)表中與公共列匹配的行或不存在匹配行况褪，然后撕贞，僅保留不匹配的行。
select d.*
from dept d
left join emp e
on (d.deptno = e.deptno)
where e.deptno is null;
3.6 像查詢中增加連接而不影響其它連接
emp_bonus表:
create table emp_bonus(empno int,received date,type int);
insert into emp_bonus(empno,received,type) values (7369,'2005-03-14',1);
insert into emp_bonus(empno,received,type) values (7900,'2005-03-14',2);
insert into emp_bonus(empno,received,type) values (7788,'2005-03-14',3);
問(wèn)題:
已經(jīng)有了一個(gè)查詢可以返回所需要的值窝剖，還需要得到其他信息麻掸，但當(dāng)加入這些信息時(shí)，發(fā)現(xiàn)原始結(jié)果集中的數(shù)據(jù)有丟失赐纱。
例如脊奋，要返回所有的員工信息、他們工作部門(mén)的地點(diǎn)及所獲得的獎(jiǎng)勵(lì)疙描。
要求將每個(gè)員工說(shuō)獲得的獎(jiǎng)勵(lì)的日期列加入到結(jié)果數(shù)據(jù)中诚隙，但是連接到emp_bonus表后，所返回的記錄數(shù)要比所希望的要少起胰，因?yàn)椴⒉皇敲總€(gè)員工都有獎(jiǎng)勵(lì)久又。
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno;
解決方案:
可以使用外連接來(lái)獲取這些附加的信息巫延，并且原始查詢中的數(shù)據(jù)不會(huì)丟失。
select e.ename, d.loc,eb.received
from emp e
inner join dept d
on e.deptno = d.deptno
left join emp_bonus eb
on e.empno = eb.empno
order by 2;
其實(shí)也可以使用標(biāo)量子查詢
select e.ename,
d.loc,
(select eb.received from emp_bonus eb where eb.empno = e.empno) as received
from emp e, dept d
where e.deptno = d.deptno
order by 2;
3.7 檢測(cè)兩個(gè)表中是否有相同數(shù)據(jù)
檢測(cè)兩個(gè)表中是否有相同數(shù)據(jù)
3.8 識(shí)別和消除笛卡爾積
需求:
要返回在部門(mén)10中每個(gè)員工的姓名地消，以及部門(mén)的工作地點(diǎn)
select e.ename, d.loc
from emp e, dept d
where e.deptno = 10
測(cè)試記錄:
mysql> select e.ename, d.loc
-> from emp e, dept d
-> where e.deptno = 10;
+--------+----------+
| ename | loc |
+--------+----------+
| CLARK | NEW YORK |
| KING | NEW YORK |
| MILLER | NEW YORK |
| CLARK | DALLAS |
| KING | DALLAS |
| MILLER | DALLAS |
| CLARK | CHICAGO |
| KING | CHICAGO |
| MILLER | CHICAGO |
| CLARK | BOSTON |
| KING | BOSTON |
| MILLER | BOSTON |
+--------+----------+
12 rows in set (0.00 sec)
可以看到多了很多不必要的輸出炉峰，因?yàn)橥泴?xiě)表連接條件了
emp表4條記錄，dept表3條記錄脉执，笛卡爾積產(chǎn)生的數(shù)據(jù)就是4*3=12條
其實(shí)真正需要的輸出是4條
解決方案:
在from子句對(duì)表進(jìn)行連接來(lái)返回正確的結(jié)果集:
select e.ename, d.loc
from emp e, dept d
where e.deptno = 10
and d.deptno = e.deptno;
3.9 聚集與連接
emp_bonus表:
create table emp_bonus(empno int,received date,type int);
insert into emp_bonus(empno,received,type) values (7934,'2005-03-17',1);
insert into emp_bonus(empno,received,type) values (7934,'2005-09-15',2);
insert into emp_bonus(empno,received,type) values (7839,'2005-09-15',3);
insert into emp_bonus(empno,received,type) values (7782,'2005-09-15',1);
需求:
要在包含多個(gè)表的查詢中執(zhí)行聚集運(yùn)算疼阔，要確保表間連接不能使聚集運(yùn)算發(fā)生錯(cuò)誤。
例如半夷，要查找在部門(mén)10中所有員工的工資合計(jì)和獎(jiǎng)金合計(jì)婆廊。由于有些員工的工資的獎(jiǎng)金紀(jì)錄不止一條，在表EMP表EMP_BONUS之間做連接會(huì)導(dǎo)致聚集函數(shù)SUM算得的值錯(cuò)誤巫橄。
解決方案:
當(dāng)處理聚集與連接混合操作時(shí)淘邻，一定要小心。如果連接產(chǎn)生重復(fù)行湘换，可以有兩種方法來(lái)避免聚集函數(shù)計(jì)算錯(cuò)誤:
方法1：
只要在調(diào)用聚集函數(shù)時(shí)使用關(guān)鍵字DISTINCT宾舅，這樣每個(gè)值只能參與計(jì)算一次
方法2：
在進(jìn)行連接操作前前先執(zhí)行聚集操作(在內(nèi)聯(lián)視圖中)，這樣枚尼，因?yàn)榫奂?jì)算已經(jīng)在進(jìn)行連接前完成了贴浙，所以可以避免聚集函數(shù)計(jì)算有誤砂吞，可以規(guī)避此類問(wèn)題署恍。
方法1使用distinct，個(gè)人感覺(jué)存在問(wèn)題蜻直，如果真有不同員工的工資相同盯质，會(huì)少計(jì)算了
select deptno,
sum(distinct sal) as total_sal,
sum(bonus) as total_bonus
from (
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal* case when eb.type = 1 then 0.1 when eb.type =2 then 0.2 else 0.3 end as bonus
from emp e, emp_bonus eb
where e.empno = eb.empno
and e.deptno = 10
) x
group by deptno;
方法2在表連接在聚集運(yùn)算之前
select d.deptno,
d.total_sal,
sum(e.sal*case when eb.type = 1 then 0.1 when eb.type =2 then 0.2 else 0.3 end) as bonus
from emp e,
emp_bonus eb,
(
select deptno, sum(sal) as total_sal
from emp
where deptno = 10
group by deptno
) d
where e.deptno = d.deptno
and e.empno = eb.empno
group by d.deptno,d.total_sal;
3.10 聚集與外連接
emp_bonus表:
create table emp_bonus(empno int,received date,type int);
insert into emp_bonus(empno,received,type) values (7934,'2005-03-17',1);
insert into emp_bonus(empno,received,type) values (7934,'2005-09-15',2);
方法1使用distinct，個(gè)人感覺(jué)存在問(wèn)題概而，如果真有不同員工的工資相同呼巷，會(huì)少計(jì)算了
select deptno,
sum(distinct sal) as total_sal,
sum(bonus) as total_bonus
from (
select e.empno,
e.ename,
e.sal,
e.deptno,
e.sal* case when eb.type = 1 then 0.1 when eb.type =2 then 0.2 when eb.type =3 then 0.3 end as bonus
from emp e
left join emp_bonus eb
on e.empno = eb.empno
where 1 = 1
and e.deptno = 10
) x
group by deptno;
方法2在表連接在聚集運(yùn)算之前
select d.deptno,
d.total_sal,
sum(e.sal*case when eb.type = 1 then 0.1 when eb.type =2 then 0.2 else 0.3 end) as bonus
from emp e,
emp_bonus eb,
(
select deptno, sum(sal) as total_sal
from emp
where deptno = 10
group by deptno
) d
where e.deptno = d.deptno
and e.empno = eb.empno
group by d.deptno,d.total_sal;
3.11 從多個(gè)表中返回丟失的數(shù)據(jù)
此處解決方案是full out join全連接
參考之前寫(xiě)的表連接的blog:
MySQL表連接小結(jié)
3.12 在運(yùn)算和比較時(shí)使用null值
需求:
NULL值永遠(yuǎn)不會(huì)等于或不等于任何值，也不等于NULL值自己赎瑰，但是需要像計(jì)算真實(shí)值一樣計(jì)算可為空的返回值王悍。
例如，需要在表EMP中查出所有比"WARD"提成(COMM)低的員工餐曼，提成為NULL(空)的員工也應(yīng)當(dāng)包括在其中压储。
解決方案:
使用COALESCE函數(shù)將NULL值轉(zhuǎn)換為一個(gè)可以用來(lái)作為標(biāo)準(zhǔn)值進(jìn)行比較的真實(shí)值:
select ename,comm
from emp
where coalesce(comm,0) < ( select comm
from emp
where ename = 'WARD'）
四.插入、更新與刪除
4.1 插入新紀(jì)錄
需求:
像表中插入一條新的記錄源譬。
例如集惋，要想DEPT表中插入一條新的記錄。其中踩娘，DEPTNO值為50刮刑、DNAME的值為"PROGRAMMING"、LOC的值為"BALTIMORE"
解決方案:
使用VALUES子句的INSERT語(yǔ)句來(lái)插入一行:
insert into dept (deptno,dname,loc)
values (50,'PROGRAMMING','BALTIMORE');
對(duì)于MySQL，可以選擇一次插入一行雷绢，或者用多個(gè)值列表一次插入多行:
/* multi row insert */
insert into dept (deptno,dname,loc)
values (1,'A','B'),
(2,'C','D');
4.2 插入默認(rèn)值
需求:
定義表時(shí)可以為某些列定義默認(rèn)值》豪樱現(xiàn)要以默認(rèn)值插入一行，而無(wú)需指定各列的值翘紊。
create table D (id int default 0,name varchar(20));
解決方案:
插入語(yǔ)句可以直接用default或者不指定
insert into D (id, name) values (default, 'test1');
insert into D (name) values ('test2');
4.3 使用NULL代替默認(rèn)值
需求:
在一個(gè)定義了默認(rèn)值的列插入數(shù)據(jù)胶惰，并且需要不管該列的默認(rèn)值是什么，都將該列值設(shè)為null
create table D ( id int default 0, name varchar(10));
解決方案:
可以在值列表中明確地指定NULL值
insert into d (id, name) values (null, 'test3');
4.4 從一個(gè)表向另外的表中復(fù)制行
create table DEPT_EAST like dept;
需求:
要使用查詢從一個(gè)表中向另外的表中富支行霞溪。該查詢可能非常復(fù)雜孵滞，也可能非常簡(jiǎn)單，但是最終是需要將查詢的結(jié)果插入到其它的表中鸯匹。
例如,要將表dept中的行復(fù)制到表DEPT_EAST中坊饶。
解決方案:
說(shuō)使用的方法就是在INSERT語(yǔ)句后面緊跟一個(gè)用來(lái)產(chǎn)生說(shuō)要插入的行的查詢:
insert into dept_east(deptno, dname,loc)
select deptno,dname,loc
from dept
where loc in ('NEW YORK','BOSTON');
4.5 復(fù)制表定義
需求:
要?jiǎng)?chuàng)建新表，該表與已有表的列設(shè)置相同殴蓬。
例如匿级，想要?jiǎng)?chuàng)建一個(gè)DEPT表的副本，名為DEPT_1,但只是想復(fù)雜表結(jié)構(gòu)而不想要復(fù)制源表中的記錄染厅。
解決方案:
可以使用CTAS命令痘绎，也可以使用like命令
create table dept_1 as select * from dept where 1 = 0;
create table dept_2 like dept;
查看表結(jié)構(gòu):
mysql> show create table dept_1\G
*************************** 1. row ***************************
Table: dept_1
Create Table: CREATE TABLE `dept_1` (
`deptno` int NOT NULL,
`dname` varchar(14) DEFAULT NULL,
`loc` varchar(13) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table dept_2\G
*************************** 1. row ***************************
Table: dept_2
Create Table: CREATE TABLE `dept_2` (
`deptno` int NOT NULL,
`dname` varchar(14) DEFAULT NULL,
`loc` varchar(13) DEFAULT NULL,
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>
4.6 一次性向多個(gè)表中插入記錄
目前MySQL還不支持此功能
目前Oracle、DB2肖粮、Hive支持此功能孤页，但是實(shí)現(xiàn)方式不同。
4.7 阻止對(duì)某幾列插入
4.8 在表中編輯紀(jì)錄
問(wèn)題:
要修改表中某寫(xiě)(或全部)行的值涩馆。
例如行施，可能想要將部門(mén)20中所有員工的工資增加10%。
解決方案:
使用Update語(yǔ)句來(lái)修改數(shù)據(jù)庫(kù)表中已有行魂那。
update emp
set sal = sal * 1.1
where deptno = 20;
4.9 當(dāng)相應(yīng)行存在時(shí)更新
create table emp_bonus(empno int,received date,type int);
insert into emp_bonus(empno,received,type) values (7369,'2005-03-14',1);
insert into emp_bonus(empno,received,type) values (7900,'2005-03-14',2);
insert into emp_bonus(empno,received,type) values (7788,'2005-03-14',3);
問(wèn)題:
僅當(dāng)另一個(gè)表中相應(yīng)的行存在時(shí)蛾号，更新某表中的一些行。
例如涯雅，如果表emp_bonus中存在某位員工鲜结，則要將該員工的工資增加20%(在表emp中)。
解決方案:
可以在where子句中結(jié)合子查詢使用
update emp
set sal = sal* 1.2
where empno in (select empno from emp_bonus);
4.10 用其它表中的值更新
4.11 合并記錄
目前只有Oracle的merge語(yǔ)句可以解決此類問(wèn)題活逆。
MySQL暫不支持此類需求精刷。
4.12 從表中刪除所有記錄
delete from emp;
4.13 刪除指定記錄
delete from emp where deptno = 10;
4.14 刪除單個(gè)記錄
從表中刪除單個(gè)記錄，一般需要找到表的主鍵列划乖，然后根據(jù)主鍵列進(jìn)行刪除贬养。
delete from emp where empno = 7369;
4.15 刪除違反參照完整性的記錄
例如，需要?jiǎng)h除emp表中deptno在dept表中不存在的記錄琴庵。
此時(shí)可以使用not exists來(lái)解決误算。
delete from emp
where not exists (
select 1 from dept
where dept.deptno = emp.deptno
);
4.16 刪除重復(fù)記錄
create table test_repeat(id int,name varchar(20),mobile varchar(20));
insert into test_repeat(id ,name, mobile) values (1,'A','123456');
insert into test_repeat(id ,name, mobile) values (2,'B','123456');
insert into test_repeat(id ,name, mobile) values (3,'C','123456');
insert into test_repeat(id ,name, mobile) values (4,'A','123456');
insert into test_repeat(id ,name, mobile) values (5,'A','123456');
insert into test_repeat(id ,name, mobile) values (6,'B','123456');
需要?jiǎng)h除重復(fù)記錄仰美，此時(shí)可以根據(jù)其它列進(jìn)行分組，然后保留主鍵id列最大的一條記錄儿礼。
delete from test_repeat
where id not in
( select max_id from
( select max(id) as max_id from test_repeat group by name,mobile) tmp );
測(cè)試記錄:
mysql> select * from test_repeat;
+------+------+--------+
| id | name | mobile |
+------+------+--------+
| 1 | A | 123456 |
| 2 | B | 123456 |
| 3 | C | 123456 |
| 4 | A | 123456 |
| 5 | A | 123456 |
| 6 | B | 123456 |
+------+------+--------+
6 rows in set (0.00 sec)
mysql> delete from test_repeat
-> where id not in
-> ( select max_id from
-> ( select max(id) as max_id from test_repeat group by name,mobile) tmp );
Query OK, 3 rows affected (0.01 sec)
mysql> select * from test_repeat;
+------+------+--------+
| id | name | mobile |
+------+------+--------+
| 3 | C | 123456 |
| 5 | A | 123456 |
| 6 | B | 123456 |
+------+------+--------+
3 rows in set (0.00 sec)
mysql>
4.17 刪除從其它表引用的記錄
需求:
從一個(gè)表中刪除被另一個(gè)表應(yīng)用的記錄咖杂。
考慮下面的dept_accidents表，其中每行代表生產(chǎn)過(guò)程中的一次失誤蚊夫，每行中記錄了事故發(fā)生的部門(mén)以及事故類型诉字。
create table dept_accidents
( deptno int,
accident_name varchar(20) );
insert into dept_accidents values (10, 'BROKEN FOOT');
insert into dept_accidents values (10, 'FLESH WOUND');
insert into dept_accidents values (20, 'FIRE');
insert into dept_accidents values (20, 'FIRE');
insert into dept_accidents values (20, 'FLOOD');
insert into dept_accidents values (30, 'BRUISED GLUTE');
要從表EMP中刪除所在部門(mén)已經(jīng)發(fā)生了三次以上的事務(wù)的所有員工的記錄。
解決方案:
使用子查詢和聚集函數(shù)count來(lái)查找出發(fā)生三次事故以上的部門(mén)知纷，然后將這些部門(mén)的員工全部刪除壤圃。
delete from emp
where deptno in ( select deptno
from dept_accidents
group by deptno
having count(*) >= 3 );
五.元數(shù)據(jù)查詢
包含5.1到5.4章節(jié)
5.1 列出模式的表
select table_name
from information_schema.tables
where table_schema = 'ZQS';
5.2 列出表的列
select column_name,data_type,ordinal_position
from information_schema.columns
where table_schema = 'ZQS'
and table_name = 'EMP';
5.3 列出表的索引列
show index from emp\G
5.4 列出表的約束
select a.table_name,
a.constraint_name,
b.column_name,
a.constraint_type
from information_schema.table_constraints a,
information_schema.key_column_usage b
where a.table_name = 'EMP'
and a.table_schema = 'ZQS'
and a.table_name = b.table_name
and a.table_schema = b.table_schema
and a.constraint_name = b.constraint_name;
5.5 列出沒(méi)有相應(yīng)索引的外鍵
可以使用 SHOW INDEX 命令來(lái)檢查索引信息，例如索引名琅轧，在索引中的列和在索引中這些列的順序伍绳。
另外，可以查詢INFORMATION_SCHEMA.KEY_COLUMN_USAGE列出指定表的外鍵乍桂。
在MySQL 5中冲杀，外鍵號(hào)稱可以自動(dòng)索引，但事實(shí)上是可以解除索引的睹酌。
要檢測(cè)外鍵索引是否被解除权谁，可以對(duì)該表執(zhí)行 SHOW INDEX命令，并且將結(jié)果跟INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME中同一個(gè)表的結(jié)果相比憋沿，如果KEY_COLUMN_USAGE中有COLUMN_NAME,但SHOW INDEX的結(jié)果中沒(méi)有旺芽，則該列沒(méi)有被索引。
5.6 使用SQL來(lái)生成SQL
六.使用字符串
6.1 遍歷字符串
6.2 字符串文字中包含引號(hào)
6.3 計(jì)算字符在字符串中出現(xiàn)的次數(shù)
6.4 從字符串中刪除不需要的字符
6.5 將字符串和數(shù)字?jǐn)?shù)據(jù)分離
此解決方案需要使用到translate卤妒，MySQL暫時(shí)不支持
6.6 判斷字符串是不是數(shù)字字符類型
6.7 提取姓名大寫(xiě)首字母縮寫(xiě)
6.8 按字符串中的部分內(nèi)容排序
前面章節(jié)已經(jīng)涉及
order by substr(col_name,length(col_name) - 2)
6.9 按字符串中的數(shù)值排序
此解決方案需要使用到translate甥绿，MySQL暫時(shí)不支持
6.10 根據(jù)表中的行創(chuàng)建一個(gè)分隔列表
根據(jù)表中的行創(chuàng)建一個(gè)分隔列表
6.11 將分隔數(shù)據(jù)轉(zhuǎn)換為多值IN列表
將分隔數(shù)據(jù)轉(zhuǎn)換為多值IN列表
6.12 按字母順序排列其中的各個(gè)字符
6.13 判別可以作為數(shù)值的字符串
6.14 提取第n個(gè)分隔的子串
需求:
從字符串中提取出一個(gè)指定的字币、由分隔符隔開(kāi)的子字符串则披。
例如，去除每行中第二個(gè)姓名洗出。
create view V as
select 'mo,larry,curly' as name
union all
select 'tina,gina,jaunita,regina,leena' as name;
drop table if exists t10;
create table t10(id int);
insert into t10 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
解決方案:
解決此問(wèn)題的關(guān)鍵就是將每個(gè)姓名作為單獨(dú)的行返回士复，并保留每個(gè)姓名在列表中的順序。
select v.name,
t10.id,
substring_index(v.name,',',t10.id) as middle_name,
substring_index(substring_index(v.name,',',t10.id),',',-1) as new_name
from V
inner join t10
on t10.id <= length(v.name) - length(replace(v.name,',','')) + 1
order by v.name,t10.id;
測(cè)試記錄:
mysql> select v.name,
-> t10.id,
-> substring_index(v.name,',',t10.id) as middle_name,
-> substring_index(substring_index(v.name,',',t10.id),',',-1) as new_name
-> from V
-> inner join t10
-> on t10.id <= length(v.name) - length(replace(v.name,',','')) + 1
-> order by v.name,t10.id;
+--------------------------------+------+--------------------------------+----------+
| name | id | middle_name | new_name |
+--------------------------------+------+--------------------------------+----------+
| mo,larry,curly | 1 | mo | mo |
| mo,larry,curly | 2 | mo,larry | larry |
| mo,larry,curly | 3 | mo,larry,curly | curly |
| tina,gina,jaunita,regina,leena | 1 | tina | tina |
| tina,gina,jaunita,regina,leena | 2 | tina,gina | gina |
| tina,gina,jaunita,regina,leena | 3 | tina,gina,jaunita | jaunita |
| tina,gina,jaunita,regina,leena | 4 | tina,gina,jaunita,regina | regina |
| tina,gina,jaunita,regina,leena | 5 | tina,gina,jaunita,regina,leena | leena |
+--------------------------------+------+--------------------------------+----------+
8 rows in set (0.00 sec)
6.15 分解IP地址
七.使用數(shù)字
7.1 計(jì)算平均值
select avg(sal) from emp;
7.2 求某列的最小/最大值
select min(sal) as min_sal,max(sal) as max_sal from emp;
7.3 對(duì)某列的值求和
select sum(sal) from emp ;
select deptno,sum(sal) from emp group by deptno;
7.4 求一個(gè)表的行數(shù)
select count(*) from emp;
select deptno,count(*) from emp group by deptno;
7.5 求某列值的個(gè)數(shù)
7.6 生成累計(jì)和
7.7 生成累計(jì)乘積
7.8 計(jì)算累積差
7.9 計(jì)算模式
需求:
查找某個(gè)列中值的模式(數(shù)學(xué)中的模式概念就是對(duì)于給定的數(shù)據(jù)集出現(xiàn)最頻繁的元素)翩活。
例如阱洪，查找deptno 20中工資的模式。
解決方案:
select sal
from
(
select sal,
dense_rank() over (order by cnt desc) as rnk
from (
select sal,count(*) as cnt
from emp
where deptno = 20
group by sal
) x
) y
where rnk = 1
7.10 計(jì)算中間值
7.11 求總和的百分比
select (sum(
case when deptno = 10 then sal end)/ sum(sal)
)*100 as pct
from emp;
7.12 對(duì)可空列做聚集
對(duì)空值做聚集的時(shí)候菠镇，最好將空值轉(zhuǎn)為0冗荸，不然會(huì)影響到平均值的計(jì)算。
select avg(coalesce(comm,0)) as avg_comm from emp where deptno = 30;
測(cè)試記錄:
mysql> select ename,comm from emp where deptno = 30;
+--------+---------+
| ename | comm |
+--------+---------+
| ALLEN | 700.00 |
| WARD | 500.00 |
| MARTIN | 1400.00 |
| BLAKE | NULL |
| TURNER | 0.00 |
| JAMES | NULL |
+--------+---------+
6 rows in set (0.00 sec)
mysql> select avg(comm) from emp where deptno = 30;
+------------+
| avg(comm) |
+------------+
| 650.000000 |
+------------+
1 row in set (0.00 sec)
mysql> select avg(coalesce(comm,0)) as avg_comm from emp where deptno = 30;
+------------+
| avg_comm |
+------------+
| 433.333333 |
+------------+
1 row in set (0.00 sec)
mysql>
7.13 計(jì)算不包含最大值和最小值的均值
7.14 把字母數(shù)字串轉(zhuǎn)換為數(shù)值
MySQL目前不支持translate函數(shù)利耍，暫時(shí)無(wú)解決方案蚌本。
7.15 更改累積和中的值
問(wèn)題:
根據(jù)另一列中的值修改累計(jì)和中的值盔粹。
假設(shè)一個(gè)場(chǎng)景，要顯示信用卡賬號(hào)的事務(wù)處理歷史遺跡每次事務(wù)處理之后的當(dāng)前余額程癌。
create view v (id,amt,trx)
as
select 1, 100 , '存款'
union all
select 2, 100 , '存款'
union all
select 3, 50 , '取款'
union all
select 4, 100 , '存款'
union all
select 5, 200 , '取款'
union all
select 6, 50 , '取款'
解決方案:
使用標(biāo)量子查詢創(chuàng)建累計(jì)和舷嗡，并使用CASE表達(dá)式判斷事務(wù)處理的類型
select id,
amt,
trx,
sum(new_amt) over (order by id) as balance
from
(
select case when v.trx = '取款' then -amt
when v.trx = '存款' then amt
else null end as new_amt,
id,
amt,
trx
from v
) tmp1;
測(cè)試記錄:
mysql> select id,
-> amt,
-> trx,
-> sum(new_amt) over (order by id) as balance
-> from
-> (
-> select case when v.trx = '取款' then -amt
-> when v.trx = '存款' then amt
-> else null end as new_amt,
-> id,
-> amt,
-> trx
-> from v
-> ) tmp1;
+----+-----+--------+---------+
| id | amt | trx | balance |
+----+-----+--------+---------+
| 1 | 100 | 存款 | 100 |
| 2 | 100 | 存款 | 200 |
| 3 | 50 | 取款 | 150 |
| 4 | 100 | 存款 | 250 |
| 5 | 200 | 取款 | 50 |
| 6 | 50 | 取款 | 0 |
+----+-----+--------+---------+
6 rows in set (0.00 sec)
八.日期運(yùn)算
8.1 加減日、月嵌莉、年
select hiredate - interval 5 day as d_minus_5d,
hiredate + interval 5 day as d_plus_5D,
hiredate - interval 5 month as d_minus_5M,
hiredate + interval 5 month as d_plus_5M,
hiredate - interval 5 year as d_minus_5Y,
hiredate + interval 5 year as d_plus_5Y
from emp
where deptno = 10;
8.2 計(jì)算兩個(gè)日期之間的天數(shù)
select ward_hd,allen_hd,datediff(ward_hd,allen_hd)
from (
select hiredate as ward_hd
from emp
where ename = 'WARD'
) x,
(
select hiredate as allen_hd
from emp
where ename = 'ALLEN'
) y
8.3 確定兩個(gè)日期之間工作日數(shù)目
weekday 返回?cái)?shù)值的星期數(shù)
0 = Monday, 1 = Tuesday, … 6 = Sunday
根據(jù)這個(gè)剔除进萄，然后count(*) 求總數(shù)即可
8.4 確定兩個(gè)日期之間的月份數(shù)或年數(shù)
select mnth, mnth/12
from (
select (year(max_hd) - year(min_hd)) *12 +
(month(max_hd) - month(min_hd)) as mnth
from (
select min(hiredate) as min_hd, max(hiredate) as max_hd
from emp
) x
) y;
8.5 確定兩個(gè)日期之間秒、分锐峭、小時(shí)數(shù)
select datediff(ward_hd,allen_hd)*24 hr,
datediff(ward_hd,allen_hd)*24*60 min,
datediff(ward_hd,allen_hd)*24*60*60 sec
from (
select max(case when ename = 'WARD'
then hiredate
end) as ward_hd,
max(case when ename = 'ALLEN'
then hiredate
end) as allen_hd
from emp
) x
8.6 計(jì)算一年中周內(nèi)各日期次數(shù)
生成包含一年的所有日期
使用DATE_FORMAT函數(shù)中鼠，確定每個(gè)日期為星期幾，然后計(jì)算周內(nèi)各日期的次數(shù)沿癞。
with recursive c(n) as
(
select 1
union all
select n + 1 from c where n < 400
),
tmp1 as
(
select '2021-01-01' as dt
),
tmp2 as
(
select adddate(dt,n-1) dt
from tmp1
inner join c
)
select date_format(dt,'%a'),
count(*)
from tmp2
where dt < '2022-01-01'
group by date_format(dt,'%a')
8.7 確定當(dāng)前記錄和下一條記錄之間相差的天數(shù)
問(wèn)題:
求兩個(gè)日期之間相差的天數(shù)兜蠕。
例如，對(duì)于deptno 10中的每個(gè)員工抛寝，確定聘用他們的日期及聘用下一個(gè)員工的日期之間相差的天數(shù)熊杨。
解決方案：
這個(gè)問(wèn)題的解決方案是找到當(dāng)前員工聘用的最早hiredate，再來(lái)求差盗舰。
select x.*,
datediff(x.next_hd,x.hiredate) diff
from (
select e.deptno, e.ename, e.hiredate,
(select min(d.hiredate) from emp d
where d.hiredate > e.hiredate) next_hd
from emp e
where e.deptno = 10
) x;
九.日期操作
9.1 確定一年是否為閏年
9.2 確定一年內(nèi)的天數(shù)
9.3 從日期中提取時(shí)間的各部分
select date_format(current_timestamp,'%k') hr,
date_format(current_timestamp,'%i') min,
date_format(current_timestamp,'%s') sec,
date_format(current_timestamp,'%d') dy,
date_format(current_timestamp,'%m') mon,
date_format(current_timestamp,'%Y') yr;
9.4 確定某個(gè)月的第一天和最后一天
9.5 確定一年內(nèi)屬于周內(nèi)某一天的所有日期
with recursive c(n) as
(
select 1
union all
select n + 1 from c where n < 400
),
tmp1 as
(
select '2021-01-01' as dt
),
tmp2 as
(
select adddate(dt,n-1) dt
from tmp1
inner join c
)
select dt,
date_format(dt,'%a') week
from tmp2
where dt < '2022-01-01'
and date_format(dt,'%a') = 'Fri'
order by dt;
9.6 確定某月內(nèi)第一個(gè)和最后一個(gè)"周內(nèi)某天"的日期
9.7 創(chuàng)建日歷
9.8 列出一年中每個(gè)季度的開(kāi)始日期和結(jié)束日期
select quarter(adddate(dy,-1)) qtr,
date_add(dy,interval -3 month) q_start,
adddate(dy,-1) q_end
from (
select date_add(dy,interval (3*id) month) dy
from (
select id,
adddate(current_date,-dayofyear(current_date) + 1 ) dy
from t10
where id <= 4
) x
) y;
測(cè)試記錄:
mysql> select quarter(adddate(dy,-1)) qtr,
-> date_add(dy,interval -3 month) q_start,
-> adddate(dy,-1) q_end
-> from (
-> select date_add(dy,interval (3*id) month) dy
-> from (
-> select id,
-> adddate(current_date,-dayofyear(current_date) + 1 ) dy
-> from t10
-> where id <= 4
-> ) x
-> ) y;
+------+------------+------------+
| qtr | q_start | q_end |
+------+------------+------------+
| 1 | 2021-01-01 | 2021-03-31 |
| 2 | 2021-04-01 | 2021-06-30 |
| 3 | 2021-07-01 | 2021-09-30 |
| 4 | 2021-10-01 | 2021-12-31 |
+------+------------+------------+
4 rows in set (0.00 sec)
9.9 確定某個(gè)給定季度的開(kāi)始日期和結(jié)束日期
下面這個(gè)sql輸出還是存在一點(diǎn)問(wèn)題
select date_add(adddate(q_start,-day(q_start)+1) , interval -2 month) q_start,
q_end
from
(
select date_format(q_end,'%Y-%m-%d') as q_start,
q_end
from (
select last_day(
str_to_date(
concat(
substr(yrq,1,4), mod(yrq,10)*3), '%Y%m')) as q_end
from (
select 20211 as yrq union all
select 20212 as yrq union all
select 20213 as yrq union all
select 20214 as yrq
) x
) y
) z
測(cè)試記錄:
mysql> select date_add(
-> adddate(q_end,-day(q_end)+1),
-> interval -2 month) q_start,
-> q_end
-> from (
-> select last_day(
-> str_to_date(
-> concat(
-> substr(yrq,1,4), mod(yrq,10)*3), '%Y%m')) q_end
-> from (
-> select 20211 as yrq union all
-> select 20212 as yrq union all
-> select 20213 as yrq union all
-> select 20214 as yrq
-> ) x
-> ) y
-> ;
+---------+------------+
| q_start | q_end |
+---------+------------+
| NULL | 2021-03-31 |
| NULL | 2021-06-30 |
| NULL | 2021-09-30 |
| NULL | 2021-12-31 |
+---------+------------+
4 rows in set, 4 warnings (0.00 sec)
mysql> select date_add(adddate(q_start,-day(q_start)+1) , interval -2 month) q_start,
-> q_end
-> from
-> (
-> select date_format(q_end,'%Y-%m-%d') as q_start,
-> q_end
-> from (
-> select last_day(
-> str_to_date(
-> concat(
-> substr(yrq,1,4), mod(yrq,10)*3), '%Y%m')) as q_end
-> from (
-> select 20211 as yrq union all
-> select 20212 as yrq union all
-> select 20213 as yrq union all
-> select 20214 as yrq
-> ) x
-> ) y
-> ) z;
+------------+------------+
| q_start | q_end |
+------------+------------+
| 2021-01-01 | 2021-03-31 |
| 2021-04-01 | 2021-06-30 |
| 2021-07-01 | 2021-09-30 |
| 2021-10-01 | 2021-12-31 |
+------------+------------+
4 rows in set (0.00 sec)
mysql>
9.10 填充丟失的日期
需求:
為給定范圍內(nèi)的每個(gè)日期(每個(gè)月晶府、周或年)生成一行信息。這樣的行集通常用于生成綜合報(bào)告钻趋。
例如川陆，計(jì)算沒(méi)年內(nèi)每個(gè)月聘用的員工數(shù)。
解決方案:
這里的敲門(mén)是為每個(gè)月返回一行信息蛮位，即使并未聘用任何員工(此時(shí)該數(shù)為0)较沪。
由于1980年1987年間并不是每個(gè)月都聘用過(guò)員工，因此必須生成這些月份失仁，然后把按HIREDATE(聘用日期)與表EMP進(jìn)行外連接(對(duì)HIREDATE截取月份尸曼，以便它能與生成的月份相匹配)。
with recursive c(n) as
(
select 1 as n
union all
select n + 1 from c
where n < 400
),
tmp1 as
(
select min(hiredate) min_hiredate,max(hiredate) max_hiredate
from emp
),
tmp2 as
(
select min_hiredate,
adddate(min_hiredate,-dayofyear(min_hiredate) + 1) new_min_hiredate,
max_hiredate,
date_add(adddate(max_hiredate,-dayofyear(max_hiredate) + 1),interval 12 month) new_max_hiredate
from tmp1
),
tmp3 as
(
select date_add(new_min_hiredate, interval c.n - 1 month) new_min_hiredate2
from tmp2
inner join c
where date_add(new_min_hiredate, interval c.n - 1 month) < new_max_hiredate
)
select date_format(new_min_hiredate2,'%Y-%m') hired_mth,
count(e.hiredate) as hired_nums
from tmp3
left join emp e
on date_format(new_min_hiredate2,'%Y-%m') = date_format(e.hiredate,'%Y-%m')
group by date_format(new_min_hiredate2,'%Y-%m')
order by date_format(new_min_hiredate2,'%Y-%m')
測(cè)試記錄:
mysql> with recursive c(n) as
-> (
-> select 1 as n
-> union all
-> select n + 1 from c
-> where n < 400
-> ),
-> tmp1 as
-> (
-> select min(hiredate) min_hiredate,max(hiredate) max_hiredate
-> from emp
-> ),
-> tmp2 as
-> (
-> select min_hiredate,
-> adddate(min_hiredate,-dayofyear(min_hiredate) + 1) new_min_hiredate,
-> max_hiredate,
-> date_add(adddate(max_hiredate,-dayofyear(max_hiredate) + 1),interval 12 month) new_max_hiredate
-> from tmp1
-> ),
-> tmp3 as
-> (
-> select date_add(new_min_hiredate, interval c.n - 1 month) new_min_hiredate2
-> from tmp2
-> inner join c
-> where date_add(new_min_hiredate, interval c.n - 1 month) < new_max_hiredate
-> )
-> select date_format(new_min_hiredate2,'%Y-%m') hired_mth,
-> count(e.hiredate) as hired_nums
-> from tmp3
-> left join emp e
-> on date_format(new_min_hiredate2,'%Y-%m') = date_format(e.hiredate,'%Y-%m')
-> group by date_format(new_min_hiredate2,'%Y-%m')
-> order by date_format(new_min_hiredate2,'%Y-%m');
+-----------+------------+
| hired_mth | hired_nums |
+-----------+------------+
| 1980-01 | 0 |
| 1980-02 | 0 |
| 1980-03 | 0 |
| 1980-04 | 0 |
| 1980-05 | 0 |
| 1980-06 | 0 |
| 1980-07 | 0 |
| 1980-08 | 0 |
| 1980-09 | 0 |
| 1980-10 | 0 |
| 1980-11 | 0 |
| 1980-12 | 1 |
| 1981-01 | 0 |
| 1981-02 | 2 |
| 1981-03 | 0 |
| 1981-04 | 1 |
| 1981-05 | 1 |
| 1981-06 | 1 |
| 1981-07 | 0 |
| 1981-08 | 0 |
| 1981-09 | 2 |
| 1981-10 | 0 |
| 1981-11 | 1 |
| 1981-12 | 2 |
| 1982-01 | 1 |
| 1982-02 | 0 |
| 1982-03 | 0 |
| 1982-04 | 0 |
| 1982-05 | 0 |
| 1982-06 | 0 |
| 1982-07 | 0 |
| 1982-08 | 0 |
| 1982-09 | 0 |
| 1982-10 | 0 |
| 1982-11 | 0 |
| 1982-12 | 0 |
| 1983-01 | 0 |
| 1983-02 | 0 |
| 1983-03 | 0 |
| 1983-04 | 0 |
| 1983-05 | 0 |
| 1983-06 | 0 |
| 1983-07 | 0 |
| 1983-08 | 0 |
| 1983-09 | 0 |
| 1983-10 | 0 |
| 1983-11 | 0 |
| 1983-12 | 0 |
| 1984-01 | 0 |
| 1984-02 | 0 |
| 1984-03 | 0 |
| 1984-04 | 0 |
| 1984-05 | 0 |
| 1984-06 | 0 |
| 1984-07 | 0 |
| 1984-08 | 0 |
| 1984-09 | 0 |
| 1984-10 | 0 |
| 1984-11 | 0 |
| 1984-12 | 0 |
| 1985-01 | 0 |
| 1985-02 | 0 |
| 1985-03 | 0 |
| 1985-04 | 0 |
| 1985-05 | 0 |
| 1985-06 | 0 |
| 1985-07 | 0 |
| 1985-08 | 0 |
| 1985-09 | 0 |
| 1985-10 | 0 |
| 1985-11 | 0 |
| 1985-12 | 0 |
| 1986-01 | 0 |
| 1986-02 | 0 |
| 1986-03 | 0 |
| 1986-04 | 0 |
| 1986-05 | 0 |
| 1986-06 | 0 |
| 1986-07 | 0 |
| 1986-08 | 0 |
| 1986-09 | 0 |
| 1986-10 | 0 |
| 1986-11 | 0 |
| 1986-12 | 0 |
| 1987-01 | 0 |
| 1987-02 | 0 |
| 1987-03 | 0 |
| 1987-04 | 0 |
| 1987-05 | 0 |
| 1987-06 | 2 |
| 1987-07 | 0 |
| 1987-08 | 0 |
| 1987-09 | 0 |
| 1987-10 | 0 |
| 1987-11 | 0 |
| 1987-12 | 0 |
+-----------+------------+
96 rows in set (0.00 sec)
mysql>
9.11 按照給定的時(shí)間單位進(jìn)行查找
需求:
查找與給定月份萄焦、星期幾或其它時(shí)間單位相匹配的日期控轿。
例如，找到2月份和12月份聘用的所有員工拂封，或者查找星期二聘用的所有員工茬射。
解決方案:
用MySQL自帶的函數(shù)即可。
select ename
from emp
where month(hiredate) in (2,12) or weekday(hiredate) = 1;
測(cè)試記錄:
mysql> select ename
-> from emp
-> where month(hiredate) in (2,12) or weekday(hiredate) = 1;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| CLARK |
| KING |
| TURNER |
| JAMES |
| FORD |
+--------+
8 rows in set (0.00 sec)
9.12 使用日期的特殊部分比較記錄
需求:
查找聘用日期月份和周內(nèi)日期都相同的員工冒签。
例如在抛，如果在1983年3月10日星期一聘用了某個(gè)員工，而在2001年3月2日星期一聘用了另一個(gè)員工萧恕，那么二者的聘用日都在星期一刚梭，而且月份名一致档悠，則可以認(rèn)為他們相匹配。
解決方案:
因?yàn)橐岩粋€(gè)員工的hiredate與另一個(gè)員工的hiredate相比較望浩，所以需要對(duì)表emp進(jìn)行自連接辖所，這樣，就可以對(duì)hiredate的每種組合進(jìn)行比較磨德，然后從每個(gè)hiredate中提取它是星期幾及月份名缘回，并進(jìn)行比較。
select concat(e1.ename,' was hired on the same month and weekday as ',e2.ename) as str
from emp e1
inner join emp e2
on month(e1.hiredate) = month(e2.hiredate)
and weekday(e1.hiredate) = weekday(e2.hiredate)
and e1.empno < e2.empno
測(cè)試記錄:
mysql> select concat(e1.ename,' was hired on the same month and weekday as ',e2.ename) as str
-> from emp e1
-> inner join emp e2
-> on month(e1.hiredate) = month(e2.hiredate)
-> and weekday(e1.hiredate) = weekday(e2.hiredate)
-> and e1.empno < e2.empno;
+--------------------------------------------------------+
| str |
+--------------------------------------------------------+
| SCOTT was hired on the same month and weekday as ADAMS |
| JAMES was hired on the same month and weekday as FORD |
+--------------------------------------------------------+
2 rows in set (0.00 sec)
9.13 識(shí)別重疊的日期范圍
create table emp_project(empno int, ename varchar(20), proj_id int, proj_start date, proj_end date);
insert into emp_project values (7782,'CLARK',1,'2005-06-16','2005-06-18');
insert into emp_project values (7782,'CLARK',4,'2005-06-19','2005-06-24');
insert into emp_project values (7782,'CLARK',7,'2005-06-22','2005-06-25');
insert into emp_project values (7782,'CALRK',10,'2005-06-25','2005-06-28');
insert into emp_project values (7782,'CLARK',13,'2005-06-28','2005-07-02');
insert into emp_project values (7839,'KING',2,'2005-06-17','2005-06-21');
insert into emp_project values (7839,'KING',8,'2005-06-23','2005-06-25');
insert into emp_project values (7839,'KING',14,'2005-06-29','2005-06-30');
insert into emp_project values (7839,'KING',11,'2005-06-26','2005-06-27');
insert into emp_project values (7839,'KING',5,'2005-06-20','2005-06-24');
insert into emp_project values (7934,'MILLER',3,'2005-06-18','2005-06-22');
insert into emp_project values (7934,'MILLER',12,'2005-06-27','2005-06-28');
insert into emp_project values (7934,'MILLER',15,'2005-06-30','2005-07-03');
insert into emp_project values (7934,'MILLER',9,'2005-06-24','2005-06-27');
insert into emp_project values (7934,'MILLER',6,'2005-06-21','2005-06-23');
需求:
查找員工在老工程結(jié)束之前就開(kāi)始新工程的所有實(shí)例典挑。
觀察一下員工KING的結(jié)果酥宴，會(huì)浮現(xiàn)KING在完成PROJ_ID 5之前開(kāi)始了PROJ_ID 8，而且在完成PROJ_ID之前又開(kāi)始了PROJ_ID5.
解決方案:
這里的關(guān)鍵是,找打PROJ_START(新工程的開(kāi)始日期)出現(xiàn)另一個(gè)工程的PROJ_START之后PROJ_END日期之前的那些行您觉。
首先拙寡，應(yīng)該把同一個(gè)員工的每個(gè)工程與另一個(gè)工程相比較。
對(duì)EMP_PROJECT按員工進(jìn)行自連接琳水，得到每個(gè)員工任意兩個(gè)工程的所有組合肆糕。
要找到重疊的情況，只需找到一個(gè)PROJ_ID的PROJ_START介于另一個(gè)PROJ_ID的PROJ_START和PROJ_END之間的所有行即可在孝。
select a.empno, a.ename,
concat('project ',b.proj_id,
' overlaps project ',a.proj_id) as msg
from emp_project a,
emp_project b
where a.empno = b.empno
and b.proj_start >= a.proj_start
and b.proj_start <= a.proj_end
and a.proj_id != b.proj_id;
測(cè)試記錄:
mysql> select a.empno, a.ename,
-> concat('project ',b.proj_id,
-> ' overlaps project ',a.proj_id) as msg
-> from emp_project a,
-> emp_project b
-> where a.empno = b.empno
-> and b.proj_start >= a.proj_start
-> and b.proj_start <= a.proj_end
-> and a.proj_id != b.proj_id;
+-------+--------+--------------------------------+
| empno | ename | msg |
+-------+--------+--------------------------------+
| 7782 | CLARK | project 7 overlaps project 4 |
| 7782 | CLARK | project 10 overlaps project 7 |
| 7782 | CALRK | project 13 overlaps project 10 |
| 7839 | KING | project 8 overlaps project 5 |
| 7839 | KING | project 5 overlaps project 2 |
| 7934 | MILLER | project 12 overlaps project 9 |
| 7934 | MILLER | project 6 overlaps project 3 |
+-------+--------+--------------------------------+
7 rows in set (0.00 sec)
mysql>
十.范圍處理
10.1 定位連續(xù)值范圍
10.2 查找同一組或分區(qū)中行之前的差
10.3 定義連續(xù)值范圍的開(kāi)始和結(jié)束點(diǎn)
定義連續(xù)值范圍的開(kāi)始點(diǎn)和結(jié)束點(diǎn)
10.4 補(bǔ)充范圍內(nèi)丟失的值
問(wèn)題:
返回1980年起始的十年間每年聘用的員工數(shù)诚啃，但有些奶粉并沒(méi)有聘用員工。
解決方案:
這個(gè)解決方案的技巧是私沮，對(duì)為聘用任何員工的年份返回0始赎。
with recursive c(n) as
(
select 1 as n
union all
select n + 1 from c where n < 10
),
tmp1 as
(
select year(min(hiredate)) min_hiredate
from emp
),
tmp2 as
(
select min_hiredate + n as new_hired
from tmp1
inner join c
)
select new_hired,
count(emp.hiredate)
from tmp2
left join emp
on tmp2.new_hired = year(hiredate)
group by new_hired
order by new_hired
測(cè)試記錄:
mysql> with recursive c(n) as
-> (
-> select 1 as n
-> union all
-> select n + 1 from c where n < 10
-> ),
-> tmp1 as
-> (
-> select year(min(hiredate)) min_hiredate
-> from emp
-> ),
-> tmp2 as
-> (
-> select min_hiredate + n as new_hired
-> from tmp1
-> inner join c
-> )
-> select new_hired,
-> count(emp.hiredate)
-> from tmp2
-> left join emp
-> on tmp2.new_hired = year(hiredate)
-> group by new_hired
-> order by new_hired;
+-----------+---------------------+
| new_hired | count(emp.hiredate) |
+-----------+---------------------+
| 1981 | 10 |
| 1982 | 1 |
| 1983 | 0 |
| 1984 | 0 |
| 1985 | 0 |
| 1986 | 0 |
| 1987 | 2 |
| 1988 | 0 |
| 1989 | 0 |
| 1990 | 0 |
+-----------+---------------------+
10 rows in set (0.00 sec)
10.5 生成連續(xù)數(shù)字
十一.高級(jí)查找
11.1 給結(jié)果集分頁(yè)
11.2 跳過(guò)表中的n行
11.3 在外連接中用OR邏輯
11.4 確定哪些行是彼此互換的
11.5 選擇前n個(gè)記錄
11.6 找到包含最大值和最小值的記錄
11.7 存取"未來(lái)"行
11.8 輪換行值
11.9 給結(jié)果集分等級(jí)
11.10 抑制重復(fù)
11.11 找到騎士值
11.12 生成簡(jiǎn)單的預(yù)測(cè)
十二.報(bào)表和數(shù)據(jù)倉(cāng)庫(kù)運(yùn)算
12.1 將結(jié)果集轉(zhuǎn)為一行
12.2 把結(jié)果集轉(zhuǎn)置為多行
12.3 反向轉(zhuǎn)置結(jié)果集
12.4 將結(jié)果集反向轉(zhuǎn)置為一列
12.5 抑制結(jié)果集中的重復(fù)值
12.6 轉(zhuǎn)置結(jié)果集以利于跨行計(jì)算
12.7 創(chuàng)建固定大小的數(shù)據(jù)桶
12.8 創(chuàng)建預(yù)定數(shù)目的桶
12.9 創(chuàng)建橫向直方圖
12.10 創(chuàng)建縱向直方圖
12.11 返回未包含在group by中的列
12.12 計(jì)算簡(jiǎn)單的小計(jì)
12.13 計(jì)算所有表達(dá)式組合的小計(jì)
12.14 判別非小計(jì)的行
根據(jù)grouping來(lái)判斷是否是小計(jì)的行
12.15 使用case表達(dá)式給行做標(biāo)記
12.16 創(chuàng)建稀疏矩陣
需求:
創(chuàng)建一個(gè)稀疏的矩陣。
例如這個(gè)sql:
select case deptno when 10 then ename end as d10,
case deptno when 20 then ename end as d20,
case deptno when 30 then ename end as d30,
case job when 'CLERK' then ename end as clerks,
case job when 'MANAGER' then ename end as mgrs,
case job when 'PRESIDENT' then ename end as prez,
case job when 'ALALYST' then ename end as anals,
case job when 'SALESMAN' then ename end as sales
from emp;
生成了一個(gè)稀疏的矩陣
解決方案:
把deptno和job行變換為列仔燕，只需使用case表達(dá)式造垛，判斷由這些行返回的可能性。這就是對(duì)它的所有解釋晰搀。
另外五辽，如果想使報(bào)表變'稠密',并去除一些null行，就需要找到分組的依據(jù)厕隧。
例如奔脐，使用串口函數(shù)row_number over,為每個(gè)deptno中的每個(gè)員工分等級(jí)，然后使用聚集函數(shù)max去掉一些null
mysql> select case deptno when 10 then ename end as d10,
-> case deptno when 20 then ename end as d20,
-> case deptno when 30 then ename end as d30,
-> case job when 'CLERK' then ename end as clerks,
-> case job when 'MANAGER' then ename end as mgrs,
-> case job when 'PRESIDENT' then ename end as prez,
-> case job when 'ALALYST' then ename end as anals,
-> case job when 'SALESMAN' then ename end as sales
-> from emp;
+--------+-------+--------+--------+-------+------+-------+--------+
| d10 | d20 | d30 | clerks | mgrs | prez | anals | sales |
+--------+-------+--------+--------+-------+------+-------+--------+
| NULL | SMITH | NULL | SMITH | NULL | NULL | NULL | NULL |
| NULL | NULL | ALLEN | NULL | NULL | NULL | NULL | ALLEN |
| NULL | NULL | WARD | NULL | NULL | NULL | NULL | WARD |
| NULL | JONES | NULL | NULL | JONES | NULL | NULL | NULL |
| NULL | NULL | MARTIN | NULL | NULL | NULL | NULL | MARTIN |
| NULL | NULL | BLAKE | NULL | BLAKE | NULL | NULL | NULL |
| CLARK | NULL | NULL | NULL | CLARK | NULL | NULL | NULL |
| NULL | SCOTT | NULL | NULL | NULL | NULL | NULL | NULL |
| KING | NULL | NULL | NULL | NULL | KING | NULL | NULL |
| NULL | NULL | TURNER | NULL | NULL | NULL | NULL | TURNER |
| NULL | ADAMS | NULL | ADAMS | NULL | NULL | NULL | NULL |
| NULL | NULL | JAMES | JAMES | NULL | NULL | NULL | NULL |
| NULL | FORD | NULL | NULL | NULL | NULL | NULL | NULL |
| MILLER | NULL | NULL | MILLER | NULL | NULL | NULL | NULL |
+--------+-------+--------+--------+-------+------+-------+--------+
14 rows in set (0.00 sec)
解決方案:
select max(case deptno when 10 then ename end) as d10,
max(case deptno when 20 then ename end) as d20,
max(case deptno when 30 then ename end) as d30,
max(case job when 'CLERK' then ename end) as clerks,
max(case job when 'MANAGER' then ename end) as mgrs,
max(case job when 'PRESIDENT' then ename end) as prez,
max(case job when 'ANALYST' then ename end) as anals,
max(case job when 'SALESMAN' then ename end) as sales
from (
select deptno, job, ename,
row_number() over (partition by deptno order by empno) rn
from emp
) x
group by rn
;
12.17 按時(shí)間按單位給行分組
12.18 對(duì)不同組/分區(qū)同時(shí)實(shí)現(xiàn)聚集
12.19 對(duì)移動(dòng)范圍內(nèi)的值進(jìn)行聚集
對(duì)移動(dòng)范圍內(nèi)的值進(jìn)行聚集
12.20 轉(zhuǎn)置帶小計(jì)的結(jié)果集
十三.分層查詢
13.1 表示父子關(guān)系
13.2 表示父-子-祖關(guān)系
13.3 創(chuàng)建表的分層視圖
13.4 為給定父行找到所有子行
13.5 確定哪些是葉節(jié)點(diǎn)吁讨、分支節(jié)點(diǎn)及根節(jié)點(diǎn)
確定哪些是葉節(jié)點(diǎn)、分子節(jié)點(diǎn)峦朗、根節(jié)點(diǎn)
