數(shù)據(jù)庫(kù)原理第4章sql語(yǔ)言基礎(chǔ)

1、數(shù)據(jù)庫(kù)原理與應(yīng)用高等院校計(jì)算機(jī)教材系列第4章 SQL語(yǔ)言基礎(chǔ)4.1 基本概念4.2 SQL的數(shù)據(jù)類型 4.3 基本表的定義、刪除及修改 4.4 數(shù)據(jù)查詢功能 4.5 數(shù)據(jù)更改功能 4.6 建立與刪除索引4.1 基本概念4.1.1 SQL語(yǔ)言的發(fā)展 4.1.2 SQL語(yǔ)言的特點(diǎn)4.1.3 SQL語(yǔ)言功能概述 4.1.1 SQL語(yǔ)言的發(fā)展1986年10月由美國(guó)ANSI 公布最早的SQL標(biāo)準(zhǔn)。 1989年4月，ISO提出了具備完整性特征的SQL，稱為SQL-89 。1992年11月，ISO又公布了新的SQL標(biāo)準(zhǔn)，稱為SQL-92（以上均為關(guān)系形式）。 1999年頒布SQL-99，是SQL92的擴(kuò)展。

2、4.1.2 SQL語(yǔ)言的特點(diǎn)1. 一體化。 2. 高度非過(guò)程化。 3. 簡(jiǎn)潔。 4. 使用方式多樣。4.1.3 SQL語(yǔ)言功能概述SQL功能命令動(dòng)詞數(shù)據(jù)查詢SELECT數(shù)據(jù)定義CREATE、DROP、ALTER數(shù)據(jù)操縱INSERT、UPDATE、DELETE數(shù)據(jù)控制GRANT、REVOKE四部分：數(shù)據(jù)定義功能、數(shù)據(jù)控制功能、數(shù)據(jù)查詢功能和數(shù)據(jù)操縱功能。 4.2 SQL的數(shù)據(jù)類型數(shù)值型 字符串型日期時(shí)間型貨幣型數(shù)值型準(zhǔn)確型 整數(shù)Bigint: 8字節(jié)， Int：4字節(jié)Smallint：2字節(jié)， Tinyint：1字節(jié) Bit：1位，存儲(chǔ)1或0小數(shù)Numeric（p,q）或Decimal（p,q）

3、， 其中：p為數(shù)字位長(zhǎng)度，q：小數(shù)位長(zhǎng)度。近似型 Float：8字節(jié) Real：4字節(jié)字符串型普通編碼字符串類型統(tǒng)一字符編碼字符串類型二進(jìn)制字符串類型普通編碼字符串類型Char（n）:定長(zhǎng)存儲(chǔ)，n=8000 Varchar（n）：不定長(zhǎng)存儲(chǔ)（按實(shí)際長(zhǎng)度存儲(chǔ)），長(zhǎng)度最大不超過(guò)n , n=8000 注：n 為字符個(gè)數(shù)Text：存儲(chǔ)大于8000字節(jié)的文本統(tǒng)一字符編碼字符串類型nchar（n）:定長(zhǎng)存儲(chǔ)，n=4000 nvarchar（n）：不定長(zhǎng)存儲(chǔ)，長(zhǎng)度最大不超過(guò)n , n=4000 ntext：存儲(chǔ)大于8000字節(jié)的文本特點(diǎn)：每個(gè)字符占兩個(gè)字節(jié)二進(jìn)制字符串類型Binary(n)：固定長(zhǎng)度，n =

4、 8000。Varbinary(n)：可變長(zhǎng)度，n 8000 。注：n為二進(jìn)制數(shù)據(jù)的字節(jié)數(shù)image：大容量、可變長(zhǎng)二進(jìn)制字符數(shù)據(jù)，可用于存儲(chǔ)文件。日期時(shí)間型Datetime：8字節(jié)，年月日時(shí)分秒毫秒（例：2001/08/03 10:30:00.000 ） SmallDateTime：4字節(jié)，年月日時(shí)分 （例： 2001/08/03 10:30:00 ）日期、時(shí)間的輸入格式貨幣類型Money： 8 個(gè)字節(jié)，精確到貨幣單位的千分之十。Smallmoney： 4 個(gè)字節(jié)，精確到貨幣單位的千分之十。限制到小數(shù)點(diǎn)后 4 位?？梢詭в羞m當(dāng)?shù)呢泿欧?hào)。例如，100 英鎊可表示為 100。4.3 基本表的定

