第07章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL

第07章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言內(nèi)容提要認(rèn)識SQL語言創(chuàng)建SQL查詢SQL數(shù)據(jù)更新SQL數(shù)據(jù)查詢7.1認(rèn)識SQL語言SQL:StructuredQueryLanguage，結(jié)構(gòu)化查詢語言是用戶和DBMS通信的語言和工具發(fā)展：1974年提出1986年被美國國家標(biāo)準(zhǔn)局批準(zhǔn)成為關(guān)系型數(shù)據(jù)庫語言的標(biāo)準(zhǔn)各種類型的RDBMS都采用作為其存取語言和標(biāo)準(zhǔn)接口7.1認(rèn)識SQL語言特點：功能豐富、使用靈活、語言簡捷易學(xué)高度的綜合：數(shù)據(jù)操作、數(shù)據(jù)定義、數(shù)據(jù)控制等非過程化：命令方式，提出“做什么”面向集合：操作對象和結(jié)果是元組的集合兩種使用方式，可嵌入語言結(jié)構(gòu)簡單、易學(xué)易用功能：數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)更新、數(shù)據(jù)控制7.2創(chuàng)建SQL查詢課堂演示7.3數(shù)據(jù)更新數(shù)據(jù)插入INSERTINTO…數(shù)據(jù)修改UPDAE…數(shù)據(jù)刪除DELETE…7.3SQL數(shù)據(jù)更新數(shù)據(jù)插入給新記錄的字段賦值來添加INSERTINTO表名

VALUES(值1,值2,....)

為表中所有字段指定值，或者：INSERTINTO表名(字段1,字段2,...)

VALUES(值1,值2,....)

指定所要插入數(shù)據(jù)的字段7.3SQL數(shù)據(jù)更新舉例：“訂單狀態(tài)”表如圖，添加新記錄（4，已回款）。說明：“狀態(tài)ID”是表的主鍵INSERTINTO訂單狀態(tài)(狀態(tài)ID,狀態(tài)名)

VALUES(4,”已回款”)由于是給表中所有字段添加值，SQL語句也可以寫成：INSERTINTO訂單狀態(tài) VALUES(4,”已回款”)提問：當(dāng)該語言已經(jīng)執(zhí)行成功后，再執(zhí)行會出現(xiàn)什么情況？7.3SQL數(shù)據(jù)更新數(shù)據(jù)修改：將表中字段的值更改為指定的值，通過UPDATE語句實現(xiàn)。語法：

UPDATE表名 SET字段1=新值[,字段1=新值,…] [WHERE條件表達(dá)式]說明：中括號部分表示可選項，一次可以更新一個字段，也可以更新多個字段，多字段之間用英文逗號分隔；WHERE條件表達(dá)式限制僅更新滿足條件的記錄，沒有該子句，則更新全部記錄。7.3SQL數(shù)據(jù)更新舉例：更新“客戶”表中ID為4的客戶電子郵件地址為“MrHuang@”，并將職務(wù)修改為“銷售經(jīng)理”。UPDATE客戶

SET電子郵件地址=”MrHuang@”,職務(wù)=”銷售經(jīng)理”

WHEREID=47.3SQL數(shù)據(jù)更新數(shù)據(jù)刪除：將指定表的記錄刪除，通過DELETE語句實現(xiàn)。語法： DELETEFROM表名 [WHERE條件表達(dá)式]舉例：刪除“訂單狀態(tài)”表狀態(tài)ID為4的記錄。 DELETEFROM訂單狀態(tài)

WHERE狀態(tài)ID=4

