学习MySQL仅此一篇就够了（多表查询）

多表查询

概述：指从多张表中查询数据

笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。

• 一对多

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的一方的主键

• 多对多

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

 

create table student (
    id int primary key auto_increment comment '主键id',
    name varchar(10) comment '姓名',
    num varchar(10) comment '学号'
) comment '学生表';
insert into student values(null, 'wyx', '1234'), (null, 'sxx', '4321');

create table course (
    id int primary key auto_increment comment '主键id',
    name varchar(10) comment '课程名称'
) comment '课程表';
insert into course values(null, 'java'), (null, 'python'), (null, 'go'), (null, 'c++');

create table student_course(
    id int primary key auto_increment comment '主键',
    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);

 

• 一对一

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表上，其他详细字段放在另一张表中，以提示效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的unique

create table tb_user(
    id int primary key auto_increment comment '主键id',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1:男， 2:女',
    phone char(1) comment '手机号'
) comment '用户基本信息表';

insert into tb_user(id, name, age, gender, phone) VALUES (null, 'sxx', 45, '1', '1'),(null, 'wyx', 23, '2', '1');

create table tb_user_edu(
    id int primary key auto_increment comment '主键id',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primarysch varchar(50) comment '小学',
    middlesch varchar(50) comment '中学',
    universch varchar(50) comment '大学',
    userid int unique comment '用户id',
    constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';

insert into tb_user_edu(id, degree, major, primarysch, middlesch, universch, userid) values
        (null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院',1 ),
        (null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院',2 );
        
select * from tb_user_edu,tb_user where tb_user.id = tb_user_edu.id;

内连接

• 隐式内连接

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

• 显示内连接

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

#查询学生姓名以及学生的学校
select tb_user.name, tb_user_edu.universch from tb_user, tb_user_edu where tb_user.id = tb_user_edu.id;

#显示内连接查询
select t.name, e.universch from tb_user_edu e join tb_user t on e.userid = t.id;

外连接

• 左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件..;
相当于查询表1的所有数据包含表1和表2交集部分的数据

select tb_user.*,tb_user_edu.universch from tb_user left join tb_user_edu on tb_user.id = tb_user_edu.userid;

• 右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件..;
相当于查询表2的所有数据包含表1和表2交集部分的数据

select tb_user.name,tb_user_edu.* from tb_user right join tb_user_edu on tb_user.id = tb_user_edu.userid;

子连接

select 字段列表 from 表a 别名a join 表a  别名b on 条件...;

select a.id,b.name from tb_user a join tb_user b on a.id = b.phone;

联合查询-union,union all

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表A...
	union [all]
select 字段列表 from 表B...;

#将薪资低于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);

• 标量子查询

子查询返回的结果是单个值，最简单的形式，这种子查询称为标量子查询。

#查询销售部门id
select id from emp where name = '销售部';
#根据销售部部门ID，查询员工信息
select * from emp where dept_id = (select id from emp where name = '销售部')

• 列子查询

子查询返回的结果是一列，这种子查询称为列子查询

常用操作符：in,not in ,any, some, all

#查询销售部和市场部的部门id
select id from dept where name = '销售部' or name = '市场部';

#根据id查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

• 行子查询

子查询返回的结果是一行，这种子查询称为行子查询

#查询wyx的薪资及直属领导相同的员工信息
a查询wyx的薪资及直属领导
select salary,managerid from emp where name = 'wyx' ;
b查询于wyx的薪资及直属领导相同的员工信息
select * from emp where (salsry,managerid) = (select salary,managerid from emp where name = 'wyx');

• 表子查询

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

常用操作符：in

#查询与wyx，sxx的职位和薪资相同的员工信息
select salary,job from emp where name = 'wyx' or name = 'sxx';
select * from emp where (job,salary) in (select salary,job from emp where name = 'wyx' or name = 'sxx');