Mysql连接查询介绍

场景：

需要得到一个学生列表，要求是展示如下列表：

学生，性别，班级名字

此时，需要不单从学生表获取数据，还需要从班级表获取数据 于是就需要：

连接查询，join

语法：from tbl_name1 join tbl_name2 on 连接条件;

select stu_name,gender,class_id,class_name from select_student join select_class on select_student.class_id=select_class.id;

过程是：先执行

from子句，需要连接join

两个特殊的地方：join , on

join 连接：

除了默认的连接外，还有其他形式的连接方式： 内连接

外连接，左外连接，右外连接，【全外连接，也是外连接，但不是mysql所支持的】 交叉连接

自然连接

内连接：inner join

记录与真实的记录连接，称之为内连接（两条真实存在的记录连接） mysql默认的连接就是inner join，可省略inner

首先给 select_student 表再添加一个学生，但不分配班级：

insert into select_student values (56,'李莫愁','female',33,1111.11,167.56);

注意：此时班级表中没有class_id为33的班级：

再次执行上面的连接查询：

select stu_name,gender,class_id,class_name from select_student join select_class on select_student.class_id=select_class.id;

此时，发现刚刚新添加的数据（李莫愁）没有出现在查询结果中。因为‘李莫愁’这条数据中的class_id，在select_class表中不存在，所以不会出现在展示结果中！此称之为内连接！

内连接可以省略连接条件：

on可以省略，相当于连接条件永远成立！返回值是一个笛卡尔积！

select stu_name,gender,class_id,class_name from select_student inner join select_class;

外连接：left join , right join

连接的记录，可能是一方不存在的！（两条记录中可能某条不存在） 示例：（李莫愁这条数据在表select_class中不存在）

select stu_name,gender,class_id,class_name from select_student left join select_class on select_student.class_id=select_class.id;

结果中只有连接成功的（两条记录都真实存在的）。

注意左外与右外的区别：

区别在于，哪个表的记录会最终出现在连接结果内！该记录指的是连接失败的记录

总结：内连接，外连接差别不大，只是外连接会将没有连接成功的记录也会展示在结果中，而内连接的

(在select_class表中再增加一条记录：insert into select_class values(10,'what110','209');)

select stu_name,class_name from select_student left join select_class on select_student.class_id=select_class.id;

select stu_name,class_name from select_student right join select_class on select_student.class_id=select_class.id;

什么是左表和右表？

join关键字前面的（左边的）是左表，join关键字后面的（右边的）是右表

左外：如果出现左表记录连接不上右表记录，左表记录会出现在最终的连接结果内，而右表记 录相应的字段会被设置成null

右外：如果出现右表记录连接不上左表记录，右表记录会出现在最终的连接结果内，而左表 记录相应的字段会被设置成null

因此，可以交换表的位置，达到使用left 与 right 混用的目的！

问题：

统计每个班级内学生的数量！在班级列表内展示如下列表：

班级名，教室号，学生数量

select class_name,classroom,count(select_student.id) as s_count from select_class left join select_student on select_class.id=select_student.class_id group by select_class.id;

注意：外连接应该要有条件！

交叉连接：cross join

select stu_name,class_name from select_class cross join select_student on select_class.id=select_student.class_id; 结果与内连接一致！

有时，在获得笛卡尔积时显示时才会使用交叉连接 交叉连接相当于是没有条件的内连接！

自然连接：natural join

mysql自动判断连接条件，帮助我们完成连接！

select stu_name,class_name from select_class natural join select_student;

典型的条件就是两个表中的同名字段。

为了测试：我们将 select_class 表中的 id 字段修改为 class_id ：

alter table select_class change id class_id int unsigned auto_increment;

再执行自然连接查询：

自然连接也分内连接与外连接！ 自然内连接：natural join

自然外连接分：自然左外连接 natural left join，自然右外连接 natural right join

select stu_name,class_name from select_class natural left join select_student;

select stu_name,class_name from select_class natural right join select_student;

总结：

最终的效果只有：内连接，左外连接，右外连接！（只要掌握这三个即可） 交叉连接，是特殊的内连接

自然连接，相当于自动判断连接条件，完成内，左外，右外！

连接条件，on，using，where on后面使用一个连接条件表达式

using(连接字段)，要求使用同名字段进行连接！

select class_name,stu_name from select_class inner join select_student using(class_id);

using特别的地方：

会对字段列表做一次整理！将连接字段作为一次显示：

select * from select_class left join select_student using(class_id)\\G

练习

设计一个系统，保存乒乓球联赛的比赛信息 队员使用 select_student 表：

创建一个比赛信息表： create table select_match (

match_id int unsigned primary key auto_increment,

player_1 int unsigned comment '选手1的ID', player_2 int unsigned comment '选手2的ID', match_time datetime, match_result char(3), match_add int default 0 ) engine=innodb;

insert into select_match values

(null,22,45,'2015-10-10 10:10:10','5:3',0), (null,34,44,'2015-11-11 20:10:10','2:4',0), (null,11,99,'2014-12-10 19:10:10','4:1',0), (null,56,67,'2015-10-19 20:00:00','4:3',0), (null,99,45,'2015-09-10 18:00:00','4:0',0);

使用 select_student 与 select_match 这两个表做一个比赛信息公告板，要求格式如下： 比赛时间 选手1名字 比赛结果 选手2名字

思路：

如果将选手1与选手2名字用id来表示：

select match_time,player_1,match_result,player_2 from select_match;

将player_1换成 stu_name来展示：

select match_time,stu_name,match_result,player_2 from select_match left join select_student on select_match.player_1=select_student.id;

此时，可以对学生表再次连接！出现一个表在一次查询时，被多次使用！

注意：保证在使用没有歧义，就要为表取别名

select match_time,s1.stu_name,match_result,s2.stu_name from select_match left join select_student as s1 on select_match.player_1=s1.id left join select_student as s2 on select_match.player_2=s2.id;