數(shù)據(jù)庫(kù)原理與設(shè)計(jì)課件：第4章 SQL

1、第4章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL22022/7/184.1 SQL簡(jiǎn)介結(jié)構(gòu)化查詢語(yǔ)言SQL（Structured Query Language）是一種介于關(guān)系代數(shù)與關(guān)系演算之間的語(yǔ)言，是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言，是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言。SQL語(yǔ)言的版本包括：SQL-89，SQL-92， SQL-99(SQL3) 增加了面向?qū)ο蟮母拍?SQL2003(SQL4),SQL語(yǔ)言集數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)定義和數(shù)據(jù)控制功能于一體，充分體現(xiàn)了關(guān)系數(shù)據(jù)語(yǔ)言的特點(diǎn)和優(yōu)點(diǎn)32022/7/18SQL的特點(diǎn)綜合統(tǒng)一集數(shù)據(jù)定義語(yǔ)言DDL、數(shù)據(jù)操縱語(yǔ)言DML、數(shù)據(jù)控制語(yǔ)言DCL的功能于一體，可以完成數(shù)據(jù)庫(kù)

2、生命周期中的全部活動(dòng)。關(guān)系模型中實(shí)體和實(shí)體間的聯(lián)系都用關(guān)系來(lái)表示，使得操作符單一，每種操作只使用一個(gè)操作符。高度非過(guò)程化使用SQL語(yǔ)言，只需要提出“做什么”，而無(wú)需指明“怎么做”，無(wú)需了解存取路徑，提高了數(shù)據(jù)的獨(dú)立性面向集合的操作方式SQL語(yǔ)言采用集合操作方式，查詢、插入、刪除、修改操作的對(duì)象都是集合。42022/7/18SQL的特點(diǎn)以同一種語(yǔ)法結(jié)構(gòu)提供多種使用方式作為獨(dú)立的語(yǔ)言提供聯(lián)機(jī)交互工具，在終端鍵盤上直接鍵入SQL命令對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作,由DBMS來(lái)進(jìn)行解釋作為嵌入式語(yǔ)言 SQL語(yǔ)句能嵌入到高級(jí)語(yǔ)言程序中，使應(yīng)用程序充分利用SQL訪問(wèn)數(shù)據(jù)庫(kù)的能力不同方式下，SQL的語(yǔ)法結(jié)構(gòu)基本上是一致的

3、，提供了極大的靈活性和方便性。52022/7/18第4章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL4.1SQL簡(jiǎn)介 4.2SQL的系統(tǒng)結(jié)構(gòu)4.3SQL的數(shù)據(jù)定義4.4SQL的數(shù)據(jù)操縱4.5SQL中的視圖4.6SQL的數(shù)據(jù)控制4.7嵌入式SQL4.8小結(jié)62022/7/184.2 SQL的系統(tǒng)結(jié)構(gòu)SQL語(yǔ)言支持?jǐn)?shù)據(jù)庫(kù)的三級(jí)模式結(jié)構(gòu) 在SQL中，關(guān)系模式稱為基本表（Table），基本表的集合形成數(shù)據(jù)庫(kù)模式，對(duì)應(yīng)三級(jí)模式結(jié)構(gòu)的模式基本表在物理上與存儲(chǔ)文件相對(duì)應(yīng)，所有存儲(chǔ)文件的集合為物理數(shù)據(jù)庫(kù)。外模式由視圖(View) 組成視圖圖存儲(chǔ)文件視圖基本表基本表基本表SQL存儲(chǔ)文件存儲(chǔ)文件外模式模式內(nèi)模式72022/7/18

4、4.3 數(shù) 據(jù) 定 義SQL的數(shù)據(jù)定義功能主要包括定義表、定義視圖和定義索引，在SQL2中還增加了對(duì)SQL數(shù)據(jù)庫(kù)模式的定義 82022/7/184.3.1 模式的定義和刪除在SQL中，一個(gè)SQL模式(SQL Schema)由模式名、權(quán)限標(biāo)識(shí)符和模式中元素的描述符組成。權(quán)限標(biāo)識(shí)符指明擁有該模式的用戶或帳號(hào)模式元素包含一個(gè)數(shù)據(jù)庫(kù)應(yīng)用的表、視圖和索引等屬于同一應(yīng)用的表、視圖和索引等可以定義在同一模式中。在定義模式時(shí)可先給出模式名和權(quán)限標(biāo)識(shí)符，以后再定義其中的元素 ，語(yǔ)法格式：CREATE SCHEMA AUTHORIZATION 如果沒(méi)有指定，則隱含為92022/7/184.3.1 模式的定義和刪除

5、【例4-1】定義學(xué)生數(shù)據(jù)庫(kù)模式SST，用戶為SDBA。 CREATE SHEMA SST AUTHORITHZATION SDBA;例CREATE SCHEMA AUTHORIZATION WANG 沒(méi)有指定“模式名”，所以“模式名”隱含為用戶名WANG102022/7/18關(guān)于模式Oracle中：用戶帳號(hào)擁有的對(duì)象集稱為用戶的模式在SQL Server中，由于架構(gòu)的原因，User和Schema總有一層隱含的關(guān)系，讓用戶很少意識(shí)到其實(shí)User和Schema是兩種完全不同的概念。在SQL Server2000中，在某一個(gè)數(shù)據(jù)庫(kù)中創(chuàng)建了用戶Bosco，后臺(tái)默認(rèn)地創(chuàng)建了Schema 【Bosco】。

