MSSQL_quick_review
===

SQL重要語法
===

W3School筆記
--

1. BETWEEN

首尾都會包含

SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60;

2. IN

SELECT * FROM Customers
WHERE City IN ('Paris','London');

3. NOT

SELECT * FROM Customers
WHERE NOT Country='Germany';
SELECT * FROM Customers
WHERE NOT Country='Germany' AND NOT Country='USA';

4. ORDER BY多個欄位

有逗號

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

5. MIN與MAX

SELECT MAX(Price) AS LargestPrice
FROM Products;

6. LIKE與底線百分比符號合用

**'a%'** 代表出現a且a後面至少兩個字元

SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';

7. NOT LIKE

SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';

8. Wildcards

8.1 [charlist]

舉例 以a或b或c開頭

SELECT * FROM Customers
WHERE City LIKE '[abc]%';
8.2 [!charlist]

舉例 不以d,e,f開頭
SELECT * FROM Customers
WHERE City LIKE '[!def]%';

9. EXISTS

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

10. SELECT ALL

所有學生的歷史成績都要高過80成績 才會列出FirstName
否則就不顯示任何row

SELECT FirstName
FROM Students
WHERE StudentID = ALL (SELECT StudentID FROM History WHERE Score > 80);