Jinyun's Notes

没什么天赋,爱好也不多,但愿坚持做些喜欢的事情

0%

MySQL 联合查询

联合查询语句牵扯到多张表,多表查询有多种语法,多种使用场景,不同的场景需要不同的语法。

数据准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
/*
Navicat Premium Data Transfer

Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50718
Source Host : 127.0.0.1
Source Database : test

Target Server Type : MySQL
Target Server Version : 50718
File Encoding : utf-8

Date: 05/16/2017 08:44:43 AM
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
`title` varchar(50) NOT NULL DEFAULT '' COMMENT '课程标题名称',
`englishName` varchar(50) DEFAULT '' COMMENT '英文名称',
`createdAt` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间戳',
`updatedAt` int(10) unsigned DEFAULT '0' COMMENT '更新时间戳',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '语文', 'Chinese', '0', '0'), ('2', '数学', 'Mathematics', '0', '0'), ('3', '英语', 'English', '0', '0'), ('4', '历史', 'History', '0', '0'), ('5', '政治', 'Politics', '0', '0'), ('6', '地理', 'Geography', '0', '0'), ('7', '物理', 'Physics', '0', '0'), ('8', '化学', 'Chemistry', '0', '0'), ('9', '生物', 'Biology', '0', '0'), ('10', '体育', 'Sports', '0', '0');
COMMIT;

-- ----------------------------
-- Table structure for `lesson`
-- ----------------------------
DROP TABLE IF EXISTS `lesson`;
CREATE TABLE `lesson` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
`courseId` int(10) unsigned DEFAULT '0' COMMENT '所属课程 ID',
`title` varchar(50) NOT NULL DEFAULT '' COMMENT '课时标题名称',
`tags` varchar(255) DEFAULT '' COMMENT '所属标签',
`createdAt` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间戳',
`updatedAt` int(10) unsigned DEFAULT '0' COMMENT '更新时间戳',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of `lesson`
-- ----------------------------
BEGIN;
INSERT INTO `lesson` VALUES ('1', '1', '高中一年级语文', '高一语文', '0', '0'), ('2', '1', '高中二年级语文', '高二语文', '0', '0'), ('3', '1', '高中三年级语文', '高三语文', '0', '0'), ('4', '0', '高中一年级数学', '高一数学', '0', '0'), ('5', '0', '高中二年级数学', '高二数学', '0', '0'), ('6', '0', '高中三年级数学', '高三数学', '0', '0'), ('7', '3', '高中一年级英语', '高一英语', '0', '0'), ('8', '3', '高中二年级英语', '高二英语', '0', '0'), ('9', '3', '高中三年级英语', '高三英语', '0', '0'), ('10', '0', '高中一年级历史', '高一历史', '0', '0'), ('11', '0', '高中二年级历史', '高二历史', '0', '0'), ('12', '0', '高中三年级历史', '高三历史', '0', '0'), ('13', '5', '高中一年级政治', '高一政治', '0', '0'), ('14', '5', '高中二年级政治', '高二政治', '0', '0'), ('15', '5', '高中三年级政治', '高三政治', '0', '0'), ('16', '0', '高中一年级地理', '高一地理', '0', '0'), ('17', '0', '高中二年级地理', '高二地理', '0', '0'), ('18', '0', '高中三年级地理', '高三地理', '0', '0'), ('19', '7', '高中一年级物理', '高一物理', '0', '0'), ('20', '7', '高中二年级物理', '高二物理', '0', '0'), ('21', '7', '高中三年级物理', '高三物理', '0', '0'), ('22', '0', '高中一年级化学', '高一化学', '0', '0'), ('23', '0', '高中二年级化学', '高二化学', '0', '0'), ('24', '0', '高中三年级化学', '高三化学', '0', '0'), ('25', '9', '高中一年级生物', '高一生物', '0', '0'), ('26', '9', '高中二年级生物', '高二生物', '0', '0'), ('27', '9', '高中三年级生物', '高三生物', '0', '0'), ('28', '0', '高中一年级体育', '高一体育', '0', '0'), ('29', '0', '高中二年级体育', '高二体育', '0', '0'), ('30', '0', '高中三年级体育', '高三体育', '0', '0');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

连接查询

INNER JOIN - 内连接

mysql-inner-join.png

1
2
3
4
5
6
7
8
9
10
SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a
INNER JOIN test.`lesson` AS b ON a.`id` = b.`courseId`;

OUTER JOIN - 外连接

MySQL 并不支持 FULL OUTER JOIN 语法。

mysql-outer-join.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a
LEFT JOIN test.`lesson` AS b ON a.`id` = b.`courseId`
UNION
SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a
RIGHT JOIN test.`lesson` AS b ON a.`id` = b.`courseId`;

LEFT JOIN - 左连接

mysql-left-join.png

1
2
3
4
5
6
7
8
9
10
SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a
LEFT JOIN test.`lesson` AS b ON a.`id` = b.`courseId`;

RIGHT JOIN 右连接

mysql-right-join.png

1
2
3
4
5
6
7
8
9
10
SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a
RIGHT JOIN test.`lesson` AS b ON a.`id` = b.`courseId`;

LEFT JOIN EXCLUDING INNER JOIN

mysql-left-excluding-join.png

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a
LEFT JOIN test.`lesson` AS b ON a.`id` = b.`courseId`
WHERE
b.`id` IS NULL;

RIGHT JOIN EXCLUDING INNER JOIN

mysql-right-excluding-join.png

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a
RIGHT JOIN test.`lesson` AS b ON a.`id` = b.`courseId`
WHERE
a.`id` IS NULL;

OUTER JOIN EXCLUDING INNER JOIN

mysql-outer-excluding-join.png

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a
LEFT JOIN test.`lesson` AS b ON a.`id` = b.`courseId`
WHERE
b.`courseId` IS NULL
UNION
SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a
RIGHT JOIN test.`lesson` AS b ON a.`id` = b.`courseId`
WHERE
a.`id` IS NULL;

CROSS JOIN

笛卡尔积, 即 N * M
三种表示方法: CROSS JOIN, JOIN, ,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a
CROSS JOIN test.`lesson` AS b;

SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a
JOIN test.`lesson` AS b;

SELECT
a.`id` AS courseId,
a.`title` AS courseTitle,
a.`englishName`,
b.`id` AS lessonId,
b.`title` AS lessonTitle,
b.`tags`
FROM
test.`course` AS a,
test.`lesson` AS b;
本笔记是笔者在学习和工作中的一些整理,如对您有用,请鼓励我继续写作