MySQL无限级目录的构建与父目录查询

数据库 专栏收录该内容
4 篇文章 0 订阅

1.问题

当一个数据库中只有一张数据表的时候,是否有可能需要用到连接查询?设学生选课数据库中,使用SQL语句完成查询课程的先修课,列出课程名称以及它的先修课的名称。
当一张表连接自身的连接称为自连接 。其应用场景主要是在构建无限多级目录。比如线上课程的多级目录,商品类别的无限分类,以及本题所探讨的先修课程的问题。
以下就以先修课程为例进行阐述(这里只考虑每门课程只有一个直接先修课程)

2.数据表的创建及数据录入

(1) 创建表专业课程表course

#创建表专业课程表course
create table course
(
	id int primary key auto_increment, #课程id,自增长
	name varchar(20) not null , #课程名
	parent_id int  #先修课程名
);

(2)插入若干课程数据

#插入记录
insert into course values(null,'C语言程序设计',0),
(null,'HTML网页设计基础',0),
(null,'数据库应用基础',1),
(null,'数据结构',1),
(null,'Java程序设计',1),
(null,'Jsp应用程序设计',5),
(null,'SSM框架技术',6),
(null,'数据库高级应用',3),
(null,'软件测试技术',5),
(null,'JavaScript程序设计',2)

(3)查询course数据表

#查询数据表
select * from course;

查询结果如下图所示:
在这里插入图片描述

从查询结果可以看出,“C语言程序设计”,“HTML网页设计基础”两门课程没有先修课。其它课程的先修课可以通过parent_id属性找到,比如“数据库应用基础”的先修课程是“C语言程序设计”,而数据库高级应用的先修课是“数据库应用基础”。
这里看明白了,就可以继续往下看哈

3.查找每门课程的直接先修课程名称

从表设计可以看出,课程的先修课名parent_id所指示的值对应用name的值,这里介绍两种方法。

方法1:使用自连接,查找直接先修课名称
将course作自连接时,连接的条件是course(c1)的parent_id和course(c2)的id值相同。由于id为1,2的课程没有先修课,为了显示出所有课程,这里需要用到left join。

#自连接
select c1.id,c1.name,c1.parent_id,c2.name
from course c1 left join course c2  #对同一张表使用自连接时必须要指定表的别名,以区分为不同的表对象
	on c1.parent_id = c2.id

方法2:子查询用于相关计算,查找直接先修课名称
子查询相关计算的方法比较好理解,当外层查询获得一个parent_id是传递到子查询求出先修课程对应的课程名。实现代码如下:

#子查询作为相关计算
select id,name,parent_id,
       (select name from course 
         where id = c.parent_id) as parent_name
from course c; 

两种方法运行的结果相同,结果如下图
在这里插入图片描述

4.查找出指定课程的所有先修课程名称

如何找出某一门课程的所有先修课(也就是回溯到最先修课程)呢?比如“数据库高级应用”的先修课就是“数据库应用基础”—>“C语言程序设计”。
要在这一个select语句中指出所有的一对多目录层次的所有祖先。需要在select用到mysql中的用户变量。
ps: mysql中的变量也分成若干类别,这里仅介绍用户变量

4.1了解mysql中的用户变量

mysql的用户变量只作用在当前连接的当前会话中。用@标识。比如@x,表示用户变量x, 在mysql中用户变量不需要定义,直接可以使用,其赋值方式可用使用set或select查询。
比如:

#mysql定义变量本地变量
#set赋值,可以使用=,:=两种形式赋值
set @x := 10;    #定义变量var
select @x, @x*2 as double_x; #输出 10, 20
#select赋值,只能使用:=
select @y := 10, @y*2 as double_y; #输出 10, 20

自已在mysql中运行看效果哈,这里不截图了。

4.2找出指定某课程的ID为8的前修课程id

select @id,(SELECT @id := parent_id 
            FROM course 
            WHERE id = @id) AS parent_id
from (select @id := 8) tmp,course
where @id <> 0;

在这里插入图片描述
从这个查询结果可以看到,我们找出了id为8的课程的所有先修课程的id,即3,1。
看明白这个查询没?

接下来我们解析下这个查询。
第1步:分析查询的数据源:

select *
from (select @id := 8) tmp,course

定义用户变量@id,其值为8,将查询结果作为临时表tmp与course表作笛卡儿积。结果如下图所示。
在这里插入图片描述
第2步:分析parent_id列
在这里插入图片描述
有没有觉得眼熟?Bingo,这里就是一个子查询用于相关计算,不同于我们平常写的相关子查询的是,这里每读取一行记录都会更新用户变量@id。通过不断迭代@id的值,可以找出@id为8的所有课程的先修祖先。
当找到的先修课程的parent_id值为0时,后面的记录就都为0了。
如果要把每个先修课程名称都显示出来,可不可以直接在上面查询的select的列表中添加一个name列呢?
可以尝试下哦?如果不行应该怎么做?这个问题大家可以回复在评论区,答对了送你一朵大红花。
以下我介绍一种方法

4.3找出指定某课程的ID为8的前修课程的课程名称

这里只需要将上面查询的结果集作一个派生表来使用,再与course表进行连接,就可以找出所有课程名称。

select id,name
	from (
		select @id as _id, (SELECT @id := parent_id 
					FROM course 
					WHERE id = _id) AS parent_id
		from (select @id := 8 ) vars,course) as tt
	join course c on tt._id = c.id

ps: 当然也可以用子查询哦,可以自己试试
查询结果如下:
在这里插入图片描述
如果要将这些先修课拼成一个字符串。就可以这样做:

select group_concat(name order by id separator ' / ') as 先修课程
from (select id,name
	from (
		select @id as _id, (SELECT @id := parent_id 
					FROM course 
					WHERE id = _id) AS parent_id
		from (select @id := 8 ) vars,course) as tt
	join course c on tt._id = c.id
	) as t

结果如下:
在这里插入图片描述
若要求出每门课程的所有先修课。用一个select就难以实现了,实际中可以将查询逻辑封装到存储过程中实现。

  • 2
    点赞
  • 0
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值