V2AS问路
day64--pymysql模块的使用、视图、触发器、函数、存储过程、事务
阅读原文时间：2023年07月12日阅读：1

一、pymysql的下载和使用

（一）pymysql模块的下载：pip3 install pymysql

# 实现：使用Python实现用户登录，如果用户存在则登录成功（假设该用户已在数据库中）

import pymysql
user = input('请输入用户名：')

pwd = input('请输入密码：')

1.连接

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='db8', charset='utf8')
#host,port,user,password,db,charset这些参数是必须的。

2.创建游标

cursor = conn.cursor()

#注意%s需要加引号
sql = "select * from userinfo where username='%s' and pwd='%s'" %(user, pwd)
print(sql)

3.执行sql语句

cursor.execute(sql)

result=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目
print(result)

关闭连接，游标和连接都要关闭

cursor.close()
conn.close()

if result:
print('登陆成功')
else:
print('登录失败')

在python中的代码显示

（二）execute()之sql注入

最后那一个空格，在一条sql语句中如果遇到select * from userinfo where username='mjj' -- asadasdas' and pwd='' 则--之后的条件被注释掉了(注意--后面还有一个空格)

1、sql注入之：用户存在，绕过密码==》xxx' -- 任意字符

解释：xxx:用户名
2、sql注入之：用户不存在，绕过用户与密码==》xxx' or 1=1 -- 任意字符

解释：A or B的这种逻辑结构只要A或B其中一个满足条件即为True，所以该条件一定满足，其后果既是：绕过用户名和密码直接走下一步，不需要从数据库去除数据进行验证。

# 原来是我们对sql进行字符串拼接

sql="select * from userinfo where name='%s' and password='%s'" %(username,pwd)

print(sql)

result=cursor.execute(sql)

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
result=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

解决sql注入的办法

（三）、增、删、改：conn.commit()

注：commit()方法：在数据库里增、删、改的时候，必须要有该方法：commit()提交，否则插入的数据不生效。

import pymysql
username = input('请输入用户名：')

pwd = input('请输入密码：')

1.连接

conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8')

2.创建游标

cursor = conn.cursor()

操作

增

sql = "insert into userinfo(username,pwd) values (%s,%s)"

effect_row = cursor.execute(sql,(username,pwd))

#同时插入多条数据
#cursor.executemany(sql,[('李四'，'110'),（'王五'，'119'）])

print(effect_row)

改

sql = "update userinfo set username = %s where id = 2"

effect_row = cursor.execute(sql,username)

print(effect_row)

删

sql = "delete from userinfo where id = 2"
effect_row = cursor.execute(sql)
print(effect_row)

#一定记得commit
conn.commit()

4.关闭游标

cursor.close()

5.关闭连接

conn.close()

增、删、改的操作

（四）、查：fetchone、fetchmany、fetchall

fetchone():获取下一行数据，第一次为首行；
fetchall():获取所有行数据源
fetchmany(4):获取4行数据

import pymysql

1.连接

conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8')

2.创建游标

cursor = conn.cursor()

sql = 'select * from userinfo'
cursor.execute(sql)

查询第一行的数据

row = cursor.fetchone()
print(row) # (1, 'mjj', '123')

查询第二行数据

row = cursor.fetchone()
print(row) # (3, '张三', '110')

4.关闭游标

cursor.close()

5.关闭连接

conn.close()

使用fetchone()方法

import pymysql

1.连接

conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8')

2.创建游标

cursor = conn.cursor()

sql = 'select * from userinfo'
cursor.execute(sql)

获取所有的数据

rows = cursor.fetchall()
print(rows)

4.关闭游标

cursor.close()

5.关闭连接

conn.close()

#运行结果
((1, 'mjj', ''), (3, '张三', ''), (4, '李四', ''))

使用fetchall()方法

默认情况下，我们获取到的返回值是元组，只能看到每行的数据，却不知道每一列代表的是什么，这个时候可以使用以下方式来返回字典，每一行的数据都会生成一个字典：

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #在实例化的时候，将属性cursor设置为pymysql.cursors.DictCursor

