Mysql基础

MySql进阶

1.单表储存的问题

• 假如要增加一个商品分类,但是分类里没有商品

• 要修改分类信息,修改的数据庞大,影响性能

• 解决方案:
  • 拆表: 一个商品信息表product,一个分类信息表category

2.多表储存的问题

• 拆分成两张表：

  • category：只存储分类信息
  • product：只存储商品信息，商品所属分类使用了字段cid，字段值就是所属分类的id

• 解决的问题：

  • 维护方便：
    • 增加一个分类，但是暂时没有商品数据，可以增加
    • 要修改分类，只要修改一条数据即可

• 存在的问题：

  • product表的cid字段，要从category表的cid取值

    category表：被取值的表，叫主表（一的一方叫主表）

    product表：从其它表引用值的表，叫从表（多的一方叫从表）
    如果删除主表category里，cid为1的数据。可以删除成功

• 注意：分类为1，在从表里有关联的数据

• 一旦删除成功了，商品表里的数据就成为脏数据了：商品信息找不到所属的分类信息了，数据不完整

  外键约束

  分析

刚刚使用多表存储数据时，理想的状态是：

• 删除主表（category）数据时，如果从表（product）里有数据，应该是不让删除的
• 添加从表（product）数据时，如果主表（category）里没有数据，应该是不让添加的
• 目的是：保证了数据的完整性和一致性，避免了脏数据
• 实现的方案：给从表的字段，增加外键约束
  • 外键约束字段的值，必须从主表的主键中取值
  • 如果删除主表的数据，而从表里有关联的数据，外键约束会报错，删除失败，数据是一致的完整的

讲解

外键约束介绍

• 作用：从表（product）里 外键约束的字段(cid)，必须从主表（category）的主键（cid）中取值。
  • 如果删除了主表里的数据，从表里有数据，是会报错的，删除不掉的
• 目的：保证数据的一致性和完整性，避免脏数据

添加外键约束的语法

• 创建从表时设置外键：[constraint 约束名称] foreign key(外键字段) references 主表(主键)
• 已有表里设置外键：alter table 表名称 add [constraint 约束名称] foreign key(外键字段) references 主表(主键)

表关系

分析

• 表之间的关系有：
  • 一对一
  • 一对多（多对一）
  • 多对多

讲解

1. 一对一（了解）

• 不常用，因为一对一的两张表，通常可以合并成一张表。但是以下情况，建议拆分表：

  • 出于效率的考虑：常用字段放在一张表，不常用字段放在另外一张。两张表一对一
  • 出于业务划分的考虑：用户信息表，和帐户信息表。

  • 2. 一对多

• 例如：分类和商品，用户和订单

• 建表原则：在从表上增加一个字段，作为外键指向主表的主键。

  • 从表：多的一方，引用其它表数据的。product， 订单表
  • 主表：一的一方，被引用数据的。category，用户表

3. 多对多

• 例如：老师和学生，订单和商品，学生和课程
• 建表原则：建立一张中间关系表，表里要有两个字段，是外键分别指向两张表的主键

二、多表查询

1. 多表查询和迪卡尔积

什么是多表查询

• 使用一条SQL语句，从多表中联合查询数据，叫多表查询。

  在设计数据库时，我们使用数据库范式创建了更科学、更合理的数据库。但是也把数据拆分到了多表中进行保存。那么在我们需要查询数据库时，就可能需要从多表中进行查询了。

什么是迪卡尔积

• 多表之间毫无意义的排列组合，叫迪卡尔积
  • A表有3条数据，B表有4条数据，迪卡尔积有12条数据
  • 多表之间的数据是有关联关系的，那么在多表查询时，就必须要使用关联条件进行数据筛选，否则会出现大量的脏数据，形成迪卡尔积、
• 多表查询中，一定要避免迪卡尔积，因为有大量的脏数据

如何避免迪卡尔积

• 多表查询时，一定要提供多表关联条件

• 2. 多表查询语法

• MySql中多表查询的语法有：

• 内连接查询：

• 外连接查询：

• 子查询

• 多表查询的技巧

1. 确定需要的数据在哪些表里
2. 找到这些表之间的关联条件
3. 使用关联条件，进行多表关联查询（相当于把多表的数据又重新合并成到了一张表里）
4. 从多表关联查询结果里，进行条件筛选、分组、排序等操作

内连接查询

