1 简介

之前写过一个比较单表查询比较简单的内容。在单表查询查询中，还有较为高级的部分，诸如聚合查询，排序和分组查询，熟悉的了解关系型数据库中此类操作，可以非常方便的帮助我们实现自定义的数据提取操作。单表查询的简单查询内容可以参见

https://blog.csdn.net/lk142500/article/details/82728889

2 材料

因为所有的数据库管理系统的底层均为命令行，只不过提供界面之后更加的直观和容易操作，为了基础，仍以命令行为工具，查询不同命令的输入和输出结果，以期增加对关系型数据库的理解和使用。
楼主使用的数据库为MySQL 8.0.11

3 操作记录

单表操作的主要使用语句为SELECT语句，在SELECT语句中，可以根据自己对数据的需求，使用不同的查询条件，基本语法格式如下：

SELECT  [DISTINCT] *|{字段名1, 字段名2, 字段名3,……}
                       FROM 表名
	         [WHERE 条件表达式1]
	         [GROUP BY 字段名 [HAVING 条件表达式2]]
	         [ORDER BY 字段名 [ASC|DESC]]
	         [LIMIT [OFFSET] 记录数]

3.1 产生数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| act                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database test
    -> ;
Query OK, 1 row affected (0.20 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| act                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

使用数据库test，并在数据库中产生表student

mysql> use test;
Database changed
mysql> create table student(
    -> id INT(3) PRIMARY KEY AUTO_INCREMENT,
    -> name varchar(20) not null,
    -> grade float,
    -> gender CHAR(2)
    -> );
Query OK, 0 rows affected (0.51 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

使用insert语句向表student中插入8条数据
mysql> insert into student (name, grade, gender)
-> VALUES (“songjiang”, 40, ‘男’),
-> (“wuyong”, 100, ‘男’),
-> (“qinming”, 90, ‘男’),
-> (“husanniang”, 80, ‘男’),
-> (“sunerniang”, 66, ‘男’),
-> (“wusong”, 86, ‘男’),
-> (“linchong”, 92, ‘男’),
-> (“yanqing”, 90, ‘男’);
Query OK, 8 rows affected (0.19 sec)
Records: 8 Duplicates: 0 Warnings: 0

3.2 聚合函数

实际开发中，经常需要对某些数据进行统计，例如统计某个字段的最大值，最小值，平均值等，为了实现这些功能，MySQL提供了一些函数来实现这些功能。

3.2.1 COUNT()

COUNT函数用来统计记录的条数，语法格式如下：

select COUNT(*) from 表名

统计student表中记录数

mysql> select * from student;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  1 | songjiang  |    40 | 男     |
|  2 | wuyong     |   100 | 男     |
|  3 | qinming    |    90 | 男     |
|  4 | husanniang |    80 | 男     |
|  5 | sunerniang |    66 | 男     |
|  6 | wusong     |    86 | 男     |
|  7 | linchong   |    92 | 男     |
|  8 | yanqing    |    90 | 男     |
+----+------------+-------+--------+
8 rows in set (0.05 sec)

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.05 sec)

为查询到的记录总数起个别名

mysql> select count(*) as records from student;
+---------+
| records |
+---------+
|       8 |
+---------+
1 row in set (0.00 sec)

mysql> select count(*) records from student;
+---------+
| records |
+---------+
|       8 |
+---------+
1 row in set (0.00 sec)

3.2.2 SUM()

sum是求和函数，用于求出表中某个字段所有值的总和，语法如下：

select sum(字段名) from 表名

使用该语句可以求出指定字段值的总和
求出student表中grade成绩的总和

mysql> select sum(grade) 成绩总和 from student;
+--------------+
| 成绩总和     |
+--------------+
|          644 |
+--------------+
1 row in set (0.01 sec)

3.2.3 AVG()

AVG可以用于求出某个字段所有值得平均值

select avg(字段名) from student;


mysql> select avg(grade)  from student;
+------------+
| avg(grade) |
+------------+
|       80.5 |
+------------+
1 row in set (0.00 sec)

3.2.4 MAX/min
max/min函数则是用于求最大值/最小值的函数，用于求出某个字段的最大值

mysql> select max(grade) from student;
+------------+
| max(grade) |
+------------+
|        /100 |
+------------+
1 row in set (0.00 sec)

mysql> select min(grade) from student;
+------------+
| min(grade) |
+------------+
|         40 |
+------------+
1 row in set (0.00 sec)

3.3 对查询结果进行排序
从表中查询出来的数据可能是无序的，或者其排列顺序不是用户预期的。为了使查询结果满足用户的要求，可以使用ORDER BY对查询结果进行排序，语法格式如下：

SELECT 字段名1,字段名2,……
FROM 表名
ORDER BY 字段名1 [ASC | DESC]，字段名2 [ASC | DESC]……

查询student表中的所有记录，按照grade进行排序

mysql> select * from student order by grade;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  1 | songjiang  |    40 | 男     |
|  5 | sunerniang |    66 | 男     |
|  4 | husanniang |    80 | 男     |
|  6 | wusong     |    86 | 男     |
|  3 | qinming    |    90 | 男     |
|  8 | yanqing    |    90 | 男     |
|  7 | linchong   |    92 | 男     |
|  2 | wuyong     |   100 | 男     |
+----+------------+-------+--------+
8 rows in set (0.00 sec)

mysql> select * from student order by grade ASC;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  1 | songjiang  |    40 | 男     |
|  5 | sunerniang |    66 | 男     |
|  4 | husanniang |    80 | 男     |
|  6 | wusong     |    86 | 男     |
|  3 | qinming    |    90 | 男     |
|  8 | yanqing    |    90 | 男     |
|  7 | linchong   |    92 | 男     |
|  2 | wuyong     |   100 | 男     |
+----+------------+-------+--------+
8 rows in set (0.00 sec)

mysql>

从查询结果可以看到，返回的记录按照order by指定的字段进行排序，并且默认是升序的。两次查询的结果是一只的。
按照grade 逆序排列

mysql> select * from student order by grade desc;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  2 | wuyong     |   100 | 男     |
|  7 | linchong   |    92 | 男     |
|  3 | qinming    |    90 | 男     |
|  8 | yanqing    |    90 | 男     |
|  6 | wusong     |    86 | 男     |
|  4 | husanniang |    80 | 男     |
|  5 | sunerniang |    66 | 男     |
|  1 | songjiang  |    40 | 男     |
+----+------------+-------+--------+
8 rows in set (0.00 sec)

更新student表中yanqing行性别为NULL

mysql> update student set gender=NULL where name='yanqing';
Query OK, 1 row affected (0.15 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在MySQL中，可以指定按照多个字段对查询结果进行排序。例如按照gender和grade字段进行排序，按照gender字段升序排列，按照grade字段降序排列

mysql> select * from student order by gender asc, grade desc;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  8 | yanqing    |    90 | NULL   |
|  4 | husanniang |    80 | 女     |
|  5 | sunerniang |    66 | 女     |
|  2 | wuyong     |   100 | 男     |
|  7 | linchong   |    92 | 男     |
|  3 | qinming    |    90 | 男     |
|  6 | wusong     |    86 | 男     |
|  1 | songjiang  |    40 | 男     |
+----+------------+-------+--------+
8 rows in set (0.00 sec)

上述的命令使得记录按照多个字段进行排序，可以看到输出的记录先按照gender进行升序排列，相同的gender，则按照grade逆序排列。

3.4 分组查询

在对表中数据进行统计时，也可能需要按照一定的类别进行统计，比如，分别统计student表中gender字段为“男” “女“， “NULL”的学生成绩之和。在mysql中，可以使用group by按某个字段或者多个字段中的值进行分组，字段中值相同的为1组，其语法格式如下：

SELECT 字段名1,字段名2,……
     FROM 表名
     GROUP BY 字段名1，字段名2，……[HAVING 条件表达式];

HAVING关键字指定条件表达式对分组后的内容进行过滤。需要注意的是，Group BY一般和聚合函数一起使用，如果查询的字段出现在Group By后，却没有包含在聚合函数中，该字段显示的是分组后第一条记录的值，这样可能会导致查询的结果与预期不符合。

3.4.1 单独使用Group By进行分组

mysql> select * from student group by gender;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.stude
nt.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_g
roup_by
mysql> select * from student group by id;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  1 | songjiang  |    40 | 男     |
|  2 | wuyong     |   100 | 男     |
|  3 | qinming    |    90 | 男     |
|  4 | husanniang |    80 | 女     |
|  5 | sunerniang |    66 | 女     |
|  6 | wusong     |    86 | 男     |
|  7 | linchong   |    92 | 男     |
|  8 | yanqing    |    90 | NULL   |
+----+------------+-------+--------+
8 rows in set (0.00 sec)

mysql> select count(*), gender from student group by gender;
+----------+--------+
| count(*) | gender |
+----------+--------+
|        5 | 男     |
|        2 | 女     |
|        1 | NULL   |
+----------+--------+
3 rows in set (0.00 sec)

3.4.2 Group By和聚合函数一起使用
Group BY和聚合函数一起使用，可以统计处某个或者某些字段在一个分组中的最大值，最小值，平均值。
将student表按照gender字段值进行分组查询，计算出每个分组有多少个学生

mysql> select count(*), gender from student group by gender;
+----------+--------+
| count(*) | gender |
+----------+--------+
|        5 | 男     |
|        2 | 女     |
|        1 | NULL   |
+----------+--------+
3 rows in set (0.00 sec)

从查询结果可以看出，group by对student表按照gender字段中不同的值进行了分组，并通过count函数统计出每个分组的个数。

3.4.3 Group By和Having关键字一起使用

Having关键字与where字句作用相同，都用于设置条件表达式对查询结果进行过滤，两者的区别在于，Having关键字后可以跟聚合函数，而where子句不可以。通常情况先Having关键字都和Group By一起使用，用于对分组后的结果进行过滤。
将student表中按照gender字段进行分组排序，查询出grade字段值之和小于300的分组

mysql> select sum(grade), gender from student group by gender having sum(grade)<300;
+------------+--------+
| sum(grade) | gender |
+------------+--------+
|        146 | 女     |
|         90 | NULL   |
+------------+--------+
rows in set (0.06 sec)

对gender值为’男’的所有学生其grade字段值之和进行查询

    mysql> select sum(grade), gender from student where gender='男';
    +------------+--------+
    | sum(grade) | gender |
    +------------+--------+
    |        408 | 男     |
    +------------+--------+
    1 row in set (0.00 sec )

3.5 使用LIMIT限制查询结果的数量

查询数据时，可能会返回很多数据，而用户需要的记录可能只是其中的一条或者几条，比如实现分页功能，每页显示十条数据，每次查询就只需要十条数据。为此，MySQL提供了LIMIT关键字，可以指定查询结果从那一条记录开始，以及一共查询多少条信息，语法格式如下：

SELECT 字段名1,字段名2,……
FROM 表名
LIMIT [OFFSET，] 记录数

LIMIT：后面可以跟2个参数，“OFFSET”：为可选值，表示偏移量，如果偏移量为0则从查询结果的第一条记录开始…以此类推，如果不指定其默认值为0。
“记录数”：表示返回查询记录的条数。
查询表student的前四条数据

mysql> select * from student limit 4
    -> ;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  1 | songjiang  |    40 | 男     |
|  2 | wuyong     |   100 | 男     |
|  3 | qinming    |    90 | 男     |
|  4 | husanniang |    80 | 女     |
+----+------------+-------+--------+
4 rows in set (0.00 sec)

查询student表中grade字段值从第5到第8位的学生，从高到底

mysql> select * from student order by grade desc limit 4, 4;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  6 | wusong     |    86 | 男     |
|  4 | husanniang |    80 | 女     |
|  5 | sunerniang |    66 | 女     |
|  1 | songjiang  |    40 | 男     |
+----+------------+-------+--------+
rows in set (0.00 sec)

注意：LIMIT后跟了两个参数，两个参数以逗号隔开，第一个参数表示偏移量为4，即从第五条记录开始查询，第二个参数表示一共返回4条数据，即从第五到第八位。是哟个ORDER BY DESC使学生记录按照grade字段从高到低的顺序进行排列。
显示所有student记录，以grade排序，逆序

mysql> select * from student order by grade desc;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  2 | wuyong     |   100 | 男     |
|  7 | linchong   |    92 | 男     |
|  3 | qinming    |    90 | 男     |
|  8 | yanqing    |    90 | NULL   |
|  6 | wusong     |    86 | 男     |
|  4 | husanniang |    80 | 女     |
|  5 | sunerniang |    66 | 女     |
|  1 | songjiang  |    40 | 男     |
+----+------------+-------+--------+
8 rows in set (0.00 sec)

3.6 函数(列表)

MySQL中提供了丰富的函数，通过这些函数可以简化用户对数据的操
作，MySQL中的函数包括数学函数，字符串函数，日期和时间函数条件判断函数，加密函数，由于函数较多，不一一讲解。

3.6.1 数学函数

函数名称作用
ABS(x)绝对值
SQRT(x)返回x的非负二次方根
MOD(x, y)返回x被y除后的余数
FLOOR(x)返回不大于x的最大整数
ROUND(x, y)对x进行四舍五入整数，小数点保持y位
TRUNCATE(x, y)舍去x中小数点y位后面的数
SIGN(x)返回x的符号，-1， 0， 1

3.6.2 字符串函数

函数作用
length(str)返回str的长度
concat(s1, s2,…)返回一个或者多个字符串连接产生的新字符串
trim(str)删除字符串两侧的空格
replace(str, s1, s2)使用字符串s2替换字符串str中所有的字符串s1
substring(str, n, len)返回字符串str的子串，起始位置为n，长度为len
reverse(str)返回字符串反转后的结果
locate(s1, str)返回字符串s1在字符串str中的起始位置。

3.6.3 日期和时间函数

函数作用
curdate()获取当前日期
curtime()获取当前时间
sysdate()获取当前系统日期和时间
time_to_sec()返回将时间转换成秒的结果
adddate()执行日期的加运算
subdate()执行日期的减运算
date_format()格式化输出日期和时间值

3.6.4 条件判断 函数

函数作用
IF(expr, v1, v2)如果表达式expr为true，返回v1，否则v2
IFNULL(v1, v2)如果v1不为NULL，返回v1，否则返回v2
CASE expr when v1 then r1 [when v2 then r2…] else rn end如果expr的值等于v1，v2等值，则返回对应位置的then之后的结果，否则返回else之后的结果

3.6.5 加密函数

函数作用
MD5(str)对字符串str进行MD5加密
Encode(str, pwd_str)使用pwd作为密码加密字符串str
decode(str, pwd_str)使用pwd作为密码解密字符串str

查询student表中所有记录，将各个字段值使用下划线”_”连接起来

mysql> select concat(id, "_", name, "_", grade, "_", gender) from student;
+------------------------------------------------+
| concat(id, "_", name, "_", grade, "_", gender) |
+------------------------------------------------+
| 1_songjiang_40_男                              |
| 2_wuyong_100_男                                |
| 3_qinming_90_男                                |
| 4_husanniang_80_女                             |
| 5_sunerniang_66_女                             |
| 6_wusong_86_男                                 |
| 7_linchong_92_男                               |
| NULL                                           |
+------------------------------------------------+
8 rows in set (0.00 sec)

查询student表中的id和gender值，如果gender为男，则返回1否则返回0

mysql> select id, if(gender='男', 1, 0) from student;
+----+------------------------+
| id | if(gender='男', 1, 0)  |
+----+------------------------+
|  1 |                      1 |
|  2 |                      1 |
|  3 |                      1 |
|  4 |                      0 |
|  5 |                      0 |
|  6 |                      1 |
|  7 |                      1 |
|  8 |                      0 |
+----+------------------------+
8 rows in set (0.00 sec)

4 为表和字段取别名

4.1 为表取别名

在查询数据时，可以为表和字段取别名，这个别名可以代替指定的表和字段。如果表名很长，用起来不方便，则可以用一个别名来代替，语法如下：

select * from 表名 [as] 别名

as关键字可以省略。
如下例子所示:

mysql> select * from student as s where s.gender='女';
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  4 | husanniang |    80 | 女     |
|  5 | sunerniang |    66 | 女     |
+----+------------+-------+--------+
2 rows in set (0.00 sec)

mysql> select * from student s where s.gender='女';
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  4 | husanniang |    80 | 女     |
|  5 | sunerniang |    66 | 女     |
+----+------------+-------+--------+
2 rows in set (0.00 sec)

4.2 为字段取别名

SELECT 字段名 [AS] 别名[,字段名 [AS] 别名,……] FROM 表名;

如例子：

mysql> select name as stu_name, gender stu_gender from student;
+------------+------------+
| stu_name   | stu_gender |
+------------+------------+
| songjiang  | 男         |
| wuyong     | 男         |
| qinming    | 男         |
| husanniang | 女         |
| sunerniang | 女         |
| wusong     | 男         |
| linchong   | 男         |
| yanqing    | NULL       |
+------------+------------+
8 rows in set (0.00 sec)

5 总结

本文主要是简要讲述了Mysql中单表的高级查询，通过熟练掌握命令行操作，可以加深对mysql的基本使用，对于关系型数据库的理解非常有助。在Java spring对数据库的增删改查操作时，使用的是同样的原理。

6 参考

https://blog.csdn.net/lk142500/article/details/82728889
https://download.csdn.net/download/lk142500/10740289
传智博客MySQL数据库入门

Logo
一门面向 Data 和 AI 的低代码、云原生的开源编程语言

无需安装部署，在线快速体验 Byzer

更多推荐

编译时权限控制

前言 权限控制，对于 MLSQL 而言的重要程度可以说是生命线。 MLSQL 需要面对各式各样的资源访问，比如 MySQL, Oracle,HDFS，Hive，Kafka，Sorl，ElasticSearch，Redis，API，Web等等，不同用户对这些数据源（以及表，列）的权限是不一样的。 传统模式是，每个用户都需要有个 proxy user，然后到每个数据源里面给这个 proxy user

avatar Byzer 白泽

Byzer 支持 JDBC 聚合下推

聚合下推PR链接聚合下推 我们知道 Byzer 支持多数据源和联邦查询，可以方便分析师在一个平台上快速的分析来自多种数据源的数据，从而进行灵活的探索式分析。 使用场景和优势 当前 Byzer 中加载数据的方式会拉取明细数据到 spark 中进行聚合计算，对于小数据量或者在分布式文件系统上的数据源来说是常规操作。但是对于 JDBC 数据源或者有分析能力的 OLAP 系统来说拉取明细数据可能就不是最

avatar Byzer 白泽

Byzer 术语表

Byzer-lang Byzer ，又称为 Byzer-lang，一门面向 Data 和 AI 的低代码、云原生的开源编程语言。 Byzer 是一门结合了声明式编程和命令式编程的混合编程语言，其低代码且类 SQL 的编程逻辑配合内置算法及插件的加持，能帮助数据工作者们高效打通数据链路，完成数据的清洗转换，并快速地进行机器学习相关的训练及预测。 Byzer 希望能够提供一套语言、一个引擎，就能覆盖整

avatar Byzer 白泽