数据库笔记03 MySQL中的多表

数据库笔记03 MySQL中针对多表的操作

一.多表中的外键约束：

我们在上次的笔记中有介绍MySQL中的约束，如：主键约束，自增约束，非空约束等等，但是这些约束是针对单表进行约束的，针对多表中为了描述不同表之间的关系有一种约束，为外键约束（FOREIGN KEY）

1.外键约束介绍：

外键约束（FOREIGN KEY）： 是针对实体和实体之间的关系（或：表和表之间的关系）为以下三种时：一对一，一对多，多对多，为描述表之间的相关性而设定的约束
建多表时之前先画E-R图，再建表：

我们在建表时可以将外键和主键之间进行关联，加上一列数据可以更清晰，其中：

• 一对多的关系：我们把一方这个表叫一表，多方这个表叫多表，或从表
• 我们会在多表一方会有一个外键和一表进行关联，来描述它们之间的关系

需要添加外键约束：为了数据的有效性和完整性，来约束不合理的操作：（删除一表中与多表中的关联信息列中的数据，直接删一表数据不合理，因为表中数据是相互关联的，单独删去某表关联列中的数据会使得其他表中被关联列出现数据缺失和数据空白）
因此我们会在多表一方添加外键约束，去关联一表一方的主键，添加了外键之后，就会有如下特点：

• 1）主表一方，就不能删除还在引用的数据
• 2）多表一方，不能添加主表没有描述的数据

添加外键约束的方法，通过alter操作修改多表：

ALTER TABLE orders ADD FOREIGN KEY(uid) REDERENCES 主表(user)
-- 上面主表user与多表中uid相互关联

2.级联操作：

我们在上面提示到若对于关联数据进行操作时，很可能会出现数据缺失和数据空白，为了解决这种问题，MySQL中规定了外键对应的几种操作，其中最常用的是级联操作
级联操作由以下两种操作组成：

• 级联删除：在上面加外键的语句后面加上ON DELETE CASCADE
• 级联更新：在上面加外键的语句后面加上ON UPDATE CASCADE

两种级联操作对应的效果：

• 级联删除的效果：删除主表中关联列的信息，在多表中的被关联列中所对应的元组也会被删除
• 级联更新的效果：在多表中的被关联列元组更新数据时，在主表中的关联列会生成对应的新的数据

注意： 外键约束和级联效果在新建一个表的时候也可以直接加在对应的字段上

3.多对多关系：

• 为了数据更容易描述，在多表关系中，表1和表2之间引入一个中间表：目的是将多对多转换成两个一对多
• 多对多之间在加入外键时，在中间表中加入两个外键，分别关联表1和表2
• 一张表中只能有一个主键，但是可以有多个外键，添加外键约束：在多表一方的外键约束去关联

二.多表查询/连接查询：

多表查询：将多张表关联起来进行查询（即：连接查询），查数据只是将对应的信息查询出来，通过笛卡尔积的形式将多个表进行连接
例如：

select user.*,order.* from 表1，表2 ；

我们在进行多表查询时需要关联调件，笛卡尔积在查询条件时数据呈现没有意义，因此在关联信息时，需要关联N个数据从N张表查询，就需要N-1个关联条件，其中多表查询的细节如下：

• 1）内连接：不符合条件的数据是不予展示的；
  隐式内连接格式：select from where
  例如：查询所有用户的所有订单信息：

select user.*,orders.* from user,orders where user.`id`=orders.`user_id` ;

显式内连接：

select user.*,orders.* from user inner join orders on user.`id`=orders.`user_id` ;

注意： 其中使用inner join on中inner可以省略不写

• 2）针对需求：我们要查询所有用户的订单信息，如果该用户没有订单信息，用户信息必须展示出来，订单信息以null展示，所以在内连接中这种需求是达不到的，我们需要使用外连接来辅助：
  外连接： 左外连接和右外连接(outer可以不写)
  左外连接：

left (outer) join on: select user.*,orders.* from user left join orders on user.`id`=orders.`user_id` ;

特点： 以left分左右，left左边的表中数据会全部展示出来，如果右边表中没有数据，会以null进行展示，即需要查询所有订单所对应的用户信息，当订单对应的用户未知时，显示NULL；
右外连接：

right (outer) join on: select user.*,orders.* from user right join orders on user.`id`=orders.`user_id` ;

左外连接和右外连接是多表查询中的对称方法，二者可以互换，即：与哪边连接，哪边的表会全部展示出来

• 可以在多表查询中使用重命名进行简写：

select u.*,i.*,o.* from user as u, orders as o, orderitem as i... ;

三.子查询/嵌套查询：

子查询：一个主查询的条件来自于另外一个主查询
子查询的中需要注意的有：

• 1）要有括号
• 2）合理的书写风格
• 3）可以在主查询的where ,select, having ,from后面都可以使用子查询，select 语句后面使用子查询,只能使用单行子查询,即只允许返回一条记录
• 4）不可以在group by后面使用子查询
• 5）强调from后面的子查询
• 6）主查询和子查询可以不是同一张表；只要子查询返回的结果主查询可以使用 即可
• 7）一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
• 8）一般先执行子查询 再执行主查询；但相关子查询例外
• 9）单行子查询只能使用单行操作符；多行子查询只能使用多行操作，主查询可以有多个子查询,即1:n关系,子查询可以嵌套用,最多855层，子查询效率没有主查询效率高
• 10）子查询中的null ,为什么集合中若有空值,不能用not in(10,20,null) 可以用in( );

例如：查询用户为张三的订单详情：
1.查询user表中张三的id
2.用张三的id去订单表中找订单信息
第一步：

select id from user where username='张三'  ;

第二步：

select * from orders where userid=3 ;

其中张三在orders中对应的userid为3，userid是从user中获取的，因此将二者合并
将上面两步进行合并为子查询：

select * from orders where userid=(select id from user where username = '张三') ;

注意事项：

• 子查询在应用中可以进行无限嵌套
• 在子查询中获得多个值时在主查询中的where加条件时不用=赋值，而用in进行值传递
• 同样子查询可以作为多表查询中的临时表进行出现
• 实际上能用多表查询写出来的尽量不用子查询，但是子查询的嵌套层次思路会更加清晰

四.自查询：

• 自查询：比如我要在emp表中查询员工姓名所对应的老板
• 多个数据在一张表中，将表中数据由一张看成两张表进行查询

五.复制表：

create table 新表名字 as select * from 旧表名字 ;

新表中数据就从旧表中复制过来了，在复制过程中可以只复制对应几个字段，就可以根据旧表中的查询规则将字段给到新表
创建的表同样可以来自一个子查询：

create table 新表名字 as select * from 子查询 ;