05-數(shù)碼編程分區(qū)控制器-廣播系統(tǒng)介紹

1、數(shù)據(jù)庫技術與應用數(shù)據(jù)庫技術與應用 袁寶庫 SQL語言基礎語言基礎 o 基本概念 o SQL的數(shù)據(jù)類型 o 基本表的定義、刪除及修改 o 數(shù)據(jù)查詢功能 o 數(shù)據(jù)更改功能 o 建立與刪除索引 SQL語言的發(fā)展語言的發(fā)展 o 1986年10月由美國ANSI 公布最早的SQL 標準。 o 1989年4月，ISO提出了具備完整性特征的 SQL，稱為SQL-89 。 o 1992年11月，ISO又公布了新的SQL標準， 稱為SQL-92。 o 1999年頒布SQL-99，是SQL92的擴展。 SQL語言的特點語言的特點 o 一體化。 o 高度非過程化。 o 簡潔。 o 使用方式多樣。 SQL語言功能概述語

2、言功能概述 o 四部分：數(shù)據(jù)定義功能、數(shù)據(jù)控制功能、數(shù) 據(jù)查詢功能和數(shù)據(jù)操縱功能。 SQL功能功能命令動詞命令動詞 數(shù)據(jù)查詢數(shù)據(jù)查詢SELECT 數(shù)據(jù)定義數(shù)據(jù)定義CREATE、DROP、ALTER 數(shù)據(jù)操縱數(shù)據(jù)操縱INSERT、UPDATE、DELETE 數(shù)據(jù)控制數(shù)據(jù)控制GRANT、REVOKE SQL Server 2008 的數(shù)據(jù)類型的數(shù)據(jù)類型 o 數(shù)字數(shù)據(jù)類型 o 字符數(shù)據(jù)類型 o 二進制數(shù)據(jù)類型 o 日期和時間數(shù)據(jù)類型 o 貨幣型 o 特殊數(shù)據(jù)類型 數(shù)值型數(shù)值型 o 準確型 n 整數(shù) Bigint: 8字節(jié)， Int：4字節(jié) Smallint：2字節(jié)， Tinyint：1字節(jié) Bit：

3、1位，存儲1或0 n 小數(shù) Numeric（p,q）或Decimal（p,q）， n 近似型 Float：8字節(jié) Real：4字節(jié) 字符串型字符串型 o 普通編碼字符串類型 o 統(tǒng)一字符編碼字符串類型 普通編碼字符串類型普通編碼字符串類型 o Char（n）:定長存儲，n=8000 o Varchar（n）：不定長存儲（按實際長度 存儲），長度最大不超過n , n=8000 注：n 為字符個數(shù) o Text：存儲大于8000字節(jié)的文本 統(tǒng)一字符編碼字符串類型統(tǒng)一字符編碼字符串類型 o nchar（n）:定長存儲，n=4000 o nvarchar（n）：不定長存儲，長度最大不 超過n , n=

4、4000 o ntext：存儲大于8000字節(jié)的文本 特點：每個字符占兩個字節(jié) 二進制字數(shù)據(jù)二進制字數(shù)據(jù) o Binary(n)：固定長度，n = 8000。 o Varbinary(n)：可變長度，n 8000 。 注：n為二進制數(shù)據(jù)的字節(jié)數(shù) o image：大容量、可變長二進制字符數(shù)據(jù)， 可用于存儲文件。 日期時間型日期時間型 o Datetime：8字節(jié)，年月日時分秒毫秒 （例：2001/08/03 10:30:00.000 ） o SmallDateTime：4字節(jié)，年月日時分 （例： 2001/08/03 10:30:00 ） o 日期、時間的輸入格式 貨幣類型貨幣類型 o Mone

5、y： 8 個字節(jié)，精確到貨幣單位的萬 分之一。 o Smallmoney： 4 個字節(jié)，精確到貨幣單 位的萬分之一。 o 限制到小數(shù)點后 4 位。 o 可以帶有適當?shù)呢泿欧枴＠纾?00 英鎊 可表示為 100。 特殊數(shù)據(jù)類型特殊數(shù)據(jù)類型 o timestamp、uniqueidentifier和xml等。 基本表的定義、刪除及修改基本表的定義、刪除及修改 o 基本表的定義與刪除 o 修改表結構 基本表的定義與刪除基本表的定義與刪除 o 定義基本表 使用SQL語言中的CREATE TABLE語句實現(xiàn)， 其一般格式為： CREATE TABLE （ 列級完整性約束定義 , 列級完整性約束定義

6、, 表級完整性約束定義 ） 在列級完整性約束定在列級完整性約束定 義處可以定義的約束義處可以定義的約束 o NOT NULL：限制列取值非空。 o DEFAULT：給定列的默認值。 o UNIQUE：限制列取值不重。 o CHECK：限制列的取值范圍。 o PRIMARY KEY：指定本列為主碼。 o FOREIGN KEY：定義本列為引用其他表的外 碼。使用形式為： FOREIGN KEY（） REFERENCES （） 幾點說明幾點說明 o NOT NULL和DEFAULT只能是列級完整性約束； o 其他約束均可在表級完整性約束處定義。 o 注意以下幾點： n 第一，如果CHECK約束是定

7、義多列之間的取值約束， 則只能在表級完整性約束處定義； n 第二，如果表的主碼由多個列組成，則也只能在表級 完整性約束處定義，并將主碼列用括號括起來，即： PRIMARY KEY（列1，列2 ）； n 第三，如果在表級完整性約束處定義外碼，則 “FOREIGN KEY ()”部分不能省。 約束定義約束定義 o 列取值非空約束 n NOT NULL 例：sname char(10) NOT NULL 約束定義約束定義 o 表主碼約束 o 在定義列時定義主碼（僅用于單列主碼） 列定義 PRIMARY KEY 例： SNO char(7) PRIMARY KEY o 在定義完列時定義主碼（用于單列或

8、多列主碼） PRIMARY KEY （） 例： PRIMARY KEY(SNO) PRIMARY KEY(SNO，CNO) 約束定義約束定義 o 外碼引用約束 o 指明本表外碼列引用的表及表中的主碼列。 FOREIGN KEY （） REFERENCES （） 例： FOREIGN KEY （sno） REFERENCES 學生表（sno） 約束定義約束定義 o 默認值約束 格式：DEFAULT 默認值 例：定義系的默認值為“計算機系”。 DEFAULT 計算機系 約束定義約束定義 o CHECK約束 格式：CHECK (約束表達式) 例：定義成績大于等于0。 CHECK ( grade =

9、0 ) 約束定義約束定義 o UNIQUE約束 o 在列級約束定義（僅用于單列約束） 列定義 UNIQUE 例： SNAME char(7) UNIQUE o 在表級約束定義（用于單列或多列組合約束） UNIQUE （） 例： UNIQUE (SNO，CNO) 創(chuàng)建學生表 CREATE TABLE Student ( Sno char ( 7 ) PRIMARY KEY, Sname char ( 10 ) NOT NULL, Ssex char (2) CHECK (Ssex = 男 OR Ssex = 女), Sage tinyint CHECK (Sage = 15 AND Sage 0

10、), Semester tinyint CHECK (Semester 0), Period int CHECK (Period 0), PRIMARY KEY(Cno) ) 創(chuàng)建SC表 CREATE TABLE SC ( Sno char(7) NOT NULL, Cno char(10) NOT NULL, Grade tinyint, CHECK (Grade = 0 and Grade = 100), PRIMARY KEY ( Sno, Cno ), FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno )

11、 REFERENCES Course ( Cno ) ) 刪除表刪除表 o 當確信不再需要某個表時，可以將其刪除 o 刪除表時會將與表有關的所有對象一起刪掉， 包括表中的數(shù)據(jù)。 o 刪除表的語句格式為： DROP TABLE , 例：刪除test表的語句為： DROP TABLE test 修改表結構修改表結構 o 在定義完表之后，如果需求有變化，比如 添加列、刪除列或修改列定義，可以使用 ALTER TABLE語句實現(xiàn)。 o ALTER TABLE語句可以對表添加列、刪除 列、修改列的定義、定義主碼、外碼，也 可以添加和刪除約束。 修改表結構語法修改表結構語法 ALTER TABLE ALT

