《課件9數據庫》PPT課件.ppt

1、Web程序設計,第四章 數據庫編程技術,定義基本表語句格式：,CREATE TABLE （ ， ， ）； ：所要定義的基本表的名字 ：組成該表的各個屬性（列） ：涉及相應屬性列的完整性約束條件 ：涉及一個或多個屬性列的完整性約束條件,例題 ：,例1 建立一個”學生“表Student，它由學號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成。其中學號不能為空，值是唯一的，并且姓名取值也唯一。 CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE， Sname CHAR(20) UNIQUE， Ssex CHAR(1)

2、， Sage INT， Sdept CHAR(15)；,列級完整性約束Sno不能為空，取唯一值,定義基本表（續(xù)）,常用完整性約束 主碼約束： PRIMARY KEY 唯一性約束：UNIQUE 非空值約束：NOT NULL 參照完整性約束 PRIMARY KEY與 UNIQUE的區(qū)別？,例題 （續(xù)）,例2 建立一個“學生選課”表SC，它由學號Sno、課程號Cno，修課成績Grade組成，其中(Sno, Cno)為主碼。 CREATE TABLE SC( Sno CHAR(5) , Cno CHAR(3) , Grade int, Primary key (Sno, Cno);,例題 （續(xù)）,刪除

3、基本表,DROP TABLE ; 基本表刪除：當某個基本表不再需要時，就可刪除。數據、表上的索引都會刪除，表上的視圖往往仍然保留，但無法引用。 刪除基本表時，系統(tǒng)會從數據字典中刪去有關該基本表及其索引的描述。,例題,例5 刪除Student表 DROP TABLEStudent ;,修改基本表,ALTER TABLE ADD 完整性約束 DROP MODIFY ； ：要修改的基本表 ADD子句：增加新列和新的完整性約束條件 DROP子句：刪除指定的完整性約束條件 MODIFY子句：用于修改列名和數據類型,例題,例2 向Student表增加”入學時間“列，其數據類型為日期型。 ALTER TAB

4、LE Student ADD Scome DATE； 不論基本表中原來是否已有數據，新增加的列一律為空值。,語句格式（續(xù)）,刪除屬性列 直接/間接刪除 把表中要保留的列及其內容復制到一個新表中 刪除原表 再將新表重命名為原表名 直接刪除屬性列:(新) 例：ALTER TABLE Student Drop Scome；,例3 將年齡的數據類型改為半字長整數。 ALTER TABLE Student MODIFY Sage SMALLINT； 注：修改原有的列定義有可能會破壞已有數據,例題,例4 刪除學生姓名必須取唯一值的約束。 ALTER TABLE student DROP UNIQUE(Sn

5、ame);, 建立與刪除索引,建立索引是加快查詢速度的有效手段 建立索引 DBA（系統(tǒng)管理員）或表的屬主（即建立表的人）根據需要建立。 有些DBMS自動建立以下列上的索引: PRIMARY KEY UNIQUE 維護索引 DBMS自動完成 使用索引 DBMS自動選擇是否使用索引以及使用哪些索引,一、建立索引,語句格式 CREATE UNIQUE CLUSTER INDEX ON (, )； 用指定要建索引的基本表名字 索引可以建立在該表的一列或多列上，各列名之間用逗號分隔 用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC UNIQUE表明此索引的每一個索引值只對應唯一的數據

6、記錄 CLUSTER表示要建立的索引是聚簇索引，,例題,例6 為學生-課程數據庫中的Student，Course，SC三個表建立索引。其中Student表按學號升序建唯一索引，Course表按課程號升序建唯一索引，SC表按學號升序和課程號降序建唯一索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno)； CREATE UNIQUE INDEX Coucno ON Course(Cno)； CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)；,建立索引 （續(xù)）,唯一值索引 對于已含重復值的屬性列不能建UNIQUE

