第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL 單表查詢

1、數(shù)據(jù)庫(kù)系統(tǒng)概論數(shù)據(jù)庫(kù)系統(tǒng)概論an introduction to database system第三章第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言sqlsql第三章第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言sql3.1 sql概述3.2 學(xué)生課程數(shù)據(jù)庫(kù)3.3 數(shù)據(jù)定義3.4 數(shù)據(jù)查詢3.5 數(shù)據(jù)更新3.6 視圖3.7 小結(jié)3.1 sql概述概述sql的產(chǎn)生與發(fā)展n1970年，美國(guó)ibm研究中心的e.f.codd連續(xù)發(fā)表多篇論文，提出關(guān)系模型。n1972年，ibm公司開始研制實(shí)驗(yàn)型關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)system r，配制的查詢語(yǔ)言稱為square (specifying queries as

2、relational expression )語(yǔ)言，在語(yǔ)言中使用了較多的數(shù)學(xué)符號(hào)。n1974年，boyce和chamberlin把square修改為sequel (structured english query language )語(yǔ)言。后來(lái)sequel簡(jiǎn)稱為sql (structured query language )，即“結(jié)構(gòu)式查詢語(yǔ)言”，sql的發(fā)音仍為“sequel”?，F(xiàn)在sql已經(jīng)成為一個(gè)標(biāo)準(zhǔn) 。sqlsql數(shù)據(jù)庫(kù)的體系結(jié)構(gòu)數(shù)據(jù)庫(kù)的體系結(jié)構(gòu) 用戶用戶1用戶用戶2用戶用戶3用戶用戶4視圖視圖1視圖視圖1基本表基本表1基本表基本表2基本表基本表3基本表基本表4存儲(chǔ)文件存儲(chǔ)文件1存儲(chǔ)文

3、件存儲(chǔ)文件2存儲(chǔ)文件存儲(chǔ)文件3存儲(chǔ)文件存儲(chǔ)文件4圖圖3.1 sql3.1 sql數(shù)據(jù)庫(kù)的體系結(jié)構(gòu)數(shù)據(jù)庫(kù)的體系結(jié)構(gòu)sql用戶用戶viewbase tablestored filensql的特點(diǎn)n1. 綜合統(tǒng)一n2. 高度非過(guò)程化n3. 面向集合的操作方式n4. 以同一種語(yǔ)法結(jié)構(gòu)提供兩種使用方法n5. 語(yǔ)言簡(jiǎn)潔，易學(xué)易用n1. 綜合統(tǒng)一:sql語(yǔ)言綜合：數(shù)據(jù)定義語(yǔ)言ddl數(shù)據(jù)操縱語(yǔ)言dml數(shù)據(jù)控制語(yǔ)言dcl“什么事都可以用sql語(yǔ)言來(lái)做”n2、高度非過(guò)程化 只須說(shuō)“做什么”，不用管“怎么做”減輕了用戶的負(fù)擔(dān)，有利于提高數(shù)據(jù)獨(dú)立性n3、面向集合的操作方式n非關(guān)系數(shù)據(jù)模型：面向記錄nsql：集合操作n

4、4、以同一種語(yǔ)法結(jié)構(gòu)提供兩種使用方式n既可聯(lián)機(jī)交互操作，n也可以嵌入到高級(jí)語(yǔ)言中去5. 語(yǔ)言簡(jiǎn)捷，易學(xué)易用語(yǔ)言簡(jiǎn)捷，易學(xué)易用表表3.1 sql語(yǔ)語(yǔ)言言的的動(dòng)動(dòng)詞詞sql 功功 能能動(dòng)動(dòng) 詞詞數(shù)數(shù) 據(jù)據(jù) 定定 義義create， drop， alter數(shù)數(shù) 據(jù)據(jù) 查查 詢?cè)僺elect數(shù)數(shù) 據(jù)據(jù) 操操 縱縱insert，updatedelete數(shù)數(shù) 據(jù)據(jù) 控控 制制grant，revoke 核心功能九個(gè)動(dòng)詞開頭核心功能九個(gè)動(dòng)詞開頭第三章第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言sql3.1 sql概述3.2 學(xué)生課程數(shù)據(jù)庫(kù)（課本p82）3.3 數(shù)據(jù)定義3.4 數(shù)據(jù)查詢3.5 數(shù)據(jù)更新3.6 視

