mysql内置功能
一、视图
create view course2teacher as select * from course inner join teacher on course.teacher_id = teacher.tid; # 只有表结构，没有表数据，因为它的数据是基于其他表的。不建议使用，因为以后扩展sql语句的时候，视图也需要跟着修改。 # 修改视图 alter view teacher_view as select * from course where cid>3; # 删除视图 drop view teacher_view -- 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 // # 定义sql语句的结束语 CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW BEGIN 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 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'); # 删除触发器 drop trigger tri_after_insert_cmd; -- 3 存储过程 # (1) 无参存储过程 delimiter // create procedure p1() BEGIN select * from db7.teacher; END // delimiter ; # MySQL中调用 call p1(); # Python中调用 cursor.callproc('p1') # (2) 有参存储过程。不但要指定是接收还是返回，还要指定类型 delimiter // create procedure p2(in n1 int,in n2 int,out res int) BEGIN select * from db7.teacher where tid > n1 and tid < n2; set res = 1; END // delimiter ; # inout 可进可出 了解就行 # MySQL中调用 set @x=0 call p2(2,4,@x); select @x; # 查看返回值结果 # Python中调用 cursor.callproc('p2',(2,4,0)) # @_p2_0=2,@_p2_1=4,@_p2_2=0 cursor.excute('select @_p3_2') cursor.fetchall() ## 应用程序和数据库结合使用 ### 方式一： MySQL: 编写存储过程 python：调用存储过程 ### 方式二： Python：编写纯生SQL MySQL：什么都不用干 ### 方式三： Python：ORM --> 纯生SQL MySQL： # 运行效率方式二高，开发效率方式三高（运行效率比方式二慢不了多少），我们主要是用方式三，偶尔用方式二，很少会去用方式一，除非一个人应用程序开发和DBA开发都很厉害。
pymysql储存过程的执行
# 1、增删改 import pymysql # 建立链接 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='112233', db='db7', charset='utf8' ) # 拿游标 cursor = conn.cursor(pymysql.cursors.DictCursor) # 执行sql # cursor.callproc('p1') # print(cursor.fetchall()) cursor.callproc('p2', (2, 4, 0)) # @_p2_)=2,@_p2_1=4,@_p3_2=0 cursor.execute('select @_p2_2') print(cursor.fetchone()) # {'@_p2_2': 1} # 关闭游标和链接 cursor.close() conn.close()
二、事物
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;
三、函数和流程控制
#1 准备表和记录 CREATE TABLE blog ( id INT PRIMARY KEY auto_increment, NAME CHAR (32), sub_time datetime ); INSERT 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'); #2. 提取sub_time字段的值，按照格式后的结果即"年月"来分组 select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
四、
#1. 准备表 create table s1( id int, name varchar(20), gender char(6), email varchar(50) ); #2. 创建存储过程，实现批量插入记录 delimiter $$ # create procedure auto_insert1() BEGIN declare i int default 1; while (i<3000000) do insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy')); set i=i+1; end while; END$$ delimiter ; #3. 查看存储过程 show create procedure auto_insert1\G #4. 调用存储过程 call auto_insert1(); #无索引：mysql根本就不知道到底是否存在id等于333333333的记录，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢 mysql> select * from s1 where id=333333333; Empty set (0.33 sec) # 创立索引前 select count(id) from s1 where id = 1000 1 row in set (0.80 sec) # 创立索引 create index idx_id on s1(id) Query OK, 0 rows affected (2.63 sec) Records: 0 Duplicates: 0 Warnings: 0 # 创立索引后 select count(id) from s1 where id = 1000; 1 row in set (0.00 sec)
