【单表数据记录查询 三】

已于 2023-03-27 20:52:03 修改
阅读量173 收藏
点赞数
分类专栏： MySQL 学习 文章标签： sql java 数据库
于 2023-03-27 20:39:33 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_56767169/article/details/129743939
版权

目录


本文所涉及到的表（t_employee）在 【表单数据记录查询一】中可以找到

排序数据记录查询

通过条件数据查询，虽然可以查询到符合用户需求的数据记录，但是查询到的数据记录在默认情况下都是按照数据记录最初添加到表中的顺序来显示。如果需要排序查询结果，需要关键字 ORDER BY 来实现，其语法形式为：

SELECT field1,field2.…,fieldn
	FROM table_name
		ORDER BY fieldm1 [ASC|DESC][,fieldm2 [ASC|DESC]]

参数 fieldm ---- 按照该字段进行排序
参数 ASC ---- 按照升序进行排序（默认）
参数 DESC ---- 按照降序进行排序
因为，在关键字 ORDER BY 后面可以设置多个不同的字段进行排序，因此包含：按照单字段排序、按照多字段排序

一、按照单字段排序

关键字 ORDER BY 后面只有一个字段，即可实现单字段排序

1.升序排序

【实例】查询表 t_employee 中所有雇员，同时按照字段 sal（工资）对查询结果进行升序排序。

mysql> SELECT ename,sal
    ->  FROM t_employee
    ->          ORDER BY sal ASC;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+
14 rows in set (0.01 sec)

结果显示查询的所有结果是按照字段 sal 的值从小到大升序排列，由于上面的 ORDER BY 默认的就是升序排列，所以上面的语句可以修改成：

mysql> SELECT ename,sal
    ->  FROM t_employee
    ->          ORDER BY sal;

2. 降序排序

【实例】查询所有雇员，同时按照字段 mgr（领导编号）对查询结果进行降序排序

mysql> SELECT *
    ->  FROM t_employee
    ->          ORDER BY mgr DESC;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | MGR  | Hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1981-03-12 |  800.00 |    NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-03-12 | 2975.00 |    NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-03-12 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1985-03-12 | 2450.00 |    NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1998-03-12 | 1100.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1981-03-12 | 1300.00 |    NULL |     10 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1982-03-12 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1983-03-12 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-03-12 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1989-03-12 | 1500.00 |    0.00 |     30 |
|  7900 | JAMES  | CLERK     | 7698 | 1997-03-12 |  950.00 |    NULL |     30 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1981-03-12 | 3000.00 |    NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 0000-00-00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-03-12 | 5000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

结果显示：所有查询结果都是按照字段 mgr 的值从大到小进行排序。
**注意：**在 MySQL 软件中，如果字段的值为空值（NULL），则该值为最小值，因此在降序排序中将最后显示（最后一个），在升序中则将最先显示（第一行）

二、按照多字段排序

在上面的实例中，由于字段 mgr 中存在值相同的数据记录，因此字段值为 7698 的数据记录顺序没有任何意义。为了解决该问题，可以按照断字段进行排序：首先按照第一个字段进行排序，如果遇到值相同的字段会按照第二个字段进行排序，一次类推。
【实例】查询所有雇员，首先按照字段 mgr（领导编号）进行降序排序，然后在按照字段 hiredate（雇佣日期）进行升序排列：

mysql> SELECT ename,mgr,Hiredate
    ->  FROM t_employee
    ->          ORDER BY mgr DESC,Hiredate;
+--------+------+------------+
| ename  | mgr  | Hiredate   |
+--------+------+------------+
| SMITH  | 7902 | 1981-03-12 |
| JONES  | 7839 | 1981-03-12 |
| BLAKE  | 7839 | 1981-03-12 |
| CLARK  | 7839 | 1985-03-12 |
| ADAMS  | 7788 | 1998-03-12 |
| MILLER | 7782 | 1981-03-12 |
| MARTIN | 7698 | 1981-03-12 |
| ALLEN  | 7698 | 1982-03-12 |
| WARD   | 7698 | 1983-03-12 |
| TURNER | 7698 | 1989-03-12 |
| JAMES  | 7698 | 1997-03-12 |
| FORD   | 7566 | 0000-00-00 |
| SCOTT  | 7566 | 1981-03-12 |
| KING   | NULL | 1981-03-12 |
+--------+------+------------+
14 rows in set (0.00 sec)

