iT邦幫忙

第 11 屆 iThome 鐵人賽

DAY 2
1
Software Development

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

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

  • 分享至 

  • 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
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言