第四章 結(jié)構(gòu)化查詢語言SQL(2012)

1、數(shù)理學(xué)院 馮莉第四章 結(jié)構(gòu)化查詢語言SQL12SQL概述數(shù)據(jù)定義語句3 查詢54數(shù)據(jù)操縱視圖4.1 SQL概述概述n SQL(Structured Query Language) SQL(Structured Query Language)結(jié)構(gòu)化查詢語結(jié)構(gòu)化查詢語言，言，19741974年年BoyceBoyce和和ChamberlinChamberlin提出，首先在提出，首先在IBMIBM公公司的關(guān)系數(shù)據(jù)庫系統(tǒng)司的關(guān)系數(shù)據(jù)庫系統(tǒng)System RSystem R上實現(xiàn)。上實現(xiàn)。 特點：功能豐富、使用方便、靈活、語言簡潔易特點：功能豐富、使用方便、靈活、語言簡潔易學(xué)，學(xué)，應(yīng)用系統(tǒng)范圍廣，統(tǒng)一標(biāo)準(zhǔn)應(yīng)

2、用系統(tǒng)范圍廣，統(tǒng)一標(biāo)準(zhǔn)。19861986年，年，ANSIANSI數(shù)據(jù)庫委員會數(shù)據(jù)庫委員會X3H2X3H2批準(zhǔn)了批準(zhǔn)了SQLSQL作為作為數(shù)據(jù)庫語言的美國標(biāo)準(zhǔn)，數(shù)據(jù)庫語言的美國標(biāo)準(zhǔn)，ISOISO隨后也通過這一標(biāo)準(zhǔn)，隨后也通過這一標(biāo)準(zhǔn)，使得使得SQLSQL成為數(shù)據(jù)庫領(lǐng)域的主流語言成為數(shù)據(jù)庫領(lǐng)域的主流語言。4.1.1 SQL語言的發(fā)展語言的發(fā)展n有關(guān)標(biāo)準(zhǔn)有關(guān)標(biāo)準(zhǔn): :n SQL/86 SQL/86n SQL/89 SQL/89n SQL/92 SQL/92n SQL99 SQL99n SQL2003 SQL2003n SQL2006 SQL2006n 現(xiàn)狀現(xiàn)狀: :大部分大部分DBMSDBMS產(chǎn)品都

3、支持產(chǎn)品都支持SQLSQL，成為數(shù)據(jù)庫的標(biāo)，成為數(shù)據(jù)庫的標(biāo)準(zhǔn)語言，是一個通用的、功能極強的關(guān)系數(shù)據(jù)庫語言。準(zhǔn)語言，是一個通用的、功能極強的關(guān)系數(shù)據(jù)庫語言。4.1.1 SQL語言的發(fā)展語言的發(fā)展4.1.2 SQL語言的語言的特點特點一一. . 綜合統(tǒng)一綜合統(tǒng)一 集數(shù)據(jù)定義語言集數(shù)據(jù)定義語言DDLDDL、數(shù)據(jù)操縱語言、數(shù)據(jù)操縱語言DMLDML和數(shù)據(jù)和數(shù)據(jù)控制語言控制語言DCLDCL于一體，語言風(fēng)格統(tǒng)一，可以獨立完于一體，語言風(fēng)格統(tǒng)一，可以獨立完成數(shù)據(jù)庫生命周期中的全部活動。成數(shù)據(jù)庫生命周期中的全部活動。二二. . 高度非過程化高度非過程化 用戶只需提出用戶只需提出“做什么做什么”，至于，至于“怎么

4、做怎么做”由由DBMSDBMS解決；用戶無需了解存取路徑，存取路徑的選解決；用戶無需了解存取路徑，存取路徑的選擇以及擇以及SQLSQL語句的操作過程由系統(tǒng)自動完成。語句的操作過程由系統(tǒng)自動完成。 數(shù)據(jù)定義（數(shù)據(jù)定義（DDLDDL） 定義、刪除、修改關(guān)系模式定義、刪除、修改關(guān)系模式 定義、刪除視圖（定義、刪除視圖（ViewView） 定義、刪除索引（定義、刪除索引（IndexIndex） 數(shù)據(jù)操縱（數(shù)據(jù)操縱（DMLDML） 數(shù)據(jù)查詢數(shù)據(jù)查詢 數(shù)據(jù)增、刪、改數(shù)據(jù)增、刪、改 數(shù)據(jù)控制（數(shù)據(jù)控制（DCLDCL） 用戶訪問權(quán)限的授予、收回用戶訪問權(quán)限的授予、收回4.1.2 SQL語言的特點語言的特點三三

5、. . 面向集合的操作方式面向集合的操作方式 每一個每一個SQLSQL的操作對象是一個或多個關(guān)系，操的操作對象是一個或多個關(guān)系，操作的結(jié)果也是關(guān)系。作的結(jié)果也是關(guān)系。四四. . 以同一種語法結(jié)構(gòu)提供兩種使用方式以同一種語法結(jié)構(gòu)提供兩種使用方式 即可獨立使用，又可嵌入到高級語言中使用，即可獨立使用，又可嵌入到高級語言中使用，具有自主型和嵌入型兩種特點，且在兩種使用方具有自主型和嵌入型兩種特點，且在兩種使用方式下，式下，SQLSQL語言的語法結(jié)構(gòu)基本一致。語言的語法結(jié)構(gòu)基本一致。4.1.2 SQL語言的特點語言的特點五五. . 語言簡捷、易學(xué)易用語言簡捷、易學(xué)易用 核心功能只有核心功能只有9 9個

