3.1SQL概述3.2數(shù)據(jù)定義3.3數(shù)據(jù)查詢3.4數(shù)據(jù)更新3.5視圖3.6案例2：活期儲(chǔ)蓄管理系統(tǒng)

數(shù)據(jù)庫(kù)上的查詢3/14/20233.1SQL概述

SQL(StructuredQueryLanguage)的字面意思是結(jié)構(gòu)化的查詢語(yǔ)言，但它的功能并不僅僅是查詢，它是關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)的標(biāo)準(zhǔn)語(yǔ)言。3.1.1SQL的主要功能SQL語(yǔ)言按照功能可以分為3大類：DDL（DataDefinitionLanguage）數(shù)據(jù)定義語(yǔ)言用于定義關(guān)系數(shù)據(jù)庫(kù)的模式、外模式和內(nèi)模式，以實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)基本表、視圖及索引文件的定義、修改和刪除等操作。最常用的DDL語(yǔ)句是CREATE、DROP和ALTER命令。DML（DataManipulationLanguage）數(shù)據(jù)操縱語(yǔ)言用于完成數(shù)據(jù)查詢和數(shù)據(jù)更新操作。其中數(shù)據(jù)更新指對(duì)數(shù)據(jù)進(jìn)行插入、刪除和修改操作。最常使用的DML語(yǔ)句是SELECT、INSERT、UPDATE和DELETE命令。3/14/2023

DCL（DataControlLanguage）數(shù)據(jù)控制語(yǔ)言用于控制對(duì)數(shù)據(jù)庫(kù)的訪問，服務(wù)器的關(guān)閉、啟動(dòng)等操作。常使用的DCL命令有：GRANT、REVOKE等。3.1.2SQL的特點(diǎn)語(yǔ)言簡(jiǎn)潔，風(fēng)格統(tǒng)一，易學(xué)易懂SQL語(yǔ)言接近英語(yǔ)，只用幾個(gè)英文單詞的組合就能完成所有功能，因此初學(xué)者非常容易上手。既是自含式語(yǔ)言，又是嵌入式語(yǔ)言作為自含式語(yǔ)言，SQL能夠獨(dú)立地用于聯(lián)機(jī)交互的使用方式。當(dāng)作為嵌入式語(yǔ)言使用時(shí)，SQL語(yǔ)句幾乎可以不加修改地嵌入到如VB、PB這樣的前端開發(fā)平臺(tái)上，利用前端工具的計(jì)算能力和SQL的數(shù)據(jù)庫(kù)操縱能力，可以快速地建立數(shù)據(jù)庫(kù)應(yīng)用程序。高度非過程化用SQL語(yǔ)言進(jìn)行數(shù)據(jù)操作，只要提出“做什么”，而無須知道“怎么做”，SQL語(yǔ)句的實(shí)現(xiàn)過程由系統(tǒng)自動(dòng)完成。3/14/2023大括號(hào)“{}”與豎線“|”表明此處為選擇項(xiàng)，在所列出的各項(xiàng)中僅需選擇一項(xiàng)。例如：{A|B|C|D}意思是A、B、C、D中取其一。SQL中的數(shù)據(jù)項(xiàng)（包括列項(xiàng)、表和視圖）分隔符為“，”；其字符串常數(shù)的定界符用單引號(hào)“′”表示。在編寫SQL語(yǔ)句時(shí)，遵從某種準(zhǔn)則以提高語(yǔ)句的可讀性，使其易于編輯，是很有好處的。以下是一些通常的準(zhǔn)則：SQL語(yǔ)句對(duì)大小寫不敏感為了提高SQL語(yǔ)句的可讀性，子句開頭的關(guān)鍵字通常采用大寫形式。SQL語(yǔ)句可寫成一行或多行，習(xí)慣上每個(gè)子句占用一行。關(guān)鍵字不能在行與行之間分開，并且很少采用縮寫形式。SQL語(yǔ)句的結(jié)束符為分號(hào)“；”，分號(hào)必須放在語(yǔ)句中的最后一個(gè)子句后面，但可以不在同一行。3/14/20233.2數(shù)據(jù)定義

SQL語(yǔ)言的數(shù)據(jù)定義功能包括定義數(shù)據(jù)庫(kù)、定義基本表、定義索引和定義視圖。其基本語(yǔ)句如表3-1所示。

表3-1SQL的數(shù)據(jù)定義語(yǔ)句操作對(duì)象操作方式創(chuàng)建語(yǔ)句刪除語(yǔ)句修改語(yǔ)句數(shù)據(jù)庫(kù)CREATEDATABASEDROPDATABASEALTERDATABASE基本表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEX視圖CREATEVIEWDROPVIEW3/14/2023基本表是獨(dú)立存儲(chǔ)在數(shù)據(jù)庫(kù)中的表在SQL中一個(gè)關(guān)系對(duì)應(yīng)一個(gè)基本表，一個(gè)（或多個(gè)）基本表對(duì)應(yīng)一個(gè)存儲(chǔ)文件，基本表對(duì)應(yīng)的數(shù)據(jù)必須在數(shù)據(jù)庫(kù)中存放。存儲(chǔ)文件的物理結(jié)構(gòu)對(duì)用戶而言是透明的，用戶無需關(guān)心。一個(gè)基本表可以根據(jù)需要帶一個(gè)或多個(gè)索引，索引也存放在存儲(chǔ)文件中。視圖是由一個(gè)或幾個(gè)基本表導(dǎo)出的，它的外部形式也是一個(gè)表，是由基本表中選取的行和列組成的視圖本身不獨(dú)立，它依附于基本表，在數(shù)據(jù)庫(kù)中不存放視圖對(duì)應(yīng)的數(shù)據(jù)，而只存放關(guān)于視圖的定義，因此視圖只是一個(gè)虛表。3/14/20233.2.2表的創(chuàng)建與刪除

