第四章關系數(shù)據(jù)庫語言SQL

1、第四章關系數(shù)據(jù)庫語言SQL第四章 關系數(shù)據(jù)庫語言SQL第四章關系數(shù)據(jù)庫語言SQL第四章 關系數(shù)據(jù)庫語言SQL 4.1SQL簡介簡介 4.2查詢語句查詢語句 4.3數(shù)據(jù)更新數(shù)據(jù)更新 4.4數(shù)據(jù)定義數(shù)據(jù)定義 小結(jié)小結(jié)第四章關系數(shù)據(jù)庫語言SQL4.1 SQL簡介SQL是結(jié)構(gòu)化查詢語言（是結(jié)構(gòu)化查詢語言（Structured Query Language）的縮寫，的縮寫， 是一種面向關系數(shù)據(jù)庫的國是一種面向關系數(shù)據(jù)庫的國際標準語言，在當前數(shù)據(jù)庫領域中應用最為廣泛和際標準語言，在當前數(shù)據(jù)庫領域中應用最為廣泛和成功，美國國家標準局成功，美國國家標準局ANSI在在1986年將年將SQL作為作為關系數(shù)據(jù)庫系統(tǒng)

2、的標準語言，后被國際標準化組織關系數(shù)據(jù)庫系統(tǒng)的標準語言，后被國際標準化組織（ISO）采納為國際標準?，F(xiàn)在很多大型數(shù)據(jù)庫都采納為國際標準。現(xiàn)在很多大型數(shù)據(jù)庫都實現(xiàn)了實現(xiàn)了SQL語言。語言。第四章關系數(shù)據(jù)庫語言SQL4.1.1 SQL語言的發(fā)展SQL的發(fā)展歷程主要有以下幾個階段：的發(fā)展歷程主要有以下幾個階段：（1）1974年由年由Boyce和和Chamberlin提出，當時稱為提出，當時稱為SEQUEL（Structured English Query Language）。）。（2）19751979年年IBM公司對公司對SEQUEL進行了修改，并由進行了修改，并由San Jose Research

3、 Laboratory研制了著名的關系數(shù)據(jù)庫管理系統(tǒng)原研制了著名的關系數(shù)據(jù)庫管理系統(tǒng)原型型System R，實現(xiàn)了這種語言。實現(xiàn)了這種語言。（3）1981年年IBM推出了商用關系數(shù)據(jù)庫推出了商用關系數(shù)據(jù)庫SQL/DS，并將其改名為并將其改名為SQL，由于它功能豐富，語言簡潔，因此倍受用戶及計算機工業(yè)由于它功能豐富，語言簡潔，因此倍受用戶及計算機工業(yè)界歡迎，被眾多計算機公司和軟件公司所采用。界歡迎，被眾多計算機公司和軟件公司所采用。（4）目前）目前SQL語言不僅用于小型數(shù)據(jù)庫如語言不僅用于小型數(shù)據(jù)庫如Foxpro、Access，而且而且更廣泛用于各種大型數(shù)據(jù)庫，如更廣泛用于各種大型數(shù)據(jù)庫，如Sy

4、sbase、SQL Server、Oracle、Informix、DB2、Ingres等。等。第四章關系數(shù)據(jù)庫語言SQLSQL在發(fā)展中經(jīng)歷了一系列標準化過程：在發(fā)展中經(jīng)歷了一系列標準化過程：（1）1982年美國國家標準局（年美國國家標準局（American National Standard Institute，簡稱簡稱ANSI）開始制訂開始制訂SQL標準。標準。（2）1986年年10月月ANSI的數(shù)據(jù)庫委員會的數(shù)據(jù)庫委員會X3H2批準了批準了SQL作為關系作為關系數(shù)據(jù)庫語言的美國標準；同年數(shù)據(jù)庫語言的美國標準；同年ANSI公布公布SQL語言的第一個標準語言的第一個標準SQ L - 8 6 ，

5、 并 于 次 年 由 國 際 標 準 化 組 織 （并 于 次 年 由 國 際 標 準 化 組 織 （ In t e rn a t i o n a l Standardization Organization，簡稱簡稱ISO）通過通過SQL-86標準。標準。（3）1989年年ISO對對SQL-86進行補充，推出進行補充，推出SQL-89標準，也稱標準，也稱SQL1。（4）1992年年ISO又推出又推出SQL-92標準，亦稱標準，亦稱SQL2。（5）1999年起年起ANSI陸續(xù)公布增加了面向?qū)ο蠊δ艿男聵藴赎懤m(xù)公布增加了面向?qū)ο蠊δ艿男聵藴蔛QL-99（亦稱亦稱SQL3）的的12個標準文本。個標

6、準文本。第四章關系數(shù)據(jù)庫語言SQL4.1.2 SQL語言的特點（1）非過程化：）非過程化：SQL語言是非過程化語言，在語言是非過程化語言，在SQL語言中，只要求用戶提語言中，只要求用戶提出出“做什么做什么”，而無需指出，而無需指出“怎樣做怎樣做”。SQL語句操作的過程由系統(tǒng)自語句操作的過程由系統(tǒng)自動完成。動完成。（2）一體化：）一體化：SQL可以操作于不同層次模式，集數(shù)據(jù)定義語言（可以操作于不同層次模式，集數(shù)據(jù)定義語言（DDL）、）、數(shù)據(jù)操縱語言數(shù)據(jù)操縱語言（DML）、）、數(shù)據(jù)控制語言（數(shù)據(jù)控制語言（DCL）為一體。用為一體。用SQL語言可語言可實現(xiàn)實現(xiàn)DB生命周期的全部活動，其中包括建立數(shù)據(jù)

