MS SQL 學習紀錄-4

建立和管理資料表(關聯表) - 基礎(續)

SELECT INTO 產生副本資料表

• SELECT INTO 會在目前資料庫中建立新的 (副本) 資料表，然後將查詢結果集的資料列插入其中，這個新的資料表和來源資料表不同的地方有：

  • 新資料表的欄位是根據 SELECT 子句「選取清單」的內容，再參考來源資料表的綱要結構(欄位名稱、資料類型、是否 NULL)來建立，例如：來源資料表有 10 個欄位，但 SELECT 「選取清單」只列了 3 個欄位，因此新資料表就只有 3 個欄位
  • 不會複製來源資料表的條件約束 CONSTRAINTS 或索引 INDEXES

• 若來源資料表的欄位有 IDENTITY 屬性，也會一併複製，IDENTITY 屬性後續課程會說明

• 常用來快速建立資料表副本或空的資料表 (不含資料列的綱要結構)

• 基本語法：

SELECT <select-list> INTO 新資料表
FROM 來源資料表名稱

注意事項：

新資料表僅支援標準資料表、暫存資料表

暫存資料表和資料表變數

• 暫存資料表和資料表變數，常用在儲存臨時性資料，例如：程式作業期間的中繼資料，建議當暫存的資料筆數小於 100 筆時使用資料表變數，否則使用暫存表，因為資料表變數 SQL Server 不會去解析最佳化它的效能
• 暫存資料表和使用者定義資料表相同，都是建立在資料庫，但不同的是：
  • 暫存資料表自動建立在 TempDB 系統資料庫 (無法變更)，使用者定義資料表建立在用戶自己的資料庫中
  • 暫存資料表會自動刪除，傳統資料表必須人為加工刪除
• 資料表變數顧名思義是宣告一個變數，其資料類型為 TABLE，再加上資料表的綱要結構定義 (資料行、資料類型、是否NULL)
• 暫存資料表和資料表變數都是用完即丟的類型：
  • 暫存資料表適合存放大量記錄
  • 資料表變數適合存放小量記錄

暫存資料表

