从零开始的Linux运维屌丝之路,资源免费分享平台   运维人员首选:简单、易用、高效、安全、稳定、社区活跃的开源软件
  • 首页
  • MYSQL
  • 08 - Mysql视图 触发器 函数 存储过程 事务 游标

08 - Mysql视图 触发器 函数 存储过程 事务 游标

发布:蔺要红08-11分类: MYSQL


视图   (了解即可,不用)
虚拟表,从一个表或多个表中导出来的表,作用和真实表一样,包含一系列带有行和列的数据 视图中,
用户可以使用SELECT语句查询数据,也可以使用INSERT,UPDATE,DELETE修改记录,视图可以使用户操作方便,并保障数据库系统安全

优点
简单化,数据所见即所得
安全性,用户只能查询或修改他们所能见到得到的数据
逻辑独立性,可以屏蔽真实表结构变化带来的影响

缺点
性能相对较差,简单的查询也会变得稍显复杂
修改不方便,特变是复杂的聚合视图基本无法修改

create view 视图名称 as  CREATE VIEW v1  AS  SELECT * from test;   # 创建
alter view 视图名称 as  SQL    # 修改
drop view 视图名称;            # 删除 

SELECT * from A  # 直接从视图里查数据 A 为视图名称

触发器
 
当对某张表做:增删改操作时,可以使用触发器自定义关联行为(代码可也控制,触发器也可以)

扩展知识:
delimiter //
CREATE TRIGGER t1 BEFORE INSERT ON student FOR EACH ROW 
BEGIN
	INSERT INTO teacher(tname) VALUES(NEW.sname);
END //
delimiter ;

-- delimiter // 修改数据库语句的终止符,这里使用了 // 创建完触发器后又修改为;


NEW : 代指新数据插入
OLD :老数据,删除和更新都有OLD

after是先完成数据的增删改,再触发,触发的语句晚于监视的增删改操作
before是先完成触发,再增删改,触发的语句先于监视的增删改,

-- 删除触发器  t1代表触发器名称
DROP TRIGGER t1;

-- 实例:创建插入前的触发器
CREATE TRIGGER t2 BEFORE INSERT ON student FOR EACH ROW 
BEGIN
	INSERT INTO teacher(tname) VALUES(NEW.sname);
END;

-- 测试
INSERT into student(sname,gender,age,class_id) VALUES("测试","av",18,1),("测试","av",18,1)
-- 插入后会自动往teacher表里添加两条数据,student表每变动一行都会插入

-- -------------------------------------------------------------------------------------------------------

-- 创建删除的触发器
delimiter //
CREATE TRIGGER t1 BEFORE DELETE ON student FOR EACH ROW 
BEGIN
	INSERT INTO teacher(tname) VALUES(OLD.sname);
END //
delimiter ;

-- 测试
DELETE from student WHERE sname="测试"
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...;
END;

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...;
END;

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...;
END;

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...;
END;

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...;
END;

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...;
END;

函数

为了简化操作,mysql提供了大量的函数给程序员使用(比如你想输入当前时间,可以调用now()函数)
函数可以出现的位置:插入语句的values()中,更新语句中,删除语句中,查询语句及其子句中

 
SELECT CURRENT_DATE; -- 显示年月日
SELECT CURRENT_TIME; -- 显示时分秒
SELECT CURRENT_TIMESTAMP; -- 显示全部时间

时间格式化
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
-- 如果数据里存的是2019-11-11 12:20可以通过时间格式化让其显示2019-11-11

DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
);
INSERT into blog(create_time)  VALUES
('2019-07-28 17:54:29'),('2019-06-28 17:54:29'),
('2019-06-28 17:54:29'),('2019-06-28 17:54:29'),
('2019-07-28 17:54:29'),('2019-07-28 17:54:29'),
('2019-06-28 17:54:29'),('2019-06-28 17:54:29'),
('2019-07-28 17:54:29'),('2019-04-28 17:54:29');

-- 时间格式化

mysql> SELECT DATE_FORMAT(create_time,"%Y-%m"),count(1) from blog  GROUP BY DATE_FORMAT(create_time,"%Y-%m");
+----------------------------------+----------+
| DATE_FORMAT(create_time,"%Y-%m") | count(1) |
+----------------------------------+----------+
| 2019-04                          |        1 |
| 2019-06                          |       13 |
| 2019-07                          |       12 |
| 2019-08                          |        4 |
+----------------------------------+----------+
4 rows in set (0.00 sec)
字符串函数
https://dev.mysql.com/doc/refman/5.7/en/string-functions.html
SELECT CHAR_LENGTH("linyaohong"); -- 统计字符串长度
SELECT CONCAT('lin','yao','hong'); -- 字符串拼接

自定义函数
-- 自定义函数
CREATE FUNCTION f2(
	i1 int,
	i2 int)
returns int
BEGIN
	declare num int DEFAULT 0;  -- 可以设置默认值为0,设置变量
	set num = i1 +i2;
	return(num);
END; 

-- 调用自定义函数(可以传参,有返回值)
mysql> select f2(10,2);
+----------+
| f2(10,2) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

