數(shù)據(jù)庫(kù)實(shí)踐3-SQL和QBE

數(shù)據(jù)庫(kù)設(shè)計(jì)

DatabaseSolutions第三章SQL和QBE本章主題SQL的用途和重要性用“SELECT”檢索數(shù)據(jù)用“INSERT”插入數(shù)據(jù)用“UPDATE”更新數(shù)據(jù)用“DELETE”刪除數(shù)據(jù)用“CREATETABLE”建表關(guān)于QBE本節(jié)主題SQL的用途和重要性用“SELECT”檢索數(shù)據(jù)用“INSERT”插入數(shù)據(jù)用“UPDATE”更新數(shù)據(jù)用“DELETE”刪除數(shù)據(jù)用“CREATETABLE”建表關(guān)于QBESQL關(guān)系DBMS的主要語(yǔ)言主要特征：相對(duì)容易學(xué)習(xí)非過程化–

只要求用戶指定需要什么數(shù)據(jù)，而非指明怎樣獲得這些數(shù)據(jù)。自由格式由標(biāo)準(zhǔn)英語(yǔ)單詞組成能被廣泛用戶使用SQL的重要性首先，迄今為止，只有標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)語(yǔ)言獲得了普遍的接受。來自于廠商和用戶的巨大的投資聯(lián)邦信息處理標(biāo)準(zhǔn)（FIPS

）被用作其它標(biāo)準(zhǔn)的基礎(chǔ)SQL的目標(biāo)理想上，數(shù)據(jù)庫(kù)語(yǔ)言應(yīng)該讓用戶：創(chuàng)建數(shù)據(jù)庫(kù)和表結(jié)構(gòu)。執(zhí)行基本的任務(wù)，像插入、更新、刪除等。執(zhí)行簡(jiǎn)單和復(fù)雜的查詢。必須讓用戶最省力的執(zhí)行這些任務(wù)。必須易于學(xué)習(xí)。SQL的目標(biāo)SQL是面向變換（非過程）的語(yǔ)言，有兩個(gè)組件：DDL：定義數(shù)據(jù)庫(kù)結(jié)構(gòu)DML：檢索和更新數(shù)據(jù)庫(kù)直到SQL3為止，SQL并不包含控制流命令SQL能被交互地使用在或嵌入在上層語(yǔ)言中（像C/C++等）書寫SQL命令SQL語(yǔ)句由保留字和用戶自定義字組成。保留字：SQL的固定部分，并且必須按要求正確拼寫，同時(shí)不能拆分成多行（只能出現(xiàn)在一行里）。用戶自定義字：由用戶構(gòu)造，用于表示不同數(shù)據(jù)庫(kù)對(duì)象名，諸如表名、列名、視圖名。書寫SQL命令除了文字字符數(shù)據(jù)外，SQL語(yǔ)句中大部分的組成部分是大小寫不敏感的。使用擴(kuò)展的BNF標(biāo)記形式大寫字母表示保留字小寫字母表示用戶自定義字“|”

表示“或”，在可選項(xiàng)中的選擇。圓括號(hào)表示必需的元素方括號(hào)表示可選的元素“…”

表示可選的重復(fù)（0次或多次）文字量文字量是使用在SQL語(yǔ)句中的常量。所有的非數(shù)值文字量必須用單引號(hào)封閉起來，如‘London’。所有的數(shù)值文字量不能用引號(hào)封閉，如6500.00。本節(jié)主題SQL的用途和重要性用“SELECT”檢索數(shù)據(jù)用“INSERT”插入數(shù)據(jù)用“UPDATE”更新數(shù)據(jù)用“DELETE”刪除數(shù)據(jù)用“CREATETABLE”建表關(guān)于QBESelect語(yǔ)句SELECT[DISTINCT|ALL]{*|[columnExprn[ASnewName]][,...]}FROMTableName[alias][,...][WHEREcondition][GROUPBYcolumnList][HAVING condition][ORDERBY columnList]Select語(yǔ)句FROM 標(biāo)明所用的表WHERE 過濾符合某種條件的記錄GROUPBY 根據(jù)各記錄相同的字段進(jìn)行分 組HAVING 通過某一條件過濾分組中的記 錄SELECT 標(biāo)明輸出的列ORDERBY 對(duì)輸出進(jìn)行排序Select語(yǔ)句兩個(gè)注意點(diǎn)：１.以上子句的順序不能更改。２.Select和From是必須的。3.1所有的列，所有的行列出所有錄像完整的細(xì)節(jié)SELECTcatalogNo,title,category,dailyRental,price,directorNoFROMVideo;可以使用“＊”表示“所有的列”的含義