提示：如果沒有WHERE條件表達(dá)式，將可能刪除所有記錄。課堂練習(xí)在右圖所示表中完成：表字段說明：“貨號”字段類型是自動編號，“貨名”和“規(guī)格”文本型，“進(jìn)貨價格”是貨幣型，進(jìn)貨數(shù)量是數(shù)字型?！柏浱枴笔侵麈I添加新記錄，貨名：貝雷帽，規(guī)格：頂，進(jìn)貨價格：98，進(jìn)貨數(shù)量：6。將“貨號”是8的貨名改成“普通長褲”。將表中規(guī)格是“頂”的商品進(jìn)貨價格改為以前的9折。刪除進(jìn)貨數(shù)量在10以上記錄。7.4數(shù)據(jù)查詢7.4數(shù)據(jù)查詢Select[All|Distinct][Topn[Percent]][<表名|表別名>.]<字段名1>[As<別名>][,[<表名|表別名.>]<字段名2>[As<別名>]][,…][Into<新表名>]From<表名或查詢名>[<表名或查詢名>…][Inner/Left/Right/FullJoin[<數(shù)據(jù)庫名!>]<表名>[On<連接條件>…]][Where<條件表達(dá)式>][Groupby<字段名1>[,<字段名2>…][Having<篩選條件表達(dá)式>]][Orderby<字段名1>[Asc/Desc][,<字段名2[Asc/Desc]…]]7.4數(shù)據(jù)查詢例7-4查詢羅斯文經(jīng)營的所有產(chǎn)品，即檢索“產(chǎn)品”表中的所有行和列。Select[供應(yīng)商ID],ID,產(chǎn)品代碼,產(chǎn)品名稱,說明,標(biāo)準(zhǔn)成本,列出價格,再訂購水平,目標(biāo)水平,單位數(shù)量,中斷,最小再訂購數(shù)量,類別,附件From

產(chǎn)品如果查詢某表的所有列時，可用*代替所有字段名，即SQL語句又可寫成： Select

*

From

產(chǎn)品字段名中有特殊符號時，如空格等，字段名加中括號7.4數(shù)據(jù)查詢例7-5查詢羅斯文經(jīng)營的所有產(chǎn)品的ID、產(chǎn)品代碼和產(chǎn)品名稱，即檢索“產(chǎn)品”表中的部分字段。 Select

ID,產(chǎn)品代碼,產(chǎn)品名稱

From

產(chǎn)品7.4數(shù)據(jù)查詢例7-6查詢羅斯文經(jīng)營的前4種產(chǎn)品的ID、產(chǎn)品名稱。

Select

top4ID,產(chǎn)品名稱

From

產(chǎn)品例7-7查詢羅斯文經(jīng)營的前10%產(chǎn)品的ID、產(chǎn)品名稱。

Select

top10PercentID,產(chǎn)品名稱

From

產(chǎn)品7.4數(shù)據(jù)查詢Select類別from產(chǎn)品例7-8查詢產(chǎn)品中出現(xiàn)的不同類別。

Select

distinct

類別

From

產(chǎn)品7.4數(shù)據(jù)查詢字段需要計算后輸出計算訂單明細(xì)的金額selectID

,[訂單ID]

,數(shù)量,單價,數(shù)量*單價

from訂單明細(xì)7.4數(shù)據(jù)查詢統(tǒng)計的需求統(tǒng)計有多少種產(chǎn)品計算平均價格統(tǒng)計總數(shù)量查找最高單價和

最低單價7.4數(shù)據(jù)查詢數(shù)據(jù)查詢還可以使用集合函數(shù)實現(xiàn)數(shù)據(jù)統(tǒng)計的功能函數(shù)名功

能COUNT(*)計算出所有記錄數(shù)SUM(字段名)計算指定的字段的數(shù)值總和AVG(字段名)計算一個數(shù)值型字段的平均值MAX(字段名)計算指定的字段中的最大值MIN(字段名)計算指定的字段中的最小值7.4數(shù)據(jù)查詢例7-10查詢羅斯文經(jīng)營的產(chǎn)品數(shù)目，即統(tǒng)計“產(chǎn)品”表一共有多少行記錄。

Select

count(*)

From

產(chǎn)品例7-11查詢羅斯文經(jīng)營的產(chǎn)品中的最高列出價格和最低列出價格。

Select

