國開MySQL大數(shù)據(jù)庫應(yīng)用形考任務(wù)4

1、實驗訓(xùn)練1在MySQL中創(chuàng)建數(shù)據(jù)庫和表請到電腦端查看實驗?zāi)康氖煜?MySQL 環(huán)境的使用，掌握在MySQL 中創(chuàng)建數(shù)據(jù)庫和表的方法，理解MySQL 支持的數(shù)據(jù)類型、數(shù)據(jù)完整性在 MySQL下的表現(xiàn)形式，練習(xí)MySQL數(shù)據(jù)庫服務(wù)器的使用，練習(xí)CREATE TABLE SHOW TABLES DESCRIBE TABLEALTER TABLE DROP TABL造句的操作方法。實驗容：【實驗1-1】 MySQL的安裝與配置。參見4.1節(jié)容，完成MySQL數(shù)據(jù)庫的安裝與配置?！緦嶒?1-2】創(chuàng)建“汽車用品網(wǎng)上商城系統(tǒng)”數(shù)據(jù)庫。用CREATE DATABASE句創(chuàng)建 Shopping數(shù)據(jù)庫，或者通過

2、MySQL Workbench圖形化工具創(chuàng) 建 Shopping 數(shù)據(jù)庫?！緦嶒?-3在Shopping數(shù)據(jù)庫下，參見3.5節(jié)，創(chuàng)建表3-4表3-11的八個表?？梢允褂?CREATE TABLE句，也可以用 MySQL Workbench創(chuàng)建表?！緦嶒?-4使用SHOWA DESCRIB晤句查看表?！緦嶒?-5】使用 ALTER TABbE RENAME TABLED句管理表?！緦嶒?-6使用DROP TABL晤句刪除表，也可以使用MySQL Workbench刪除表。（注意：刪除前最好對已經(jīng)創(chuàng)建的表進行復(fù)制。）【實驗1-7】連接、斷開 MySQL服務(wù)器，啟動、停止 MySQL服務(wù)器?！緦嶒?

3、-8】使用SHOW DATABASE USE DATABASEDROP DATABASE1句管理“網(wǎng)上商城系 統(tǒng)” Shopping 數(shù)據(jù)庫。實驗要求：1 配合第 1 章第 3 章的理論講解，理解數(shù)據(jù)庫系統(tǒng)。2 .掌握MySQL工具的使用，通過 MySQL Workbench圖形化工具完成。3 每執(zhí)行一種創(chuàng)建、刪除或修改語句后，均要求通過MySQL Workbench 查看執(zhí)行結(jié)果。4 將操作過程以屏幕抓圖的方式復(fù)制，形成實驗文檔。實驗訓(xùn)練2：數(shù)據(jù)查詢操作請到電腦端查看實驗?zāi)康模夯趯嶒?創(chuàng)建的汽車用品網(wǎng)上商城數(shù)據(jù)庫Shopping,理解MySQL運算符、函數(shù)、謂詞,練習(xí)Select語句的操作

4、方法。實驗容：1. 單表查詢2.1】字段查詢1 )查詢商品名稱為“擋風(fēng)玻璃”的商品信息。分析： 商品信息存在于商品表，而且商品表中包含商品名稱此被查詢信息，因此這是只需要涉及一個表就可以完成簡單單表查詢。(2)查詢ID為1的訂單。分析：所有的訂單信息存在于訂單表中，而且訂單用戶ID 也存在于此表中，因此這是只需要查詢訂單表就可以完成的查詢。2.2 】多條件查詢查詢所有促銷的價格小于1000 的商品信息。分析： 此查詢過程包含兩個條件，第一個是是否促銷，第二個是價格，在商品表中均有此信息，因此這是一個多重條件的查詢。2.3 】 DISTINCT1 ）查詢所有對商品ID 為 1 的商品發(fā)表過評論的

