詹姆士的筆記本

NULL

空值(Empty)

未知(Unkown)

必要性：Employee table中 leave_date，盤點table actural_qty

3 VALUE LOGICAL

AND	T	F	U (T與F各帶一次)
T	T	F	U
F	F	F	F
U	U	F	U

OR	T	F	U
T	T	T	T
F	T	F	U
U	T	U	U

NOT T = F ，NOT F = T ，NOT U = U

SELECT c1,c2,c3,
c1+c2+c3 AS total
FROM dbo.TestNull

--運算中包含NULL則NULL

SELECT c1,c2,c3,
ISNULL(c1,0) + ISNULL(c2,0) +ISNULL(c3,0) AS total
FROM dbo.TestNull
--使用ISNULL解決

條件下在ON 與WHERE的差別

• 只對LEFT JOIN、RIGHT JOIN 有差異，INNER JOIN 沒差
• LEFT JOIN 時ON則過濾右表，WHERE為JOIN後的條件過濾

SELECT c.custid,c.companyname,o.orderid
FROM Sales.Customers c
LEFT JOIN Sales.Orders o
  ON c.custid = o.custid AND c.city='Paris';
--左邊至少出現一次，但右邊只符合city為paris的有值，其他為null

SELECT c.custid,c.companyname,o.orderid
FROM Sales.Customers c
LEFT JOIN Sales.Orders o
  ON c.custid = o.custid
WHERE c.city = 'paris';
--表join完才過濾條件

 Function

numeric

SELECT ROUND(12345.6789,2)--12345.6800
SELECT ROUND(12345.6789,-3)--12000.0000
SELECT ROUND(12345.6789,0,1)--123456.00 無條件捨去

string

--擷取字串
SELECT LEFT('Microsoft sql server sql',9), --Microsoft
SUBSTRING('Microsoft sql server sql',11,3) --sql
,RIGHT('Microsoft sql server',6) --server


--取出比對字串的起始INDEX
SELECT CHARINDEX('SQL', 'Microsoft SQL Server SQL SQL');--11
SELECT CHARINDEX('SQL', 'Microsoft SQL Server SQL SQL',12);--22
SELECT CHARINDEX('SQL', 'Microsoft SQL Server SQL SQL',23);--26
SELECT CHARINDEX('SQL', 'Microsoft SQL Server SQL SQL',27);-- 0


--字串取代
SELECT REPLACE('Learning about T-SQL string functions', 'T-SQL', 'Transact-SQL');


--去除字串空白
SELECT RTRIM('   ABC   '),
LTRIM('   ABC   '),
RTRIM(LTRIM('   ABC   '));


--串接
--SQL2012
SELECT CONCAT(productid, '-', productname, ':', unitprice)
FROM Production.Products;

• 計算字串長度
  • LEN：計算字串有多少個字元，中英文UNICODE與否，每個字元都是一個會忽略後面空白
  • DATALENGTH：計算字串佔多少Byte
    • 中文x2
    • 英數：unicode x2 ，ANSI x1
    • 不會忽略空白

SELECT LEN('   中文字ABC   '),--9
DATALENGTH('   中文字ABC   '),--15
LEN(N'   中文字ABC   '),--9
DATALENGTH(N'   中文字ABC   ');--24

DateTime

--取得年、月、日
SELECT YEAR('2016-3-16'),MONTH('2016-3-16'),DAY('2016-3-16');

--利用DATEPART取得年、月、日、周、星期幾、時、分、秒，會依設定的語言有所不同
SELECT DATEPART(YEAR, GETDATE()),
DATEPART(MONTH, GETDATE()),
DATEPART(DAY, GETDATE()),
DATEPART(WEEK, GETDATE()),
DATEPART(WEEKDAY, GETDATE()),
DATEPART(HOUR, GETDATE()),
DATEPART(MINUTE, GETDATE()),
DATEPART(SECOND, GETDATE());


--利用DATENAME取得日期NAME
SELECT * FROM sys.syslanguages;
SET LANGUAGE N'繁體中文'
SET LANGUAGE N'us_english'
SELECT DATENAME(YEAR, GETDATE()),
DATENAME(MONTH, GETDATE()),
DATENAME(DAY, GETDATE()),
DATENAME(WEEK, GETDATE()),
DATENAME(WEEKDAY, GETDATE()),
DATENAME(HOUR, GETDATE()),
DATENAME(MINUTE, GETDATE()),
DATENAME(SECOND, GETDATE());