6、SQL Server2000中表的名稱體現(xiàn)數(shù)據(jù)庫(kù)、用戶和表名三方面的信息： database_name. owner. table_name在SQL Server2005中這種架構(gòu)被打破，User和Schema被分開112022/7/18SQL Server2005中的模式用Create User創(chuàng)建數(shù)據(jù)庫(kù)用戶時(shí)，可以為該用戶指定一個(gè)已經(jīng)存在的Schema作為默認(rèn)Schema，如果不指定，則該用戶所默認(rèn)的Schema即為dbo Schema，dbo Schema好比一個(gè)大的公共房間在SQL Server2005中創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)的時(shí)候，會(huì)有一些Schema包括進(jìn)去，被包括進(jìn)去的Schema有：d

7、bo， guest，sys， INFORMATION_SCHEMA, 還有一些角色Schema等等當(dāng)create table A時(shí),如果沒(méi)有指定特定的Schema,則A表創(chuàng)建在：1.如果當(dāng)前操作數(shù)據(jù)庫(kù)的用戶有默認(rèn)的Schema（在創(chuàng)建用戶的時(shí)候指定了），那么表A被創(chuàng)建在了默認(rèn)的Schema上。2. 如果當(dāng)前操作數(shù)據(jù)庫(kù)的用戶沒(méi)有默認(rèn)的Schema（即在創(chuàng)建User的時(shí)候默認(rèn)為空），創(chuàng)建在dbo Schema上。3.如果在創(chuàng)建表A的時(shí)候指定了特定的Schema做前綴，則表A被創(chuàng)建在了指定的 Schema上122022/7/18SQL Server2005中的模式現(xiàn)在如果登錄的用戶為Sue，該用戶有

8、一個(gè)默認(rèn)Schema也為Sue，那么如果現(xiàn)在有一條查詢語(yǔ)句為Select * from mytable, 那么搜尋每個(gè)房間（Schema）的順序是怎樣的呢？1. 首先搜尋sys.mytable （Sys Schema）2. 然后搜尋Sue.mytable (Default Schema)3. 最后搜尋 dbo.mytable (Dbo Schema)每個(gè)數(shù)據(jù)庫(kù)在創(chuàng)建后，有4個(gè)Schema是必須的(刪不掉),這4個(gè)Schema為dbo , guest, sysINFORMATION_SCHEMA132022/7/184.3.1 模式的定義和刪除定義模式后，實(shí)際上定義了一個(gè)命名空間，可以進(jìn)一步定義

9、該模式包含的數(shù)據(jù)庫(kù)對(duì)象，如表，視圖和索引等可以在創(chuàng)建模式的同時(shí)在模式定義中進(jìn)一步創(chuàng)建基本表、視圖、定義授權(quán)等CREATE SCHEMA 模式名 AUTHORIZATION 用戶名表定義子句視圖定義子句授權(quán)定義子句例創(chuàng)建相互依賴的 FOREIGN KEY 約束CREATE SCHEMA AUTHORIZATION ross CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT REFERENCES t2(c1) CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT REFERENCES t1(c1) 用其它方法完成需要執(zhí)行多個(gè)步

10、驟142022/7/184.3.1 模式的定義和刪除刪除模式語(yǔ)句：DROP SCHEMA模式名CASCADERESTRICTCASCADE (級(jí)聯(lián)式)方式表示在刪除模式的同時(shí)把該模式中所有的數(shù)據(jù)庫(kù)對(duì)象全部一起刪除RESTRICT (限制式)方式表示如果該模式中已經(jīng)定義了下屬的數(shù)據(jù)庫(kù)對(duì)象, 則拒絕該刪除語(yǔ)句的執(zhí)行152022/7/184.3.2 表的定義、刪除與修改1. 定義基本表CREATE TABLE （ ， ， ）；：所要定義的基本表的名字：組成該表的各個(gè)屬性（列）：涉及相應(yīng)屬性列的完整性約束條件：涉及一個(gè)或多個(gè)屬性列的完整性約束條件 162022/7/181. 定義基本表【例4-3】建立

