MySQL学习（四） SQL连接查询详解数据库

更多情况下，我们查询的数据来源于多张表，所有有必要了解一下MySQL中的连接查询。

SQL中将连接查询分成四类：交叉连接，内连接，外连接和自然连接。

数据准备

student表

-- ---------------------------- 
-- Table structure for `student` 
-- ---------------------------- 
DROP TABLE IF EXISTS `student`; 
CREATE TABLE `student` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `name` varchar(10) DEFAULT NULL, 
  `age` tinyint(4) DEFAULT NULL, 
  `classId` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; 
 
-- ---------------------------- 
-- Records of student 
-- ---------------------------- 
INSERT INTO `student` VALUES ('1', 's1', '20', '1'); 
INSERT INTO `student` VALUES ('2', 's2', '22', '1'); 
INSERT INTO `student` VALUES ('3', 's3', '22', '2'); 
INSERT INTO `student` VALUES ('4', 's4', '22', null);

class表

-- ---------------------------- 
-- Table structure for `class` 
-- ---------------------------- 
DROP TABLE IF EXISTS `class`; 
CREATE TABLE `class` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `cname` varchar(10) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; 
 
-- ---------------------------- 
-- Records of class 
-- ---------------------------- 
INSERT INTO `class` VALUES ('1', '一班'); 
INSERT INTO `class` VALUES ('2', '二班'); 
INSERT INTO `class` VALUES ('3', '');

score表

-- ---------------------------- 
-- Table structure for `score` 
-- ---------------------------- 
DROP TABLE IF EXISTS `score`; 
CREATE TABLE `score` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(10) DEFAULT NULL, 
  `score` decimal(4,1) DEFAULT NULL, 
  `studentId` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
-- ---------------------------- 
-- Records of score 
-- ---------------------------- 
INSERT INTO `score` VALUES ('1', '语文', '90.0', '1'); 
INSERT INTO `score` VALUES ('2', '数学', '95.0', '1'); 
INSERT INTO `score` VALUES ('3', '语文', '92.0', '2'); 
INSERT INTO `score` VALUES ('4', '数学', '88.0', '2'); 
INSERT INTO `score` VALUES ('5', '语文', '96.0', '3'); 
INSERT INTO `score` VALUES ('6', '数学', null, '3');

交叉连接

交叉连接(CROSS JOIN)是用左表中的每一行与右表中的每一行进行连接，不能使用ON关键字。所得到的结果将是这两个表中各行数据的所有组合，即这两个表所有数据的笛卡尔积。如果A表有4条记录，B表有3条，则结果有4*3=12条记录。

mysql> SELECT * FROM student CROSS JOIN class; 
+----+------+-----+---------+----+-------+ 
| id | name | age | classId | id | cname | 
+----+------+-----+---------+----+-------+ 
|  1 | s1   |  20 |       1 |  1 | 一班  | 
|  1 | s1   |  20 |       1 |  2 | 二班  | 
|  1 | s1   |  20 |       1 |  3 |       | 
|  2 | s2   |  22 |       1 |  1 | 一班  | 
|  2 | s2   |  22 |       1 |  2 | 二班  | 
|  2 | s2   |  22 |       1 |  3 |       | 
|  3 | s3   |  22 |       2 |  1 | 一班  | 
|  3 | s3   |  22 |       2 |  2 | 二班  | 
|  3 | s3   |  22 |       2 |  3 |       | 
|  4 | s4   |  22 | NULL    |  1 | 一班  | 
|  4 | s4   |  22 | NULL    |  2 | 二班  | 
|  4 | s4   |  22 | NULL    |  3 |       | 
+----+------+-----+---------+----+-------+ 
12 rows in set

如果给交叉连接加上WHERE关键字，此时将返回符合条件的结果集，这时候与内连接的执行结果一样。

mysql> SELECT * FROM student CROSS JOIN class WHERE student.classId = class.id; 
+----+------+-----+---------+----+-------+ 
| id | name | age | classId | id | cname | 
+----+------+-----+---------+----+-------+ 
|  1 | s1   |  20 |       1 |  1 | 一班  | 
|  2 | s2   |  22 |       1 |  1 | 一班  | 
|  3 | s3   |  22 |       2 |  2 | 二班  | 
+----+------+-----+---------+----+-------+ 
3 rows in set

内连接

内连接(INNER JOIN)是用左表中的每一行与右表中的所有记录进行匹配，查询的结果为两个表经过ON条件过滤后的笛卡尔积

mysql> SELECT * FROM student INNER JOIN class ON student.classId = class.id; -- 推荐写法，INNER可写可不写 
+----+------+-----+---------+----+-------+ 
| id | name | age | classId | id | cname | 
+----+------+-----+---------+----+-------+ 
|  1 | s1   |  20 |       1 |  1 | 一班  | 
|  2 | s2   |  22 |       1 |  1 | 一班  | 
|  3 | s3   |  22 |       2 |  2 | 二班  | 
+----+------+-----+---------+----+-------+ 
3 rows in set

等价于

