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

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

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

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

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

語法：ORDERBY排序表達式1[ASCENDING|DESCENDING][，排序表達式2[ASCENDING|DESCENDING]…]

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

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

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

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

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

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

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

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

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

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

SELECT列

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

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

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

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

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

1

Exists及NotExists查詢 例28查詢沒有任何訂購的產(chǎn)品的信息。 例29查詢所有由‘5’號員工處理的產(chǎn)品信息。

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

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

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

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

INTO表名[(列名列表）]

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