11、學(xué)生表Student，表中屬性有：學(xué)號(hào)Sno,姓名Sname，年齡Sage，性別Ssex，學(xué)生所在系SdeptCREATE TABLE Student ( Sno CHAR(6) NOT NULL UNIQUE, Sname CHAR(8), Sage INT, Ssex CHAR(2), Sdept CHAR(12), CONSTRAINT C1 CHECK (Ssex IN(男,女), CONSTRAINT S_PK PRIMARY KEY(Sno);CONSTRAINT子句定義列級(jí)或表級(jí)約束，其格式為CONSTRAINT 172022/7/181. 定義基本表在SQL2中增加了定義域的語(yǔ)

12、句，可以用域名代替指定列的數(shù)據(jù)類型。如果有一個(gè)或多個(gè)表的屬性的域是相同的，通過(guò)對(duì)域的修改可以很容易地改變屬性的數(shù)據(jù)類型。域定義語(yǔ)句的格式為：CREATE DOMAIN ;例 CREATE DOMAIN Sdept_TYPE CHAR(12);域Sdept_TYPE創(chuàng)建后，定義學(xué)生表時(shí)，對(duì)列Sdept的類型定義可以用域名代替：Sdept Sdept_TYPE。 182022/7/181. 定義基本表 【例4-4】 CREATE TABLE Course (Cno CHAR(6) NOT NULL, Cname CHAR(20), Ccredit INT, PRIMARY KEY(Cno);CRE

13、ATE TABLE SC ( Sno CHAR(6) NOT NULL, Cno CHAR(6) NOT NULL, Grade INT CHECK (Grade BETWEEN 0 AND 100), CONSTRAINT SC_PK PRIMARY KEY(Sno,Cno), CONSTRAINT SC_FK1 FOREIGN KEY (Sno) REFERENCES Student(Sno), CONSTRAINT SC_FK2 FOREIGN KEY (Cno) REFERENCES Course(Cno);192022/7/181. 定義基本表例CREATE TABLE Course

14、 ( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT, FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); CREATE TABLE SC( Sno CHAR (9) , Cno CHAR (4) , Grade smallint, Primary key (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno);202022/7

15、/181. 定義基本表在定義基本表時(shí)，表所屬的數(shù)據(jù)庫(kù)模式一般被隱式指定，也可以顯式地在定義表時(shí)指定表所屬的數(shù)據(jù)庫(kù)模式名。如下語(yǔ)句在定義學(xué)生表時(shí)，同時(shí)指出學(xué)生表所在的模式為學(xué)生數(shù)據(jù)庫(kù)模式SST。CREATE TABLE SST.Student (Sno CHAR(6) NOT NULL UNIQUE,)；在創(chuàng)建模式語(yǔ)句中同時(shí)創(chuàng)建表212022/7/182. 修改基本表ALTER TABLE ADD DROP CASCADE | RESTRICT ALTER ;ADD子句用于增加新列，包括列名、數(shù)據(jù)類型和列級(jí)完整性約束 DROP子句用于刪除指定的列名，CASCADE表示刪除列時(shí)自動(dòng)刪除引用該列的視

16、圖和約束RECTRICT表示沒(méi)有視圖和約束引用時(shí)才能刪除該列，否則將拒絕刪除操作 ALTER子句用于修改列的定義，如修改列的數(shù)據(jù)類型或修改列的寬度 222022/7/182. 修改基本表【例4-5】在學(xué)生表Student中增加一列，列名為班級(jí)。ALTER TABLE Student ADD CLASS CHAR(8);不論基本表中原來(lái)是否已有數(shù)據(jù)，新增加的列一律為空值【例4-6】修改學(xué)生表Student中姓名列的長(zhǎng)度為20。ALTER TABLE Student ALTER Sname CHAR(20) ;修改原有的列定義有可能會(huì)破壞已有數(shù)據(jù)232022/7/183. 刪除基本表 DROP T

17、ABLE 表名 RESTRICTCASCADE;若選擇RESTRICT，則刪除的基本表不能是由 FOREIGN KEY 約束引用的表 , 不能有視圖，不能有觸發(fā)器，不能有存儲(chǔ)過(guò)程或函數(shù)等。如果存在這些依賴該表的對(duì)象，則此表不能被刪除若選擇CASCADE，則該表的刪除沒(méi)有限制條件。在刪除基本表的同時(shí)，相關(guān)的依賴對(duì)象，例如視圖等都將被一起刪除。在缺省的情況下，默認(rèn)為RESTRICT242022/7/183. 刪除基本表 SQL Server刪除表DROP TABLE 表名 數(shù)據(jù)、表上的索引都刪除,刪除基本表時(shí)，系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該基本表及其索引的描述 表上的視圖往往仍然保留，但無(wú)法引用DR

18、OP TABLE 不能用于刪除有 FOREIGN KEY 約束引用的表Oracle刪除表時(shí),自動(dòng)刪除表上所有索引,保留視圖定義(但失效),保留建立在此表上的存儲(chǔ)過(guò)程和函數(shù)(但失效), CASCADE選項(xiàng)下可以刪除有 FOREIGN KEY 約束引用的表 SQL Server 沒(méi)有 RESTRICT和CASCADE選項(xiàng)Oracle9i 沒(méi)有RESTRICT選項(xiàng)252022/7/184.3.3 索引的建立與刪除索引是一種數(shù)據(jù)結(jié)構(gòu)，可以提高查詢速度。大型關(guān)系數(shù)據(jù)庫(kù)中的索引的實(shí)現(xiàn)技術(shù)是數(shù)據(jù)庫(kù)管理系統(tǒng)實(shí)現(xiàn)中最重要的核心問(wèn)題。當(dāng)關(guān)系變得很大時(shí)，通過(guò)掃描所有關(guān)系中所有元組來(lái)找出那些(可能數(shù)量很少)匹配給定條

19、件的元組的操作方式代價(jià)太高。例如select * from Movie where studioName=Disneyand year = 1990方法1：取得全部元組，用where子句逐一匹配方法2：取得1990年的元組,判斷studio.Name =Disney。在year上創(chuàng)建索引，SQL查詢處理器進(jìn)行年份查詢時(shí)，僅對(duì)指定年份的元組測(cè)試 262022/7/184.3.3 建立與刪除索引 建立索引是加快查詢速度的有效手段可根據(jù)需要在一個(gè)表上建立一個(gè)或多個(gè)索引，以提供多種存取路徑，加快查找速度。也可不建建立索引DBA或表的屬主（即建立表的人）根據(jù)需要建立有些DBMS自動(dòng)建立以下列上的索引 P

20、RIMARY KEY， UNIQUE維護(hù)索引： DBMS自動(dòng)完成使用索引系統(tǒng)在存取數(shù)據(jù)時(shí)會(huì)自動(dòng)選擇是否使用索引以或合適的索引作為存取路徑，用戶不必也不能選擇索引272022/7/181. 建立索引 語(yǔ)句格式CREATE UNIQUE CLUSTER INDEX ON (, )指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號(hào)分隔指定索引值的排列次序，升序ASC，降序DESC。缺省值：ASCUNIQUE表明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄CLUSTER表示要建立的索引是聚簇索引282022/7/181. 建立索引【例4-8】在學(xué)生表Student的列學(xué)號(hào)上按升序建

21、立惟一索引。 CREATE UNIQUE INDEX S_SNO ON Student(Sno);對(duì)于已含重復(fù)值的屬性列不能建UNIQUE索引對(duì)某個(gè)列建立UNIQUE索引后，插入新記錄時(shí)DBMS會(huì)自動(dòng)檢查新記錄在該列上是否取了重復(fù)值【例4-9】在表Student上，班級(jí)按降序、年齡按升序建立索引。CREATE INDEX SCLASS_AGE ON Student（CLASS DESC, Sage ASC）;以上語(yǔ)句的執(zhí)行在學(xué)生表上建立班級(jí)為降序和年齡為升序排列的索引文件SCLASS-AGE，當(dāng)班級(jí)值相同時(shí)將按年齡升序排列 ； 292022/7/181. 建立索引依據(jù)索引的順序和數(shù)據(jù)庫(kù)的物理存

22、儲(chǔ)順序是否相同，索引分為兩類：聚簇索引、非聚簇索引非聚集索引與課本中的索引類似。數(shù)據(jù)存儲(chǔ)在一個(gè)地方，索引存儲(chǔ)在另一個(gè)地方，索引帶有指針指向數(shù)據(jù)的存儲(chǔ)位置302022/7/181. 建立索引聚簇索引對(duì)表的物理數(shù)據(jù)頁(yè)中的數(shù)據(jù)按列進(jìn)行排序，然后再重新存儲(chǔ)到磁盤上，即聚集索引與數(shù)據(jù)是混為一體的，它的葉節(jié)點(diǎn)中存儲(chǔ)的是實(shí)際的數(shù)據(jù)312022/7/181. 建立索引聚簇索引確定表中數(shù)據(jù)的物理順序。一個(gè)表只能包含一個(gè)聚簇索引。但該索引可以包含多個(gè)列（組合索引）。聚簇索引適用于： 很少對(duì)基表進(jìn)行增刪操作； 很少對(duì)其中的變長(zhǎng)列進(jìn)行修改操作 聚簇索引對(duì)于那些經(jīng)常要搜索范圍值的列特別有效使用聚集索引找到包含第一個(gè)值的

23、行后，便可以確保包含后續(xù)索引值的行在物理相鄰。有助于提高搜索范圍值類查詢的性能如果對(duì)從表中檢索的數(shù)據(jù)進(jìn)行排序時(shí)經(jīng)常要用到某一列，則可以將該表在該列上聚簇(物理排序),避免每次查詢?cè)摿袝r(shí)都進(jìn)行排序322022/7/182. 刪除索引 DROP INDEX ；刪除索引時(shí)，系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該索引的描述?！纠?-10】刪除學(xué)生表上建立的S_SNO索引。DROP INDEX S_SNO;332022/7/18索引選擇索引為性能所帶來(lái)的好處卻是有代價(jià)的。數(shù)據(jù)庫(kù)設(shè)計(jì)者需要對(duì)索引作一個(gè)折中的選擇，這種選擇是衡量數(shù)據(jù)庫(kù)設(shè)計(jì)成敗的重要因素對(duì)某個(gè)屬性使用索引能極大提高對(duì)該屬性上的值的檢索效率，使用到該屬性

24、時(shí)，還可以加快連接帶索引的表在數(shù)據(jù)庫(kù)中會(huì)占據(jù)更多的空間。維護(hù)索引的代價(jià)，對(duì)數(shù)據(jù)進(jìn)行插入、更新、刪除操作的命令所花費(fèi)的時(shí)間會(huì)更長(zhǎng)。應(yīng)評(píng)估兩個(gè)因素：數(shù)據(jù)的性質(zhì)和基于表的查詢的性質(zhì),來(lái)決定是否創(chuàng)建索引，應(yīng)確保對(duì)性能的提高程度大于在存儲(chǔ)空間和處理資源方面的代價(jià)342022/7/18索引選擇索引的選擇是數(shù)據(jù)庫(kù)設(shè)計(jì)中最困難的部分之一，需要估計(jì)對(duì)數(shù)據(jù)庫(kù)上使用什么樣的查詢組合以及其他操作如果某個(gè)關(guān)系的查詢操作對(duì)它的更新操作多，那么建立在該關(guān)系上的索引具有較高的效率。對(duì)于經(jīng)常和查詢where子句中的常量作比較的屬性，以及頻繁出現(xiàn)在連接條件中的屬性應(yīng)該建立索引更新操作頻繁，創(chuàng)建索引要謹(jǐn)慎。需要仔細(xì)估算更新和查詢數(shù)

25、量的相對(duì)比例來(lái)決定索引的使用。352022/7/184.4 SQL的數(shù)據(jù)操縱SQL的數(shù)據(jù)操縱包括數(shù)據(jù)的查詢、插入、修改和刪除4.4.1 數(shù)據(jù)查詢1. 單表查詢2. 連接查詢3. 嵌套查詢4. 集合查詢4.4.2 數(shù)據(jù)更新362022/7/184.4.1 查 詢語(yǔ)句格式SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對(duì)象(基本表或視圖)WHERE子句：指定查詢條件 GROUP BY子句：對(duì)查詢結(jié)果按指定列的值分組，該屬性列值相等的元組為一個(gè)組。通常

26、會(huì)在每組中作用集函數(shù)。HAVING短語(yǔ)：篩選出只有滿足指定條件的組ORDER BY子句：對(duì)查詢結(jié)果表按指定列值的升序或降序排序 372022/7/184.4.1 查 詢數(shù)據(jù)查詢是數(shù)據(jù)庫(kù)應(yīng)用的核心功能 基本結(jié)構(gòu)Select A1, A2, ., An From R1, R2, ., Rm Where P關(guān)系代數(shù)中基于關(guān)系是一個(gè)集合這樣的數(shù)學(xué)概念,因此,重復(fù)的元組不會(huì)在關(guān)系中出現(xiàn)。但在實(shí)踐中,要?jiǎng)h除查詢結(jié)果中的重復(fù)元組是相當(dāng)費(fèi)時(shí)的,所以在商用數(shù)據(jù)庫(kù)產(chǎn)品中,允許在關(guān)系和SQL表達(dá)式的結(jié)果中出現(xiàn)重復(fù)元組。SelectWhereFrom A1, A2, ., An(p(R1R2.Rm)382022/7/

27、181. 單表查詢【例4-11】查詢計(jì)算機(jī)系學(xué)生的學(xué)號(hào)和姓名。SELECT Sno, Sname FROM Student WHERE SD=CS;【例4-12】查詢選修了課的學(xué)生學(xué)號(hào)，并按學(xué)號(hào)升序排列。SELECT DISTINCT Sno FROM SC ORDER BY Sno;【例4-13】查詢年齡在18到25歲之間的學(xué)生信息。SELECT * FROM Student WHERE Sage BETWEEN 18 AND 25;392022/7/181. 單表查詢查詢經(jīng)過(guò)計(jì)算的值SELECT子句的不僅可以是表中的屬性列，也可以是表達(dá)式算術(shù)表達(dá)式 、字符串常量、函數(shù)、列別名等 【例4-1

28、4】查全體學(xué)生的姓名及其出生年份。 SELECT Sname，2000-Sage FROM Student；輸出結(jié)果： Sname 2000-Sage - - 李勇 1976 劉晨 1977 王名 1978 張立 1978 402022/7/181. 單表查詢查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。SELECT Sname, Year of Birth:, 2000-Sage, LOWER(Sdept) FROM Student;輸出結(jié)果： Sname Year of Birth: 2000-Sage LOWER(Sdept) 李勇 Year of Birth: 19

29、76 cs 劉晨 Year of Birth: 1977 is 王名 Year of Birth: 1978 ma 張立 Year of Birth: 1977 is使用列別名改變查詢結(jié)果的列標(biāo)題SELECT Sname AS NAME, Year of Birth: BIRTH, 2000-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT FROM Student;412022/7/181. 單表查詢消除取值重復(fù)的行在SELECT子句中使用DISTINCT短語(yǔ)假設(shè)SC表中有下列數(shù)據(jù) Sno Cno Grade - - - 95001 1 92 95001 2 85

30、 95001 3 88 95002 2 90 95002 3 80例 查詢選修了課程的學(xué)生學(xué)號(hào)(1)SELECT Sno FROM SC;或(默認(rèn) ALL) SELECT ALL Sno FROM SC;結(jié)果： Sno - 95001 95001 95001 95002 95002 (2) SELECT DISTINCT Sno FROM SC;結(jié)果： Sno - 95001 95002 422022/7/18注意 DISTINCT短語(yǔ)的作用范圍是所有目標(biāo)列例：查詢選修課程的各種成績(jī)錯(cuò)誤的寫法SELECT DISTINCT Cno，DISTINCT GradeFROM SC;正確的寫法 SEL

31、ECT DISTINCT Cno，Grade FROM SC;1. 單表查詢432022/7/181. 單表查詢WHERE子句常用的查詢條件比較表達(dá)式 比較算符 比較算符：=、=、=、（或!=）邏輯表達(dá)式 邏輯算符 邏輯算符：AND、OR、NOTBETWEEN （NOT）BETWEEN AND IN（NOT）IN （常量表列 或 SELECT語(yǔ)句）LIKE（NOT）LIKE 匹配字符串匹配符：“_”表示匹配一個(gè)字符，“%”表示匹配任意字符串NULL IS（NOT） NULLEXISTS（NOT）EXISTS （SELECT語(yǔ)句）442022/7/18(1) 比較大小在WHERE子句的中使用比較

32、運(yùn)算符=，=，=，!= 或 ，!，!， 邏輯運(yùn)算符NOT + 比較運(yùn)算符例 查詢所有年齡在20歲以下的學(xué)生姓名及其年齡 SELECT Sname, Sage FROM Student WHERE Sage = 20； 452022/7/18(2) 確定范圍使用謂詞 BETWEEN AND NOT BETWEEN AND 例 查詢年齡在2023歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23； (WHERE Sage = 20 AND Sage =23)例 查

33、詢年齡不在2023歲之間的學(xué)生姓名、系別和年齡SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23；462022/7/18(3) 確定集合使用謂詞 IN , NOT IN ：用逗號(hào)分隔的一組取值例查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。SELECT Sname，Ssex FROM Student WHERE Sdept IN ( IS，MA，CS ); ( WHERE Sdept = IS OR Sdept = MA OR Sdept = CS )例查詢既不是信息系、數(shù)學(xué)系，

34、也不是計(jì)算機(jī)科學(xué)系的學(xué)生的姓名和性別。SELECT Sname，Ssex FROM Student WHERE Sdept NOT IN ( IS，MA，CS )472022/7/18(4) 字符串匹配 NOT LIKE ESCAPE ：指定匹配模板匹配模板：固定字符串或含通配符的字符串當(dāng)匹配模板為固定字符串時(shí),可以用 = 運(yùn)算符取代 LIKE 謂詞,用 != 或 運(yùn)算符取代 NOT LIKE謂詞通配符% 代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串例：a%b表示以a開頭，以b結(jié)尾的任意長(zhǎng)度的字符串。如acb，addgb，ab 等都滿足該匹配串(下橫線) 代表任意單個(gè)字符例：a_b表示以a開頭，以b結(jié)

35、尾的長(zhǎng)度為3的任意字符串。如acb，afb等都滿足該匹配串482022/7/18(4) 字符串匹配 NOT LIKE ESCAPE ESCAPE 短語(yǔ)：當(dāng)用戶要查詢的字符串本身就含有 % 或 _ 時(shí)，要使用ESCAPE 短語(yǔ)對(duì)通配符進(jìn)行轉(zhuǎn)義492022/7/18(4) 字符串匹配匹配模板為含通配符的字符串例 查詢所有姓劉學(xué)生的姓名、學(xué)號(hào)和性別。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 劉%；例 查詢姓歐陽(yáng)且全名為三個(gè)漢字的學(xué)生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 歐陽(yáng)_ _

36、；502022/7/18(4) 字符串匹配使用換碼字符將通配符轉(zhuǎn)義為普通字符 例 查詢DB_Design課程的課程號(hào)和學(xué)分。 SELECT Cno，Ccredit FROM Course WHERE Cname LIKE DB_Design ESCAPE 例 查詢以DB_開頭，且倒數(shù)第3個(gè)字符為 i的課程的詳細(xì)情況。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE 512022/7/18(5) 涉及空值的查詢 使用謂詞 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “= NULL” 代替例 某些學(xué)生選修課程后

37、沒(méi)有參加考試，所以有選課記錄，但沒(méi)有考試成績(jī)。查詢?nèi)鄙俪煽?jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL；例 查所有有成績(jī)的學(xué)生學(xué)號(hào)和課程號(hào)。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL522022/7/18空值SQL允許屬性有一個(gè)特殊值NULL稱作空值。未知值：有值但是不知道是什么，例如未知生日不適用的值：例如配偶的名字保留的值：無(wú)權(quán)知道的值，例未公布的電話號(hào)碼空值的運(yùn)算空值不同于空白或零值。沒(méi)有兩個(gè)相等的空值。空值和任何值進(jìn)行算術(shù)運(yùn)算，結(jié)果仍為空值。執(zhí)行計(jì)算時(shí)消除空值很重要

38、，因?yàn)榘罩盗械哪承┯?jì)算（如平均值）會(huì)不準(zhǔn)確。 當(dāng)使用邏輯運(yùn)算符和比較運(yùn)算符，有可能返回 結(jié)果 UNKNOWN， 是與TRUE 和 FALSE 相同的布爾值空串指的是零長(zhǎng)度字符串當(dāng) m 為0或負(fù)數(shù)時(shí)，RIGHT(123, m) 返回空字符串RTRIM( ) 返回空字符串。532022/7/18(6) 多重條件查詢用邏輯運(yùn)算符AND和 OR來(lái)聯(lián)結(jié)多個(gè)查詢條件 AND的優(yōu)先級(jí)高于OR 可以用括號(hào)改變優(yōu)先級(jí)可用來(lái)實(shí)現(xiàn)多種其他謂詞 NOT IN NOT BETWEEN AND 例 查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名。 SELECT Sname FROM Student WHERE Sdept=

39、CS AND Sage20；542022/7/18對(duì)查詢結(jié)果排序 使用ORDER BY子句 可以按一個(gè)或多個(gè)屬性列排序 升序：ASC；降序：DESC；缺省值為升序空值將作為最大值排序ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示 552022/7/18對(duì)查詢結(jié)果排序例 查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC； 查詢結(jié)果Sno Grade - - 95010 95024 95007 92 95003 82 95010 82 9500

40、9 75 95014 61 95002 55562022/7/18使用集函數(shù) 主要集函數(shù)計(jì)數(shù)COUNT（DISTINCT|ALL *）COUNT（DISTINCT|ALL ）計(jì)算總和 SUM（DISTINCT|ALL ） 計(jì)算平均值 AVG（DISTINCT|ALL ）求最大值 MAX（DISTINCT|ALL ）求最小值 MIN（DISTINCT|ALL ）DISTINCT短語(yǔ)：在計(jì)算時(shí)要取消指定列中的重復(fù)值A(chǔ)LL短語(yǔ)：不取消重復(fù)值; ALL為缺省值572022/7/18使用集函數(shù)例 查詢學(xué)生總?cè)藬?shù)。 SELECT COUNT(*) FROM Student；例 查詢選修了課程的學(xué)生人數(shù)。