max(列出價格),min(列出價格)

From

產(chǎn)品7.4數(shù)據(jù)查詢別名列名不能表達(dá)含義時，通過AS起別名 Select

count(*)as

產(chǎn)品數(shù)目

From

產(chǎn)品同樣適用于其他查詢

SelectID,產(chǎn)品名稱as

名稱

From

產(chǎn)品課堂練習(xí)查看“學(xué)生”表的學(xué)號、姓名、出生日期，其中出生日期字段顯示為生日。統(tǒng)計表中入學(xué)成績的平均分。查看表中有哪些民族（即只顯示不同的民族）。查看表中的學(xué)號、姓名和年齡學(xué)號姓名性別出生日期民族入學(xué)成績專業(yè)2014999001王梅女1996.10.4漢523大氣2014990030張鵬飛男1995.12.5彝480網(wǎng)絡(luò)安全2014992043歐陽洋男1996.1.23漢511計算機(jī)應(yīng)用2014999024卓瑪女1995.11.2藏478大氣學(xué)生(學(xué)號,姓名,性別,出生日期,民族,入學(xué)成績,專業(yè))7.4數(shù)據(jù)查詢條件：只查看某些記錄查看“麻油”和“醬油”的產(chǎn)品信息查看價格低于30元的產(chǎn)品查看轉(zhuǎn)入庫存的采購訂單明細(xì)記錄查看2006年6月30日之前接收的采購訂單明細(xì)where子句7.4數(shù)據(jù)查詢例如：查看價格低于30元的產(chǎn)品信息

select*

from

產(chǎn)品

where

列出價格<30例如：查看醬油的產(chǎn)品信息

select*

from

產(chǎn)品

where

產(chǎn)品名稱="醬油“7.4數(shù)據(jù)查詢例如：查看2006年6月30日之前接收的采購訂單

select*

from

采購訂單明細(xì)

where

接收日期<#2006/06/30#例如：查看轉(zhuǎn)入庫存的采購訂單明細(xì)記錄

select*

from

采購訂單明細(xì)

where

轉(zhuǎn)入庫存=truewhere

轉(zhuǎn)入庫存7.4數(shù)據(jù)查詢查詢條件運算符實例比較（比較運算符）=、>、<、>=、<=、<>標(biāo)準(zhǔn)成本>10確定范圍Between…And…標(biāo)準(zhǔn)成本Between10And20

（包含邊界值10和20）確定集合In、NotIn產(chǎn)品名稱In(“啤酒”,”茶”)字符匹配Like、NotLike公司like“*公司”空值ISNull、IsNotNull單位數(shù)量ISNull嵌套查詢All、Any（見7.4.4嵌套查詢）多重條件（邏輯運算符）And、Or、Not標(biāo)準(zhǔn)成本<20And標(biāo)準(zhǔn)成本>10字符說明?任意單字符*零個或多個字符#任意一位數(shù)字(0-9)[字符列表]字符列表中的任意單字符[!字符列表]不在字符列表中的任意單字符7.4數(shù)據(jù)查詢例7-13查詢羅斯文經(jīng)營的產(chǎn)品名稱中第3個字為“粉”或“糕”的產(chǎn)品代碼和產(chǎn)品名稱。

Select

產(chǎn)品代碼,產(chǎn)品名稱

From

產(chǎn)品

Where

產(chǎn)品名稱like

“??[粉糕]”7.4數(shù)據(jù)查詢Where子句為多重條件時，必須使用AND或OR來表達(dá)整體邏輯含義。Where

<條件表達(dá)式1>AND<條件表達(dá)式2>：表示既滿足第一個條件，又滿足第二個條件，即同時滿足。Where

<條件表達(dá)式1>OR<條件表達(dá)式2>：表示滿足第一個條件或者第二個條件，即滿足其一。7.4數(shù)據(jù)查詢例如SQL語句：

Select

類別,ID,產(chǎn)品代碼,產(chǎn)品名稱,標(biāo)準(zhǔn)成本

