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语句，如下： 但是我们可以通过视图简化操作，例如我们创建视图 view_student_course 如下： 几点说明（MySQL中的视图在标准SQL的基础之上做了扩展）： 创建好视图之后，我们可以直接用以下SQL语句在视图上查询小飞上的所以课程相关信息，同样可以得到所需结果： 可以尝试对视图进行增删改操作，这里总结如下： （1）视图与表是一对一关系情况：如果没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行增删改数据操作； （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 触发器的创建 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 创建只有一个执行语句的触发器 例1：创建了一个名为trig1的触发器，一旦在work表中有插入动作，就会自动往time表里插入当前时间 2.4 创建有多个执行语句的触发器 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”）。 2.6 查看触发器 删除触发器 删除触发器之后最好使用上面的方法查看一遍；同时，也可以使用database.trig来指定某个数据库中的触发器。 注意：如果不需要某个触发器时一定要将这个触发器删除，以免造成意外操作，这很关键。 3. 函数 3.1 什么是函数 函数存储着一系列sql语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。但要注意的是函数注重返回值，不注重执行过程，所以一些语句无法执行。所以函数并不是单纯的sql语句集合。mysql有内置函数，也能够自定义函数 补充：函数与存储过程的区别：函数只会返回一个值，不允许返回一个结果集。函数强调返回值，所以函数不允许返回多个值的情况，即使是查询语句。 3.2 函数的创建 语法： 相关说明： 函数名：应该合法的标识符，并且不应该与已有的关键字冲突。一个函数应该属于某数据库，可以使用db_name.funciton_name的形式执行当前函数所属数据库，否则默认为当前数据库。 参数列表：可以有一个或多个函数参数，甚至是没有参数也是可以的。对于每个参数，由参数名和参数类型组成。 返回值： 指明返回值类型 函数体：自定义函数的函数体由多条可用的MySQL语句，流程控制，变量申明等语句构成。需要指明的是函数体中一定要含有return 返回语句。 3.3 自定义示例 （1）无参数函数定义 调用函数： （2）含有参数的自定义函数 调用函数： 3.4 查看库中的函数 4.1 什么是存储过程 一组可编程的函数，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。 优点： （1）将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用； （2）批量处理：SQL+循环，减少流量，也就是“跑批”； （3）统一接口，确保数据的安全 相对于oracle数据库来说，MySQL的存储过程相对功能较弱，使用较少。 4.2 存储过程的创建和调用 存储过程就是具有名字的一段代码，用来完成一个特定的功能，创建的存储过程保存在数据库的数据字典中。 创建存储过程 现有两张表（userinfo）和（teacher）表，表结构及数据： 创建一个存储过程： 执行存储过程： 解析： 这个存储过程做了两件事，一个是查询所有的teacher，另一个就是向student表中插入一条数据 4.3 存储过程的参数 存储过程可以有 0 个或多个参数，用于存储过程的定义。 3 种参数类型： （1）IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）； （2）OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）； （3）INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量） IN输入参数的使用 调用存储过程： 以上可以看出，t1 在存储过程中被修改，但并不影响@t1 的值，因为前者为局部变量、后者为全局变量。 OUT 输出参数 调用存储过程： inout输入参数 调用存储过程： 调用了 p4 存储过程，接受了输入的参数，也输出参数，改变了变量 注意： （1）如果过程没有参数，也必须在过程名后面写上小括号 （2）确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理 建议使用： 输入值使用 in 参数； 输入值使用 in 参数； inout参数就尽量少用 4.4 存储过程-事务 在执行一个存储过程中，我们无法确定这个存储过程是否执行成功，如果执行失败，我们是否要考虑回滚的问题。这里就需要存储过程对于事务的支持： 存储过程支持事务如下： 变量 p_return_code = 2 说明存储过程执行成功。 4.5 使用 pymysql 模块调用存储过程 4.6 查看存储过程SELECT sid, sname, student.name from course LEFT JOIN student on course.sid = student.course_idwhere student.name = '小飞';create ALGORITHM = UNDEFINEDDEFINER = 'root'@'%'SQL SECURITY DEFINERVIEW view_student_course AS (SELECT sid, sname, student.name from course LEFT JOIN student on course.sid = student.course_id);ALGORITHM=UNDEFINED：指定视图的处理算法；DEFINER=`root`@`localhost`：指定视图创建者；SQL SECURITY DEFINER：指定视图查询数据时的安全验证方式；SELECT * from view_student_course where name = '小飞';show table status where comment = 'view';
2. 触发器
CREATE [DEFINER = { user | CURRENT_USER }]TRIGGER trigger_nametrigger_time trigger_eventON tbl_name FOR EACH ROW[trigger_order]trigger_bodytrigger_time: { BEFORE | AFTER }trigger_event: { INSERT | UPDATE | DELETE }trigger_order: { FOLLOWS | PRECEDES } other_trigger_nameCREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;mysql> CREATE TRIGGER trig1 AFTER INSERT -> ON work FOR EACH ROW -> INSERT INTO time VALUES(NOW());CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件ON 表名 FOR EACH ROWBEGIN 执行语句列表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 ;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 |+----------+---------+mysql> SHOW TRIGGERSG;……结果，显示所有触发器的基本信息；无法查询指定的触发器。在information_schema.triggers表中查看触发器信息mysql> SELECT * FROM information_schema.triggersG……结果，显示所有触发器的详细信息；同时，该方法可以查询制定触发器的详细信息。mysql> select * from information_schema.triggers -> where trigger_name='upd_check'G;DROP TRIGGER [IF EXISTS] [schema_name.]trigger_nameCreate function function_name(参数列表)returns 返回值类型BEGIN函数体内容ENDdelimiter $$CREATE FUNCTION hello()RETURNS VARCHAR(255)BEGINRETURN 'Hello world, i am mysql';END $$delimiter ;MariaDB [db1]> select hello();+-------------------------+| hello() |+-------------------------+| Hello world, i am mysql |+-------------------------+delimiter $$CREATE FUNCTION f1( t1 int, t2 int)RETURNS INTBEGIN DECLARE num int; set num = t1 + t2; RETURN(num);END $$delimiter ;MariaDB [db1]> select f1(1, 100);+------------+| f1(1, 100) |+------------+| 101 |+------------+-- 查看函数show FUNCTION status;-- 查看函数的创建过程：show create function func_name;4. 存储过程
CREATE [DEFINER = { user | CURRENT_USER }]PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_bodyproc_parameter: [ IN | OUT | INOUT ] param_name typecharacteristic: 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]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)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 |+------+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 变量的值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 |+----------+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 |+----------------+import pymysqlconfig = { '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}]-- 查看存储过程：show procedure status; -- 查看存储过程创建的过程：show create procedure proc_name;
版权声明：本文内容来自CSDN：hukey，遵循CC 4.0 BY-SA版权协议上原文接及本声明。本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行可。原文链接：https://www.cnblogs.com/hukey/p/10396404.html如有涉及到侵权，请联系，将立即予以删除处理。在此特别鸣谢原作者的创作。此篇文章的所有版权归原作者所有，与本公众号无关，商业转载建议请联系原作者，非商业转载请注明出处