mySql数据库学习003-多表查询

多表查询

创建数据表：班级表与学生表

create table if not exists class(
	id tinyint unsigned primary key auto_increment,
	name varchar(20) not null,
	description varchar(255),
	createAt timestamp default current_timestamp,
	updateAt timestamp default current_timestamp on update current_timestamp
);

insert into class (name, description) values ('一班','火箭班');
insert into class (name, description) values ('二班','平行班');
insert into class (name, description) values ('三班','实验班');
insert into class (name, description) values ('四班','待定班');



create table if not exists student (
	id int unsigned primary key auto_increment,
	name varchar(20) not null,
	age tinyint unsigned not null,
	gender enum('男','女','保密') default '保密',
	class_id tinyint unsigned,
	createAt timestamp default current_timestamp,
	updateAt timestamp default current_timestamp on update current_timestamp,
	foreign key(class_id) references class(id) on update cascade on delete set null
);


insert into student (name,age,gender,class_id) values ('张三',20,'男',1);
insert into student (name,age,gender,class_id) values ('李四',19,'女',1);
insert into student (name,age,gender,class_id) values ('王五',21,'女',2);
insert into student (name,age,gender,class_id) values ('赵六',18,'女',2);
insert into student (name,age,gender,class_id) values ('孙七',19,'男',3);
insert into student (name,age,gender,class_id) values ('周八',19,'男',3);
insert into student (name,age,gender) values ('张三丰',15,'男');

解释：

• student 表设置外键 class_id, 在进行 update 操作时，级联变动（即从表也跟随更新）。delete 操作时，设置为 null。

一、直接查询

select * from student, class

查询结果:

……

解释：

• 通过查询发现，查询的数据总量为 4 * 7 = 28 条，是 class 表与 student 表的组合。

select * from student, class where student.class_id = class.id

查询结果:

解释:

• 因为 student 表中 name 为“张三丰”的数据，class_id 为 null，故没有被查询出来；

二、连接查询

1. 内连接查询 （inner join 或 join）

select * from student join class on student.class_id = class.id;

查询结果:

解释:

• 查询语句中，join 表示连接，条件语句采用关键词 on，功能类似 where；
• 内连接查询的结果与直接查询的结果是一样的。相关sql语句如下：

select * from student, class where student.class_id = class.id

• 如果需要查询出指定字段，需要设置具体哪个表的哪个字段。sql 语句如下：

select student.id, student.name,class.name, from student, class where student.class_id = class.id

• 同时，可以设置别名，具体的 sql语句如下:

select stu.id, stu.name, cls.name className from student stu, class cls where stu.class_id = cls.id;
-- 注意：stu 为 student 的别名，cls 为 class 的别名，className 为 class 表的 name 的别名；

2. 外连接查询

2.1 左外连接查询（left outer join 或 left join）

select * from student left join class on student.class_id = class.id;

查询结果:

解释:

• 结果集中，把左表 student 的数据全部查询出来了，包括 class_id = null 的数据（name = '张丰' ）；
• 左外连接查询，on 后的条件是不约束 左表 student 的，只 约束 右表 class。
• 简单来说，左外连接的特点是：会将左表的数据全部查询出来，并把右表符合条件的数据查询出来。

2.2 右外连接查询

select * from student right join class on student.class_id = class.id;

查询结果:

解释:

• 左外连接查询，on 后的条件是不约束 右表 class 的，只 约束 左表 student。
• 简单来说，右外连接的特点是：会将右表的数据全部查询出来，并把左表符合条件的数据查询出来。

3. UNION 查询

select id, name from student union select id, name from class;

查询结果:

解释：

• union 查询的作用是：在纵向上将多张表的查询结果拼接起来。同时条件是，两张表的查询字段个数形同，如都是 id, name 字段。

4. 子查询

4.1 将一个查询语句的查询结果作为另外一个查询语句的条件来使用

select name from class where id = (select class_id from student where id = 3)

查询结果:

select name from class where id in (select class_id from student where id >= 3)

查询结果:

解释：

• 做为条件的查询语句的结果是一个值，可以使用 = 做为另外查询语句的条件；如果做为条件的查询语句的结果是多个值，需要使用 in 做为另外查询语句的条件。

4.2 将一个查询语句的查询结果作为另外一个查询语句的表来使用

select * from (select name from class where id >= 2) t

查询结果:

解释：

• 将一个查询语句的查询结果作为另外一个查询语句的表来使用，必须对条件(表)设置别名，如示例中的 t