02-MySQL高级

MySQL 高级

1、约束

1.1、约束介绍

• 什么是约束
  
  
  
  • 对表中的数据进行限定，保证数据的正确性、有效性、完整性
    
  
  
• 约束类型
  
  
  
  • 约束说明PRIMARY KEY主键约束UNIQUE唯一约束NOT NULL非空约束DEFAULT默认值约束FOREIGN KEY外键约束CHECK检查约束（MySQL并不支持）
    
  
  
• 注意事项
  
  
  
  • MySQL不支持检查约束
    
  • 约束通常是在创建表结构的时候创建
    
    
    
    • 如果在创建表结构的时候没增加约束，后续再添加约束的话，有可能会导致垃圾数据的进入
      
    
    
  
  

1.2、主键约束

• 主键的作用
  
  
  
  • 用来区分表中的数据
    
  
  
• 主键的特点
  
  
  
  • 主键必须是唯一不重复的值
    
  • 主键不能包含NULL值
    
  
  
• 建表的时候添加主键约束
  
  
  
  • 1. CREATE TABLE 表名 (
    2.         字段名 字段类型 PRIMARY KEY,
    3.         字段名 字段类型
    4. );
    6. CREATE TABLE 表名(
    7.    列名 数据类型,
    8.    [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
    9. );

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

    复制代码
  • 注意事项
    
    
    
    • 非空主键不会随着主键约束的删除而消失，在MySQL中会保存下来
      
    
    
  
  
• 建表后单独添加主键约束
  
  
  
  • 1. ALTER TABLE 表名 ADD PRIMARY KEY (字段名);

    复制代码
  • 注意事项
    
    
    
    • 当添加主键约束的时候，字段的值如果在表中存在有重复值，那么建表后单独添加主键约束会报错
      
    
    
  
  
• 主键自增
  
  
  
  • 主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新纪录时，数据库自动生成主键字段的值
    
  • 主键设置为自增后，允许插入的主键为NULL值，自增的主键会自动把NULL值改为自增后的数据
    
  • 格式
    
    
    
    • 字段名  字段类型  PRIMARY KEY AUTO_INCREMENT
      
    • 注意事项：AUTO_INCREMENT 的字段必须是数值类型
      
    
    
  
  
• 面试题：修改自动增长的开始值
  
  
  
  • 1. ALTER TABLE st2 AUTO_INCREMENT = 1000;
    2. INSERT INTO st2 (NAME, age) VALUES ('校长', 22);
    4. ALTER TABLE st2 AUTO_INCREMENT = 500;
    5. INSERT INTO st2 (NAME, age) VALUES ('coolman', 23);

    复制代码
  • 注意事项
    
    
    
    • 自增以出现过的最大值为基准而+1
      
    
    
  
  

1.3、非空约束

• 非空约束的作用
  
  
  
  • 让字段的值不能为NULL
    
  
  
• 非空约束的格式
  
  
  
  • 1. CREATE TABLE 表名 (
    2.         字段名 字段类型 NOT NULL,
    3.     字段名  字段类型
    4. );

    复制代码
  
  

1.4、唯一约束

• 唯一约束的作用
  
  
  
  • 让字段的值唯一，不能重复
    
  
  
• 唯一约束的格式
  
  
  
  • 1. CREATE TABLE 表名 (
    2.         字段名 字段类型 UNIQUE,
    3.           字段名 字段类型
    4. );

    复制代码
  
  

1.5、默认约束

• 默认约束的作用
  
  
  
  • 如果这个字段不设置值，就使用默认值
    
  
  
• 默认约束的格式
  
  
  
  • 1. CREATE TABLE 表名(
    2.           字段名 数据类型 DEFAULT 值,
    3.           字段名 字段类型
    4. );

    复制代码
  
  

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、外键约束的使用

• 新建表的时候增加外键约束
  
  
  
  • 1. CREATE TABLE 表名 (
    2.         字段名 字段类型,
    3.           字段名 字段类型,
    4.           -- 添加外键约束
    5.           [CONSTRAINT 外键约束名] FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名)
    6. );

    复制代码
  • 关键字解释
    
    
    
    • CONSTRAINT
      
      
      
      • 表示约束外键约束名：给外键取个名字，将来通过约束名可以删除这个约束
        
      
      
    • FOREIGN KEY(外键字段名)
      
      
      
      • 指定某个字段左外外键
        
      
      
    • REFERENCES 主表(主键字段名)
      
      
      
      • 引用主表的主键的值
        
      
      
    
    
  
  
• 删除外键约束
  
  
  
  • 1. ALTER TABLE 表名 DROP FOREGIN KEY 外键约束名;

    复制代码
  • 注意事项
    
    
    
    • 删除外键的时候，外键名不需要添加单引号（外键名等同于其他字段名）
      
    
    
  
  
• 已有表增加外键约束
  
  
  
  • 1. 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、表连接查询--内连接

• 隐式内连接
  
  
  
  • 1. SELECT 字段列表 FROM 表1, 表2,... WHERE 条件;

    复制代码
  • 看不到 JOIN关键字，条件使用WHERE指定
    
  
  
• 显式内连接
  
  
  
  • 1. SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件；

    复制代码
  • 使用INNER JOIN ... ON 条件，可以省略INNER
    
  
  
• 内连接效果
  
  
  
  • 
    
  
  

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

3.4.1、左外查询

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

  复制代码
• 使用LEFT OUTER JOIN ... ON，OUTER可以省略
  
• 左外连接效果
  
  
  
  • 
    
  • 左外连接可以理解为：将满足要求的数据显示 ，左表不满足要求的数据也显示
    
  
  

3.4.2、右外查询

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

  复制代码
• 使用RIGHT OUTER JOIN ... ON，OUTER可以省略
  
• 右外连接效果
  
  
  
  • 
    
  • 右外连接可以理解为：满足要求的数据显示，并且右表不满足要求的也显示
    
  
  

3.5、多表查询之子查询

<ul>什么是子查询

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

Demo

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

  复制代码
• 子查询要放在()中
  
• 先执行子查询，将子查询的结果作为父查询的一部分
  

子查询结果的三种情况
<ul>子查询结果是单行单列
<ul>

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

复制代码

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