-- 删除现有表
drop table if exists class;
drop table if exists student;
-- 建表
create table class (
id int,
name varchar(50)
);
create table student (
id int,
name varchar(50),
class_id int
);
-- 查看表结构
desc class;
desc student;
-- 清空现有表数据
truncate class;
truncate student;
-- 表数据初始化
insert into class (id, name) values (1, '班级1');
insert into class (id, name) values (2, '班级2');
insert into class (id, name) values (3, '班级3');
insert into class (id, name) values (4, '班级4');
insert into student (id, name, class_id) values (1, '学生1', 1);
insert into student (id, name, class_id) values (2, '学生2', 2);
insert into student (id, name, class_id) values (3, '学生3', 3);
insert into student (id, name, class_id) values (4, '学生4', 1);
insert into student (id, name, class_id) values (5, '学生5', null);
insert into student (id, name, class_id) values (6, '学生6', 1);
-- 查看表数据
select * from class;
select * from student;
-- test join...
select * from student a join class b on a.class_id = b.id; -- 查询【所有有班级】的学生,并带出对应的班级信息
select * from student a left join class b on a.class_id = b.id; -- 查询【所有】学生,并带出对应的班级信息,如果某个学生没有班级,则班级信息列以 null 填充
select * from student a right join class b on a.class_id = b.id; -- 查询【所有】班级,并带出其下所有的学生信息,如果某个班级没有学生,则学生信息列以 null 填充
select * from class a join student b on a.id = b.class_id; -- 查询【所有有学生】的班级,并带出其下所有的学生信息
select * from class a left join student b on a.id = b.class_id; -- 查询【所有】班级,并带出其下所有的学生信息,如果某个班级没有学生,则学生信息列以 null 填充
select * from class a right join student b on a.id = b.class_id; -- 查询【所有】学生,并带出对应的班级信息,如果某个学生没有班级,则班级信息列以 null 填充
MySQL JOIN
最新推荐文章于 2021-10-14 23:02:40 发布