mysql 存储过程锁_mysql的存储过程，函数，事件，权限，触发器，事务，锁，视图，导入导出...

1.创建过程

1.1 简单创建

--创建员工表

DROP TABLE IF EXISTSemployee;CREATE TABLEemployee(

idint auto_increment primary key,

`name`varchar(50),

depidint);insert into employee(name,depid) values('vic',1),('myvic',2),('liu',2);--1.创建存储过程

DROP PROCEDURE IF EXISTSpro_employee;

DELIMITER//

CREATE PROCEDURE pro_employee(IN pdepid INT,OUT pcount INT)BEGIN

SELECT COUNT(id) INTO pcount FROM employee WHERE depid=pdepid; #into是把值赋值给pcountEND

//DELIMITER ;

#调用

call pro_employee(2,@pcount);select @pcount;--2. 返回最后的id值

DROP PROCEDURE IF EXISTSpro_insert;

DELIMITER//

CREATE PROCEDURE pro_insert(IN name varchar(50),IN depid INT,OUT id int)BEGIN

insert into employee(name,depid) values(name,depid);set id =last_insert_id(); # 获取最后的id值END //DELIMITER ;--3 inout 模式，作为输入和输出

DELIMITER //

CREATE PROCEDURE p4(INOUT v_id INT)BEGIN# 定义变量DECLARE v_count int;IF v_id > 3 THEN

SET v_count = 100;ELSE

SET v_count =500;END IF;

# 返回值SET v_id =v_count;END //delimiter ;

# 调用set @id =1;

call p4(@id);select @id;--4 变量/**

DECLARE tax int DEFAULT 6; #存储过程变量

set @var =1; #会话变量；

二则的区别：

在调用存储过程时，以DECLARE声明的变量都会被初始化为 NULL。

而会话变量(即@开头的变量)则不会被再初始化，在一个会话内，

只须初始化一次，之后在会话内都是对上一次计算的结果，

就相当于在是这个会话内的全局变量。*/DELIMITER//

CREATE procedure p3(in n int,OUT total int)begin

DECLARE num int default 0; #变量必须声明后，才可以用SET num = 100;SET @a = 100; #如果没有declare，必须带上@，使用的时候也需要的SET total = n +1+num+@a;END //DELIMITER ;--5 循环

DELIMITER //

CREATE PROCEDUREp5()BEGIN

DECLARE i int;set i = 1;while i<5doinsert into employee(name,depid) values(concat('vic',i),i);set i=i+1;end while;END //DELIMITER ;

1.2 存储过程的修改

一般不太支持；

2.函数

-- 1.函数的创建

DELIMITER//CREATE FUNCTIONgetSum(num INT)

RETURNS INT#返回值类型

BEGIN

SET num= num+1;RETURNnum;END //DELIMITER ;-- 2.函数的删除

DROPFUNCTION IF EXISTS func;

-- 3.查看所有的函数

show FUNCTION status;

3.事件

--1 创建事件

# 查看调度器

show variableslike '%event_scheduler%';

# show variables 可以查看系统变量及值

# show status 系统运行的状态，不可更改；

#开启调度器SET GLOBAL event_scheduler = ON;SET GLOBAL event_scheduler = OFF;#关闭

#查看调度器线程

show processlist;

# 测试表DROP TABLE IF EXISTSevents_list;CREATE TABLEevents_list(

idint auto_increment primary key,

event_namevarchar(20) not null,

event_startedtimestamp not null);

# 事件1 (立即启动事件)CREATE EVENT IF not EXISTSevent_nowONSCHEDULE

AT now()

doinsert into events_list(event_name,event_started) values('event_now',now());

# 事件2 (每分钟启动事件)createevent events_minuteONschedule

every1minute

doinsert into events_list(event_name,event_started) values('event_minute',now());

# 查询事件

show events;select * frommysql.event; #查看所有事件

#修改时间alterevent event_minuteonschedule

every30second

doinsert into events_list(event_name,event_started) value('event_second',now());

# 修改为不再次活动alterevent event_second disable;alterevent event_second enable; # 再次活动

