各连接的由来
1、当业务系统演变复杂时,系统内部数据库的表结构也会随着复杂化,而不在是简单的单表操作。某个系统业务,往往伴随着是多个表之间的连接查询。
2、为了消除多表查询中出现的笛卡尔积效应导致的多冗余数据。
各连接的讲解
为了能帮助大家理解各连接的情况和区别,因此创建了两张表,具体表结构和数据如下:
DROP TABLE IS EXISTS `student`;
DROP TABLE IS EXISTS `course`;
DROP TABLE IS EXISTS `sc`;
-- 学生表
CREATE TABLE student (
`sid` INT NOT NULL AUTO_INCREMENT,
`snum` INT NOT NULL PRIMARY KEY,
`sname` VARCHAR (20) NOT NULL,
`sage` TINYINT,
`sclass` SMALLINT NOT NULL,
UNIQUE KEY `student_num` (`sid`) # 最后不能加',',否则会报错
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
# 使用'='的都放在student()之外。
INSERT INTO `student` (
`snum`,
`sname`,
`sage`,
`sclass`
)
VALUES
(20201101, 'zhangsan', 20, 150),
(20201102, 'lisi', 18, 151),
(20201103, 'wangwu', 19, 151),
(20201104, 'zhaoliu', 18, 150),
(20201105, 'qianqi', 21, 151),
(20201106, 'sunba', 20, 152);
-- 课程表
CREATE TABLE course (
`cid` INT NOT NULL AUTO_INCREMENT,
`cnum` INT NOT NULL PRIMARY KEY,
`cname` VARCHAR (20) NOT NULL,
UNIQUE KEY `course_num` (`cid`) # 最后不能加',',否则会报错
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
# 使用'='的都放在student()之外。
INSERT INTO `course` (
`cnum`,
`cname`
)
VALUES
(101, '数据结构'),
(102, '编译原理'),
(103, '计算机网络'),
(105,'计算机组成原理'),
(106, '数据库');
-- 学生课程中间表
CREATE TABLE `sc` (
`scid` INT NOT NULL UNIQUE AUTO_INCREMENT,
`snum` INT NOT NULL,
`cnum` INT NOT NULL,
`grade` SMALLINT,
PRIMARY KEY (`snum`, `cnum`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
INSERT INTO `sc` (
`snum`,
`cnum`,
`grade`
) VALUES
(20201101, 101, 85),
(20201102, 101, 55),
(20201103, 101, 90),
(20201101, 102, 88),
(20201102, 102, 75),
(20201103, 102, 58),
(20201101, 103, 72),
(20201102, 103, 80),
(20201103, 103, 92);
1. 等值连接
等值连接也称为显示内连接,在进行多表联合查询时通过“=”等号来连接多张表之间相字段对应的值,其产生的结果会出现重复列。意思是,如果对多张表进行等值连接操作,那么前提要求是这多张表之间必须有相同的字段名。比如上述表中,课程表和学生课程中间表均有相同的“cnum”字段,学生表与学生课程中间表均有相同的“snum”字段。
2. 自连接
自连接是一种特殊的连接,它的本质是将一个表与它自身做连接, 若要在一个表中查找具有相同列值的行,则可以使用自连接。使用自连接时需为表指定两个别名,且对所有列的引用均要用别名限定。
3. 自然连接
自然连接是一种特殊的等值连接,在进行多表联合查询时通过“natural join”关键字来连接多张表之间相同字段对应的值,其产生的结果会将重复列只保留一个。如果对多张表进行自然连接操作,那么前提要求是这多张表之间必须有相同的字段名。比如上述表中,课程表和学生课程中间表均有相同的“cnum”字段,学生表与学生课程中间表均有相同的“snum”字段。
4. 左右外连接
左外连接在进行多表联合查询时通过“left join.. on ”关键字来连接多张表之间符合条件的字段,其查询的结果会返回左表的所有数据,以及左右表交集的数据,若右表记录不足,则使用null来表示需要筛选的右表的值。
右外连接在进行多表联合查询时通过“right join.. on ”关键字来连接多张表之间符合条件的字段,其查询的结果会返回右表的所有数据,以及左右表交集的数据,若左表记录不足,则使用null来表示需要筛选的右表的值。
5. 内连接
内外接在进行多表联合查询时通过“inner join.. on ”关键字来连接多张表之间符合条件的字段,其查询的结果左右表匹配的数据。
6. 全连接
全连接在进行多表联合查询时通过full join.. on ”关键字来连接多张表之间符合条件的字段,其查询的结果除了会返回左右表匹配的数据,还会返回两个表的所有行。但是mysql中没有全连接,也可以使用"UNION " 来实现full join 功能,union 连接左右外连接。