7、庫、建立用戶賬號、生命周期的全部活動，其中包括建立數(shù)據(jù)庫、建立用戶賬號、定義關系模式、查詢及數(shù)據(jù)維護、數(shù)據(jù)庫安全控制等。定義關系模式、查詢及數(shù)據(jù)維護、數(shù)據(jù)庫安全控制等。（3）兩種使用方式，統(tǒng)一的語法結(jié)構(gòu)：）兩種使用方式，統(tǒng)一的語法結(jié)構(gòu)：兩種使用方式兩種使用方式:自含式語言與嵌入式語言自含式語言與嵌入式語言 自含式方式就是聯(lián)機交互使用方式自含式方式就是聯(lián)機交互使用方式; 嵌入式方式是指嵌入式方式是指SQL語句嵌入某種高級程序設計語言的程序中，以實現(xiàn)語句嵌入某種高級程序設計語言的程序中，以實現(xiàn)數(shù)據(jù)庫操作。數(shù)據(jù)庫操作。盡管這兩種使用方式不同，但盡管這兩種使用方式不同，但SQL語言的語法結(jié)構(gòu)基本是一致

8、的。語言的語法結(jié)構(gòu)基本是一致的。第四章關系數(shù)據(jù)庫語言SQL4.1.3 SQL語言的書寫準則 遵從某種準則可以提高語句的可讀性，通常遵循的遵從某種準則可以提高語句的可讀性，通常遵循的準則主要有：準則主要有：SQL語句對大小寫不敏感，但關鍵字常用大寫語句對大小寫不敏感，但關鍵字常用大寫SQL語句可寫在一行上，但為便于理解，增強條理性，語句可寫在一行上，但為便于理解，增強條理性，常習慣于每個字句占用一行常習慣于每個字句占用一行關鍵字不能在行與行之間分開，很少采用縮寫形式關鍵字不能在行與行之間分開，很少采用縮寫形式SQL中的數(shù)據(jù)項需同時列出時，分隔符用中的數(shù)據(jù)項需同時列出時，分隔符用“,”；字符；字符

9、或字符串常量的定界符用單引號或字符串常量的定界符用單引號“ ”表示表示第四章關系數(shù)據(jù)庫語言SQL4.2 查詢語句 4.2.1 基本查詢基本查詢 4.2.2 使用列表達式使用列表達式 4.2.3 WHERE從句的進一步使用從句的進一步使用 4.2.4 數(shù)據(jù)匯總數(shù)據(jù)匯總 4.2.5 連接查詢連接查詢 4.2.6 嵌套查詢嵌套查詢 4.2.7 聯(lián)合查詢聯(lián)合查詢 4.2.8 使用系統(tǒng)內(nèi)置函數(shù)的查詢使用系統(tǒng)內(nèi)置函數(shù)的查詢第四章關系數(shù)據(jù)庫語言SQL SQL的核心是數(shù)據(jù)查詢。的核心是數(shù)據(jù)查詢。對于數(shù)據(jù)庫的查詢操作是通過對于數(shù)據(jù)庫的查詢操作是通過 SELECT 查詢命查詢命令實現(xiàn)的，它的基本形式由令實現(xiàn)的，它

10、的基本形式由 SELECTFROMWHERE 查詢塊組成，多個查詢塊可以嵌套執(zhí)行。查詢塊組成，多個查詢塊可以嵌套執(zhí)行。總述第四章關系數(shù)據(jù)庫語言SQLSQL查詢語句的完整語法描述如下： SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ； 指定要顯示的屬性列指定要顯示的屬性列指定查詢對象指定查詢對象指定查詢條件指定查詢條件查詢結(jié)果按指查詢結(jié)果按指定列的值分組定列的值分組篩選出只有滿足指篩選出只有滿足指定條件的組定條件的組對查詢結(jié)果表按指列值對查詢結(jié)果表按指列值的升序或降序排序的升序或降序排序 第四章關系數(shù)據(jù)庫

11、語言SQL4.2.1 基本查詢 最基本的查詢就是從指定的表中找出符最基本的查詢就是從指定的表中找出符合條件的記錄。這樣的查詢由合條件的記錄。這樣的查詢由 SELECT和和 FROM 短語構(gòu)成無條件查詢，或由短語構(gòu)成無條件查詢，或由 SELECT、FROM 和和 WHERE 或其他短或其他短語構(gòu)成條件查詢。語構(gòu)成條件查詢。第四章關系數(shù)據(jù)庫語言SQL （1 1）簡單的無簡單的無條件查詢條件查詢設某個數(shù)據(jù)庫中有表設某個數(shù)據(jù)庫中有表stu_info，請把該表中的所請把該表中的所有記錄的所有屬性查詢出來有記錄的所有屬性查詢出來: SELECT * / *表示所有字段，是通配符表示所有字段，是通配符 FR

12、OM stu_info/ 選定數(shù)據(jù)庫中的表選定數(shù)據(jù)庫中的表 （2 2）簡單的條件查詢簡單的條件查詢請查找請查找stu_info表中所有的女生的信息表中所有的女生的信息。SELECT * FROM stu_info WHERE xbm=女女 /設定條件設定條件第四章關系數(shù)據(jù)庫語言SQL 在在SELECT子句中可以決定哪些列出現(xiàn)在結(jié)果關子句中可以決定哪些列出現(xiàn)在結(jié)果關系中，這相當于關系代數(shù)中的投影運算。系中，這相當于關系代數(shù)中的投影運算。 具體辦法：在具體辦法：在SELECT子句之后不是寫子句之后不是寫“*”，而是根據(jù)需要列出在而是根據(jù)需要列出在FROM子句中聲明的諸關系的屬子句中聲明的諸關系的屬

13、性。例：性。例：SELECT xh,xm /選定字段選定字段FROM stu_info /選定數(shù)據(jù)庫中的表選定數(shù)據(jù)庫中的表WHERE xbm=女女 /設定條件設定條件 （3 3）查詢語句中的投影操作）查詢語句中的投影操作第四章關系數(shù)據(jù)庫語言SQL使用使用 SQL 的的SELECT 可以將查詢結(jié)果排序，排可以將查詢結(jié)果排序，排序的短語是序的短語是 ORDER BY，具體格式如下：具體格式如下： SELECT 列名列名1，列名，列名2， FROM 表名表名 WHERE 條件表達式條件表達式 ORDER BY 列名列名1 ASC | DESC ，列名列名2ASC | DESC按升序（按升序（ASC）