創(chuàng)建基本表創(chuàng)建基本表的結(jié)構(gòu)是建立數(shù)據(jù)庫(kù)最重要的一步，其一般語(yǔ)法如下：

CREATETABLE<表名>（<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>][，<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>]][,…][,<表級(jí)完整性約束條件>][,…])；說明：<表名>是指要?jiǎng)?chuàng)建的基本表的名稱，該名稱應(yīng)符合具體DBMS的標(biāo)識(shí)符的命名規(guī)則。<列名>指的是表的屬性名稱。3/14/2023定義表的各個(gè)屬性時(shí)應(yīng)指出其相應(yīng)的數(shù)據(jù)類型和長(zhǎng)度，不同DBMS支持的數(shù)據(jù)類型不完全相同，表3-2列出的是IBMDB2SQL支持的主要數(shù)據(jù)類型：類型表示類型說明數(shù)值型SMALLINT半字長(zhǎng)二進(jìn)制整數(shù)INT或INTEGER全字長(zhǎng)二進(jìn)制整數(shù)DECIMAL(p[,q])十進(jìn)制數(shù)，共p位（含小數(shù)點(diǎn)），其中小數(shù)點(diǎn)后q位FLOAT雙字長(zhǎng)浮點(diǎn)數(shù)字符型CHAR(n)或CHARTER(n)長(zhǎng)度為n的定長(zhǎng)字符串,如果省略n,字符串長(zhǎng)度被假定為1VARCHAR(n)最大長(zhǎng)度為n的可變長(zhǎng)字符串日期時(shí)間型DATE日期型，格式為YYYY-MM-DDTIME時(shí)間型，格式為HH.MM.SSTIMESTAMP日期加時(shí)間3/14/2023關(guān)于完整性約束條件，需要說明以下幾點(diǎn)：完整性約束條件分為列級(jí)完整性約束條件和表級(jí)完整性約束條件，它們之間的區(qū)別在于：列級(jí)完整性約束條件只能用于列，而表級(jí)完整性約束條件能夠用于一張表中的多列。SQL的完整性約束條件有以下幾種：NOTNULL或NULL約束這個(gè)約束條件為列級(jí)完整性約束條件。NOTNULL為不允許該列存在空值，而NULL為允許該列存在空值。UNIQUE約束UNIQUE約束是惟一性約束。即不允許表中的某一列或者某幾列有重復(fù)的屬性值。DEFAULT約束DEFAULT約束為默認(rèn)值約束，是列級(jí)完整性約束條件。當(dāng)向表中插入一個(gè)新行時(shí)，如果對(duì)于特定列沒有指定數(shù)值，則使用DEFAULT子句指定的默認(rèn)值。3/14/2023

【例3-3】創(chuàng)建“學(xué)生表”student，它由學(xué)號(hào)id、姓名name、性別sex、班級(jí)號(hào)class、出生日期birthday等5個(gè)屬性組成。其中學(xué)號(hào)不能為空，值必須惟一，且姓名也必須惟一和非空。

CREATETABLEstudent(idCHAR(8)NOTNULLUNIQUE,nameVARCHAR(20)NOTNULLUNIQUE,sexCHAR(1)DEFAULT‘男’NOTNULL,classCHAR(4),birthdayDATE,sumintconstraintc2checksum<2000,CONSTRAINTC1CHECK(sexIN(‘男’，‘女’)))；3/14/2023

【例3-4】創(chuàng)建“班級(jí)信息表”class，它由班級(jí)號(hào)id、班級(jí)名name、班長(zhǎng)monitor等3個(gè)屬性組成，其中班級(jí)號(hào)是主鍵，班長(zhǎng)是外鍵，它是【例3-3】學(xué)生表中學(xué)號(hào)的某個(gè)值。

CREATETABLEclass(idCHAR(4)NOTNULL,nameVARCHAR(50)NOTNULL,monitorCHAR(8)CONSTRAINTC2PRIMARYKEY(id),CONSTRAINTC3FOREIGNKEY(monitor)REFERENCESstudent(id))；

說明：本例定義了2個(gè)列級(jí)約束、2個(gè)表級(jí)約束。CONSTRAINT子句定義的是表級(jí)約束，C2、C3是約束名，分別將id定義為主鍵，monitor定義為外鍵。

3/14/2023刪除基本表當(dāng)某個(gè)基本表不再需要時(shí)，可以使用DROPTABLE語(yǔ)句將它刪除。其一般語(yǔ)法為：DROPTABLE<表名>；

【例3-5】刪除student表。

DROPTABLEstudent；該語(yǔ)句一旦執(zhí)行，基本表的定義、數(shù)據(jù)、此表上建立的索引和視圖都將自動(dòng)被刪除掉。3/14/2023使用DROP子句刪除指定的完整性約束條件