41、SELECT COUNT(DISTINCT Sno) FROM SC；注：用DISTINCT以避免重復(fù)計(jì)算學(xué)生人數(shù)例 計(jì)算1號(hào)課程的學(xué)生平均成績(jī)。 SELECT AVG(Grade) FROM SC WHERE Cno= 1 ；例 查詢選修1號(hào)課程的學(xué)生最高分?jǐn)?shù)。 SELECT MAX(Grade) FROM SC WHERE Cno= 1 ； 582022/7/18對(duì)查詢結(jié)果分組 使用GROUP BY子句分組 細(xì)化集函數(shù)的作用對(duì)象 未對(duì)查詢結(jié)果分組，集函數(shù)將作用于整個(gè)查詢結(jié)果 對(duì)查詢結(jié)果分組后，集函數(shù)將分別作用于每個(gè)組 分組方法:按指定的一列或多列值分組,值相等的為一組使用GROUP BY子

42、句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù)例 求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno；592022/7/18對(duì)查詢結(jié)果分組SELECT productid, orderid,quantity FROM orderhistSELECT productid,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productidproductidtotal_quantity115235345productidorderidquantity11511

43、102110222531153230productidtotal_quantity235Only rows thatsatisfy the WHERE clause are groupedSELECT productid,SUM(quantity) AS total_quantity FROM orderhist WHERE productid = 2 GROUP BY productid602022/7/18對(duì)查詢結(jié)果分組GROUP BY子句的作用對(duì)象是查詢的中間結(jié)果表使用HAVING短語(yǔ)篩選最終輸出結(jié)果只有滿足HAVING短語(yǔ)指定條件的組才輸出例 查詢選修了3門以上課程的學(xué)生學(xué)號(hào)。 SEL

