MySQL多表查询

多表查询的关系

一对一

一对多

多对多

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

或

create table student_course(
    id int auto_increment comment '主键' primary key ,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    foreign key (courseid) references course (id),
    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);

笛卡尔积

如何在多表查询时消除笛卡尔积

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

多表查询的分类

连接查询

内连接

相当于查询A、B交集部分数据

隐式内连接

select 字段列表 from 表1，表2 where 条件 ...;

显示内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

查询每一个员工的姓名，及关联的部门的名称

表结构：emp,dept

连接条件：emp.dept_id = dept.id

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

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

外连接

左外连接

查询左表所有数据，以及两张表交集部分数据

select 字段列表 from 表1 left [outer] join 表2 on 条件 ... ;

相当于查询表1（左表）的所有数据 并且也包含 表1和表2交集部分的数据

右外连接

查询右表所有数据，以及两张表交集部分数据

select 字段列表 from 表1 right [outer] join 表2 on 条件 ... ;

相当于查询表2（右表）的所有数据 并且也包含 表1和表2交集部分的数据

查询emp表的所有数据，和对应的部门信息（左外连接）

select emp.* dept.* from emp left join dept on emp.dept_id = dept.id;

查询dept表的所有数据，和对应的员工信息（右外连接）

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

自连接

当前表与自身的连接查询，自连接必须使用表别名

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

查询员工 及其所属领导的名字 -- 交集，内连接

表结构：emp

连接条件：a.managerid = b.id

select a.name,b.name from emp a,emp b where a.managerid = b.id;

查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来 -- 外连接

表结构：emp a, emp b

左外连接

select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid = b.id;

联合查询 - union,union all

把多次查询的结果合并起来，形成一个新的查询结果集

select 字段列表 from 表A ...

union [all]

select 字段列表 from 表B ...;

union all :直接对查询结果合并 ；union：合并后去重

将薪资低于5000的员工，和年龄大于50的员工全部查询出来

select * from emp where salary < 5000

union

select * from emp where age >50;

注意：对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

子查询

sql语句中嵌套select语句，称为嵌套查询，又称子查询

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

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

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

标量子查询

• 查询”销售部“的所有员工信息

1.查询”销售部“部门ID

select id from dept where name ='销售部';

2.根据销售部门ID，查询员工信息

select * from emp where dept_id=4;

=> select * from emp where dept_id = (select id from dept where name = '销售部');

• 查询在”方东白“入职之后的员工信息

1.查询”方东白"的入职日期

select entrydate from emp where name = "方东白";

2.查询指定入职日期之后入职的员工信息

select * from emp where entrydate > '2009-02-12'；

=> select * from emp where entrydate > (select entrydate from emp where name = "方东白")

列子查询

• 查询“销售部"和"市场部"的所有员工信息

1.查询”销售部"和"市场部"的部门ID

select id from dept where name ='销售部' or name ='市场部';

2.根据部门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 ='市场部');

• 查询比财务部所有人工资都高的员工信息

1.查询所有 财务部 人员工资

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 ='财务部');

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

select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name ='财务部'));

• 查询比研发部其中任意一人工资高的员工信息

1.查询研发部所有人工资

select salary from emp where dept_id = (select id from dept where name ='研发部');

2.比研发部其中任意一人工资高的员工信息

select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name ='研发部'));

行子查询

• 查询与“张无忌"的 薪资 及 直属领导 相同的员工信息

1.查询”张无忌"的薪资及直属领导

select salary,managerid from emp where name ='张无忌';

2.查询与“张无忌"的 薪资 及 直属领导 相同的员工信息

select * from emp where salary = 12500 and managerid = 1;

select * from emp where (salary,managerid) = (12500,1) ;

=>select * from emp where (salary,managerid) = (select salary,managerid from emp where name ='张无忌') ;

表子查询

• 查询与"鹿杖客"，"宋远桥"的职位和薪资相同的员工信息

  1.查询 "鹿杖客"，"宋远桥"的职位和薪资

  select job,salary from emp where name = '鹿杖客' or name ='宋远桥';

  1,2多选1

  

  2.查询与"鹿杖客"，"宋远桥"的职位和薪资相同的员工信息

  select * from emp where (job,salary) in (select job,salary from emp where name = '鹿杖客' or name ='宋远桥');

• 查询入职日期是"2006-01-01"之后的员工信息及其部门信息

1. 入职日期是"2006-01-01"之后的员工信息

   select * from emp where entrydate > '2006-01-01'；

   

2. 查询这部分员工，对应的部门信息

   select e.* , d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;