MySQL基础知识整理 ---- 多表关系和多表查询

一.多表关系

在数据库表和表之间存在三中对应关系,一对一，一对多(多对一)，多对多。

1.一对一关系
理论上可以将任意一方向另外一方合并，但一般结合实际情况进行合并。实际运用中一般不会出现一对一的情况。比如，学生的学号和姓名是一对一的关系，但是我们在设计表时一般不会将二者分开。

2.一对多关系
理论上我们将一的一方向多的一方合并。实际操作时我们可以在多的一方设置外键来关联一的一方，实现合并。比如，班级和学生时一对多的关系，那我们就可以在学生表中设置外键来将其和班级表关联。

3.多对多关系
不能直接将量表合并。但是可以借助一张中间表来实现二者的关联。比如，学生和课程之间的关系，一个学生可以选多门课程，一门课程也可以被多个学生选，这时如果我们要将二者联系起来的话，就需要借助一张选课表。

二.多表查询

多表查询可以分为三种:内连接查询,外连接查询,子查询。其主要的作用是查询时消除无用的数据。因为当我们直接查询多张表时，其查询的结果是多张表的笛卡尔集,笛卡尔集中存在许多无用数据。

/*
创建班级表
*/
create table class{
   id int primary key,
   cname varchar(10),
   class_location varchar(10)  -- 班级所在校区
};

/*
创建学生表
*/
create table student{
  son char(5) primary key,
  sname varchar(10),
  cls_id int,  -- 外键列
  constraint stu_cls_fk foreign key (cls_id) references class(id) on update cascade on delete cascade;  -- 关联并添加级联
};

/*
直接查询会显示笛卡尔集
*/
select *
from student,class;

1.内连接查询
(1)隐式内连接
使用where条件消除无用的数据

select son,sname,con,cname
from student,class
where student.cls_id = class.id;

(2)显示内连接

select son,sname,con,cname
from student inner join class on student.cls_id = class.id; -- inner可以省略

2.外连接查询
(1)左外连接查询
查询的是左表的所有内容以及其与另一表的交集部分。

select son,sname,cls_id,id,cname,class_location
from student left outer join class on student.cls_id = class.id;

结果展示:

(2)右外连接查询
查询的是右表的所有内容,以及其与另一表的交集部分

select son,sname,cls_id,id,cname,class_location
from student right outer join class on student.cls_id = class.id;

3.子查询
查询中嵌套查询,称嵌套查询为子查询

(1)子查询的结果是单行单列的

/*
查询cls_id值最大的学生的信息
*/
select son,sname
from student
where id = (
   select max(id)
   from student
);

(2) 子查询的结果是多行单列的
可以结合 in 运算符使用

(3)子查询的结果是多行多列的
子查询可以作为一张虚拟表

/*
子查询的结果是多行多列
*/

select *
from student (select * from class where id > 1) t  -- 筛选班级ID>1的班级,形成的虚拟表起别名为t
where student.cls_id = t.id; -- 将虚拟表和student表通过id连接起来