12、ER COLUMN | ADD COLUMN | DROP COLUMN | ADD PRIMARY KEY（ ， n ） | ADD FOREIGN KEY（ ） REFERNECES 表名（ ） 示例示例 o 為SC表添加“修課類別”列，此列的定義 為：XKLB char(4) ALTER TABLE SC ADD XKLB char(4) NULL o 將新添加的XKLB的類型改為 char(6)。 ALTER TABLE SC ALTER COLUMN XKLB char(6) 示例示例 o 刪除Course表的Period列。 ALTER TABLE Course DROP COLU

13、MN Period 數(shù)據(jù)查詢功能數(shù)據(jù)查詢功能 o 查詢語句的基本結構 o 簡單查詢 o 多表連接查詢 o 子查詢 查詢語句基本格式查詢語句基本格式 SELECT -需要哪些列 FROM -來自于哪些表 WHERE -根據(jù)什么條件 GROUP BY HAVING ORDER BY 簡單查詢簡單查詢-選擇表中若干列 o 查詢指定的列 n 查詢表中用戶感興趣的部分屬性列。 n 例5：查詢?nèi)w學生的學號與姓名。 n 例6：查詢?nèi)w學生的姓名、學號和所在系。 SELECT Sno，Sname FROM Student SELECT Sname，Sno，Sdept FROM Student 簡單查詢簡單查

14、詢-選擇表中若干列 o 查詢?nèi)康牧?n 例7查詢?nèi)w學生的記錄 n 等價于： SELECT Sno，Sname，Ssex, Sage, Sdept FROM Student SELECT * FROM Student 簡單查詢簡單查詢-選擇表中若干列 o 查詢經(jīng)過計算的列 n 例8查詢?nèi)w學生的姓名及其出生年份。 n 例9查詢?nèi)w學生的姓名和出生年份所在系， 并在出生年份列前加入一個列，此列的每行數(shù) 據(jù)均為出生年份常量值。 SELECT Sname，2013 - Sage FROM Student SELECT Sname，出生年份, 2012-Sage FROM Student 簡單查詢簡

15、單查詢-選擇表中若干列 o 改變列標題 n 語法： 列名 | 表達式 AS 列標題 n 或： 列標題 列名 | 表達式 n 例： SELECT Sname 姓名，Year of Birth 出生年份, 2006 - Sage 年份， FROM Student 簡單查詢簡單查詢-選擇表中若干元組 o 例10在修課表中查詢有哪些學生修了課程， 要求列出學生的學號。 o 消除取值相同的記錄 n 結果中有重復的行。 n 用DISTINCT關鍵字可以去掉結果中的重復行。 n DISTINCT關鍵字放在SELECT詞的后邊、目標列 名序列的前邊。 SELECT Sno FROM SC SELECT DIS

16、TINCT Sno FROM SC 簡單查詢簡單查詢-選擇表中若干元組 o 查詢滿足條件的元組 查詢條件查詢條件謂謂 詞詞 比較運算符 =, , =, , =, （或!=） NOT+比較運算符 確定范圍BETWEENAND, NOT BETWEENAND 確定集合IN, NOT IN 字符匹配LIKE, NOT LIKE 空值IS NULL, IS NOT NULL 邏輯謂詞AND, OR 簡單查詢簡單查詢-選擇表中若干元組 o 比較 n 例11查詢計算機系全體學生的姓名。 n 例12查詢年齡在20歲以下的學生的姓名及年齡。 n 例13查詢考試成績有不及格的學生的學號 SELECT Sname

17、 FROM Student WHERE Sdept = 計算機系 SELECT Sname, Sage FROM Student WHERE Sage 20 SELECT DISTINCT Sno FROM SC WHERE Grade 60 簡單查詢簡單查詢-選擇表中若干元組 o 確定范圍 n 用BETWEENAND和NOT BETWEENAND n 是邏輯運算符，可以用來查找屬性值在或不在指定 范圍內(nèi)的元組，其中BETWEEN后邊指定范圍的下 限，AND后邊指定范圍的上限。 n BETWEENAND的格式為： 列名|表達式 NOT BETWEEN 下限值 AND 上限值 n 如果列或表達式