结果中显示：查询的所有记录先按照字段 mgr 从大到小进行降序排序，当遇到值相同的数据记录，再按照字段 Hiredate 的值进行从小到大进行排序。由于 ORDER BY 是默认是升序排序，因此 Hiredate 后面可以省略关键字 ASC。

限制数据记录查询数量

进行条件数据查询时，有时所查询的数据记录太多，如果全部显示则不符合实际需求，这时可以通过 MySQL 软件提供的关键字 LIMIT 来限制查询结果的数量，其语法形式如下：

SELECT field1,field2,…,field3
	FROM table_name
		WHERE CONDITION
			LIMIT OFFSET_START,ROW_COUNT;

上述语句中，通过关键字 LIMIT 来限制数据查询结果的数量
OFFSET_START ---- 数据记录的起始偏移量（不包括本行）
ROW_COUNT ---- 显示的行数

一、不指定初始位置

对于关键字 LIMIT 如果不指定初始位置，则默认值为 0 ，表示从第一条记录开始显示。语法形式：

LIMIT row_count

上述语句表示显示 row_count 条数据查询结果数量，如果 ROW_COUNT 值小于查询结果的总数量将会从第一条数据记录开始，显示 ROW_COUNT 条数据记录；如果 ROW_COUNT 值大于查询结果的总数量，将会显示所有的查询结果。

1. 显示记录数小于查询结果

【实例】查询不领奖金（字段 comm ）的所有雇员，同时对查询结果只显示两条记录：

mysql> SELECT *
    ->  FROM t_employee
    ->          WHERE comm IS NULL
    ->                  LIMIT 2;
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | MGR  | Hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK   | 7902 | 1981-03-12 |  800.00 | NULL |     20 |
|  7566 | JONES | MANAGER | 7839 | 1981-03-12 | 2975.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
2 rows in set (0.01 sec)

上述语句中，设置查询数据条件为 “comm is NULL ”，最后通过关键字LIMIT 设置显示数据记录数目为2。
结果显示：虽然字段 comm 值为 NULL 的数据记录有10条，但由于使用了关键字 LIMIT ，所以只显示2条。

2. 显示记录数大于查询结果

上面的实例中，如果设置关键字 LIMIT 的 ROW_COUNT 参数大于10是，会产生什么效果

mysql> SELECT *
    ->  FROM t_employee
    ->          WHERE comm IS NULL
    ->                  LIMIT 11;
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename  | job       | MGR  | Hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1981-03-12 |  800.00 | NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-03-12 | 2975.00 | NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-03-12 | 2850.00 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1985-03-12 | 2450.00 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1981-03-12 | 3000.00 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-03-12 | 5000.00 | NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1998-03-12 | 1100.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1997-03-12 |  950.00 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 0000-00-00 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1981-03-12 | 1300.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
10 rows in set (0.00 sec)

结果显示：虽然要求显示的查询结果数量为10 ，但是查询结果总数才使10 ，所以只会将所有查询结果显示出来。

二、指定初始位置

LIMIT 关键字经常被应用在分页系统中，对于第一页的数据记录，可以通过不指定初始位置来实现，但是对于第二页等其他页面则必须指定初始位置（OFFSET_START），否则将无法实现分页功能。
此外，LIMIT 关键字还经常与 ORDER BY 关键字一起使用，即先对查询结果进行排序，然后显示其中部分数据记录。
【实例】查询不领奖金（字段 comm）的所有雇员，然后对排序结果根据入职时间 （字段 Hiredate）进行从早到晚排序，同时分两次进行显示，第一次从第1条记录开始，共显示5条记录；第二次从第6条记录开始显示，共显示5条。

mysql> SELECT *
    ->  FROM t_employee
    ->          WHERE comm is NULL
    ->                  ORDER BY Hiredate
    ->                          LIMIT 0,5;
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | MGR  | Hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7902 | FORD  | ANALYST | 7566 | 0000-00-00 | 3000.00 | NULL |     20 |
|  7369 | SMITH | CLERK   | 7902 | 1981-03-12 |  800.00 | NULL |     20 |
|  7566 | JONES | MANAGER | 7839 | 1981-03-12 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER | 7839 | 1981-03-12 | 2850.00 | NULL |     30 |
|  7788 | SCOTT | ANALYST | 7566 | 1981-03-12 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
5 rows in set (0.01 sec)

结果中显示：未领奖金的雇员以入职时间从早到晚进行排序后，显示前5个记录

