MySQL 高级

1、约束

1.1、约束介绍

• 什么是约束

  • 对表中的数据进行限定，保证数据的正确性、有效性、完整性

• 约束类型

  • 约束	说明
    PRIMARY KEY	主键约束
    UNIQUE	唯一约束
    NOT NULL	非空约束
    DEFAULT	默认值约束
    FOREIGN KEY	外键约束
    CHECK	检查约束（MySQL并不支持）

• 注意事项

  • MySQL不支持检查约束
  • 约束通常是在创建表结构的时候创建
    • 如果在创建表结构的时候没增加约束，后续再添加约束的话，有可能会导致垃圾数据的进入

1.2、主键约束

• 主键的作用

  • 用来区分表中的数据

• 主键的特点

  • 主键必须是唯一不重复的值
  • 主键不能包含NULL值

• 建表的时候添加主键约束

  • CREATE TABLE 表名 (
    	字段名 字段类型 PRIMARY KEY,
    	字段名 字段类型
    );
    
    CREATE TABLE 表名(
       列名 数据类型,
       [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
    ); 
    

• 删除主键约束

  • ALTER TABLE 表名 DROP PAIMARY KEY;	-- 非空主键不会随着主键约束的删除而消失，在MySQL中会保存下来
    

  • 注意事项

    • 非空主键不会随着主键约束的删除而消失，在MySQL中会保存下来

• 建表后单独添加主键约束

  • ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
    

  • 注意事项

    • 当添加主键约束的时候，字段的值如果在表中存在有重复值，那么建表后单独添加主键约束会报错

• 主键自增

  • 主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新纪录时，数据库自动生成主键字段的值
  • 主键设置为自增后，允许插入的主键为NULL值，自增的主键会自动把NULL值改为自增后的数据
  • 格式
    • 字段名 字段类型 PRIMARY KEY AUTO_INCREMENT
    • 注意事项：AUTO_INCREMENT 的字段必须是数值类型

• 面试题：修改自动增长的开始值

  • ALTER TABLE st2 AUTO_INCREMENT = 1000;
    INSERT INTO st2 (NAME, age) VALUES ('校长', 22);
    
    ALTER TABLE st2 AUTO_INCREMENT = 500;
    INSERT INTO st2 (NAME, age) VALUES ('coolman', 23);
    

  • 注意事项

    • 自增以出现过的最大值为基准而+1

1.3、非空约束

• 非空约束的作用

  • 让字段的值不能为NULL

• 非空约束的格式

  • CREATE TABLE 表名 (
    	字段名 字段类型 NOT NULL,
        字段名  字段类型
    );
    

1.4、唯一约束

• 唯一约束的作用

  • 让字段的值唯一，不能重复

• 唯一约束的格式

  • CREATE TABLE 表名 (
    	字段名 字段类型 UNIQUE,
      	字段名 字段类型
    );
    

1.5、默认约束

• 默认约束的作用

  • 如果这个字段不设置值，就使用默认值

• 默认约束的格式

  • CREATE TABLE 表名(
      	字段名 数据类型 DEFAULT 值,
      	字段名 字段类型
    );
    

1.6、外键约束

1.6.1、使用外键约束的意义

• 当我们在employee的dep_id里面输入不存在的部门，数据依然可以添加，但是并没有对应的部门，不能出现在这种情况。employee的dep_id的内容只能是department表中存在的id
  • 
• 解决方式
  • 需要约束dep_id只能是department表中已经存在id
  • 可以使用外键约束来解决这类问题
• 外键约束的作用
  • 1.限制表中的数据只能使用另外一张表的数据
  • 2.保证数据的一致性、完整性

1.6.2、外键约束的概念

• 什么是外键
  • A1表中的字段C1，引用了A2表中字段C2，那么C1字段叫做外键，A2表交主表，A1表叫从表（也叫副表）
  • 主表：将数据给别人用的表
  • 副表：使用别人数据的表
  • 

1.6.3、外键约束的使用

• 新建表的时候增加外键约束

  • CREATE TABLE 表名 (
    	字段名 字段类型,
      	字段名 字段类型,
      	-- 添加外键约束
      	[CONSTRAINT 外键约束名] FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名)
    );
    

  • 关键字解释

    • CONSTRAINT
      • 表示约束外键约束名：给外键取个名字，将来通过约束名可以删除这个约束
    • FOREIGN KEY(外键字段名)
      • 指定某个字段左外外键
    • REFERENCES 主表(主键字段名)
      • 引用主表的主键的值