44、ECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3；612022/7/18對(duì)查詢結(jié)果分組SELECT productid, orderid, quantity FROM orderhistSELECT productid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid HAVING SUM(quantity)=30productidtotal_quantity235345productidorderidquantity1151110211022253115323062

45、2022/7/18對(duì)查詢結(jié)果分組HAVING短語(yǔ)與WHERE子句的區(qū)別作用對(duì)象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。HAVING短語(yǔ)作用于組，從中選擇滿足條件的組 WHERE子句中不能使用聚集函數(shù)；而HAVING短語(yǔ)中可以使用聚集函數(shù)632022/7/184.4.1 數(shù)據(jù)查詢1. 單表查詢2. 連接查詢連接查詢是關(guān)系數(shù)據(jù)庫(kù)中最主要的查詢等值連接、自然連接、非等值連接查詢、自身連接查詢、外聯(lián)接查詢、復(fù)合條件連接查詢3. 嵌套查詢4. 集合查詢642022/7/182. 連接查詢 同時(shí)涉及多個(gè)表的查詢稱為連接查詢用來(lái)連接兩個(gè)表的條件稱為連接條件或連接謂詞 連接謂詞中的列名稱為

46、連接字段連接條件中的各連接字段類型必須是可比的，但不必是相同的SQL中連接查詢的主要類型 (1) 廣義笛卡爾積 (2) 等值(含自然連接)與非等值連接查詢 (3) 自身連接查詢 (4) 外連接查詢 (5) 復(fù)合條件連接查詢652022/7/18(1) 廣義笛卡爾積 不帶連接謂詞的連接（即沒(méi)有WHERE子句）。廣義笛卡爾積是兩表元組的交叉乘積，其連接的結(jié)果會(huì)產(chǎn)生沒(méi)有意義的元組，實(shí)際上很少使用。例： SELECT Student.* , SC.* FROM Student, SC662022/7/18(2) 等值與非等值連接查詢 等值連接、自然連接，和非等值連接等值連接連接運(yùn)算符為 = 的連接操作

