学习SQL系列之查询数据(一)

查询数据

使用SELECT语句查询单列数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT prod_name FROM Products;

+---------------------+
| prod_name |
+---------------------+
| Fish bean bag toy |
| Bird bean bag toy |
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
| 18 inch teddy bear |
| Raggedy Ann |
| King doll |
| Queen doll |
+---------------------+

使用SELECT语句查询多列数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT prod_id, prod_name FROM Products;

+---------+---------------------+
| prod_id | prod_name |
+---------+---------------------+
| BNBG01 | Fish bean bag toy |
| BNBG02 | Bird bean bag toy |
| BNBG03 | Rabbit bean bag toy |
| BR01 | 8 inch teddy bear |
| BR02 | 12 inch teddy bear |
| BR03 | 18 inch teddy bear |
| RGAN01 | Raggedy Ann |
| RYL01 | King doll |
| RYL02 | Queen doll |
+---------+---------------------+

使用SELECT语句查询所有的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT* FROM Products;

+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+
| BNBG01 | DLL01 | Fish bean bag toy | 3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02 | DLL01 | Bird bean bag toy | 3.49 | Bird bean bag toy, eggs are not included |
| BNBG03 | DLL01 | Rabbit bean bag toy | 3.49 | Rabbit bean bag toy, comes with bean bag carrots |
| BR01 | BRS01 | 8 inch teddy bear | 5.99 | 8 inch teddy bear, comes with cap and jacket |
| BR02 | BRS01 | 12 inch teddy bear | 8.99 | 12 inch teddy bear, comes with cap and jacket |
| BR03 | BRS01 | 18 inch teddy bear | 11.99 | 18 inch teddy bear, comes with cap and jacket |
| RGAN01 | DLL01 | Raggedy Ann | 4.99 | 18 inch Raggedy Ann doll |
| RYL01 | FNG01 | King doll | 9.49 | 12 inch king doll with royal garments and crown |
| RYL02 | FNG01 | Queen doll | 9.49 | 12 inch queen doll with royal garments and crown |
+---------+---------+---------------------+------------+-----------------------------------------------------------------------+

使用SELECT语句查询去除重复数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT vend_id FROM Products;

+---------+
| vend_id |
+---------+
| BRS01 |
| BRS01 |
| BRS01 |
| DLL01 |
| DLL01 |
| DLL01 |
| DLL01 |
| FNG01 |
| FNG01 |
+---------+

SELECT DISTINCT vend_id FROM Products;

+---------+
| vend_id |
+---------+
| BRS01 |
| DLL01 |
| FNG01 |
+---------+

指定查询结果的数目

1
2
3
4
5
6
7
8
9
10
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;

+--------------------+
| prod_name |
+--------------------+
| 18 inch teddy bear |
| Raggedy Ann |
| King doll |
| Queen doll |
+--------------------+

注意:

  • LIMIT只适用MySQL, MariaDB, PostgreSQL and SQLite
  • Microsoft SQL Server和Microsoft Access 使用 TOP
  • DB2使用FIRST

查询数据并排序

ORDER BY是用来对查询结果排序的语句

基于单列的排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT prod_name FROM Products ORDER BY prod_name;

+---------------------+
| prod_name |
+---------------------+
| 12 inch teddy bear |
| 18 inch teddy bear |
| 8 inch teddy bear |
| Bird bean bag toy |
| Fish bean bag toy |
| King doll |
| Queen doll |
| Rabbit bean bag toy |
| Raggedy Ann |
+---------------------+

基于多列的排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;

+---------+------------+---------------------+
| prod_id | prod_price | prod_name |
+---------+------------+---------------------+
| BNBG02 | 3.49 | Bird bean bag toy |
| BNBG01 | 3.49 | Fish bean bag toy |
| BNBG03 | 3.49 | Rabbit bean bag toy |
| RGAN01 | 4.99 | Raggedy Ann |
| BR01 | 5.99 | 8 inch teddy bear |
| BR02 | 8.99 | 12 inch teddy bear |
| RYL01 | 9.49 | King doll |
| RYL02 | 9.49 | Queen doll |
| BR03 | 11.99 | 18 inch teddy bear |
+---------+------------+---------------------+

基于查询列的位置排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;

+---------+------------+---------------------+
| prod_id | prod_price | prod_name |
+---------+------------+---------------------+
| BNBG02 | 3.49 | Bird bean bag toy |
| BNBG01 | 3.49 | Fish bean bag toy |
| BNBG03 | 3.49 | Rabbit bean bag toy |
| RGAN01 | 4.99 | Raggedy Ann |
| BR01 | 5.99 | 8 inch teddy bear |
| BR02 | 8.99 | 12 inch teddy bear |
| RYL01 | 9.49 | King doll |
| RYL02 | 9.49 | Queen doll |
| BR03 | 11.99 | 18 inch teddy bear |
+---------+------------+---------------------+

指定排序的方向

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;

