iT邦幫忙

第 11 屆 iThome 鐵人賽

DAY 9
1
Software Development

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

第九堂:家事管理 - 使用INSERT INTO產生隨機排班資料 (Window Function-Ranking Function、UUID()、RAND() )

一、回顧:從每張表的CREATE TABLE 活用常見的欄位限制(主鍵、外來鍵、索引、唯一)

(一)主鍵 (PRIMARY KEY)

  1. 適用情境:用來明確識別在資料當中的每一筆資料,是不同的資料。每筆資料的主鍵不能重複
  2. 主鍵常見的使用型態:
  • int
  • GUID/UUID

例如:每一個排班紀錄都有一個不重複的流水號(CleanScheduleId)

(二) 外來鍵 (FOREIGN KEY)

  1. 適用情境:用來存放來別張資料表的資料主鍵,透過JOIN合併資料
  2. 常見的外來鍵參考對象的刪除/更新規則:
  • 連鎖反應做法 CASCADE:
    來源的資料被刪除了,有關聯來源的資料也會一併刪除。
  • 限制性做法 RESTRICT:
    有關聯來源的資料如果沒有被刪除,來源的資料無法刪除
  • 虛值化做法 SET NULL:
    有關聯來源的資料如果沒有被刪除,來源的資料刪除後,有關聯來源的資料值將被修改為NULL

例如:每一筆實際做家事紀錄(CleanRecord) 都有在排班表(CleanScheduleId)對應的排班流水號 (CleanScheduleId)

(三) 索引 (INDEX)

  1. 適用情境:設定在常用來當作查詢條件的欄位
  2. 目的:藉由資料庫的演算法提高查詢速度
  3. 成本:占用額怪的磁碟空間

例如:家事排班表的做家事日期欄位(CleanDateTime),經常被拿來當作查詢統計的條件

題目

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

根據「Orders」資料表裡面的資料,撰寫Orders的Create Table SQL,並且在每個欄位加入適當的欄位限制。

參考答案

CREATE TABLE `Orders` (
	`OrderID` int AUTO_INCREMENT PRIMARY KEY,
    `CustomerID` VARCHAR(50),
    `EmployeeID` VARCHAR(50),
    `OrderDate` DateTime,
    CONSTRAINT `FK_Orders_Customers` FOREIGN KEY (`CustomerID`) REFERENCES `Customers` (`CustomerID`),
    CONSTRAINT `FK_Orders_Employees` FOREIGN KEY (`EmployeeID`) REFERENCES `Employees` (`EmployeeID`),
    INDEX `OrderDate` (`OrderDate`)
)

二、第九堂範例:產生隨機排班資料到排班表

需求

隨機產生某個日期的家事排班資料

實作結果

https://ithelp.ithome.com.tw/upload/images/20190925/20120331Azo3S0bjNE.png

1.不支援Window Function:MySQL 5.7以下 MariaDB 10.1以下

SET @rownum = 0;
SELECT UUID() AS CleanScheduleId,
		 '2019-09-25' AS CleanDateTime,
       FamilyData.FamilyId,
       @rownum:= @rownum + 1   AS CleanItem
FROM (SELECT Family.FamilyId
	  FROM Family
	  ORDER BY RAND()) AS FamilyData

2. 支援Window Function: MySQL 8.0 以上、 MariaDB 10.2

INSERT INTO CleanSchedule
SELECT UUID() AS CleanScheduleId,
	   '2019-09-25' AS CleanDateTime,
	   FamilyId,
	   Row_Number() OVER(ORDER BY FamilyID) AS CleanItem
FROM Family
ORDER BY RAND();

3. SQL Server 2012 以上

INSERT INTO CleanSchedule
SELECT NEWID() AS CleanScheduleId,
	   DATEADD(DAY, 0, '2019-08-25') AS CleanDateTime,
       FamilyID AS FamilyID,
	   ROW_NUMBER() OVER(ORDER BY NEWID()) - 1 AS CleanItem
FROM Family

三、認識 INSERT INTO 語法

  1. 新增指定值到指定資料表的指定欄位
INSERT INTO 指定的資料表名稱 (欄位1, 欄位2, 欄位3, ...)
VALUES (欄位1的值, 欄位2的值, 欄位3的值, ...); 

2.將查詢的結果新增到指定資料表

INSERT INTO table2 (欄位1, 欄位2, 欄位3, ...)
SELECT 欄位1的值, 欄位2的值, 欄位3的值, ...
FROM table1
WHERE condition; 

