首页 > 数据库 >MySQL之多表关系篇与多表查询篇

MySQL之多表关系篇与多表查询篇

时间：2024-09-30 22:48:08浏览次数：3  
标签：多表 id score student MySQL 之多表 学生 连接 SELECT

学习前思路拆解：

1.理解数据为什么要拆表存储

2.明白多表有几种数据关系

3.掌握表关系限制的语法并实现

4.掌握多表查询语法和练习

数据库多表关系

1.为什么要拆表存储数据？

因为将数据全部存储在一个表中，容易出现数据冗余。

例子：学生表和学生成绩表

不拆表存储：

拆表存储：

                           

       学生表和学生成绩表不拆分，表内出现重复学号，人名等信息，显得臃肿。而将学生表和学生成绩表拆分存储。学生表可以包含学生的基本信息，如姓名、年龄和班级，而成绩表则专注于记录各科目的成绩。

仅是通过将数据分散到多个表中，就可以减少数据冗余，优化查询速度，并提升数据的维护性。

2.多表有几种数据关系

多（两及两以上）表数据关系总共可以分为三种：

  1. 一对一（1:1）：一个表中的一条记录仅对应另一个表中的一条记录。例如，每个学生只对应一个学生档案。

  2. 一对多（1:N）：一个表中的一条记录可以对应另一个表中的多条记录。例如，一个班级可以有多个学生。

  3. 多对多（M:N）：两个表中的多条记录可以互相对应。这通常通过中间表实现，例如学生和课程之间的关系，一个学生可以选修多门课程，而一门课程也可以有多个学生。（双向查看）。

例子：

       

3.表关系限制的语法并实现

3.1 一对一（1:1）

特点：
每条记录在一个表中与另一表的唯一一条记录对应。
冗余解决：
一对一并不能解决数据冗余问题
存在意义：
有助于将不同类型的信息分开管理，例如将基本信息与详细信息分开，使数据更具可读性。
关系维护：
只需在两个表中维护相应的外键关系，但外键唯一。

两种方案：1.子表外键于主键融合，两表共用同个主键。2.给子表外键添加唯一约束。

 例子代码：

一对一：                                                    

 

3.2 一对多（1:N）

特点：
一条记录在一个表中可以在另一个表中对应多条记录。
即：主表对应多条子表数据，子表对应主表至多一条数据。
冗余解决：
一对多可以解决数据冗余问题。
存在意义：
适用于多个对象归属于同一类别的情况，如一个班级下有多个学生，便于组织和管理数据。
关系维护：
需要确保一个表的主键在另一个表中作为外键。（外键不唯一）

例子代码：

一对多

3.3 多对多（M:N）

特点：
两个表中的多条记录可以相互对应，通常通过中间表来实现。
冗余解决：
通过中间表，将多对多的关系转化为两次一对多的关系，减少数据冗余。
存在意义：
适用于复杂的关系场景，例如学生可以选修多门课程，而一门课程也可以有多个学生参与。
关系维护：
中间表包含两个外键，主表数据之间间接关联。

例子代码：

多对多：

4.多表查询语法和练习

数据查询语法分类：1.单表查询 2.多表查询

在本文章中主要介绍多表查询，单表查询不做过多讨论。

多表查询的关键是理解如何通过不同的连接和合并方式来获取和处理数据。

按照多表查询的结果可以分为垂直合并与水平合并。

4.1 垂直合并

垂直合并使用的是：联合语法，即 UNION 和 UNION ALL 

UNION :合并记录，同时去掉重复数据
UNION ALL :合并记录，但不去掉重复数据（都不要求主外键）

例子：通过搜索旅游，得到的数据有视频，图片，文章等，数据分别可视为来自视频数据库，图片数据库，以及网站数据库。

代码实战：

CREATE TABLE a (
    aname VARCHAR(100),
    score INT
);

CREATE TABLE b (
    bname VARCHAR(100),
    bcore INT
);
INSERT INTO a (aname, score) VALUES
('小明', 88),
('小红', 77);
INSERT INTO b (bname, bcore) VALUES
('小明', 88),
('小蓝', 55),
('小鹿', 66);

表数据结果：

a表：                                            b表：

 

使用 UNION
UNION 用于合并两个结果集，并去除重复记录。
SELECT aname, score FROM a
UNION
SELECT bname, bcore FROM b;

查询结果为：

结果分析：可以看到小明只出现了一次，因为UNION去除了重复记录。

