MySQL数据库中的其他结构 By 尚硅谷

一、约束

数据完整性：数据的精确性和可靠性。防止数据库中存在不符合语义规定的数据和防止因错误信息的输入造成无效操作或错误信息而提出的。

分类：

• 实体完整性
• 域完整性
• 引用完整性
• 用户自定义完整性

约束的作用场景：

• 创建表时：添加约束
• 修改表时：修改、删除约束

1. 非空约束（not null）

-- 使用场景：添加非空约束
CREATE TABLE test1(
	id INT NOT NULL,
    last_name VARCHAR(15) NOT NULL,
    email VARCHAR(25),
    salary DECIMAL(10,2)
);
-- 使用场景：删除非空约束
ALTER TABLE test1 
MODIFY email VARCHAR(25) NULL;

2. 唯一性约束（unique）

-- 使用场景：创建表的时候添加约束
CREATE TABLE test1(
	id INT UNIQUE,
    last_name VARCHAR(15),
    email VARCHAR(25) UNIQUE,
    salary DECIMAL(10,2)
);

-- 使用场景：修改表时添加约束
ALTER TABLE test1 
MODIFY email VARCHAR(25) UNIQUE;

-- 使用场景：删除唯一性约束
ALTER TABLE test1 
DROP INDEX email;

3. 主键约束（primary key）

主键约束相当于唯一性约束 + 非空约束 + 只能声明一个

-- 使用场景：创建表的时候声明主键约束
CREATE TABLE test1(
	id INT PRIMARY KEY,
    last_name VARCHAR(15),
    email VARCHAR(25),
    salary DECIMAL(10,2)
);

-- 使用场景：在修改表时添加主键约束
ALTER TABLE test1 
ADD PRIMARY KEY (id);

-- 使用场景：删除主键约束（不用）
ALTER TABLE test1 
DROP PRIMARY KEY;

设置主键自增：AUTO_INCREMENT

-- 创建时添加主键自增
CREATE TABLE test1(
	id INT PRIMARY KEY AUTO_INCREMENT,
    last_name VARCHAR(15),
    email VARCHAR(25),
    salary DECIMAL(10,2)
);

-- 修改时添加主键自增
ALTER TABLE test1 
MODIFY id INT AUTO_INCREMENT;

-- 删除主键自增
ALTER TABLE test1 
MODIFY id INT;

4. 外键约束（foreign key）

说明：可以在应用层或者程序后端进行数据写入的约束，这意味着我们在使用数据库的时候不一定要使用外键约束。而且外键使用的时候会影响数据库的性能，为了规范也不建议使用外键约束。

-- 前期准备
CREATE TABLE dept1(
	dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(25)
);

-- 在创建时添加
CREATE TABLE emp1(
	emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCAHR(15),
    department_id INT,
    
    # 表级约束 
    # fk_emp1_dept_id：外键约束名
    # department_id：作为外键约束的字段
    # dept1：主表
    # dept_id：主表的主键或者唯一性字段
    CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
);

-- 删除外键约束
ALTER TABLE emp1 
DROP FOREIGN KEY fk_emp1_dept_id;

-- 删除外键约束对应的普通索引
	# 查看索引
	# emp1：表名
	SHOW INDEX FROM emp1;
# department_id：索引名
ALTER TABLE emp1 
DROP INDEX department_id;

约束等级（了解）：

约束的等级不同会导致能够修改的范围也不同

5. 检查约束（check）

检查是否在某些范围（不用！）

6. 默认值约束（default）

默认值约束、设置默认值（不用！）

-- 使用场景：创建时使用
CREATE TABLE test1(
	id INT PRIMARY KEY AUTO_INCREMENT,
    last_name VARCHAR(25),
    salary DECIMAL(10,2) DEFAULT 2000
);

二、视图（主要用于查询）

常见的数据库对象

对象	描述
表	表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录
数据字典	就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看
约束	执行数据校验的规则，用于保证数据完整性的规则
视图	一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
索引	用于提高查询性能，相当于书的目录（哈希）
存储过程	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调 用环境
存储函数	用于完成一次特定的计算，具有一个返回值
触发器	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

视图：

• 看做一个虚拟表，视图select涉及到的表称为基表

• 视图中做增删改查操作会影响基表

• 视图的删除不会影响基表

• 视图优点：操作简单、减少数据冗余、数据安全、适应灵活多变的需求、能够分解复杂的查询逻辑。

1. 创建视图

语法：

CREATE VIEW 视图名(列名1,列名2)

AS

查询语句;

-- 单表创建视图
CREATE VIEW vu_emp 
AS 
SELECT emp_id,last_name,salary 
FROM emps;

-- 多表创建视图
CREATE VIEW vu_emp_dept 
AS 
SELECT e.emp_id d.dept_id d.dept_name 
FROM emps e JOIN dept d 
ON e.emp_id = d.dept_id;

-- 基于视图创建视图

2. 查看视图

-- 查看所有表和视图
SHOW TABLES;
-- 查看视图结构
DESCRIBE vu_emp;
-- 查看视图的属性信息
SHOW TABLE STATUS LIKE 'vu_emp';
-- 查看视图的详细定义信息
SHOW CREATE VIEW vu_emp;

3. 更新视图

语法：

UPDATE 视图名
SET 修改信息
WHERE 过滤信息;

UPDATE vu_emp 
SET salary = 20000 
WHERE emp_id = 10;

4. 删除视图

DROP VIEW vu_emp;

三、存储过程、存储函数

相当于自己写SELECT关键字的逻辑

看资料

语法：

DELIMITER $

CREATE PROCEDURE 存储过程名(IN OUT INOUT 参数名 参数类型,...)[characteristics ...]
BEGIN
存储过程体
END $

DELIMITER ;

调用：

CALL 存储过程方法();

四、变量、流程控制、游标（存储函数的使用细节）

看资料

变量

• 查看系统变量（全局系统变量、会话系统变量）
• 修改系统变量
• 用户变量分类（会话用户变量、局部用户变量）

注意：1. 全局–> 当前服务 2. 会话 --> 一次sql连接 3. 局部变量 --> 一个存储过程、一个存储函数

定义条件与处理程序

• 定义条件：给错误码命名，让错误更清晰
• 定义处理程序：发生某种错误定义的特殊处理程序
  处理方式：CONTINUE、EXIST、UNDO

流程控制

• 分支结构
• 顺序结构
  • IF else语句
  • CASE WHEN THEN语句
• 循环结构
  • LOOP语句
  • WHILE DO语句
  • REPEATE UNTILE语句
    • LEAVE跳出当前循环
    • ITERATE继续当前循环

游标

1. 声明游标（一个萝卜一个坑）
2. 打开游标
3. 使用游标
4. 关闭游标

五、触发器

触发器属于自动运行的一些操作，由此实现一些操作的并发（原子性）

1. 创建触发器
2. 查看触发器
3. 修改触发器
4. 删除触发器