6、動詞，個動詞，語法簡單，接近英語。語法簡單，接近英語。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，REVOKE4.1.2 SQL語言的特點語言的特點4.1.3 SQL語言的語言的基本概念基本概念 支持關(guān)系數(shù)據(jù)庫的三級模式結(jié)構(gòu)支持關(guān)系數(shù)據(jù)庫的三級模式結(jié)構(gòu)SQL用戶用戶基本表基本表1視圖視圖1視圖視圖2基本表基本表2基本表基本表3基本表基本表4存儲文件存儲文件1存儲文件存儲文件2外模式外模式模式模式內(nèi)模式內(nèi)模式4.2 數(shù)據(jù)定義語句數(shù)據(jù)定義語句 SQL的數(shù)據(jù)

7、定義語句的數(shù)據(jù)定義語句操作操作對象對象操作方式操作方式創(chuàng)建創(chuàng)建刪除刪除修改修改表表CREAT TABLEDROP TABLEALTER TABLE視圖視圖CREAT VIEWDROP VIEW索引索引CREAT INDEXDROP INDEX注：注：SQLSQL通常不提供修改視圖定義和索引定義的操作。通常不提供修改視圖定義和索引定義的操作。補充補充 數(shù)據(jù)庫的定義數(shù)據(jù)庫的定義定義數(shù)據(jù)庫定義數(shù)據(jù)庫 CREATE DATABASE ON / LOG ON ( NAME=， FILENAME=， SIZE=， MAXSIZE=， FILEGROWTH= ) 補充補充 數(shù)據(jù)庫的定義數(shù)據(jù)庫的定義CREAT

8、E DATABASE Sample; 電力搶修工程數(shù)據(jù)庫的數(shù)據(jù)庫名為電力搶修工程數(shù)據(jù)庫的數(shù)據(jù)庫名為SampleSample，建立電力搶修工程數(shù)據(jù)庫的命令：，建立電力搶修工程數(shù)據(jù)庫的命令：刪除數(shù)據(jù)庫刪除數(shù)據(jù)庫 DROP DATABASE 例：例： drop database Sample;4.2.1 基本表的定義基本表的定義 CREATE TABLE (列級完整性約束條件列級完整性約束條件, 列級完整性約束條件列級完整性約束條件 ,表級完整性約束條件表級完整性約束條件);注：注：若完整性約束條件涉及多個屬性列，則必須定義在表級上，若完整性約束條件涉及多個屬性列，則必須定義在表級上，否則即可定義在

9、列級也可定義在表級。否則即可定義在列級也可定義在表級。創(chuàng)建表語句中創(chuàng)建表語句中常用完整性約束常用完整性約束、主碼約束：、主碼約束： PRIMARY KEYPRIMARY KEY、唯一性約束：、唯一性約束：UNIQUEUNIQUE、非空值約束：、非空值約束：NOT NULLNOT NULL、參照完整性約束、參照完整性約束: :FOREIGN KEY REFERENCES FOREIGN KEY REFERENCES 引用表名（引用列）引用表名（引用列）、檢查約束：、檢查約束：CHECK(CHECK(檢查表達式檢查表達式) ) 、默認(rèn)值約束：、默認(rèn)值約束：DEFAULTDEFAULT默認(rèn)值默認(rèn)值4

10、.2.1 基本表的定義基本表的定義 本章所有例子均來自電力搶修工程數(shù)據(jù)庫，本章所有例子均來自電力搶修工程數(shù)據(jù)庫，該數(shù)據(jù)庫包括三個表：該數(shù)據(jù)庫包括三個表：(1)(1)搶修工程計劃表：搶修工程計劃表：salvagingsalvaging（prj_num, prj_name, start_date, prj_num, prj_name, start_date, end_date, Prj_statusend_date, Prj_status） 其中其中prj_numprj_num為主碼為主碼 各屬性含義如下：各屬性含義如下： 工程項目編號（工程項目編號（prj_numprj_num） 工程項目名稱（

11、工程項目名稱（prj_nameprj_name） 開始日期（開始日期（start_datestart_date） 結(jié)束日期（結(jié)束日期（end_dateend_date） 是否按期完成（是否按期完成（Prj_statusPrj_status）4.2.1 基本表的定義基本表的定義(2)(2)配電物資庫存記錄表：配電物資庫存記錄表：stockstock（mat_num, mat_name, speci, warehouse, mat_num, mat_name, speci, warehouse, amount, unit, totalamount, unit, total） 其中其中mat_num

12、mat_num為主碼。為主碼。各屬性含義如下：各屬性含義如下： 物資編號（物資編號（mat_nummat_num） 物資名稱（物資名稱（mat_namemat_name） 規(guī)格（規(guī)格（specispeci） 倉庫名稱（倉庫名稱（warehousewarehouse） 數(shù)量（數(shù)量（amountamount） 單價（單價（unitunit） 總金額（總金額（total)total)4.2.1 基本表的定義基本表的定義(3)(3)配電物資領(lǐng)料出庫表：配電物資領(lǐng)料出庫表：out_stockout_stock（prj_num, mat_num, amount, get_date, prj_num, ma

