實驗訓練2數(shù)據(jù)查詢操作

實驗訓練2：數(shù)據(jù)查詢操作

基于實驗1創(chuàng)建的汽車用品網(wǎng)上商城數(shù)據(jù)庫Shopping,

實驗目的：理解MySQL運算符、函數(shù)、謂詞，練習Select語句的操作

方法。

1.單表查詢

【實驗2.1】字段查詢

(1)查詢商品名稱為“擋風玻璃”的商品信息。

分析：商品信息存在于商品表，而且商品表中包含商品名稱

此被查詢信息，因此這是只需要涉及一個表就可以完成簡單

單表查詢。

(2)查詢ID為1的訂單。

分析：所有的訂單信息存在于訂單表中，而且訂單用戶ID

也存在于此表中，因此這是只需要查詢訂單表就可以完成的

查詢。

【實驗2.21多條件查詢

查詢所有促銷的價格小于1000的商品信息。

分析：此查詢過程包含兩個條件，第一個是是否促銷，第二

個是價格，在商品表中均有此信息，因此這是一個多重條件

的查詢。

【實驗2.3]DISTINCT

(1)查詢所有對商品ID為1的商品發(fā)表過評論的用戶ID。

分析：條件和查詢對象存在于評論表中，對此商品發(fā)表過評

實驗內(nèi)容：論的用戶不止一個，而且一個用戶可以對此商品發(fā)表多個評

論，因此，結(jié)果需要進行去重，這里使用DISTINCT實現(xiàn)。

(2)查詢此汽車用品網(wǎng)上商城會員的創(chuàng)建時間段，1年為一

段。

分析：通過用戶表可以完成查詢，每年可能包含多個會員，

如果把此表中的創(chuàng)建年份都列出來會有重復，因此使用

DISTINCT去重。

【實驗2.4]ORDERBY

(1)查詢類別ID為1的所有商品，結(jié)果按照商品ID降序

排列。

分析：從商品表中可以查詢出所有類別ID為1的商品信息，

結(jié)果按照商品ID的降序排列，因此使用ORDERBY語句，降

序使用DESC關(guān)鍵字。

(2)查詢今年新增的所有會員，結(jié)果按照用戶名字排序。

分析：在用戶表中可以完成查詢，創(chuàng)建日期條件設置為今年，

此處使用語句ORDERBYo

【實驗2.5]GROUPBY

(1)查詢每個用戶的消費總金額(所有訂單)。

分析：訂單表中包含每個訂單的訂單總價和用戶ID?，F(xiàn)在需

要將每個用戶的所有訂單提取出來分為一類，通過SUM()函

數(shù)取得總金額。此處使用GROUPBY語句和SUM。函數(shù)。

(2)查詢類別價格一樣的各種商品數(shù)量總和。

分析：此查詢中需要對商品進行分類，分類依據(jù)是同類別和

價格，這是“多列分組”，較上一個例子更為復雜。

2.聚合函數(shù)查詢

【實驗2.6】COUNT()

(1)查詢類別的數(shù)量。

分析：此查詢利用COUNT。函數(shù)，返回指定列中值的數(shù)目，

此處指定列是類別表中的ID(或者名稱均可)。

(2)查詢汽車用品網(wǎng)上商城的每天的接單數(shù)。

分析：訂單相關(guān)，此處使用聚合函數(shù)COUNT。和Groupby子

句。

【實驗2.71SUMO

查詢該商城每天的銷售額。

分析：在訂單表中，有一列是訂單總價，將所有訂單的訂單

總價求和，按照下單日期分組，使用SUM。函數(shù)和Groupby

子句。

【實驗2.8]AVGO

(1)查詢所有訂單的平均銷售金額。

分析：同上一個相同，還是在訂單表中，依然取用訂單總價

列，使用AVGO函數(shù)，對指定列的值求平均數(shù)。

【實驗2.9]MAXO

(1)查詢所有商品中的數(shù)量最大者。

分析：商品的數(shù)量信息存在于商品表中，此處查詢應該去商

品表，在商品數(shù)量指定列中求值最大者。使用MAX。函數(shù)。

(2)查詢所有用戶按字母排序中名字最靠前者。

分析：MAXO或者MIN。也可以用在文本列，以獲得按字母順

序排列的最高或者最低者。同上一個實驗一樣，使用MAX()

函數(shù)。

【實驗2.10]MINO

(1)查詢所有商品中價格最低者。

分析：同MAX()用法相同，找到表和列，使用MINO函數(shù)。

3.連接查詢

【實驗2.11】內(nèi)連接查詢