7、索引 對某個列建立UNIQUE索引后，插入新記錄時DBMS會自動檢查新記錄在該列上是否取了重復值。這相當于增加了一個UNIQUE約束,建立索引 （續(xù)）,聚簇索引: 建立聚簇索引后，基表中數據也需要按指定的聚簇屬性值的升序或降序存放。即聚簇索引的索引項順序與表中記錄的物理順序一致 例： CREATE CLUSTER INDEX Stusname ON Student(Sname)； 在Student表的Sname（姓名）列上建立一個聚簇索引，而 且Student表中的記錄將按照Sname值的升序存放,建立索引 （續(xù)）,在一個基本表上最多只能建立一個聚簇索引 聚簇索引的用途：對于某些類型的查詢，可

8、以提高查詢效率 聚簇索引的適用范圍 很少對基表進行增刪操作 很少對其中的變長列進行修改操作,二、刪除索引,DROP INDEX ； 刪除索引時，系統(tǒng)會從數據字典中刪去有關該索引的描述。 例 刪除Student表的Stusname索引。 DROP INDEX Stusname；, 查 詢,概述 單表查詢 連接查詢 嵌套查詢 集合查詢 小結,1 概述,語句格式 SELECT ALL/DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC/DESC ；,語句格式,SELECT子句：指定要顯示的屬性列 FROM子句：指定查詢對象(基本表或視圖) WHE

9、RE子句：指定查詢條件 GROUP BY子句：對查詢結果按指定列的值分組，該屬性列值相等的元組為一個組。通常會在每組中作用集函數。 HAVING短語：篩選出只有滿足指定條件的組 ORDER BY子句：對查詢結果表按指定列值的升序或降序排序,示例數據庫,學生-課程數據庫（包括三個表） 學生表：Student(Sno，Sname，Ssex，Sage，Sdept) 課程表：Course(Cno，Cname，Cpno，Ccredit) 學生選課表：SC(Sno，Cno，Grade),單表查詢,查詢僅涉及一個表，是一種最簡單的查詢操作 一、選擇表中的若干列 二、選擇表中的若干元組 三、對查詢結果排序 四

10、、使用集函數 五、對查詢結果分組,選擇表中的若干列查詢指定列,例1 查詢全體學生的學號與姓名。 SELECT Sno，Sname FROM Student； 例2 查詢全體學生的姓名、學號、所在系。 SELECT Sname，Sno，Sdept FROM Student；,選擇表中的若干列查詢全部列,例3 查詢全體學生的詳細記錄。 SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student； 或 SELECT FROM Student；,選擇表中的若干列查詢經過計算的值,SELECT子句的為表達式 算術表達式 字符串常量 函數 列別名,查詢經過計算的值,例 查全

11、體學生的姓名及其出生年份。 SELECT Sname，2006-Sage FROM Student； 輸出結果： Sname 2006-Sage - - 李勇 1986 劉晨 1987 王名 1988 張立 1988,此結果是當前年份減去學生的年齡,查詢經過計算的值,例 查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。 SELECT Sname，Year of Birth: ，2006-Sage， ISLOWER(Sdept) FROM Student；,例題（續(xù)）,輸出結果： Sname Year of Birth: 2006-Sage ISLOWER(Sdept) - -

12、 - - 李勇 Year of Birth: 1986 cs 劉晨 Year of Birth: 1987 is 王名 Year of Birth: 1988 ma 張立 Year of Birth: 1987 is,例 使用列別名改變查詢結果的列標題,SELECT Sname NAME，Year of Birth: BIRTH， 2006-Sage BIRTHDAY，ISLOWER(Sdept) DEPARTMENT FROM Student； 輸出結果： NAME BIRTH BIRTHDAY DEPARTMENT - - - - 李勇 Year of Birth: 1986 cs 劉晨