13、t_num, amount, get_date, departmentdepartment） 主碼為（主碼為（prj_num, mat_numprj_num, mat_num）各屬性含義如下：各屬性含義如下： 工程項目編號（工程項目編號（prj_numprj_num） 物資編號（物資編號（mat_nummat_num） 領(lǐng)取數(shù)量（領(lǐng)取數(shù)量（amountamount） 領(lǐng)料日期（領(lǐng)料日期（get_dateget_date） 領(lǐng)料部門（領(lǐng)料部門（departmentdepartment）4.2.1 基本表的定義基本表的定義【例例4.14.1】建立建立 “搶修工程計劃表搶修工程計劃表”表表salva

14、gingsalvagingCREATE TABLE salvagingCREATE TABLE salvaging( prj_num char(8) PRIMARY KEY , ( prj_num char(8) PRIMARY KEY , / /* * 列級完整性約束，列級完整性約束，prj_numprj_num是主碼是主碼 * */ / prj_name varchar(50) , prj_name varchar(50) , start_date datetime , start_date datetime , end_date datetime , end_date datetime

15、, prj_status bit , prj_status bit ,);); 4.2.1 基本表的定義基本表的定義【例例4.24.2】建立一個建立一個“配電物資庫存記錄表配電物資庫存記錄表”表表stockstock。CREATE TABLE stockCREATE TABLE stock( mat_num char(8) PRIMARY KEY, ( mat_num char(8) PRIMARY KEY, mat_name varchar(50) NOT NULL, mat_name varchar(50) NOT NULL, speci varchar(20) NOT NULL, spe

16、ci varchar(20) NOT NULL, warehouse varchar(50) , warehouse varchar(50) , amount int , amount int , unit decimal(18, 2) , unit decimal(18, 2) , total AS (amount total AS (amount * * unit), unit), / /* * as as為自動計算字段，不能輸入值，表示：總金額為自動計算字段，不能輸入值，表示：總金額= =數(shù)量數(shù)量* *單單價價* */ /););4.2.1 基本表的定義基本表的定義【例例4.34.3】建

17、立一個建立一個“配電物資領(lǐng)料出庫表配電物資領(lǐng)料出庫表”表表out_stockout_stock。CREATE TABLE out_stockCREATE TABLE out_stock( prj_num char(8) ,( prj_num char(8) , mat_num char(8) , mat_num char(8) , amount int , amount int , get_date datetime , get_date datetime , department varchar(100) , department varchar(100) , PRIMARY KEY(prj

18、_num,mat_num), PRIMARY KEY(prj_num,mat_num), / /* * 主碼由兩個屬性構(gòu)成，必須作為表級完整性約束主碼由兩個屬性構(gòu)成，必須作為表級完整性約束 * */ / FOREIGN KEY (prj_num) REFERENCES salvaging(prj_num), FOREIGN KEY (prj_num) REFERENCES salvaging(prj_num), FOREIGN KEY (mat_num) REFERENCES stock(mat_num), FOREIGN KEY (mat_num) REFERENCES stock(mat_

19、num),););4.2.1 基本表的定義基本表的定義4.2.1 基本表的定義基本表的定義定義表的各個屬性時需要指明其數(shù)據(jù)類型及長度。要注意，定義表的各個屬性時需要指明其數(shù)據(jù)類型及長度。要注意，不同的不同的RDBMSRDBMS支持的數(shù)據(jù)類型不完全相同。支持的數(shù)據(jù)類型不完全相同。4.2.2 基本表的修改基本表的修改 ALTER TABLE ADD 完整性約束條件完整性約束條件DROP COLUMN ALTER COLUMN ；其中：其中： ADDADD增加新列和新的完整性約束條件；增加新列和新的完整性約束條件； DROPDROP刪除指定列或者完整性約束條件；刪除指定列或者完整性約束條件； ALT

20、ERALTER修改原有的列定義，包括修改列名和數(shù)據(jù)類型。修改原有的列定義，包括修改列名和數(shù)據(jù)類型。4.2.2 基本表的修改基本表的修改【例例4.44.4】向搶修工程計劃表向搶修工程計劃表salvagingsalvaging增加增加“工程項目負(fù)工程項目負(fù)責(zé)人責(zé)人”列，數(shù)據(jù)類型為字符型。列，數(shù)據(jù)類型為字符型。ALTER TABLE salvagingALTER TABLE salvaging ADD prj_director VARCHAR(10); ADD prj_director VARCHAR(10);【例例4.54.5】刪除搶修工程計劃表刪除搶修工程計劃表salvagingsalvagin

21、g中中“工程項工程項目負(fù)責(zé)人目負(fù)責(zé)人”的屬性列。的屬性列。ALTER TABLE salvagingALTER TABLE salvaging DROP COLUMN prj_director; DROP COLUMN prj_director; 【例例4.64.6】將配電物資領(lǐng)料出庫表將配電物資領(lǐng)料出庫表out_stockout_stock中領(lǐng)取數(shù)量中領(lǐng)取數(shù)量的數(shù)據(jù)類型由字符型改為正數(shù)。的數(shù)據(jù)類型由字符型改為正數(shù)。ALTER TABLE out_stockALTER TABLE out_stock ALTER COLUMN amount int; ALTER COLUMN amount in

22、t;注意：不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。注意：不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。注意：修改原有的列定義有可能會破壞已有數(shù)據(jù)。注意：修改原有的列定義有可能會破壞已有數(shù)據(jù)。4.2.3 基本表的刪除基本表的刪除 DROP TABLE 如：如：DROP TABLE out_stock;：基本表一旦刪除，基本表一旦刪除，則表中的數(shù)據(jù)，以及在其則表中的數(shù)據(jù)，以及在其基礎(chǔ)上建立的索引和視圖基礎(chǔ)上建立的索引和視圖都將自動刪除。都將自動刪除。注意：注意：基本表的刪除是有限制條件基本表的刪除是有限制條件的，欲刪除的基本表不能被其他表的，欲刪除的基本表不能被其他表的約束所引用