From

產(chǎn)品

Where

類別="調(diào)味品"AND標(biāo)準(zhǔn)成本<107.4數(shù)據(jù)查詢例如SQL語句：

Select

類別,ID,產(chǎn)品代碼,產(chǎn)品名稱,標(biāo)準(zhǔn)成本,再訂購水平

From

產(chǎn)品

Where

類別="焙烤食品"OR

類別="谷類"提示：每個條件表達(dá)式一定要完整，如：

Where類別="焙烤食品"OR類別="谷類"，不能寫成：Where類別="焙烤食品"OR"谷類"。課堂練習(xí)查找表中姓張的學(xué)生信息查找表中入學(xué)成績500~550的學(xué)生信息查找大氣專業(yè)藏族的學(xué)生信息查找1996年以前出生的女同學(xué)學(xué)號姓名性別出生日期民族入學(xué)成績專業(yè)2014999001王梅女1996.10.4漢523大氣2014990030張鵬飛男1995.12.5彝480網(wǎng)絡(luò)安全2014992043歐陽洋男1996.1.23漢511計算機(jī)應(yīng)用2014999024卓瑪女1995.11.2藏478大氣學(xué)生(學(xué)號,姓名,性別,出生日期,民族,入學(xué)成績,專業(yè))7.4數(shù)據(jù)查詢排序Orderby字段1[Asc|Desc][,字段2[Asc|Desc]][,…]Asc：指定的排序項按升序排列。Desc：指定的排序項按降序排列。排序字段通常寫為字段名，有時也用字段序號代替，第1個字段為1，第2個字段為2，…。默認(rèn)情況下相當(dāng)于使用Asc，Orderby按升序進(jìn)行排序，如果用戶特別要求按降序進(jìn)行排序，必須使用Desc。7.4數(shù)據(jù)查詢例7-14先按“類別”升序、再按“再訂購水平”降序查詢“產(chǎn)品”表中的類別、產(chǎn)品代碼、產(chǎn)品名稱和再訂購水平。

Select

類別,產(chǎn)品代碼,產(chǎn)品名稱,再訂購水平

From

產(chǎn)品

Orderby類別,再訂購水平Desc7.4數(shù)據(jù)查詢例如：查詢大氣專業(yè)的學(xué)生信息，要求按入學(xué)成績從高到低的順序顯示。

select*

from

學(xué)生

where

專業(yè)="大氣"

orderby入學(xué)成績desc學(xué)號姓名性別出生日期民族入學(xué)成績專業(yè)2014999001王梅女1996.10.4漢523大氣2014990030張鵬飛男1995.12.5彝480網(wǎng)絡(luò)安全2014992043歐陽洋男1996.1.23漢511計算機(jī)應(yīng)用2014999024卓瑪女1995.11.2藏478大氣學(xué)生(學(xué)號,姓名,性別,出生日期,民族,入學(xué)成績,專業(yè))7.4數(shù)據(jù)查詢重定向輸出Into新表名用于把查詢結(jié)果存放到一個新表中例7-15將“產(chǎn)品”表的所有調(diào)味品記錄存放在新表“調(diào)味品產(chǎn)品”中。

Select類別,產(chǎn)品代碼,產(chǎn)品名稱,再訂購水平

Into

調(diào)味品產(chǎn)品

From

產(chǎn)品

Where

類別="調(diào)味品"7.4數(shù)據(jù)查詢7.4數(shù)據(jù)查詢分組查詢查找結(jié)果如右圖，統(tǒng)計不同類別的產(chǎn)品數(shù)Select

類別,count(*)as產(chǎn)品數(shù)From

產(chǎn)品Groupby

類別只查找產(chǎn)品數(shù)少于3？Select

類別,count(*)as產(chǎn)品數(shù)From

產(chǎn)品Groupby

