MySQL进阶操作

一、存储过程

1、概述

        存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化开发工作，减少数据在数据库与应用服务器之间的传输，提高数据处理效率。就是数据库SQL语言层面的代码封装与调用

2、存储过程有什么好处

        提高性能：存储过程通常在数据库服务器上进行编译和优化，因此它们可以提供更快的执行速度。一旦存储过程被编译，多次调用它将比每次单独发送SQL查询更高效。

        减少网络流量：由于存储过程在数据库服务器上执行，而不是在客户端执行，因此减少了网络流量。这对于远程连接或大型数据集特别有用。

        提高数据安全性：存储过程可以控制对数据库中数据的访问权限。通过存储过程，可以实现访问控制、数据验证和数据权限控制，从而增强数据的安全性。

        封装业务逻辑：存储过程可以包含复杂的业务逻辑，将应用程序的业务逻辑封装在数据库层，从而降低了应用程序的复杂性。这也可以提高代码的可维护性。

        减少SQL注入风险：通过使用存储过程，可以减少SQL注入攻击的风险，因为存储过程可以参数化输入，而不是直接将用户输入的数据拼接到SQL查询中。

        支持批处理：存储过程可以处理批处理操作，允许一次性执行多个SQL语句，从而减少通信开销和提高效率。

        提高一致性：通过使用存储过程，可以确保多个应用程序使用相同的逻辑来操作数据，从而提高数据的一致性。

        降低维护成本：一旦存储过程被创建和测试，它们可以在多个应用程序中共享，从而降低了维护成本。

3、怎么定义、调用存储过程

-- 创建存储过程语法
    create procedure 存储过程名称([参数列表])
    begin
        --SQL语句
    end；

-- 调用存储过程语法
    call 名称([参数])

-- 查看存储过程信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA="db_company";
-- 查看存储过程的创建语句
show create procedure p1;

-- 删除存储过程
drop procedure if exists p1;

举例： 

-- 定义存储过程
create procedure p1()
begin
		select count(*) num from db_company.stu;
end;

-- 调用存储过程
call p1();

4、存储过程--if判断语法

-- 定义存储过程
create procedure p3()
begin
    -- 申明变量 score、 result 
    declare score int default 50;
    declare result varchar(10);
    if score >=80 then
            set result:='优秀';
    elseif score >=60 then
            set result:='及格';
    else
            set result:='不及格';
    end if;
    -- 查看result
    select result;
end;
-- 调用存储过程p3
call p3();

5、存储过程--参数

    参数分类：
    in：该类参数作为输入（默认类型）
    out：该类参数作为输出
    inout：该类参数作为输入，也可以做输出
    

案例1（in、out）：

 create procedure p4(in score int,out result varchar(10))
    begin
            if score >=80 then
                    set result:='优秀';
            elseif score >= 60 then
                    set result:='及格';
            else
                    set result:='不及格';
            end if;
    end;
    -- 调用存储过程
    call p4(68,@result); -- 定义自义变量接收返回值

案例2（inout）： 

create procedure p5(inout score double)
begin
		set score := score /200*100;
end;

-- 先定义score
set  @score=78;
-- 调用存储过程
call p5(@score);-- 通过@score传递参数,计算后复制给@score

6、存储过程--case语法

-- 定义存储过程
create procedure p6(in month int,out quarter varchar(10))
begin
    case
        when month>=1 and month<=3 then
                set quarter ='一季度';
        when month>=4 and month<=6 then
                set quarter ='二季度';
        when month>=7 and month<=9 then
                set quarter ='三季度';
        when month>=10 and month<=12 then
                set quarter :='四季度';
        else
                set quarter ='输入错误';
        end case;
end;
-- 调用存储过程
call p6(5,@quarter);-- 定义自义变量接收返回值

7、存储过程--while循环语法

-- 定义存储过程
create procedure p7(in num int,out result int)
begin
        set result:=0;
        while num >0
                do
                    set result:=result+num;
                    set num :=num -1;
                end while;
end;

-- 调用存储过程
call p7(10,@result);

8、存储过程--游标

        MySQL 支持游标（Cursor）用于在存储过程（Stored Procedure）或函数中处理查询结果集。游标是一种数据库对象，可以遍历查询的结果集并逐行处理数据。游标通常用于需要在结果集上进行逐行操作的情况，例如在存储过程中执行数据逻辑或迭代结果集。

-- 声明游标语法：declare 游标名称 cursor for 查询语句;
-- 打开游标：open 游标名称;
-- 获取游标记录：fetch 游标名称 into 变量...;
-- 关闭游标：close 游标名称;

