cgb2111-day04

一,多表聯查

–1,準備表和資料

create table courses
(
cno varchar(5) not null,
cname varchar(10) not null,
tno varchar(3) not null,
primary key (cno)
);
create table scores
(
sno varchar(3) not null,
cno varchar(5) not null,
degree numeric(10,1) not null,
primary key (sno, cno)
);

create table students
(
sno varchar(3) not null,
sname varchar(4) not null,
ssex varchar(2) not null,
sbirthday datetime,
class varchar(5),
primary key (sno)
);

create table teachers
(
tno varchar(3) not null,
tname varchar(4),
tsex varchar(2),
tbirthday datetime,
prof varchar(6),
depart varchar(10),
primary key (tno)
);

INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾華' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王麗' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李軍' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陸君' ,'男' ,'1974-06-03',95031);
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','計算機系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','講師','電子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','計算機系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陳冰','女','1977-08-14','助教','電子工程系');
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'計算機導論',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'作業系統' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模擬電路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率論' ,831);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等數學' ,831);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

二,笛卡爾積

–1,概述

本質上就是把多張表,聯合查詢.要求,多張表用逗號隔開

–2,測驗

#多表聯查:
#1.笛卡爾積
#練習1:查詢部門表和員工表里的所有資料
SELECT * FROM dept,emp
#問題:產生了龐大的結果集,出現了重復的資料
#練習2:查詢部門表和員工表里的所有資料,添加過濾條件
SELECT * FROM dept,emp
WHERE dept.deptno=emp.deptno; #描述了兩個表的關系
#練習3:計算計算機導論課程所得的總分
      #聚合函式(表名.欄位名)    表1,表2
SELECT SUM(scores.degree) FROM courses,scores
#描述了兩個表的關系(表名.欄位名)
WHERE courses.cno=scores.cno
AND courses.cname='計算機導論'#真正的業務條件
#練習4:查詢學員陸君的總得分 sno
SELECT SUM(scores.degree) FROM scores,students
WHERE scores.sno=students.sno#描述了兩個表的關系(表名.欄位名)
AND students.sname='陸君'

三,連接查詢

–1,概述

本質上就是把多張表,聯合查詢.要求,多張表用join連接

–2,測驗

#2.連接查詢:join連接表,用on描述表關系
#練習5:統計陳冰老師能講的課程名稱
SELECT courses.cname FROM teachers JOIN courses
ON teachers.tno=courses.tno#描述了兩個表的關系
WHERE teachers.tname='陳冰'#真正的業務條件
#練習6:統計作業系統課程的總得分
SELECT SUM(scores.degree) FROM courses JOIN scores
ON courses.cno=scores.cno#描述了兩個表的關系
WHERE courses.cname='作業系統'#真正的業務條件
#面試題:三種連接查詢的區別?inner join/left join/right join
#inner join內連接,取兩個表的交集的資料
#left join左外連接,取左表的所有和右表里滿足條件的資料
#right join右外連接,取右表的所有和左表里滿足條件的資料

#中午,自己測驗三種區別???
SELECT * FROM 
dept INNER JOIN emp #內連接,取兩張表的交集
ON dept.deptno=emp.deptno
SELECT * FROM 
dept LEFT JOIN emp 
#左連接,取左表的所有和右表滿足條件的,不滿足的填充null
ON dept.deptno=emp.deptno
SELECT * FROM 
emp RIGHT JOIN dept
#右連接,取右表的所有和左表滿足條件的,不滿足的填充null
ON dept.deptno=emp.deptno

四,子查詢

–1,概述

又叫嵌套查詢,是指,把第一次的查詢結果,作為第二次的查詢條件,繼續發起查詢陳述句.

–2,測驗

#3.子查詢
#練習1:查詢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'
)
#練習2:查詢tony所在的部門名稱
#第1次:根據員工名字查部門編號
SELECT deptno FROM emp WHERE ename='tony'
#第2次:根據部門編號查部門名稱
SELECT dname FROM dept WHERE deptno=2
#子查詢:
SELECT dname FROM dept WHERE deptno=(
 SELECT deptno FROM emp WHERE ename='tony'
)
#練習3:查詢java開發部和research部門的員工姓名
#第1次:根據部門名稱查部門編號
SELECT deptno FROM dept 
WHERE dname='java開發部' OR dname='research'
#第2次:根據編號查員工姓名
SELECT ename FROM emp 
#where deptno=1 or deptno=2
WHERE deptno IN(1,2) #效果同上
#子查詢:第一次查到了多個結果,第二次查就要用in
SELECT ename FROM emp WHERE deptno IN(
 SELECT deptno FROM dept 
  WHERE dname='java開發部' OR dname='research'
)

