MySQL基础教程(十五)

连接(Join)是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。多表连接查询是使用SQL的基本操作，但连接的方式却有多种，熟练使用这些连接方式能够简化SQL语句，提高数据库运行效率。

SQL的连接分为以下七种情况：

INNER JOIN:内连接也称为等值连接。
LEFT JOIN或者LEFT OUTER JOIN:左连接
LEFT JOIN的特殊情况：只查询左连接时不满足条件的记录。
RIGHT JOIN或者 RIGHT OUTER JOIN：右连接
RIGHT JOIN的特殊情况：只查询右连接时不满足条件的记录。
FULL JOIN 或者 FULL OUTER JOIN:全连接（MySQL不支持全连接）
CROSS JOIN：交叉连接，等同于与笛卡尔积。

创建三张测试表，分别是学生表、课程表、考试成绩表。

#MySQL创建方式
#学生表
create table students(
   sid int not null auto_increment primary key,
   sname varchar(20),
   gender varchar(2)
);

#课程表
create table course(
   cid varchar(4) not null primary key,
   cname varchar(20)
);

#学生成绩表
create table score(
   sid int not null,
   cid varchar(4) not null,
   score int ,
   primary key(sid,cid)  /*sid+cid是联合主键*/
);

#Oracle创建方式
#学生表
create table students(
   sid int not null ,
   sname varchar2(20),
   gender varchar2(4),
   constraint pk_students primary key (sid)
);

#课程表
create table course(
   cid varchar2(4) not null,
   cname varchar2(20),
   constraint pk_course primary key (cid)
);

#学生成绩表
create table score(
   sid int not null,
   cid varchar2(4) not null,
   score int ,
   constraint pk_score primary key (sid,cid)
);

初始化数据如下图：

其中成绩表的SID和学生表的SID之间并不构成外键关联关系，也就是说成绩表中允许出现学生表中不存在的学生记录。因此在成绩表中出现了一条非法记录编号为2的学生，课程C002的成绩为89分。

1.等值连接(INNER JOIN)

执行SQL语句如下：

select stu.sid as 学生编号,sname as 姓名 ,s.cid as 课程编号 ,score as 分数
from score s 
inner join students stu on s.sid = stu.sid

#或者
select stu.sid as 学生编号,sname as 姓名 ,s.cid as 课程编号 ,score as 分数
from score s , students stu where s.sid = stu.sid

可以看出通过等值连接可以把所有合法记录全部查询出来。

2.左连接

左连接称为：LEFT JOIN或者LEFT OUTER JOIN。

执行SQL语句如下：

select s.sid as 学生编号,sname as 姓名 ,s.cid as 课程编号 ,score as 分数
from score s 
left join students stu on s.sid = stu.sid

可以看出是以左表（score）为基础查询出所有的记录，尽管其中包含非法的成绩记录。

LEFT JOIN的特殊情况：只查询左连接时不满足条件的记录。

执行SQL语句如下：

select s.sid as 学生编号,sname as 姓名 ,s.cid as 课程编号 ,score as 分数
from score s 
left join students stu on s.sid = stu.sid
where stu.sid is null;

可以看出仅仅查询出左连接时的非法记录。

3.右连接

右连接：称为RIGHT JOIN或者 RIGHT OUTER JOIN。

执行SQL语句如下：

select stu.sid as 学生编号,sname as 姓名 ,s.cid as 课程编号 ,score as 分数
from score s 
right join students stu on s.sid = stu.sid;

可以看出是以右表(students)为基础查询出所有记录，其中不包含非法的成绩记录。

RIGHT JOIN的特殊情况：只查询右连接时不满足条件的记录。执行SQL语句如下：

select s.sid as 学生编号,sname as 姓名 ,s.cid as 课程编号 ,score as 分数
from score s 
right join students stu on s.sid = stu.sid
where s.sid is null;

可以看出没有查询出任何记录。

4.全连接

全连接：也称为FULL JOIN 或者 FULL OUTER JOIN。由于MySQL并不支持全连接，下面的测试代码，感兴趣的同学可以在Oracle上尝试。

执行SQL语句如下：

select stu.sid as 学生编号,sname as 姓名 ,s.cid as 课程编号 ,score as 分数
from score s 
full outer join students stu on s.sid = stu.sid;

select stu.sid as 学生编号,sname as 姓名 ,s.cid as 课程编号 ,score as 分数
from score s 
full outer join students stu on s.sid = stu.sid WHERE s.sid IS NULL OR stu.sid IS NULL;

可以看出查询出左表和右表的全部记录。至于学生编号(SID)是否有值取决于查询时参考左表数据还是右表数据。

5.交叉连接

交叉连接，也称为CROSS JOIN，等同于与笛卡尔积。

执行SQL语句如下：

select s.sid as 学生编号,sname as 姓名 ,s.cid as 课程编号 ,score as 分数
from score s
cross join students stu;

可以看出查询出是左表和右表的一个笛卡尔积。至于学生编号(SID)的值取决于查询时参考左表数据还是右表数据。