存储过程(Stored Procedure):是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数:  表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值。
OUT 输出参数:  该值可在存储过程内部被改变,并可返回。
INOUT 输入输出参数:  调用时指定,并且可被改变和返回

 
-- ----------------------简单存储过程(in,OUT,INOUT)
CREATE PROCEDURE p1()
BEGIN
	SELECT * FROM student;
	INSERT INTO class(caption) VALUES("测试班级");
END;
-- -- 调用存储过程
-- CALL p1()

-- ----------------------创建传参存储过程 (in)
CREATE PROCEDURE p2(
	IN n1 int,
	IN n2 int
	)
BEGIN
	SELECT * FROM student WHERE sid >n1;
END;
-- 调用存储过程
CALL p2(8,2)

-- ----------------------创建传参存储过程 (in,OUT) OUT必须是变量
CREATE PROCEDURE p3(
	IN n1 int,
	OUT n2 int  -- 可以用于标识存储过程的执行结果 1 2 3 
	)
BEGIN
	set n2 = 123456;
	SELECT * FROM student WHERE sid >n1;
END;

-- 调用存储过程
set @v1 = 0;
CALL p3(8,@v1);
SELECT @v1;

-- -------------------------- 创建传参存储过程 (INOUT)
CREATE PROCEDURE p4(
	INOUT n1 int,
	in n2 int
	)
BEGIN
	INSERT INTO class(caption) VALUES(n1);
	INSERT INTO class(caption) VALUES(n2);
	SELECT * FROM student WHERE sid >n2;
END;

set @v1 = 123456789000;
call p4(@v1,7);
SELECT @v1;
-- 利用存储过程批量插入简单的数据
DROP TABLE IF EXISTS `test`; 
CREATE TABLE test(
	id int PRIMARY key auto_increment,
	num int not null
	)ENGINE=INNODB CHARSET=UTF8;
	
create PROCEDURE pro_test(in para int(11))
	BEGIN
		declare i int DEFAULT 0;
		declare rand_num int;
		while i < para do
			SELECT cast(rand()*10000 as UNSIGNED) into rand_num;
			INSERT into test(num) VALUES(rand_num);
			set i = i+1;
		end while;
	END;
	
call pro_test(500000)


事务

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
事务用来管理insert,update,delete语句


一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
1、事务的原子性:一组事务,要么成功;要么撤回。
2、稳定性 : 有非法数据(外键约束之类),事务撤回。
3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
 
结合上文所述的存储过程的事务
 
mysql> show global variables like "%autocommit%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
-- 如果错误返回 1 如果warning返回 2 如果全部执行完毕并且提交则返回 0 

delimiter \\
	create PROCEDURE p1(
			OUT p_return_code tinyint
	)
	BEGIN 
		DECLARE exit handler for sqlexception 
		BEGIN 
			-- ERROR 
			set p_return_code = 1; 
			rollback; 
		END; 
	 
		DECLARE exit handler for sqlwarning 
		BEGIN 
			-- WARNING 
			set p_return_code = 2; 
			rollback; 
		END; 
	 
		START TRANSACTION; 
			DELETE from tb1;
			insert into tb2(name)values('seven');
		COMMIT; 
	 
		-- SUCCESS 
		set p_return_code = 0; 
	 
		END\\
delimiter ;
 
-- 在Mysql控制台使用事务来操作

mysql> begin;            #开始一个事务
mysql> insert into student (name) values(lin);
mysql> rollback;           #回滚这样数据是不会写入的

当然如果上面的数据没问题,就输入commit提交命令就行
mysql> commit;    # commit 提交
 
游标 (只有当对每行进行计算的时候才会使用,一般不使用,性能低)
 
-- 创建相关表
CREATE TABLE `A`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `num` int(0) NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8;

INSERT INTO `a`(`num`) VALUES (99);
INSERT INTO `a`(`num`) VALUES (98);
INSERT INTO `a`(`num`) VALUES (97);

-- 创建存储过程

delimiter //
create procedure p6()
begin 
	declare row_id int; -- 自定义变量1  
	declare row_num int; -- 自定义变量2 
	declare done INT DEFAULT FALSE;
	declare temp int;
	
	declare my_cursor CURSOR FOR select id,num from A;
	declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	
	open my_cursor;
		xxoo: LOOP
			fetch my_cursor into row_id,row_num;
			if done then 
				leave xxoo;
			END IF;
			set temp = row_id + row_num;
			insert into B(number) values(temp);
		end loop xxoo;
	close my_cursor;
end  //
delimiter ;

-- 调用存储过程

call p6();

动态执行SQL(防SQL注入)
delimiter \\
CREATE PROCEDURE p7(
		in nid int
)
BEGIN
	set @nid = nid
	PREPARE prod FROM 'select * from student where sid > ?'; -- 准备
	EXECUTE prod USING @nid; -- @nid 代替? 而且只支持@
	DEALLOCATE prepare prod; 
END\\
delimiter ;
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,如有侵权我会在24小时之内删除!

欢迎使用手机扫描访问本站