mysql多表关系与查询

embedded/2024/10/20 19:59:19/

一、多表关系

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/embedded/129073.html

相关文章

使用React Router实现前端的权限访问控制

使用React Router实现前端的权限访问控制

前段时间学习了React Router&#xff0c;发现没有Vue里面的路由功能强大&#xff0c;没有直接提供路由中间件&#xff0c;不能像Vue里面一样在路由配置上设置任意的额外属性&#xff0c;但是可以通过一些技巧来实现这些功能。 1、配置菜单 后台管理系统一般都会在左侧显示菜单…
阅读更多...
SQL第19课——使用存储过程

SQL第19课——使用存储过程

介绍什么是存储过程&#xff1f;为什么要使用存储过程&#xff1f;如何使用存储过程&#xff1f;创建和使用存储过程的基本语法&#xff1f; 19.1 存储过程 到目前为止&#xff0c;使用的大多数SQL语句都是针对一个或多个表的单条语句。 对于一些复杂的操作需要多条语句才能…
阅读更多...
超GPT3.5性能，无限长文本，超强RAG三件套，MiniCPM3-4B模型分享

超GPT3.5性能，无限长文本，超强RAG三件套，MiniCPM3-4B模型分享

MiniCPM3-4B是由面壁智能与清华大学自然语言处理实验室合作开发的一款高性能端侧AI模型&#xff0c;它是MiniCPM系列的第三代产品&#xff0c;具有4亿参数量。 MiniCPM3-4B模型在性能上超过了Phi-3.5-mini-Instruct和GPT-3.5-Turbo-0125&#xff0c;并且与多款70亿至90亿参数的…
阅读更多...
爬虫逆向学习(十二)：一个案例入门补环境

爬虫逆向学习(十二)：一个案例入门补环境

此分享只用于学习用途&#xff0c;不作商业用途&#xff0c;若有冒犯&#xff0c;请联系处理 反爬前置信息 站点&#xff1a;aHR0cDovLzEyMC4yMTEuMTExLjIwNjo4MDkwL3hqendkdC94anp3ZHQvcGFnZXMvaW5mby9wb2xpY3k 接口&#xff1a;/xjzwdt/rest/xmzInfoDeliveryRest/getInfoDe…
阅读更多...
线程池原理（一）

线程池原理（一）

一、常用线程池体系结构图如下&#xff1a; 由上边的体系图可以知道&#xff0c;要想了解线程池 ThreadPoolExecutor 的实现原理&#xff0c;则需要先 了解下 Executor、ExecutorService、AbstractExecutorService 的实现&#xff0c;下面就分别看下 这3个类的实现 二、Executo…
阅读更多...
6.计算机网络_UDP

6.计算机网络_UDP

UDP的主要特点&#xff1a; 无连接&#xff0c;发送数据之前不需要建立连接。不保证可靠交付。面向报文。应用层给UDP报文后&#xff0c;UDP并不会抽象为一个一个的字节&#xff0c;而是整个报文一起发送。没有拥塞控制。网络拥堵时&#xff0c;发送端并不会降低发送速率。可以…
阅读更多...
MongoDB如何查找数据以及条件运算符使用的详细说明

MongoDB如何查找数据以及条件运算符使用的详细说明

以下是关于MongoDB如何查找数据以及条件运算符使用的详细说明&#xff1a; 查找数据的基本方法 在MongoDB中&#xff0c;使用db.collection.find()方法来查找集合中的数据。如果不添加任何条件&#xff0c;直接使用db.collection.find()会返回集合中的所有文档。例如&#xf…
阅读更多...
【STM32 HAL库】MPU6050姿态解算 卡尔曼滤波

【STM32 HAL库】MPU6050姿态解算 卡尔曼滤波

【STM32 HAL库】MPU6050姿态解算 卡尔曼滤波 前言MPU6050寄存器代码详解mpu6050.cmpu6050.h 使用说明 前言 本篇文章基于卡尔曼滤波的原理详解与公式推导&#xff0c;来详细的解释下如何使用卡尔曼滤波来解算MPU6050的姿态 参考资料&#xff1a;Github_mpu6050 MPU6050寄存器…
阅读更多...
最新文章

Copyright @ 2022~2023