# 删除事件DROP EVENT IF EXISTS event_name;

4.权限

# 1权限

#1.1创建用户usemysql;create user li@localhost IDENTIFIED BY 'li'; #identified by会将纯文本加密作散列值存储

#1.2修改用户

renameuser 'li'@'localhost' to 'newuser'@'localhost';

#1.3删除用户DROP user 'li'@'localhost';

#1.4更改密码set password for li@localhost = password('root');

#1.5查看用户权限

show grantsfor li@localhost; # grands usage 没有权限

#1.6赋予权限grant select on text_db.* to 'li'@'localhost';

#1.7回收权限revoke delete,select on *.* from 'li'@localhost;

#1.8立即使用

flushprivileges;/*1.9 user表中host的值的意义

% 匹配所有主机

localhost localhost不会被解析成IP地址，直接通过UNIXsocket连接

127.0.0.1 会通过TCP/IP协议连接，并且只能在本机访问；

::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1*/#1.10 grant命令grant all privileges on *.* to jack@'localhost' identified by 'jack' with grant option;/*说明：

all privileges:所有权限，你也可以使用select,update等权限

on 权限针对那些库.表

*.* :前*号指定数据库名，后面*号指定表名

to ：将权限赋予某个用户

jack@localhost : Jack表示用户，@后限制主机 %指向任何地方

iDentitied by :用户登录的密码

with grant option :将自己拥有的权限授权给别人，注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。*/# 参考资料：https://www.cnblogs.com/fslnet/p/3143344.html

# https://www.cnblogs.com/Richardzhu/p/3318595.html

5.触发器

# 5.触发器

#5.1创建触发器

# 另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，

# 因此在一个表上最多建立6个触发器。CREATE TABLEstudent_info(

stu_noint not null auto_increment primary key,

stu_namevarchar(255)

)ENGINE=InnoDB auto_increment=1 default charset=utf8;CREATE TABLEstudent_count(

student_countint default 0);

DELIMITER//

CREATE triggertrigger_name

afterinsert

on student_info foreach rowBEGIN

update student_count SET student_count = student_count+1;END //DELIMITER ;CREATE triggertrigger_del

afterdelete

on student_info foreach rowupdate student_count set student_count= student_count-1;insert into student_info(stu_name) values('vic3');delete from student_info where stu_no=2;

#5.2查看触发器

show triggers[from schema_name];

#5.3删除触发器DROP trigger [if EXISTS] [schema_name]trigger_name;

#5.4触发器执行顺序/*①如果 BEFORE 触发器执行失败，SQL 无法正确执行。

②SQL 执行失败时，AFTER 型触发器不会触发。

③AFTER 类型的触发器执行失败，SQL 会回滚。*/# 参考资料：https://www.cnblogs.com/CraryPrimitiveMan/p/4206942.html

6.事务处理

# 6事务/*事务是一条或多条数据库操作语句的组合 具备acid

原子性：要不全部成功，要不全部撤销

隔离性：事务之间相互独立，互不干扰

一致性：数据库正确地改变状态后，数据库的一致性约束没有被破坏

持久性：事务的提交结果，将持久保存在数据库中

1.事务并发出现的问题

脏读，不可重复读(修改)，更新丢失，幻读(新增或删除)

2.解决并发问题

(1)READ_UNCOMMITTED

这是事务最低的隔离级别，它充许另外一个事务可以看到这个事务未提交的数据。

解决第一类丢失更新的问题，但是会出现脏读、不可重复读、第二类丢失更新的问题，幻读 。

(2)READ_COMMITTED

保证一个事务修改的数据提交后才能被另外一个事务读取，即另外一个事务不能读取该事务未提交的数据。

解决第一类丢失更新和脏读的问题，但会出现不可重复读、第二类丢失更新的问题，幻读问题

(3)REPEATABLE_READ；默认级别

保证一个事务相同条件下前后两次获取的数据是一致的

解决第一类丢失更新，脏读、不可重复读、第二类丢失更新的问题，但会出幻读。

(4)SERIALIZABLE

事务被处理为顺序执行。

