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表有两个同名的字段,所有结果为空
上一篇: 妈妈,还是你来保管安全点
下一篇: 赶脚是这个冬季最冷的笑话了
推荐阅读
-
iis7 配置php5.4连接sql2008图文教程
-
SQL Server 2008 连接JDBC详细图文教程
-
VS2010/VS2013项目创建 ADO.NET连接mysql/sql server详细步骤
-
Navicat远程连接SQL Server并转换成MySQL步骤详解
-
数据库查询排序使用随机排序结果示例(Oracle/MySQL/MS SQL Server)
-
SQL如何实现MYSQL的递归查询
-
SQL Server中的连接查询详解
-
navicat如何连接mysql?navicat连接mysql详细图文教程
-
【MySQL】MySQL内连接,左连接,右连接查询
-
Oracle 数据库连接查询SQL语句