MySQL多表查询

最新推荐文章于 2024-09-20 21:26:51 发布
最新推荐文章于 2024-09-20 21:26:51 发布
阅读量174 收藏 1
点赞数
文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_58535137/article/details/128272564
版权 
-- 创建dept表，并插入数据
create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';

INSERT INTO dept (id, name)
VALUES (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办'),
       (6, '人事部');

-- 创建emp表，并插入数据
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 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);

select *
from emp,
     dept
where emp.dept_id = dept.id;

# 内连接（两表交集）
# 查询每一个员工的姓名，及关联的部门的名称
# 1.隐式内连接实现
select e.name,
       d.name
from emp e,
     dept d
where e.dept_id = d.id;

# 2.显式内连接实现
select e.name,
       d.name
from emp e
         inner join dept d on e.dept_id = d.id; # inner可省略

select e.name,
       d.name
from emp e
         join dept d on e.dept_id = d.id;

# 外连接
# 查询emp表的所有数据，和对应的部门信息（左外连接）
select e.*,
       d.name
from emp e
         left outer join dept d on e.dept_id = d.id; # outer可省略

select e.*,
       d.name
from emp e
         left join dept d on e.dept_id = d.id;

# 查询dept表的所有数据，和对应的员工信息（右外连接）
select d.*,
       e.*
from emp e
         right outer join dept d on e.dept_id = d.id; # outer可省略

select d.*,
       e.*
from emp e
         right join dept d on e.dept_id = d.id;

# 自连接
# 1.查询员工及其所属领导的名字
select e.name       '员工',
       manager.name '领导'
from emp e
         join emp manager on e.managerid = manager.id;

# 2.查询所有员工及其领导的名字，员工没有领导也要查出来
select e.name       '员工',
       manager.name '领导'
from emp e
         left join emp manager on e.managerid = manager.id;

# 联合查询，多张表的列数必须保持一致，字段类型也要保持一致
# union，union all
# 将薪资低于5000的员工和年龄大于50的员工全部查询出来
select *
from emp
where salary < 5000
union all
# union all 直接合并，不去重
select *
from emp
where age > 50;

select *
from emp
where salary < 5000
union
# union 对合并后的数据去重
select *
from emp
where age > 50;

# 子查询
# 1.标量子查询:子查询返回的结果是单个值（数字、字符串、日期等），常用的操作符：= <> > >= < <=
# 1.查询“销售部”的所有员工信息
# a.查询“销售部”部门id
select id
from dept
where name = '销售部';
# b.根据“销售部”部门id，查询员工信息
select *
from emp
where dept_id = (select id
                 from dept
                 where name = '销售部');

# 使用左外连接也可以做到
select e.*
from emp e
         left join dept d on e.dept_id = d.id
where d.name = '销售部';

# 2.查询在“方东白”入职之后的员工信息
# a.查询“方东白”入职时间
select entrydate
from emp
where name = '方东白';
# b.查询在此时间之后入职的员工信息
select *
from emp
where entrydate > (select entrydate
                   from emp
                   where name = '方东白');

# 使用自连接也可以做到
select e1.*
from emp e1
         left join emp e2 on e1.entrydate > e2.entrydate
where e2.name = '方东白';

# 2.列子查询:子查询返回的结果是一列（可以是多行）
# 常用的操作符：IN 、NOT IN 、 ANY（子查询返回列表中，有任意一个满足即可） 、SOME（同ANY） 、 ALL（子查询返回列表的所有值都必须满足）
# 1.查询“销售部”和“市场部”的所有员工信息
# a.查询“销售部”和“市场部”的部门id
select id
from dept
where name in ('销售部', '市场部');
# b.根据部门id，查询员工信息
select *
from emp
where dept_id in (select id
                  from dept
                  where name in ('销售部', '市场部'));

# 2.查询比财务部所有人工资都高的员工信息
# a.查询财务部所有人员工资

# a.1:查询财务部id
select id
from dept
where name = '财务部';
# a.2:查询财务部所有人员工资
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.查询比研发部其中任意一人工资高的员工信息
# a.1:查询研发部id
select id
from dept
where name = '研发部';
# a.2:查询研发部员工工资
select salary
from emp
where dept_id = (select id
                 from dept
                 where name = '研发部');

# b.查询比研发部其中任意一人工资高的员工信息
select *
from emp
where salary > any (select salary
                    from emp
                    where dept_id = (select id
                                     from dept
                                     where name = '研发部'));
select *
from emp
where salary > some (select salary
                     from emp
                     where dept_id = (select id
                                      from dept
                                      where name = '研发部'));

# 行子查询:子查询返回的结果是一行（可以是多列）
# 常用的操作符：= 、<> 、IN 、NOT IN

