AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL-查询语句（二）

2023-06-30 133
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 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)


文章标签：
云数据库 RDS MySQL 版
关系型数据库
数据库管理
MySQL
数据库
关键词：
云数据库 RDS MySQL 版查询
云数据库 RDS MySQL 版查询语句
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发，帮助IT从业者们通过“阿里云Web应用上云解决方案”，了解一个企业级Web应用上云的常见架构，了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品：云数据库RDS MySQL 版 阿里云关系型数据库RDS（Relational Database Service）是一种稳定可靠、可弹性伸缩的在线数据库服务，提供容灾、备份、恢复、迁移等方面的全套解决方案，彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
大虾好吃吗
目录
相关文章
艾伦~耶格尔
|
3月前
|
SQL 缓存 监控
MySQL缓存机制：查询缓存与缓冲池优化
MySQL缓存机制是提升数据库性能的关键。本文深入解析了MySQL的缓存体系，包括已弃用的查询缓存和核心的InnoDB缓冲池，帮助理解缓存优化原理。通过合理配置，可显著提升数据库性能，甚至达到10倍以上的效果。
艾伦~耶格尔
304 1
艾伦~耶格尔
|
3月前
|
SQL 存储 关系型数据库
MySQL体系结构详解：一条SQL查询的旅程
本文深入解析MySQL内部架构，从SQL查询的执行流程到性能优化技巧，涵盖连接建立、查询处理、执行阶段及存储引擎工作机制，帮助开发者理解MySQL运行原理并提升数据库性能。
艾伦~耶格尔
198 0
互联网课堂
|
5月前
|
SQL 人工智能 关系型数据库
如何实现MySQL百万级数据的查询？
本文探讨了在MySQL中对百万级数据进行排序分页查询的优化策略。面对五百万条数据，传统的浅分页和深分页查询效率较低，尤其深分页因偏移量大导致性能显著下降。通过为排序字段添加索引、使用联合索引、手动回表等方法，有效提升了查询速度。最终建议根据业务需求选择合适方案：浅分页可加单列索引，深分页推荐联合索引或子查询优化，同时结合前端传递最后一条数据ID的方式实现高效翻页。
互联网课堂
304 0
蓝易云
|
3月前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
储存过程(Stored Procedures) 和 函数(Functions) : 储存过程和函数允许用户编写 SQL 脚本执行复杂任务.
蓝易云
244 14
蓝易云
|
3月前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
以上概述了MySQL 中常见且重要 的几种 SQL 查询及其相关概念 这些知识点对任何希望有效利用 MySQL 进行数据库管理工作者都至关重要
蓝易云
123 15
艾伦~耶格尔
|
3月前
|
SQL 监控 关系型数据库
SQL优化技巧：让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧，涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面，帮助开发者显著提升数据库性能，解决慢查询问题，适用于高并发与大数据场景。
艾伦~耶格尔
279 2
艾伦~耶格尔
|
3月前
|
SQL 关系型数据库 MySQL
MySQL入门指南：从安装到第一个查询
本文为MySQL数据库入门指南，内容涵盖从安装配置到基础操作与SQL语法的详细教程。文章首先介绍在Windows、macOS和Linux系统中安装MySQL的步骤，并指导进行初始配置和安全设置。随后讲解数据库和表的创建与管理，包括表结构设计、字段定义和约束设置。接着系统介绍SQL语句的基本操作，如插入、查询、更新和删除数据。此外，文章还涉及高级查询技巧，包括多表连接、聚合函数和子查询的应用。通过实战案例，帮助读者掌握复杂查询与数据修改。最后附有常见问题解答和实用技巧，如数据导入导出和常用函数使用。适合初学者快速入门MySQL数据库，助力数据库技能提升。
艾伦~耶格尔
165 4
蓝易云
|
4月前
|
存储 关系型数据库 MySQL
使用命令行cmd查询MySQL表结构信息技巧分享。
掌握了这些命令和技巧，您就能快速并有效地从命令行中查询MySQL表的结构信息，进而支持数据库维护、架构审查和优化等工作。
蓝易云
430 9
德国都芳最好
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中，需要根据具体的业务需求和查询模式，综合运用索引策略和查询性能调优方法，不断地测试和优化，以提高MySQL数据库的查询性能。
德国都芳最好
629 66
艾伦~耶格尔
|
3月前
|
SQL 监控 关系型数据库
MySQL高级查询技巧：子查询、联接与集合操作
本文深入解析了MySQL高级查询的核心技术，包括子查询、联接和集合操作，通过实际业务场景展示了其语法、性能差异和适用场景，并提供大量可复用的代码示例，助你从SQL新手进阶为数据操作高手。
艾伦~耶格尔
94 0

热门文章

最新文章

  • 1
    阿里云DTS踩坑经验分享系列｜如何使用DTS进行MySQL->ClickHouse同步
  • 2
    PostgreSQL 最佳实践 - 逻辑增量复制(MySQL <-> PgSQL <-> PgSQL)
  • 3
    Docker部署WordPress LNMP(Nginx PHP MySQL)环境实践
  • 4
    datax从mysql导入数据到mysql
  • 5
    mysql主主
  • 6
    【七天深入MySQL实战营】答疑汇总Day4 MySQL查询优化实战
  • 7
    【招聘】Alibaba 数据库团队招聘Oracle MySQL DBA(无效)
  • 8
    数据库优化——慢查询MySQL定位优化流程
  • 9
    让MySql插入中文字符
  • 10
    MySQL5.7 加强了root用户登录安全性
  • 1
    Python 操作 MySQL 数据库
    235
  • 2
    MySQL in 太慢的 3 种优化方案
    2642
  • 3
    MySQL函数
    148
  • 4
    AnalyticDB MySQL新购页面融合升级，提供企业版购买选项
    532
  • 5
    Mysql 数据库基本操作
    156
  • 6
    POLARDB：新零售用户MySQL上云最佳选择
    577
  • 7
    MySQL (索引 & 事务)
    156
  • 8
    MySQL索引
    101
  • 9
    MySQL事务
    110
  • 10
    MySQL表的进阶知识（下）
    143

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 阿里云数据库产品家族及特性
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布
  • 阿里云MySQL云数据库产品体系介绍
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL

    • 推荐镜像

    更多
  • mysql
    • 下一篇
    「直播预告」Streaming Lakehouse Meetup EP.2｜Paimon × StarRocks 共话实时湖仓