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
=,,>,=,能被写作为!=
解释:
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 |