1 案例1：内连接
1.1 问题
- 练习等值连接查询
- 练习非等值连接查询
1.2 方案
使用tarena库下的3张表做今天的查询练习。如图-1、图-2、图-3所示
- departments：部门表，存储部门信息
- employees： 员工表，存储员工信息
- salary: 工资表，存储工资信息
三张表的关系如图
1.3 步骤
实现此案例需要按照如下步骤进行。
步骤一：练习等值连接查询
1) 查询每个员工所在的部门名
Use tarena;
select name, dept_name
from employees inner join departments on employees.dept_id=departments.dept_id;2）查询员工编号8 的 员工所在部门的部门名称
select name, dept_name from employees inner join departments
on employees.dept_id=departments.dept_id where employees.employee_id=8;3）查询每个员工所有信息及所在的部门名称
给表名定义别名 ，定义别名后必须使用别名表示表名
select e.* , d.dept_name
from employees as e inner join departments as d on e.dept_id=d.dept_id;4）查询每个员工姓名、部门编号、部门名称
两个表有同名表头，表头名前必须加表名
select e.dept_id , name , dept_name from employees as e inner join departments as d on e.dept_id=d.dept_id;其他练习：对连接后的查询结果，筛选、分组、排序、过滤
1）查询11号员工的名字及2018年每个月总工资
select e.employee_id, name, date, basic+bonus as total
from employees as e inner join salary as s
on e.employee_id=s.employee_id
where year(date)=2018 and e.employee_id=11;2） 查询每个员工2018年的总工资
#没分组前
select employees.employee_id,date,basic,bonus from employees inner join salary
on employees.employee_id=salary.employee_id where year(date)=2018;
# 分组后
select employees.employee_id, sum(basic+bonus) from employees inner join salary
on employees.employee_id=salary.employee_id
where year(date)=2018 group by employees.employee_id;3）查询每个员工2018年的总工资，按总工资升序排列
select employees.employee_id, sum(basic+bonus) as total
from employees inner join salary on employees.employee_id=salary.employee_id
where year(salary.date)=2018 group by employee_id order by total asc;4）查询2018年总工资大于30万的员工，按2018年总工资降序排列
select employees.employee_id, sum(basic+bonus) as total
from employees inner join salary on employees.employee_id=salary.employee_id
where year(salary.date)=2018
group by employees.employee_id
having total > 300000
order by total desc;步骤二：练习非等值连接查询
环境准备：
创建工资等级表wage_grade ,表头如下：
Id 行号
Grade 工资等级
Low 等级最低值
High 等级最高值
建表
create table tarena.wage_grade(
id int, grade char(1), low int , high int );插入记录
insert into wage_grade(id,grade,low,high)
values
(1,'A', 5000, 8000),
(2,'B', 8001, 10000),
(3,'C', 10001, 15000),
(4,'D', 15001, 20000),
(5,'E', 20001, 1000000);查看表记录
select * from wage_grade;
+----+-------+-------+---------+
| id | grade | low | high |
+----+-------+-------+---------+
| 1 | A | 5000 | 8000 |
| 2 | B | 8001 | 10000 |
| 3 | C | 10001 | 15000 |
| 4 | D | 15001 | 20000 |
| 5 | E | 20001 | 1000000 |
+----+-------+-------+---------+
5 rows in set (0.00 sec)1）查询2018年12月员工基本工资级别
select employee_id, date, basic, grade
from salary as s inner join wage_grade as g
on s.basic between g.low and g.high
where year(date)=2018 and month(date)=12;
+-------------+------------+-------+-------+
| employee_id | date | basic | grade |
+-------------+------------+-------+-------+
| 1 | 2018-12-10 | 17016 | D |
| 2 | 2018-12-10 | 20662 | E |
| 3 | 2018-12-10 | 9724 | B |
| 4 | 2018-12-10 | 17016 | D |
| 5 | 2018-12-10 | 17016 | D |
| 6 | 2018-12-10 | 17016 | D |
| 7 | 2018-12-10 | 23093 | E |
| 8 | 2018-12-10 | 23093 | E |
| 9 | 2018-12-10 | 13369 | C |
| 10 | 2018-12-10 | 21878 | E |
| 11 | 2018-12-10 | 17016 | D |
| 12 | 2018-12-10 | 15800 | D |
| 13 | 2018-12-10 | 18231 | D |
| 14 | 2018-12-10 | 12154 | C |
| 15 | 2018-12-10 | 10938 | C |
| 16 | 2018-12-10 | 21878 | E |
| 17 | 2018-12-10 | 19448 | D |
| 18 | 2018-12-10 | 7292 | A |
| 20 | 2018-12-10 | 19448 | D |
| 21 | 2018-12-10 | 18231 | D |
| 22 | 2018-12-10 | 14585 | C |
| 25 | 2018-12-10 | 23093 | E |
| 26 | 2018-12-10 | 8507 | B |
| 27 | 2018-12-10 | 24309 | E |
| 28 | 2018-12-10 | 17016 | D |
| 29 | 2018-12-10 | 23093 | E |
| 30 | 2018-12-10 | 7292 | A |
| 31 | 2018-12-10 | 25524 | E |
| 32 | 2018-12-10 | 18231 | D |
| 33 | 2018-12-10 | 23093 | E |
| 34 | 2018-12-10 | 23093 | E |
| 37 | 2018-12-10 | 24309 | E |
| 38 | 2018-12-10 | 23093 | E |
| 39 | 2018-12-10 | 14585 | C |
| 40 | 2018-12-10 | 20662 | E |
| 41 | 2018-12-10 | 9724 | B |
| 43 | 2018-12-10 | 13369 | C |
| 44 | 2018-12-10 | 13369 | C |
| 45 | 2018-12-10 | 14585 | C |
| 46 | 2018-12-10 | 15800 | D |
| 47 | 2018-12-10 | 13369 | C |
| 48 | 2018-12-10 | 25524 | E |
| 49 | 2018-12-10 | 15800 | D |
| 50 | 2018-12-10 | 10938 | C |
| 51 | 2018-12-10 | 6076 | A |
| 52 | 2018-12-10 | 19448 | D |
| 53 | 2018-12-10 | 25524 | E |
| 54 | 2018-12-10 | 21878 | E |
| 55 | 2018-12-10 | 6076 | A |
| 56 | 2018-12-10 | 23093 | E |
| 57 | 2018-12-10 | 23093 | E |
| 58 | 2018-12-10 | 13369 | C |
| 59 | 2018-12-10 | 10938 | C |
| 60 | 2018-12-10 | 13369 | C |
| 61 | 2018-12-10 | 24309 | E |
| 62 | 2018-12-10 | 15800 | D |
| 63 | 2018-12-10 | 15800 | D |
| 64 | 2018-12-10 | 6076 | A |
| 65 | 2018-12-10 | 14585 | C |
| 66 | 2018-12-10 | 19448 | D |
| 67 | 2018-12-10 | 9724 | B |
| 68 | 2018-12-10 | 25524 | E |
| 69 | 2018-12-10 | 13369 | C |
| 70 | 2018-12-10 | 12154 | C |
| 72 | 2018-12-10 | 13369 | C |
| 73 | 2018-12-10 | 17016 | D |
| 74 | 2018-12-10 | 12154 | C |
| 76 | 2018-12-10 | 20662 | E |
| 77 | 2018-12-10 | 8507 | B |
| 78 | 2018-12-10 | 10938 | C |
| 79 | 2018-12-10 | 15800 | D |
| 81 | 2018-12-10 | 17016 | D |
| 82 | 2018-12-10 | 8507 | B |
| 83 | 2018-12-10 | 9724 | B |
| 84 | 2018-12-10 | 10938 | C |
| 85 | 2018-12-10 | 15800 | D |
| 86 | 2018-12-10 | 24309 | E |
| 87 | 2018-12-10 | 7292 | A |
| 88 | 2018-12-10 | 20662 | E |
| 89 | 2018-12-10 | 7292 | A |
| 90 | 2018-12-10 | 6076 | A |
| 91 | 2018-12-10 | 20662 | E |
| 92 | 2018-12-10 | 19448 | D |
| 93 | 2018-12-10 | 8507 | B |
| 94 | 2018-12-10 | 14585 | C |
| 95 | 2018-12-10 | 19448 | D |
| 96 | 2018-12-10 | 14585 | C |
| 97 | 2018-12-10 | 7292 | A |
| 98 | 2018-12-10 | 19448 | D |
| 99 | 2018-12-10 | 15800 | D |
| 100 | 2018-12-10 | 14585 | C |
| 101 | 2018-12-10 | 7292 | A |
| 102 | 2018-12-10 | 23093 | E |
| 105 | 2018-12-10 | 21878 | E |
| 106 | 2018-12-10 | 23093 | E |
| 107 | 2018-12-10 | 18231 | D |
| 108 | 2018-12-10 | 18231 | D |
| 109 | 2018-12-10 | 8507 | B |
| 110 | 2018-12-10 | 18231 | D |
| 111 | 2018-12-10 | 6076 | A |
| 112 | 2018-12-10 | 24309 | E |
| 113 | 2018-12-10 | 10938 | C |
| 114 | 2018-12-10 | 6076 | A |
| 115 | 2018-12-10 | 13369 | C |
| 116 | 2018-12-10 | 9724 | B |
| 117 | 2018-12-10 | 25524 | E |
| 118 | 2018-12-10 | 23093 | E |
| 119 | 2018-12-10 | 23093 | E |
| 120 | 2018-12-10 | 10938 | C |
| 121 | 2018-12-10 | 9724 | B |
| 122 | 2018-12-10 | 21878 | E |
| 123 | 2018-12-10 | 19448 | D |
| 124 | 2018-12-10 | 14585 | C |
| 125 | 2018-12-10 | 17016 | D |
| 126 | 2018-12-10 | 10938 | C |
| 127 | 2018-12-10 | 17016 | D |
| 129 | 2018-12-10 | 14585 | C |
| 130 | 2018-12-10 | 9724 | B |
| 132 | 2018-12-10 | 10938 | C |
| 133 | 2018-12-10 | 6076 | A |
+-------------+------------+-------+-------+
120 rows in set (0.00 sec)2）查询2018年12月员工各基本工资级别的人数
select grade as 工资等级, count(employee_id) as 总人数
from salary as s inner join wage_grade as g
on s.basic between g.low and g.high
where year(date)=2018 and month(date)=12
group by grade;
+--------------+-----------+
| 工资等级 | 总人数 |
+--------------+-----------+
| D | 32 |
| E | 33 |
| B | 12 |
| C | 30 |
| A | 13 |
+--------------+-----------+
5 rows in set (0.00 sec)3）查询2018年12月员工基本工资级别，员工需要显示姓名
//3张表连接的例子
select name as 姓名, date as 发工资日期, basic as 基本工资, grade as 工资等级
from employees as e inner join salary as s on e.employee_id=s.employee_id
inner join wage_grade as g on s.basic between g.low and g.high
where year(date)=2018 and month(date)=12;
+-----------+-----------------+--------------+--------------+
| 姓名 | 发工资日期 | 基本工资 | 工资等级 |
+-----------+-----------------+--------------+--------------+
| 梁伟 | 2018-12-10 | 17016 | D |
| 郭岩 | 2018-12-10 | 20662 | E |
| 李玉英 | 2018-12-10 | 9724 | B |
| 张健 | 2018-12-10 | 17016 | D |
| 郑静 | 2018-12-10 | 17016 | D |
| 牛建军 | 2018-12-10 | 17016 | D |
| 刘斌 | 2018-12-10 | 23093 | E |
| 汪云 | 2018-12-10 | 23093 | E |
| 张建平 | 2018-12-10 | 13369 | C |
| 郭娟 | 2018-12-10 | 21878 | E |
| 郭兰英 | 2018-12-10 | 17016 | D |
| 王英 | 2018-12-10 | 15800 | D |
| 王楠 | 2018-12-10 | 18231 | D |
| 廖娜 | 2018-12-10 | 12154 | C |
| 窦红梅 | 2018-12-10 | 10938 | C |
| 聂想 | 2018-12-10 | 21878 | E |
| 陈阳 | 2018-12-10 | 19448 | D |
| 戴璐 | 2018-12-10 | 7292 | A |
| 蒋红 | 2018-12-10 | 19448 | D |
| 曹宁 | 2018-12-10 | 18231 | D |
| 吕刚 | 2018-12-10 | 14585 | C |
| 邵佳 | 2018-12-10 | 23093 | E |
| 党丽 | 2018-12-10 | 8507 | B |
| 梁勇 | 2018-12-10 | 24309 | E |
| 郑秀珍 | 2018-12-10 | 17016 | D |
| 胡秀云 | 2018-12-10 | 23093 | E |
| 邢淑兰 | 2018-12-10 | 7292 | A |
| 刘海燕 | 2018-12-10 | 25524 | E |
| 冯建国 | 2018-12-10 | 18231 | D |
| 曹杰 | 2018-12-10 | 23093 | E |
| 苗桂花 | 2018-12-10 | 23093 | E |
| 朱淑兰 | 2018-12-10 | 24309 | E |
| 曹凯 | 2018-12-10 | 23093 | E |
| 张倩 | 2018-12-10 | 14585 | C |
| 王淑珍 | 2018-12-10 | 20662 | E |
| 陈玉 | 2018-12-10 | 9724 | B |
| 王波 | 2018-12-10 | 13369 | C |
| 黄文 | 2018-12-10 | 13369 | C |
| 陈刚 | 2018-12-10 | 14585 | C |
| 罗建华 | 2018-12-10 | 15800 | D |
| 黄建平 | 2018-12-10 | 13369 | C |
| 范秀英 | 2018-12-10 | 25524 | E |
| 李平 | 2018-12-10 | 15800 | D |
| 臧龙 | 2018-12-10 | 10938 | C |
| 吴静 | 2018-12-10 | 6076 | A |
| 张冬梅 | 2018-12-10 | 19448 | D |
| 邢成 | 2018-12-10 | 25524 | E |
| 孙丹 | 2018-12-10 | 21878 | E |
| 梁静 | 2018-12-10 | 6076 | A |
| 陈洁 | 2018-12-10 | 23093 | E |
| 许辉 | 2018-12-10 | 23093 | E |
| 张伟 | 2018-12-10 | 13369 | C |
| 钟倩 | 2018-12-10 | 10938 | C |
| 贺磊 | 2018-12-10 | 13369 | C |
| 沈秀梅 | 2018-12-10 | 24309 | E |
| 林刚 | 2018-12-10 | 15800 | D |
| 王玉华 | 2018-12-10 | 15800 | D |
| 徐金凤 | 2018-12-10 | 6076 | A |
| 张淑英 | 2018-12-10 | 14585 | C |
| 罗岩 | 2018-12-10 | 19448 | D |
| 潘玲 | 2018-12-10 | 9724 | B |
| 柴冬梅 | 2018-12-10 | 25524 | E |
| 谢莹 | 2018-12-10 | 13369 | C |
| 傅雪 | 2018-12-10 | 12154 | C |
| 赵杰 | 2018-12-10 | 13369 | C |
| 王璐 | 2018-12-10 | 17016 | D |
| 赵成 | 2018-12-10 | 12154 | C |
| 刘桂兰 | 2018-12-10 | 20662 | E |
| 吴丽娟 | 2018-12-10 | 8507 | B |
| 张娜 | 2018-12-10 | 10938 | C |
| 叶欣 | 2018-12-10 | 15800 | D |
| 徐成 | 2018-12-10 | 17016 | D |
| 韩丹 | 2018-12-10 | 8507 | B |
| 蒋秀芳 | 2018-12-10 | 9724 | B |
| 朱文 | 2018-12-10 | 10938 | C |
| 刘玲 | 2018-12-10 | 15800 | D |
| 张宇 | 2018-12-10 | 24309 | E |
| 田萍 | 2018-12-10 | 7292 | A |
| 田英 | 2018-12-10 | 20662 | E |
| 唐芳 | 2018-12-10 | 7292 | A |
| 莫凤兰 | 2018-12-10 | 6076 | A |
| 李建华 | 2018-12-10 | 20662 | E |
| 陶红 | 2018-12-10 | 19448 | D |
| 余春梅 | 2018-12-10 | 8507 | B |
| 熊东 | 2018-12-10 | 14585 | C |
| 毛丹 | 2018-12-10 | 19448 | D |
| 胡瑜 | 2018-12-10 | 14585 | C |
| 崔志强 | 2018-12-10 | 7292 | A |
| 李莹 | 2018-12-10 | 19448 | D |
| 王畅 | 2018-12-10 | 15800 | D |
| 马涛 | 2018-12-10 | 14585 | C |
| 李柳 | 2018-12-10 | 7292 | A |
| 张亮 | 2018-12-10 | 23093 | E |
| 王小红 | 2018-12-10 | 21878 | E |
| 苏波 | 2018-12-10 | 23093 | E |
| 游静 | 2018-12-10 | 18231 | D |
| 宋艳 | 2018-12-10 | 18231 | D |
| 巫杨 | 2018-12-10 | 8507 | B |
| 萧秀华 | 2018-12-10 | 18231 | D |
| 赵凤兰 | 2018-12-10 | 6076 | A |
| 田兰英 | 2018-12-10 | 24309 | E |
| 杨桂香 | 2018-12-10 | 10938 | C |
| 黄秀云 | 2018-12-10 | 6076 | A |
| 陈建军 | 2018-12-10 | 13369 | C |
| 贾荣 | 2018-12-10 | 9724 | B |
| 和林 | 2018-12-10 | 25524 | E |
| 宋慧 | 2018-12-10 | 23093 | E |
| 张梅 | 2018-12-10 | 23093 | E |
| 段杨 | 2018-12-10 | 10938 | C |
| 孙婷 | 2018-12-10 | 9724 | B |
| 区军 | 2018-12-10 | 21878 | E |
| 许欣 | 2018-12-10 | 19448 | D |
| 李慧 | 2018-12-10 | 14585 | C |
| 李静 | 2018-12-10 | 17016 | D |
| 谢琴 | 2018-12-10 | 10938 | C |
| 李瑞 | 2018-12-10 | 17016 | D |
| 汤华 | 2018-12-10 | 14585 | C |
| 王玉兰 | 2018-12-10 | 9724 | B |
| 刘倩 | 2018-12-10 | 10938 | C |
| 杨金凤 | 2018-12-10 | 6076 | A |
+-----------+-----------------+--------------+--------------+
120 rows in set (0.00 sec)2 案例2：外连接
2.1 问题
- 练习左连接查询
- 练习右连接查询
- 练习全外连接查询
2.2 步骤
实现此案例需要按照如下步骤进行。
步骤一：练习左连接查询
环境准备：
向departments表里添加3个部门:小卖部 行政部 公关部
mysql> insert into departments(dept_name) values("小卖部"),("行政部"),("公关部");查询部门信息
mysql> select * from tarena.departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
| 9 | 小卖部 |
| 10 | 行政部 |
| 11 | 公关部 |
+---------+-----------+
11 rows in set (0.00 sec)
mysql>左连接查询例子：输出没有员工的部门名
//左连接查询
mysql> select d.dept_name,e.name from departments as d left join employees as e on d.dept_id=e.dept_id;
...
...
| 法务部 | 王荣 |
| 法务部 | 刘倩 |
| 法务部 | 杨金凤 |
| 小卖部 | NULL |
| 行政部 | NULL |
| 公关部 | NULL |
+-----------+-----------+
136 rows in set (0.00 sec)连接后 输出与筛选条件匹配的行
select d.dept_name,e.name from departments as d left join employees as e on d.dept_id=e.dept_id where e.name is null;
+-----------+------+
| dept_name | name |
+-----------+------+
| 小卖部 | NULL |
| 行政部 | NULL |
| 公关部 | NULL |
+-----------+------+
3 rows in set (0.01 sec)
mysql>仅显示departments表中dept_name表头
select d.dept_name from departments as d left join employees as e on d.dept_id=e.dept_id where e.name is null;
+-----------+
| dept_name |
+-----------+
| 小卖部 |
| 行政部 |
| 公关部 |
+-----------+
3 rows in set (0.01 sec)
mysql>步骤二：练习右连接查询
环境准备
向employees表中添加3个员工 只给name表头赋值
insert into employees(name) values ("bob"),("tom"),("lily");右连接查询例子：显示没有部门的员工名
//右连接查询
select e.name ,d.dept_name from departments as d right join employees as e on d.dept_id=e.dept_id;
//加筛选条件
select e.name ,d.dept_name from departments as d right join employees as e on d.dept_id=e.dept_id where d.dept_name is null ;
+------+-----------+
| name | dept_name |
+------+-----------+
| bob | NULL |
| tom | NULL |
| lily | NULL |
+------+-----------+
3 rows in set (0.00 sec)
mysql>
//仅显示员工名
select e.name from departments as d right join employees as e on d.dept_id=e.dept_id where d.dept_name is null ;
+------+
| name |
+------+
| bob |
| tom |
| lily |
+------+步骤三：练习全外连接查询
输出2018年基本工资的最大值和最小值
mysql> ( select basic from salary where year(date)=2018 order by basic desc limit 1) union (select basic from salary where year(date)=2018 order by basic asc limit 1 );
+-------+
| basic |
+-------+
| 25524 |
| 5787 |
+-------+
2 rows in set (0.01 sec)输出2018年1月10号 基本工资的最大值和最小值
mysql> (select date , max(basic) as 工资 from salary where date=20180110)union(select date,min(basic) from salary where date=20180110);
+------------+--------+
| date | 工资 |
+------------+--------+
| 2018-01-10 | 24309 |
| 2018-01-10 | 5787 |
+------------+--------+
2 rows in set (0.00 sec)union 去掉查询结果中重复的行
mysql> (select employee_id , name , birth_date from employees where employee_id <= 5) union (select employee_id , name , birth_date from employees where employee_id <= 6);
+-------------+-----------+------------+
| employee_id | name | birth_date |
+-------------+-----------+------------+
| 1 | 梁伟 | 1971-08-19 |
| 2 | 郭岩 | 1974-05-13 |
| 3 | 李玉英 | 1974-01-25 |
| 4 | 张健 | 1972-06-07 |
| 5 | 郑静 | 1997-02-14 |
| 6 | 牛建军 | 1985-03-19 |
+-------------+-----------+------------+第二个查询只输出了与条件匹配的最后1行
mysql> (select employee_id , name , birth_date from employees where employee_id <= 5) union (select employee_id , name , birth_date from employees where employee_id <= 6);
+-------------+-----------+------------+
| employee_id | name | birth_date |
+-------------+-----------+------------+
| 1 | 梁伟 | 1971-08-19 |
| 2 | 郭岩 | 1974-05-13 |
| 3 | 李玉英 | 1974-01-25 |
| 4 | 张健 | 1972-06-07 |
| 5 | 郑静 | 1997-02-14 |
| 6 | 牛建军 | 1985-03-19 |
+-------------+-----------+------------+
6 rows in set (0.00 sec)union all 不去重显示查询结果
mysql> (select employee_id , name , birth_date from employees where employee_id <= 5) union all (select employee_id , name , birth_date from employees where employee_id <= 6);
+-------------+-----------+------------+
| employee_id | name | birth_date |
+-------------+-----------+------------+
| 1 | 梁伟 | 1971-08-19 |
| 2 | 郭岩 | 1974-05-13 |
| 3 | 李玉英 | 1974-01-25 |
| 4 | 张健 | 1972-06-07 |
| 5 | 郑静 | 1997-02-14 |
| 1 | 梁伟 | 1971-08-19 |
| 2 | 郭岩 | 1974-05-13 |
| 3 | 李玉英 | 1974-01-25 |
| 4 | 张健 | 1972-06-07 |
| 5 | 郑静 | 1997-02-14 |
| 6 | 牛建军 | 1985-03-19 |
+-------------+-----------+------------+
11 rows in set (0.00 sec)3 案例3：嵌套查询
3.1 问题
- 练习where之后嵌套查询
- 练习having之后嵌套查询
- 练习from之后嵌套查询
- 练习select之后嵌套查询
3.2 方案
嵌套查询：是指在一个完整的查询语句之中，包含若干个不同功能的小查询；从而一起完成复杂查询的一种编写形式。包含的查询放在（）里 ， 包含的查询出现的位置：
- SELECT之后
- FROM之后
- WHERE
- HAVING之后
3.3 步骤
实现此案例需要按照如下步骤进行。
步骤一：练习where之后嵌套查询
1）查询运维部所有员工信息
#先把 运维部的id 找到
mysql> select dept_id from departments where dept_name="运维部";
+---------+
| dept_id |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
#员工表里没有部门名称 但有部门编号 （和部门表的编号是一致的）
mysql> select * from employees where dept_id = (select dept_id from departments where dept_name="运维部");
+-------------+-----------+------------+------------+--------------------+--------------+---------+
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
+-------------+-----------+------------+------------+--------------------+--------------+---------+
| 14 | 廖娜 | 2012-05-20 | 1982-06-22 | liaona@tarena.com | 15827928192 | 3 |
| 15 | 窦红梅 | 2018-03-16 | 1971-09-09 | douhongmei@tedu.cn | 15004739483 | 3 |
| 16 | 聂想 | 2018-09-09 | 1999-06-05 | niexiang@tedu.cn | 15501892446 | 3 |
| 17 | 陈阳 | 2004-09-16 | 1991-04-10 | chenyang@tedu.cn | 15565662056 | 3 |
| 18 | 戴璐 | 2001-11-30 | 1975-05-16 | dailu@tedu.cn | 13465236095 | 3 |
| 19 | 陈斌 | 2019-07-04 | 2000-01-22 | chenbin@tarena.com | 13621656037 | 3 |
+-------------+-----------+------------+------------+--------------------+--------------+---------+
6 rows in set (0.00 sec)2）查询人事部2018年12月所有员工工资
//查看人事部的部门id
mysql> select dept_id from departments where dept_name='人事部';
+---------+
| dept_id |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
//查找employees表里 人事部的员工id
select employee_id from employees where dept_id=(select dept_id from departments where dept_name='人事部');
+-------------+
| employee_id |
+-------------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+-------------+
8 rows in set (0.00 sec)
//查询人事部2018年12月所有员工工资
select * from salary where year(date)=2018 and month(date)=12
and employee_id in (select employee_id from employees
where dept_id=(select dept_id from departments where dept_name='人事部') );
+------+------------+-------------+-------+-------+
| id | date | employee_id | basic | bonus |
+------+------------+-------------+-------+-------+
| 6252 | 2018-12-10 | 1 | 17016 | 7000 |
| 6253 | 2018-12-10 | 2 | 20662 | 9000 |
| 6254 | 2018-12-10 | 3 | 9724 | 8000 |
| 6255 | 2018-12-10 | 4 | 17016 | 2000 |
| 6256 | 2018-12-10 | 5 | 17016 | 3000 |
| 6257 | 2018-12-10 | 6 | 17016 | 1000 |
| 6258 | 2018-12-10 | 7 | 23093 | 4000 |
| 6259 | 2018-12-10 | 8 | 23093 | 2000 |
+------+------------+-------------+-------+-------+
8 rows in set (0.00 sec)3）查询人事部和财务部员工信息
//查看人事部和财务部的 部门id
select dept_id from departments where dept_name in ('人事部', '财务部');
+---------+
| dept_id |
+---------+
| 1 |
| 2 |
+---------+
2 rows in set (0.00 sec)
//查询人事部和财务部员工信息
select dept_id , name from employees
where dept_id in (
select dept_id from departments where dept_name in ('人事部', '财务部')
);
+---------+-----------+
| dept_id | name |
+---------+-----------+
| 1 | 梁伟 |
| 1 | 郭岩 |
| 1 | 李玉英 |
| 1 | 张健 |
| 1 | 郑静 |
| 1 | 牛建军 |
| 1 | 刘斌 |
| 1 | 汪云 |
| 2 | 张建平 |
| 2 | 郭娟 |
| 2 | 郭兰英 |
| 2 | 王英 |
| 2 | 王楠 |
+---------+-----------+
13 rows in set (0.00 sec)4）查询2018年12月所有比100号员工基本工资高的工资信息
//把100号员工的基本工资查出来
select basic from salary where year(date)=2018 and
month(date)=12 and employee_id=100;
+-------+
| basic |
+-------+
| 14585 |
+-------+
1 row in set (0.00 sec)
//查看比100号员工工资高的工资信息
select * from salary
where year(date)=2018 and month(date)=12 and
basic>(select basic from salary where year(date)=2018 and
month(date)=12 and employee_id=100);
+------+------------+-------------+-------+-------+
| id | date | employee_id | basic | bonus |
+------+------------+-------------+-------+-------+
| 6252 | 2018-12-10 | 1 | 17016 | 7000 |
| 6253 | 2018-12-10 | 2 | 20662 | 9000 |
| 6255 | 2018-12-10 | 4 | 17016 | 2000 |
| 6256 | 2018-12-10 | 5 | 17016 | 3000 |
| 6257 | 2018-12-10 | 6 | 17016 | 1000 |
| 6258 | 2018-12-10 | 7 | 23093 | 4000 |
| 6259 | 2018-12-10 | 8 | 23093 | 2000 |
| 6261 | 2018-12-10 | 10 | 21878 | 8000 |
| 6262 | 2018-12-10 | 11 | 17016 | 8000 |
| 6263 | 2018-12-10 | 12 | 15800 | 4000 |
| 6264 | 2018-12-10 | 13 | 18231 | 3000 |
| 6267 | 2018-12-10 | 16 | 21878 | 8000 |
| 6268 | 2018-12-10 | 17 | 19448 | 7000 |
| 6271 | 2018-12-10 | 20 | 19448 | 3000 |
| 6272 | 2018-12-10 | 21 | 18231 | 11000 |
| 6276 | 2018-12-10 | 25 | 23093 | 3000 |
| 6278 | 2018-12-10 | 27 | 24309 | 5000 |
| 6279 | 2018-12-10 | 28 | 17016 | 9000 |
| 6280 | 2018-12-10 | 29 | 23093 | 1000 |
| 6282 | 2018-12-10 | 31 | 25524 | 9000 |
| 6283 | 2018-12-10 | 32 | 18231 | 11000 |
| 6284 | 2018-12-10 | 33 | 23093 | 6000 |
| 6285 | 2018-12-10 | 34 | 23093 | 1000 |
| 6288 | 2018-12-10 | 37 | 24309 | 4000 |
| 6289 | 2018-12-10 | 38 | 23093 | 3000 |
| 6291 | 2018-12-10 | 40 | 20662 | 2000 |
| 6297 | 2018-12-10 | 46 | 15800 | 7000 |
| 6299 | 2018-12-10 | 48 | 25524 | 1000 |
| 6300 | 2018-12-10 | 49 | 15800 | 9000 |
| 6303 | 2018-12-10 | 52 | 19448 | 9000 |
| 6304 | 2018-12-10 | 53 | 25524 | 8000 |
| 6305 | 2018-12-10 | 54 | 21878 | 9000 |
| 6307 | 2018-12-10 | 56 | 23093 | 3000 |
| 6308 | 2018-12-10 | 57 | 23093 | 3000 |
| 6312 | 2018-12-10 | 61 | 24309 | 3000 |
| 6313 | 2018-12-10 | 62 | 15800 | 4000 |
| 6314 | 2018-12-10 | 63 | 15800 | 8000 |
| 6317 | 2018-12-10 | 66 | 19448 | 7000 |
| 6319 | 2018-12-10 | 68 | 25524 | 9000 |
| 6324 | 2018-12-10 | 73 | 17016 | 10000 |
| 6327 | 2018-12-10 | 76 | 20662 | 11000 |
| 6330 | 2018-12-10 | 79 | 15800 | 4000 |
| 6332 | 2018-12-10 | 81 | 17016 | 3000 |
| 6336 | 2018-12-10 | 85 | 15800 | 1000 |
| 6337 | 2018-12-10 | 86 | 24309 | 4000 |
| 6339 | 2018-12-10 | 88 | 20662 | 2000 |
| 6342 | 2018-12-10 | 91 | 20662 | 11000 |
| 6343 | 2018-12-10 | 92 | 19448 | 2000 |
| 6346 | 2018-12-10 | 95 | 19448 | 8000 |
| 6349 | 2018-12-10 | 98 | 19448 | 9000 |
| 6350 | 2018-12-10 | 99 | 15800 | 5000 |
| 6353 | 2018-12-10 | 102 | 23093 | 3000 |
| 6356 | 2018-12-10 | 105 | 21878 | 8000 |
| 6357 | 2018-12-10 | 106 | 23093 | 5000 |
| 6358 | 2018-12-10 | 107 | 18231 | 7000 |
| 6359 | 2018-12-10 | 108 | 18231 | 2000 |
| 6361 | 2018-12-10 | 110 | 18231 | 2000 |
| 6363 | 2018-12-10 | 112 | 24309 | 9000 |
| 6368 | 2018-12-10 | 117 | 25524 | 11000 |
| 6369 | 2018-12-10 | 118 | 23093 | 3000 |
| 6370 | 2018-12-10 | 119 | 23093 | 10000 |
| 6373 | 2018-12-10 | 122 | 21878 | 2000 |
| 6374 | 2018-12-10 | 123 | 19448 | 10000 |
| 6376 | 2018-12-10 | 125 | 17016 | 5000 |
| 6378 | 2018-12-10 | 127 | 17016 | 6000 |
+------+------------+-------------+-------+-------+
65 rows in set (0.01 sec)步骤二：练习having之后嵌套查询
查询部门员工总人数比开发部总人数少 的 部门名称和人数
//统计开发部员工总人数
select count(name) from employees where dept_id = (select
dept_id from departments where dept_name="开发部");
+-------------+
| count(name) |
+-------------+
| 55 |
+-------------+
1 row in set (0.00 sec)
//统计每个部门总人数
select dept_id , count(name) from employees group by dept_id;
+---------+-------------+
| dept_id | count(name) |
+---------+-------------+
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 4 | 55 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
+---------+-------------+
8 rows in set (0.00 sec)
//输出总人数比开发部总人数少的部门名及总人数
select dept_id , count(name) as total from employees group by dept_id
having total < (
select count(name) from employees where dept_id=(
select dept_id from departments where dept_name='开发部')
);
+---------+-------+
| dept_id | total |
+---------+-------+
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
+---------+-------+
7 rows in set (0.00 sec)步骤三：练习from之后嵌套查询
查询3号部门 、部门名称 及其部门内 员工的编号、名字 和 email
select dept_id, dept_name, employee_id, name, email from (
select d.dept_name, e.* from departments as d inner join employees as e
on d.dept_id=e.dept_id ) as tmp_table where dept_id=3;
+---------+-----------+-------------+-----------+--------------------+
| dept_id | dept_name | employee_id | name | email |
+---------+-----------+-------------+-----------+--------------------+
| 3 | 运维部 | 14 | 廖娜 | liaona@tarena.com |
| 3 | 运维部 | 15 | 窦红梅 | douhongmei@tedu.cn |
| 3 | 运维部 | 16 | 聂想 | niexiang@tedu.cn |
| 3 | 运维部 | 17 | 陈阳 | chenyang@tedu.cn |
| 3 | 运维部 | 18 | 戴璐 | dailu@tedu.cn |
| 3 | 运维部 | 19 | 陈斌 | chenbin@tarena.com |
+---------+-----------+-------------+-----------+--------------------+
6 rows in set (0.00 sec)步骤四：练习select之后嵌套查询
查询每个部门的人数： dept_id dept_name 部门人数
//显示部门表所有数据
select d.* from departments as d;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
+---------+-----------+
8 rows in set (0.00 sec)
统计每个部门总人数
select d.* , ( select count(name) from employees as e where d.dept_id=e.dept_id) as 部门人数 from departments as d;
+---------+-----------+--------------+
| dept_id | dept_name | 部门人数 |
+---------+-----------+--------------+
| 1 | 人事部 | 8 |
| 2 | 财务部 | 5 |
| 3 | 运维部 | 6 |
| 4 | 开发部 | 55 |
| 5 | 测试部 | 12 |
| 6 | 市场部 | 9 |
| 7 | 销售部 | 35 |
| 8 | 法务部 | 3 |
+---------+-----------+--------------+
8 rows in set (0.00 sec)
