1 一对多、多对多、自关联多对多、自关联一对多场景描述

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 = (
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处理关系表的文章，比对一下两种方法的优劣势
                    1 一对多、多对多、自关联多对多、自关联一对多场景描述之前在做网页开发的时候一直用Sqlalchemy来操作数据库，当我用到自关联多对多和自关联一对多的时候，sqlalchemy的配置会有一些辅助的参数，配置起来很麻烦，灵机一动我就想了一下，为什么不能直接写sql呢！！！虽然sql语句写起来不是很方便，但是sql才是各种ORM框架的基本，话不多说，开搞1.1 概念本节内容是自己对于数据库表直接关系的一些个人理解，非官方语言描述，都是大白话一对多关系：生活中有很多一对多关系的实例，比如一个教师
  表中同名列被选择时必须添加表名前缀进行修饰 否则无法确定这一列是属于哪个表
  A表中的某列数据和B表中一列或多列的关系是非等值关系,大于,小于,不等于,等条件都属于不等连接的范畴
  数据都来自一张表,所以在from字句中需要对表添加别名,添加表别名后才能合法化的引用表中的列名.
  本质就是将一张表虚拟成了两张表
  即是选择出满足等连接条件及其以外的行
  (+)修饰符号用法:放置在选出结果只包含等连接的列后,则另一列的结果就是等值行+非等值行
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8
    INSERT INTO teacher(`id`, `name`) VALUES 
				
开发工具与关键技术：Visual Studio 2015、SQL
作者： 饶芝华 ；年级：2017；撰写时间：2019年03月 19日
常见的业务中我们都会发现到订单就是一个非常显眼的“一对多”典例。
结构：一个大的订单可以有多个“小订单”（订单详情），每个“小订单”都对应有多个商品。
首先看下表结构：
订单表：（SYS_Order）
订单ID  (OrderID)  int
订单名称  （Ord...
多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。
1. 一个案例引发的多表连接
1.1 案例说明
从多个表中获取数据：
#案例：查询员工的姓名及其部门名称
SELECT last_n
是在年提出的一种关系数据库语言。
由于语言接近英语的语句结构,方便简洁、使用灵活、功能强人,倍受用户及计算机工业
界的欢迎,被众多计算机公司和数据库厂商所采用,经各公司的不断修改、扩充和完善,语
言最终发展成为关系数据库的标准语言。
的第一个标准是年月由美国国家标准化组织公布的
数据库语言
年国际标准化组织也通过了这一标准。以后通过对
的不断修改和完善,于年第二次公布了标准
年又公布了标准
。最新的标准是
《信息技术——数据库语言》发布。我国也相继
公布了数据库语言的国家标准。
成为国际标准以后,其影响远远超出了薮据库领域。例如在
软件工程、人工智
能、分布式等领域,人们不仅把作为检索数据的语言规范,而且也把作为检索图形、
图象、声音、文字等信息类型的语言规范。目前,世界上大型的著名数据库管理系统均支持
等。在未来相当长的时间里,仍将是数据库领
域以至信息领域中数据处理的主流语言之
由于不同的产品,大都按自己产品的特点对语言进行了扩充,很难完全符合
标准。目前在
市场上已将的符合夲作为衡量产品质量的重要指标,并研制成专门的
测试软件,如
入门级和过渡级的符合率均达到,并且部分支持
标准。同时还兼容
的部分语言特性。本章主要
介绍系统所支持的语言
语言的特点
语言符合结构化査询语言标准,是标准的扩充。它集数据定乂、数据査
询、薮据操纵和数据控制于一体,是一种统一的、综合的关系数据库语言。它功能强大,使用简
单方便、容易为用户掌握
语言具有如下特点:
功能一体化
的功能一体化表现在以下两个方面
支持多媒体数据类型,用户在建表时可直接使用。系统在处理常规数据与
多媒体数据时达到了四个一体化:一体化定义、一体化存储、一体化检索、一体化处理,最大限
度地提高了数据库管理系统处理多媒体的能力和速度;
语言集数据库的定义、査询、更新、控制、维护、恢复、安全等一系列操作于
体,每一项操作都只需一种操作符表示,格式规范,风格一致,简单方便,很容易为用户所掌
两种用户接口使用统一语法结构的语言
语言既是自含式语言,又是嵌入式语言。作为自含式语言,它能独立运行于联机交
互方式。作为嵌入式语言,
浯句能够嵌入到和语言程序中,将高级语言也称主
语言灵活的表达能力、强大的计算功能与
语言的数据处理功能相结合,完成各种复杂
的事务处理。而在这两种不同的使用方式中,
语言的语法结构是一致的,从而为用户使
第1章结构化查询语言简介
用提供了极大的方使性和灵活性。
高度非过程化
语言是·种非过程化语言。用户只需指出“做什么”,而不需指出“怎么做”,对数
据存取路径的选择以及
语句功能的实现均由系统自动完成,与用户编制的应用程序与
具体的机器及关系
的实现细节无关,从而方便了用户,提高了应用程序的开发效率,也
增强了数据独立性和应用系统的叮移植性。
面向集合的操作方式
语言采用了集合操作方式。不仅查询结果可以是元组的集合,而且一次插入、删除、
修改操作的对象也可以是元组的集合,相对于面向记录的数据库语言一次只能操作一条记录来
语言的使用简化了用户的处理,提高了应用程序的运行效率
语言简洁,方便易学
语言功能强大,格式规范,表达简洁,接近英语的语法结构,容易为用户所掌握。
保留字与标识符
标识符的语法规则兼容标准
,标识符分为正规标识符和定界标识符两大类。
正规标识符以字母、、、或汉字开头,后面可以跟随字母、数字、、、或者汉字,正
规标识符的最大长度是个英文字符或个汉字。正规标识符不能是保留字
正规标识符的例子:,
定界标识符的标识符体用双引号括起来时,标识符体可以包含任意字符,特别地,其中使用
连续两个双引号转义为一个双引号
定界标识符的例子:
保留字的清单参见附录
语言的功能及语句
语言是一种介于关系代数与关系演算之间的语言,其功能主要包括数据定义、查询
操纵和控制四个方面,通过各种不同的语句米实现。按照所实现的功能,
为以下几种
数据库、登录、用户、模式、基表、视图、索引、序列、全文索引、存储过程和触发器
的定义和删除语句,登录、基表、视图、仝文索引的修改语句,对象的更名语句;
査询(含全文检索)、插入、删除、修改语句;
数据库安全语句。包括创建角色语句、删除角色语句,授权语句、回收权限语句,修改
登录口令语句,审计设置语句、取消审计设置语句等。
在嵌入方式中,为了协调
语言与主语言不同的数据处理方式
了游标的概念。因此在嵌入方式下,除了数据查询语句一次查询一条记录外,还有几种与游标
有关的语句:
游标的定义、打廾、关闭、拨动语句
游标定位方式的数据修改与删除语句。
为了有效维护数据库的完整性和一致性,支持
的并发控制机制
了事务的回滚(
)与提交(
)语句。同时允许选择实施事务级读一致
性,它保证同一事务内的可重复读,为此提供用户多种手动上锁语句,和设置事务隔离级别
第1章结构化查询语言简介
所支持的数据类型
数据类型是可表示值的集。值的逻辑表示是字值。值的物理表示依赖于实现。系统具
的绝大部分数据类型,以及部分
的数据类型。
常规数据类型
字符数据类型
语法:长度
数据类型指定定长字符串。在基表中,定义
类型的列时,可以指
定一个不超过的正整数作为字符长度,例如
如果未指定长度,缺省为。
确保存储在该列的所有值都具有这一长度。
数据类型的最大长度由数据库页面大
小决定,字符类型最大长度和页面大小的对应关系请见下表支持按字节存放字符
数据库页面大
数据类型指定变长字符串,用法类似
数据类型,可以指定一
个不超过的正整数作为字符长度,例如:
。如果未指定长度,缺省为
在系统中,
数据类型的实际最大长度由数据库页面大小决定,具体最
大长度算法如表
的区别在于前者长度不足时,系统自动填充空
格,而后者只占用实际的字节空间。
数据库页面大
实际最大长度
注:这个限制长度只针对建表的情况,在定义变量的时候,可以不受这个限制长度的限
数值数据类型
				
二 动态SQL和多对一，一对多10、多对一处理测试环境搭建按照查询嵌套处理(子查询)按照结果嵌套处理(联表查询)11、一对多按照结果嵌套处理按照查询嵌套处理小结12、动态 SQLIFchoose (when, otherwise)trim (where,set)SQL片段foreach
10、多对一处理
子查询，联表查询
多个学生，对应一个老师
对于学生这边而言，关联（association）多个学生，关联一个老师【多对一】
对于老师而言，集合（Collection），一个老师，有很多学生【一对多】
				
在 SQL Server 中建立多对多关系需要使用中间表来实现。以下是一个示例：
假设有两个表：学生(Student)和课程(Course)，其中一个学生可以选修多门课程，一门课程也可以被多个学生选修。这就是一个典型的多对多关系。
首先，我们需要创建两个表：
CREATE TABLE Student
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50)
CREATE TABLE Course
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50)
然后，我们需要创建一个中间表来存储学生和课程之间的关系。这个中间表应该包含两个外键分别指向学生表和课程表。
CREATE TABLE StudentCourse
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
在中间表中，主键由两个列 StudentID 和 CourseID 组成，这是因为同一个学生可以选修多门课程，同一门课程也可以被多个学生选修。同时，我们在中间表中创建了两个外键，分别指向学生表和课程表，以保证数据的完整性。
现在我们就可以通过向中间表中插入数据来建立多对多关系了。例如，如果学生1选修了课程1和课程2，我们可以这样插入数据：
INSERT INTO StudentCourse (StudentID, CourseID)
VALUES (1, 1), (1, 2)
这样，我们就建立了一个学生和两门课程之间的多对多关系。
                    CSDN-Ada助手: 
                    恭喜您的文章入围 每日分享小能手「2023-05-15」榜单, 排名「9」, 一定要再接再厉哦, 创作更多高质量好文, 争取拿到更好成绩, 全部的排名请看: https://bbs.csdn.net/topics/615305760
