MySQL连接查询

数据准备

# 创建班级表
create table classes(
 	id int primary key auto_increment,
 	name varchar(40) not null unique
);

# 添加班级数据
insert into classes values(0,'北京大学1班');
insert into classes values(0,'北京大学2班');
insert into classes values(0,'北京大学3班');
insert into classes values(0,'清华大学1班');
insert into classes values(0,'清华大学2班');
insert into classes values(0,'清华大学3班');
insert into classes values(0,'北京邮电大学1班');
insert into classes values(0,'北京邮电大学2班');
insert into classes values(0,'北京邮电大学3班');

# 创建学生表
create table students(
 id char(8) primary key,
 name varchar(20) not null,
 age int not null,
 gender char(2) not null,
 height int Unsigned default 160,
 cid int,
 constraint FK_STUDENTS_CLASSES foreign key(cid) references
classes(id) ON UPDATE CASCADE ON DELETE CASCADE
);

# 添加学生数据
INSERT into db_select.students VALUES(0,'诺克萨斯之手','20','男','180',1);
INSERT into db_select.students VALUES(1,'武器大师','20','男','170',2);
INSERT into db_select.students VALUES(2,'雷克顿','20','男','150',3);
INSERT into db_select.students VALUES(3,'瑞文','20','女','170',1);
INSERT into db_select.students VALUES(4,'发条魔灵','20','女','160',1);
INSERT into db_select.students VALUES(5,'流浪法师','20','男','170',5);
INSERT into db_select.students VALUES(6,'暗夜之手','22','女','170',7);
INSERT into db_select.students VALUES(7,'锤石','21','男','170',9);
INSERT into db_select.students VALUES(8,'辛德拉','18','女','165',1);
INSERT into db_select.students VALUES(9,'诡术妖姬','18','女','180',1);

内连接 inner join

# 内连接语法
select * from tb1 inner join tb2 ON 匹配条件 [where 条件];
# where设置过滤条件：先⽣成笛卡尔积再从笛卡尔积中过滤数据（效率很低）
select * from students INNER JOIN classes where students.cid=classes.id;
# ON设置连接查询条件：先判断连接条件是否成⽴，如果成⽴两张表的数据进⾏组合⽣成⼀条结果记录
select * from students INNER JOIN classes ON students.cid=classes.id;
# 查询班里里男生的个人信息
select * from students inner join classes on students.cid=classes.id where students.gender='男';

左连接 left join

# 左连接语法
select * from left_tb1 LEFT JOIN right_tb1 ON 匹配条件 [where 条件];

# 左连接显示左表全部数据，如果右表中不存在的数据则显示null
select * from students LEFT JOIN classes ON students.cid =classes.id;

右连接 right join

select * from right_tb1 right JOIN left_tb1 ON 匹配条件 [where 条件];

# 右连接显示右表全部数据，如果左表中不存在的数据则显示null
select * from students right join classes on students.cid=classes.id;

自连接

子查询