cgb2109-day05

一,多表聯查

–1,方式3:子查詢

也叫嵌套查詢,把上次的查詢結果,當這次查詢的條件來用

#方式3:子查詢:要分析第一次查啥,第二次查啥
#練習1:查詢計算機導論課程的總分
#第一次查:根據cname查cno- 3-105
SELECT cno FROM courses WHERE cname='計算機導論'
#第二次查:根據cno查degree
SELECT SUM(degree) FROM scores WHERE cno='3-105'
#改成子查詢:嵌套查詢
SELECT SUM(degree) FROM scores WHERE cno=(
 SELECT cno FROM courses WHERE cname='計算機導論'
)
#練習2:查詢易天老師能講的課程名稱
#第一次查:根據tname查tno 804
SELECT tno FROM teachers WHERE tname='易天'
#第二次查:根據tno查cname
SELECT cname FROM courses WHERE tno=804
#改造 (子查詢)
SELECT cname FROM courses WHERE tno=(
 SELECT tno FROM teachers WHERE tname='易天'
)
#練習3:查詢accounting部門的員工的名字
SELECT ename FROM emp WHERE deptno IN(
 SELECT deptno FROM dept WHERE loc='二區'
)
#練習4:查詢高于平均工資的員工資訊
SELECT * FROM emp WHERE sal>(
 SELECT AVG(sal) FROM emp #平均工資
)

–練習1:查詢research部門的所有員工姓名和工資

#練習1:查詢research部門的所有員工姓名和工資
#方式3:子查詢
SELECT emp.ename,emp.sal FROM emp WHERE deptno=(
 SELECT deptno FROM dept WHERE dname='research'
)
#方式1:笛卡爾積
SELECT emp.ename,emp.sal FROM emp,dept 
WHERE emp.deptno=dept.deptno#表關系
AND dept.dname='research'#業務條件
#方式2:連接查詢
SELECT emp.ename,emp.sal FROM dept INNER JOIN emp 
ON emp.deptno=dept.deptno#表關系
WHERE dept.dname='research'#業務條件

–練習2:查詢research部門的所有員工姓名和工資

#練習2:查詢jack所在的部門資訊
#方式3:子查詢
SELECT * FROM dept WHERE deptno=(
 SELECT deptno FROM emp WHERE ename='jack'
)
#方式1:笛卡爾積
SELECT dept.* FROM dept,emp
WHERE emp.deptno=dept.deptno#表關系
AND emp.ename='jack'#業務條件
#方式2:連接查詢
SELECT dept.* FROM dept JOIN emp
ON emp.deptno=dept.deptno#表關系
WHERE  emp.ename='jack'#業務條件

–練習3:查詢總監的部門資訊

#練習3:查詢總監的部門資訊
#方式1:笛卡爾積
SELECT dept.* FROM dept,emp
WHERE emp.deptno=dept.deptno#表關系
AND emp.job='總監'#業務條件
#方式2:連接查詢
SELECT dept.* FROM dept JOIN emp
ON emp.deptno=dept.deptno#表關系
WHERE emp.job='總監'#業務條件
#方式3:子查詢
SELECT dept.* FROM dept WHERE deptno=(
 SELECT deptno FROM emp WHERE job='總監'
)

–練習4:查詢李軍的平均分

#練習4:查詢李軍的平均分
#方式3:子查詢
SELECT AVG(degree) FROM scores WHERE sno=(
 SELECT sno FROM students WHERE sname='李軍'
)
#方式1:笛卡爾積
SELECT AVG(scores.degree) FROM students,scores
WHERE students.sno=scores.sno#表關系
AND students.sname='李軍'#業務條件
#方式2:連接查詢
SELECT AVG(scores.degree) FROM students JOIN scores
ON students.sno=scores.sno#表關系
WHERE students.sname='李軍'#業務條件

–練習5:查詢陳冰能講的課程名

#練習5:查詢陳冰能講的課程名
#方式3:子查詢
SELECT courses.cname FROM courses WHERE tno=(
 SELECT tno FROM teachers WHERE tname='陳冰'
)
#方式1:笛卡爾積
SELECT courses.cname FROM teachers,courses
WHERE teachers.tno=courses.tno #表關系
AND teachers.tname='陳冰'#業務條件
#方式2:連接查詢
SELECT courses.cname FROM teachers JOIN courses
ON teachers.tno=courses.tno #表關系
WHERE teachers.tname='陳冰'#業務條件

二,資料庫的擴展

–1,索引

概述

好處是: 提高查詢效率 壞處是: 本身是單獨的空間來存盤
分類:
1,單值索引:一個索引只包含一列
2,唯一索引:一個索引只包含一列,值不能重復
3,復合索引:一個索引包含多個列

單值索引

create index 索引名 on 表名(欄位名)–一個索引只包含一個列