14、或降序（或降序（DESC）排序，允許按一列排序，允許按一列或多列排序?；蚨嗔信判?。（4 4）設定排序條件）設定排序條件第四章關系數(shù)據(jù)庫語言SQL下面是使查詢結(jié)果排序的例子下面是使查詢結(jié)果排序的例子:SELECT * /“*”表示所有字段表示所有字段 FROM stu_info /選定數(shù)據(jù)庫中的表選定數(shù)據(jù)庫中的表 WHERE xbm=女女 /設定條件設定條件 ORDER BY xh /設定排序字段設定排序字段 第四章關系數(shù)據(jù)庫語言SQL 在查詢過程中經(jīng)常會出現(xiàn)一些重復記錄，例如在查詢過程中經(jīng)常會出現(xiàn)一些重復記錄，例如“SELECT bh FROM stu_info”。其查詢結(jié)果將會有其查詢結(jié)果將

15、會有多個重復的班級名多個重復的班級名，顯然這種結(jié)果不能令人滿意。因顯然這種結(jié)果不能令人滿意。因此需要去掉重復值，此時需要指定此需要去掉重復值，此時需要指定 DISTINCT 短語：短語： SELECT DISTINCT bh FROM stu_info 這里短語這里短語DISTINCT 的作用是去掉查詢結(jié)果中的重復的作用是去掉查詢結(jié)果中的重復值。執(zhí)行結(jié)果中班級名都不重復。值。執(zhí)行結(jié)果中班級名都不重復。 （5 5）限定重復記錄）限定重復記錄第四章關系數(shù)據(jù)庫語言SQL4.2.2 使用列表達式在在 SQL SQL中，指定列的方法我們學習了以下兩種：中，指定列的方法我們學習了以下兩種： （1 1）指定

16、列）指定列 （2 2）使用通配符）使用通配符* * 第第(3)(3)種是：可用列表達式，來獲取經(jīng)過計算的種是：可用列表達式，來獲取經(jīng)過計算的查詢結(jié)果。查詢結(jié)果。列表達式不僅可以是算術表達式，還可以是字符列表達式不僅可以是算術表達式，還可以是字符串常量、函數(shù)等。串常量、函數(shù)等。第四章關系數(shù)據(jù)庫語言SQL(6) (6) 計算列值計算列值SELECT 學號,xh,年齡, YEAR(GETDATE( )-YEAR(CSRQ),bhFROM stu_info(7) (7) 修改查詢結(jié)果的列標題修改查詢結(jié)果的列標題(1) SELECT YEAR(GETDATE( )-YEAR(CSRQ) AS 年齡, n

17、l FROM stu_info(2) SELECT 學號=xh, nl FROM stu_info(3) SELECT xh 學號, nl FROM stu_info第四章關系數(shù)據(jù)庫語言SQL查詢工作的關鍵就在通過分析怎樣把實際問查詢工作的關鍵就在通過分析怎樣把實際問題中的自然語言描述轉(zhuǎn)化為題中的自然語言描述轉(zhuǎn)化為1 1）從哪個表中查詢。即：）從哪個表中查詢。即：from子句子句2 2）要查詢哪些列。）要查詢哪些列。 即：即：select子句子句3) 3) 要查詢的條件。要查詢的條件。 即：即：where子句子句完成查詢工作的三個關鍵步驟完成查詢工作的三個關鍵步驟其中第其中第3 3步最復雜步最

18、復雜第四章關系數(shù)據(jù)庫語言SQL4.1.3 WHERE從句的進一步使用常用的查詢條件常用的查詢條件查詢條件查詢條件謂詞謂詞比較比較= = = != ! !20（9 9）確定范圍）確定范圍between and和和 not between and如如: :查詢考試成績在查詢考試成績在60-7060-70之間的學號、課程號、考試成績：之間的學號、課程號、考試成績：SELECT xh, kch, kscjFROM xkWHERE kscj BETWEEN 60 AND 70第四章關系數(shù)據(jù)庫語言SQL（10 10）確定集合）確定集合 IN 和和 NOT IN例：查詢復材例：查詢復材01080108 、復

19、材復材01090109班的學生的信息班的學生的信息SELECT xh,xm,xbm,bhFROM stu_infoWHERE bh IN( 復材復材0108, 復材復材0109)第四章關系數(shù)據(jù)庫語言SQL（11 11）字符匹配）字符匹配NOT LIKE 和和 LIKE 與與 % 和和 _ 搭配。搭配。%代表任意長度的字符串代表任意長度的字符串. .如如 a%b: acb, addgb a%b: acb, addgb 等都滿足匹配等都滿足匹配_( (下橫線下橫線) )代表任意單個字符。代表任意單個字符。例：查詢班級名中第例：查詢班級名中第1個字為個字為“計計”字的學生信息：字的學生信息： SEL

20、ECT * FROM stu_info WHERE bh like 計計%例：查詢名字中第例：查詢名字中第2個字為個字為“寧寧”字的學生信息：字的學生信息： SELECT * FROM stu_info WHERE xm like _寧寧%第四章關系數(shù)據(jù)庫語言SQL例：查詢例：查詢DB_Design課程的課程號和學分。課程的課程號和學分。 SELECT ckh,kcxf FROM GCourse WHERE km LIKE DB_Design ESCAPE 例：查詢以例：查詢以DB_開頭，且倒數(shù)第開頭，且倒數(shù)第3個字符為個字符為 i的課程的詳細情的課程的詳細情況。況。 SELECT * FRO

21、M GCourse WHERE km LIKE DB_%i_ _ ESCAPE 當用戶要查詢的字符串本身就含有當用戶要查詢的字符串本身就含有 % % 或或 _ _ 時，要使用時，要使用 ESCAPE 換碼字符換碼字符 短語對通配符進行轉(zhuǎn)義。短語對通配符進行轉(zhuǎn)義。第四章關系數(shù)據(jù)庫語言SQL(12) (12) 涉及空值的查詢涉及空值的查詢SELECT * FROM stu_info WHERE bh IS NULL第四章關系數(shù)據(jù)庫語言SQL(13) (13) 多重條件查詢多重條件查詢 用邏輯運算符用邏輯運算符and和和or連接，連接，and的優(yōu)先的優(yōu)先級高于級高于or。如：查詢應什么班，姓劉的學生

