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？
至于where和having的区别，见：MySQL中where和having的区别、SQL语句执行顺序
评论区