47、 . = .任何子句中引用表1和表2中的同名屬性時(shí)，都必須加表名前綴引用唯一屬性名時(shí)可以加也可以省略表名前綴。672022/7/18等值連接例 查詢每個(gè)學(xué)生及其選修課程的情況。SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno = SC.Sno；Student表 SC表 Sno SnameSsexSageSdept95001 李勇 男 20 CS95002 劉晨 女 19 IS95003 王敏 女 18 MA95004 張立 男 19 ISSno CnoGrade95001 1 9295001 2 859500138895002290

48、95002380結(jié)果表 Student.Sno Sname Ssex Sage Sdept SC.Sno Cno Grade 95001 李勇 男 20 CS95001 1 92 95001 李勇 男 20CS95001 2 85 95001 李勇 男 20CS95001 3 88 95002 劉晨 女 19IS 95002 2 90 95002 劉晨 女 19IS95002 3 80 682022/7/18自然連接例 查詢每個(gè)學(xué)生及其選修課程的情況。SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno = SC.Sno；Student

49、表 SC表 Sno SnameSsexSageSdept95001 李勇 男 20 CS95002 劉晨 女 19 IS95003 王敏 女 18 MA95004 張立 男 19 ISSno CnoGrade95001 1 9295001 2 85950013889500229095002380結(jié)果表 Student.Sno Sname Ssex Sage Sdept SC.Sno Cno Grade 95001 李勇 男 20 CS95001 1 92 95001 李勇 男 20CS95001 2 85 95001 李勇 男 20CS95001 3 88 95002 劉晨 女 19IS 95

