MySQL高级

约束

概念和分类

• 约束的概念

  • 约束是作用于表中列上的规则，用于限制加入表的数据
  • 约束的存在保证了数据库中数据的正确性、有限性和完整性

• 约束的分类

约束名称	描述	关键字
非空约束	保证列中所有数据不能有null值	NOT NULL
唯一约束	保证列中所有数据各不相同	UNIQUE
自动增长	当列是数字类型并且唯一约束	AUTO_INCREMENT
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
检查约束	保证列中的值满足某一条件	CHECK
默认约束	保存数据时，未指定值则采用默认值	DEFAULT 值
外键约束	外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

注意：MySQL不支持检查约束

非空约束

• 概念：非空约束用于保证列中所有数据不能有NULL值

• 语法

  • 添加约束

  -- 创建表时添加非空约束
  CREATE TABLE 表名(
      列名 数据类型 NOT NULL,
      ...
  );
  

  -- 建表完后添加非空约束
  ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
  

  • 删除约束

  ALTER TABLE 表名 MODIFY 字段名 数据类型;
  

唯一约束

• 概念：唯一约束用于保证列中所有数据各不相同

• 语法

  • 添加约束

  -- 创建表时添加唯一约束
  CREATE TABLE 表名(
      列名 数据类型 UNIQUE [AUTO_INCREMENT],
      -- AUTO_INCREMENT：当不指定值时自动增长
      ...
  );
  CREATE TABLE 表名(
      列名 数据类型,
      ...
      [CONSTRAINT] [约束名称] UNIQUE(列名)
  );
  

  -- 建完表后添加唯一约束
  ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
  

  • 删除约束

  • ALTER TABLE 表名 DROP INDEX 字段名;
    

主键约束

• 概念

  • 主键时一行数据的唯一标识，要求非空且唯一
  • 一张表只能有一个主键

• 语法

  • 添加约束

  -- 创建表时添加主键约束
  CREATE TABLE 表名(
      列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
      ...
  );
  CREATE TABLE 表名(
      列名 数据类型,
      [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
  );
  

  -- 建完表后添加主键约束
  ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
  

  • 删除约束

  ALTER TABLE 表名 DROP PRIMARY KEY;
  

默认约束

• 概念：保存数据是，未指定值则采用默认值

• 语法

  • 添加约束

  -- 创建表时添加默认约束
  CREATE TABLE 表名(
      列名 数据类型 DEFAULT 默认值,
      ...
  );
  

  -- 建完表后添加默认约束
  ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
  

  • 删除约束

  ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
  

外键约束

• 概念：外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性

• 语法

  • 添加约束

  -- 创建表时添加外键约束
  CREATE TABLE 表名(
      列名 数据类型,
      ...
      [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主列表名)
  );
  

  -- 建完表后添加外键约束
  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表名称(主列表名称);
  

  • 删除约束

  ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
  

数据库设计

数据库设计概念

• 数据库设计就是根据业务系统的具体要求，结合我们所用的DBMS，为这个业务系统构造出最优的数据存储模型
• 建立数据库中的表结构以及表与表之间的关联关系的过程
• 有哪些表？表里有哪些字段？表和表之间有声明关系

数据库设计的步骤

• 需求分析（数据是什么？数据具有哪些属性？数据与属性的特点是什么）
• 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
• 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
• 维护设计（1.对新的需求进行建表 2.表优化）

表关系之一对多

• 一对多（多对一）：

  如：部门表 和 员工表

  一个部门对应多个员工，一个员工对应一个部门

• 实现方式：在多的一方建立外键，指向一的一方的主键

表关系之多对多

• 多对多：

如：订单 和 商品

一个商品对应多个订单，一个订单对含多个商品

• 实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

表关系之一对一

• 一对一：

如：用户 和 用户详情

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

• 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

多表查询

多表查询简介

多表查询：从多张表查询数据

• 连接查询

  • 内连接：相当于查询 A B 交集数据

  • 外连接：

    • 左外连接：相当于查询 A 表所有数据和交集部分数据
    • 右外连接：相当于查询 B 表所有数据和交集部分数据

• 子查询

内连接

• 语法

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

-- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

• 内连接相当于查询 A B 交集数据

外连接

• 语法

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

• 左外连接：相当于查询 A 表所有数据和交集部分数据
• 右外连接：相当于查询 B 表所有数据和交集部分数据

子查询

• 概念

查询中嵌套查询，称嵌套查询为子查询

• 子查询根据查询结果不同，作用不同：

  • 单行单列：作为条件值，使用 = != > < 等进行条件判断

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

  • 多行单列：作为条件值，使用 in 等关键字进行条件判断

  SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);
  

  • 多行多列：作为虚拟表

  DELECT 字段列表 FROM (子查询) WHERE 条件;
  

事务

事务简介

• 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令
• 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
• 事务是一个不可分割的工作逻辑单元

事务操作

-- 开启事务
START TRANSACTION;
或者  BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

事务的四大特征

• 原子性（Atomicity）：事务是不可分割的最小操作单位，要么同时成功，要么同时失败
• 一致性（Consistency）：事务完成时，必须使所有的数据保持一致状态
• 隔离性（Isolation）：多个事务之间，操作的可见性
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

MySQL事务默认自动提交

-- 查看事务的默认提交方式
SELECT @@AUTOCOMMIT;
-- 1 自动提交  0 手动提交
-- 修改事务提交方式
set @@autocommit = 0;