23、。如果存在這些依賴的約束所引用。如果存在這些依賴該表的對象，則此表不能被刪除。該表的對象，則此表不能被刪除。比如若執(zhí)行：比如若執(zhí)行：DROP TABLE stock;DROP TABLE stock;系統(tǒng)將會彈出如下圖的提示：系統(tǒng)將會彈出如下圖的提示：4.3 數(shù)據(jù)查詢數(shù)據(jù)查詢 查詢是對現(xiàn)有的基本表和視圖進行數(shù)據(jù)查詢，并不改查詢是對現(xiàn)有的基本表和視圖進行數(shù)據(jù)查詢，并不改變數(shù)據(jù)本身，是數(shù)據(jù)庫的核心操作。變數(shù)據(jù)本身，是數(shù)據(jù)庫的核心操作。 一般格式：一般格式：SELECT ALL|DISTINCTFROM WHERE GROUP BY HAVING ORDER BY ASC|DESC;4.3 數(shù)據(jù)查詢

24、數(shù)據(jù)查詢執(zhí)行過程：執(zhí)行過程：(5)ORDER(5)ORDER子句對輸出的目標(biāo)表進行排序子句對輸出的目標(biāo)表進行排序, ,可選擇升序可選擇升序或降序或降序. .(4)(4)按按SELECTSELECT子句中給出的列名或列表達式求值輸出子句中給出的列名或列表達式求值輸出; ;(2)(2)選取滿足選取滿足WHEREWHERE子句中給出的條件表達式的元組子句中給出的條件表達式的元組; ;(1)(1)讀取讀取FROMFROM子句中基本表、視圖的數(shù)據(jù)子句中基本表、視圖的數(shù)據(jù), ,執(zhí)行笛卡執(zhí)行笛卡兒積操作兒積操作; ;(3)(3)按按GROUPGROUP子句中指定列的值分組子句中指定列的值分組, ,同時提取滿

25、足同時提取滿足HAVINGHAVING子句中組條件表達式的那些組子句中組條件表達式的那些組; ;4.3 數(shù)據(jù)查詢數(shù)據(jù)查詢 SelectSelect語句的含義語句的含義 對對 From From 子句中的各關(guān)系，作笛卡兒積子句中的各關(guān)系，作笛卡兒積( ();); 對對 Where Where 子句中的邏輯表達式進行選擇（子句中的邏輯表達式進行選擇（）運算，找出符合條件的元組運算，找出符合條件的元組; ; Select Select 子句中的屬性列表，對上述結(jié)果作投子句中的屬性列表，對上述結(jié)果作投影（影（）操作）操作. . 結(jié)果集結(jié)果集 查詢操作的對象是關(guān)系，結(jié)果還是一個關(guān)系，查詢操作的對象是關(guān)系

26、，結(jié)果還是一個關(guān)系，是一個結(jié)果集，而且是一個動態(tài)數(shù)據(jù)集。是一個結(jié)果集，而且是一個動態(tài)數(shù)據(jù)集。等價于：等價于：a a1 1 , a , a2 2 , , , a , an n( ( p p(R(R1 1 R R2 2 R Rm m) 我們以電力搶修工程數(shù)據(jù)庫我們以電力搶修工程數(shù)據(jù)庫 為例說明為例說明SELECTSELECT語句的各種用法語句的各種用法. . (a) stock表4.3 數(shù)據(jù)查詢數(shù)據(jù)查詢(b) salvaging表4.3 數(shù)據(jù)查詢數(shù)據(jù)查詢(c) out_stock表4.3 數(shù)據(jù)查詢數(shù)據(jù)查詢4.3.1 4.3.1 單表查詢單表查詢【例例4.84.8】查詢所有配電物資的物資編號、物資名

27、稱、查詢所有配電物資的物資編號、物資名稱、規(guī)格。規(guī)格。 SELECT mat_num, mat_name, speciSELECT mat_num, mat_name, speci FROM stock; FROM stock;選擇表中若干列選擇表中若干列選擇表中的全部列或部分列，即關(guān)系代數(shù)的投影運算。選擇表中的全部列或部分列，即關(guān)系代數(shù)的投影運算。1) 查詢指定的列查詢指定的列【例例4.94.9】查詢所有配電物資的物資名稱、物資編號、查詢所有配電物資的物資名稱、物資編號、規(guī)格和所在倉庫名稱。規(guī)格和所在倉庫名稱。 SELECT mat_name, mat_num, speci, warehou

28、seSELECT mat_name, mat_num, speci, warehouse FROM stock; FROM stock;注：注： 中各個列的先后順序可以與表中的順序不一致，用戶可以中各個列的先后順序可以與表中的順序不一致，用戶可以根據(jù)應(yīng)用的需要改變列的顯示順序。根據(jù)應(yīng)用的需要改變列的顯示順序?！纠?.104.10】查詢所有配電物資的記錄。查詢所有配電物資的記錄。 SELECT SELECT * * FROM stock FROM stock等價于：等價于：SELECT mat_num, mat_name, speci, warehouse, SELECT mat_num, m

29、at_name, speci, warehouse, amount, unit, totalamount, unit, totalFROM stockFROM stock4.3.1 單表查詢單表查詢2) 查詢?nèi)苛胁樵內(nèi)苛凶⒆ⅲ河茫河? *，則列的顯示順序與基本表一致。，則列的顯示順序與基本表一致。 【例例4.114.11】查詢所有搶修工程的搶修天數(shù)。查詢所有搶修工程的搶修天數(shù)。SELECT prj_name, start_date, end_date, SELECT prj_name, start_date, end_date, datediff(day, start_date,end_d

30、ate )datediff(day, start_date,end_date )FROM salvaging;FROM salvaging;4.3.1 單表查詢單表查詢3) 查詢經(jīng)過計算的值查詢經(jīng)過計算的值注注： 不僅不僅可以是屬性列，也可以可以是屬性列，也可以是算術(shù)表達式，還可以是算術(shù)表達式，還可以是字符串常量、函數(shù)等。是字符串常量、函數(shù)等。 SQL Server 2005 SQL Server 2005提供時間函數(shù)，可以對日期和時間輸入提供時間函數(shù)，可以對日期和時間輸入值執(zhí)行操作，并返回一個字符串、數(shù)字或日期和時間值，如值執(zhí)行操作，并返回一個字符串、數(shù)字或日期和時間值，如下表所示下表所示

