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()函数

连接查询

内连接查询

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像