MySQL之视图、存储过程、函数、触发器
一.视图(View)
1.视图概述
视图是一种虚拟存在的表,实际并不存在。其数据来自于数据库中自定义的表,实际是查询那些表动态生成的数据。
2.视图的使用
#语法:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
举个栗子:
class表:
student表:
创建视图:
CREATE VIEW student_info_view
AS
SELECT s.id,s.name,s.class_id,c.class_name FROM student s ,class c WHERE s.id = c.id;
查看视图:
注:
更新视图中的数据实际更新到数据库中。
update student_info_view set name = 'hahaha' where id = 1;
二.存储过程(Procedure)
1.储存过程概述
存储过程是事先编译并存储于数据库的sql语句集合。
2.储存过程的使用
#语法:
CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQL语句
end ;
举个栗子:
数据库进行分表时,需要创建多张表,可以使用存储过程来创建。
#创建存储过程
CREATE PROCEDURE pro_create_student_table()
BEGIN
DECLARE `@i` INT(11);
DECLARE `@sqlstr` VARCHAR(2800);
SET `@i` = 1;
WHILE `@i` < 10 DO
SET @sqlstr = CONCAT(
"CREATE TABLE student_",`@i`," (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`name` varchar(20) NOT NULL COMMENT '学生姓名',
`age` smallint(6) DEFAULT NULL COMMENT '学生年龄',
`sex` smallint(1) DEFAULT NULL COMMENT '学生性别 0:男 1:女',
`class_id` int(11) DEFAULT NULL COMMENT '班级id',
PRIMARY KEY (`id`),
KEY `class_id` (`class_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8"
);
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
SET `@i` = `@i` + 1;
END WHILE;
END
#调用存储过程
CALL pro_create_student_table();
生成多张数据库表:
三.函数
1.函数概述
函数是事先编译并存储于数据库的sql语句集合。函数和存储过程的区别是,函数有返回值。函数分为自定义函数和内置函数。
2.自定义函数的使用
#语法:
CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
...
END;
举个栗子:
计算学生表的数据总数。
#创建函数
CREATE FUNCTION fun_student_count()
RETURNS INT
BEGIN
DECLARE `count` int(11);
SELECT count(*) into count from student;
RETURN count;
END
#调用函数
select fun_student_count();
四.触发器
1.触发器概述
触发器可以在数据库表进行Update(增、删、改)操作的之前或之后,触发sql语句的执行。
2.触发器的使用
#语法:
create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] -- 行级触发器
begin
trigger_stmt ;
end;
举个栗子:
创建student日志记录表:
create table student_logs(
id int(11) not null auto_increment,
operate_id int(11) not null comment '操作表的ID',
operate_time datetime not null comment '操作时间',
operation varchar(20) not null comment '操作类型, insert/update/delete',
primary key(`id`)
)engine=innodb default charset=utf8;
创建触发器:
CREATE TRIGGER student_logs_insert_trigger
AFTER INSERT
ON student
FOR EACH ROW
BEGIN
insert into student_logs(id,operate_id,operate_time,operation) VALUES(NULL,new.id,now(),"INSERT");
END
查看触发器:
SHOW TRIGGERS;
测试触发器:
如有不对之处 敬请指正