Mysql Select-join(连接查询)
// 老师表
create table join_teacher(
id int primary key auto_increment,
t_name varchar(10),
gender enum('male', 'female', 'secret')
)engine innodb character set uff8;
insert into join_teacher values
(1, '韩信', 'male'),
(2, '李白', 'female'),
(3, '韩非子', 'secret')
(4, '孙武', 'male');
// 班级表
create table join_class(
id int primary key auto_increment,
c_name char(7),
root char(3)
)engine innodb character set utf8;
insert into join_class values
(1, 'node0115', '207'),
(2, 'node0228', '104'),
(3, 'node0331', '102')
(4, 'node0505', '202');
// 代课信息表
create table join_teacher_class(
id int primary key auto_increment,
t_id int,
c_id int,
days tinyint,
begin_date date,
end_date date
)engine innodb character set utf8;
insert into join_teacher_class values
(1, '1', '1', 15, '2013-01-15', '2013-02-20'),
(2, '1', '2', 18, '2013-02-28', '2013-03-30'),
(3, '1', '3', 22, '2013-03-31', '2013-05-05'),
(4, '2', '1', 20, '2013-02-22', '2013-03-25'),
(5, '2', '2', 22, '2013-03-31', '2013-04-29'),
(6, '3', '1', 15, '2013-03-27', '2013-04-18'),
(7, '1', '1', 15, '2013-04-19', '2013-05-01'),
(8, '3', '3', 15, '2013-05-28', '2013-06-15'),
(9, '2', '1', 5, '2013-05-04', '2013-05-15');
连接查询思路
连接的分类
- 根据连接的条件不同
- 内连接(inner): 只能连接真实存在数据
- 外连接(outer):可以连接不存在的数据
- 自然连接(natrual)
连接过程
- 连接过程:左表第一条数据一次与右边每条数据匹配,符合条件则保留
连接注意事项
- 内连接
- 内连接可以省略连接条件,所有左表都要与右表连接,即m*n。称之为交叉连接或者笛卡尔积连接
- 字段无冲突,不用写表名(table.colum)
- 表别名可以使查询语句更精简
- 字段别名可以区别重复字段
- 外链接不能使用where作为连接关键字
- 外链接不能省略条件
// inner join 内连接
// tel_left inner join tbl_right on 连接条件
select join_teacher.t_name, join_teacher_class.begin_date, join_teacher_class.days from join_teacher inner join join_teacher_class on
join_teacher.id = join_teacher_class.t_id;
// left outer join 外连接
// tel_left left outer join tbl_right on 连接条件
select join_teacher.t_name, join_teacher_class.begin_date, join_teacher_class.days from join_teacher left outer join join_teacher_class on
join_teacher.id = join_teacher_class.t_id;
// cross join 笛卡尔积连接(等同于没有条件的内连接)
// mysql里 cross join 和inner join功能相同,但在数据库定义上,cross join特质笛卡尔积连接。
select join_teacher.t_name, join_teacher_class.begin_date, join_teacher_class.days from join_teacher cross join join_teacher_class on
join_teacher.id = join_teacher_class.t_id;
- mysql里,省略连接方式时,inner join为默认连接方式
select join_teacher.t_name, join_teacher_class.begin_date, join_teacher_class.days from join_teacher join join_teacher_class on
join_teacher.id = join_teacher_class.t_id;
- mysql里,直接from多个表,默认为做多个表的笛卡尔积的连接
内连接代替on的写法,逻辑含以上有区别,结果相同,只是语义上更准确
- on:直接使用连接条件
- where:先形成笛卡尔积,然后过滤条件
- using:前提要求连接表之间的字段相同
外链接
- 左外连接(left outer join):连接不到数据时,左链接数据优先保留
- 右外链接 (right outer join) :连接不到数据时,右链接数据优先保留
- 全外链接:mysql没有全外,可以用 左连接union右连接来模拟
自然连接
- 根据mysql自己的选择连接
- 分为内连接(natrual join)、外链接(左外、右外)
- 内连接:相当于 inner join using
- 左外连接(natrual left join):相当于 left join using
- 右外连接(natrual right join):相当于 right join using
多表连接
// 可以无限连
select tab_1.* tab_2.* from tab_1 left join tab_2 on tab_1.id = tab_2._id left join tab_3 on tab_3.id = tab_2.id where ...