第3章關(guān)系數(shù)據(jù)庫語言SQL2012.032目錄Contents3.1數(shù)據(jù)庫的用戶接口3.2SQL語言概況3.3SQL數(shù)據(jù)定義語言3.4SQL數(shù)據(jù)查詢語言3.5SQL數(shù)據(jù)操縱語言3.6SQL中的視圖3.7嵌入式SQL33.1數(shù)據(jù)庫的用戶接口一、數(shù)據(jù)庫操作與數(shù)據(jù)庫語言數(shù)據(jù)庫操作包括：數(shù)據(jù)定義在數(shù)據(jù)庫中創(chuàng)建、撤銷、修改數(shù)據(jù)模式。數(shù)據(jù)查詢在數(shù)據(jù)庫中查詢檢索所需的數(shù)據(jù)。數(shù)據(jù)操縱－也稱更新數(shù)據(jù)在數(shù)據(jù)庫中增加、刪除、修改數(shù)據(jù)。數(shù)據(jù)控制控制用戶對(duì)數(shù)據(jù)庫中數(shù)據(jù)的訪問權(quán)限。43.1數(shù)據(jù)庫的用戶接口一、數(shù)據(jù)庫操作與數(shù)據(jù)庫語言數(shù)據(jù)庫語言：DBMS提供的語言，以支持用戶進(jìn)行數(shù)據(jù)庫操作。包括：數(shù)據(jù)定義語言(DataDefinitionLanguage,DDL)定義、撤銷和修改數(shù)據(jù)模式－基表/視圖，索引/簇集查詢語言(QueryLanguage,QL)查詢數(shù)據(jù)數(shù)據(jù)操縱語言(DataManipulationLanguage,DML)數(shù)據(jù)的插入/刪除/修改，簡單的數(shù)值計(jì)算與統(tǒng)計(jì)功能數(shù)據(jù)控制語言(DataControlLanguage,DCL)數(shù)據(jù)的完整性、安全性、并發(fā)控制、故障恢復(fù)等53.1數(shù)據(jù)庫的用戶接口一、數(shù)據(jù)庫操作與數(shù)據(jù)庫語言數(shù)據(jù)庫語言的特點(diǎn)面向記錄(record-oriented)與面向集合(set-oriented)嵌入式(embedded)和交互式(interactive)過程性(procedural)與說明性(declarative)63.1數(shù)據(jù)庫的用戶接口數(shù)據(jù)庫語言的特點(diǎn)1面向記錄(record-oriented)與面向集合(set-oriented)

早期的數(shù)據(jù)庫操作(e.g.層次、網(wǎng)狀數(shù)據(jù)庫)是使用物理指針的導(dǎo)航式訪問(navigationalaccess)，一次操作一個(gè)記錄(onerecordonetime)，操作過程繁瑣，效率低下。相應(yīng)的數(shù)據(jù)庫語言稱面向記錄的語言(record-orientedlanguage)。關(guān)系數(shù)據(jù)庫使用聯(lián)想式訪問(associativeaccess)，即按數(shù)據(jù)的內(nèi)容訪問數(shù)據(jù)（按屬性值選取數(shù)據(jù)），一次操作得到一個(gè)記錄的集合。相應(yīng)的數(shù)據(jù)庫語言稱面向集合的語言(set-orientedlanguage)。

73.1數(shù)據(jù)庫的用戶接口數(shù)據(jù)庫語言的特點(diǎn)2嵌入式(embedded)和交互式(interactive)數(shù)據(jù)庫語言往往不是計(jì)算完備(computingcomplete)的語言。要實(shí)現(xiàn)數(shù)據(jù)“管理”與“計(jì)算”的集成，可以將數(shù)據(jù)庫語言嵌入(embeding)到高級(jí)語言(e.g.PASCAL,C,FORTRAN)，稱宿主語言(hostlanguage)中。因此，數(shù)據(jù)庫語言就有兩種：嵌入式和交互式。83.1數(shù)據(jù)庫的用戶接口數(shù)據(jù)庫語言的特點(diǎn)3過程性(procedural)與說明性(declarative)早期的數(shù)據(jù)庫語言(e.g.層次、網(wǎng)狀數(shù)據(jù)庫)是過程性的，使用這樣的語言進(jìn)行數(shù)據(jù)庫操作，程序員的負(fù)擔(dān)很重；而且編寫的應(yīng)用程序與數(shù)據(jù)的獨(dú)立性(independence)很差、可維護(hù)性很差。關(guān)系數(shù)據(jù)庫提供了非過程性（non-procedural）語言即說明性語言(只要告訴DBMS“Whattodo?”)。但某些過程性機(jī)制(e.g.存儲(chǔ)過程(storedprocedure)、計(jì)算流程控制，etc.)對(duì)方便用戶、提高數(shù)據(jù)庫性能是很有用的。因此，關(guān)系數(shù)據(jù)庫語言(e.g.SQL)后來又進(jìn)行了過程化擴(kuò)充，作為可選項(xiàng)提供給用戶。93.1數(shù)據(jù)庫的用戶接口二、用戶接口與前端開發(fā)工具用戶接口(userinterface)是DBMS提供給用戶操作數(shù)據(jù)庫的界面。用戶接口將用戶對(duì)數(shù)據(jù)庫的操作請(qǐng)求以數(shù)據(jù)庫語言的形式提交給系統(tǒng)，并接受系統(tǒng)的處理結(jié)果、將結(jié)果返回給用戶。用戶接口提供了兩種操作數(shù)據(jù)庫的方式：交互方式和批處理方式（即編寫應(yīng)用程序）。用戶接口風(fēng)格可有：文本的和GUI。前端開發(fā)工具(front-enddevelopmenttools)DBMS廠商或第三方提供的數(shù)據(jù)庫應(yīng)用集成化開發(fā)工具(e.g.ORACLEDeveloper/2000，PowerBuilder)。

