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

创建计算字段(aculated Fields)

在查询数据时,有时候我们需要的数据,数据库里面没有哪一列数据正好匹配。比如说我需要查询出来的数据是详细地址,而数据库里面只有City, State, ZIP的列。这时候怎么办?当然我们可以把这些相关数据都查询出来, 然后再组合。其实数据库本身就提供了这个功能,并且效率更高,它就是计算属性(Caculated Fields)

连接字段(Concatenating Fields)

1
2
3
4
5
6
7
8
9
10
11
12
SELECT Concat(vend_name, ' (', vend_country, ')') FROM Vendors ORDER BY vend_name;

+--------------------------------------------+
| Concat(vend_name, ' (', vend_country, ')') |
+--------------------------------------------+
| Bear Emporium (USA) |
| Bears R Us (USA) |
| Doll House Inc. (USA) |
| Fun and Games (England) |
| Furball Inc. (USA) |
| Jouets et ours (France) |
+--------------------------------------------+

注:不同的数据库实现连接字段的方式不一样,有的使用+, 还有的使用||,具体请查阅相应数据库的文档

给计算字段赋个名字

1
2
3
4
5
6
7
8
9
10
11
12
SELECT Concat(vend_name, ' (', vend_country, ')') AS vend_title  FROM Vendors ORDER BY vend_name;

+-------------------------+
| vend_title |
+-------------------------+
| Bear Emporium (USA) |
| Bears R Us (USA) |
| Doll House Inc. (USA) |
| Fun and Games (England) |
| Furball Inc. (USA) |
| Jouets et ours (France) |
+-------------------------+

做计算操作 (+, -, *, /)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT prod_id, quantity, item_price FROM OrderItems WHERE order_num = 20008;

+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| RGAN01 | 5 | 4.99 |
| BR03 | 5 | 11.99 |
| BNBG01 | 10 | 3.49 |
| BNBG02 | 10 | 3.49 |
| BNBG03 | 10 | 3.49 |
+---------+----------+------------+

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;

+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| RGAN01 | 5 | 4.99 | 24.95 |
| BR03 | 5 | 11.99 | 59.95 |
| BNBG01 | 10 | 3.49 | 34.90 |
| BNBG02 | 10 | 3.49 | 34.90 |
| BNBG03 | 10 | 3.49 | 34.90 |
+---------+----------+------------+----------------+

数据库操作函数(Manipulation Function)

数据库一般都支持使用函数(Function)来操作数据,实现同样功能的函数基本上每个数据库都用,但是它们的名字和语法可能会有很大区别。比如像MySQLSUBSTRING函数是用来截取字符串的一部分的函数,但是在DB2和SQLite中实现同样功能的是SUBSTR

大部分数据库都支持以下四类函数:

操作文本类函数

  • SUBSTRING()/SUBSTR()/MID()/LEFT()/RIGHT(): 截取字符串一部分函数
  • LENGTH()/DATALENGTH()/LEN(): 返回字符串长度
  • LOWER()/LCASE(): 字符串全部小写
  • UPPER()/UCASE(): 字符串全部大写
  • LTRIM(): 去掉开头的所有空格
  • RTRIM(): 去掉结尾所有空格
  • SOUNDEX(): 查询出发音相同的结果
1
2
3
4
5
6
7
8
9
10
11
12
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name;

+-----------------+------------------+
| vend_name | vend_name_upcase |
+-----------------+------------------+
| Bear Emporium | BEAR EMPORIUM |
| Bears R Us | BEARS R US |
| Doll House Inc. | DOLL HOUSE INC. |
| Fun and Games | FUN AND GAMES |
| Furball Inc. | FURBALL INC. |
| Jouets et ours | JOUETS ET OURS |
+-----------------+------------------+
1
2
3
4
5
6
7
8
9
10
11
SELECT cust_name, cust_contact FROM Customers WHERE cust_contact = 'Michael Green';

Empty set (0.00 sec)

SELECT cust_name, cust_contact FROM Customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

+------------+----------------+
| cust_name | cust_contact |
+------------+----------------+
| Kids Place | Michelle Green |
+------------+----------------+

操作数字类函数

  • ABS(): 返回数字的绝对值
  • COS(): 返回指定角度的三角余弦
  • SIN(): 返回指定角度的三角正弦
  • TAN(): 返回指定角度的三角正切
  • EXP(): 返回指定数字的指数值
  • PI(): 返回PI的值
  • SQRT(): 返回指定数字的平方根

操作时间类函数

各个数据库对操作时间的函数的命名和语法差别很大,具体请查阅相关数据库的文档。下面举一个MySQL的例子

1
2
3
4
5
6
7
8
9
10
11
SELECT order_num, order_date  FROM Orders WHERE YEAR(order_date) = 2012;

+-----------+---------------------+
| order_num | order_date |
+-----------+---------------------+
| 20005 | 2012-05-01 00:00:00 |
| 20006 | 2012-01-12 00:00:00 |
| 20007 | 2012-01-30 00:00:00 |
| 20008 | 2012-02-03 00:00:00 |
| 20009 | 2012-02-08 00:00:00 |
+-----------+---------------------+