四、不使用Window Function的思維

  1. 將 SELECT 的資料新增到某個資料表:Insert INTO SELECT
INSERT INTO CleanSchedule
SELECT ...
  1. 排序亂數,讓每次的FamilyId前後順序都不一樣:Order By + RAND() 或 Order by + UUID()

SQL Server 是產生GUID的 NEWID(),MySQL是產生UUID的 UUID()
[解釋] uuid 與 guid 的差異
http://blog.caesarchi.com/2014/03/06/-e8-a7-a3-e9-87-8b-uuid--e8-88-87-guid--e7-9a-84-e5-b7-ae-e7-95-b0/

FROM (SELECT Family.FamilyId
	  FROM Family
	  ORDER BY RAND()) AS FamilyData
  1. 第一欄使用UUID()產生家事排班編號,並將剛才取得打亂的成員資料給予1~6的家事項目編號
SELECT UUID(),
		 '2019-09-25' AS CleanDate,
       FamilyData.FamilyId,
       @rownum:= @rownum + 1   AS CleanItem

https://ithelp.ithome.com.tw/upload/images/20190925/201203314ztUsodN1P.png

五、使用Window Function 的思維

從Family取得所有的家庭成員資料後,接著使用Row_Number() OVER(ORDER BY RAND()) 子句,對每個家庭成員給予亂數的家事分工項目。

SELECT UUID() AS CleanScheduleId,
	   '2019-09-25' AS CleanDateTime,
	   FamilyId,
	   Row_Number() OVER(ORDER BY RAND()) AS CleanItem
FROM Family

https://ithelp.ithome.com.tw/upload/images/20190925/20120331O0Paukb1V6.png

六、 延伸閱讀:認識Window Function

(一) Window Function 的語法結構

<function> OVER (        [PARTITION BY clause]
                         [ORDER BY clause]
                         [ROWS or RANGE clause])

(二) Window Function 的用途

在資料庫中,「分析函式」是計算一組資料列匯總值的函式。分析函式會對一組輸入資料列計算函式來傳回每個資料列的單一值,而匯總函式則是會傳回一組資料列的單一匯總值,這是兩者之間的差異。

分析函式可以透過簡要的方式來表示複雜的分析運算作業,是一種功能十分強大的機制,而且可以提供有效率的評估方式,若以其他方式執行評估,會涉及耗費大量資源的 Self-JOIN (自我連結),或者必須在 SQL 查詢之外進行運算。

分析函式在 SQL 標準及某些商用資料庫中又稱為「(分析) 窗型函式」。這是因為分析函式會對一組資料列進行評估,這稱為 window 或 window frame。 在其他某些資料庫中,它們可能稱為「線上分析處理」(OLAP) 函式。

七、 相關情境練習

使用資料表:

課堂八的四張家事管理資料表

https://ithelp.ithome.com.tw/upload/images/20190925/20120331zGLPzbAesy.png

題目:

使用Insert Into SELECT 產生實際做家事的資料

範本資料:

/* 家庭成員資料表 */
-- 傾印  表格 ithome2019_familydb.family 結構
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;


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');
    