(1)查詢所有訂單的發(fā)出者名字。

分析：此處訂單的信息需要從訂單表中得到，訂單表中主鍵

是訂單號，外鍵是用戶ID,同時查詢需要得到訂單發(fā)出者的

姓名，也就是用戶名，因此需要將訂單表和用戶表通過用戶

ID進行連接。使用內(nèi)連接的(INNER)JOIN語句。

(2)查詢每個用戶購物車中的商品名稱。

分析：購物車中的信息可以從購物車表中得到，購物車表中

有用戶ID和商品ID兩項，通過這兩項可以與商品表連接，

從而可以獲得商品名稱。與上一個實驗相似，此查詢使用

(INNER)JOIN語句。

【實驗2.12］外連接查詢

(1)查詢列出所有用戶ID,以及他們的評論，如果有的話。

分析：此查詢首先需列出所有用戶ID,如果參與過評論的話，

再列出相關(guān)的評論。此處使用外查詢中的LEFT(OUTER)JOIN

語句，注意需將全部顯示的列名寫在JOIN語句左邊。

(2)查詢列出所有用戶ID,以及他們的評論，如果有的話。

分析：依然是上一個實驗，還可以使用RIGHT(OUTER)JOIN

語句，注意需將全部顯示的列名寫在JOIN語句右邊。

【實驗2.13】復合條件連接查詢

(1)查詢用戶ID為1的客戶的訂單信息和客戶名。

分析：復合條件連接查詢是在連接查詢的過程中，通過添加

過濾條件，限制查詢的結(jié)果，使查詢的結(jié)果更加準確。此查

詢需在內(nèi)查詢的基礎上加上另一個條件，用戶iD為1,使用

AND語句添加精確條件。

(2)查詢每個用戶的購物車中的商品價格，并且按照價格

順序排列。

分析：此查詢需要先使用內(nèi)連接對商品表和購物車表進行連

接，得到商品的價格，在使用ORDERBY語句對價格進行順

序排列。

4.嵌套查詢

【實驗2.141IN

(1)查詢訂購商品ID為1的訂單ID,并根據(jù)訂單ID查詢

發(fā)出此訂單的用戶ID。

分析：此查詢需要使用IN關(guān)鍵字進行子查詢，子查詢是通

過SELECT語句在訂單明細表中先確定此訂單ID,在通過

SELECT在訂單表中查詢到用戶ID。

(2)查詢訂購商品ID為1的訂單ID,并根據(jù)訂單ID查詢

未發(fā)出此訂單的用戶ID。

分析：此查詢和前一個實驗相似，只是需使用NOTIN語句。

【實驗2.15】比較運算符

(1)查詢今年新增會員的訂單，并且列出所有訂單總價小

于100的訂單IDo

分析：此查詢需要使用嵌套，子查詢需先查詢用戶表得到今

年創(chuàng)建的用戶信息，在將用戶ID匹配找打訂單信息，其中

使用比較運算符提供訂單總價小于100的條件。

(2)查詢所有訂單商品數(shù)量總和小于100的商品ID,并將

不在此商品所在類別的其他類別的ID列出來。

分析：此查詢需要進行嵌套查詢，子查詢過程需要使用到

SUMO函數(shù)和GROUPBY求出同種商品的所有被訂數(shù)量，使用

比較運算符得到數(shù)量總和小于100的商品ID,再使用比較運

算符“不等于”得到非此商品所在類的類別ID。

【實驗2.16]EXISTS

(1)查詢表中是否存在用戶ID為100的用戶，如果存在，

列出此用戶的信息。

分析：EXISTS關(guān)鍵字后面的參數(shù)是一個任意的子查詢，系統(tǒng)

對于查詢進行運算以判斷它是否返回行，如果至少返回一

行，那以EXISTS的結(jié)果為TRUE,此時外層查詢語句將進行

查詢。此查詢需要對用戶ID進行EXIST操作。

(2)查詢表中是否存在類別ID為100的商品類別，如果存

在，列出此類別中商品價格小于5的商品ID。

分析：與上一個實驗相似，此實驗在外查詢過程添加了比較

運算符。

【實驗2.17]ANY

查詢所有商品表中價格比訂單表中商品ID對應的價格大的

商品IDo

分析：ANY關(guān)鍵字在一個比較操作符的后面，表示若與子查

詢返回的任何值比較為TRUE,則返回TRUE。此處使用ANY

來引出內(nèi)查詢。

【實驗2.18]ALL

查詢所有商品表中價格比訂單表中所有商品ID對應的價格