从第6条记录开始显示，共显示5条记录，语句如下：

mysql> SELECT *
    ->  FROM t_employee
    ->          WHERE comm IS NULL
    ->                  ORDER BY hiredate
    ->                          LIMIT 5,5;
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename  | job       | MGR  | Hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7839 | KING   | PRESIDENT | NULL | 1981-03-12 | 5000.00 | NULL |     10 |
|  7934 | MILLER | CLERK     | 7782 | 1981-03-12 | 1300.00 | NULL |     10 |
|  7782 | CLARK  | MANAGER   | 7839 | 1985-03-12 | 2450.00 | NULL |     10 |
|  7900 | JAMES  | CLERK     | 7698 | 1997-03-12 |  950.00 | NULL |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1998-03-12 | 1100.00 | NULL |     20 |
+-------+--------+-----------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)

结果显示：根据字段 Hiredate 进行从早到晚排序，然后从第6条记录开始显示，共显示5条。
注：显示的是偏移量+1开始的记录

统计函数数据记录查询

在数据库软件中，很多情况下都需要进行一些统计汇总操作能够，比如统计公司、部门的人数，这时就会用到该软件所支持的统计函数：

  • COUNT() 函数 ---- 统计表中记录的条数
  • AVG() 函数 ---- 计算字段值的平均值
  • MAX() 函数 ---- 查询字段值的最大值
  • MIN() 函数 ---- 查询字段值的最小值

其语法形式为：

SELECT function(field)
	FROM table_name
		WHERE CONDITION;

上述语句中利用统计函数 function 来统计关于字段 field 的值

一、MySQL 支持的统计函数

1. 统计数据记录条数

统计函数 COUNT() 用来实现统计数据记录条数，可以通过以下两种方式实现：

  • COUNT(*) 使用方式：实现对表中记录数量进行统计，不管表字段中包含的是 NULL 值还是非 NULL 值
  • COUNT(field) 使用方式：实现对指定字段的记录进行统计，在具体统计是将忽略 NULL 值。

【实例1】统计雇员表（t_employee）中的雇员人数

mysql> SELECT COUNT(*) '本公司雇员人数'
    ->  FROM t_employee;
+----------------+
| 本公司雇员人数 |
+----------------+
|             14 |
+----------------+
1 row in set (0.02 sec)

上述语句，用到了前面的知识点，用函数 COUNT(*) 统计表中所有记录条数，并放在 “本公司雇员人数”字段中显示

【实例2】统计雇员表中领奖金的雇员人数

mysql> SELECT COUNT(comm) '领取奖金雇员人数'
    ->  FROM t_employee
    ->          WHERE NOT comm=0;
+------------------+
| 领取奖金雇员人数 |
+------------------+
|                3 |
+------------------+
1 row in set (0.01 sec)

上述语句中，由于奖金数（字段 comm ）有的值为0，也算是没有领取奖金，所以是指查询条件为 “NOT comm=0”。

2. 统计计算平均值

统计函数 AVG() 首先用来实现统计计算特定字段之和，然后求得该字段的平均值。该函数可以用来计算指定字段的平均值或符合特定条件的指定字段的平均值，该函数只有一种使用方式：
AVG(field) 方式：实现对指定字段的平均值进行计算，在具体统计时将忽略 NULL 值。
【实例】计算领取奖金雇员的平均奖金数

mysql> SELECT AVG(comm) '平均奖金'
    ->  FROM t_employee;    
+------------+
| 平均奖金   |
+------------+
| 550.000000 |
+------------+
1 row in set (0.01 sec)

这种思路对吗，我们来看看

mysql> SELECT ename,comm
    ->  FROM t_employee
    ->          WHERE NOT comm IS NULL;
+--------+---------+
| ename  | comm    |
+--------+---------+
| ALLEN  |  300.00 |
| WARD   |  500.00 |
| MARTIN | 1400.00 |
| TURNER |    0.00 |
+--------+---------+
4 rows in set (0.00 sec)