mysql> SELECT * FROM student, class WHERE student.classId = class.id; 
+----+------+-----+---------+----+------+ 
| id | name | age | classId | id | name | 
+----+------+-----+---------+----+------+ 
|  1 | s1   |  20 |       1 |  1 | 一班 | 
|  2 | s2   |  22 |       1 |  1 | 一班 | 
|  3 | s3   |  22 |       2 |  2 | 二班 | 
+----+------+-----+---------+----+------+ 
3 rows in set

可以连接多张表

mysql> SELECT * FROM student 
    -> JOIN class ON student.classId = class.id 
    -> JOIN score ON student.id = score.studentId; 
+----+------+-----+---------+----+-------+----+------+-------+-----------+ 
| id | name | age | classId | id | cname | id | name | score | studentId | 
+----+------+-----+---------+----+-------+----+------+-------+-----------+ 
|  1 | s1   |  20 |       1 |  1 | 一班  |  1 | 语文 | 90    |         1 | 
|  1 | s1   |  20 |       1 |  1 | 一班  |  2 | 数学 | 95    |         1 | 
|  2 | s2   |  22 |       1 |  1 | 一班  |  3 | 语文 | 92    |         2 | 
|  2 | s2   |  22 |       1 |  1 | 一班  |  4 | 数学 | 88    |         2 | 
|  3 | s3   |  22 |       2 |  2 | 二班  |  5 | 语文 | 96    |         3 | 
|  3 | s3   |  22 |       2 |  2 | 二班  |  6 | 数学 | NULL  |         3 | 
+----+------+-----+---------+----+-------+----+------+-------+-----------+ 
6 rows in set

外连接

左外连接

左外连接包含LEFT JOIN左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL)。

mysql> SELECT * FROM student LEFT JOIN class ON student.classId = class.id; -- 或者LEFT OUTER JOIN 
+----+------+-----+---------+------+-------+ 
| id | name | age | classId | id   | cname | 
+----+------+-----+---------+------+-------+ 
|  1 | s1   |  20 |       1 |    1 | 一班  | 
|  2 | s2   |  22 |       1 |    1 | 一班  | 
|  3 | s3   |  22 |       2 |    2 | 二班  | 
|  4 | s4   |  22 | NULL    | NULL | NULL  | 
+----+------+-----+---------+------+-------+ 
4 rows in set

右外连接

右外连接包含RIGHT JOIN左表所有行，如果右表中某行在左表没有匹配，则结果中对应行左表的部分全部为空(NULL)。

mysql> SELECT * FROM student RIGHT JOIN class ON student.classId = class.id; -- 或者RIGHT OUTER JOIN 
+------+------+------+---------+----+-------+ 
| id   | name | age  | classId | id | cname | 
+------+------+------+---------+----+-------+ 
|    1 | s1   |   20 |       1 |  1 | 一班  | 
|    2 | s2   |   22 |       1 |  1 | 一班  | 
|    3 | s3   |   22 |       2 |  2 | 二班  | 
| NULL | NULL | NULL | NULL    |  3 |       | 
+------+------+------+---------+----+-------+ 
4 rows in set

全外连接

如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL)，如果右表中某行在左表没有匹配，则结果中对应行左表的部分全部为空(NULL)。MySQL不支持FULL JOIN，但是我们可以对左连接和右连接的结果做UNION操作来实现

mysql> SELECT * FROM student LEFT JOIN class ON student.classId = class.id 
    -> UNION 
    -> SELECT * FROM student RIGHT JOIN class ON student.classId = class.id; 
+------+------+------+---------+------+-------+ 
| id   | name | age  | classId | id   | cname | 
+------+------+------+---------+------+-------+ 
|    1 | s1   |   20 |       1 |    1 | 一班  | 
|    2 | s2   |   22 |       1 |    1 | 一班  | 
|    3 | s3   |   22 |       2 |    2 | 二班  | 
|    4 | s4   |   22 | NULL    | NULL | NULL  | 
| NULL | NULL | NULL | NULL    |    3 |       | 
+------+------+------+---------+------+-------+ 
5 rows in set

自然连接

自然连接无需指定连接列，SQL会检查两个表中是否有相同名称的列，且相同的列名称只能有一个，自然连接基本不用。

mysql> SELECT * FROM student NATURAL JOIN class; 
+----+------+-----+---------+-------+ 
| id | name | age | classId | cname | 
+----+------+-----+---------+-------+ 
|  1 | s1   |  20 |       1 | 一班  | 
|  2 | s2   |  22 |       1 | 二班  | 
|  3 | s3   |  22 |       2 |       | 
+----+------+-----+---------+-------+ 
3 rows in set

可以看到只有一列id了，因为student与class两张表中相同的id列自动合并了，相当于内连接

SELECT * FROM student INNER JOIN class ON student.id = class.id

如果更改class表的cname字段名称为name会出现什么情况呢？

mysql> ALTER TABLE class CHANGE cname name VARCHAR(10); 
Query OK, 3 rows affected 
Records: 3  Duplicates: 0  Warnings: 0 
 
mysql> SELECT * FROM student NATURAL JOIN class; -- 因为两张表同名字段有两个，所有结果为空 
Empty set

由于现在student与class表有两个同名的字段，所有结果为空

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/4796.html