五,綜合練習

–1,測驗

#綜合練習:
#練習1:查詢可以講高等數學的老師的名字
#笛卡爾積
SELECT tname FROM teachers,courses
WHERE teachers.tno=courses.tno#描述兩個表的關系
AND courses.cname='高等數學'
#連接查詢
SELECT tname FROM teachers a INNER JOIN courses b
ON a.tno=b.tno#描述兩個表的關系
WHERE b.cname='高等數學'
#子查詢
SELECT tname FROM teachers WHERE tno=(
 SELECT tno FROM courses WHERE cname='高等數學'
)
#練習2:查詢學員曾華的總得分
#笛卡爾積
SELECT SUM(a.degree) FROM scores a,students b
WHERE a.sno=b.sno  #描述兩個表的關系
AND  b.sname='曾華' #業務需求
#連接查詢
SELECT SUM(a.degree) FROM scores a JOIN students b
ON a.sno=b.sno  #描述兩個表的關系
WHERE  b.sname='曾華' #業務需求
#子查詢
SELECT SUM(degree) FROM scores WHERE sno=(
 SELECT sno FROM students WHERE sname='曾華'
)
#查詢高于平均工資的員工資訊
SELECT * FROM emp WHERE sal>(
 SELECT AVG(sal) FROM emp
)

六,擴展:索引

–1,概述

用來提高資料庫的查詢效率.
分類:
1,單值索引: 是指一個索引只包含著一個欄位/列
2,復合索引: 是指一個索引包含著多個欄位/列
3,唯一索引: 是一種特殊的單值索引,一個索引只包含著一個欄位/列,這個欄位的值要唯一

–2,測驗

#索引:提高查詢效率,建議給經常用來查詢的欄位加索引
#1.查看索引(主鍵自帶索引)
SHOW INDEX FROM students
#2.創建單值索引
#create index 索引名 on 表名(欄位名)
CREATE INDEX index1 ON students(sname)
#3.使用索引(按照索引列去查)
SELECT * FROM students WHERE sname='陸君'
#4.查看SQL的執行性能/計劃(只想關注你的SQL是否用到了索引)
EXPLAIN #看執行結果里的key
SELECT * FROM students WHERE sname='陸君'

#5.創建唯一索引:找到合適的列,值要唯一
#語法:create unique index 索引名 on 表名(欄位名)
#create unique index index2 on students(ssex)#不讓加,ssex值大量重復
CREATE UNIQUE INDEX index2 ON students(sno)
SHOW INDEX FROM students#查看索引
EXPLAIN SELECT * FROM students WHERE sno=101 #使用索引,索引失效!!
#!!6.創建復合索引:一個索引包含著多個列
CREATE INDEX index3 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 deptno=2#復合索引失效!
EXPLAIN SELECT * FROM emp WHERE ename='jack' AND job='總監'#生效
EXPLAIN SELECT * FROM emp WHERE job='總監' AND ename='jack'#生效
EXPLAIN SELECT * FROM emp WHERE job='總監' OR ename='jack'#失效!
#洗掉索引
ALTER TABLE emp DROP INDEX index3

–3,總結

1,優點:
大大提高了查詢效率
本質上資料庫會為索引列的資料進行排序,快速查詢
2,缺點:
本身索引是一個單獨的表,也需要占空間的
索引適合查詢的業務,但是,也需要同步更新修改一些新的資料,需要一定的時間
3,原則:
什么時候加索引? 頻繁的按照一個規則去查詢的資料,就應該考慮添加索引
給誰加索引? 給那些經常作為查詢條件的欄位添加索引
加啥索引? 索引是有分類的, 單值索引 / 唯一索引 / 復合索引,看情況選擇不同的索引型別
如何查看SQL的執行計劃/性能? explain

作業

用三種多表聯查的方式(笛卡爾積/連接查詢/子查詢),完成以下的練習:
練習1:查詢research部門的所有員工姓名和工資
練習2:查詢jack所在的部門資訊
練習3:查詢總監的部門資訊
練習4:查詢李軍的平均分
練習5:查詢陳冰能講的課程名