- 文字
- collation
- predicate
- DISTINCT用法
- CASE用法
文字
- char:(1~8000)
- varchar:(1~8000)
- nchar:(1~4000)
- nvarchar:(1~4000)
--------------------------------
- varchar(max)
- nvarchar(max)
--------------------------------
**text => SQL2005之後被varchar(max)取代
**ntext =>被nvarchar(max)取代
**n支援UNICODE UCS-2字元,因為萬國編碼(支援中文字),所以1字儲存2Byte
Predicate logic 述詞(謂詞)邏輯
SELECT * FROM dbo.Emplyee
WHERE employee_id =3
employee_id =3 即為述詞(謂詞)邏輯
employee_id | employee_name | birth_date | salary | employee_id =3
...
3 | Linda | 1972-1-1 | 42000 | true
4 | Alice | 1975-1-1 | 36000 | false
...
等式、不等式 | =,>,<,>=,<=,<> |
IN | employee_id IN (1,3,5) |
BETWEEN | salary BETWEEN 3000 AND 4000 |
LIKE |
%:0個以上字元 _:1個字元 |
邏輯連接詞 |
( ),NOT,AND,OR 優先順序:1 2 3 4 |
SELECT * FROM dbo.Employee
WHERE employee_name LIKE N'A%' --以A為字首
--WHERE employee_name LIKE N'%A' --以A為結尾
--WHERE employee_name LIKE N'%A%' --包含A
--WHERE employee_name LIKE N'J_ _ _%'--3個字元
Collation 定序
定序指的就是決定資料被「排序」與「比對」的規則
- 定序層級:
- Server
- Database
- Table=>Column
- SQL語法
- 定序分類
- Case (大小寫):CI(Case Insensitive)、CS(Case Sensitive)
- Accent (腔調字)
- Kana (平/片假名)
- Width (全/半行字)
SELECT * FROM dbo.Employee
WHERE employee_name =N'DUKE';
SELECT * FROM dbo.Employee
WHERE employee_name =N'duke';
SELECT * FROM dbo.Employee
WHERE employee_name =N'Duke';
SELECT * FROM dbo.Employee
WHERE employee_name =N'DUKE' COLLATE Chinese_Taiwan_Stroke_CI_AS;
DISTINCT
DISTINCT運作在所有的欄位上,而非第一個欄位上
SELECT
YEAR(orderdate) AS yr
MONTH(orderdate) AS mn
FROM Sales.Orders;
SELECT DISTINCT
YEAR(orderdate) AS yr
MONTH(orderdate) AS mn
FROM Sales.Orders;
Case
simple case
SELECT orderid,orderdate,shipperid,
CASE shipperid
WHEN 1 THEN N'陸'
WHEN 2 THEN N'海'
WHEN 3 THEN N'空'
ELSE N'未知'
END AS shippname
FROM Sales.Orders;
search case
SELECT productid,productname,unitprice,
CASE
WHEN unitprice <10 THEN N'低'
WHEN unitprice <20 THEN N'中'
WHEN unitprice >=20 THEN N'高'
ELSE N'未知'
END AS PriceType
FROM Production.Products;
Reference: