SQL实现⼀对多、多对多建表与查询
1 ⼀对多、多对多、⾃关联多对多、⾃关联⼀对多场景描述
之前在做⽹页开发的时候⼀直⽤Sqlalchemy来操作数据库，当我⽤到⾃关联多对多和⾃关联⼀对多的时候，sqlalchemy的配置会有⼀些辅助的参数，配置起来很⿇烦，灵机⼀动我就想了⼀下，为什么不能直接写sql呢！！！虽然sql语句写起来不是很⽅便，但是sql才是各种ORM框架的基本，话不多说，开搞
1.1 概念
本节内容是⾃⼰对于数据库表直接关系的⼀些个⼈理解，⾮官⽅语⾔描述，都是⼤⽩话
⼀对多关系：
⽣活中有很多⼀对多关系的实例，⽐如⼀个教师内有n个上课的学⽣，教师是⼀⽅，学⽣是多⽅
⼯作中每个职位的多名员⼯，职位是⼀⽅，同属这个职位的员⼯是多⽅
项⽬开发中可能会遇到⼀些权限的划分，权限规则role 和同⼀个role下⾯的user也是⼀个⼀对多的关系
多对多关系：
学⽣选课系统，所选的课程与选课的学⽣就是⼀个多对多关系，多对多关系不能通过主键与外键的关联实现，
必须建⼀个三⽅表，三⽅表中存放两个关系表的主键，将⼀个多对多的关系转换成两个⼀对多的关系。
在第三张表中通过外键关联另外两张表的主键
⾃关联⼀对多关系：
新闻⽹站、社交⽹站的评论系统就是⼀个⾃关联的⼀对多关系，⼀条评论可以有n条⼦评论，
但是⼀条⼦评论只能有⼀条⽗评论
⾃关联多对多关系：
典型的⾃关联多对多是⽤户关注表，⽐如微博、等社交软件，⽤户表之间会存在⾃关联多对多关系，
⼀个⽤户可以有n个粉丝，同样⼀个⽤户也可以被N个⽤户关注
1.2 创建表格& 插⼊数据
⼀对多关系：
# 1：role表 M：user表
# 新建roles表
create table roles(
id int primary key auto_increment,
name varchar(10) unique not null
)charset=utf8;
# 插⼊⼀些测试数据
insert into roles (`name`) values ("admin"),("other");
# 新建users表
CREATE TABLE `test`.`users` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`uname` varchar(255) NULL,
`role_id` int(0) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `role_id` FOREIGN KEY (`role_id`) REFERENCES `test`.`roles` (`id`)
);
# 插⼊⼀些测试数据
insert into users (`uname`,`r_id`) values ("张三",1),("⾥斯",2),("王刚",2),
("⾦鹏展翅",2),("⼤鸟飞天",2),("花花公⼦",2),("犯上作乱",2),("天兵天将",2),("⾮你莫属",2)
多对多关系：
# ⽤户表、兴趣爱好表、user_habbit_table
# 新建兴趣爱好表
create table habbits (
id int primary key auto_increment,
hname varchar(30)
)charset=utf8;
# 插⼊⼀些测试数据
insert into habbits (hname)
values ("打篮球"),("打台球"),("踢⾜球"),("乒乓球"),("橄榄球"),("⽻⽑球"),("⽹球")
# 新建⽤户与爱好关联表
CREATE TABLE `test`.`union_table` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`user_id` int(0) NULL,
`h_id` int(0) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `test`.`users` (`id`),
CONSTRAINT `habbit_id` FOREIGN KEY (`h_id`) REFERENCES `test`.`habbits` (`id`)
);
# 插⼊测试数据
insert into union_table (user_id,h_id) values (1,1),(1,2),(1,5),(2,4),(2,3),(2,7),(3,1),(3,2),(3,3),(3,6)⾃关联⼀对多关系：
# 创建评论表
create table comments (
id int primary key auto_increment,
content varchar(200),
parent_id int,
ctime TIMESTAMP default now()
) charset =utf8;
# 插⼊测试数据
insert into comments (content,parent_id) values ("今天天⽓真好",NULL),
("我觉得⽂章写的不错",NULL),
("为什么今天库⾥投篮不准了",NULL),
("明天上学的路上我要买⼀张报纸",NULL),
("你是⼀个乖宝宝",NULL)
# 添加⼦评论
insert into comments (content,parent_id) values ("天龙⼋部好看",1),
("乔丹的篮球打的很棒",2),
("明天是周末，可以出去玩",3),
("想想明天的⽣活也会很好",4),
("今天吃了三个包⼦",5)
insert into comments (content,parent_id) values ("潘⾦莲怒打西门庆",1),
("吕布死后，关⽻见到谁都是匹夫",2),
("当你慢慢学会设计数据路的时候",3),
("天⽓虽然很好但是我也不想出去打球",4),
("明明就是⼀个⼤骗⼦⾮要说⾃⼰是好⼈",5)
⾃关联多对多关系：
# ⾃关联⼀对多可以在⼀张表中表现出关系，但是⾃关联多对多需要借助⼀张follows表才能表现结构
# 新建⼀张follows表
CREATE TABLE `test`.`Untitled` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`follower_id` int(11) NULL DEFAULT NULL,
`followed_id` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `follower_id`(`follower_id`) USING BTREE,
INDEX `followed_id`(`followed_id`) USING BTREE,
CONSTRAINT `followed_id` FOREIGN KEY (`followed_id`) REFERENCES `test`.`users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `follower_id` FOREIGN KEY (`follower_id`) REFERENCES `test`.`users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT )
ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
# 插⼊⼀些测试数据
insert into follows (follower_id,followed_id)
values (1,2),(1,3),(1,4),
(2,5),(2,7),(2,1),
(3,6),(3,1),(3,2),
(4,1),(4,2),(4,6)
1.3 查询数据
⼀对多查询
# 查询某⼀种⾓⾊的所有⽤户
select * from users
where r_id = (
sql中union多表合并select id from roles where name = "other"
)
多对多查询
# 查询某个⼈的所有爱好
select * from habbits
where id in (
select h_id from union_table where user_id = (
select id from users where uname like "张三"))
# 查询某⼀种爱好有哪些⽤户喜欢
select * from users
where id in (
select user_id from union_table where h_id = (
select id from habbits where hname like "打篮球"
)
)
⾃关联⼀对多查询
# 查询⼀级评论
select * from comments
where parent_id is null
# 查询⼀个⽗级评论的所有⼦评论
select * from comments
where parent_id = 1
⾃关联多对多查询
# 查询⼀个⽤户的所有粉丝
select u.id,u.uname from follows as f
inner join users as u on u.id = f.follower_id
where f.followed_id = (
select id from users where uname = "张三"
)
1.4 总结
本⽂⽤的数据库是mysql
后续会写⼀个详细的sqlalchemy处理关系表的⽂章，⽐对⼀下两种⽅法的优劣势
发表评论