数据库多表操作

最新推荐文章于 2024-08-17 15:39:03 发布

Insist__

最新推荐文章于 2024-08-17 15:39:03 发布

阅读量687

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/Insist__/article/details/80290936

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.1 多表之间的关系

关联关系:在实际开发中,需要根据实体的内容设计数据表,实体间会有各种关联关系,所以根据实体设计的数据表之间也存在着各种关联关系,MySQL中数据表的关联关系有三种,具体如下:

一对多/多对一

多对一是数据表中最常见的一种关系,比如,员工和部门之间的关系,一个部门可以有多个员工,而一个员工不能属于多个部门,也就是说部门表中的一行在员工表中可以有许多匹配行,但员工表中的一行在在部门表中只能有一个匹配行。

表之间的关系通过外键建立的，在多对一的表关系中，应该将外键建在多的一方，否则造成数据的冗余。

多对多

多对多也是数据表中的一种关系，比如学生与课程之间的关系，一个学生可以选择多门课程，当然一门课程也供多个学生选择，也就是说学生表中的一行在课程表中可以有许多匹配行，课程表中的一行在学生表中的也有许多匹配行。

通常情况下，为了实现这种关系，需要定义一张中间表（称为连接表），该表会存在两个外键，分别参考课程表和学生表，在多对多关系中，需要注意的是，连接表的两个外键都是可以重复的，但是两个外键之间的关系是不能重复的，所以这两个外键又是连接表的联合主键

一对一

一对一关系在实际生活中比较常见，例如人与身份证之间就是一对一的关系，一个人对应一张身份证，一张身份证只能匹配一个人，那么一对一关系的两张表如何建立外键？

首先要分清主从关系，从表需要主表的存在才有意义，身份证需要人的存在才有意义。因此人为主表，身份证为从表，要在身份证中建立外键，由实际的经验可知，身份证中的外键必须是非空唯一的，因此会直接用从表（表身份证）的主键作为外键。

1.2 多表之间的关系如何来维护

-- 数据库的五大约束:

 
      主键约束: primary key 
     
      唯一 / 非空 
      唯一约束 : unique 
     
      非空约束 : not null 
     
      检查约束: mysql里面不支持 
     
      check 
      外键约束: foreign key

--外键约束(多表约束): 外键是指引用另一张表中的一列或多列,被引用的列应该具有主键约束或唯一性约束**.外键用于建立和加强两个表数据之间的连接，实际上是用来约束两张表,约束从表中的记录必须存在于主表中

作用:就是确保表中数据的完整性

从表:商品表依赖于分类表,所以商品称为从表

主表:分类表

书写语法:

 
       -- 添加外键约束方式一 语法格式 
      
       alter table 表名 add foreign key(外键字段名) references 主表表名(主键字段名); 
       ​ 
      
       --添加外键约束方式二 建表时候就添加外键  
       语法格式 
      
       foreign key(列名) references 表名(列名)

注意：

插入数据: 数据必须先存在于主表,然后再插入从表中的数据
删除主表数据时候: 必须先删除从表中已引用了主表中数据,再删除主表中的数据
删除表的时候,如果有从表里面有外键约束,必须先删除从表再删除主表
从表在添加外键约束的时候,外键必须是主表中的主键

1.3 多表之间的建表原则

一对多:
- 建表原则: 在多的一方添加一个键,然后让它作为外键指向一的一方
- 例如:学生和班级,一个班级对多个学生,一个学生只属于一个班级,那么就可以在学生表里面建立外键指向班级表的主键
- 商品分类和商品,一个分类下可以有多个商品,一个商品只属于一个分类,那么就可以在商品表里面建立外键指向商品分类表
多对多:
- 建表原则:创建一张中间表,将多对多的关系,拆分成一对多的关系, 中间表至少要包含两个外键,这两个外键分别指向各自原来的表
- 例如:学生和选课
一对一:
- 建表原则: 1. 将两张表的主键建立连接, 2. 将两张合并成一张表 3. 将一对一的关系,拆分成一对多的关系
- 合并表,拆表
- 例如公民和身份证号

2 多表查询

概述：在关系型数据库管理系统中，建立表时各个数据之间的关系不必确定，通常将每个实体的所有信息存放在一个表中，当查询数据时候，通过连接操作查询多个表中的实体信息，当两个或多个表中存在相同意义的字段时候，便可以通过这些字段对不同的表进行连接查询，连接查询包括交叉连接查询，内连接查询，外连接查询.

2.1 - 交叉连接查询

概述:交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积,也就是返回第一个表中符合查询条件的数据行乘以第二个表中符合查询条件的数据行数,例如 department表中有4个部门,employee表中有4个员工,那么交叉连接的结果有4*4=16条数据，注意:交叉查询得到的是笛卡尔积 : 两张表的乘积, 实际没有意义

-- 交叉连接的语法格式如下:select * from 表1 cross join 表2;select * from 表1,表2;-- 以上语法格式 cross join 用于连接两个要查询的表,通过该语句可以查询两个表中所有的数据组合-- 交叉查询SELECT * FROM USER CROSS JOIN orders;SELECT * FROM USER,orders;

2.2 - 内连接查询:

概述:

内连接(inner join) 又称为简单连接或自然连接,是一种常见的连接查询,内连接使用比较运算符对两个表中的数据进行比较,并列出与连接条件匹配的数据行,组合成新的结果,也就是说在内连接查询中,只有满足条件的记录才能出现在查询结果中,内连接查询的语法如下:

内连接查询语法:

显式内连接: select 查询字段 from 表 1 inner join 表2 on 表1.关系字段 = 表2.关系字段; -- inner 可以省略不写隐式内连接: select 查询字段 from 表1,表2 where 条件;

注意:

inner join 用于连接两个表

on 用于指定连接条件

其中inner 可以省略

2.3 - 外连接查询:

概述:

内连接查询中,返回的结果只包含符合查询条件和连接条件的数据,然而有时还需要包含没有关联的数据,即返回查询结果中不仅包含符合条件的数据,而且还包括左表(左连接或左外连接)/右表(右连接或右外连接)或者两个表(全外连接)中的所有数据,此时就需要使用外连接查询,外链接分为左连接和右连接.

外连接语法:

select 所查字段 from 表 1 left|right [outer] join 表2 on 表1.关系字段 = 表2.关系字段 where 条件;

left join(左外连接): 以左表为基础,将左表中所有的记录都查询出来,如果没有对应的记录,用null值填充

Right join(右外连接):以右表为基础,将右表中的所有记录都查询出来,如果没有对应的记录,用null值填充

 
           -- 使用外连接查询 
           -- 左外查询  
           -- 使用左外查询查用户和订单表,即使用户表中有用户没有购买任何商品,也会显式出来 
          
           SELECT * FROM USER LEFT OUTER JOIN orders ON user.userid = orders.uno; 
          
           -- 右外查询 
           -- 使用右外查询查用户和订单表,即使用户表中有用户没有购买任何商品,也会显式出来 
          
           SELECT * FROM orders RIGHT OUTER JOIN USER ON user.userid = orders.uno;