mysql多表关系与查询

news/2024/10/23 0:51:11/

一、多表关系

1.多表操作分类

1.1 多对一

关系举例：
多对一：多名学生在一个班级里
一对多主要是靠 “外键” 实现。在 “多” 的表中建立外键，指向 "一"的主键
一对多的关系在实际生产当中使用非常常见。
一对多的核心解决方案是：如何让记录能够正确的匹配到另外表中的数据

1.2 多对多

关系举例：

一个学生可以学习多门课程。一门课程也可以被多名学生学习。
多对多主要是第三张表来维护两张表之间的关系。第三张表主要记录两张表的主键来记录对应关系。
这两个字段分别使用外键来关联两张表的主键

1.3 一对一

一对一的关系，多用于单表拆分，将一张表的基础字段放在一张表中，详情信息放在另外一张表中，以提升操作效率。
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

1.4 区别

多对一：

​ 1.A表的一行数据只能和B表的某一行数据建立关系
​ 2.B表的一行数据可以和A表的多数据建立联系
多对多：
​ 1.A表的一行数据可以和B表的多行数据建立关系
​ 2.B表的一行数据也可以和A表的多行数据建立关系

2.多对一

在前边的 “约束” 中没有写外键约束，就是放在这里在举例。

2.1 外键概念

在一张表中指向外部表 “主键””的字段定义成为 “外键”.

2.2 外键语法

外键必须要通过语法指定才能称之为外键.
外键也可以不指定名称，系统会自动生成

mysql">[constraint 外键名] foreign key(当前表字段名) references 外部表(主键字段)

2.3 外键构成条件

外键字段必须与对应表的主键字段类型一致
外键字段本身要求是一个索引（创建外键会自动生成一个索引）

2.4 建立一（班级表）

mysql">create table class( id int primary key auto_increment, name varchar(50) not null unique 
)charset utf8;

2.5 建立多（学生表）

mysql">create table students( id int primary key auto_increment,name varchar(50) not null,c_id int comment  "指向class表的主键",foreign key(c_id) references class(id)
);

2.6 新增班级数据

mysql">INSERT INTO class VALUES(NULL,"python"),
(NULL,"C"),
(NULL,"C++"),
(NULL,"java"),
(NULL,"go");

内容如下：

mysql">mysql> select * from class;
+----+--------+
| id | name   |
+----+--------+
|  2 | C      |
|  3 | C++    |
|  5 | go     |
|  4 | java   |
|  1 | python |
+----+--------+

2.7 新增学生表数据

mysql">INSERT INTO students VALUES(NULL,"张三",1),
(NULL,"李四",2),
(NULL,"王五",1),
(NULL,"王强",2),
(NULL,"孙红",4);

结果如下：

mysql">mysql> select * from students;
+----+--------+------+
| id | name   | c_id |
+----+--------+------+
|  1 | 张三   |    1 |
|  2 | 李四   |    2 |
|  3 | 王五   |    1 |
|  4 | 王强   |    2 |
|  5 | 孙红   |    4 |
+----+--------+------+

3.多对多

3.1 创建表1

创建武林高手表

mysql">CREATE TABLE persion(id INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(20)
)

插入数据

mysql">INSERT INTO persion VALUES(NULL,"张无忌"),
(NULL,"周芷若"),
(NULL,"谢逊"),
(NULL,"崆峒五老"),
(NULL,"黄衫女");

内容如下：

mysql">mysql> select * from persion;
+----+--------------+
| id | NAME         |
+----+--------------+
|  1 | 张无忌       |
|  2 | 周芷若       |
|  3 | 谢逊         |
|  4 | 崆峒五老     |
|  5 | 黄衫女       |
+----+--------------+

3.2 创建表2

创建武功秘籍表

mysql">CREATE TABLE manual(id INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(30)
)

插入数据

mysql">INSERT INTO manual VALUES(NULL,"九阴真经"),
(NULL,"七伤拳"),
(NULL,"乾坤大挪移");

查询数据

mysql">mysql> select * from manual;
+----+-----------------+
| id | NAME            |
+----+-----------------+
|  1 | 九阴真经          |
|  2 | 七伤拳           |
|  3 | 乾坤大挪移        |
+----+-----------------+

3.2 创建关系表

两个字段分别关联到persion表和manual表的两个主键

mysql">CREATE TABLE PersionToManual(id INT AUTO_INCREMENT PRIMARY KEY,persion_id INT,manual_id INT,FOREIGN KEY(persion_id) REFERENCES persion(id),FOREIGN KEY(manual_id) REFERENCES manual(id)
)

插入数据