10目錄Contents3.1數(shù)據(jù)庫的用戶接口3.2SQL語言概況3.3SQL數(shù)據(jù)定義語言3.4SQL數(shù)據(jù)查詢語言3.5SQL數(shù)據(jù)操縱語言3.6SQL中的視圖3.7嵌入式SQL113.2SQL語言概況一、SQL的歷史SEQUEL(StructuredEnglishQUEryLanguage),(IBMSystemR,BoyceandChamberlinproposed,1974)SQL-86(SQL1,

StandardQueryLanguage),(ANSI,1986)SQL-89(ANSI/ISO,1989)SQL-92(SQL2),(ANSI/ISO,1992)SQL-93(ANSI/ISO,1993)SQL-99(SQL3),(ANSI/ISO,1999)SQL-2008S—標(biāo)準(zhǔn)化：按照英語的結(jié)構(gòu)要求設(shè)計(jì)，可以嵌套Q—查詢：以查詢?yōu)橹鳎ㄔ鰟h改等功能L—語言：第四代非過程式描述性語言123.2SQL語言概況二、SQL的特點(diǎn)非過程化的說明性語言嵌入式和交互式相同/相似語法的語言一體化的統(tǒng)一語言關(guān)系數(shù)據(jù)庫的公共語言English-like、簡單易學(xué)的語言133.2SQL語言概況三、SQL標(biāo)準(zhǔn)與實(shí)現(xiàn)ISO的SQL標(biāo)準(zhǔn)SQL-89、SQL2以及SQL:1999(即SQL3)是國際標(biāo)準(zhǔn)，而各個(gè)RDBMS廠商提供的是SQL實(shí)現(xiàn)。SQL標(biāo)準(zhǔn)文本可參見：/~ftp/isowg3/dbl/BASEdocsSQL實(shí)現(xiàn)可參見：各廠商提供的技術(shù)文檔資料。14目錄Contents3.1數(shù)據(jù)庫的用戶接口3.2SQL語言概況3.3SQL數(shù)據(jù)定義語言3.4SQL數(shù)據(jù)查詢語言3.5SQL數(shù)據(jù)操縱語言3.6SQL中的視圖3.7嵌入式SQL153.3SQL數(shù)據(jù)定義語言SQL數(shù)據(jù)類型基表模式的創(chuàng)建基表模式的修改與撤銷其他模式的建立與撤銷163.3SQL數(shù)據(jù)定義語言一、SQL基本數(shù)據(jù)類型序號(hào)符號(hào) 數(shù)據(jù)類型 備注1 INT 整數(shù)2 SMALLINT 短整數(shù)3 DEC(m，n)十進(jìn)制數(shù)m為位數(shù),n為小數(shù)點(diǎn)后位數(shù)4 FLOAT 浮點(diǎn)數(shù)5 CHAR(n) 定長字符串n表示字符串位數(shù)6 VARCHAR(n)變長字符串n表示最大變長數(shù)7 BIT(n) 位串 n為位串長度8 BITVARYING(n)變長位串n為最大變長數(shù)9 DATE 日期10 TIME 時(shí)間11TIMESTAMP 時(shí)間戳1718192021223.3SQL數(shù)據(jù)定義語言SQL數(shù)據(jù)類型基表模式的創(chuàng)建基表模式的修改與撤銷其他模式的建立與撤銷233.3SQL數(shù)據(jù)定義語言二、基表的創(chuàng)建<表名>：所要定義的基本表的名字<列名>：組成該表的各個(gè)屬性（列）<列級(jí)完整性約束條件>：涉及相應(yīng)屬性列的完整性約束條件<表級(jí)完整性約束條件>：涉及一個(gè)或多個(gè)屬性列的完整性約束條件CREATETABLE

<表名>(<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>][,<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>]]…[,<表級(jí)完整性約束條件>]);243.3SQL數(shù)據(jù)定義語言二、基表的創(chuàng)建(cont.)常用完整性約束實(shí)體完整性約束：PRIMARYKEY唯一性約束：UNIQUE非空值約束：NOTNULL引用完整性約束：FOREIGNKEY

PRIMARYKEY與

UNIQUE的區(qū)別？253.3.2SQL數(shù)據(jù)定義功能[例1]

建立一個(gè)“部門”表dept，它由部門號(hào)deptno、部門名deptname、位置loc等屬性組成。其中部門號(hào)是主鍵，部門名取值不能為空，且唯一。部門：dept（deptno,dname,loc）

CREATETABLE

dept

(

deptno

INT

PRIMARYKEY,

dname

VARCHAR(10)

NOTNULLUNIQUE,

loc

VARCHAR(10)

CHECK(locIN(‘Shanghai’,’Nanjing’,‘Wuhan’,‘Xian’,‘Beijing’)));263.3.2SQL數(shù)據(jù)定義功能[例2]

建立一個(gè)“職員”表emp，它由工號(hào)、姓名、工種、主管經(jīng)理、雇用時(shí)間、薪水、傭金、所在部門等屬性組成。職員：emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)

CREATETABLE

emp(empno

INT

PRIMARYKEY,

ename

VARCHAR(10)

NOTNULL,

job

VARCHAR(9)

NOTNULL,

mgr

INT

REFERENCESemp(empno),

hiredate

DATE

DEFAULTSYSDATE,

sal

DEC(7,2)

CHECK(sal>1000.0)

comm

DEC(7,2)

DEFAULTNULL,

deptno

INT

NOTNULLREFERENCESdept(deptno)ONDELETECASCADE

)；273.3SQL數(shù)據(jù)定義語言SQL數(shù)據(jù)類型基表模式的創(chuàng)建基表模式的修改與撤銷其他模式的建立與撤銷283.3SQL數(shù)據(jù)定義語言三、基表模式的修改與撤消增加列ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]];<表名>

：要修改的基本表ADD子句：增加新列和新的完整性約束條件[例3]