【例3-7】刪除student表學(xué)生姓名必須取惟一值的約束條件。

ALTERTABLEstudentDROPUNIQUE(name)；使用MODIFY子句修改基本表的列定義

【例3-8】將student表name列的數(shù)據(jù)類型改為定長(zhǎng)字符串型。

ALTERTABLEstudentMODIFYnamechar(8)NOTNULL；注意：(1)修改列定義時(shí)，要將原來的列級(jí)約束條件寫上，否則原有的列級(jí)約束會(huì)不起作用。(2)修改列定義時(shí)，有可能會(huì)破壞已有的數(shù)據(jù)，應(yīng)事先作好備份工作。(3)SQL未提供刪除屬性列的語(yǔ)句，只能采取間接的方法。3/14/20233.2.4建立索引

索引的概念索引是建立在列上的一種數(shù)據(jù)庫(kù)對(duì)象，它對(duì)表中的數(shù)據(jù)提供邏輯順序，當(dāng)在數(shù)據(jù)庫(kù)表中搜索某一行時(shí)，可以通過使用索引來找到它的物理位置。索引建立后，什么時(shí)候使用索引以及使用哪一個(gè)索引（當(dāng)有多個(gè)索引存在時(shí)），由DBMS內(nèi)部根據(jù)情況自行決定，不需要人員干預(yù)。索引是動(dòng)態(tài)的，每當(dāng)數(shù)據(jù)庫(kù)表的數(shù)據(jù)更新一次，相應(yīng)的索引也隨之更新。

3/14/2023

建立索引在SQL語(yǔ)言中，建立索引使用CREATEINDEX命令，其一般語(yǔ)法為：CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][，<列名>[<次序>]][,…])；

說明：(1)表名是要建立索引的基本表的名字。(2)列名是被建立索引的列的名稱。索引可以建立在某一列或多個(gè)列上。(3)次序是指按照該列名的索引值的排列順序。次序可以取值A(chǔ)SC（升序）或DESC（降序），默認(rèn)值是ASC。(4)UNIQUE表示創(chuàng)建的索引是惟一索引，索引列上的數(shù)據(jù)不能有重復(fù)值。(5)CLUSTER表示要建立的是聚簇索引。聚簇索引是指索引項(xiàng)的順序與表中記錄的物理順序一致的索引。3/14/2023

刪除索引在SQL語(yǔ)言中，使用DROPINDEX命令刪除索引，其語(yǔ)法如下：DROPINDEX<索引名>；

【例3-11】刪除為student表建立的索引stu_cluind。

DROPINDEXstu_cluind；

刪除索引時(shí)，系統(tǒng)會(huì)同時(shí)從數(shù)據(jù)庫(kù)中刪去有關(guān)該索引的描述。對(duì)于數(shù)據(jù)庫(kù)系統(tǒng)而言，索引一經(jīng)建立，一般不應(yīng)隨意刪除。SQL沒有提供修改索引的語(yǔ)句，對(duì)于一些在使用中證明不合適的索引，只能先刪除后重建。3/14/20233.3數(shù)據(jù)查詢

3.3.1SELECT語(yǔ)句的結(jié)構(gòu)SQL語(yǔ)言提供的SELECT語(yǔ)句的一般格式如下：SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>][,…]FROM<表名或視圖名>[,<表名或視圖名>][,…][WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY]<列名2>[ASC|DESC]]；說明：SELECT語(yǔ)句的含義是，根據(jù)WHERE子句指定的條件，從FROM子句后面的基本表或視圖中找出滿足條件的記錄，再按照SELECT子句指定的目標(biāo)列表達(dá)式，選出這些記錄相應(yīng)的列形成結(jié)果集返回。其中，SELECT子句和FROM子句是必選的，而WHERE子句、GROUPBY子句、HAVING子句以及ORDERBY子句都是可選的。3/14/20233.3.2選擇行和列--選擇和投影運(yùn)算的實(shí)現(xiàn)下面以人事工資管理系統(tǒng)的員工表employee和部門表dept為例介紹SELECT語(yǔ)句的使用方法。

Employee(emp_id,ename,job,mgr_id,workdate,sal,comm,dept_id)

員工號(hào)姓名職位主管參加工作時(shí)間月薪崗位津貼部門號(hào)

Dept(dept_id，dname，tel)部門號(hào)，部門名稱，電話用下面語(yǔ)句創(chuàng)建表結(jié)構(gòu)：

CREATETABLEemployee(emp_idCHAR(4)PRIMARYKEY,enameVARCHAR(20),jobVARCHAR(9),mgr_idCHAR(4),workdateDATE,salSMALLINT,commSMALLINT,dept_idCHAR(2)NOTNULLFOREIGNKEY(dept_id)REFERENCESDept(dept_id),FOREIGNKEY(mgr_id)REFERENCESemployee(emp_id));3/14/2023【例3-14】查詢員工號(hào)為1001的員工姓名及部門號(hào)。

SELECTename,dept_idFROMemployeeWHEREemp_id=’1001’;

投影運(yùn)算的實(shí)現(xiàn)查詢表的全部列

【例3-15】查詢部門表中部門號(hào)為11的全部?jī)?nèi)容。