5、義、刪除及修改 4.3.1 基本表的定義與刪除 4.3.2 修改表結(jié)構(gòu) 4.3.1 基本表的定義與刪除1定義基本表 使用SQL語(yǔ)言中的CREATE TABLE語(yǔ)句實(shí)現(xiàn)，其一般格式為：CREATE TABLE （ 列級(jí)完整性約束定義, 列級(jí)完整性約束定義 , 表級(jí)完整性約束定義 ） 在列級(jí)完整性約束定義處可以定義的約束NOT NULL：限制列取值非空。DEFAULT：給定列的默認(rèn)值。UNIQUE：限制列取值不重。CHECK：限制列的取值范圍。PRIMARY KEY：指定本列為主碼。FOREIGN KEY：定義本列為引用其他表的外碼。使用形式為：FOREIGN KEY（） REFERENCES （

6、） 幾點(diǎn)說(shuō)明NOT NULL和DEFAULT只能是列級(jí)完整性約束；其他約束均可在表級(jí)完整性約束處定義。注意以下幾點(diǎn)：第一，如果CHECK約束是定義多列之間的取值約束，則只能在表級(jí)完整性約束處定義；第二，如果表的主碼由多個(gè)列組成，則也只能在表級(jí)完整性約束處定義，并將主碼列用括號(hào)括起來(lái)，即：PRIMARY KEY（列1，列2 ）；第三，如果在表級(jí)完整性約束處定義外碼，則“FOREIGN KEY ()”部分不能省。約束定義 列取值非空約束 NOT NULL例：sname char(10) NOT NULL約束定義（續(xù)） 表主碼約束在定義列時(shí)定義主碼（僅用于單列主碼）列定義 PRIMARY KEY例：

7、 SNO char(7) PRIMARY KEY在定義完列時(shí)定義主碼（用于單列或多列主碼）PRIMARY KEY （）例： PRIMARY KEY(SNO) PRIMARY KEY(SNO，CNO)約束定義（續(xù)）外碼引用約束指明本表外碼列引用的表及表中的主碼列。 FOREIGN KEY （）REFERENCES （）例： FOREIGN KEY （sno） REFERENCES 學(xué)生表（sno）約束定義（續(xù)）默認(rèn)值約束格式：DEFAULT 默認(rèn)值例：定義系的默認(rèn)值為“計(jì)算機(jī)系”。 DEFAULT 計(jì)算機(jī)系約束定義（續(xù)）CHECK約束格式：CHECK (約束表達(dá)式)例：定義成績(jī)大于等于0。 CH

8、ECK ( grade = 0 )約束定義（續(xù)）UNIQUE約束在列級(jí)約束定義（僅用于單列約束）列定義 UNIQUE例： SNAME char(7) UNIQUE在表級(jí)約束定義（用于單列或多列組合約束）UNIQUE （）例： UNIQUE (SNO，CNO)創(chuàng)建學(xué)生表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),

9、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 ) REF

10、ERENCES Course ( Cno ) )刪除表當(dāng)確信不再需要某個(gè)表時(shí)，可以將其刪除刪除表時(shí)會(huì)將與表有關(guān)的所有對(duì)象一起刪掉，包括表中的數(shù)據(jù)。刪除表的語(yǔ)句格式為：DROP TABLE , 例：刪除test表的語(yǔ)句為：DROP TABLE test4.3.2 修改表結(jié)構(gòu) 在定義完表之后，如果需求有變化，比如添加列、刪除列或修改列定義，可以使用ALTER TABLE語(yǔ)句實(shí)現(xiàn)。ALTER TABLE語(yǔ)句可以對(duì)表添加列、刪除列、修改列的定義、定義主碼、外碼，也可以添加和刪除約束。 修改表結(jié)構(gòu)語(yǔ)法ALTER TABLE ALTER COLUMN | ADD COLUMN | DROP COLUMN

