MySQL复习（四）

一、存储过程

1、储存过程的理解

假设要开发一个使用数据库的应用程序，如果将SQL语句内嵌在应用程序的代码里，将使其混乱且难以维护，所以应该将SQL代码和应用程序代码分开，将 SQL代码储存在所属的数据库中，具体来说，是放在储存过程（stored procedure）和函数中。

存储过程是一个由许多SQL语句组成的函数，用于访问数据库系统。几个SQL语句被合并到一个存储过程中【模块化】，并在需要时随时随地执行它们，从而节省了时间并避免了重复编写代码。

储存过程是一个包含SQL代码模块的数据库对象，在应用程序代码中，我们调用储存过程来获取和保存数据。

好处1：使用储存过程来储存和管理SQL代码。

好处2：大部分DBMS会对储存过程中的代码进行一些优化，因此有时储存过中的SQL代码执行起来会更快。

好处3：就像视图一样，储存过程能加强数据安全。比如，我们可以移除对所有原始表的访问权限，让各种增删改的操作都通过储存过程来完成，然后就可以决定谁可以执行何种储存过程，用以限制用户对我们数据的操作范围，例如，防止特定的用户删除数据。

缺点：存储过程的唯一缺点是它只能在数据库中执行，并占用数据库服务器中更多的内存。

2、储存过程的创查增删改

DROP PROCEDURE IF EXISTs get clients by_state;
--注意DROP语句删除储存过程时直接指明要删除的储存过程名就行了，不涉及参数的问题所以不需要带括号
--但创建CREATE和调用CALL储存过程时需要加括号指明储存过程有没有参数或有什么参数

DELIMITER $$

CREATE/UPDATE PROCEDURE:get clients by_state
(
state CHAR(2) --参数的数据类型
)
BEGIN
    SELECT *
    FROM clients c
    WHERE c.state = state;-- 注意区分参数和列名
END$$

DELIMITER ;


CALL get_invoices_with_balance();

3、注意

• 储存过程主体中所有语句都要以 ; 结尾并且因此要暂时修改SQL本身的默认分隔符，这些都是MySQL的特性，在 SQL Server等就不需要这样
• 储存过程的调用更多是在应用程序代码（可能是C#、JAVA 或 Python 编写的）中调用。
• 注意要调用储存过程并使用其默认值时时要传入参数 NULL ，MySQL不允许不传参数

二、触发器

是在插入、更新、删除语句前后自动执行的一堆SQL代码，用于对表执行特定操作。

目的：保持数据的一致性

最好将删除和创建数据库/视图/储存过程/触发器的语句放在同一个脚本中（即将删除语句放在创建语句前，DROP IF EXISTS + CREATE，用于创建或更新数据库/视图/储存过程/触发器，等效于 CREATE OR REPLACE，但分成了 两个语句）并将脚本录入源码库中，这样不仅团队都可以创建相同的数据库，还都能查看数据库的所有修改历史

DELIMITER $$

DROP TRIGGER [IF EXISTS] payments_after_insert

CREATE 【TRIGGER】【payments_after_insert】--命名习惯
    【AFTER INSERT ON payments -- 触发条件语句
    FOR EACH ROW】-- 触发频率语句
BEGIN
    UPDATE invoices
    【SET payment total = payment_total + NEW.amount
    WHERE invoice id = NEW.invoice_id】;
    -- 注意 NEW/OLD 的使用

    -- 添加至日志表，以记录谁在什么时间做了什么修改
    INSERT INTO payments_audit 
    VALUES (NEW.client_id, NEW.date, NEW.amount,'insert', NOW()); 
END$$

DELIMITER ;

SHOW TRIGGERS LIKE 'payments%' 

几个关键点:

1.命名习惯(三要素):触发表 before/after(表示SQL语句执行之前或之后触发)触发的SQL语句类型

2.触发条件语句:BEFORE/AFTER INSERT/UPDATE/DELETE ON 触发表

3.触发频率语句:这里 FOR EACH ROW 表明每一个受影响的行都会启动一次触发器。