22、。如：查詢應什么班，姓劉的學生。SELECT * FROM stu_info WHERE bh like 應應% and 姓名姓名 like 劉劉%第四章關系數(shù)據(jù)庫語言SQL4.2.4 數(shù)據(jù)匯總（14 14）聚合函數(shù)）聚合函數(shù)count (*)統(tǒng)計元組（記錄）個數(shù)統(tǒng)計元組（記錄）個數(shù)count(distinct | all) 統(tǒng)計一列中值的個數(shù)統(tǒng)計一列中值的個數(shù)sum(distinct | all)求一列值的總和求一列值的總和（數(shù)值型）數(shù)值型）avg(distinct | all)求一列值的平均數(shù)求一列值的平均數(shù)max(distinct | all)求一列值中的最大值求一列值中的最大值min(

23、distinct | all)求一列值中的最小值求一列值中的最小值聚合函數(shù)一覽表聚合函數(shù)一覽表第四章關系數(shù)據(jù)庫語言SQL例：查詢學生總數(shù)例：查詢學生總數(shù)SELECT count(*) FROM stu_info例：已知數(shù)據(jù)庫表例：已知數(shù)據(jù)庫表XK中高等數(shù)學課程的代碼為中高等數(shù)學課程的代碼為090101，查詢學生選修高等數(shù)學的平均成績：，查詢學生選修高等數(shù)學的平均成績：SELECT avg(KSCJ) AS 高等數(shù)學高等數(shù)學 FROM XK WHERE KCH=090101第四章關系數(shù)據(jù)庫語言SQL（15 15）GROUP BY子句子句 Group by 子句將查詢結(jié)果表按某一列或多列值分組，值

24、相子句將查詢結(jié)果表按某一列或多列值分組，值相等的分為一組。等的分為一組。如：在表如：在表xk中查詢各課程編號及相應的選課人數(shù)中查詢各課程編號及相應的選課人數(shù)注：注：GROUP BYGROUP BY子句中不能使用子句中不能使用聚合函數(shù)，并且必須在聚合函數(shù)，并且必須在GROUP GROUP BYBY子句中列出子句中列出SELECTSELECT列表中所列表中所有的非聚合項。有的非聚合項。例：在表例：在表stu_info中查詢每個專業(yè)的男生、女生人數(shù)中查詢每個專業(yè)的男生、女生人數(shù)在在group by子句后面加子句后面加WITH ROLLUP或或WITH CUBE如果還要顯示每個專如果還要顯示每個專業(yè)的

25、總?cè)藬?shù)及全體學業(yè)的總?cè)藬?shù)及全體學生總?cè)藬?shù)，如何實現(xiàn)？生總?cè)藬?shù)，如何實現(xiàn)？ SELECT kch,count(xh) FROM xk GROUP BY kch SELECT zyh, xbm, count(*) as 人數(shù)人數(shù) FROM stu_info GROUP BY zyh, xbm第四章關系數(shù)據(jù)庫語言SQL例：查詢選修了例：查詢選修了3 3門以上課程的學生及選課數(shù)：門以上課程的學生及選課數(shù)：SELECT XH, COUNT(*)FROM XKGROUP BY XHHAVING (COUNT(*) 3) HAVING短語指定分組或聚合的條件。只有滿足條件的分短語指定分組或聚合的條件。只有滿

26、足條件的分組才被選出來，組才被選出來，HAVING一般與一般與GROUP BY子句一起使用。子句一起使用。（16 16）HAVING 短語短語SELECT XH, COUNT(*)FROM XKWHERE (XH LIKE 2001%)GROUP BY XHHAVING (COUNT(*) 3)例：查詢學號前四位是例：查詢學號前四位是20012001的學生的學號及選課數(shù)：的學生的學號及選課數(shù)：第四章關系數(shù)據(jù)庫語言SQLhaving having 短語指定選擇組的條件，只有滿足短語指定選擇組的條件，只有滿足條件的組才被選出來。一般和條件的組才被選出來。一般和group bygroup by子子句

27、一起使用句一起使用wherewhere子句從基本表中選擇滿足條件的元組，子句從基本表中選擇滿足條件的元組，而不是指定滿足條件的分組，這是二者的而不是指定滿足條件的分組，這是二者的根本區(qū)別。根本區(qū)別。第四章關系數(shù)據(jù)庫語言SQL若一個查詢同時涉及兩個以上的表，稱為連接若一個查詢同時涉及兩個以上的表，稱為連接查詢。查詢。連接查詢是關系數(shù)據(jù)庫中最主要的查詢，按連連接查詢是關系數(shù)據(jù)庫中最主要的查詢，按連接類型可分為：內(nèi)連接、外連接和交叉連接。其中接類型可分為：內(nèi)連接、外連接和交叉連接。其中內(nèi)連接又分：等值連接、非等值連接和自然連接；內(nèi)連接又分：等值連接、非等值連接和自然連接；外連接又分：左外連接、右外連

28、接和全外連接等。外連接又分：左外連接、右外連接和全外連接等。4.2.5 連 接 查 詢第四章關系數(shù)據(jù)庫語言SQL 連接查詢中用來連接兩個關系的條件稱為連接條連接查詢中用來連接兩個關系的條件稱為連接條件或連接謂詞，格式：件或連接謂詞，格式： 表名表名1.1.列名列名 比較運算符比較運算符 表名表名2.2.列名列名其中比較運算符主要有：其中比較運算符主要有：= =、 、 =、=、!=!=當連接運算符為當連接運算符為= =時，叫等值連接。時，叫等值連接。使用其它運算符稱為非等值連接。使用其它運算符稱為非等值連接。連接謂詞中的列名稱為連接字段。連接謂詞中的列名稱為連接字段。 第四章關系數(shù)據(jù)庫語言SQL

