# 一 \ 内连接

# select * from department

# inner join employee

# on department.id = employee.dep_id;

# 只会把左表和右表对应的行显示出来

# 如果左表中的department.id在employee.dep_id中没有出现,那么会抛弃这个行

# 如果右表中的employee.dep_id在department.id中没有出现,那么也会抛弃这个行

# 内连接的另一种方式 用where

# select * from department,employee

# where department.id = employee.dep_id;

# 从不同的表中取字段

# select employee.name,employee.sex,department.name

# from department,employee

# where department.id = employee.dep_id;

# 给表起别名,让sql更简单

# select emp.name,emp.sex,dep.name

# from department as dep,employee as emp

# where dep.id = emp.dep_id;

# 给字段起别名,让显示的效果更明确

# select emp.name,emp.sex,dep.name as dep_name

# from department as dep,employee as emp

# where dep.id = emp.dep_id;

# 外连接

# 二 \左外连接 ： 显示左表中的所有项，和右表中所有满足拼接条件的项

# select * from department

# left join employee

# on department.id = employee.dep_id;

# 三 \右外连接：显示右表中的所有项，和左表中所有满足拼接条件的项

# select * from department

# right join employee

# on department.id = employee.dep_id;

# 四 \全外连接：左表和右表都完全显示出来了

# select * from department left join employee on department.id = employee.dep_id

# union all

# select * from department right join employee on department.id = employee.dep_id

# 练习

# 示例一 : 找到年龄> 25的员工的姓名和部门

# select * from employee

# inner join department

# on employee.dep_id = department.id

# where age>25;

# 示例一变式 : 找到alex员工的年龄和部门

# select age,department.name from employee

# inner join department

# on employee.dep_id = department.id

# where employee.name = 'alex';

# 给表重命名

# select age,dep.name from employee as emp

# inner join department as dep

# on emp.dep_id = dep.id

# where emp.name = 'alex';

# 给字段重命名引起的错误

# select employee.name as emp_name,age,department.name from employee

# inner join department

# on employee.dep_id = department.id

# where emp_name = 'alex';

# 报错,因为在select处重名名不能在where/group by/having中使用,由于mysql的词法分析顺序导致该问题

# 示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显

# select * from employee

# inner join department

# on employee.dep_id = department.id

# order by age;

# 示例一 : 查询平均年龄在25岁以上的部门名

# 涉及到年龄 员工表

# 部门名字   部门表

# 你的结果在哪个表,那个表一定不是子查询的表

# 1. 连表查询的结果

# 先内连接得到一张大表

# select * from department

# inner join employee

# on department.id = employee.dep_id

# 再根据部门分组

# select department.name from department

# inner join employee

# on department.id = employee.dep_id

# group by department.id

# having avg(age) > 25;

# 2. 子查询的结果

# 先完成一部分需求,求每一个部门的人的平均年龄

# select dep_id,avg(age) from employee group by dep_id

# 再筛选出平均年龄大于25的部门

# select dep_id,avg(age) from employee group by dep_id having avg(age)>25

# 由于我们只需要部门名称,而和部门名称相关的项就只有部门id,所以我们只留下dep_id字段

# select dep_id from employee group by dep_id having avg(age)>25

# 查询部门表,找到id在上面这个查询结果内的内容

# select name from department where id in (

# select dep_id from employee group by dep_id having avg(age)>25

# )

pass

# 示例2 : 查看"技术"部员工姓名

# 结果是 : 姓名 -  员工表

# 怎么知道技术部是谁? 怎么和员工表关联?

# 如果我能知道技术部的id是多少,就可以查询了

# 1.查询技术部的id

# select id from department where name = '技术'

# 2.取id=200的所有人

# select * from employee where dep_id = (

# select id from department where name = '技术');

# 3.只取名字

# select name from employee where dep_id = (

# select id from department where name = '技术');

pass

# 示例3 :查看不足1人的部门名

# 结果是 部门名 - 部门表

# 先操作员工表

# 1.找到员工表中所有人的部门id

# select dep_id from employee group by dep_id;

# select distinct dep_id from employee;

# 2.操作部门表查看id not in 上面范围中的项目

# select name from department where id not in (

# select dep_id from employee group by dep_id);

# select name from department where id not in (

# select distinct dep_id from employee);