系统信息类函数

系统信息类函数主要是返回一些跟你正在使用的数据库相关的信息,比如返回用户登录信息

数据库聚合函数(Aggregate Function)

聚合函数就是对一组数据进行计算并返回单个值得函数

聚合函数有:

  • AVG():返回一列的平均值
  • COUNT():返回查询出的某列的个数
  • MAX():返回某列数据的最大值
  • MIN():返回某列数据的最小值
  • SUM():返回某列数据的总和

AVG()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT AVG(prod_price) AS avg_price FROM Products;

+-----------+
| avg_price |
+-----------+
| 6.823333 |
+-----------+

SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

+-----------+
| avg_price |
+-----------+
| 3.865000 |
+-----------+

SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

+-----------+
| avg_price |
+-----------+
| 4.240000 |
+-----------+

注意:

  • 在计算平均值的时候,NULL值得数据将被忽略掉

COUNT()

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
SELECT cust_id, cust_email FROM Customers;

+------------+-----------------------+
| cust_id | cust_email |
+------------+-----------------------+
| 1000000001 | sales@villagetoys.com |
| 1000000002 | NULL |
| 1000000003 | jjones@fun4all.com |
| 1000000004 | dstephens@fun4all.com |
| 1000000005 | NULL |
+------------+-----------------------+

SELECT COUNT(*) AS num_cust FROM Customers;

+----------+
| num_cust |
+----------+
| 5 |
+----------+

SELECT COUNT(cust_email) AS num_cust FROM Customers;

+----------+
| num_cust |
+----------+
| 3 |
+----------+

注意:

  • COUNT(*)将会把所有数据都计算在内
  • COUNT(column)将计算column不为NULL的所有数据

MAX() AND MIN()

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
32
33
34
35
36
37
38
39
SELECT prod_id, prod_price FROM Products;

+---------+------------+
| prod_id | prod_price |
+---------+------------+
| BNBG01 | 3.49 |
| BNBG02 | 3.49 |
| BNBG03 | 3.49 |
| BR01 | 5.99 |
| BR02 | 8.99 |
| BR03 | 11.99 |
| RGAN01 | 4.99 |
| RYL01 | 9.49 |
| RYL02 | 9.49 |
+---------+------------+

SELECT MAX(prod_price) AS max_price FROM Products;

+-----------+
| max_price |
+-----------+
| 11.99 |
+-----------+

SELECT MIN(prod_price) AS min_price FROM Products;

+-----------+
| min_price |
+-----------+
| 3.49 |
+-----------+

SELECT COUNT(*) AS num_items, MIN(prod_price) AS min_price, MAX(prod_price) AS max_price, AVG(prod_price) AS price_avg FROM Products;

+-----------+-----------+-----------+-----------+
| num_items | min_price | max_price | price_avg |
+-----------+-----------+-----------+-----------+
| 9 | 3.49 | 11.99 | 6.823333 |
+-----------+-----------+-----------+-----------+

注意:

  • 在计算最大值和最小值的时候,NULL将会被忽略

SUM()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;

+---------------+
| items_ordered |
+---------------+
| 200 |
+---------------+

SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;

+-------------+
| total_price |
+-------------+
| 1648.00 |
+-------------+

SELECT SUM(quantity) AS items_ordered, SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;

+---------------+-------------+
| items_ordered | total_price |
+---------------+-------------+
| 200 | 1648.00 |
+---------------+-------------+

数据分组

使用分组

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 vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01 | 3 |
| DLL01 | 4 |
| FNG01 | 2 |
+---------+-----------+

过滤分组

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cust_id;

+------------+--------+
| cust_id | orders |
+------------+--------+
| 1000000001 | 2 |
| 1000000003 | 1 |
| 1000000004 | 1 |
| 1000000005 | 1 |
+------------+--------+

SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*) >= 2;

+------------+--------+
| cust_id | orders |
+------------+--------+
| 1000000001 | 2 |
+------------+--------+

WHEREHAVING

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 vend_id, prod_price FROM Products WHERE prod_price >= 4;

+---------+------------+
| vend_id | prod_price |
+---------+------------+
| BRS01 | 5.99 |
| BRS01 | 8.99 |
| BRS01 | 11.99 |
| DLL01 | 4.99 |
| FNG01 | 9.49 |
| FNG01 | 9.49 |
+---------+------------+

SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01 | 3 |
| DLL01 | 1 |
| FNG01 | 2 |
+---------+-----------+

SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01 | 3 |
| FNG01 | 2 |
+---------+-----------+

HAVINGWHERE一样都是用来过滤数据的,唯一不用就是WHERE是用来过滤行数据,而HAVING是用来过滤分组数据的

分组和排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3;

+-----------+-------+
| order_num | items |
+-----------+-------+
| 20006 | 3 |
| 20007 | 5 |
| 20008 | 5 |
| 20009 | 3 |
+-----------+-------+

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;

+-----------+-------+
| order_num | items |
+-----------+-------+
| 20006 | 3 |
| 20009 | 3 |
| 20007 | 5 |
| 20008 | 5 |
+-----------+-------+

说明

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