第3章：關係資料庫標準語言 SQL

2020 年 5 月 8 日
筆記
資料庫系統概論(第5版)

第3章：關係資料庫標準語言 SQL

基於SQLServer學習使用，與MySQL有略微差別！

3.1、SQL概述

3.1.1、歷史

1974年，由Boyce和Chamber提出。
1975-1979年，在由IBM的San Jose研究室研製的System R上實現，稱為Sequel，現在稱為SQL (Struceured Query Languang)。
1986年10月，美國國家標準局（American National Standard Institute，簡稱ANSI）的資料庫委員會批准了SQL作為關係資料庫語言的美國標準。
1987年，國際標準組織（International Organization for Standardization，簡稱ISO）也通過了這一標準。

3.3.2、SQL語言的功能

SQL是一個綜合的、通用的、功能極強的關係資料庫語言，它具有四個方面的功能：

數據定義（Data Definition）
數據查詢（Data Query）
數據操縱（Data Manipulation）
數據控制（Data Contro1）

3.3.3、SQL的特點

1、綜合統一

集DDL、DML、DCL功能於一體。
可以獨立完成資料庫生命周期中的全部活動
- 建立資料庫，定義關係模式，插入數據；
- 對資料庫中的數據進行查詢和更新；
- 資料庫重構和維護
- 資料庫安全性、完整性控制等

2、高度非過程化

用戶只需提出「做什麼」，無須告訴「怎麼做」，不必了解存取路徑。
存取路徑的選擇由系統自動完成。

3、面向集合的操作方式

一次一集合。

4、以同一種語法結構提供兩種使用方式

SQL是自含式語言
SQL是嵌入式語言

5、語言簡潔，易學易用

SQL語言完成核心功能只用9個動詞，語法接近英語口語。

SQL語言支援資料庫的三級模式結構：

所有基本表←→模式
部分視圖和部分基本表←→外模式，
所有存儲文件←→內模式

3.3.4、基本概念

基本表(Base Table)是本身獨立存在的表，每個（多個）基本表對應一個存儲文件，一個表可以帶若干索引。
視圖(View)是從一個或多個基本表中導出的表，資料庫中只存放視圖的定義而不存放視圖對應的數據，可以將其理解為一個虛表。用戶可以在視圖上再定義視圖
存儲文件的邏輯結構組成了關係資料庫的內模式。

