MySQL内连接

2019独角兽企业重金招聘Python工程师标准>>>

SQL连接

基于 ANSI 标准的 SQL 列出了五种 JOIN 方式: 

1. 内连接(INNER JOIN) 

2. 全外连接(FULL OUTER JOIN) 

3. 左外连接(LEFT OUTER JOIN) 

4. 右外连接(RIGHT OUTER JOIN) 

5. 交叉连接(CROSS JOIN) 

在特定的情况下, 一张表(基本表, 视图, 或连接表)可以和自身进行连接, 成为自连接(self-join)。

建表语句：

CREATE TABLE student(
sid varchar(10) not null,
sname varchar(10) not null,
sage datetime not null,
ssex enum('男','女') not null,
PRIMARY KEY (sid)
);
 
CREATE TABLE teacher(
tid varchar(10) not null,
tname varchar(10) not null,
PRIMARY KEY (tid)
);
 
CREATE TABLE course(
cid varchar(10) not null,
cname varchar(10),
tid varchar(10) COMMENT 'teacher的id，是表的外键，为空说明该课程没有授课老师',
PRIMARY KEY (cid),
CONSTRAINT course_foreign_key FOREIGN KEY (tid) REFERENCES teacher (tid)
);
 
CREATE TABLE student_course(
sid varchar(10) not null COMMENT '学生的id',
cid varchar(10) not null COMMENT '课程的id',
score double default 0 COMMENT '学生该课程的分数',
PRIMARY KEY (sid,cid),
INDEX sid_index (sid),
INDEX cid_index (cid),
CONSTRAINT sid_f_key FOREIGN KEY (sid) REFERENCES student (sid),
CONSTRAINT cid_f_key FOREIGN KEY (cid) REFERENCES course (cid)
);

MySQL 内连接

内连接(inner join)是应用程序中用的普遍的"连接"操作，它一般都是默认连接类型。内连接基于连接谓词将两张表(如 A 和 B)的列组合在一起，产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足连接谓词的组合。当连接谓词被满足，A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行。连接产生的结果集，可以定义为首先对两张表做笛卡尔积(交叉连接)——将 A 中的每一行和 B 中的每一行组合，然后返回满足连接谓词的记录。实际上 SQL 产品会尽可能用其他方式去实现连接，笛卡尔积运算是非常没效率的。

SQL 定义了两种不同语法方式去表示"连接"。首先是"显式连接符号"，它显式地使用关键字 JOIN，其次是"隐式连接符号"，它使用所谓的"隐式连接符号"。隐式连接符号把需要连接的表放到 SELECT 语句的 FROM 部分，并用逗号隔开。这样就构成了一个"交叉连接"，WHERE 语句可能放置一些过滤谓词(过滤条件)。那些过滤谓词在功能上等价于显式连接符号。

内连接"可以进一步被分为: 相等连接，自然连接，和交叉连接。

程序要应该特别注意连接依据的列可能包含 NULL 值，NULL 值不与任何值匹配(甚至和它本身) -- 除非连接条件中显式地使用 IS NULL 或 IS NOT NULL 等谓词。

MySQL 内连接的分类：

相等链接

相等连接 (equi-join，或 equijoin)，是比较连接(θ连接)的一种特例，它的连接谓词只用了相等比较。使用其他比较操作符(如 <  或  > )的不是相等连接。

示例：

select * from course c inner join student_course sc on c.cid = sc.cid;

SQL 提供了一种可选的简短符号去表达相等连接，它使用 USING 关键字。

select * from course c inner join student_course sc using(cid);

USING 结构并不仅仅是语法糖，上面查询的结果和使用显式谓词得到的查询得到的结果是不同的。特别地，在 USING 部分列出的列(column)将以只出现一次，且名称无表名修饰。

自然连接

‍自然连接比相等连接的进一步特例化。两表做自然连接时，两表中的所有名称相同的列都将被比较，这是隐式的。‍自然连接得到的结果表中，两表中名称相同的列只出现一次。

select * from course c natural join student_course sc;

两表中相同的列有cid，所以以cid为连接条件。

交叉连接

‍交叉连接(cross join)，又称笛卡尔连接(cartesian join)，它是所有类型的内连接的基础。把表视为行记录的集合，交叉连接即返回这两个集合的笛卡尔积。‍这其实等价于内连接的链接条件为"永真"，或连接条件不存在。

如果 A 和 B 是两个集合，它们的交叉连接就记为: A × B。

笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。

用于交叉连接的 SQL 代码在 FROM 列出表名，但并不包含任何过滤的连接谓词。

显式的交叉连接实例：

select * from course c cross join student_course sc;

隐式的交叉连接实例：

select * from course,student_course;

交叉连接不会应用任何谓词去过滤结果表中的记录。可以用 WHERE 语句进一步过滤结果集。

===========================================END===========================================

转载于:https://my.oschina.net/xinxingegeya/blog/385220