• **查询效果：**查询表之间必定有关联的数据（无关数据是被剔除掉的）

• 查询语法：有显式内连接和隐式内连接两种写法，语法不同，但结果完全相同

  • 显式内连接：select * from 表1 inner join 表2 on 表关联条件 where 筛选条件
  • 隐式内连接：select * from 表1, 表2 where 表关联条件 and 筛选条件

  • 外连接查询

• 查询效果：查询一张表的全部数据，及另外一张表的关联数据

• 查询语法：有左外连接和右外连接两种，作用相同，但方向相反

  • 左外连接：查询左表的全部数据，及右表的关联数据

    select * from 左表 left join 右表 on 表关联条件 where 筛选条件

  • 右外连接：查询右表的全部数据，及左表的关联数据

    select * from 左表 right join 右表 on 表关联条件 where 筛选条件

    子查询

• 子查询：仅仅是一种应用灵活的技巧，使用查询嵌套的方式进行查询，没有固定的语法

• 查询语法：把子查询结果，作为SQL语句中的一部分进行使用使用，通常有以下三种：

  • 子查询结果是一个值。
  • 子查询结果是一个集合。
  • 子查询结果是一张虚拟表。建议使用内连接/外连接查询，更简单

三、事务

• 什么是事务：事务是数据库里的一个概念，表示一个事务的多个组成单元，要么全部成功，要么全部失败

• 事务的作用：用于保证事务里的多个操作，要么全部成功，要么全部失败

• 事务的经典使用场景：银行转账，张三要给李四转账1000元钱，步骤如下：

• 开启事务

  • 张三的帐号，扣钱1000
  • 李四的帐号，加钱1000
  • 关闭事务：
    • 提交事务：事务里所有的操作会全部立即生效
    • 回滚事务：事务里所有的操作会全部立即撤消
    • MySql的事务管理有两种：

• 手动管理方式

• 自动提交方式

• 手动事务管理

• 语法

  • 开启事务：start transaction
  • 提交事务：commit
  • 回滚事务：rollback

• 步骤

  1. 手动开启事务

  2. 执行SQL语句

  3. 提交/回滚

     如果还有事务操作，循环以上1~3步

自动事务管理

• 语法

  • 开启事务（关闭自动提交）：set autocommit=0
  • 提交事务：commit
  • 回滚事务：rollback

  拓展：查看自动提交是否已经关闭 select @@autocommit;。1是开启，0是关闭。默认是1

• 步骤

  1. 关闭自动提交（开启事务）

  2. 执行SQL语句

  3. 提交/回滚

     如果还有事务操作，循环以上2~3步

     事务特性和并发问题

     事务的四大特性ACID

• A：Atomicity，原子性。指事务是不可分割的，不可能存在成功一半的情况
• C：Consistency，一致性。指事务提交前后的状态和数据是一致的
• I：Isolation，隔离性。指多事务并发时，理论上是互不干扰、相互独立的
• D：Durability，持久性。指事务一旦提交，数据变更就永久保存到磁盘文件上，不会再丢失，除非再次变更

事务的并发问题

• 什么是事务并发问题：事务有隔离性，即理论上多个事务并发时，是完全独立、互不干扰的。但是实际情况做不到完全隔离，事务之间就会形成干扰，这些干扰的现象，就是事务并发的问题

• 有哪些并发问题：有3个事务并发问题，按严重程序从高到低分别是：

  问题	描述
  脏读	一个事务里读取到另外一个事务未提交的数据，是一定要避免的
  不可重复读	一个事务里多次读取的数据不一致–受其它事务的update操作干扰了
  幻读	一个事务里多次读取的数据不一致–受其它事务的insert/delete操作干扰了

• 如何解决事务并发问题：事务并发问题的原因，是事务之间的隔离级别不够。我们可以通过设置事务的隔离级别，来解决事务并发问题

3. 事务的隔离级别

隔离级别介绍

隔离级别	脏读	不可重复读	幻读
read uncommitted	有	有	有
read committed（Oracle的默认值）	无	有	有
repeatable read （MySql的默认值）	无	无	有
serializable串行化	无	无	无

• 安全性：serializable > repeatable read > read committed > read uncommitted
• 性能：serializable < repeatable read < read committed < read uncommitted

隔离级别方法

• 隔离级别的SQL语法：

  • 查看隔离级别：select @@tx_isolation
  • 设置隔离级别：set session transaction isolation level 隔离级别