mysql 存储过程 lock_Mysql--视图、触发器、存储过程、函数、事物、数据库锁

视图

视图:是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据

视图有如下特点;

1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。

2. 视图是由基本表(实表)产生的表(虚表)。

3. 视图的建立和删除不影响基本表。

4. 对视图内容的更新(添加、删除和修改)直接影响基本表。

5. 当视图来自多个基本表时，不允许添加和删除数据。

--创建表

create tablestudy (

idint not null auto_increment PRIMARY key,

nameVARCHAR(10) not null,

ageint);--插入单条数据--插入关键字：insert into 表名(字段名) values (值)；

insert into study(name,age) values ('jerry',18)--插入多条数据；

insert into study(name,age) values ('tom',11),

('Amy',12),

('lily',13);--查询表

select * from study;

Create data

创建视图

--创建视图

create view 视图名 as select查询语句;

使用视图

--使用视图

select * from 视图名;

更新视图

--更新视图

alter view 视图名 assql语句;--注意：更新后的语句会直接覆盖之前的信息

删除视图

--删除视图

drop view 视图名;

-- 注意 ：单视图可以进行修改查询删除操作 并直接影响实表

create view v_test as select * fromstudy;--查看视图

show create viewv_test;--使用视图

select * fromv_test;--对视图操作--更新

update v_test set name = 'haha' where id =1;select * fromstudy;select * fromv_test;--删除

DELETE from v_test where id =1;select * fromstudy;select * fromv_test;--插入

insert into v_test(name,age) value('jerry',18);select * fromstudy;select * fromv_test;--更新视图2

select * fromv_test;alter view v_test as select age fromstudy;select * fromv_test;--删除视图--只是删除了视图，并不会对实表产生影响

drop viewv_test;select * fromv_test;select * from study;

例子：单表关联视图

-- 通过连接多表产生的视图，只能进行修改操作

create table study1(id int not null auto_increment primary key,

sexVARCHAR(2) not null);--修改表属性

alter table study1 MODIFY sex VARCHAR(10) null;--插入数据

insert into study1(sex) values ('boy'),('girl');--创建多表视图

create view v_test1 as select * from study,(select sex from study1) asstudy1;--查询视图

select * from v_test1; --会出现笛卡尔乘积 是因为没有指定数据

--修改视图

update v_test1 set name = 'haha' where id =2;select * fromv_test1;select * fromstudy--删除视图内的数据

delete from v_test1 where id =2;--报错：Can not delete from join view 'day45.v_test1'

--插入数据

insert into v_test1(name,age,sex) values('tom',11,'boy');--报错：The target table v_test1 of the INSERT is not insertable-into

例子：多表关联视图

触发器

触发器：监视某种情况，并触发某种操作。

--关键字 TRIGGER--四要素：

--1、监事地点(table)

--2、监视事件(insert\update\delete)

--3、触发时间(after\before)

--4、触发事件(insert\update\delete)

-- 注意：触发器只能查看，无法修改，如果要修改，只能删除原有的触发器，重新创建触发器，以达到修改的目的。

创建触发器

--创建触发器

create trigger 触发器名 after\before(触发时间) insert\update\delete(监视事件)on table(监视地点)for each row --这是规定格式

begin

--sql语句 --触发事件

end

-- 注意1:after/before: 只能选一个 ,after 表示 后置触发, before 表示前置触发

-- 注意2:insert/update/delete:只能选一个

查看触发器

--查看触发器

show TRIGGERS;

删除触发器

--删除触发器

drop trigger 触发器名;

例子

--环境模拟，购买商品，产生订单，以及库存数变化--创建数据并插入数据

create tablegoods(

idint not null auto_increment primary key,

namevarchar(100) not null,

quantityint not null)create tableorder_table(

oidint not null auto_increment primary key,

gidint,

muchint)insert into goods(name,quantity) values('商品1',10),('商品2',10),('商品3',10);--需求一、随意购买一款商品并同时减去对应的数量--监视事件 update -- 会用到关键字 new 获取到最新的value值--先创建触发器--思路 当创建了订单后，库存就立刻减去相应数量

