MySQL-查询语句（二）-阿里云开发者社区

分组查询

分组插叙是对数据按照某个或多个字段进行分组，MySQL中使用group by关键字对数据进行分组

基本语法形式为：group by 字段 1、创建分组Group by 关键字通常和集合函数一起使用。

函数	说明
AVG	求平均值
COUNT	统计行的数量
MAX	求最大值
MIN	求最小值
SUM	求累加和

1、根据s_id对fruits表中的数据进行分组

1. mysql> select s_id,count(*) as total from fruits group by s_id;
2. +------+-------+
3. | s_id | total |
4. +------+-------+
5. |  101 |     3 |
6. |  102 |     3 |
7. |  103 |     2 |
8. |  104 |     2 |
9. |  105 |     3 |
10. |  106 |     1 |
11. |  107 |     2 |
12. +------+-------+
13. 7 rows in set (0.01 sec)

根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来

1. mysql> select s_id,group_concat(f_name) as name from fruits group by s_id;
2. +------+-------------------------+
3. | s_id | name                    |
4. +------+-------------------------+
5. |  101 | apple,blackberry,cherry |
6. |  102 | orange,banana,grape     |
7. |  103 | apricot,coconut         |
8. |  104 | berry,lemon             |
9. |  105 | melon,xbabay,xxtt       |
10. |  106 | mango                   |
11. |  107 | xxxx,xbababa            |
12. +------+-------------------------+
13. 7 rows in set (0.00 sec)

2、使用having过滤分组

根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息

1. mysql> select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) > 1;
2. +------+-------------------------+
3. | s_id | name                    |
4. +------+-------------------------+
5. |  101 | apple,blackberry,cherry |
6. |  102 | orange,banana,grape     |
7. |  103 | apricot,coconut         |
8. |  104 | berry,lemon             |
9. |  105 | melon,xbabay,xxtt       |
10. |  107 | xxxx,xbababa            |
11. +------+-------------------------+
12. 6 rows in set (0.00 sec)

3、在group by 子句中使用with rollup 使用with rollup关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

1. mysql> select s_id,count(*) as total
2.     -> from fruits
3.     -> group by s_id with rollup;
4. +------+-------+
5. | s_id | total |
6. +------+-------+
7. |  101 |     3 |
8. |  102 |     3 |
9. |  103 |     2 |
10. |  104 |     2 |
11. |  105 |     3 |
12. |  106 |     1 |
13. |  107 |     2 |
14. | NULL |    16 |
15. +------+-------+
16. 8 rows in set (0.00 sec)

4、多字段分组使用group by可以对多个字段进行分组，group by关键字后面跟需要分组的字段，MySQL根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组，以此类推。

1. mysql> select * from fruits group by f_id,f_name;
2. +------+------+------------+---------+
3. | f_id | s_id | f_name     | f_price |
4. +------+------+------------+---------+
5. | a1   |  101 | apple      |    5.20 |
6. | a2   |  103 | apricot    |    2.20 |
7. | b1   |  101 | blackberry |   10.20 |
8. | b2   |  104 | berry      |    7.60 |
9. | b5   |  107 | xxxx       |    3.60 |
10. | bs1  |  102 | orange     |   11.20 |
11. | bs2  |  105 | melon      |    8.20 |
12. | c0   |  101 | cherry     |    3.20 |
13. | l2   |  104 | lemon      |    6.40 |
14. | m1   |  106 | mango      |   15.70 |
15. | m2   |  105 | xbabay     |    2.60 |
16. | m3   |  105 | xxtt       |   11.60 |
17. | o2   |  103 | coconut    |    9.20 |
18. | t1   |  102 | banana     |   10.30 |
19. | t2   |  102 | grape      |    5.30 |
20. | t4   |  107 | xbababa    |    3.60 |
21. +------+------+------------+---------+
22. 16 rows in set (0.00 sec)

5、group by和order by一起使用某些情况下需要对分组进行排序

1. mysql> create table orderitems
2.  -> (
3.  -> o_num int not null,
4.  -> o_item int not null,
5.  -> f_id char(10) not null,
6.  -> quantity int not null,
7.  -> item_price decimal(8,2) not null,
8.  -> primary key(o_num,o_item)
9.  -> );
10. Query OK, 0 rows affected (0.03 sec)
11. mysql> insert into orderitems(o_num,o_item,f_id,quantity,item_price)
12.  -> values(30001,1,'a1',10,'5.2'),
13.  -> (30001,2,'b2',3,'7.6'),
14.  -> (30001,3,'bs1',5,'11.2'),
15.  -> (30001,4,'bs2',15,'9.2'),
16.  -> (30002,1,'b3',2,'20.0'),
17.  -> (30003,1,'c0',100,10),
18.  -> (30004,1,'o2',50,'2.50'),
19.  -> (30005,1,'c0',5,'10'),
20.  -> (30005,2,'b1',10,'8.99'),
21.  -> (30005,3,'a2',10,'2.2'),
22.  -> (30005,4,'m1',5,'14.99');
23. Query OK, 11 rows affected (0.00 sec)
24. Records: 11 Duplicates: 0 Warnings: 0