+---------+------------+---------------------+
| prod_id | prod_price | prod_name |
+---------+------------+---------------------+
| BR03 | 11.99 | 18 inch teddy bear |
| RYL01 | 9.49 | King doll |
| RYL02 | 9.49 | Queen doll |
| BR02 | 8.99 | 12 inch teddy bear |
| BR01 | 5.99 | 8 inch teddy bear |
| RGAN01 | 4.99 | Raggedy Ann |
| BNBG01 | 3.49 | Fish bean bag toy |
| BNBG02 | 3.49 | Bird bean bag toy |
| BNBG03 | 3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+

SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;

+---------+------------+---------------------+
| prod_id | prod_price | prod_name |
+---------+------------+---------------------+
| BR03 | 11.99 | 18 inch teddy bear |
| RYL01 | 9.49 | King doll |
| RYL02 | 9.49 | Queen doll |
| BR02 | 8.99 | 12 inch teddy bear |
| BR01 | 5.99 | 8 inch teddy bear |
| RGAN01 | 4.99 | Raggedy Ann |
| BNBG02 | 3.49 | Bird bean bag toy |
| BNBG01 | 3.49 | Fish bean bag toy |
| BNBG03 | 3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+

查询数据并过滤

WHERE是用来在查询时指定过滤的条件的语句

WHERE语句的操作符

  • =:等于
  • <>:不等于
  • !=:不等于
  • <:小于
  • <=:小于等于
  • !<:不小于
  • >:大于
  • >=:大于等于
  • !>:不大于
  • BETWEEN:在两个指定值之间包括指定值
  • IS NULL:是空值

使用WHERE条件语句

1
2
3
4
5
6
7
8
9
SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;

+---------------------+------------+
| prod_name | prod_price |
+---------------------+------------+
| Fish bean bag toy | 3.49 |
| Bird bean bag toy | 3.49 |
| Rabbit bean bag toy | 3.49 |
+---------------------+------------+

多个条件语句

AND

1
2
3
4
5
6
7
8
9
SELECT prod_id, prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;

+---------+---------------------+------------+
| prod_id | prod_name | prod_price |
+---------+---------------------+------------+
| BNBG01 | Fish bean bag toy | 3.49 |
| BNBG02 | Bird bean bag toy | 3.49 |
| BNBG03 | Rabbit bean bag toy | 3.49 |
+---------+---------------------+------------+

OR

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT prod_id, prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

+---------+---------------------+------------+
| prod_id | prod_name | prod_price |
+---------+---------------------+------------+
| BNBG01 | Fish bean bag toy | 3.49 |
| BNBG02 | Bird bean bag toy | 3.49 |
| BNBG03 | Rabbit bean bag toy | 3.49 |
| BR01 | 8 inch teddy bear | 5.99 |
| BR02 | 12 inch teddy bear | 8.99 |
| BR03 | 18 inch teddy bear | 11.99 |
| RGAN01 | Raggedy Ann | 4.99 |
+---------+---------------------+------------+

使用IN操作符

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01', 'BRS01');

+---------------------+------------+
| prod_name | prod_price |
+---------------------+------------+
| Fish bean bag toy | 3.49 |
| Bird bean bag toy | 3.49 |
| Rabbit bean bag toy | 3.49 |
| 8 inch teddy bear | 5.99 |
| 12 inch teddy bear | 8.99 |
| 18 inch teddy bear | 11.99 |
| Raggedy Ann | 4.99 |
+---------------------+------------+

使用NOT操作符

1
2
3
4
5
6
7
8
9
10
11
SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01';

+--------------------+
| prod_name |
+--------------------+
| 8 inch teddy bear |
| 12 inch teddy bear |
| 18 inch teddy bear |
| King doll |
| Queen doll |
+--------------------+

使用LIKE操作符和通配符

通配符%表示在指定位置有零到多个字符 (注:Microsoft Access 使用*)

1
2
3
4
5
6
7
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'FISH%';

+---------+-------------------+
| prod_id | prod_name |
+---------+-------------------+
| BNBG01 | Fish bean bag toy |
+---------+-------------------+
1
... WHERE email LIKE 'b%@gmail.com';

注意:

  • 通配符%只能用于过滤字符类型的数据
  • 通配符%不能匹配NULL值,即使是LIKE '%'

通配符_表示在指定位置有一个字符 (注:DB2不支持_, Microsoft Access 使用?)

1
2
3
4
5
6
7
8
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';

+---------+--------------------+
| prod_id | prod_name |
+---------+--------------------+
| BR02 | 12 inch teddy bear |
| BR03 | 18 inch teddy bear |
+---------+--------------------+

总结:

  • 使用通配符的查询比一般查询需要花费更多时间,尽量避免使用通配符
  • 如果必须使用通配符,尽量不要在最开始的地方就使用通配符,这样的查询最慢
  • 注意通配符的位置,否则可能返回的不是你想要的数据

说明

  • 本文所有例子都来自SQL IN 10 MINUTES
  • 本文的所有例子都是基于MySQL,其它数据库可能会有微小差别,具体请查阅特定数据库的文档