13、Year of Birth: 1987 is 王名 Year of Birth: 1988 ma 張立 Year of Birth: 1987 is,消除取值重復的行:ALL 與 DISTINCT,例 查詢選修了課程的學生學號。 (1) SELECT Sno FROM SC; 或(默認 ALL) SELECT ALL Sno FROM SC; 結果： Sno - 95001 95001 95001 95002 95002,例題（續(xù)）,(2) DISTINCT 是去掉重復字段查詢記錄 SELECT DISTINCT Sno FROM SC; 結果： Sno - 95001 95002,例題（續(xù)）

14、,注意 DISTINCT短語的作用范圍是所有目標列 例：查詢選修課程的各種成績 錯誤的寫法 SELECT DISTINCT Cno，DISTINCT Grade FROM SC; 正確的寫法 SELECT DISTINCT Cno，Grade FROM SC;,選擇表中的若干元組-查詢滿足條件的元組,WHERE子句常用的查詢條件,(1) 比較大小,在WHERE子句的中使用比較運算符 =，=，!，!= 20；,(2) 確定范圍,使用謂詞 BETWEEN AND NOT BETWEEN AND 例 查詢年齡在2023歲（包括20歲和23歲）之間的學生的姓名、系別和年齡。 SELECT Sname，

15、Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23；,例題（續(xù)）,例 查詢年齡不在2023歲之間的學生姓名、系別和年齡。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23；,(3) 確定集合,使用謂詞 IN可以用來查找屬性值屬于指定集合的元組. IN , NOT IN ：用逗號分隔的一組取值 例查詢信息系（IS）、數學系（MA）和計算機科學系（CS）學生的姓名和性別。 SELECT Sname，Ssex FROM Student WHERE Sdept

16、 IN ( IS，MA，CS );,(3) 確定集合,例查詢既不是信息系、數學系，也不是計算 機科學系的學生的姓名和性別。 SELECT Sname，Ssex FROM Student WHERE Sdept NOT IN ( IS，MA，CS );,(4) 字符串匹配,NOT LIKE ESCAPE ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 當匹配模板為固定字符串時， 可以用 = 運算符取代 LIKE 謂詞 用 != 或 運算符取代 NOT LIKE 謂詞,通配符,% (百分號) 代表任意長度（長度可以為0）的字符串 例：a%b表示以a開頭，以b結尾的任意長度的字符串。如acb

17、，addgb，ab 等都滿足該匹配串 _ (下橫線) 代表任意單個字符 例：a_b表示以a開頭，以b結尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串,(5) 涉及空值的查詢,使用謂詞 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “= NULL” 代替 例21 某些學生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢缺少成績的學生的學號和相應的課程號。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL；,例題(續(xù)),例22 查所有有成績的學生學號和課程號。 SELECT Sno，Cno FROM SC WHE

18、RE Grade IS NOT NULL；,(6) 多重條件查詢,用邏輯運算符AND和 OR來聯結多個查詢條件 AND的優(yōu)先級高于OR 可以用括號改變優(yōu)先級 可用來實現多種其他謂詞 NOT IN NOT BETWEEN AND ,例題,例23 查詢計算機系年齡在20歲以下的學生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage20；,查詢年齡在2023歲（包括20歲和23歲）之間的學生的姓名、系別和年齡。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23；

19、 可改寫為： SELECT Sname，Sdept，Sage FROM Student WHERE Sage=20 AND Sage=23；,三、對查詢結果排序,使用ORDER BY子句 可以按一個或多個屬性列排序 升序：ASC；降序：DESC；缺省值為升序ASC 當排序列含空值時 ASC：排序列為空值的元組最后顯示 DESC：排序列為空值的元組最先顯示,對查詢結果排序（續(xù)）,例24 查詢選修了3號課程的學生的學號及其成績，查詢結果按分數降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC；,查詢結果,Sno Grade