11、| ADD PRIMARY KEY（列名 ， n ） | ADD FOREIGN KEY（列名） REFERNECES 表名（列名）示例例2為SC表添加“修課類別”列，此列的定義為：XKLB char(4)ALTER TABLE SC ADD XKLB char(4) NULL 示例例3將新添加的XKLB的類型改為 char(6)。ALTER TABLE SC ALTER COLUMN XKLB char(6)示例例4刪除Course表的Period列。 ALTER TABLE Course DROP COLUMN Period 4.4 數(shù)據(jù)查詢功能 4.4.1 查詢語(yǔ)句的基本結(jié)構(gòu)4.4.2

12、簡(jiǎn)單查詢4.4.3 多表連接查詢4.4.4 子查詢 查詢語(yǔ)句基本格式SELECT -需要哪些列 FROM -來(lái)自于哪些表 WHERE -根據(jù)什么條件 GROUP BY HAVING ORDER BY 4.4.2 簡(jiǎn)單查詢1. 選擇表中若干列 之1. 查詢指定的列查詢表中用戶感興趣的部分屬性列。例5：查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名。SELECT Sno，Sname FROM Student例6：查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)和所在系。SELECT Sname，Sno，Sdept FROM Student 2. 查詢?nèi)苛?例7查詢?nèi)w學(xué)生的記錄SELECT Sno，Sname，Ssex, Sage, Sde

13、pt FROM Student等價(jià)于： SELECT * FROM Student 3. 查詢經(jīng)過(guò)計(jì)算的列 例8查詢?nèi)w學(xué)生的姓名及其出生年份。SELECT Sname，2006 - Sage FROM Student例9查詢?nèi)w學(xué)生的姓名和出生年份所在系，并在出生年份列前加入一個(gè)列，此列的每行數(shù)據(jù)均為出生年份常量值。SELECT Sname，出生年份, 2006-Sage FROM Student 改變列標(biāo)題 語(yǔ)法：列名 | 表達(dá)式 AS 列標(biāo)題或：列標(biāo)題 列名 | 表達(dá)式例： SELECT Sname 姓名，Year of Birth 出生年份, 2006 - Sage 年份， FROM

14、Student 4.4.2 簡(jiǎn)單查詢2. 選擇表中若干元組 之1. 消除取值相同的記錄例10在修課表中查詢有哪些學(xué)生修了課程，要求列出學(xué)生的學(xué)號(hào)。 SELECT Sno FROM SC結(jié)果中有重復(fù)的行。用DISTINCT關(guān)鍵字可以去掉結(jié)果中的重復(fù)行。DISTINCT關(guān)鍵字放在SELECT詞的后邊、目標(biāo)列名序列的前邊。SELECT DISTINCT Sno FROM SC2. 查詢滿足條件的元組 查詢條件謂 詞比較運(yùn)算符=, , =, , =, （或!=）NOT+比較運(yùn)算符 確定范圍BETWEENAND, NOT BETWEENAND確定集合IN, NOT IN字符匹配LIKE, NOT LIK

15、E 空值IS NULL, IS NOT NULL邏輯謂詞）AND, OR比較大小例11查詢計(jì)算機(jī)系全體學(xué)生的姓名。SELECT Sname FROM Student WHERE Sdept = 計(jì)算機(jī)系例12查詢年齡在20歲以下的學(xué)生的姓名及年齡。SELECT Sname, Sage FROM Student WHERE Sage 20例13查詢考試成績(jī)有不及格的學(xué)生的學(xué)號(hào) SELECT DISTINCT Sno FROM SC WHERE Grade 60確定范圍用BETWEENAND和NOT BETWEENAND是邏輯運(yùn)算符，可以用來(lái)查找屬性值在或不在指定范圍內(nèi)的元組，其中BETWEEN后

16、邊指定范圍的下限，AND后邊指定范圍的上限。BETWEENAND的格式為： 列名 | 表達(dá)式 NOT BETWEEN 下限值 AND 上限值如果列或表達(dá)式的值在不在下限值和上限值范圍內(nèi)，則結(jié)果為T(mén)rue，表明此記錄符合查詢條件。 示例例14查詢年齡在2023歲之間的學(xué)生的姓名、所在系和年齡。SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23例15查詢年齡不在2023之間的學(xué)生姓名、所在系和年齡。SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWE

17、EN 20 AND 23確定集合使用IN運(yùn)算符。用來(lái)查找屬性值屬于指定集合的元組。格式為： 列名 NOT IN （常量1, 常量2, 常量n）當(dāng)列中的值與IN中的某個(gè)常量值相等時(shí)，則結(jié)果為T(mén)rue，表明此記錄為符合查詢條件的記錄；NOT IN：當(dāng)列中的值與某個(gè)常量值相同時(shí)，則結(jié)果為False，表明此記錄為不符合查詢條件的記錄 示例例16查詢信息系、數(shù)學(xué)系和計(jì)算機(jī)系學(xué)生的姓名和性別。SELECT Sname, Ssex FROM Student WHERE Sdept IN (信息系, 數(shù)學(xué)系, 計(jì)算機(jī)系)例17查詢既不是信息系、數(shù)學(xué)系，也不是計(jì)算機(jī)系學(xué)生的姓名和性別。SELECT Sname,

18、 Ssex FROM Student WHERE Sdept NOT IN (信息系, 數(shù)學(xué)系, 計(jì)算機(jī)系)字符匹配使用LIKE運(yùn)算符一般形式為： 列名 NOT LIKE 匹配串中可包含如下四種通配符：_：匹配任意一個(gè)字符；%：匹配0個(gè)或多個(gè)字符； ：匹配 中的任意一個(gè)字符； ：不匹配 中的任意一個(gè)字符 示例例18查詢姓張的學(xué)生的詳細(xì)信息。SELECT * FROM Student WHERE Sname LIKE 張%例19查詢學(xué)生表中姓張、李和劉的學(xué)生的情況。SELECT * FROM Student WHERE Sname LIKE 張李劉%例20查詢名字中第2個(gè)字為小或大的學(xué)生的姓名和

19、學(xué)號(hào) SELECT Sname, Sno FROM Student WHERE Sname LIKE _小大%示例（續(xù)）例21查詢所有不姓“劉”的學(xué)生。SELECT Sname FROM Student WHERE Sname NOT LIKE 劉%例22查詢學(xué)號(hào)的最后一位不是2、3、5的學(xué)生情況。SELECT * FROM Student WHERE Sno LIKE %235 涉及空值的查詢空值（NULL）在數(shù)據(jù)庫(kù)中表示不確定的值。例如，學(xué)生選修課程后還沒(méi)有考試時(shí)，這些學(xué)生有選課記錄，但沒(méi)有考試成績(jī)，因此考試成績(jī)?yōu)榭罩?。判斷某個(gè)值是否為NULL值，不能使用普通的比較運(yùn)算符。判斷取值為空的語(yǔ)

20、句格式為：列名 IS NULL判斷取值不為空的語(yǔ)句格式為：列名 IS NOT NULL 示例例23查詢無(wú)考試成績(jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。SELECT Sno, Cno FROM SC WHERE Grade IS NULL例24查詢所有有考試成績(jī)的學(xué)生的學(xué)號(hào)和課程號(hào)。SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL 多重條件查詢?cè)赪HERE子句中可以使用邏輯運(yùn)算符AND和OR來(lái)組成多條件查詢。用AND連接的條件表示必須全部滿足所有的條件的結(jié)果才為T(mén)rue；用OR連接的條件表示只要滿足其中一個(gè)條件結(jié)果即為T(mén)rue。例25查詢計(jì)算機(jī)系年齡在20歲以

21、下的學(xué)生姓名。SELECT Sname FROM Student WHERE Sdept = CS AND Sage 20 4.4.2 簡(jiǎn)單查詢3. 對(duì)查詢結(jié)果進(jìn)行排序 之對(duì)查詢結(jié)果進(jìn)行排序可對(duì)查詢結(jié)果進(jìn)行排序。 排序子句為：ORDER BY ASC | DESC , 說(shuō)明：按進(jìn)行升序（ASC）或降序（DESC）排序。示例例26將學(xué)生按年齡的升序排序。SELECT * FROM Student ORDER BY Sage例27查詢選修了c02號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按成績(jī)降序排列。SELECT Sno, Grade FROM SCWHERE Cno=c02 ORDER BY Gra

