第八章 利用SELECT查詢資料.

Presentation on theme: "第八章 利用SELECT查詢資料."— Presentation transcript:

1 第八章 利用SELECT查詢資料

2 章節概覽 SELECT的基本使用 WHERE指令的使用 ORDER BY指令

3 8-1-1 SELECT指令的基本語法結構 SELECT 欄位列表 [INTO 新資料表名稱] FROM 資料表 [WHERE 搜尋條件]
[GROUP BY 群組欄位] [HAVING 搜尋群組結果] [ORDER BY 排序欄位 [ASC | DESC]]

4 8-1-2 撰寫一個簡單的SELECT指令 範例8-1 USE Northwind SELECT * FROM ORDERS

5 使用欄位列表 範例8-2 SELECT OrderID,CustomerID,OrderDate FROM ORDERS

6 使用中文欄位列表 SELECT OrderID as 訂單編號,CustomerID as 顧客編號,
範例8-3 SELECT OrderID as 訂單編號,CustomerID as 顧客編號, OrderDate as '訂單日期' FROM ORDERS

7 另一種欄位列表的表示 SELECT '訂單編號' = OrderID ,'顧客編號' = CustomerID,
範例8-4 SELECT '訂單編號' = OrderID ,'顧客編號' = CustomerID, '訂單日期' = OrderDate FROM ORDERS

8 欄位後接運算式 SELECT ProductName as 產品名稱,UnitPrice as 單價 FROM Products
範例8-5 SELECT ProductName as 產品名稱,UnitPrice as 單價 FROM Products 範例8-6 打八折後的結果 SELECT ProductName as 產品名稱,UnitPrice as 單價 , UnitPrice * 0.8 as 打八折後 FROM Products

9 8-1-3 資料表的別名 資料表名稱.欄位名稱 SELECT Products.ProductName as 產品名稱 from Products 範例8-7 SELECT d.OrderID,d.ProductID FROM [Order Details] as d

10 8-1-4 取回不重覆的資料 (DISTINCT)1/2
SELECT TitleOfCourtesy FROM Employees 範例8-8 SELECT DISTINCT TitleOfCourtesy FROM Employees

11 取回不重覆的資料 (DISTINCT)2/2 SELECT DISTINCT 居住地 FROM MEMBERS

12 8-2 WHERE指令的使用 8-2-1 WHERE條件指令
範例8-9 SELECT * FROM Orders WHERE OrderID = 10248 範例8-10 SELECT * FROM Employees WHERE TitleOfCourtesy <> 'Mr.'

13 8-2-2 使用LIKE做模糊查詢(1/2) SELECT * FROM EMPLOYEES WHERE
範例8-11 SELECT * FROM EMPLOYEES WHERE LastName LIKE 'D%'

14 使用LIKE做模糊查詢(2/2) SELECT * FROM EMPLOYEES WHERE TITLE LIKE '%si%'
範例8-12 SELECT * FROM EMPLOYEES WHERE TITLE LIKE '%si%'

15 8-2-3 使用BETWEEN…AND查詢區間資料(1/3)
SELECT * FROM 學生成績 WHERE 數學成績 >= 60 AND 數學成績 <=70 範例8-13 SELECT * FROM 學生成績 WHERE 數學成績 BETWEEN 60 AND 70

16 使用BETWEEN…AND查詢區間資料(2/3)
範例8-14 SELECT * FROM [Order Details] WHERE Quantity BETWEEN 20 AND 30

17 使用BETWEEN…AND查詢區間資料(3/3)
範例8-15 SELECT * FROM Customers WHERE CustomerID BETWEEN 'A' AND 'D'

18 8-2-4 IN條件指令 SELECT * FROM EMPLOYEES WHERE
範例8-16 SELECT * FROM EMPLOYEES WHERE TitleOfCourtesy = 'Ms.' OR TitleOfCourtesy = 'Dr.' 範例8-17 SELECT * FROM EMPLOYEES WHERE TitleOfCourtesy IN( 'Ms.','Dr.')

19 8-3 ORDER BY指令(1/3) 8-3-1 利用ORDER BY將欄位排序 利用ORDER BY將欄位排序
範例8-18 利用ORDER BY將欄位排序 SELECT * FROM [Order Details] WHERE OrderID = 10248 ORDER BY Quantity

20 ORDER BY指令(2/3) 由大排到小 SELECT * FROM [Order Details]
範例8-19 由大排到小 SELECT * FROM [Order Details] WHERE OrderID = 10248 ORDER BY Quantity DESC

21 ORDER BY指令(3/3) 多欄排序 SELECT * FROM [Order Details]
範例8-20 , 8-21 多欄排序 SELECT * FROM [Order Details] ORDER BY OrderID,ProductID

22 8-3-2 取回前幾筆資料(TOP N) SELECT TOP 5 * FROM Products UnitPrice ORDER
範例8-22 , 8-23 SELECT TOP 5 * FROM Products UnitPrice ORDER BY UnitPrice DESC

23 8-3-3 WITH TIES的使用 SELECT TOP 15 WITH TIES * FROM Products
範例8-24 , 8-25 SELECT TOP 15 WITH TIES * FROM Products UnitPrice ORDER BY UnitPrice DESC

24 學習成果回顧 利用SELECT將資料表的內容查詢出來 利用WHERE條件查詢指令，將想要查詢的列查詢 出來
利用BETWEEN…AND以及IN指令，簡化SELECT指 令的撰寫 ORDER BY可針對一個欄位，或兩個欄位以上的資 料排序 利用TOP N指令將前N筆的資料取回