20、 - - 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55,對查詢結果排序（續(xù)）,例25 查詢全體學生情況，查詢結果按所在系的系號升序排列，同一系中的學生按年齡降序排列。 SELECT FROM Student ORDER BY Sdept，Sage DESC；,四、使用集函數,5類主要集函數： 計數 COUNT（DISTINCT|ALL ）統(tǒng)計元組個數 COUNT（DISTINCT|ALL ）統(tǒng)計一列中值的個數 計算一列值總和 SUM（DISTINCT|ALL ）數值型 計算平均值 AVG（DISTINCT|A

21、LL ）,使用集函數（續(xù)）,求最大值：MAX（DISTINCT|ALL ） 求最小值：MIN（DISTINCT|ALL ） DISTINCT短語：在計算時要取消指定列中的重復值 ALL短語：不取消重復值 ALL為缺省值,使用集函數 （續(xù)）,例26 查詢學生總人數。 SELECT COUNT() FROM Student； 例27 查詢選修了課程的學生人數。 SELECT COUNT(DISTINCT Sno) FROM SC； 注：用DISTINCT以避免重復計算學生人數,使用集函數 （續(xù)）,例28 計算1號課程的學生平均成績。 SELECT AVG(Grade) FROM SC WHERE

22、Cno= 1 ； 例29 查詢選修1號課程的學生最高分數。 SELECT MAX(Grade) FROM SC WHERE Cno= 1 ；,五、對查詢結果分組,使用GROUP BY子句分組 細化集函數的作用對象 未對查詢結果分組，集函數將作用于整個查詢結果 對查詢結果分組后，集函數將分別作用于每個組,使用GROUP BY子句分組,例30 求各個課程號及相應的選課人數。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno； 結果 Cno COUNT(Sno) 1 22 2 34 3 44 4 33 5 48,對查詢結果分組 （續(xù)）,GROUP BY子句的作用對

23、象是查詢的中間結果表 分組方法：按指定的一列或多列值分組，值相等的為一組 使用GROUP BY子句后，SELECT子句的列名列表中只能出現分組屬性和集函數,使用HAVING短語篩選最終輸出結果,例31 查詢選修了3門以上課程的學生學號。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT() 3；,例題,例32 查詢有3門以上課程是90分以上的 學生的學號及（90分以上的）課程數 SELECT Sno, COUNT(*) FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(*)=3;,使用HAVING短語篩選最

24、終輸出結果,只有滿足HAVING短語指定條件的組才輸出 HAVING短語與WHERE子句的區(qū)別：作用對象不同 WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。 HAVING短語作用于組，從中選擇滿足條件的組。,數據操作語句數據更新,數據操作語句主要對數據庫中的數據進行插入、修改、刪除。 一、插入數據 、插入單個元組 Insert Into(,) Values(, ); 例將一個新生記錄（學號：95020；姓名：陳冬；性別：男；所在系：IS；年齡：18）插入學生表中。 Insert into student Values(95020,陳冬，男，IS,18）；,數據操作語句數據更新,、插入

25、子查詢結果 Insert Into,) 子查詢; 例對每一個系,求學生的平均年齡,并把結果存入到數據庫。 Insert Into deptage(Sdept,Avgage) select Sdept,AVG(Sage) FORM Student GROUP BY Sdept;,首先建立一個新表，其中一列存放系名，另一列存放相應的學生平均年齡。然后對學生表按系分組求平均年齡，再把系名和平均年齡存入新表中。,二、修改數據 UPDATE SET=,= WHERE; 其功能是修改指定表中滿足WHERE子句條件的元組.其中SET子句給出的值取代相應的屬性列值。如省略WHERE子句,則表示要修改表中的所有

26、元組。 1、修改某一個元組的值 將學生95001的年齡改為22歲。 UPDATE STUDENT SET SAGE=22 WHERE SNO=95001；,數據操作語句數據更新,數據操作語句數據更新,2、修改多個元組的值 例 將所有學生的年齡增加1歲 UPDATE STUDENT SET SAGE =SAGE+1； 3、帶子查詢的修改語句：子查詢可嵌套在UPDATE語句中，用來構造修改的條件。 例將計算科學系全體學生的成績置零 UPDATE SC SET GRADE=0 WHERE CS=（SELETE SDEPT FORM STUDENT WHERE STUDENT.SNO=SC.SNO);