SELECTdept_id,dname,telFROMdeptWHEREdept_id=’11’;在SQL語(yǔ)言中，可以用星號(hào)“*”代表所有列名，列的顯示順序與基本表中列的順序一致。

【例3-16】下面語(yǔ)句的結(jié)果等價(jià)于【例3-15】的語(yǔ)句。

SELECT*FROMdeptWHEREdept_id=’11’;3/14/2023查詢表的部分列

【例3-17】查詢?nèi)w員工的員工號(hào)，姓名和參加工作時(shí)間。

SELECTemp_id,ename,workdateFROMemployee;查詢經(jīng)過計(jì)算的值SELECT子句的<目標(biāo)列表達(dá)式>不僅可以是基本表的屬性，也可以是表達(dá)式，包括算術(shù)表達(dá)式、字符串常量和函數(shù)等。

【例3-18】查詢?nèi)w員工的姓名及年薪。

SELECTename,sal*12FROMemployee;

結(jié)果:enamesal*12吳偉15600岳玲13200王斌18000徐歡96003/14/20233.3.3SQL的運(yùn)算符SQL語(yǔ)言使用的運(yùn)算符包括算術(shù)運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符等。

算術(shù)運(yùn)算符算術(shù)運(yùn)算符有4種：+、-、*、/

比較運(yùn)算符基本比較運(yùn)算符共9種：=、>、<、<=、>=、！=或<>、！>（不大于）、！<（不小于）特殊比較運(yùn)算符有4類：BETWEEN…AND…、IN、LIKE和ISNULL3/14/2023運(yùn)算符BETWEEN…AND…運(yùn)算符BETWEEN…AND…是用于確定記錄的范圍，即某屬性值在指定的范圍之內(nèi)（包括邊界值）的記錄，其中BETWEEN后面是下限，AND后面是上限。

【例3-21】查詢?cè)滦皆?000到1500之間的員工姓名及月薪。

SELECTename,salFROMemployeeWHEREsalBETWEEN1000and1500;查詢結(jié)果包括那些月薪等于1000和月薪等于1500的記錄。運(yùn)算符IN運(yùn)算符IN是用于查找某屬性值包含在指定集合內(nèi)的記錄，IN后面跟的是指定集合。

【例3-22】查找部門號(hào)屬于11和21的員工姓名及部門號(hào)。

SELECTename,dept_idFROMemployeeWHEREdept_idIN(‘11’,’21’);3/14/2023

運(yùn)算符LIKE運(yùn)算符LIKE可用來進(jìn)行字符串的匹配，LIKE后面跟的是匹配模式。匹配模式可以是一個(gè)包含通配符“%”（百分號(hào)）和“_”（下劃線）的字符串?！?”代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串。

“_”代表任意單個(gè)字符。

【例3-23】查詢所有姓張員工的姓名。

SELECTenameFROMemployeeWHEREenameLIKE‘張%’;【例3-24】查找所有1990年參加工作的員工姓名及參加工作時(shí)間。

SELECTename,workdateFROMemployeeWHEREworkdateLIKE‘%1990’;

【例3-25】查找姓名第2個(gè)字為“小”的員工姓名。

SELECTenameFROMemployeeWHEREenameLIKE‘__小%’;

3/14/2023運(yùn)算符ISNULL運(yùn)算符ISNULL用來測(cè)試某個(gè)屬性值是否為空。

【例3-27】在employee表中，職位是總經(jīng)理的員工在屬性mgr_id這欄應(yīng)該不填，因?yàn)樗麤]有上司。因此查詢總經(jīng)理的姓名的語(yǔ)句應(yīng)為：SELECTenameFROMemployeeWHEREmgr_idISNULL;

【例3-28】可能有這樣的情況：新進(jìn)員工暫時(shí)沒有崗位津貼，等試用期滿后再領(lǐng)崗位津貼。查詢沒有崗位津貼的員工姓名。

SELECTenameFROMemployeeWHEREcommISNULL;3/14/2023

邏輯運(yùn)算符SQL語(yǔ)言的邏輯運(yùn)算符有3種：NOT、AND、OR?！纠?-29】查詢?cè)滦匠^1500的部門主管的姓名及月薪。

SELECTename,salFROMemployeeWHEREsal>1500ANDjob=’部門主管’;【例3-30】查詢?cè)滦匠^1500的員工和所有部門主管的姓名及月薪。

SELECTename,salFROMemployeeWHEREsal>1500ORjob=’部門主管’;

3/14/2023【例3-31】查詢領(lǐng)有崗位津貼的員工姓名。

SELECTenameFROMemployeeWHEREcommISNOTNULL;【例3-32】查找部門號(hào)不屬于11和21的員工姓名及部門號(hào)。

SELECTename,dept_idFROMemployeeWHEREdept_idNOTIN(‘11’,’21’);3/14/20233.3.4對(duì)查詢結(jié)果排序SQL語(yǔ)言中用ORDERBY子句實(shí)現(xiàn)對(duì)查詢結(jié)果的排序，可以根據(jù)包含的一列或者多列的表達(dá)式進(jìn)行ASC（升序）或DESC（降序）的排列，默認(rèn)值是ASC。【例3-33】查詢所有員工的姓名及月薪，結(jié)果按月薪的降序排列。

