搜索
简帛阁>技术文章>MySQL 视图、触发器、函数、存储过程

MySQL 视图、触发器、函数、存储过程

1. 视图

1.1 什么是视图

通俗来讲,视图就是一条 select 语句执行后返回的结果集。所有我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上。

 

1.2 视图的特性

视图是对若干张基本表的引用,一张虚表,查询语句的执行结果,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变)

 

1.3 视图的作用

方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性;更加安全,数据库授权命令不能限定到特定的行和特定的列,但通过合理创建视图,可以把权限限定到行列级别;

 

1.4 使用场合

权限控制的时候,不希望用户访问表中某些敏感信息的列,比如 salary… 关键信息来源于多个复杂关联表,可以创建视图提取我们需要的信息,简化操作;

 

1.5 视图的使用

视图实例1-创建视图及查询数据操作

现有三张表:用户(user)、课程(course)、用户课程中间表(user_course),表结构及数据如下:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');
INSERT INTO `course` VALUES ('3', '英语');
INSERT INTO `course` VALUES ('4', '物理');
INSERT INTO `course` VALUES ('5', '');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `course_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_student_course` (`course_id`),
  CONSTRAINT `fk_student_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '小飞', '1');
INSERT INTO `student` VALUES ('2', 'hukey', '2');
INSERT INTO `student` VALUES ('3', '小王', '3');
INSERT INTO `student` VALUES ('4', '阿狗', '4');
表定义及数据

这时,当我们想要查询小飞上的所以课程相关信息的时候,需要这样写一条长长的SQL语句,如下:

SELECT sid, sname, student.name from course 
LEFT JOIN student on course.sid = student.course_id
where student.name = '小飞';

但是我们可以通过视图简化操作,例如我们创建视图 view_student_course 如下:

create ALGORITHM = UNDEFINED
DEFINER = 'root'@'%'
SQL SECURITY DEFINER
VIEW view_student_course AS (
SELECT sid, sname, student.name from course 
LEFT JOIN student on course.sid = student.course_id
);

几点说明(MySQL中的视图在标准SQL的基础之上做了扩展):

ALGORITHM=UNDEFINED:指定视图的处理算法;
DEFINER=`root`@`localhost`:指定视图创建者;
SQL SECURITY DEFINER:指定视图查询数据时的安全验证方式;

 

创建好视图之后,我们可以直接用以下SQL语句在视图上查询小飞上的所以课程相关信息,同样可以得到所需结果:

SELECT * from view_student_course where name = '小飞';

 

可以尝试对视图进行增删改操作,这里总结如下:

  (1)视图与表是一对一关系情况:如果没有其它约束(如视图中没有的字段,在基本表中是必填字段情况),是可以进行增删改数据操作;
  (2)视图与表是一对多关系情况:如果只修改一张表的数据,且没有其它约束(如视图中没有的字段,在基本表中是必填字段情况),是可以进行改数据操作;

 

除了以上两条外都是无法进行增删改,但是强烈不建议直接对视图进行增删改操作,可能不经意就修改了真实表中的多条数据

 

查看库中的视图:

show table status where comment = 'view';

 

 

 

2. 触发器

 

2.1 什么是触发器

触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。

触发器的特性:

  1. 在 begin end体, begin … end; 之间的语句可以写的简单或者复杂
  2. 什么条件触发:insert、update、delete
  3. 什么时候触发:在增删改前或者后
  4. 触发频率: 针对每一行执行
  5. 触发器定义在表上,附着在表上

 

也就是由事件来触发某个操作,事件包括INSERT语句,UPDATE语句和DELETE语句;可以协助应用在数据库端确保数据的完整性。

 

尽量少使用触发器、不建议使用

假设触发器触发每次执行1s,insert table 500条数据,那么就需要触发500次触发器,光是触发器执行的时间就花费了500s,而insert 500条数据一共是1s,那么这个insert的效率就非常低了。因此我们特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高,资源消耗要小。

 

2.2 触发器的创建

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

 

trigger_time: { BEFORE | AFTER }
    BEFORE 和 AFTER 参数指定了触发的时间,在事件之前或之后

FOR EACH ROW
    表示任何一条记录上的操作满足触发事件都会触发该触发器,也就是说触发器的触发频率是针对每一行数据触发一次。
    
trigger_event: { INSERT | UPDATE | DELETE }
    (1)INSERT型触发器:插入某一行时激活触发器,可能通过INSERT、LOAD DATA、REPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中,相当与一系列的INSERT操作);
    (2)UPDATE型触发器:更改某一行时激活触发器,可能通过UPDATE语句触发;
    (3)DELETE型触发器:删除某一行时激活触发器,可能通过DELETE、REPLACE语句触发。

 

2.3 创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;

例1:创建了一个名为trig1的触发器,一旦在work表中有插入动作,就会自动往time表里插入当前时间

mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());

 

2.4 创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
        执行语句列表
END;
mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    -> INSERT INTO time VALUES(NOW());
    -> INSERT INTO time VALUES(NOW());
    -> END||
mysql> DELIMITER ;

 

 

2.5 NEW 和 OLD 详解

MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中,触发了触发器的那一行数据,来引用触发器中发生变化的记录内容,具体地:
    (1)在INSERT型触发器中,NEW用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
    (2)在UPDATE型触发器中,OLD用来表示将要或已经被修改的原数据,NEW用来表示将要或已经修改为的新数据;
    (3)在DELETE型触发器中,OLD用来表示将要或已经被删除的原数据;

 

使用方法:

  NEW.columnName (columnName为相应数据表某一列名)

另外,OLD是只读的,而NEW则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用(如每插入一个学生前,都在其学号前加“2013”)。

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);

mysql> delimiter $$
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    -> IF NEW.amount < 0 THEN
    -> SET NEW.amount = 0;
    -> ELSEIF NEW.amount > 100 THEN
    -> SET NEW.amount = 100;
    -> END IF;
    -> END$$
mysql> delimiter ;

mysql> update account set amount=-10 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |    0.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+

mysql> update account set amount=200 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |  100.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+

 

 

2.6 查看触发器

mysql> SHOW TRIGGERS\G;
……

结果,显示所有触发器的基本信息;无法查询指定的触发器。


在information_schema.triggers表中查看触发器信息
mysql> SELECT * FROM information_schema.triggers\G
……

结果,显示所有触发器的详细信息;同时,该方法可以查询制定触发器的详细信息。
mysql> select * from information_schema.triggers 
    -> where trigger_name='upd_check'\G;

 

 

删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

 

删除触发器之后最好使用上面的方法查看一遍;同时,也可以使用database.trig来指定某个数据库中的触发器。

 

注意:
如果不需要某个触发器时一定要将这个触发器删除,以免造成意外操作,这很关键。

 

3. 函数

 

3.1 什么是函数

函数存储着一系列sql语句,调用函数就是一次性执行这些语句。所以函数可以降低语句重复。但要注意的是函数注重返回值,不注重执行过程,所以一些语句无法执行。所以函数并不是单纯的sql语句集合。mysql有内置函数,也能够自定义函数

补充函数与存储过程的区别:函数只会返回一个值,不允许返回一个结果集。函数强调返回值,所以函数不允许返回多个值的情况,即使是查询语句。

 

3.2 函数的创建

语法:

Create function function_name(参数列表)
returns 返回值类型
BEGIN
函数体内容
END

相关说明:

        函数名:应该合法的标识符,并且不应该与已有的关键字冲突。一个函数应该属于某数据库,可以使用db_name.funciton_name的形式执行当前函数所属数据库,否则默认为当前数据库。
        参数列表:可以有一个或多个函数参数,甚至是没有参数也是可以的。对于每个参数,由参数名和参数类型组成。
        返回值: 指明返回值类型
        函数体:自定义函数的函数体由多条可用的MySQL语句,流程控制,变量申明等语句构成。需要指明的是函数体中一定要含有return 返回语句。

 

3.3 自定义示例

  (1)无参数函数定义

delimiter $$

CREATE FUNCTION hello()
RETURNS VARCHAR(255)
BEGIN
RETURN 'Hello world, i am mysql';
END $$

delimiter ;

 

调用函数:

MariaDB [db1]> select hello();
+-------------------------+
| hello()                 |
+-------------------------+
| Hello world, i am mysql |
+-------------------------+

 

  (2)含有参数的自定义函数

delimiter $$
CREATE FUNCTION f1(
    t1 int,
    t2 int)
RETURNS INT
BEGIN
    DECLARE num int;
  set num = t1 + t2;
  RETURN(num);
END $$
delimiter ;

 

调用函数:

MariaDB [db1]> select f1(1, 100);
+------------+
| f1(1, 100) |
+------------+
|        101 |
+------------+

 

3.4 查看库中的函数

-- 查看函数
show FUNCTION status;

-- 查看函数的创建过程:
show create function func_name;

 

 

4. 存储过程

 

4.1 什么是存储过程

一组可编程的函数,是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

优点:
    (1)将重复性很高的一些操作,封装到一个存储过程中,简化了对这些SQL的调用;
    (2)批量处理:SQL+循环,减少流量,也就是“跑批”;
    (3)统一接口,确保数据的安全

 

相对于oracle数据库来说,MySQL的存储过程相对功能较弱,使用较少。

 

4.2 存储过程的创建和调用

  存储过程就是具有名字的一段代码,用来完成一个特定的功能,创建的存储过程保存在数据库的数据字典中。

 

创建存储过程

 

CREATE
    [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
Valid SQL routine statement

[begin_label:] BEGIN
[statement_list]
……
END [end_label]

 

 

 现有两张表(userinfo)和(teacher)表,表结构及数据:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '周杰伦');
INSERT INTO `teacher` VALUES ('2', '那英');
INSERT INTO `teacher` VALUES ('3', '汪峰');
INSERT INTO `teacher` VALUES ('4', '哈林');

-- ----------------------------
-- Table structure for userinfo
-- ----------------------------
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES ('1', 'admin', 'admin');
INSERT INTO `userinfo` VALUES ('2', 'superman', '123456');
INSERT INTO `userinfo` VALUES ('3', 'batman', '666');
表结构及数据

 

 

创建一个存储过程:

delimiter $$
CREATE PROCEDURE p1()
BEGIN
    select * from teacher;
    insert into userinfo(username, password) VALUES ('xiaoA', '123');
END $$
delimiter ;

 

 

执行存储过程:

MariaDB [db1]> call p1;
+-----+-----------+
| tid | name      |
+-----+-----------+
|   1 | 周杰伦    |
|   2 | 那英      |
|   3 | 汪峰      |
|   4 | 哈林      |
+-----+-----------+
4 rows in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

解析:
    这个存储过程做了两件事,一个是查询所有的teacher,另一个就是向student表中插入一条数据

 

 4.3 存储过程的参数

   存储过程可以有 0 个或多个参数,用于存储过程的定义。

  3 种参数类型:

    (1)IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量);

    (2)OUT输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量);

    (3)INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

 

IN输入参数的使用

delimiter $$
CREATE PROCEDURE p2(in t1 int)
BEGIN
    SELECT t1;
  set t1 = 2;
    SELECT t1;
END $$
delimiter ;

 

 调用存储过程:

MariaDB [db1]> set @t1 = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> call p2(@t1);
+------+
| t1   |
+------+
|    1 |
+------+


+------+
| t1   |
+------+
|    2 |
+------+

MariaDB [db1]> select @t1;
+------+
| @t1  |
+------+
|    1 |
+------+

以上可以看出,t1 在存储过程中被修改,但并不影响@t1 的值,因为前者为局部变量、后者为全局变量。

 

OUT 输出参数

delimiter $$
CREATE PROCEDURE p3(out t_out int)
BEGIN
    SELECT t_out;
  set t_out = 2;
    SELECT t_out;
END $$
delimiter ;

调用存储过程:

MariaDB [db1]> set @t_out =1 ;

MariaDB [db1]> call p3(@t_out);
+-------+
| t_out |
+-------+
|  NULL |
+-------+
# 因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null

+-------+
| t_out |
+-------+
|     2 |
+-------+

MariaDB [db1]> select @t_out;
+--------+
| @t_out |
+--------+
|      2 |
+--------+
# 调用了 p3 存储过程,输出参数,改变了 t_out 变量的值

 

 

inout输入参数

 

delimiter $$
CREATE PROCEDURE p4(inout t_inout int)
BEGIN
    SELECT t_inout;
  set t_inout = 2;
    SELECT t_inout;
END $$
delimiter ;

 

 调用存储过程:

MariaDB [db1]> set @t_inout = 1;

MariaDB [db1]> call p4(@t_inout);
+---------+
| t_inout |
+---------+
|       1 |
+---------+

+---------+
| t_inout |
+---------+
|       2 |
+---------+

MariaDB [db1]> select @t_inout;
+----------+
| @t_inout |
+----------+
|        2 |
+----------+

 

 

调用了 p4 存储过程,接受了输入的参数,也输出参数,改变了变量

 

注意:

   (1)如果过程没有参数,也必须在过程名后面写上小括号

   (2)确保参数的名字不等于列的名字,否则在过程体中,参数名被当做列名来处理

 

建议使用:

  输入值使用 in 参数;

  输入值使用 in 参数;

  inout参数就尽量少用

 

4.4 存储过程-事务

  在执行一个存储过程中,我们无法确定这个存储过程是否执行成功,如果执行失败,我们是否要考虑回滚的问题。这里就需要存储过程对于事务的支持:

delimiter //
create procedure p4(
    out status int
)
BEGIN
    1. 声明如果出现异常则执行{
        set status = 1;
        rollback;
    }
       
    开始事务
        -- 由秦兵账户减去100
        -- 方少伟账户加90
        -- 张根账户加10
        commit;
    结束
    
    set status = 2;
    
    
END //
delimiter ;

存储过程支持事务如下:

delimiter $$
CREATE PROCEDURE p5(out p_return_code tinyint)
BEGIN
    DECLARE exit HANDLER for SQLEXCEPTION
    BEGIN
    -- 执行失败,则返回 1
        set p_return_code = 1;
        ROLLBACK; -- 如果出错,则回滚
    END;
    START TRANSACTION;
    INSERT into userinfo(username, password) VALUES ('xiaoB', '222');
    COMMIT;
    -- 执行成功,则返回 2
    set p_return_code = 2;
END $$
delimiter ;

执行:
MariaDB [db1]> set @p_return_code=0;

MariaDB [db1]> call p5(@p_return_code);

MariaDB [db1]> select @p_return_code;
+----------------+
| @p_return_code |
+----------------+
|              2 |
+----------------+

变量 p_return_code = 2 说明存储过程执行成功。

 

4.5 使用 pymysql 模块调用存储过程

import  pymysql

config = {
    'host': '192.168.118.11',
    'user': 'root',
    'password': '123456',
    'database': 'db1'
}

db = pymysql.connect(**config)
with db.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
    cursor.callproc('p3', (0,))   # 使用 callproc 调用存储过程
    cursor.execute('select @_p3_0') # 查询 out 参数的返回值
    r2 = cursor.fetchall()  # 获取返回值
    print(r2)

执行结果:
[{'@_p3_0': 2}]

 

 

 4.6 查看存储过程

-- 查看存储过程:
show procedure status; 

-- 查看存储过程创建的过程:
show create procedure proc_name;

 

1视图视图的概念视图就是一条select语句执行后返回的结果集。视图的特性视图是对若干张基本表的引用,一张虚表,查询语句的执行结果,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变)视图的作用
1视图11什么是视图通俗来讲,视图就是一条select语句执行后返回的结果集。所有我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上。12视图的特性视图是对若干张基本表的引用,一张虚表,
视图:也就是一个虚拟表(不是真实存在的),它的本质就是根据SQL语句获取动态的数据集,并为其命名。用户使用时只需要使用命名的视图即可获取结果集,并可以当做表来使用。它的作用就是方便查询操作,减少复杂的
视图:一个临时表被反复使用的时候,对这个临时表起一个别名,方便以后使用,就可以创建一个视图,别名就是视图的名称。视图只是一个虚拟的表,其中的数据是动态的从物理表中读出来的,所以物理表的变更回改变视图
文章目录一、Navicat可视化软件二、python操作MySQL1下载模块2基本使用3SQL注入4其他操作三、视图1、什么是视图2、为什么要用视图3、如何用视图四、触发器1为何要用触发器2创建触发器
1、存储过程procedure(1)概念存储过程是数据库中的一个对象,存储在服务端,用来封装多条SQL语句且带有逻辑性,可以实现一个功能,由于他在创建时,就已经对SQL进行了编译,所以执行效率高,而
心得:对我来说现在最重要的是时间,一分一秒都不想去浪费它。视图视图是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结果集,可以将该
阅读目录本文内容:视图触发器事务存储过程内置函数流程控制索引一、视图视图就是通过查询得到一张虚拟表,然后保存下来,下次直接使用即可。如果要频繁使用一张虚拟表,可以不用重复查询视图使用方法:注:如果你对
视图什么是视图视图的作用是什么?视图(view)是一种虚拟存在的表,是一个逻辑表,它本身是不包含数据的。作为一个select语句保存在数据字典中的。通过视图,可以展现基表(用来创建视图的表叫做基表
Mysql视图触发器存储过程游标游标是从数据表中提取出来的数据,以临时表的形式存放在内存中,在游标中有一个数据指针,在初始状态下指向的是首记录,利用fetch语句可以移动该指针,从而对游标中的数据进行