-- 定义存储过程
create procedure p11(in num int)
begin
        -- 声明变量
        declare name varchar(10);
        declare job varchar(10);
        declare salary int;
        -- 声明游标语法
        declare cursor_001 cursor for select ename,job,sal from db_company.emp where emp.deptno <=num;

        -- 创建新表
        drop table if exists tab_user;
        create table tab_user
        (
                id int primary key auto_increment,
                name varchar(10),
                job    varchar(10),
                salary int
        );
        -- 打开游标
        open cursor_001;

        while true
                do
                        -- 获取游标记录,从游标每一条记录中循环获取各个字段的值
                        fetch cursor_001 into name,job,salary;
                        -- 插入数据到新表中
                        insert into tab_user values(null,name,job,salary);
                end while;

        -- 关闭游标
        close cursor_001;
end;

-- 调用存储过程
call p11(10);

9、存储过程--存储函数

        MySQL 存储函数（Stored Functions）是一种数据库对象，它们是预先定义的一系列 SQL 语句和逻辑操作，可用于返回一个单一的值。存储函数通常接受参数，执行一些操作，然后返回一个结果。与存储过程类似，存储函数允许你在数据库层面封装和重用逻辑，以便在查询中调用。

语法：

CREATE FUNCTION function_name ([parameter1 data_type, parameter2 data_type, ...]) RETURNS return_data_type
BEGIN
    -- 存储函数的逻辑操作
    -- 可以包括变量声明、控制流语句、SQL查询等
    -- 使用 RETURN 语句返回结果
    RETURN result_value;
END;

案例： 

-- 创建存储函数
create function fun1(num int)-- 参数只能是int类型
		returns int deterministic
begin
		declare result int default0;
		while num >0
				do
						set result:=result+num;
						set num :=num -1;
				end while;
		return result;
end;
-- 调用存储函数
select fun1(10);

 9、存储过程--条件处理程序（handler）

用于定义在流程控制结构执行过程中遇到问题时相应处理步骤，语法如下：

DECLARE handler_type HANDLER FOR condition_value
BEGIN
    -- 处理异常的逻辑操作
END;

-- DECLARE handler_type HANDLER: 这是用于声明条件处理程序的关键字。handler_type 可以是 CONTINUE、EXIT 或 UNDO，分别表示继续执行、中止执行或回滚操作。你可以根据需要选择适当的处理程序类型。
-- FOR condition_value: 在 FOR 关键字后指定要处理的条件，condition_value 是一个具体的异常条件或 SQLSTATE 值。条件通常包括 SQL 异常、警告或自定义异常。
-- BEGIN 和 END: 条件处理程序的主体位于 BEGIN 和 END 之间。在这里，你可以定义处理异常的逻辑操作，例如记录错误、输出信息、回滚事务等。

 案例：

-- 定义存储过程
create procedure p12(in  num int)
begin
        -- 声明变量
        declare name varchar(10);
        declare job varchar(10);
        declare salary int;
        -- 声明游标语法
        declare cursor_001 cursor for select ename,job,sal from db_company.emp where emp.deptno <=num;
        -- 声明条件处理程序
        -- (sql报错时，有一个sqlstate。如果条件处理程序中的sqlstate与之相等，则执行推出程序并关闭游标)
        declare exit handler for SQLSTATE'02000'close cursor_001;
        -- 创建新表
        drop table if exists tab_user;
        create table tab_user
        (
                id int primary key auto_increment,
                name varchar(10),
                job    varchar(10),
                salary int
        );
        -- 打开游标
        open cursor_001;

        while true
                do
                        -- 获取游标记录,从游标每一条记录中循环获取各个字段的值
                        fetch cursor_001 into name,job,salary;
                        -- 插入数据到新表中
                        insert into tab_user values(null,name,job,salary);
                end while;

        -- 关闭游标
        close cursor_001;
end;

-- 调用存储过程
call p12(30);

二、触发器

        MySQL 触发器（Trigger）是一种数据库对象，它是与表相关联的一段代码，会在表中的特定事件发生时自动触发执行。触发器通常用于在数据库发生变化时执行额外的逻辑操作，例如插入、更新或删除数据时触发某些操作。

触发器语法：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- 触发器的逻辑操作
END;

-- CREATE TRIGGER: 这是用于创建触发器的 SQL 语句的起始部分。
-- trigger_name: 触发器的名称，你可以自定义触发器的名称。
-- {BEFORE | AFTER}: 触发器可以在数据操作之前（BEFORE）或之后（AFTER）触发执行。BEFORE 触发器允许你在数据操作之前干预和修改数据，而 AFTER 触发器用于在数据操作完成后执行其他逻辑。
-- {INSERT | UPDATE | DELETE}: 触发器可以与插入、更新或删除操作关联。你可以选择触发器与哪种数据操作相关联。
-- ON table_name: 触发器与哪个表相关联，table_name 是表的名称。
-- FOR EACH ROW: 这表示触发器会为每一行记录执行，通常用于处理单个行的变化。
-- BEGIN 和 END: 触发器的主体包括在 BEGIN 和 END 之间。这里定义了触发器的逻辑操作，例如记录数据、更新其他表、执行条件检查等。

