MYSQL(多表联查)

MYSQL （多表联查）

数据准备：

-- 准备数据
create table dept(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
)comment '部门表';

create table emp(
    id  int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '姓名',
    age  int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
)comment '员工表'

-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办'), (6, '人事部');
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
            (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),

            (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
            (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
            (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
            (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
            (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),

            (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
            (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
            (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),

            (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
            (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
            (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
            (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),

            (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
            (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
            (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
            (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

子查询#

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

select * from t1 where column1 = (select column1 from t2)

子查询外部的语句可以是 insert/update/delete/select 中的任意一个

常见操作符：=、<>(不等于)、>、>=、<、<=

• 根据子查询结果不同分为：

  1. 标量子查询（子查询结果为单个值）
  2. 列子查询（子查询结果为一列）
  3. 行子查询（子查询结果为一行）
  4. ​ 表子查询（子查询结果为多行多列）

• 根据子查询位置，分为：where之后、from之后、select之后

• 标量子查询

--1.查询销售部的所有员工信息

--a.查询销售部的部门id
select id from dept where name = "销售部"
--b.根据部门id查询员工信息
select * from emp where dept_id = 4;
--将上面的sql语句合为一句
select * from emp where dept_id = (select id from dept where name = "销售部")

--2.查询在东方白入职之后的员工信息

--a.查询东方白的入职时间
select entry_date from emp where name = "东方白"
--b.查询东方白入职之后员工的信息
select * from emp where entry_date > 2024-7-5
--上面两句合为一句
select * from emp where entry_date > (select entry_date from emp where name = "东方白")

• 列子查询

  子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

  

  常用的操作符：IN、NOTIN、ANY、SOME、ALL

  ​

  操作符	描述
  IN	在指定的集合范围内，多选一
  NOTIN	不在指定的集合范围内
  ANY	子查询返回列表中，有任意一个满足即可
  SOME	与ANY等同，使用SOME的地方都可以使用ANY
  ALL	子查询返回列表的所有值都必须满足

--1.查询销售部和市场部的所有员工信息

--a.查询销售部和市场部的部门id
select id from dept where name = "销售部" or name = "市场部";
--b.根据销售部和市场部的部门id查询员工信息
select * from emp where dept_id in(2,4);
--合并上面两个语句
select * from emp where dept_id in(select id from dept where name = "销售部" or name = "市场部")

--2.查询比财务部所有人工资都高的员工信息
--a.查询财务部人员的工资信息
select id from dept where name = "财务部"
select salary from emp where dept_id = 3
--合并
select salary from emp where dept_id = (select id from dept where name = "财务部")
--b.查询比财务部所有人工资高的人员的信息
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = "财务部"))

--3.查询比财务部中任意一人工资高的员工信息
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = "财务部"))

• 行子查询

子查询返回的结果是一行（可以是多列），这种子查询就称为行子查询。

常见的操作符：=、<>(不等于)、IN、NOT IN

--1.查询与张无忌的薪资及直属领导相同的员工信息
--a.查询张无忌的薪资以及直属领导
select salary,managerid from emp where name = "张无忌"
--b.查询与张无忌的薪资以及直属领导相同的员工信息
select * from emp where salary = "12500" and managerid = 1
或：select * from emp where (salary,managerid) =("125000",1) #这是要后面的按顺序满足前面的
--合并两句
select * from emp where (salary,managerid) =(select salary,managerid from emp where name = "张无忌")

• 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

--1.查询与鹿杖客，宋远桥的职位和薪资相同的员工信息
--a.查询鹿杖客、宋远桥的职位和薪资信息
select job,salary from tb_emp where `name` IN ('鹿杖客','宋远桥')
或: select job,salary from tb_emp where `name` = '张无忌' or `name` = '金庸'
--查询与他俩职位和薪资相同的员工的信息
select * from emp where (job,salary) in (select job,salary from tb_emp where `name` = '张无忌' or `name` = '金庸')

--2.查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
--a.查询入职日期“2006-1-01”之后的员工信息
select * from emp where entry_date > "2006-01-01" 注:这个结果需要当做第二个中的一张表使用
--b.查询这部分员工的部门信息
select e.*,d.* from (select * from emp where entry_date > "2006-01-01") e left join dept d where e.dept_id = d.id;
或：
select e.*,d.* from emp e left join dept d where e.dept_id=d.id and e.entry_date > "2006-01-01";

注意：
	1.这两条 SQL 查询语句的主要区别在于它们的语义和执行顺序，虽然它们可以产生相同的结果，但它们的优劣取决于查询的需求和数据库优化器的执行计划。
哪条更好？
	1.性能考虑： 大多数情况下，数据库优化器会尝试重新排列和优化查询，以找到最有效的执行计划。因此，实际执行的效率可能取决于数据库系统的优化策略和具体数据分布情况。
	2.语义清晰： 第一条查询在逻辑上更清晰，先进行筛选，再进行连接，使得查询意图更加明确。第二条查询虽然也能产生相同的结果，但语义上不如第一条直观。
	3.维护性： 如果你需要在多个查询中使用相同的筛选条件，第一条查询可以更容易地重用子查询部分，而不必重复条件。

的执行计划。因此，实际执行的效率可能取决于数据库系统的优化策略和具体数据分布情况。
2.语义清晰： 第一条查询在逻辑上更清晰，先进行筛选，再进行连接，使得查询意图更加明确。第二条查询虽然也能产生相同的结果，但语义上不如第一条直观。
3.维护性： 如果你需要在多个查询中使用相同的筛选条件，第一条查询可以更容易地重用子查询部分，而不必重复条件。