18、的值在不在下限值和上限值范圍內(nèi)， 則結果為True，表明此記錄符合查詢條件。 簡單查詢簡單查詢-選擇表中若干元組 o 例14查詢年齡在2023歲之間的學生的 姓名、所在系和年齡。 o 例15查詢年齡不在2023之間的學生姓 名、所在系和年齡。 SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23 SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23 簡單查詢簡單查詢-選擇表中若干元組 o 使用IN運算符 n 用來查找屬性值屬

19、于指定集合的元組。 n 格式為： 列名 NOT IN （常量1, 常量2, 常量n） n 當列中的值與IN中的某個常量值相等時，則結 果為True，表明此記錄為符合查詢條件的記錄； n NOT IN：當列中的值與某個常量值相同時，則 結果為False，表明此記錄為不符合查詢條件的 記錄 簡單查詢簡單查詢-選擇表中若干元組 o 例16查詢信息系、數(shù)學系和計算機系學生 的姓名和性別。 o 例17查詢既不是信息系、數(shù)學系，也不是 計算機系學生的姓名和性別。 SELECT Sname, Ssex FROM Student WHERE Sdept IN (信息系, 數(shù)學系, 計算機系) SELECT S

20、name, Ssex FROM Student WHERE Sdept NOT IN (信息系, 數(shù)學系, 計算機系) 簡單查詢簡單查詢-選擇表中若干元組 o 字符匹配 n 使用LIKE運算符 n 一般形式為： 列名 NOT LIKE n 匹配串中可包含如下四種通配符： o _：匹配任意一個字符； o %：匹配0個或多個字符； o ：匹配 中的任意一個字符； o ：不匹配 中的任意一個字符 簡單查詢簡單查詢-選擇表中若干元組 o 例18查詢姓張的學生的詳細信息。 o 例19查詢學生表中姓張、李和劉的學 生的情況。 o 例20查詢名字中第2個字為小或大的學生的 姓名和學號 SELECT * FR

21、OM Student WHERE Sname LIKE 張% SELECT * FROM Student WHERE Sname LIKE 張李劉% SELECT Sname, Sno FROM Student WHERE Sname LIKE _小大% 簡單查詢簡單查詢-選擇表中若干元組 o 例21查詢所有不姓“劉”的學生。 o 例22查詢學號的最后一位不是2、3、5的學 生情況。 SELECT Sname FROM Student WHERE Sname NOT LIKE 劉% SELECT * FROM Student WHERE Sno LIKE %235 簡單查詢簡單查詢-選擇表中若

22、干元組 o 涉及空值的查詢 n 空值（NULL）在數(shù)據(jù)庫中表示不確定的值。 n 例如，學生選修課程后還沒有考試時，這些學生有 選課記錄，但沒有考試成績，因此考試成績?yōu)榭罩怠?n 判斷某個值是否為NULL值，不能使用普通的比較運 算符。 n 判斷取值為空的語句格式為： 列名 IS NULL n 判斷取值不為空的語句格式為： 列名 IS NOT NULL 簡單查詢簡單查詢-選擇表中若干元組 o 例23查詢無考試成績的學生的學號和相應的 課程號。 o 例24查詢所有有考試成績的學生的學號和課 程號。 SELECT Sno, Cno FROM SC WHERE Grade IS NULL SELECT

23、 Sno, Cno FROM SC WHERE Grade IS NOT NULL 簡單查詢簡單查詢-選擇表中若干元組 o 多重條件查詢 n 在WHERE子句中可以使用邏輯運算符AND和OR來 組成多條件查詢。 n 用AND連接的條件表示必須全部滿足所有的條件的 結果才為True； n 用OR連接的條件表示只要滿足其中一個條件結果即 為True。 n 例25查詢計算機系年齡在20歲以下的學生姓名。 SELECT Sname FROM Student WHERE Sdept = CS AND Sage 20 簡單查詢簡單查詢-對查詢結果進行排序 o 可對查詢結果進行排序。 o 排序子句為： OR