#索引:創建索引+使用索引
#1.查看索引
SHOW INDEX FROM emp
#2.創建 單值索引:一個索引包含一列
CREATE INDEX nameindex ON emp(ename)
#3.使用索引,發起select陳述句
SELECT * FROM emp WHERE ename='jack'
#4.檢查SQL的執行效率(觀察用沒用索引)
EXPLAIN#解釋說明SQL的執行計劃/性能(觀察possible keys的值)
SELECT * FROM emp WHERE ename='jack'
#練習:
CREATE INDEX jobindex ON emp(job)#單值索引
SHOW INDEX FROM emp#查看索引
EXPLAIN SELECT * FROM emp WHERE job='員工'#檢查是否使用了索引

唯一索引

create unique index 索引名 on 表名(欄位名)
一個索引只包含一個列,列的值不能重復

#5.創建 唯一索引:一個索引包含一列,值要唯一
CREATE UNIQUE INDEX locindex ON dept(loc)
#添加失敗,因為loc的值有重復的
CREATE UNIQUE INDEX dnameindex ON dept(dname)
SHOW INDEX FROM dept#查看索引
#使用索引
EXPLAIN SELECT * FROM dept WHERE dname='research'

復合索引

create index 索引名 on 表名(欄位名1,欄位名2,欄位名3…)
一個索引包含多個欄位,用時要遵循最左原則,否則復合索引失效
失效的情況:按照 2 3 23 ,沒有包含最左邊的

#6.創建 復合索引:
CREATE INDEX fuheindex ON emp(ename,job,deptno)
SHOW INDEX FROM emp
#用時可能會讓復合索引失效--必須遵循最左原則(必須包含著最左邊的)
EXPLAIN SELECT * FROM emp WHERE ename='jack' #生效
EXPLAIN SELECT * FROM emp WHERE job='總監' #失效
EXPLAIN SELECT * FROM emp WHERE ename='jack' AND job='副總' #生效
EXPLAIN SELECT * FROM emp WHERE job='副總'AND ename='jack'#生效

洗掉索引

alter table 表名 drop index 索引名
show index from 表名
使用explain關鍵字檢查,SQL中是否使用了索引(檢查SQL的執行性能)

#7.洗掉索引
SHOW INDEX FROM emp
ALTER TABLE emp DROP INDEX jobindex

總結

缺點:
索引本身也是一張表，該表保存了主鍵與索引欄位，并指向物體表的記錄，所以索引列也要占用空間
索引表中的內容，在業務表中都有，資料是重復的，空間是“浪費的”
雖然索引大大提高了查詢的速度，但對資料的增、刪、改的操作需要更新索引表資訊，如果資料量非常巨大，更新效率就很慢,因為更新表時,MySQL不僅要保存資料,也要保存一下索引檔案
隨著業務的不斷變化，之前建立的索引可能不能滿足查詢需求，需要消耗我們的時間去更新索引

–2,視圖

概念

是一個特殊的表,存了上次查詢的結果.

測驗

#視圖:創建視圖+使用視圖
#練習:查詢名字里有a的員工資訊
SELECT * FROM emp WHERE ename LIKE '%a%'
#1.創建視圖:create view 視圖名 as select陳述句
CREATE VIEW empview AS 
SELECT * FROM emp WHERE ename LIKE '%a%'
#2.使用視圖
SELECT * FROM empview
#好處:提高了SQL的復用性+屏蔽了業務表的復雜性+資料共享
#壞處:是一張單獨的表存了業務表的資料造成了資料重復+無法優化

–3,SQL優化

1, 用欄位名稱代替*
2, where里: 盡量用and不用or , 盡量用=不用!= <> , 條件越精確越好 ,
3, 表設計:
表里的索引不要超過5個 , 給where后或者order by經常用的欄位加索引 ,復合索引要遵循最左特性不然就失效了 , 索引表及時刪掉多余的索引
用varchar代替char , 用數字代替字串 , 用默認值代替null
4, 批量處理:
批量查,批量的刪,最好使用分頁
詳情見課件:https://blog.csdn.net/u012932876/article/details/117359992

–4,三范式

資料庫的專業術語,用來設計表要遵循的原則,范式NF
分為六大范式,通常只要遵循前三大范式就可以了
第一范式1NF: 表里的欄位 不可分割,是指欄位的值就是最小單位
簡而言之，第一范式就是無重復的列
第二范式2NF: 基于第一范式的基礎上產生的,
指表里都應該設計主鍵/主關鍵字/主屬性
每行都應該圍繞著主鍵來 描述資料
總之，第二范式就是非主屬性完全依賴于主關鍵字
第三范式3NF: 基于第二范式的基礎上產生的,
是指表里的欄位之間盡量不要產生依賴,
總之,第三范式就是屬性不依賴于其它非主屬性