MySQL学习——多表查询

该笔记内容来源于黑马程序视频课​​​​​​02. 基础-课程内容&数据库相关概念_哔哩哔哩_bilibili

1、多表关系

（1）一对多(多对一)

        例如，一个部门对应对个员工的情况。通过在多的一方简历外键，指向以的一方的主键。

（2）多对多

        例如，一个学生可以选修多门课程，一门课程也可以供多个学生选择。通过建立第三张中间表实现，中间表至少包含两个外键，分别关联两方主键。

# 多对多学生表
create table student(
    id int auto_increment primary key  comment '主键ID',
    name varchar(10) comment '姓名',
    number varchar(10) comment '学号'
) comment '学生表';
insert into student values (null, '黑胡椒', '2023010001'),(null,'六小猕猴','2023010002'),(null,'韦一笑','2023010003'),(null,'谢逊','2023010004');
# 多对多课程表
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, 'C++')
# 多对多中间表
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)
#注意：SQL语言中：""和''有区别！

多表关系可视化

 （3）一对一

        多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段房子另一张表中，以提升操作效率。通过在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

2、多表查询

（1）从多表中查询数据：SELECT * FROM 表1,表2,,...; 直接这样查询数据会导致笛卡尔积现象，也就是表1和表2...中数据的所有组合情况。因此在多表查询时，需要添加限制条件以消除笛卡尔积。

 

（2）多表查询分类：

1、连接查询 
（1）内连接：相当于查询A、B交集部分数据。
（1.1）隐式内连接：SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
（1.2）显式内连接：SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 on 连接条件...;
（2）外连接
（2.1）左外连接：查询左表所有数据，以及两张表交集部分数据。
    SELECT 字段列表 FROM 表1 LEFT [UOTER] JOIN 表2 ON 条件...;
（2.2）右外连接：查询右表所有数据，以及两张表交集部分数据。
    SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
（3）自连接：当前表与自身的连接查询，自连接必须使用表别名。
    SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
2、联合查询
    把多次查询的结果合并起来，形成一个新的查询结果集。
    SELECT 字段列表 FROM 表A...
    UNION[ALL] # UNION ALL是直接将查询的结果合并，UNION会将合并后的结果去重
    SELECT 字段列表 FROM 表B...
    注：使用联合查询时，多张表的列数必须保持一致，字段类型也需要保持一致！

b. 子查询：SQL语句中嵌套SELECT语句，称之为嵌套查询，又称为子查询。

SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2);

子查询外部的语句可以使INSERT、UPDATE、DELETE、SELECT中的任何一个。

1、标量查询：子查询返回的结果为单个值(数字、字符串、日期等)，最简单的形式。
    常用的操作符有：= <>(不等于) > < <=
2、列子查询：子查询返回的结果为一列(可以是多行)。
    常见的操作符有：IN(在指定的集合范围之内，多选一)、NOT IN(不在指定的集合范围之内)、ANY/SOME(子查询返回列表中，有任意一个满足即可)、ALL(子查询返回列表的所有值都必须满足)
3、行子查询：子查询返回的结果为一行。
    常见的操作符有：=、<>、IN、NOT IN
4、表子查询：子查询结果为多行多列。
    常用的操作符：IN
注：根据子查询位置可分为：WHERE之后、FROM之后、SELECT之后