Transcript SQL Server教育訓練課程

Structured Query
Language
1
Select 指令介紹
2
資料查詢（Select語法介紹）
SELECT [predicate] select_list
[ INTO new_table]
FROM <table list>
[WHERE <row selection specification>]
[GROUP BY <grouping specification>]
[HAVING search_condition]
[ORDER BY order_expression [ASC|DESC] ]
3
Predicate
ALL :
select all * from suppliers
select * from suppliers
DISTINCT: 省略重複資料之欄位
DISTINCEROW :省略重複資料的前後記錄列
TOP N [PERCENT]: 傳回記錄中最前面的n個或
n%數目的記錄
4
選取所有的欄位
範例1、查詢供應商表格所有資料錄所有
欄位的資料
select * from suppliers
5
選取特定的欄位
範例2、查詢所有經銷商的公司名稱與負
責人姓名
select company, boss_name from
distributors
6
選取唯一的數值
範例3、查詢已經下訂單的經銷商號碼，
但去除重複的經銷商號碼
 Select distinct distri_num from orders
 Select distinctrow distri_num from orders
7
選取子字串
範例4、查詢所有經銷商的電話區域碼
select left(phone, 2) from distributors
8
練習1
練習1、我們於範例4中雖然查詢出所有
經銷商的電話區域碼，但是重複資料甚
多，請設計一SQL敘述顯示單一區域碼
9
選取特定的資料錄
範例5：查詢供應商代碼為HHT供應商所
有的相關資料
Select * from suppliers where
supp_code="HHT"
10
排除特定的資料錄
範例6：查詢供應商代碼不為HHT的供應
商所有的相關資料
Select * from suppliers where supp_code
<> "HHT"
11
選取介於某一範圍的資料
範例7：查詢單價介於20元與30元之間所
有產品的產品名稱與單價資料
Select description, unit_price from products
where unit_price between 20 and 30
12
練習2
•練習2：使用’(‘ 、 ‘)’ 、 ‘>=’ 、
‘<=’ 以及 AND邏輯運算元，將範
例7改寫，查詢單價介於20元與30元
之間所有產品名稱與單價資料。
13
選取屬於某一子集合的資料
範例8：查詢經銷商號碼為1018、1014、
1006、1001或1027的經銷商資料，我們
所關心的資訊為經銷商號碼、負責人姓
名以及公司名稱。
Select distri_num, boss_name, company from
distributors where distri_num in
(1018,1014,1006,1001,1027)
14
識別NULL值
範例9：查詢尚未付款客戶的所有資料
Select * from orders where paid_date is null
範例10：查詢已付款客戶的所有資料
Select * from orders where paid_date is not null
15
練習3
練習3：請設計一SELECT敘述，查詢於
06/01/1996以後訂貨的所有訂單資料。
16
搜尋完全相等的字串
範例11：查詢「傑印電腦」經銷商的電
話號碼與地址
select phone, address from distributors
where company = "傑印電腦"
17
搜尋子字串
範例12：查詢某一公司的地址，只記得
該公司在台北市，而且電話號碼前兩碼
為27
Select company, phone, address from
distributors where phone like "0227*"
18
在WHERE子句中使用子字串
範例13：查詢電話區域碼為03所有經銷
商的公司名稱與電話號碼
Select company, phone from distributors
where left(phone, 2) = "03"
19
練習4
練習4：查詢位於台北市以外所有經銷商
的公司名稱、電話號碼以及地址資料。
20
練習5
練習5：查詢一客戶的電話號碼，但是只
記得該公司為某某科技，而且公司名稱
中有一個「美」字。
21
使用ORDER BY子句
範例14：請查詢所有產品的產品號碼、
供應商代碼、產品名稱以及單價資訊，
請依單價由小至大加以排序（Sorting）
Select prod_num, supp_code, description,
unit_price from products order by
unit_price
22
使用ORDER BY子句
範例15：請查詢所有點陣式印表機的產
品號碼、供應商代碼、產品名稱以及單
價資訊，請依供應商代碼由大至小
（Descending）加以排序
Select prod_num, supp_code, description,
unit_price from products where description
= "點陣式印表機" order by supp_code desc
23
練習6
練習6：查詢位於台北縣市、負責人姓李
或陳的經銷商資料，列出公司名稱、負
責人姓名以及所在縣市代碼。以縣市代
碼升冪排序
24
使用GROUP BY子句
 Syntax ：