大的商品ID。

分析：使用ALL時需要同時滿足所有內(nèi)層查詢的條件。ALL

關(guān)鍵字在一個比較操作符的后面，表示與子查詢返回的所有

值比較為TRUE,則返回TRUE。此處使用ALL來引出內(nèi)查詢。

【實驗2.19)集合查詢

(1)查詢所有價格小于5的商品，查詢類別ID為1和2的

所有商品，使用UNION連接查詢結(jié)果。

分析：由前所述，UNION將多個SELECT語句的結(jié)果組合成一

個結(jié)果集合，第1條SELECT語句查詢價格小于5的商品，

第2條SELECT語句查詢類別ID為1和2的商品，使用UNION

將兩條SELECT語句分隔開，執(zhí)行完畢之后把輸出結(jié)果組合

為單個的結(jié)果集，并刪除重復的記錄。

(2)查詢所有價格小于5的商品，查詢類別ID為1和2的

所有商品，使用UNIONALL連接查詢結(jié)果。

分析：使用UNIONALL包含重復的行，在前面的例子中，分

開查詢時，兩個返回結(jié)果中有相同的記錄，使用UNION會自

動去除重復行。UNIONALL從查詢結(jié)果集中自動要返回所有

匹配行，而不進行刪除。

1.所有操作必須通過MySQLWorkbench完成；

2.每執(zhí)行一種查詢語句后，均要求通過MySQLWorkbench

實驗要求：

查看執(zhí)行結(jié)果；

3.將操作過程以屏幕抓圖的方式拷貝，形成實驗文檔。

實驗過程:

1.單表查詢

【實驗2.1】字段查詢

（1）查詢商品名稱為“擋風玻璃”的商品信息。

在查詢窗口中，輸入下面的命令。

SELECT*FROMshopping,autopartswhereApname="擋風玻璃”;

如下圖。

據(jù)MySQLWorkbench

倚LocalinstanceMySQLx

E>1?RditVir*Su?ryDatabaseS.?rv?r工。ohScripting

幻的,正上星1圉國瓦I伺w_______________

執(zhí)行結(jié)果如下。

Query1xSQL

1?SELECT*FROMshopping.autopartswhereApname="擋風玻漓I

Variablesnr

ResultSetFilterAEdit掰］口＞0a|Export/Import

ApnameimageJink1imagejink2image_link3Introductioin

擋風玻璃n-

TimeAction

Q108:49:27SELECT.FROMshopping.autopartswhereApname="擋風?！?row(s)returned

(2)查詢訂單表(Order)中客戶編號(Client_cid)為1的訂單,

輸入命令：SELECT*FROMshopping.OrderwhereClientcid=l;

執(zhí)行得到如下結(jié)果;

INavigator

1

MANAGEMENTaa：穹畫跖uQjji回

OServerStatus

1?SELECT,FROMshopping.OrderwhereClient_cid=3

jClientConnections2

fUsersandPrivileges