5、圖3.7 小結(jié)3.2 數(shù)數(shù) 據(jù)據(jù) 定定 義義 表表3.2 sql的的 數(shù)數(shù) 據(jù)據(jù) 定定 義義 語(yǔ)語(yǔ) 句句操操 作作 方方 式式操操 作作 對(duì)對(duì)象象創(chuàng)創(chuàng) 建建刪刪 除除修修 改改表表cre atet abledropt ablealte rt able視視 圖圖cre atevie wdrop vie w索索 引引cre atein de xdropin de x3.3.1 3.3.1 模式的定義與刪除模式的定義與刪除 nsql模式的定義（創(chuàng)建）可用create schema語(yǔ)句定義，其基本句法如下： create schema模式名authorization用戶名 n刪除：drop語(yǔ)句的句法如

6、下：d r o p s c h e m a 模 式 名 cascaderestrictn其方式有兩種：cascade (級(jí)聯(lián)式)方式。restrict (約束式)方式。3.3.2 基本表的定義、刪除與修改基本表的定義、刪除與修改create table （ ， ， ）；n：所要定義的基本表的名字n：組成該表的各個(gè)屬性（列）n：涉及相應(yīng)屬性列的完整性約束條件n：涉及一個(gè)或多個(gè)屬性列的完整性約束條件 例題例題 例1 建立一個(gè)“學(xué)生”表student，它由學(xué)號(hào)sno、姓名sname、性別ssex、年齡sage、所在系sdept五個(gè)屬性組成。其中學(xué)號(hào)不能為空，值是唯一的，并且姓名取值也唯一。 crea

7、te table student (sno char(5) not null unique， sname char(20) unique， ssex char(1) ， sage int， sdept char(15)； 例題例題 （續(xù)）（續(xù)）snosnam essexsagesd ep t 字字 符符 型型 字字 符符 型型 字字 符符 型型 整整 數(shù)數(shù) 字字 符符 型型 長(zhǎng)長(zhǎng) 度度 為為5 5 長(zhǎng)長(zhǎng) 度度 為為2 20 0 長(zhǎng)長(zhǎng) 度度 為為1 1 長(zhǎng)長(zhǎng) 度度 為為1 15 5 不不 能能 為為 空空 值值定義基本表（續(xù)）定義基本表（續(xù)）n常用完整性約束n主碼約束： primary keyn

8、唯一性約束：uniquen非空值約束：not nulln參照完整性約束:foreign keyprimary key與 unique的區(qū)別？nprimary key在建立的時(shí)候會(huì)默認(rèn)地建立此field的索引，且此primary key可以作為作為另外的表的foreign key，nprimary key跟unique的區(qū)別區(qū)別是： primary key 一定是not null，而unique則無(wú)此限制n例2 建立一個(gè)“課程”表c，它由課程號(hào)cno，課程名稱cname，教師teacher組成，其中cno為主碼。ncreate table c(n cno char(5) ,n cname cha

9、r(10) , n teacher char(10),n primary key (cno);n或像書上寫的將primary key 直接定義在屬性列后。例題例題 （續(xù)）（續(xù)）n例3 建立一個(gè)“學(xué)生選課”表sc，它由學(xué)號(hào)sno、課程號(hào)cno，修課成績(jī)grade組成，其中(sno, cno)為主碼。ncreate table sc(n sno char(5) ,n cno char(3) , n grade int,n primary key (sno, cno), n foreign key(sno) references s(sno),n foreign key(cno) reference

10、s c(cno)n);基本數(shù)據(jù)類型基本數(shù)據(jù)類型n(1)數(shù)值型（db2)nsmallint 半字長(zhǎng)二進(jìn)制整數(shù)ninteger 全字長(zhǎng)二進(jìn)制整數(shù)ndecimal(p,q）或者dec（p,q)n壓縮十進(jìn)制數(shù)，共p位，其中小數(shù)點(diǎn)后q位nfloat 雙字長(zhǎng)浮點(diǎn)數(shù)n（2)字符串型ncharter(n)或char(n)nvarchar(n)n(3)時(shí)間型ndatentime二、修改基本表二、修改基本表alter table add 完整性約束 drop modify ；n：要修改的基本表nadd子句：增加新列和新的完整性約束條件ndrop子句：刪除指定的完整性約束條件nmodify子句：用于修改列名和數(shù)據(jù)類

