SQL速成（2）

SQL高级语法

包括一些关键字：TOP、LIKE、IN、BETWEEN AND、ALIASES、JOIN、INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN、UNION、SELECT INTO、CREATE DB/TABLE/INDEX、CONSTRAINTS、NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT、DROP、ALTER、INCREMENT、VIEW、DATE等。

（1）TOP/LIMIT只列出部分结果

SELECT TOP 2 * FROM Persons

在MYSQL中，用LIMIT实现相同的功能

SELECT * FROM Persons LIMIT 2

（2）LIKE在WHERE字句中搜索自定模式

SELECT * FROM Persons WHERE City LIKE 'N%'

SELECT * FROM Persons WHERE City NOT LIKE '%lon%'

SELECT * FROM Persons WHERE FirstName LIKE '_eorge'

SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'

SELECT * FROM Persons WHERE City LIKE '[ALN]%'

SELECT * FROM Persons WHERE City LIKE '[!ALN]%'

（3）IN操作符在WHERE字句中指定多个值

SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

SELECT * FROM Persons WHERE LastName NOT IN ('Adams','Carter')

（4）BETWEEN操作符选取介于两个值之间的数据范围，可以是数值、文本或日期。

SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'

SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'

（5）Alias别名

SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'

SELECT LastName AS Family, FirstName AS Name FROM Persons

下一次写join连接

注：摘自 W3CSchool