查询价格大于100的订单号和总价订单价格

1. mysql> select o_num,sum(quantity*item_price) as ordertotal
2.  -> from orderitems
3.  -> group by o_num
4.  -> having sum(quantity*item_price) >= 100;
5. +-------+------------+
6. | o_num | ordertotal |
7. +-------+------------+
8. | 30001 |     268.80 |
9. | 30003 |    1000.00 |
10. | 30004 |     125.00 |
11. | 30005 |     236.85 |
12. +-------+------------+
13. 4 rows in set (0.00 sec)

使用limit限查询结果的数量

select返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用limit关键字，语法格式如下：limit [位置偏移量] 行数

1. mysql> select * from fruits limit 4;
2. +------+------+------------+---------+
3. | f_id | s_id | f_name     | f_price |
4. +------+------+------------+---------+
5. | a1   |  101 | apple      |    5.20 |
6. | a2   |  103 | apricot    |    2.20 |
7. | b1   |  101 | blackberry |   10.20 |
8. | b2   |  104 | berry      |    7.60 |
9. +------+------+------------+---------+
10. 4 rows in set (0.00 sec)

查看从第四行开始，到第三行结束（前面的4为第几行，后面的3为数量）

1. mysql> select * from fruits limit 4,3;
2. +------+------+--------+---------+
3. | f_id | s_id | f_name | f_price |
4. +------+------+--------+---------+
5. | b5   |  107 | xxxx   |    3.60 |
6. | bs1  |  102 | orange |   11.20 |
7. | bs2  |  105 | melon  |    8.20 |
8. +------+------+--------+---------+
9. 3 rows in set (0.00 sec)

聚合函数查询

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

count()函数

Count()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。 Count(*)计算表中总的函数，不管某列有数列或者为空值 Count(字段名)计算指定列下总的行数，计算时将忽略空值的行

1. mysql> select count(*) from customers;
2. +----------+
3. | count(*) |
4. +----------+
5. |        4 |
6. +----------+
7. 1 row in set (0.01 sec)
8. mysql> select count(c_email) from customers;
9. +----------------+
10. | count(c_email) |
11. +----------------+
12. |              3 |
13. +----------------+
14. 1 row in set (0.00 sec)
15. mysql> select o_num,count(f_id) from orderitems group by o_num;
16. +-------+-------------+
17. | o_num | count(f_id) |
18. +-------+-------------+
19. | 30001 |           4 |
20. | 30002 |           1 |
21. | 30003 |           1 |
22. | 30004 |           1 |
23. | 30005 |           4 |
24. +-------+-------------+
25. 5 rows in set (0.00 sec)

sum()函数

sum()是一个求总和的函数，返回指定列值得总和。

1. mysql> select sum(quantity) from orderitems where o_num = 30005;
2. +---------------+
3. | sum(quantity) |
4. +---------------+
5. |            30 |
6. +---------------+
7. 1 row in set (0.00 sec)
8. mysql> select o_num,sum(quantity)
9.     -> from orderitems
10.     -> group by o_num;
11. +-------+---------------+
12. | o_num | sum(quantity) |
13. +-------+---------------+
14. | 30001 |            33 |
15. | 30002 |             2 |
16. | 30003 |           100 |
17. | 30004 |            50 |
18. | 30005 |            30 |
19. +-------+---------------+
20. 5 rows in set (0.01 sec)

注意：sum()函数在计算时，忽略列值为NULL的行。

avg()函数

avg()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

1. mysql> select avg(f_price)
2.     -> from fruits
3.     -> where s_id=103;
4. +--------------+
5. | avg(f_price) |
6. +--------------+
7. |     5.700000 |
8. +--------------+
9. 1 row in set (0.02 sec)
10. mysql> select s_id,avg(f_price)
11.     -> from fruits
12.     -> group by s_id;
13. +------+--------------+
14. | s_id | avg(f_price) |
15. +------+--------------+
16. |  101 |     6.200000 |
17. |  102 |     8.933333 |
18. |  103 |     5.700000 |
19. |  104 |     7.000000 |
20. |  105 |     7.466667 |
21. |  106 |    15.700000 |
22. |  107 |     3.600000 |
23. +------+--------------+
24. 7 rows in set (0.01 sec)

max()函数

max()返回指定列中的最大值