(StatusandSystemVariables?m>

±DataExport

R*tuitS*tRlter*Edit;￡口>[1^Export/Import的Wrap3iConUntE

QdSsasus<xrtor_<late0>ent_c?dooodsjxscccarnagejxcetotaljwcetotal_we?chtDwtnbutxxiType_dp?dcouner_mr

INSIANCt0,1談入購物率2022-0^)600:00:00118000500185002送貨上門001

QStartup/Shutdown

2放入購稅車2022go700:00:001240.006.00246.003快^皿1

AServerLogs*K-WIHCWldUFJ-nTOl

7OptionsFile

SCHEMAS??t*

QHtrrobjects

_.“一八___,__

【實驗2.21多條件查詢

查詢所有促銷的價格小于1000的商品信息。

分析：此查詢過程包含兩個條件，第一個是是否促銷，第二個是價格，在

商品表中均有此信息，因此這是一個多重條件的查詢。

命令如下：

SELECT*FROMshopping.Autopartswhereis_sale=landPrice<1000;

查詢結(jié)果如下圖。

QueryQ.atabasegerver[oolsScripting

匚W.2，「，\Q回

1?SELECT*FROMshopping.Autopartswhereis_sale?andPrice<;

ons

eges

emVariables4fll?|

ResultSetFilter*Edit值金;；.C>d,Export/lmoort匕WrapCellContentE

store

Apnamemage_link1fnagejird<2image_link3introductioinis_saleoW_pncePriceWeighi8_generalviitua

UuuniCTi

1詞潛油1100.0090.0099

ownMU

2玻璃水1150.00140.00100

cm

3檔風技博1300.00200.0030

K1"?KIU”,thu”

4坡璃刷1250.00200.0045

即C2I3cmstnm皿ESg皿CZ3皿回

*

【實驗2.3]DISTINCT

(1)查詢所有對商品ID為1的商品發(fā)表過評論的用戶ID。

SELECT*FROMshopping,commentwhereAutoparts_apid=l;

執(zhí)行結(jié)果如下。

㈡國17野蚊。I',■雷血回

1?SELECT*FROMmentwhereAutoparts_apid=3

■

ResultSetFilterEdit與Q巨:?Export/Import

CommentjdCommentsAutoparts_apidQient_cid

?1性能不錯，容易使用。11

3質(zhì)里好，體蛤不錯11

6服芬好12

分析：條件和查詢對象存在于評論表中，對此商品發(fā)表過評論的用戶不止

一個，而且一個用戶可以對此商品發(fā)表多個評論，因此，結(jié)果需要進行去

重，這里使用DISTINCT實現(xiàn)，命令如下。

selectdistinctClient_cidfromcommentwhereAutoparts_apid=l;

執(zhí)行如下；

ResultSetFilterExportLiQWrapCellContentJA

Qient_cid

(2)查詢此汽車用品網(wǎng)上商城會員的創(chuàng)建時間段，1年為一段。

分析：通過用戶表可以完成查詢，每年可能包含多個會員，如果把此表中

的創(chuàng)建年份都列出來會有重復，因此使用DISTINCT去重。

查詢2021年5月1日到2022年5月1日創(chuàng)建的會員，命令如下。

SelectdistinctCreatetimefromclientwhereCreatetime>“2021-05-01r

andCreatetime<w2022-05-01w;

執(zhí)行結(jié)果如下。__________________________________________________

CJHiaA外國

1?selectdistinctCreatetinefromclientwhereCreatetin?e>"2021-O5-01"andCreatetime<"2022-05-01"j

ariables<

ResultSetFilterExport_2^WrapC?llContentJA

Createtime

>2022-01-0100:00:00

12022-01-0300:00:00

【實驗2.4]ORDERBY

(1)查詢類別ID為1的所有商品，結(jié)果按照商品ID降序排列。

分析：從商品表中可以查詢出所有類別ID為1的商品信息，結(jié)果按照商品

ID的降序排列，因此使用ORDERBY語句，降序使用DESC關(guān)鍵字。

命令如下。

SELECT*FROMshopping,autopartswhereSecondClass_scid=lORDERBY

Apiddesc;

結(jié)果如下。

□H血回

1?SELECT*FROMshopping.autopartswhereSecondClass_scid=ORDERBYApiddesc;

sResultSetFilter*Edit[F篇ouExport/Import一日:隹jWrapCellContentJA

ApidApnameimagejink1imageJink2imageJink3Introductioints_saledd_pncePriceWeghis_generalvitu

-num-ennrQQS-i-BWW-nnn

?4玻璃刷250.002000045

nmii

3擋風玻璃1300.002000030

加，?GUM*

2玻璃水?1150.00140.00100

ntwCETI

1閏滑油1100.0090.0099

(2)查詢今年新增的所有會員，結(jié)果按照用戶名字排序。

分析：在用戶表中可以完成查詢，創(chuàng)建日期條件設置為今年，此處使用語

句ORDERBY,命令如下。

SELECT*FROMshopping,clientwhereCreatetime>z/2021-12-3l，zORDER

BYCname;

執(zhí)行結(jié)果。

.SQLFileVBM1

OQf'fAI?BI牌4Q血回

1?目；LECT*FROMshopping.clientwhereCreatetime>"2021-12-31"ORDERBYCnamej^U

<I_________________

iblesResultSetAlterEdit|Export/ImportLifij|WrapCellContentJA

ddImageCnamePasswordphone_numberEmadCreatetimeCkind

.1剛53張三111111asd@163.com2022-01-0100:00:0022

2GE3李四222222dfsdf@163.com2022-01-0300:00:0033

【實驗2.5]GROUPBY

（1）查詢每個用戶的消費總金額（所有訂單）。

分析：訂單表中包含每個訂單的訂單總價和用戶ID?，F(xiàn)在需要將每個用戶

的所有訂單提取出來分為一類，通過SUM。函數(shù)取得總金額。此處使用GROUP

BY語句和SUMO函數(shù)。

命令如下。

SELECT*FROMshopping,orderGROUPBYClient_cid;

執(zhí)行結(jié)果。

■■■?■M-RY?~v—uu

