学习MySQL仅此一篇就够了(多表查询)

发布时间:2024年01月24日

多表查询

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

笛卡尔积:笛卡尔乘积是指在数学中,两个集合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');

文章来源:https://blog.csdn.net/m0_66011019/article/details/135829029
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。