Transcript 學習SQL 語言





熟悉資料庫系統的SQL查詢語言。
透過實例來學習SQL的語法。
安裝與設定MySQL。
利用MySQL測試SQL語法。


SQL是什麼 ?
SQL有什麼用途 ?
這是一個腦力激盪的園地!
relational data model與
programming language
的computation model不
同的特性也稱為
impedance mismatch。

資料查詢語言（DQL, Data Query Language）


資料定義語言（DDL, Data Definition
Language）


Create table, alter table
資料處理語言（DML, Data Manipulation
Language）


Select
Insert, Update, Delete
資料控制語言（DCL, Data Control Language）

Begin transaction, commit




將汽車銷售範例資料庫(EX5.mdf,EX5_log.ldf)
Attach（附加）到 MS SQL 資料庫伺服器
查出[台北銷售紀錄]之中，RAV4的銷售數量
查出產品編號 04 的產品名稱
查出 Yaris 的售價與庫存量

使用汽車銷售範例資料庫



USE EX5
結合台北銷售紀錄與產品基本資料，列出台北銷
售月報表，欄位包括：產品編號 ,產品名稱,數量,
售價
序前題，將售價改稱為[單價]，並計算『小計』


用 AS 更改欄位名稱
欄位可以直接加減乘除
-- 建立資料表
create table customers
(
cust_id char(3) not null primary key,
cust_name nvarchar(20),
region char(2),
phone char(13)
);
-- 刪除資料表
drop table customers;
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', '02999-1234-5');
'以單引號括住字串'
注意字串括號必須是半形字
‘不可以是全形字’
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');
-- 刪除一筆資料
delete from customers
where cust_id='I01';
-- 刪除全部資料
delete from customers;
-- 清除整個資料表內的資料
truncate table customers;
update customers
set phone='02-292-3311-4'
where cust_id='I02';



如何查出所有的日本（JP）客戶？
如何將項慧慧的區域別（region）改成TW？
如何新增一位客戶：




Cust_id=I07
Cust_name=Marty
Region=CN
可否將 Marty 的 cust_id 改成 I01？
SELECT [DISTINCT|ALL] {*|欄位名稱}
FROM 表格名稱
[WHERE 條件設定]
[GROUP BY 欄位名稱]
[HAVING 條件設定] /*針對GROUP BY */
[ORDER BY 欄位名稱]
關聯運算(relational operator)
作用
=
比較是否相等(equal)
<>
比較是否不相等(not equal)
>
比較是否大於
<
比較是否小於
>=
比較是否大於等於
<=
比較是否小於等於
select * from orders;
SELECT COUNT (DISTINCT ITEM) FROM ORDERS
-- 結果為3
SELECT AVG(QUANTITY) * COUNT(DISTINCT ITEM) FROM ORDERS
/* 結果為81 */
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');





AVG() 計算平均值 Average
SUM() 計算加總 Summation
COUNT() 計算個數
MAX() 求最大值
MIN() 求最小值



如何算出總銷售量（total quantity）？
如何算出產品I03總共賣出多少個？
如何算出產品I01共有幾張訂單？



用 AS 幫欄位取別名
用 AS 幫資料表取別名
AS 可以省略！





FIRST() 第一個
LAST() 最後一個
UCASE() 轉成大寫英文字母
LCASE() 轉成小寫英文字母
MID() 擷取字串中間的一段字



MID(column_name,start[,length])
從 1 開始數
LEN() 計算字串長度

ROUND() 四捨五入


ROUND(column_name, 小數點以下的位數)
NOW() 現在日期與時間

MS SQL 用 GetDate()
資料集VIEW，在微軟的系統中翻譯成『檢視』或『檢視表』
是一種虛擬的資料表
儲存的是SQL敘述，不是資料！

提昇資料存取使用的彈性


安全性


只要記一個名字，不必記憶複雜的整串SQL
可以隱藏不想讓特定人士看到的資料
一致性

一致的資料表示法
資料表 EMPLOYEE
檢視表 AvgSal
VIEW：觀點
資料集
檢視表
CREATE VIEW AvgSal
AS
SELECT AVG(Salary) AS 平均薪資
FROM EMPLOYEE ;
SELECT * FROM AvgSal;
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 /* 部門主管 */
);
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');
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);
資料表 EMPLOYEE
虛擬表格 DepEmp
CREATE VIEW DepEmp
AS
SELECT DepNo, EmpNo, Name, Salary
FROM Employee;
SELECT * FROM DepEmp ORDER BY DepNo, Salary;


有時候查詢所得到的資料很多，經過排序
(sorting)與分組(grouping)以後，會比較容易瀏
覽
資料記錄的排序可以用「order by」的語法，然
後以關鍵字「asc」代表升冪（Ascending）的
排序，以「desc」代表降冪（Descending）的
排序

以SQL列出各項產品(item)的總銷售量



以SQL列出各項產品被下訂的次數（訂單量）





SUM(quantity)
GROUP BY item
COUNT(order_no)
GROUP BY item
請將上面兩個結果合成一個
試計算各項產品的『平均訂單銷售量』，
平均訂單銷售量=總銷售量/訂單量
試比較使用VIEW和不用VIEW的差異

設計一個SQL敘述，列出各部門的平均薪資，依
照平均薪資的高低排列，薪資水準高的排前面。



AVG(Salary)
GROUP BY DepNo
以設計好的SQL製作一個名為 DepSalaryLevel
的 VIEW