向emp表增加“性別”列，其數(shù)據(jù)類型為字符型。ALTERTABLEempADDGENDERCHAR(2)NOTNULL；不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。293.3SQL數(shù)據(jù)定義語言三、基表模式的修改與撤消(cont.)刪除基表DROPTABLE<表名>;基表刪除，數(shù)據(jù)、表上的索引都刪除系統(tǒng)從數(shù)據(jù)字典中刪去有關(guān)該基表及其索引的描述[例4]

刪除emp表DROPTABLE

emp;刪除屬性列-間接刪除把表中要保留的列及其內(nèi)容復(fù)制到一個(gè)新表中刪除原表再將新表重命名(RENAME)為原表名303.3SQL數(shù)據(jù)定義語言三、基表模式的修改與撤消(cont.)修改屬性列ALTERTABLE

<表名>

MODIFY

<列名><數(shù)據(jù)類型>;[例5]

將emp表中工號(hào)改為字符類型，串長度改為8位。

ALTERTABLEempMODIFY

empnoCHAR(8)；注：修改原有屬性定義有可能會(huì)破壞已有數(shù)據(jù)補(bǔ)充定義主鍵ALTERTABLE<表名>ADDPRIMARYKEY(<列名表>);要求定義為主鍵的列名表必須滿足NOTNULL和唯一性條件撤銷主鍵定義ALTERTABLE<表名>DROPPRIMARYKEY;暫時(shí)撤銷主鍵定義，在插入新的元組時(shí)，可以提高系統(tǒng)的性能313.3SQL數(shù)據(jù)定義語言三、基表模式的修改與撤消(cont.)補(bǔ)充定義外鍵ALTERTABLE

<表名1>

ADDFOREIGNKEY[

<外鍵名>](<列名表>)

REFERENCES<表名2>

[ONDELETE{RESTRICT|CASCADE|SETNULL}];撤銷外鍵定義ALTERTABLE

<表名1>DROP<外鍵名>;323.3SQL數(shù)據(jù)定義語言SQL數(shù)據(jù)類型基表模式的創(chuàng)建基表模式的修改與撤銷其他模式的建立與撤銷333.3SQL數(shù)據(jù)定義語言四、其他模式對(duì)象的定義與撤消

別名(alias)用簡單的別名代替全名，書寫和輸入都比較方便由于各個(gè)用戶對(duì)同一數(shù)據(jù)對(duì)象可能有不同的命名，若為不同用戶定義不同的別名，則各用戶可以保留自己習(xí)慣的命名。定義別名CREATESYNONYM

<標(biāo)識(shí)符>

FOR

{<基表名>|<視圖名>};

撤銷別名DROPSYNONYM

<標(biāo)識(shí)符>;343.3SQL數(shù)據(jù)定義語言四、其他模式對(duì)象的定義與撤消(cont.)索引的建立與撤銷索引是物理存儲(chǔ)路徑，不屬于邏輯數(shù)據(jù)模式。建立索引是加快查詢速度的有效手段。建立索引DBA或表的創(chuàng)建者根據(jù)需要建立有些DBMS自動(dòng)建立以下列上的索引

PRIMARYKEYUNIQUE維護(hù)索引

DBMS自動(dòng)完成

使用索引

DBMS自動(dòng)選擇是否使用索引，以及使用哪些索引353.3SQL數(shù)據(jù)定義語言四、其他模式對(duì)象的定義與撤消(cont.)建立索引的語句格式

CREATE

[UNIQUE][CLUSTER]

INDEX<索引名>

ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)；用<表名>指定要建索引的基表名字索引可以建立在該表的一列或多列上，各列名之間用逗號(hào)分隔用<次序>指定索引值的排列次序，升序(ASC),降序(DESC)。缺省值：ASCUNIQUE表明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄CLUSTER表示要建立的索引是聚簇索引363.3SQL數(shù)據(jù)定義語言四、其他模式對(duì)象的定義與撤消(cont.)建立索引唯一值索引(UNIQUE)對(duì)于已含重復(fù)值的屬性列不能建UNIQUE索引對(duì)某個(gè)列建立UNIQUE索引后，插入新記錄時(shí)DBMS會(huì)自動(dòng)檢查新記錄在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個(gè)UNIQUE約束聚簇索引(CLUSTER)建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項(xiàng)順序與表中記錄的物理順序一致第五章詳細(xì)介紹373.3SQL數(shù)據(jù)定義語言建立索引例子[例6]

為dept和emp兩個(gè)表建立索引。其中dept表按depto升序建唯一索引，emp表按deptno降序建唯一索引。CREATEUNIQUEINDEXDNOONdept(deptno);CREATEUNIQUEINDEXENOONemp(deptnoDESC);383.3SQL數(shù)據(jù)定義語言四、其他模式對(duì)象的定義與撤消(cont.)撤銷索引語句格式：DROPINDEX<索引名>；撤銷索引時(shí)，系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。[例7]

撤銷emp表的ENO索引。

DROPINDEX

ENO；39目錄Contents3.1數(shù)據(jù)庫的用戶接口3.2SQL語言概況3.3SQL數(shù)據(jù)定義語言3.4SQL數(shù)據(jù)查詢語言3.5SQL數(shù)據(jù)操縱語言3.6SQL中的視圖3.7嵌入式SQL403.4SQL數(shù)據(jù)查詢語言3.4.1SELECT語句的語法3.4.2

各種條件查詢舉例3.4.3查詢結(jié)果分組3.4.4查詢結(jié)果排序3.4.5

集合操作查詢413.4.1SELECT語句的語法一、語句格式SELECT

[ALL|DISTINCT]<列表達(dá)式>[,<列表達(dá)式>…]FROM