5、用戶ID。分析： 條件和查詢對象存在于評論表中，對此商品發(fā)表過評論的用戶不止一個，而且一個用戶可以對此商品發(fā)表多個評論，因此，結(jié)果需要進行去重，這里使用DISTINCT實現(xiàn)。2）查詢此汽車用品網(wǎng)上商城會員的創(chuàng)建時間段，1 年為一段。分析： 通過用戶表可以完成查詢，每年可能包含多個會員，如果把此表中的創(chuàng)建年份都列出來會有重復(fù)，因此使用 DISTINCT去重。2.4 】 ORDER BY1 ）查詢類別ID 為 1 的所有商品，結(jié)果按照商品ID 降序排列。分析：從商品表中可以查詢出所有類別ID為1的商品信息，結(jié)果按照商品 ID的降序排列,因此使用ORDER BYg句，降序使用 DESC關(guān)鍵字。2）查

6、詢今年新增的所有會員，結(jié)果按照用戶名字排序。分析：在用戶表中可以完成查詢，創(chuàng)建日期條件設(shè)置為今年，此處使用語句ORDER BY。2.5 】 GROUP BY1 ）查詢每個用戶的消費總金額（所有訂單）分析：訂單表中包含每個訂單的訂單總價和用戶ID?，F(xiàn)在需要將每個用戶的所有訂單提取出來分為一類，通過 SUM()函數(shù)取得總金額。此處使用GROUP BY句和SUM()函數(shù)。2)查詢類別價格一樣的各種商品數(shù)量總和。分析：此查詢中需要對商品進行分類，分類依據(jù)是同類別和價格，這是“多列分組”，較上一個例子更為復(fù)雜。2.聚合函數(shù)查詢2.6 】 COUNT()1 )查詢類別的數(shù)量。分析：此查詢利用 COUNT(

7、)®數(shù)，返回指定列中值的數(shù)目，此處指定列是類別表中的ID (或者名稱均可)。2)查詢汽車用品網(wǎng)上商城的每天的接單數(shù)。分析：訂單相關(guān)，此處使用聚合函數(shù)COUNT(和Group by子句。2.7 】 SUM()查詢該商城每天的銷售額。分析：在訂單表中，有一列是訂單總價，將所有訂單的訂單總價求和，按照下單日期分組，使用SUM()函數(shù)和 Group by子句。2.8 】 AVG()1 )查詢所有訂單的平均銷售金額。分析：同上一個相同，還是在訂單表中，依然取用訂單總價列，使用AVG()函數(shù)，對指定列的值求平均數(shù)。2.9 】 MAX()1 )查詢所有商品中的數(shù)量最大者。分析： 商品的數(shù)量信息存在

8、于商品表中，此處查詢應(yīng)該去商品表，在商品數(shù)量指定列中求值最大者。使用 MAX()函數(shù)。2)查詢所有用戶按字母排序中名字最靠前者。分析：MAX()或者MIN()也可以用在文本列，以獲得按字母順序排列的最高或者最低者。同 上一個實驗一樣，使用 MAX()函數(shù)。2.10 】 MIN()1 )查詢所有商品中價格最低者。分析：同MAX ()用法相同，找到表和列，使用 MIN()函數(shù)。3.連接查詢2.11】連接查詢1 )查詢所有訂單的發(fā)出者名字。分析：此處訂單的信息需要從訂單表中得到，訂單表中主鍵是訂單號，外鍵是用戶ID,同時查詢需要得到訂單發(fā)出者的，也就是用戶名，因此需要將訂單表和用戶表通過用戶ID 進

9、行連接。使用連接的(INNER) JOIN語句。( 2)查詢每個用戶購物車中的商品名稱。分析：購物車中的信息可以從購物車表中得到，購物車表中有用戶ID 和商品 ID 兩項，通過這兩項可以與商品表連接，從而可以獲得商品名稱。與上一個實驗相似，此查詢使用(INNER)JOIN語句?！緦嶒?.12】外連接查詢(1)查詢列出所有用戶ID,以及他們的評論，如果有的話。分析：此查詢首先需列出所有用戶ID,如果參與過評論的話，再列出相關(guān)的評論。此處使用外查詢中的LEFT (OUTER) JOI語句，注意需將全部顯示的列名寫在JOIN語句左邊。(2)查詢列出所有用戶ID,以及他們的評論，如果有的話。分析：依然