1. mysql> select max(f_price) from fruits;
2. +--------------+
3. | max(f_price) |
4. +--------------+
5. |        15.70 |
6. +--------------+
7. 1 row in set (0.00 sec)
8. mysql> select s_id,max(f_price) 
9.     -> from fruits
10.     -> group by s_id;
11. +------+--------------+
12. | s_id | max(f_price) |
13. +------+--------------+
14. |  101 |        10.20 |
15. |  102 |        11.20 |
16. |  103 |         9.20 |
17. |  104 |         7.60 |
18. |  105 |        11.60 |
19. |  106 |        15.70 |
20. |  107 |         3.60 |
21. +------+--------------+
22. 7 rows in set (0.01 sec)

min()函数

min()返回查询列中的最小值

1. mysql> select min(f_price) from fruits;
2. +--------------+
3. | min(f_price) |
4. +--------------+
5. |         2.20 |
6. +--------------+
7. 1 row in set (0.00 sec)
8. mysql> select s_id,min(f_price) from fruits group by s_id;
9. +------+--------------+
10. | s_id | min(f_price) |
11. +------+--------------+
12. |  101 |         3.20 |
13. |  102 |         5.30 |
14. |  103 |         2.20 |
15. |  104 |         6.40 |
16. |  105 |         2.60 |
17. |  106 |        15.70 |
18. |  107 |         3.60 |
19. +------+--------------+
20. 7 rows in set (0.00 sec)

连接查询

连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，主要包括内连接、外连接。通过连接运算符可以实现多个表查询。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表现中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。

内连接查询

内连接（inner join）使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新纪录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

创建表并插入数据

1. mysql> create table suppliers
2.  -> (
3.  -> s_id int not null auto_increment,
4.  -> s_name char(50) not null,
5.  -> s_city char(50) null,
6.  -> s_zip char(10) null,
7.  -> s_call char(50) not null,
8.  -> primary key(s_id) 
9.  -> );
10. Query OK, 0 rows affected (0.02 sec)
11. mysql> insert into suppliers(s_id,s_name,s_city,s_zip,s_call)
12.  -> values(101,'FastFruit Inc.','tianjin','300000','48075'),
13.  -> (102,'LT Supplies','chongqing','400000','44333'),
14.  -> (103,'acme','shanghai','200000','90046'),
15.  -> (104,'fnk inc.','zhongshan','528437','11111'),
16.  -> (105,'good set','taiyuang','030000','22222'),
17.  -> (106,'just eat ours','beijing','010','45678'),
18.  -> (107,'dk inc.','zhengzhou','450000','33332');
19. Query OK, 7 rows affected (0.01 sec)
20. Records: 7 Duplicates: 0 Warnings: 0
21. 
22. mysql> select suppliers.s_id,s_name,f_name,f_price
23.     -> from fruits,suppliers
24.     -> where fruits.s_id = suppliers.s_id;
25. +------+----------------+------------+---------+
26. | s_id | s_name         | f_name     | f_price |
27. +------+----------------+------------+---------+
28. |  101 | FastFruit Inc. | apple      |    5.20 |
29. |  103 | acme           | apricot    |    2.20 |
30. |  101 | FastFruit Inc. | blackberry |   10.20 |
31. |  104 | fnk inc.       | berry      |    7.60 |
32. |  107 | dk inc.        | xxxx       |    3.60 |
33. |  102 | LT Supplies    | orange     |   11.20 |
34. |  105 | good set       | melon      |    8.20 |
35. |  101 | FastFruit Inc. | cherry     |    3.20 |
36. |  104 | fnk inc.       | lemon      |    6.40 |
37. |  106 | just eat ours  | mango      |   15.70 |
38. |  105 | good set       | xbabay     |    2.60 |
39. |  105 | good set       | xxtt       |   11.60 |
40. |  103 | acme           | coconut    |    9.20 |
41. |  102 | LT Supplies    | banana     |   10.30 |
42. |  102 | LT Supplies    | grape      |    5.30 |
43. |  107 | dk inc.        | xbababa    |    3.60 |
44. +------+----------------+------------+---------+
45. 16 rows in set (0.04 sec)

如果在一个连接查询中，涉及的两个表都是同一个表，这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

1. mysql> select f1.f_id,f1.f_name
2.     -> from fruits as f1, fruits as f2
3.     -> where f1.s_id = f2.s_id and f2.f_id = 'a1';
4. +------+------------+
5. | f_id | f_name     |
6. +------+------------+
7. | a1   | apple      |
8. | b1   | blackberry |
9. | c0   | cherry     |
10. +------+------------+
11. 3 rows in set (0.00 sec)

MySQL-查询语句（二）

分组查询

使用limit限查询结果的数量

聚合函数查询

count()函数

sum()函数

avg()函数

max()函数

min()函数

连接查询

内连接查询

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL-查询语句（二）

分组查询

使用limit限查询结果的数量

聚合函数查询

count()函数

sum()函数

avg()函数

max()函数

min()函数

连接查询

内连接查询

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像