---
# System prepended metadata

title: MSSQL_quick_review

---

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);
```

        