mysql 存储函数和事务_mysql 事务 存储过程 函数

一：事务：

开启一个事务可以包含一些SQL语句，这些sql语句要么同时成功， 要么一个都别想成功， 称之我事务的原子性

事务用于将某些操作的多个SQL 作为原子性操作， 一旦有某一个出现错误， 即可以滚回到原来的状态， 从而保证数据库数据的完整性

1.1 ： 如何用：

create table user(

idintprimary key auto_increment,

namechar(32),

balanceint);

insert into user (name,balance) values

('wsb',1000),

('egon',1000),

('ysb',1000);try:

update userset balance=900 where name='wsb';

update userset balance=1010 where name='egon';

update userset 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、开发效率，执行效率都不如方式12、编写原生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)

beginselect tname from teacher where tid>m and tid

swt res=0;

end $$

delimiter ;

2.4: 如何用存储过程

1：直接在mysql 中调用

set @res =10call 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 iint default 0;if i = 1THEN

SELECT1;

ELSEIF i= 2THEN

SELECT2;

ELSE

SELECT7;

END IF;

END//delimiter ；

4.2  循环语句

delimiter //CREATE PROCEDURE proc_while ()

BEGIN

DECLARE num INT ;

SET num= 0;

WHILE num< 10DO

SELECT

num ;

SET num= num + 1;

END WHILE ;

END//delimiter ;

delimiter //CREATE PROCEDURE proc_repeat ()

BEGIN

DECLARE i INT ;

SET i= 0;

repeatselecti;set i = i + 1;

until i>= 5end repeat;

END//delimiter ;

BEGIN

declare iint default 0;

loop_label: loopset i=i+1;if i<8then

iterate loop_label;

endif;if i>=10then

leave loop_label;

endif;selecti;

end loop loop_label;

END