24、DER BY ASC | DESC , o 說明：按進行升序（ASC）或降序 （DESC）排序。 o 例26將學生按年齡的升序排序。 SELECT * FROM Student ORDER BY Sage 簡單查詢簡單查詢-對查詢結果進行排序 o 例27查詢選修了c02號課程的學生的學號 及其成績，查詢結果按成績降序排列。 o 例28查詢?nèi)w學生的信息，查詢結果按所在 系的系名升序排列，同一系的學生按年齡降序 排列。 SELECT Sno, Grade FROM SC WHERE Cno=c02 ORDER BY Grade DESC SELECT * FROM Student ORDER B

25、Y Sdept, Sage DESC 思考題思考題 o 查詢所有年齡在20歲以下姓劉的學生姓名及其 年齡。 SELECT SNAME，SAGE FROM STUDENT WHERE SAGE20 AND SNAME LIKE 劉% 簡單查詢簡單查詢-使用計算函數(shù)匯總數(shù)據(jù) o SQL提供的計算函數(shù)有： n COUNT（ * ）：統(tǒng)計表中元組個數(shù)； n COUNT（DISTINCT ）：統(tǒng)計本列列 值個數(shù)； n SUM（DISTINCT ）：計算列值總和； n AVG（DISTINCT ）：計算列值平均值； n MAX（DISTINCT ）：求列值最大值； n MIN（DISTINCT ）：求列值

26、最小值。 o 上述函數(shù)中除COUNT（*）外，其他函數(shù)在計 算過程中均忽略NULL值。 簡單查詢簡單查詢-使用計算函數(shù)匯總數(shù)據(jù) o 例29統(tǒng)計學生總?cè)藬?shù)。 o 例30統(tǒng)計選修了課程的學生的人數(shù)。 o 例31 計算9512101號學生的考試總成績之和。 SELECT COUNT(*) FROM Student SELECT COUNT (DISTINCT Sno) FROM SC SELECT SUM(Grade) FROM SC WHERE Sno = 9512101 簡單查詢簡單查詢-使用計算函數(shù)匯總數(shù)據(jù) o 例32計算C01號課程學生的考試平均成績。 o 例33查詢選修了C01號課程的學生

27、的最高 分和最低分。 o 注意：計算函數(shù)不能出現(xiàn)在WHERE子句中 SELECT AVG(Grade) FROM SC WHERE Cno=C01 SELECT MAX(Grade) , MIN(Grade) FROM SC WHERE Cno=C01 簡單查詢簡單查詢-對查詢結果進行分組計算 o 作用：可以控制計算的級別：對全表還是對一 組。 o 目的：細化計算函數(shù)的作用對象。 o 分組語句的一般形式： GROUP BY HAVING 簡單查詢簡單查詢-使用計算函數(shù)匯總數(shù)據(jù) o 使用GROUP BY n 例34統(tǒng)計每門課程的選課人數(shù)，列出課程 號和人數(shù)。 n 對查詢結果按Cno的值分組，所有

28、具有相同 Cno值的元組為一組，然后再對每一組使用 COUNT計算，求得每組的學生人數(shù)。 SELECTCno as 課程號, COUNT(Sno) as 選課人數(shù) FROM SC GROUP BY Cno 簡單查詢簡單查詢-使用計算函數(shù)匯總數(shù)據(jù) SnoCnoGrade 951201C0180 951201C0278 951202C0190 952103C0288 952103C0185 952103C0391 952103C0474 SnoCnoGrade 951201C0180 951202C0190 952103C0185 951201C0278 952103C0288 952103C03