50、002 2 90 95002 劉晨 女 19IS95002 3 80 等值連接的一種特殊情況，把目標(biāo)列中重復(fù)屬性去掉SELECT Student.Sno,Sname,Ssex,Sage, Sdept,Cno,Grade FROM Student，SC WHERE Student.Sno = SC.Sno；結(jié)果表 Student.Sno Sname Ssex Sage Sdept Cno Grade 95001 李勇 男 20 CS 1 92 95001 李勇 男 20 CS 2 85 95001 李勇 男 20 CS 3 88 95002 劉晨 女 19 IS 2 90 95002 劉晨 女

51、19 IS 3 80 692022/7/18自然連接目標(biāo)列中的屬性可能需要加表前綴。SELECT buyer_name, sales.buyer_id, qty FROM buyers , sales WHERE buyers.buyer_id = sales.buyer_idsalesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameAdam BarrSean ChaiEva CoretsErin OMeliabuyer_id1234Resultbuyer_nameAdam BarrAdam BarrErin OMeliaEva C

52、oretsbuyer_idqty11431553711Erin OMelia41003702022/7/18連接查詢內(nèi)連接典型的聯(lián)接運(yùn)算，使用像 = 或 之類的比較運(yùn)算符）。包括相等聯(lián)接和自然聯(lián)接。 內(nèi)聯(lián)接使用比較運(yùn)算符根據(jù)每個(gè)表共有的列的值匹配兩個(gè)表中的行SQL Server 的語(yǔ)法格式，INNER 可以省略SELECT buyer_name, sales.buyer_id, qty FROM buyers INNER JOIN sales ON buyers.buyer_id = sales.buyer_idsalesbuyer_idprod_idqty114323151553711421

