結(jié)構(gòu)化查詢語言

1、清華大學(xué)出版社2013年8月1日2 2第5章 結(jié)構(gòu)化查詢語言SQL 5.1 SQL語言概述5.2 SQL數(shù)據(jù)定義5.3 SQL數(shù)據(jù)更新5.4 SQL數(shù)據(jù)查詢 5.4.1 查詢的語法 5.4.2 基本的查詢 5.4.3 連接查詢 5.4.4 子查詢 5.4.5 合并查詢本章小結(jié)3 35.1 SQL語言概述pSQLSQL是是Structured Query LanguageStructured Query Language的縮寫，即結(jié)構(gòu)化的縮寫，即結(jié)構(gòu)化查詢語查詢語言。是一種通用的且功能強(qiáng)大的關(guān)系數(shù)據(jù)庫語言，言。是一種通用的且功能強(qiáng)大的關(guān)系數(shù)據(jù)庫語言，也是也是關(guān)系關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)數(shù)據(jù)庫的標(biāo)準(zhǔn)語言語

2、言。它它既可以用于大型數(shù)據(jù)庫管理系統(tǒng)，也既可以用于大型數(shù)據(jù)庫管理系統(tǒng)，也可以用于微型機(jī)可以用于微型機(jī)數(shù)據(jù)庫管理系統(tǒng)。數(shù)據(jù)庫管理系統(tǒng)。p具有具有數(shù)據(jù)定義數(shù)據(jù)定義、數(shù)據(jù)更新、數(shù)據(jù)查詢、數(shù)據(jù)更新、數(shù)據(jù)查詢、數(shù)據(jù)控制等功能。數(shù)據(jù)控制等功能。4 45.1 SQL語言概述p SQLSQL的主要特點(diǎn)：的主要特點(diǎn)：n 高度集成化n 集數(shù)據(jù)的定義、操縱、查詢和控制功能于一體。n 高度非過程化n 用SQL語言進(jìn)行數(shù)據(jù)操作，不必告訴計算機(jī)怎么做，只要提出做什么，系統(tǒng)就可以自動完成全部工作。n 面向集合的操作方式n 采用集合操作方式，操作對象和操作結(jié)果都是記錄集。n 簡潔易學(xué)n 完成核心功能只用了9個命令動詞，且接近

3、英語的自然語法。n 用法靈活n 既可以獨(dú)立使用，也可以嵌入到高級語言中使用。5 5在Access中使用SQL語句n 使用方法使用方法n 打開查詢設(shè)計視圖。n 執(zhí)行“查詢工具/設(shè)計”“查詢類型”“數(shù)據(jù)定義”命令，打開“數(shù)據(jù)定義”查詢窗口，輸入SQL語句。n 注意：在“數(shù)據(jù)定義”查詢窗口中一次只能輸入一條SQL語句。n 單擊功能區(qū)的“運(yùn)行”按鈕，執(zhí)行SQL語句。n 根據(jù)需要，將SQL語句保存為一個查詢對象，或直接關(guān)閉查詢窗口。6 65.2 SQL數(shù)據(jù)定義n使用SQL的CREATE、ALTER和DROP命令可以實(shí)現(xiàn)數(shù)據(jù)定義功能，包括表、索引等的創(chuàng)建、修改和刪除。nCREATE TABLEnCREAT

4、E INDEXnALTER TABLEnDROP TABLE nDROP INDEX7 75.2 SQL數(shù)據(jù)定義n1.1.定義表定義表n使用使用 CREATE TABLE CREATE TABLE 命令定義命令定義表表n示例：nCreate Table Create Table empemp( (員工號員工號 Text(3)Text(3), ,姓名姓名 Text(4),Text(4),性別性別 Text(1),Text(1),婚否婚否 Logical,Logical,聘用日期聘用日期 Date,Date,照片照片 OLEObject,OLEObject,簡歷簡歷 MemoMemo) )表名字段