31、函數(shù)函數(shù)功能功能getdate()getdate()返回系統(tǒng)當(dāng)前的日期和時間返回系統(tǒng)當(dāng)前的日期和時間year(date)year(date)返回一個整數(shù)，表示指定日期中的年份返回一個整數(shù)，表示指定日期中的年份month(date)month(date)返回一個整數(shù)，表示指定日期中的月份返回一個整數(shù)，表示指定日期中的月份day(date)day(date)返回一個整數(shù)，表示指定日期中的天數(shù)返回一個整數(shù)，表示指定日期中的天數(shù)datediff(datepart,dadatediff(datepart,date1,date2)te1,date2)返回返回date1date1和和date2date2的時

32、間間隔，其單位由的時間間隔，其單位由datepartdatepart參數(shù)指定參數(shù)指定4.3.1 單表查詢單表查詢【例例4.124.12】查詢所有搶修工程的搶修天數(shù)，并在實際搶查詢所有搶修工程的搶修天數(shù)，并在實際搶修天數(shù)列前加入一個列，此列的每行數(shù)據(jù)均為修天數(shù)列前加入一個列，此列的每行數(shù)據(jù)均為搶修搶修天數(shù)天數(shù)常量值。常量值。SELECT prj_name,SELECT prj_name,搶修天數(shù)搶修天數(shù),datediff(day, ,datediff(day, start_date,end_date )start_date,end_date )FROM salvaging;FROM salvag

33、ing; 4.3.1 單表查詢單表查詢用戶可以通過指定別名來改變查詢結(jié)果的列標(biāo)題，語法用戶可以通過指定別名來改變查詢結(jié)果的列標(biāo)題，語法格式為：格式為：列名列名| |表達式表達式 AS AS 列標(biāo)題列標(biāo)題 或：或：列標(biāo)題列標(biāo)題= =列名列名| |表達式表達式SELECT prj_name SELECT prj_name 項目名稱項目名稱, start_date , start_date 開始日期開始日期, , end_date end_date 結(jié)束日期結(jié)束日期, datediff(day, , datediff(day, start_date,end_date ) start_date,end

34、_date ) 搶修天數(shù)搶修天數(shù)FROM salvaging;FROM salvaging;4.3.1 單表查詢單表查詢【例例4.134.13】在配電物資庫存記錄表中查詢出所有的在配電物資庫存記錄表中查詢出所有的倉庫名稱，并去掉結(jié)果表中的重復(fù)行。倉庫名稱，并去掉結(jié)果表中的重復(fù)行。SELECT warehouseSELECT warehouseFROM stock;FROM stock;4.3.1 單表查詢單表查詢2. 選擇表中若干元組選擇表中若干元組1）消除取值重復(fù)的行）消除取值重復(fù)的行保留表中取值重復(fù)行保留表中取值重復(fù)行 刪除表中取值重復(fù)行刪除表中取值重復(fù)行 SELECT DISTINCT

35、warehouseSELECT DISTINCT warehouseFROM stock;FROM stock;4.3.1 單表查詢單表查詢查詢條件查詢條件 比較比較確定范圍確定范圍確定集合確定集合字符匹配字符匹配 空值空值多重條件多重條件謂詞謂詞=,=,=,!=,!,!, NOT+上述比較運算上述比較運算BETWEEN AND, NOT BETWEEN ANDIN, NOT INLIKE, NOT LIKEIS NULL, IS NOT NULLAND, OR,NOT2） 查詢滿足條件的元組（查詢滿足條件的元組（WHERE）【例例4.144.14】查詢供電局查詢供電局1#1#倉庫存放的所有物

36、資編號、倉庫存放的所有物資編號、物資名稱、規(guī)格以及數(shù)量。物資名稱、規(guī)格以及數(shù)量。SELECT mat_num, mat_name, speci,amountSELECT mat_num, mat_name, speci,amountFROM stockFROM stockWHERE warehouse =WHERE warehouse =供電局供電局1#1#倉庫倉庫 4.3.1 單表查詢單表查詢（1）比較大?。┍容^大小【例例4.154.15】查詢所有單價小于查詢所有單價小于8080的物資名稱、數(shù)量的物資名稱、數(shù)量及其單價。及其單價。 SELECT mat_name, amount,unit S

