MySQL的进阶使用方法

 【前言】

view
    直接对某张表的操作，就是在对数据库系统的逻辑模型层的操作。但让所有用户都看到整个逻辑模型是不合适的。出于安全的考虑，如会隐藏某个属性。这时就需要这种‘虚表’，它向用户透露一部分的数据，它不属于逻辑模型层的。我们称它为视图。视图并不是预先计算并存储的，而是在使用的时候才通过执行查询被计算出来

定义一个视图：

    create view V as <查询语句> 

create view stuent_view as
	select a.*,b.name as gradeName  from 
		student a  join 
		grade b on a.gradeId = b.id 
	where a.age>20;

                存在的视图也可以用来定义另一个视图

---

视图维护
    为保证视图中数据的一致性，必需有一些的策略对其进行维护

            第一种，数据库系统仅仅只是存储视图的查询表达式。当真正的用到该视图时才去执行这个查询。这种方式保证了数据的一致，但是如果频繁使用该视图，这种方式就不如直接存储视图内容来的划算。

            第二种，数据库系统直接存储视图的内容。为保证数据的一致性，就要求在逻辑视图更新后，相应的视图也要即时的更新。同时暴露的问题就是，如果逻辑层更新频繁，那么视图也会跟着更新。最差的情况是，你还不怎么用这个视图。

            第三种，存储内容后定期的更新视图。这种似乎综合上面两种方案。但有个致命的点是，你没法保证视图中的数据是最新的。

    使用哪种方式，不仅要根据情况来定。而且最重要的是，数据库系统是否支持你使用以上三种的维护方式

---

视图更新
    视图是一张” 虚表 “，想要这张表支持增删改，却是一个重大的问题。至少目前不能做到

【1】什么是视图

• 视图就是通过查询得到一张虚拟表，然后保持下来，下次可以直接使用

• 视图也是一张表

【2】为什么要用视图

• 如果要频繁操作一张虚拟表（拼表组成），就可以制作成视图，后续直接操作。

【3】视图的优点

• 简化复杂查询：

  • 视图可以对基本表进行复杂的操作，包括连接多个表、过滤条件、聚合函数等。

  • 通过定义视图，可以将复杂的查询逻辑封装到一个简单的视图中，使用户能够以更简洁明了的方式进行数据检索。

• 数据安全性：

  • 视图可以限制用户对数据的访问权限，通常用于隐藏敏感数据或只提供部分数据给特定的用户。

  • 通过定义视图并设置相应的权限，可以保护数据的安全性，防止未经授权的用户访问敏感信息。

• 数据完整性：

  • 视图可以用于实现数据完整性约束，即对数据的有效性进行验证。

  • 通过定义视图并添加计算列、过滤条件等约束，可以确保所返回的数据满足一定的条件，提高数据的准确性和一致性。

• 逻辑数据独立性：

  • 视图使得应用程序与数据之间解耦，即应用程序不需要了解底层表结构的细节。

  • 这样，当底层数据库发生变化时（如表结构修改），只需调整底层视图的定义而无需修改应用程序，从而提高系统的可维护性和扩展性。

• 性能优化：

  • 物化视图是一种缓存机制，可以将视图的查询结果存储在磁盘上，以提高查询性能。

  • 当基本表的数据频繁变动时，物化视图可以减少查询的计算开销和响应时间，提升系统的性能。

【4】总结

• 使用视图可以简化查询操作、保护数据安全性、实现数据完整性、提高系统的可维护性和性能优化。

• 视图提供了一种灵活安全的数据访问方式，使用户能够根据自身需要方便地获取和操作数据。

【1】语法结构

create view 视图名（表名） as 虚拟表的查询SQL语句

【2】创建视图

• 首先，要使用create view 语句创建一个视图。

  • 在创建视图时，需要指定视图的名称以及要查询的源表和过滤条件。

删除视图

drop view if exists scores_school;

create view scores_school as
    select school.course_name,scores.student_id
    from scores, school
    where scores_school.course_name = 'go';

【3】更新视图

• 如果需要对视图进行更新操作，您可以使用UPDATE语句。
  • 注意，不是所有的视图都可以进行更新，只有满足一定条件的视图才支持更新操作。
  • 以下是一个更新视图的示例语句：

UPDATE my_view SET column1 = value1 WHERE condition;

• 在上述语句中，column1是要更新的列，value1是要设置的值，condition是更新条件。

【4】删除视图

• 如果您不再需要某个视图，可以使用DROP VIEW语句将其删除。
• 示例如下：

DROP VIEW my_view;

• 上述语句将删除名为my_view的视图。

【三】总结

• 创建视图咋硬盘上只会有表结构，没有表数据
  • 表数据还是来自之前的表
• 视图一般只用来查询
  • 不建议对视图内的数据进行更改，有可能会对原始数据产生影响