SELECT*FROMVideo;3.1所有的列，所有的行3.2特定的列，所有的行列出所有錄像的“分類號(hào)”，“標(biāo)題”和“日租金”SELECTcatalogNo,title,dailyRentalFROMVideo;3.2特定的列，所有的行3.3使用關(guān)鍵字“DISTINCT”列出所有錄像的種類SELECTcategory

FROMVideo;3.3使用關(guān)鍵字“DISTINCT”使用“DISTINCT”消除冗余（副本）。SELECTDISTINCTcategoryFROMVideo;3.4計(jì)算的域列出租借三天的錄像的費(fèi)用SELECTcatalogNo,title,dailyRental*3FROMVideo;3.4計(jì)算的域使用AS子句來命名列SELECTcatalogNo,title,

dailyRental*3ASthreeDayRateFROMVideo;3.5“比較”搜索條件列出薪水超過$10,000的員工SELECTstaffNo,name,position,salaryFROMStaffWHEREsalary>10000;3.6“范圍”搜索條件列出薪水在$45,000～$50,000之間的員工SELECTstaffNo,name,position,salaryFROMStaffWHEREsalaryBETWEEN45000AND50000;BETWEEN的范圍包括端點(diǎn)值。3.6“范圍”搜索條件3.6“范圍”搜索條件還有一個(gè)否定的版本NOTBETWEENBETWEEN沒有為SQL增強(qiáng)表達(dá)能力，同樣可以寫成：

SELECTstaffNo,name,position,salary FROMStaff WHEREsalary>=45000ANDsalary<=50000;

然而，BETWEEN在表達(dá)值范圍的時(shí)候是很有用的。3.7集合成員關(guān)系列出動(dòng)作和兒童錄像SELECTcatalogNo,title,categoryFROMVideoWHEREcategoryIN(‘Action’,‘Children’);3.7集合成員關(guān)系還有一個(gè)否定版本(NOTIN)。IN沒有增強(qiáng)SQL的表達(dá)能力，也可以寫成：SELECTcatalogNo,title,categoryFROMVideoWHEREcategory=‘Action’ORcategory=‘Children’當(dāng)集合包含很多元素的時(shí)候，IN是很有效的。3.8模式匹配列出人名中第一個(gè)字是Sally的員工：SELECTstaffNo,name,position,salaryFROMStaffWHEREnameLIKE‘Sally%’;3.8模式匹配SQL有兩個(gè)特殊的模式匹配符號(hào)：%:零或多個(gè)字符的序列_(下劃線)：任何單一字符LIKE‘Sally%’表示前５個(gè)字符必須是Sally后跟隨任何字符。3.9“空”搜索條件列出尚未歸還的錄像的租金：必須顯式地使用特殊的關(guān)鍵字ISNULL測(cè)試是否為NULLSELECTdateOut,memberNo,videoNoFROMRentalAgreementWHEREdateReturnISNULL;3.9“空”搜索條件否定版本ISNOTNULL能夠測(cè)試非空值3.10單列排序列出按價(jià)格降序排列的所有錄像：SELECT*FROMVideoORDERBYpriceDESC;3.10單列排序SELECT語(yǔ)句–

聚集ISOSQL定義了五個(gè)聚集函數(shù):COUNT返回指定列的值的個(gè)數(shù)。SUM 返回指定列的值的和。AVG 返回指定列的值的平均值。MIN 返回指定列的值的最小值。MAX 返回指定列的值的最大值。SELECT語(yǔ)句–

聚集每個(gè)函數(shù)操作在表的單一列上并返回單一的值。

COUNT,MIN,和MAX應(yīng)用于數(shù)字和非數(shù)字域,SUM和AVG僅僅用于數(shù)字域.除了COUNT(*)之外,每個(gè)函數(shù)首先消除空值并且僅僅在剩余的非空值上操作。SELECT語(yǔ)句–

聚集COUNT(*)計(jì)數(shù)一張表的所有行,無論空值和重復(fù)值的情況是否發(fā)生?？梢栽诹忻笆褂肈ISTINCT消除重復(fù)。DISTINCT對(duì)MIN/MAX沒有影響,但是可能對(duì)SUM/AVG有影響。SELECT語(yǔ)句–

聚集聚集函數(shù)只能用于Select列表和Having子句中。如果SELECT列表中包括聚集函數(shù)并且沒有GROUPBY子句，那么SELECT列表不能參照用聚集函數(shù)的列。如，下例是非法的：SELECTstaffNo,COUNT(salary)FROMStaff;3.11使用COUNT和SUM列出薪水超過$40,000員工的總數(shù)以及他們薪水的總和。SELECTCOUNT(staffNo)AStotalStaff, SUM(salary)astotalSalaryFROMStaffWHEREsalary>40000;3.11使用COUNT和SUM3.12使用MIN,MAX和AVG列出員工薪水的最大值，最小值和平均值：SELECTMIN(salary)ASminSalary,MAX(salary)ASmaxSalary,AVG(salary)ASavgSalaryFROMStaff;SELECT語(yǔ)句–分組使用GROUPBY子句得到記錄分組。SELECT和GROUPBY合用在一起：在SELECT列表中的每個(gè)項(xiàng)目必須是每組單值的（single-valuedpergroup