create trigger tg_test1 after insert onorder_tableforeach rowbegin

update goods set quantity = quantity-new.much where id = new.gid; --new.字段 --获取到监视表执行的值

end

drop triggertg_test1;select * fromgoods;insert into order_table(gid,much) values(1,1);select * fromgoods;--需求2：发生退货事件，即修改订单，根据修改的信息改变库存--监视事件 update 会使用到关键字 old 获取到更新数据之前的值

create trigger tg_test2 after update onorder_tableforeach rowbegin

update goods set quantity = quantity+old.much-new.much where id =old.gid;--算法解析：最新数量等于当前数量加上之前订单的值减去更改的值 例如 10台一开头卖出3 台，那么库存7台 退货2台 那么最新库存有多少台 7+3-2

end

select * fromorder_table;select * fromgoods;update order_table set much =0 where oid =4;select * fromorder_table;select * fromgoods;--需求3：发生订单已经生成，但是客户不想要了，要删除订单，并恢复对应的库存；--监视事件delete 会用到old关键字

create trigger tg_test3 after delete onorder_tableforeach rowbegin

update goods set quantity = quantity+old.much where id =old.gid;end

select * fromorder_table;select * fromgoods;delete from order_table where oid =5;select * fromorder_table;select * from goods;

例子-商品出售与库存

存储过程

存储过程：类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句(对数据的增删改查)、条件语句和循环语句等。

查看现有的存储过程

--查看现有的存储过程

show PROCEDURE status;

删除存储过程

--删除存储过程

drop procedure 存储过程名;

调用存储过程

--调用存储过程

call 存储过程名(参数入/出类型 参数名 数据类型); --括号内可以传参，若不填默认使用入参--三种参数

--in 入参 -- 只进不出的参数

--out 出参 -- 可以输出的参数，可以在存储过程外被调用

--inout 出入参

--创建存储过程 in 入参

create procedure p_in (IN num int)begin

selectnum;set num=100;selectnum;end;--调用

set @num=1;

call p_in(@num);select @num;--总结: IN 参数只是将变量在存储过程内部做了修改，并没有影响到外部，@num仍为1。

in 入参

--创建存储过程 out 出参

create procedure p_out (out num int)begin

selectnum;set num=100;selectnum;end;--drop PROCEDURE p_out--调用

set @numm=1;

call p_out(@numm);select @numm;--总结：out 对内外部变量都进行了修，开头声明的变量值并没有传进去

out 出参

--创建存储过程 inout 出入参

create procedure p_inout (inout num int)begin

selectnum;set num=100;selectnum;end;--调用

set @num=1;

call p_inout(@num);select @num;--总结：开头的值传了进去，并使用了，接着在内容进行了重新赋值，接着out将外部的值也更改了；

inout 出入参

创建存储过程

--创建存储过程

create procedure存储过程名()begin# sql语句end

体验封装 --不带参数

create procedurepro_test()begin

select * fromgoods;endcall pro_test();

体验参数 --带参数

create procedure pro_test1(in i int,out n int)begin

select quantity into n from goods where id =i;--语法解析，根据用户输入商品ID，出结果n

end

-- 注意into 关键字 可以 将前面字段的查询结果 赋值 给 into 后面的变量.

--声明变量 n -- 因为不知道值是多少，声明一个变量等于null

set @n =null;--执行存储过程

call pro_test1(1,@n);--查询结果

select @n as quantity;

体验控制 -- if

--体验控制 --if

create procedure pro_if(in i int)begin

if i > 10 then

select * fromgoods;else

select * fromorder_table;end if;endcall pro_if(11);

call pro_if(9);

体验循环 -- while

--体验循环 while--计算根据用户输入的值计算累加的和，并输出结果

drop PROCEDUREpro_while;create procedure pro_while(inout sum_all int)begin

declare sum_pro,n int;--初始化变量 --用来储存和,和初识值