• 视图的使用频率并不高
  • 当创建了较多视图后，会造成数据的难以维护

视图小结：

• 创建视图硬盘上只会有表的结构，没有表的数据

  • 表的数据还是来自之前的表

• 视图一般只用来查询

  • 不建议对视图内的数据进行更改，有可能会对原始数据产生影响

• 视图的使用频率并不高

  • 当创建较多视图后，会造成数据的难以维护。

【触发器】

【1】什么是触发器

在满足对表的数据进行增删改的情况下，自动触发的功能，称为触发器

触发器是数据库管理系统中的一个概念，它是一种在数据库中定义的特殊对象，用于在满足特定条件时触发自动化的操作或逻辑。

触发器通常与数据库表关联，当数据库发生特定的数据变化时，触发器会自动执行的相关的操作。

比如插入、更新、删除或者查询数据等操作。

【2】触发器的特点

触发器通常与表一起创建、修改和删除。

触发器可以在特定的数据之前或者之后触发执行。

触发器可以根据用户定义的条件判断是否执行相应的逻辑。

触发器可以调用存储过程、函数、触发其他触发器等，实现更复杂的业务逻辑。

【3】怎么什么情况下使用触发器

• 可以帮助我们实现日志、监控、处理等操作。

• 使用触发器可以实现很多功能

• 比如数据验证、数据补全、数据同步、日志记录

• 但需要注意，触发器的使用也需要谨慎，过多或者不当的触发器可能会对数据库性能产生负面影响，因此在设计和使用触发器时应考虑到业务需求和性能方面的平衡。

【4】触发器六种使用情况

• 增前

• 增后

• 删前

• 删后

• 改前

• 该后

【5】语法结构

create trigger 触发器的名字
before/after insert / update/delete
on 表名 for each row
begin 
    SQL语句
end

​

【6】自定义触发器

【1】创建触发器

创建一个新的触发器对象

在创建触发器时，需要指定触发器的名称、触发时机（例如在插入、更新或者删除之前或者之后）触发的表以及触发执行的

【七】参考模板

【1】在表中插入新记录时，自动生成唯一标识符

CREATE TRIGGER generate_uuid_trigger BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    SET NEW.uuid = UUID();
END;

【2】在表中更新记录时，更新最后修改时间

CREATE TRIGGER update_last_modified_trigger BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
    SET NEW.last_modified = NOW();
END;

【3】在表中删除记录时，将记录添加到历史记录表

CREATE TRIGGER archive_deleted_record_trigger AFTER DELETE ON table_name
FOR EACH ROW
BEGIN
    INSERT INTO history_table (id, deleted_at)
    VALUES (OLD.id, NOW());
END;

【4】在表中插入新记录时，检查是否满足某个条件，若不满足则取消插入操作

CREATE TRIGGER check_condition_trigger BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    IF NEW.column_name < 10 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Value must be greater than 10';
    END IF;
END;

上述模板中的"table_name"和"column_name"应替换为您实际使用的表名和列名。

【5】日志模板

# 创建语句前修改默认语句结束符
delimiter $$
# 主要是为了区分错误执行语句和全局结束语句
create trigger tri_after_insert_cmd after insert on cmd 
for each row
begin
    if NEW.success = "no" then
        insert into errlog(err_cmd,err_time)
    values(NEW.cmd,NEW.sub_time);
        end if;
end $$
# 使用完以后要修改回原来的默认语句
delimiter ;

存储过程

【1】什么是存储过程

存储过程就类似与Python中的自定义函数。

内部包含了一系列可以执行的SQL语句，存储过程在MySQL服务器中，可以通过调用存储过程触发内部的SQL语句

储存过程是在关系性数据库中的存储的一组预定的SQL语句集合，可以接收参数并返回结果。

它们被封装在数据库服务器中，并由应用程序通过调用存储过程来执行特定的数据库操作。

【2】如何使用存储过程

【1】定义存储器

create procedure 存储过程的名字(形参1,形参2...)
begin
    sql 代码
end

【2】调用

call 存储过程的名字（）；

【3】查看存储过程具体信息

show create procedure pro1;

【4】查看所有存储过程

show procedure status;

【5】删除存储过程

drop procedure pro1;

【具体演示】
（1）在MySQL中
创建存储过程
delimiter $$
​

create procedure p1(
      in m int, # in表示这个参数必须只能是传入不能被返回出去
    in n int.
    out res int, # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select name from emp where dep_id > m and dep_id <m;
    set res = 666 # 将res变量修改，用来标识当前的存储过程代码确实执行了
end $$
​
delimiter ;

使用存储过程
# 定义存储过程中的变量

set @res=10;  

​
# 查看写好的存储过程

select @res; 

​
# 调用存储过程

call p1(1,5,@res)  

​
# 查看存储过程信息

select @res;