mysql">INSERT INTO PersionToManual VALUES(NULL,1,2),
(NULL,1,3),
(NULL,2,1),
(NULL,3,2),
(NULL,4,2),
(NULL,5,1)

查询结果如下：

mysql">mysql> SELECT * FROM PersionToManual;
+----+------------+-----------+
| id | persion_id | manual_id |
+----+------------+-----------+
|  1 |          1 |         2 |
|  2 |          1 |         3 |
|  3 |          2 |         1 |
|  4 |          3 |         2 |
|  5 |          4 |         2 |
|  6 |          5 |         1 |
+----+------------+-----------+

4.一对一

一对一关系，多用于表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
一对一的实现主要是靠 在任意一方加入外键，关联另外一方的主键，并且设置外键唯一（UNIQUE）

二、多表查询

1.内连接

内连接的连接条件必须是 外键id = 主表主键id
内连接查询的是两张表交集的部分。
这里的查询实例使用的是上边的class表和students表，这里在students表中插入了一条没有外键id的一行数据，为了能够明显的看出效果

INSERT INTO students VALUES(NULL,"高圆圆",NULL)

1.1 隐士内连接

语法：

mysql">SELECT 字段名 FROM 表1,表2 WHERE 表1.字段 = 表2.字段

。如果不加条件，直接查询连长表，就会出现笛卡尔乘积

mysql">SELECT * FROM students,class

使用隐士内连接进行查询。

mysql">SELECT * FROM students,class WHERE students.c_id = class.id

查询结果如下：结果中没有出现笛卡尔乘积

mysql">mysql> SELECT * FROM students,class WHERE students.c_id = class.id;
+----+--------+------+----+--------+
| id | name   | c_id | id | name   |
+----+--------+------+----+--------+
|  1 | 张三   |    1 |  1 | python |
|  2 | 李四   |    2 |  2 | C      |
|  3 | 王五   |    1 |  1 | python |
|  4 | 王强   |    2 |  2 | C      |
|  5 | 孙红   |    4 |  4 | java   |
+----+--------+------+----+--------+

1.2 显示内连接

语法: 其中inner可以省略

mysql">SELECT 字段名 FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段

实例：这里为两张表起了别名。

mysql">SELECT  s.name, c.name FROM students s JOIN class c ON s.c_id = c.id

查询结果如下：

mysql">+--------+--------+
| name   | name   |
+--------+--------+
| 张三   | python |
| 李四   | C      |
| 王五   | python |
| 王强   | C      |
| 孙红   | java   |
+--------+--------+

2.外连接：

2.1 左外连接：

查询左表所有数据，以及两张表交集部分.这个交集部分是针对与笛卡尔积而言的
语法：

mysql">select * from 表1 left join 表2 on 条件;

实例：

mysql">SELECT * FROM students s LEFT JOIN class c ON s.c_id = c.id

结果如下：查询出了左表所有的数据以及交集部分

mysql">+----+-----------+------+------+--------+
| id | name      | c_id | id   | name   |
+----+-----------+------+------+--------+
|  1 | 张三      |    1 |    1 | python |
|  2 | 李四      |    2 |    2 | C      |
|  3 | 王五      |    1 |    1 | python |
|  4 | 王强      |    2 |    2 | C      |
|  5 | 孙红      |    4 |    4 | java   |
|  6 | 高圆圆    | NULL | NULL | NULL   |
+----+-----------+------+------+--------+

2.2 右外连接：

查询右表所有数据，以及两张表交集部分，这个交集部分是针对与笛卡尔积而言的

mysql">select * from 表1 rigth join 表2 on 条件;

实例：查询出了右表所有数据，以及两张表交集的数据

mysql">mysql> SELECT * FROM students s RIGHT JOIN class c ON s.c_id = c.id;
+------+--------+------+----+--------+
| id   | name   | c_id | id | name   |
+------+--------+------+----+--------+
|    4 | 王强   |    2 |  2 | C      |
|    2 | 李四   |    2 |  2 | C      |
| NULL | NULL   | NULL |  3 | C++    |
| NULL | NULL   | NULL |  5 | go     |
|    5 | 孙红   |    4 |  4 | java   |
|    3 | 王五   |    1 |  1 | python |
|    1 | 张三   |    1 |  1 | python |
+------+--------+------+----+--------+

3.子查询

3.1 分类

子查询类型含义
标量子查询子查询返回结果为单个值
列子查询子查询返回结果为一列
行子查询子查询返回结果为一行
表子查询子查询返回结果为多行多列

根据位置又可以分为：

在where之后出现、from后边出现、select之后出现的子查询

3.2 标量子查询