10、是上一個實驗，還可以使用RIGHT (OUTER) JOIM句，注意需將全部顯示的列名寫在JOIN語句右邊。2.13】復(fù)合條件連接查詢1 )查詢用戶ID 為 1 的客戶的訂單信息和客戶名。分析： 復(fù)合條件連接查詢是在連接查詢的過程中，通過添加過濾條件，限制查詢的結(jié)果，使查詢的結(jié)果更加準(zhǔn)確。此查詢需在查詢的基礎(chǔ)上加上另一個條件，用戶iD 為 1 ，使用 AND語句添加精確條件。( 2)查詢每個用戶的購物車中的商品價格，并且按照價格順序排列。分析： 此查詢需要先使用連接對商品表和購物車表進行連接，得到商品的價格，在使用 ORDERBY語句對價格進行順序排列。4.嵌套查詢2.14】IN(1)查詢訂購

11、商品ID為1的訂單ID,并根據(jù)訂單ID查詢發(fā)出此訂單的用戶ID。分析：此查詢需要使用IN關(guān)鍵字進行子查詢，子查詢是通過SELEC由句在訂單明細表中先確定此訂單ID,在通過SELEC加訂單表中查詢到用戶 ID。(2)查詢訂購商品ID為1的訂單ID,并根據(jù)訂單ID查詢未發(fā)出此訂單的用戶ID。分析：此查詢和前一個實驗相似，只是需使用NOT IN語句。2.15】比較運算符1 )查詢今年新增會員的訂單，并且列出所有訂單總價小于100 的訂單ID。分析： 此查詢需要使用嵌套，子查詢需先查詢用戶表得到今年創(chuàng)建的用戶信息，在將用戶ID匹配找打訂單信息，其中使用比較運算符提供訂單總價小于100 的條件。(2)查

12、詢所有訂單商品數(shù)量總和小于100的商品ID,并將不在此商品所在類別的其他類別ID 列出來。分析：此查詢需要進行嵌套查詢，子查詢過程需要使用到SUM()函數(shù)和GROUP BY出同種商品的所有被訂數(shù)量，使用比較運算符得到數(shù)量總和小于100的商品ID,再使用比較運算符“不等于”得到非此商品所在類的類別ID。2.16】 EXISTS1 )查詢表中是否存在用戶ID 為 100 的用戶，如果存在，列出此用戶的信息。分析：EXIST法鍵字后面的參數(shù)是一個任意的子查詢， 系統(tǒng)對于查詢進行運算以判斷它是否 返回行，如果至少返回一行，那以 EXISTS勺結(jié)果為TRUE,此時外層查詢語句將進行查詢。 此查詢需要對用

13、戶ID進彳T EXISTS作。(2)查詢表中是否存在類別 ID為100的商品類別，如果存在，列出此類別中商品價格小于 5 的商品ID。分析：與上一個實驗相似，此實驗在外查詢過程添加了比較運算符。2.17】 ANY查詢所有商品表中價格比訂單表中商品ID對應(yīng)的價格大的商品ID。分析：ANY關(guān)鍵字在一個比較操作符的后面，表示若與子查詢返回的任何值比較為TRUE則返回TRUE此處使用ANY來引出查詢。2.18】 ALL查詢所有商品表中價格比訂單表中所有商品ID 對應(yīng)的價格大的商品ID。分析：使用ALL時需要同時滿足所有層查詢的條件。ALL關(guān)鍵字在一個比較操作符的后面,表示與子查詢返回的所有值比較為TR

14、UE則返回TRUE此處使用 ALL來引出查詢。2.19】集合查詢( 1 )查詢所有價格小于5 的商品，查詢類別ID 為 1 和 2 的所有商品，使用UNION 連接查詢結(jié)果。分析：由前所述，UNION將多個SELEC曲句的結(jié)果組合成一個結(jié)果集合，第 1條SELECTS 句查詢價格小于 5的商品，第2條SELECTS句查詢類別ID為1和2的商品，使用 UNION 將兩條SELEC砒句分隔開，執(zhí)行完畢之后把輸出結(jié)果組合為單個的結(jié)果集，并刪除重復(fù)的 記錄。(2)查詢所有價格小于 5的商品，查詢類別ID為1和2的所有商品，使用UNION ALL連接查詢結(jié)果。分析：使用UNION ALL包含重復(fù)的行，在