53、003buyersbuyer_nameAdam BarrSean ChaiEva CoretsErin OMeliabuyer_id1234Resultbuyer_nameAdam BarrAdam BarrErin OMeliaEva Coretsbuyer_idqty11431553711Erin OMelia41003712022/7/18連接查詢(2) 等值與非等值連接查詢非等值連接查詢 . .比較運(yùn)算符：、=、 b.buyer_idsales bbuyer_idprod_idqty114323151553711421003sales abuyer_idprod_idqty114323

54、151553711421003Resultbuyer14prod_d_id121b.buyer_d_ida.buyer_id242131311414535321242424752022/7/18(4) 外連接（Outer Join） 外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出外連接在表名后面加外連接操作符指定非主體表非主體表有一“萬(wàn)能”的虛行,該行全部由空值組成虛行可以和主體表中所有不滿足連接條件的元組進(jìn)行連接由于虛行各列全部是空值，因此與虛行連接的結(jié)果中，來(lái)自非主

55、體表的屬性值全部是空值 左外連接列出左邊關(guān)系中所有的元組右外連接列出右邊關(guān)系中所有的元組762022/7/18(4) 外連接（Outer Join） 例 查詢每個(gè)學(xué)生及其選修課程的情況包括沒(méi)有選修課程的學(xué)生SELECT Student.Sno,Sname,Ssex, Sage,Sdept,Cno,Grade FROM Student LEFT JOIN SC ON (Student.Sno = SC.Sno) Sno SnameSsexSageSdept95001 李勇 男 20 CS95002 劉晨 女 19 IS95003 王敏 女 18 MA95004 張立 男 19 ISSno Cno

56、Grade95001 1 9295001 2 85950019500295002 3 2 3 88 90 80結(jié)果： Student.Sno Sname Ssex Sage Sdept Cno Grade 95001 李勇 男 20 CS 1 92 95001 李勇 男 20 CS 2 85 95001 李勇 男 20 CS 3 88 95002 劉晨 女 19 IS 2 90 95002 劉晨 女 19 IS 3 80 95003 王敏 女 18 MA 95004 張立 男 19 IS772022/7/18（4）外連接（Outer Join ）外連接SQL Server 2000中左外連接的

57、表示SELECT buyer_name, sales.buyer_id, qty FROM buyers LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_idsalesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameAdam BarrSean ChaiEva CoretsErin OMeliabuyer_id1234Resultbuyer_nameAdam BarrAdam BarrErin OMeliaEva Coretsbuyer_idqty11431553711Er

58、in OMelia41003Sean Chai2NULL782022/7/18（5）復(fù)合條件連接WHERE子句中含多個(gè)連接條件時(shí)，稱為復(fù)合條件連接復(fù)合條件連接可以看作（普通）連接后得到的關(guān)系（表）又進(jìn)行一次選擇運(yùn)算例查詢選修2號(hào)課程且成績(jī)?cè)?0分以上的所有學(xué)生的 學(xué)號(hào)、姓名SELECT Student.Sno, student.Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND /* 連接謂詞*/ SC.Cno= 2 AND /* 其他限定條件 */ SC.Grade 90； /* 其他限定條件 */792022/7/18（5）復(fù)合條件連

59、接多表連接：連接操作可以是多個(gè)表以上的連接例 查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績(jī)。 SELECT Student.Sno，Sname，Cname，Grade FROM Student，SC，Course WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno；結(jié)果：Student.Sno Sname Cname Grade 95001 李勇 數(shù)據(jù)庫(kù) 92 95001 李勇 數(shù)學(xué) 85 95001 李勇 信息系統(tǒng) 88 95002 劉晨 數(shù)學(xué) 90 95002 劉晨 信息系統(tǒng) 80 802022/7/18（5）復(fù)合條件連接多表連接的結(jié)果可以

60、看作是兩表連接結(jié)果與第三表的連接，并依此類推SELECT buyer_name, prod_name, qty FROM buyers, sales , produce WHERE buyers.buyer_id = sales.buyer_id AND d_id = d_idproduceprod_idprod_name1234ApplesPearsOrangesBananas5Peachesbuyersbuyer_id1234buyer_nameAdam BarrSean ChaiEva CoretsErin OMeliasalesbuyer_id1