iT邦幫忙

第 11 屆 iThome 鐵人賽
DAY 2
1
Software Development

從問題理解與活用SQL語法系列 第 2 篇

第二堂：家事管理 - 認識查詢 (FROM→WHERE→SELECT→ORDER BY)

11th鐵人賽
3268 瀏覽

  • 分享至 

  • xImage
    •  

準備資料：初嘗CREATE TABLE、INSERT INTO

一、建立家事管理資料表到 SQL Fiddle

A.實作過程

網址：
https://www.db-fiddle.com/

  1. 進到SQL Fiddle網址
  2. 選擇資料庫版本為MySQL 5.6
  3. 於左方輸入下列SQL。輸入完成後點擊左上的 「Run」，建立範例資料
    https://ithelp.ithome.com.tw/upload/images/20190922/20120331X0W8pg5lms.png

MySQL DDL：

/* 家庭成員資料表 範例資料 */
CREATE TABLE `Family` (
  `FamilyId` varchar(100) NOT NULL PRIMARY KEY,
  `FamilyName` varchar(10) DEFAULT NULL,
  `FamilySex` varchar(10) DEFAULT NULL,
  `BirthDate` datetime DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL
)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的環境，可使用下面的SQL 建立並產生資料

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 INTO [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')
  1. 於右方輸入下方SQL語句
SELECT *
FROM Family
  1. 點擊Run，得到剛才建立的家庭成員的所有資料
    https://ithelp.ithome.com.tw/upload/images/20190922/20120331hTarPcBsk3.png

B.講解

  1. 我們新增一個資料表，叫做「Family」，裡面包含五個欄位
  • FamilyId：家庭成員的代號，資料型態為長度100的nvarchar，不可為空
  • FamilyName：家庭成員的姓名，資料型態為長度10的nvarchar，可為空值
  • FamilySex： 家庭成員的性別，資料型態為長度10的nvarchar，可為空值
  • BirthDate：家庭成員的生日，資料型態為DateTime(日期)，可為空值
  • PhoneNumber：家庭成員的電話，資料型態為長度20的nvarchar，可為空值

MySQL：

CREATE TABLE `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;

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,

  1. 由於代號、姓名、性別、電話不是非純數字的資料，所以資料型態選擇用字串類型varchar，字串的資料類型主要分為「可變動長度」、「固定長度」，延伸閱讀可參考下方兩個網址
    http://lesliechang-blog.logdown.com/posts/690954-690954-char-varchar-nchar-nvarchar
    https://dotblogs.com.tw/henryli/archive/2014/05/27/145277.aspx

  2. 每張資料表需要一個「主鍵」，清楚識別每一筆資料是不同的資料。

MySQL 在Create Table結束前設定，或設定在欄位後方

CREATE TABLE `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;

SQL Server 的 CREATE TABLE 後方的SQL，則是將Family 的FamilyId 透過Constraint的語法，設定為主鍵

 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]

或者，直接在欄位後面設定主鍵，得到簡化後的CREATE TABLE：

CREATE TABLE [dbo].[Family](
	[FamilyId] [nvarchar](100) NOT NULL　PRIMARY KEY,
	[FamilyName] [nvarchar](10) NULL,
	[FamilySex] [nvarchar](10) NULL,
	[BirthDate] [datetime] NULL,
	[PhoneNumber] [nvarchar](20) NULL
)
  1. 最後，我們透過Insert Into語法，依序加入六個家庭成員的「FamilyId」、「FamilySex」、「BirthDate」、「PhoneNumber」資料到剛才建立的Family 資料表

MySQL：

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：

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

課程備忘錄

第二堂課大家只要知道，CREATE TABLE 用來建立資料表，INSERT INTO 用來新增資料到資料表即可
其餘的觀念介紹，等熟悉SELECT 語法之後我們再回來討論。

CREATE TABLE 在各大資料庫的語法:
https://www.javatpoint.com/sql-create-table

Insert INTO 在各大資料庫的語法
https://www.javatpoint.com/sql-insert

二、SELECT 語法結構

SELECT 需要的欄位  
FROM 資料的來源
WHERE 過濾所需資料的篩選條件
ORDER BY 用什麼欄位決定資料的呈現順序 ASC 升冪/ DESC 降冪

三、從問題理解SELECT 查詢的執行先後順序

問題1.列出所有的家庭成員欄位資料

A.實作結果

SELECT *
FROM Family;
FamilyId FamilyName FamilySex BirthDate PhoneNumber
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

B.講解

思考順序1：資料來源
我要哪張表的資料？家庭成員表Family

FROM　Family

思考順序2：需要的欄位
我要哪些欄位？Family資料表的所有的欄位

SELECT  Family.*

因為只有查詢一張表，所以欄位的部分可以不用指定表格的名稱

SELECT  *

問題2.列出所有的家庭成員的「姓名」、「性別」、「出生年月日」、「手機號碼」

A.實作結果

SELECT FamilyName,
       FamilySex,
       BirthDate,
       PhoneNumber