3.2、學生-課程資料庫

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-X0SlKt4U-1588934201045)(//cdn.rainszj.com/學生-課程.png)]

student(sno, sname, ssex, sage, sdept)
course(cno, cname, cpno, ccredit)
cpno為外碼
sc(sno, cno, grade)
sno為外碼，cno為外碼

3.3、數據定義

3.3.1、使用資料庫

創建資料庫：create database <資料庫名>

CREATE DATABASE test;

使用資料庫：use <資料庫名>

use test;

刪除資料庫：drop database <資料庫名>

drop database test;

在SQLServer中，一個正在使用的資料庫是不能刪除的；

在MySQL中，一個正在使用的資料庫是可以刪除的；

SQLServer資料庫中有哪幾種文件組成？

1、主數據文件：*.mdf

每個資料庫有且只有一個主數據文件，它是資料庫和其他數據文件的起點，擴展名一般為 .mdf

2、次數據文件：*.ndf

用於存儲主數據文件中未存儲的剩餘資料和資料庫對象；
一個資料庫可以沒有次要數據文件，但也可以同時擁有多個次要數據文件；
次要數據文件的多少主要根據資料庫的大小、磁碟存儲情況和存儲性能要求而設置；
擴展名一般為：.ndf

3、日誌文件：*.ldf

存儲資料庫的事務日誌資訊，當資料庫損壞時，管理員使用事務日誌恢復資料庫；
擴展名一般為：.ldf

每個資料庫中至少有兩個文件：主數據文件、日誌文件

MySQL資料庫中有哪幾種文件組成？

3.3.2、SQLServer中的數據類型

3.3.3、基本表的建立（create）

語法：

create table <表名> (
		<列名> <數據類型>,
    <列名> <數據類型>,
  	……
    <列名> <數據類型>
);

注意：建表時，最後一個列不能加 *逗號（,）*

以下程式碼在SQLServer中執行可以，列級完整性約束中的 constraint 在MySQL中無法執行！

列級完整性約束：

CREATE TABLE course(
	cno CHAR(1) CONSTRAINT PK_course_cno PRIMARY KEY,
	cname varchar(20) CONSTRAINT UQ_course_cname UNIQUE,
	cpno CHAR(1),
	ccredit SMALLINT
);

表級完整性約束：

CREATE TABLE course (
	cno CHAR(1),
	cname VARCHAR(20),
	cpno CHAR(1),
	ccredit SMALLINT,
	CONSTRAINT PK_course_cno PRIMARY KEY(cno),
	CONSTRAINT UQ_course_cname UNIQUE(cname)
);

如果完整性約束條件涉及到該表的多個屬性列，完整性約束條件必須定義在表級上
如果完整性約束條件只涉及到該表的一個屬性列，完整性約束條件既可以定義在列級上也可以定義在表級上。

約束名：

每一個約束都有一個名字，稱為約束名。
約束名要全資料庫唯一。
在定義約束的時候，如果沒有指定名字，系統默認給定一個名字。
在定義約束的時候，用戶可以顯式指定約束名，方法：

constraint <約束名> <具體約束>

常用約束：

主碼約束：PK_
唯一性約束：UQ_（某一列有了唯一性約束之後，在該列只允許一個值為null）
默認值約束：DF_（只能定義在列級上）
參照完整性約束：FK_
CHECK約束：CK_

默認值約束舉例：

create table student(
	sno char(5),
	sname varchar(20) not null,
	sage smallint constraint DF_student_sage default(20),
	constraint PK_student_sno primary key(sno)
);

參照完整性（外碼）約束舉例：

CREATE TABLE course (
	cno CHAR(1),
	cname VARCHAR(20),
	cpno CHAR(1),
	ccredit SMALLINT,
	CONSTRAINT PK_course_cno PRIMARY KEY(cno),
	CONSTRAINT UQ_course_cname UNIQUE(cname),
	constraint CK_course_ccredit check(ccredit >0),
	constraint FK_course_cpno foreign key(cpno) references course(cno)
);

注意：外碼的數據類型必須和相應的主碼的數據類型保持一致

CHECK約束舉例：

CREATE TABLE course (
	cno CHAR(1),
	cname VARCHAR(20),
	cpno CHAR(1),
	ccredit SMALLINT,
	CONSTRAINT PK_course_cno PRIMARY KEY(cno),
	CONSTRAINT UQ_course_cname UNIQUE(cname),
	constraint CK_course_ccredit check(ccredit > 0)
);

3.3.4、基本表的修改（add、alter、drop）

add方式：用於增加新的列和完整性約束

語法：

alter table <表名> add <列定義> | <完整性約束定義>

<列定義> 格式為：<列名> <數據類型> [ null | not null ]
<完整性約束定義>格式為：constraint <約束名> <具體約束>

添加屬性scardid到student，scardid取值不重複

-- 方式一
alter table student add scardid char(18);
alter table stuent add constraint UQ_student_scardid unique(scardid);
-- 方式二
alter table student add scardid char(18) constraint UQ_student_scardid unique;

在sc表中增加完整性約束定義，使grade在0~100之間。

-- 方式一
ALTER TABLE sc ADD CONSTRAINT CK_sc_grade CHECK(grade >= 0 AND grade <= 100);
-- 方式二
ALTER TABLE sc ADD CHECK(grade BETWEEN 0 AND 100);

在course表中為ccredit增加默認約束，約束值為2。

-- SQLServer中
ALTER TABLE course ADD CONSTRAINT DF_course_ccredit DEFAULT(2) FOR ccredit;
-- MYSQL中
ALTER TABLE course MODIFY ccredit SMALLINT DEFAULT 2;

alter方式：只能用於修改某些列定義

語法：

alter table <表名> alter column <列定義>;

<列定義> 格式為：<列名> <數據類型> [ null | not null ]
alter 方式只能修改列的定義，不能修改約束，約束只能增加或刪除

注意：

使用 alter 方式修改列的定義時，不能將含有空值的列的定義修改為 not null
不能修改列名

drop方式：用於刪除某些列以及某些約束

語法：

alter table <表名> drop column <列名>;
alter table <表名> drop constraint <約束名>;

舉例：

-- 根據約束名刪除約束
alter table student drop constraint UQ_student_scardid;
-- 刪除列
alter table student drop column sdept;

3.3.5、基本表的刪除（drop）

語法：

drop table <表名>

注意：

在sqlserver中刪除基本表後，基本表的定義、表中數據、索引、約束都將被刪除。
在sqlserver中刪除基本表後，由此表導出的視圖仍然保留，但用戶引用會出錯。
在sqlserver中，如果有另外一個表的外碼參照該表的主碼，則不允許刪除該基本表。

3.3.6、索引的建立和刪除

索引建立的目的：提高查詢速度，但會降低插入數據和修改數據的速度

索引的種類：

1、聚集索引

表數據按照索引的順序來存儲，也就是說索引項的順序與表中記錄的物理順序一致；
對於聚集索引，節點即存儲了真實的數據行，不再有另外單獨的數據頁；
在一張表上最多只能創建一個聚集索引，因為真實數據的物理順序只能有一種；

2、非聚集索引

表數據存儲順序與索引順序無關；
對於非聚集索引，節點包含索引欄位值及指向數據頁數據行的邏輯指針；

3、唯一索引

每一個索引值只對應唯一的數據記錄

創建索引：

語法：

create [unique] [clustered] index <索引名> 
	     on <表名>(<列名>[<次序>][,<列名>[<次序>]]…);

注意：

一個表中的索引名需要唯一，多個表中的索引名可以重名；
一個索引可以建立在該表的一列或者多列上，各列名之間用逗號分隔；
用次序指定索引值的排列次序，升序：ASC，降序：DESC，默認是ASC

舉例：

create table t2(
	c1 int constraint PK_t2_c1 primary key, -- 自動創建聚集唯一性索引
	c2 int constraint UQ_t2_c2 unique  			-- 自動創建非聚集的唯一性索引
);

-- 在SQLServer中執行失敗，因為SQLServer會根據表中的主碼默認自動創建一個聚集索引
create clustered index ID_c2 on t2(c2);

刪除索引

語法：

drop index <表名>.<索引名>

舉例：

create table t1(
	c1 int,
	c2 int
);
-- 創建索引
create clustered index ID_t1_c1 on t1(c1);
-- 刪除索引
drop index t1.ID_t1_c1;

索引的有關說明：

一個表中可以有多個索引，索引可以提高查詢效率，但索引過多耗費空間，且降低了插入、刪除、更新的效率。
應該在使用頻率高的、經常用於連接的列上建索引。
可以動態地定義索引，即可以隨時建立和刪除索引。

3.4、數據查詢

SQLServer 建表數據下載地址

MySQL 建表數據下載地址

3.4.1、單表查詢

單表查詢的語法：

select [all|distinct] <目標列表達式>  [as] [別名] 
			[ ,<目標列表達式>  [as] [別名] ]…
	from <表名或視圖名>
	[where <條件表達式>]
	[order by <列名> [ASC|DESC] ]

1、通過select查詢指定的列：

select 子句的<目標列表達式>可以為：算術表達式、函數、屬性列名

-- 查詢學生的姓名、年齡、出生日期 SQLServer中有該三個參數的庫函數
select sname name, Datediff(YEAR, borndate, GETDATE()) age, borndate from Student
-- 查詢學號、課程號、考試時間、分數、分數+10
select sno, cno, examdate, grade, grade + 10 from sc
-- 查詢學號、課程號
select sno, cno from sc
-- 不寫，默認為 all，【all】展示該列的所有行
select all sno, cno from sc
-- 通過 【distinct】 關鍵字將查詢出的結果進行【去重】，去除重複行
select distinct sno, cno from sc
-- 通過 as 可以為目標列表達式起別名，改變查詢結果的列標題，as也可以省略，用空格代替
select sno as 學號, cno 課程號 from sc;

2、通過select查詢所有屬性列：

-- 查詢所有學生的資訊。
-- 在SELECT關鍵字後面列出所有列名 
SELECT Sno, Sname, Ssex, Sage, Sdept	FROM Student; 
-- 把SELECT關鍵字後面指定為 *
select * from student;

where子句，相當於關係代數中的選擇

3、比較大小運算符：=、>、 <、>=、 <=、<>（不等於、!=）、!>、!<

-- 查詢學號不等於 95001 的學生的資訊
select * from student where sno <> '95001'
-- 查詢年齡小於 35的學生的資訊
select * from student where Datediff(YEAR, borndate, GETDATE()) < 35
-- 查詢有課程不及格的學生的學號，必須進行【去除重複行】
select distinct sno from sc where grade < 60

4、確定範圍

X between A and B（在A和B之間，包含A和B）
X not between A and B（不在A和B之間）

-- 查詢年齡 在 [35,40] 的學生的學號、姓名、年齡
select sno, sname,Datediff(YEAR, borndate, GETDATE()) age from student where Datediff(YEAR, borndate, GETDATE()) between 35 and 40
-- 查詢年齡 不在 [35,40] 的學生的學號、姓名、年齡
select sno, sname,Datediff(YEAR, borndate, GETDATE()) age from student where Datediff(YEAR, borndate, GETDATE()) not between 35 and 40

4、確定集合

X in ( A, B, … )
X not in ( A, B, … )

-- 查詢資訊系或者數學系學生的資訊
select * from student where sdept in('資訊系', '數學系')
-- 查詢不在資訊系或者數學系學生的資訊
select * from student where sdept not in('資訊系', '數學系')

5、字元匹配

X like ‘<字元串>’ [esacape ‘<換碼符>’]
X not like ‘<字元串>’ [esacape ‘<換碼符>’]
X 必須是字元串
% ：代表任意長度（長度可以為0）的字元串
_ ：代表任意單個字元
[ ]：指定集合或範圍中的任何單個字元。
- 集合：c[adf]n，表示can、cdn、cfn
- 範圍： [C-P]arsen，表示以C 與 P 之間的任何單個字元開始的以’arsen’結尾的字元，例如，Carsen、Larsen、Karsen 等。
distinct：去除重複行
escape ‘<標識字元>’：不轉義標識字元後面那個字元

-- 查詢學號為95001的學生的資訊，完全匹配
select * from student where sno = '95001'
-- 查詢學號為95001的學生的資訊，模糊查詢
select * from student where sno like '95001'
-- 查詢所有姓劉的學生的所有資訊
select * from student where sname like '劉%'
-- 查詢所有姓【劉%】的學生的所有資訊
select * from student where sname = '劉%'
-- 查詢所有姓劉的且姓名為3個字的學生的資訊
select * from student where sname like '劉__'
-- 查詢所有姓劉的且姓名為2個字的學生的資訊
select * from student where sname like '劉_'
-- 查詢名字中第2個字為「陽」字的學生的姓名和學號
SELECT sname, Sno FROM student WHERE sname LIKE '_陽%'
-- 查詢所有姓王或者姓李的學生的姓名和學號
SELECT sname, Sno FROM student WHERE sname LIKE '[王李]%'
-- 查詢DB_Design課程的所有資訊，完全匹配
select * from course where cname = 'DB_Design'
-- 查詢前綴為 DB_ 的所有課程的所有資訊，模糊查詢
select * from course where cname like 'DB_%'						 	 -- 【錯誤】
select * from course where cname like 'DB\_%' escape '\'  -- 【正確】
select * from course where cname like 'DB!_%' escape '!'	 -- 【正確】

6、涉及空值的查詢

使用謂詞 is null 或 is not null 來測試指定列的值是否為空值。
「is null」不能用「= null」代替，
「is not null」不能用「!= null」代替

select * from course where cpno is null
select * from course where cpno is not null

7、邏輯運算符：not、and、or

and 的優先順序高於or，可以用括弧改變優先順序

-- 查詢電腦系並且年齡小於35的學生的資訊
select * from student where sdept = '電腦系' and  Datediff(YEAR, borndate, GETDATE()) < 35
-- 查詢電腦系或者年齡小於35的學生的資訊
select * from student where sdept = '電腦系' or  Datediff(YEAR, borndate, GETDATE()) < 35
-- 查詢不是電腦系的學生的資訊
select * from student where not sdept = '電腦系'

8、order by 子句

可以對查詢結果按一個或多個屬性列排序
升序：ASC；降序：DESC；預設值為升序
當排序列含空值時
- ASC：排序列為空值的元組最先顯示
- DESC：排序列為空值的元組最後顯示

-- 查詢學生課程表中的所有資訊，按照考試時間升序、成績降序排序
select * from sc order by examdate asc, grade desc
-- 查詢課程表中的所有資訊，按照先行課升序【默認】排序
select * from course order by cpno
-- 查詢課程表中的所有資訊，按照先行課降序排序
select * from course order by cpno desc

9、集函數（聚合函數）

計數:

count（[distinct|all] * ）
count（[distinct|all] <列名>）
計算總和:
sum（[distinct|all] <列名>）
計算平均值:
avg（[distinct|all] <列名>）
求最大值:
max（[distinct|all] <列名>）
求最小值:
min（[distinct|all] <列名>）

-- 查詢 95001 號學生的總成績
select sum(grade) as sumgrade from sc where sno = '95001'
-- 查詢課程表中所有課程的數目，其中 cno 為 primary key
select count(*) as count_course from course
select count(cno) as count_course from course
-- 查詢選課總人數
select count(sno) from sc;				-- 【錯誤】
select count(distinct sno) from sc	  -- 【正確】
-- 求學號為』95001』學生的總分、最高分、最低分、平均分
select sum(grade), max(grade), min(grade), avg(grade) from sc where sno = '95001'

當集函數遇到空值時，除count(*) 外，其餘集函數都不處理空值，只處理非空值。

如下例：

insert sc values( '10000', '01', getdate(), 90 );
insert sc values( '10000', '02', getdate(), 70 );
insert sc values( '10000', '03', getdate(),null );

select * from sc;
select count(*) from sc where sno = '10000';				     -- 3
select count(grade) from sc where sno = '10000';		     -- 2
select sum(grade) from sc where sno = '10000';
select avg(grade) from sc where sno = '10000';
select max(grade) from sc where sno = '10000';
select min(grade) from sc where sno = '10000';

考慮下面這種情形？

select sno from sc;
select count(*) from sc
select sno, count(*) from sc;						  	 -- 【錯誤】
select sno, count(*) from sc group by sno;	 -- 【正確】

錯誤原因：

選擇列表中的列 'sc.sno' 無效，因為該列沒有包含在聚合函數或 GROUP BY 子句中。

10、group by 子句

注意：

使用 group by 子句後，select 子句的後面只能出現

分組屬性
對每一組返回單一值的集函數

group by 子句後面可以跟上 having 短語

having 短語用來指定條件，只有滿足 having 短語指定條件的組才輸出

-- 查詢每個學生的學號、選課的數目
select cno, count(*) as num from sc group by cno
select cno, count(cno) from sc group by cno;
-- 列出每門課程的最高、最低分
select cno, max(grade) as maxg, min(grade) as ming from sc group by cno
select cno, sno from SC group by cno 					--【錯誤】
-- 查詢每個學生的選課門數
select sno, count(*) from sc group by sno
-- 查詢選修了3門以上課程的學生學號及選課門數
select sno, count(*) from sc group by sno having count(*) > 3
-- 查詢平均成績大於85分的各門課程的課號及平均成績
select cno, avg(grade) from sc group by cno having avg(grade) > 85
-- 查詢有2門以上課程是80分以上的學生的學號及（80分以上的）課程數
select sno, count(*) from sc where grade > 80 group by sno having count(*) > 2

3.4.2、集合查詢

1）集合併（union）：

注意：

集合併操作自動去除重複元組，如果要保留重複元組的話，在 union 後面上all關鍵詞指明
參加 union 操作的各結果表的列數必須相同，對應列的數據類型也必須相同

-- 求選修了01號課程的的學生學號和選修了02號課程的學生學號的並集 【默認去重】
select SNO from SC where CNO ='01'
union
select  SNO from SC where CNO ='02';
-- 求選修了01號課程的的學生學號和選修了02號課程的學生學號的並集【去重】
select distinct sno from sc where cno = '02' or cno = '01';
-- 求選修了01號課程的的學生學號和選修了02號課程的學生學號的並集 【指明不去重】
select SNO from SC where CNO ='01'
union all
select  SNO from SC where CNO ='02';
-- 求選修了01號課程的的學生學號和選修了02號課程的學生學號的並集 【默認不去重】
select sno from sc where cno = '02' or cno = '01';

注意：

order by 子句只能用於對最終查詢結果排序，不能對中間結果排序
任何情況下，order by 子句只能出現在查詢語句最後

-- 查詢 電腦系 系或年齡小於19的學生, 按學號排序
-- 【錯誤】
SELECT * FROM Student
WHERE Sdept= '電腦系'
ORDER BY Sno 
UNION
SELECT * FROM Student
WHERE datediff(YEAR, borndate, GETDATE()) < 19
ORDER BY Sno;

-- 【正確】
SELECT * FROM Student
WHERE Sdept = '電腦系'
UNION
SELECT * FROM Student
WHERE datediff(YEAR, borndate, GETDATE()) < 19
ORDER BY Sno;

2）集合交：intersect

-- 查詢電腦系中年齡不大於38的人
select * from student where sdept = '電腦系'
intersect
select * from student where DATEDIFF(YEAR, borndate, GETDATE()) <= 38;

3）集合差：except

-- 查詢電腦科學系的學生與年齡不大於38歲的學生的差集
select * from student where sdept = '電腦系'
except
select * from student where DATEDIFF(YEAR, borndate, getDate()) <= 38;

3.4.3、連接查詢

連接總可以表示成在廣義笛卡爾積上先進行選擇，然後在進行適當的投影
關係代數里的選擇對應著SQL語句中的 where 子句。
關係代數里的投影對應著SQL語句中的 select 子句。

1、廣義的笛卡爾積

語法：<表1> cross join <表2>
用法：用在from子句後面，cross join 也可用逗號代替。
意義：表1與表2之間做廣義笛卡兒積。

-- 廣義的笛卡爾積
select * from student, sc, course;
select * from student cross join sc cross join course;
select * from student, sc cross join course;
-- 查詢選課學生的基本資訊及其選修課程的情況,課程只給出課程號和成績
select student.*, sc.cno, sc.grade
from student cross join sc
where student.sno = sc.sno
-- 查詢每個學生的資訊以及課程的成績
select s.*, c.cname, sc.grade, sc.examdate
from student s, sc, course c
where s.sno = sc.sno and c.cno = sc.cno
-- 查詢選課學生的學號和姓名
select distinct s.sno, s.sname
from student s, sc
where s.sno = sc.sno

2、內連接

語法：<表1> inner join <表2> on <條件>
用法：用在from子句後面，
意義：表1與表2之間在on指定的條件上進行θ連接。

-- 查詢95001號學生選修的課程號和課程名
select distinct c.cno, c.cname
from sc inner join course c 
on sc.cno = c.cno
where sc.sno = '95001';
-- 查詢所有選修了2號課程的學生學號、姓名。
select distinct s.sno, s.sname
from student s inner join sc
on sc.sno = s.sno
where  sc.cno = '02'
-- 查詢2號課成績在85分以上的所有學生的學號、姓名
select s.sno, s.sname
from student s inner join sc 
on s.sno = sc.sno
where sc.grade >= 85 and sc.cno = '02'
-- 查詢選修了'數學'課的學生學號及姓名
select s.sno, s.sname
from student s inner join sc on s.sno = sc.sno
inner join course c on sc.cno = c.cno
where c.cname = '數學'

3、自身連接

一個表與其自己進行連接
需要給表起別名以示區別
由於所有屬性名都是同名屬性，因此必須使用表的別名來訪問各個屬性

-- 查詢每一門課的間接先修課（即先修課的先修課，沒有直接先行課的不輸出）
select c1.cno, c2.cpno
from course c1 inner join course c2 on c1.cpno = c2.cno
-- 查詢資料庫的間接先行課的課號、課程名稱
select c2.cpno, c2.cname
from course c1 inner join course c2 on c1.cpno = c2.cno
where c1.cname = '資料庫'

4、外連接

1）左外連接

語法：<表1> left outer join <表2> on <條件>
用法：用在from子句後面，
意義：表1與表2之間在on指定的條件上進行左外連接，防止左邊的表中（表1 ）的資訊因失配而丟失。

2）右外連接

語法：<表1> right outer join <表2> on <條件>
用法：用在from子句後面，
意義：表1與表2之間在on指定的條件上進行右外連接，防止右邊的表中（表2 ）的資訊因失配而丟失。

3）全外連接

語法：<表1> full outer join <表2> on <條件>
用法：用在from子句後面，
意義：表1與表2之間在on指定的條件上進行全外連接，防止左右兩邊的表中的資訊因失配而丟失。

-- 查詢每個學生及其選修課程的情況(包括沒有選課的學生，課程只給出課程號和成績）
select s.*, c.cno, sc.grade
from student s left join sc on s.sno = sc.sno
left join course c on sc.cno = c.cno
-- 學生表與成績表內連接
select * from student inner join sc on student.sno = sc.sno
-- 學生表與成績表左外連接
select * from student left join sc on student.sno = sc.sno
-- 學生表與成績表右外連接
select * from sc right join student on student.sno = sc.sno
-- 查詢每個學生和每門課的資訊，對於每個學生選修了哪些課，對於每門課有哪些學生選修
-- 方式一
select s.*, c.*, sc.grade
from student s left outer join sc on s.sno = sc.sno
full outer join course c on c.cno = sc.cno
-- 方式二
select s.*, c.*, sc.grade
from student s full outer join sc on s.sno = sc.sno
full outer join course c on c.cno = sc.cno

3.4.4、嵌套查詢

嵌套查詢的概述：一個 select-from-where 語句稱為一個查詢塊，在查詢時，可以將一個查詢塊嵌套在另一個查詢塊的 where 子句或 having 短語中，這樣的查詢稱為嵌套查詢

-- 查詢選修了02號課程的學生學號，姓名
-- 內連接
select s.sno, s.sname
from student s inner join sc on s.sno = sc.sno 
where sc.cno = '02'
-- 嵌套查詢,查詢結果有多列，當做另一張表來處理
select s.sno, s.sname
from student s inner join (select distinct sc.sno from sc where sc.cno = '02') t
on t.sno = s.sno
select distinct sc.sno from sc where sc.cno = '02'
-- 嵌套查詢,查詢結果有多列，使用 in 來處理
select s.sno, s.sname
from student s
where s.sno in (select distinct sc.sno from sc where sc.cno = '02')

注意：

子查詢不能使用 order by 子句，order by 子句子句只能作用於最終查詢結果
有些嵌套查詢可以用連接運算替代

子查詢的形式：

帶有 in 謂詞的子查詢
帶有比較運算符子查詢
帶有any 和 all 謂詞子查詢
帶有exists 謂詞子查詢

1、嵌套查詢（帶有 in 謂詞）

基本格式：

表達式 in (子查詢)
表達式 not in (子查詢)
判斷表達式的值是否在子查詢的結果中。

-- 查詢與 '95001'學號的學生在一個系的學生
select s.sdept from student s where s.sno = '95001'
select s.sno, s.sname, s.sdept
from student s
where s.sdept in (select s.sdept from student s where s.sno = '95001')
-- 查詢哪門課的先行課是：數據處理
-- 方式1：使用集合
select * from course 
where cpno in (select cno from course where cname ='數據處理')
-- 方式2：自身連接
select c1.*, c2.cname
from course c1 inner join course c2 on c1.cpno = c2.cno
where c2.cname = '數據處理'
-- 查詢選修了課程名為「資訊系統」的學生學號和姓名
-- 內連接
select s.sno, s.sname, c.cname
from course c inner join sc on c.cno = sc.cno 
inner join student s on s.sno = sc.sno
where c.cname = '資訊系統'
-- 嵌套查詢
-- 方式1 一層內連接，兩層嵌套
select c.cno
from course c where c.cname = '資訊系統'

select s.sno, s.sname, sc.cno
from sc inner join student s on s.sno = sc.sno
where sc.cno in (select c.cno
from course c where c.cname = '資訊系統')
-- 方式2 一層內連接，兩層嵌套
select distinct sc.sno
from course c inner join sc on c.cno = sc.cno
where c.cname = '資訊系統'

select s.sno, s.sname
from student s
where s.sno in (select distinct sc.sno
from course c inner join sc on c.cno = sc.cno
where c.cname = '資訊系統')
-- 方式3 三層嵌套
select c.cno
from course c
where c.cname = '資訊系統'

select sc.sno
from sc
where sc.cno in (select c.cno
from course c
where c.cname = '資訊系統')

select s.sno, s.sname
from student s
where s.sno in (select sc.sno
from sc
where sc.cno in (select c.cno
from course c
where c.cname = '資訊系統'))

不相關子查詢：

子查詢的查詢條件不依賴於父查詢。
是由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結果用於建立其父查詢的查找條件

2、嵌套查詢（帶有比較運算符）

當能確切知道內層查詢返回單值時，可用比較運算符（>，<，=，>=，<=，< >或!= ）來確定父查詢的條件。

-- 查詢與 '95001'學號的學生在一個系的學生
select s.sdept from student s where s.sno = '95001'
select s.sno, s.sname, s.sdept
from student s
where s.sdept = (select s.sdept from student s where s.sno = '95001')
-- 查詢與「劉晨」在同一個系學習的學生
-- 當有多個叫「劉晨」的學生且這多個「劉晨」不在同一個系是時，下面的SQL語句出錯。
-- 帶有比較運算符的子查詢要求返回單值
select s.sdept from student s where s.sname = '劉晨';
select s.* from student s where s.sdept = (select s.sdept from student s where s.sname = '劉晨')
-- 查詢與95001號學生在同一個系學習的學生
select s.sdept from student s where s.sno = '95001'
select * from student s where (select s.sdept from student s where s.sno = '95001') = s.sdept
-- 找出95002號學生在哪些課上的成績超過或等於他所選修課程的平均成績（給出課程號）
select cno from sc 
where sno = '95002' and grade >= (select avg(grade) from sc where sno = '95002')
-- 找出每個學生超過或等於他選修課程平均成績的課程號 【相關子查詢】
select sno, cno
from sc t1
where t1.grade >= (select avg(grade) from sc t2 where t2.sno = t1.sno)

註：在SQLServer中子查詢可以跟在比較符之前

相關子查詢（相當於雙層 for 循環，裡層藉助於外層）：

子查詢的查詢條件依賴於父查詢。
首先取外層查詢中的表中的第一個元組，根據它與內層查詢相關的屬性值處理內層查詢，若 where 子句返回值為真，則取此元組放入結果表；
然後再取外層表的下一個元組進行同樣處理；
重複這一過程，直至外層表全部檢查完為止。

3、嵌套查詢（帶有 any、all 謂詞）

謂詞語義

any：某一個值
all：所有值

需要配合比較運算符使用

表達式比較運算符 any (子查詢)
表達式的值與子查詢結果中的某一個值相比滿足比較運算符。
表達式比較運算符 all (子查詢)
表達式的值與子查詢結果中的所有的值相比都滿足比較運算符。

-- 查詢其他系中比資訊系中所有學生年齡小的學生姓名和年齡
-- 出生日期比他大，年齡就比他小
-- 方式1
select s.borndate, DATEDIFF(YEAR, borndate, GETDATE()) sage
from student s
where s.sdept = '資訊系'

select s.sname, DATEDIFF(YEAR, borndate, GETDATE()) sage, s.sdept
from student s
where s.sdept <> '資訊系' and s.borndate > all(select s.borndate
from student s
where s.sdept = '資訊系')
-- 方式2
select s1.sname, DATEDIFF(YEAR, borndate, GETDATE()) sage, s1.sdept
from student s1
where s1.sdept <> '資訊系' and s1.borndate > (select max(s2.borndate)
from student s2
where s2.sdept = '資訊系')

ANY和ALL謂詞有時可以用集函數實現：

對應關係：

	=	<>	<	<=	>	>=
any	in	—	< max	<= min	> min	>= min
all	—	not in	< min	<= min	> max	>= max

4、嵌套查詢（帶有EXISTS謂詞）

不同形式的查詢間的替換
所有帶 in 謂詞、比較運算符、any 和 all 謂詞的子查詢都能用帶 exists 謂詞的子查詢等價替換。反過來不一定成立

-- 用於判斷一個查詢語句查詢的結果是否為空
if exists( SELECT * FROM SC WHERE Cno =  '21')  
	print '存在'
else
	print '不存在'
-- 查詢選修了2號課程的學生學號、姓名
-- 內連接
select s.sno, s.sname
from sc inner join student s on s.sno = sc.sno
where sc.cno = '02'
-- 嵌套查詢
select sno from sc where sc.cno = '02'
select s.sno, s.sname
from student s
where s.sno in (select sno from sc where sc.cno = '02')
-- 相關子查詢
select sno, sname from
student where exists(select * from sc where cno ='02' and sno = student.sno)
-- 查詢沒有選修2號課程的學生學號、姓名
-- 內連接
select s.sno, s.sname
from student s inner join sc on s.sno = sc.sno
where sc.cno <> '02'
-- 嵌套查詢
select sc.sno
from sc
where sc.cno = '02'

select s.sno, s.sname
from student s
where s.sno not in (select sc.sno
from sc
where sc.cno = '02')
select * from student inner join sc on student.sno = sc.sno
-- 相關子查詢 【正確】
select s.sno, s.sname
from student s
where not exists (select * from sc where sc.sno = s.sno and sc.cno = '02')
-- 查詢同時選修了2號和3號課程的學生學號，姓名
-- 集合的交
select s.sno, s.sname
from student s inner join sc on sc.sno = s.sno
where sc.cno = '02'
intersect
select s.sno, s.sname
from student s inner join sc on sc.sno = s.sno
where sc.cno = '03'
-- 相關子查詢
select s.sno, s.sname
from student s
where 
		exists(select * from sc where sc.sno = s.sno and sc.cno = '02')
	and 
		exists(select * from sc where sc.sno = s.sno and sc.cno = '03')
-- 查詢95001號學生沒有選修的課
-- 相關子查詢
select * from course c
where not exists (
select * from sc 
where sc.cno = c.cno and sc.sno = '95001')
-- 查詢選修了全部課程的學生資訊
-- 方式1
-- 不能存在沒有選修的課，也就是全部課程都選修了
select * from student s
where not exists (
select * from course c
where not exists 
(select * from sc 
where sc.cno = c.cno and sc.sno = s.sno)
)
-- 方式2
-- 1）查詢所有課程的數目
-- 2）通過學號進行分組，查詢學號
-- 3）所有課程的數目 = 該學生選修課程數目
-- 4）判斷學號是否在查詢結果裡面
select * from student s where s.sno in ( 
	select sno from sc group by sno 
	having count(distinct cno) = (select count(cno) from course)
)
-- 怎樣查找一門課，這門課95001選修了，95002沒有選修
select * from course c
where
		exists (select * from sc where sc.sno = '95001'and c.cno = sc.cno)
	and 
		not exists(select * from sc where sc.sno = '95002' and c.cno = sc.cno)
-- 查詢選修了95001選修的課，但是沒有選修其他人選的課的學生資訊
select * from student s where sno <> '95001' and not exists (
	select * from course c
	where 
			exists (select * from sc where sc.cno = c.cno and sc.sno = '95001')
		and	
			not exists(select * from sc where sc.cno = c.cno and sc.sno = s.sno)
)

3.5、數據更新

3.5.1、插入數據（insert）

兩種插入數據方式：

插入單個元組
插入子查詢結果

可以一次插入多個元組

DBMS在執行插入語句時會檢查所插元組是否破壞表上已定義的完整性規則

實體完整性
參照完整性
用戶定義的完整性
- 對於有 unique 約束的屬性列是否提供了非重複值
- 對於有 check 約束的屬性列所提供的屬性值是否滿足 check 條件

1、插入單個元組
語句格式：

insert into 表名[(屬性列1[,屬性列2]…)]
values (值1 [,值2]…)

功能：插入一條指定好值的元組

insert into student(sno, sname, ssex, borndate, sdept) 
	values ('95007', '王大明', 1, '2010-10-20', 'CS' )
-- 不寫屬性列，默認將該表中的所有屬性列都插入值
insert into student
	values ('95009', '王小明', 1, '1997-07-02', 'CS' )
-- 只插入部分屬性列
insert into student(sno, sname, ssex, sdept) 
	values ('95008', '王大明', 1, 'CS' )
INSERT INTO SC( Sno, Cno, examdate, grade ) 
	VALUES ('95007', '01', '2020-04-03 10:00:00',85)

注意：

可以只指定部分屬性列
- 未指定的屬性列上如果沒有默認約束的話，插入元組在這些屬性列取空值
- 未指定屬性列上如果有默認約束的話，插入元組在這些屬性列取默認值
插入元組時，要保證在定義為 not null 的屬性列上不能取空值。
屬性列的順序可以為任意順序，但要注意數據與屬性列的對應關係

2、插入子查詢
語句格式：

insert into 表名[(屬性列1[,屬性列2]…)]
	(子查詢)

功能：把子查詢結果中的若干條元組插入到指定的表中

-- 第一步：建表
CREATE TABLE savg(
	sno CHAR(5), 
	avggrade INT,
	constraint PK_savg_sno primary key(sno),
	constraint FK_savg_sno foreign key(sno) references student(sno)
) 
-- 第二步：插入查詢後的元組
-- 子查詢目標列必須與 into 子句中的屬性列匹配（個數和類型）
insert into savg(sno, avggrade)
	select sno, avg(grade) from sc  group by sno

3.5.2、修改數據（update）

語句格式：

update  <表名>
	set <列名>=<表達式> [,<列名>=<表達式>]…
	[where <條件>]；

<表達式>也可以是一個返回單個值的子查詢

功能：修改指定表中滿足 where 子句條件的元組，省略 where 子句表示修改表中所有元組。
三種修改方式：

修改某一個元組的值
修改多個元組的值
帶子查詢的修改語句

1、修改某一個元組的值

-- 修改一個屬性列的值
update student set borndate = '1981-10-10'	where  sno='95001'; 
-- 修改多個屬性列的值
update student set borndate = '1983-10-10', ssex = 1 where  sno='95002';

2、修改多個個元組的值

-- 將所有學生的成績開方乘10。(省略了where子句)
update sc set  grade = sqrt(grade) * 10;

3、帶子查詢的修改語句

-- 電腦系的學生成績上浮5%
update sc set sc.grade = sc.grade * 1.05
where sc.sno in (select sno from student s where s.sdept = '電腦系')
-- 內連接方式更新
update sc set sc.grade = sc.grade * 1.05
from sc inner join student s 
on s.sno = sc.sno 
where s.sdept = '電腦系'
-- 將95001號學生各門課程的成績改為該學生的平均成績
update sc set sc.grade = (select avg(grade) from sc where sc.sno = '95001') where sc.sno = '95001'

DBMS在執行修改語句時會檢查修改操作是否破壞表上已定義的完整性規則。

-- 將SC表中學生95001的選課記錄改為學生95020的選課記錄。
UPDATE sc SET sno = '95020' WHERE  sno='95001';  -- 【失敗】

3.5.3、刪除數據（delete）

語句格式：

delete from <表名>
	[where <條件>]；

功能：

刪除指定表中滿足 where 子句條件的元組
省略 where 子句表示要刪除表中的全部元組，表的定義仍在數據字典中。

刪除元組是也不能破壞資料庫的完整性

如果sc表存在與95001相關的記錄，系統對於上面的語句有兩種處理方式。

宣布上面語句執行失敗。
系統首先自動地刪除sc表中與95001相關的記錄，然後執行上面語句

-- 刪除學號為95001的學生記錄
delete from sc where sno = '95001'
delete from student where sno = '95001' -- 級聯

3.6、視圖

3.6.1、視圖概述

視圖的特點：

視圖是一個虛表
資料庫中只存放視圖的定義；
視圖對應的數據仍存放在原來的表中；
隨著表中數據的變化，視圖的數據隨之變化；
對視圖的查詢最終轉變成對基本表的查詢；
可以在視圖上建立視圖；

例如：某一用戶關心被選修的各門課的平均分

select cno,	avg(grade) from sc group by cno

如果建個表存儲上面資訊，產生數據冗餘，可以建立一個視圖來方便查詢

create view cgrade( cno, avgg )
	as
	   select cno,avg(grade)
	   from sc group by cno
select * from cgrade

3.6.2、定義視圖

1、創建視圖

語句格式：

create view <視圖名>[(<列名>[,<列名>]…)]
	as  <子查詢>
	[WITH  CHECK  OPTION];

2、刪除視圖

drop view <視圖名>

刪除視圖是從數據字典中刪除指定的視圖定義，並不刪除數據
SQLServer中，刪除視圖後，由該視圖導出的其他視圖定義仍在數據字典中，但已不能使用，必須顯式刪除
SQLServer中，刪除基表時，由該基表導出的所有視圖仍在數據字典中，但已不能使用，也必須顯式刪除

示例：

-- 建立電腦系學生的視圖，包括學號、姓名、年齡
create view v1(sno, sname, sage)
as select sno, sname, Datediff(year, borndate, GETDATE())  from student where sdept = '電腦系'

select * from v1
-- 建立電腦系選修了02號課程的學生的視圖，包括學號、姓名。(基於多表的視圖)
create view cs_v1(sno, sname) 
as
	select s.sno, s.sname from student s inner join sc on sc.sno = s.sno where sc.cno = '02' and s.sdept = '電腦系'
-- 建立電腦系選修了02號課程且成績在80分以上的學生的視圖，包括學號、姓名。(基於已有的視圖再創建視圖)
create view cs_v2(sno, sname) 
as 
	select distinct c.sno, c.sname from cs_v1 c inner join sc on sc.sno = c.sno where sc.grade > 80
-- 查詢視圖
select * from cs_v2
-- 刪除視圖
drop view cs_v1

注意：多表連接時選出了幾個同名列作為視圖的欄位時，要全部指定組成視圖的所有屬性列名

組成視圖的屬性列的名子可以全部省略或全部指定，不能部分指定

省略：視圖的屬性列的名子和子查詢中各個目標列的名字相同。
下面情況下全部指定組成視圖的所有屬性列名
- 多表連接時選出了幾個同名列作為視圖的欄位
- 子查詢中的某個目標列是集函數或列表達式，並且沒有定義別名。
- 需要在視圖中為某個列啟用新的更合適的名字

3.6.3、查詢視圖

和對基本表的查詢基本一致；

3.6.4、更新視圖

通過視圖更新某些元組時，這些元組要滿足視圖定義才能被更新（元組在視圖中）
通過視圖更新數據時，不能更改不在視圖定義中的的屬性
DBMS允許用戶對視圖進行更新，對視圖的更新最終會轉化為對基本表的更新。
並不是所有的視圖都可以更新

select * from cs_v1
-- 更新視圖的方式和更新基本表基本一樣
update cs_v1 set sname = '郭靖1' where sno = '10000'
select * from student where sno = '10000'
select * from cgrade
-- 【執行失敗】
update cgrade  set avgg = 90	
	WHERE  cno = '01'

3.6.5、視圖的作用

視圖對資料庫提供了一定程度的邏輯獨立性

Student(Sno,Sname,Ssex,Sage,Sdept)
	「垂直」地分成兩個基本表：
	SX(Sno,Sname,Sage)
	和
	SY(Sno,Ssex,Sdept)
select * from student;    語句失效。
SX ∞ SY

視圖能夠對機密數據提供安全保護
- 對不同用戶定義不同視圖，使每個用戶只能看到他有權看到的數據
- student表涉及到15個院系的學生，可以建立15個視圖，每個視圖只包含一個院系中的學生數據，對每個系主任只授予查詢和修改本系視圖的許可權

Tags: 資料庫系統概論(第5版)