group by column#1, column#2, …
[having expression]
distri_num from orders group by
distri_num
25
使用GROUP BY子句
範例16：查詢位於各供應商之供應產品
總金額。以金額升冪排序。
Select supp_code, sum(total_price) as
Tprice from items group by supp_code
order by sum(total_price)
26
使用HAVING子句
請查詢每一訂單的總金額大於500並依總
金額由小至大排序
 select order_num, sum(total_price) as
totalprice from items group by order_num
having sum(total_price) > 500 order by
sum(total_price)
27
使用算數運算式
範例17：林老闆擬將所有產品的價格調
高5%，但是在正式調漲之前，他希望能
看一下調漲後各產品的價位，並且以產
品單價由小至大排序。
Select prod_num, supp_code, description,
unit_price * 1.05 from products order by
unit_price
28
使用萬用字元
*字元:符合任何字元個數
– wh*  which, where, when …
 ?字元:符合任何單一字母的字元
 [charlist] :符合任何charlist中的單一字元
– C[ae]ll  Call 和Cell
 [!charlist] :符合任何不在charlist中的單一字元
– C[!ae]ll 不包含Call 和Cell
 C[a-e]ll  Call 和Cell
– [a-z], [A-Z]不可[z-a]或[Z-A]
#字元:符合任何單一數值的字元
– 1#3  112,123,133…
29
使用萬用字元
select * from suppliers where
supp_name like "[寧和]* "
select * from suppliers where
supp_name like "[!寧]* "
select * from products where
supp_code like "[A-Z]F[A-Z] "
select * from orders where order_num
like "1#3"
30
為虛擬欄位命名
範例18：由上一範例得知，在輸出結果
中，虛擬欄位是沒有欄位名稱的。為了
易於閱讀起見，請為該虛擬欄位加上欄
位名稱new_price。
Select prod_num, description, unit_price *
1.05 as new_price from products order by
unit_price
31
為虛擬欄位命名
範例19：修改範例18中的SQL敘述，輸
出標題分別為「產品號碼」、「產品名
稱」以及「產品新單價」，並且以產品
新單價由小至大排序
 select prod_num as 產品號碼, description
as 產品名稱, unit_price * 1.05 as 產品新單
價 from products order by unit_price * 1.05
32
搜尋單一長度的任意字串
範例20：請查詢供應代碼第一、第三位
分別為H與T所有供應商的資料
select * from suppliers where supp_code
like "H?T"
33
新增資料錄
 Syntax :
