mysql 事务 存储过程 函数
一：事务：
开启一个事务可以包含一些SQL语句，这些sql语句要么同时成功， 要么一个都别想成功， 称之我事务的原子性
事务用于将某些操作的多个SQL 作为原子性操作， 一旦有某一个出现错误， 即可以滚回到原来的状态， 从而保证数据库数据的完整性
1.1 ： 如何用：
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); try: update user set balance=900 where name='wsb'; update user set balance=1010 where name='egon'; update user set balance =1090 where name = 'ysb'; excepy : rollback; else: commit;
二 存储过程
2.1： 介绍：
存储过程包含了一系列可执行的sql 语句，存储过程存放于mysql 中，通过调用它的名字可以执行器内部的一堆sql
使用存储过程的优点：
1：用于提单程序写的 SQL语句，实现程序与sql解耦合 2：基于网络传输，传别名的数据量小， 而直接传SQL 数据量大
使用存储过程的缺点：
程序员扩展不方便
2.2：三种开发模型
1： 应用程序：只需要开发应用程序的逻辑 mysql：编写好存储过程， 以供应用程序调用 优点：开发效率，执行效率都高 缺点：考虑到人为因素，跨部门沟通等问题，会导致扩展性差， 2、 应用程序：除了开发应用程序的逻辑， 还需要编写原生sql mysql: 优点：比方式1，扩展性高（非技术性的） 缺点： 1、开发效率，执行效率都不如方式1 2、编写原生sql 太过于复杂，而且需要考虑到sql语句优化问题 3、 应用程序：开发应用程序的逻辑， 不需要编写原生sql,基于 别人编写好的框架来处理数据， ORM mysql: 优点：不用在编写纯生sql, 这意味着开发效率方式比方式2 高， 同时兼容方式2 扩展性高的好处 缺点：执行效率练方式2都比不过
2.3：创建存储过程
delimiter $$ create procedure pl( in m int, in n int, out res int) begin select tname from teacher where tid>m and tid<n; swt res=0; end $$ delimiter ;
2.4: 如何用存储过程
1：直接在mysql 中调用
set @res =10 call p1(2,4,10); 查看结果 select @res;
2:在python程序中调用
import pymysql conn =pymysql.connect( host='127.0.0.1', port=3306, user='root', password ='123', charset='utf8', database='db42', ) cursor=conn.cursor(pymysql.cursors.DicCursor) cursor.callproc('pl',(2,4,10)) print(cursor.fetchall()) cursor.execute('select @_pl_2;') print(cursor.fetchone()) cursor.close() conn.close()
2.5 补充：
创建存储过程有参： 一堆与存储过程，可以接受参数， 其参数有三类： in 仅用于传入参数用 out 仅用于返回值用 inout 即可以传入又可以当做返回值
2.6：删除存储过程
drop procedure proc_name;
三、函数
MySQL中提供了许多内置函数，
需要掌握的：
#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 | +-------------------------------+----------+ 3 rows in set (0.00 sec)
3.1 自定义函数
#！！！注意！！！
#函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能
#若要想在begin...end...中写sql，请用存储过程
3.2 删除函数
drop function func_name
3.3：执行函数
# 获取返回值 select UPPER('egon') into @res; SELECT @res; # 在查询中使用 select f1(11,nid) ,name from tb2;
四：流程控制
4.1：条件语句
delimiter // CREATE PROCEDURE proc_if () BEGIN declare i int default 0; if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF; END // delimiter ；
4.2 循环语句
delimiter // CREATE PROCEDURE proc_while () BEGIN DECLARE num INT ; SET num = 0 ; WHILE num < 10 DO SELECT num ; SET num = num + 1 ; END WHILE ; END // delimiter ;
delimiter // CREATE PROCEDURE proc_repeat () BEGIN DECLARE i INT ; SET i = 0 ; repeat select i; set i = i + 1; until i >= 5 end repeat; END // delimiter ;
BEGIN declare i int default 0; loop_label: loop set i=i+1; if i<8 then iterate loop_label; end if; if i>=10 then leave loop_label; end if; select i; end loop loop_label; END