常用的操作符号：=,<> >,>= <,<=
这里还是使用class和students表
例子： 查询学习C语言的学生有谁

SELECT * FROM students WHERE c_id = (SELECT id FROM class WHERE NAME = "C")

3.2 列子查询

常用的操作符有

操作符号含义
IN在指定的集合范围之内，多选一
NOT IN不在指定的集合范围之内
ANY子查询返回列表中，有任意一个满足即可
SOME与any等同，使用some的地方都可以使用any
ALL子查询返回列表的值都必须满足

例子：

查询学习C或者C++的学生有谁

SELECT * FROM students WHERE c_id IN(SELECT id FROM class WHERE NAME = "c" OR NAME ="c++")

3.2 行子查询

这里没有合适数据，只写一下语法

select * from 表 where (字段1，字段2) = （子查询回来的两个字段结果）

http://www.ppmy.cn/news/1541197.html

相关文章

前端拦截302重定向

前端拦截302重定向

背景: 根据业务场景需要拦截302做后续的逻辑处理 尝试一: : axios拦截 、、、、、async created() {// 获取302请求返回的location后手动修改video的src路径let targetSrc;try {await axios.get(this.video).then((res) > {const { headers, status } res;const { locat…
阅读更多...
开源医疗管理的未来：参与码良诊所管理系统，助力智能医疗

开源医疗管理的未来：参与码良诊所管理系统，助力智能医疗

开源医疗管理的未来&#xff1a;参与码良诊所管理系统&#xff0c;助力智能医疗 引言 在过去的六个多月里&#xff0c;我们公司 码良互联网科技有限公司 专注于开发一个全面、智能的诊所管理系统&#xff0c;旨在帮助中小型医疗机构提升运营效率、优化患者管理流程、以及降低…
阅读更多...
CentOS 7 安装gcc编译环境

CentOS 7 安装gcc编译环境

有时需要使用源码安装某个应用程序&#xff0c;有时还需要对源码进行一定程度的修改和定制才能满足业务需求&#xff0c;有时需要在linux环境下开发某个特定功能的c程序&#xff0c;此时都需要用到gcc编译环境&#xff0c;此时就需要安装gcc编译环境。 在 CentOS 7 上安装 C 编…
阅读更多...
Spring Ai 对接智谱清言结合vue(清测成功)

Spring Ai 对接智谱清言结合vue(清测成功)

智谱文档&#xff1a;智谱AI开放平台 注意:springboot版本要在3.0以上&#xff0c;pom.xml要配置下载的源。 pml文件如下 建议使用下科学上网~~~ <?xml version"1.0" encoding"UTF-8"?> <project xmlns"http://maven.apache.org/POM/4.…
阅读更多...
linux 查看CPU信息 核心数 逻辑核心数

linux 查看CPU信息 核心数 逻辑核心数

cat /proc/cpuinfo Linux操作系统的CPU信息被保存在/proc/cpuinfo文件中&#xff0c; processor: 这是逻辑CPU的编号&#xff0c;从0开始。 physical id: 如果你有多个物理CPU&#xff0c;每个物理CPU都会有一个唯一的ID。 core id: 每个核心的唯一ID。有了HT技术后&#xf…
阅读更多...
svn安装完成，但在cmd窗口运行是报错svn不是内部或外部命令

svn安装完成，但在cmd窗口运行是报错svn不是内部或外部命令

已经安装了svn&#xff0c;但是在cmd中输入svn命令的时候提示svn不是内部或外部命令是因为没有安装svn client。 解决办法&#xff1a; windows安装svn的时候默认是不安装 svn comand line这个东西的&#xff0c;重新安装svn客户端&#xff0c;将“command line client tools”…
阅读更多...
Lua字符串

Lua字符串

软考鸭微信小程序 过软考,来软考鸭! 提供软考免费软考讲解视频、题库、软考试题、软考模考、软考查分、软考咨询等服务 Lua作为一种轻量级、高效的脚本语言&#xff0c;在字符串处理方面提供了丰富的功能和灵活的操作方式。字符串在Lua中是一系列的字节&#xff0c;可以包含任意…
阅读更多...
OpenLayers：构建现代Web地图应用

OpenLayers：构建现代Web地图应用

&#x1f493; 博客主页&#xff1a;瑕疵的CSDN主页 &#x1f4dd; Gitee主页&#xff1a;瑕疵的gitee主页 ⏩ 文章专栏&#xff1a;《热点资讯》 OpenLayers&#xff1a;构建现代Web地图应用 文章目录 OpenLayers&#xff1a;构建现代Web地图应用1. 简介2. 为什么选择 OpenLa…
阅读更多...
最新文章

Copyright @ 2022~2023