第3章 SQL數(shù)據(jù)操縱語(yǔ)言

第三章SQL數(shù)據(jù)操縱語(yǔ)言3.1數(shù)據(jù)查詢3.2數(shù)據(jù)更新如沒(méi)有特別說(shuō)明本節(jié)的所有查詢都在Northwind數(shù)據(jù)庫(kù)中進(jìn)行。3.1數(shù)據(jù)查詢3.1.1SELECT語(yǔ)句結(jié)構(gòu)SELECT<字段列表表達(dá)式>［INTO表名］FROM<表名或視圖名列表>[WHERE<條件表達(dá)式>][GROUPBY<分組列表>[HAVING<篩選條件>]][ORDERBY<關(guān)鍵字表達(dá)式>[ASC|DESC][,<關(guān)鍵字表達(dá)式>[ASC|DESC]…]][TOP<數(shù)值表達(dá)式>[PERCENT]][UNION[ALL]<SELECT命令>]3.1.2簡(jiǎn)單查詢

最簡(jiǎn)單的查詢只由SELECT、FROM兩個(gè)子句構(gòu)成。1即指定字段列表表達(dá)式，每個(gè)之間用逗號(hào)分開(kāi)，可以使用星號(hào)(*)檢索所有列。例1查詢所有產(chǎn)品的基本信息。 例2查詢所有供應(yīng)商的名稱、所在城市和州。2使用DISTINCT|ALL消除重復(fù)行

例3查詢哪些州有供應(yīng)商。3.1.3使用WHERE子句指定行使用比較運(yùn)算符>、<、=、>=、<=、!=、<>例4查詢定購(gòu)數(shù)量大于10的定單信息。邏輯運(yùn)算符AND、OR、NOT例5查詢要求到貨日期在98-5-6或者是98-5-10號(hào)的定單信息。使用[NOT]BETWEEN…AND…BETWEEN…AND…是一個(gè)閉區(qū)間。例6查詢單價(jià)在15到20之間的所有產(chǎn)品的信息。使用[NOT]IN例7查詢所在州為‘TN’，‘OR’，‘MI’之一的供應(yīng)商的信息。使用[NOT]LIKE通配符%，代表任意數(shù)量的任意字符，？代表一個(gè)任意字符。 例8查詢產(chǎn)品名中含有“Tofu”的所有產(chǎn)品信息。判斷空值的查詢條件

IS［NOT］NULL ISNULL()函數(shù)例9查詢傳真號(hào)為空值的所有供應(yīng)商的信息。1使用AS更改列名 例10查詢所有訂單的訂單號(hào)、產(chǎn)品號(hào)和訂購(gòu)該產(chǎn)品的金額。2使用ORDERBY排序數(shù)據(jù)

語(yǔ)法：ORDERBY排序表達(dá)式1[ASCENDING|DESCENDING][，排序表達(dá)式2[ASCENDING|DESCENDING]…]

例11對(duì)上例查詢的結(jié)果按訂單號(hào)排序，同一訂單號(hào)的按金額降序排序。3.1.4格式化結(jié)果集3使用TOP返回指定行數(shù)

使用TOPN(N為整數(shù))返回指定的行，或使用TOPNPERCENT返回滿足查詢條件的總記錄的百分比。 例12查詢單價(jià)最高的5種商品。

3.1.5使用集函數(shù)COUNT(*|[DISTINCT|ALL]<列名>)統(tǒng)計(jì)元組(*)或統(tǒng)計(jì)一列中值(列名)的個(gè)數(shù)SUM([DISTINCT|ALL]<列名>)計(jì)算一列值的總和(該列必須為數(shù)值型)AVG([DISTINCT|ALL]<列名>)計(jì)算一列值的平均值(該列必須為數(shù)值型)MAX([DISTINCT|ALL]<列名>)求一列值中的最大值MIN([DISTINCT|ALL]<列名>)求一列值中的最小值如果指定了DISTINCT，則表示在計(jì)算時(shí)要取消指定列中的重復(fù)值。例13查詢所有定單的總交易額。例14查詢產(chǎn)品號(hào)為’11’的產(chǎn)品交易發(fā)生的次數(shù)和總交易額，平均、最高、最低交易額。3.1.6對(duì)查詢結(jié)果分組和篩選1分組查詢結(jié)果