），并且SELECT子句只能包含：列名聚集函數(shù)常數(shù)上述內(nèi)容組合的表達(dá)式SELECT語(yǔ)句–分組在SELECT列表中所有的列名必須出現(xiàn)在GROUPBY子句中，除非僅用于聚集函數(shù)中。WHERE首先執(zhí)行，然后在滿足謂詞的剩余的記錄中形成分組。對(duì)于GROUPBY，ISO認(rèn)為兩個(gè)NULL值是相等的。3.13使用GROUPBY找出每個(gè)分公司的員工數(shù)，以及他們的薪水總數(shù)。

SELECTbranchNo,COUNT(staffNo)AStotalStaff, SUM(salary)AStotalSalaryFROMStaffGROUPBYbranchNoORDERBYbranchNo;3.13使用GROUPBY受限分組–HAVING子句設(shè)計(jì)HAVING子句同GROUPBY子句一起使用，用于限制最后出現(xiàn)在結(jié)果表中的分組。相似于WHERE，但是WHERE過濾單獨(dú)的行然而HAVING過濾分組。HAVING子句中的列名必須出現(xiàn)在GROUPBY列表中或者包含在聚集函數(shù)中。3.14使用HAVING對(duì)每個(gè)超過1個(gè)員工的分公司，找出每個(gè)分公司的員工數(shù)，以及他們的薪水總數(shù)。

SELECTbranchNo,COUNT(staffNo)AStotalStaff, SUM(salary)AStotalSalaryFROMStaffGROUPBYbranchNoHAVINGCOUNT(staffNo)>1ORDERBYbranchNo;3.14使用

HAVING子查詢一些SQL語(yǔ)句能夠嵌套SELECT語(yǔ)句。子select能夠使用在外層SELECT語(yǔ)句的WHERE和HAVING子句中，稱作子查詢或者嵌套查詢。子select可以出現(xiàn)在INSERT,UPDATE和DELETE語(yǔ)句中。3.15使用等式的子查詢找出地處8JeffersonWay街的分公司工作的員工。

SELECTstaffNo,name,position FROMStaff WHEREbranchNo= (SELECTbranchNo FROMBranch WHEREstreet=‘8JeffersonWay’);3.15使用等式的子查詢內(nèi)層SELECT找出地處8JeffersonWay街的分公司號(hào)(‘B001’)。外層SELECT就檢索工作在該分公司的所有員工的細(xì)節(jié)。外層SELECT等同于:

SELECTstaffNo,name,positionFROMStaffWHEREbranchNo=‘B001’;3.15使用等式的子查詢3.16使用聚集的子查詢列出薪水高于平均值的所有員工。SELECTstaffNo,name,positionFROMStaffWHEREsalary>

(SELECTAVG(salary)

FROMStaff);3.16使用聚集的子查詢不能寫成‘WHEREsalary>AVG(salary)’使用子查詢找出平均的薪水(41166.67),同時(shí)使用外層SELECT找出那些薪水高于這個(gè)值的員工:SELECTstaffNo,name,positionFROMStaffWHEREsalary>41166.67;3.16使用聚集的子查詢子查詢規(guī)則ORDERBY子句不可以用在子查詢中（盡管它可以用在最外層SELECT中）。除了使用EXISTS子句的子查詢情況外，子查詢SELECT列表必須由單一的列名或者表達(dá)式組成。缺省情況下,列名引用了在子查詢的FROM子句中的表名；可以在FROM中使用別名指定表。當(dāng)子查詢作為比較運(yùn)算中的一個(gè)運(yùn)算數(shù)時(shí)，子查詢必須出現(xiàn)在右手邊。子查詢不可以作為表達(dá)式的運(yùn)算數(shù)。多表查詢?nèi)绻Y(jié)果列來自于同一表，那么可以使用子查詢。如果結(jié)果列來自于多個(gè)表，那么必須使用連接。為了能夠進(jìn)行連接操作，要在FROM子句中包括多個(gè)表（使用逗號(hào)分隔表名）。典型地，用WHERE指定進(jìn)行連接的列。多表查詢可能的話，為在FROM子句中指定的表使用別名。

別名同表名用空格分隔。

當(dāng)存在歧義時(shí)，可以用別名修飾列名消除歧義。3.17簡(jiǎn)單連接

列出所有的錄像及其導(dǎo)演：

SELECTcatalogNo,title,category,

v.directorNo,directorName

FROMVideov,Directord