5、名數(shù)據(jù)類型及大小8 85.2 SQL數(shù)據(jù)定義n進(jìn)一步進(jìn)一步：nCreate Create Table empTable emp( (員工號員工號 Text(3Text(3) ) Primary KeyPrimary Key, ,姓名姓名 Text(4Text(4) ) Not NullNot Null, ,性別性別 Text(1),Text(1),婚否婚否 Logical,Logical,聘用日期聘用日期 Date,Date,照片照片 OLEObject,OLEObject,簡歷簡歷 MemoMemo) )定義主鍵指定字段不允許為空值UNIQUE 定義唯一鍵9 95.2 SQL數(shù)據(jù)定義nCRE

6、ATE TABLE ( () NOT NULL PRIMARY KEY | UNIQUE , () NOT NULL PRIMARY KEY | UNIQUE , )n 定義表時，必須指定表名表名、各個字段名字段名及相應(yīng)的數(shù)據(jù)類型數(shù)據(jù)類型和字段大小字段大?。ㄓ上到y(tǒng)自動確定的字段大小省略），并且各個字段之間用英文的逗號分隔。n 字段的數(shù)據(jù)類型必須用字符表示數(shù)據(jù)類型必須用字符表示。n primary key primary key 定義主鍵主鍵，unique unique 定義惟一鍵定義惟一鍵。n 命令關(guān)鍵詞和語句中的其他關(guān)鍵詞不區(qū)分大小寫。1010定義表示例【例5-1】使用SQL語句定義一個名為

7、emp的表，結(jié)構(gòu)如下： 員工號（Text，3字符）、姓名（Text，4字符）、性別（Text，1字符）、婚否（Logical）、聘用日期（Date）、照片（OLEObject）、簡歷（Memo），員工號為主鍵，姓名不允許為空值。 1111定義表示例1212定義表示例n【例5-2】在“銷售管理”數(shù)據(jù)庫中，使用SQL語句定義item表和sale表。Item表結(jié)構(gòu)：商品號（Text，6字符）、品牌（Text，10字符）、類別（Text，6字符）、型號（Text，15字符）、價格（Single），商品號為主鍵。Sale表結(jié)構(gòu)：序號（Counter）、員工號（Text，3字符）、商品號（Text，6字符

8、）、銷售量（Integer）、銷售日期（Date），序號為主鍵1313定義表示例nCreate Table itemCreate Table item( (商品號商品號 Text(6) Primary Text(6) Primary key,key,品牌品牌 Text(10) ,Text(10) ,類別類別 Text(6),Text(6),型號型號 Text(15),Text(15),價格價格 SingleSingle) )nCreate Table saleCreate Table sale( (序號序號 Counter Primary key,Counter Primary key,員工號

9、員工號 Text(3) Not Null,Text(3) Not Null,商品號商品號 Text(6) Not Null,Text(6) Not Null,銷售量銷售量 Integer Not Null,Integer Not Null,銷售日期銷售日期 DateDate) )1414定義表示例索引名表名索引字段指定唯一索引降序n進(jìn)一步： create unique index uni_model on item(型號)nCreate index idx_price on item(價格 desc) n 2. 建立索引建立索引n 使用 CREATE INDEX 命令建立索引n示例： crea

10、te index idx_date on emp (聘用日期)1515建立索引nCREATE CREATE UNIQUEUNIQUE INDEX INDEX ON ON ( (1ASC|DESCASC|DESC,ASC|DESC,)2ASC|DESC,) WITH WITH PRIMARYPRIMARY n UNIQUE 指定唯一索引，WITH PRIMARY 指定主索引。nASC和DESC指定索引值得排序方式，ASC表示升序，DESC表示降序，默認(rèn)為升序 。【例5-3】使用SQL語句建立索引，在item表的“型號”字段上建立惟一索引，索引名稱為uni_model；在“類別”和“價格”2個字段

11、上分別按升序和降序建立組合索引，索引名稱為idx_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) 1616建立表間關(guān)系n3.3.建立表間關(guān)系建立表間關(guān)系 n使用使用 Create Table Create Table 命令，在命令，在定義表的同時建立表間關(guān)系定義表的同時建立表間關(guān)系【