<表標(biāo)識(shí)>[<別名>][,<表標(biāo)識(shí)>[<別名>]…][WHERE<查詢條件>][GROUPBY<列標(biāo)識(shí)>[,<列標(biāo)識(shí)>…][HAVING<分組條件>]][ORDERBY<列標(biāo)識(shí)>|<序號(hào)>[ASC|DESC][,<列標(biāo)識(shí)>|<序號(hào)>[ASC|DESC]…]]；SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對(duì)象(基本表或視圖)WHERE子句：指定查詢條件GROUPBY子句：對(duì)查詢結(jié)果按指定列的值分組，該屬性列值相等的元組為一個(gè)組。通常會(huì)在每組中使用聚集函數(shù)。HAVING短語：篩選出只有滿足指定條件的組ORDERBY子句：對(duì)查詢結(jié)果表按指定列值的升序或降序排序

423.4.1SELECT語句的語法查詢語句的組成1)目標(biāo)子句：

SELECT

{ALL|[DISTINCT]}<列表達(dá)式>[，<列表達(dá)式>…]定義結(jié)果關(guān)系所需要的屬性目標(biāo)子句的構(gòu)造方式DISTINCT：對(duì)查詢結(jié)果中的重復(fù)行只返回其中一行，消除結(jié)果關(guān)系中的重復(fù)元組。（查詢結(jié)果為set）ALL：(缺省)。返回查詢結(jié)果的所有行，不去掉重復(fù)行。（查詢結(jié)果為bag）<列標(biāo)識(shí)>：用以標(biāo)識(shí)一個(gè)表中的一個(gè)列。形式：[<表名>∣<別名>.]<列名>。433.4.1SELECT語句的語法查詢語句的組成(cont.)1)目標(biāo)子句(cont.)：

SELECT

{ALL|[DISTINCT]}<列表達(dá)式>[，<列表達(dá)式>…]目標(biāo)子句的構(gòu)造方式(cont.)<列表達(dá)式>:

算術(shù)表達(dá)式。用于投影表中的列，并對(duì)列值進(jìn)行計(jì)算。定義如下：列標(biāo)識(shí)是一個(gè)<列表達(dá)式>；列標(biāo)識(shí)的SQL函數(shù)是一個(gè)<列表達(dá)式>；<列表達(dá)式>、常量、算術(shù)運(yùn)算符（+，-，*，/）及括號(hào)所組成的算術(shù)表達(dá)式是一個(gè)<列表達(dá)式>；*

表示一個(gè)表中的所有列,是一個(gè)特殊的<列表達(dá)式>。結(jié)果屬性的重命名<column_expression>AS<column_name>443.4.1SELECT語句的語法查詢語句的組成(cont.)2)范圍子句：FROM

<表標(biāo)識(shí)>[<別名>][,<表標(biāo)識(shí)>[<別名>]…]指定操作對(duì)象（被訪問的關(guān)系）<表標(biāo)識(shí)>與<列標(biāo)識(shí)><表標(biāo)識(shí)>：用以標(biāo)識(shí)一個(gè)表（基表、視圖或快照）。形式：[<模式名>.]<表名>。<列標(biāo)識(shí)>：用以標(biāo)識(shí)一個(gè)表中的一個(gè)列。形式：[<表名>∣<別名>.]<列名>。可以將FROM子句中對(duì)一個(gè)關(guān)系重新命名（即定義一個(gè)別名）<table_name><alias_name>主要用于關(guān)系自身的聯(lián)接運(yùn)算SELECT子句和FROM子句是查詢語句中必不可少的兩個(gè)部分453.4.1SELECT語句的語法查詢語句的組成(cont.)3)條件子句：WHERE<查詢條件>是查詢語句中的可選部分，用于定義查詢條件（即結(jié)果關(guān)系中的元組必須滿足的條件）包括“單個(gè)關(guān)系中的元組選擇條件”以及“關(guān)系與關(guān)系之間的連接條件”都需要在WHERE子句中通過一定的邏輯表達(dá)式顯式地表示出來。463.4.1SELECT語句的語法查詢語句的組成(cont.)3)條件子句：WHERE<查詢條件>(cont.)<查詢條件>邏輯表達(dá)式,用于選擇表中的行。定義如下：簡單條件：比較條件－－用于比較大?。?/p>

<列標(biāo)識(shí)>IS[NOT]NULL∣Θ<列表達(dá)式>∣Θ[ALL∣ANY∣SOME](<子查詢>)其中，Θ為關(guān)系運(yùn)算符。BETWEEN條件－－用于確定范圍：<列標(biāo)識(shí)>[NOT]BETWEEN<列表達(dá)式1>AND<列表達(dá)式2>LIKE條件－－用于字符匹配：

<列標(biāo)識(shí)>[NOT]LIKE‘xx…x’

其中，x可為字符（精確匹配）、_（單字符匹配）、%（任意多個(gè)字符匹配）。473.4.1SELECT語句的語法3)條件子句：WHERE<查詢條件>(cont.)<查詢條件>(cont.)簡單條件：IN條件－－用于屬于判斷：

<列標(biāo)識(shí)>[NOT]IN(常量1,常量2,…,常量n)∣(<子查詢>)EXISTS條件－－用于存在判斷：

[NOT]EXISTS（<子查詢>）

復(fù)合條件：簡單條件、邏輯運(yùn)算符(NOT,AND,OR)及括號(hào)所組成的邏輯表達(dá)式。

483.4SQL數(shù)據(jù)查詢語言3.4.1SELECT語句的語法3.4.2各種條件查詢舉例3.4.3查詢結(jié)果分組3.4.4查詢結(jié)果排序3.4.5

集合操作查詢493.4.2各種條件查詢舉例背景dept(deptno,dname,loc)emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)無條件查詢1)查詢?nèi)柯殕T的工號(hào)與姓名。

SELECTempno,ename

FROMemp;2)查詢?nèi)坎块T的號(hào)碼、名稱、所在地。

SELECTdeptno,dname,locFROMdept;或SELECT*FROMdept;

3)查詢職員的所有工種。

SELECT

DISTINCTjobFROMemp;503.4.2各種條件查詢舉例4)查詢每個(gè)職員提薪20%后的薪水。

