cgb2106-day04

一,索引

–1,概述

為了提高資料庫的查詢效率,可以使用索引.
給哪些欄位設計索引? 查詢需要量比較大. 欄位的值比較大的時候…
分類:
單值索引(一個索引只包含一個列) create index 索引名 on 表名(欄位名)
唯一索引(索引列的值不能重復) alter table 表名 add unique(欄位名)
復合索引(一個索引包含多個列) alter table 表名 add index 索引名(欄位名,1,1,1)
好處: 大大的提高了SQL的查詢效率
壞處: 索引本身也是一張表,表里的資料和真正的表里的資料是重復的,浪費了空間.

–2,測驗

#索引的分類:單值/唯一/復合
#1.創建 單值索引:一個索引包含了一個列
CREATE INDEX job_index ON emp(job)
#創建 唯一索引
#alter table emp add unique(deptno)報錯,deptno的值重復啦
ALTER TABLE emp ADD UNIQUE(ename)
#創建 復合索引:最左特性
ALTER TABLE emp ADD INDEX many_index(ename,job,hiredate)

#2.查看索引
SHOW INDEX FROM emp

#3.使用索引
EXPLAIN 
#用來觀察SQL的執行計劃,主要看有沒有用索引
#(觀察兩列的結果:key和possible_keys)
SELECT * FROM emp WHERE job='經理'#背后會用job索引

EXPLAIN
SELECT * FROM emp WHERE empno=100#背后會用主鍵索引

EXPLAIN
SELECT * FROM emp WHERE ename='jack'#背后會用唯一索引
SELECT * FROM emp WHERE ename='jack' AND job='經理'#復合索引生效
SELECT * FROM emp WHERE hiredate='2010-1-1'#復合索引失效
SELECT * FROM emp WHERE job='經理' AND hiredate='2010-1-1'#復合索引失效

二,關聯查詢

–1,測驗

#多表聯查:一張表已經無法滿足業務需求,需要聯合查詢多張表
#方式1:笛卡爾積
SELECT * FROM dept,emp #查出了所有資料
SELECT * FROM dept,emp
     #表名.欄位名 
WHERE dept.deptno=emp.deptno#兩張表的關聯關系
AND dept.dname='accounting'

#方式2:連接查詢join
   #inner join/left join/right join
#作業中,常用的是: 小表 left join 大表,小表驅動大表
SELECT * FROM emp 
#inner join dept #兩邊都滿足的交集
LEFT JOIN dept #左邊的所有和右邊滿足了的
#right JOIN dept #右邊的所有和左邊滿足了的
ON dept.deptno=emp.deptno #描述欄位
WHERE dept.dname='accounting' #具體過濾條件

#方式3:子查詢:把上次的查詢結果作為條件再次查詢
#1.根據部門名稱查部門編號dept
SELECT deptno FROM dept WHERE dname='accounting'
#2.把查到的編號作為條件,查員工資訊emp
SELECT * FROM emp WHERE deptno=1
#查詢部門名稱是accounting的所有資訊
SELECT * FROM emp WHERE deptno=(
  SELECT deptno FROM dept WHERE dname='accounting'
)

–2,練習

#練習1:查詢辦公地址在一區的員工資訊
#子查詢:根據地址查編號,再根據編號查員工
SELECT * FROM emp WHERE deptno=(
 SELECT deptno FROM dept WHERE loc='一區'
)
#笛卡爾積
SELECT * FROM dept,emp WHERE 
dept.`deptno`=emp.`deptno`
AND dept.`loc`='一區'
#join
SELECT * FROM dept LEFT JOIN emp  
ON dept.`deptno`=emp.`deptno`
WHERE dept.`loc`='一區'
#練習2:查詢辦公地址在二區的員工資訊
#子查詢:先根據地址查編號,再根據編號查員工資訊
SELECT deptno FROM dept WHERE loc='二區'# 2 3
SELECT * FROM emp WHERE deptno IN (2,3) #in查詢
SELECT * FROM emp WHERE deptno IN (
 SELECT deptno FROM dept WHERE loc='二區'
)
#笛卡爾積
SELECT * FROM dept a, emp b
WHERE a.deptno=b.deptno
AND a.loc='二區'
#join
SELECT * FROM dept a INNER JOIN emp b 
ON a.deptno=b.deptno
WHERE a.loc='二區'

三,SQL的練習題

SELECT * FROM emp #低效
SELECT empno,ename,job,sal FROM emp #高效
SELECT * FROM emp WHERE empno=100
SELECT * FROM emp WHERE job LIKE '總%'#高效
SELECT * FROM emp WHERE job LIKE '_總'#_通配一個字符
SELECT * FROM emp WHERE job LIKE '%總%'#%通配n個字符
SELECT * FROM emp WHERE empno>100 AND empno<300
SELECT * FROM emp WHERE empno BETWEEN 100 AND 300#包含
SELECT * FROM emp 
#where ename='rose' and job='副總' #并且關系
#where ename='jack' or job='員工' #或者關系
WHERE ename='jack' OR ename='rose' #或者關系
#WHERE ename in('jack','rose')

#查詢>45歲的人的名字
SELECT sname FROM students 
#where里不能用聚合函式max min sum avg count
WHERE YEAR(NOW())-YEAR(sbirthday) > 45

#按照年齡排序
SELECT *,YEAR(NOW())-YEAR(sbirthday) AS age 
FROM students
ORDER BY age
#查詢年齡最大的
#desc limit 1
SELECT sname,MIN(sbirthday) FROM students
GROUP BY sname
#查詢高于平均工資的員工資訊
SELECT * FROM emp WHERE sal > (
  SELECT AVG(sal) FROM emp
)
#查詢部門地址在二區的員工資訊
SELECT deptno FROM dept WHERE loc='二區'
SELECT * FROM emp WHERE deptno IN (2,3)
SELECT * FROM emp WHERE deptno IN (
  SELECT deptno FROM dept WHERE loc='二區'
)

#笛卡爾積/join/子查詢
#練習1:查詢李軍的總得分
#練習2:查詢易天負責的課程名稱

作業

#笛卡爾積/join/子查詢
#練習1:查詢李軍的總得分
#練習2:查詢易天負責的課程名稱