Day 46 视图、存储过程、触发器、函数、事物、锁
一 、存储过程
create view stu_view as select * from ren
视图:是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据
视图有如下特点;
1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。
2. 视图是由基本表（实表）产生的表（虚表）。
3. 视图的建立和删除不影响基本表。
4. 对视图内容的更新（添加、删除和修改）直接影响基本表。
5. 当视图来自多个基本表时，不允许添加和删除数据。
1.创建视图
|
1
|
create view 视图名称 as sql 查询语句 |
2.使用视图
|
1
|
select * from 视图名称; |
3.更新视图
|
1
|
alter view 视图名称 AS SQL语句 |
4. 删除视图
|
1
|
drop view ren_view; |
二 、触发器
触发器：监视某种情况，并触发某种操作。
触发器创建语法四要素：1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
1.创建触发器语法
|
1
2
3
4
5
6
7
|
create trigger triggerName after/before insert/update/delete on 表名 for each row #这句话是固定的 begin #需要执行的sql语句 end注意1:after/before: 只能选一个 ,after 表示 后置触发, before 表示前置触发注意2:insert/update/delete:只能选一个 |
创建两张表
|
#商品表 create table goods( id int primary key auto_increment, name varchar(20), num int ); |
#订单表 create table order_table( oid int primary key auto_increment, gid int, much int ); |
添加3条商品数据
|
1
|
insert into goods(name,num) values('商品1',10),('商品2',10),('商品3',10); |
如果我们在没使用触发器之前：假设我们现在卖了3个商品1，我们需要做两件事
1.往订单表插入一条记录
|
1
|
insert into order_table(gid,much) values(1,3); |
2.更新商品表商品1的剩余数量
update goods set num=num-3 where id=1;
|
现在，我们来创建一个触发器：
|
1
2
3
4
5
|
create trigger tg1 after insert on order_tablefor each rowbegin update goods set num = num -3 where id = 1;end |
这时候我们只要执行：
|
1
|
insert into order_table(gid,much) values(1,3); |
案例一（不能动态改）
创建一个触发器 ，
create trigger tgr1 after insert on order_table for each row begin update goods set num =num -3 where id =1 ; end
然后执行语句：
insert into order_table(gid,much) values(1,3);
会发现商品1的数量变为7了，说明在我们插入一条订单的时候，
触发器自动帮我们做了更新操作。
但现在会有一个问题，因为我们触发器里面num和id都是写死的，所以不管我们买哪个商品，最终更新的都是商品1的数量。比如：我们往订单表再插入一条记录：
|
1
|
insert into order_table(gid,much) values(2,3); |
执行完后会发现商品1的数量变4了，而商品2的数量没变，这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。
我们如何在触发器引用行的值，也就是说我们要得到我们新插入的订单记录中的gid或much的值。
对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。
所以现在我们可以这样来改我们的触发器:
案例二、
create trigger t2 after insert on order_table for each row begin update goods set num =num-new.much where id =new.gid ; end
insert into table order_talbe (gid,much) values(1,3)
现在还存在两种情况：
1.当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？
对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示旧表中的值，old.列名可以引用原(旧)表中的值。
那我们的触发器就该这样写：
|
1
2
3
4
5
|
create trigger tg3 afert delete on order_tablefor each rowbigen update goods set num = num + old.much where id = old.gid;-- (注意这边的变化)end |
2.当用户修改一个订单的数量时，我们触发器修改怎么写?
|
1
2
3
4
5
|
create trigger tg4 after update on order_tablefor each rowbegin update goods set num = num+old.much-new.much where id = old.gid;end |
三 、存储过程
MySQL数据库在5.0版本后开始支持存储过程，那么什么是存储过程呢？怎么创建、查看和删除存储过程呢？存储过程有什么优点？
存储过程：类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句（对数据的增删改查）、条件语句和循环语句等。
1. 查看现有的存储过程
|
1
|
show procedure status; |
2 .删除存储过程
|
1
|
drop procedure 存储过程名称; |
3. 调用 存储过程
|
1
|
call 存储过程名称(参数入/出类型 参数名 数据类型); |
4.创建存储过程
1.体会封装
|
1
2
3
4
5
|
#1.体会封装create procedure p1 ()begin select * from account; end |
2.SQL 体会参数
|
1
2
3
4
5
6
7
8
9
|
create procedure p2(in i int,out n varchar(50))begin select name into n from account where id = i;end-- 调用set @name =null;CALL p2(1,@name);select @name; |
注意1: mysql中有三种出入参数类型:分别为:1. in 入参类型 2.out 出参类型 3. inout 出入参类型
注意2: into 关键字 可以 将前面字段的查询结果 执行 给 into 后面的变量.
存储过程优点：
1、存储过程增强了SQL语言灵活性。
存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；
2、减少网络流量，降低了网络负载。
存储过程在服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行
3、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译。
一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
存储过程缺点：
1、扩展功能不方便
2、不便于系统后期维护
四、函数
一、数学函数
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，否则返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END
如果test和valN相等，则返回resultN，否则返回default
IF(test,t,f)
如果test是真，返回t；否则返回f
IFNULL(arg1,arg2)
如果arg1不是空，返回arg1，否则返回arg2
例如:
SELECT IFNULL('bbb','abc'); 结果为: bbb
SELECT IFNULL(null,'abc'); 结果为: abc
NULLIF(arg1,arg2)
如果arg1=arg2返回NULL；否则返回arg1
例如:
SELECT NULLIF('bbb','bbb');结果为： null
SELECT NULLIF('aaa','bbb');结果为： aaa
复制代码
更多函数: 官方猛击这里
1、自定义函数
|
1
2
3
4
5
6
7
8
9
|
CREATE FUNCTION fun1(i1 int,i2 int)RETURNS INT //设置返回类型BEGIN DECLARE sum int default 0; set sum = i1+i2; RETURN(sum); //返回结果end |
2.调用自定义函数
|
1
2
3
4
5
|
#直接调用自定义函数select fun1(1,5);#在sql语句中使用自定义函数select fun1(参数1,参数2),name from 表名 |
3.删除自定义函数
|
1
|
DROP FUNCTION fun_name; |
4.函数与存储过程的区别:
五、事物的处理
一、 什么是事务
一组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 开启自动提交.
六、数据锁
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后，我们就可以执行我们的正常业务了。具体如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
-- 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; |
在另外的查询页面执行:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
-- 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
2
3
4
5
6
7
8
9
10
11
12
13
|
-- 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，否则就是版本冲突。
悲观锁与乐观锁的优缺点:
两种锁各有其有点缺点,不能单纯的讲哪个更好.
乐观锁适用于写入比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
但如果经常产生冲突，上层应用会不断的进行重试操作，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适.
http://www.cnblogs.com/wangfengming/articles/7883974.html 博客参考