解决所有问题*/#6.2使用

starttransaction;update t set number=2131 where id=2;update t set number=3141 where id=3;select count(*) fromt;commit;rollback;

7.mysql 锁机制

# 7.mysql的锁机制/*1. 共享锁：读锁 不好阻塞其他读操作，会阻塞其他用户写操作

2. 排它锁：写锁 阻塞其他用户的读写操作

MyISM默认的锁调度机制是写优先*/lock tablesorder read local, order_detail readlocal;select sum(total) fromorders;select sum(subtotal) fromorder_detail;

unlock tables;

#7.2整理空间碎片

optimizetablet_name;#myisam下，innodb需要独享表空间

show variableslike 'innodb_file_per_table';#off不是独享表空间

analyzetablet_name;#innodb 优化表

#7.3InnoDB锁机制

show statuslike 'innodb_row_lock%';#查看锁状态set autocommit=0;

lock tables t1 write,t2read;[]

commit;

unlock tables;

# 参考资料：https://www.cnblogs.com/chenqionghe/p/4845693.html

8.视图

# 8视图

#8.1创建视图DROP view IF EXISTSv1;CREATE ALGORITHM =UNDEFINED

DEFINER=`root`@`localhost`

SQL SECURITY DEFINERview v2 as select id,number fromt;/*几点说明(MySQL中的视图在标准SQL的基础之上做了扩展)：

ALGORITHM=UNDEFINED：指定视图的处理算法；

DEFINER=`root`@`localhost`：指定视图创建者；

SQL SECURITY DEFINER：指定视图查询数据时的安全验证方式；*/#8.2视图修改update v1 set number=1 where id=1;

# 注意：不能在一张由多张关联表连接而成的视图上做同时修改两张表的操作

#8.3增加insert into v1(number) values(2000);--注意：表中的其他字段，要允许为空，否则操作失败

# 8.4删除delete from v1 where id=8;

#8.5删除视图DROP view IF EXISTS v1;

9.mysql的导出和导入

# 9.数据导入导出

#9.1第一种方法

mysql-hlocalhost -uroot -proot input_out < E:\phpwebenv\PHPTutorial\WWW\test\dump.sql --default-character-set=utf8

# 第二种方法useinput_out;

source E:\phpwebenv\PHPTutorial\WWW\test\input.sql;

#9.2导出数据

mysqldump-hlocalhost -uroot -proot test_db > E:\phpwebenv\PHPTutorial\WWW\test\dump.sql --default-character-set=utf8

#9.3备份和还原

mysqldump-uroot -proot input_out t >E:\phpwebenv\PHPTutorial\WWW\test\back.sql

mysqldump-uroot -proot --databases input_out test_db > backup.sql # 备份多个数据库

mysql-uroot -proot < E:\phpwebenv\PHPTutorial\WWW\test\backup.sql

导出mysqldump详细介绍：

mysqldump -uroot -proot --databases db1 --tables a1 a2 >/tmp/db1.sql

--按照where条件

mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' >/tmp/a1.sql

--只导表结构

mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql

sql 语句导出excel 文件

mysql> show variables like '%secure%';+--------------------------+-----------------------+

| Variable_name | Value |

+--------------------------+-----------------------+

| require_secure_transport | OFF |

| secure_auth | ON |

| secure_file_priv | /var/lib/mysql-files/ |

select * into outfile "/var/lib/mysql-files/reports.xls" from mv_reprots;

如果导出表中的部分数据如何处理；

mysql -uroot -p mv -e "select id,name from system into outfile '/var/lib/mysql-files/my_name.sql'"

报错：

ERROR 1290 (HY000) at line 1: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

解决：

show variables like '%secure%';+------------------+-----------------------+

| Variable_name | Value |

+------------------+-----------------------+

| secure_auth | ON |

| secure_file_priv | /var/lib/mysql-files/ |

+------------------+-----------------------+

2 rows in set (0.00 sec)

导出的文件必须放到这个目录项目，

数据：

# 导出excel文件

mysql> select * from refer into outfile '/var/lib/mysql-files/refer.xls'\G