1 / 63

學習目標

學習目標. 熟悉資料庫系統的 SQL 查詢語言。 透過實例來學習 SQL 的語法。 安裝與設定 MySQL 。 利用 MySQL 測試 SQL 語法。. 從疑惑中開始學習. SQL 是什麼 ? SQL 有什麼用途 ?. 這是一個腦力激盪的園地 !. 關聯式資料庫查詢系統. SQL 資料查詢語言. relational data model 與 programming language 的 computation model 不同的特性也稱為 impedance mismatch 。. 資料查詢語言（ DQL, Data Query Language ）

abba
Download Presentation

學習目標

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 學習目標 • 熟悉資料庫系統的SQL查詢語言。 • 透過實例來學習SQL的語法。 • 安裝與設定MySQL。 • 利用MySQL測試SQL語法。

  2. 從疑惑中開始學習 • SQL是什麼 ? • SQL有什麼用途 ? 這是一個腦力激盪的園地!

  3. 關聯式資料庫查詢系統

  4. SQL資料查詢語言 relational data model與programming language的computation model不同的特性也稱為impedance mismatch。

  5. 資料查詢語言（DQL, Data Query Language） • Select • 資料定義語言（DDL, Data Definition Language） • Create table, alter table • 資料處理語言（DML, Data Manipulation Language） • Insert, Update, Delete • 資料控制語言（DCL, Data Control Language） • Begin transaction, commit

  6. 練習 • 將汽車銷售範例資料庫(EX5.mdf,EX5_log.ldf) Attach（附加）到 MS SQL 資料庫伺服器 • 查出[台北銷售紀錄]之中，RAV4的銷售數量 • 查出產品編號 04 的產品名稱 • 查出 Yaris 的售價與庫存量

  7. 練習 • 使用汽車銷售範例資料庫 • USE EX5 • 結合台北銷售紀錄與產品基本資料，列出台北銷售月報表，欄位包括：產品編號 ,產品名稱,數量,售價 • 序前題，將售價改稱為[單價]，並計算『小計』 • 用 AS 更改欄位名稱 • 欄位可以直接加減乘除

  8. DDL 資料定義語言 -- 建立資料表 create table customers ( cust_id char(3) not null primary key, cust_name nvarchar(20), region char(2), phone char(13) ); -- 刪除資料表 drop table customers;

  9. insert into customers (cust_id, cust_name, region, phone) values('I01', 'John', 'TW', '02-232-1111-3'); insert into customers (cust_id, cust_name, region, phone) values('I02', 'Mary', 'JP', '03-393-7457-4'); insert into customers values('I03', 'Anne', 'CA', '02-999-1234-5'); '以單引號括住字串' 注意字串括號必須是半形字 ‘不可以是全形字’

  10. MySQL與MS SQL 2008 適用 insert into customers values ('I04','許蓋功','TW','02-232-1111-3'), ('I05','蘇瑜珮','JP','03-393-7457-4'), ('I06','項慧慧','CA','02-999-1234-5'); 舊版的只能一次新增一筆紀錄 insert into customers values ('I04','許蓋功','TW','02-232-1111-3'); insert into customers values ('I05','蘇瑜珮','JP','03-393-7457-4'); insert into customers values ('I06','項慧慧','CA','02-999-1234-5');

  11. -- 刪除一筆資料 delete from customers where cust_id='I01'; -- 刪除全部資料 delete from customers; -- 清除整個資料表內的資料 truncate table customers;

  12. update customers set phone='02-292-3311-4' where cust_id='I02';

  13. 練習 • 如何查出所有的日本（JP）客戶？ • 如何將項慧慧的區域別（region）改成TW？ • 如何新增一位客戶： • Cust_id=I07 • Cust_name=Marty • Region=CN • 可否將 Marty 的 cust_id 改成 I01？

  14. SQL的查詢語法 SELECT [DISTINCT|ALL] {*|欄位名稱}FROM 表格名稱[WHERE 條件設定][GROUP BY 欄位名稱][HAVING 條件設定] /*針對GROUP BY */[ORDER BY 欄位名稱]

  15. 聚集函數的使用Aggregation Functions select * from orders; SELECT COUNT (DISTINCT ITEM) FROM ORDERS -- 結果為3 SELECT AVG(QUANTITY) * COUNT(DISTINCT ITEM) FROM ORDERS /* 結果為81 */

  16. create table orders ( order_no char(4) not null primary key, item char(3) not null, quantity int default 1, date datetime default GETDATE() ); insert into orders (order_no, item) values ('P001', 'I01'); update orders set quantity=40, date='2010/02/01' where order_no='P001'; insert into orders values ('P002','I03',20,'2010/3/4'), ('P003','I01',50,'2010/2/15'), ('P004','I03',10,'2010/5/1'), ('P005','I02',15,'2010/5/9');

  17. 聚集函數的使用Aggregation Functions • AVG() 計算平均值 Average • SUM() 計算加總 Summation • COUNT() 計算個數 • MAX() 求最大值 • MIN() 求最小值

  18. 練習 • 如何算出總銷售量（total quantity）？ • 如何算出產品I03總共賣出多少個？ • 如何算出產品I01共有幾張訂單？

  19. 進階SQL語法 • 用 AS 幫欄位取別名 • 用 AS 幫資料表取別名 • AS 可以省略！

  20. 其他好用的SQL函數 • FIRST() 第一個 • LAST() 最後一個 • UCASE() 轉成大寫英文字母 • LCASE() 轉成小寫英文字母 • MID() 擷取字串中間的一段字 • MID(column_name,start[,length]) • 從 1 開始數 • LEN() 計算字串長度

  21. 其他好用的SQL函數 • ROUND() 四捨五入 • ROUND(column_name, 小數點以下的位數) • NOW() 現在日期與時間 • MS SQL 用 GetDate()

  22. 資料集VIEW，在微軟的系統中翻譯成『檢視』或『檢視表』資料集VIEW，在微軟的系統中翻譯成『檢視』或『檢視表』 是一種虛擬的資料表 儲存的是SQL敘述，不是資料！

  23. 提昇資料存取使用的彈性 • 只要記一個名字，不必記憶複雜的整串SQL • 安全性 • 可以隱藏不想讓特定人士看到的資料 • 一致性 • 一致的資料表示法

  24. 資料集的定義與使用(1) 資料表 EMPLOYEE 檢視表 AvgSal VIEW：觀點 資料集 檢視表 CREATE VIEW AvgSal AS SELECT AVG(Salary) AS 平均薪資 FROM EMPLOYEE ; SELECT * FROM AvgSal;

  25. CREATE TABLE EMPLOYEE ( EmpNo CHAR(3) NOT NULL PRIMARY KEY, Name NVARCHAR(20) NOT NULL, Salary MONEY, DepNo CHAR(3) /* 員工不一定歸部門 */ ); CREATE TABLE DEPARTMENT ( DepNo CHAR(3) NOT NULL PRIMARY KEY, Name NVARCHAR(20) NOT NULL, Manager CHAR(3) NOT NULL /* 部門主管 */ );

  26. INSERT INTO EMPLOYEE VALUES ('E01','Marty',36000,'D01'), ('E02','Rick',15000,'D03'), ('E03','Allen',24000,'D02'), ('E04','Mary',12000,'D01'), ('E05','Tom',24000,'D01'), ('E06','Joseph',38000,'D03'); INSERT INTO DEPARTMENT VALUES ('D01','RD','E01'), ('D02','MIS','E02'), ('D03','Marketing','E06');

  27. ALTER TABLE EMPLOYEE WITH NOCHECK ADD CONSTRAINT FK1 FOREIGN KEY(DepNo) REFERENCES DEPARTMENT(DepNo); ALTER TABLE DEPARTMENT ADD CONSTRAINT FK2 FOREIGN KEY(Manager) REFERENCES EMPLOYEE(EmpNo);

  28. 資料集的定義與使用(2) 資料表 EMPLOYEE 虛擬表格 DepEmp CREATE VIEW DepEmp AS SELECT DepNo, EmpNo, Name, Salary FROM Employee; SELECT * FROM DepEmp ORDER BY DepNo, Salary;

  29. 有時候查詢所得到的資料很多，經過排序(sorting)與分組(grouping)以後，會比較容易瀏覽有時候查詢所得到的資料很多，經過排序(sorting)與分組(grouping)以後，會比較容易瀏覽 • 資料記錄的排序可以用「order by」的語法，然後以關鍵字「asc」代表升冪（Ascending）的排序，以「desc」代表降冪（Descending）的排序

  30. 練習：orders 資料表 • 以SQL列出各項產品(item)的總銷售量 • SUM(quantity) • GROUP BY item • 以SQL列出各項產品被下訂的次數（訂單量） • COUNT(order_no) • GROUP BY item • 請將上面兩個結果合成一個 • 試計算各項產品的『平均訂單銷售量』，平均訂單銷售量=總銷售量/訂單量 • 試比較使用VIEW和不用VIEW的差異

  31. 練習：orders 資料表 • 設計一個SQL敘述，列出各部門的平均薪資，依照平均薪資的高低排列，薪資水準高的排前面。 • AVG(Salary) • GROUP BY DepNo • 以設計好的SQL製作一個名為 DepSalaryLevel 的 VIEW

  32. 進階SQL語法 • 合併 • 自然合併 JOIN … ON • 兩邊資料表都有的才留下 • LEFT OUTER JOIN … ON • 左邊資料表有的都必須留下，右邊對不上的資料顯示 null • RIGHT OUTER JOIN … ON • 右邊資料表有的都必須留下，左邊對不上的資料顯示null • 聯集 • UNION

  33. 內部合併 INNER JOIN SELECT …FROM T1, T2WHERE T1.F1=T2.F2; 相當於 SELECT …FROM T1 INNER JOIN T2 ON T1.F1=T2.F2;

  34. 左外部合併列出左邊資料表全部的項目 SELECT …FROM T1 LEFT OUTER JOIN T2 ONT1.F1=T2.F2;

  35. 右外部合併列出右邊資料表全部的項目 SELECT …FROM T1 RIGHT OUTER JOIN T2 ONT1.F1=T2.F2;

  36. 練習：JOIN • 使用 ch6-ex3.sql • 以 SQL 列出員工名單，包含員工所屬的部門名稱。 • 製作一個VIEW，名稱為EmpDept，列出『所有的』員工，包含員工所屬的部門名稱。 • 製作一個VIEW，名稱為DeptEmp，列出『所有的』部門，以及歸屬於該部門的員工。

  37. 聯集 UNION SELECT … UNION SELECT … • 將兩個(以上) SQL 查詢的結果合併起來 • 個別 SQL 語句所產生的資料必須 • 欄位數量相同 • 資料型別相同 • 順序相同 • UNION只返回不同值的資料列如同 SELECT DISTINCT

  38. 聯集 UNION ALL SELECT … UNION ALL SELECT … • UNION ALL 列出所有的資料 • 重複列出相同的資料

  39. 練習：UNION • 使用汽車銷售範例資料庫（EX5.mdfEX5_log.ldf） • 以 SQL 查詢『台北銷售紀錄』，列出『月份』、『產品編號』、『產品名稱』三個欄位 • 以 SQL 查詢『台中銷售紀錄』，列出『月份』、『產品編號』、『產品名稱』三個欄位 • 以 UNION ALL 指令將上述查詢結果合併成一筆 • 改以 UNION 指令將上述查詢結果合併成一筆

  40. 進階SQL語法 • 複合條件（Compound Condition） • AND、OR、NOT（且、或、否） • 述詞（Predicate） • BETWEEN ... AND（資料值介於…之間） • IN（在集合內） • EXISTS（交集不是空集合） • IS NULL（是空值，空值不能用等號比對） • LIKE（長得像…，模糊比對） • 萬用字元 • % 任何長度的任何字，沒有也可以 • _ 一個字，任何字皆可（Access 用 ?）

More Related