# 查询与"张无忌"的薪资及直属领导相同的员工信息
# a.查询"张无忌"的薪资及直属领导id
select salary, managerid
from emp
where name = '张无忌';

# b.查询与"张无忌"的薪资及直属领导相同的员工信息
select *
from emp
where (salary, managerid) = (select salary, managerid
                             from emp
                             where name = '张无忌');



# 表子查询:子查询返回的结果是多行多列
# 常用的操作符：IN
# 1.查询与鹿杖客、宋远桥的职位和薪资相同的员工信息
# a.查询鹿杖客、宋远桥的职位和薪资
select job, salary
from emp
where name in ('鹿杖客', '宋远桥');
# b.查询与鹿杖客、宋远桥的职位和薪资相同的员工信息
select *
from emp
where (job, salary) in (select job, salary
                        from emp
                        where name in ('鹿杖客', '宋远桥'));

# 2.查询入职日期是'2006-01-01'之后的员工信息，及其部门信息
# a.查询入职日期是'2006-01-01'之后的员工信息
select *
from emp
where entrydate > '2006-01-01';

# b.查询入职日期是'2006-01-01'之后的员工信息，及其部门信息
select *
from (select *
      from emp
      where entrydate > '2006-01-01') e
         left join dept d on e.dept_id = d.id;

# 另一种写法
with e as (select *
           from emp
           where entrydate > '2006-01-01')
select *
from e
         left join dept d on e.dept_id = d.id;

# 多表查询练习
create table salgrade
(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';
insert into salgrade
values (1, 0, 3000);
insert into salgrade
values (2, 3001, 5000);
insert into salgrade
values (3, 5001, 8000);
insert into salgrade
values (4, 8001, 10000);
insert into salgrade
values (5, 10001, 15000);
insert into salgrade
values (6, 15001, 20000);
insert into salgrade
values (7, 20001, 25000);
insert into salgrade
values (8, 25001, 30000);

# 1.查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
select e.name,
       e.age,
       e.job,
       d.name
from emp e,
     dept d
where e.dept_id = d.id;

# 2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select e.name,
       e.age,
       e.job,
       d.name
from emp e
         join dept d on d.id = e.dept_id
where e.age < 30;

# 3.查询拥有员工的部门ID、部门名称
select distinct d.*
from dept d
         join emp e on d.id = e.dept_id;

# 4.查询所有年龄大于40岁的员工, 及其归属的部门名称;
# 如果员工没有分配部门, 也需要展示出来(外连接)
select e.*,
       d.name
from emp e
         left join dept d on d.id = e.dept_id
where age > 40;

# 5.查询所有员工的工资等级
select e.*,
       s.grade,
       s.losal,
       s.hisal
from emp e,
     salgrade s
where e.salary >= s.losal
  and e.salary <= s.hisal;

select e.*,
       s.grade,
       s.losal,
       s.hisal
from emp e,
     salgrade s
where e.salary between s.losal and s.hisal;

# 6.查询 "研发部" 所有员工的信息及工资等级
# 先查询研发部所有员工信息
select e.*
from emp e
         join dept d on d.id = e.dept_id
where d.name = '研发部';

# 再查询 "研发部" 所有员工的信息及工资等级
select distinct e.*
from (select e.*
      from emp e
               join dept d on d.id = e.dept_id
      where d.name = '研发部') e,
     salgrade s
where e.salary <= s.losal
  and e.salary <= s.hisal;

# 新玩法
with e as (select e.*
           from emp e
                    join dept d on d.id = e.dept_id
           where d.name = '研发部')
select distinct e.*
from e,
     salgrade s
where e.salary <= s.losal
  and e.salary <= s.hisal;

select e.*,
       s.grade
from emp e
         join dept d on d.id = e.dept_id
         join salgrade s on e.salary >= s.losal and e.salary <= s.hisal
where d.name = '研发部';

select *
from emp e,
     dept d,
     salgrade s
where e.dept_id = d.id
  and (e.salary >= s.losal and e.salary <= s.hisal)
  and d.name = '研发部';

# 7.查询 "研发部" 员工的平均工资
select avg(salary)
from emp e
         join dept d on d.id = e.dept_id
where d.name = '研发部';

select avg(salary)
from emp e,
     dept d
where d.id = e.dept_id
  and d.name = '研发部';

# 8.查询工资比 "灭绝" 高的员工信息
# 查询灭绝的工资
select salary
from emp e
where e.name = '灭绝';

select *
from emp e
where e.salary > (select salary
                  from emp e
                  where e.name = '灭绝');

with miejue as (select salary
                from emp
                where name = '灭绝')
select *
from emp e,
     miejue
where e.salary > miejue.salary;

with miejue as (select salary
                from emp
                where name = '灭绝')
select *
from emp e
         join miejue
              on e.salary > miejue.salary;


# 9.查询比平均薪资高的员工信息
# 查询员工的平均工资
select avg(salary)
from emp;
# 查询比平均薪资高的员工信息
select *
from emp e
where e.salary > (select avg(salary)
                  from emp);

with avg as (select avg(salary) avg
             from emp)
select *
from emp e,
     avg
where e.salary > avg.avg;

# 10.查询低于本部门平均工资的员工信息
# 查询指定部门的平局薪资
select avg(e.salary)
from emp e
where e.dept_id = 1;

# 查询低于本部门平均工资的员工信息
select *
from emp e2
where e2.salary < (select avg(e.salary)
                   from emp e
                   where e.dept_id = e2.dept_id);

# 11.查询所有的部门信息, 并统计部门的员工人数
# 查询所有的部门信息
select *
from dept;

# 统计部门的员工人数
select e.dept_id, count(*)
from emp e
group by e.dept_id;

select d.*,
       ifnull(e.count, 0) '人数'
from dept d
         left join (select dept_id, count(*) count
                    from emp
                    group by dept_id) e
                   on d.id = e.dept_id;

with num as (select dept_id, count(*) count
             from emp
             group by dept_id)
select d.*,
       ifnull(num.count, 0) '人数'
from dept d
         left join num
                   on d.id = num.dept_id;

select count(*) '人数'
from emp e
where e.dept_id = 1;
# select之后也可以出现子查询
select d.*,
       (select count(*) '人数'
        from emp e
        where e.dept_id = d.id) '人数'
from dept d;


create table student
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no   varchar(10) comment '学号'
) comment '学生表';