37、ELECT mat_name, amount,unit FROM stock FROM stock WHERE unit 80; WHERE unit =80; WHERE NOT unit =80;4.3.1 單表查詢單表查詢【例例4.164.16】查詢單價在查詢單價在5010050100之間的物資名稱、數(shù)之間的物資名稱、數(shù)量及其單價。量及其單價。 SELECT mat_name, amount, unitSELECT mat_name, amount, unit FROM stock FROM stock WHERE unit BETWEEN 50 AND 100; WHERE unit

38、BETWEEN 50 AND 100;此句等價于：此句等價于： SELECT mat_name, amount, unitSELECT mat_name, amount, unit FROM stock FROM stock WHERE unit =50 AND unit =50 AND unit =100; 4.3.1 單表查詢單表查詢（2）確定范圍）確定范圍(between and, not between and) 【例例4.174.17】查詢單價不在查詢單價不在5010050100之間的物資名稱、之間的物資名稱、數(shù)量及其單價。數(shù)量及其單價。 SELECT mat_name, amoun

39、t, unitSELECT mat_name, amount, unit FROM stock FROM stock WHERE unit NOT BETWEEN 50 AND 100; WHERE unit NOT BETWEEN 50 AND 100;此句等價于：此句等價于： SELECT mat_name, amount, unit SELECT mat_name, amount, unit FROM stock FROM stock WHERE unit100; WHERE unit100;4.3.1 單表查詢單表查詢【例例4.184.18】查詢存放在供電局查詢存放在供電局1#1#倉庫

40、和供電局倉庫和供電局2#2#倉庫的倉庫的物資名稱、規(guī)格及其數(shù)量。物資名稱、規(guī)格及其數(shù)量。SELECT mat_name, speci, amountSELECT mat_name, speci, amountFROM stockFROM stockWHERE warehouse IN(WHERE warehouse IN(供電局供電局1#1#倉庫倉庫,供電局供電局2#2#倉庫倉庫)此句等價于：此句等價于：SELECT mat_name, speci, amountSELECT mat_name, speci, amountFROM stockFROM stockWHERE warehouse

41、=WHERE warehouse =供電局供電局1#1#倉庫倉庫 OR warehouse = OR warehouse =供電局供電局2#2#倉庫倉庫 4.3.1 單表查詢單表查詢（3）確定集合）確定集合(in, not in)【例例4.194.19】查詢既沒有存放在供電局查詢既沒有存放在供電局1#1#倉庫，也沒有倉庫，也沒有存放在供電局存放在供電局2#2#倉庫的物資名稱、規(guī)格及其數(shù)量。倉庫的物資名稱、規(guī)格及其數(shù)量。SELECT mat_name, speci, amountSELECT mat_name, speci, amountFROM stockFROM stockWHERE war