11、型例題例題 例2 向student表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型。 alter table student add s_entence date；n不論基本表中原來(lái)是否已有數(shù)據(jù)，新增加的列一律為空值。 例3 將年齡的數(shù)據(jù)類型改為半字長(zhǎng)整數(shù)（假定原來(lái)為int）。 alter table student alter column sage smallint；或alter table student modify sage smallint；n注：修改原有的列定義有可能會(huì)破壞已有數(shù)據(jù)例題例題 例4 刪除學(xué)生姓名必須取唯一值的約束。alter table student drop uniqu

12、e(sname)； 語(yǔ)句格式（續(xù)）語(yǔ)句格式（續(xù)）n刪除屬性列 直接刪除屬性列可能引起違反完整性約束規(guī)則例：alter table student drop scome；alter table student drop column sdept ;(sql server)3.2 數(shù)數(shù) 據(jù)據(jù) 定定 義義 表表3.2 sql的的數(shù)數(shù)據(jù)據(jù)定定義義語(yǔ)語(yǔ)句句操操 作作 方方 式式操操 作作 對(duì)對(duì)象象創(chuàng)創(chuàng) 建建刪刪 除除修修 改改表表createt abledropt ablealtert able視視 圖圖createviewdrop view索索 引引createindexdropindex三、刪除基本

13、表三、刪除基本表 drop table ; 基本表刪除后，數(shù)據(jù)、表上的視圖、索引都刪除。有的系統(tǒng)如oracle，基本表刪除后，在表上的視圖往往仍然保留，但無(wú)法引用。例題例題例5 刪除student表 drop table student ;3.3.3 建立與刪除索引建立與刪除索引 n建立索引是加快查詢速度的有效手段n建立索引ndba或表的屬主（即建立表的人）根據(jù)需要建立n有些dbms自動(dòng)建立以下列上的索引n primary keyn uniquen維護(hù)索引n dbms自動(dòng)完成 n使用索引n dbms自動(dòng)選擇是否使用索引以及使用哪些索引n在sql86和sql89標(biāo)準(zhǔn)中，基本表沒(méi)有關(guān)鍵碼概念，可以

14、用索引機(jī)制來(lái)彌補(bǔ)。n索引屬于物理存儲(chǔ)的路徑概念，而不是邏輯的概念。n在定義基本表時(shí)，還要定義索引，就把數(shù)據(jù)庫(kù)的物理結(jié)構(gòu)和邏輯結(jié)構(gòu)混在一塊了。n因此在sql2中引入了主碼（主鍵）的概念，用戶在創(chuàng)建基本表時(shí)用主碼子句primary key直接定義主碼。n但至今大多數(shù)dbms仍使用索引機(jī)制，有索引創(chuàng)建和撤銷語(yǔ)句，其功能僅限于查詢時(shí)起作用。一、建立索引一、建立索引 n語(yǔ)句格式create unique cluster index on (, )；n用指定要建索引的基本表名字n索引可以建立在該表的一列或多列上，各列名之間用逗號(hào)分隔n用指定索引值的排列次序，升序：asc，降序：desc。缺省值：ascnu

15、nique表明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄ncluster表示要建立的索引是聚簇索引（p90）ncreate unique cluster index on (, )；例題例題 例6 為學(xué)生-課程數(shù)據(jù)庫(kù)中的student，course，sc三個(gè)表建立索引。其中student表按學(xué)號(hào)升序建唯一索引，course表按課程號(hào)升序建唯一索引，sc表按學(xué)號(hào)升序和課程號(hào)降序建唯一索引。create unique index stusno on student(sno)；create unique index coucno on course(cno)；create unique index s

16、cno on sc(sno asc，cno desc)； 建立索引建立索引 （續(xù)）（續(xù)）n唯一值索引n對(duì)于已含重復(fù)值的屬性列不能建unique索引n對(duì)某個(gè)列建立unique索引后，插入新記錄時(shí)dbms會(huì)自動(dòng)檢查新記錄在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個(gè)unique約束建立索引建立索引 （續(xù)（續(xù)）n聚簇索引n建立聚簇索引后，基表中數(shù)據(jù)也需要按指建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項(xiàng)順序與表中記錄的物理聚簇索引的索引項(xiàng)順序與表中記錄的物理順序一致順序一致例：create cluster index stus

