資料庫管理 Database Managent Ex.1-3 SQL指令練習

Presentation on theme: "資料庫管理 Database Managent Ex.1-3 SQL指令練習"— Presentation transcript:

1 資料庫管理 Database Managent Ex.1-3 SQL指令練習
系級：物理四 學號： 姓名：吳嘉峰 授課老師：楊維邦 教授 日期：2013/04/01

2 目錄 建立所需表格 線上購物系統 SQL指令練習 情境模擬 Aggregate Function 心得感想 表格設計心得 情境聯想心得

3 建立所需表格 線上購物系統

4 建立線上購物系統用表格 指令格式： CREATE TABLE 表格名稱 ( 欄位名稱1 資料型別(資料長度),
1. Customer 3. Smart_Phone 2. Order_list 4. Tabelt_Computer PRIMARY KEY(Order_Number) PRIMARY KEY(Order_Number) 指令格式： CREATE TABLE 表格名稱 ( 欄位名稱1 資料型別(資料長度), 欄位名稱2 資料型別(資料長度) PRIMARY KEY(欄位名稱) ) CREATE TABLE Customer ( Name char((16), Order_Number char(8) PRIMARY KEY(Order_Number) ) 設為PRIMARY KEY 表示該欄位不得為空值 且為唯一

5 檢視表格並輸入資料

6 SQL指令練習 情境模擬

7 Query： 以中文顯示欄位名稱 用途： 建置多語言網站時可使用
The Rename Operation Query： 以中文顯示欄位名稱 用途： 建置多語言網站時可使用 指令語法： SELECT `欄位1` as 新名稱 FROM `資料表名稱` WHERE 敘述式

8 Ordering the Display of Tuples
Query： 將手機依照售價遞增排序 指令語法： SELECT `欄位` FROM `資料表` ORDER BY `要排序的欄位` DESC #遞減排序

9 Query： 排除網站中售價最高的手機 並顯示其他手機
Tuple Variables Query： 排除網站中售價最高的手機 並顯示其他手機 價格22030的HTC One沒有被顯示！ 指令語法： SELECT `欄位名稱` FROM `資料表1`, `資料表1` as 表格變數名稱 WHERE 敘述式 # 由敘述式決定要顯示的東西，重點在於可以利用Tuple Varibles來比較相同表格內的欄位

10 各種Android版本的手機都顯示出來了
String Operation Query： 顯示作業系統為Android之手機 各種Android版本的手機都顯示出來了 指令語法： SELECT `欄位名稱` FROM `資料表名稱` WHERE `欄位名稱` LIKE “%要查詢的字串%” # %代表任何子字串 _代表任何字元

11 Set Operation – Union (聯集)
Brand Apple ASUS Google HTC HUAWEI LG NOKIA SAMSUNG Sharp Sony Acer ViewSonic Query： 找出有販賣手機或平板的品牌 指令語法： SELECT `欄位名稱1` FROM `資料表名稱1` UNION SELECT `欄位名稱2` FROM `資料表名稱2`

12 Set Operation – Intersection (交集)
MySQL指令語法： (替代語法) SELECT * FROM ( SELECT DISTINCT `欄位1` FROM `表格1` UNION ALL #欄位1和欄位2中相同的資料不會被彙整 SELECT DISTINCT `欄位2` FROM `表格2` ) AS TEMP GROUP BY TEMP.Brand #依照Brand欄位的名稱來做顯示 HAVING COUNT( * ) = 2 #顯示出現兩次的欄位 Query： 找出有販賣手機 也有販賣平板的品牌 不幸的是， 在MySQL中不存在交集指令！ Brand Apple ASUS SAMSUNG SQL指令語法： SELECT `欄位名稱1` FROM `資料表名稱1` INTERSECT SELECT `欄位名稱2` FROM `資料表名稱2`

13 Set Operation – Complement (差集)
Query： 找出只有販賣平板 但沒有販賣手機的品牌 在MySQL中亦不存在EXCEPT指令！ Brand Acer ViewSonic SQL指令語法： SELECT `欄位名稱1` FROM `資料表名稱1` EXCEPT SELECT `欄位名稱2` FROM `資料表名稱2` MySQL指令語法： (替代語法) SELECT `欄位1` FROM `資料表1` LEFT JOIN `資料表名稱2` ON 資料表1.欄位1 = 資料表2.欄位2 WHERE 資料表2.欄位2 IS NULL

14 Aggreate Functions – Average Value
Query： 找出所販售手機的平均價格 平均價格= 指令語法： AVG(`欄位名稱`)

15 Aggreate Functions – 最大最小值
Query： 找出最貴和最便宜的手機 最低價手機：HUAWEI U8520 價格：5150 最高價手機：HTC ONE 價格：22030 指令語法： MIN(`欄位名稱`) MAX(`欄位名稱`)

16 Aggreate Functions – sum of value
指令語法： SUM(`欄位名稱`) Query： 找出目前訂單的總銷售額 先顯示出目前訂單的狀況 總銷售額=76210

17 心得感想 表格設計& 情境聯想心得

18 心得感想 這次練習，我覺得最困難的地方在於要設想一個情境問 題，並用指定的語法解決，因為要假設問題，就必須先 假設系統已經設計完畢，不過其實我們還沒學過如何分 割表格，所以在假設表格時就花了不少功夫，接著還要 設計出一些問題可以用到我們所教得語法，最後還要完 美的解決它！ 在解決問題的過程中，我發現：表格設計的好壞真的很 重要！因為設計差的表格，你要讀娶得時候也會需要用 到非常複雜語法，增加程式設計師的困擾，我這次就被 我自己設計的訂單表格給逼瘋了！ 接著，第二大困境，MySQL居然沒有提供交集與差集 的語法！這時候我們只能土法煉鋼，用既有的語法組合 來達到目的，設計的過程中，便以經使用到了COUNT, GROUP BY, HAVING等aggregate function。 最後，本來想設計出可以計算每個顧客所消費的總金額， 不過因為表格設計不良，所以只好改計算訂單的總銷售 額，相信等我們學會表格設計的方法後，一定可以更容 易的完成這個目標。

19 The End. Thank you!