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

1、2 2第第5 5章章 結(jié)構(gòu)化查詢語言結(jié)構(gòu)化查詢語言SQL SQL 5.1 5.1 SQLSQL語言概述語言概述5.2 5.2 SQLSQL數(shù)據(jù)定義數(shù)據(jù)定義5.3 5.3 SQLSQL數(shù)據(jù)更新數(shù)據(jù)更新5.4 SQL5.4 SQL數(shù)據(jù)查詢數(shù)據(jù)查詢 5.4.1 5.4.1 查詢的語法查詢的語法 5.4.2 5.4.2 基本的查詢基本的查詢 5.4.3 5.4.3 連接查詢連接查詢 5.4.4 5.4.4 子查詢子查詢 5.4.5 5.4.5 合并查詢合并查詢本章小結(jié)本章小結(jié)3 35.1 SQL5.1 SQL語言概述語言概述 SQLSQL語言是數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。語言是數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。 高度集成化高度

2、集成化 集數(shù)據(jù)的定義、操縱、查詢和控制功能于一體。集數(shù)據(jù)的定義、操縱、查詢和控制功能于一體。 高度非過程化高度非過程化 用用SQLSQL語言進(jìn)行數(shù)據(jù)操作，不必告訴計算機(jī)怎么做，只要提出語言進(jìn)行數(shù)據(jù)操作，不必告訴計算機(jī)怎么做，只要提出做什么，系統(tǒng)就可以自動完成全部工作。做什么，系統(tǒng)就可以自動完成全部工作。 面向集合的操作方式面向集合的操作方式 采用集合操作方式，操作對象和操作結(jié)果都是記錄集。采用集合操作方式，操作對象和操作結(jié)果都是記錄集。 簡潔易學(xué)簡潔易學(xué) 完成核心功能只用了完成核心功能只用了9 9個命令動詞，且接近英語的自然語法。個命令動詞，且接近英語的自然語法。 用法靈活用法靈活 既可以獨立

3、使用，也可以嵌入到高級語言中使用。既可以獨立使用，也可以嵌入到高級語言中使用。4 4在在AccessAccess中使用中使用SQLSQL語句語句 使用方法使用方法 打開查詢設(shè)計視圖。打開查詢設(shè)計視圖。 執(zhí)行執(zhí)行“查詢查詢|SQL|SQL特定查詢特定查詢| |數(shù)據(jù)定義數(shù)據(jù)定義”命令，打開命令，打開“數(shù)據(jù)定義查詢數(shù)據(jù)定義查詢”窗口，輸入窗口，輸入SQLSQL語句。語句。 注意：在注意：在“數(shù)據(jù)定義查詢數(shù)據(jù)定義查詢”窗口中一次只能輸入一條窗口中一次只能輸入一條SQLSQL語句。語句。 單擊工具欄的單擊工具欄的“運(yùn)行運(yùn)行”按鈕，執(zhí)行按鈕，執(zhí)行SQLSQL語句。語句。 根據(jù)需要，將根據(jù)需要，將SQLSQ

4、L語句保存為一個查詢對象，或直接語句保存為一個查詢對象，或直接關(guān)閉查詢窗口。關(guān)閉查詢窗口。5 55.2 SQL5.2 SQL數(shù)據(jù)定義數(shù)據(jù)定義 使用使用 CREATE TABLE CREATE TABLE 命令定義表命令定義表 CREATE TABLE CREATE TABLE ( ( () NOT NULL ) NOT NULL PRIMARY KEY | UNIQUE PRIMARY KEY | UNIQUE , , () NOT NULL ) NOT NULL PRIMARY KEY | UNIQUE PRIMARY KEY | UNIQUE , ) , ) 定義表時，必須指定表名、各個字

5、段名及相應(yīng)的數(shù)據(jù)類型和字段定義表時，必須指定表名、各個字段名及相應(yīng)的數(shù)據(jù)類型和字段大小（由系統(tǒng)自動確定的字段大小省略），并且各個字段之間用大小（由系統(tǒng)自動確定的字段大小省略），并且各個字段之間用英文的逗號分隔。英文的逗號分隔。 字段的數(shù)據(jù)類型必須用字符表示。字段的數(shù)據(jù)類型必須用字符表示。 primary key primary key 定義主鍵，定義主鍵，unique unique 定義惟一鍵。定義惟一鍵。 命令關(guān)鍵詞和語句中的其他關(guān)鍵詞不區(qū)分大小寫。命令關(guān)鍵詞和語句中的其他關(guān)鍵詞不區(qū)分大小寫。表示可選項表示必選項 | 表示多選1 6 6定義表示例定義表示例【例例5-15-1】使用使用SQLS

