⑥、order by (排序）
mysql> select * from emp order by salary; # 按照工资升序排序
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
| 11 | 丁丁 | female | 18 | 2011-03-12 | 销售部 | NULL | 1000 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | 销售部 | NULL | 2000 | 402 | 2 |
| 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | 销售部 | NULL | 3000 | 402 | 2 |
| 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
| 13 | 格格 | female | 28 | 2017-01-27 | 销售部 | NULL | 4000 | 402 | 2 |
| 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
| 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
| 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | 运营部 | NULL | 17000 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | 运营部 | NULL | 18000 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | 运营部 | NULL | 19000 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | 运营部 | NULL | 20000 | 403 | 3 |
| 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
| 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
mysql> select * from emp order by age,salary; # 首选排序规则为age，当排完序的age中有相同的时，在相同age的几个记录中进行salary排序。
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
| 11 | 丁丁 | female | 18 | 2011-03-12 | 销售部 | NULL | 1000 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | 销售部 | NULL | 3000 | 402 | 2 |
| 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | 运营部 | NULL | 17000 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | 运营部 | NULL | 18000 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | 运营部 | NULL | 19000 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | 运营部 | NULL | 20000 | 403 | 3 |
| 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
| 13 | 格格 | female | 28 | 2017-01-27 | 销售部 | NULL | 4000 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | 销售部 | NULL | 2000 | 402 | 2 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
| 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
| 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
| 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
# 默认为升序，如果想要降序，在后面加上desc。
mysql> select * from emp order by salary desc;
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
| 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
| 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | 运营部 | NULL | 20000 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | 运营部 | NULL | 19000 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | 运营部 | NULL | 18000 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | 运营部 | NULL | 17000 | 403 | 3 |
| 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
| 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
| 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
| 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
| 13 | 格格 | female | 28 | 2017-01-27 | 销售部 | NULL | 4000 | 402 | 2 |
| 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | 销售部 | NULL | 3000 | 402 | 2 |
| 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | 销售部 | NULL | 2000 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | 销售部 | NULL | 1000 | 402 | 2 |
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
# 来个复杂的：
# 统计各部门年龄在25岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
mysql> select post,avg(salary) from emp
-> where age>25
-> group by post
-> having avg(salary)>1000
-> order by avg(salary) desc;
+-----------+-------------+
| post | avg(salary) |
+-----------+-------------+
| 教学部 | 204780.0000 |
| 运营部 | 10000.0000 |
| 销售部 | 3000.0000 |
+-----------+-------------+⑦、limit （限制显示条数）
# 限制展示条数 limit 参数1 limit 参数1，参数2
mysql> select * from emp limit 5; # 单个数字5代表从最前面开始显示5条
+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
| 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
| 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+
mysql> select * from emp limit 2,6; # 从起始行数为2开始往后显示6行，这里不包含其实行数2。
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
| 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
| 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
| 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
| 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
复制代码
8、正则（没错，表查询也能用正则匹配）
复制代码
# 查询记录，其中匹配名字条件为以j开头，n或者y结尾的名字
mysql> select * from emp where name regexp '^j.*(n|y)$';
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+二、多表查询
多个表之间的查询一般都是在 表之间存在某种逻辑关联的情况下进行的查询，这种逻辑上的关联其实就是表中某个字段名和另外一个表中的字段名存在一个一一对应的关系或者关联。
先创建2张表作为示例
mysql> #建表
mysql> create table dep(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> create table emp(
-> id int primary key auto_increment,
-> name varchar(20),
-> sex enum('male','female') not null default 'male',
-> age int,
-> dep_id int
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> #插入数据
mysql> insert into dep values
-> (200,'技术'),
-> (201,'人力资源'),
-> (202,'销售'),
-> (203,'运营');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into emp(name,sex,age,dep_id) values
-> ('jason','male',18,200),
-> ('egon','female',48,201),
-> ('kevin','male',38,201),
-> ('nick','female',28,202),
-> ('owen','male',18,200),
-> ('jerry','female',18,204)
-> ;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
4 rows in set (0.00 sec)
mysql> select * from emp;
+----+-------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-------+--------+------+--------+
| 1 | jason | male | 18 | 200 |
| 2 | egon | female | 48 | 201 |
| 3 | kevin | male | 38 | 201 |
| 4 | nick | female | 28 | 202 |
| 5 | owen | male | 18 | 200 |
| 6 | jerry | female | 18 | 204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)多表查询示例：
# 笛卡尔积 多表查询
mysql> select * from emp,dep;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 1 | jason | male | 18 | 200 | 201 | 人力资源 |
| 1 | jason | male | 18 | 200 | 202 | 销售 |
| 1 | jason | male | 18 | 200 | 203 | 运营 |
| 2 | egon | female | 48 | 201 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 2 | egon | female | 48 | 201 | 202 | 销售 |
| 2 | egon | female | 48 | 201 | 203 | 运营 |
| 3 | kevin | male | 38 | 201 | 200 | 技术 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 202 | 销售 |
| 3 | kevin | male | 38 | 201 | 203 | 运营 |
| 4 | nick | female | 28 | 202 | 200 | 技术 |
| 4 | nick | female | 28 | 202 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 4 | nick | female | 28 | 202 | 203 | 运营 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
| 5 | owen | male | 18 | 200 | 201 | 人力资源 |
| 5 | owen | male | 18 | 200 | 202 | 销售 |
| 5 | owen | male | 18 | 200 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | 200 | 技术 |
| 6 | jerry | female | 18 | 204 | 201 | 人力资源 |
| 6 | jerry | female | 18 | 204 | 202 | 销售 |
| 6 | jerry | female | 18 | 204 | 203 | 运营 |
+----+-------+--------+------+--------+------+--------------+
# 将所有的数据都对应了一遍进行合并，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据
# 查询员工及所在部门的信息
mysql> select , from emp,dep where emp.dep_id=;
+--------------+-------+
| name | name |
+--------------+-------+
| 技术 | jason |
| 人力资源 | egon |
| 人力资源 | kevin |
| 销售 | nick |
| 技术 | owen |
+--------------+-------+
# 查询部门为技术部的员工及部门信息
mysql> select * from emp,dep where emp.dep_id= and ='技术';
+----+-------+------+------+--------+------+--------+
| id | name | sex | age | dep_id | id | name |
+----+-------+------+------+--------+------+--------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
+----+-------+------+------+--------+------+--------+多表查询分为内连接、左连接、右连接、全连接，在产生连接后 如需跟条件此时就不能用到where，只能使用on作为替代。
# 将2张表关联到一起的操作，有专门的方法
# 1、内连接（inner join）：只取两张表有对应关系的记录
mysql> select * from emp inner join dep on emp.dep_id = ;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
+----+-------+--------+------+--------+------+--------------+
# 2、左连接（left join）：在内连接的基础上保留左表满足条件的全部内容，右表没有对应上的内容用null表示
mysql> select * from emp left join dep on emp.dep_id = ;
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
+----+-------+--------+------+--------+------+--------------+
# 3 右连接（right join）：在内连接的基础上，保留右表满足条件的全部内容，左表没有对应上的内容用null表示
mysql> select * from emp right join dep on emp.dep_id = ;
+------+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+-------+--------+------+--------+------+--------------+
# 4 全连接（用union将左连接和右连接联合起来）：在内连接的基础上保留左右表没有对应
mysql> select * from emp left join dep on emp.dep_id =
-> union
-> select * from emp right join dep on emp.dep_id = ;
+------+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 5 | owen | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 38 | 201 | 201 | 人力资源 |
| 4 | nick | female | 28 | 202 | 202 | 销售 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+-------+--------+------+--------+------+--------------+三、子查询：
#就是将一个查询语句的结果用括号括起来当做另一个查询语句的条件去用
# 接着上面的表：
mysql> select * from emp;
+----+-------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-------+--------+------+--------+
| 1 | jason | male | 18 | 200 |
| 2 | egon | female | 48 | 201 |
| 3 | kevin | male | 38 | 201 |
| 4 | nick | female | 28 | 202 |
| 5 | owen | male | 18 | 200 |
| 6 | jerry | female | 18 | 204 |
+----+-------+--------+------+--------+
mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
# 1.查询部门是技术或者人力资源的员工信息
mysql> # 先将技术和人力资源部门的id号先找出来：
mysql> select id from dep where name='技术' or name='人力资源';
+------+
| id |
+------+
| 200 |
| 201 |
+------+
mysql> # 在将上述查询到的id号作为条件，进行再一次查询：
mysql> select * from emp where dep_id in (select id from dep where name='技术' or name='人力资源');
+----+-------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-------+--------+------+--------+
| 1 | jason | male | 18 | 200 |
| 2 | egon | female | 48 | 201 |
| 3 | kevin | male | 38 | 201 |
| 5 | owen | male | 18 | 200 |
+----+-------+--------+------+--------+
--------------------------------------------------------------------------------------------------------------------------------
# 2.每个部门最新入职的员工
有这张表emp：
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 外交部 | NULL | 6600 | 401 | 0 |
| 2 | egon | male | 78 | 2015-03-02 | 教学部 | NULL | 1000000 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | 教学部 | NULL | 8300 | 401 | 1 |
| 4 | tank | male | 73 | 2014-07-01 | 教学部 | NULL | 3500 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | 教学部 | NULL | 2100 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | 教学部 | NULL | 9000 | 401 | 1 |
| 7 | nick | male | 18 | 1900-03-01 | 教学部 | NULL | 30000 | 401 | 1 |
| 8 | sean | male | 48 | 2010-11-11 | 教学部 | NULL | 10000 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | 销售部 | NULL | 3000 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | 销售部 | NULL | 2000 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | 销售部 | NULL | 1000 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | 销售部 | NULL | 3000 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | 销售部 | NULL | 4000 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | 运营部 | NULL | 10000 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | 运营部 | NULL | 20000 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | 运营部 | NULL | 19000 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | 运营部 | NULL | 18000 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | 运营部 | NULL | 17000 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
# ①先将入职日期最大的员工查询处理起名为表t2
# (select post,max(hire_date) as max_date from emp group by post) as t2
# ②为了易读性，将emp表起名为t1，将需要查询的结果字段名写在select后（此时包括t2中的字段，因为select是最后执行的语句）
# ③ 用内连接将t1和t2连接起来，通过t1.hire_date=t2.max_date
最终结果：
mysql> select ,,t1.sex,t1.age,t1.salary,t1.office,t2.* from emp as t1
-> inner join (select post,max(hire_date) as max_date from emp group by post) as t2 where
-> t1.hire_date=t2.max_date;
+----+--------+--------+-----+---------+--------+-----------+------------+
| id | name | sex | age | salary | office | post | max_date |
+----+--------+--------+-----+---------+--------+-----------+------------+
| 1 | jason | male | 18 | 6600 | 401 | 外交部 | 2017-03-01 |
| 2 | egon | male | 78 | 1000000 | 401 | 教学部 | 2015-03-02 |
| 13 | 格格 | female | 28 | 4000 | 402 | 销售部 | 2017-01-27 |
| 14 | 张野 | male | 28 | 10000 | 403 | 运营部 | 2016-03-11 |
+----+--------+--------+-----+---------+--------+-----------+------------+
记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询额外题：
部门中薪资超过部门平均薪资的员工姓名及薪资
mysql> select ,t1.salary,t1.post,t2.avg_salary from emp t1
-> inner join (select post,avg(salary) as avg_salary from emp group by post) as t2
-> where t1.post=t2.post and t1.salary>t2.avg_salary;
+-----------+---------+-----------+-------------+
| name | salary | post | avg_salary |
+-----------+---------+-----------+-------------+
| egon | 1000000 | 教学部 | 151842.8571 |
| 歪歪 | 3000 | 销售部 | 2600.0000 |
| 星星 | 3000 | 销售部 | 2600.0000 |
| 格格 | 4000 | 销售部 | 2600.0000 |
| 程咬金 | 20000 | 运营部 | 16800.0000 |
| 程咬银 | 19000 | 运营部 | 16800.0000 |
| 程咬铜 | 18000 | 运营部 | 16800.0000 |
| 程咬铁 | 17000 | 运营部 | 16800.0000 |
+-----------+---------+-----------+-------------+
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
