Consolexin's blog
consolexinhun
2025-05-20
目录

3-数据处理、汇总和分组

# 创建计算字段

# 计算字段

应用程序需要的数据需要通过从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。

字段：基本上与列的意思相同，经常互换使用，不过数据库一般称为列，而属于字段通常用在计算字段的连接上。

# 拼接字段

拼接：将值联结到一起构成单个值

在SELECT语句中，可使用Concat()函数来拼接两个列。Concat()函数需要一个或多个指定的串，各个串之间用逗号分隔。

SELECT Concat(vend_name, ' (',vend_country,')') FROM vendors
ORDER BY vend_name;
#output
+-----------------------------------------+
| Concat(vendname,' (',vend_country,')') |
+-----------------------------------------+
| ACME (USA)                              |
| Anvils R Us (USA)                       |
| Furball Inc. (USA)                      |
| Jet Set (England)                       |
| Jouets Et Ours (France)                 |
| LT Supplies (USA)                       |
+-----------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13

使用 RTrim()函数可以删除右侧多余的空格来整理数据，例：

SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;
1
2
3
函数 说明
Trim() 去掉两边的空格
LTrim() 去掉左边的空格
RTrim() 去掉右边的空格

# 使用别名

拼接的结果只是一个值，未命名。可以用AS关键字赋予别名

常见的用途包括在实际的表列名包含不符合规定的字符(如空格)时重新命名它，在原来的名字含混或容易误解时扩充它等等。 别名有时也称为导出列（derived column）

SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
#output
+----------------------------+
| vend_name                  |
+----------------------------+
| ACME (USA)                 |
| Anvils R Us (USA)          |
| Furball Inc. (USA)         |
| Jet Set (England)          |
| Jouets Et Ours (France)    |
| LT Supplies (USA)          |
+----------------------------+
#指示SQL创建一个包含指定计算的名为vend_title的计算字段
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 执行算术计算

例：汇总物品的价格（单价乘以订购数量）

SELECT prod_id,
	   quantity,
       item_price,
       quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
#output
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
操作符 说明
+ 加
- 减
* 乘
/ 除

SELECT Now() 利用 Now()函数返回当前日期和时间

# 使用数据处理函数

函数没有SQL的可移植性强

# 使用函数

大多数SQL实现支持以下类型的函数

  • 用于处理文本串的文本函数
  • 在数值数据上进行算术操作的数值函数
  • 处理日期和时间值并从这些值中提取特定成分的日期和时间函数
  • 返回DBMS正是用的特殊信息的系统函数

# 文本处理函数

常用的文本处理函数

函数 说明
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转换为大写

SOUNDEX是一个将任何文本转换为描述其语音表示的字母数字模式的算法，使得能对串进行发音比较而不是字母比较。MySQL提供对SOUNDEX的支持。

例：联系人Y.Lie输入错误为Y.Lee，使用SOUNDEX检索，匹配发音类似于Y.Lie的联系名