27、,數據操作語句數據更新,三、刪除數據 DELETE FROM WHERE; 、刪除某一個元組的值 例刪除學號為95019的學生記錄 DELETE FROM STUDENT WHERE SNO=95019；,DELETE語句的功能是從指定表中刪除滿足WHERE子句條件的所有元組,省略WHERE子句,表示刪除表中全部元組.此語句只刪除表中的數據.,數據操作語句數據更新,2、刪除多個元組的值 例刪除所有學生的選課記錄 DELETE FROM SC 3、帶子查詢的刪除語句 例刪除計算機科學系所有學生的選課記錄 DELETE FROM SC WHERE CS=（SELECT SDEPT FROM STU

28、DENT WHERE STUDENT.SNO=SC.SNO)； 4、更新操作與數據庫的一致性,視圖,視圖是從一個基本表或幾個基本表導出的表，是一個虛表。只存放視圖的定義，不存放視圖相應的數據。數據仍存放在原來的表中。 一、定義視圖 1、建立視圖: SQL語言用CREATE VIEW 命令建立視圖 CREATE VIEW(, ) AS SELECT WITH CHECK OPTION;,視圖,AS:可以是任意的SELECT語句,但通常不允許有ORDER BY 和DISTINCT語句。 WITH CHECK OPTION:表示對視圖進行UPDATE、INSERT、DELETE操作時要保證更新、插入

29、或刪除的行滿足視圖定義中的謂詞條件（即子查詢中的條件表達式）。 例建立信息系學生的視圖: CREATE VIEW IS_Student AS SELECT Sno, Sname,Sage FORM Student WHERE Sdept=IS,視圖,、刪除視圖： DROP VIEW視圖名； 二、查詢視圖 視圖定義后，用戶就可以象對基本表一樣對視圖進行查詢了。 例在信息系學生的視圖中找出年齡小于20歲的學生。 SELECT SNO，SAGE FORM ISSTUDENT WHERE SAGE20,DBMS執(zhí)行對視圖的查詢時，首先要檢查查詢的表、視圖是否存在，再對其查詢。等價于：SELECT SN

30、O，SAGE FORM STUDENT WHERE SDEP=IS AND SAGE20,視圖,三、更新視圖： 是指通過視圖來插入（INSERT）、刪除（DELETE）和修改（UPDATE）數據。由于視圖不存儲數據的虛表，對視圖的更新要轉換為對基本表的更新。 例將信息系學生視圖IS STUDENT中學號為95002的學生姓名改為“劉辰” UPDATE ISSTUDENT SET SNAME=劉辰 WHERE SNO=95002 轉換后的更新語句為： UPDATE STUDENT SET SNAME=劉辰 WHERE SNO=95002 AND SDEPT=IS；,視圖的作用,視圖能夠簡化用戶的

31、操作 視圖使用戶能以多種角度看待同一數據 視圖對重構數據庫提供了一定程度的邏輯獨立性 視圖能夠對機密數據提供安全保護,ADO編程技術,Active Data Object(ADO) 是一種功能強大的數據訪問編程模式，它把大部分數據源可編程的屬性直接擴展到服務器上。 ADO組建包括了以下重要對象： Connection Command Parameter Recordset Field,connection,Connection對象是建立與數據存儲進行連接的對象，代表一個打開的，與數據源的連接。 功能：指定使用的OLE DB提供者，連接到數據存儲的安全細節(jié)以及其他任何連接到數據存儲特有的細節(jié)。 創(chuàng)建：使用Server對象的CreateObject(“ADODB.Connection”)即可。,connection,Connection連接到數據源的方式： 連接字符串 數據連接文件 ODBC數據源 創(chuàng)建一個connection接口：調用Server對象的CreateObject方法： Set myconnection=Server.CreateObject(“ADO