一、SQL语法——11-多表连接查询

11-多表连接查询

1.SQL 92的连接查询

（1）多个数据表都放在from之后，多个表之间以逗号隔开；连接条件放在where之后，与查询条件之间用and逻辑运算符连接。如果连接条件需要两列值相等，则称为等值连接，否则称为非等值连接；如果没有任何连接条件，则称为广义笛卡尔积。

（2）SQL 92中多表连接查询的语法格式如下：

select column1,,column2 from table1,table2 ...

[where join_condition];

（3）多表连接查询可以理解为多个表的嵌套for循环，有如下伪代码：

//依次遍历table1中的每条记录

for table1{

　　//依次遍历table2中的每条记录

　　for table2{

　　　　//条件，即where，当满足此条件时，输出两个表连接后的结果

　　　　if(condition){

　　　　　　output table1 + table2

　　　　}

　　}

}

（4）示例：

--创建需要使用到的表并添加数据
create table teacher_table(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    foreign key (java_teacher) references teacher_table(teacher_id)
);

--为表中添加数据
insert into teacher_table
values
(null,'teacher_1'),
(null,'teacher_2'),
(null,'teacher_3');

insert into student_table
values
(null,'studnet_1',1),
(null,'studnet_2',1),
(null,'studnet_3',1),
(null,'studnet_4',2),
(null,'studnet_5',2),
(null,null,2),
(null,'studnet_6',null);


/*
多表连接查询中可能出现两个或多个列的名称相同，需要在这些同名列之间使用表名作为前缀，或者使用
表别名作为前缀加以区分
*/
--查询所有学生资料以及对应的老师姓名
select s.*,teacher_name
--指定多个数据表，并指定表别名
from student_table s,teacher_table table
--使用where指定连接条件
where s.java_teacher = t.teacher_id;



/*
广义笛卡尔积的记录条数有n*m条
n:table1的记录条数
m:table2的记录条数
*/
--不使用连接条件where将会得到广义笛卡尔积
select s.*,teacher_name
--指定数据表，并指定表别名
from student_table s,teacher_table t;


--非等值连接
select s.*,teacher_name
--指定多个数据表，并指定表别名
from student_table s,teacher_table table
--使用where指定连接条件，非等值连接
where s.java_teacher>t.teacher_id;


--如果需要对记录进行过滤，则将过滤条件和连接条件使用and连接起来
select s.*,teacher_name
--指定多个数据表，并指定表别名
from student_table s,teacher_table t
--使用where指定连接条件，并指定student_name列不能为null
where s.java_teacher = t.teacher_id and student_name is not null;

--SQL 92的左外连接和右外连接就是在列名称后添加(*)或者(+)
--MySQL不支持SQL 92的外连接
select s.*,teacher_name
from student_table s,teacher_table t
--右外连接
where s.java_teacher=t.teacher_id(*);


--自连接
/*
自连接的实质是一种用法，并不是一种连接类型，它的本质是把一个表当做两个表使用
*/
--建立一个自关联的数据表
create table emp_table(
    emp_id int auto_increment primary key,
    emp_name varchar(255),
    manager_id int,
    foreign key(manager_id) references emp_table(emp_id)
);
--添加数据
insert into emp_table
values
(null,'name1',null),
(null,'name2',1),
(null,'name3',1),
(null,'name4',1);

--查询所有员工名以及对应的经理名
select emp.emp_id,emp.emp_name 员工名,mgr.emp_name 经理名
from emp_table emp,emp_table mgr
where emp.manager_id = mgr.emp_id;

2.SQL 99的连接查询

（1）SQL 99与SQL 92的连接查询原理基本相似，而SQL 99连接查询的可读性更强：查询用的多个数据表显式使用xxx join连接，而不是直接依次排列在from后，from后只需要放一个数据表；连接条件不再放在where后，而是提供了专门的连接条件子句；

（2）示例：

--多表连接查询————SQL 99

create table teacher_table(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    foreign key (java_teacher) references teacher_table(teacher_id)
);

--为表中添加数据
insert into teacher_table
values
(null,'teacher_1'),
(null,'teacher_2'),
(null,'teacher_3');

insert into student_table
values
(null,'studnet_1',1),
(null,'studnet_2',1),
(null,'studnet_3',1),
(null,'studnet_4',2),
(null,'studnet_5',2),
(null,null,2),
(null,'studnet_6',null);



/*
交叉连接(cross join):
交叉连接效果就是SQL 92中的广义笛卡儿积，所以交叉连接不需要连接条件
*/

select s.*,teacher_name
--SQL 99的多表连接查询from后只跟一个表名
from student_table s
--cross join交叉连接相当于广义笛卡尔积
cross join teacher_table t;


/*
自然连接(natural join):
自然连接表面上看来不需要连接条件，但是natural join是有连接条件的，自然连接会以
两个表中的同名列作为连接条件，如果两个表中没有同名列，则natural join的效果与cross join
的效果完全一样
*/

select s.*,teacher_name
from student_table s
natural join teacher_table t;


/*
using子句连接：
using子句可以指定一列或多列，用于显式指定两个表中的同名列作为连接条件。假设两个表中有超过一列
的同名列，如果使用natural join会把所有同名列当成连接条件，而使用using子句，就可以显式指定使用那些同名
列作为连接条件了
*/

select s.*,teacher_name
from student_table s
--join连接另一个表
join teacher_table t
--这里会出现错误，因为在student_table中不存在teacher_id，所以在使用using子句时，两个表必须存在同名列
using(teacher_id);


/*
on子句连接：
这是最常用的连接方式，SQL 99语法的连接条件放在on子句中指定，而且每个on子句只指定一个连接条件
即：如果有N个表连接，则需要有N-1个join ... on对
*/

select s.*,teacher_name
from student_table s
--join连接另一个表
join teacher_table t
--使用on来指定连接条件
on s.java_teacher = t.teacher_id;

select s.*,teacher_name
from student_table s
join teacher_table t
on s.java_teacher>t.teacher_id;



/*
左、右、全外连接
*/

--右外连接
select s.*,teacher_name
from student_table s
--right join右外连接
right join teacher_table t
on s.java_teacher=t.teacher_id;

--左外连接
select s.*,teacher_name
from student_table s
--left join左外连接
left join teacher_table t
on s.java_teacher=t.teacher_id;

--全外连接
select s.*,teacher_name
from student_table s
--full join全外连接
full join teacher_table t
on s.java_teacher=t.teacher_id;
/*
使用外连接可以把所有不满足条件的结果查询出来，而具体查询出哪一部分需要看使用
right join ，left join还是 full join(MySQL中不支持此连接)
*/