合併

自然合併 JOIN … ON
 兩邊資料表都有的才留下

LEFT OUTER JOIN … ON
 左邊資料表有的都必須留下，右邊對不上的資料顯示
null

RIGHT OUTER JOIN … ON
 右邊資料表有的都必須留下，左邊對不上的資料顯示
null

聯集

UNION
SELECT …
FROM T1, T2
WHERE T1.F1=T2.F2;
相當於
SELECT …
FROM T1 INNER JOIN T2 ON
T1.F1=T2.F2;
SELECT …
FROM T1 LEFT OUTER JOIN T2
ON
T1.F1=T2.F2;
SELECT …
FROM T1 RIGHT OUTER JOIN
T2 ON
T1.F1=T2.F2;




使用 ch6-ex3.sql
以 SQL 列出員工名單，包含員工所屬的部門名
稱。
製作一個VIEW，名稱為EmpDept，列出『所有
的』員工，包含員工所屬的部門名稱。
製作一個VIEW，名稱為DeptEmp，列出『所有
的』部門，以及歸屬於該部門的員工。
SELECT …
UNION
SELECT …
將兩個(以上) SQL 查詢的結果合併起來

個別 SQL 語句所產生的資料必須
 欄位數量相同
 資料型別相同
 順序相同
UNION只返回不同值的資料列
如同 SELECT DISTINCT
SELECT …
UNION ALL
SELECT …
 UNION ALL 列出所有的資料

重複列出相同的資料





使用汽車銷售範例資料庫（EX5.mdf
EX5_log.ldf）
以 SQL 查詢『台北銷售紀錄』，列出『月份』、
『產品編號』、『產品名稱』三個欄位
以 SQL 查詢『台中銷售紀錄』，列出『月份』、
『產品編號』、『產品名稱』三個欄位
以 UNION ALL 指令將上述查詢結果合併成一
筆
改以 UNION 指令將上述查詢結果合併成一筆

複合條件（Compound Condition）


AND、OR、NOT（且、或、否）
述詞（Predicate）





BETWEEN ... AND（資料值介於…之間）
IN（在集合內）
EXISTS（交集不是空集合）
IS NULL（是空值，空值不能用等號比對）
LIKE（長得像…，模糊比對）
 萬用字元
 % 任何長度的任何字，沒有也可以
 _ 一個字，任何字皆可（Access 用 ?）

將一個結果塞入另一個資料表
INSERT INTO 表格名稱
SELECT …







使用汽車銷售範例資料庫（EX5.mdf
EX5_log.ldf）
把『台北銷售紀錄』和『台中銷售紀錄』合併，
存入『銷售紀錄』
查出售價介於160000和260000之間的產品
列出產品名稱之中有『a』字的產品
列出產品名稱以『C』開頭的產品
列出產品名稱以『s』結尾的產品
列出產品名稱第二個字是『i』的產品



『次查詢』或『子查詢』
以SELECT敘述產生一個集合
外層再包一個SQL敘述，以WHERE的條件與集
合內的資料進行比對




IN 在集合內
EXISTS 集合運算的結果不是空的
NOT EXISTS 集合運算的結果是空的
= 相等，必須確定集合運算的結果只有一個元素
括號裡頭限定只
能出現一個值！
SELECT DepNo AS 部門代碼, Name AS 部門名稱
FROM DEPARTMENT
WHERE Manager = (
SELECT EmpNo
FROM EMPLOYEE
WHERE Name='Marty'
);
SELECT D.DepNo AS 部門代碼, D.Name AS 部門名稱, E.Name AS 經理姓名
FROM DEPARTMENT D INNER JOIN EMPLOYEE E ON D.Manager=E.EmpNo
WHERE E.Name='Marty';
用 Subquery 查出來的資訊，大部份也都可以用 JOIN 查出來！？
SELECT EmpNo AS 員工代碼, Name AS 員工姓名
FROM EMPLOYEE
WHERE DepNo IN (
SELECT D.DepNo
FROM DEPARTMENT D INNER JOIN EMPLOYEE E
ON D.Manager=E.EmpNo
WHERE E.Name='Marty'
);
把括號裡的資料當成『集合』。
以另一個 SQL 產生一個資料集合，用 IN 判斷所需的資料是否在這個集合內！
如何列出 Marty 下屬們的『部門名稱』？






使用 ch6-ex3.sql
以 SQL 查出薪水比 Marty 高的人是誰？
全公司的平均薪資是多少錢？
列出薪水高於平均薪資的人
那一個部門的平均薪資最高？
那一位經理的部門平均薪資最高？


使用汽車銷售範例資料庫（EX5.mdf
EX5_log.ldf）
查出台北賣得最好（銷售金額最高）的產品


查出台中賣得最好的產品



以排序+TOP達成目的
以 MAX 達成目的
查出台北有賣，台中沒賣的產品
查出台中有賣，台北沒賣的產品


Adventure Works Cycles 商務案例
AdventureWorks 結構描述


MySQL：
`關鍵字`
MS SQL：
[關鍵字]



MySQL的取得
MySQL的安裝
MySQL使用的練習






建立使用環境與資料庫的指令
資料維護的指令
查詢的指令
進階的查詢指令
與系統相關的指令
更改資料庫結構的指令

列出所有的資料庫


建立資料庫


create database mydb;
使用資料庫


show databases;
use mydb;
列出資料庫中的表格

show tables;