更多创作活动请看:
新星计划2023: https://marketing.csdn.net/p/1738cda78d47b2ebb920916aab7c3584?utm_source=csdn_ai_ada_redpacket
	新星计划2023: https://marketing.csdn.net/p/1738cda78d47b2ebb920916aab7c3584?utm_source=csdn_ai_ada_redpacket
	上传ChatGPT/计算机论文等资源，瓜分￥5000元现金: https://blog.csdn.net/VIP_Assistant/article/details/130196121?utm_source=csdn_ai_ada_redpacket
	新人首创任务挑战赛: https://marketing.csdn.net/p/90a06697f3eae83aabea1e150f5be8a5?utm_source=csdn_ai_ada_redpacket
	Microsoft Edge功能测评！: https://activity.csdn.net/creatActivity?id=10403?utm_source=csdn_ai_ada_redpacket
	生物识别技术能否成为应对安全挑战的绝佳选择？: https://activity.csdn.net/creatActivity?id=10411?utm_source=csdn_ai_ada_redpacket
	应届生如何提高职场竞争力: https://activity.csdn.net/creatActivity?id=10409?utm_source=csdn_ai_ada_redpacket
	讯飞星火大模型将超越chatgpt？: https://activity.csdn.net/creatActivity?id=10407?utm_source=csdn_ai_ada_redpacket
	职场新人备忘录: https://activity.csdn.net/creatActivity?id=10405?utm_source=csdn_ai_ada_redpacket
	VR vs AR：哪种技术更有潜力改变未来？: https://activity.csdn.net/creatActivity?id=10399?utm_source=csdn_ai_ada_redpacket
	“裸奔”时代下该如何保护网络隐私: https://activity.csdn.net/creatActivity?id=10401?utm_source=csdn_ai_ada_redpacket
	蓝桥杯备赛指南分享: https://activity.csdn.net/creatActivity?id=10317?utm_source=csdn_ai_ada_redpacket
	有哪些工具软件是一旦用了就离不开的？: https://activity.csdn.net/creatActivity?id=10397?utm_source=csdn_ai_ada_redpacket
	量子计算：下一个大风口，还是一个热炒概念？: https://activity.csdn.net/creatActivity?id=10395?utm_source=csdn_ai_ada_redpacket