29、91 952103C0474 簡單查詢簡單查詢-使用計算函數(shù)匯總數(shù)據(jù) Cno Count(Sno) C013 C022 C031 C041 簡單查詢簡單查詢-使用計算函數(shù)匯總數(shù)據(jù) o 使用GROUP BY n 例35查詢每名學生的選課門數(shù)和平均成績 SELECTSno as 學號, COUNT(*) as 選課門數(shù), AVG(Grade) as 平均成績 FROMSC GROUP BY Sno 簡單查詢簡單查詢-使用計算函數(shù)匯總數(shù)據(jù) o 使用HAVING n HAVING用于對分組自身進行限制，它有點 象WHERE子句，但它用于組而不是對單個 記錄。 n 例36查詢修了3門以上課程的學生的學號

30、 SELECTSno FROMSC GROUP BYSno HAVINGCOUNT(*) 3 簡單查詢簡單查詢-使用計算函數(shù)匯總數(shù)據(jù) o 使用HAVING n 例37查詢修課門數(shù)等于或大于4門的學生 的平均成績和選課門數(shù) SELECTSno, AVG(Grade) 平均成績, COUNT(*) 修課門數(shù) FROMSC GROUP BYSno HAVINGCOUNT(*) = 4 多表連接查詢多表連接查詢 o 若一個查詢同時涉及兩個或兩個以上的表， 則稱之為連接查詢。 o 連接查詢是關系數(shù)據(jù)庫中最主要的查詢 o 連接查詢包括內(nèi)連接、外連接和交叉連接等 多表連接查詢多表連接查詢 o 連接基礎知識

31、n 連接查詢中用于連接兩個表的條件稱為連接條 件或連接謂詞。 n 一般格式為： 必須是可比的必須是可比的 多表連接查詢多表連接查詢 o 內(nèi)連接 n SQL-92 內(nèi)連接語法如下： SELECT FROM 表名 INNER JOIN 被連接表 ON 連接條件 多表連接查詢多表連接查詢 o 執(zhí)行連接操作的過程： n 首先取表1中的第1個元組，然后從頭開始掃描 表2，逐一查找滿足連接條件的元組， n 找到后就將表1中的第1個元組與該元組拼接起 來，形成結果表中的一個元組。 n 表2全部查找完畢后，再取表1中的第2個元組， 然后再從頭開始掃描表2， n 重復這個過程，直到表1中的全部元組都處理完 畢為

32、止。 多表連接查詢多表連接查詢 o 例38.查詢每個學生的基本信息及其選課情況 o 例39.去掉上例中重復的列 SELECT * FROM Student INNER JOIN SC ON Student.Sno = SC.Sno SELECT Student.Sno, Sname, Ssex, Sage, Cno, Grade ,XKLB FROMStudentJOIN SC ONStudent.Sno = SC.Sno 多表連接查詢多表連接查詢 o 例40.查詢計算機系學生的修課情況，要求 列出學生的名字、所修課的課程號和成績。 SELECT Sname, Cno, Grade FROMS

33、tudent JOIN SC ONStudent.Sno = SC.Sno WHERESdept = 計算機系 多表連接查詢多表連接查詢 o 例41. 查詢信息系修了VB課程的學生的修 課成績，要求列出學生姓名、課程名和成績。 SELECTSname, Cname, Grade FROMStudent s JOIN SC ONs.Sno = SC. Sno JOINCourse c ON c.Cno = SC.Cno WHERESdept = 信息系 ANDCname = VB 多表連接查詢多表連接查詢 o 自連接 n 為特殊的內(nèi)連接 n 相互連接的表物理上為同一張表。 n 必須為兩個表取別名

34、，使之在邏輯上成為兩個 表。 多表連接查詢多表連接查詢 o 例43. 查詢與劉晨在同一個系學習的學生的 姓名和所在的系。 SELECTS2.Sname, S2.Sdept FROMStudent S1 JOIN Student S2 ONS1.Sdept = S2.Sdept WHERES1.Sname = 劉晨 ANDS2.Sname != 劉晨 多表連接查詢多表連接查詢 o 外連接 n 只限制一張表中的數(shù)據(jù)必須滿足連接條件，而 另一張表中數(shù)據(jù)可以不滿足連接條件。 n ANSI方式的外連接的語法格式為： FROM 表1 LEFT | RIGHT OUTER JOIN 表2 ON 多表連接查詢

