mysql杂项，第六十四天

mysql7.24

mysql7.24 1

1. pymysql模块 2

1.1. 代码演示 2

1.2. 基于mysql模块的增删改 2

1.3. 基于pymysql模块的查询 3

1.3.1. 注入问题 3

1.3.2. 解决注入问题 4

1.3.3. 提交查询语句并且拿到查询结果 4

2. 杂项 5

2.1. 视图 5

2.2. 触发器 5

2.3. 事务 6

3. 存储过程 7

3.1. 无参创造存储过程 7

3.2. 有参创造存储过程 8

 

 

1. pymysql模块
   1. 代码演示

import pymysql

flient = pymysql.connect(

host='127.0.0.1',

port=3306,

user='root',

password='',

database='db6',

charset='utf8')

cursor = client.cursor() # 连接mysql套接字

sql='insert into t1 calues(1,"egon");'

try:

res = cursor.execute(sql) # 执行代码

print(res)

client.commit() # 将执行代码发送给终端

except Exception:

client.rollback() # 如果失败，回滚信息至初始状态

cursor.close()

client.close()

1. 1. 基于mysql模块的增删改

import pymysql

client=pymysql.connect(...)

cursor=client.cursor()

sql='insert into t1 values(3,"alex"),(4,"lxx");'

userinfo=[

(3,"alex"),

(4,"lxx"),

(5,"yxx")]

for user in userinfo:

sql='insert into t1 values(%s,"%s");'%(user[0],user[1])

cursor.execute(sql)

sql='insert into t1 values(%s,%s);'

cursor.executemany(sql,userinfo) # 等同于上一段的for循环，发送多条消息

cursor.execute('delete from t1 where id=3;')

client.commit()

cursor.close()

client.close()

1. 1. 基于pymysql模块的查询

import pymysql

client=pymysql.connect=()

cursor=client.cursor()

# 查询

inp_user=input("username").strip()

inp_pwd=input("password").strip()

sql='select id from user where name="%s" and pwd ="%s";'

%(inp_user,inp_pwd)

print(sql)

rows=cursor.execute(sql)

if rows:

pirnt('login successful')

else:

print('username or password eroor')

cursor.close()

client.close()

1. 1. 1. 注入问题

注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符

根本原理：就根据程序的字符串拼接name='%s'，我们输入一个xxx' -- haha,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'

1. 1. 1. 解决注入问题

# 解决sql注入问题

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）

sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上

res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

import pymysql

client=pymysql.connect(...)

cursor=client.cursor()

#查询

inp_user=input("username").strip()

inp_pwd=input("password").strip()

sql='select id from user where name=%s and pwd=%s;'

rows=cursor.execute(sql,(inp_user,inp_pwd))

if rows:

print('login successful')

else:

print('username or password error')

cursor.close()

client.close

1. 1. 1. 提交查询语句并且拿到查询结果

import pymysql

client=pymysql.connect()

cursor=client.cursor(pymysql.cursors.DictCursor) # 输出结果以字典的方式显示

sql='select * from user where id > 3'

rows=cursor.execute(sql)

print(rows) # 这里返回的是多少行被影响

pirnt(cursor.fetchall()) # 取出管道内的所有信息，类似于迭代器，取一个少一个

print(cursor.fetchall()) # 取出空列表，因为已经被上一个fetchall取干净了

print(cursor.getchone()) # 取出管道内的一条信息

print(cursor.fetchmany(n)) # 取出管道内的n条信息

cursor.scroll(n,mode='absolute') # 绝对位置移动，从第n条信息开始移动

cursor.scroll(n,mose=relative') # 相对位置移动，从当前位置移动两条信息

1. 杂项
   1. 视图

# 强调

-字段名不能重复

-视图是为了简化查询的sql语句，不应该修改视图中的记录，记住视图只是拿来查看表与表之间的联系的

## 通过连接表一的table_two_id和表二的id创建一个表一的所有信息和表二的名字组成的视图

create view table_one 2 table_two as select table_one.*,table_two.name as table_two_name from table_one inner join table_two on table_one.table_two_id = table_two.id

1. 1. 触发器

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

## 特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。

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_id int

);

delimiter $$

CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW

BEGIN

if NEW.success = 'no' then

insert into errlog(err_id) values(NEW.id);

end if;

END $$

delimiter ;

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

1. 1. 事务

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

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;

try:

update user set balance=900 where id=1;

update user set balance=1010 where id=2;

update user set balance=1090 where id=3;

commit;

except Exception:

rollback;

1. 存储过程

方案一：

应用程序:

mysql:编写存储过程

方案二：

应用程序：原生sql

mysql：

方案三：

应用程序：ORM（类/对象 ---> 原生sql）

mysql:

执行效率：

方案一>方案二>方案三

开发效率：

方案一>方案三>方案二

但一般情况下都是用方案三，因为用类帮我们封装了mysql的原生sql语句，我们就不需要去学习sql原生语句，使用类就行了。虽然方案一的执行效率和开发效率都高，但在实际应用场景中，会涉及跨部门沟通问题，所以一般被pass

1. 1. 无参创造存储过程

delimiter $$

create procedure p1()

BEGIN

select * from blog;

END $$

delimiter;

create table s1(

id int,

name varchar(20),

gender char(5),

email varchar(50));

## 用while循环插入100条信息

delimiter $$

create procedure p2()

BEGIN

declare n int default 1;

while (n < 100) do

insert into s1 values(n,concat('egon',n),'male',concat('egon',n,'@163.com'));

set n=n+1;

end while;

END $$

delimiter ;

1. 1. 有参创造存储过程

# 有参

delimiter $$

create procedure p3(

in n int, # 这相当于python中的形参

out res int # 这相当于python中的返回值

)

BEGIN

select * from blog where id > n;

set res = 0; # 相当于返回值是0

END $$

delimiter ;

# 直接在mysql中调用：

mysql> set @x=111; # 传入变量x=111

mysql> call p3(3,@x); # 相当于调用p3函数，并传值以及传入返回值，最后x会被p3内的res给修改成0

mysql> select @x;

+------+

| @x   |

+------+

|    0 |

+------+

1 row in set (0.00 sec)

# 在python中调用：

cursor.callproc('p4',(3,111)) #set @_p4_0 = 3; set @_p4_1 = 111

print(cursor.fetchall())

cursor.execute('select @_p4_1;') # pymsql模块会把p4修改成@_p4_1去执行

print(cursor.fetchone())

谢谢观看！！