MySQL之其他类型数据对象

野性酷女 2023-10-03 22:51 14阅读 0赞

## 1. 常见的数据库对象 ##

![在这里插入图片描述][549d52c6c20a4c89bb62e2dc5da577d4.png]

## 2. 视图 ##

### 2.1 为什么使用视图？ ###

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。

比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他。再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段。

### 2.2 视图的理解 ###

试图是一种**虚拟表**，本身是**不具有数据**的，占用很少的内存空间，它是SQL中的一个重要概念。

**视图建立在已有表的基础上**，视图赖以建立的这些表称为**基表**。  
![在这里插入图片描述][5ba8f7089d5e4d37927c76c011c2834c.png]  
向视图提供数据内容的语句为SELECT语句，可以将视图理解为**存储起来的SELECT语句**。

视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。

视图的优点：简化查询；控制数据的访问。

### 2.3 查看视图 ###

# 语法1：查看数据库的表对象、视图对象
    SHOW TABLES;
    # 语法2：查看视图的结构
    DESCRIBE vu_empl;
    # 语法3：查看视图的属性信息
    SHOW TABLE STATUS LIKE 'vu_empl';
    # 语法4：查看视图的详细定义信息
    SHOW CREATE VIEW vu_empl;

### 2.4 更新视图数据与视图的删除 ###

# 方式1
    CREATE OR REPLACE VIEW vu_empl
    AS
    SELECT employee_id, last_name, salary, email
    FROM emps
    WHERE salary > 7000;
    
    # 方式2
    ALTER VIEW vu_empl
    AS
    SELECT employee_id, last_name, salary, email, hire_date
    FROM emps;

### 2.5 删除视图 ###

DROP VIEW vu_emp4

## 3. 存储过程 ##

### 3.1 含义 ###

含义：一组经过**预先编译**的SQL语句的封装。

### 3.2 存储过程和视图、函数的对比 ###

存储过程是程序化的SQL，可以**直接操作底层数据表**，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是**没有返回值**的。

### 3.3 创建存储过程 ###

类型一：

# 1. 创建存储过程
    # 举例1：创建存储过程select_all_data(),查看employees表的所有数据
    DELIMITER $
    CREATE PROCEDURE select_all_data()
    BEGIN
    	SELECT * FROM employees;
    END $
    DELIMITER ;
    # 2. 存储过程的调用
    CALL select_all_data();

类型二：带OUT

# 1. 创建存储过程show_min_salary(),查看"emps"表的最低薪资值。并将最低薪资值通过OUT参数"ms"输出
    DELIMITER //
    CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
    BEGIN
    	SELECT MIN(salary) INTO ms
    	FROM employees;
    END //
    DELIMITER ;
    # 2. 调用
    CALL show_min_salary(@ms);

类型三：带IN

# 1. 创建存储过程show_someone_salary(),查看"emps"表的某个员工的薪资，并用IN参数empname输入员工姓名
    DELIMITER//
    CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
    BEGIN
    	SELECT salary FROM employees
    	WHERE last_name = empname;
    END //
    DELIMITER ;
    
    # 调用方式1
    CALL show_someone_salary('Abel');
    # 调用方式2
    # =在SQL中表示相等逻辑判断，用:=表示赋值
    SET @empname := 'Abel';
    CALL show_someone_salary(@empname);

## 4. 存储函数 ##

举例1：创建存储函数，名称为email by name(), 参数定义为空，  
该函数查询Abel的email，并返回，数据类型为字符串型

DELIMITER //
    
    CREATE FUNCTION email_by_name()
    RETURNS VARCHAR(25)
    	DETERMINISTIC
    	CONTAINS SQL
    	READS SQL DATA
    BEGIN
    	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
    END //
    
    DELIMITER ;

举例2：创建存储函数，名称为email by id(), 参数传入emp\_id,该函数查询emp\_id的email，并返回，数据类型为字符类型

# 创建函数前执行此语句,保证函数的创建会成功
    SET GLOBAL log_bin_trust_function_creators = 1;
    
    DELIMITER //
    
    CREATE FUNCTION email_by_id(emp_id INT)
    RETURNS VARCHAR(25)
    
    BEGIN
    	RETURN(SELECT email FROM employees WHERE employee_id = emp_id);
    END //
    
    DELIMITER ;

## 5. 存储函数和存储过程的对比 ##

![在这里插入图片描述][18f049ee9f5942e2943d64f34dab73da.png]

## 6. 存储过程、存储函数的查看 ##

# 方式一：使用SHOW CREATE语句查看存储过程和函数的创建信息
    SHOW CREATE PROCEDURE show_mgr_name;
    SHOW CREATE FUNCTION count_by_id;
    # 方式二：使用SHOW STATUS语句查看存储过程和函数的状态信息
    SHOW PROCEDURE STATUS;
    SHOW PROCEDURE STATUS LIKE 'show_max_salary';
    SHOW FUNCTION STATUS LIKE 'email_by_id';
    # 方式三：从information_schema.Routines表中查看存储过程和函数的信息
    SELECT * FROM information_schema.Routines
    WHERE ROUTINE_NAME = 'email_by_id' AND ROUTINE_TYPE = 'FUNCTION';
    SELECT * FROM information_schema.Routines
    WHERE ROUTINE_NAME = 'show_min_salary' AND ROUTINE_TYPE = 'PROCEDURE';

## 7. 优缺点 ##

优点：

1.  存储过程可以一次编译多次使用
2.  可以减少开发工作量
3.  存储过程的安全性强
4.  可以减少网络传输量
5.  良好的封装性

缺点：

1.  可移植性差
2.  调试困难
3.  存储过程的版本管理很困难
4.  它不适合高并发的场景

