實驗訓練2：數據查詢操作實驗目的：基于實驗1創建的汽車用品網上商城數據庫Shopping，理解MySQL運算符、函數、謂詞，練習Select語句的操作方法。實驗內容：1.單表查詢【實驗2.1】字段查詢（1）查詢商品名稱為“擋風玻璃”的商品信息。分析：商品信息存在于商品表，而且商品表中包含商品名稱此被查詢信息，因此這是只需要涉及一個表就可以完成簡單單表查詢。（2）查詢ID為1的訂單。分析：所有的訂單信息存在于訂單表中，而且訂單用戶ID也存在于此表中，因此這是只需要查詢訂單表就可以完成的查詢。【實驗2.2】多條件查詢查詢所有促銷的價格小于1000的商品信息。分析：此查詢過程包含兩個條件，第一個是是否促銷，第二個是價格，在商品表中均有此信息，因此這是一個多重條件的查詢。【實驗2.3】DISTINCT（1）查詢所有對商品ID為1的商品發表過評論的用戶ID。分析：條件和查詢對象存在于評論表中，對此商品發表過評論的用戶不止一個，而且一個用戶可以對此商品發表多個評論，因此，結果需要進行去重，這里使用DISTINCT實現。（2）查詢此汽車用品網上商城會員的創建時間段，1年為一段。分析：通過用戶表可以完成查詢，每年可能包含多個會員，如果把此表中的創建年份都列出來會有重復，因此使用DISTINCT去重。【實驗2.4】ORDERBY（1）查詢類別ID為1的所有商品，結果按照商品ID降序排列。分析：從商品表中可以查詢出所有類別ID為1的商品信息，結果按照商品ID的降序排列，因此使用ORDERBY語句，降序使用DESC關鍵字。（2）查詢今年新增的所有會員，結果按照用戶名字排序。分析：在用戶表中可以完成查詢，創建日期條件設置為今年，此處使用語句ORDERBY。【實驗2.5】GROUPBY（1）查詢每個用戶的消費總金額（所有訂單）。分析：訂單表中包含每個訂單的訂單總價和用戶ID。現在需要將每個用戶的所有訂單提取出來分為一類，通過SUM()函數取得總金額。此處使用GROUPBY語句和SUM()函數。（2）查詢類別價格一樣的各種商品數量總和。分析：此查詢中需要對商品進行分類，分類依據是同類別和價格，這是“多列分組”，較上一個例子更為復雜。2.聚合函數查詢【實驗2.6】COUNT()（1）查詢類別的數量。分析：此查詢利用COUNT()函數，返回指定列中值的數目，此處指定列是類別表中的ID（或者名稱均可）。（2）查詢汽車用品網上商城的每天的接單數。分析：訂單相關，此處使用聚合函數COUNT()和Groupby子句。【實驗2.7】SUM()查詢該商城每天的銷售額。分析：在訂單表中，有一列是訂單總價，將所有訂單的訂單總價求和，按照下單日期分組，使用SUM()函數和Groupby子句。【實驗2.8】AVG()（1）查詢所有訂單的平均銷售金額。分析：同上一個相同，還是在訂單表中，依然取用訂單總價列，使用AVG()函數，對指定列的值求平均數。【實驗2.9】MAX()（1）查詢所有商品中的數量最大者。分析：商品的數量信息存在于商品表中，此處查詢應該去商品表，在商品數量指定列中求值最大者。使用MAX()函數。（2）查詢所有用戶按字母排序中名字最靠前者。分析：MAX()或者MIN()也可以用在文本列，以獲得按字母順序排列的最高或者最低者。同上一個實驗一樣，使用MAX()函數。【實驗2.10】MIN()（1）查詢所有商品中價格最低者。分析：同MAX（）用法相同，找到表和列，使用MIN()函數。3.連接查詢【實驗2.11】內連接查詢（1）查詢所有訂單的發出者名字。分析：此處訂單的信息需要從訂單表中得到，訂單表中主鍵是訂單號，外鍵是用戶ID，同時查詢需要得到訂單發出者的姓名，也就是用戶名，因此需要將訂單表和用戶表通過用戶ID進行連接。使用內連接的(INNER)JOIN語句。（2）查詢每個用戶購物車中的商品名稱。分析：購物車中的信息可以從購物車表中得到，購物車表中有用戶ID和商品ID兩項，通過這兩項可以與商品表連接，從而可以獲得商品名稱。與上一個實驗相似，此查詢使用(INNER)JOIN語句。【實驗2.12】外連接查詢（1）查詢列出所有用戶ID，以及他們的評論，如果有的話。分析：此查詢首先需列出所有用戶ID，如果參與過評論的話，再列出相關的評論。此處使用外查詢中的LEFT(OUTER)JOIN語句，注意需將全部顯示的列名寫在JOIN語句左邊。（2）查詢列出所有用戶ID，以及他們的評論，如果有的話。分析：依然是上一個實驗，還可以使用RIGHT(OUTER)JOIN語句，注意需將全部顯示的列名寫在JOIN語句右邊。【實驗2.13】復合條件連接查詢（1）查詢用戶ID為1的客戶的訂單信息和客戶名。分析：復合條件連接查詢是在連接查詢的過程中，通過添加過濾條件，限制查詢的結果，使查詢的結果更加準確。此查詢需在內查詢的基礎上加上另一個條件，用戶iD為1，使用AND語句添加精確條件。（2）查詢每個用戶的購物車中的商品價格，并且按照價格順序排列。分析：此查詢需要先使用內連接對商品表和購物車表進行連接，得到商品的價格，在使用ORDERBY語句對價格進行順序排列。4.嵌套查詢【實驗2.14】IN（1）查詢訂購商品ID為1的訂單ID，并根據訂單ID查詢發出此訂單的用戶ID。分析：此查詢需要使用IN關鍵字進行子查詢，子查詢是通過SELECT語句在訂單明細表中先確定此訂單ID，在通過SELECT在訂單表中查詢到用戶ID。（2）查詢訂購商品ID為1的訂單ID，并根據訂單ID查詢未發出此訂單的用戶ID。分析：此查詢和前一個實驗相似，只是需使用NOTIN語句。【實驗2.15】比較運算符（1）查詢今年新增會員的訂單，并且列出所有訂單總價小于100的訂單ID。分析：此查詢需要使用嵌套，子查詢需先查詢用戶表得到今年創建的用戶信息，在將用戶ID匹配找打訂單信息，其中使用比較運算符提供訂單總價小于100的條件。（2）查詢所有訂單商品數量總和小于100的商品ID，并將不在此商品所在類別的其他類別的ID列出來。分析：此查詢需要進行嵌套查詢，子查詢過程需要使用到SUM()函數和GROUPBY求出同種商品的所有被訂數量，使用比較運算符得到數量總和小于100的商品ID，再使用比較運算符“不等于”得到非此商品所在類的類別ID。【實驗2.16】EXISTS（1）查詢表中是否存在用戶ID為100的用戶，如果存在，列出此用戶的信息。分析：EXISTS關鍵字后面的參數是一個任意的子查詢，系統對于查詢進行運算以判斷它是否返回行，如果至少返回一行，那以EXISTS的結果為TRUE，此時外層查詢語句將進行查詢。此查詢需要對用戶ID進行EXIST操作。（2）查詢表中是否存在類別ID為100的商品類別，如果存在，列出此類別中商品價格小于5的商品ID。分析：與上一個實驗相似，此實驗在外查詢過程添加了比較運算符。【實驗2.17】ANY查詢所有商品表中價格比訂單表中商品ID對應的價格大的商品ID。分析：ANY關鍵字在一個比較操作符的后面，表示若與子查詢返回的任何值比較為TRUE，則返回TRUE。此處使用ANY來引出內查詢。【實驗2.18】ALL查詢所有商品表中價格比訂單表中所有商品ID對應的價格大的商品ID。分析：使用ALL時需要同時滿足所有內層查詢的條件。ALL關鍵字在一個比較操作符的后面，表示與子查詢返回的所有值比較為TRUE，則返回TRUE。此處使用ALL來引出內查詢。【實驗2.19】集合查詢（1）查詢所有價格小于5的商品，查詢類別ID為1和2的所有商品，使用UNION連接查詢結果。分析：由前所述，UNION將多個SELECT語句的結果組合成一個結果集合，第1條SELECT語句查詢價格小于5的商品，第2條SELECT語句查詢類別ID為1和2的商品，使用UNION將兩條SELECT語句分隔開，執行完畢之后把輸出結果組合為單個的結果集，并刪除重復的記錄。（2）查詢所有價格小于5的商品，查詢類別ID為1和2的所有商品，使用UNIONALL連接查詢結果。分析：使用UNIONALL包含重復的行，在前面的例子中，分開查詢時，兩個返回結果中有相同的記錄，使用UNION會自動去除重復行。UNIONALL從查詢結果集中自動要返回所有匹配行，而不進行刪除。實驗要求：1．所有操作必須通過MySQLWorkbench完成；2．每執行一種查詢語句后，均要求通過MySQLWorkbench查看執行結果；3．將操作過程以屏幕抓圖的方式拷貝，形成實驗文檔。實驗過程：1.單表查詢【實驗2.1】字段查詢查詢商品名稱為“擋風玻璃”的商品信息。在查詢窗口中，輸入下面的命令。SELECT*FROMshopping.autopartswhereApname="擋風玻璃";如下圖。執行結果如下。查詢訂單表（Order）中客戶編號（Client_cid）為1的訂單，輸入命令：SELECT*FROMshopping.OrderwhereClient_cid=1;執行得到如下結果；【實驗2.2】多條件查詢查詢所有促銷的價格小于1000的商品信息。分析：此查詢過程包含兩個條件，第一個是是否促銷，第二個是價格，在商品表中均有此信息，因此這是一個多重條件的查詢。命令如下：SELECT*FROMshopping.Autopartswhereis_sale=1andPrice<1000;查詢結果如下圖。【實驗2.3】DISTINCT（1）查詢所有對商品ID為1的商品發表過評論的用戶ID。SELECT*FROMmentwhereAutoparts_apid=1;執行結果如下。分析：條件和查詢對象存在于評論表中，對此商品發表過評論的用戶不止一個，而且一個用戶可以對此商品發表多個評論，因此，結果需要進行去重，這里使用DISTINCT實現，命令如下。selectdistinctClient_cidfromcommentwhereAutoparts_apid=1;執行如下；（2）查詢此汽車用品網上商城會員的創建時間段，1年為一段。分析：通過用戶表可以完成查詢，每年可能包含多個會員，如果把此表中的創建年份都列出來會有重復，因此使用DISTINCT去重。查詢2021年5月1日到2022年5月1日創建的會員，命令如下。SelectdistinctCreatetimefromclientwhereCreatetime>”2021-05-01”andCreatetime<”2022-05-01”;執行結果如下。【實驗2.4】ORDERBY（1）查詢類別ID為1的所有商品，結果按照商品ID降序排列。分析：從商品表中可以查詢出所有類別ID為1的商品信息，結果按照商品ID的降序排列，因此使用ORDERBY語句，降序使用DESC關鍵字。命令如下。SELECT*FROMshopping.autopartswhereSecondClass_scid=1ORDERBYApiddesc;結果如下。（2）查詢今年新增的所有會員，結果按照用戶名字排序。分析：在用戶表中可以完成查詢，創建日期條件設置為今年，此處使用語句ORDERBY，命令如下。SELECT*FROMshopping.clientwhere Createtime>"2021-12-31"ORDERBYCname;執行結果。【實驗2.5】GROUPBY（1）查詢每個用戶的消費總金額（所有訂單）。分析：訂單表中包含每個訂單的訂單總價和用戶ID。現在需要將每個用戶的所有訂單提取出來分為一類，通過SUM()函數取得總金額。此處使用GROUPBY語句和SUM()函數。命令如下。SELECT*FROMshopping.orderGROUPBYClient_cid;執行結果。（2）查詢類別價格一樣的各種商品數量總和。分析：此查詢中需要對商品進行分類，分類依據是同類別和價格，這是“多列分組”，較上一個例子更為復雜。計算表autoparts中“分類編號”為1的商品現價總和，命令如下。selectsum(price)fromshopping.autopartswhereSecondClass_scid=1;計算結果如下。2.聚合函數查詢【實驗2.6】COUNT()（1）查詢類別的數量。分析：此查詢利用COUNT()函數，返回指定列中值的數目，此處指定列是類別表中的ID（或者名稱均可）。統計表autoparts中，字段“Apid“有幾條記錄，命令如下。selectCOUNT(Apid)fromshopping.autoparts;執行結果如下。（2）查詢汽車用品網上商城的每天的接單數。分析：訂單相關，此處使用聚合函數COUNT()和Groupby子句。統計表“order”中每天的接單數，命令如下。selectCOUNT(order_date)fromshopping.orderGroupbyorder_date;執行結果如下。【實驗2.7】SUM()查詢該商城每天的銷售額。分析：在訂單表中，有一列是訂單總價，將所有訂單的訂單總價求和，按照下單日期分組，使用SUM()函數和Groupby子句。統計表“order”中每天的訂單總價，命令如下。selectsum(total_price)fromshopping.orderGroupbyorder_date;執行結果如下。【實驗2.8】AVG()（1）查詢所有訂單的平均銷售金額。分析：同上一個相同，還是在訂單表中，依然取用訂單總價列，使用AVG()函數，對指定列的值求平均數。計算訂單表“order”中訂單的平均價格，命令如下。selectavg(total_price)fromshopping.order;行動結果。【實驗2.9】MAX()（1）查詢所有商品中的數量最大者。分析：商品的數量信息存在于汽車配件表中，此處查詢應該去汽車配件表，在汽車配件表指定列中求值最大者。使用MAX()函數。查詢汽車配件表（autoparts）中實際庫存（Inventory）最大的商品，命令如下。selectmax(Inventory)fromshopping.autoparts;運行結果如下。（2）查詢所有用戶按字母排序中名字最靠前者。分析：MAX()或者MIN()也可以用在文本列，以獲得按字母順序排列的最高或者最低者。同上一個實驗一樣，使用MAX()函數。找出會員表（client）中，會員名稱（Cname）按字母順序排列的最靠前者，命令如下：selectmin(Cname)fromshopping.client;運行結果如下；找出會員表（client）中，會員名稱（Cname）按字母順序排列的最靠后者，命令如下：selectmax(Cname)fromshopping.client;運行結果如下；【實驗2.10】MIN()（1）查詢所有商品中價格最低者。分析：同MAX（）用法相同，找到表和列，使用MIN()函數。找出汽車配件表（autoparts）中，現價（price）最低者，命令如下。selectmin(price)fromshopping.autoparts;運行結果如下。3.連接查詢【實驗2.11】內連接查詢（1）查詢所有訂單的發出者名字。分析：此處訂單的信息需要從訂單表中得到，訂單表中主鍵是訂單號，外鍵是用戶ID，同時查詢需要得到訂單發出者的姓名，也就是用戶名，因此需要將訂單表和用戶表通過用戶ID進行連接。使用內連接的(INNER)JOIN語句。把訂單表中所用的訂單會員名稱從會員表中顯示出來，命令如下。selectshopping.client.Cnamefromshopping.clientjoinshopping.orderonshopping.client.Cid=shopping.order.Client_cid;執行結果如下；（2）查詢每個用戶購物車中的商品名稱。分析：購物車中的信息可以從購物車表中得到，購物車表中有用戶ID和商品ID兩項，通過這兩項可以與商品表連接，從而可以獲得商品名稱。與上一個實驗相似，此查詢使用(INNER)JOIN語句。把“購物車配件表”中的所有“汽車配件名稱”都顯示出來，命令如下。selectshopping.autoparts.Apnamefromshopping.autopartsjoinshopping.shoppingcartonshopping.autoparts.Apid=shopping.shoppingcart.Autoparts_apid;執行結果如下。【實驗2.12】外連接查詢（1）查詢列出所有用戶ID，以及他們的評論，如果有的話。分析：此查詢首先需列出所有用戶ID，如果參與過評論的話，再列出相關的評論。此處使用外查詢中的LEFT(OUTER)JOIN語句，注意需將全部顯示的列名寫在JOIN語句左邊。顯示表“client”中所有的用戶ID和他們在表“comment”中的評論，命令如下。selectshopping.client.cid,ment.Commentsfromshopping.clientleftouterjoinmentonshopping.client.cid=shopping.Comment.Client_cid;執行結果如下。（2）查詢列出所有用戶ID，以及他們的評論，如果有的話。分析：依然是上一個實驗，還可以使用RIGHT(OUTER)JOIN語句，注意需將全部顯示的列名寫在JOIN語句右邊。命令如下：selectshopping.client.cid,ment.Commentsfromshopping.clientrightouterjoinmentonshopping.client.cid=shopping.Comment.Client_cid;執行結果。通過觀察，兩者的運行結果不一樣。【實驗2.13】復合條件連接查詢（1）查詢用戶ID為1的客戶的訂單信息和客戶名。分析：復合條件連接查詢是在連接查詢的過程中，通過添加過濾條件，限制查詢的結果，使查詢的結果更加準確。此查詢需在內查詢的基礎上加上另一個條件，用戶iD為1，使用where語句添加精確條件。查找“會員編號（英文cid）”為1的所有訂單信息，命令如下。select*fromshopping.orderjoinshopping.clientonclient.cid=order.Client_cidwhereshopping.client.cid=1;行動結果。（2）查詢每個用戶的購物車中的商品價格，并且按照價格順序排列。分析：此查詢需要先使用內連接對商品表和購物車表進行連接，得到商品的價格，在使用ORDERBY語句對價格進行順序排列。對每個用戶購物車中的商品價格進行排序，命令如下。select*fromshopping.orderjoinshopping.clientonshopping.client.cid=shopping.order.Client_cidORDERBYshopping.order.goods_price;執行結果如下。4.嵌套查詢【實驗2.14】IN（1）查詢訂購商品ID為1的訂單ID，并根據訂單ID查詢發出此訂單的用戶ID。分析：此查詢需要使用IN關鍵字進行子查詢，子查詢是通過SELECT語句在訂單明細表中先確定此訂單ID，在通過SELECT在訂單表中查詢到用戶ID。顯示訂單中所有客戶在會員表（client）用戶的信息，命令如下。select*fromshopping.orderwhereshopping.order.Client_cidIN(selectshopping.Client.cidfromshopping.Client);執行結果。（2）查詢訂購商品ID為1的訂單ID，并根據訂單ID查詢未發出此訂單的用戶ID。分析：此查詢和前一個實驗相似，只是需使用NOTIN語句。顯示訂單中所有客戶不在會員表（client）訂單的信息，命令如下。select*fromshopping.orderwhereshopping.order.Client_cidnotIN(selectshopping.Client.cidfromshopping.Client);執行結果如下。【實驗2.15】比較運算符（1）查詢今年新增會員的訂單，并且列出所有訂單總價小于100的訂單ID。分析：此查詢需要使用嵌套，子查詢需先查詢用戶表得到今年創建的用戶信息，在將用戶ID匹配找打訂單信息，其中使用比較運算符提供訂單總價小于100的條件。找出訂單中所有貨品總價小于100的訂單，命令如下。select*fromshopping.orderwhereshopping.order.goods_price<100;執行結果如下。（2）查詢所有訂單商品數量總和小于100的商品ID，并將不在此商品所在類別的其他類別的ID列出來。分析：此查詢需要進行嵌套查詢，子查詢過程需要使用到SUM()函數和GROUPBY求出同種商品的所有被訂數量，使用比較運算符得到數量總和小于100的商品ID，再使用比較運算符“不等于”得到非此商品所在類的類別ID。命令如下。selectsum(Inventory)fromshopping.autopartsgroupbyshopping.autoparts.SecondClass_scid;執行結果。【實驗2.16】EXISTS（1）查詢表中是否存在用戶ID為100的用戶，如果存在，列出此用戶的信息。分析：EXISTS關鍵字后面的參數是一個任意的子查詢，系統對于查詢進行運算以判斷它是否返回行，如果至少返回一行，那以EXISTS的結果為TRUE，此時外層查詢語句將進行查詢。此查詢需要對用戶ID進行EXIST操作。找出會員表中，會員編號為100的用戶，命令如下。select*fromshopping.clientwhereshopping.client.cid=100;運行結果如下。（2）查詢表中是否存在類別ID為100的商品類別，如果存在，列出此類別中商品價格小于5的商品ID。分析：與上一個實驗相似，此實驗在外查詢過程添加了比較運算符。找出商品編號為100，并且價格小于5的商品，命令如下。select*fromshopping.Autopartswhereshopping.Autoparts.Apid=100andshopping.Autoparts.price<5;執行結果。【實驗2.17】ANY查詢所有商品表中價格比訂單表中商品ID對應的價格大的商品ID。分析：ANY關鍵字在一個比較操作符的后面，表示若與子查詢返回的任何值比較為TRUE，則返回TRUE。此處使用ANY來引出內查詢。命令如下。select*fromshopping.Autopartswhereshopping.Autoparts.price>any(selectshopping.order.goods_pricefromshopping.order);【實驗2.18】ALL查詢所有商品表中價格比訂單表中所有商品ID對應的價格大的商品ID。分析：使用ALL時需要同時滿足所有內層查詢的條件。ALL關鍵字在一個比較操作符的后面，表示與子查詢返回的所有值比