侧边栏壁纸
博主头像
乌拉队长博主等级

你只管努力,其余的交给命运

  • 累计撰写 122 篇文章
  • 累计创建 34 个标签
  • 累计收到 31 条评论

目 录CONTENT

文章目录

MySQL中using、having用法

乌拉队长
2022-02-13 / 0 评论 / 0 点赞 / 778 阅读 / 6,153 字

MySQL中using、having用法

初始化数据

定义两个表:学生信息表student和学生分数表sc。
定义如下:

# student表
CREATE TABLE `student` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  `sex` varchar(10) DEFAULT NULL COMMENT '学生性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COMMENT='学生表' 

# sc表
CREATE TABLE `sc` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '学生成绩id',
  `score` int(20) DEFAULT NULL COMMENT '学生分数',
  `sid` int(20) DEFAULT NULL COMMENT '学生id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COMMENT='学生成绩表'

表结构如下:

# student表
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(20)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| sex   | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

# sc表
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(20) | NO   | PRI | NULL    | auto_increment |
| score | int(20) | YES  |     | NULL    |                |
| sid   | int(20) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+

向表中插入数据,两个表中的数据如下:

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | 张三 | 男   |
|  2 | 李四 | 男   |
|  3 | 王五 | NULL |
|  4 | NULL | 女   |
|  5 | 赵六 | 女   |
+----+------+------+


mysql> select * from sc;
+----+-------+------+
| id | score | sid  |
+----+-------+------+
|  1 |    70 |    1 |
|  2 |    80 |    3 |
|  3 |    95 |    2 |
|  4 |    99 | NULL |
|  5 |  NULL |    4 |
|  6 |  NULL |    8 |
|  7 |    60 |   12 |
+----+-------+------+

USING的用法

我们知道自然连接(不清楚什么是自然连接的可以看一下这里:MySQL内连接、左连接、右连接、交叉连接、自连接的区别)能够简化等值连接的书写,但是写法不够明了。那么这节就找一个既简单又明了的写法,那就是使用using()函数了.
using()函数用于两张表之间的join等值连接查询,但是using()函数要求参数中的字段需要在两张表中都存在。

注意:using()函数可以传入多个字段作为参数

# 使用using()函数查询两个表中id相同的记录
mysql> select * from student t1 join sc t2  using(id);
+----+------+------+-------+------+
| id | name | sex  | score | sid  |
+----+------+------+-------+------+
|  1 | 张三 | 男   |    70 |    1 |
|  2 | 李四 | 男   |    80 |    3 |
|  3 | 王五 | NULL |    95 |    2 |
|  4 | NULL | 女   |    99 | NULL |
|  5 | 赵六 | 女   |  NULL |    4 |
+----+------+------+-------+------+


# 上述查询语句与如下语句效果相同
mysql> select * from student t1 join sc t2 ON t1.id=t2.id;
+----+------+------+----+-------+------+
| id | name | sex  | id | score | sid  |
+----+------+------+----+-------+------+
|  1 | 张三 | 男   |  1 |    70 |    1 |
|  2 | 李四 | 男   |  2 |    80 |    3 |
|  3 | 王五 | NULL |  3 |    95 |    2 |
|  4 | NULL | 女   |  4 |    99 | NULL |
|  5 | 赵六 | 女   |  5 |  NULL |    4 |
+----+------+------+----+-------+------+


# 或者
mysql> select * from student t1, sc t2 where t1.id=t2.id;
+----+------+------+----+-------+------+
| id | name | sex  | id | score | sid  |
+----+------+------+----+-------+------+
|  1 | 张三 | 男   |  1 |    70 |    1 |
|  2 | 李四 | 男   |  2 |    80 |    3 |
|  3 | 王五 | NULL |  3 |    95 |    2 |
|  4 | NULL | 女   |  4 |    99 | NULL |
|  5 | 赵六 | 女   |  5 |  NULL |    4 |
+----+------+------+----+-------+------+

HAVING用法

通常我们都在where后添加过滤条件对表中的数据进行过滤,然而,如果在过滤条件中使用了聚合函数(即:MAX()、MIN()、SUM()等)时,那么就必须使用HAVING关键字来替换WHERE。否则将会报错。

为了验证方便,重新创建员工工资表employees,表的定义如下:

# employees表
CREATE TABLE `employees` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '工资id',
  `employee_id` int(11) DEFAULT NULL COMMENT '员工id',
  `department_id` int(20) DEFAULT NULL COMMENT '部门id',
  `salary` int(20) DEFAULT NULL COMMENT '工资',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 COMMENT='员工工资表' |

表结构如下:

