SQL语法手册
Select
用途：
从指定表中取出指定的列的数据
语法：
SELECT column_name(s) FROM table_name |
解释：
从数据库中选取资料列，并允许从一或多个资料表中，选取一或多个资料列或资料行。SELECT 陈述式的完整语法相当复杂，但主要子句可摘要为：
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
例：
“Persons” 表中的数据有
|
LastName
|
FirstName
|
Address
|
City
|
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
选出字段名” LastName”、” FirstName” 的数据
SELECT LastName,FirstName FROM Persons |
返回结果:
|
LastName
|
FirstName
|
|
Hansen
|
Ola
|
|
Svendson
|
Tove
|
|
Pettersen
|
Kari
|
选出所有字段的数据
SELECT * FROM Persons |
返回结果:
|
LastName
|
FirstName
|
Address
|
City
|
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
Where
用途：
被用来规定一种选择查询的标准
语法：
SELECT column FROM table WHERE column condition value |
下面的操作符能被使用在WHERE中：
=,<>,>,<,>=,<=,BETWEEN,LIKE
注意： 在某些SQL的版本中不等号< >能被写作为!=
解释：
SELECT语句返回WHERE子句中条件为true的数据
例：
从” Persons”表中选出生活在” Sandnes” 的人
SELECT * FROM Persons WHERE City= Sandnes |
"Persons" 表中的数据有:
|
LastName
|
FirstName
|
Address
|
City
|
Year
|
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
1951
|
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
1978
|
|
Svendson
|
Stale
|
Kaivn 18
|
Sandnes
|
1980
|
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
1960
|
返回结果:
|
LastName
|
FirstName
|
Address
|
City
|
Year
|
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
1951
|
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
1978
|
|
Svendson
|
Stale
|
Kaivn 18
|
Sandnes
|
1980
|
And & Or
用途：
在WHERE子句中AND和OR被用来连接两个或者更多的条件
解释：
AND在结合两个布尔表达式时，只有在两个表达式都为 TRUE 时才传回 TRUE
OR在结合两个布尔表达式时，只要其中一个条件为 TRUE 时，OR便传回 TRUE
例：
"Persons" 表中的原始数据:
|
LastName
|
FirstName
|
Address
|
City
|
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
|
Svendson
|
Stephen
|
Kaivn 18
|
Sandnes
|
用AND运算子来查找"Persons" 表中FirstName为”Tove”而且LastName为” Svendson”的数据
SELECT * FROM Persons WHERE FirstName= Tove AND LastName= Svendson |
返回结果:
|
LastName
|
FirstName
|
Address
|
City
|
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
用OR运算子来查找"Persons" 表中FirstName为”Tove”或者LastName为” Svendson”的数据
SELECT * FROM Persons WHERE firstname= Tove OR lastname= Svendson |
返回结果:
|
LastName
|
FirstName
|
Address
|
City
|
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
|
Svendson
|
Stephen
|
Kaivn 18
|
Sandnes
|
你也能结合AND和OR (使用括号形成复杂的表达式),如:
SELECT * FROM Persons WHERE (FirstName= Tove OR FirstName= Stephen ) AND LastName= Svendson |
返回结果:
|
LastName
|
FirstName
|
Address
|
City
|
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
|
Svendson
|
Stephen
|
Kaivn 18
|
Sandnes
|
Between…And
用途：
指定需返回数据的范围
语法：
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 |
例：
“Persons”表中的原始数据
|
LastName
|
FirstName
|
Address
|
City
|
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
|
Nordmann
|
Anna
|
Neset 18
|
Sandnes
|
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
用BETWEEN…AND返回LastName为从”Hansen”到”Pettersen”的数据：
SELECT * FROM Persons WHERE LastName BETWEEN Hansen AND Pettersen |
返回结果:
|
LastName
|
FirstName
|
Address
|
City
|
|
Hansen
|
Ola
|
Timoteivn 10
|
Sandnes
|
|
Nordmann
|
Anna
|
Neset 18
|
Sandnes
|
|
Pettersen
|
Kari
|
Storgt 20
|
Stavanger
|
为了显示指定范围之外的数据，也可以用NOT操作符：
SELECT * FROM Persons WHERE LastName NOT BETWEEN Hansen AND Pettersen |
返回结果:
|
LastName
|
FirstName
|
Address
|
City
|
|
Svendson
|
Tove
|
Borgvn 23
|
Sandnes
|
Distinct
用途：
DISTINCT关键字被用作返回唯一的值
语法：
SELECT DISTINCT column-name(s) FROM table-name |
解释：
当column-name(s)中存在重复的值时，返回结果仅留下一个
例：
“Orders”表中的原始数据
|
Company
|
OrderNumber
|
|
Sega
|
3412
|
|
W3Schools
|
2312
|
|
Trio
|
4678
|
|
W3Schools
|
6798
|
用DISTINCT关键字返回Company字段中唯一的值：
SELECT DISTINCT Company FROM Orders |
返回结果:
|
Company
|
|
Sega
|
|
W3Schools
|
|
Trio
|
Order by
用途：
指定结果集的排序
语法：
SELECT column-name(s) FROM table-name ORDER BY |
解释：
指定结果集的排序，可以按照ASC(递增方式排序，从最低值到最高值)或者DESC(递减方式排序，从最高值到最低值)的方式进行排序，默认的方式是ASC
例：
“Orders”表中的原始数据:
|
Company
|
OrderNumber
|
|
Sega
|
3412
|
|
ABC Shop
|
5678
|
|
W3Schools
|
2312
|
|
W3Schools
|
6798
|
按照Company字段的升序方式返回结果集：
SELECT Company, OrderNumber FROM Orders ORDER BY Company |
返回结果:
|
Company
|
OrderNumber
|
|
ABC Shop
|
5678
|
|
Sega
|
3412
|
|
W3Schools
|
6798
|
|
W3Schools
|
2312
|
按照Company字段的降序方式返回结果集：
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC |
返回结果:
|
Company
|
OrderNumber
|
|
W3Schools
|
6798
|
|
W3Schools
|
2312
|
|
Sega
|
3412
|
|
ABC Shop
|
5678
|