22、de DESC 例28查詢?nèi)w學(xué)生的信息，查詢結(jié)果按所在系的系名升序排列，同一系的學(xué)生按年齡降序排列。SELECT * FROM StudentORDER BY Sdept, Sage DESC 4.4.2 簡(jiǎn)單查詢4. 使用計(jì)算函數(shù)匯總數(shù)據(jù) 之使用計(jì)算函數(shù)匯總數(shù)據(jù) SQL提供的計(jì)算函數(shù)有：COUNT（ * ）：統(tǒng)計(jì)表中元組個(gè)數(shù)；COUNT（DISTINCT ）：統(tǒng)計(jì)本列列值個(gè)數(shù)；SUM（DISTINCT ）：計(jì)算列值總和；AVG（DISTINCT ）：計(jì)算列值平均值；MAX（DISTINCT ）：求列值最大值； MIN（DISTINCT ）：求列值最小值。上述函數(shù)中除COUNT（*）外，其他

23、函數(shù)在計(jì)算過(guò)程中均忽略NULL值。示例例29統(tǒng)計(jì)學(xué)生總?cè)藬?shù)。SELECT COUNT(*) FROM Student 例30統(tǒng)計(jì)選修了課程的學(xué)生的人數(shù)。SELECT COUNT (DISTINCT Sno) FROM SC例31 計(jì)算9512101號(hào)學(xué)生的考試總成績(jī)之和。SELECT SUM(Grade) FROM SC WHERE Sno = 9512101 示例（續(xù)）例32計(jì)算C01號(hào)課程學(xué)生的考試平均成績(jī)。SELECT AVG(Grade) FROM SC WHERE Cno=C01例33查詢選修了C01號(hào)課程的學(xué)生的最高分和最低分。SELECT MAX(Grade) , MIN(Gra

24、de) FROM SC WHERE Cno=C01注意：計(jì)算函數(shù)不能出現(xiàn)在WHERE子句中 4.4.2 簡(jiǎn)單查詢5. 對(duì)查詢結(jié)果進(jìn)行分組計(jì)算 之對(duì)查詢結(jié)果進(jìn)行分組計(jì)算 作用：可以控制計(jì)算的級(jí)別：對(duì)全表還是對(duì)一組。目的：細(xì)化計(jì)算函數(shù)的作用對(duì)象。分組語(yǔ)句的一般形式： GROUP BY HAVING 1. 使用GROUP BY例34統(tǒng)計(jì)每門(mén)課程的選課人數(shù)，列出課程號(hào)和人數(shù)。 SELECT Cno as 課程號(hào), COUNT(Sno) as 選課人數(shù) FROM SC GROUP BY Cno 對(duì)查詢結(jié)果按Cno的值分組，所有具有相同Cno值的元組為一組，然后再對(duì)每一組使用COUNT計(jì)算，求得每組的學(xué)生

25、人數(shù)。SnoCnoGrade951201C0180951201C0278951202C0190952103C0288952103C0185952103C0391952103C0474CnoCount(Sno)C013C022C031C041SnoCnoGrade951201C0180951202C0190952103C0185951201C0278952103C0288952103C0391952103C0474例35查詢每名學(xué)生的選課門(mén)數(shù)和平均成績(jī)。 SELECT Sno as 學(xué)號(hào), COUNT(*) as 選課門(mén)數(shù), AVG(Grade) as 平均成績(jī) FROM SC GROUP BY

26、 Sno2. 使用HAVINGHAVING用于對(duì)分組自身進(jìn)行限制，它有點(diǎn)象WHERE子句，但它用于組而不是對(duì)單個(gè)記錄。例36查詢修了3門(mén)以上課程的學(xué)生的學(xué)號(hào)SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3 示例例37查詢修課門(mén)數(shù)等于或大于4門(mén)的學(xué)生的平均成績(jī)和選課門(mén)數(shù)。SELECT Sno, AVG(Grade) 平均成績(jī), COUNT(*) 修課門(mén)數(shù) FROM SC GROUP BY Sno HAVING COUNT(*) = 4 4.4.3 多表連接查詢 若一個(gè)查詢同時(shí)涉及兩個(gè)或兩個(gè)以上的表，則稱之為連接查詢。連接查詢是關(guān)系數(shù)據(jù)庫(kù)中最主要的