15、前面的例子中， 分開查詢時，兩個返回結(jié)果中有相 同的記錄，使用 UNION會自動去除重復(fù)行。 UNION ALL從查詢結(jié)果集中自動要返回所有匹 配行，而不進行刪除。實驗要求：1 所有操作必須通過MySQL Workbench 完成；2每執(zhí)行一種查詢語句后，均要求通過MySQL Workbench 查看執(zhí)行結(jié)果；3將操作過程以屏幕抓圖的方式拷貝，形成實驗文檔。實驗訓(xùn)練3 數(shù)據(jù)增刪改操作請到電腦端查看實驗?zāi)康模夯趯嶒? 創(chuàng)建的汽車用品網(wǎng)上商城數(shù)據(jù)庫Shopping， 練習(xí) Insert、 Delete、 TRUNCATE TABL、 EUpdate語句的操作方法， 理解單記錄插入與批量插入、DE

16、LET四TRUNCATE TABLE句、單表修改與多表修改的區(qū)別。實驗容：3-1 】插入數(shù)據(jù)(1)使用單記錄插入Insert語句分別完成汽車配件表Autoparts、商品類別表category、用戶表 Client、用戶類別表 Clientkind、購物車表 shoppingcart、訂單表 Order、訂單明細表 order_has_Autoparts 、 評論 Comment 的數(shù)據(jù)插入，數(shù)據(jù)值自定；并通過 select 語句檢查插入前后的記錄情況。( 2) 使用帶 Select 的 Insert 語句完成汽車配件表Autoparts 中數(shù)據(jù)的批量追加；并通過 select語句檢查插入前后

17、的記錄情況?！緦嶒?-2】刪除數(shù)據(jù)(1)使用 Delete語句分別完成購物車表shoppingcart、訂單表 Order、訂單明細表Order_has_Autoparts 、評論 Comment 的數(shù)據(jù)刪除，刪除條件自定；并通過select 語句檢查刪除前后的記錄情況。(2)使用TRUNCATETABLE語句分另1J完成購物車表shoppingcart、評論 Comment的數(shù)據(jù)刪除?！緦嶒?-3】修改數(shù)據(jù)使用Update分別完成汽車配件表Autoparts、商品類別表 category>用戶表 Client、用戶類別表 Clientkind > 購物車表 shoppingcar

18、t、訂單表 Order、訂單明細表 Order_has_Autoparts、 評論 Comment 的數(shù)據(jù)修改，修改后數(shù)據(jù)值自定，修改條件自定；并通過select 語句檢查修改前后的記錄情況。實驗要求：1 所有操作必須通過MySQL Workbench 完成；2 每執(zhí)行一種插入、刪除或修改語句后，均要求通過MySQL Workbench 查看執(zhí)行結(jié)果及表中數(shù)據(jù)的變化情況；3將操作過程以屏幕抓圖的方式拷貝，形成實驗文檔。實驗訓(xùn)練4：數(shù)據(jù)庫系統(tǒng)維護請到電腦端查看實驗?zāi)康模夯趯嶒? 創(chuàng)建的汽車用品網(wǎng)上商城，練習(xí)創(chuàng)建用戶、權(quán)限管理，數(shù)據(jù)庫備份與恢復(fù)方法，數(shù)據(jù)導(dǎo)出導(dǎo)入的方法，體會數(shù)據(jù)庫系統(tǒng)維護的主要工作。實驗容：1 數(shù)據(jù)庫安全性【實驗6-1】建立賬戶：創(chuàng)建一個用戶名為Teacher'密碼為'T99999'的用戶；創(chuàng)建一個用戶名為Student'密碼為'S11111'的用戶?！緦嶒?-2】用戶授權(quán)：將 Shopping數(shù)據(jù)庫上 SELECT INSERT DELETE UP