mysql的存储过程，函数，事件，权限，触发器，事务，锁，视图，导入导出
1.创建过程
1.1 简单创建
-- 创建员工表 DROP TABLE IF EXISTS employee; CREATE TABLE employee( id int auto_increment primary key, `name` varchar(50), depid int ); insert into employee(name,depid) values('vic',1),('myvic',2),('liu',2); -- 1.创建存储过程 DROP PROCEDURE IF EXISTS pro_employee; DELIMITER // CREATE PROCEDURE pro_employee(IN pdepid INT,OUT pcount INT) BEGIN SELECT COUNT(id) INTO pcount FROM employee WHERE depid=pdepid; #into是把值赋值给pcount END // DELIMITER ; #调用 call pro_employee(2,@pcount); select @pcount; -- 2. 返回最后的id值 DROP PROCEDURE IF EXISTS pro_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 PROCEDURE p5() BEGIN DECLARE i int; set i = 1; while i<5 do insert into employee(name,depid) values(concat('vic',i),i); set i=i+1; end while; END // DELIMITER ;
1.2 存储过程的修改
一般不太支持；
2.函数
-- 1.函数的创建 DELIMITER // CREATE FUNCTION getSum(num INT) RETURNS INT # 返回值类型 BEGIN SET num = num+1; RETURN num; END // DELIMITER ; -- 2.函数的删除 DROP FUNCTION IF EXISTS func;
-- 3.查看所有的函数
show FUNCTION status;
3.事件
-- 1 创建事件 # 查看调度器 show variables like '%event_scheduler%'; # show variables 可以查看系统变量及值 # show status 系统运行的状态，不可更改； #开启调度器 SET GLOBAL event_scheduler = ON; SET GLOBAL event_scheduler = OFF;#关闭 #查看调度器线程 show processlist; # 测试表 DROP TABLE IF EXISTS events_list; CREATE TABLE events_list( id int auto_increment primary key, event_name varchar(20) not null, event_started timestamp not null ); # 事件1 (立即启动事件) CREATE EVENT IF not EXISTS event_now ON SCHEDULE AT now() do insert into events_list(event_name,event_started) values('event_now',now()); # 事件2 (每分钟启动事件) create event events_minute ON schedule every 1 minute do insert into events_list(event_name,event_started) values('event_minute',now()); # 查询事件 show events; select * from mysql.event; #查看所有事件 #修改时间 alter event event_minute on schedule every 30 second do insert into events_list(event_name,event_started) value('event_second',now()); # 修改为不再次活动 alter event event_second disable; alter event event_second enable; # 再次活动 # 删除事件 DROP EVENT IF EXISTS event_name;
4.权限
# 1 权限 # 1.1 创建用户 use mysql; create user li@localhost IDENTIFIED BY 'li'; #identified by 会将纯文本加密作散列值存储 # 1.2 修改用户 rename user 'li'@'localhost' to 'newuser'@'localhost'; # 1.3 删除用户 DROP user 'li'@'localhost'; # 1.4 更改密码 set password for li@localhost = password('root'); # 1.5 查看用户权限 show grants for 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 立即使用 flush privileges; /* 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 TABLE student_info( stu_no int not null auto_increment primary key, stu_name varchar(255) )ENGINE=InnoDB auto_increment=1 default charset=utf8; CREATE TABLE student_count( student_count int default 0 ); DELIMITER // CREATE trigger trigger_name after insert on student_info for each row BEGIN update student_count SET student_count = student_count+1; END // DELIMITER ; CREATE trigger trigger_del after delete on student_info for each row update 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 使用 start transaction; update t set number=2131 where id=2; update t set number=3141 where id=3; select count(*) from t; commit; rollback;
7.mysql 锁机制
# 7.mysql的锁机制 /* 1. 共享锁：读锁 不好阻塞其他读操作，会阻塞其他用户写操作 2. 排它锁：写锁 阻塞其他用户的读写操作 MyISM默认的锁调度机制是写优先 */ lock tables order read local, order_detail read local; select sum(total) from orders; select sum(subtotal) from order_detail; unlock tables; # 7.2 整理空间碎片 optimize table t_name;#myisam下，innodb需要独享表空间 show variables like 'innodb_file_per_table';#off不是独享表空间 analyze table t_name;#innodb 优化表 # 7.3 InnoDB锁机制 show status like 'innodb_row_lock%';#查看锁状态 set autocommit=0; lock tables t1 write,t2 read; [] commit; unlock tables; # 参考资料：https://www.cnblogs.com/chenqionghe/p/4845693.html
8.视图
# 8 视图 # 8.1 创建视图 DROP view IF EXISTS v1; CREATE ALGORITHM = UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER view v2 as select id,number from t; /* 几点说明（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 # 第二种方法 use input_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