類別Havingcount(*)<3重要提示：SELECT字段列表中的所有字段都必須包括在GROUPBY子句中，或作為SQL聚合函數(shù)的參數(shù)包括在內(nèi)。7.4數(shù)據(jù)查詢Having子句與Where子句都是條件限定子句，但其作用對象不同，Having子句需要使用集合函數(shù)來限定查詢結(jié)果。對比項Where子句Having子句作用對象表字段計算字段與分組的關(guān)系無關(guān)只能用于分組查詢中，必須在Groupby之后7.4數(shù)據(jù)查詢例7-17按“訂單ID”統(tǒng)計“訂單明細(xì)”中已開票的每張訂單金額，只返回訂單金額在500元以下的訂單ID和訂單金額。

Select

[訂單ID],Sum(數(shù)量*單價)as訂單金額

From

訂單明細(xì)

Where

[狀態(tài)ID]=2

Groupby[訂單ID]

Having

Sum(數(shù)量*單價)<500課堂練習(xí)統(tǒng)計學(xué)生表中不同民族的學(xué)生人數(shù)。計算學(xué)生表中男生和女生的平均入學(xué)成績。統(tǒng)計平均入學(xué)成績在498分以上的專業(yè)。學(xué)號姓名性別出生日期民族入學(xué)成績專業(yè)2014999001王梅女1996.10.4漢523大氣2014990030張鵬飛男1995.12.5彝480網(wǎng)絡(luò)安全2014992043歐陽洋男1996.1.23漢511計算機(jī)應(yīng)用2014999024卓瑪女1995.11.2藏478大氣學(xué)生(學(xué)號,姓名,性別,出生日期,民族,入學(xué)成績,專業(yè))7.4數(shù)據(jù)查詢7.4數(shù)據(jù)查詢連接查詢又稱為多表查詢，用于檢索來自于多個數(shù)據(jù)源的查詢?nèi)N內(nèi)連接（INNERJOIN）外連接（OUTERJOIN）交叉連接（CROSSJOIN）建立連接的方法Where子句From子句7.4數(shù)據(jù)查詢例7-18查詢“訂單明細(xì)”表中產(chǎn)品的發(fā)貨城市。訂單(訂單ID,發(fā)貨城市,…)訂單明細(xì)(ID,訂單ID,產(chǎn)品ID,數(shù)量,單價)

Select

發(fā)貨城市,[產(chǎn)品ID],數(shù)量,單價

From

訂單,訂單明細(xì)

Where

訂單.[訂單ID]=訂單明細(xì).[訂單ID]7.4數(shù)據(jù)查詢Where

訂單.[訂單ID]=訂單明細(xì).[訂單ID]提示1：參與連接運算的多個表具有相同的字段名，必須在字段名前加上所屬的表名作為前綴，格式：

表名.字段名表名字段名表名字段名7.4數(shù)據(jù)查詢提示2：為了方便的引用表（或查詢），會給表（或查詢）起別名。

Select

發(fā)貨城市,[產(chǎn)品ID],數(shù)量,單價

From

訂單ASa,訂單明細(xì)ASb

Where

a.[訂單ID]=b.[訂單ID]

通過AS關(guān)鍵字起別名，AS可以省略。7.4數(shù)據(jù)查詢使用From子句建立連接查詢語法格式：From表1[連接類型]表2On連接條件連接類型，即指是內(nèi)連接（INNERJOIN）、外連接（LEFT|RIGHTJOIN）還是交叉連接（CROSSJOIN）。表1、表2分別指連接的兩個表對象（或查詢），如果連接的是同一個表，稱為內(nèi)連接，內(nèi)連接時，表必須起別名。連接條件，同上例中WHERE子句條件連接表達(dá)式，由被連接表中的列和比較運算符、邏輯運算符等構(gòu)成。7.4數(shù)據(jù)查詢兩種不同的寫法對比：

Select

發(fā)貨城市,[產(chǎn)品ID],數(shù)量,單價

From

訂單,訂單明細(xì)

Where

訂單.[訂單ID]=訂單明細(xì).[訂單ID]

Select