SELECTempno,ename,sal*1.2

FROMemp;5)查詢單位的薪水種類。

SELECT

[ALL]salFROMemp;

SELECT

DISTINCTsalFROMemp;注意DISTINCT短語的作用范圍是所有目標(biāo)列例：查詢所有主管經(jīng)理的工號(hào)錯(cuò)誤的寫法

SELECTDISTINCTename,DISTINCTmgrFROMemp;正確的寫法

SELECTDISTINCTename,mgr

FROMemp;

這與SELECTename,mgr

FROMemp;查詢結(jié)果不同

513.4.2各種條件查詢舉例比較條件查詢6)查詢所有銷售人員的姓名、所在部門號(hào)。

SELECTename,

deptno

FROMemp

WHEREjob=’salesman’;7)查詢薪水超過5000的職員。

SELECTempno,ename,salFROMempWHEREsal>5000;8)查詢沒有傭金的職員。

SELECTempno,enameFROMempWHEREcommISNULL;

錯(cuò)誤：

SELECTempno,enameFROMempWHEREcomm=NULL;

范圍查詢

9)查詢薪水在3000與5000之間的職員。

SELECTempno,enameFROMempWHEREsalBETWEEN3000AND5000;523.4.2各種條件查詢舉例空值注意事項(xiàng)除is[not]null之外，空值不滿足任何查找條件如果null參與算術(shù)運(yùn)算，則該算術(shù)表達(dá)式的值為null如果null參與比較運(yùn)算，則結(jié)果可視為false。在SQL－92中視為unknown。如果null參與聚集運(yùn)算，則除count(*)之外其它聚集函數(shù)都忽略nullcount（*）與count（列名）的差別53空值 select

sum(G)

fromSC select

count(G)

fromSC select

count(*)

fromSCS#C#Gs1c180s1c290s1c395s2c185s2c2nulls3c2null36064543.4.2各種條件查詢舉例字符匹配查詢

%(百分號(hào))

代表任意長度（長度可以為0）的字符串例：a%b表示以a開頭，以b結(jié)尾的任意長度的字符串。如acb，addgb，ab等都滿足該匹配串_(下橫線)

代表任意單個(gè)字符例：a_b表示以a開頭，以b結(jié)尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串ESCAPE短語：當(dāng)用戶要查詢的字符串本身就含有%或_時(shí)，要使用ESCAPE‘<換碼字符>’短語對(duì)通配符進(jìn)行轉(zhuǎn)義。用\%去匹配%，用\_去匹配_553.4.2各種條件查詢舉例字符匹配查詢

10)找出姓名以M打頭的所有職員。

SELECTdeptno,enameFROMempWHEREenameLIKE‘M%’;11)找出姓名第三個(gè)字母為r的所有職員。

SELECTdeptno,enameFROMempWHEREenameLIKE‘__R%’;12)查詢DB_Design課程的課程號(hào)和學(xué)分。

SELECTCNO，CREDITFROMCourse

WHERECNAME

LIKE'DB\_Design'ESCAPE'\’;屬于判斷查詢

13)找出不是經(jīng)理、銷售人員的所有職員的薪水。

SELECTename,job,salFROMempWHEREjobNOTIN(‘manager’,‘salesman’);563.4.2各種條件查詢舉例連接查詢14)（多表連接）查詢職員Allen的工作所在地。

SELECTename,locFROMemp,deptWHEREename=’Allen’ANDemp.deptno=dept.deptno;15)（單表連接）查詢薪水超過其部門經(jīng)理的職員、及其經(jīng)理姓名。

SELECTworker.ename,manager.enameFROMempworker,empmanagerWHEREmanager.empno=worker.mgr

AND

worker.sal>manager.sal;16)（單表連接）查詢薪水比Jones高的職員。

SELECTx.empno,x.enameFROMempx,empyWHEREy.ename=‘Jones’ANDx.sal>y.sal;573.4.2各種條件查詢舉例存在查詢

17)查詢所有已雇用職員的部門。

SELECTdeptno,dnameFROMdeptWHEREEXISTS(SELECT*FROMempWHEREemp.deptno=dept.deptno);子查詢/嵌套查詢子查詢的SELECT語句不能有ORDERBY之句；子查詢可嵌套。18)查詢與Jones相同工種的所有職員。

SELECTempno,enameFROMempWHEREjob=（SELECTjobFROMempWHEREename=‘Jones’）;子查詢（subquery）/嵌套查詢（nestedquery）用IN更安全!583.4.2各種條件查詢舉例子查詢/嵌套查詢19)查詢比部門30中所有人薪水高的職員。

SELECTempno,ename,deptnoFROMempWHEREdeptno<>30ANDsal>ALL

(SELECTsalFROMempWHEREdeptno=30);20)(相關(guān)子查詢(correlatedsubquery))

查詢薪水超過其所在部門平均薪水的所有職員。

SELECTempno,ename,salFROMempxWHEREsal>(SELECTAVG(sal)

FROMempyWHEREy.deptno=x.deptno);謂詞！SQL函數(shù)相關(guān)SQL函數(shù)包括：－單行函數(shù)e.g.ABS－組函數(shù)e.g.AVG593.4SQL數(shù)據(jù)查詢語言3.4.1SELECT語句的語法3.4.2各種條件查詢舉例3.4.3查詢結(jié)果分組3.4.4查詢結(jié)果排序3.4.5

集合操作查詢603.4.3查詢結(jié)果分組

SELECT語句中，可使用GROUPBY子句對(duì)已選擇的行進(jìn)行分組，HAVING子句用于進(jìn)一步選擇已分的組，對(duì)每個(gè)已選中的組在查詢結(jié)果中只返回其單行總計(jì)信息。GROUPBY將表中的元組按指定列上值相等的原則分組，然后在每一分組上使用聚集函數(shù)，得到單一值。HAVING則對(duì)分組進(jìn)行選擇，只將聚集函數(shù)作用到滿足條件的分組上。613.4.3查詢結(jié)果分組GROUPBY子句將表按列的值分組，列值相同的分在一組。若GROUPBY子句后有多個(gè)列名，則從第一列名分組，再第二列，一直分下去。使用GROUPBY子句分組，細(xì)化SQL函數(shù)的作用對(duì)象

