连接查询

通过查询多张表，用连接查询进行多表联合查询

关键字：inner join 内连接

               left join 左连接

               right join 右连接 

数据准备

创建新的数据库：create database 数据库名;

create database db_test2;

使用数据库：use 数据库名;

use db_test2;

创建班级信息表：

create table 表名（字段名1，字段名2，......);

代码实现

create table classes(
	class_id int primary key auto_increment,#主键自增
	class_name varchar(40) not null unique,
	class_remark varchar(200)
);

创建学生表：

create table 表名（字段名1，字段名2，......);

代码实现

create table students(
	stu_num char(8) primary key ,
	stu_name varchar(20) not null,
	stu_gender char(2) not null,
	stu_age int not null,
	cid int,
	constraint FK_STUDENTS_CLASSES foreign key(cid) 
	references classes(class_id)
	on update cascade ON DELETE CASCADE
);

添加班级信息

insert into 表名 （字段名）values(添加的数据);

代码实现

insert into classes (class_name,class_remark)values('Java2204','......');

insert into classes (class_name,class_remark)values('Java2205','......');

insert into classes (class_name,class_remark)values('Java2206','......');

insert into classes (class_name,class_remark)values('Python','......');

运行结果

添加学生信息

以下三个信息，属于class_id=1 的班级 （Java2204）

代码实现

insert into students (stu_num,stu_name,stu_gender,stu_age,cid)
values('20220101','张三','男',20,1);

insert into students (stu_num,stu_name,stu_gender,stu_age,cid)
values('20220102','李四','女',20,1);

insert into students (stu_num,stu_name,stu_gender,stu_age,cid)
values('20220103','王五','男',20,1);

以下两个学生信息，属于class_id=2 的班级 （Java2205）

代码实现

insert into students (stu_num,stu_name,stu_gender,stu_age,cid)
values('20220104','赵婷','女',20,1);

insert into students (stu_num,stu_name,stu_gender,stu_age,cid)
values('20220105','孙七','男',20,2);

小红和小明没有设置班级信息

代码实现

insert into students (stu_num,stu_name,stu_gender,stu_age)
values('20220106','小红','女',20);

insert into students (stu_num,stu_name,stu_gender,stu_age)
values('20220107','小明','男',20);

运行结果

select * from 表名；查询表

代码实现

select * from students s ;
select * from classes c ;

运行结果

内连接

语法

select ...... from 表名1 inner join 表名2 on 匹配条件 [where 条件]；

经过内连接（表连接）之后，将两张表的数据以笛卡尔积的效果进行连接

代码实现

select * from 表名1 inner join 表名2;

select * from students inner join classes;

 运行结果

产生笛卡尔积，效果如下，将表1中的每个数据与第二个表中的每个数据都进行匹配

7 * 3 = 28，共有28个数据

消除笛卡尔积，在前缀后加上 on 匹配条件 [where 条件];

select ...... from 表名1 inner join 表名2 on 匹配条件 [where 条件]； 

内连接条件

两张表使用inner join连接查询之后生产的笛卡尔积数据中很多数据都是无意义的，我们如何消除无意义的数据呢？——添加两张进行连接查询时的匹配条件

使用 on 设置两张表连接查询时的匹配条件

两张表连接查询+条件

代码实现

使用where进行两个表之间的连接

select * from students inner join classes where students.cid=classes.class_id ;

运行结果

代码实现

使用on进行两个表之间的连接

select * from students inner join classes on students.cid=classes.class_id ;

运行结果

on连接查询和where连接查询的区别

where筛选：先生成笛卡尔积后进行判断连接条件是否成立

on筛选：先进行判断连接条件是否成立，如果成立后，再会进行组合，就不会有笛卡尔积的结果

左连接 LEFT JOIN

左连接定义

左连接显示左表中的所有数据，如果在右表中存在与左表记录满足匹配条件的数据，则进行匹配，如果右表中不存在匹配数据，则显示为NULL；

语法

select * from 左表名 left join 右表名 on 匹配条件 [where 条件]；

作用

左连接：显示左表中的所有记录

例

需求

请查询出所有学生信息，如果有学生有对应的班级信息，则将对应的班级信息也查询出来

代码实现

select * from students left join classes on students.cid=classes.class_id ;

运行结果

右连接 RIGHT JOIN

右连接定义

右连接显示右表中的所有数据，如果在左表中存在与右表记录满足匹配条件的数据，则进行匹配，如果左表中不存在匹配数据，则显示为NULL；

语法

select * from 表名1 RIGHT JOIN 表名2 ON 表名1与表名2的关联字符；

作用

右连接：显示右表中的所有记录

例

需求

将右表中的所有数据显示出来

代码实现

select * from students right join classes on students.cid=classes.class_id ;

运行结果

左连接、右连接与内连接的区别

内连接：只会显示出两表中有关联的数据

左连接：显示出左表中的所有数据，右表中只写有关联的数据

右连接：显示出右表中的所有数据，左表中只写有关联的数据

数据表别名

语法

alter table 表名 rename column 列名 to 新列名；

案例

代码实现

修改列名

alter table students rename column stu_name to name;
alter table classes rename column class_name to name;

运行结果

当两个表的字段名称相同时，如何进行查询字段

代码实现

select students.name,classes.name from students inner join classes on students.cid = classes.class_id ;

运行结果

使用别名查询字段

代码实现

select s.name,c.name from students s inner join classes c on s.cid=c.class_id ;

运行结果 

子查询/嵌套查询 

定义

子查询——先进行一次查询，第一次查询的结果作为第二次查询的源/条件（第二次查询是基于第一次的查询结果来进行的）

子查询返回单个值——单行单列

案例 

查询班级表中字段名为Java2204的数据， 查询所有Java2204班级中选课副码为1的学生信息

代码实现

查询班级表中字段名为Java2204的数据

select class_id from classes c where name ='Java2204';

 查询所有Java2204班级中选课副码为1的学生信息

select * from students s where cid=1;

运行结果

查询所有Java班级中的学生信息 单列多行查询

代码实现

select class_id from classes c where name like 'Java%' ;

运行结果

显示三条查询语句 union连接关键字

代码实现

#显示三条查询语句 union连接关键字
select * from students s where cid=1
union
select * from students s where cid=2
union
select * from students s where cid=3;

运行结果

子查询，in关键字 单列多行查询

如果查询结果是单列多行，要有关键字in

in代表的是包含，not in代表不包含

代码实现

select * from students s where cid in(select class_id from classes c where name like 'Java%');

运行结果

 查询cid=1的班级中性别为男的学生信息

语法

select * from (select * from 表名 where 限制) 别名 where 别名.列名 = 限制；

将第一步查询语句当作一个虚拟表（限制信息）查询第二个表

代码实现

select * from (select * from students where cid = 1) t where t.stu_gender='男';

运行结果