29、例：查詢每個學生及其選課情況例：查詢每個學生及其選課情況SELECT *FROM stu_info,xkWHERE stu_info.xh=xk.xh例：查詢應化例：查詢應化041041班每個學生及其選修課程的情況班每個學生及其選修課程的情況SELECT stu_info.xh, stu_info.xm, stu_info.bh, stu_info.xbm, xk.kch, xk.kscjFROM stu_info,xkWHERE stu_info.xh = xk.xh and stu_info.bh=應化應化041 /復合條件連接復合條件連接第四章關系數(shù)據(jù)庫語言SQL多表查詢：涉及兩個表以上

30、的查詢多表查詢：涉及兩個表以上的查詢SELECT xm FROM stu_info, xk, gcourseWHERE gcourse.km=數(shù)據(jù)庫原理數(shù)據(jù)庫原理 and gcourse.kch=xk.kch and xk.xh=stu_info.xh為了簡化，表名可以取為了簡化，表名可以取別名，且別名只在本次別名，且別名只在本次查詢有效查詢有效增加表別名后的結(jié)果：增加表別名后的結(jié)果：SELECT xm FROM stu_info a, xk b, gcourse cWHERE c.km=數(shù)據(jù)庫原理數(shù)據(jù)庫原理 and c.kch=b.kch and b.xh=a.xh例如：查詢選修了有機化學這

31、門課程的學生的姓名例如：查詢選修了有機化學這門課程的學生的姓名第四章關系數(shù)據(jù)庫語言SQL下面討論另一類基于多個關系的查詢，這類查下面討論另一類基于多個關系的查詢，這類查詢所要求的結(jié)果出自一個關系，但相關的條件卻涉詢所要求的結(jié)果出自一個關系，但相關的條件卻涉及多個關系。在前面的例子中，及多個關系。在前面的例子中，WHERE 之后是一之后是一個相對獨立的條件，這個條件或者為真、或者為假。個相對獨立的條件，這個條件或者為真、或者為假。但是，有時需要用另外的方式來表達檢索要求。比但是，有時需要用另外的方式來表達檢索要求。比如，當檢索關系如，當檢索關系X中的記錄時，它的條件依賴于相關中的記錄時，它的條件

32、依賴于相關的關系的關系Y中的記錄屬性值，這時使用中的記錄屬性值，這時使用SQL的嵌套查的嵌套查詢功能將非常方便。詢功能將非常方便。 4.2.6 嵌 套 查 詢第四章關系數(shù)據(jù)庫語言SQL 在在SQL語言中，一個語言中，一個select-from-where語句稱為一個語句稱為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的查詢塊，將一個查詢塊嵌套在另一個查詢塊的where子句或子句或having短語的條件中的查詢稱為嵌套查詢。短語的條件中的查詢稱為嵌套查詢。等價形式：等價形式：SELECT xm FROM stu_info,xk WHERE stu_info.xh=xk.xh and kch=05S

33、ELECT xmFROM stu_info例：在例：在stu_info表中查詢選修了表中查詢選修了05號課程的學生姓名號課程的學生姓名WHERE xh IN ( SELECT xh FROM xk WHERE kch =05 )第四章關系數(shù)據(jù)庫語言SQL例：查詢與劉玉濤在同一例：查詢與劉玉濤在同一個班學習的學生的學號、個班學習的學生的學號、姓名、班級。姓名、班級。SELECT xh, xm, bhFROM stu_infoWHERE bh IN(17)(17)帶有帶有in in謂詞的子查詢謂詞的子查詢(18)(18)帶有比較運算符的子查詢帶有比較運算符的子查詢例：查詢與學號是的學生在同例：查詢

34、與學號是的學生在同一個班學習的學生，按學號排一個班學習的學生，按學號排序。序。SELECT xh, xm, bhFROM stu_infoWHERE bh ORDER BY xh ( SELECT bh FROM stu_info WHERE xm =劉玉濤劉玉濤 ) = ( SELECT bh FROM stu_info WHERE xh =20029001 )第四章關系數(shù)據(jù)庫語言SQL(19)(19)帶有帶有anyany或或allall謂詞的子查詢謂詞的子查詢比較子查詢通常用到操作符比較子查詢通常用到操作符ALL、ANY。其通用格式為：其通用格式為： ALL|ANY例：查詢其他班級中比應化

35、例：查詢其他班級中比應化041班某個學生年齡小的班某個學生年齡小的那些學生的姓名和年齡。那些學生的姓名和年齡。SELECT xm,nl FROM stu_info WHERE bh應化應化041 and nl any( select nl from stu_info where bh=應化應化041)第四章關系數(shù)據(jù)庫語言SQL(20)(20)帶有帶有existsexists謂詞的子查詢謂詞的子查詢EXISTS是測試子查詢是否有數(shù)據(jù)行返回，有則返回是測試子查詢是否有數(shù)據(jù)行返回，有則返回TRUE，否則返回否則返回FALSE。例：查詢選修了高等數(shù)學例：查詢選修了高等數(shù)學(090101)的學生姓名、性

36、別和班級。的學生姓名、性別和班級。SELECT xm,xbm,bh FROM stu_infoWHERE exists ( select * from xk where xh=stu_info.xh and kch=090101)例：查詢沒有選修高等數(shù)學例：查詢沒有選修高等數(shù)學(090101)的學生姓名、性別和班級。的學生姓名、性別和班級。SELECT xm,xbm,bh FROM stu_infoWHERE not exists(select * from xk where xh=stu_info.xh and kch=090101)第四章關系數(shù)據(jù)庫語言SQL4.2.7 聯(lián)合(集合)查詢 s

37、elect語句的查詢結(jié)果是元組的集合，所以多個語句的查詢結(jié)果是元組的集合，所以多個select語句的結(jié)果可進行集合操作。語句的結(jié)果可進行集合操作。集合操作主要包括：集合操作主要包括：并操作并操作UNION、交操作、交操作INTERSECT、差操作差操作MINUS標準標準SQL只提供只提供UNION，其語法格式如下：其語法格式如下： SELECT_1 UNION ALL SELECT_2 第四章關系數(shù)據(jù)庫語言SQL如果要將查詢的結(jié)果保存在當前數(shù)據(jù)庫新建的表如果要將查詢的結(jié)果保存在當前數(shù)據(jù)庫新建的表NEWSTU中，可以使用中，可以使用INTO子句：子句：SELECT * INTO NEWSTU F