未對(duì)查詢結(jié)果分組，SQL函數(shù)將作用于整個(gè)查詢結(jié)果對(duì)查詢結(jié)果分組后，SQL函數(shù)將分別作用于每個(gè)分組

使用HAVING子句篩選最終輸出結(jié)果只有滿足HAVING子句指定條件的組才輸出HAVING子句與WHERE子句的區(qū)別：作用對(duì)象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。HAVING子句作用于分組，從中選擇滿足條件的元組。623.4.3查詢結(jié)果分組21)查詢每個(gè)部門的薪水最大值、最小值和平均值。

SELECTdeptno,MAX(sal),MIN(sal),AVG(sal)

FROMempGROUPBYdeptno;22)查詢整個(gè)公司的薪水最大值、最小值和平均值。

SELECTdeptno,MAX(sal),MIN(sal),AVG(sal)FROMemp;23)查詢每個(gè)部門中clerk人員的人數(shù)、平均薪水。

SELECTdeptno部門號(hào),COUNT(*),AVG(sal)FROMempWHEREjob=‘clerk’GROUPBYdeptno;24)查詢每個(gè)部門中salesman人員最高薪水、最低薪水，要求最高薪水、最低薪水相差超過1000。

SELECTdeptno,MAX(sal),MIN(sal)FROMempWHEREjob=‘salesman’GROUPBYdeptno

HAVINGMAX(sal)–MIN(sal)>1000;633.4.3查詢結(jié)果分組25)查詢每個(gè)部門中每個(gè)工種有多少職員。

SELECTdeptno,job,COUNT(job)FROMempGROUPBYdeptno,job;

可能的結(jié)果是：

DEPTNOJOBCOUNT（JOB）

11clerk211manager113manager213analyst413clerk312manager112clerk112salesman5643.4SQL數(shù)據(jù)查詢語言3.4.1SELECT語句的語法3.4.2各種條件查詢舉例3.4.3查詢結(jié)果分組3.4.4查詢結(jié)果排序

3.4.5

集合操作查詢653.4.4查詢結(jié)果排序?qū)Σ樵兘Y(jié)果排序使用ORDERBY子句

可以按一個(gè)或多個(gè)屬性列排序

升序：ASC；降序：DESC；

缺省值為升序排序時(shí)遵循“NULL值最大”原則ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示ORDERBY<列標(biāo)識(shí)>∣<序號(hào)>[ASC∣DESC][,<列標(biāo)識(shí)>∣<序號(hào)>[ASC∣DESC]…]663.4.4查詢結(jié)果排序26)查詢每個(gè)部門中每個(gè)工種有多少職員，要求查詢結(jié)果按deptno升序、job降序輸出。

SELECTdeptno,job,COUNT(job)FROMempGROUPBYdeptno,job;ORDERBYdeptno,2DESC;

可能的結(jié)果是：DEPTNOJOBCOUNT（JOB）11

manager

111

clerk

212

salesman

512

manager

112

clerk

113manager

213

clerk

313analyst

4673.4SQL數(shù)據(jù)查詢語言3.4.1SELECT語句的語法3.4.2各種條件查詢舉例3.4.3查詢結(jié)果分組3.4.4查詢結(jié)果排序3.4.5集合操作查詢

683.4.5集合操作查詢標(biāo)準(zhǔn)SQL直接支持的集合操作種類并操作(UNION)一般商用數(shù)據(jù)庫支持的集合操作種類并操作(UNION)交操作(INTERSECT)差操作(MINUS)這些運(yùn)算符可連接多個(gè)SELECT，括號(hào)可改變?nèi)笔〉倪\(yùn)算次序。693.4.5集合操作查詢并操作(UNION)形式:<查詢塊>UNION<查詢塊>參加UNION操作的各結(jié)果表的屬性數(shù)必須相同；對(duì)應(yīng)的屬性域也必須相同27)列出所有老銷售人員及剛剛雇用的新銷售人員名單。

SELECTempno,ename

FROMemp

WHEREjob=‘salesman’

UNION

SELECTempno,ename

FROMnew-emp;

并兼容！703.4SQL數(shù)據(jù)查詢語言回顧一下查詢語句的格式

SELECT

[ALL|DISTINCT]<列表達(dá)式>[,<列表達(dá)式>…]FROM

<表標(biāo)識(shí)>[<別名>][,<表標(biāo)識(shí)>[<別名>]…][WHERE<查詢條件>][GROUPBY<列標(biāo)識(shí)>[,<列標(biāo)識(shí)>…][HAVING<分組條件>]][ORDERBY<列標(biāo)識(shí)>|<序號(hào)>[ASC|DESC][,<列標(biāo)識(shí)>|<序號(hào)>[ASC|DESC]…]]；713.4SQL數(shù)據(jù)查詢語言查詢語句的處理順序合并FROM子句中表利用WHERE子句中的條件進(jìn)行元組選擇，丟棄不滿足WHERE條件的那些元組根據(jù)GROUPBY子句對(duì)保留下來的元組進(jìn)行分組利用HAVING子句中的條件對(duì)分組后的元組集合(group)進(jìn)行選擇，丟棄不滿足HAVING條件的那些元組集合根據(jù)SELECT子句進(jìn)行統(tǒng)計(jì)計(jì)算，生成結(jié)果關(guān)系中的元組根據(jù)ORDERBY子句對(duì)查詢結(jié)果進(jìn)行排序72目錄Contents3.1數(shù)據(jù)庫的用戶接口3.2SQL語言概況3.3SQL數(shù)據(jù)定義語言3.4SQL數(shù)據(jù)查詢語言3.5SQL數(shù)據(jù)操縱語言3.6SQL中的視圖3.7嵌入式SQL733.5SQL數(shù)據(jù)操縱語言3.5.1插入數(shù)據(jù)3.5.2修改數(shù)據(jù)3.5.3刪除數(shù)據(jù)743.5.1插入數(shù)據(jù)兩種插入數(shù)據(jù)方式單行直接插入多行間接插入753.5.1插入數(shù)據(jù)插入單個(gè)元組語句格式

INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]

VALUES

(<常量1>[，<常量2>]…)屬性列的順序可與表定義中的順序不一致。屬性列表可以被省略。在此情況下，表示要插入的是一條完整的元組，屬性的排列順序采用基表定義中的順序。常量表表示被插入的常量元組值。其中屬性值的數(shù)量及其排列順序必須與INTO子句的屬性名列表一致。每個(gè)屬性值可以為空，用NULL表示空值。功能將新元組插入指定表中。763.5.1插入數(shù)據(jù)28)在dept表中增加一個(gè)新部門。

INSERTINTOdept

VALUES

(14,‘production’,‘Wuhan’);29)在emp表中增加一個(gè)新的銷售員工，部門為14，部門經(jīng)理為158。

INSERTINTO

emp(empno,ename,job,mgr,hiredate,deptno)

VALUES

(298,‘Julian’,‘salesman’,158,2006-09-01,14)；

新插入的記錄在sal和comm列上取空值。等價(jià)于：INSERTINTO

empVALUES

(298,‘Julian’,‘salesman’,158,2006-09-01,NULL,NULL,14)；773.5.1插入數(shù)據(jù)多行間接插入語句格式

INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]

子查詢;功能:

將子查詢結(jié)果插入指定表中783.5.1插入數(shù)據(jù)30)(多行間接插入)將emp表中manager員工或傭金超過其工資50%的員工之相關(guān)數(shù)據(jù)拷貝到bonus表中。

INSERTINTObonus(e-name,work,salary,comm)SELECTename,job,sal,commFROMempWHEREjob=’manager’ORcomm>0.5*sal;注：大型DBMS廠商一般還提供非SQL手段的批量數(shù)據(jù)插入工具。e.g.OracleSQL*Loader可將Text數(shù)據(jù)、Excel數(shù)據(jù)批量插入基表。793.5SQL數(shù)據(jù)操縱語言3.5.1插入數(shù)據(jù)3.5.2修改數(shù)據(jù)3.5.3刪除數(shù)據(jù)803.5.2修改數(shù)據(jù)語句格式

UPDATE

<表名>

SET<列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]…[WHERE<條件>]；功能修改指定表中滿足WHERE子句條件的元組。即：用SET子句中的賦值語句修改相關(guān)元組上的屬性值。三種修改方式修改某一個(gè)元組的值修改多個(gè)元組的值帶子查詢的修改語句813.5.2修改數(shù)據(jù)31)(全部更新)

將emp表中所有員工的傭金置為NULL。

UPDATEemp

SETcomm=NULL;32)(條件更新)

將Jones提升為14部門的經(jīng)理，其薪水增加2000。

UPDATEemp

SET

job=’manager’,sal=sal+2000.0,

deptno=14

WHEREename=’Jones’;33)(復(fù)雜更新)

在西安或武漢的部門要撤消，其員工全部調(diào)入公司總部，職務(wù)不變，薪水和傭金分別是原部門平均值的1.1倍和1.5倍。

UPDATE

empx

SET

deptno

=(SELECTdeptnoFROMdept

WHEREdname=’headquarters’),

(sal,comm)=(SELECT

1.1*AVG(sal),1.5*AVG(comm)

FROMempy

WHEREy.deptno=x.deptno）

WHEREdeptnoIN(SELECTdeptnoFROMdept

WHEREloc=’Xian’OR

loc=’Wuhan’);823.5SQL數(shù)據(jù)操縱語言3.5.1插入數(shù)據(jù)3.5.2修改數(shù)據(jù)3.5.3刪除數(shù)據(jù)833.5.3刪除數(shù)據(jù)語句格式DELETEFROM<表名>[WHERE<條件>]；功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要?jiǎng)h除的元組缺省表示要修改表中的所有元組，但表仍作為一個(gè)空表存在。三種刪除方式刪除某一個(gè)元組的值刪除多個(gè)元組的值帶子查詢的刪除語句843.5.3刪除數(shù)據(jù)34)(全部刪除)

將emp表中全部行刪除。

DELETEFROMemp;[注]：區(qū)別DROPTABLE語句。35)(條件刪除)

將emp表無傭金及傭金低于500的salesman數(shù)據(jù)。

DELETEFROMemp

