SQL Server T-SQL 學習筆記

2020-11-24

資料庫使用的基本功 Query Data with T-SQL 😎

說明

學習 T-SQL 的重點順序，首先是熟悉 ANSI SQL 共有的查詢，包括：

SELECT, UPDATE, DROP, ALTER, DELETE, TRUNCATE, ORDER BY, GROUP BY, HAVING, SET Operator, Join Operator, Window Function 等

接著是學習 T-SQL 專有的保留字及功能，例如：

IIF, CHOOSE, OUTPUT 等

進階則是往 CTE 邁進，使用暫存表、程式化的方式來客製查詢需求。

Function

ISNULL

SELECT
  [SalesPerson],
  ISNULL([Title], '') + [LastName] as CustomerName,
  [Phone]
FROM [AdventureWorksLT2016].[SalesLT].[Customer]

IS NULL & IN

SELECT ProductCategoryID, Name, ListPrice, SellEndDate
FROM SalesLT.Product
WHERE ProductCategoryID IN (5, 6, 7) AND SellEndDate IS NULL;

NULLIF

SELECT Name, NULLIF(Color, 'Multi') AS SingleColor
FROM SalesLT.Product;

COALESCE

WHEN CASE

SELECT Name,
  CASE Size
    WHEN 'S' THEN 'Small'
    WHEN 'M' THEN 'Medium'
    WHEN 'L' THEN 'Large'
    WHEN 'XL' THEN 'Extra-Large'
    ELSE ISNULL(Size, 'n/a')
  END AS ProductSize
FROM SalesLT.Product;

DISTINCT

SELECT Name, ListPrice
FROM SalesLT.Product
ORDER BY ProductNumber OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT & Predicate

SELECT Name, ListPrice, ProductNumber
FROM SalesLT.Product
WHERE ProductNumber LIKE 'FR-_[0-9][0-9]_-[0-9][0-9]';

SELECT Name FROM SalesLT.Product
WHERE SellEndDate IS NOT NULL;

SELECT Name FROM SalesLT.Product
WHERE SellEndDate BETWEEN '2006/1/1' AND '2006/12/31';

String

LEFT, SUBSTRING, CHARINDEX

SELECT Name, ProductNumber,
  LEFT(ProductNumber, 2) AS ProductType,
  SUBSTRING(
    ProductNumber,
    CHARINDEX('-', ProductNumber) + 1, 4
  ) AS ModelCode
FROM SalesLT.Product;

將字串 NULL 轉為 NULL

UPDATE dbo.TableName SET [Column1] = NULL WHERE [Column1] = 'NULL'

Date

Date Convert

SQL Server DateTime Convert

DATEPART

SELECT GETDATE() NOW -- 2020-12-17  18:00:00.000
SELECT DATEPART(YEAR , GETDATE()) Year -- 2020
SELECT DATEPART(WEEKDAY , GETDATE()) WeekDay -- 5

DATEPART / DATENAME

DATEDIFF

SELECT DATEDIFF(yy,SellStartDate, GETDATE()) YearsSold, ProductID, Name
FROM SalesLT.Product
ORDER BY ProductID;

DATEADD

取得最近 24 小時的日期時間，用於篩選資料範圍的時候非常好用。

SELECT DATEADD(day, -1, GETDATE())

Logical

IIF

SELECT productid, listprice, IIF(listprice > 50, 'high','low') AS PricePoint
FROM Production.Product;

Choose

SELECT CHOOSE(0, 'one', 'two', 'three') AS Mapping -- NULL
SELECT CHOOSE(1, 'one', 'two', 'three') AS Mapping -- onw
SELECT CHOOSE(2, 'one', 'two', 'three') AS Mapping -- two
SELECT CHOOSE(3, 'one', 'two', 'three') AS Mapping -- three
SELECT CHOOSE(4, 'one', 'two', 'three') AS Mapping -- NULL

Window Function

RANK

SELECT TOP(100) ProductID, Name, ListPrice,
	RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM SalesLT.Product AS p
ORDER BY RankByPrice;

Alter Table

變更資料表名稱 Rename Table

exec sp_rename 'dbo.TableName', 'dbo.NewTableName';

新增 / 異動資料表欄位

ALTER TABLE dbo.TableName ADD Column1 NVARCHAR(128) NULL
ALTER TABLE dbo.TableName ADD Column2 DateTime NULL
ALTER TABLE dbo.TableName ADD Column3 INT NULL

ALTER TABLE dbo.TableName ALTER COLUMN Column1 NVARCHAR(32) NULL
ALTER TABLE dbo.TableName DROP COLUMN Column1

子查詢 SubQuery

SQL Server SubQuery 子查詢筆記

Set Operator

Union

Union 可以將不同資料表的特定欄位合併為新的查詢結果，不同資料表的不同欄位會以型別轉換的方式構成新查詢結果，如果無法轉換則會報錯。

此外也可以利用 SELECT 'string', 'string' 的方式客製要 Union 的資料。

SELECT COL1, COL2 FROM TB1
UNION
SELECT COL1, COL3 FROM TB2
UNION
SELECT 'string', 'string'

預設情況下重複的結果移除，如果想要保留可以加上關鍵字 ALL

SELECT COL1, COL2 FROM TB1
UNION ALL
SELECT COL1, COL3 FROM TB2

Except

Intersect

...

Common Table Expression, CTE

WITH TempTableName
AS (
  SELECT *
  FROM dbo.TablenAME
)