在fetchone示例中，在获取行数据的时候，可以理解开始的时候，有一个行指针指着第一行的上方，获取一行，它就向下移动一行，所以当行指针到最后一行的时候，就不能再获取到行的内容，所以我们可以使用如下方法来移动行指针：

cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
第一个值为移动的行数，整数为向下移动，负数为向上移动，mode指定了是相对当前位置移动，还是相对于首行移动

# 1.Python实现用户登录

2.Mysql保存数据

import pymysql

1.连接

conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8')

2.创建游标

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = 'select * from userinfo'
cursor.execute(sql)

查询第一行的数据

row = cursor.fetchone()
print(row) # (1, 'mjj', '123')

查询第二行数据

row = cursor.fetchone() # (3, '张三', '110')
print(row)

cursor.scroll(-1,mode='relative') #设置之后，光标相对于当前位置往前移动了一行，所以打印的结果为第二行的数据
row = cursor.fetchone()
print(row)

cursor.scroll(0,mode='absolute') #设置之后，光标相对于首行没有任何变化，所以打印的结果为第一行数据
row = cursor.fetchone()
print(row)

4.关闭游标

cursor.close()

5.关闭连接

conn.close()

#结果如下

{'id': 1, 'username': 'mjj', 'pwd': ''}
{'id': 3, 'username': '张三', 'pwd': ''}
{'id': 3, 'username': '张三', 'pwd': ''}
{'id': 1, 'username': 'mjj', 'pwd': ''}

演示

import pymysql

1.连接

conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8')

2.创建游标

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = 'select * from userinfo'
cursor.execute(sql)

获取2条数据

rows = cursor.fetchmany(2)
print(rows)

4.关闭游标

rows = cursor.fetchall()

print(rows)

cursor.close()

5.关闭连接

conn.close()

#结果如下：
[{'id': 1, 'username': 'mjj', 'pwd': ''}, {'id': 3, 'username': '张三', 'pwd': ''}]

fetchmany()方法

二、视图

（一）定义和优缺点：

1、定义：

视图是虚拟表或逻辑表，它被定义为具有连接的SQL SELECT查询语句。因为数据库视图与数据库表类似，它由行和列组成，因此可以根据数据库表查询数据。其内容由查询定义。
但是，视图并不在数据库中以存储的数据值集形式存在，行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。简单的来说视图是由其定义结果组成的表

2、优缺点

优点：

（1）数据库视图允许简化复杂查询，通过数据库视图，您只需使用简单的SQL语句，而不是使用具有多个连接的复杂的SQL语句。

（2）安全性。一般是这样做的:创建一个视图，定义好该视图所操作的数据。之后将用户权限与视图绑定。这样的方式是使用到了一个特性：grant语句可以针对视图进行授予权限

缺点：

（1）性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的。

（2）表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。

（二）视图的具体操作步骤

1、视图的创建语法：create view 视图名 as sql语句；

#两张有关系的表mysql>select*fromcourse;+-----+--------+------------+|cid|cname|teacher_id|+-----+--------+------------+|1|生物|1||2|物理|2||3|体育|3||4|美术|2|+-----+--------+------------+rowsinset(0.00sec)mysql>select*fromteacher;+-----+-----------------+|tid|tname|+-----+-----------------+|1|张磊老师||2|李平老师||3|刘海燕老师||4|朱云海老师||5|李杰老师|+-----+-----------------+rowsinset(0.00sec)#查询李平老师教授的课程名mysql>selectcnamefromcoursewhereteacher_id=(selecttidfromteacherwheretname='李平老师');+--------+|cname|+--------+|物理||美术|+--------+rowsinset(0.00sec)#子查询出临时表，作为teacher_id等判断依据selecttidfromteacherwheretname='李平老师'临时表应用举例

第一步：创建两张有关系的表c

create view teacher_view as select tid from teacher where tname='李平老师';

#于是查询李平老师教授的课程名的sql可以改写为
mysql> select cname from course where teacher_id = (select tid from teacher_view);
+--------+
| cname |
+--------+
| 物理 |
| 美术 |
+--------+
rows in set (0.00 sec)

