基本結構:SELECT 選擇清單、FROM 子句、WHERE 子句。
SELECT nickname, email
FROM testtable
WHERE name = '張三';
SELECT * FROM testtable;SELECT nickname, email FROM testtable;SELECT 昵稱 = nickname, 電子郵件 = email FROM testtable; (或 nickname AS 昵稱)SELECT DISTINCT column FROM table; (預設 ALL 保留重複)SELECT TOP 2 * FROM testtable; 或 TOP 20 PERCENTcitytable.cityid。FROM usertable a, citytable b。SELECT ... FROM authors a, titleauthor ta, (SELECT title_id, title FROM titles WHERE ytd_sales > 10000) AS t WHERE ...| 類別 | 運算子 | 範例 |
|---|---|---|
| 比較 | >, >=, =, <, <=, <>, !>, !< | age > 20 |
| 範圍 | BETWEEN ... AND ... / NOT BETWEEN | age BETWEEN 10 AND 30 |
| 列表 | IN (...) / NOT IN (...) | country IN ('Germany','China') |
| 模式比對 | LIKE / NOT LIKE | name LIKE '%Publishing' |
| 空值判斷 | IS NULL / IS NOT NULL | age IS NULL |
| 邏輯 | NOT, AND, OR (優先序:NOT > AND > OR) | age > 20 AND city = 'Taipei' |
LIKE 通配字元:% (任意長度)、_ (單一字元)、[] (指定字元集合)、[^] (排除字元集合)。中文建議用 %%。
SELECT * FROM usertable ORDER BY age DESC, userid ASC;
ASC 升序 (預設),DESC 降序。ntext、text、image 排序。SELECT 陳述式1
UNION [ALL]
SELECT 陳述式2
[UNION [ALL] SELECT 陳述式3 ...];
ALL 保留重複列,預設移除重複。ORDER BY 需使用第一個查詢的欄位名/別名/序號。同樣遵循 SQL-92 FROM join_table join_type join_table [ON (join_condition)] 語法。
LEFT [OUTER] JOIN 保留左表所有列。RIGHT [OUTER] JOIN 保留右表所有列。FULL [OUTER] JOIN 保留兩表所有列。WHERE,回傳笛卡爾積。SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city;
SELECT a.*, p.pub_id, p.pub_name, p.country
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city;
SELECT p1.pub_id, p2.pub_id, p1.pr_info
FROM pub_info AS p1 INNER JOIN pub_info AS p2
ON DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info);