数据库系统原理与应用教程（048）—— MySQL 查询（十）：自连接查询
自连接查询
·
数据库系统原理与应用教程（048）—— MySQL 查询（十）：自连接查询
自连接就是一张表和自身进行连接查询。进行自连接查询时把一张表看成两张表，使用别名进行区分。一般的连接查询使用两张不同的表，指定连接条件，然后进行查询。
自连接查询格式和普通的连接查询书写格式完全相同，只不过我们需要把一张表想象成两张表使用。自连接查询是自己和自己连接，分别给一张表取两个不同的别名，然后附上连接条件。
一、自连接的语法格式
自连接查询的语法格式如下：
FROM 表1 alias1 [INNER|left outer|right outer] JOIN 表1 alias2
ON alias1.列1 = alias2.列2
-- 或
FROM 表1 alias1, 表2 alias1
WHERE alias1.列1 = alias2.列2
二、准备数据
创建如下表格并输入数据：
/*
create table stu(
s_id int primary key,
s_name char(20),
birth datetime,
phone char(20),
president int,
foreign key(president) references stu(s_id)
);
insert into stu values(1101,'张涛','2001-1-23','13637321547',null);
insert into stu values(1102,'刘明','2000-8-15','13637322455',null);
insert into stu values(1103,'刘刚','2000-12-5','13637321666',1103);
insert into stu values(1104,'张丽丽','2002-8-6','13637333366',1103);
insert into stu values(1201,'李静伊','1999-8-9','13637377766',null);
insert into stu values(1202,'王云飞','2001-11-12','13637355566',1202);
insert into stu values(1203,'张家友','1998-10-16','13637335566',1202);
insert into stu values(1204,'王光辉','2003-7-12','13637345666',1202);
insert into stu values(1205,'张鹏飞','2001-6-9','13637336566',1202);
insert into stu values(1301,'刘青云','2000-4-17','13637222666',null);
insert into stu values(1302,'赵佳燕','2002-8-11','13637444666',null);
insert into stu values(1303,'王军伟','1999-1-22','13637555666',1303);
*/
-- 建立外键，president 列的值参照自己的 s_id 列取值。
mysql> create table stu(
-> s_id int primary key,
-> s_name char(20),
-> birth datetime,
-> phone char(20),
-> president int,
-> foreign key(president) references stu(s_id)
-> );
Query OK, 0 rows affected (0.08 sec)
-- 插入数据失败，外键的取值：1103 在表中的 s_id 列中不存在
mysql> insert into stu values(1101,'张涛','2001-1-23','13637321547',1103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`president`) REFERENCES `stu` (`s_i
mysql> insert into stu values(1101,'张涛','2001-1-23','13637321547',null);
Query OK, 1 row affected (0.01 sec)
....
mysql> update stu set president=1103 where s_id=1101;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update stu set president=1103 where s_id=1102;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update stu set president=1202 where s_id=1201;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update stu set president=1303 where s_id=1301;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update stu set president=1303 where s_id=1302;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stu;
+------+-----------+---------------------+-------------+-----------+
| s_id | s_name | birth | phone | president |
+------+-----------+---------------------+-------------+-----------+
| 1101 | 张涛 | 2001-01-23 00:00:00 | 13637321547 | 1103 |
| 1102 | 刘明 | 2000-08-15 00:00:00 | 13637322455 | 1103 |
| 1103 | 刘刚 | 2000-12-05 00:00:00 | 13637321666 | 1103 |
| 1104 | 张丽丽 | 2002-08-06 00:00:00 | 13637333366 | 1103 |
| 1201 | 李静伊 | 1999-08-09 00:00:00 | 13637377766 | 1202 |
| 1202 | 王云飞 | 2001-11-12 00:00:00 | 13637355566 | 1202 |
| 1203 | 张家友 | 1998-10-16 00:00:00 | 13637335566 | 1202 |
| 1204 | 王光辉 | 2003-07-12 00:00:00 | 13637345666 | 1202 |
| 1205 | 张鹏飞 | 2001-06-09 00:00:00 | 13637336566 | 1202 |
| 1301 | 刘青云 | 2000-04-17 00:00:00 | 13637222666 | 1303 |
| 1302 | 赵佳燕 | 2002-08-11 00:00:00 | 13637444666 | 1303 |
| 1303 | 王军伟 | 1999-01-22 00:00:00 | 13637555666 | 1303 |
+------+-----------+---------------------+-------------+-----------+
12 rows in set (0.00 sec)
三、自连接查询
根据 stu 表查询每个学生的信息及班长（president）姓名。
/*
select s.*,p.s_name president_name
from stu s join stu p on s.president = p.s_id;
*/
mysql> select s.*,p.s_name president_name
-> from stu s join stu p on s.president = p.s_id;
+------+-----------+---------------------+-------------+-----------+----------------+
| s_id | s_name | birth | phone | president | president_name |
+------+-----------+---------------------+-------------+-----------+----------------+
| 1101 | 张涛 | 2001-01-23 00:00:00 | 13637321547 | 1103 | 刘刚 |
| 1102 | 刘明 | 2000-08-15 00:00:00 | 13637322455 | 1103 | 刘刚 |
| 1103 | 刘刚 | 2000-12-05 00:00:00 | 13637321666 | 1103 | 刘刚 |
| 1104 | 张丽丽 | 2002-08-06 00:00:00 | 13637333366 | 1103 | 刘刚 |
| 1201 | 李静伊 | 1999-08-09 00:00:00 | 13637377766 | 1202 | 王云飞 |
| 1202 | 王云飞 | 2001-11-12 00:00:00 | 13637355566 | 1202 | 王云飞 |
| 1203 | 张家友 | 1998-10-16 00:00:00 | 13637335566 | 1202 | 王云飞 |
| 1204 | 王光辉 | 2003-07-12 00:00:00 | 13637345666 | 1202 | 王云飞 |
| 1205 | 张鹏飞 | 2001-06-09 00:00:00 | 13637336566 | 1202 | 王云飞 |
| 1301 | 刘青云 | 2000-04-17 00:00:00 | 13637222666 | 1303 | 王军伟 |
| 1302 | 赵佳燕 | 2002-08-11 00:00:00 | 13637444666 | 1303 | 王军伟 |
| 1303 | 王军伟 | 1999-01-22 00:00:00 | 13637555666 | 1303 | 王军伟 |
+------+-----------+---------------------+-------------+-----------+----------------+
12 rows in set (0.00 sec)
四、使用视图理解自连接
上面的查询可以使用视图来理解，生成一个视图 president，SQL语句如下：
mysql> create view president as select * from stu;
Query OK, 0 rows affected (0.03 sec)
使用 stu 表和视图 president 建立连接：
/*
select s.*,p.s_name president_name
from stu s join president p on s.president = p.s_id;
*/
mysql> select s.*,p.s_name president_name
-> from stu s join president p on s.president = p.s_id;
+------+-----------+---------------------+-------------+-----------+----------------+
| s_id | s_name | birth | phone | president | president_name |
+------+-----------+---------------------+-------------+-----------+----------------+
| 1101 | 张涛 | 2001-01-23 00:00:00 | 13637321547 | 1103 | 刘刚 |
| 1102 | 刘明 | 2000-08-15 00:00:00 | 13637322455 | 1103 | 刘刚 |
| 1103 | 刘刚 | 2000-12-05 00:00:00 | 13637321666 | 1103 | 刘刚 |
| 1104 | 张丽丽 | 2002-08-06 00:00:00 | 13637333366 | 1103 | 刘刚 |
| 1201 | 李静伊 | 1999-08-09 00:00:00 | 13637377766 | 1202 | 王云飞 |
| 1202 | 王云飞 | 2001-11-12 00:00:00 | 13637355566 | 1202 | 王云飞 |
| 1203 | 张家友 | 1998-10-16 00:00:00 | 13637335566 | 1202 | 王云飞 |
| 1204 | 王光辉 | 2003-07-12 00:00:00 | 13637345666 | 1202 | 王云飞 |
| 1205 | 张鹏飞 | 2001-06-09 00:00:00 | 13637336566 | 1202 | 王云飞 |
| 1301 | 刘青云 | 2000-04-17 00:00:00 | 13637222666 | 1303 | 王军伟 |
| 1302 | 赵佳燕 | 2002-08-11 00:00:00 | 13637444666 | 1303 | 王军伟 |
| 1303 | 王军伟 | 1999-01-22 00:00:00 | 13637555666 | 1303 | 王军伟 |
+------+-----------+---------------------+-------------+-----------+----------------+
12 rows in set (0.05 sec)
五、自连接查询举例
有一个 area 表，表结构和数据如下：
/*
create table area(
area_id int primary key,
area_name char(50) not null default '',
parent_id int not null default 0
);
insert into area values(1,'河南省',0),(2,'湖北省',0),(3,'河北省',0),
(101,'郑州市',1),(102,'新乡市',1),(10101,'金水区',101),
(10102,'中牟县',101),(10103,'巩义市',101),(10104,'新郑市',101),
(10201,'红旗区',102),(10202,'牧野区',102),(10203,'辉县市',102),
(10204,'原阳县',102),(10205,'修武县',102),(10206,'获嘉县',102),
(201,'武汉市',2),(202,'宜昌市',2),(20101,'汉阳区',201),(20102,'武昌区',201),
(20103,'青山区',201),(20104,'洪山区',201),(20201,'当阳市',202),(20202,'枝江市',202);
*/
mysql> select * from area;
+---------+-----------+-----------+
| area_id | area_name | parent_id |
+---------+-----------+-----------+
| 1 | 河南省 | 0 |
| 2 | 湖北省 | 0 |
| 3 | 河北省 | 0 |
| 101 | 郑州市 | 1 |
| 102 | 新乡市 | 1 |
| 201 | 武汉市 | 2 |
| 202 | 宜昌市 | 2 |
| 10101 | 金水区 | 101 |
| 10102 | 中牟县 | 101 |
| 10103 | 巩义市 | 101 |
| 10104 | 新郑市 | 101 |
| 10201 | 红旗区 | 102 |
| 10202 | 牧野区 | 102 |
| 10203 | 辉县市 | 102 |
| 10204 | 原阳县 | 102 |
| 10205 | 修武县 | 102 |
| 10206 | 获嘉县 | 102 |
| 20101 | 汉阳区 | 201 |
| 20102 | 武昌区 | 201 |
| 20103 | 青山区 | 201 |
| 20104 | 洪山区 | 201 |
| 20201 | 当阳市 | 202 |
| 20202 | 枝江市 | 202 |
+---------+-----------+-----------+
23 rows in set (0.03 sec)
（1）根据 area 表查询某个地区所属的地区
/*
select area.*,superior_area.area_name as superior_area
from area left join area superior_area
on area.parent_id=superior_area.area_id;
*/
mysql> select area.*,superior_area.area_name as superior_area
-> from area left join area superior_area
-> on area.parent_id=superior_area.area_id;
+---------+-----------+-----------+---------------+
| area_id | area_name | parent_id | superior_area |
+---------+-----------+-----------+---------------+
| 1 | 河南省 | 0 | NULL |
| 2 | 湖北省 | 0 | NULL |
| 3 | 河北省 | 0 | NULL |
| 101 | 郑州市 | 1 | 河南省 |
| 102 | 新乡市 | 1 | 河南省 |
| 201 | 武汉市 | 2 | 湖北省 |
| 202 | 宜昌市 | 2 | 湖北省 |
| 10101 | 金水区 | 101 | 郑州市 |
| 10102 | 中牟县 | 101 | 郑州市 |
| 10103 | 巩义市 | 101 | 郑州市 |
| 10104 | 新郑市 | 101 | 郑州市 |
| 10201 | 红旗区 | 102 | 新乡市 |
| 10202 | 牧野区 | 102 | 新乡市 |
| 10203 | 辉县市 | 102 | 新乡市 |
| 10204 | 原阳县 | 102 | 新乡市 |
| 10205 | 修武县 | 102 | 新乡市 |
| 10206 | 获嘉县 | 102 | 新乡市 |
| 20101 | 汉阳区 | 201 | 武汉市 |
| 20102 | 武昌区 | 201 | 武汉市 |
| 20103 | 青山区 | 201 | 武汉市 |
| 20104 | 洪山区 | 201 | 武汉市 |
| 20201 | 当阳市 | 202 | 宜昌市 |
| 20202 | 枝江市 | 202 | 宜昌市 |
+---------+-----------+-----------+---------------+
23 rows in set (0.11 sec)
（2）根据 area 表查询某个地区包含地区
/*
select area.area_id, area.area_name,
subordinate_area.area_id as subordinate_area_id,
subordinate_area.area_name as subordinate_area_name
from area subordinate_area right join area
on subordinate_area.parent_id=area.area_id;
*/
mysql> select area.area_id, area.area_name,
-> subordinate_area.area_id as subordinate_area_id,
-> subordinate_area.area_name as subordinate_area_name
-> from area subordinate_area right join area
-> on subordinate_area.parent_id=area.area_id;
+---------+-----------+---------------------+-----------------------+
| area_id | area_name | subordinate_area_id | subordinate_area_name |
+---------+-----------+---------------------+-----------------------+
| 1 | 河南省 | 101 | 郑州市 |
| 1 | 河南省 | 102 | 新乡市 |
| 2 | 湖北省 | 201 | 武汉市 |
| 2 | 湖北省 | 202 | 宜昌市 |
| 101 | 郑州市 | 10101 | 金水区 |
| 101 | 郑州市 | 10102 | 中牟县 |
| 101 | 郑州市 | 10103 | 巩义市 |
| 101 | 郑州市 | 10104 | 新郑市 |
| 102 | 新乡市 | 10201 | 红旗区 |
| 102 | 新乡市 | 10202 | 牧野区 |
| 102 | 新乡市 | 10203 | 辉县市 |
| 102 | 新乡市 | 10204 | 原阳县 |
| 102 | 新乡市 | 10205 | 修武县 |
| 102 | 新乡市 | 10206 | 获嘉县 |
| 201 | 武汉市 | 20101 | 汉阳区 |
| 201 | 武汉市 | 20102 | 武昌区 |
| 201 | 武汉市 | 20103 | 青山区 |
| 201 | 武汉市 | 20104 | 洪山区 |
| 202 | 宜昌市 | 20201 | 当阳市 |
| 202 | 宜昌市 | 20202 | 枝江市 |
| 3 | 河北省 | NULL | NULL |
| 10101 | 金水区 | NULL | NULL |
| 10102 | 中牟县 | NULL | NULL |
| 10103 | 巩义市 | NULL | NULL |
| 10104 | 新郑市 | NULL | NULL |
| 10201 | 红旗区 | NULL | NULL |
| 10202 | 牧野区 | NULL | NULL |
| 10203 | 辉县市 | NULL | NULL |
| 10204 | 原阳县 | NULL | NULL |
| 10205 | 修武县 | NULL | NULL |
| 10206 | 获嘉县 | NULL | NULL |
| 20101 | 汉阳区 | NULL | NULL |
| 20102 | 武昌区 | NULL | NULL |
| 20103 | 青山区 | NULL | NULL |
| 20104 | 洪山区 | NULL | NULL |
| 20201 | 当阳市 | NULL | NULL |
| 20202 | 枝江市 | NULL | NULL |
+---------+-----------+---------------------+-----------------------+
37 rows in set (0.00 sec)
（3）对上面的例子按 area_id 分组显示，使用 group_concat 函数集中显示一个地区包含的所有地区
/*
select area.area_id, area.area_name,
group_concat(subordinate_area.area_name) subordinate_area_name
from area subordinate_area right join area
on subordinate_area.parent_id=area.area_id
group by area.area_id;
*/
mysql> select area.area_id, area.area_name,
-> group_concat(subordinate_area.area_name) subordinate_area_name
-> from area subordinate_area right join area
-> on subordinate_area.parent_id=area.area_id
-> group by area.area_id;
+---------+-----------+-------------------------------------------------------------+
| area_id | area_name | subordinate_area_name |
+---------+-----------+-------------------------------------------------------------+
| 1 | 河南省 | 郑州市,新乡市 |
| 2 | 湖北省 | 武汉市,宜昌市 |
| 3 | 河北省 | NULL |
| 101 | 郑州市 | 金水区,中牟县,巩义市,新郑市 |
| 102 | 新乡市 | 牧野区,辉县市,原阳县,修武县,红旗区,获嘉县 |
| 201 | 武汉市 | 汉阳区,武昌区,青山区,洪山区 |
| 202 | 宜昌市 | 当阳市,枝江市 |
| 10101 | 金水区 | NULL |
| 10102 | 中牟县 | NULL |
| 10103 | 巩义市 | NULL |
| 10104 | 新郑市 | NULL |
| 10201 | 红旗区 | NULL |
| 10202 | 牧野区 | NULL |
| 10203 | 辉县市 | NULL |
| 10204 | 原阳县 | NULL |
| 10205 | 修武县 | NULL |
| 10206 | 获嘉县 | NULL |
| 20101 | 汉阳区 | NULL |
| 20102 | 武昌区 | NULL |
| 20103 | 青山区 | NULL |
| 20104 | 洪山区 | NULL |
| 20201 | 当阳市 | NULL |
| 20202 | 枝江市 | NULL |
+---------+-----------+-------------------------------------------------------------+
23 rows in set (0.11 sec)
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
1
0- 0
已为社区贡献4条内容
所有评论(0)