1?SELECT*FROMshopping.orderGROUPBYClient_cidj

<IiirI?

ResultSetFilterOEditEEExport/Importg上叵WrapCellContent]A

(W^atusorder_dateQient_ddgoods_pricecania9e_pricetotaljxicetotal.weightDistribubonType_dpidcourier一num

十放入購物車2022-0^)600:00:001180.005.00185.002送貨上門

3放人購物車2022-054)60^00:00290.009.0099.003郎寄

*P331；JZ?

(2)查詢類別價格一樣的各種商品數(shù)量總和。

分析：此查詢中需要對商品進行分類，分類依據(jù)是同類別和價格，這是“多

列分組”，較上一個例子更為復雜。

計算表autoparts中“分類編號”為1的商品現(xiàn)價總和，命令如下。

selectsum（price）fromshopping,autopartswhereSecondClass_scid=l;

計算結(jié)果如下。

1?selectsuir(price)fromshopping.autopartswhereSecondClass_scid=lj

nr

ResultSetFilter:AExport|WrapCellContent

sum(price)

1390.00

2.聚合函數(shù)查詢

【實驗2.6]COUNT()

(1)查詢類別的數(shù)量。

分析：此查詢利用COUNT。函數(shù)，返回指定列中值的數(shù)目，此處指定列是類

別表中的ID(或者名稱均可)。

統(tǒng)計表autoparts中，字段"Apid"有幾條記錄，命令如下。

selectCOUNT(Apid)fromshopping,autoparts;

執(zhí)行結(jié)果如下。

LJHIliz加乂比舞20罔I叟。也四

1?selectCOUNT(Apid)fromshopping.autopartsj|

4Irn|

ResultSetFilterExportWrai

COUNT(Apid)

(2)查詢汽車用品網(wǎng)上商城的每天的接單數(shù)。

分析：訂單相關(guān)，此處使用聚合函數(shù)COUNT。和Groupby子句。

統(tǒng)計表“order”中每天的接單數(shù)，命令如下。

selectCOUNT(order_date)fromshopping,orderGroupbyorder_date;

執(zhí)行結(jié)果如下。

selectCOUNT(orderdate)fromshopping.orderGroupbyorderdate

nr

ResultSetFilterExport毛|WrapCellContentJA

COUNT(order_date)

1

1

【實驗2.7】SUMO

查詢該商城每天的銷售額。

分析：在訂單表中，有一列是訂單總價，將所有訂單的訂單總價求和，按

照下單日期分組，使用SUM()函數(shù)和Groupby子句。

統(tǒng)計表“order”中每天的訂單總價，命令如下。

selectsum(total_price)fromshopping,orderGroupbyorder_date;

執(zhí)行結(jié)果如下。一

□HIf?fao?ai'?；?/p>

1?selectsum(total_price)fromshopping.orderGroupbyorder_datei|

4|m

ResultSetRlter|Export..g|WrapCellContent五

sum^otal_price)

?,185,00

246.00

99.00

【實驗2.8]AVG()

(1)查詢所有訂單的平均銷售金額。

分析：同上一個相同，還是在訂單表中，依然取用訂單總價列，使用AVG()

函數(shù)，對指定列的值求平均數(shù)。

計算訂單表“order”中訂單的平均價格，命令如下。

selectavg(total_price)fromshopping,order;

行動結(jié)果。

口ET夕派RO闿困1際I3Q血回

1?selectavg(total_price)fromshopping.orderj|

，I加一

ResultSetFilter*Export電WrpC

avgftotal_price)

>76666667

【實驗2.9]MAX()

(1)查詢所有商品中的數(shù)量最大者。

分析：商品的數(shù)量信息存在于汽車配件表中，此處查詢應該去汽車配

件表，在汽車配件表指定列中求值最大者。使用MAX。函數(shù)。

查詢汽車配件表(autoparts)中實際庫存(Inventory)最大的商品,

命令如下。

selectmax(Inventory)fromshopping.autoparts;

運行結(jié)果如下。

SULHietfx

□ROI^HI際l/Q血回

1?selectmax(Inventory)fromshopping.autopartsj|

ResultSetFilterExport園WrapCellContent:JA

max(lnventory)

,60

(2)查詢所有用戶按字母排序中名字最靠前者。

分析：MAX?；蛘進IN()也可以用在文本列，以獲得按字母順序排列的

最高或者最低者。同上一個實驗一樣，使用MAX。函數(shù)。

找出會員表(client)中，會員名稱(Cname)按字母順序排列的最靠

前者，命令如下：

selectmin(C