数据库多表关系与查询操作详解-优快云博客

学习参考https://www.bilibili.com/video/BV1Kr4y1i7ru?spm_id_from=333.999.0.0

一.多表关系

1.一对多

2.多对多

create table student(
	id int auto_increment primary key comment '主键id',
    name varchar(10) not null comment '姓名'
)comment '学生';
insert into student(name) values 
('张三'),
('李四'),
('王五'),
('老六');

create table course(
	id int auto_increment primary key comment '主键id',
    name varchar(10) not null comment '课程'
)comment '可选课程';
insert into course(name) values
('C'),
('C++'),
('C#'),
('go');

create table sc(
	id int auto_increment primary key comment'主键id',
    sid int not null comment'学生id',
    cid int not null comment'课程id',
    constraint fkc foreign key (cid) references course(id),
    constraint fks foreign key (sid) references student(id)
)comment '学生与课程';
insert into sc(sid, cid) values
(1,1),
(1,2),
(1,3),
(1,4),
(2,1),
(3,2),
(4,4);

(1)sc

(2)studet

(3)course

3.一对一

二.多表查询概述

以多对多为例。

select *from 表,表,···;

故可以添加where条件。

select *from student, course ,sc where sc.sid = student.id && sc.cid = course.id;

三.内连接

(1)隐式内连接

select 表1.字段, 表2.字段 from 表1,表2,··· where 条件

(2)显式内连接

select 表1.字段 from 表1 inner join (各种表) on 条件;

四.外连接

(1)左外连接

select * from 表 left join 表2 on 条件;

(2)右外连接

五.自连接

六.联合查询

select *from sc where sid<1
union all
select *from sc where cid>1

select *from sc where sid<1
union 
select *from sc where cid>1

七.子查询

1.标量子查询

select course.id from course where name = 'C++';
select sc.sid from sc where cid = (select course.id from course where name = 'C++');

注意一种错误

select student.name from student where student.id = (select sc.sid from sc where cid = (select course.id from course where name = 'C++'));

22:42:37	select student.name from student where student.id = (select sc.sid from sc where cid = (select course.id from course where name = 'C++')) LIMIT 0, 1000	Error Code: 1242. Subquery returns more than 1 row	0.000 sec

这样做法违背了子查询返回的结果是单个值

2.列子查询

接上

select student.name from student where student.id in (select sc.sid from sc where cid = (select course.id from course where name = 'C++'));

3.行子查询

select course.id,course.name from course where name = 'C++';
select course.id,course.name from course where (id,name) = (select course.id,course.name from course where name = 'C++');

4.表子查询

select sc.sid,sc.cid from sc where (sid = 3 or sid = 2);
select student.name,course.name from student,course where (student.id,course.id) in (select sc.sid,sc.cid from sc where (sid = 3 or sid = 2));