案例1（insert）： 

-- 插入数据触发器
create trigger tab_user_insert_trigger
		after insert-- insert类型触发器,在insert之后触发触发器
		on tab_user
		for each row-- 行级触发器
begin
		-- 编写触发器逻辑
		insert into tab_user_logs(id,operation,operate_time,operate_id,operate_params)
		values(null,'insert',now(),new.id,
						concat('插入的数据内容:id=',new.id,', name=',new.name,', jab=',new.job,', salary=',new.salary));
end;

案例2（update）： 

-- 更新数据触发器
create trigger tab_user_update_trigger
		after update
		on tab_user
		for each row
begin
		insert into tab_user_logs(id,operation,operate_time,operate_id,operate_params)
		values(null,'update',now(),new.id,
						concat('更新前的数据内容:id=',old.id,', name=',old.name,', jab=',old.job,', salary=',old.salary,
									  '; 更新后的数据内容:id=',new.id,', name=',new.name,', jab=',new.job,', salary=',new.salary));
end;

案例3（delete）： 

-- 删除数据触发器
create trigger tab_user_delete_trigger
		before delete
		on tab_user
		for each row
begin
		insert into tab_user_logs(id,operation,operate_time,operate_id,operate_params)values
		(null,'delete',now(),old.id,concat('删除的数据:id=',old.id,', name=',old.name,', job=',old.job,', salary=',old.salary));
end;

三、索引

        索引（index）是帮助MySQL高效获取数据的一种有序数据结构。除了数据外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指引数据，在这些数据结构上实现高级算法快速查找数据，这种数据结构就是算法。 

1、常见索引

    PRIMARY KEY 索引：用于唯一标识每行数据的索引，每个表只能有一个主键索引。
    UNIQUE 索引：确保索引列的值在整个表中是唯一的，允许一个 NULL 值。
    普通索引（Non-Unique 索引）：用于提高查询性能，但不要求唯一性。
    全文索引（Full-Text Index）：用于在文本列中进行全文搜索，允许更复杂的文本搜索操作。

2、索引优缺点

优点：

提高查询性能：索引允许数据库引擎更快地定位和检索数据，特别是在大型表中，查询性能显著提高。

加速排序：索引不仅用于加速查找操作，还用于排序操作，因此在使用 ORDER BY 子句时也提供性能优势。

唯一性约束：主键索引和唯一索引可以确保索引列的唯一性，防止重复数据的插入。

外键支持：外键通常需要与索引一起使用，以确保关联表的一致性和引用完整性。

空间索引：对于包含空间数据（如地理坐标）的列，空间索引支持空间查询。

全文搜索：全文索引允许在文本列上执行全文搜索，支持更高级的文本检索操作。

缺点：

占用存储空间：索引占用额外的存储空间，随着索引数量和大小的增加，数据库的存储需求也会增加。

写操作性能下降：插入、更新和删除操作会导致索引的维护，因此写操作性能可能下降。

维护成本：随着数据的变化，索引需要不断地维护，包括重新构建、优化和重新组织索引。这增加了数据库的维护成本。

内存要求：索引通常需要加载到内存中以提供快速查询，这可能会增加数据库服务器的内存需求。

选择正确的索引：错误的索引设计可能会导致性能问题，需要精心选择和管理索引。

过多的索引：过多的索引可能会导致混乱，使数据库变得不可维护。维护多个索引也会增加数据库的复杂性。

不适用于小表：对于小型表，索引可能不会提供显著的性能提升，并可能浪费存储空间。

3、索引语法

创建索引
语法：create [unique|fulltext] index index_name on table_name(index_col_name,….);
    Unique：表示创建的是一个唯一的索引
    fulltext：表示创建的是一个全文索引

#创建常规索引
Create index index_user_name on index_tab(name);
#创建唯一索引
Create unique index index_user_phone on index_tab(phone);
#创建联合索引
Create index index_user_pro_age on index_tab(profession,age);
#创建联合索引，指定索引的默认排序规则（默认排序规则:asc）,后续order by时按照索引的规则排序，就可以通过索引排序
Create index index_user_pro_age on index_tab(profession desc,age asc);

查看索引
语法：show index from table_name;

#查看索引
Show index from index_tab;

删除索引
语法：drop index index_name on table_name;

#删除索引
Drop index index_amail on index_tab;