封装方法 事务管理 封装存储函数 索引

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

1./*封装方法*/

create PROCEDURE p2()
BEGIN
    SELECT id from user;
end;
call p2();
DROP PROCEDURE p2;
    

DROP PROCEDURE IF EXISTS findData;
create procedure findData3(IN name1 VARCHAR(255))
begin
#set @'name'=cid
#set @a=cid;
select * from user where name=name1;
end;
call findData3("王航波");

drop PROCEDURE if EXISTS finddate1;
CREATE PROCEDURE finddate1(in cid varchar(200))
BEGIN
set @a=cid;
SELECT * FROM user where name=@a;
end;
call finddate1("王航波")

2.

什么是事务？

事务(TRANSACTION)，就是mysql的一个具有完整逻辑的sql组，这个组里包含多个sql操作。这些sql必须同时执行，这个逻辑才算完成。要么，就回滚(rollback)到事务执行之前的数据。

最开始学习事务的时候，老师习惯讲到银行转账的情景。即a用户给b用户转账，需经过a用户扣款，b用户到账。如果在a用户扣款之后，环节发生了错误，需要a用户扣款取消，否则数据会出现a用户扣款，b用户未到账，金额的总数出现了错误。此时就需要在转账这个操作中加入事务，要么同时执行（a扣款，b到账）成功，否则回滚(a扣款，异常错误，b账号未到账，取消a扣款)。这也是事务中特性的完整性、一致性的概念。

重要：mysql的myisam类型引擎不支持事务。

事务的特性

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务

DCL用来控制数据库的访问，包括如下SQL语句：

GRANT：授予访问权限

REVOKE：撤销访问权限

COMMIT：提交事务处理

ROLLBACK：事务处理回退

SAVEPOINT：设置保存点

LOCK：对数据库的特定部分进行锁定

1、事务

思考：我去银行给朋友汇款,我卡上有1000元,朋友卡上500元,我给朋友转账50元(无手续费),如果,我的钱刚扣,而朋友的钱又没加时,网线断了,怎么办?

1.1事务的ACID特性

原子性(Atomicity)：原子意为最小的粒子，或者说不能再分的事物。数据库事务的不可再分的原则即为原子性。

组成事务的所有查询必须：要么全部执行，要么全部取消（就像上面的银行例子）。

一致性(Consistency)：指数据的规则,在事务前/后应保持一致

隔离性(Isolation)：简单点说，某个事务的操作对其他事务不可见的.

持久性(Durability)：当事务提交完成后，其影响应该保留下来，不能撤消

/*
事务管理
*/
-- 自动提交
 show variables like 'autocommit';
-- 关闭自动提交
 set autocommit=0;
set autocommit=1;
-- 开始事务
BEGIN;
-- 修改
update emp set sal=sal+500 where empno=7369;
SELECT * from emp;
update emp set sal=sal-500 where empno=7499;
-- 回滚
rollback; 
-- 提交
update emp set sal=sal+500 where empno=7369;
SELECT * from emp;
update emp set sal=sal+500 where empno=7499;
COMMIT;
-- 还原点
BEGIN;
#插入一条记录
INSERT INTO emp VALUES
(8010, '1','2',7499, '2020-10-23', 8000, 500,40,'1');
#保存还原点1
savepoint point1;
#插入一条记录
INSERT INTO emp VALUES
(8011, '1','2',7499, '2020-10-23', 8000,200,40,'1');
#保存还原点2
savepoint point2;
#查看当前效果
select * from emp;
#回滚到某个还原点
rollback to point1;
rollback to point2;
#提交事务
commit;

BEGIN;
SELECT * FROM student;
INSERT INTO `2411`.`student`(`id`, `NAME`, `gender`, `birthday`, `address`, `weight`, `phone`, `description`) VALUES (2, 'aaa', '女', '2024-03-14', 'xxx', 20, '456546', 'sdfsdf')

#清空表
truncate student;
#回滚，对于truncate无法回滚
rollback;

#修改表结构
alter table student add description varchar(50);
#回滚，对于修改表结构的语句无法回滚
rollback;

SET GLOBAL log_bin_trust_function_creators = 1;//日志权限 可以让你调用不安全函数
3.
-- 封装存储函数（只能在查询中用）
-- 创建一个名为t1的存储函数

CREATE FUNCTION t1()
RETURNS VARCHAR(50)
BEGIN
RETURN "hello,world";
end;
-- 函数的调用使用select
SELECT t1();

CREATE FUNCTION getavg(num1 int,num2 int)
RETURNS int
return (num1+num2)/2;

SELECT getavg(2,4);

CREATE FUNCTION fun_name1()
 returns int--声明返回值的数据类型*注意在里不能加分号
 begin
 DECLARE num int;--函数前边变量不能加@；加@是全局变量，不加为局部；
 
 
 SELECT COUNT(*) INTO num FROM emp;
 RETURN num;--返回返回值
 end;
 
 SELECT fun_name1();
 
4.
/*索引*/
1.
-- 开启
set profiling = on;
-- 查看索引状态
show variables like 'profiling';
2
-- 建立一个查询用来对比
SELECT * FROM emp where job='程序员';
SELECT * FROM emp where sal>1;
3.
-- 查看索引
SHOW PROFILEs;
show index from emp;
4.
-- 建立索引
CREATE INDEX job1 on emp(job);
CREATE INDEX sal1 on emp(sal);
5.
-- 用索引字段进行查询
SELECT * FROM emp where job<>'程序员';
SELECT * FROM emp where sal1;
6.
-- 删除索引
drop INDEX job1 on emp;
drop INDEX sal1 on emp;
7.
-- 对比
SHOW PROFILEs;