17、name on student(sname)；在在student表的表的sname（姓名）列上建立一個(gè)聚簇索引，而（姓名）列上建立一個(gè)聚簇索引，而且且student表中的記錄表中的記錄將按照將按照sname值的升序存放值的升序存放 建立索引建立索引 （續(xù)）（續(xù)）n在一個(gè)基本表上最多只能建立一個(gè)聚簇索引n聚簇索引的用途：對(duì)于某些類型的查詢，可以提高查詢效率n聚簇索引的適用范圍n 很少對(duì)基表進(jìn)行增刪操作n 很少對(duì)其中的變長(zhǎng)列進(jìn)行修改操作 二、刪除索引二、刪除索引 drop index ；n刪除索引時(shí)，系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。例7 刪除student表的stusname索引。drop

18、 index stusname；索引一經(jīng)建立，就由系統(tǒng)來(lái)維護(hù)它，不需要用戶干預(yù)。建立索引減少查詢操作的時(shí)間，但如果數(shù)據(jù)增加刪改頻繁，系統(tǒng)會(huì)花費(fèi)許多時(shí)間來(lái)維護(hù)索引。所以，這時(shí)需要?jiǎng)h除一些不必要的索引。3.4 數(shù)據(jù)查詢數(shù)據(jù)查詢 3.4.1 單表查詢3.3.2 連接查詢3.3.3 嵌套查詢3.3.4 集合查詢3.3.5 select語(yǔ)句的一般格式數(shù)據(jù)查詢語(yǔ)句格式數(shù)據(jù)查詢語(yǔ)句格式概述概述 select all|distinct ， from ， where group by having order by asc|desc ； 語(yǔ)句格式語(yǔ)句格式nselect子句：指定要顯示的屬性列nfrom子句：指定

19、查詢對(duì)象(基本表或視圖)nwhere子句：指定查詢條件n group by子句：對(duì)查詢結(jié)果按指定列的值分組，該屬性列值相等的元組為一個(gè)組。通常會(huì)在每組中作用集函數(shù)。nhaving短語(yǔ)：篩選出只有滿足指定條件的組norder by子句：對(duì)查詢結(jié)果表按指定列值的升序或降序排序 select select查詢語(yǔ)句查詢語(yǔ)句nselectfromwhere句型在關(guān)系代數(shù)中最常用的式子是下列表達(dá)式： a1,an(f(r1rm) 這里r1、rm為關(guān)系，f是公式，a1、an為屬性。針對(duì)上述表達(dá)式，sql為此設(shè)計(jì)了selectfromwhere句型：select a1，anfrom r1，rmwhere f 這

20、個(gè)句型是從關(guān)系代數(shù)表達(dá)式演變來(lái)的，但where子句中的條件表達(dá)式f要比關(guān)系代數(shù)中公式更靈活。示例數(shù)據(jù)庫(kù)示例數(shù)據(jù)庫(kù) 學(xué)生-課程數(shù)據(jù)庫(kù)n學(xué)生表：student(sno，sname，ssex，sage，sdept)n課程表：course(cno，cname，cpno，ccredit)n 學(xué)生選課表：sc(sno，cno，grade) 3.4.1 單表查詢單表查詢 查詢僅涉及一個(gè)表，是一種最簡(jiǎn)單的查詢操作一、選擇表中的若干列二、選擇表中的若干元組三、對(duì)查詢結(jié)果排序四、使用集函數(shù)五、對(duì)查詢結(jié)果分組 selectselect語(yǔ)句完整的句法語(yǔ)句完整的句法n語(yǔ)句格式select all|distinct ，

21、from ， where group by having order by asc|desc ； 查詢指定列查詢指定列例1 查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名。select sno，snamefrom student； 例2 查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。select sname，sno，sdeptfrom student；查詢?nèi)苛胁樵內(nèi)苛欣? 查詢?nèi)w學(xué)生的詳細(xì)記錄。select sno，sname，ssex，sage，sdept from student； 或select *from student； 3. 查詢經(jīng)過(guò)計(jì)算的值查詢經(jīng)過(guò)計(jì)算的值 select子句的為表達(dá)式n算術(shù)表達(dá)式n字符串常量