FROM Family;
FamilyName FamilySex BirthDate PhoneNumber
泰肝 女生 2007-10-11 00:00:00
泰熱 男生 2001-12-10 00:00:00 0934567890
泰胖 女生 2003-05-13 00:00:00
泰瘦 女生 1985-07-10 00:00:00 0944623456
泰冷 男生 1995-01-23 00:00:00 0977654258
泰賢 男生 1977-02-03 00:00:00 0944589456

B.講解

思考順序1：資料來源
我要哪張表的資料？家庭成員表Family

FROM Family

思考順序2：需要的欄位
我要哪些欄位？家庭成員表Family的成員姓名、成員性別、生日、電話

SELECT Family.FamilyName,
       Family.FamilySex,
       Family.BirthDate,
       Family.PhoneNumber

因為只有查詢一張表，所以欄位的部分可以不用指定表格的名稱 (與問題一相同)

問題3.列出家中所有的男性成員的所有姓名

A.實作結果

SELECT FamilyName
FROM Family
WHERE FamilySex = '男生';
FamilyName
泰熱
泰冷
泰賢

B.講解

思考順序1：資料來源
我要哪張表的資料？家庭成員表Family

FROM Family

思考順序2：過濾需要的資料
我要那些資料？性別為男生的成員

WHERE FamilySex = N'男生'

由於網頁編碼的關係，所以在SQL Fiddle 查詢中文時，字串前面要加上N，指定字串「男生」為Unicode編碼格式

思考順序3：需要的欄位
我要哪些欄位？姓名

SELECT FamilyName

問題4. 依照出生年月日，由年紀最大到年紀最小，依序列出所有男生成員的「姓名」、「出生年月日」

A.實作結果

SELECT FamilyName,
       BirthDate
FROM Family
WHERE FamilySex = '男生'
ORDER BY BirthDate DESC;
FamilyName BirthDate
泰熱 2001-12-10 00:00:00
泰冷 1995-01-23 00:00:00
泰賢 1977-02-03 00:00:00

B.講解

思考順序1：資料來源
我要哪張表的資料？家庭成員表Family

FROM Family

思考順序2：過濾需要的資料
我要那些資料？性別為男生的成員

WHERE FamilySex = N'男生'

由於網頁編碼的關係，所以在SQL Fiddle 查詢中文時，字串前面要加上N，指定字串「男生」為Unicode編碼格式

思考順序3：需要的欄位
我要哪些欄位？姓名、生日

SELECT FamilyName,
       BirthDate

思考順序4：用什麼欄位決定資料的呈現順序
依照生日，由年長→年幼排序(DESC 降冪)

ORDER BY BirthDate DESC

如果是升冪，則使用ASC

ORDER BY BirthDate ASC

或者直接省略不寫ASC

ORDER BY BirthDate

四、SELECT 的欄位、表格可以另外取別名

SELECT 原本的欄位名稱 AS 替欄位取的別名
FROM 原本的表格名稱 AS 替表格取得別名

範例：sql

SELECT FamilyName AS 姓名,
       FamilySex AS 性別
FROM Family AS 家庭成員

https://ithelp.ithome.com.tw/upload/images/20190930/20120331zPDXlX5RFv.png

五、觀念延伸閱讀

MySQL 超新手入門（3）SELECT 基礎查詢
http://www.codedata.com.tw/database/mysql-tutorial-basic-query/

[SQL] SQL指令重點整理(三)：SQL限制(SQL Constraint)之介紹
https://peimei0808.pixnet.net/blog/post/155927679-%5Bsql%5D-sql%E6%8C%87%E4%BB%A4%E9%87%8D%E9%BB%9E%E6%95%B4%E7%90%86(%E4%B8%89)%EF%BC%9Asql%E9%99%90%E5%88%B6(sql-constraint)

六、相關情境練習

練習題目:員工資料表
https://www.tutorialrepublic.com/codelab.php?topic=sql&file=select-all

操作說明：
https://ithelp.ithome.com.tw/upload/images/20190918/20120331vwqnaMqWjz.png

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

https://ithelp.ithome.com.tw/upload/images/20190918/20120331rBLrBhwgvP.png

七、第二堂總結

建立表格與新增資料的流程

  • 建立表格：CREATE TABLE
  • 新增資料：Insert INTO

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)


上一篇
第一堂：泰D 在鐵人賽的 SQL 課程，上線囉 (家事管理介紹)
下一篇
第三堂：家事管理 - 分類後統計資料(GROUP BY → HAVING→SELECT、Aggregrate Function)
系列文
從問題理解與活用SQL語法 共 30 篇
  1. 26
    第二十六堂：英文單字測驗程式 - 學生帳號資料管理(5) - 修改學生與刪除學生 (認識實體)
  2. 27
    第二十七堂：英文單字測驗程式 - 學生帳號資料管理(6) - 物件實體操作資料 (ORM、Entity Framework)
  3. 28
    第二十八堂：英文單字測驗程式 - 單字測驗 - 亂數取得單字 (LIMIT、TOP、綜合練習)
  4. 29
    第二十九堂：英文單字測驗程式 - 測驗主明細紀錄與正確率統計 (綜合練習)
  5. 30
    第三十堂：師父引進門，分手在個人 (專案開源+從與SQL的分分合合到現在開課誤人子弟)
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22200 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