sql进阶使用
1.SQL TOP
TOP 子句用于规定要返回的记录的数目。对于拥有数千条记录的大型表来说,TOP 子句是非常有用的。
注释:并非所有的数据库系统都支持 TOP 子句。
- SQL Server 的语法:
SELECT TOP number|percent column_name(s) FROM table_name
MySQL 和 Oracle 中的 SQL SELECT TOP 是等价的
- MySQL 语法
SELECT column_name(s) FROM table_name LIMIT number
例子:
SELECT * FROM Persons LIMIT 5
- Oracle 语法
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number
例子:
SELECT * FROM Persons WHERE ROWNUM <= 5
2.Like操作符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
- SQL LIKE 操作符语法
SELECT column_name(s) FROM table_name
WHERE column_name LIKE pattern
- LIKE 操作符实例
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford | London |
2 | Bush | George | Fifth | New York |
3 | Carter | Thomas | Changan | Beijing |
- 例子1:
现在,我们希望从上面的 "Persons" 表中选取居住在以 "N" 开始的城市里的人:
我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons WHERE City LIKE 'N%'
提示:"%" 可用于定义通配符(模式中缺少的字母)。
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
2 | Bush | George | Fifth | New York |
- 例子2:
接下来,我们希望从 "Persons" 表中选取居住在以 "g" 结尾的城市里的人:我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons WHERE City LIKE '%g'
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
3 | Carter | Thomas | Changan | Beijing |
- 例子 3:
接下来,我们希望从 "Persons" 表中选取居住在包含 "lon" 的城市里的人:我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons WHERE City LIKE '%lon%'
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford | London |
- 例子 4:
通过使用 NOT 关键字,我们可以从 "Persons" 表中选取居住在不包含 "lon" 的城市里的人:我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons WHERE City NOT LIKE '%lon%'
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
2 | Bush | George | Fifth | New York |
3 | Carter | Thomas | Changan | Beijing |
3.SQL 通配符
在搜索数据库中的数据时,您可以使用 SQL 通配符。
在搜索数据库中的数据时,SQL通配符可以替代一个或多个字符。SQL 通配符必须与 LIKE 运算符一起使用。在 SQL 中,可使用以下通配符:
通配符 | 描述 |
---|---|
% | 代表零个或多个字符 |
- | 仅替代一个字符 |
[charlist] | 字符列中的任何单一字符 |
[^charlist]或者[!charlist] | 不在字符列中的任何单一字符 |
原始的表 (用在例子中的):
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
- 例子1:
使用%通配符
现在,我们希望从上面的 "Persons" 表中选取居住在以 "Ne" 开始的城市里的人:我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons WHERE City LIKE 'Ne%'
- 例子 2:
接下来,我们希望从 "Persons" 表中选取居住在包含 "lond" 的城市里的人:
我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons WHERE City LIKE '%lond%'
- 例子3:
使用 _ 通配符;现在,我们希望从上面的 "Persons" 表中选取名字的第一个字符之后是 "eorge" 的人:
我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons WHERE FirstName LIKE '_eorge'
- 例子 4
接下来,我们希望从 "Persons"表中选取的这条记录的姓氏以 "C"开头,然后是一个任意字符,然后是"r",然后是一个任意字符,然后是 "er":
SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'
- 例子5
使用 [charlist] 通配符
现在,我们希望从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人:
我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons WHERE City LIKE '[ALN]%'
- 例子 6
现在,我们希望从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人:
我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons WHERE City LIKE '[!ALN]%'
4.SQL IN 操作符
IN 操作符允许我们在 WHERE 子句中规定多个值。
SQL IN 语法
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
- IN 操作符实例
现在,我们希望从上表中选取姓氏为 Adams 和 Carter 的人:
我们可以使用下面的 SELECT 语句:
SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')
5.BETWEEN 操作符
BETWEEN 操作符在 WHERE 子句中使用,作用是选取介于两个值之间的数据范围。
操作符 BETWEEN ... AND会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
- SQL BETWEEN 语法
SELECT column_name(s) FROM table_name WHERE column_name
BETWEEN value1 AND value2
- 1.BETWEEN 操作符实例
如需以字母顺序显示介于 "Adams"(包括)和 "Carter"(不包括)之间的人,请使用下面的 SQL:
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter'
重要事项:不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。某些数据库会列出介于 "Adams" 和 "Carter" 之间的人,但不包括 "Adams" 和 "Carter" ;某些数据库会列出介于 "Adams" 和 "Carter" 之间并包括 "Adams" 和 "Carter" 的人;而另一些数据库会列出介于 "Adams" 和 "Carter" 之间的人,包括 "Adams" ,但不包括 "Carter" 。
所以,请检查你的数据库是如何处理 BETWEEN....AND 操作符的!
- 实例2
如需使用上面的例子显示范围之外的人,请使用 NOT 操作符:
SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Adams' AND 'Carter'
- SQL Alias(别名)
通过使用 SQL,可以为列名称和表名称指定别名(Alias)。
表的 SQL Alias 语法
SELECT column_name(s) FROM table_name AS alias_name
列的 SQL Alias 语法
SELECT column_name AS alias_name FROM table_name
- Alias 实例: 使用表名称别名
假设我们有两个表分别是:"Persons" 和 "Product_Orders"。我们分别为它们指定别名 "p" 和 "po"。现在,我们希望列出 "John Adams" 的所有定单。
我们可以使用下面的 SELECT 语句:
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'
相当于下面不用别名的select语句
SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName
FROM Persons, Product_Orders
WHERE Persons.LastName='Adams' AND Persons.FirstName='John'
- Alias 实例: 使用一个列名别名
- SELECT LastName AS Family, FirstName AS Name
FROM Persons
结果:
Family | Name |
---|---|
x | x |
x | x |
x | x |
7.SQL JOIN
SQL join 用于根据两个或多个表中的列之间的关系,从这些表中查询数据。
Join 和 Key
有时为了得到完整的结果,我们需要从两个或更多的表中获取结果。我们就需要执行 join。
数据库中的表可通过键将彼此联系起来。主键(Primary Key)是一个列,在这个列中的每一行的值都是唯一的。在表中,每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下,把表间的数据交叉捆绑在一起。
引用两个表
我们可以通过引用两个表的方式,从两个表中获取数据:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P
- SQL JOIN - 使用 Join
除了上面的方法,我们也可以使用关键词 JOIN 来从两个表中获取数据。
如果我们希望列出所有人的定购,可以使用下面的 SELECT 语句:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
不同的 SQL JOIN
除了我们在上面的例子中使用的 INNER JOIN(内连接),我们还可以使用其他几种连接。
下面列出了您可以使用的JOIN类型,以及它们之间的差异。
- JOIN: 如果表中有至少一个匹配,则返回行
- LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
- RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
- FULL JOIN: 只要其中一个表中存在匹配,就返回行
8.SQL INNER JOIN 关键字
在表中存在至少一个匹配时,INNER JOIN 关键字返回行。
INNER JOIN 关键字语法
SELECT column_name(s) FROM table_name1 INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释:INNER JOIN 与 JOIN 是相同的。
- 内连接(INNER JOIN)实例
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
注: INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Persons" 中的行在 "Orders" 中没有匹配,就不会列出这些行。
9.SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
- LEFT JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释: 在某些数据库中, LEFT JOIN 称为 LEFT OUTER JOIN。
- 左连接(LEFT JOIN)实例
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
注意: LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行,即使在右表 (Orders) 中没有匹配的行。
10.SQL RIGHT JOIN 关键字
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。
- RIGHT JOIN 关键字语法
- SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
- 右连接(RIGHT JOIN)实例
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果如下
LastName | FirstName | OrderNo |
---|---|---|
Adams | x | 34764 |
Adams | x | 34764 |
Adams | x | 34764 |
Adams | x | 34764 |
34764 |
注意: RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行,即使在左表 (Persons) 中没有匹配的行。
11.SQL FULL JOIN 关键字
只要其中某个表存在匹配,FULL JOIN 关键字就会返回行。
- FULL JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释: 在某些数据库中, FULL JOIN 称为 FULL OUTER JOIN。
- 全连接(FULL JOIN)实例
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果如下:
LastName | FirstName | OrderNo |
---|---|---|
Adams | x | 34764 |
Adams | x | 34764 |
Adams | x | |
Adams | 34764 | |
34764 |
注意: FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配,或者如果 "Orders" 中的行在表 "Persons" 中没有匹配,这些行同样会列出。