## 8. 变量 ##

MySQL数据库中，变量分为**系统变量**和**用户自定义变量**。

### 8.1 系统变量 ###

#### 8.1.1 分类 ####

系统变量分为**全局系统变量**和**会话系统变量**。

#### 8.1.2 查看系统变量 ####

# 查看所有全局变量
    SHOW GLOBAL VARIABLES;
    # 查看所有会话变量
    SHOW SESSION VARIABLES;
    # 查看指定的全局变量（举例）
    SELECT @@global.max_connections;
    # 查看指定的会话变量（举例）
    SELECT @@session.max_connections;

#### 8.1.3 修改系统变量 ####

# 全局系统变量：
    # 方式1：
    SET @@global.max_connections = 161;
    # 方式2：
    SET GLOBAL max_connections = 171;
    #针对于当前的数据库实例是有效的，一旦重启mysql服务，就失效了。
    
    # 会话系统变量：
    # 方式1：
    SET @@session.character_set_client = 'gbk';
    # 方式2：
    SET SESSION character_set_client = 'gbk';
    #针对于当前的会话是有效的，一旦结束会话，重新建立新的会话，就失效了。

### 8.2 用户变量 ###

#### 8.2.1 分类 ####

用户变量是用户自己定义的，作为MySQL编码规范，MySQL中的用户变量以一个“@”开头。根据作用范围不同，又分为**会话用户变量**和**局部变量**。

1.  会话用户变量：作用域和会话变量一样，只对**当前连接**会话有效。
2.  局部变量：只在BEGN和END语句块中有效。局部变量只能在**存储过程和函数**中使用。

#### 8.2.2 会话用户变量 ####

/*
    1. 变量的声明和赋值：
    # 方式1：“=”或“：=”
    SET @用户变量 = 值;
    SET @用户变量 ：= 值;
    # 方式2：“：=”或INTO关键字
    SELECT @用户变量 ：= 表达式 [FROM 等子句];
    SELECT 表达式 INTO @用户变量 [FROM 等子句];
    
    2. 使用
    SELECT @变量名
    */
    # 方式1：
    SET @m1 = 1;
    SET @m2 := 2;
    SET @sum := @m1 + @m2;
    # 方式2：
    SELECT @count := COUNT(*) FROM employees;
    SELECT AVG(salary) INTO @avg_sal FROM employees;

#### 8.2.3 局部变量 ####

/*
    1. 局部变量必须满足：1）使用DECLARE声明 2）声明并使用在BEGIN...END中(使用在存储过程、函数中) 3) DECLARE的方式声明的局部变量必须声明在BEGIN中的首行的位置。
    2. 声明格式：DECLARE 变量名 类型 [default值]；#如果没有DEFAULT子句，初始值为NULL。
    3. 赋值：
    方式1:
    SET 变量名=值;
    SET 变量名:=值;
    方式2:
    SELECT 字段名或表达式 INTO 变量名 FROM 表;
    4. 使用
    SELECT 局部变量名;
    */
    
    # 举例：
    DELIMITER //
    CREATE PROCEDURE test_var()
    BEGIN 
    	# 1. 声明局部变量
    	DECLARE a INT DEFAULT 0;
    	DECLARE b INT;
    	#DECLARE a,b INT DEFAULT 0;
    	DECLARE emp_name VARCHAR(25);
    
    	# 2. 赋值
    	SET a = 1;
    	SET b := 2;
    
    	SELECT last_name INTO emp_name FROM employees WHERE employee_id = 101;
    
    	# 3. 使用
    	SELECT a,b,emp_name;
    END //
    DELIMITER ;
    
    # 调用存储过程
    CALL test_var();

## 9. 程序出错的处理机制 ##

### 9.1 定义条件 ###

格式：DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

举例1：定义“Field\_Not\_Be\_NULL"错误名与MySQL中违反非空约束的错误类型是"ERROR 1048(23000)"对应。

# 方式1：使用MySQL_error_code
    DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
    # 方式2：使用sqlstate_value
    DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

### 9.2 定义处理程序 ###

格式：DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

## 10. 对比三种循环结构 ##

1.  这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称
2.  LOOP:一般用于实现简单的“死”循环  
    WHILE:先判断后执行  
    REPEAT:先执行后判断，无条件至少执行一次

## 11. 游标 ##

游标使用的步骤：1. 声明游标 2. 打开游标 3. 使用游标（从游标中获取数据）4. 关闭游标

## 12. 触发器 ##

定义：由**事件来触发**某个操作，这些事件包括**INSERT, UPDATE, DELETE**事件。  
![在这里插入图片描述][3a2ddb74b6124fc794e0486229b903ef.png]

## 13. 窗口函数 ##

需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数更好。

窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。

参考资料：[MySQL数据库教程天花板][MySQL]

[549d52c6c20a4c89bb62e2dc5da577d4.png]: https://img-blog.csdnimg.cn/549d52c6c20a4c89bb62e2dc5da577d4.png
[5ba8f7089d5e4d37927c76c011c2834c.png]: https://img-blog.csdnimg.cn/5ba8f7089d5e4d37927c76c011c2834c.png
[18f049ee9f5942e2943d64f34dab73da.png]: https://img-blog.csdnimg.cn/18f049ee9f5942e2943d64f34dab73da.png
[3a2ddb74b6124fc794e0486229b903ef.png]: https://img-blog.csdnimg.cn/3a2ddb74b6124fc794e0486229b903ef.png
[MySQL]: https://www.bilibili.com/video/BV1iq4y1u7vj/?p=95&spm_id_from=333.880.my_history.page.click&vd_source=a1af903e57688df870625021f7f3a7eb