SELECTename,salFROMemployeeORDERBYsalDESC;ORDERBY子句指定的排序列可以不只一個(gè)。3/14/2023

【例3-34】查詢所有員工的姓名、部門號(hào)及月薪，結(jié)果按部門號(hào)升序排列，同一部門按月薪降序排列。SELECTename,dept_id,salFROMemployeeORDERBYdept_id,salDESC;說明：上例中dept_id稱為主排序關(guān)鍵字，sal成為次排序關(guān)鍵字。

注意：

(1)ORDERBY子句不改變基本表中行或列的順序，只改變查詢顯示的順序。(2)ORDERBY子句指定排序的列必須出現(xiàn)在SELECT子句的列表達(dá)式中。(3)排序是查詢語(yǔ)句的最后一步工作，所以O(shè)RDERBY子句一般放在查詢語(yǔ)句的最后。3/14/20233.3.5消除重復(fù)行基本表中不相同的行，經(jīng)過對(duì)某些指定列進(jìn)行投影運(yùn)算后，可能會(huì)變成完全相同的行，顯示結(jié)果不直觀，這時(shí)需要用DISTINCT選項(xiàng)消除重復(fù)的行。【例3-35】查詢表employee中的所有職位。

SELECTDISTINCTjobFROMemployee;

注意：在一個(gè)SELECT語(yǔ)句中DISTINCT只能出現(xiàn)一次，并且DISTINCT必須在所有列名之前，否則會(huì)發(fā)生語(yǔ)法錯(cuò)誤。與DISTINCT選項(xiàng)含義相反的是ALL選項(xiàng)，在SELECT語(yǔ)句中使用ALL選項(xiàng)，表示結(jié)果重復(fù)的行也將顯示。ALL選項(xiàng)是默認(rèn)選項(xiàng)。3/14/20233.3.6SQL的統(tǒng)計(jì)函數(shù)SQL語(yǔ)言提供了許多統(tǒng)計(jì)函數(shù)，主要的統(tǒng)計(jì)函數(shù)見表3-3。

統(tǒng)計(jì)函數(shù)語(yǔ)義COUNT([DISTINCT|ALL]*)統(tǒng)計(jì)表的記錄個(gè)數(shù)COUNT([DISTINCT|ALL]<列名>)統(tǒng)計(jì)一列中值不為NULL值的個(gè)數(shù)SUM([DISTINCT|ALL]<列名>)計(jì)算一列值的總和（此列必須為數(shù)值型）AVG([DISTINCT|ALL]<列名>)計(jì)算一列值的平均值（此列必須為數(shù)值型）MAX([DISTINCT|ALL]<列名>)給出一列值中的最大值MIN([DISTINCT|ALL]<列名>)給出一列值中的最小值3/14/2023【例3-36】統(tǒng)計(jì)員工總?cè)藬?shù)。

SELECTCOUNT（*）FROMemployee;也可以寫成：

SELECTCOUNT（emp_id）FROMemployee;【例3-37】統(tǒng)計(jì)部門號(hào)“11”的部門領(lǐng)取崗位津貼的人數(shù)。

SELECTCOUNT(comm)FROMemployeeWHEREdept_id=’11’;這里統(tǒng)計(jì)的是屬性列comm不為空值的行數(shù)。3/14/2023【例3-38】統(tǒng)計(jì)部門號(hào)“11”的部門全體員工人數(shù)。

SELECTCOUNT(*)FROMemployeeWHEREdept_id=’11’;【例3-39】查詢最早參加工作時(shí)間和最晚參加工作時(shí)間。

SELECTMIN(workdate),MAX(workdate)FROMemployee;【例3-39】統(tǒng)計(jì)所有員工的崗位津貼總數(shù)及平均崗位津貼。

SELECTSUM(comm),AVG(comm)FROMemployee;

注意：除COUNT（*）外，所有的統(tǒng)計(jì)函數(shù)都不包括取值為空值的行。3/14/20233.3.7數(shù)據(jù)分組利用GROUPBY子句可以將查詢結(jié)果按照一列或者多列分組，值相等的為一組。

基于單列的分組【例3-40】按部門號(hào)查詢各部門的平均月薪。

SELECTdept_id,AVG(sal)FROMemployeeGROUPBYdept_id;

基于多列的分組

GROUPBY子句還可以作用于多列上，此時(shí)的數(shù)據(jù)分組意義是分大組之后再分小組。

【例3-41】分各部門各職位統(tǒng)計(jì)月薪總額。

SELECTdept_id,job,sum(sal)FROMemployeeGROUPBYdept_id,job;

說明：先按照部門號(hào)分組，部門號(hào)相同的組再按職位細(xì)分，最后部門號(hào)和職位完全相同的行才會(huì)分在一組，然后每組統(tǒng)計(jì)一個(gè)月薪總額。3/14/2023

HAVING子句

如果分組后還要根據(jù)一定條件對(duì)這些組進(jìn)行篩選，則使用HAVING子句來實(shí)現(xiàn)。

【例3-42】按部門號(hào)查詢各部門的平均月薪，要求只顯示平均月薪在1000以上的部門編號(hào)和平均月薪。

SELECTdept_id,AVG(sal)FROMemployeeGROUPBYdept_idHAVINGAVG(sal)>1000;