35、多表連接查詢 o 外連接 n theta方式的外連接的語法格式為： n 左外連接： FROM 表1, 表2 WHERE 表1.列名(+) 表2.列名 n 右外連接： FROM 表1, 表2 WHERE 表1.列名 表2.列名(+) 多表連接查詢多表連接查詢 o 例44. 查詢學生的修課情況，包括修了課程 的學生和沒有修課的學生。 SELECTStudent.Sno, Sname, Cno, Grade FROMStudent LEFT OUTER JOIN SC ONStudent.Sno = SC.Sno 子查詢子查詢 o 在SQL語言中，一個SELECTFROM WHERE語句稱為一個查詢

36、塊。 o 子查詢是一個 SELECT 查詢，它嵌套在 SELECT、INSERT、UPDATE、DELETE 語句的 WHERE 或 HAVING 子句內(nèi)，或其 它子查詢中 o 子查詢的 SELECT 查詢總是使用圓括號括 起來。 子查詢子查詢-使用子查詢進行基于集合的測試使用子查詢進行基于集合的測試 o 使用子查詢進行基于集合的測試的語句的一 般格式為： n 列名 NOT IN (子查詢) 子查詢子查詢 o 例45. 查詢與劉晨在同一個系的學生。 SELECTSno, Sname, Sdept FROMStudent WHERE Sdept IN ( SELECT Sdept FROM St

37、udent WHERE Sname = 劉晨 ) AND Sname != 劉晨 子查詢子查詢 o 例46. 查詢成績?yōu)榇笥?0分的學生的學 號、姓名。 SELECTSno, Sname FROMStudent WHERESno IN ( SELECT Sno FROM SC WHERE Grade 90 ) 子查詢子查詢 o 例47. 查詢選修了“數(shù)據(jù)庫”課程的學生 的學號、姓名。 SELECTSno, Sname FROMStudent WHERESno IN ( SELECTSno FROMSC WHERECno IN (SELECT Cno FROM Course WHERE Cnam

38、e = 數(shù)據(jù)庫) 子查詢子查詢-使用子查詢進行比較測試使用子查詢進行比較測試 o 帶比較運算符的子查詢指父查詢與子查詢之 間用比較運算符連接， o 當用戶能確切知道內(nèi)層查詢返回的是單值時， 可用、=、=、運算符。 子查詢子查詢 o 例48. 查詢修了c02課程且成績高于此 課程的平均成績的學生的學號和成績。 SELECTSno , Grade FROMSC WHERECno = c02 AND Grade (SELECT AVG(Grade) FROM SC WHERECno = c02) 子查詢子查詢-使用子查詢進行存在性測試使用子查詢進行存在性測試 o 一般使用EXISTS謂詞。 o 帶E

39、XISTS謂詞的子查詢不返回查詢的數(shù)據(jù)， 只產(chǎn)生邏輯真值（有數(shù)據(jù)）和假值（沒有數(shù) 據(jù)）。 子查詢子查詢 o 例49.查詢選修了c01號課程的學生姓 名。 SELECTSname FROMStudent WHEREEXISTS (SELECT* FROMSC WHERESno = Student.Sno ANDCno = c01) 子查詢子查詢-使用子查詢進行存在性測試使用子查詢進行存在性測試 o 注1：處理過程為：先外后內(nèi)；由外層的值 決定內(nèi)層的結果；內(nèi)層執(zhí)行次數(shù)由外層結果 數(shù)決定。 o 注2: 由于EXISTS的子查詢只能返回真或假 值，因此在這里給出列名無意義。所以在有 EXISTS的子查

40、詢中，其目標列表達式通常 都用*。 子查詢子查詢-使用子查詢進行存在性測試使用子查詢進行存在性測試 o 例49的處理過程 n 找外層表Student表的第一行，根據(jù)其Sno值處 理內(nèi)層查詢 n 由外層的值與內(nèi)層的結果比較，由此決定外層 條件的真、假 n 順序處理外層表Student表中的第2、3、行。 子查詢子查詢 o 例50.查詢沒有選修c01號課程的學生 姓名和所在系。 SELECTSname, Sdept FROMStudent WHERENOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = c01) 子查詢子查詢