22、n函數(shù):n列別名n等 3. 查詢經(jīng)過(guò)計(jì)算的值查詢經(jīng)過(guò)計(jì)算的值例4 查全體學(xué)生的姓名及其出生年份。select sname，2000-sagefrom student； 輸出結(jié)果： sname 2000-sage - - 李勇 1976 劉晨 1977 王名 1978 張立 1978 3. 查詢經(jīng)過(guò)計(jì)算的值查詢經(jīng)過(guò)計(jì)算的值例5 查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。select sname，year of birth: ，2000-sage， islower(sdept)from student； 例題（續(xù)）例題（續(xù)）輸出結(jié)果： sname year of birth

23、: 2000-sage islower(sdept) - - - - 李勇 year of birth: 1976 cs 劉晨 year of birth: 1977 is 王名 year of birth: 1978 ma 張立 year of birth: 1977 is 例例5.1 使用列使用列別名別名改變查詢結(jié)果的列標(biāo)題改變查詢結(jié)果的列標(biāo)題select sname name，year of birth: birth， 2000-sage birthday，islower(sdept) departmentfrom student；輸出結(jié)果： name birth birthday de

24、partment - - - - 李勇 year of birth: 1976 cs 劉晨 year of birth: 1977 is 王名 year of birth: 1978 ma 張立 year of birth: 1977 is二、選擇表中的若干元組二、選擇表中的若干元組 (投影優(yōu)化投影優(yōu)化)n消除取值重復(fù)的行n查詢滿足條件的元組 1. 消除取值重復(fù)的行消除取值重復(fù)的行n在select子句中使用distinct短語(yǔ)假設(shè)sc表中有下列數(shù)據(jù) sno cno grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 8

25、0all 與與 distinct 例6 查詢選修了課程的學(xué)生學(xué)號(hào)。(1) select sno from sc;或(默認(rèn) all) select all sno from sc; 結(jié)果： sno - 95001 95001 95001 95002 95002 例題（續(xù)）例題（續(xù)）(2) select distinct sno from sc; 結(jié)果： sno - 95001 95002 例題（續(xù)）例題（續(xù)）n注意 distinct短語(yǔ)的作用范圍是所有目標(biāo)列所有目標(biāo)列例：查詢選修課程的各種成績(jī)錯(cuò)誤的寫法select distinct cno，distinct gradefrom sc;正確的寫法

26、 select distinct cno，grade from sc; 2.查詢滿足條件的元組查詢滿足條件的元組表表3.3 常常用用的的查查詢?cè)儣l條件件查查 詢?cè)?條條 件件謂謂 詞詞比比 較較=，=，=，!=，!，!；not + 上上述述比比較較運(yùn)運(yùn)算算符符確確定定范范圍圍between and，not betw een and確確定定集集合合in，not in字字符符匹匹配配like，not like空空 值值is null，is not null多多重重條條件件and，orwhere子句常用的查詢條件子句常用的查詢條件(1) 比較大小比較大小在where子句的中使用比較運(yùn)算符n=，=，=

27、，!= 或 ，!，!， n邏輯運(yùn)算符not + 比較運(yùn)算符例8 查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。 select sname，sage from student where sage = 20； (2) 確定范圍確定范圍n使用謂詞 between and not between and 例10 查詢年齡在2023歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。 select sname，sdept，sagefrom studentwhere sage between 20 and 23； 例題（續(xù)）例題（續(xù)）例11 查詢年齡不在2023歲之間的學(xué)生姓名、系別和年齡。select

28、 sname，sdept，sagefrom studentwhere sage not between 20 and 23； (3) 確定集合確定集合使用謂詞 in , not in ：用逗號(hào)分隔的一組取值例12查詢信息系（is）、數(shù)學(xué)系（ma）和計(jì) 算機(jī)科學(xué)系（cs）學(xué)生的姓名和性別。select sname，ssexfrom studentwhere sdept in ( is，ma，cs );(3) 確定集合確定集合例13查詢既不是信息系、數(shù)學(xué)系，也不是計(jì)算 機(jī)科學(xué)系的學(xué)生的姓名和性別。select sname，ssexfrom student where sdept not in (

29、is，ma，cs );(4) 字符串匹配字符串匹配n not like escape ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 當(dāng)匹配模板為固定字符串時(shí)， 可以用 = 運(yùn)算符取代 like 謂詞 用 != 或 運(yùn)算符取代 not like 謂詞通配符通配符w% (百分號(hào)) 代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串n例：a%b表示以a開頭，以b結(jié)尾的任意長(zhǎng)度的字符串。如acb，addgb，ab 等都滿足該匹配串_ (下橫線) 代表任意單個(gè)字符n例：a_b表示以a開頭，以b結(jié)尾的長(zhǎng)度為3的任意字符串。如acb，afb等都滿足該匹配串escape 短語(yǔ)：短語(yǔ)：n當(dāng)用戶要查詢的字符串本身就含