insert into table_name values (column#1
value, column#2 value, … )
insert into table_name defaults values
insert into table_name1 select column#1
from table_name2 where …..
34
新增資料錄
範例21：請在products表格中新增一筆資
料錄，其產品號碼為120、供應商代碼為
HHT、產品名稱為「新產品一」以及單價
為195
insert into products values (120, "HHT", "
新產品一",195)
35
新增資料錄
先複製distributors 為distributors1並修改
其資料內容並將distributors1之內容新增
到distributors
insert into distributors select * from
distributors1
只增加boss_name之資料到distributors
insert into distributors select boss_name
from distributors1
36
刪除資料錄
 Syntax :
delete [from] table_name
where column_name = value
請刪除經銷商1018的資料錄
delete from distributors where distri_num
=1018
37
修改資料錄的內容
 Syntax :
update table_name set col#1 = value1,
col#2 = value2, col#3 = value3, …
[where …..]
 Example :
 update products set unit_price =
unit_price*1.03
 update products set unit_price =
unit_price*1.03 where prod_num=310
38
修改資料錄的內容
範例22：請將經銷商1018的公司名稱改為
「新公司一」、電話號碼改為023334444
update distributors set company ="新公司一",
phone= "023334444" where distri_num = 1018
39
練習7
練習7：將供應商編號為‘FYC’之供應商
所供應之每一項產品的供應價打九五折
40
範例23： Parameters [Enter Price]
currency; select * from items where
total_price > [Enter Price]
41
範例24： Parameters [Order_number]
integer;
select * from items where order_num =
[Order_number]
42
範例25： Parameters [orderdate] datetime;
select * from orders where order_date >=
[orderdate]
43
範例26： Parameters [Supply_code] text;
select * from items where supp_code =
[Supply_code]
44
在一select 敘述中使用子查詢
範例27：請查詢與「負責人為張江悅的經
銷商」在同一縣市的所有經銷商的資料
select * from distributors where hsienshi_code
=(select hsienshi_code from distributors where
boss_name = "張江悅")
45
請列出和供應商代碼(supp_code)為WCC供應相
同產品(description) 之供應商代碼(supp_code)
及供應商名稱(supp_name)
SELECT products.supp_code, supp_name,
description from products inner join suppliers on
products.supp_code=suppliers.supp_code where
description = (select description from products
where supp_code="WCC")
46
練習8
請查詢所有訂單項單項價大於所屬訂單
最小單項總價兩倍的訂單項目資料。
47
練習9
請查詢每一經銷商的經銷商名稱與最早訂
貨日期
48
TRANSFORM
TRANSFORM aggfunction selectstatement
PIVOT pivotfield [IN (value1[,
vlaue2[, …]]]
49
範例28：請找出員工之各種請假之時數總合
TRANSFORM Sum(AbsHour)
SELECT Name
FROM 員工基本資料 INNER JOIN 員工請假資
料 ON 員工基本資料.EmpNo = 員工請假資
料.EmpNo
GROUP BY Name
PIVOT AbsType;
50
TRANSFORM Sum(items.total_price) AS
total_price之總計 SELECT orders.order_num
FROM (distributors INNER JOIN orders ON
distributors.distri_num = orders.distri_num)
INNER JOIN items ON orders.order_num =
items.order_num GROUP BY orders.order_num
PIVOT distributors.distri_num;
51
Union 特性
不同資料表或使用不同的方法擷取相同
格式的資料，希望將所選取的資料整合
在一起
輸出資料需具有相同的資料型態
輸出資料的欄位必須一致
Union兩邊的欄位輸出順序須一致
會自動移除重覆之資料
52
Union
Syntax :
SELECT column_1, …., column_n
FROM table_1, … , table_n
WHERE column_name comparsion_op value
[GROUP BY ...][HAVING][ORDER BY]
UNION [ALL]
SELECT column_1, …., column_n
FROM table_1, … , table_n
WHERE column_name comparsion_op value
[GROUP BY ...][HAVING][ORDER BY]
53
使用union運算元
範例29：請查詢單價大於25元或是已訂
貨項目中數量小於2的產品資料，列出產品
號碼與供應商代碼。
Select prod_num, supp_code from products
Where unit_price <25 union Select prod_num,
supp_code from items
Where quantity < 2
54
union
範例30：
select company, address, phone from
distributors
select prod_num,supp_code, total_price from
items
select company, address, phone from
distributors union select prod_num,supp_code,
total_price from items
55
Embedding Functions
56
LEN函數
範例31：請計算經銷商1001地址的的字
串長度
select address, len(address) as 長度 from
distributors where distri_num = 1001
57
常用的時間函數
 day函數
 month函數
 year函數
 now函數
 date函數
 weekday函數
58
day函數
擷取一日期的日數
select order_num,day(order_date) from
orders where order_num =102
select order_num,day(order_date) from
orders where order_num like "10#“
59
month函數
擷取一日期的月數
 select order_num,month(order_date) from
orders where order_num =102
select order_num,month(order_date) from
orders where order_num like "10#"
請查詢每一訂單號碼的下單月份
select order_num, month(paid_date) as month
from orders
60
year函數
請擷取一日期的年份
select order_num,year(order_date) from
orders where order_num =102
61
weekday函數
select order_num, weekday(order_date)
from orders where order_num =102
62
練習10
李老板希望能得知與本月相同月份訂貨
的訂單號碼與訂貨日期。
63
常用的集總函數
count函數
sum函數
avg函數
max函數
min函數
STDEV, STDEVP函數
VAR, VARP函數
64
count
 請查詢本公司總共銷售多少種產品
select count(*) from products
select count(*) from distributors
select count(*) from suppliers
65
sum
請查詢HHT供應商已訂貨的總金額
select sum(total_price) from items where
supp_code="HHT"
66
avg
請查詢所有供應商所供應貨品的平均單價
select supp_code as 供應商代碼,
avg(unit_price) as 平均單價 from products
group by supp_code
67
max
請查詢每一供應商產品的最高單價
select supp_code, max(unit_price) from
products group by supp_code
68
min
請查詢每一經銷商的最早訂貨日期
select distri_num, min(order_date) from
orders group by distri_num
69
練習11
請設計一敘述，列出超過一筆訂單的所
有經銷商的經商號碼與訂單總數
70
Outline
 Join
 Inner Join
 Outer Join
Left Join and Right Join
Union
71
Join的特性
兩個以上資料來源的彙整與連結查詢
需設定資料來源的連結條件(Join condition)
使用Select指令的join 關鍵字進行資料來源
的連結, 同時使用了on設定了連結控制條件
一般連結會產生的結果為多欄位之Result
Set
72
Join的類型
內部合併（Inner Join）
外部合併（Outer Join）
73
Inner Join
連結與比對兩個資料來源之間的連結控制指
定的欄位內容, 是否兩邊同時吻合
不能在連結控制條件中, 使用具有 NULL 值
的欄位進行比對
可以加上篩選條件 On 進行記錄的篩選
Syntax : Select <output fileds> From <tables>
Inner Join <tables> On <conditions>
74
範例 32
建立一查詢, 可以讓我們看到所有訂單之編
號、訂貨日期、經銷商之編號、經銷商之
公司名稱以及付款日期
75
範例32解答
SELECT orders.order_num, orders.order_date,
distributors.distri_num, distributors.company,
orders.paid_date FROM orders INNER JOIN
distributors ON orders.distri_num =
distributors.distri_num;
SELECT orders.order_num, orders.order_date,
distributors.distri_num, distributors.company,
orders.paid_date FROM orders, distributors where
orders.distri_num = distributors.distri_num; 76
練習12
請查詢每一經銷商所有的供應資料，列
出經銷商號碼、公司名稱、電話號碼、
訂單編號，訂貨日期並以經銷商號碼由
小至大排序。
77
在select 敘述中使用多個資料表
請查詢1004經銷商所有訂單的資料，列出經銷商號碼、
訂單號碼、項目號碼以及單項總價。
SELECT distributors.distri_num, orders.order_num,
items.item_num, items.total_price FROM distributors
INNER JOIN (orders INNER JOIN items ON
orders.order_num = items.order_num) ON
distributors.distri_num = orders.distri_num and
distributors.distri_num =1004;
select distributors.distri_num, orders.order_num,
item_num, total_price from distributors, orders, items
where distributors.distri_num=orders.distri_num
and orders.order_num = items.order_num and
distributors.distri_num =1004
78
Outer Join
79
Outer Join
可以指定某一個資料來源為主, 連結至另一
個資料來源時, 若兩邊記錄無法同時吻合時,
可以將主要的資料來源之不對應連結的記錄
完全呈現,對應不到的連結資料之欄位經均以
NULL 顯示
Left Outer
Right Outer
80
範例33
建立一查詢, 可以讓我們看到所有訂單之
編號、訂貨日期、貨品編號、數量以及總
價(檢查不合理之訂單)
 select orders.order_num, orders.order_date,
items.prod_num, items.quantity,
items.total_price from orders left join items
on orders.order_num=items.order_num
81
範例34
請查詢每一經銷商的下單明細（要包含
尚未下單的經銷商），列出經銷商號碼、
負責人姓名以及訂單號碼
select distributors.distri_num, boss_name,
order_num from distributors left outer join
orders on distributors.distri_num =
orders.distri_num
82
練習13
請查詢每一供應商已售出貨品的總金
額，列出供應商公司名稱與售出貨品的
總金額。(並查出是否有供應商沒有出售
任何貨品)
83
Self Join
84
Self Join
同一資料表可以和自己做JOIN
常應用在同一資料表內不同資料間對同
一欄位作比較
85
一表格與自己作合併
請查詢供應相同產品的供應商代碼，列出產品
名稱與供應商代碼，以成對的方式列出。
SELECT products.supp_code,
products_1.supp_code, products_1.description FROM
products INNER JOIN products products_1 ON
products.supp_code <> products_1.supp_code and
products.description = products_1.description
GROUP BY products.supp_code,
products_1.supp_code, products_1.description;
86
練習 14
請查詢價格低於300元之相同產品(兩個以
上)的產品名稱與產品價格。
87
在一select 敘述中使用子查詢
請查詢與「負責人為張江悅的經銷商」在同
一縣市的所有經銷商的資料
select * from distributors where hsienshi_code =
(select hsienshi_code from distributors where
boss_name = "張江悅")
88
練習15
請查詢所有訂單項單項價大於所屬訂單最
小單項總價兩倍的訂單項目資料。
89
練習16
請查詢每一經銷商的經銷商名稱與最早訂
貨日期
90