注意:WHERE子句和HAVING子句有相似之處，即后面都跟指定條件；但是它們又有區(qū)別：前者直接用于SELECT子句中，作用于基本表或視圖；而后者一定跟在GROUPBY子句后面，作用于分組。3/14/20233.3.8連接查詢

連接查詢的概念如果一個(gè)查詢需要從兩個(gè)或兩個(gè)以上的數(shù)據(jù)表中獲取數(shù)據(jù)時(shí)，則稱之為連接查詢。連接查詢包括廣義笛卡爾積、等值連接、自然連接、外連接、內(nèi)連接、左連接、右連接和自連接等。

廣義笛卡爾積廣義笛卡爾積是不帶連接條件的連接操作。兩個(gè)表的廣義笛卡爾積即是兩個(gè)表中所有記錄的交叉組合，其形成的結(jié)果集是所有連接種類中最大的。比如：表1有3條記錄，表2有5條記錄，則廣義笛卡爾積產(chǎn)生3*5=15條記錄。由于這種連接操作是不帶條件的表的拼接，因此實(shí)際意義不大。3/14/2023

等值連接

[<表名1>.]<列名1>=[<表名2>.]<列名2>等值連接又稱為內(nèi)連接。若將查詢結(jié)果的目標(biāo)列中重復(fù)的列去掉，則稱為自然連接，在實(shí)際中等值連接一般以自然連接的形式出現(xiàn)。

【例3-43】查詢每位員工的員工號(hào)，姓名，部門號(hào)、部門名稱及部門電話。

SELECTemp_id,ename,dept.dept_id,dname,telFROMemployee,deptWHEREemployee.dept_id=dept.dept_id;

說明：(1)如果屬性列名在參加連接的各表中是惟一的，可以省略表名前綴；如果屬性列名是兩個(gè)表共同的屬性，則一定要加表名前綴。(2)在書寫連接查詢時(shí)，為了簡(jiǎn)化，可以為表名取別名，別名應(yīng)該簡(jiǎn)單。別名只在本次查詢有效。3/14/2023【例3-44】查詢每位員工的員工號(hào)，姓名，部門號(hào)、部門名稱及部門電話。

SELECTemp_id,ename,d.dept_id,dname,telFROMemployeee,deptdWHEREe.dept_id=d.dept_id;

不等連接

當(dāng)連接條件中的比較運(yùn)算符不為“=”時(shí)，此時(shí)的連接查詢稱為不等連接。3/14/2023

假設(shè)有JOB表（職位表），它包含兩個(gè)屬性：職位job_level和標(biāo)準(zhǔn)月薪std_sal，該表包含記錄如下：

job_levelstd_sal部門經(jīng)理2200出納1200【例3-45】列出可提供給employee表中每位員工比現(xiàn)在薪水高的職位。

SELECTename,sal,job_level,std_salFROMemployee,jobWHEREstd_sal>sal

查詢結(jié)果如下：

enamesaljob_levelstd_sal吳偉1300部門經(jīng)理2200岳玲1100部門經(jīng)理2200王斌1500部門經(jīng)理2200徐歡800部門經(jīng)理2200岳玲1100出納1200徐歡800出納12003/14/20233.4數(shù)據(jù)更新

3.4.1插入記錄插入單條記錄INSERTINTO<表名>[(<屬性列1>[,<屬性列2>]…)]VALUES(<常量1>[,<常量2>]…);注意：（1）屬性列的個(gè)數(shù)與常量的個(gè)數(shù)要相等，且順序一致，否則會(huì)產(chǎn)生語(yǔ)法錯(cuò)誤。（2）在表結(jié)構(gòu)定義中未說明為NOTNULL的屬性列，如果沒有出現(xiàn)在INTO子句后，這些列將取空值。已經(jīng)說明為NOTNULL的屬性列，則必須出現(xiàn)在INTO子句后。（3）如果INTO子句后沒有指定任何列，則VALUES子句后面的常量個(gè)數(shù)必須與基本表中列的個(gè)數(shù)相等，且類型、順序一致，否則會(huì)出語(yǔ)法錯(cuò)誤或?qū)е沦x值不正確。

3/14/2023【例3-47】插入一條部門新記錄。

INSERTINTOdept(dept_id,dname,tel)VALUES(‘31’,’產(chǎn)品開發(fā)部’,’’);該語(yǔ)句等價(jià)于：

INSERTINTOdeptVALUES(‘31’,’產(chǎn)品開發(fā)部’,’’);【例3-48】插入一條員工新記錄。

INSERTINTOemployeeVALUES(‘1311’,‘淳’,NULL,NULL,to_date(’2004/08/15’),800,’11’);

3/14/2023插入子查詢的結(jié)果SELECT語(yǔ)句可以作為子查詢嵌套在INSERT語(yǔ)句中，用以插入批量記錄。其語(yǔ)句格式一般為：

INSERTINTO<表名>[(<屬性列1>[,<屬性列2>]…)]子查詢;【例3-49】求出每個(gè)部門平均月薪，將部門號(hào)和平均月薪放入一張新表dept_sal中。先創(chuàng)建新表的結(jié)構(gòu)：

CREATETABLEdept_sal(dept_idCHAR(2),avg_salSMALLINT);然后將子查詢求出的數(shù)據(jù)批量插入新表中：