使用 UNION ALL
UNION ALL 用于合并两个结果集，并保留所有记录，包括重复记录。
SELECT aname, score FROM a
UNION ALL
SELECT bname, bcore FROM b;

结果分析：小明在结果中出现了两次，因为 UNION ALL 保留所有记录，包括重复的。

使用细节：

1.重复数据：一行中所有列值都相同则为重复行。
2.合并的多个结果集之间的列数和类型要一一对应。

4.2 水平合并

水平合并可分为：内连接，外连接以及交叉连接   （要求主外键）

水平合并的例子：

          

水品合并后

4.2.1 内连接

基本语法:
SELECT 列 FROM 表1 as 别名 INNER JOIN 表2 别名 ON 表1别名.主键 = 表2别名.外键;

SELECT 列 FROM 表1 as 别名,表2 别名 WHERE 表1别名.主键 = 表2别名.外键;

注意：

       使用表来代替是因为不同的表存在相同的列名导致理解上出现错误；因为连接本质上是笛卡尔积，即将所有的行都拼接一边，所以为了避免出现错误的数据连接所以在连接查询必须添加主外键。

例子：

/*
   学生表（students）
   学生ID（student_id）
   姓名（name）*/

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100)
);

/*
成绩表（grades）
学生ID（student_id）
成绩（score）*/

CREATE TABLE grades (
    student_id INT,
    score INT
);

--  插入数据到学生表
INSERT INTO students (student_id, name) VALUES
(1, '小明'),
(2, '小红'),
(3, '小蓝');

--  插入数据到成绩表
INSERT INTO grades (student_id, score) VALUES
(1, 88),
(2, 77),
(3, 90),
(4, 85);  -- 注意：这里的学生ID 4 没有对应学生

/*内连接查询
我们要查询每位学生及其对应的成绩：*/

SELECT s.name, g.score
FROM students AS s
INNER JOIN grades AS g ON s.student_id = g.student_id;

结果图：

注意：学生ID 为 4 的记录没有被返回，因为内连接只返回匹配的记录。


4.2.2 外连接

外连接分为左外连接和右外连接。

左外连接（LEFT JOIN）：返回左表的所有记录，以及右表中匹配的记录。

基本语法：
SELECT * FROM 表a AS a LEFT [OUTER] JOIN 表b AS b ON 表a.主键 = 表b.外键;

右外连接（RIGHT JOIN）：返回右表的所有记录，以及左表中匹配的记录。

基本语法：
SELECT * FROM 表a AS a RIGHT [OUTER] JOIN 表b AS b ON 表a.主键 = 表b.外键;

注意：

       外连接可以通过左和右指定一个逻辑主表，逻辑主表数据一定会查询到；但内连接只满足匹配条件的行数，要求两个表必须存在且主外键值相等才会被返回；[OUTER]可以被省略；如果有逻辑主表，就将逻辑主表放在第一位，后面全为左外连接。

例子：

/*
   学生表（students）
   学生ID（student_id）
   姓名（name）*/

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100)
);

/*
成绩表（grades）
学生ID（student_id）
成绩（score）*/

CREATE TABLE grades (
    student_id INT,
    score INT
);

--  插入数据到学生表
INSERT INTO students (student_id, name) VALUES
(1, '小明'),
(2, '小红'),
(3, '小蓝');

--  插入数据到成绩表
INSERT INTO grades (student_id, score) VALUES
(1, 88),
(2, 77),
 -- 注意：这里的学生ID 3 没有对应成绩

--左外连接查询

SELECT s.name, g.score
FROM students AS s
LEFT JOIN grades AS g ON s.student_id = g.student_id;

结果：

结果分析：因为成绩表中小蓝没有成绩，所以左外连接查询小蓝结果为NULL

--右外连接查询

SELECT s.name, g.score 
FROM students AS s 
RIGHT JOIN grades AS g ON s.student_id = g.student_id;

结果：

结果分析：因为成绩表中没有学号为3的学生，所以右外连接不会显示任何匹配成绩。

4.2.3 自然连接

自然连接根据同名列自动匹配两个表的记录。
SELECT * FROM  emp NATURAL JOIN dept;（自然内连接）

SELECT * FROM  emp NATURAL LEFT JOIN dept; (自然左外连接)

SELECT * FROM  emp NATURAL RIGHT JOIN dept; (自然右外连接)

作用：和内外连接效果一致；自动查找主外键相等（找到列名相同判定相等，两个表只有主外键命名相等）；