GROUPBY子句將查詢結(jié)果進(jìn)行分組，分組原則是按分組列名表的值相等的分為一組。分組的目的是為了細(xì)化集函數(shù)的作用對(duì)象。如果未對(duì)查詢結(jié)果分組，則集函數(shù)將作用于整個(gè)查詢結(jié)果，即整個(gè)查詢結(jié)果只有一個(gè)函數(shù)值。

例15查詢每個(gè)定單的交易額。 例16查詢每種產(chǎn)品的交易發(fā)生的次數(shù)和交易額，交易量。2對(duì)分組結(jié)果篩選

使用HAVING短語(yǔ)，使返回的結(jié)果只包含滿足條件的組。HAVING短語(yǔ)只能放在GROUPBY分組子句中，和分組一起使用。 例17查詢交易額超過(guò)1000元的定單。 例18查詢單次交易量超過(guò)10且交易次數(shù)超過(guò)20次的產(chǎn)品。 什么時(shí)候該用HAVING篩選條件？其實(shí)很簡(jiǎn)單：要使用集函數(shù)的統(tǒng)計(jì)值的就應(yīng)該用HAVING篩選，在WHERE子句不能使用集函數(shù)。3.1.7聯(lián)接查詢1使用WHERE條件聯(lián)接

例19查詢1998年每個(gè)月每個(gè)員工接收訂單的總額。 例20查詢銷往每個(gè)國(guó)家的總金額。 例21查詢每份訂單的公司名稱、產(chǎn)品名稱。2使用JOIN聯(lián)接

FROM<數(shù)據(jù)源表或視圖>[[INNER|LEFTOUTER|RIGHTOUTER|FULL

OUTER｜CROSS]JOIN<表名>ON<聯(lián)接條件>…]

內(nèi)連接也叫自然連接，它是組合兩個(gè)表的常用方法。自然連接將兩個(gè)表中的列進(jìn)行比較，將兩個(gè)表中滿足連接條件的行組合起來(lái)，作為結(jié)果。語(yǔ)法：

SELECT列

FROM表1[inner]JION表2 ON表1.列=表2.列 試將上面幾例改為內(nèi)聯(lián)接查詢。1．內(nèi)連接（Innerjoin）2外聯(lián)接（outerjoin)在自然連接中，只有在兩個(gè)表中匹配的行才能在結(jié)果集中出現(xiàn)。而在外連接中可以只限制一個(gè)表，而對(duì)另外一個(gè)表不加限制（即所有的行都出現(xiàn)在結(jié)果集中）。外連接分為左外連接、右外連接和全外連接。左外連接是對(duì)連接條件中左邊的表不加限制；右外連接是對(duì)右邊的表不加限制；全外連接對(duì)兩個(gè)表都不加限制，所有兩個(gè)表中的行都會(huì)包括在結(jié)果集中。例22查詢所有客戶的公司名及與其處于同一城市的供應(yīng)商的公司名稱。交叉連接交叉連接也叫非限制連接，它將兩個(gè)表不加任何約束地組合起來(lái)。在數(shù)學(xué)上，就是兩個(gè)表的笛卡爾積。交叉連接后得到的結(jié)果集的行數(shù)是兩個(gè)被連接表的行數(shù)的乘積。3.1.8嵌套子查詢嵌套子查詢的執(zhí)行不依賴于外部嵌套。嵌套子查詢的執(zhí)行過(guò)程為：首先執(zhí)行子查詢，子查詢得到的結(jié)果集不被顯示出來(lái)，而是傳給外部查詢，作為外部查詢的條件使用，然后執(zhí)行外部查詢，并顯示查詢結(jié)果。子查詢可以多層嵌套。嵌套子查詢一般也分為兩種：子查詢返回單個(gè)值和子查詢返回一個(gè)值列表。（1）返回單個(gè)值，該值被外部查詢的比較操作（如，=、!=、<、<=、>、>=）使用，該值可以使子查詢中使用集合函數(shù)得到的值。

例23查詢單價(jià)高于平均價(jià)格的所有產(chǎn)品。 例24在pubs數(shù)據(jù)庫(kù)查詢書(shū)號(hào)為pc1035的作者的作者號(hào)、作者姓名。