38、ROM aUNION ALLSELECT * FROM b例：查詢編號為例：查詢編號為01的學院的學生及所有學院的本科生的學院的學生及所有學院的本科生SELECT * FROM stu_info WHERE xsh=01 UNIONSELECT * FROM stu_info WHERE pyccm=本科本科 第四章關系數(shù)據(jù)庫語言SQL4.2.8 使用系統(tǒng)內(nèi)置函數(shù)的查詢(21) (21) 數(shù)學函數(shù)數(shù)學函數(shù) ABS(ABS(數(shù)字型表達式數(shù)字型表達式) )、RAND(RAND(整型表達式整型表達式) )(22) (22) 字符串處理函數(shù)字符串處理函數(shù) ASCII(ASCII(字符型字符型) )：返

39、回表達式最左端字符的：返回表達式最左端字符的ASCIIASCII值值 CHAR(0-255CHAR(0-255之間的整數(shù)之間的整數(shù)) )：將：將ASCIIASCII轉(zhuǎn)換為字符轉(zhuǎn)換為字符 LEFT(LEFT(字符型，整型字符型，整型) )：返回從字符串左邊開始指定個數(shù)的字符：返回從字符串左邊開始指定個數(shù)的字符 LTRIM( LTRIM(字符型字符型) )：刪除字符串最左邊的空格，并返回字符串：刪除字符串最左邊的空格，并返回字符串 REPLACE( REPLACE(字符型字符型1 1，字符型，字符型2 2，字符型，字符型3)3)：用：用3 3替換替換1 1中包含的中包含的2 2，并返回替，并返回替

40、換后的表達式換后的表達式 SUBSTRING(express,start,length) SUBSTRING(express,start,length)：返回返回expressexpress中指定的部分數(shù)據(jù)中指定的部分數(shù)據(jù) STR(float_exp ,length ,decimal) STR(float_exp ,length ,decimal)：將數(shù)字數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)將數(shù)字數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)第四章關系數(shù)據(jù)庫語言SQL(23) (23) 系統(tǒng)函數(shù)系統(tǒng)函數(shù)getdate( ):getdate( ):返回當前的系統(tǒng)日期和時間返回當前的系統(tǒng)日期和時間year( )year( )、month( )

41、month( )、day( ):day( ):分別返回指定日期的年、月、日分別返回指定日期的年、月、日 cast(cast(表達式表達式 AS AS 數(shù)據(jù)類型數(shù)據(jù)類型) )：將表達式的值轉(zhuǎn)換為數(shù)據(jù)類型參：將表達式的值轉(zhuǎn)換為數(shù)據(jù)類型參數(shù)所指定的類型。數(shù)所指定的類型。casecase函數(shù)：一種是簡單的函數(shù)：一種是簡單的CASECASE函數(shù)，另一種是搜索型的函數(shù)，另一種是搜索型的CASECASE函函數(shù)。數(shù)。簡單簡單CASECASE語法格式：語法格式：CASE CASE 輸入表達式輸入表達式 WHEN WHEN 比較表達式比較表達式 THEN THEN 結(jié)果表達式結(jié)果表達式 ELSE ELSE 表達式

42、表達式ENDEND搜索搜索CASECASE語法格式：語法格式：CASECASE WHEN WHEN 條件條件1 1 THEN THEN 表達式表達式1 1 WHEN WHEN 條件條件2 2 THEN THEN 表達式表達式2 2 ELSE ELSE 表達式表達式ENDEND第四章關系數(shù)據(jù)庫語言SQL例例：在在STU_INFOSTU_INFO中查詢中查詢0303學院學生的學號、姓名、性別，并將性別分別轉(zhuǎn)換成學院學生的學號、姓名、性別，并將性別分別轉(zhuǎn)換成“男男生生”、“女生女生”。SELECT XH,XM,XBM,XBM=SELECT XH,XM,XBM,XBM= CASE CASE 男男 WH

43、EN XBM THEN WHEN XBM THEN 男生男生 ELSE ELSE 女生女生 ENDENDFROM STU_INFOFROM STU_INFOWHERE XSH=03WHERE XSH=03例：查詢成績表例：查詢成績表CSCS中選修了中選修了“數(shù)據(jù)庫技術及應用數(shù)據(jù)庫技術及應用”課程的學生的學號、姓名、課程的學生的學號、姓名、5 5分制成分制成績績SELECT SELECT 學號學號, ,姓名姓名, ,5 5分制成績分制成績= = CASECASE WHEN WHEN 成績成績60 =60 =60 ANDAND成績成績70 =70 =70 ANDAND成績成績80 =80 =80

44、ANDAND成績成績90 =90 =90 THEN THEN 優(yōu)秀優(yōu)秀 ENDENDFROM CS FROM CS 第四章關系數(shù)據(jù)庫語言SQL4.3 數(shù)據(jù)更新 4.3.1 插入數(shù)據(jù)插入數(shù)據(jù) 4.3.2 修改數(shù)據(jù)修改數(shù)據(jù) 4.3.3 刪除數(shù)據(jù)刪除數(shù)據(jù)第四章關系數(shù)據(jù)庫語言SQL4.2.1 插入數(shù)據(jù)n語句格式語句格式INSERTINTO (， )VALUES ( ， )n功能功能 將新元組插入指定表中。將新元組插入指定表中。(1) (1) 插入單個元組插入單個元組例：例：INSERT INTO stu_info (xh,xm,xbm) VALUES (2005090209, 王東方王東方, 男男)如

45、果基本表只有上述三個屬性，則該句等價于：如果基本表只有上述三個屬性，則該句等價于： INSERT INTO stu_info VALUES (2005090209, 王東方王東方, 男男)第四章關系數(shù)據(jù)庫語言SQL(2) (2) 插入子查詢結(jié)果插入子查詢結(jié)果例：在例：在STU_INFO表中查詢出應化表中查詢出應化041班的學生情況并存放在班的學生情況并存放在YINGHUA表中。表中。 INSERT INTO yinghua SELECT xh,xm,xbm,csrq FROM stu_info WHERE bh=應化應化041 n語句格式語句格式 INSERT INTO ( ， ) 子查詢子查