SELECT cust_name, cust_contact FROM customers
WHERE Soundex(cust_contact）= Soundex('Y Lie');
#output
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+
1
2
3
4
5
6
7
8

# 日期和时间处理函数

函数 说明
AddDate() 增加一个日期（天、周等）
AddTime() 增加一个时间（时、分等）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期计算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分
# MySQL使用的日期格式

日期必须为格式yyyy-mm-dd 支持2位数字的年份，MySQL处理00-69为2000-2069，70-99为1970-1999，但使用4为数字年份更可靠。 例：

SELECT cust_id, order_num FROM orders
WHERE order_date = '2005-09-01';
1
2

order_date类型为datetime，样例表中的值全部具有时间值00：00：00，但是如果order_date的值为2005-09-01 11：30：05则上面的WHERE order_date = '2005-09-11'不会检索出这一行，因此必须使用Date()函数。

SELECT cust_id, order_num FROM orders
WHERE Date(order_date) = '2005-09-01';
1
2

例：检索出2005年9月下的所有订单

SELECT cust_id, order_num FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
#BETWEEN把2005-09-01和2005-09-30定义为一个要匹配的日期范围。
#另一种方法
SELECT cust_id, order_num FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
1
2
3
4
5
6

# 数值处理函数

函数 说明
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切

# 汇总数据

函数 说明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

# AVG()函数

例：返回products表中所有产品的平均价格

SELECT AVG(prod_price) AS avg_price FROM products;
1

例：返回特定供应商所提供产品的平均价格

SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
1
2
3

# COUNT()函数

例：返回customer表中客户的总数

SELECT COUNT(*) AS num_cust FROM customers;
1

例：只对具有电子邮件地址的客户计数

SELECT COUNT(cust_email) AS num_cust
FROM customers;
1
2

# MAX()函数

例：返回products表中最贵的物品价格

SELECT MAX(prod_price) AS max_price
FROM products;
1
2

对非数值数据使用MAX() MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。MAX()函数忽略列值为NULL的行。

# MIN()函数

例：

SELECT MIN(prod_price) AS min_price FROM products;
1

# SUM()函数

返回指定列值的和（总计） 例：检索所订购物品的总数

SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;
1
2
3

例：合计计算值，合计每项物品item_price*quantity，得出订单总金额

SELECT SUM(item_price*quantity) AS total_price
FORM orderitems
WHERE order_num = 20005;
1
2
3

# 聚集不同值（适用于5.0.3后的版本）

上述五个聚集函数都可以如下使用：

  • 对所有的行执行计算，指定ALL参数或不给参数（ALL为默认）
  • 只包含不同的值，指定DISTINCT参数

例：

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
1
2
3

注意：如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT）， 否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

# 组合聚集函数

SELECT语句可根据需要包含多个聚集函数

SELECT COUNT(*) AS num_items;
	   MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM products;
#output
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.50 | 16.133571 |
+-----------+-----------+-----------+-----------+
1
2
3
4
5
6
7
8
9
10
11

# 分组数据

# 创建分组

例：根据vend_id分组，对每个分组分别计算总数

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
#output
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12
  • GROUP BY 子句可以包含任意数目的列，使得能对分组进行嵌套，为数据分组提供更细致的控制
  • 如果GROUP BY子句中中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
  • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
  • 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
  • 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下所示：

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;
1
2
3

# 过滤分组

WHERE指定的是行，不是分组，WHERE没有分组的概念

使用HAVING过滤分组

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
#output
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
1
2
3
4
5
6
7
8
9
10

WHERE不起作用，因为过滤是基于分组聚集值而不是特定行值的。

例：列出具有2个（含）以上、价格为10（含）以上的产品的供应商

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >=2
#output
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12

# 分组和排序

例：检索总计订单价格大于等于50的订单的订单号和总计订单价格

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertital;
1
2
3
4
5

# SELECT子句顺序

SELECT子句及其顺序

子句 说明 是否必须使用
SELECT 要返回的列或表达式 是
WHERE 从中检索数据的表 仅在从表选择数据时使用
GROUP BY 分组说明 尽在按组计算聚集是使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否

上述子句使用时必须遵循该顺序

# 使用子查询

要求4.1以上版本

例：列出订购物品TNT2的所有客户

  1. 检索包含物品TNT2的所有订单的编号
  2. 检索具有前一步骤列出的订单编号的所有客户的ID
  3. 检索前一步骤返回的所有客户ID的客户信息
#(1)
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
#output
+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+
#(2)
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
+-----------+
|  cust_id  |
+-----------+
|     10001 |
|     10004 |
+-----------+

#(1)+(2)
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+

#(3)
SELECT clust_name. cust_contact FROM customers WHERE cust_id IN (10001, 10004)

#(1)+(2)+(3)
SELECT cust_name, cust_contact FROM customers
WHERE cust_id IN(SELECT cust_id FROM orders
				 WHERE order_num IN(SELECT order_num FROM orderitems
                 					 WHERE prod_id ='TNT2'));
#output
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
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
40
41
42
43
44
45

在WHERE子句中使用子查询应保证SELECT语句有与WHERE子句中相同数目的列。

# 作为计算字段使用子查询

需要显示customers表中每个客户的订单总数，订单与相应的客户ID存储在orders表中

  1. 从customers表中检索客户列表
  2. 对于检索出的每个客户，统计其在orders表中的订单数目
# 对客户10001的订单进行计数
SELECT COUNT (*) AS orders FROM orders WHERE cust_id = 10001;
# 为了对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders
                              WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;
1
2
3
4
5
6

相关子查询：涉及外部查询的子查询 在任何时候只要列明可能有多义性，就必须使用这种语法（表明和列名由一个句点分隔）

编辑 (opens new window)
上次更新: 2025/05/21, 06:42:57
2-数据过滤
4-联结

← 4-联结→

最近更新
01
6-其他操作
05-20
02
4-联结
05-20
03
7-管理
05-20
更多文章>
Theme by Vdoing | Copyright © 2019-2025 Consolexinhun | MIT License
×