cgb2108-day04-有解無憂

文章目錄

- 一,索引
- - --1,概述
  - --2,測驗
- 二,視圖
- - --1,概述
  - --2,測驗
- 三,多表聯查
- - --1,準備資料
  - --2,三種方式
  - --3,測驗
- 四,作業

一,索引

–1,概述

為了提高查詢效率. 索引會單獨生成一張表,要合理的使用索引.

分類:
1,單值索引: 一個索引只包含一個欄位
2,唯一索引: 一個索引只包含一個欄位,但欄位的值不能重復
3,復合索引: 一個索引可以包含著多個欄位
使用步驟:
1,創建索引(經常按照指定欄位查詢) + 使用索引

–2,測驗

#1.索引:好處是:提高查詢效率 壞處是:索引需要單獨的一張表
#1.1 查看索引:show index from 表名
SHOW INDEX FROM emp #主鍵自帶索引
#1.2 創建單值索引:一個索引只包含一個欄位
CREATE INDEX ename_index ON emp(ename)
#1.3 創建唯一索引:一個索引只包含一個欄位,索引列值不能重復
#create unique index uni_index on emp(job)#失敗,因為job的值有重復的
CREATE UNIQUE INDEX uni_index ON emp(sal)
#1.4 創建復合索引:一個索引包含著多個欄位,遵循最左特性
CREATE INDEX fuhe_index ON emp(job,hiredate,comm)
#1.5 使用索引:背后的故事,按照索引列去查
SELECT * FROM emp WHERE ename='jack' #使用單值索引
SELECT * FROM emp WHERE sal=10000 #使用唯一索引
#使用復合索引,遵循最左特性
EXPLAIN SELECT * FROM emp WHERE job='總監' #生效
EXPLAIN SELECT * FROM emp WHERE job='總監'AND hiredate='2019-1-1'#生效
EXPLAIN SELECT * FROM emp WHERE hiredate='2019-1-1' #失效	
EXPLAIN SELECT * FROM emp WHERE hiredate='2019-1-1'AND comm=100#失效 
EXPLAIN SELECT * FROM emp WHERE hiredate='2019-1-1' AND job='總監'#生效
#1.6 查詢SQL的執行計劃/性能(看看用沒用索引)
EXPLAIN #possible_keys用到的索引
SELECT * FROM emp WHERE ename='jack'
EXPLAIN
SELECT * FROM emp WHERE sal=10000
#1.7 洗掉索引
ALTER TABLE emp DROP INDEX fuhe_index

二,視圖

–1,概述

和索引一樣,都是對資料庫優化的有效方案.
特點: 1, 可以把視圖當做表來使用 2,視圖里存的資料是 SQL查詢到的結果 3,SQL無法優化,要合理的使用視圖

–2,測驗

#2.視圖:快取了SQL陳述句的執行結果,當做表來用--了解
#好處是:
  #1.簡化了查詢的SQL(相同的SQL需求不必再寫SQL了,直接查視圖)
  #2.視圖可以被共享,視圖屏蔽了真實業務表的復雜性
#壞處是:視圖一旦創建,SQL無法被優化
#2.1 創建視圖
#語法:create view 視圖名 as 查詢的SQL陳述句
CREATE VIEW name_view AS 
	SELECT * FROM emp WHERE ename LIKE '%a%'
#2.2 使用視圖
SELECT * FROM name_view

三,多表聯查

–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);

–2,三種方式

1, 笛卡爾積: 語法 select * from 表名1,表名2,表名3
2, 連接查詢
3, 子查詢

–3,測驗

#多表聯查:
#方式1:笛卡爾積,通過逗號連接表名
#練習1:查詢部門編號是1的部門資訊和員工資訊
SELECT * FROM dept,emp
    #表名.欄位名=表名.欄位名
WHERE dept.deptno=emp.deptno#描述兩個表的關系
       AND dept.deptno=1#查deptno=1的資料