set n =0;set sum_pro =0;while n<=sum_all do --开始循环 语法 while 条件 do 成立时执行什么 end while;结束循环

set sum_pro = sum_pro +n;set n = n+1;end while;selectsum_pro;set sum_all =sum_pro;end

set @n = 1000;

call pro_while(@n);select @n;

函数

在使用过程中，一般都是调用内部自带的函数，虽然可以创建属于自己的函数，但是一般不建议这样操作；

我们常用的内建函数

--数学函数 round()、rand()--聚合函数 avg()\count()\min()\max()\sum()\group_concat()--字符串函数 insert() substring()--时间和日期函数 DATE_FORMAT(date,format) 根据format字符串格式化date值、CURDATE()、CURTIME()--加密函数 MD5()\PASSWORD()--控制流函数 case when...then...else...end if(条件，成立结果，不成立结果)

Mysql提供的内建函数

一、数学函数ROUND(x,y)

返回参数x的四舍五入的有y位小数的值RAND()

返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

二、聚合函数(常用于GROUP BY从句的SELECT查询中)AVG(col)返回指定列的平均值COUNT(col)返回指定列中非NULL值的个数MIN(col)返回指定列的最小值MAX(col)返回指定列的最大值SUM(col)返回指定列的所有值之和

GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

三、字符串函数

CHAR_LENGTH(str)

返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。

CONCAT(str1,str2,...)

字符串拼接

如有任何一个参数为NULL ，则返回值为NULL。

CONCAT_WS(separator,str1,str2,...)

字符串拼接(自定义连接符)

CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的NULL)。

FORMAT(X,D)

将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。

例如：SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'

INSERT(str,pos,len,newstr)

在str的指定位置插入字符串

pos：要替换位置其实位置len：替换的长度

newstr：新字符串

例如:SELECT INSERT('abcd',1,2,'tt'); 结果为: 'ttcd'

SELECT INSERT('abcd',1,4,'tt'); 结果为: 'tt'特别的：

如果pos超过原字符串长度，则返回原字符串

如果len超过原字符串长度，则由新字符串完全替换

INSTR(str,substr)

返回字符串str中子字符串的第一个出现位置。LEFT(str,len)

返回字符串str 从开始的len位置的子序列字符。

例如:SELECT INSTR('abc','c'); 结果为： 3

SELECT INSTR('abc','d'); 结果为： 0

LOWER(str)

变小写UPPER(str)

变大写REVERSE(str)

返回字符串str，顺序和字符顺序相反。

例如:SELECT REVERSE('1234567') 结果为：7654321

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

mysql> SELECT SUBSTRING('Quadratically',5); --从第5位开始截取

-> 'ratically'mysql> SELECT SUBSTRING('foobarbar' FROM 4); --从第4位开始截取

-> 'barbar'mysql> SELECT SUBSTRING('Quadratically',5,6); --从第5位开始截取,截取6个长度

-> 'ratica'mysql> SELECT SUBSTRING('Sakila', -3); --从倒数第3位开始截取

-> 'ila'mysql> SELECT SUBSTRING('Sakila', -5, 3); --从倒数第5位开始截取,截取3个长度

-> 'aki'四、日期和时间函数

CURDATE()或CURRENT_DATE() 返回当前的日期

CURTIME()或CURRENT_TIME() 返回当前的时间

DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)

DAYOFMONTH(date) 返回date是一个月的第几天(1~31)

DAYOFYEAR(date) 返回date是一年的第几天(1~366)

DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);

FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts

HOUR(time) 返回time的小时值(0~23)

MINUTE(time) 返回time的分钟值(0~59)MONTH(date) 返回date的月份值(1~12)

MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);

NOW() 返回当前的日期和时间

QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);

WEEK(date) 返回日期date为一年中第几周(0~53)YEAR(date) 返回日期date的年份(1000~9999)

重点:

