Mysql Select-join

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');

连接查询思路

• 将数据按照某种条件，连接起来，在进行筛选处理

连接的分类

• 根据连接的条件不同
  
  1. 内连接(inner): 只能连接真实存在数据
  2. 外连接(outer)：可以连接不存在的数据
  3. 自然连接(natrual)

连接过程

• 连接过程：左表第一条数据一次与右边每条数据匹配，符合条件则保留

连接注意事项

• 内连接
  
  1. 内连接可以省略连接条件，所有左表都要与右表连接，即m*n。称之为交叉连接或者笛卡尔积连接
  2. 字段无冲突，不用写表名（table.colum）
  3. 表别名可以使查询语句更精简
  4. 字段别名可以区别重复字段
  5. 外链接不能使用where作为连接关键字
  6. 外链接不能省略条件

// 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 ...