27、查詢連接查詢包括內(nèi)連接、外連接和交叉連接等。連接基礎(chǔ)知識(shí)連接查詢中用于連接兩個(gè)表的條件稱為連接條件或連接謂詞。 一般格式為： 必須是可比的內(nèi)連接SQL-92 內(nèi)連接語(yǔ)法如下： SELECT FROM 表名 INNER JOIN 被連接表 ON 連接條件執(zhí)行連接操作的過(guò)程：首先取表1中的第1個(gè)元組，然后從頭開(kāi)始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第1個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中的一個(gè)元組。表2全部查找完畢后，再取表1中的第2個(gè)元組，然后再?gòu)念^開(kāi)始掃描表2， 重復(fù)這個(gè)過(guò)程，直到表1中的全部元組都處理完畢為止。 例40.查詢計(jì)算機(jī)系學(xué)生的修課情況，要求列出學(xué)生的名字、所修

28、課的課程號(hào)和成績(jī)。SELECT Sname, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = 計(jì)算機(jī)系 例41. 查詢信息系修了VB課程的學(xué)生的修課成績(jī)，要求列出學(xué)生姓名、課程名和成績(jī)。SELECT Sname, Cname, Grade FROM Student s JOIN SC ON s.Sno = SC. Sno JOIN Course c ON o = o WHERE Sdept = 信息系 AND Cname = VB 自連接為特殊的內(nèi)連接相互連接的表物理上為同一張表。必須為兩個(gè)表取別名，使

29、之在邏輯上成為兩個(gè)表。例43. 查詢與劉晨在同一個(gè)系學(xué)習(xí)的學(xué)生的姓名和所在的系。SELECT S2.Sname, S2.Sdept FROM Student S1 JOIN Student S2 ON S1.Sdept = S2.Sdept WHERE S1.Sname = 劉晨 AND S2.Sname != 劉晨外連接只限制一張表中的數(shù)據(jù)必須滿足連接條件，而另一張表中數(shù)據(jù)可以不滿足連接條件。 ANSI方式的外連接的語(yǔ)法格式為：FROM 表1 LEFT | RIGHT OUTER JOIN 表2 ON theta方式的外連接的語(yǔ)法格式為：左外連接： FROM 表1, 表2 WHERE 表1.

30、列名(+) 表2.列名右外連接： FROM 表1, 表2 WHERE 表1.列名 表2.列名(+) 例44. 查詢學(xué)生的修課情況，包括修了課程的學(xué)生和沒(méi)有修課的學(xué)生。SELECT Student.Sno, Sname, Cno, GradeFROM Student LEFT OUTER JOIN SCON Student.Sno = SC.Sno 4.4.4 子查詢 在SQL語(yǔ)言中，一個(gè)SELECTFROMWHERE語(yǔ)句稱為一個(gè)查詢塊。子查詢是一個(gè) SELECT 查詢，它嵌套在 SELECT、INSERT、UPDATE、DELETE 語(yǔ)句的 WHERE 或 HAVING 子句內(nèi)，或其它子查詢中

31、 子查詢的 SELECT 查詢總是使用圓括號(hào)括起來(lái)。使用子查詢進(jìn)行基于集合的測(cè)試使用子查詢進(jìn)行基于集合的測(cè)試的語(yǔ)句的一般格式為：列名 NOT IN (子查詢) 示例例45. 查詢與劉晨在同一個(gè)系的學(xué)生。SELECT Sno, Sname, Sdept FROM StudentWHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname = 劉晨 ) AND Sname != 劉晨 示例例46. 查詢成績(jī)?yōu)榇笥?0分的學(xué)生的學(xué)號(hào)、姓名。SELECT Sno, Sname FROM StudentWHERE Sno IN( SELECT Sno FR

32、OM SCWHERE Grade 90 ) 例47. 查詢選修了“數(shù)據(jù)庫(kù)基礎(chǔ)”課程的學(xué)生的學(xué)號(hào)、姓名。 SELECT Sno, Sname FROM Student WHERE Sno IN( SELECT Sno FROM SC WHERE Cno IN(SELECT Cno FROM Course WHERE Cname = 數(shù)據(jù)庫(kù)基礎(chǔ)) ) 使用子查詢進(jìn)行比較測(cè)試 帶比較運(yùn)算符的子查詢指父查詢與子查詢之間用比較運(yùn)算符連接，當(dāng)用戶能確切知道內(nèi)層查詢返回的是單值時(shí)，可用、=、=、運(yùn)算符。例48. 查詢修了c02課程且成績(jī)高于此課程的平均成績(jī)的學(xué)生的學(xué)號(hào)和成績(jī)。SELECT Sno , Gra

