數(shù)據(jù)庫系統(tǒng)概論第3章的知識

1、第三章 關系數(shù)據(jù)庫標準語言SQL1第三章 關系數(shù)據(jù)庫標準語言SQL3.1 SQL概述3.2 數(shù)據(jù)定義3.3 數(shù)據(jù)查詢3.4 數(shù)據(jù)更新3.5 視圖23.1 SQL概述SQL（Structured Query Language） 結構化查詢語言，是關系數(shù)據(jù)庫的標準語言數(shù)據(jù)查詢數(shù)據(jù)定義數(shù)據(jù)操縱數(shù)據(jù)控制3SQL視圖2視圖1基本表2基本表1基本表3基本表4存儲文件2存儲文件1外模式模 式內(nèi)模式SQL支持關系數(shù)據(jù)庫三級模式結構53.2 數(shù)據(jù)定義 SQL的數(shù)據(jù)定義功能: 63.2.1 模式的定義與刪除CREATE SCHEMA AUTHORIZATION | CREATE SCHEMA StuCourse

2、AUTHORIZATION WANG; DROP SCHEMA DROP SCHEMA StuCourse RESTRICT 73.2.2 基本表的定義、刪除與修改（1）基本表的定義CREATE TABLE （ ， ， ）；8課程表Course 例 建立一個“課程”表Course CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY， Cname CHAR(40)， Cpno CHAR(4) ， Ccredit SMALLINT， FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); 先修課 Cpno是外碼 被參照表是C

3、ourse被參照列是Cno9問題下面建立一個“學生選課”表SC方法是否正確？CREATE SC (Sno CHAR(9) PRIMARY KEY null， Cno CHAR(4) PRIMARY KEY ， Grade SMALLINT null， FOREIGN KEY (Sno) REFERENCES Student(Sno)， FOREIGN KEY (Cno) REFERENCES Course(Cno)，); 10Sql server 主要數(shù)據(jù)類型數(shù) 據(jù) 類 型符 號 標 識數(shù) 據(jù) 類 型符 號 標 識整數(shù)型bigint, int, smallint, tinyintUnicode

4、字符型nchar, nvarchar、精確數(shù)值型decimal, numeric文本型text, ntext浮點型float, real二進制型binary, varbinary貨幣型money, smallmoney日期時間類型datetime, smalldatetime, date, time位型bit時間戳型timestamp字符型char, varchar圖像型image其他cursor, sql_variant, table, uniqueidentifier, xml3.2.2 基本表的定義、刪除與修改（2）修改基本表ALTER TABLE ADD 完整性約束 DROP ALTE

5、R COLUMN ；例：向Student表增加“入學時間”列，其數(shù)據(jù)類型為日期型。ALTER TABLE Student ADD S_entrance DATE；123.2.2 基本表的定義、刪除與修改（3）刪除基本表 DROP TABLE RESTRICT| CASCADE；例:刪除Student表，同時刪除表上定義的索引等其他對象 DROP TABLE Student CASCADE ;133.2.3 索引的建立與刪除索引的建立CREATE UNIQUE CLUSTER INDEX ON (, )；CREATE CLUSTER INDEX Stusname ON Student(Sname

6、)；索引的刪除DROP INDEX Stusname；143.3數(shù)據(jù)查詢SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；153.3 數(shù)據(jù)查詢 3.3.1 單表查詢3.3.2 連接查詢3.3.3 嵌套查詢3.3.4 集合查詢3.3.5 Select語句的一般形式 163.3.1 單表查詢 查詢僅涉及一個表：選擇表中的若干列選擇表中的若干元組ORDER BY子句聚集函數(shù)GROUP BY子句Having 子句17選擇表中的若干列例1 選擇部分列SELECT Sno，Sname FROM Student；例選擇

7、所有列SELECT * FROM Student例獲得經(jīng)過計算的列SELECT Sname，2012-Sage FROM Student；例使用列別名SELECT Sname，2012-Sage year-of-birthFROM Student；18選擇表中的若干元組（1）消除取值重復的行：DISTINCT SELECT DISTINCT Sno FROM SC；19選擇表中的若干元組（2）查 詢 條 件謂 詞比 較=，=，=，!=，!，!；NOT+上述比較運算符確定范圍BETWEEN AND，NOT BETWEEN AND確定集合IN，NOT IN字符匹配LIKE，NOT LIKE空 值I

8、S NULL，IS NOT NULL多重條件（邏輯運算）AND，OR，NOT比較大小SELECT DISTINCT SnoFROM SCWHERE Grade60；確定范圍SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23； 21確定集合SELECT Sname，SsexFROM StudentWHERE Sdept IN ( IS，MA，CS );字符匹配 SELECT * FROM Student WHERE Sname LIKE 劉%；22空值的查詢 SELECT Sno，Cno FROM SC WHERE Gr

9、ade IS NULL多重條件查詢 SELECT Sname FROM Student WHERE Sdept= CS AND Sage20；23ORDER BY子句 可以按一個或多個屬性列排序SELECT *FROM StudentORDER BY Sdept，Sage DESC； 當排序列含空值時ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示 24聚集函數(shù) 計數(shù)COUNT（DISTINCT|ALL ）計算總和SUM（DISTINCT|ALL ） 計算平均值AVG（DISTINCT|ALL ）最值 MAX（DISTINCT|ALL ） MIN（DISTINCT|ALL

