1.视图
#修改视图，原始表也跟着改mysql> select * from course;+-----+--------+------------+| cid | cname | teacher_id |+-----+--------+------------+| 1 | 生物 | 1 || 2 | 物理 | 2 || 3 | 体育 | 3 || 4 | 美术 | 2 |+-----+--------+------------+rows in set (0.00 sec)mysql> create view course_view as select * from course; #创建表course的视图Query OK, 0 rows affected (0.52 sec)mysql> select * from course_view;+-----+--------+------------+| cid | cname | teacher_id |+-----+--------+------------+| 1 | 生物 | 1 || 2 | 物理 | 2 || 3 | 体育 | 3 || 4 | 美术 | 2 |+-----+--------+------------+rows in set (0.00 sec) mysql> update course_view set cname=‘xxx‘; #更新视图中的数据Query OK, 4 rows affected (0.04 sec)Rows matched: 4 Changed: 4 Warnings: 0mysql> insert into course_view values(5,‘yyy‘,2); #往视图中插入数据Query OK, 1 row affected (0.03 sec)mysql> select * from course; #发现原始表的记录也跟着修改了+-----+-------+------------+| cid | cname | teacher_id |+-----+-------+------------+| 1 | xxx | 1 || 2 | xxx | 2 || 3 | xxx | 3 || 4 | xxx | 2 || 5 | yyy | 2 |+-----+-------+------------+rows in set (0.00 sec)
2.触发器
#准备表CREATE TABLE cmd ( id INT PRIMARY KEY auto_increment, USER CHAR (32), priv CHAR (10), cmd CHAR (64), sub_time datetime, #提交时间 success enum (‘yes‘, ‘no‘) #0代表执行失败);CREATE TABLE errlog ( id INT PRIMARY KEY auto_increment, err_cmd CHAR (64), err_time datetime);#创建触发器delimiter //CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROWBEGIN IF NEW.success = ‘no‘ THEN #等值判断只有一个等号 INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号 END IF ; #必须加分号END//delimiter ;#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志INSERT INTO cmd ( USER, priv, cmd, sub_time, success)VALUES (‘egon‘,‘0755‘,‘ls -l /etc‘,NOW(),‘yes‘), (‘egon‘,‘0755‘,‘cat /etc/passwd‘,NOW(),‘no‘), (‘egon‘,‘0755‘,‘useradd xxx‘,NOW(),‘no‘), (‘egon‘,‘0755‘,‘ps aux‘,NOW(),‘yes‘);#查询错误日志，发现有两条mysql> select * from errlog;+----+-----------------+---------------------+| id | err_cmd | err_time |+----+-----------------+---------------------+| 1 | cat /etc/passwd | 2017-09-14 22:18:48 || 2 | useradd xxx | 2017-09-14 22:18:48 |+----+-----------------+---------------------+rows in set (0.00 sec)
create table user(id int primary key auto_increment,name char(32),balance int);insert into user(name,balance)values(‘wsb‘,1000),(‘egon‘,1000),(‘ysb‘,1000);#原子操作start transaction;update user set balance=900 where name=‘wsb‘; #买支付100元update user set balance=1010 where name=‘egon‘; #中介拿走10元update user set balance=1090 where name=‘ysb‘; #卖家拿到90元commit;#出现异常，回滚到初始状态start transaction;update user set balance=900 where name=‘wsb‘; #买支付100元update user set balance=1010 where name=‘egon‘; #中介拿走10元uppdate user set balance=1090 where name=‘ysb‘; #卖家拿到90元,出现异常没有拿到rollback;commit;mysql> select * from user;+----+------+---------+| id | name | balance |+----+------+---------+| 1 | wsb | 1000 || 2 | egon | 1000 || 3 | ysb | 1000 |+----+------+---------+rows in set (0.00 sec)
#方式一： MySQL：存储过程 程序：调用存储过程#方式二： MySQL： 程序：纯SQL语句#方式三： MySQL: 程序：类和对象，即ORM（本质还是纯SQL语句）
#in 仅用于传入参数用#out 仅用于返回值用#inout 既可以传入又可以当作返回值
delimiter //create procedure p2( in n1 int, in n2 int)BEGIN select * from blog where id > n1;END //delimiter ;#在mysql中调用call p2(3,2)#在python中基于pymysql调用cursor.callproc(‘p2‘,(3,2))print(cursor.fetchall())in:传入参数
delimiter //create procedure p3( in n1 int, out res int)BEGIN select * from blog where id > n1; set res = 1;END //delimiter ;#在mysql中调用set @res=0; #0代表假（执行失败），1代表真（执行成功）call p3(3,@res);select @res;#在python中基于pymysql调用cursor.callproc(‘p3‘,(3,0)) #0相当于set @res=0print(cursor.fetchall()) #查询select的查询结果cursor.execute(‘select @_p3_0,@_p3_1;‘) #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值print(cursor.fetchall())out：返回值
delimiter //create procedure p4( inout n1 int)BEGIN select * from blog where id > n1; set n1 = 1;END //delimiter ;#在mysql中调用set @x=3;call p4(@x);select @x;#在python中基于pymysql调用cursor.callproc(‘p4‘,(3,))print(cursor.fetchall()) #查询select的查询结果cursor.execute(‘select @_p4_0;‘) print(cursor.fetchall())inout:既可以传入又可以返回
#介绍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 -- 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 blog(name,sub_time) values(‘yyy‘,now()); COMMIT; -- SUCCESS set p_return_code = 0; #0代表执行成功END //delimiter ;#在mysql中调用存储过程set @res=123;call p5(@res);select @res;#在python中基于pymysql调用存储过程cursor.callproc(‘p5‘,(123,))print(cursor.fetchall()) #查询select的查询结果cursor.execute(‘select @_p5_0;‘)print(cursor.fetchall())事务
-- 无参数call proc_name()-- 有参数，全incall proc_name(1,2)-- 有参数，有in，out，inoutset @t1=0;set @t2=3;call proc_name(1,2,@t1,@t2)执行存储过程在MySQL中执行存储过程
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123‘, db=‘t1‘)cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 执行存储过程cursor.callproc(‘p1‘, args=(1, 22, 3, 4))# 获取执行完存储的参数cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")result = cursor.fetchall()conn.commit()cursor.close()conn.close()print(result)在python中基于pymysql执行存储过程
#1 基本使用mysql> SELECT DATE_FORMAT(‘2009-10-04 22:23:00‘, ‘%W %M %Y‘); -> ‘Sunday October 2009‘mysql> SELECT DATE_FORMAT(‘2007-10-04 22:23:00‘, ‘%H:%i:%s‘); -> ‘22:23:00‘mysql> SELECT DATE_FORMAT(‘1900-10-04 22:23:00‘, -> ‘%D %y %a %d %m %b %j‘); -> ‘4th 00 Thu 04 10 Oct 277‘mysql> SELECT DATE_FORMAT(‘1997-10-04 22:23:00‘, -> ‘%H %k %I %r %T %S %w‘); -> ‘22 22 10 10:23:00 PM 22:23:00 00 6‘mysql> SELECT DATE_FORMAT(‘1999-01-01‘, ‘%X %V‘); -> ‘1998 52‘mysql> SELECT DATE_FORMAT(‘2006-06-00‘, ‘%d‘); -> ‘00‘#2 准备表和记录CREATE TABLE blog ( id INT PRIMARY KEY auto_increment, NAME CHAR (32), sub_time datetime);INSERT INTO blog (NAME, sub_time)VALUES (‘第1篇‘,‘2015-03-01 11:31:21‘), (‘第2篇‘,‘2015-03-11 16:31:21‘), (‘第3篇‘,‘2016-07-01 10:21:31‘), (‘第4篇‘,‘2016-07-22 09:23:21‘), (‘第5篇‘,‘2016-07-23 10:11:11‘), (‘第6篇‘,‘2016-07-25 11:21:31‘), (‘第7篇‘,‘2017-03-01 15:33:21‘), (‘第8篇‘,‘2017-03-01 17:32:21‘), (‘第9篇‘,‘2017-03-01 18:31:21‘);#3. 提取sub_time字段的值，按照格式后的结果即"年月"来分组SELECT DATE_FORMAT(sub_time,‘%Y-%m‘),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,‘%Y-%m‘);#结果+-------------------------------+----------+| DATE_FORMAT(sub_time,‘%Y-%m‘) | COUNT(1) |+-------------------------------+----------+| 2015-03 | 2 || 2016-07 | 4 || 2017-03 | 3 |+-------------------------------+----------+rows in set (0.00 sec)需要掌握函数：date_format