46、詢n功能功能: 將子查詢結(jié)果插入指定表中，用以插入批量記錄將子查詢結(jié)果插入指定表中，用以插入批量記錄第四章關系數(shù)據(jù)庫語言SQL4.3.2 修改數(shù)據(jù) 語句格式語句格式UPDATE SET =，=,WHERE 功能功能修改指定表中滿足修改指定表中滿足WHERE子句條件的元組子句條件的元組第四章關系數(shù)據(jù)庫語言SQL(3) (3) 修改一個元組的值修改一個元組的值 UPDATE stu_info SET bh=材材0169 WHERE xh=2005090209注意：對批量數(shù)據(jù)的修改注意：對批量數(shù)據(jù)的修改，一定把將要修改的記錄先查一定把將要修改的記錄先查詢出來，確認無誤后再修改。詢出來，確認無誤后再修

47、改。(4) (4) 修改多個元組的值修改多個元組的值 UPDATE gcourse SET kcxf=2 (5) (5) 帶子查詢的修改語句帶子查詢的修改語句 將選修了高數(shù)的學生的成績加將選修了高數(shù)的學生的成績加10分分 UPDATE xk SET kscj=kscj+10 WHERE kch=(select kch from gcourse where km=高等數(shù)學高等數(shù)學)第四章關系數(shù)據(jù)庫語言SQL4.3.3 刪除數(shù)據(jù) 語法格式語法格式 DELETE FROM WHERE 功能功能w 刪除指定表中滿足刪除指定表中滿足WHERE子句條件的元組子句條件的元組 WHERE子句子句w 指定要刪除

48、的元組指定要刪除的元組w 缺省表示要刪除表中的所有元組缺省表示要刪除表中的所有元組第四章關系數(shù)據(jù)庫語言SQL(6) (6) 刪除一個元組的值刪除一個元組的值 DELETE FROM stu_info WHERE xh=2006050010(7) (7) 刪除多個元組的值刪除多個元組的值 DELETE FROM stu_info WHERE bh=應應0203 刪除表中所有數(shù)據(jù)刪除表中所有數(shù)據(jù) DELETE FROM stu_info 第四章關系數(shù)據(jù)庫語言SQL(8) 帶子查詢的刪除語句帶子查詢的刪除語句 將信息學院學生的成績?nèi)縿h除將信息學院學生的成績?nèi)縿h除DELETE FROM xk WH

49、ERE 12= ( SELECT xsh FROM stu_info WHERE stu_info.xh=xk.xh )注意：在日常工作中，操作刪除語句，一定要先查詢要刪除的注意：在日常工作中，操作刪除語句，一定要先查詢要刪除的數(shù)據(jù)，確認無誤后再刪除。數(shù)據(jù)，確認無誤后再刪除。SELECT * FROM xk WHERE 12= ( select xsh from stu_info where stu_info.xh=xk.xh )第四章關系數(shù)據(jù)庫語言SQL4.4 數(shù)據(jù)定義 4.4.1 定義基本表定義基本表 4.4.2 修改基本表修改基本表 4.4.3 刪除基本表刪除基本表 4.4.4 視圖視圖

50、 4.4.5 索引索引第四章關系數(shù)據(jù)庫語言SQL4.4 數(shù)據(jù)定義 SQL的數(shù)據(jù)定義語句的數(shù)據(jù)定義語句操作對象操作對象操作方式操作方式創(chuàng)建創(chuàng)建刪除刪除修改修改表表視圖視圖索引索引第四章關系數(shù)據(jù)庫語言SQL4.4.1 定義基本表語句格式：語句格式： CREATE TABLE ( ， ， ) ：所要定義的基本表的名字：所要定義的基本表的名字 ：組成該表的各個屬性（列）：組成該表的各個屬性（列） ：涉及相應屬性列的完整性約：涉及相應屬性列的完整性約束條件束條件 ：涉及一個或多個屬性列的完：涉及一個或多個屬性列的完整性約束條件整性約束條件 第四章關系數(shù)據(jù)庫語言SQL例例：建立一個學生表建立一個學生表St

51、udent，它由學號它由學號Sno、姓名姓名Sname、性別性別Ssex、年齡年齡Sage、所在系所在系Sdept五個屬性組成。其中學號不能為空，值是唯一的，五個屬性組成。其中學號不能為空，值是唯一的，并且姓名取值也唯一。并且姓名取值也唯一。 CREATE TABLE Student ( Sno VARCHAR(5) NOT NULL UNIQUE， Sname VARCHAR(20) UNIQUE， Ssex VARCHAR(1) ， Sage INT， Sdept VARCHAR(15)SnoSnameSsex SageSdept字符型字符型長度為長度為5不能為不能為空值取值唯一空值取值唯

52、一字符型字符型長度為長度為20字符型字符型長度為長度為1整數(shù)整數(shù)字符型字符型長度為長度為15第四章關系數(shù)據(jù)庫語言SQL 常用完整性約束常用完整性約束 主碼約束：主碼約束： PRIMARY KEY 唯一性約束：唯一性約束：UNIQUE 非空值約束：非空值約束：NOT NULL 默認值約束：默認值約束：DEFAULT 檢查約束：檢查約束：CHECK 參照完整性約束：參照完整性約束：FOREIGN KEYREFERENCE例：建立一個例：建立一個“學生選課學生選課”表表SC，它由學號它由學號Sno、課程號課程號Cno，修課成績修課成績Grade組成，其中組成，其中(Sno, Cno)為主碼。為主碼。

53、CREATE TABLE SC( Sno VARCHAR(5) , Cno VARCHAR(3) , Grade int, Primary key (Sno, Cno) )第四章關系數(shù)據(jù)庫語言SQL例：創(chuàng)建表例：創(chuàng)建表n_jobsCREATE TABLE n_jobs( job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, job_desc varchar(50) NOT NULL DEFAULT (新部門新部門), min_lvl tinyint NOT NULL CHECK (min_lvl = 12), max_lvl tinyint

54、NOT NULL CHECK (max_lvl = 250)例：使用表達式例：使用表達式(最低分最低分+最高分最高分)/2)生成中等分計算列。生成中等分計算列。CREATE TABLE 得分得分 ( 最低分最低分 int, 最高分最高分 int, 中等分中等分 AS (最低分最低分+最高分最高分)/2)第四章關系數(shù)據(jù)庫語言SQL4.4.2 修改基本表語法格式：語法格式：ALTER TABLE ALTER COLUMN ADD 完整性約束完整性約束 DROP ：要修改的基本表：要修改的基本表 ALTER COLUMN 子句：要更改的列，要更改的列不能子句：要更改的列，要更改的列不能是數(shù)據(jù)類型為是

55、數(shù)據(jù)類型為text,image,ntext或或timestamp等列等列 ADD子句：增加新列和新的完整性約束條件子句：增加新列和新的完整性約束條件 DROP子句：刪除指定的完整性約束條件或列名子句：刪除指定的完整性約束條件或列名第四章關系數(shù)據(jù)庫語言SQL1、添加列、添加列例：向?qū)W生表中添加入學時間列，其數(shù)據(jù)類型為日期型例：向?qū)W生表中添加入學時間列，其數(shù)據(jù)類型為日期型 ALTER TABLE stu_info ADD rxsj DATETIME可能破壞可能破壞已有數(shù)據(jù)已有數(shù)據(jù)2、修改列修改列例：將教師表的例：將教師表的xb列改為列改為smallint數(shù)據(jù)類型數(shù)據(jù)類型 ALTER TABLE g

