SQL的最完全语法介绍
<P>Select用途：
从指定表中取出指定的列的数据</P>
<P>语法：
SELECT column_name(s) FROM table_name
解释：
从数据库中选取资料列，并允许从一或多个资料表中，选取一或多个资料列或资料行。SELECT 陈述式的完整语法相当复杂，但主要子句可摘要为：</P>
<P>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 ] ] </P>
<P>例：
"Persons" 表中的数据有
</P>
<P>LastName
FirstName
Address
City
Hansen
Ola
Timoteivn 10
Sandnes
Svendson
Tove
Borgvn 23
Sandnes
Pettersen
Kari
Storgt 20
Stavanger
</P>
<P>选出字段名" LastName"、" FirstName" 的数据</P>
<P>SELECT LastName,FirstName FROM Persons
返回结果:
</P>
<P>LastName
FirstName
Hansen
Ola
Svendson
Tove
Pettersen
Kari
</P>
<P>选出所有字段的数据</P>
<P>SELECT * FROM Persons
返回结果:
</P>
<P>
</P>
<P>LastName
FirstName
Address
City
Hansen
Ola
Timoteivn 10
Sandnes
Svendson
Tove
Borgvn 23
Sandnes
Pettersen
Kari
Storgt 20
Stavanger </P> <P>Where
</P><P>用途：
</P><P>被用来规定一种选择查询的标准
</P><P>语法：
</P><P>SELECT column FROM table WHERE column condition value
下面的操作符能被使用在WHERE中：
</P><P>=,<>,>,<,>=,<=,BETWEEN,LIKE
</P><P>注意： 在某些SQL的版本中不等号< >能被写作为!=
</P><P>解释：
</P><P> SELECT语句返回WHERE子句中条件为true的数据
</P><P>例：
</P><P>从" Persons"表中选出生活在" Sandnes" 的人
</P><P>SELECT * FROM Persons WHERE City='Sandnes'
"Persons" 表中的数据有:
</P><P>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
返回结果:
</P><P>LastName
FirstName
Address
City
Year
Hansen
Ola
Timoteivn 10
Sandnes
1951
Svendson
Tove
Borgvn 23
Sandnes
1978
Svendson
Stale
Kaivn 18
Sandnes
1980
</P> <P>And & Or
</P><P>用途：
</P><P>在WHERE子句中AND和OR被用来连接两个或者更多的条件
</P><P>解释：
</P><P>AND在结合两个布尔表达式时，只有在两个表达式都为 TRUE 时才传回 TRUE
</P><P> OR在结合两个布尔表达式时，只要其中一个条件为 TRUE 时，OR便传回 TRUE
</P><P>例：
</P><P> "Persons" 表中的原始数据:
</P><P>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"的数据
</P><P>SELECT * FROM Persons
WHERE FirstName='Tove'
AND LastName='Svendson'
返回结果:
</P><P>LastName
FirstName
Address
City
Svendson
Tove
Borgvn 23
Sandnes
用OR运算子来查找"Persons" 表中FirstName为"Tove"或者LastName为" Svendson"的数据
</P><P>SELECT * FROM Persons
WHERE firstname='Tove'
OR lastname='Svendson'
返回结果:
</P><P>LastName
FirstName
Address
City
Svendson
Tove
Borgvn 23
Sandnes
Svendson
Stephen
Kaivn 18
Sandnes
你也能结合AND和OR (使用括号形成复杂的表达式),如:
</P><P>SELECT * FROM Persons WHERE
(FirstName='Tove' OR FirstName='Stephen')
AND LastName='Svendson'
返回结果:
</P><P>LastName
FirstName
Address
City
Svendson
Tove
Borgvn 23
Sandnes
Svendson
Stephen
Kaivn 18
Sandnes
</P> <P>Between...And
用途：
</P><P> 指定需返回数据的范围</P><P>语法：
</P><P>SELECT column_name FROM table_name
WHERE column_name
BETWEEN value1 AND value2
例：
</P><P>"Persons"表中的原始数据
</P><P>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"的数据：
</P><P>SELECT * FROM Persons WHERE LastName
BETWEEN 'Hansen' AND 'Pettersen'
</P><P>返回结果:</P><P>LastName
FirstName
Address
City
Hansen
Ola
Timoteivn 10
Sandnes
Nordmann
Anna
Neset 18
Sandnes
Pettersen
Kari
Storgt 20
Stavanger
</P><P>为了显示指定范围之外的数据，也可以用NOT操作符：</P><P>SELECT * FROM Persons WHERE LastName
NOT BETWEEN 'Hansen' AND 'Pettersen'
</P><P>返回结果:</P><P>LastName
FirstName
Address
City
Svendson
Tove
Borgvn 23
Sandnes </P> <P>Distinct
用途：
</P><P> DISTINCT关键字被用作返回唯一的值</P><P>语法：
</P><P>SELECT DISTINCT column-name(s) FROM table-name
解释：
</P><P> 当column-name(s)中存在重复的值时，返回结果仅留下一个</P><P>例：
</P><P>"Orders"表中的原始数据
</P><P>Company
OrderNumber
Sega
3412
W3Schools
2312
Trio
4678
W3Schools
6798
</P><P>用DISTINCT关键字返回Company字段中唯一的值：</P><P>SELECT DISTINCT Company FROM Orders
</P><P>返回结果:</P><P>Company
Sega
W3Schools
Trio
</P> <P>Order by
用途：
</P><P>指定结果集的排序</P><P>语法：
</P><P>SELECT column-name(s) FROM table-name ORDER BY { order_by_expression [ ASC | DESC ] }
解释：
</P><P> 指定结果集的排序，可以按照ASC(递增方式排序，从最低值到最高值)或者DESC(递减方式排序，从最高值到最低值)的方式进行排序，默认的方式是ASC</P><P>例：
</P><P>"Orders"表中的原始数据:
</P><P>Company
OrderNumber
Sega
3412
ABC Shop
5678
W3Schools
2312
W3Schools
6798
</P><P>按照Company字段的升序方式返回结果集：</P><P>SELECT Company, OrderNumber FROM Orders
ORDER BY Company
返回结果:
</P><P>Company
OrderNumber
ABC Shop
5678
Sega
3412
W3Schools
6798
W3Schools
2312
</P><P>按照Company字段的降序方式返回结果集：</P><P>SELECT Company, OrderNumber FROM Orders
ORDER BY Company DESC
</P><P>返回结果:</P><P>Company
OrderNumber
W3Schools
6798
W3Schools
2312
Sega
3412
ABC Shop
5678
</P> <P>Group by
用途：
</P><P>对结果集进行分组，常与汇总函数一起使用。</P><P>语法：
</P><P>SELECT column,SUM(column) FROM table GROUP BY column
例：
</P><P>"Sales"表中的原始数据:
</P><P>Company
Amount
W3Schools
5500
IBM
4500
W3Schools
7100
</P><P>按照Company字段进行分组，求出每个Company的Amout的合计：</P><P>SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company
返回结果：
</P><P>Company
SUM(Amount)
W3Schools
12600
IBM
4500 </P> <P>Having
用途：
</P><P>指定群组或汇总的搜寻条件。</P><P>语法：
</P><P>SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value
解释：
</P><P>HAVING 通常与 GROUP BY 子句同时使用。不使用 GROUP BY 时，HAVING 则与 WHERE 子句功能相似。
</P><P>例：
</P><P>"Sales"表中的原始数据:
</P><P>Company
Amount
W3Schools
5500
IBM
4500
W3Schools
7100
</P><P>按照Company字段进行分组，求出每个Company的Amout的合计在10000以上的数据：</P><P>SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company HAVING SUM(Amount)>10000
</P><P>返回结果：</P><P>Company
SUM(Amount)
W3Schools
12600
</P><P>
</P><P>Join
用途：
</P><P>当你要从两个或者以上的表中选取结果集时，你就会用到JOIN。</P><P>例：
</P><P>"Employees"表中的数据如下，（其中ID为主键）:
</P><P>ID
Name
01
Hansen, Ola
02
Svendson, Tove
03
Svendson, Stephen
04
Pettersen, Kari
"Orders"表中的数据如下：
</P><P>ID
Product
01
Printer
03
Table
03
Chair
</P><P>用Employees的ID和Orders的ID相关联选取数据：</P><P>SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.ID = Orders.ID
返回结果：
</P><P>Name
Product
Hansen, Ola
Printer
Svendson, Stephen
Table
Svendson, Stephen
Chair
</P><P>或者你也可以用JOIN关键字来完成上面的操作：</P><P>SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.ID = Orders.ID
</P> INNER JOIN的语法：
<P>SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
解释：
</P><P> INNER JOIN返回的结果集是两个表中所有相匹配的数据。</P><P>
</P><P>LEFT JOIN的语法：
</P><P>SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
</P><P>用"Employees"表去左外联结"Orders"表去找出相关数据：</P><P>SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.ID = Orders.ID
</P><P>返回结果：</P><P>Name
Product
Hansen, Ola
Printer
Svendson, Tove
Svendson, Stephen
Table
Svendson, Stephen
Chair
Pettersen, Kari
解释：
</P><P> LEFT JOIN返回"first_table"中所有的行尽管在" second_table"中没有相匹配的数据。</P><P>
</P><P>RIGHT JOIN的语法：</P><P>SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield
</P><P>用"Employees"表去右外联结"Orders"表去找出相关数据：</P><P>SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.ID = Orders.ID
</P><P>返回结果：</P><P>Name
Product
Hansen, Ola
Printer
Svendson, Stephen
Table
Svendson, Stephen
Chair
解释：
</P><P> RIGHT JOIN返回" second_table"中所有的行尽管在"first_table"中没有相匹配的数据。</P><P>
</P> <P>Alias
用途：
</P><P>可用在表、结果集或者列上，为它们取一个逻辑名称</P><P>语法：
</P><P>给列取别名：</P><P>SELECT column AS column_alias FROM table
</P><P> 给表取别名：</P><P>SELECT column FROM table AS table_alias
例：
</P><P>"Persons"表中的原始数据:
</P><P>LastName
FirstName
Address
City
Hansen
Ola
Timoteivn 10
Sandnes
Svendson
Tove
Borgvn 23
Sandnes
Pettersen
Kari
Storgt 20
Stavanger
</P><P>运行下面的SQL：</P><P>SELECT LastName AS Family, FirstName AS Name
FROM Persons
</P><P>返回结果：</P><P>Family
Name
Hansen
Ola
Svendson
Tove
Pettersen
Kari
</P><P>运行下面的SQL：</P><P>SELECT LastName, FirstName
FROM Persons AS Employees
返回结果：
</P><P>Employees中的数据有：</P><P>LastName
FirstName
Hansen
Ola
Svendson
Tove
Pettersen
Kari
</P>