"SELECT ... FROM table a JOIN table b ON a.column = b.column"
常用的關聯方式有inner join (交集), left join (同左表), right join (同右表), cross join (並集)。
例如:
SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID;
SQL模糊查詢的語法為
“SELECT column FROM table WHERE column LIKE 'pattern';”。
SQL提供了四種匹配模式:
1. % 表示任意0個或多個字符。如下語句:
SELECT * FROM user WHERE name LIKE 'a%';
將會把a打頭的名字(name)全找出來;
2. _ 表示任意單個字符。語句:
SELECT * FROM user WHERE name LIKE 'a_';
將會把a打頭的兩個字母的名字(name)全找出來,如a1,a2...;
3. [ ] 表示括號內所列字符中的壹個。語句:
SELECT * FROM user WHERE name LIKE '[abc]1';
將找出“a1”、“b1”、“c1”(而不是“abc1”);
如 [ ] 內有壹系列字符(01234、abcde之類的)則可略寫為“0-4”、“a-z”,所以,本例可改寫為:
SELECT * FROM user WHERE name LIKE '[a-c]1';
4. [^] 表示不在括號所列之內的單個字符。語句:
SELECT * FROM user WHERE name LIKE '[^abc]1';
將找出“d1”、“e1”、“f1”等;
如 [ ] 內有壹系列字符(01234、abcde之類的)則可略寫為“0-4”、“a-z”,所以,本例可改寫為:
SELECT * FROM user WHERE name LIKE '[^a-c]1';