12、例5-4】使用SQL語句定義sale2表，并通過“員工號”字段與emp表建立關(guān)系，通過“商品號”字段與item表建立關(guān)系。references 表示參照引表示參照引用，即用，即sale2(子表子表)中的商品號中的商品號為外鍵，為外鍵，item(主表主表)中的中的商品號為主鍵商品號為主鍵1717修改表n4.4.修改表修改表 n使用 Alter Table 命令，可以修改表的結(jié)構(gòu)，包括修改、添加個刪除字段等。n （1）修改字段 ALTER TABLE ALTER column ()n 使用該命令不能修改字段名使用該命令不能修改字段名。n （2）添加字段 ALTER TABLE ADD column

13、 ()n （3）刪除字段 ALTER TABLE DROP column 1818修改表例如，在emp表中增加一個“電話號碼”字段（Integer），然后將該字段修改為Text型（11字符），最后刪除該字段。 alter table emp add column 電話號碼 integer alter table emp alter 電話號碼 text(11) alter table emp drop 電話號碼1919刪除表和索引n5.刪除索引或表刪除索引或表 n使用 DROP 命令 ，可以刪除表上的索引或刪除表。n 刪除索引 DROP INDEX ON n 刪除表 DROP TABLE n 刪

14、除表后，在表上定義的索引也一起被刪除。例如，使用SQL語句刪除item表中的uni_model索引項。 drop index uni_model on item 使用SQL語句刪除sale表。 drop table sale20205.3 SQL數(shù)據(jù)更新n使用SQL的INSERT、UPDATE和DELETE命令可以實(shí)現(xiàn)數(shù)據(jù)更新功能，包括插入記錄、更新記錄和刪除記錄。nINSERT命令nUPDATE命令nDELETE命令21215.3 SQL數(shù)據(jù)更新n1.插入記錄n使用 Insert Into 命令n示例：ninsert into emp values (a01, 馬立, 男, yes, #20

15、11-5-15#, null, null)ninsert into emp(員工號, 姓名, 性別, 聘用日期) values (b01, 陳慧娟, 女, #2011-7-10#)22225.3 SQL數(shù)據(jù)更新nINSERT INTO (,，) VALUES(,)n 如果缺省字段名，則必須為新記錄中的每個字段都賦值，且如果缺省字段名，則必須為新記錄中的每個字段都賦值，且數(shù)據(jù)類型和順序要與表中定義的字段一一對應(yīng)。數(shù)據(jù)類型和順序要與表中定義的字段一一對應(yīng)?！纠?-5】使用SQL語句在emp表中插入2條員工記錄。 insert into emp values (a01, 馬立, 男, yes, #2

16、011-5-15#, null, null) insert into emp(員工號, 姓名, 性別, 聘用日期) values (b01, 陳慧娟, 女, #2011-7-10#)2323SQL數(shù)據(jù)更新n2.更新記錄 n使用 Update Set 命令更新記錄 UPDATEUPDATE SETSET = ,=, WHEREWHERE 例如，使用SQL語句將emp表中所有男員工的“婚否”字段改為“否”。 update emp set 婚否=no where 性別=男n3.刪除記錄 n使用 Delete From 命令刪除記錄 DELETE FROM WHERE 例如，使用SQL語句刪除emp表

17、中的所有記錄。 delete from emp24245.4 SQL數(shù)據(jù)查詢n數(shù)據(jù)查詢是數(shù)據(jù)庫的核心操作。nSQL語言的SELETCT 命令可以實(shí)現(xiàn)數(shù)據(jù)查詢功能。n包括：選擇查詢、投影查詢、連接查詢、子查詢、合并查詢等。25255.4.1 查詢的語法nSELECT命令是SQL的核心語句，具有靈活的使用方式和豐富的功能。n其語法： SELECTSELECT ALL|DISTINCT TOP PERCENT AS FROMFROM AS ,AS WHEREWHERE AND GROUP BY GROUP BY HAVING ORDER BY ORDER BY ASC | DESC 26265.4.

18、1 查詢的語法從功能區(qū)的“視圖”列表中選擇“SQL視圖”命令或“設(shè)計視圖”命令，可以在SQL命令窗口和查詢設(shè)計視圖之間切換。27275.4.2 基本的查詢n基本的查詢包括投影投影、選擇選擇、排序排序、分組分組等操作 。n1.投影投影n 從數(shù)據(jù)源中選擇若干從數(shù)據(jù)源中選擇若干列，相當(dāng)于關(guān)系代數(shù)中的投影運(yùn)算。列，相當(dāng)于關(guān)系代數(shù)中的投影運(yùn)算。 SELECTSELECT ,， FROMFROM n 目標(biāo)列可以是數(shù)據(jù)源中已有的字段，也可以是一個計算列也可以是一個計算列。n 若要選擇數(shù)據(jù)源中的所有字段所有字段，目標(biāo)列可表示為“* *”。n 在字段名前加上DISTINCTDISTINCT關(guān)鍵字，可消除重復(fù)的記

19、錄。 28285.4.2 基本的查詢【例5-6】查詢“員工”表中所有員工的員工編號、姓名、性別和聘用日期。 select select 員工編號員工編號, , 姓名姓名, , 性別性別, , 聘用日期聘用日期 from from 員工員工若要查詢所有員工的所有記錄，則： select select * * from from 員工員工 【例5-7】查詢“員工”表中所有員工的員工編號、姓名和聘用年數(shù)。 select 員工編號, 姓名, year(date()-year(year(date()-year(聘用日期聘用日期) as ) as 聘用聘用年數(shù)年數(shù) from 員工AS關(guān)鍵字改變輸出列的列標(biāo)

20、題29295.4.2 基本的查詢【例5-8】在“銷售”表中查詢每件商品的商品編號 select distinctdistinct 商品編號 from 銷售 消除查詢結(jié)果中的重復(fù)記錄3030基本的查詢n 2.2.選擇選擇n 從表中選出滿足條件的記錄，相當(dāng)于關(guān)系代數(shù)中的選擇運(yùn)算。 SELECT SELECT FROM FROM WHERE WHERE n 是一個邏輯表達(dá)式，由多個關(guān)系表達(dá)式通過邏輯運(yùn)算符連接而成。【例5-9】查詢“員工”表中所有已婚的女員工的員工編號、姓名和聘用日期。 select 員工編號, 姓名, 聘用日期 from 員工 where 性別=女 and 婚否=yes【例5-10

21、】查詢“商品”表中商品編號以“D”打頭的所有商品記錄。 select * from 商品 where 商品編號 like D* 3131基本的查詢n 3.排序排序n 在SELECT語句中使用ORDER BY子句可以對查詢結(jié)果按照一個或多個列的升序（ASC）或降序（DESC）排列，默認(rèn)是升序默認(rèn)是升序。 ORDER BY ORDER BY ASC|DESC ASC|DESCn 可以是字段名，或目標(biāo)列的序號，如1，2，。n 在目標(biāo)列前加上TOPTOP短語短語，可以從查詢結(jié)果中選擇排在前面的若干條記錄（用數(shù)值或百分比指定）。 SELECT TOP PERCENT ORDER BY ASC|DESC3

22、232基本的查詢【例5-11】查詢“商品”表中銷售價在900-1200之間（包括900和1200）的商品記錄，并按銷售價降序排列。 select * from 商品 where 價格 between 900 and 1200 order by order by 價格價格 descdesc【例5-12】查詢“銷售”表中員工編號為“001”、“003”和“005”的員工的銷售記錄，并按“員工編號”排序，對同一員工再按“銷售日期”降序排列。 select * from 銷售 where 員工編號 in(001,003,005) order by order by 員工編號員工編號, , 銷售日期銷售

23、日期 descdesc【例5-13】查詢“商品”表中銷售價最低的3種商品的商品編號、品牌和銷售價。 select top 3top 3 商品編號,品牌,銷售價 from 商品 order by order by 銷售價銷售價3333基本的查詢n 4.分組分組n在SELECT語句中使用使用GROUP BY子句可以按照某一列的值分組。 GROUP BY GROUP BY HAVING HAVING n 使用HAVING短語可以對分組后的結(jié)果進(jìn)行篩選。n 注意：HAVING短語必須和GROUP BY子句同時使用。n 分組查詢通常與分組查詢通常與SQLSQL聚合函數(shù)一起使用聚合函數(shù)一起使用，先按指定的

24、數(shù)據(jù)項分組，再對各組進(jìn)行總計，如計數(shù)、求和、求平均值等。如果未分組，則聚合函數(shù)作用于整個表。n 常用SQL聚合函數(shù)：COUNT、AVG、SUM、MIN、MAX3434基本的查詢【例5-14】統(tǒng)計“商品”表中的商品數(shù) Select count(*) as 商品數(shù) from 商品【例5-15】統(tǒng)計“商品”表中各類別商品的數(shù)量select 類別, count(*) as 各類別數(shù)量 from 商品 group by 類別【補(bǔ)充例】按“商品編號”統(tǒng)計每件商品的銷售總量。SELECT 商品編號, Sum(銷售量) AS 銷售總量FROM 銷售GROUP BY 商品編號3535基本的查詢【例5-16】在“

25、銷售”表中查詢有3次及3次以上銷售記錄的員工編號。select 員工編號 from 銷售 group by 員工編號 havinghaving count(*)=3n本例的執(zhí)行過程是：先用GROUP BY子句中指定的員工編號對“銷售”表進(jìn)行分組，將員工編號相同的記錄分為一組，然后用HAVING短語中的COUNT函數(shù)對每一組計數(shù)（即統(tǒng)計每個員工的小銷售次數(shù)），并將計數(shù)結(jié)果大于等于3的員工編號選出來作為查詢的最終結(jié)果。3636基本的查詢n【例5-17】在“銷售”表中查詢2009年有2次及2次以上銷售記錄的員工編號select 員工編號 from 銷售 where year(銷售日期)=2009 g

26、roup by 員工編號 havinghaving count(*)=2n當(dāng)WHERE子句、GROUP BY子句和HAVING短語同時出現(xiàn)在一個查詢語句中時，先執(zhí)行WHERE子句，從表中選取滿足條件的記錄，然后執(zhí)行GROUP BY子句對選取的記錄進(jìn)行分組，再執(zhí)行HAVING短語從分組結(jié)果中選取滿足條件的組。37375.4.3 連接查詢n 連接查詢同時涉及2個或多個表的數(shù)據(jù)。n 連接查詢需要指定2個表的連接條件，有2種格式：n 在 Where 子句中指定連接條件 SELECT SELECT FROM FROM , 2 WHERE WHERE . = = .2n 在 FROM 子句中指定連接條件S

27、ELECT SELECT FROM FROM INNER JOIN | LEFT JOIN | RIGHT JOIN INNER JOIN | LEFT JOIN | RIGHT JOIN 2 ON ON . = = .2n INNER JOIN 內(nèi)部連接,LEFT JOIN 左外部連接,RIGHT JOIN 右外部連接 n 連接條件中的連接字段通常為2個表中的公共字段或語義相同的字段。3838連接查詢【例5-18】查詢每個員工的商品銷售記錄，要求查詢結(jié)果中包含員工編號、姓名、商品編號、銷售量和銷售日期。 select 員工.員工編號, 姓名, 商品編號, 銷售量, 銷售日期 from 員工,

28、 銷售 where 員工.員工編號 = 銷售.員工編號 n為簡化輸入，可以使用表的別名代替表名，別名在FROM子句中指定 select yg.員工編號, 姓名, 商品編號, 銷售量, 銷售日期 from 員工 as yg, 銷售 as xs where yg.員工編號 = xs.員工編號n 在FROM子句中指定連接條件： select yg.員工編號, 姓名, 商品編號, 銷售量, 銷售日期 from 員工 as yg inner join 銷售 as xs on yg.員工編號 = xs.員工編號3939連接查詢n【例5-19】查詢20102010年年每個員工的商品銷售記錄，要求查詢結(jié)果中包含員工編號、姓名、商品編號、銷售量和銷售日期。 select 員工.員工編號, 姓名, 商品編號, 銷售量, 銷售日期 from 員工, 銷售 where 員工.員工編號 = 銷售.員工編號 and year(銷售日期)=201040405.4.4 子查詢n 子查詢(或稱嵌套查詢)n 指在一個SELECT語句的WHEREWHERE子句中包含另一個子句中包含另一個SELECTSELECT語句語句n 子查詢必須用括號“( )”括起來。n 子查詢（內(nèi)層查詢）的結(jié)果用作建立其父查詢（外層查詢）的條件，因此，子查詢的結(jié)果必須有確定的值。n 利用子查詢可以將幾個簡單