• 删除外键约束

  • ALTER TABLE 表名 DROP FOREGIN KEY 外键约束名;
    

  • 注意事项

    • 删除外键的时候，外键名不需要添加单引号（外键名等同于其他字段名）

• 已有表增加外键约束

  • ALTER TABLE 从表 ADD [CONSTRAINT 外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
    

2、数据库设计（范式）

2.1、数据库设计简介

• 1.软件的研发步骤
  • 
• 2.数据库设计概念
  • 数据设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型
  • 建立数据库中的表结构以及表与表之间的关联关系的过程
  • 有哪些表？表里有哪些字段？表和表之间有什么关系？
• 3.数据库设计的步骤
  • 需求分析（数据是什么，数据具有哪些属性，数据与属性的特点是什么）
  • 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
  • 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
  • 维护设计（对新的需求进行建模；表优化）
• 论坛系统设计案例
  • 

2.2、表关系

2.2.1、表关系之一对多

• 一对多（多对一）
  • 部门表和员工表
  • 一个部门对应多个员工，一个员工对应一个部门
• 
• 实现方式
  • 在多的一方建立外键，指向一的一方的主键

2.2.2、表关系之多对多

• 多对多
  • 订单表和商品表
  • 一个商品对应多个订单，一个订单包含多个商品
• 
• 实现方式
  • 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

2.2.3、表关系之一对一

• 一对一
  • 用户表和用户详情表
  • 一对一关系多用于表拆分，将一个实体中经常使用的字段放在一张表，不经常使用的字段放另一张表，用于提升查询性能
• 
• 实现方式
  • 在任意一方加入外键，关联另一方主键，并且设置外键为唯一（UNIQUE）

2.3、数据库设计案例

3、MySQL多表查询

3.1、MySQL多表查询介绍

• 为什么要有多表查询
  • 例如要查询某员工的名字和他所在的部门名字（这里假设数据库中员工表和部门表是关联的）
  • 需要查询多张表才能得到我们想要的数据
• 多表查询的分类
  • 表连接查询（同时查询多张表）
    • 内连接
    • 外连接
  • 子查询

3.2、表连接笛卡尔积现象

• 查询孙悟空员工的信息，包括所在的部门名称
  • 
  • 左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积
  • 我们发现不是所有的数据组合都是游泳的，只有员工表.dept_id = 部门表.id的数据才是游泳的。所以需要通过条件过滤掉没用的数据。
  • 过滤掉没用数据的条件称为表连接条件

3.3、表连接查询--内连接

• 隐式内连接

  • SELECT 字段列表 FROM 表1, 表2,... WHERE 条件;
    

  • 看不到 JOIN关键字，条件使用WHERE指定

• 显式内连接

  • SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件；
    

  • 使用INNER JOIN ... ON 条件，可以省略INNER

• 内连接效果

  • 

3.4、表连接查询--外连接

3.4.1、左外查询

• SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
  

• 使用LEFT OUTER JOIN ... ON，OUTER可以省略

• 左外连接效果

  • 
  • 左外连接可以理解为：将满足要求的数据显示 ，左表不满足要求的数据也显示

3.4.2、右外查询

• SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
  

• 使用RIGHT OUTER JOIN ... ON，OUTER可以省略

• 右外连接效果

  • 
  • 右外连接可以理解为：满足要求的数据显示，并且右表不满足要求的也显示

3.5、多表查询之子查询

• 什么是子查询

  • 一个查询语句的结果作为另一个查询语句的一部分

• Demo

  • SELECT 查询字段 FROM 表 WHERE 条件;
    
    SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);
    

  • 子查询要放在()中

  • 先执行子查询，将子查询的结果作为父查询的一部分