INSERTINTOdept_sal(dept_id,avg_sal)SELECTdept_id,AVG(sal)FROMemployeeGROUPBYdept_id;3/14/20233.4.2修改記錄

SQL語(yǔ)言修改記錄的語(yǔ)句為UPDATE。該語(yǔ)句有3種形式：修改單條記錄、修改多條記錄以及使用子查詢修改記錄。其一般語(yǔ)句格式為：UPDATE<表名>SET<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>][,…][WHERE<條件>];SQL語(yǔ)言的修改語(yǔ)句功能是將表中符合WHERE子句條件的記錄找出，以表達(dá)式的值替代相應(yīng)屬性列的值。3/14/2023修改單條記錄【例3-50】修改1311號(hào)員工的月系薪為1700。

UPDATEemployeeSETsal=1700WHEREemp_id=’1311’;修改多條記錄【例3-51】所有員工月薪上調(diào)5%。

UPDATEemployeeSETsal=sal*1.05;【例3-52】21號(hào)部門的所有員工取消崗位津貼。

UPDATEemployeeSETcomm=0WHEREdept_id=’21’;3/14/2023用子查詢修改記錄UPDATE語(yǔ)句可以和SELECT語(yǔ)句聯(lián)合使用。后者作為子查詢嵌套。這種情況也屬于批量修改。

【例3-53】將產(chǎn)品開發(fā)部的部門主管的崗位津貼加500。

UPDATEemployeeSETcomm=comm+500WHEREjob=’部門主管’ANDdept_id=(SELECTdept_idFROMdeptWHEREdept.dname=’產(chǎn)品開發(fā)部’);說明：

(1)因?yàn)閷?duì)應(yīng)產(chǎn)品開發(fā)部的部門號(hào)只有一個(gè)，所以子查詢檢索出的記錄只有一條，因此子查詢前面可以用“=”。(2)這條UPDATE語(yǔ)句的WHERE子句有兩個(gè)條件，它們是邏輯與關(guān)系。根據(jù)這兩個(gè)條件查詢找出的記錄可能是多條。3/14/20233.4.3刪除記錄DELETE語(yǔ)句一般格式：

DELETEFROM<表名>[WHERE<條件>];DELETE語(yǔ)句的功能是：先按照WHERE子句中指定的條件范圍將記錄找出來，然后進(jìn)行刪除。刪除單條記錄

【例3-54】刪除員工號(hào)為1045的記錄。

DELETEFROMemployeeWHEREemp_id=’1045’;3/14/2023刪除多條記錄【例3-55】刪除部門號(hào)為31的所有記錄。

DELETEFROMemployeeWHEREdept_id=’31’;【例3-56】刪除所有員工記錄。

DELETEFROMemployee;刪除所有記錄，是清空表中數(shù)據(jù)，因此不加WHERE子句。這類操作的執(zhí)行應(yīng)非常小心。3/14/2023使用子查詢刪除SELECT語(yǔ)句同樣也可以和DELETE語(yǔ)句聯(lián)合使用。

【例3-57】刪除產(chǎn)品開發(fā)部和事業(yè)推廣部的所有員工的記錄。

DELETEFROMemployeeWHEREdept_idIN(SELECTdept_idFROMdeptWHEREdept.dname=’產(chǎn)品開發(fā)部’ORdept.dname=’事業(yè)推廣部’);因?yàn)镾ELECT子查詢得到的記錄一般不止一條，所以子查詢前面的運(yùn)算符不能為“=”，而應(yīng)該用表示取值范圍的“IN”。3/14/20233.5視圖

3.5.1視圖的概念視圖不包含任何數(shù)據(jù)，只是定義在一個(gè)或多個(gè)基表上或其他視圖上，并且提供一種訪問基表數(shù)據(jù)的方法。在物理磁盤上存儲(chǔ)的有關(guān)視圖的信息是：視圖的名稱和視圖的定義。視圖的所有數(shù)據(jù)來自基本表。因此，當(dāng)基本表的數(shù)據(jù)發(fā)生變化時(shí)，對(duì)應(yīng)視圖中的查詢出的數(shù)據(jù)也會(huì)隨之變化。視圖一經(jīng)定義，就可以象基本表一樣執(zhí)行查詢、刪除等操作，也可以在視圖上定義新的視圖，但對(duì)視圖的更新操作則有一定限制。3.5.2視圖的作用可以滿足不同用戶的需求不同的用戶對(duì)數(shù)據(jù)庫(kù)操作有不同的需求，即使相同的數(shù)據(jù)也可能有不同的操作要求。一張基本表可能有很多屬性列，利用視圖，用戶可以把自己感興趣的屬性列集中起來，放在一個(gè)視圖中，此后用戶可以將視圖作為一張表來對(duì)待。3/14/2023可以簡(jiǎn)化數(shù)據(jù)讀取查詢數(shù)據(jù)時(shí)，通常要用SELECT語(yǔ)句編寫復(fù)雜的連接、統(tǒng)計(jì)、函數(shù)等，以產(chǎn)生所需要的結(jié)果。使用視圖，可以隱蔽這種復(fù)雜性。可以將經(jīng)常用到的復(fù)雜查詢的語(yǔ)句定義為視圖，不必每次查詢都寫上復(fù)雜查詢條件，這樣就簡(jiǎn)化了用戶的查詢操作。保證了基本表數(shù)據(jù)和應(yīng)用程序的邏輯獨(dú)立性當(dāng)應(yīng)用程序通過視圖來訪問數(shù)據(jù)時(shí)，視圖實(shí)際上成為應(yīng)用程序和基本表數(shù)據(jù)之間的橋梁。如果應(yīng)用程序直接調(diào)用基本表，則一旦基本表的數(shù)據(jù)發(fā)生變化時(shí)，應(yīng)用程序必須隨之改動(dòng)。而通過視圖訪問數(shù)據(jù)，則可以通過改變視圖來適應(yīng)基本表的變化，使應(yīng)用程序不必作改變，保證了基本表數(shù)據(jù)和應(yīng)用程序的邏輯獨(dú)立性?？梢蕴峁┌踩珯C(jī)制利用視圖可以限制數(shù)據(jù)訪問。如果某個(gè)用戶需要訪問表中的某些列，但另一些屬性列必須對(duì)該用戶保密，則可以利用視圖達(dá)到此目的，將視圖建立在該用戶需要訪問的那些列上。3/14/20233.5.3視圖的建立SQL語(yǔ)言用CREATEVIEW語(yǔ)句來建立視圖，其一般格式為：