/* 家事項目清單 */
CREATE TABLE `CleanItemList` (
  `CleanItem` varchar(50) NOT NULL,
  `ItemName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`CleanItem`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `CleanItemList` (`CleanItem`, `ItemName`) VALUES
	('0', '打掃'),
	('1', '拖地'),
	('2', '洗碗'),
	('3', '倒垃圾');

/* 家事排班一覽表 */
CREATE TABLE `CleanSchedule` (
  `CleanScheduleId` varchar(100) NOT NULL,
  `CleanDateTime` datetime DEFAULT NULL,
  `FamilyId` varchar(100) DEFAULT NULL,
  `CleanItem` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`CleanScheduleId`),
  KEY `FK_cleanschedule_family` (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `CleanSchedule` (`CleanScheduleId`, `CleanDateTime`, `FamilyId`, `CleanItem`) VALUES
	('0208B1E1-4F72-4545-8722-E220894526BB', '2019-08-17 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2'),
	('046188FB-B13C-45B9-9EA0-0A998B24263A', '2019-08-13 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '5'),
	('0C7CF82D-D75A-452A-9775-39A47A0E1F08', '2019-08-15 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '5'),
	('10DCAA5C-5E44-416D-A758-1846DFA72837', '2019-08-20 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '3'),
	('150A422A-3A65-4404-9C9A-61A820C13D17', '2019-08-23 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '1'),
	('1D0CCF2A-B5F9-4A3D-B701-328B26CFB522', '2019-08-22 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '5'),
	('2049F029-0CAA-4249-99C4-5F647B09526D', '2019-08-20 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('2B7E17DA-E144-4832-B6B9-A54C1EE4FA49', '2019-08-15 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('2D682CA3-B900-41B0-9AD9-5611296DFBE5', '2019-08-18 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2'),
	('34931A84-85E7-4236-B1C4-01D190EEFE27', '2019-08-16 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '0'),
	('34D5124E-21FE-4A74-BD25-1190B063D0EE', '2019-08-14 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '3'),
	('37F40275-D8DD-43A2-B37E-2DB649A8BA3B', '2019-08-22 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '3'),
	('384343F7-AC7E-4C71-BFC9-9D5AFCE58113', '2019-08-19 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '4'),
	('3A37A6D9-8ED8-4DF2-BB67-B71560DA1D34', '2019-08-14 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2'),
	('3CC296C1-172B-4EBB-81C0-67B1A849B978', '2019-08-14 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('3FDF9E95-82EB-4A91-811F-870DA2C0F788', '2019-08-13 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('43483F07-889A-4BA8-B0D5-149FBCD270F8', '2019-08-14 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '5'),
	('4508A7DA-A570-4918-86DF-591686233A9C', '2019-08-15 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '3'),
	('4B4E212B-C40B-4233-B3B3-3531D6FE7915', '2019-08-18 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('56AA47E8-E0E0-4224-8EA9-96950B6B7AC5', '2019-08-15 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('5B29E024-BB11-4336-ADD7-60443851F599', '2019-08-23 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '0'),
	('5CD82BE7-E711-4BB1-B1C5-7D729CAAB02D', '2019-08-20 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '0'),
	('5CFD5FD4-1999-4EBA-B31B-5A2BD1B8C0E5', '2019-08-19 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '3'),
	('5EF225E5-DE30-46A0-AFF9-2A9DA7D5AF1E', '2019-08-15 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('6698F721-F6E6-4C58-93E9-1EC39FC3A50D', '2019-08-13 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '4'),
	('685D2679-4C17-4FB9-9FB8-EC6F856DA121', '2019-08-17 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('695A1809-37DE-416E-970E-DF43857ADC14', '2019-08-20 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '5'),
	('70AD02FA-9FD7-4AB6-B22C-D3D33F48CD54', '2019-08-13 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '3'),
	('71CBD806-D0E4-4E87-8ACF-1BF9995EF69C', '2019-08-16 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '1'),
	('74A6DFBE-FFE8-4013-9C7B-E17640D36475', '2019-08-21 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '4'),
	('7BE1E533-FDE3-40EA-9A4F-E7638BA1E168', '2019-08-18 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '5'),
	('7E54D4BD-3DD2-4696-8DBC-BCCB0FD71EE2', '2019-08-17 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '1'),
	('83595DE1-12FE-453D-9DDD-6ED7D0A355F4', '2019-08-18 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('83B129E4-E37F-4F1F-B013-C2B2CECC8181', '2019-08-20 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2'),
	('843D27D9-CB11-446B-AF65-563C641D872B', '2019-08-16 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '3'),
	('8C1F882A-A36B-4023-B311-25D7EC1F8EA8', '2019-08-17 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '3'),
	('905AEEEF-2750-4934-9CE5-87416027F09B', '2019-08-22 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '1'),
	('98ADC0FA-508E-4117-8075-4567995A8C45', '2019-08-19 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '1'),
	('9B1B4243-2C42-4268-A4FB-A5FE95DF57E6', '2019-08-21 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '1'),
	('9B3946B3-C184-4EFE-B005-568D930101FD', '2019-08-14 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '4'),
	('9EFD2E6E-15E9-48CE-B8C9-75033D75AD00', '2019-08-14 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('A1596044-F368-46CB-9D2F-8C286C51BEF9', '2019-08-22 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '4'),
	('A25F668E-5E12-4E15-A71D-9ACBC51200A5', '2019-08-13 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '1'),
	('A47EA3A8-3EAC-4171-92B2-B91B5478C957', '2019-08-21 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2'),
	('ABA175B5-2865-4396-8B0A-39E889A8FAE0', '2019-08-21 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '0'),
	('AE3D7002-EB24-4105-ABB3-FB7ABB9E175A', '2019-08-19 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '0'),
	('BE41D9A4-358B-41E6-A016-9829701E2DCF', '2019-08-17 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '5'),
	('C0F392D2-0461-4589-88A9-7E4C3E24C79E', '2019-08-19 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('C23CAA9C-8C63-4F74-8251-1A0773B18104', '2019-08-23 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2'),
	('C464A299-5EF7-4999-B4F2-578B20BBB524', '2019-08-23 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '5'),
	('C6727911-38B7-40F8-9C4F-730F652A67D0', '2019-08-22 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2'),
	('C78F99F6-37B6-4FD5-AD04-D586BF032D1C', '2019-08-16 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '5'),
	('CDEC8205-C50F-4688-BF10-3600A2689753', '2019-08-21 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '5'),
	('CE8F3CFB-5959-451B-94B9-95CFBE77DAFB', '2019-08-19 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '5'),
	('CEC240B4-7988-479F-BEEE-7D39999067B3', '2019-08-17 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '0'),
	('D420FFDD-6BAE-435F-85AE-57D917EA63ED', '2019-08-18 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '3'),
	('D60541EA-5A4E-46F9-8322-BDE6A0E5380D', '2019-08-21 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '3'),
	('E2EC6280-307F-4E47-BA9E-0A08EF4602DD', '2019-08-23 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '3'),
	('EF29679A-4952-4F37-AE3F-8F2A14A5A0FF', '2019-08-13 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('F7B09E72-69BA-4D00-A157-5E36DD1ED4CE', '2019-08-15 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2'),
	('F802E6A4-5F10-4985-BFD2-0D5C4435EF6A', '2019-08-18 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('F80F67C5-6849-4FF7-B2C5-739268BD51BF', '2019-08-23 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '4'),
	('FA20D45B-9B64-4C61-9148-270FF13CF38E', '2019-08-20 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '4'),
	('FA6FC631-97B1-4A9E-807D-2B520ACB7D28', '2019-08-16 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('FA796E8C-5B52-46DF-8910-F23080B9ECA7', '2019-08-22 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '0'),
	('FF257219-DB3E-4CBB-8E63-C5A85B09950A', '2019-08-16 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '4;');


/* 實際做家事紀錄表 */
CREATE TABLE `CleanRecord` (
  `CleanRecordId` varchar(100) NOT NULL,
  `CleanScheduleId` varchar(100) DEFAULT NULL,
  `FamilyId` varchar(100) DEFAULT NULL,
  `StartTime` datetime DEFAULT NULL,
  `EndTime` datetime DEFAULT NULL,
  PRIMARY KEY (`CleanRecordId`),
  KEY `FK_cleanrecord_cleanschedule` (`CleanScheduleId`),
  KEY `FK_cleanrecord_family` (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `CleanRecord` (`CleanRecordId`, `CleanScheduleId`, `FamilyId`, `StartTime`, `EndTime`) VALUES
	('1BBFDF7D-D064-4091-925B-0541C27BD321', '5CD82BE7-E711-4BB1-B1C5-7D729CAAB02D', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-20 18:12:00', '2019-08-20 19:02:00'),
	('38F23254-67F5-4AB5-A8DE-929501AB802E', '150A422A-3A65-4404-9C9A-61A820C13D17', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-23 18:52:00', '2019-08-23 20:19:00'),
	('457B8655-44A1-4F59-8B45-7DB0FD2E1A8E', 'AE3D7002-EB24-4105-ABB3-FB7ABB9E175A', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-19 18:30:00', '2019-08-19 19:32:00'),
	('4911A9BD-BE90-4174-990B-A72498678A92', '8C1F882A-A36B-4023-B311-25D7EC1F8EA8', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-17 18:09:00', '2019-08-17 19:56:00'),
	('49292BD5-27D1-40E8-8A64-73F6A99211C9', '2049F029-0CAA-4249-99C4-5F647B09526D', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-20 18:21:00', '2019-08-20 19:12:00'),
	('4A182CCE-4267-4451-A456-3A0B4CD3ED36', '2B7E17DA-E144-4832-B6B9-A54C1EE4FA49', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-15 18:04:00', '2019-08-15 19:34:00'),
	('4E10D641-A5E9-4FCF-A2EF-5B1896A091D2', '56AA47E8-E0E0-4224-8EA9-96950B6B7AC5', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-15 18:43:00', '2019-08-15 19:23:00'),
	('5E4206B9-1078-41FF-BF54-A2920A37D843', 'C6727911-38B7-40F8-9C4F-730F652A67D0', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-22 18:59:00', '2019-08-22 20:21:00'),
	('65B732DE-AE07-45E6-8D14-C356E1BDEBE9', '3CC296C1-172B-4EBB-81C0-67B1A849B978', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-14 18:10:00', '2019-08-14 19:40:00'),
	('675FFDEF-1000-4507-87A6-8DAEDD2247BD', 'D60541EA-5A4E-46F9-8322-BDE6A0E5380D', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-21 18:01:00', '2019-08-21 19:43:00'),
	('6E2CB5C0-FDD3-4ABA-B38B-BC4915A458EE', '37F40275-D8DD-43A2-B37E-2DB649A8BA3B', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-22 18:14:00', '2019-08-22 20:19:00'),
	('7353332C-2E99-48CB-900F-BDE2B7FE39B6', '83B129E4-E37F-4F1F-B013-C2B2CECC8181', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-20 18:29:00', '2019-08-20 19:57:00'),
	('79D7DF92-8A68-4AF1-B5FE-7733EEB495E6', '905AEEEF-2750-4934-9CE5-87416027F09B', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-22 18:44:00', '2019-08-22 19:21:00'),
	('7D4085F9-614D-41FB-AE16-1442E13DEACD', '3A37A6D9-8ED8-4DF2-BB67-B71560DA1D34', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-14 18:31:00', '2019-08-14 19:25:00'),
	('8A284F82-BB85-4B10-A56A-4B07B79A2D80', 'D420FFDD-6BAE-435F-85AE-57D917EA63ED', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-18 18:37:00', '2019-08-18 20:21:00'),
	('8FBCF266-C104-4113-B3DB-461ACAD3048B', '843D27D9-CB11-446B-AF65-563C641D872B', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-16 18:38:00', '2019-08-16 19:40:00'),
	('A7960AAC-6F6D-4B58-94BD-2253253C572D', 'E2EC6280-307F-4E47-BA9E-0A08EF4602DD', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-23 18:10:00', '2019-08-23 20:16:00'),
	('A8D86DAE-4135-4090-9D57-04F2D585DD61', '98ADC0FA-508E-4117-8075-4567995A8C45', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-19 18:09:00', '2019-08-19 20:11:00'),
	('ADF35E1E-1760-4012-98CF-CE0B5751C096', 'A25F668E-5E12-4E15-A71D-9ACBC51200A5', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-13 18:09:00', '2019-08-13 19:16:00'),
	('B56F7CC2-EC30-4EB0-9969-5058FDE390AC', 'F7B09E72-69BA-4D00-A157-5E36DD1ED4CE', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-15 18:15:00', '2019-08-15 19:02:00'),
	('BA54CEF9-55EC-449B-BA32-E48AA84EAFD1', '10DCAA5C-5E44-416D-A758-1846DFA72837', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-20 18:03:00', '2019-08-20 19:51:00'),
	('DAF64DBF-0B83-40CF-A616-D7DD8A538C7C', '9B1B4243-2C42-4268-A4FB-A5FE95DF57E6', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-21 18:41:00', '2019-08-21 20:19:00'),
	('DB564B2A-F025-43D9-8D27-8E14E717FB4C', '2D682CA3-B900-41B0-9AD9-5611296DFBE5', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-18 18:47:00', '2019-08-18 20:07:00'),
	('DE5FA643-1485-4CC5-B913-82CC5A989929', '9EFD2E6E-15E9-48CE-B8C9-75033D75AD00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-14 18:48:00', '2019-08-14 20:12:00'),
	('E5BE6337-DDEB-4107-A5A6-FAD1F3DD4422', 'FA6FC631-97B1-4A9E-807D-2B520ACB7D28', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-16 18:52:00', '2019-08-16 19:36:00'),
	('E8A0FFAB-BADD-4DCD-8278-F32BD0C06922', '7E54D4BD-3DD2-4696-8DBC-BCCB0FD71EE2', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-17 18:45:00', '2019-08-17 19:59:00'),
	('EA538BCF-FE8E-45A8-9BEF-7C08ABBFCB60', '71CBD806-D0E4-4E87-8ACF-1BF9995EF69C', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-16 18:27:00', '2019-08-16 19:49:00');

上一篇
第八堂:家事管理 - 從每張表的CREATE TABLE 活用常見的欄位限制(主鍵、外來鍵、索引、唯一)
下一篇
第十堂:家事管理 - 使用Update修改休息的成員排班 (UPDATE + WHERE IN 相同的表格)
系列文
從問題理解與活用SQL語法30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言