• 子查询结果的三种情况

  • 子查询结果是单行单列

    • SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
      

    • 子查询结果是单行单列，在WHERE后面作为条件，WHERE后面使用的是比较运算符：=，>, <=, <>

    • 

  • 子查询结果是多行单列

    • SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
      

    • 子查询结果是多行单列，结果集类似于一个数组，在WHERE后面作为条件，父类使用IN/ANY/ALL运算符

    • 

  • 子查询结果是多行多列

    • SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;
      

    • 子查询结果是多行多列，在FROM后面作为虚拟表

    • 

3.6、多表查询案例

• 我们在公司开发中，根据不同的业务需求往往需要通过2张及以上的表中去查询需要的数据。所以我们有必要学习2张及以上的表的查询。其实不管是几张表的查询，都是有规律可循的。
  • 准备数据在备注中
  • 练习1:查询所有员工信息。显示员工编号, 员工姓名, 工资, 职务名称, 职务描述
  • 练习2:查询所有员工信息。显示员工编号, 员工姓名, 工资, 职务名称, 职务描述, 部门名称, 部门位置
  • 练习3:查询经理的信息。显示员工姓名, 工资, 职务名称, 职务描述, 部门名称, 部门位置, 工资等级
  • 练习4:查询出部门编号、部门名称、部门位置、部门人数
  • 练习5:列出所有员工的姓名及其直接上级的姓名, 没有上级领导的员工也需要显示,显示自己的名字和领导的名字
  • 练习6:查询出所有的普通员工
  • 练习7:查询工资高于公司平均工资的所有员工信息。显示员工id, 员工姓名, 员工工资, 部门名称, 工资等级

4、数据库事务

4.1、事务简介

• 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令

• 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败；

• 事务是一个不可分割的工作逻辑单元

• 事务的使用

  • -- 开启事务
    START TRANSACTION; -- 或者 BEGIN; 效果一样
    -- 提交事务
    COMMIT;
    -- 回滚事务
    ROLLBACK;
    

• 事务Demo

  • 

4.2、转账事务案例

• 演示手动提交事务
  • 模拟张三给李四转500元钱（成功）
    • 在DOS命令行执行以下SQL语句： 1.开启事务 2.张三账号-500 3.李四账号+500
    • 在DOS命令行执行commit：提交事务
    • 使用SQLYog查看数据库：发现数据改变
  • 模拟张三给李四转500元钱（失败）
    • 在DOS命令行执行以下SQL语句：1.开启事务， 2.张三账号-500
    • 在DOS命令行执行rollback回滚事务
    • 使用SQLYog查看数据库：发现数据没有改变

4.3、事务的四大特性

• 原子性（Atomicity）
  • 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
• 一致性（Consistency）
  • 事务前后数据的完整性必须保持一致
• 隔离性（Isolation）
  • 指多个事务并发访问数据库时，一个事务不能被其他的事务所干扰，多个并发事务之间数据要相互隔离，不能互相影响
• 持久性（Durability）
  • 事务一旦提交或回滚，它对数据中的数据的改变就是永久的

4.4、自动提交事务

• 在没有手动开启的情况下，每条增删改语句执行完毕自动提交事务，MySQL默认开始自动提交事务
• 查看MySQL是否开启自动提交事务
  • SELECT @@autocommit;
    • 0：关闭自动提交
    • 1：开启自动提交
• 关闭自动提交事务
  • set autocommit = 0;
• 关闭事务后的案例
  • 在控制台执行以下SQL语句：张三-500
  • 使用SQLYog查看数据库，发现数据并没有改变
  • 在控制台执行commit提交任务
  • 使用SQLYog查看数据库，发现数据改变