MySQL学习笔记(二)
一、概述
本篇文章主要介绍MySQL视图,触发器,存储过程,函数,事务,索引等内容,本节内容在Navicat上进行试验,具体软件安装及操作不再赘述。
二、视图
视图是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结果集,可以将该结果集当做表来使用。使用视图我们可以把查询过程中的临时表摘出来,用视图去实现,这样以后再想操作该临时表的数据时就无需重写复杂的sql了,直接去视图中查找即可,但视图有明显地效率问题,并且视图是存放在数据库中的,如果我们程序中使用的sql过分依赖数据库中的视图,即强耦合,那就意味着扩展sql极为不便,因此并不推荐使用
如某个SQL语句获取的动态结果集重复使用,使用视图可以很大程度上减少我们的SQL语句代码量
创建部门表 create table dep( id int primary key auto_increment, name char(32) ); 创建用户表 create table user( id int primary key auto_increment, name char(32), dep_id int, foreign key(dep_id) references dep(id) ); 数据插入 INSERT into dep(name) VALUES('财政部'); INSERT into dep(name) VALUES('后勤部'); INSERT into dep(name) VALUES('研发部'); INSERT into dep(name) VALUES('采购部'); INSERT into user(name,salary,dep_id) VALUES('小花','5000','1'); INSERT into user(name,salary,dep_id) VALUES('太阳','8000','3'); INSERT into user(name,salary,dep_id) VALUES('豆豆','12000','2'); INSERT into user(name,salary,dep_id) VALUES('小虎','11000','2');
如我们要查询工资高于所有人员平均工资的人员姓名,通常我们这样写
SELECT name from user WHERE salary >(SELECT avg(salary) from user);
此时我们可以使用视图查询
CREATE VIEW salary_avg_view AS SELECT avg(salary) from user; SELECT name from user WHERE salary >(SELECT * from salary_avg_view);
1、创建视图(--格式:CREATE VIEW 视图名称 AS SQL语句)
CREATE VIEW salary_avg_view AS SELECT avg(salary) from user;
2、删除视图(--格式:DROP VIEW 视图名称)
DROP VIEW salary_avg_view;
3、修改视图(-- 格式:ALTER VIEW 视图名称 AS SQL语句)
ALTER VIEW salary_avg_view AS SELECT avg(salary) from user; #此处并未做修改,实际操作可根据需要修改
4、使用视图(当作普通表使用即可)
SELECT * from salary_avg_view
三、触发器
对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时,可以使用触发器,触发器用于定制用户对表的行进行【增/删/改】前后的行为。注:没有查
现在我们新建一个user人数统计的表user_count,设置user_total字段
CREATE TABLE user_count( id int NOT NULL auto_increment PRIMARY KEY, user_total int DEFAULT 0 );
user表中已有4人,这里就偷懒直接设置下user_total值为4
UPDATE user_count SET user_total=4;
下面定义一个触发器,规定当user表中新增时,user_count加1,当user表中有删除行时,user_count减1
CREATE TRIGGER tigger_user_count_insert AFTER INSERT ON user FOR EACH ROW UPDATE user_count SET user_total=user_total+1 WHERE id=1; CREATE TRIGGER tigger_user_count_delete AFTER DELETE ON user FOR EACH ROW UPDATE user_count SET user_total=user_total-1 WHERE id=1;
此处补张图
此时,user_total值为4,我们试着插入一个值
测试代码:
INSERT INTO user(name,salary,dep_id) VALUES('小明',8000,4);
执行后user_total字段变为5
同理,删除的触发器的测试
测试代码:
DELETE from user WHERE name='小明';
执行后user_total字段变为4
下面具体介绍下触发器的使用
1、创建触发器
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body
trigger_name:触发器的名称,不能与已经存在的触发器重复;
trigger_time:{ BEFORE | AFTER },表示在事件之前或之后触发;
trigger_event::{ INSERT |UPDATE | DELETE },触发该触发器的具体事件;
tbl_name:该触发器作用在tbl_name上;
1 # 插入前 2 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW 3 BEGIN 4 ... 5 END 6 7 # 插入后 8 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW 9 BEGIN 10 ... 11 END 12 13 # 删除前 14 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW 15 BEGIN 16 ... 17 END 18 19 # 删除后 20 CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW 21 BEGIN 22 ... 23 END 24 25 # 更新前 26 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW 27 BEGIN 28 ... 29 END 30 31 # 更新后 32 CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW 33 BEGIN 34 ... 35 END
如:
1 CREATE TRIGGER trigger_user_count_insert AFTER INSERT 2 ON user FOR EACH ROW 3 UPDATE user_count SET user_total=(user_total+1); 4 5 CREATE TRIGGER trigger_user_count_delete AFTER DELETE 6 ON user FOR EACH ROW 7 UPDATE user_count SET user_total=user_total-1;
2、删除触发器
DROP TRIGGER 触发器名称;
如:
DROP TRIGGER trigger_user_count_delete;
3、使用触发器
触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的。
如:
INSERT INTO user(name,salary,dep_id) VALUES('小明',8000,4);
四、存储过程
MySQL数据库在5.0版本后开始支持存储过程,存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql,类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合,该语句集包括SQL语句(对数据的增删改查)、条件语句和循环语句等(流程控制见part 9)
存储过程优点: 1、存储过程增强了SQL语言灵活性。 存储过程可以使用控制语句编写,可以完成复杂的判断和较复杂的运算,有很强的灵活性; 2、减少网络流量,降低了网络负载。 存储过程在服务器端创建成功后,只需要调用该存储过程即可,而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行 3、存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译。 一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 存储过程缺点: 1、扩展功能不方便 2、不便于系统后期维护
1、查看存储过程
SHOW PROCEDURE STATUS;
2、创建存储过程
无参存储过程
1 -- 创建存储过程 2 3 delimiter // 4 create procedure 存储过程名称() 5 BEGIN 6 SQL语句 7 END// 8 delimiter ; 9 10 11 12 -- 执行存储过程 13 14 call 存储过程名称()
对于存储过程,可以接收参数,其参数有三类:
- in 仅用于传入参数用
- out 仅用于返回值用
- inout 既可以传入又可以当作返回值
1 -- 创建存储过程 2 delimiter \\ 3 create procedure p1( 4 in i1 int, 5 in i2 int, 6 inout i3 int, 7 out r1 int 8 ) 9 BEGIN 10 DECLARE temp1 int; 11 DECLARE temp2 int default 0; 12 13 set temp1 = 1; 14 15 set r1 = i1 + i2 + temp1 + temp2; 16 17 set i3 = i3 + 100; 18 19 end\\ 20 delimiter ; 21 22 -- 执行存储过程 23 set @t1 =4; 24 set @t2 = 0; 25 CALL p1 (1, 2 ,@t1, @t2); 26 SELECT @t1,@t2;
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; --into 关键字 可以 将前面字段的查询结果 执行 给 into 后面的变量.
create procedure p3(in x int,in c char(1)) begin if c ='d' then select * from account where money >x; else select * from account where money <x; end if; end
1 create procedure p4(inout n int) 2 begin 3 DECLARE sum int default 0; -- 设置总和变量,并且指定初始值0 4 declare i int; -- 声明变量 5 set i = 0; -- 通过set为变量设置值 6 while i<=n DO -- 开始循环 7 set sum = sum +i; 8 set i = i+1; 9 end while; -- 结束循环 10 11 select sum; -- 提供结果 12 13 set n = sum;--将计算结果提供给 输出变量 n; 14 end; 15 16 -- 调用: 17 set @n = 100; 18 call p4(@n); 19 select @n;
3、删除存储过程
drop procedure proc_name;
4、执行存储过程
-- 无参数 call proc_name() -- 有参数,全in call proc_name(1,2) -- 有参数,有in,out,inout set @t1=0; set @t2=3; call proc_name(1,2,@t1,@t2)
五、函数
1、MySQL提供的内建函数
1 一、数学函数 2 ROUND(x,y) 3 返回参数x的四舍五入的有y位小数的值 4 5 RAND() 6 返回0到1内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。 7 8 二、聚合函数(常用于GROUP BY从句的SELECT查询中) 9 AVG(col)返回指定列的平均值 10 COUNT(col)返回指定列中非NULL值的个数 11 MIN(col)返回指定列的最小值 12 MAX(col)返回指定列的最大值 13 SUM(col)返回指定列的所有值之和 14 GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果 15 16 三、字符串函数 17 18 CHAR_LENGTH(str) 19 返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。 20 CONCAT(str1,str2,...) 21 字符串拼接 22 如有任何一个参数为NULL ,则返回值为 NULL。 23 CONCAT_WS(separator,str1,str2,...) 24 字符串拼接(自定义连接符) 25 CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。 26 27 FORMAT(X,D) 28 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点,或不含小数部分。 29 例如: 30 SELECT FORMAT(12332.1,4); 结果为: '12,332.1000' 31 32 INSERT(str,pos,len,newstr) 33 在str的指定位置插入字符串 34 pos:要替换位置其实位置 35 len:替换的长度 36 newstr:新字符串 37 例如: 38 SELECT INSERT('abcd',1,2,'tt'); 结果为: 'ttcd' 39 SELECT INSERT('abcd',1,4,'tt'); 结果为: 'tt' 40 特别的: 41 如果pos超过原字符串长度,则返回原字符串 42 如果len超过原字符串长度,则由新字符串完全替换 43 44 INSTR(str,substr) 45 返回字符串 str 中子字符串的第一个出现位置。 46 47 LEFT(str,len) 48 返回字符串str 从开始的len位置的子序列字符。 49 例如: 50 SELECT INSTR('abc','c'); 结果为: 3 51 SELECT INSTR('abc','d'); 结果为: 0 52 53 LOWER(str) 54 变小写 55 56 UPPER(str) 57 变大写 58 59 REVERSE(str) 60 返回字符串 str ,顺序和字符顺序相反。 61 例如: 62 SELECT REVERSE('1234567') 结果为:7654321 63 64 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) 65 不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 66 67 mysql> SELECT SUBSTRING('Quadratically',5); -- 从第5位开始截取 68 -> 'ratically' 69 70 mysql> SELECT SUBSTRING('foobarbar' FROM 4); -- 从第4位开始截取 71 -> 'barbar' 72 73 mysql> SELECT SUBSTRING('Quadratically',5,6); --从第5位开始截取,截取6个长度 74 -> 'ratica' 75 76 mysql> SELECT SUBSTRING('Sakila', -3); -- 从倒数第3位开始截取 77 -> 'ila' 78 79 mysql> SELECT SUBSTRING('Sakila', -5, 3); -- 从倒数第5位开始截取,截取3个长度 80 -> 'aki' 81 82 四、日期和时间函数 83 CURDATE()或CURRENT_DATE() 返回当前的日期 84 CURTIME()或CURRENT_TIME() 返回当前的时间 85 DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7) 86 DAYOFMONTH(date) 返回date是一个月的第几天(1~31) 87 DAYOFYEAR(date) 返回date是一年的第几天(1~366) 88 DAYNAME(date) 返回date的星期名,如:SELECT DAYNAME(CURRENT_DATE); 89 FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式,格式化UNIX时间戳ts 90 HOUR(time) 返回time的小时值(0~23) 91 MINUTE(time) 返回time的分钟值(0~59) 92 MONTH(date) 返回date的月份值(1~12) 93 MONTHNAME(date) 返回date的月份名,如:SELECT MONTHNAME(CURRENT_DATE); 94 NOW() 返回当前的日期和时间 95 QUARTER(date) 返回date在一年中的季度(1~4),如SELECT QUARTER(CURRENT_DATE); 96 WEEK(date) 返回日期date为一年中第几周(0~53) 97 YEAR(date) 返回日期date的年份(1000~9999) 98 99 重点: 100 DATE_FORMAT(date,format) 根据format字符串格式化date值 101 102 mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); 103 -> 'Sunday October 2009' 104 mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); 105 -> '22:23:00' 106 mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', 107 -> '%D %y %a %d %m %b %j'); 108 -> '4th 00 Thu 04 10 Oct 277' 109 mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', 110 -> '%H %k %I %r %T %S %w'); 111 -> '22 22 10 10:23:00 PM 22:23:00 00 6' 112 mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); 113 -> '1998 52' 114 mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); 115 -> '00' 116 117 五、加密函数 118 MD5() 119 计算字符串str的MD5校验和 120 例如: 121 SELECT MD5('1234') 结果为:81dc9bdb52d04dc20036dbd8313ed055 122 PASSWORD(str) 123 返回字符串str的加密版本,这个加密过程是不可逆转的 124 例如: 125 SELECT PASSWORD('1234') 结果为:*A4B6157319038724E3560894F7F932C8886EBFCF 126 127 六、控制流函数 128 CASE WHEN[test1] THEN [result1]...ELSE [default] END 129 如果testN是真,则返回resultN,否则返回default 130 CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 131 如果test和valN相等,则返回resultN,否则返回default 132 133 IF(test,t,f) 134 如果test是真,返回t;否则返回f 135 136 IFNULL(arg1,arg2) 137 如果arg1不是空,返回arg1,否则返回arg2 138 例如: 139 SELECT IFNULL('bbb','abc'); 结果为: bbb 140 SELECT IFNULL(null,'abc'); 结果为: abc 141 142 NULLIF(arg1,arg2) 143 如果arg1=arg2返回NULL;否则返回arg1 144 例如: 145 SELECT NULLIF('bbb','bbb');结果为: null 146 SELECT NULLIF('aaa','bbb');结果为: aaa
delimiter \\ create function f1( i1 int, i2 int) returns int BEGIN declare num int; set num = i1 + i2; return(num); END \\ delimiter ;
2、删除函数
drop function func_name;
3、执行函数
# 获取返回值 declare @i VARCHAR(32); select UPPER('doudou') into @i; SELECT @i; # 在查询中使用 select f1(11,nid) ,name from tb2;
4、函数与存储过程区别
六、事务
事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。
1、事务的特性
原子性(Atomicity)
原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚,这和前面两篇博客介绍事务的功能是一样的概念,因此事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有任何影响。
一致性(Consistency)
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态。
拿转账来说,假设用户A和用户B两者的钱加起来一共是5000,那么不管A和B之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是5000,这就是事务的一致性。
隔离性(Isolation)
隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。
即要达到这么一种效果:对于任意两个并发的事务T1和T2,在事务T1看来,T2要么在T1开始之前就已经结束,要么在T1结束之后才开始,这样每个事务都感觉不到有其他事务在并发地执行。
关于事务的隔离性数据库提供了多种隔离级别,稍后会介绍到。
持久性(Durability)
持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
注意:
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
- 事务用来管理 insert,update,delete 语句
2、事务的控制
-
BEGIN 或 START TRANSACTION;显式地开启一个事务;
-
COMMIT;也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的;
-
ROLLBACK;有可以使用ROLLBACK WORK,不过二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
- SAVEPOINT : 保存点,可以把一个事物分割成几部分.在执行ROLLBACK 时 可以指定在什么位置上进行回滚操作.
注意: SET AUTOCOMMIT=0 ;禁止自动提交 和 SET AUTOCOMMIT=1 开启自动提交.
3、应用举例(双方转账)
create table account( id int(50) not null auto_increment primary key, name VARCHAR(50) not null, money DOUBLE(5,2) not NULL );
insert into account (name,money) values('鲁班',250),('后羿',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 * from account;
START TRANSACTION ; insert into account (name,money) values('李元芳',1000); SAVEPOINT s1; -- 设置保存点 insert into account (name,money) values('张桂枝',1500); ROLLBACK to s1; -- 事物回滚到保存点<br>COMMIT; --提交事物
七、索引
索引,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取即可。
常见的索引有普通索引,唯一索引,主键索引,组合索引。
1、普通索引
普通索引仅有一个功能:加速查询
1 create table in1( 2 nid int not null auto_increment primary key, 3 name varchar(32) not null, 4 email varchar(64) not null, 5 extra text, 6 index ix_name (name) 7 )
1 create index index_name on table_name(column_name)
drop index_name on table_name;
show index from table_name;
注意:对于创建索引时如果是BLOB 和 TEXT 类型,必须指定length。
1 create index ix_extra on in1(extra(32));
2、唯一索引
1 create table in1( 2 nid int not null auto_increment primary key, 3 name varchar(32) not null, 4 email varchar(64) not null, 5 extra text, 6 unique ix_name (name) 7 )
1 create unique index 索引名 on 表名(列名)
drop unique index 索引名 on 表名
3、主键索引
主键有两个功能:加速查询 和 唯一约束(不可含null)
1 create table in1( 2 nid int not null auto_increment primary key, 3 name varchar(32) not null, 4 email varchar(64) not null, 5 extra text, 6 index ix_name (name) 7 ) 8 9 OR 10 11 create table in1( 12 nid int not null auto_increment, 13 name varchar(32) not null, 14 email varchar(64) not null, 15 extra text, 16 primary key(ni1), 17 index ix_name (name) 18 )
1 alter table 表名 add primary key(列名);
1 alter table 表名 drop primary key; 2 alter table 表名 modify 列名 int, drop primary key;
4、组合索引
组合索引是将n个列组合成一个索引,频繁的同时使用n列来进行查询,如:where name = 'doudou' and age = 18。
1 create table in3( 2 nid int not null auto_increment primary key, 3 name varchar(32) not null, 4 email varchar(64) not null, 5 extra text 6 )
1 create index ix_name_email on in3(name,email);
注意:对于同时搜索n个条件时,组合索引的性能好于多个单一索引合并。
八、数据锁
1、锁的基本概念
当并发事务同时访问一个资源时,有可能导致数据不一致,因此需要一种机制来将数据访问顺序化,以保证数据库数据的一致性。
2、共享锁与排他锁
多个事务同时读取一个对象的时候,是不会有冲突的。同时读和写,或者同时写才会产生冲突。因此为了提高数据库的并发性能,通常会定义两种锁:共享锁和排它锁。
共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。
排他锁(X)表示对数据进行写操作。如果一个事务对 对象加了排他锁,其他事务就不能再给它加任何锁了。
3、悲观锁与乐观锁
悲观锁 顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会block(阻塞)直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制.
注意:要使用悲观锁,我们必须关闭mysql数据库的自动提交属性.因为MySQL默认使用autocommit模式,也就是说,当你执行一个更新操作后,MySQL会立刻将结果进行提交。关闭自动提交命令为:set autocommit=0;
乐观锁, 顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制。
使用乐观锁的两种方式:
1.使用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现 方式。何谓数据版本?即为数据增加一个版本标识,一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时,将version字段的值一同读出,数据每更新一次,对此version值加一。当我们提交更新的时候,判断数据库表对应记录 的当前版本信息与第一次取出来的version值进行比对,如果数据库表当前版本号与第一次取出来的version值相等,则予以更新,否则认为是过期数据。
2.乐观锁定的第二种实现方式和第一种差不多,同样是在需要乐观锁控制的table中增加一个字段,名称无所谓,字段类型使用时间戳 (datatime), 和上面的version类似,也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比,如果一致则OK,否则就是版本冲突。
1 悲观锁与乐观锁的优缺点: 2 3 两种锁各有其有点缺点,不能单纯的讲哪个更好. 4 5 乐观锁适用于写入比较少的情况下,即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。 6 7 但如果经常产生冲突,上层应用会不断的进行重试操作,这样反倒是降低了性能,所以这种情况下用悲观锁就比较合适.
4、试验示例
扯了一堆概念,下面介绍数据锁试验,需求: 有一个账户,两个人在同一时间要对此账户操作,A要对账户充值100块,B要从账户中取出100块.操作前都要先看一下账户的余额然后再操作
1 -- 窗口1 用户进行充值 2 3 -- 充值前 先查看余额 4 set @m=0; 5 6 SELECT money into @m from account where id = 1; 7 8 select @m; 9 10 -- 看到余额后 充值100 块 11 update account set money = @m + 100 where id = 1; 12 13 SELECT * from account; 14 15 -------------------------------------------------------------- 16 -- 窗口2 用户进行取款 17 18 -- 取款前 先查看余额 19 set @m=0; 20 21 SELECT money into @m from account where id = 1; 22 23 select @m; 24 25 -- 看到余额后 取款100 块 26 update account set money = @m - 100 where id = 1; 27 28 SELECT * from account;
4.1、使用悲观锁
设置完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;
4.2、使用乐观锁
-- 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;
九、流程控制(补充)
1、条件语句
1 delimiter \\ 2 CREATE PROCEDURE proc_if () 3 BEGIN 4 5 declare i int default 0; 6 if i = 1 THEN 7 SELECT 1; 8 ELSEIF i = 2 THEN 9 SELECT 2; 10 ELSE 11 SELECT 7; 12 END IF; 13 14 END\\ 15 delimiter ;
2、循环语句
1 delimiter \\ 2 CREATE PROCEDURE proc_while () 3 BEGIN 4 5 DECLARE num INT ; 6 SET num = 0 ; 7 WHILE num < 10 DO 8 SELECT 9 num ; 10 SET num = num + 1 ; 11 END WHILE ; 12 13 END\\ 14 delimiter ;
1 delimiter \\ 2 CREATE PROCEDURE proc_repeat () 3 BEGIN 4 5 DECLARE i INT ; 6 SET i = 0 ; 7 repeat 8 select i; 9 set i = i + 1; 10 until i >= 5 11 end repeat; 12 13 END\\ 14 delimiter ;
1 BEGIN 2 3 declare i int default 0; 4 loop_label: loop 5 6 set i=i+1; 7 if i<8 then 8 iterate loop_label; 9 end if; 10 if i>=10 then 11 leave loop_label; 12 end if; 13 select i; 14 end loop loop_label; 15 16 END
3、动态执行sql
1 delimiter \\ 2 DROP PROCEDURE IF EXISTS proc_sql \\ 3 CREATE PROCEDURE proc_sql () 4 BEGIN 5 declare p1 int; 6 set p1 = 11; 7 set @p1 = p1; 8 9 PREPARE prod FROM 'select * from tb2 where nid > ?'; 10 EXECUTE prod USING @p1; 11 DEALLOCATE prepare prod; 12 13 END\\ 14 delimiter ;
十、数据库备份
mysqldump 命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。
mysqldump命令的工作原理很简单。它先查出需要备份的表的结构,再在文本文件中生成一个CREATE语句。然后,将表中的所有记录转换成一条INSERT语句。然后通过这些语句,就能够创建表并插入数据。
1、使用mysqldump实现逻辑备份
1 #语法: 2 # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql 3 4 #示例: 5 #单库备份 6 mysqldump -uroot -p123456 db1 > c:/db1.sql 7 mysqldump -uroot -p123456 db1 table1 table2 > c:/db1-table1-table2.sql 8 9 #多库备份 10 mysqldump -uroot -p123456 --databases db1 db2 mysql db3 > c:/db1_db2_mysql_db3.sql 11 12 #备份所有库 13 mysqldump -uroot -p123456 --all-databases > c:/all.sql
1 --all-databases , -A 2 导出全部数据库。 3 mysqldump -uroot -p --all-databases 4 --all-tablespaces , -Y 5 导出全部表空间。 6 mysqldump -uroot -p --all-databases --all-tablespaces 7 --no-tablespaces , -y 8 不导出任何表空间信息。 9 mysqldump -uroot -p --all-databases --no-tablespaces 10 --add-drop-database 11 每个数据库创建之前添加drop数据库语句。 12 mysqldump -uroot -p --all-databases --add-drop-database 13 --add-drop-table 14 每个数据表创建之前添加drop数据表语句。(默认为打开状态,使用--skip-add-drop-table取消选项) 15 mysqldump -uroot -p --all-databases (默认添加drop语句) 16 mysqldump -uroot -p --all-databases –skip-add-drop-table (取消drop语句) 17 --add-locks 18 在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态,使用--skip-add-locks取消选项) 19 mysqldump -uroot -p --all-databases (默认添加LOCK语句) 20 mysqldump -uroot -p --all-databases –skip-add-locks (取消LOCK语句) 21 --allow-keywords 22 允许创建是关键词的列名字。这由表名前缀于每个列名做到。 23 mysqldump -uroot -p --all-databases --allow-keywords 24 --apply-slave-statements 25 在'CHANGE MASTER'前添加'STOP SLAVE',并且在导出的最后添加'START SLAVE'。 26 mysqldump -uroot -p --all-databases --apply-slave-statements 27 --character-sets-dir 28 字符集文件的目录 29 mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets 30 --comments 31 附加注释信息。默认为打开,可以用--skip-comments取消 32 mysqldump -uroot -p --all-databases (默认记录注释) 33 mysqldump -uroot -p --all-databases --skip-comments (取消注释) 34 --compatible 35
相关文章:
-
-
linux各个目录介绍,文件夹介绍,linux安装软件放在哪儿 ... [阅读全文]
-
请点击相应的步骤查看详情 我们首先搭建一个 dva Demo 项目(请参考react快速构建一个应用项目),然后逐步完成以下内容: 第一步,我们会划... [阅读全文]
-
最近开始大面积使用ES,很多地方都是知其然不知其所以然,特地翻看了很多资料和大牛的文档,简单汇总一篇。内容多为摘抄,说是深入其实也是一点浅尝辄止的理... [阅读全文]
-
Cheatsheet: 2018 01.01 ~ 02.28
JAVAHow to Improve the Performance of a Java ApplicationJava Memory Manage... [阅读全文] -
5. 跟踪标记 (Trace Flag) 834, 845 对内存页行为的影响
跟踪标记:834 功能: 在64位的windows环境下,为SQL Server开启这个跟踪标记,那么SQL Server 会使用大页(Large ... [阅读全文] -
版权声明:本文内容由互联网用户贡献,该文观点仅代表作者本人。本站仅提供信息存储服务,不拥有所有权,不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 2386932994@qq.com 举报,一经查实将立刻删除。
下一篇: 50个常用的Unix/Linux命令分享
发表评论