--利用DATEADD取得增加後的單位時間
SELECT DATEADD(YEAR, 10, GETDATE()),
DATEADD(MONTH, 10, GETDATE()),
DATEADD(WEEK, 10, GETDATE()),
DATEADD(DAY, 10, GETDATE()),
DATEADD(HOUR, 10, GETDATE()),
DATEADD(MINUTE, 10, GETDATE()),
DATEADD(SECOND, 10, GETDATE());


--利用DATEDIFF取得時間單位差值
SELECT DATEDIFF(YEAR, '2017-12-31', '2018-1-1'),
DATEDIFF(MONTH, '2017-12-31', '2018-1-1'),
DATEDIFF(DAY, '2017-12-31', '2018-1-1'),
DATEDIFF(HOUR, '2017-12-31', '2018-1-1'),
DATEDIFF(MINUTE, '2017-12-31', '2018-1-1'),
DATEDIFF(SECOND, '2017-12-31', '2018-1-1');


--取得月底最後一天
SELECT EOMONTH(GETDATE()),
EOMONTH(GETDATE(), 1),--2019-12-31
EOMONTH(GETDATE(), 2),--2020-01-31
EOMONTH(GETDATE(), 3),--2020-02-29
EOMONTH(GETDATE(), -1),--2019-11-30
EOMONTH(GETDATE(), -2),--2019-10-31
EOMONTH(GETDATE(), -3);--2019-09-30


--組合時間
SELECT DATETIMEFROMPARTS(2016, 3, 16, 12, 30, 0, 0),
DATETIME2FROMPARTS(2016, 3, 16, 12, 30, 0, 0, 0),
DATEFROMPARTS(2016, 3, 16),
TIMEFROMPARTS(12, 30, 0, 0, 0 );

資料轉型

• CONVERT：CONVERT為SQL SERVER專有，標準的ANSI為CAST，但由於格式化回傳的附加功能所以還是有可能用到
• CAST

--CONVERT 轉型
SELECT CONVERT(varchar(10), productid) + '-' + productname + ':' + CONVERT(varchar(10), unitprice)
FROM Production.Products;

--CONCERT 格式轉換
SELECT CONVERT(varchar(20), GETDATE(), 111);
SELECT CONVERT(varchar(20), GETDATE(), 112);

SELECT CONVERT(varchar(20), GETDATE(), 11);
SELECT CONVERT(varchar(20), GETDATE(), 12);

--CAST 轉型
SELECT CAST('2016-2-31' AS date)

--TRY_PARSE轉型 失敗時會給NULL
SELECT TRY_PARSE('2016-2-31' AS date)--NULL
SELECT TRY_PARSE('2016-2-12' AS date)--201602-12

SELECT phone, TRY_PARSE(RIGHT(phone,4) AS int)
FROM Sales.Customers;

FORMAT

SELECT FORMAT(12345.6789, 'c', 'en-us'),
FORMAT(12345.6789, 'c', 'zh-tw'),
FORMAT(12345.6789, 'c', 'ja-jp'),
FORMAT(12345.6789, 'c', 'de-de'),
FORMAT(12345.6789, '#,##0.00');

SELECT FORMAT(GETDATE(), 'd', 'en-us'),
FORMAT(GETDATE(), 'd', 'zh-tw'),
FORMAT(GETDATE(), 'd', 'ja-jp'),
FORMAT(GETDATE(), 'd', 'en-gb');

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd MMM MMMM ddd dddd', 'en-us'),
FORMAT(GETDATE(), 'yyyy-MM-dd MMM MMMM ddd dddd', 'ja-jp'),
FORMAT(GETDATE(), 'yyyy-MM-dd MMM MMMM ddd dddd', 'zh-tw'),
FORMAT(GETDATE(), 'yyyy-MM-dd MMM MMMM ddd dddd', 'de-de');

IFF

SELECT productid, productname, unitprice, 
IIF(unitprice >= 20, N'高價位',
	IIF(unitprice >=10, N'中價位',	
		IIF(unitprice < 10, N'低價位', N'未知'))) AS pricetype
FROM Production.Products;

CHOOSE

需配合資料庫的設計

SELECT productid, productname, unitprice, 
IIF(unitprice >= 20, N'高價位',
	IIF(unitprice >=10, N'中價位',	
		IIF(unitprice < 10, N'低價位', N'未知'))) AS pricetype
FROM Production.Products;

boolean function

SELECT ISDATE('2016-2-31')
SELECT ISDATE('2016-2-12')
SELECT ISDATE(NULL)

SELECT ISNUMERIC('123');
SELECT ISNUMERIC('abc');
SELECT ISNUMERIC(null);