CREATEVIEW<視圖名>[(<列名>[，<列名>]…)]AS<子查詢>[WITHCHECKOPTION]；說明：(1)選項(xiàng)WITHCHECKOPTION確保用戶只能查詢和修改他們所看到的數(shù)據(jù)，強(qiáng)制所有在視圖上使用的數(shù)據(jù)修改語(yǔ)句滿足定義視圖時(shí)的條件。(2)組成視圖的各屬性列可以顯式指定，也可以省略。如果省略不寫，則組成視圖的各屬性列由子查詢中SELECT子句的各目標(biāo)列組成。

下列情形建立視圖時(shí)必須顯式指定屬性列：1)視圖的某列不是原屬性列，而是統(tǒng)計(jì)函數(shù)或者表達(dá)式。2)多表連接時(shí)選出了兩個(gè)或者多個(gè)同名列作為視圖的屬性列。3)需要對(duì)視圖中的某些列重新命名。3/14/2023

【例3-58】建立僅包含部門主管視圖。

CREATEVIEWmgr_vuASSELECT*FROMemployeeWHEREJOB=’部門主管’;【例3-59】為21號(hào)部門的所有員工的員工號(hào)、姓名及月薪建立視圖。

CREATEVIEWsal21_vu(eno,ename,salary)ASSELECTemp_id,ename,salFROMemployeeWHEREdept_id=’21’;

3/14/2023

【例3-60】為21號(hào)部門月薪超過1500的員工的員工號(hào)、姓名及月薪建立視圖。

CREATEVIEWsal21_vu1(eno,ename,salary)ASSELECTemp_id,ename,salFROMemployeeWHEREdept_id=’21’ANDsal>1500;

該題也可以對(duì)【例3-55】定義的視圖進(jìn)行進(jìn)一步的篩選。由于視圖不僅可以建立在基本表之上，還可以建立在視圖上，因此該語(yǔ)句也可寫作：

CREATEVIEWsal21_vu1ASSELECT*FROMsal21_vuWHEREsal>1500;3/14/2023【例3-61】為各部門的平均月薪建立視圖

CREATEVIEWsalavg_vu(dno,sal_avg)ASSELECTdept_id,AVG(sal)FROMemployeeGROUPBYdept_id;本例中因使用了統(tǒng)計(jì)函數(shù)，因而對(duì)視圖的列作了顯式指定。

視圖還可以建立在多個(gè)表或視圖之上?！纠?-62】為產(chǎn)品開發(fā)部的員工的員工號(hào)、姓名、月薪以及工齡建立視圖。

CREATEVIEWdept_vu(eno,name,salary,wage)ASSELECTemp_id,ename,sal,2004-year(workdate)FROMemployee,deptWHEREemployee.dept_id=dept.dept_idANDdept.dname=’產(chǎn)品開發(fā)部’;3/14/20233.6案例2：活期儲(chǔ)蓄管理系統(tǒng)數(shù)據(jù)庫(kù)上的查詢

示例數(shù)據(jù)由案例1知，活期儲(chǔ)蓄管理系統(tǒng)數(shù)據(jù)庫(kù)包括：儲(chǔ)戶基本信息、儲(chǔ)戶動(dòng)態(tài)信息、儲(chǔ)蓄所、存取款4個(gè)基本表，根據(jù)案例1對(duì)關(guān)系結(jié)構(gòu)的描述（也稱為數(shù)據(jù)字典），這里給出活期儲(chǔ)蓄管理系統(tǒng)數(shù)據(jù)庫(kù)中基本表的示例數(shù)據(jù)。

儲(chǔ)蓄所數(shù)據(jù)

編號(hào)名稱電話地址1001中國(guó)建行杭州分行秋濤支行(0571)88049082秋濤北街283號(hào)1002中國(guó)建行杭州分行武林支行(0571)68066080武林南路366號(hào)1208中國(guó)建行杭州分行下沙支行(0571)28800088