MySQL深入浅出之多表查询实操

-- 创建员工表
create table employee(
num int(10) COMMENT'员工编号' PRIMARY KEY, -- 设置唯一主键
name varchar(50) COMMENT'员工姓名',
wage int, 
bonus int(11) COMMENT '奖金', 
dptnum int COMMENT '所属部门'
) ENGINE=INNODB,
default charset=utf8;  -- 设置默认InnoDB存储引擎, 设置默认字符集utf8

-- 创建部门表
create table department(
dptname varchar(50) comment '部门名称',
dptnum int(10) comment '部门编号',
dptboosID int(10) comment '老大编号'
) ENGINE=INNODB,
DEFAULT charset=utf8;

-- 插入员工数据
insert into employee(num, name, wage, bonus, dptnum) values
(001,"zjq", 20000, 2000, 0002),
(002,"rqx", 12000, 1200, 0001),
(003,"wbd", 15640, 1500, 0001),
(004,"zly", 45892, 2000, 0003),
(005,"xgn", 25896, 1600, 0002),
(006,"qxf", 10154, 2500, 0002),
(007,"wyl", 85204, 1300, 0004),
(008,"dgb", 96458, 1800, 0001),
(009,"rew", 32540, 2500, 0002),
(010,"bty", 25841, 2700, 0004);

-- 插入部门数据
insert into department(dptname, dptnum, dptboosID) VALUES
("y_head", 0001, 001),
("t_head", 0002, 025),
("z_head", 0003, 126),
("w_head", 0004, 258);

# 查询员工的编号,姓名, 薪水, 部门编号
select num, name, wage, dptnum from employee; 

# 查询员工的编号,姓名, 薪水, 部门编号, 部门名称
select num, name, wage, dptnum from employee; 
select DISTINCT dptnum from employee; -- 去重查询, 查询所有部门 
select dptname from department where dptnum = 1 or dptnum = 2; # 这个20是上个命令查到的
# 缺点: 通过多条sql语句完成操作, 效率低下

# 解决: 多条单表查询语句转换为一条多表查询语句
# 下面四种是互斥的, 不能同时用
# 1. 交叉连接 这其实是笛卡尔积, 没有实际意义 cross
select * from employee; # 14个人
select * from department; # 4个部门

select * from employee cross join department; # 14*4的长度; cross可以省略, 笛卡尔积

# 2. 自然连接 自动匹配所有同名列, 同名列只保留一份, 简单
select * from employee natural join department; 
select num, name, wage, dptnum, dptname from employee natural join department; 
# 缺点: 没有指明各列属于哪个表, 效率低下

# 解决: 指定表名字
select employee.num, employee.name, employee.wage, employee.dptnum, department.dptname from employee natural join department; 
# 缺点: 表名可能比较长了

# 解决: 别名
select e.num, e.name, e.wage, e.dptnum, d.dptname
from employee e 
natural join department d; 

# 3. using子句
# natural自然连接的缺点: 会自动按照所有同名列进行匹配, 如果希望只按照某个同名列匹配怎么办?
# 解决 using 
select e.num, e.name, e.wage, e.dptnum, d.dptname
from employee e 
join department d
using(dptnum); # 表示同名列是department, 隐藏的语句是e.deptno = d.deptno

# 4. on子句
# using的缺点: 必须有同名列, 比如学生的班级可能是number, 另一张表可能是classNumber
# 如果主键, 外键不同命, 怎么办
# 解决: on语句
select e.num, e.name, e.wage, e.dptnum, d.dptname
from employee e 
join department d
on(e.dptnum = d.dptnum); # 表示同名列是department, 隐藏的语句是e.deptno = d.deptno

select e.num, e.name, e.wage, e.dptnum, d.dptname
from employee e
join department d
on(e.dptnum=d.dptnum)
WHERE e.wage>20000
order by e.wage desc;  # 降序

# 此时手动把num=9的值置空
update employee set dptnum=NULL where num=9;
update employee set dptnum=2 where num=9; # 记得后面恢复的时候使用这个命令

# 比如没有部门的员工, 想要显示也是可以的 
select *
from employee e
join department d
on(e.dptnum = d.dptnum);
# 此时显示的时候已经没有num=9的员工信息了, 这是因为 "e.dptnum = d.dptnum" 没有成立的了

# 左外连接 员工表1个不匹配到部门在最后显示
select e.num, e.name, e.wage, e.dptnum, d.dptname
from employee e
left join department d
on(e.dptnum = d.dptnum);


insert into department(dptname, dptnum, dptboosID) VALUES
("head_5", 0005, 001),
("head_6", 0006, 002),
("head_7", 0007, 002),
("head_8", 0008, 005);

# 右外连接 right  不匹配的4个部门也拿出来了
select e.num, e.name, e.wage, e.dptnum, d.dptname
from employee e
right join department d
on(e.dptnum = d.dptnum);


# 全外连接 注意这是错误的, mysql不支持, 
select e.num, e.name, e.wage, e.dptnum, d.dptname
from employee e
full join department d
on(e.dptnum = d.dptnum);

# 变通解决方案
# 左右连接的并集 union

select e.num, e.name, e.wage, e.dptnum, d.dptname
from employee e
left join department d
on(e.dptnum = d.dptnum)
union # 注意这个并集会有重复的, 因此需要去重
select e.num, e.name, e.wage, e.dptnum, d.dptname
from employee e
right join department d
on(e.dptnum = d.dptnum);

create table wageLevel(
grade int,
minLevel int,
maxLevel int
) ENGINE=INNODB,
default charset=utf8;  -- 设置默认InnoDB存储引擎, 设置默认字符集utf8

insert into wageLevel (grade, minLevel, maxLevel) values
(1, 90001, 100000),
(2, 60001, 90000),
(3, 25001, 60000),
(1, 10000, 25000);

#  查询 员工的编号,姓名,薪水, 部门名称, 部门的编号, 薪水的等级
# 2. 查部门编号
select e.num, e.name, e.wage, e.dptnum, d.dptname
from employee e
left join department d # 左连接的原因是查询的所有的人
on (e.dptnum=d.dptnum);

# 3. 薪水等级
select * 
from wageLevel; 

# 4. 带上薪水
select e.num, e.name, e.wage, e.dptnum, d.dptname, w.grade
from employee e
left join department d
on(e.dptnum = d.dptnum)
join wageLevel w
on (e.wage between w.minLevel and w.maxLevel);

select e.num, e.name, e.wage, e.dptnum, d.dptname, w.grade
from employee e
left join department d
on(e.dptnum = d.dptnum)
join wageLevel w
on (e.wage between w.minLevel and w.maxLevel)
where e.num<6 # 这里注意把显示格式比如排序或者表的范围都放到最后
order by e.wage desc;

# 自连接
create table userT(
eID int PRIMARY key,
eName VARCHAR(50),
eBoosID int comment "上级ID",
wage int
) ENGINE=INNODB,
default charset=utf8;  -- 设置默认InnoDB存储引擎, 设置默认字符集utf8

insert into userT (eID, eName, eBoosID, wage) values
(1, "zjq", 2, 2000),
(2, "rqx", 4, 1500),
(3, "wbd", 7, 1200),
(4, "nmd", 9, 800),
(5, "ymd", 1, 10);

# 查询员工编号, 姓名, 上级编号, 上级姓名
select u.eID, u.eName, boos.eName
from userT u
left join userT boos
on (u.eBoosID = boos.eID);