6、QL語句定義一個名為語句定義一個名為empemp的表，結(jié)構(gòu)如下：的表，結(jié)構(gòu)如下： 員工號（員工號（TextText，3 3字符）、姓名（字符）、姓名（TextText，4 4字符）、性別字符）、性別（TextText，1 1字符）、婚否（字符）、婚否（LogicalLogical）、聘用日期（）、聘用日期（DateDate）、）、照片（照片（OLEObjectOLEObject）、）、簡歷（簡歷（MemoMemo），員工號為主鍵，姓名不允許為空值。），員工號為主鍵，姓名不允許為空值?！纠?-25-2】【】【例例5-35-3】7 7建立索引建立索引 使用使用 CREATE INDEX CREA

7、TE INDEX 命令建立索引命令建立索引 CREATE UNIQUE INDEX CREATE UNIQUE INDEX ON ON ( (ASC|DESC,ASC|DESC,ASC|DESC,)2ASC|DESC,) WITH PRIMARY WITH PRIMARY UNIQUE UNIQUE 指定唯一索引，指定唯一索引，WITH PRIMARY WITH PRIMARY 指定主索引。指定主索引。建立索引時關(guān)閉表建立索引時關(guān)閉表 【例例5-35-3】使用使用SQLSQL語句建立索引，在語句建立索引，在itemitem表的表的“型號型號”字段上建立惟一字段上建立惟一索引，索引名稱為索引，索

8、引名稱為uni_modeluni_model；在；在“類別類別”和和“價格價格”2 2個字段上分別個字段上分別按升序和降序建立組合索引，索引名稱為按升序和降序建立組合索引，索引名稱為idx_tpriceidx_tprice。 create unique index uni_model on item(create unique index uni_model on item(型號型號) ) create index idx_tprice on item( create index idx_tprice on item(類別類別, , 價格價格 desc) desc) 8 8建立表間關(guān)系建立表間

9、關(guān)系 使用使用 Create Table Create Table 命令，在定義表的同命令，在定義表的同時建立表間關(guān)系時建立表間關(guān)系【例例5-45-4】使用使用SQLSQL語句定義語句定義sale2sale2表，并通過表，并通過“員工號員工號”字字段與段與empemp表建立關(guān)系，通過表建立關(guān)系，通過“商品號商品號”字段與字段與itemitem表建立表建立關(guān)系。關(guān)系。references 表示參照引用，即sale2(子表)中的商品號為外鍵，item(主表)中的商品號為主鍵9 9修改表修改表 使用使用 Alter Table Alter Table 命令命令 修改字段修改字段 ALTER TABL

10、E ALTER TABLE ALTER ALTER () 使用該命令不能修改字段名。使用該命令不能修改字段名。 添加字段添加字段 ALTER TABLE ALTER TABLE ADD ADD () 刪除字段刪除字段 ALTER TABLE ALTER TABLE DROP DROP 例如，在例如，在empemp表中增加一個表中增加一個“電話號碼電話號碼”字段（字段（IntegerInteger），然后將該字），然后將該字段修改為段修改為TextText型（型（1111字符），最后刪除該字段。字符），最后刪除該字段。 alter table emp add alter table emp ad

11、d 電話號碼電話號碼 integerinteger alter table emp alter alter table emp alter 電話號碼電話號碼 text(11)text(11) alter table emp drop alter table emp drop 電話號碼電話號碼1010刪除表和索引刪除表和索引 使用使用 DROP DROP 命令命令 刪除索引刪除索引 DROP INDEX DROP INDEX ON ON 刪除表刪除表 DROP TABLE DROP TABLE 刪除表后，在表上定義的索引也一起被刪除。刪除表后，在表上定義的索引也一起被刪除。例如，使用例如，使用S

12、QLSQL語句刪除語句刪除itemitem表中的表中的uni_modeluni_model索引項。索引項。 drop index uni_model on itemdrop index uni_model on item 使用使用SQLSQL語句刪除語句刪除salesale表。表。 drop table saledrop table sale11115.3 SQL5.3 SQL數(shù)據(jù)更新數(shù)據(jù)更新 使用使用 Insert Into Insert Into 命令插入記錄命令插入記錄 INSERT INTO INSERT INTO ( (,2，) VALUES( VALUES(,)2,) 如果缺省字段

13、名，則必須為新記錄中的每個字段都賦值，且數(shù)據(jù)如果缺省字段名，則必須為新記錄中的每個字段都賦值，且數(shù)據(jù)類型和順序要與表中定義的字段一一對應(yīng)。類型和順序要與表中定義的字段一一對應(yīng)?！纠?-55-5】使用使用SQLSQL語句在語句在empemp表中插入表中插入2 2條員工記錄。條員工記錄。 insert into emp valuesinsert into emp values (a01, (a01, 馬立馬立, , 男男, yes, #2011-5-15#, null, null), yes, #2011-5-15#, null, null) insert into emp( insert in

14、to emp(員工號員工號, , 姓名姓名, , 性別性別, , 聘用日期聘用日期) values) values (b01, (b01, 陳慧娟陳慧娟, , 女女, #2011-7-10#), #2011-7-10#)1212SQLSQL數(shù)據(jù)更新數(shù)據(jù)更新 使用使用 Update Set Update Set 命令更新記錄命令更新記錄 UPDATA UPDATA SET SET =1 , ,=,2, WHERE WHERE 例如，使用例如，使用SQLSQL語句將語句將empemp表中所有男員工的表中所有男員工的“婚否婚否”字段改為字段改為“否否”。 update emp set update

15、emp set 婚否婚否=no where =no where 性別性別=男男 使用使用 Delete From Delete From 命令刪除記錄命令刪除記錄 DELETE FROM DELETE FROM WHERE WHERE 例如，使用例如，使用SQLSQL語句刪除語句刪除empemp表中的所有記錄。表中的所有記錄。 delete from empdelete from emp13135.4.1 5.4.1 查詢的語法查詢的語法 使用使用 SELECT SELECT 命令實現(xiàn)數(shù)據(jù)查詢功能命令實現(xiàn)數(shù)據(jù)查詢功能 SELECT ALL|DISTINCT TOP SELECT ALL|DIS

16、TINCT TOP PERCENT PERCENT AS AS FROM FROM AS AS ,ASAS2 WHERE WHERE AND AND GROUP BY GROUP BY HAVING HAVING ORDER BY ORDER BY ASC | DESC ASC | DESC 打開查詢設(shè)計器，從工具欄的視圖列表中選擇“SQL視圖”選項或“設(shè)計視圖”選項，可以在SELECT命令和查詢設(shè)計器之間切換。14145.4.2 5.4.2 基本的查詢基本的查詢 投影投影 從數(shù)據(jù)源中選擇若干列從數(shù)據(jù)源中選擇若干列 SELECT SELECT ,2， FROM FROM 目標(biāo)列可以是數(shù)據(jù)源中已

17、有的字段，也可以是一個計算列。目標(biāo)列可以是數(shù)據(jù)源中已有的字段，也可以是一個計算列。 若要選擇數(shù)據(jù)源中的所有字段，目標(biāo)列可表示為若要選擇數(shù)據(jù)源中的所有字段，目標(biāo)列可表示為“* *”。 在字段名前加上在字段名前加上DISTINCTDISTINCT關(guān)鍵字，可消除重復(fù)的記錄。關(guān)鍵字，可消除重復(fù)的記錄。 例如，例如， select select 員工編號員工編號, , 姓名姓名, , 性別性別, , 聘用日期聘用日期 from from 員工員工 select select * * from from 員工員工 select select 員工編號員工編號, , 姓名姓名, , year(date()-

18、year(year(date()-year(聘用日期聘用日期) as ) as 聘用年聘用年數(shù)數(shù) from from 員工員工 select select distinctdistinct 商品編號商品編號 from from 銷售銷售 1515基本的查詢基本的查詢 選擇選擇 從表中選出滿足條件的記錄從表中選出滿足條件的記錄 SELECT SELECT FROM FROM WHERE WHERE 是一個邏輯表達(dá)式，由多個關(guān)系表達(dá)式通過邏輯是一個邏輯表達(dá)式，由多個關(guān)系表達(dá)式通過邏輯運(yùn)算符連接而成。運(yùn)算符連接而成。例如，例如， select select 員工編號員工編號, , 姓名姓名, , 聘

19、用日期聘用日期 from from 員工員工 where where 性別性別=女女 and and 婚否婚否=yes=yes select select * * from from 商品商品 where where 商品編號商品編號 like Dlike D* * 1616基本的查詢基本的查詢 排序排序 使用使用ORDER BYORDER BY子句可以對查詢結(jié)果按照一個或多個列的升序子句可以對查詢結(jié)果按照一個或多個列的升序（ASCASC）或降序（）或降序（DESCDESC）排列，默認(rèn)是升序。）排列，默認(rèn)是升序。 ORDER BY ORDER BY ASC|DESC ASC|DESC 可以是字

20、段名，或目標(biāo)列的序號，如可以是字段名，或目標(biāo)列的序號，如1 1，2 2，。 在目標(biāo)列前加上在目標(biāo)列前加上TOPTOP短語短語，可以從查詢結(jié)果中選擇排在前面的若，可以從查詢結(jié)果中選擇排在前面的若干條記錄（用數(shù)值或百分比指定）。干條記錄（用數(shù)值或百分比指定）。 SELECT TOP SELECT TOP PERCENT ORDER BY PERCENT ORDER BY ASC|DESC ASC|DESC例如，例如， select select * * from from 商品商品 where where 價格價格 between 900 and 1200between 900 and 1200

21、order by order by 價格價格 descdesc select select * * from from 銷售銷售 where where 員工編號員工編號 in(001,003,005)in(001,003,005) order by order by 員工編號員工編號, , 銷售日期銷售日期 descdesc select select top 3 top 3 * * from from 商品商品 order by order by 價格價格 1717基本的查詢基本的查詢 分組分組 使用使用GROUP BYGROUP BY子句可以按照某一列的值分組。子句可以按照某一列的值分組

22、。 GROUP BY GROUP BY HAVING HAVING 使用使用HAVINGHAVING短語可以對分組后的結(jié)果進(jìn)行篩選。短語可以對分組后的結(jié)果進(jìn)行篩選。 注意：注意：HAVINGHAVING短語必須和短語必須和GROUP BYGROUP BY子句同時使用。子句同時使用。 分組查詢通常與分組查詢通常與SQLSQL聚合函數(shù)一起使用，先按指定的數(shù)據(jù)項分組，再對聚合函數(shù)一起使用，先按指定的數(shù)據(jù)項分組，再對各組進(jìn)行總計，如計數(shù)、求和、求平均值等。如果未分組，則聚合函數(shù)各組進(jìn)行總計，如計數(shù)、求和、求平均值等。如果未分組，則聚合函數(shù)作用于整個表。作用于整個表。 常用常用SQLSQL聚合函數(shù)：聚合

23、函數(shù)：COUNTCOUNT、AVGAVG、SUMSUM、MINMIN、MAXMAX1818基本的查詢基本的查詢【例例5-155-15】統(tǒng)計商品表中各類別商品的數(shù)量統(tǒng)計商品表中各類別商品的數(shù)量 select select 類別類別, count(, count(* *) as ) as 各類別數(shù)量各類別數(shù)量 from from 商品商品 group by group by 類別類別Select Select 出版周期出版周期 ，avgavg（ 訂閱年價訂閱年價 ）as as 平均定價平均定價 from from 報紙報紙 group group by by 出版周期出版周期【例例5-165-16

24、】在銷售表中查詢有在銷售表中查詢有3 3次或次或3 3次以上銷售記錄的員工編號次以上銷售記錄的員工編號 select select 員工編號員工編號 from from 銷售銷售 group by group by 員工編號員工編號 having count(having count(* *)=3)=3【例例5-175-17】在銷售表中查詢在銷售表中查詢20092009年有年有2 2次或次或2 2次以上銷售記錄的員工編號次以上銷售記錄的員工編號 SELECT SELECT 員工編號員工編號 FROM FROM 銷售銷售WHERE year(WHERE year(銷售日期銷售日期)=2009)=

25、2009GROUP BY GROUP BY 員工編號員工編號 HAVING count( HAVING count(* *)=2;)=2;19195.4.3 5.4.3 連接查詢連接查詢 連接查詢同時涉及連接查詢同時涉及2 2個或多個表的數(shù)據(jù)。個或多個表的數(shù)據(jù)。 連接查詢需要指定連接查詢需要指定2 2個表的連接條件，有個表的連接條件，有2 2種格式：種格式： 在在 Where Where 子句中指定連接條件子句中指定連接條件 SELECT SELECT FROM FROM , 2 WHERE WHERE . = = .2 在在 FROM FROM 子句中指定連接條件子句中指定連接條件SELEC

26、T SELECT FROM FROM INNER JOIN | LEFT JOIN | RIGHT JOIN INNER JOIN | LEFT JOIN | RIGHT JOIN 2 ON ON . = = .2 INNER JOIN INNER JOIN 內(nèi)部連接內(nèi)部連接,LEFT JOIN ,LEFT JOIN 左外部連接左外部連接,RIGHT JOIN ,RIGHT JOIN 右外部右外部連接連接 連接條件中的連接字段通常為連接條件中的連接字段通常為2 2個表中的公共字段或語義相同的個表中的公共字段或語義相同的字段。字段。2020連接查詢示例連接查詢示例【例例5-185-18】查詢每個

27、員工的商品銷售記錄，要求查詢結(jié)果中包含員工編查詢每個員工的商品銷售記錄，要求查詢結(jié)果中包含員工編號、姓名、商品編號、銷售量和銷售日期。號、姓名、商品編號、銷售量和銷售日期。 select select 員工員工. .員工編號員工編號, , 姓名姓名, , 商品編號商品編號, , 銷售量銷售量, , 銷售日期銷售日期 from from 員工員工, , 銷售銷售 where where 員工員工. .員工編號員工編號 = = 銷售銷售. .員工編號員工編號 為簡化輸入，可以使用表的為簡化輸入，可以使用表的別名別名代替表名，別名在代替表名，別名在FROMFROM子句中指定子句中指定 select

28、select yg.yg.員工編號員工編號, , 姓名姓名, , 商品編號商品編號, , 銷售量銷售量, , 銷售日期銷售日期 from from 員工員工 as ygas yg, , 銷售銷售 as xsas xs where where yg.yg.員工編號員工編號 = = xs.xs.員工編號員工編號 select yg.select yg.員工編號員工編號, , 姓名姓名, , 商品編號商品編號, , 銷售量銷售量, , 銷售日期銷售日期 from from 員工員工 as yg inner join as yg inner join 銷售銷售 as xsas xs on yg. on

29、 yg.員工編號員工編號 = xs.= xs.員工編號員工編號 【例例5-195-19】查詢查詢20102010年每個員工的商品銷售記錄，要求查年每個員工的商品銷售記錄，要求查詢結(jié)果中包含員工編號、姓名、商品編號、銷售量和銷售詢結(jié)果中包含員工編號、姓名、商品編號、銷售量和銷售日期日期SELECT SELECT 員工員工. .員工編號員工編號, , 姓名姓名, , 商品編號商品編號, , 銷售量銷售量, , 銷售日期銷售日期FROM FROM 員工員工, , 銷售銷售WHERE WHERE 員工員工. .員工編號員工編號= =銷售銷售. .員工編號員工編號 And year( And year(

30、銷售日期銷售日期)=2010;)=2010;212122225.4.4 5.4.4 子查詢子查詢 子查詢子查詢( (或稱嵌套查詢或稱嵌套查詢) ) 指在一個指在一個SELECTSELECT語句的語句的WHEREWHERE子句中包含另一個子句中包含另一個SELECTSELECT語句語句 子查詢必須用括號子查詢必須用括號“( )”( )”括起來。括起來。 子查詢（內(nèi)層查詢）的結(jié)果用作建立其父查詢（外層查詢）子查詢（內(nèi)層查詢）的結(jié)果用作建立其父查詢（外層查詢）的條件，因此，子查詢的結(jié)果必須有確定的值。的條件，因此，子查詢的結(jié)果必須有確定的值。 利用子查詢可以將幾個簡單查詢構(gòu)成一個復(fù)雜查詢，從而增利用

31、子查詢可以將幾個簡單查詢構(gòu)成一個復(fù)雜查詢，從而增強(qiáng)強(qiáng)SQLSQL的查詢能力。的查詢能力。 2323子查詢示例子查詢示例【例例5-205-20】查詢與員工編號為查詢與員工編號為“005”005”的員工同一時間聘用的員工信息，的員工同一時間聘用的員工信息，包括員工編號、姓名、性別和聘用日期。包括員工編號、姓名、性別和聘用日期。 select select 員工編號員工編號, ,姓名姓名, ,性別性別, ,聘用日期聘用日期 from from 員工員工 where where 聘用日期聘用日期 = = (select (select 聘用日期聘用日期 from from 員工員工 where whe

32、re 員工編號員工編號 = 005)= 005)2424子查詢示例子查詢示例 【例例5-215-21】查找未被銷售的商品記錄，包括商品編號、品牌、類別、查找未被銷售的商品記錄，包括商品編號、品牌、類別、型號和銷售價型號和銷售價 Select Select * * from from 商品商品where where 商品編號商品編號 not in (select distinct not in (select distinct 商品編號商品編號 from from 銷售銷售);); 使用設(shè)計視圖設(shè)計使用設(shè)計視圖設(shè)計 Select Select 郵發(fā)代號，名稱郵發(fā)代號，名稱 from from 報紙報紙 where where 郵發(fā)代號郵發(fā)代號 not in not in （select distinct select distinct 郵發(fā)代號郵發(fā)