4.主体:主体里可以对各种表的数据进行修改以保持数据一致性，但注意唯一不能修改的表是触发表，否则会引发无限循环(“触发器自燃”)，主体中最关键的是使用 NEW/OLD 关键字来指代受影响的新/行(若INSERT用NEW，若DELETE用OLD，若UPDATE似乎理论上两个都可以用，但应该业主要用NEW)并可跟'点+字段'地方式来引用这些行的相应属性。

三、索引

简单描述 MySQL 中索引、唯一索引、主键、联合索引的区别，对数据库的性能有什么影响（从读写两方面）？

索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

（1）普通索引的唯一任务是加快对数据的访问速度，允许被索引的数据列包含重复的值。

（2）唯一索引中，每条数据记录都是唯一的，创建唯一索引用关键字 UNIQUE.如果列是唯一索引的，则此索引不允许字段具有重复的值，包括NULL值。如果定义了主键，则可以自动应用唯一索引。

（3）主键是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY来创建。

（4）联合索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。

聚集索引：

该索引对表的物理顺序进行重新排序，并根据键值进行搜索。每个表只能有一个聚集索引。

非聚集索引：

非聚集索引不会更改表的物理顺序，并且会保持数据的逻辑顺序。每个表可以具有许多非聚集索引。

B选项，每个表只允许有一个全文索引；每个表通常只允许有一个全文索引，因为全文索引需要额外的存储空间和计算资源，而且一般情况下一个全文索引就可以满足大部分需求

C选项，为提高效率可建立组合索引，遵循“最左前缀”原则.组合索引按照“最左前缀”原则进行匹配和查询，即在查询中使用的索引列的左侧列依次匹配，而不是随意组合。这样设计可以提高索引的效率。

D选项，非聚集索引具有完全独立于数据行的结构，所以不需要将物理数据页中的数据按列重新排序。D选项不完全正确。非聚集索引是一种数据结构，它存储了数据行的引用，而不是数据行本身。虽然非聚集索引的结构与数据行的物理存储无关，但是在实际使用中，索引的维护和数据行的物理存储仍然会相互影响。索引的建立和维护可能会导致数据页的重新排序或重组，以提高查询效率。

一文搞懂MySQL索引（清晰明了）-CSDN博客

四、用户定义函数

• 用户定义函数不用于执行修改数据库状态的操作,用户定义函数通常用于执行一些计算、数据转换或者查询操作，而不是用于修改数据库状态的操作。数据库状态修改的操作一般通过SQL语句INSERT、UPDATE、DELETE等命令完成，而不是通过用户定义函数。
• 用户定义函数（UDF）一般属于创建它们的数据库，并且主要在该数据库下使用。然而，在许多数据库系统中，用户定义函数可以在跨数据库的范围内使用，前提是访问权限和架构允许。

• EXECUTE语句用于执行数据库中的可执行模块，这包括存储过程、标量函数以及其他一些类型的可执行代码。
• 与系统函数一样，用户定义函数可以从查询中调用【系统里有的所有函数就是系统函数】用户定义函数（UDF）可以在SQL查询中使用，如SELECT、WHERE、ORDER BY、GROUP BY等语句中。

关于删除：

1：处理效率：drop>trustcate>delete

2：删除范围：drop删除整个表（结构和数据一起删除）；trustcate删除全部记录，但不删除表结构；delete只删除数据

3：高水位线：delete不影响自增ID值，高水线保持原位置不动；trustcate会将高水线复位，自增ID变为1。

其他：

TRUNCATE(1.99,1)对前面参数进行截取操作，截至小数点后一位；

LIMIT是整个的LIMIT；

ORDER BY 是在SELECT之后；

having子句即可包含聚合函数作用的字段也可包括普通的标量字段

ORDER BY 默认升序排序

聚合函数之间不能嵌套使用

行列转换

一篇文章解析mysql的 行转列（7种方法） 和 列转行_mysql 行转列-CSDN博客