30、有 % 或 _ 時(shí)，要使用escape 短語(yǔ)對(duì)通配符進(jìn)行轉(zhuǎn)義。例題例題1) 匹配模板為固定字符串 例14 查詢學(xué)號(hào)為95001的學(xué)生的詳細(xì)情況。 select * from student where sno like 95001；等價(jià)于： select * from student where sno = 95001；例題（續(xù)）例題（續(xù)）2) 匹配模板為含通配符的字符串例15 查詢所有姓劉學(xué)生的姓名、學(xué)號(hào)和性別。 select sname，sno，ssex from student where sname like 劉%；例題（續(xù)）例題（續(xù)）匹配模板為含通配符的字符串（續(xù)）例16 查詢姓歐陽(yáng)

31、且全名為三個(gè)漢字的學(xué)生的姓名。 select sname from student where sname like 歐陽(yáng)_；例題（續(xù)）例題（續(xù)）匹配模板為含通配符的字符串（續(xù)）例17 查詢名字中第2個(gè)字為陽(yáng)字的學(xué)生的姓名和學(xué)號(hào)。 select sname，sno from student where sname like _陽(yáng)%；例題（續(xù)）例題（續(xù)）匹配模板為含通配符的字符串（續(xù)）例18 查詢所有不姓劉的學(xué)生姓名。 select sname，sno，ssex from student where sname not like 劉%；例題（續(xù)）例題（續(xù)）3) 使用換碼字符將通配符轉(zhuǎn)義為普通字符

32、 例19 查詢db_design課程的課程號(hào)和學(xué)分。 select cno，ccredit from course where cname like db_design escape ;例題（續(xù)）例題（續(xù)）使用換碼字符將通配符轉(zhuǎn)義為普通字符(續(xù))例20 查詢以db_開頭，且倒數(shù)第3個(gè)字符為 i的課程的詳細(xì)情況。 select * from course where cname like db_%i_ _ escape ；(5) 涉及空值的查詢涉及空值的查詢n 使用謂詞 is null 或 is not nulln “is null” 不能用 “= null” 代替，因?yàn)榭罩挡豢筛渌鼥|西作比較

33、。例21 某些學(xué)生選修課程后沒(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；例題例題(續(xù)續(xù)) 例22 查所有有成績(jī)的學(xué)生學(xué)號(hào)和課程號(hào)。 select sno，cno from sc where grade is not null；(6) 多重條件查詢多重條件查詢用邏輯運(yùn)算符and和 or來(lái)聯(lián)結(jié)多個(gè)查詢條件n and的優(yōu)先級(jí)高于orn 可以用括號(hào)改變優(yōu)先級(jí)可用來(lái)實(shí)現(xiàn)多種其他謂詞n not inn not between and 例題例題例23 查詢計(jì)算機(jī)系年齡在20歲以下

34、的學(xué)生姓名。 select sname from student where sdept= cs and sage=20 and sage=23；selectselect語(yǔ)句完整的句法語(yǔ)句完整的句法select all|distinct ， from ， where order by asc|desc ； group by having 表表 3.3 常用的查詢條件常用的查詢條件查查 詢?cè)?條條 件件謂謂 詞詞比比 較較=，=，=，!=，!，!；not + 上述比較運(yùn)算符上述比較運(yùn)算符確定范圍確定范圍between and，not between and確定集合確定集合in，not in字符匹

35、配字符匹配like，not like空空 值值is null，is not null多重條件多重條件and，or三、對(duì)查詢結(jié)果排序三、對(duì)查詢結(jié)果排序 使用order by子句n 可以按一個(gè)或多個(gè)屬性列排序n 升序：asc；降序：desc；缺省值為升序當(dāng)排序列含空值時(shí)nasc：排序列為空值的元組最后顯示ndesc：排序列為空值的元組最先顯示 對(duì)查詢結(jié)果排序（續(xù)）對(duì)查詢結(jié)果排序（續(xù)） 例24 查詢選修了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é)果查詢結(jié)果 sno