• 自動儲存在 TempDB 中
• 資料列的大小取決於磁碟空間
• 分為下列兩種：
  • 區域暫存資料表 (#字號開頭)
    • 只要一建立好，只有在原始工作階段才能看見它們，當原始工作階段中斷與 SQL Server 執行個體的連接時，才會自動移除它們
    • 區域暫存資料表名稱會自動加上流水序號，以防止不同工作階段使用相同名稱因而造成衝突
  • 全域暫存資料表 (##字號開頭)
    • 只要一建立好，任何使用者都能看見它們
    • 全域暫存資料表名稱不會自動加上流水序號，所以不同工作階段若使用相同名稱會造成名稱衝突
    • 自動移除時機：
      • 所有其它使用者已經結束對它的參考作業 (非使用中)，建立者中斷與 SQL Server 執行個體的連接時
      • 所有其它使用者正進行對它的參考作業 (正在使用中)，建立者中斷與 SQL Server 執行個體的連接，直到當所有參考 (正在使用中) 這些資料表的使用者都中斷與 SQL Server 執行個體的連接時
• 基本語法：

CREATE TABLE {
  #資料表名稱 | ##資料表名稱}
( 欄位名稱 資料類型 [是否空值],
  ...
);

資料表變數

• 資料表變數 (用完即丟)
  • 自動儲存在記憶體中
  • 資料列的大小取決於記憶體空間
  • 不支援全域，只有區域
  • 最佳的 I/O 存取效率
  • 批次作業結束時自動移除
• 基本語法：

DECLARE @變數名稱 TABLE
( 欄位名稱 資料類型 [是否空值],
  ...
);

注意事項：

自 SQL Server 2016 起，提供記憶體最佳化資料表，參考 使用記憶體最佳化加快暫存資料表與資料表變數的速度

---

在資料表使用 DML 資料操縱語言 - 基礎

因為資料庫資料檔案儲存的設計是以「資料列集」RowSet 為主，因此，DML 的作業也是以資料列為主

• DML 主要功能是在單一資料表存取資料列，其語法都是以讀取與寫入資料庫為主
  • select：僅查詢資料列，有 where 子句
  • insert：新增資料列或記錄，無 where 子句 (無條件新增)
  • update：更新資料列指定的欄值，有 where 子句
  • delete：刪除資料列或記錄，有 where 子句

注意事項

針對工作階段已連接資料庫中的資料表存取資料列
資料表完整名稱為：結構描述名稱.資料表名稱，例如：HR.Employees
結構描述名稱 (選項)，預設為 dbo
資料表名稱 (必須)

• MERGE 陳述式可整合 DML 作業，主要在兩個資料表(來源、目標)之間，同步記錄到目標資料表中 (課程後續再說明)

• 此部份的作業有：

  • 新增記錄
  • 更新記錄的欄位值
  • 刪除記錄
  • 使用 MERGE 合併資料表的記錄

新增記錄 INSERT (Transact-SQL)

• 基本語法一：

INSERT [INTO] 資料表名稱(欄名1, 欄名2, ... , 欄名n)
VALUES (欄值1, 欄值2, ..., 欄值n);

注意事項

可省略 into 子句 (選項)，ORACLE 不可省略
出現在欄名清單的欄名，至少是不允許空值 null 的屬性，允許空值 null 則不受限
欄名清單和欄值清單必須對齊
不可以使用 where 子句
一次一筆，也可一次多筆

• 基本語法二：(省略欄名清單，不建議)

INSERT [INTO] 資料表名稱
VALUES (欄值1, 欄值2, ..., 欄值n);

注意事項

省略欄名清單，則依管理工具呈現的順序
欄名清單和欄值清單必須對齊

• 基本語法三：(一次多筆)

INSERT [INTO] ...省略...
VALUES (第一筆欄值1, 第一筆欄值2, ..., 第一筆欄值n),
       (第二筆欄值1, 第二筆欄值2, ..., 第二筆欄值n),
       ...
       (第ｎ筆欄值1, 第ｎ筆欄值2, ..., 第ｎ筆欄值n);

注意事項

每一個小括號代表一筆記錄
各小括號之間以逗號區隔
欄名清單和欄值清單必須對齊

Demonstratin：INSERT INTO

-- 一次一筆
insert into tbl(年份別, 有效樣本數, [合計 (%)],  [男性 (%)],  [女性 (%)] )
values (N'民國94-97年', 1577, 8.5, 10.4, 6.6 )

-- 一次多筆
insert into tbl(年份別, 有效樣本數, [合計 (%)],  [男性 (%)],  [女性 (%)] )
values (N'民國102-103年', 1460, 13.2, 16.2, 10.5),
       (N'民國102-104年', 2195, 12.3, 14.5, 10.4)

注意事項：

因為 INSERT INTO 是一種無條件新增的指令，若重複執行上述指令，會造成資料重複之虞，可透過資料庫完整性的相關技術加以限制，例如：Primary Key 或 Unique 等，後續課程會再說明。
若必須有條件的 INSERT INTO，除了自行利用 IF…ELSE… 控制以外，可考慮使用使用 MERGE
從其它角度來看，例如：一些登錄參加抽獎的活動，幾乎都不允許重複登錄，還有每年的報稅活動，它卻允許重複申報，不論動機為何，這在資料庫都是一種 INSERT INTO 的動作

更新記錄的欄位值 UPDATE (Transact-SQL)

• 基本語法：

UPDATE 資料表名稱
SET 欄名1 = 欄值1,
    欄名2 = 欄值2,
    ...
    欄名n = 欄值n
WHERE 篩選條件;

注意事項

若省略 where 子句，則影響整個資料表

Demonstratin：UPDATE

update tbl
set [有效樣本數] = [有效樣本數] + 100000
where [年份別] = '民國102-104年';

刪除記錄 DELETE (Transact-SQL)

• 基本語法：

DELETE [FROM] 資料表名稱
WHERE 篩選條件;

注意事項：

若省略 where 子句，則影響整個資料表
delete 是一筆一筆刪除，若要快速刪除可使用 truncate table

Demonstratin：DELETE

delete tbl
where [年份別] = '民國102-103年';

Exercises：T-SQL DML 實作練習

1. 假設客戶編號 31 號在今天直接向銷售人員編號 6 號訂購產品，相關細節如下：

• 訂購的產品：(以下提示資訊必須 INSERT 到 Sales.OrderDetails，還有將數量(Quantity) UPDATE 到 Production.Products.InStock)

ProductID	Quantity	UnitPrice	DisCount
电脑	$1600	參考自 Production.Products.ListPrice 的資料值	95折

• 訂單相關欄位的需求 Part I：(以下提示資訊必須 INSERT 到 Sales.Orders)

OrderDate	RequiredDate	ShippedDate	ShipperID	Freight
今日，可從 GetDate()，但時間值都必須是 00:00:00	一星期後	RequiredDate 的前 3 天	1	50

• 訂單相關欄位的需求 Part II：(以下提示資訊必須 INSERT 到 Sales.Orders)

ShipName	ShipAddress	ShipCity	ShipRegion	ShipPostalCode	ShipCountry
必須顯示「收件人：」的字樣在 CompanyName 前面	Address	City	Region	PostalCode	Country

注意事項：

以上 CompanyName、Address、City、Region、PostalCode、Country 參考自 Sales.Customers 查詢得到的資料值

• 請分別為 Sales.Orders (訂單主檔) 和 Sales.OrderDetails (訂單明細) 撰寫新增訂單的 INSERT 陳述式 (撰寫兩道 INSERT INTO 指令)

• 請為 Production.Products 撰寫更新庫存量 [InStock] 的 UPDATE 陳述式 (撰寫一道 UPDATE 指令)