DATE_FORMAT(date,format) 根据format字符串格式化date值

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');-> 'Sunday October 2009'mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');-> '22:23:00'mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',-> '%D %y %a %d %m %b %j');-> '4th 00 Thu 04 10 Oct 277'mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',-> '%H %k %I %r %T %S %w');-> '22 22 10 10:23:00 PM 22:23:00 00 6'mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');-> '1998 52'mysql> SELECT DATE_FORMAT('2006-06-00', '%d');-> '00'五、加密函数

MD5()

计算字符串str的MD5校验和

例如:SELECT MD5('1234') 结果为：81dc9bdb52d04dc20036dbd8313ed055

PASSWORD(str)

返回字符串str的加密版本，这个加密过程是不可逆转的

例如:SELECT PASSWORD('1234') 结果为:*A4B6157319038724E3560894F7F932C8886EBFCF

六、控制流函数CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回defaultCASE [test] WHEN[val1] THEN [result]...ELSE [default]END如果test和valN相等，则返回resultN，否则返回defaultIF(test,t,f)

如果test是真，返回t；否则返回f

IFNULL(arg1,arg2)

如果arg1不是空，返回arg1，否则返回arg2

例如:SELECT IFNULL('bbb','abc'); 结果为: bbbSELECT IFNULL(null,'abc'); 结果为: abcNULLIF(arg1,arg2)

如果arg1=arg2返回NULL；否则返回arg1

例如:SELECT NULLIF('bbb','bbb');结果为： null

SELECT NULLIF('aaa','bbb');结果为： aaa

mysql提供的内建函数

创建函数

CREATE FUNCTION fun1(i1 int,i2 int)RETURNS INT //设置返回类型BEGIN

DECLARE sum int default 0;set sum = i1+i2;RETURN(sum); //返回结果end

调用自定义函数

#直接调用自定义函数select fun1(1,5);

#在sql语句中使用自定义函数select fun1(参数1,参数2),name from 表名

删除自定义函数

DROP FUNCTION fun_name;

函数和存储过程的区别

事物处理

一、 什么是事务

一组sql语句批量执行,要么全部执行成功,要么全部执行失败

二、为什么出现这种技术

为什么要使用事务这个技术呢？ 现在的很多软件都是多用户，多程序，多线程的，对同一个表可能同时有很多人在用，为保持数据的一致性，所以提出了事务的概念。这样很抽象，举个例子：

A 给B 要划钱，A 的账户-1000元， B 的账户就要+1000元，这两个update 语句必须作为一个整体来执行，不然A 扣钱了，B 没有加钱这种情况很难处理。

三、事物的特性

80年代中国人结婚四大件：手表、自行车、缝纫机、收音机(三转一响)。要把事务娶回家同样需要四大件，所以事务很刻薄(ACID)，四大件清单：原子性(Atom)、一致性(Consistent)、隔离性(Isolate)、持久性(Durable)。ACID就是数据库事务正确执行的四个特性的缩写。

原子性：要么不谈，要谈就要结婚！

对于其数据修改，要么全都执行，要么全都不执行。

•一致性：恋爱时，什么方式爱我；结婚后还得什么方式爱我；

数据库原来有什么样的约束，事务执行之后还需要存在这样的约束，所有规则都必须应用于事务的修改，以保持所有数据的完整性。

•隔离性：闹完洞房后，是俩人的私事。

一个事务不能知道另外一个事务的执行情况(中间状态)

•持久性：一旦领了结婚证，无法后悔。

即使出现致命的系统故障也将一直保持。不要告诉我系统说commit成功了，回头电话告诉我，服务器机房断电了，我的事务涉及到的数据修改可能没有进入数据库。

注意

•在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

•事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

•事务用来管理insert,update,delete 语句

事务控制语句

•BEGIN 或 START TRANSACTION；显式地开启一个事务；

•COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

•ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

•SAVEPOINT : 保存点,可以把一个事物分割成几部分.在执行ROLLBACK 时 可以指定在什么位置上进行回滚操作.

注意:SET AUTOCOMMIT=0 ;禁止自动提交 和 SET AUTOCOMMIT=1 开启自动提交.

例子：A转账给B

--创建表

create tableaccount(

idint(50) not null auto_increment primary key,

nameVARCHAR(50) not null,money DOUBLE(10,2) not NULL);--插入数据