WHEREv.directorNo=d.directorNo;3.17簡(jiǎn)單連接僅僅兩張表的列directorNo相同的記錄(v.directorNo=d.directorNo)包含在結(jié)果中。選擇連接構(gòu)造可選擇的連接的方法:

FROMVideov

JOINDirectordONv.directorNo=d.directorNo Video用directorNo同Director連接

Video同Director進(jìn)行自然連接FROM代替了原來的FROM和WHERE。然而，首先產(chǎn)生具有兩個(gè)相等directorNo列值的表。3.18四表連接列出所有錄像、該錄像的導(dǎo)演、演員和演員角色：

SELECTv.catalogNo,title,category,directorName,actorName,character

FROMVideov,Directord,Actora,Roler

WHEREd.directorNo=v.directorNoAND

v.catalogNo=r.catalogNoAND

r.actorNo=a.actorNo;3.18四表連接本節(jié)主題SQL的用途和重要性用“SELECT”檢索數(shù)據(jù)用“INSERT”插入數(shù)據(jù)用“UPDATE”更新數(shù)據(jù)用“DELETE”刪除數(shù)據(jù)用“CREATETABLE”建表關(guān)于QBE插入INSERTINTOTableName[(columnList)]VALUES(dataValueList)columnList

是可選的;如果省略的話,SQL就認(rèn)為columnList是以原始的CREATETABLE順序的所有的列。

當(dāng)創(chuàng)建表時(shí)，任何省略的列必須已經(jīng)聲明為NULL值或者指定為默認(rèn)值。插入dataValueList

必須如下匹配columnList：每個(gè)列表中的項(xiàng)的數(shù)目必須相同;兩個(gè)列表中項(xiàng)的位置必須是直接對(duì)應(yīng)的;在dataValueList中每個(gè)項(xiàng)的數(shù)據(jù)類型必須與對(duì)應(yīng)的列的數(shù)據(jù)類型相容。插入在Video中插入一行：

INSERTINTOVideoVALUES(‘207132’,‘DieAnotherDay’,‘Action’5.00,21.99,‘D1001’

);本節(jié)主題SQL的用途和重要性用“SELECT”檢索數(shù)據(jù)用“INSERT”插入數(shù)據(jù)用“UPDATE”更新數(shù)據(jù)用“DELETE”刪除數(shù)據(jù)用“CREATETABLE”建表關(guān)于QBE更新UPDATETableNameSETcolumnName1=dataValue1 [,columnName2=dataValue2...][WHEREsearchCondition]TableName

基表或是可更新的視圖SET子句指明了要進(jìn)行更新的列名更新WHERE子句是可選的:如果省略,表中所有的行會(huì)被更新;如果被指定,僅僅滿足查詢條件searchCondition的行被更新。

新的數(shù)據(jù)值dataValue(s)

必須同對(duì)應(yīng)的列的數(shù)據(jù)類型相容。3.20更新表中的行把‘Thriller’類別的錄像的日租金提高10%。 UPDATEVideo SETdailyRental=dailyRental*1.1 WHEREcategory=‘Thriller’;本節(jié)主題SQL的用途和重要性用“SELECT”檢索數(shù)據(jù)用“INSERT”插入數(shù)據(jù)用“UPDATE”更新數(shù)據(jù)用“DELETE”刪除數(shù)據(jù)用“CREATETABLE”建表關(guān)于QBE刪除DELETEFROMTableName[WHEREsearchCondition]TableName基表或是可更新的視圖searchCondition

可選。如果省略,表中所有行被刪除，但不刪除表。如果指定searchCondition,只有滿足條件的行會(huì)被刪除。3.21刪除指定的行刪除分類號(hào)是634817的租貸錄像.

DELETEFROMVideoForRent

WHEREcatalogNo=‘634817’;本節(jié)主題SQL的用途和重要性用“SELECT”檢索數(shù)據(jù)用“INSERT”插入數(shù)據(jù)用“UPDATE”更新數(shù)據(jù)用“DELETE”刪除數(shù)據(jù)用“CREATETABLE”建表關(guān)于QBE數(shù)據(jù)定義兩個(gè)主要的SQLDDL語(yǔ)句:CREATETABLE–

創(chuàng)建新表。CREATEVIEW–

創(chuàng)建新視圖。CREATETABLE

語(yǔ)句CREATETABLETableName{(columnNamedataType[NOTNULL][UNIQUE][DEFAULTdefaultOption][,...]}[PRIMARYKEY(listOfColumns),]{[UNIQUE(listOfColumns),][…,]}{[FOREIGNKEY(listOfFKColumns)REFERENCESParentTableName[(listOfCKColumns)],[ONUPDATEreferentialAction][ONDELETEreferentialAction]][,…]}定義列columnNamedataType[NOTNULL][UNIQ