36、 grade 空值先顯示 - - 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55對(duì)查詢結(jié)果排序（續(xù)）對(duì)查詢結(jié)果排序（續(xù)） 例25 查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號(hào)升序排列，同一系中的學(xué)生按年齡降序排列。 select * from student order by sdept，sage desc； 按系號(hào)升序排列的基礎(chǔ)上，（同一系內(nèi)）年齡降序排列四、使用集函數(shù)四、使用集函數(shù) 5類主要集函數(shù)n計(jì)數(shù)count（distinct|all *）count（distinct|all ）n計(jì)算總和sum（disti

37、nct|all ）n 計(jì)算平均值avg（distinct|all ）使用集函數(shù)（續(xù)）使用集函數(shù)（續(xù)） 求最大值max（distinct|all ） 求最小值min（distinct|all ）distinct短語(yǔ)：在計(jì)算時(shí)要取消指定列中的重復(fù)值all短語(yǔ)：不取消重復(fù)值all為缺省值使用集函數(shù)使用集函數(shù) （續(xù)）（續(xù)）例26 查詢學(xué)生總?cè)藬?shù)。 select count(*) from student； 例27 查詢選修了課程的學(xué)生人數(shù)。 select count(distinct sno) from sc；注：用distinct以避免重復(fù)計(jì)算學(xué)生人數(shù)使用集函數(shù)使用集函數(shù) （續(xù)）（續(xù)）例28 計(jì)算1

38、號(hào)課程的學(xué)生平均成績(jī)。 select avg(grade) from sc where cno= 1 ； 例29 查詢選修1號(hào)課程的學(xué)生最高分?jǐn)?shù)。 select max(grade) from sc where cno= 1 ； 例30 查詢學(xué)生200215012選修課程的總學(xué)分?jǐn)?shù)。 select sum(ccredit) from sc,course where sno=200215012 and sc.cno= course.cno； (此例用到后面介紹的多表連接查詢)五、對(duì)查詢結(jié)果分組五、對(duì)查詢結(jié)果分組 使用group by子句分組 細(xì)化集函數(shù)的作用對(duì)象n 未對(duì)查詢結(jié)果分組，集函數(shù)將作用

39、于整個(gè)查詢結(jié)果n 對(duì)查詢結(jié)果分組后，集函數(shù)將分別作用于每個(gè)組 使用使用group by子句分組子句分組例31 求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。 select cno，count(sno) from sc group by cno； 結(jié)果 cno count(sno) 1 22 2 34 3 44 4 33 5 48對(duì)查詢結(jié)果分組對(duì)查詢結(jié)果分組 （續(xù)）（續(xù)）ngroup by子句的作用對(duì)象是查詢的中間結(jié)果表。n分組方法：按指定的一列或多列值分組，值相等的為一組。n使用group by子句后，select子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù)。 使用使用having短語(yǔ)篩選最終輸出結(jié)果短語(yǔ)篩選最終

40、輸出結(jié)果例32 查詢選修了3門以上課程的學(xué)生學(xué)號(hào)。 select sno from sc group by sno having count(*) 3； 例題例題例 查詢有3門以上課程是90分以上的 學(xué)生的學(xué)號(hào)及（90分以上的）課程數(shù) select sno, count(*) from sc where grade=90 group by sno having count(*)=3;使用使用having短語(yǔ)篩選最終輸出結(jié)果短語(yǔ)篩選最終輸出結(jié)果n只有滿足having短語(yǔ)指定條件的組才輸出nhaving短語(yǔ)與where子句的區(qū)別：作用對(duì)象不同nwhere子句作用于基表或視圖，從中選擇滿足條件的元組（分組前對(duì)元組進(jìn)行過(guò)濾）。nhaving短語(yǔ)作用于組，從中選擇滿足條件的組（分組后對(duì)分組進(jìn)行過(guò)濾）。 selectselect語(yǔ)句完整的句法語(yǔ)句完整的句法 (1)nselect語(yǔ)句完整的句法如下： select 目標(biāo)表的列名或列表達(dá)式序