insert into account (id,name,money) values(1,'鲁班',250),(2,'后羿',5000);--执行转账

start transaction; --开启事物

--执行sql语句操作

update account set money = money - 500 where id =1;update account set money = money+500 where id = 2;commit; --手动提交事物

rollback; --回滚事物

--查看结果

select * fromaccount;--保存点使用

STARTTRANSACTION;insert into account (name,money) values('李元芳',1000);

SAVEPOINT s1;--设置保存点

insert into account (name,money) values('张桂枝',1500);ROLLBACK to s1; --事物回滚到保存点
COMMIT; --提交事物

数据锁

例子：需求: 有一个账户,两个人在同一时间要对此账户操作,A要对账户充值100块,B要从账户中取出100块.操作前都要先看一下账户的 余额然后再操作.

--窗口1 用户进行充值

--充值前 先查看余额

set @m=0;SELECT money into @m from account where id = 1;select @m;--看到余额后 充值100 块

update account set money = @m + 100 where id = 1;SELECT * fromaccount;----------------------------------------------------------------窗口2 用户进行取款

--取款前 先查看余额

set @m=0;SELECT money into @m from account where id = 1;select @m;--看到余额后 取款100 块

update account set money = @m - 100 where id = 1;SELECT * from account;

View Code

1.锁的基本概念

当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。

2.锁的基本类型

多个事务同时读取一个对象的时候，是不会有冲突的。同时读和写，或者同时写才会产生冲突。因此为了提高数据库的并发性能，通常会定义两种锁：共享锁和排它锁。

2.1共享锁(Shared Lock，也叫S锁)

共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。(如果试衣间的门还没被锁上，顾客都能够同时进去参观)

2.2排他锁(Exclusive Lock，也叫X锁)

排他锁(X)表示对数据进行写操作。如果一个事务对 对象加了排他锁，其他事务就不能再给它加任何锁了。(某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面给打开了).

3. 实际开发中常见的两种锁:

3.1悲观锁 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block(阻塞)直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制.

注意：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性.因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。关闭自动提交命令为：set autocommit=0;

设置完autocommit后，我们就可以执行我们的正常业务了。具体如下

--0.开始事务

start transaction;--1.查询账户余额

set @m = 0; --账户余额

select money into @m from account where id = 1 for update;select @m;--2.修改账户余额

update account set money = @m -100 where id = 1;select * FROM account where id = 1;--3. 提交事务

commit;

在另外的查询页面执行:

--0.开始事务

start transaction;--1.查询账户余额

set @m = 0; --账户余额

select money into @m from account where id = 1 for update;select @m;--2.修改账户余额

update account set money = @m +100 where id = 1;select * FROM account where id = 1;--3. 提交事务

commit;

会发现当前查询会进入到等待状态,不会显示出数据,当上面的sql执行完毕提交事物后,当前sql才会显示结果.

-- 注意1:在使用悲观锁时,如果表中没有指定主键,则会进行锁表操作.

-- 注意2: 悲观锁的确保了数据的安全性，在数据被操作的时候锁定数据不被访问，但是这样会带来很大的性能问题。因此悲观锁在实际开发中使用是相对比较少的。

3.2 乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。

使用乐观锁的两种方式:

1.使用数据版本(Version)记录机制实现，这是乐观锁最常用的一种实现 方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录 的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数 据。

代码示例:

--1.查询账户余额

set @m = 0; --账户余额

select money into @m from account where id = 1;select @m;--2.查询版本号

set @version = 0; --版本号

select version into @version from account where id = 1;select @version;--3.修改账户余额

update account set money = @m -100,version=version+1 where id = 1 and version = @version;select * FROM account where id = 1;

2.乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳 (datatime), 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

-- 悲观锁与乐观锁的优缺点:

-- 两种锁各有其有点缺点,不能单纯的讲哪个更好.

-- 乐观锁适用于写入比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

--但如果经常产生冲突，上层应用会不断的进行重试操作，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适.