MySql 加強(qiáng)
三范式
第一范式：保證每列的原子性 必須遵循
第二范式：保證一張表只描述一件事情 不是必須遵循
第三范式----保證每列都和主鍵直接相關(guān) 不是必須遵循
2****、****SQL 分類
2.1****、數(shù)據(jù)查詢語言（****DQL****）
其語句，也稱為“數(shù)據(jù)檢索語句”，用以從表中獲得數(shù)據(jù)，確定數(shù)據(jù)怎樣在應(yīng)用程序給出。保留字
SELECT 是 DQL（也是所有 SQL）用得最多的動詞，其他 DQL 常用的保留字有 WHERE，ORDER
BY，GROUP BY 和 HAVING。這些 DQL 保留字常與其他類型的 SQL 語句一起使用。
2.2****、數(shù)據(jù)定義語言（****DDL****）
其語句包括動詞 CREATE 和 DROP。在數(shù)據(jù)庫中創(chuàng)建新表或刪除表（CREAT TABLE 或 DROP
TABLE）；為表加入索引等。DDL 包括許多與人數(shù)據(jù)庫目錄中獲得數(shù)據(jù)有關(guān)的保留字。它也是動作查詢
的一部分。
2.3****、數(shù)據(jù)操作語言（****DML****）其語句包括動詞 INSERT，UPDATE 和 DELETE。它們分別用于添加，修改和刪除表中的行，Insert /
Update / Delete。也稱為動作查詢語言。
2.4****、事務(wù)處理語言（****TCL****）
它的語句能確保被 DML 語句影響的表的所有行及時(shí)得以更新。TCL 語句包括 BEGIN
TRANSACTION，COMMIT 和 ROLLBACK。
2.5****、數(shù)據(jù)庫控制語言（****DCL****）
它的語句通過 GRANT 或 REVOKE 獲得許可，確定單個(gè)用戶和用戶組對數(shù)據(jù)庫對象的訪問。某些
RDBMS 可用 GRANT 或 REVOKE 控制對表單個(gè)列的訪問。
2.6****、指針控制語言（****CCL****）
它的語句，像 DECLARE CURSOR，F(xiàn)ETCH INTO 和 UPDATE WHERE CURRENT 用于對一個(gè)或多
個(gè)表單獨(dú)行的操作。
二、單表查詢
語法 :
SELECT [DISTINCT] * | 字段 [別名] [, 字段 [別名]]
FROM 表名稱 [別名]
[WHERE 條件(S)]
[ORDER BY 字段 [ASC|DESC] [, 字段 [ASC|DESC], …]];
3****、多表查詢分類
內(nèi)連接查詢
隱式內(nèi)連接查詢
SELECT [DISTINCT] * | 字段 [別名] [, 字段 [別名], …]
FROM 表名稱 [別名], [表名稱 [別名], …]
[WHERE 條件(S)/消除笛卡爾積連接]
[ORDER BY 排序字段 [ASC|DESC] [, 排序字段 [ASC|DESC], …]];
顯示內(nèi)連接查詢
SELECT table1.column, table2.column
FROM table1 [INNER] JOIN table2 ON table1.column1 = table2.column2
WHERE 條件
顯示內(nèi)連接查詢：查詢的結(jié)果和隱式內(nèi)連接一模一樣。區(qū)別在于：
顯示內(nèi)連接可以看到 [INNER] JOIN；
消除笛卡爾積條件使用寫在 ON 子句。
外連接查詢左外連接查詢
右外連接查詢
查詢出 JOIN 左邊表的全部數(shù)據(jù)查詢出來，JOIN 右邊的表不匹配的數(shù)據(jù)使用 NULL 來填充數(shù)據(jù)。
全外連接查詢
查詢出 JOIN 右邊表的全部數(shù)據(jù)查詢出來，JOIN 左邊的表不匹配的數(shù)據(jù)使用 NULL 來填充數(shù)據(jù)。
六、分組函數(shù)
1****、函數(shù)分類
2****、多行函數(shù)
COUNT()： AVG()：SUM()：MAX()：MIN()：
注意：
統(tǒng)計(jì)函數(shù)忽略空值，可以使用 IFNULL， 因?yàn)槭?NULL 不會影響匯總值，但會影響匯總數(shù)量；
不能在 where 語句中使用分組函數(shù)。
七、分組查詢
1****、分組語法
SELECT [DISTINCT] *|分組字段1 [別名] [, 分組字段2 [別名] ,…] | 統(tǒng)計(jì)函數(shù)
FROM 表名稱 [別名], [表名稱 [別名] , …]
[WHERE 條件(s)]
[GROUP BY 分組字段1 [, 分組字段2 ,…]]
[ORDER BY 排序字段 ASC | DESC [, 排序字段 ASC | DESC]];
2****、使用分組注意
SELECT 子句出現(xiàn)的字段，要不在統(tǒng)計(jì)函數(shù)中，要不出現(xiàn)在 GROUP BY 子句中，否則不合理（整
體與個(gè)體）；
在GROUP BY 子句中出現(xiàn)的字段，可以不出現(xiàn)在 SELECT 列表中；
統(tǒng)計(jì)函數(shù)可以單獨(dú)使用，SQL 中可以沒有 GROUP BY 子句；
在 GROUP BY 子句中，可以按單列進(jìn)行分組，也可以在多列上進(jìn)行分組，多列分組就是按照多個(gè)
字段的組合進(jìn)行分組，最終的結(jié)果也會按照分組字段進(jìn)行排序顯示。
3****、分組限定
不能在 WHERE 子句中對分組限定，限制組須使用 HAVING 子句；
不能在 WHERE 子句中使用統(tǒng)計(jì)函數(shù)，而在 HAVING 子句可使用統(tǒng)計(jì)函數(shù)。
八、單行函數(shù)
九、子查詢
1****、定義和作用
子查詢指的就是在一個(gè)查詢之中嵌套了其他的若干查詢。
2****、分類
3****、單行單列
4****、多行單列
5****、多行多列
單表查詢 練習(xí)演示
練習(xí)：
1. 查詢所有員工信息
SELECT * FROM emp
查詢每個(gè)員工的編號、姓名、職位
SELECT EMPNO,ENAME,JOB FROM emp
查詢所有部門信息
SELECT DEPTNO FROM emp
2、消除重復(fù)
DISTINCT 關(guān)鍵字可以用于一列，也可以用于多列。比如：SELECT distinct job,deptno FROM emp; 只
有當(dāng) job 和 deptno 相同，才認(rèn)為是重復(fù)的數(shù)據(jù)。
練習(xí)：
查詢所有有員工的部門編號
SELECT DISTINCT DEPTNO FROM emp
查詢有員工的部門和職位
SELECT DISTINCT DEPTNO,JOB FROM emp
3、算術(shù)運(yùn)算符
對 NUMBER 型數(shù)據(jù)可以使用算數(shù)操作符創(chuàng)建表達(dá)式（+ - * /）；
對 DATE 型數(shù)據(jù)可以使用算數(shù)操作符創(chuàng)建表達(dá)式（+ -）。
練習(xí)：
查詢所有員工的年薪
SELECT sal,sal*12 FROM emp
查詢所有員工的年薪（使用別名）
SELECT sal,sal*12 yearsal FROM emp
4.過濾查詢
練習(xí)：
查詢所有員工的年薪((月薪 + 獎(jiǎng)金) * 12)
SELECT sal*12,comm*12,(sal+IFNULL(COMM,0))*12 FROM emp
查詢有獎(jiǎng)金的員工信息
#不為null 且不為0 才叫有獎(jiǎng)金
SELECT * FROM emp where comm IS NOT NULL AND comm!=0
查詢公司的老板
SELECT * FROM emp WHERE mgr is null
查詢出基本工資高于 1500 的所有員工信息
SELECT * FROM emp where sal>1500
查詢名字叫 SCOTT 的員工所從事的工作
SELECT ENAME,JOB FROM emp WHERE ENAME='SCOTT'
查詢 1981 年入職的員工信息
SELECT * FROM emp where HIREDATE BETWEEN '1981-01-01' and '1981-12-31'
SELECT * FROM emp where HIREDATE LIKE '1981%';
查詢年薪小于 3W 的員工
SELECT * FROM emp where (sal*12) < 30000
查詢所有不是銷售人員的員工信息
SELECT * FROM emp where JOB!='SALESMAN'
查詢工資在 2000-3000 之間的員工信息
SELECT * from emp WHERE sal BETWEEN 2000 and 3000
查詢 1981 年入職的員工
SELECT * FROM emp where HIREDATE BETWEEN '1981-01-01' and '1981-12-31'
查詢工資為 800 或 1600 或 3000 的員工
SELECT * FROM emp where sal in (800,1600,3000)
查詢出所有雇員姓名是以 A 開頭的全部雇員信息。
SELECT * FROM emp where ename LIKE 'A%'
查詢出雇員姓名第二個(gè)字母是 M 的全部雇員信息。
SELECT * from emp where ename like '_M%'
查詢出雇員姓名任意位置上包含字母 A 的全部雇員信息。
SELECT * FROM emp WHERE ENAME LIKE ('%A%')
多表查詢 練習(xí)演示
# 隱式 內(nèi)連接
練習(xí)：
查詢員工編號，員工名稱，員工所屬部門的編號和名稱
SELECT e.DEPTNO,ENAME,d.DEPTNO,DNAME FROM emp e,dept d where e.DEPTNO=d.DEPTNO
SELECT EMPNO,ENAME,e.DEPTNO,d.DNAME
from emp e
JOIN dept d on e.DEPTNO=d.DEPTNO
查詢員工的姓名，工資，所在部門的名稱，以及工資的等級
SELECT ENAME,SAL,DEPTNO,GRADE FROM emp e , salgrade s where SAL>LOSAL and SAL<HISAL
SELECT ENAME,SAL,DEPTNO,GRADE FROM emp e , salgrade s where SAL BETWEEN LOSAL AND HISAL
#顯式內(nèi)連接
SELECT ENAME,SAL,DNAME,GRADE FROM emp e
JOIN dept d ON e.DEPTNO=d.DEPTNO
JOIN salgrade on sal BETWEEN LOSAL AND HISAL
# DEPTNO 部門 DNAME 部門名稱 LOC 位置
#EMPNO 編號 ENAME 名稱 JOB 職位 MGR 上級 HIREDATE 入職日期 SAL 工資 COMM 獎(jiǎng)金 DEPTNO 部門
#GRADE LOSAL HISAL
# 顯式內(nèi)連接查詢
練習(xí)：
查詢員工編號，員工名稱，員工所屬部門的編號和名稱
SELECT e.EMPNO , e.ENAME, d.DEPTNO , d.DNAME
FROM emp e
JOIN dept d ON e.DEPTNO=d.DEPTNO
查詢員工的姓名，工資，所在部門的名稱，以及工資的等級
SELECT ENAME,SAL,d.DEPTNO,DNAME,GRADE,LOSAL,HISAL
FROM emp e
JOIN dept d on e.DEPTNO=d.DEPTNO
JOIN salgrade ON sal BETWEEN LOSAL AND HISAL
#左外連接查詢 和 右外連接查詢
查詢出員工的編號，名字，薪水和所在部門的名稱（使用內(nèi)連接查詢）
SELECT EMPNO,ENAME, SAL,e.DEPTNO
FROM emp e
left JOIN dept d ON e.DEPTNO=d.DEPTNO
SELECT EMPNO,ENAME, SAL,e.DEPTNO
FROM emp e
left JOIN dept d ON e.DEPTNO=d.DEPTNO
# 六 , 分組函數(shù)
/*注意：
統(tǒng)計(jì)函數(shù)忽略空值，可以使用 IFNULL， 因?yàn)槭?NULL 不會影響匯總值，但會影響匯總數(shù)量；
不能在 where 語句中使用分組函數(shù)。*/
練習(xí)：
查詢所有員工每個(gè)月的平均工資及總工資
SELECT avg(sal) , sum(sal) from emp
查詢月薪在 2000 以上的員工總?cè)藬?shù)
SELECT count(*) FROM emp where sal>2000
SELECT count(*) from emp where sal>2000
查詢員工最高工資和最低工資差距
SELECT max(sal),min(sal),max(sal)-min(sal) FROM emp
SELECT ename , max(sal) from emp
SELECT ename , min(sal) from emp
SELECT max(sal)-min(sal)from emp
# 2.分組語法
練習(xí)：
按照職位分組，求出每個(gè)職位的最高和最低工資
SELECT job,max(sal),min(sal) from emp GROUP BY job
SELECT job,max(sal),min(sal) from emp GROUP BY job
查詢出每一個(gè)部門員工的平均獎(jiǎng)金
SELECT DEPTNO,sum(IFNULL(comm,0))/count(*) from emp GROUP BY DEPTNO
SELECT DEPTNO,avg(IFNULL(comm,0)) from emp GROUP BY DEPTNO
3.使用分組注意
練習(xí)：
查詢出每一個(gè)部門員工的平均工資
SELECT DEPTNO,avg(IFNULL(sal,0)) from emp GROUP BY DEPTNO
查詢各個(gè)部門和崗位的平均工資
SELECT DEPTNO,job,avg(sal)FROM emp GROUP BY DEPTNO,job
SELECT DEPTNO,job,AVG(sal) from emp GROUP BY DEPTNO,job
4、分組限定
不能在 WHERE 子句中對分組限定，限制組須使用 HAVING 子句；
不能在 WHERE 子句中使用統(tǒng)計(jì)函數(shù)，而在 HAVING 子句可使用統(tǒng)計(jì)函數(shù)。
練習(xí)：
查詢部門平均工資高于 2000 的部門及其平均工資 先分組 再 確定
SELECT DEPTNO,AVG(sal) from emp GROUP BY DEPTNO HAVING avg(sal)
SELECT dname,e.DEPTNO,AVG(sal)
from emp e
JOIN dept d on e.DEPTNO=d.DEPTNO GROUP BY e.DEPTNO HAVING avg(sal)>2000
SELECT year(HIREDATE)a,count(*) from emp GROUP BY year(HIREDATE) HAVING a in(1980,1981,1982)
查詢在 80, 81, 82 年各進(jìn)公司多少人
SELECT year(HIREDATE) a,count(*) FROM emp GROUP BY year(HIREDATE) HAVING a IN(1980,1981,1982)
單行單列
查詢出工資比 MARTIN 還要高的全部雇員信息
SELECT * from emp where sal>(SELECT sal FROM emp where ename='MARTIN')
SELECT * FROM emp where sal>(SELECT sal FROM emp where ename='MARTIN')
查詢平均工資高于公司平均工資的部門信息
SELECT DEPTNO,avg(sal) from emp GROUP BY DEPTNO HAVING avg(sal)>(SELECT avg(sal) FROM emp)
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>(SELECT AVG(sal) FROM emp)
多行單列
查詢工資等于部門經(jīng)理（職位是 MANAGER）的員工信息。
SELECT * from emp GROUP BY deptno HAVING sal in(SELECT sal FROM emp WHERE job='MANAGER')
多行多列
查詢出每個(gè)部門的編號、名稱、部門人數(shù)、平均工資
select e.deptno,d.dname,e.a,e.c from (select deptno,avg(sal) a,COUNT(*) c from emp GROUP BY deptno) e join dept d on e.deptno=d.deptno
# DEPTNO 部門 DNAME 部門名稱 LOC 位置
#EMPNO 編號 ENAME 名稱 JOB 職位 MGR 上級 HIREDATE 入職日期 SAL 工資 COMM 獎(jiǎng)金 DEPTNO 部門
#GRADE LOSAL HISA
