cgb2107-day04

一，外鍵約束

–1，測驗

CREATE TABLE tb_user(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20)
)
#外鍵約束的效果：
#1,子表的id必須取自主表的id 
#2,想洗掉主表的資料必須先刪掉子表相關的
CREATE TABLE tb_user_addr(
 user_id INT PRIMARY KEY,
 addr VARCHAR(20),
 #外鍵：通過特殊欄位(外鍵),描述了兩張表間的關系 
 #foreign key(當前表的主鍵) REFERENCES 對方表(對方表的主鍵)
 FOREIGN KEY(user_id) REFERENCES tb_user(id)
)

二，索引

–1，概述

好處是最大的作用就是提高查詢效率，壞處是索引本身也是一張表不適合大量的添加
實作程序：1，設定索引 2，使用索引
分類：單值索引 ， 唯一索引 ， 復合索引

–2，測驗

#0.查看索引
SHOW INDEX FROM dept
#1.創建單值索引(給常用來作為查詢條件的欄位加)
  #語法：CREATE INDEX 索引名  on 表名（欄位名）
CREATE INDEX dname_index ON dept(dname)
#2.使用索引(背后的手段)
EXPLAIN#觀察sql的性能/執行計劃(找possible_keys的值)
SELECT * FROM dept WHERE dname='java'
#3.創建唯一索引(索引列的值不能重復)
CREATE UNIQUE INDEX uni_index ON dept(dname)
#4. 創建復合索引 并 使用復合索引
  # CREATE INDEX 索引名 ON 表名 (欄位1,欄位2)
CREATE INDEX fuhe ON emp(ename,job)
SHOW INDEX FROM emp
#5. 使用復合索引(最左特性,否則復合索引失效)
EXPLAIN SELECT * FROM emp WHERE ename='jack' #按ename查，生效
	#按ename和job查，生效
EXPLAIN SELECT * FROM emp WHERE ename='jack' AND job='副總'
	#按job和ename查，生效
EXPLAIN SELECT * FROM emp WHERE job='副總' AND 	ename='jack'
	#按job查，失效
EXPLAIN SELECT * FROM emp WHERE job='副總'	

SHOW INDEX FROM emp
#洗掉索引
#   修改表  表名  洗掉索引  索引名
ALTER TABLE emp DROP INDEX fuhe

三，多表聯查

–1，概述

用來完成聯合多張表的查詢（3張以下），

–2，測驗

#多表聯查：產生了大量的冗余資料
#1.笛卡爾積：把多張表用逗號隔開
SELECT * FROM dept,emp 
	#描述兩張表的關系：表名.欄位名
	WHERE dept.deptno=emp.deptno 
#2.連接查詢,join..on
SELECT * FROM dept JOIN emp
	#描述兩張表的關系：表名.欄位名
	ON dept.deptno=emp.deptno
#3.子查詢：把上次的查詢結果，用來作為下次查詢的條件
	

#練習語法：teachers / courses
SELECT * FROM teachers , courses WHERE teachers.tno=courses.tno
SELECT * FROM teachers JOIN courses ON teachers.tno=courses.tno
#練習1：查詢部門名稱叫accounting的員工姓名
#子查詢：
#1，查部門表，根據部門名稱accounting查deptno
SELECT deptno FROM dept WHERE dname='accounting'
#2，查詢員工表，根據deptno查姓名
SELECT ename FROM emp WHERE deptno=1
SELECT ename FROM emp WHERE deptno=(
	SELECT deptno FROM dept WHERE dname='accounting')
	
#笛卡爾積：逗號隔開表名，where里寫查詢條件，可以多個
SELECT emp.ename FROM dept,emp 
	WHERE dept.deptno=emp.deptno #表關系
	AND dept.dname='accounting' #業務條件
#連接查詢：JOIN連接表名，ON寫表關系，where里寫查詢條件，可以多個 
SELECT emp.ename FROM dept JOIN emp 
	ON dept.deptno=emp.deptno #表關系
	WHERE dept.dname='accounting' #業務條件
#練習2：列出research部門下的所有員工的資訊
#子查詢：
#根據部門名稱查部門編號
SELECT deptno FROM dept WHERE dname='research'
#根據部門編號查員工資訊
SELECT * FROM emp WHERE deptno=2
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='research')
#笛卡爾積：
SELECT emp.* FROM dept,emp 
	WHERE dept.deptno=emp.deptno #表關系
	AND dept.dname='research' #業務條件

#三種連接查詢：
#內連接inner join:取兩個表的交集
#左外連接left join:左表的所有和右表滿足條件的，不滿足是null
#右外連接right join:右表的所有和左表滿足條件的，不滿足是null
SELECT * FROM dept LEFT JOIN emp 
	ON dept.deptno=emp.deptno #表關系
	WHERE dept.dname='research' #業務條件
#練習3：查詢作業地址在二區的所有員工資訊
#子查詢:可以用in或者=連接子查詢
#根據部門地址查部門編號
SELECT deptno FROM dept WHERE loc="二區" #會查到多個值
#根據編號查員工資訊
SELECT * FROM emp WHERE deptno=2 OR deptno=3
SELECT * FROM emp WHERE deptno IN(2,3) #in子查詢
SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE loc="二區")
#笛卡爾積
SELECT emp.* FROM dept,emp 
WHERE dept.deptno=emp.deptno #表示了兩張表的關系
AND dept.loc="二區" #業務條件
#連接查詢--相對高效，小表驅動大表（左表寫一個小表）
SELECT emp.* FROM dept INNER JOIN emp #內連接取交集
ON dept.deptno=emp.deptno #表示了兩張表的關系
WHERE dept.loc="二區" #業務條件