（2）返回一個(gè)值列表，該列表被外部查詢的IN、NOTIN、ANY或ALL比較操作使用。IN表示屬于，即外部查詢中用于判斷的表達(dá)式的值與子查詢返回的值列表中的一個(gè)值相等；NOTIN表示不屬于。 例25查詢銷售總金額前5名的產(chǎn)品信息。

例26查詢地區(qū)為‘SP’的客戶訂購(gòu)的產(chǎn)品信息。ANY、SOME和ALL用于一個(gè)值與一組值的比較，以“>”為例，ANY表示大于一組值中的任意一個(gè)，ALL表示大于一組值中的每一個(gè)。比如，>ANY(1,2,3)相當(dāng)于大于MIN（1，2，3）；而>ALL(1,2,3)相當(dāng)于大于MAX（1，2，3）。SOME在SQL-92標(biāo)準(zhǔn)中與ANY含義相同。

例27查詢比最近三個(gè)訂單中任何一個(gè)交易額高的訂單號(hào)。2．相關(guān)子查詢?cè)谙嚓P(guān)子查詢中，子查詢的執(zhí)行依賴于外部查詢，多數(shù)情況下是子查詢的WHERE子句中引用了外部查詢的表。相關(guān)子查詢的執(zhí)行過(guò)程與嵌套子查詢完全不同，嵌套子查詢中子查詢只執(zhí)行一次，而相關(guān)子查詢中的子查詢需要重復(fù)地執(zhí)行。相關(guān)子查詢的執(zhí)行過(guò)程如下：（1）子查詢?yōu)橥獠坎樵兊拿恳恍袌?zhí)行一次，外部查詢將子查詢引用的列的值傳給子查詢。（2）如果子查詢的任何行與其匹配，外部查詢就返回結(jié)果行。（3）再回到第一步，直到處理完外部表的每一行。

1

Exists及NotExists查詢 例28查詢沒(méi)有任何訂購(gòu)的產(chǎn)品的信息。 例29查詢所有由‘5’號(hào)員工處理的產(chǎn)品信息。

2計(jì)算相關(guān)子查詢 例30列出累計(jì)銷售額在50000元以上的產(chǎn)品信息。 例31查詢每種產(chǎn)品的編號(hào)、名稱、銷售數(shù)量。3.1.9合并結(jié)果集

關(guān)系的集合操作主要包括UNION(并)、INTERSECT(交)、MINUS(差)和笛卡積，其中笛卡積通過(guò)交叉聯(lián)接實(shí)現(xiàn)。在標(biāo)準(zhǔn)SQL語(yǔ)言中，并沒(méi)有直接實(shí)現(xiàn)交、差運(yùn)算的方法。并集

例32查詢所有客戶和供應(yīng)商的公司名稱。 注意：使用Orderby排序只能放在查詢最后。交集 例33查詢?cè)?jīng)訂購(gòu)過(guò)20和60號(hào)產(chǎn)品的客戶。差集 例34查詢?cè)?jīng)訂購(gòu)過(guò)20但從沒(méi)有訂購(gòu)過(guò)60號(hào)產(chǎn)品的客戶。3.1.10在查詢的基礎(chǔ)上創(chuàng)建新表使用SELECTINTO語(yǔ)句可以在查詢的基礎(chǔ)上創(chuàng)建新表，SELECTINTO語(yǔ)句首先創(chuàng)建一個(gè)新表，然后用查詢的結(jié)果填充新表。例35查詢每種產(chǎn)品的編號(hào)、銷售數(shù)量并將結(jié)果保存在sales表中。3.2數(shù)據(jù)更新3.2.1添加數(shù)據(jù)1單記錄添加語(yǔ)法：INSERT

INTO表名[(列名列表）]VALUES(值列表）例36向SHIPPERS表中添加一條記錄。2從查詢中批量添加INSERT

INTO表名[(列名列表）]

SELECT查詢例37新建一個(gè)表并將SUPPLIERS及CUSTOMERS中的公司名稱添加到新表中。3.2.2修改數(shù)據(jù)1簡(jiǎn)單修改語(yǔ)句語(yǔ)法：UPDATETABLE表名SET列名＝值[，列名＝值…］[WHERE條件］例38將訂單號(hào)為10248、產(chǎn)品號(hào)為11的訂貨數(shù)量改為20。2使用子查詢修改UPDATE的子查詢可以在SET和WHERE子句中使用。例39修改sales表添加一列銷售總額，并修改數(shù)據(jù)。3.2.3