标签：多表,id,score,student,MySQL,之多表,学生,连接,SELECT
From： https://blog.csdn.net/m0_72047169/article/details/142518908

相关文章

  • MySQL之数据表，数据类型，表约束类型的指令讲解和练习
        本篇博客主要用来记录和分享本人学习MySQL数据库的基本操作指令的笔记和心得，包括数据表操作、数据管理、数据类型的讲解以及表的约束。通过实际的示例和注意事项，帮助大家更好地理解和应用这些知识。一、数据表操作指令1.创建数据表使用CREATETABLE指令可以创......
  • Connector C++ 连接 MySQL 数据库之增删改查
    在vcpkg中折腾了mysql-connector-cpp8.0很久，一直连接不上远程数据库，后面查官方文档，mysql-connector-cpp8.0好像只支持MySQL8.0以上的数据库，本来想把远程服务器上的MySQL升级到MySQL8.0，后面发现测试服务器的配置有点拉跨，架不住MySQL8.0，但是vcpkg中又没有mysql-c......
  • 记一次mysql导致服务器CPU突然暴涨的问题
    9月30号20点，感觉网站后台怎么越来越慢，打开阿里云看了服务器CPU竟然在几天内，一直是99%~100%，今天一天就觉得网站访问慢，还想着优化读取数据，原来CPU早就干爆了。想想有人攻击的话也不至于一直这样，putty上top一下，果然是mysql占满了CPU，大致查找策略如下 1、通过ps命令查找mysql进程......
  • mysql写外键约束时不生效
    写了外键约束，删除父表内容发现删除成功解决方法查看两个表使用的引擎showcreatetable表名;发现ENGINE=MyISAM更改引擎ALTERTABLEempENGINE=InnoDB;重新写外键再次尝试删除父表内容失败MyISAM和InnoDB是MySQL中的两种存储引擎，各自有不同的特点：MyISAM：......
  • sql注入-基于MySQL
    SQL注入的失败——SQL语句的未知性导致的拼接失败SQL操作都会导致SQL注入的出现：select，insert，update，delete数据类型：数字型(无符号干扰)——select*fromnewswhereid=$id;字符型(有符号干扰)——select*fromnewswhereid='$id';搜索型(有多符号干扰)——s......
  • 【25届计算机毕设选题推荐】基于python+MySQL的社区医院挂号就诊管理系统的设计与实现
    ✍✍计算机毕业编程指导师**⭐⭐个人介绍：自己非常喜欢研究技术问题！专业做Java、Python、小程序、安卓、大数据、爬虫、Golang、大屏等实战项目。⛽⛽实战项目：有源码或者技术上的问题欢迎在评论区一起讨论交流！⚡⚡Java、Python、小程序、大数据实战项目集⚡⚡文末获取......
  • 【2025计算机毕设选题推荐】基于python+MySQL的社区医院管理系统的设计与实现
    ✍✍计算机毕业编程指导师**⭐⭐个人介绍：自己非常喜欢研究技术问题！专业做Java、Python、小程序、安卓、大数据、爬虫、Golang、大屏等实战项目。⛽⛽实战项目：有源码或者技术上的问题欢迎在评论区一起讨论交流！⚡⚡Java、Python、小程序、大数据实战项目集⚡⚡文末获取......
  • nodejs koa 用knex.js链接mysql数据库，进行数据增删改查
    1、knex.js安装npminstallknex官方Installation|Knex.js中文文档|Knex.js中文网2、进行数据库链接constknex=require('knex')({client:'mysql2',connection:{host:'127.0.0.1',//地址user:'root',//账号......
  • 初学MySQL之基础篇1
    1、基本规则SQL可以写成一行或者多行，关键字不能被缩写也不能分行；但为了提高可读性，各子句分行写，必要时使用缩进；每条命令以;或\g结束；所有的()、单引号、双引号使用英文状态输入，必须成对结束；字符串型和日期、时间类型的数据使用单引号（''）表示；列的别名尽量使用双引......
  • Windows平台下安装与配置MySQL9
    Windows平台下安装与配置MySQL9 要在Windows平台下安装MySQL，可以使用图行化的安装包。图形化的安装包提供了详细的安装向导，以便于用户一步一步地完成对MySQL的安装。本节将详细介绍使用图形化安装包安装MySQL的方法。1.2.1 安装MySQL要想在Windows中运行MySQL，需要32位或6......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99