56、tech ALTER COLUMN xb smallint3、刪除列或約束、刪除列或約束例：將數(shù)據(jù)庫表例：將數(shù)據(jù)庫表gtech中的中的xb列刪除列刪除 ALTER TABLE gtech DROP COLUMN xb例：刪除學生姓名必須取唯一值的約束例：刪除學生姓名必須取唯一值的約束 ALTER TABLE stu_info DROP UNIQUE(xm)第四章關系數(shù)據(jù)庫語言SQL4.4.3 刪除基本表語法格式：語法格式： DROP TABLE 基本表定義一旦刪除，表中的數(shù)據(jù)、表上的索基本表定義一旦刪除，表中的數(shù)據(jù)、表上的索引和視圖都將自動被刪除引和視圖都將自動被刪除 刪除基本表時，系統(tǒng)會從數(shù)

57、據(jù)字典中刪去有關刪除基本表時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關該基本表及其索引的描述。該基本表及其索引的描述。 例：刪除例：刪除EDU_D庫中的表庫中的表stu_info DROP TABLEstu_info第四章關系數(shù)據(jù)庫語言SQL4.4.4 視圖視圖是關系數(shù)據(jù)庫系統(tǒng)提供給用戶以多種角度觀察數(shù)據(jù)庫中視圖是關系數(shù)據(jù)庫系統(tǒng)提供給用戶以多種角度觀察數(shù)據(jù)庫中數(shù)據(jù)的重要機制。數(shù)據(jù)的重要機制。視圖是從一個或多個基本表（或視圖）導出的表，是個虛表。視圖是從一個或多個基本表（或視圖）導出的表，是個虛表。數(shù)據(jù)庫只存放視圖的定義，不存放視圖對應的數(shù)據(jù)，這些數(shù)數(shù)據(jù)庫只存放視圖的定義，不存放視圖對應的數(shù)據(jù)，這些數(shù)據(jù)仍存放

58、在原來的基本表中。所以，視圖的數(shù)據(jù)跟隨基本表據(jù)仍存放在原來的基本表中。所以，視圖的數(shù)據(jù)跟隨基本表的數(shù)據(jù)而變化。的數(shù)據(jù)而變化。視圖一旦被定義，就可以被查詢、刪除，修改及再定義一個視圖一旦被定義，就可以被查詢、刪除，修改及再定義一個新的視圖等。新的視圖等。 視圖包含一系列帶有名稱的列和行數(shù)據(jù)。這些數(shù)據(jù)來自定義視圖包含一系列帶有名稱的列和行數(shù)據(jù)。這些數(shù)據(jù)來自定義視圖的查詢所引用的基表中，并且在引用視圖時動態(tài)生成。視圖的查詢所引用的基表中，并且在引用視圖時動態(tài)生成。第四章關系數(shù)據(jù)庫語言SQL1、定義視圖、定義視圖(1) 用企業(yè)管理器建立視圖用企業(yè)管理器建立視圖 請同學們上機自己學習建立視圖的方法。請同

59、學們上機自己學習建立視圖的方法。(2) 用用SQL語句建立視圖語句建立視圖 CREATE VIEW 視圖名視圖名 AS WITH CHECK OPTION其中其中WITH CHECK OPTION表示對視圖進行表示對視圖進行UPDATE、INSERT和和DELETE操作時要保證更新、插入或刪除的行滿足操作時要保證更新、插入或刪除的行滿足視圖定義中的條件表達式視圖定義中的條件表達式第四章關系數(shù)據(jù)庫語言SQLA、選擇列定義視圖選擇列定義視圖 可定義一個視圖，該視圖由表的部分列組成。下例中，視可定義一個視圖，該視圖由表的部分列組成。下例中，視圖圖MyView1包含了包含了employees表中的三個

60、列。表中的三個列。 CREATE VIEW MyView1 AS SELECT lname, fname FROM employee定義了視圖后，可以和表一樣，使用定義了視圖后，可以和表一樣，使用SELECT語句訪問它。語句訪問它。例如：例如： SELECT * FROM MyView1第四章關系數(shù)據(jù)庫語言SQLB、基于列的表達式定義視圖基于列的表達式定義視圖 在定義視圖時，除了使用基礎表的列外，還可使用基本表在定義視圖時，除了使用基礎表的列外，還可使用基本表的列的表達式，生成自己的列。的列的表達式，生成自己的列。 下例中，視圖下例中，視圖MyView2的第四列即由表達式計算而來。的第四列即由