發(fā)貨城市,[產(chǎn)品ID],數(shù)量,單價

From

訂單InnerJoin

訂單明細(xì)

On

訂單.[訂單ID]=訂單明細(xì).[訂單ID]7.4數(shù)據(jù)查詢例7-19查找來自“江蘇”的訂單信息。

Select

訂單.*

From

訂單InnerJoin客戶

On訂單.[客戶ID]=客戶.[ID]

Where

客戶.[省/市/自治區(qū)]="江蘇"7.4數(shù)據(jù)查詢例7-20“客戶”表左外連接“訂單”表，查找所有客戶公司對應(yīng)的訂單信息。

Select

客戶.公司,[訂單ID],[員工ID],

訂單日期,發(fā)貨日期,[狀態(tài)ID]

From

客戶LeftJoin訂單

On訂單.[客戶ID]=客戶.[ID]7.4數(shù)據(jù)查詢嵌套查詢例7-21查找“已分派”的訂單明細(xì)信息。

Select

[訂單ID],[產(chǎn)品ID],數(shù)量,單價,[狀態(tài)ID]

From

訂單明細(xì)

Where

[狀態(tài)ID]=(Select[狀態(tài)ID] From訂單明細(xì)狀態(tài) Where狀態(tài)名="已分派")父查詢子查詢7.4數(shù)據(jù)查詢例7-22查找“東旗”和“廣通”公司的部分訂單信息。

Select

[訂單ID],[員工ID],[客戶ID],訂單日期,發(fā)貨日期

From

訂單

Where

[客戶ID]in(SelectID From客戶 Where公司="東旗"

or公司="廣通")7.4數(shù)據(jù)查詢7.4數(shù)據(jù)查詢單值嵌套返回結(jié)果是一個值的嵌套查詢比較運算符：=、>、<、>=、<=、<>多值嵌套子查詢的返回結(jié)果是一列值的嵌套查詢比較運算符：ANY（或SOME）、ALL和IN7.4數(shù)據(jù)查詢例7-23查找“產(chǎn)品”表中標(biāo)準(zhǔn)成本比所有“調(diào)味品”的標(biāo)準(zhǔn)成本還低的產(chǎn)品信息。

Select

ID,產(chǎn)品代碼,產(chǎn)品名稱,標(biāo)準(zhǔn)成本,類別

From

產(chǎn)品

Where

標(biāo)準(zhǔn)成本<ALL(Select標(biāo)準(zhǔn)成本

From產(chǎn)品

Where類別="調(diào)味品")7.4數(shù)據(jù)查詢例7-24查詢客戶ID為“28”的訂單明細(xì)。

Select

[員工ID],[產(chǎn)品ID],數(shù)量,單價

From

訂單明細(xì)innerjoin

(Select[訂單ID],[員工ID] From訂單 Where[客戶ID]=28)ASa ON訂單明細(xì).[訂單ID]=a.[訂單ID]7.4數(shù)據(jù)查詢例7-25把供應(yīng)商ID為“1”的采購訂單添加到“供應(yīng)商佳佳樂的訂單”表中。

insertinto供應(yīng)商佳佳樂的訂單

Select* From采購訂單 Where[供應(yīng)商ID]=1提示：“供應(yīng)商佳佳樂的訂單”的表結(jié)構(gòu)一定和返回的結(jié)果記錄集結(jié)構(gòu)一致，列數(shù)相同、數(shù)據(jù)類型相同。7.4數(shù)據(jù)查詢例7-26供應(yīng)商ID為8的采購訂單數(shù)量統(tǒng)一上調(diào)40。

update

采購訂單明細(xì)

set

數(shù)量=數(shù)量+40

Where

[采購訂單ID]In(Select[采購訂單ID] From采購訂單 Where[供應(yīng)商ID]=8)7.4數(shù)據(jù)查詢例7-27將“供應(yīng)商佳佳樂的訂單”表中的創(chuàng)建者“王偉”的訂單刪除。