10、 ）25 例 查詢學生總人數(shù)。 SELECT COUNT(*) FROM Student； 例 查詢選修了課程的學生人數(shù)。 SELECT COUNT(DISTINCT Sno) FROM SC；26GROUP BY子句 對查詢的中間結果表進行分組，以細化聚集函數(shù)的作用例 求各個課程號及相應的選課人數(shù)。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno；CnoCOUNT(Sno)12223434443354827having子句對分組后的數(shù)據(jù)進行篩選，選出滿足條件的組例 查詢選修了3門以上課程的學生學號。 SELECT Sno FROM SC GROUP BY

11、 Sno HAVING COUNT(*) 3； 28問題Select、from、where、order by、group by、having子句在查詢過程中的執(zhí)行順序？293.3.2 連接查詢 連接條件或連接謂詞：用來連接兩個表的條件. . . BETWEEN . AND .連接操作的執(zhí)行方法嵌套循環(huán)法(NESTED-LOOP)排序合并法(SORT-MERGE)索引連接(INDEX-JOIN30連接查詢的種類一、等值與非等值連接查詢 二、自身連接三、外連接四、復合條件連接31等值與非等值連接查詢 查詢學生的姓名及獲得的成績SELECT Student.sname,SC.grade FROM S

12、tudent, SCWHERE Student.Sno = SC.Sno 注意：當兩個以上的表具有相同的列名時，一定要再列名前加上表名作為限制問題：此查詢有多少條記錄滿足條件？32自身連接 查詢存在間接先行課程的課程號及其間接先行課程號 SELECT FIRST.Cno，SECOND.Cpno pcno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno；CnoPcno17355633外連接(1)查詢每門課程號及其間接先行課程號（如果存在的話） SELECT FIRST.Cno，SECOND.Cpno FROM Cours

13、e FIRST LEFT OUT JOIN Course SECOND ON（FIRST.Cno=SECOND.Cpno) FoSecond.cpno172 null354null566null7null34外連接（2） 左外連接列出左邊關系（如本例Student）中所有的元組 右外連接列出右邊關系中所有的元組 全外連接列出兩個連接表中的所有元組35復合條件連接復合條件連接：WHERE子句中含多個連接條件查詢選修2號課程且成績在90分以上的所有學生SELECT Student.Sno, SnameFROM Student, SCWHERE Student.Sno = SC.Sno AND SC

14、.Cno= 2 AND SC.Grade 90； 363.3.3嵌套查詢一個SELECT-FROM-WHERE語句稱為一個查詢塊嵌套查詢:嵌套兩個以上的查詢塊 SELECT Sname /*外層查詢/父查詢*/ FROM Student WHERE Sno IN （SELECT Sno /*內(nèi)層查詢/子查詢*/ FROM SC WHERE Cno= 2 ）； 37帶有比較運算符的子查詢（1） 當能確切知道內(nèi)層查詢返回單值時，可用比較運算符（，=，=，!=或）。與ANY、some、ALL謂詞配合使用38帶有比較運算符的子查詢（2）查詢與劉晨同一學院的學生 SELECT Sno，Sname，Sde

15、pt FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= 劉晨)；39帶有比較運算符的子查詢（3）例41找出每個學生超過他選修課程平均成績的課程號。 SELECT Sno， Cno FROM SC x WHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);相關子查詢 40帶有ANY（SOME）或ALL謂詞的子查詢（1）某一值需要與多值比較時的妥協(xié)方法（配合比較運算符使用）比較含義比較含義 ANY大于子查詢結果中的某個值 ALL大于子查詢結果中的所

16、有值= ALL小于等于子查詢結果中的所有值 ANY小于子查詢結果中的某個值 = ANY等于子查詢結果中的某個值 = ANY大于等于子查詢結果中的某個值 !=（或）ANY不等于子查詢結果中的某個值= ALL大于等于子查詢結果中的所有值!=（或）ALL不等于子查詢結果中的任何一個值 41帶有ANY（SOME）或ALL謂詞的子查詢(2)例 查詢其他系中比計算機科學某一學生年齡小的學生姓名和年齡 SELECT Sname，Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept= CS ) AND Sdept CS ;

17、 42帶有EXISTS謂詞的子查詢(1)EXISTS( )-true EXISTS( )-FalseNOT EXISTS( )-true NOT EXISTS( )-false43例查詢所有選修了1號課程的學生姓名。 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1 )； 44例 查詢選修了全部課程的學生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXI

18、STS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ） ）；45 例查詢至少選修了學生200215122選修的全部課程的學生號碼。 SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 200215122 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno ) )；463.3.4 集合查詢并SE

19、LECT SnoFROM SCWHERE Cno= 1 UNIONSELECT SnoFROM SCWHERE Cno= 2 ；交SELECT SnoFROM SCWHERE Cno= 1 INTERSECTSELECT SnoFROM SCWHERE Cno= 2 ；差SELECT SnoFROM SCWHERE Cno= 1 EXCEPTSELECT SnoFROM SCWHERE Cno= 2 ；473.3.5 SELECT語句的一般格式 SELECT ALL|DISTINCT 別名 ， 別名 FROM 別名 ， 別名 WHERE GROUP BY HAVING ORDER BY ASC|DESC 483.4 數(shù) 據(jù) 更 新 3.4.1 插入數(shù)據(jù)3.4.2 修改數(shù)據(jù)3.4.3 刪除數(shù)據(jù) 493.4.1 插入數(shù)據(jù)（1）插入單行將一個新學生元組（學號：201115128； 課程號：2； 成績： 95）插入到SC表中。 INSERT INTO SC (Sno,Cno,grade) VALUES (201115128， 2，95)；503.4.1插入數(shù)據(jù)（2）同時插入多行 計算每個系的學生的平均年齡，