insert into student
values (null, '黛绮丝', '2000100101'),
       (null, '谢逊',
        '2000100102'),
       (null, '殷天正', '2000100103'),
       (null, '韦一笑', '2000100104');

create table course
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';
insert into course
values (null, 'Java'),
       (null, 'PHP'),
       (null, 'MySQL'),
       (null, 'Hadoop');

create table student_course
(
    id        int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid  int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';

insert into student_course
values (null, 1, 1),
       (null, 1, 2),
       (null, 1, 3),
       (null, 2, 2),
       (null, 2, 3),
       (null, 3, 4);

# 12.查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
select s.name,
       s.no,
       c.name
from student s
         join student_course sc on s.id = sc.studentid
         join course c on sc.courseid = c.id;

select s.name,
       s.no,
       c.name
from student s,
     student_course sc,
     course c
where s.id = sc.studentid
  and sc.courseid = c.id;
绝牛雷犁热刀
关注
MySQL多表查询大全（超精确）
qq_53518630的博客
05-02 7269
目录 1、等值查询 2、内连接 3、外连接 3.1、左外连接 3.2、右外连接 3.3、完全外连接 4、交叉连接 5、自连接 6、联合查询 如果我们要查询的数据分布在不同的表时，那么需要连接多张表进行多表查询。 --而这些表当中，必须存在主外键关系（直接关联） --如果不存在直接关系，则必定存在间接关系，否则业务上是没有意义的 1、等值查询 select 字段集合 from 表1,表2,...,表n where 条件 (连接条件，一般使用主外键) #注意： 条件..
MySQL多表查询where子查询
wsdfym的博客
05-31 5381
SQL不仅支持单条的检索数据查询还允许创建子查询，即嵌套在其他查询中的查询。 表结构：https://blog.csdn.net/wsdfym/article/details/90722724 当前有用户表，以及订单表，订单详细表时，需要查询购买了某商品Id为“1001”的商品的客户信息 如果用单条语句查询需要 先从订单详细表查询商品ID为“1001”的购物订单ID 再从订单表查询该购物订单id...
mysql 多表查询
qq_63776150的博客
03-31 7611
mysql 多表查询 join的使用 及自连接
mysql多表查询的分类
Frank---7的博客
07-15 1万+
内连接 等值连接 非等值连接 自连接 外连接 inner join 等值连接 非等值连接 自连接 外连接
【Mysql】Mysql多表查询——子查询
Decade_Faiz的博客
01-24 2684
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
MySQL多表查询排序
yb233yb的博客
09-24 6222
最近看到一个面试题,是关于mysql的多表查询并排序分组的 题目如下: Music表包含所有歌曲信息，每首歌曲尤其对应的Id，Name，Year和Singer Id。 ＋－－－－＋－－－－－－－－－＋－－－－－－－－＋－－－－－－－－－－－＋ ＋－－－－＋－－－－－－－－－＋－－－－－－－－＋－－－－－－－－－－－＋ Singer表包含所有歌手信息 ＋－－－－＋－－－－－－－－－－＋ ＋－－－...
mysql多表查询，三张表或者以上
dreamStart_Lin的博客
12-18 1万+
mysql多表查询关键知识点 inner join inner join （内连接,或等值连接）：获取两个表中字段匹配关系的记录。 left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。 right join（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。 假设有tb_a / tb_b / tb_c 三张表根据条件分别返回name...
【MySQL】多表查询策略（多表联查&子查询）
热门推荐
尹煜的博客
05-01 1万+
MySQL语句学习的难点和重点就在于多表查询，同时MySQL也有诸多方法供大家选择，不论是多表联查（联结表、左连接、右连接……）还是子查询（SELECT子查询、WHERE子查询、FROM子查询），掌握一种方法达到目的即可，当然其他方法也需要理解，本文将阐述完整的多表查询方法。 首先分享下学习链接，大家可以在看完本文后选择以下链接巩固学习成果，我相信如果都给掌握的话，MySQL语句中90%的问题都将迎刃而解！！ 视频学习链接：SQL学习之道_哔哩哔哩_bilibili
MySQL多表关联查询优化
fanx_home的博客
05-25 3742
SQL优化
MySQL多表查询练习题原数据
08-18
练习题答案解决思路请查看本人原文章MySQL数据库多表查询练习题
mysql 多表查询效率优化_详解Mysql多表联合查询效率分析及优化_MySQL - join
weixin_29827279的博客
01-26 1920
...。MySQL数据库支持如下的联接查询：CROSS JOIN(交叉联接)INNER JOIN(内联接)OUTER JOIN(外联接)其它在进行各种联接操作时，一定要回忆一下在《SQL逻辑查询语句执...代码如下:SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.columnb. RIG...
基于SpringBoot+Vue+MySQL的养老院管理系统
最新发布
程序员大金的博客
09-20 577
基于SpringBoot+Vue+MySQL的养老院管理系统，附源码。
MySQL中几种常见的行格式
weixin_45923633的博客
09-13 520
动态格式会根据行中数据的实际大小动态地调整行的存储方式。如果行中的某个字段是变长的，那么这个字段的实际数据将被存储在行的末尾，而不会在行的开头占用固定的空间。这种格式适用于所有列都是固定长度的情况，可以提供最快的访问速度，但可能会浪费存储空间。：紧凑格式试图减少内部碎片，使得行的平均大小变小，从而在一个数据页中可以存储更多的行。：页面格式是一种较新的行存储格式，它旨在提高大表的访问性能和存储效率。是用来指定表中行的存储格式的属性。不同的行格式会影响数据的存储方式，进而影响存储空间的使用效率和访问性能。
MySQL练手题--体育馆的人流量(困难)
weixin_66919047的博客
09-15 643
此题稍微微有一点难度，如果看不明白可以对题目进行分解分析，分析出一步之后再接下一步来查询，题目已经要求人数大于等于100且id连续三行，就可以先筛选出大于等于100 的id 然后排序求差 差值相等的均为id连续的 ，最后筛选差值总数大于等于3 的，便出来了最后结果；
Mysql 面试题
AVICCI的博客
09-18 1326
SQL注入漏洞是一种常见的网络安全攻击方式，攻击者通过在输入的数据中注入恶意SQL代码，从而在后台数据库中执行非法的SQL操作，例如删除、修改、插入、查询等，从而获得敏感信息或者破坏数据库的完整性。为了避免SQL注入漏洞，可以采取以下措施：使用预编译语句或参数化查询来防止 SQL 注入，这样可以将 SQL 语句与用户输入的数据分离开来，有效地避免了 SQL 注入漏洞。对用户输入的数据进行严格的数据验证和过滤，过滤掉不合法的字符，例如单引号、双引号等。
【实战篇】MySQL是怎么保证高可用的？
shenyongsi的博客
09-20 919
在一个主备关系中，每个备库接收主库的 binlog 并执行。正常情况下，只要主库执行更新生成的所有 binlog，都可以传到备库并被正确地执行，备库就能达到跟主库一致的状态，这就是最终一致性。但是，MySQL 要提供高可用能力，只有最终一致性是不够的。主备切换可能是一个主动运维动作，比如软件升级、主库所在机器按计划下线等，也可能是被动操作，比如主库所在机器掉电。我们先说明一个概念，即“同步延迟”。所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。
基于SpringBoot+Vue+MySQL的招聘管理系统
程序员大金的博客
09-13 1163
基于SpringBoot+Vue+MySQL的招聘管理系统，附源码。
MySQL多表查询深入解析：SQL1999语法实战
"MySQL多表查询详解下" 在MySQL中，多表查询是数据库操作的核心技能之一，它允许我们从多个相关的表中获取数据，以满足复杂的业务需求。本篇文章主要聚焦于SQL1999标准的多表查询语法，包括各种连接类型，如内连接...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值