第二步：视图的创建

第三步：使用视图

# 往真实表中插入一条数据，查看一下视图，发现视图表也会跟着更新
insert into course(cname,teacher_id) values('张三丰',2);

更新一下数据，发现视图的数据也会跟着更新

update course set cname='王五';

注意：不能修改视图的数据，会发生错误：

错误代码如下：ERROR 1471 (HY000): The target table tt of the INSERT is not insertable-into；

2、视图的修改：alter view 视图名称 as sql语句；

3、视图的删除：drop view 视图名称

四、触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

（一）创建触发器

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
…
END

插入后

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
…
END

删除前

CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
…
END

删除后

CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
…
END

更新前

CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
…
END

更新后

CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
…
END

创建触发器

例：用户 和日志表。

要求：每次创建一个用户之后，就在日志表中生成这条记录

# 创建用户表
create table user(
id int primary key auto_increment,
name varchar(20) not null,
reg_time datetime, # 注册用户的时间
affirm enum('yes','no') # no表示该用户执行失败
);

#创建日志表
create table userLog(
id int primary key auto_increment,
u_name varchar(20) not null,
u_reg_time datetime # 注册用户的时间
);

创建触发器 delimiter 默认情况下，delimiter是分号 触发器名称应遵循命名约定[trigger time]_[table name]_[trigger event]

delimiter //
create trigger after_user_insert after insert on user for each row
begin
if new.affirm = 'yes' then
insert into userLog(u_name,u_reg_time) values(new.name,new.reg_time);
end if;

end //
delimiter ;

#往用户表中插入记录，触发触发器，根据if的条件决定是否插入数据
insert into user(name,reg_time,affirm) values ('张三',now(),'yes'),('李四',now(),'yes'),('王五',now(),'no');

查看日志表，发现多了两条记录 ，大家应该看到for each row就明白了

mysql> select * from userlog;
+----+--------+---------------------+
| id | u_name | u_reg_time |
+----+--------+---------------------+
| 1 | 张三 | 2018-06-14 17:52:49 |
| 2 | 李四 | 2018-06-14 17:52:49 |
+----+--------+---------------------+
rows in set (0.00 sec)

准备表

注意：请注意，在为INSERT定义的触发器中，可以仅使用NEW关键字。不能使用OLD关键字。但是，在为DELETE定义的触发器中，没有新行，因此您只能使用OLD关键字。在UPDATE触发器中，OLD是指更新前的行，而NEW是更新后的行

（二）使用触发器：触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

（三）删除触发器：drop trigger trigger_userLog;

　　drop trigger 触发器名;

（一）mysql的内置函数：

一、数学函数
ROUND(x,y)
返回参数x的四舍五入的有y位小数的值

RAND()  
    返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

二、聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

三、字符串函数

CHAR\_LENGTH(str)  
    返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。  
CONCAT(str1,str2,...)  
    字符串拼接  
    如有任何一个参数为NULL ，则返回值为 NULL。  
CONCAT\_WS(separator,str1,str2,...)  
    字符串拼接（自定义连接符）  
    CONCAT\_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

CONV(N,from\_base,to\_base)  
    进制转换  
    例如：  
        SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

FORMAT(X,D)  
    将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。  
    例如：  
        SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'  
INSERT(str,pos,len,newstr)  
    在str的指定位置插入字符串  
        pos：要替换位置其实位置  
        len：替换的长度  
        newstr：新字符串  
    特别的：  
        如果pos超过原字符串长度，则返回原字符串  
        如果len超过原字符串长度，则由新字符串完全替换  
INSTR(str,substr)  
    返回字符串 str 中子字符串的第一个出现位置。

LEFT(str,len)  
    返回字符串str 从开始的len位置的子序列字符。

LOWER(str)  
    变小写

UPPER(str)  
    变大写