#練習2:查詢員工姓名叫jack的部門資訊      
SELECT dept.* FROM dept,emp
WHERE dept.deptno=emp.deptno #描述了兩張表的關系
   AND emp.ename='jack' #業務條件      
#練習3:查詢崗位是總監所在的部門資訊    
SELECT dept.* FROM dept,emp
WHERE dept.deptno=emp.deptno#描述了兩張表的關系
      AND emp.job='總監' #業務條件   
#方式2:連接查詢,分為三種:
#內連接inner join:取兩張表的交集
#左連接left join:取左表的所有和右表滿足條件的
#右連接right join:取右表的所有和左表滿足條件的
#練習1:列出java開發部 部門下的所有員工的資訊
#笛卡爾積方式
SELECT emp.* FROM dept,emp
WHERE dept.deptno=emp.deptno#描述了兩張表的關系
	AND dept.dname='java開發部' #業務條件
#連接查詢方式
SELECT emp.* FROM dept JOIN emp
ON dept.deptno=emp.deptno#描述了兩張表的關系
WHERE dept.dname='java開發部' #業務條件
#練習2:查詢崗位是總監所在的部門資訊  
SELECT dept.* FROM dept JOIN emp
ON dept.deptno=emp.deptno#描述了兩張表的關系
WHERE emp.job='總監' #業務條件
#練習3:查詢員工姓名叫jack的部門資訊  
SELECT * FROM emp JOIN dept #只寫join是inner join的簡寫形式
ON emp.deptno=dept.deptno#描述了兩張表的關系
WHERE emp.ename='jack' #業務條件
#三種連接查詢:inner join/left join/right join
SELECT * FROM dept INNER JOIN emp #可以簡寫成join
#取兩張表都滿足條件后的交集
ON emp.deptno=dept.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:查詢陳冰老師能講解的課程的名稱(teachers/courses)
#笛卡爾積方式
SELECT courses.cname FROM teachers,courses
WHERE teachers.tno=courses.tno#描述兩張表的關系
      AND teachers.tname='陳冰' #業務條件
#連接查詢方式
SELECT courses.cname FROM teachers INNER JOIN courses
ON teachers.tno=courses.tno#描述兩張表的關系
      WHERE teachers.tname='陳冰' #業務條件
#綜合練習2:查詢學員李軍的總得分(students/scores)
#笛卡爾積方式
SELECT SUM(degree) FROM students s1,scores s2
WHERE s1.sno=s2.sno #描述兩張表的關系
     AND s1.sname='李軍' #業務條件
#連接查詢方式
SELECT SUM(degree) FROM students s1 INNER JOIN scores s2
ON s1.sno=s2.sno #描述兩張表的關系
WHERE s1.sname='李軍' #業務條件

#方式3:子查詢/嵌套查詢:把上次的查詢結果作為這次的查詢條件
#練習1:查詢學員李軍的總得分(students/scores)
#先查詢學生表,根據學員名字查編號
SELECT sno FROM students WHERE sname='李軍'#101
#再根據編號查得分表里的分數
SELECT SUM(degree) FROM scores WHERE sno=101
#子查詢
SELECT SUM(degree) FROM scores WHERE sno=(
  SELECT sno FROM students WHERE sname='李軍'
)
#練習2:查詢陳冰老師能講解的課程的名稱(teachers/courses)
#先查老師表,根據名字查編號
SELECT tno FROM teachers WHERE tname='陳冰' #831
#再根據編號查課程名稱
SELECT cname FROM courses WHERE tno=831
#子查詢
SELECT cname FROM courses WHERE tno<(
  SELECT tno FROM teachers WHERE tname='陳冰'
)

四,作業

#SQL的練習:
#練習1:查詢部門編號是1的員工姓名
#練習2:查詢員工姓名叫jack的部門資訊
#練習3:查詢崗位是總監所在的部門資訊
#練習4:列出java開發部部門下的所有員工的資訊
#練習5:查詢部門地址在北京和廣州的員工資訊

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/304365.html

標籤：其他

上一篇：總結15個vscode的快捷鍵

下一篇：前端之變（一）：技術的變與不變