但是在关系型数据库中，表与表之间是有联系的，所以在实际应用中，经常使用多表查询。本章节我们将向大家介绍如何使用 MySQL在两个或多个表中查询数据。
表的连接查询主要分为两种情况：有条件和无条件的，无条件的在现实应用中很少用到。其实只需要掌握交叉连接和左连接就可以了。
按照功能大致分为如下三类：
JOIN（也称为交叉连接,内连接,笛卡尔积,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
 | LEFT JOIN（左连接）和RIGHT JOIN（右连接）一定要加条件的。 |
以学生表和成绩表做本案例讲解。
这是详细信息得内容。
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`sex` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三名', '男');
INSERT INTO `student` VALUES ('2', '李四浩', '男');
INSERT INTO `student` VALUES ('3', '王五期', '女');
INSERT INTO `student` VALUES ('5', '三七熊', '女');
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`studentid` int(11) DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '1', '语文', '81');
INSERT INTO `course` VALUES ('2', '1', '数学', '74');
INSERT INTO `course` VALUES ('3', '1', '英语', '56');
INSERT INTO `course` VALUES ('4', '2', '语文', '71');
INSERT INTO `course` VALUES ('5', '2', '数学', '77');
INSERT INTO `course` VALUES ('6', '2', '英语', '66');
INSERT INTO `course` VALUES ('7', '3', '语文', '61');
INSERT INTO `course` VALUES ('8', '3', '数学', '87');
INSERT INTO `course` VALUES ('9', '3', '英语', '86');
INSERT INTO `course` VALUES ('10', '4', '语文', '71');
INSERT INTO `course` VALUES ('11', '4', '数学', '57');
INSERT INTO `course` VALUES ('12', '4', '英语', '76');student学生表原始数据
mysql> SELECT * from student; +----+-----------+------+ | id | name | sex | +----+-----------+------+ | 1 | 张三名 | 男 | | 2 | 李四浩 | 男 | | 3 | 王五期 | 女 | | 5 | 三七熊 | 女 | +----+-----------+------+
course成绩表原始数据
学生分别有语文、数学、英语三门成绩，studentid是student学生表的id
mysql> SELECT * from course; +----+-----------+--------+-------+ | id | studentid | name | score | +----+-----------+--------+-------+ | 1 | 1 | 语文 | 81 | | 2 | 1 | 数学 | 74 | | 3 | 1 | 英语 | 56 | | 4 | 2 | 语文 | 71 | | 5 | 2 | 数学 | 77 | | 6 | 2 | 英语 | 66 | | 7 | 3 | 语文 | 61 | | 8 | 3 | 数学 | 87 | | 9 | 3 | 英语 | 86 | | 10 | 4 | 语文 | 71 | | 11 | 4 | 数学 | 57 | | 12 | 4 | 英语 | 76 | +----+-----------+--------+-------+
无条件交叉连接就是两表的行数相乘。学生表4条记录，成绩表12条记录，则输出48条记录
-- 以下写法等效 -- SELECT * from student,course SELECT * from student cross join course SELECT * from student join course SELECT * from student INNER join course
mysql> SELECT * from student,course; +----+-----------+------+----+-----------+--------+-------+ | id | name | sex | id | studentid | name | score | +----+-----------+------+----+-----------+--------+-------+ | 1 | 张三名 | 男 | 1 | 1 | 语文 | 81 | | 2 | 李四浩 | 男 | 1 | 1 | 语文 | 81 | | 3 | 王五期 | 女 | 1 | 1 | 语文 | 81 | | 5 | 三七熊 | 女 | 1 | 1 | 语文 | 81 | | 1 | 张三名 | 男 | 2 | 1 | 数学 | 74 | | 2 | 李四浩 | 男 | 2 | 1 | 数学 | 74 | | 3 | 王五期 | 女 | 2 | 1 | 数学 | 74 | | 5 | 三七熊 | 女 | 2 | 1 | 数学 | 74 | | 1 | 张三名 | 男 | 3 | 1 | 英语 | 56 | | 2 | 李四浩 | 男 | 3 | 1 | 英语 | 56 | | 3 | 王五期 | 女 | 3 | 1 | 英语 | 56 | | 5 | 三七熊 | 女 | 3 | 1 | 英语 | 56 | | 1 | 张三名 | 男 | 4 | 2 | 语文 | 71 | | 2 | 李四浩 | 男 | 4 | 2 | 语文 | 71 | | 3 | 王五期 | 女 | 4 | 2 | 语文 | 71 | | 5 | 三七熊 | 女 | 4 | 2 | 语文 | 71 | | 1 | 张三名 | 男 | 5 | 2 | 数学 | 77 | | 2 | 李四浩 | 男 | 5 | 2 | 数学 | 77 | | 3 | 王五期 | 女 | 5 | 2 | 数学 | 77 | | 5 | 三七熊 | 女 | 5 | 2 | 数学 | 77 | | 1 | 张三名 | 男 | 6 | 2 | 英语 | 66 | | 2 | 李四浩 | 男 | 6 | 2 | 英语 | 66 | | 3 | 王五期 | 女 | 6 | 2 | 英语 | 66 | | 5 | 三七熊 | 女 | 6 | 2 | 英语 | 66 | | 1 | 张三名 | 男 | 7 | 3 | 语文 | 61 | | 2 | 李四浩 | 男 | 7 | 3 | 语文 | 61 | | 3 | 王五期 | 女 | 7 | 3 | 语文 | 61 | | 5 | 三七熊 | 女 | 7 | 3 | 语文 | 61 | | 1 | 张三名 | 男 | 8 | 3 | 数学 | 87 | | 2 | 李四浩 | 男 | 8 | 3 | 数学 | 87 | | 3 | 王五期 | 女 | 8 | 3 | 数学 | 87 | | 5 | 三七熊 | 女 | 8 | 3 | 数学 | 87 | | 1 | 张三名 | 男 | 9 | 3 | 英语 | 86 | | 2 | 李四浩 | 男 | 9 | 3 | 英语 | 86 | | 3 | 王五期 | 女 | 9 | 3 | 英语 | 86 | | 5 | 三七熊 | 女 | 9 | 3 | 英语 | 86 | | 1 | 张三名 | 男 | 10 | 4 | 语文 | 71 | | 2 | 李四浩 | 男 | 10 | 4 | 语文 | 71 | | 3 | 王五期 | 女 | 10 | 4 | 语文 | 71 | | 5 | 三七熊 | 女 | 10 | 4 | 语文 | 71 | | 1 | 张三名 | 男 | 11 | 4 | 数学 | 57 | | 2 | 李四浩 | 男 | 11 | 4 | 数学 | 57 | | 3 | 王五期 | 女 | 11 | 4 | 数学 | 57 | | 5 | 三七熊 | 女 | 11 | 4 | 数学 | 57 | | 1 | 张三名 | 男 | 12 | 4 | 英语 | 76 | | 2 | 李四浩 | 男 | 12 | 4 | 英语 | 76 | | 3 | 王五期 | 女 | 12 | 4 | 英语 | 76 | | 5 | 三七熊 | 女 | 12 | 4 | 英语 | 76 | +----+-----------+------+----+-----------+--------+-------+
这样输出很多重复数据，这时候可以加上条件
在多表连接可以加上条件where或者on（两表都有数据），只要成绩表对应的学生数据
mysql> SELECT * from student,course where student.id=course.studentid; +----+-----------+------+----+-----------+--------+-------+ | id | name | sex | id | studentid | name | score | +----+-----------+------+----+-----------+--------+-------+ | 1 | 张三名 | 男 | 1 | 1 | 语文 | 81 | | 1 | 张三名 | 男 | 2 | 1 | 数学 | 74 | | 1 | 张三名 | 男 | 3 | 1 | 英语 | 56 | | 2 | 李四浩 | 男 | 4 | 2 | 语文 | 71 | | 2 | 李四浩 | 男 | 5 | 2 | 数学 | 77 | | 2 | 李四浩 | 男 | 6 | 2 | 英语 | 66 | | 3 | 王五期 | 女 | 7 | 3 | 语文 | 61 | | 3 | 王五期 | 女 | 8 | 3 | 数学 | 87 | | 3 | 王五期 | 女 | 9 | 3 | 英语 | 86 | +----+-----------+------+----+-----------+--------+-------+
在上面查询中可以看到没有5号学生三七熊，因为在成绩表没有5号的数据，如果想输出则用左连接
左连接是一定要加条件的。左边的表全部输出在右表没有数据时输出NULL。
mysql> SELECT * from student LEFT JOIN course on student.id=course.studentid; +----+-----------+------+------+-----------+--------+-------+ | id | name | sex | id | studentid | name | score | +----+-----------+------+------+-----------+--------+-------+ | 1 | 张三名 | 男 | 1 | 1 | 语文 | 81 | | 1 | 张三名 | 男 | 2 | 1 | 数学 | 74 | | 1 | 张三名 | 男 | 3 | 1 | 英语 | 56 | | 2 | 李四浩 | 男 | 4 | 2 | 语文 | 71 | | 2 | 李四浩 | 男 | 5 | 2 | 数学 | 77 | | 2 | 李四浩 | 男 | 6 | 2 | 英语 | 66 | | 3 | 王五期 | 女 | 7 | 3 | 语文 | 61 | | 3 | 王五期 | 女 | 8 | 3 | 数学 | 87 | | 3 | 王五期 | 女 | 9 | 3 | 英语 | 86 | | 5 | 三七熊 | 女 | NULL | NULL | NULL | NULL | +----+-----------+------+------+-----------+--------+-------+
相反，如果输出右表数据则用右连接
右连接是一定要加条件的。右边的表全部输出在左表没有数据时输出NULL。
mysql> SELECT * from student RIGHT JOIN course on student.id=course.studentid; +------+-----------+------+----+-----------+--------+-------+ | id | name | sex | id | studentid | name | score | +------+-----------+------+----+-----------+--------+-------+ | 1 | 张三名 | 男 | 1 | 1 | 语文 | 81 | | 1 | 张三名 | 男 | 2 | 1 | 数学 | 74 | | 1 | 张三名 | 男 | 3 | 1 | 英语 | 56 | | 2 | 李四浩 | 男 | 4 | 2 | 语文 | 71 | | 2 | 李四浩 | 男 | 5 | 2 | 数学 | 77 | | 2 | 李四浩 | 男 | 6 | 2 | 英语 | 66 | | 3 | 王五期 | 女 | 7 | 3 | 语文 | 61 | | 3 | 王五期 | 女 | 8 | 3 | 数学 | 87 | | 3 | 王五期 | 女 | 9 | 3 | 英语 | 86 | | NULL | NULL | NULL | 10 | 4 | 语文 | 71 | | NULL | NULL | NULL | 11 | 4 | 数学 | 57 | | NULL | NULL | NULL | 12 | 4 | 英语 | 76 | +------+-----------+------+----+-----------+--------+-------+
左表没有4号学生数据
其实用左链接也能达到右连接的效果
-- 表位置交换，LEFT JOIN和RIGHT JOIN交换达到相同效果 -- mysql> SELECT * from course LEFT JOIN student on student.id=course.studentid; +----+-----------+--------+-------+------+-----------+------+ | id | studentid | name | score | id | name | sex | +----+-----------+--------+-------+------+-----------+------+ | 1 | 1 | 语文 | 81 | 1 | 张三名 | 男 | | 2 | 1 | 数学 | 74 | 1 | 张三名 | 男 | | 3 | 1 | 英语 | 56 | 1 | 张三名 | 男 | | 4 | 2 | 语文 | 71 | 2 | 李四浩 | 男 | | 5 | 2 | 数学 | 77 | 2 | 李四浩 | 男 | | 6 | 2 | 英语 | 66 | 2 | 李四浩 | 男 | | 7 | 3 | 语文 | 61 | 3 | 王五期 | 女 | | 8 | 3 | 数学 | 87 | 3 | 王五期 | 女 | | 9 | 3 | 英语 | 86 | 3 | 王五期 | 女 | | 10 | 4 | 语文 | 71 | NULL | NULL | NULL | | 11 | 4 | 数学 | 57 | NULL | NULL | NULL | | 12 | 4 | 英语 | 76 | NULL | NULL | NULL | +----+-----------+--------+-------+------+-----------+------+
where和on功能一样，只是在个别的情况下不能使用
-- 在使用,逗号交叉连接时不能用on要用where否则报错 -- mysql> SELECT * from student,course on student.id=course.studentid; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on student.id=course.studentid' at line 1 -- 在使用左连接和右连接时不能用where 要用on否则报错 -- mysql> SELECT * from student LEFT JOIN course where student.id=course.studentid; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where student.id=course.studentid' at line 1 mysql> SELECT * from student RIGHT JOIN course where student.id=course.studentid; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where student.id=course.studentid' at line 1
