第07章 關系數(shù)據(jù)庫標準語言SQL

第07章關系數(shù)據(jù)庫標準語言內(nèi)容提要認識SQL語言創(chuàng)建SQL查詢SQL數(shù)據(jù)更新SQL數(shù)據(jù)查詢7.1認識SQL語言SQL:StructuredQueryLanguage，結(jié)構(gòu)化查詢語言是用戶和DBMS通信的語言和工具發(fā)展：1974年提出1986年被美國國家標準局批準成為關系型數(shù)據(jù)庫語言的標準各種類型的RDBMS都采用作為其存取語言和標準接口7.1認識SQL語言特點：功能豐富、使用靈活、語言簡捷易學高度的綜合：數(shù)據(jù)操作、數(shù)據(jù)定義、數(shù)據(jù)控制等非過程化：命令方式，提出“做什么”面向集合：操作對象和結(jié)果是元組的集合兩種使用方式，可嵌入語言結(jié)構(gòu)簡單、易學易用功能：數(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,”已回款”)提問：當該語言已經(jīng)執(zhí)行成功后，再執(zhí)行會出現(xiàn)什么情況？7.3SQL數(shù)據(jù)更新數(shù)據(jù)修改：將表中字段的值更改為指定的值，通過UPDATE語句實現(xiàn)。語法：

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

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

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

WHERE狀態(tài)ID=4

提示：如果沒有WHERE條件表達式，將可能刪除所有記錄。課堂練習在右圖所示表中完成：表字段說明：“貨號”字段類型是自動編號，“貨名”和“規(guī)格”文本型，“進貨價格”是貨幣型，進貨數(shù)量是數(shù)字型。“貨號”是主鍵添加新記錄，貨名：貝雷帽，規(guī)格：頂，進貨價格：98，進貨數(shù)量：6。將“貨號”是8的貨名改成“普通長褲”。將表中規(guī)格是“頂”的商品進貨價格改為以前的9折。刪除進貨數(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<條件表達式>][Groupby<字段名1>[,<字段名2>…][Having<篩選條件表達式>]][Orderby<字段名1>[Asc/Desc][,<字段名2[Asc/Desc]…]]7.4數(shù)據(jù)查詢例7-4查詢羅斯文經(jīng)營的所有產(chǎn)品，即檢索“產(chǎn)品”表中的所有行和列。Select[供應商ID],ID,產(chǎn)品代碼,產(chǎn)品名稱,說明,標準成本,列出價格,再訂購水平,目標水平,單位數(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ù)查詢字段需要計算后輸出計算訂單明細的金額selectID

,[訂單ID]

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

from訂單明細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ù)查詢別名列名不能表達含義時，通過AS起別名 Select

count(*)as

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

From

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

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

名稱

From

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

采購訂單明細

where

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

select*

from

采購訂單明細

where

轉(zhuǎn)入庫存=truewhere

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

（包含邊界值10和20）確定集合In、NotIn產(chǎn)品名稱In(“啤酒”,”茶”)字符匹配Like、NotLike公司like“*公司”空值ISNull、IsNotNull單位數(shù)量ISNull嵌套查詢All、Any（見7.4.4嵌套查詢）多重條件（邏輯運算符）And、Or、Not標準成本<20And標準成本>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來表達整體邏輯含義。Where

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

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

Select

類別,ID,產(chǎn)品代碼,產(chǎn)品名稱,標準成本

From

產(chǎn)品

Where

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

Select

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

From

產(chǎn)品

Where

類別="焙烤食品"OR

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

Where類別="焙烤食品"OR類別="谷類"，不能寫成：Where類別="焙烤食品"OR"谷類"。課堂練習查找表中姓張的學生信息查找表中入學成績500~550的學生信息查找大氣專業(yè)藏族的學生信息查找1996年以前出生的女同學學號姓名性別出生日期民族入學成績專業(yè)2014999001王梅女1996.10.4漢523大氣2014990030張鵬飛男1995.12.5彝480網(wǎng)絡安全2014992043歐陽洋男1996.1.23漢511計算機應用2014999024卓瑪女1995.11.2藏478大氣學生(學號,姓名,性別,出生日期,民族,入學成績,專業(yè))7.4數(shù)據(jù)查詢排序Orderby字段1[Asc|Desc][,字段2[Asc|Desc]][,…]Asc：指定的排序項按升序排列。Desc：指定的排序項按降序排列。排序字段通常寫為字段名，有時也用字段序號代替，第1個字段為1，第2個字段為2，…。默認情況下相當于使用Asc，Orderby按升序進行排序，如果用戶特別要求按降序進行排序，必須使用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è)的學生信息，要求按入學成績從高到低的順序顯示。

select*

from

學生

where

專業(yè)="大氣"

orderby入學成績desc學號姓名性別出生日期民族入學成績專業(yè)2014999001王梅女1996.10.4漢523大氣2014990030張鵬飛男1995.12.5彝480網(wǎng)絡安全2014992043歐陽洋男1996.1.23漢511計算機應用2014999024卓瑪女1995.11.2藏478大氣學生(學號,姓名,性別,出生日期,民族,入學成績,專業(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子句作用對象表字段計算字段與分組的關系無關只能用于分組查詢中，必須在Groupby之后7.4數(shù)據(jù)查詢例7-17按“訂單ID”統(tǒng)計“訂單明細”中已開票的每張訂單金額，只返回訂單金額在500元以下的訂單ID和訂單金額。

Select

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

From

訂單明細

Where

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

Groupby[訂單ID]

Having

Sum(數(shù)量*單價)<500課堂練習統(tǒng)計學生表中不同民族的學生人數(shù)。計算學生表中男生和女生的平均入學成績。統(tǒng)計平均入學成績在498分以上的專業(yè)。學號姓名性別出生日期民族入學成績專業(yè)2014999001王梅女1996.10.4漢523大氣2014990030張鵬飛男1995.12.5彝480網(wǎng)絡安全2014992043歐陽洋男1996.1.23漢511計算機應用2014999024卓瑪女1995.11.2藏478大氣學生(學號,姓名,性別,出生日期,民族,入學成績,專業(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查詢“訂單明細”表中產(chǎn)品的發(fā)貨城市。訂單(訂單ID,發(fā)貨城市,…)訂單明細(ID,訂單ID,產(chǎn)品ID,數(shù)量,單價)

Select

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

From

訂單,訂單明細

Where

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

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

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

Select

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

From

訂單ASa,訂單明細ASb

Where

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

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

Select

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

From

訂單,訂單明細

Where

訂單.[訂單ID]=訂單明細.[訂單ID]

Select

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

From

訂單InnerJoin

訂單明細

On

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

Select

訂單.*

From

訂單InnerJoin客戶

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

Where

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

Select

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

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

From

客戶LeftJoin訂單

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

Select

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

From

訂單明細

Where

[狀態(tài)ID]=(Select[狀態(tài)ID] From訂單明細狀態(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)品”表中標準成本比所有“調(diào)味品”的標準成本還低的產(chǎn)品信息。

Select

ID,產(chǎn)品代碼,產(chǎn)品名稱,標準成本,類別

From

產(chǎn)品

Where

標準成本<ALL(Select標準成本

From產(chǎn)品

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

Select

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

From

訂單明細innerjoin

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

insertinto供應商佳佳樂的訂單

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

update

采購訂單明細

set

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

Where

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