# employees表
+---------------+---------+------+-----+---------+----------------+
| Field         | Type    | Null | Key | Default | Extra          |
+---------------+---------+------+-----+---------+----------------+
| id            | int(20) | NO   | PRI | NULL    | auto_increment |
| employee_id   | int(11) | YES  |     | NULL    |                |
| department_id | int(20) | YES  |     | NULL    |                |
| salary        | int(20) | YES  |     | NULL    |                |
+---------------+---------+------+-----+---------+----------------+

向表中插入数据,表数据如下:

mysql> select * from employees;
+----+-------------+---------------+--------+
| id | employee_id | department_id | salary |
+----+-------------+---------------+--------+
|  1 |        1000 |           200 |   4200 |
|  2 |        1001 |           200 |   4500 |
|  4 |        1002 |           200 |   6500 |
|  5 |        1003 |           200 |  10500 |
|  6 |        1004 |           200 |   5100 |
|  7 |        1005 |           200 |   3700 |
|  8 |        1006 |           210 |   3900 |
|  9 |        1007 |           210 |   9400 |
| 10 |        1008 |           210 |   9800 |
| 11 |        1009 |           210 |   5800 |
| 12 |        1010 |           210 |   4500 |
| 13 |        1011 |           220 |   4100 |
| 14 |        1012 |           220 |   4900 |
| 15 |        1013 |           220 |   6300 |
| 16 |        1014 |           220 |   4300 |
| 17 |        1015 |           220 |   2700 |
| 18 |        1016 |           220 |   1700 |
| 19 |        1017 |           220 |  10700 |
| 20 |        1018 |           220 |  15700 |
| 21 |        1019 |           220 |   1900 |
| 22 |        1020 |           220 |   5200 |
+----+-------------+---------------+--------+

假如现在有一个需求:
需求1: 查询所有部门中,部门的平均工资大于6000的部门id;

# 如果使用 where+条件 进行过滤,则会报错,因为where后不支持聚合函数,只支持简单逻辑运算
mysql> select department_id,avg(salary) from employees where avg(salary)>6000 group by department_id;
ERROR 1111 (HY000): Invalid use of group function


# 因此,应该使用 having+条件 进行过滤,并且分组字段必须包含在查询字段中,
# 也就是说group by后面的字段department_id必须包含在select后面的查询结果字段中
mysql> select department_id,avg(salary) from employees group by department_id having avg(salary)>6000;
+---------------+-------------+
| department_id | avg(salary) |
+---------------+-------------+
|           210 |   6680.0000 |
+---------------+-------------+


# 另外,select后面的salary字段应使用avg(salary),否则将返回满足条件的部门中的第一条数据
mysql> select department_id,salary from employees group by department_id having avg(salary)>6000;
+---------------+--------+
| department_id | salary |
+---------------+--------+
|           210 |   3900 |
+---------------+--------+

值得注意的是:where 的位置应在 group by 之前,而having应在 group by 之后

这里提一下SQL语句的顺序,现在我们通常用的比较多的应该是MySQL5.7及以上版本,他们使用的SQL99语法规则,旧版的规则暂时忽略

# SQL99语法
select 字段|表达式|常量值|函数 (可以使用聚合函数)
from 表名(单表查询)
[from 表1 [inner|left|right|cross|natural] join 表2 on 多表的连接条件]
where 过滤条件 (不可以使用聚合函数)
group by 字段
having 过滤条件 (可以使用聚合函数)
order by 字段 [asc|desc]
limit 起始索引,增量长度

再进一步,假如现在又增加了一个条件:
需求2: 查询在department_id为210和220的两个部门中,最高工资大于10000的部门id;

首先我们查询一下所有部门中最高工资大于10000的部门有几个:

mysql> select department_id,max(salary) from employees group by department_id having max(salary)>10000;
+---------------+-------------+
| department_id | max(salary) |
+---------------+-------------+
|           200 |       10500 |
|           220 |       15700 |
+---------------+-------------+

通过查询结果可以看到只有200和220两个部门满足条件,然后我们可以知道需求2的正确结果应该只返回部门id为220的信息即可。

然后我们有如下两种查询方式:

# 第一种方式:使用where+having过滤
mysql> select department_id,max(salary) from employees where department_id in (210,220) group by department_id having max(salary)>10000;
+---------------+-------------+
| department_id | max(salary) |
+---------------+-------------+
|           220 |       15700 |
+---------------+-------------+


# 第二种方式:使用having过滤
mysql> select department_id,max(salary) from employees group by department_id having max(salary)>10000 and department_id in (210,220);
+---------------+-------------+
| department_id | max(salary) |
+---------------+-------------+
|           220 |       15700 |
+---------------+-------------+

到这里就会出现一个问题,既然having后既可以接聚合函数也可以接普通调条件,那为什么还要用where
至于wherehaving的区别,见:MySQL中where和having的区别、SQL语句执行顺序

0

评论区