42、ehouse NOT IN (WHERE warehouse NOT IN (供電局供電局1#1#倉庫倉庫,供電局供電局2#2#倉庫倉庫)此句等價于：此句等價于：SELECT mat_name, speci, amountSELECT mat_name, speci, amountFROM stockFROM stockWHERE warehouse !=WHERE warehouse !=供電局供電局1#1#倉庫倉庫 AND warehouse != AND warehouse !=供電局供電局2#2#倉庫倉庫 4.3.1 單表查詢單表查詢4.3.1 單表查詢單表查詢（4）字符匹配）字符匹配

43、(like,not like，模糊查詢，模糊查詢)n找出滿足給定匹配條件的字符串，其格式為：找出滿足給定匹配條件的字符串，其格式為：NOT LIKE ESCAPE 匹配規(guī)則：匹配規(guī)則： “%” ：代表任意長度（：代表任意長度（0個或多個）字符串。個或多個）字符串。 “_”：代表任意單個字符。：代表任意單個字符。 escape ：定義換碼字符，以去掉特殊字符的特定含義，：定義換碼字符，以去掉特殊字符的特定含義，使其被作為普通字符看待。如使其被作為普通字符看待。如escape ，定義了，定義了 作為換作為換碼字符，則可用碼字符，則可用%去匹配去匹配%，用，用去匹配，用去匹配，用 去匹去匹配配 。【

44、例例4.204.20】查詢存放在供電局查詢存放在供電局1#1#倉庫的物資的詳細倉庫的物資的詳細情況。情況。SELECT SELECT * * FROM stock FROM stock WHERE warehouse LIKE WHERE warehouse LIKE 供電局供電局1#1#倉庫倉庫 等價于：等價于：SELECT SELECT * *FROM stockFROM stockWHERE warehouse =WHERE warehouse =供電局供電局1#1#倉庫倉庫 4.3.1 單表查詢單表查詢【例例4.214.21】查詢所有絕緣電線的物資編號、名稱和查詢所有絕緣電線的物資編號

45、、名稱和規(guī)格。規(guī)格。SELECT mat_num , mat_name, speciSELECT mat_num , mat_name, speciFROM stock FROM stock WHERE mat_name LIKE %WHERE mat_name LIKE %絕緣電線絕緣電線 【例例4.224.22】查詢物資名稱中第三、四個字為查詢物資名稱中第三、四個字為“絕緣絕緣”的物資編號、名稱和規(guī)格。的物資編號、名稱和規(guī)格。SELECT mat_num , mat_name, speciSELECT mat_num , mat_name, speciFROM stock FROM sto

46、ck WHERE mat_name LIKE _ _WHERE mat_name LIKE _ _絕緣絕緣%4.3.1 單表查詢單表查詢【例例4.234.23】查詢所有不帶絕緣兩個字的物資編號、名稱和查詢所有不帶絕緣兩個字的物資編號、名稱和規(guī)格。規(guī)格。SELECT mat_num , mat_name, speciSELECT mat_num , mat_name, speciFROM stock FROM stock WHERE mat_name NOT LIKE %WHERE mat_name NOT LIKE %絕緣絕緣%【例例4.244.24】查詢物資名稱為查詢物資名稱為斷路器斷路器_

47、 _戶外真空戶外真空物資信物資信息。息。SELECT SELECT * *FROM stock FROM stock WHERE mat_name LIKE WHERE mat_name LIKE 斷路器斷路器_戶外真空戶外真空 ESCAPE ; ESCAPE ; 說明：說明：ESCAPEESCAPE 短語表示短語表示“ ”為換碼字符，這樣匹配串中緊跟在為換碼字符，這樣匹配串中緊跟在“ ”后面的字符后面的字符“_ _”不再具有通配符的含義，轉(zhuǎn)義為普通的不再具有通配符的含義，轉(zhuǎn)義為普通的“_ _”字符。字符。4.3.1 單表查詢單表查詢 判斷取值為空的語句格式為：判斷取值為空的語句格式為： 列名

48、列名 IS NULLIS NULL 判斷取值不為空的語句格式為：判斷取值不為空的語句格式為：列名列名 IS NOT NULLIS NOT NULL【例例4.254.25】查詢無庫存單價的物資編號及其名稱。查詢無庫存單價的物資編號及其名稱。 SELECT mat_num , mat_nameSELECT mat_num , mat_name FROM stock FROM stock WHERE unit IS NULL WHERE unit IS NULL注意：這里的注意：這里的“ISIS”不能用等號（不能用等號（= =）代替。）代替。（5）涉及空值的查詢）涉及空值的查詢(is null, i

49、s not null)4.3.1 單表查詢單表查詢【例例4.264.26】查詢規(guī)格為查詢規(guī)格為BVV-120BVV-120的護套絕緣電線的的護套絕緣電線的物資編號、庫存數(shù)量及庫存地點。物資編號、庫存數(shù)量及庫存地點。SELECT mat_num,warehouse,amount SELECT mat_num,warehouse,amount FROM stock FROM stock WHERE mat_name=WHERE mat_name=護套絕緣電線護套絕緣電線 AND speci=BVV-120 AND speci=BVV-1204.3.1 單表查詢單表查詢（6）多重條件查詢）多重條件查

50、詢(and, or)【例例4.274.27】查詢查詢“護套絕緣電線護套絕緣電線”的物資編號及其單價，的物資編號及其單價，查詢結(jié)果按單價降序排列。查詢結(jié)果按單價降序排列。SELECT mat_name,unitSELECT mat_name,unitFROM stockFROM stockWHERE mat_name=WHERE mat_name=護套絕緣電線護套絕緣電線 ORDER BY unit DESCORDER BY unit DESC3. 3. 對查詢結(jié)果排序?qū)Σ樵兘Y(jié)果排序(order by (order by ASC|DESC)ASC|DESC)4.3.1 單表查詢單表查詢【例例4.

51、284.28】查詢所有物資的信息，查詢結(jié)果按所在倉庫查詢所有物資的信息，查詢結(jié)果按所在倉庫名降序排列，同一倉庫的物資按庫存量升序排列。名降序排列，同一倉庫的物資按庫存量升序排列。SELECT SELECT * * FROM stockFROM stockORDER BY warehouse DESC , amountORDER BY warehouse DESC , amount4.3.1 單表查詢單表查詢注：注： 1）默認(rèn)為升序；）默認(rèn)為升序； 2）認(rèn)為空值是最）認(rèn)為空值是最小的。小的。 TOP nTOP n子句子句：在查詢結(jié)果中輸出前面的：在查詢結(jié)果中輸出前面的n n條記錄；條記錄； TO

52、P n PERCENTTOP n PERCENT子句子句：在查詢結(jié)果中輸出前面占結(jié)果：在查詢結(jié)果中輸出前面占結(jié)果記錄總數(shù)的記錄總數(shù)的n%n%條記錄。條記錄。【例例4.294.29】顯示顯示stockstock表中，庫存量最大的兩條記錄。表中，庫存量最大的兩條記錄。SELECT TOP 2 SELECT TOP 2 * * FROM stock FROM stock ORDER BY amount DESCORDER BY amount DESC4. TOP4. TOP子句的用法子句的用法4.3.1 單表查詢單表查詢【例例4.304.30】顯示顯示stockstock表中占總數(shù)表中占總數(shù)20%2

53、0%的記錄。的記錄。SELECT TOP 20 PERCENT SELECT TOP 20 PERCENT * * FROM stockFROM stock 注意：注意：top top 子句不能和子句不能和DistinctDistinct關(guān)鍵字同時使用。關(guān)鍵字同時使用。4.3.1 單表查詢單表查詢4.3.1 單表查詢單表查詢5. 聚集函數(shù)聚集函數(shù) COUNT(DISTINCT|ALL COUNT(DISTINCT|ALL * *) ) 統(tǒng)計元組個數(shù)統(tǒng)計元組個數(shù) COUNT(DISTINCT|ALL COUNT(DISTINCT|ALL ) ) 統(tǒng)計一列中值的個數(shù)統(tǒng)計一列中值的個數(shù)SUM(DIS

54、TINCT|ALL SUM(DISTINCT|ALL ) ) 計算一列值的總和（此計算一列值的總和（此列必須是數(shù)值型）列必須是數(shù)值型）AVG(DISTINCT|ALL AVG(DISTINCT|ALL ) ) 計算一列值的平均（此計算一列值的平均（此列必須是數(shù)值型）列必須是數(shù)值型）MAX(DISTINCT|ALL MAX(DISTINCT|ALL ) ) 求一列值中的最大值求一列值中的最大值MIN(DISTINCT|ALL MIN(DISTINCT|ALL ) ) 求一列值中的最小值求一列值中的最小值 注：缺省為注：缺省為ALL，即計算時不取消重復(fù)值。，即計算時不取消重復(fù)值?！纠?.314.

55、31】 統(tǒng)計領(lǐng)取了物資的搶修工程項目數(shù)。統(tǒng)計領(lǐng)取了物資的搶修工程項目數(shù)。SELECT COUNT (DISTINCT prj_num)SELECT COUNT (DISTINCT prj_num)FROM out_stock;FROM out_stock;【例例4.324.32】 查詢使用查詢使用m001m001號物資的搶修工程的最高領(lǐng)號物資的搶修工程的最高領(lǐng)取數(shù)量、最低領(lǐng)取數(shù)量以及平均領(lǐng)取數(shù)量。取數(shù)量、最低領(lǐng)取數(shù)量以及平均領(lǐng)取數(shù)量。SELECT MAX(amount), MIN(amount), AVG(amount)SELECT MAX(amount), MIN(amount), AVG(

56、amount)FROM out_stockFROM out_stockWHERE mat_num = m001;WHERE mat_num = m001;注意：聚集函數(shù)中除注意：聚集函數(shù)中除COUNTCOUNT外，其他函數(shù)在計算過程中外，其他函數(shù)在計算過程中均忽略均忽略NULLNULL值；值；WHEREWHERE子句中是不能使用聚集函數(shù)作子句中是不能使用聚集函數(shù)作為條件表達式的。為條件表達式的。4.3.1 單表查詢單表查詢4.3.1 單表查詢單表查詢6. 對查詢結(jié)果分組對查詢結(jié)果分組(group by) GROUP BYGROUP BY子句可以將查詢結(jié)果表按一列或多列子句可以將查詢結(jié)果表按一列

57、或多列取值相等的原則進行分組。取值相等的原則進行分組。 分組的目的：為了分組的目的：為了細化集函數(shù)細化集函數(shù)的作用對象。如的作用對象。如果未對查詢結(jié)果分組，集函數(shù)將作用于整個查詢結(jié)果未對查詢結(jié)果分組，集函數(shù)將作用于整個查詢結(jié)果，即整個查詢結(jié)果只有一個函數(shù)值。如果用果，即整個查詢結(jié)果只有一個函數(shù)值。如果用GROUPGROUP分了組，集函數(shù)將作用于每一個組，即每一分了組，集函數(shù)將作用于每一個組，即每一組都有一個函數(shù)值。組都有一個函數(shù)值。【例例4.334.33】 查詢每個搶修工程項目號及使用的物查詢每個搶修工程項目號及使用的物資種類。資種類。SELECT prj_num SELECT prj_num

58、 項目號項目號, COUNT(, COUNT(* *) ) 物資種類物資種類FROM out_stockFROM out_stockGROUP BY prj_num;GROUP BY prj_num;4.3.1 單表查詢單表查詢3.4.1 單表查詢單表查詢HAVINGHAVING可以針對集函數(shù)的結(jié)果值進行篩選，它是作用可以針對集函數(shù)的結(jié)果值進行篩選，它是作用于分組計算的結(jié)果集；于分組計算的結(jié)果集；跟在跟在Group ByGroup By子句的后面，有子句的后面，有GROUP BYGROUP BY才有才有HAVINGHAVING【例例4.34】 查詢使用了查詢使用了2種以上物資的搶修工程項目號。

59、種以上物資的搶修工程項目號。SELECT prj_num 項目號項目號FROM out_stockGROUP BY prj_numHAVING COUNT(*)2;注：注：（1）WHERE作用于基本表或視圖，從中選擇滿足條件的元組；作用于基本表或視圖，從中選擇滿足條件的元組；（2）HAVING作用于組，從中選擇滿足條件的組。作用于組，從中選擇滿足條件的組。語法格式如下：語法格式如下： COMPUTE COMPUTE AVG | COUNT | MAX | MIN | SUM AVG | COUNT | MAX | MIN | SUM ( ( 表達式表達式) ,.n ) ,.n BY BY 表達

60、式表達式 ,.n ,.n 其中：其中：AVG | COUNT | MAX | MIN | SUMAVG | COUNT | MAX | MIN | SUM表示表示可以使用的聚集函數(shù)。表達式表示計算的列名，可以使用的聚集函數(shù)。表達式表示計算的列名，必須出現(xiàn)在選擇列表中。必須出現(xiàn)在選擇列表中。BY BY 表達式表示在結(jié)果集表達式表示在結(jié)果集中生成控制中斷和小計。中生成控制中斷和小計。 4.3.1 單表查詢單表查詢7. COMPUTER BY 子句子句獲得統(tǒng)計數(shù)據(jù)及相應(yīng)統(tǒng)計的明細數(shù)據(jù)。獲得統(tǒng)計數(shù)據(jù)及相應(yīng)統(tǒng)計的明細數(shù)據(jù)?！纠?.354.35】統(tǒng)計存放于供電局統(tǒng)計存放于供電局2#2#倉庫的所有物倉庫的