41、o 查詢選修了全部課程的學生姓名 SELECT sname FROMstudent WHERENOT EXISTS (SELECT* FROMcourse WHERENOT EXISTS (SELECT* FROMsc WHEREsno=student.sno AND cno= o) 思考題思考題 o 查詢選課門數(shù)大于10門的學生的平均成績和 選課門數(shù)。 o 查詢沒有選修課程2的學生姓名。 思考題思考題 o 查詢選課門數(shù)大于10門的學生的平均成績和 選課門數(shù)。 SELECTSNO, AVG(GRADE) 平均成績, COUNT(*) 修課門數(shù) FROMSC GROUP BYSNO HAVING

42、COUNT(*) 10 思考題思考題 o 查詢沒有選修課程2的學生姓名。 SELECTSNAME FROMSTUDENT WHERENOT EXISTS (SELECT* FROMSC WHERESNO = STUDENT.SNO AND CNO = 課程2) 數(shù)據(jù)更改功能數(shù)據(jù)更改功能 o 插入數(shù)據(jù) o 更新數(shù)據(jù) o 刪除數(shù)據(jù) 插入數(shù)據(jù)插入數(shù)據(jù) o 插入單行記錄的INSERT語句的格式為： INSERT INTO () VALUES （值表） o 功能：新增一個符合表結構的數(shù)據(jù)行，將值表數(shù)據(jù) 按表中列定義順序或列名表順序賦給對應列名 n 值列表中的值與列名表中的列按位置順序?qū)?們的數(shù)據(jù)類

43、型必須一致。 n 如果后邊沒有指明列名，則新插入記錄的值 的順序必須與表中列的定義順序一致，且每一個列 均有值（可以為空）。 插入數(shù)據(jù)插入數(shù)據(jù) o 例51將新生記錄（9521105，陳冬，男，信 息系，18歲）插入到Student表中。 o 例52在SC表中插入一新記錄，成績暫缺。 INSERT INTO Student VALUES (9521105, 陳冬, 男, 18, 信息系) INSERT INTO SC(Sno, Cno, XKLB) VALUES(9521105, c01, 必修) 實際插入的值為： (9521105, c01 ，NULL ，必修) 更新數(shù)據(jù)更新數(shù)據(jù) o 用UPD

44、ATE語句實現(xiàn)。 o 格式： UPDATE SET , n WHERE 更新更新數(shù)據(jù)數(shù)據(jù)-無條件更新 o 例53. 將所有學生的年齡加1。 UPDATEStudent SETSage = Sage + 1 更新更新數(shù)據(jù)數(shù)據(jù)-有有條件更新 o 基于本表條件的更新 o 例54. 將9512101學生的年齡改為21歲 UPDATEStudent SETSage = 21 WHERESno = 9512101 更新更新數(shù)據(jù)數(shù)據(jù)-有有條件更新 o 基于其他表條件的更新 o 例55：將計算機系全體學生的成績加5分。 n 用子查詢實現(xiàn) UPDATESC SETGrade = Grade + 5 WHERES

45、no IN (SELECTSno FROMStudent WHERESdept = 計算機系 ) 更新更新數(shù)據(jù)數(shù)據(jù)-有有條件更新 o 基于其他表條件的更新 o 例55：將計算機系全體學生的成績加5分。 n 用多表連接實現(xiàn) UPDATESC SETGrade = Grade + 5 FROMSC JOIN Student ONSC.Sno = Student.Sno WHERESdept = 計算機系 刪除數(shù)據(jù)刪除數(shù)據(jù) o 用DELETE語句實現(xiàn) o 格式： DELETE FROM WHERE 刪除刪除數(shù)據(jù)數(shù)據(jù)-無條件刪除 o 例56. 刪除所有學生的選課記錄。 DELETE FROMSC 刪除刪除數(shù)據(jù)數(shù)據(jù)-有有條件刪除 o 基于本表條件的刪除。 o 例57刪除所有不及格學生的修課記錄。 DELETEFROM SC WHERE Grade 60 刪除刪除數(shù)據(jù)數(shù)據(jù)