33、de FROM SC WHERE Cno = c02 and Grade ( SELECT AVG(Grade) from SC WHERE Cno = c02) 使用子查詢進(jìn)行存在性測(cè)試 一般使用EXISTS謂詞。帶EXISTS謂詞的子查詢不返回查詢的數(shù)據(jù)，只產(chǎn)生邏輯真值（有數(shù)據(jù)）和假值（沒(méi)有數(shù)據(jù)）。 例49.查詢選修了c01號(hào)課程的學(xué)生姓名。SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = c01) 注意注1：處理過(guò)程為：先外后內(nèi)；由外層的值決定內(nèi)層的結(jié)果；內(nèi)層

34、執(zhí)行次數(shù)由外層結(jié)果數(shù)決定。注2: 由于EXISTS的子查詢只能返回真或假值，因此在這里給出列名無(wú)意義。所以在有EXISTS的子查詢中，其目標(biāo)列表達(dá)式通常都用*。上句的處理過(guò)程1.找外層表Student表的第一行，根據(jù)其Sno值處理內(nèi)層查詢2.由外層的值與內(nèi)層的結(jié)果比較，由此決定外層條件的真、假3.順序處理外層表Student表中的第2、3、 行。例50.查詢沒(méi)有選修c01號(hào)課程的學(xué)生姓名和所在系。SELECT Sname, Sdept FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno

35、= c01) 4.5 數(shù)據(jù)更改功能 4.5.1 插入數(shù)據(jù) 4.5.2 更新數(shù)據(jù) 4.5.3 刪除數(shù)據(jù)4.5.1 插入數(shù)據(jù) 插入單行記錄的INSERT語(yǔ)句的格式為：INSERT INTO () VALUES （值表）功能：新增一個(gè)符合表結(jié)構(gòu)的數(shù)據(jù)行，將值表數(shù)據(jù)按表中列定義順序或列名表順序賦給對(duì)應(yīng)列名。注意值列表中的值與列名表中的列按位置順序?qū)?yīng)，它們的數(shù)據(jù)類型必須一致。如果后邊沒(méi)有指明列名，則新插入記錄的值的順序必須與表中列的定義順序一致，且每一個(gè)列均有值（可以為空）。示例例51將新生記錄（95020，陳冬，男，信息系，18歲）插入到Student表中。INSERT INTO Student V

36、ALUES (9521105, 陳冬, 男, 18, 信息系)例52在SC表中插入一新記錄，成績(jī)暫缺。INSERT INTO SC(Sno, Cno, XKLB) VALUES(9521105, c01, 必修) 實(shí)際插入的值為： (9521105, c01 ，NULL ，必修) 4.5.2 更新數(shù)據(jù) 用UPDATE語(yǔ)句實(shí)現(xiàn)。格式： UPDATE SET , n WHERE 無(wú)條件更新例53. 將所有學(xué)生的年齡加1。UPDATE Student SET Sage = Sage + 1有條件更新1. 基于本表?xiàng)l件的更新例54. 將9512101學(xué)生的年齡改為21歲UPDATE Student S

37、ET Sage = 21WHERE Sno = 9512101 2. 基于其他表?xiàng)l件的更新例55：將計(jì)算機(jī)系全體學(xué)生的成績(jī)加5分。（1）用子查詢實(shí)現(xiàn)UPDATE SC SET Grade = Grade + 5 WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept = 計(jì)算機(jī)系 )（2）用多表連接實(shí)現(xiàn)UPDATE SC SET Grade = Grade + 5 FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept = 計(jì)算機(jī)系4.5.3 刪除數(shù)據(jù) 用DELETE語(yǔ)句實(shí)現(xiàn)格式：DELETE FROM WHERE 無(wú)條件刪除例56. 刪除所有學(xué)生的選課記錄。DELETE FROM SC 有條件刪除 （1）基于本表?xiàng)l件的刪除。例57刪除所有不及格學(xué)生的修課記錄。 DELETE FROM SC WHERE Grade 60 基于其他表?xiàng)l件的刪除例58刪除計(jì)算機(jī)系不及格學(xué)生的修課記錄。（1）用子查詢實(shí)現(xiàn)DELETE FROM SC WHERE Grade 60 AN