WHEREjob=’salesman’AND(commISNULLORcomm<500.0);DBMS在執(zhí)行插入語句時(shí)會(huì)檢查所插元組是否破壞表上已定義的完整性規(guī)則引用完整性不允許刪除級(jí)聯(lián)刪除85目錄Contents3.1數(shù)據(jù)庫的用戶接口3.2SQL語言概況3.3SQL數(shù)據(jù)定義語言3.4SQL數(shù)據(jù)查詢語言3.5SQL數(shù)據(jù)操縱語言3.6SQL中的視圖3.7嵌入式SQL863.6SQL中的視圖3.6.1視圖的概念3.6.2定義與撤銷視圖3.6.3視圖上查詢數(shù)據(jù)3.6.4視圖上更新數(shù)據(jù)873.6.1視圖的概念視圖(view)由其他表（基表/視圖）導(dǎo)出的虛表，可用于定義外模式。又稱為：導(dǎo)出表(derivedtable)視圖與基表(basetable)的區(qū)別視圖，如同基表（basetable）一樣，它是由行、列組成的二維表，在其中可查詢數(shù)據(jù)、更新數(shù)據(jù)（有限制）。但視圖中不直接包含數(shù)據(jù)（即不對(duì)應(yīng)物理數(shù)據(jù)文件），其數(shù)據(jù)包含在導(dǎo)出它的基表中。視圖僅僅保留了其構(gòu)造信息(有關(guān)視圖的定義信息，即邏輯定義)。因此，視圖又被稱為’虛表’(virtualtable)由于數(shù)據(jù)庫中只存放視圖的定義，不會(huì)出現(xiàn)數(shù)據(jù)冗余。當(dāng)用戶執(zhí)行視圖上的訪問操作時(shí)，DBMS將根據(jù)視圖的定義命令將用戶對(duì)于視圖的訪問操作轉(zhuǎn)換稱相應(yīng)基表上的訪問操作。基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變。883.6.1視圖的概念SQL視圖1視圖2基表1基表2基表3基表4存儲(chǔ)文件1存儲(chǔ)文件2外模式邏輯模式內(nèi)模式893.6.1視圖的概念視圖的作用1.視圖能夠簡化用戶的操作當(dāng)視圖中數(shù)據(jù)不是直接來自基本表時(shí)，定義視圖能夠簡化用戶的操作基于多張表連接形成的視圖基于復(fù)雜嵌套查詢的視圖含導(dǎo)出屬性的視圖2.視圖使用戶能以多種角度看待同一數(shù)據(jù)視圖機(jī)制能使不同用戶以不同方式看待同一數(shù)據(jù)，適應(yīng)數(shù)據(jù)庫共享的需要3.視圖對(duì)重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性4.視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)對(duì)不同用戶定義不同視圖，使每個(gè)用戶只能看到他有權(quán)看到的數(shù)據(jù)903.6SQL中的視圖3.6.1視圖的概念3.6.2定義與撤銷視圖3.6.3視圖上查詢數(shù)據(jù)3.6.4視圖上更新數(shù)據(jù)913.6.2定義與撤銷視圖建立視圖的語句格式

CREATEVIEW<視圖名>[(<列名>[，<列名>]…)]

AS<子查詢>[WITHCHECKOPTION]；創(chuàng)建一個(gè)<視圖名>為表名的視圖，對(duì)應(yīng)的數(shù)據(jù)查詢語句是<子查詢>語句。以<子查詢>作查詢所得到的查詢結(jié)果即是該視圖中的元組。如果省略視圖中的屬性命名，則用<子查詢>的SELECT子句中的屬性名作為視圖的屬性名。否則視圖中的屬性必須與<子查詢>的SELECT子句中的結(jié)果屬性一一對(duì)應(yīng)。明確指定視圖的所有列名:(1)某個(gè)目標(biāo)列是SQL函數(shù)或列表達(dá)式(2)需要在視圖中為某個(gè)列啟用新的更合適的名字923.6.2定義與撤銷視圖WITHCHECKOPTION用于約束視圖上的修改操作：如果允許在該視圖上執(zhí)行更新操作，則其更新后的結(jié)果元組仍然必須滿足視圖的定義條件。即通過該視圖插入或修改的新元組能夠通過該視圖上的查詢操作。DBMS執(zhí)行CREATEVIEW語句時(shí)只是把視圖的定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。在對(duì)視圖查詢時(shí)，按視圖的定義從基本表中將數(shù)據(jù)查出。933.6.2定義與撤銷視圖定義視圖例子36)創(chuàng)建全體員工工作所在地的視圖。

CREATEVIEWemp-loc

AS

SELECTempno,ename,locFROMemp,deptWHEREemp.deptno=dept.deptno;37)創(chuàng)建12號(hào)部門全體員工年薪視圖。

CREATEVIEW

emp-ann-sal(eno,ename,ann-sal)

AS

SELECTempno,ename,12*salFROMempWHEREdeptno=12;943.6.2定義與撤銷視圖撤銷視圖的語句格式

DROPVIEW<視圖名>該語句從數(shù)據(jù)字典中刪除指定的視圖定義由該視圖導(dǎo)出的其他視圖定義仍在數(shù)據(jù)字典中，但已不能使用，必須顯式刪除刪除基表時(shí)，由該基表導(dǎo)出的所有視圖定義都必須顯式刪除例：DROPVIEW

emp-loc;953.6SQL中的視圖3.6.1視圖的概念3.6.2定義與撤銷視圖3.6.3視圖上查詢數(shù)據(jù)3.6.4視圖上更新數(shù)據(jù)963.6.3視圖上查詢數(shù)據(jù)對(duì)用戶而言如同在基表中查詢數(shù)據(jù)一樣，使用SELECT語句。對(duì)系統(tǒng)而言需進(jìn)行視圖消解(resolution):從數(shù)據(jù)字典中取視圖定義；視圖上查詢轉(zhuǎn)換成基表上查詢；再執(zhí)行基表上查詢。973.6.3視圖上查詢數(shù)據(jù)查詢視圖例子38)查詢在南京工作的全體員工名單。

SELECTenameFROMemp-locWHEREloc=’Nanjing’;SELECTenameFROMemp,deptWHEREloc=’Nanjing’ANDemp.deptno=dept.deptno;對(duì)于在定義中使用“列表達(dá)式”或“GROUPBY/分組函數(shù)”而形成的視圖，其消解過程很麻煩。系統(tǒng)會(huì)先將視圖形成一個(gè)臨時(shí)表，然后再在臨時(shí)表上執(zhí)行查詢?？傊?，視圖消解由DBMS完成，用戶不必考慮。CREATEVIEWemp-locASSELECTempno,ename,locFROMemp,deptWHEREemp.deptno=dept.deptno;983.6SQL中的視圖3.6.1視圖的概念3.6.2定義與撤銷視圖3.6.3視圖上查詢數(shù)據(jù)3.6.4視圖上更新數(shù)據(jù)993.6.4視圖上更新數(shù)據(jù)視圖上更新就不象查詢那樣容易消解了。39)假如創(chuàng)建