我们知道，在雇员表中除了字段 comm NULL 外，只有4条记录，如上所示，虽然 AVG(comm）在具体运行时，忽略了值为NULL 的数据记录，但是却没有忽略值为 0 的数据记录，因此，这种表达式不符合实际的。因此我们可以加个条件语句NOT comm=0，如下：

mysql> SELECT AVG(comm) '平均奖金'
    ->  FROM t_employee
    ->          WHERE NOT comm=0;
+------------+
| 平均奖金   |
+------------+
| 733.333333 |
+------------+
1 row in set (0.00 sec)

执行结果显示，领取奖金数分别为300.00、500.00、1400.00，因此平均值为733.33333

3. 统计计算求和

统计函数 SUM() 用来实现统计数据计算求和，该函数可以用来计算指定字段值之和或符合特定条件的指定字段值之和，与COUNT() 统计函数相比，该统计函数也只有一种使用方式；
【实例】计算雇员工资（字段 sal ）的总和

mysql> SELECT SUM(sal) '工资总和'
    ->  FROM t_employee;
+----------+
| 工资总和 |
+----------+
| 29025.00 |
+----------+
1 row in set (0.01 sec)

结果显示领取工资总和为 29025.00，
在具体运行SUM() 统计函数时，会忽略 NULL 值，因此也可以统计雇员领取奖金的总和。

mysql> SELECT SUM(comm) '已发放奖金总和'
    ->  FROM t_employee
    ->          WHERE NOT comm=0;
+----------------+
| 已发放奖金总和 |
+----------------+
|        2200.00 |
+----------------+
1 row in set (0.00 sec)

上述语句中，虽然不添加条件（WHERE NOT comm=0）语句也能得出相同的总和，但是具体运行时不符合实际需求。所以，我们尽量让语句更贴近实际。

4. 统计计算最大值和最小值

MAX(field)：计算指定字段值中的最大值，在具体统计时将忽略 NULL 值；
MIN(field)：计算指定字段值中的最小值，在具体统计时将忽略 NULL 值。
【实例】计算雇员表领取工资（字段 sal ）的最大值和最小值

mysql> SELECT MAX(sal) '最大值',MIN(sal) '最小值'
    ->  FROM t_employee;
+---------+--------+
| 最大值  | 最小值 |
+---------+--------+
| 5000.00 | 800.00 |
+---------+--------+
1 row in set (0.01 sec)

上述语句结果显示：领取工资中最多的值为5000.00，领取工资中最少的值为800.00。
在具体运行 MAX() 和 MIN() 统计函数时，会忽略 NULL 值，因此也可以统计雇员两区奖金的最大值和最小值：

mysql> SELECT MAX(comm) '最大值',MIN(comm) '最小值'
    ->  FROM t_employee;
+---------+--------+
| 最大值  | 最小值 |
+---------+--------+
| 1400.00 |   0.00 |
+---------+--------+
1 row in set (0.00 sec)

在上述函数具体的运行过程中，忽略了值为 NULL 的数据记录，但是却没有忽略值为 0 的数据记录，虽然运行结果没有错，但是具体的运行过程不符合实际需求。
因此需要添加其特定条件：

mysql> SELECT MAX(comm) '最大值',MIN(comm) '最小值'
    ->  FROM t_employee
    ->          WHERE NOT comm=0;
+---------+--------+
| 最大值  | 最小值 |
+---------+--------+
| 1400.00 | 300.00 |
+---------+--------+
1 row in set (0.00 sec)

从上面的结果中可以看到最小值为 300.00，符合实际需求。

二、关于统计函数注意点

对于 MySQL 软件所支持的统计函数，如果所操作的表中没有任何数据记录，则 COUNT() 函数返回数据 0 ，而其他函数则返回 NULL。
【实例】统计表 t_dept 中字段 deptno 的所有记录数
表 t_dept 的结构

mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int         | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

将表 t_dept 的所有记录删除

mysql> DELETE FROM t_dept;
Query OK, 2 rows affected (0.07 sec)

统计字段 deptno 中的记录数，平均值、总和、最大值、最小值

mysql> SELECT COUNT(deptno) '数量',AVG(deptno) '平均值',SUM(deptno) '总和',MAX(d
eptno) '最大值',MIN(deptno) '最小值'
    ->  FROM t_dept;
+------+--------+------+--------+--------+
| 数量 | 平均值 | 总和 | 最大值 | 最小值 |
+------+--------+------+--------+--------+
|    0 |   NULL | NULL |   NULL |   NULL |
+------+--------+------+--------+--------+
1 row in set (0.01 sec)

由于表 t_dept 中没有任何数据记录，所以 COUNT() 的执行结果为 0 ，而其他的函数则为 NULL

下表为各个函数对 NULL 是否忽略的统计表：

统计函数意义是否忽略NULL
COUNT(*)对表中所有记录进行统计不忽略
COUNT(field)对指定字段的记录进行统计忽略
AVG(field)对指定字段的记录的平均值进行计算忽略
SUM(field)计算指定字段之和忽略
MAX(field)计算指定字段值中的最大值忽略
MIN(field)计算指定字段值中的最小值忽略

分组数据记录查询

在具体应用中，统计函数经常与分组一起使用：

  • 对于公司里的所有雇员，首先分两组：男性和女性，然后分别统计各组总的人数，即统计男雇员和女雇员的数量；
  • 对于公司里的所有雇员，首先分成两组：东北地区和西北地区，然后分别统计各组中的人数，即统计东北地区雇员和西北地区雇员的数量。
  • 对于公司里的所有雇员，首先分成两组：18岁以下和18岁以上，然后分别统计各组中的人数，即统计小于18岁雇员和大于18岁雇员的数量。

一、简单分组查询

通过 SQL 语句 GROUP BY 来实现分组查询，其语法形式：

SELECT FUNCTION()
	FROM table_name
		WHERE CONDITION
			GROUP BY field;
  • FUNCTION()：函数
  • talble_name ：表名称
  • CONDITION ：查询条件
  • GROUP BY ：分组依据

注意： 在具体进行分组查询时，分组所依据的字段上的值一定要具有重复值，否则将没有任何实际意义。

【实例】在雇员表（t_employee）中按照部门号（字段 deptno ）对所有雇员进行分组

mysql> SELECT *
    ->  FROM t_employee
    ->          GROUP BY deptno;
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job      | MGR  | Hiredate   | sal     | comm   | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7369 | SMITH | CLERK    | 7902 | 1981-03-12 |  800.00 |   NULL |     20 |
|  7499 | ALLEN | SALESMAN | 7698 | 1982-03-12 | 1600.00 | 300.00 |     30 |
|  7782 | CLARK | MANAGER  | 7839 | 1985-03-12 | 2450.00 |   NULL |     10 |
+-------+-------+----------+------+------------+---------+--------+--------+
3 rows in set (0.00 sec)

执行结果中只显示了三条记录，由于字段 deptno 的值分别为 10、20、30，所以首先将所有数据记录按照这三个值分成三组，然后再显示每组中的一条记录。

如果所针对的字段没有重复值将会发生什么情况，我们可以按照雇员编号（字段 empno）分组试一试：

mysql> SELECT *
    ->  FROM t_employee
    ->          GROUP BY empno;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | MGR  | Hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1981-03-12 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1982-03-12 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1983-03-12 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-03-12 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-03-12 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-03-12 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1985-03-12 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1981-03-12 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-03-12 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1989-03-12 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1998-03-12 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1997-03-12 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 0000-00-00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1981-03-12 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

从执行结果中可以看到，由于字段 empno 中的数据没有重复，所以首先将每一条记录分成一组，再显示每组中的一条记录，因此该分组查询没有任何实际意义。

二、实现统计功能分组查询

从简单分组查询中我们可以看出：因为关键字 GROUP BY 单独使用时，默认查询出每个分组中随机的一条记录，具有很大的不确定性，因此分组关键字建议与统计函数一起使用。

如果想显示每个分组中的字段，可以通过函数 GROUP_CONCAT() 来实现，该函数可以实现显示每个分组中的指定字段值，语法形式如下：

SELECT GROUP_CONCAT(field)
	FROM table_name
		WHERE CONDITION
		GROUP BY feild;

【实例】按照部门号（字段 deptno）对所有雇员进行分组，同时显示出每组中的雇员（字段 ename）和每组中雇员的个数

mysql> SELECT deptno '部门号',GROUP_CONCAT(ename) '雇员姓名',COUNT(ename) '数量'

    ->  FROM t_employee
    ->          GROUP BY deptno;
+--------+--------------------------------------+------+
| 部门号 | 雇员姓名                             | 数量 |
+--------+--------------------------------------+------+
|     10 | CLARK,KING,MILLER                    |    3 |
|     20 | SMITH,JONES,SCOTT,ADAMS,FORD         |    5 |
|     30 | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |    6 |
+--------+--------------------------------------+------+
3 rows in set (0.00 sec)

上述语句中，分组 (GROUP BY) 依据是字段 deptno ，同时通过函数 GROUP_CONCAT() 获取每组中所指定字段的记录元素。并统计了每个部门（分组）中雇员的人数。

三、实现多个字段分组查询

使用 GROUP BY 是，其字句处理可以是一个字段外，还可以是多个字段，即可以按多个字段进行分组。其语法形式如下：

SELECT GROUP_CONCAT(field),FUNCTION(field)
	FROM table_name
		WHERE CONDITION
		GROUP BY field1,field2,…,fieldn;

在上述的语句中，首先会按照字段 field1 进行分组，然后针对每组按照字段 field2 进行分组，以此类推。
【实例】针对雇员表（t_employee ），首先按照部门号（字段 deptno）对所有雇员进行分组，然后按照雇佣日期（字段 Hiredate）对每组进行分组，同时显示出每组中雇员名（字段 ename）和个数。

mysql> SELECT deptno '部门号',Hiredate '雇用日期',GROUP_CONCAT(ename) '雇员姓名'
,COUNT(ename)
    ->  FROM t_employee
    ->          GROUP BY deptno,Hiredate;
+--------+------------+-------------------+--------------+
| 部门号 | 雇用日期   | 雇员姓名          | COUNT(ename) |
+--------+------------+-------------------+--------------+
|     10 | 1981-03-12 | KING,MILLER       |            2 |
|     10 | 1985-03-12 | CLARK             |            1 |
|     20 | 0000-00-00 | FORD              |            1 |
|     20 | 1981-03-12 | SMITH,JONES,SCOTT |            3 |
|     20 | 1998-03-12 | ADAMS             |            1 |
|     30 | 1981-03-12 | MARTIN,BLAKE      |            2 |
|     30 | 1982-03-12 | ALLEN             |            1 |
|     30 | 1983-03-12 | WARD              |            1 |
|     30 | 1989-03-12 | TURNER            |            1 |
|     30 | 1997-03-12 | JAMES             |            1 |
+--------+------------+-------------------+--------------+
10 rows in set (0.00 sec)

上述语句首先依据字段 deptno 进行分组，然后每个分组再依据字段 Hiredate 进行分组，最后通过函数 GROUP_CONCAT() 统计每个分组的雇员姓名，通过函数 COUNT() 统计每个分组的雇员人数。

四、实现HAVING字句限定分组查询

如果想实现对分组进行条件限制，决不能通过关键字 WHERE 来实现，因为该关键字主要用来实现条件限制所有记录。为了实现条件限制分组数据记录，MySQL 软件专门提供了关键字 HAVING 。其语法形式：

SELECT function(field)
	FROM talbe_name
		GROUP BY field1,field2,…,fieldn
		HAVING CONDITION;

上述语句中，通过关键字 HAVING 来指定分组后的条件。
【实例】在雇员表（t_employee）中，首先按照部门号（字段 depno ）对所有雇员进行分组，然后显示平均工资高于2000 的雇员的名字。
为了更好的理解，我们分步进行
1、按照字段 deptno 进行分组

mysql> SELECT deptno
    ->  FROM t_employee
    ->          GROUP BY deptno;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
|     10 |
+--------+
3 rows in set (0.00 sec)

结果中显示依据字段 deptno 分成了3组，分别是20、30、10。

2、通过统计函数 AVG() , 显示每组（部门）的平均工资

mysql> SELECT deptno,AVG(sal) '部门平均工资'
    ->  FROM t_employee
    ->          GROUP BY deptno;
+--------+--------------+
| deptno | 部门平均工资 |
+--------+--------------+
|     20 |  2175.000000 |
|     30 |  1566.666667 |
|     10 |  2916.666667 |
+--------+--------------+
3 rows in set (0.00 sec)

上述语句不仅按照字段 deptno 分为 3 组，而且还显示出每组的平均值。

3、通过 HAVING 关键字，显示出平均工资大于 2000 的分组

mysql> SELECT deptno,AVG(sal) '部门平均工资'
    ->  FROM t_employee
    ->          GROUP BY deptno
    ->          HAVING AVG(sal)>2000;
+--------+--------------+
| deptno | 部门平均工资 |
+--------+--------------+
|     20 |  2175.000000 |
|     10 |  2916.666667 |
+--------+--------------+
2 rows in set (0.01 sec)

结果显示了平均工资大于2000的分组（部门）

4、在上面的基础上显示每个分组中雇员名称和雇员个数

mysql> SELECT deptno '部门' ,AVG(sal) '平均工资',GROUP_CONCAT(ename) '雇员姓
名',COUNT(ename) '人数'
    ->  FROM t_employee
    ->          GROUP BY deptno
    ->          HAVING AVG(sal)>2000;
+------+--------------+------------------------------+------+
| 部门 |    平均工资   | 雇员姓名                      | 人数 |
+------+--------------+------------------------------+------+
|   10 |  2916.666667 | CLARK,KING,MILLER            |    3 |
|   20 |  2175.000000 | SMITH,JONES,SCOTT,ADAMS,FORD |    5 |
+------+--------------+------------------------------+------+
2 rows in set (0.00 sec)

执行结果显示出：平均工资大于 2000 的两组，同时通过语句 GROUP_CONCAT() 和统计函数 COUNT() 显示每组中雇员的名字和人数。

生词表

单词读音译文MySQL
OFFSET偏移量
OFFSET_START起始偏移量起始偏移量
ASCascending（英 /əˈsendɪŋ）的缩写adj.(次序)上升的，渐进的升序排序
DESCdescending（英 /dɪˈsendɪŋ）的缩写adj.(次序)下降的，递减的降序排列
GROUP_CONCAT()函数，将分组中指定字段中的记录罗列出来

如有错误敬请高人指点，书写的易读性可否，希望大家多提意见。

上一篇【单表数据记录查询（二）】
下一篇【多表数据记录查询（一）】

程小雾
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
ORACLE查询删除重复记录三种方法
09-10
本文列举了3种删除重复记录的方法，分别是rowid、group by和distinct，小伙伴们可以参考一下。
单表数据记录查询
Albert__Einstein的博客
11-18 1万+
一、学习任务1：简单数据记录查询 在MySQL软件中关于数据的操作,包括插入数据记录操作（CREATE，INSERT)、查询数据记录操作（SELECT）,更新数据记录棟作（UPDATE)和删除数据记录操作（DELETE)。第9章已经详细介绍了关于数据的操作，本章将详细介绍关于数据的操作，即查询数据记录操作。 为了便于讲解，本节所涉及的查询数据记录操作，都是针对于数据库company表示雇员信息的表t_employee，关于雇员表(t_employee)的结构如下图所示，关于雇员表（t_employee)
多表数据记录查询
Albert__Einstein的博客
11-22 8429
一、学习任务1：内连接查询 第10章详细介绍了单表查询，即在关键字where子句中只涉及一张表。在具体应用中，经常需要实现在一个查询语句中显示多张表的数据，这就是所谓的多表数据记录连接查询，简称连接查询。 MySQL软件也支持连接查询，在具体实现连接查询操作时，首先将两个或两个以上的表按照某个条件连接起来，然后再查询到所要求的数据记录，查看帮助文档可以发现，连接查询分为内连接查询和外连接查询。 在具体应用中，如果需要实现多表数据记录查询，一般不使用连接查询，因为该操作效率比较低。于是MySQL软件又提
mysql 内连接查询三表数据_数据库三表连接查询怎么做
weixin_28686463的博客
01-19 1万+
展开全部1、创建三张测试表；create table test_a(aid int,aname varchar(20));create table test_b(bid int,bname varchar(20));create table test_c(aid int, bid int,value varchar(20));2、三张表e69da5e887aa3231313335323631343...
简单数据记录查询
Albert__Einstein的博客
11-22 7916
一、学习任务1：简单数据记录查询 在MySQL软件中关于数据的操作,包括插入数据记录操作（CREATE，INSERT)、查询数据记录操作（SELECT）,更新数据记录棟作（UPDATE)和删除数据记录操作（DELETE)。第9章已经详细介绍了关于数据的操作，本章将详细介绍关于数据的操作，即查询数据记录操作。 为了便于讲解，本节所涉及的查询数据记录操作，都是针对于数据库company表示雇员信息的表t_employee，关于雇员表(t_employee)的结构如下图所示，关于雇员表（t_employee)
MySQL 数据表查询
热门推荐
weixin_67281781的博客
03-31 2万+
查询数据指从数据库中获取所需要的数据。查询数据是数据库操作中最常用，也是最重要的操作。用户可以根据自己对数据的需求，使用不同的查询方式。通过不同的查询方式，可以获得不同的数据。MySQL中是使用SELECT语句来查询数据的。 一、MySQL数据表简单查询 简单查询即不含where的select语句。在此，我们讲解简单查询中最常用的两种查询：查询所有字段和查询指定字段。 1.1、MySQL查询所有字段 查询所有字段 MySQL命令： select * from students; 1.2、MyS
SQL基本使用-查询表中的记录
tr_136163798的博客
07-25 6772
查询表中的记录 1.语法 Select 字段列表 From 表名列表 Where 条件列表 Group by 分组字段 Having 分组之后的条件限定 Order by 排序 Limit 分页限定 2.基础查询 1.多个字段的查询 SELECT 字段名1，字段名2， FROM 表名； 2.查询所有 SELECT * FROM 表名； 3.去重复查询 SELECT DISTINCT 字段名1...
【性能优化】MySQL - 单表数据量过千万，采坑优化记录 - 参考
william_n的博客
01-19 6694
主要用于如何解决单表大数据量问题，提供一个解决思路。
MySQL 删除表中的数据记录
m0_65635427的博客
11-20 8254
MySQL 删除表中的数据记录
查询数据库中的重复数据记录
wanghost的专栏
03-12 7762
SELECT a.*, dlookup("r1","表2","id=" & a.id & " and name='"& a.name & "'") AS r1, dlookup("r2","表2","id=" & a.id & " and name='"& a.name & "'") AS r2。有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
mysql 三个表内连接查询_MySQL5.5多表之连接查询
weixin_30899023的博客
02-27 4750
连接查询关系型数据库管理系统中，通常将每个实体的所有信息存放在一张表中，当查询数据时，通过连接操作查询多个表中的实体信息，当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询，连接查询包括交叉连接查询、内连接查询、外连接查询，本节将针对这些连接查询进行详细的讲解。1、交叉连接查询交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积。简化说明为：两列数据各从各列中取一个...
SQL Server数据库实验_数据查询与更新_简单的单表查询.doc
08-11
熟练掌握用SELECT语句实现简单的单表查询。掌握SELECT子句、FROM子句、WHERE子句及ORDER BY 子句的用法。 二、实验原理 用SELECT语句实现简单的单表查询。在SELECT子句中用TOP关键字来限制返回到结果集中的记录数目...
mongodb数据库基础知识之连表查询
12-16
在做自己的项目时，因为刚开始接触mongodb非关系型数据库以及关系型数据库的影响还是留在脑中，总会想着进行一个连表查询，然后看官网和资料学习了下，还有那个查询时使用正则来匹配，在这里做个记录 1.mongodb正则...
【实验报告】 数据库的定义与单表查询
01-15
【实验一 数据库的定义与单表查询】 【背景】设计一个简单的图书管理系统，该系统中包括：  图书信息：书号，书名，类别，出版社，作者，定价，备注；  读者信息：读者编号，姓名，单位，性别，电话； 出版社信息...
SQL-索引篇整理
m0_59925573的博客
05-03 1391
聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。索引需要额外的磁盘空间，并降低写操作的性能。3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。红黑树，1.每个节点都是红or黑 2.根节点是黑 3.每个叶子节点都是黑色 4.红色节点的父子节点都必须是黑的 5.从任一个节点到叶子节点的所有路径都包含相同的黑色结点。
Spring事务失效的场景
最新发布
qq_51967234的博客
05-09 529
因为Spring事务的本质是动态代理，通过生成代理对象去调用方法，并且在方法前后增加事务效果;同一类中的方法调用时，无法使用代理对象调用，使用的是this调用，因此无法实现动态代理效果。:考虑动态代理的实现原理，无论是基于JDK还是CGLib，都不允许final和static的修饰。: Spring默认只会在遇到error和RunTimeException时才会回滚。:使用catch进行捕获之后，Spring无法感知到异常，无法回滚。
常用SQL命令
wenxiaoba的博客
05-02 1580
常用SQL语句使用说明和示例
sql注入手注语句
2302_80044238的博客
05-03 396
id=1'and (ascii(substr((select table_name from information_schema.tables where table_schema='security' limit 0,1),1,1)))=101 -- asd//爆表名。id=1' and(select count(table_name) from information_schema.tables where table_schema= database() )=4-- asd //查看库里有几个表。
SQL——高级教程【菜鸟教程】
weixin_45753504的博客
05-02 658
sql语句——连接
三表查询
05-29
三表查询是指在关系型数据库中，同时查询三个表的数据，并根据某些条件进行关联。通常使用 SQL 语言来实现。 例如，我们有三个表：学生表、课程表、成绩表。学生表包含学生的基本信息，课程表包含课程的基本信息，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值