REVERSE(str)  
    返回字符串 str ，顺序和字符顺序相反。

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)  
    不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

    mysql> SELECT SUBSTRING('Quadratically',5);  
        -> 'ratically'

    mysql> SELECT SUBSTRING('foobarbar' FROM 4);  
        -> 'barbar'

    mysql> SELECT SUBSTRING('Quadratically',5,6);  
        -> 'ratica'

    mysql> SELECT SUBSTRING('Sakila', -3);  
        -> 'ila'

    mysql> SELECT SUBSTRING('Sakila', -5, 3);  
        -> 'aki'

    mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);  
        -> 'ki'

四、日期和时间函数
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回当前的日期和时间
QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date) 返回日期date为一年中第几周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)

重点:  
DATE\_FORMAT(date,format) 根据format字符串格式化date值

   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');  
    -> ''

五、加密函数
MD5()
计算字符串str的MD5校验和
PASSWORD(str)
返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。

六、控制流函数
CASE WHEN[test1] THEN [result1]…ELSE [default] END
如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]…ELSE [default]END
如果test和valN相等，则返回resultN，否则返回default

IF(test,t,f)  
    如果test是真，返回t；否则返回f

IFNULL(arg1,arg2)  
    如果arg1不是空，返回arg1，否则返回arg2

NULLIF(arg1,arg2)  
    如果arg1=arg2返回NULL；否则返回arg1

内置函数

mysql的内置cc

更多函数：中文猛击这里 OR 官方猛击这里

（二）时间格式化date_format()的用法

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');
-> ''

时间格式化

举例：

# 1、博客园 时间处理
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);

#2、插入数据
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') as y_m,COUNT(1) as num FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m');

结果：

+---------+-----+
| y_m | num |
+---------+-----+
| 2015-03 | 2 |
| 2016-07 | 4 |
| 2017-03 | 3 |c+---------+-----+
rows in set (0.00 sec)

五、存储过程

存储过程是存储在数据库目录中的一坨的声明性SQL语句。 Java，Python，PHP等应用程序都可以调用存储过程。

1、通常存储过程有助于提高应用程序的性能。

2、存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。

3、存储的程序对任何应用程序都是可重用的和透明的。

4、存储的程序是安全的。

1、如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果您在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不当于逻辑运算。

2、存储过程的构造c开发具有复杂业务逻辑的存储过程变得更加困难。

3、很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。

4、开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。

注意：MySQL存储过程有自己的优点和缺点。开发应用程序时，您应该决定是否应该或不应该根据业务需求使用存储过程。

delimiter //
create procedure b1()
begin
select * from blog;
end //
delimiter ;

参数解释：

1.第一个命令是delimiter //，它与存储过程语法无关。 delimter语句将标准分隔符 - 分号(;)更改为：//。 在这种情况下，分隔符从分号(;)更改为双斜杠//。为什么我们必须更改分隔符？ 因为我们想将存储过程作为整体传递给服务器，而不是让mysql工具一次解释每个语句。 在END关键字之后，使用分隔符//来指示存储过程的结束。 最后一个命令(DELIMITER;)将分隔符更改回分号(;)。

2.使用create procedure语句创建一个新的存储过程。在create procedure语句之后指定存储过程的名称。在这个示例中，存储过程的名称为：b1，并把括号放在存储过程的名字之后。

3.begin和end之间的部分称为存储过程的主体。将声明性SQL语句放在主体中以处理业务逻辑。 在这个存储过程中，我们使用一个简单的select语句来查询blog表中的数据。

# mysql中调用存储过程
call b1()

#在python中基于pymysql调用
cursor.callproc('b1')
print(cursor.fetchall())

（五）声明变量

在存储过程中声明变量：declare 变量名 datetype(size) default 默认值;

首先，在DECLARE关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。
其次，指定变量的数据类型及其大小。变量可以有任何MySQL数据类型，如INT，VARCHAR，DATETIME等。
第三，当声明一个变量时，它的初始值为NULL。但是可以使用DEFAULT关键字为变量分配默认值。

实现：

delimiter //
create procedure b2()
begin
DECLARE n int DEFAULT 1;
set n = 5;
select * from blog where id = n;
end //
delimiter ;

mysql中调用存储过程

call b2();

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章

[译]这几个CSS小技巧，你知道吗？