学习参考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));