sqlserver 20761上課筆記02

  • 文字
  • 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: