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

末蓝、 2022-09-07 14:39 136阅读 0赞

# 1. 两表: 查询员工信息和所属部门的名称 #

## 1.1 创建资源以便后面查询 ##

-- 创建员工表
    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);

## 1.2 查询员工的编号,姓名, 薪水, 部门编号, 部门名称 ##

# 查询员工的编号,姓名, 薪水, 部门编号
    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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70]

## 1.3 查询员工信息和对应部门信息, 要求薪水大于2w的,降序 ##

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;  # 降序

# 2. 两表: 外连接 可以显示全部或者不匹配的数据 <left, right> #

*  `left` 是左表包含全部, 右表进行匹配
 *  `right` 是右表包含全部, 左表进行匹配
 *  `union` 左右表都包含, 进行匹配

# 此时手动把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);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 1]

# 3. 多表 查询员工对应部门和薪水的等级 #

## 3.1 创建薪水等级表 ##

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);

## 3.2 查询员工对应部门和薪水 ##

*  先查询员工
 *  在左连接部门(因为要求查询所有员工, 所以左连接)
 *  在连接薪水

# 查询 员工的编号,姓名,薪水, 部门名称, 部门的编号, 薪水的等级
    # 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);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 2]

## 3.3 前6个员工对应的部门信息和薪水信息, 按照薪水排序 ##

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;

![在这里插入图片描述][353fc5c18afb4c83b5f5e1cac191b288.png]

# 4. 单表 自连接 #

*  单表自连接查询是因为一张表中含有相关信息
 *  可以将单张表想象成多张表 通过重命名即可 `from table t1 join table t2 join table t3` 这样`t1, t2, t3`表示的就是三张表了
 *  在多表连接即可

# 自连接
    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);

![在这里插入图片描述][0c9428c71efb435b80ff4d545e7bdb4e.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70]: /images/20220829/756c3bed4b314032a799c99d2f8096a9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 1]: /images/20220829/ac9aa84d32144840be3883bb909dbf0c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNDYwODE5_size_16_color_FFFFFF_t_70 2]: /images/20220829/6a4377cc8c3c4eb694c450d47a16245f.png
[353fc5c18afb4c83b5f5e1cac191b288.png]: /images/20220829/d1069022582a427581648400d6ffe9ad.png
[0c9428c71efb435b80ff4d545e7bdb4e.png]: /images/20220829/e8e9b01be77e425e973fa3800748625a.png