一、回顧：第二堂 情境練習題

題目

從薪水(salary)低的人開始往薪水高的員工排列，列出所有部門編號(dept_id)=5 的 員工姓名(emp_name)、雇用日期(hire_date)、薪水(salary)

查詢結果

參考SQL

SELECT employees.emp_name,
	   employees.hire_date,
	   employees.salary
FROM employees
WHERE employees.dept_id = 5
ORDER BY employees.salary

複習內容

思考順序1：資料來源
員工資料表(employees)

FROM employees

思考順序2：過濾需要的資料
所有部門編號(dept_id)=5 的 員工

WHERE employees.dept_id = 5

思考順序3：需要的欄位
員工姓名(emp_name)、雇用日期(hire_date)、薪水(salary)

SELECT employees.emp_name,
	   employees.hire_date,
	   employees.salary

思考順序4：用什麼欄位決定資料的呈現順序
從薪水(salary)低的人開始往薪水高的員工排列

ORDER BY employees.salary

二、準備資料

DB Fiddle:
https://www.db-fiddle.com/

與第二堂相同，將下方的SQL輸入至DB Fiddle 左方後，按下上方的「Run」建立資料：

MySQL：

/* 家庭成員資料表 範例資料 */
CREATE TABLE IF NOT EXISTS `Family` (
  `FamilyId` varchar(100) NOT NULL,
  `FamilyName` varchar(10) DEFAULT NULL,
  `FamilySex` varchar(10) DEFAULT NULL,
  `BirthDate` datetime DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `Family` (`FamilyId`, `FamilyName`, `FamilySex`, `BirthDate`, `PhoneNumber`) VALUES
	('34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '泰肝', '女生', '2007-10-11 00:00:00', ''),
	('3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '泰熱', '男生', '2001-12-10 00:00:00', '0934567890'),
	('91b18f1f-4ef8-4066-97c4-28daea585db5', '泰胖', '女生', '2003-05-13 00:00:00', ''),
	('91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '泰瘦', '女生', '1985-07-10 00:00:00', '0944623456'),
	('bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '泰冷', '男生', '1995-01-23 00:00:00', '0977654258'),
	('de8cb5db-2061-4d35-a662-ba5f528fadba', '泰賢', '男生', '1977-02-03 00:00:00', '0944589456');

自備SQL Server的環境：

CREATE TABLE [dbo].[Family](
	[FamilyId] [nvarchar](100) NOT NULL,
	[FamilyName] [nvarchar](10) NULL,
	[FamilySex] [nvarchar](10) NULL,
	[BirthDate] [datetime] NULL,
	[PhoneNumber] [nvarchar](20) NULL,
 CONSTRAINT [PK_Family] PRIMARY KEY CLUSTERED 
(
	[FamilyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'34bf1b6f-191d-40e9-9a8c-3c282e6a700d', N'泰肝', N'女生', CAST(0x000099C400000000 AS DateTime), NULL)
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'3ad93ba4-c799-4a32-ac2e-8abc74dd6375', N'泰熱', N'男生', CAST(0x0000917100000000 AS DateTime), N'0934567890')
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'91b18f1f-4ef8-4066-97c4-28daea585db5', N'泰胖', N'女生', CAST(0x0000937800000000 AS DateTime), NULL)
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'91dcde4b-10b3-421e-ab8e-bb6bc23b4350', N'泰瘦', N'女生', CAST(0x00007A0400000000 AS DateTime), N'0944623456')
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', N'泰冷', N'男生', CAST(0x000087A000000000 AS DateTime), N'0977654258')
INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) VALUES (N'de8cb5db-2061-4d35-a662-ba5f528fadba', N'泰賢', N'男生', CAST(0x00006DFD00000000 AS DateTime), N'0944589456')

三、語法結構：分類並計算欄位的SELECT

SELECT 沒有被計算的欄位1, 沒有被計算的欄位2, ...
       拿來計算的欄位1, 拿來計算的欄位2, ...
FROM 資料來源
WHERE 計算前過濾需要的資料條件
GROUP BY 沒有被計算的欄位1, 沒有被計算的欄位2
HAVING 計算後過濾需要的資料條件
ORDER BY 用什麼欄位決定資料的呈現順序

四、問題情境

問題1. 計算家庭的男女生的人數

實作

SELECT FamilySex AS "性別",
       COUNT(FamilyName) AS "人數"
FROM Family
GROUP BY FamilySex

查詢結果

問題2. 分別找出家庭當中最年長的男生與女生成員的出生日期

實作

SELECT FamilySex "性別" ,
       MAX(BirthDate) "最年長的出生日期"
FROM Family
GROUP BY FamilySex

查詢結果

問題3. 分別算出男生與女生在2001年以前出生的人數

實作

SELECT FamilySex "性別",
       COUNT(BirthDate) "2001年以前的出生人數"
FROM Family
WHERE BirthDate < '2001-01-01'
GROUP BY FamilySex

查詢結果

問題4. 分別計算所有男生與女生的平均出生年，過濾出平均出生年大於1995的性別

實作

SELECT FamilySex "性別",
       AVG(Year(BirthDate)) "平均出生年"
FROM Family
GROUP BY FamilySex
HAVING AVG(Year(BirthDate)) > 1995

查詢結果

五、從問題中理解SQL語法

理解順序1:統計前的分類基準:GROUP BY

在統計之前，必須先將欄位做好分類，讓SQL對相同分類的資料進行統計。

以第一個問題為例：

1. 將所有家庭成員的資料用「性別」欄位進行分類，分成「男生」與「女生」

GROUP BY FamilySex

2. 接著將「男生」的資料與「女生」的資料分別進行統計

SELECT FamilySex AS "性別",
       COUNT(FamilyName) AS "人數"

理解順序2:統計前的資料過濾 WHERE 和 統計後的資料過濾HAVING

問題3 要求「只要統計2001年以前出生的人」
統計前的資料過濾條件要寫在 WHERE

WHERE BirthDate < '2001-01-01'

問題4 要求「統計完所有男生與女生的平均後，過濾出平均出生年大於1995的性別」
統計後的欄位過濾條件要寫在 HAVING

HAVING AVG(Year(BirthDate)) > 1995

理解順序3: 被統計的欄位(SQL Aggregate Functions)

在 SELECT 查詢，我們可以針對不同欄位進行統計處理，包含「計算出現次數 (COUNT)」、「總和 (SUM)」、「平均 (AVG)」「最大 (MAX)」、「最小 (MIN)」，這類的函式我們稱之為「彙總函式(SQL Aggregate Functions)」

衍生閱讀：
https://www.javatpoint.com/dbms-sql-aggregate-function

六、觀念延伸閱讀：運算式與函式

MySQL 超新手入門（4）運算式與函式
http://www.codedata.com.tw/database/mysql-tutorial-4-expression-function/

七、相關情境練習

作業跟第二堂相同，使用employees資料表
https://www.tutorialrepublic.com/codelab.php?topic=sql&file=select-all

題目

透過員工資料表的「部門欄位(dept_id)」與「員工薪水(salary)」

統計各部門的員工薪資總和，依照各部門薪水總和最高到薪水總和最低，依序列出部門員工薪資總和大於10000的部門ID、部門薪資總和。沒有部門編號的員工(dept_id <> 'null') 不包含在資料的統計結果內

八、第三堂總結

SELECT 查詢的執行先後順序

1. FROM 資料來源
2. ON
3. JOIN
4. WHERE 計算前的資料過濾
5. GROUP BY 統計前的分類基準
6. WITH CUBE 或 WITH ROLLUP
7. HAVING 計算後的資料過濾
8. WINDOW functions
9. SELECT 需要的欄位
10. DISTINCT
11. ORDER BY 用什麼欄位決定資料的呈現順序
12. TOP (LIMIT / OFFSET)