数据库之MySQL高级

程序员文章站 2022-10-18 11:22:15

python用于操作数据的pymysql模块，及相关的视图，触发器，函数，存储过程，事务，SQL注入等相关拓展。 ......

内容来自本人以前在github搭建的博客写的：mysql高级之视图事务函数触发器

pymysql安装

pymysql是python用于连接并操作数据库的一个原生模块

linux下:

pip3 install pymysql

sql注入

1.简单模拟登录

先来看一个简单例子，代码如下：

import pymysql

user = input("username:")
pwd = input("password:")

# 与数据库建立连接
conn = pymysql.connect(host="localhost", user="root", password="root", database="test")
# 设置游标
cursor = conn.cursor()
# 将要执行的sql语句
sql = "select * from userinfo where username = '%s' and password = '%s'" % (user, pwd)
# 执行语句
cursor.execute(sql)
# 用fetchone获取查询结果
result = cursor.fetchone()
# 关闭连接
cursor.close()
conn.close()

if result:
    print("登录成功")
else:
    print("用户名或密码错误")

2.sql注入漏洞

注意，上面的代码虽然可正确执行，但是是存在漏洞的。

如图，漏洞为即使你随便输入一个账户，按照”xxx’ or 1=1 – “的格式，不输入密码也能登录进去，这种漏洞我们称呼为”sql注入”；问题主要存在于sql语句的写法。

3.sql注入原理

如图

数据库之MySQL高级

4.避免sql注入

为了避免sql注入，我们不要自己做拼接，用pymysql自带的execute后面传参数的方式,有如下三种方法:

sql = "select * from userinfo where username = %s and password = %s"
cursor.execute(sql, (user, pwd))

或者：

sql = "select * from userinfo where username = %s and password = %s"
cursor.execute(sql, [user, pwd])

或者：

sql = "select * from userinfo where username = %(u)s and password = %(p)s"
cursor.execute(sql, {'u': user, 'p': pwd})

顺利解决sql注入问题，如图

5.模拟登陆代码

import pymysql

name = input("username:")
pwd = input("password:")

conn = pymysql.connect(host='localhost', user='root', password='root', database='test')
cursor = conn.cursor()

sql = "select * from userinfo where name=%s and password=%s"
r = cursor.execute(sql, (name, pwd))
# 方法二
# sql = "select * from userinfo where name=%s and password=%s"
# r = cursor.execute(sql, [name, pwd])
# 方法三
# sql = "select * from userinfo where name=%(u)s and password=%(p)s"
# r = cursor.execute(sql, {'u': name, 'p': pwd})
result = cursor.fetchone()

cursor.close()
conn.close()

if result:
print("登陆成功")
else:
print("账户名或密码错误")

pymysql操作数据库

1.pymysql增

1.1.插入一行数据

代码如下：

import pymysql

conn = pymysql.connect(host="localhost", user="root", password="root", database="test")
cursor = conn.cursor()
sql = "insert into userinfo(username,password) values('那英','naying')"
cursor.execute(sql)
conn.commit()
cursor.close()
conn.close()

值得注意的是，在增删改时我们用的是commit进行提交，在查时用的是fetchone进行查找。
当然，我们也可以自定义插入，如下：

sql = "insert into userinfo(username,password) values(%s,%s)"
cursor.execute(sql, [user, pwd])
conn.commit()

1.2.插入多行数据

在插入当行数据是，应注意此时我们用的是executemany，而不是execute。

sql = "insert into userinfo(username,password) values(%s,%s)"
r = cursor.executemany(sql, [('张学友', 'zhangxueyou'), ('林俊杰', 'linjunjie')])
conn.commit()

1.3.execute的返回值r

execute和executemany都有返回值，用来记录受影响的行数，如下：

sql = "insert into userinfo(username,password) values(%s,%s)"
r = cursor.executemany(sql, [('张学友', 'zhangxueyou'), ('林俊杰', 'linjunjie')])
print(r)
conn.commit()

结果为2；
不仅仅是增删改会有受影响的行数，查询时也会有。
同时，executemany只适用于插入数据，删和改用execute即可。

2.pymysql查

2.1.fetchone

代码如下：

result = cursor.fetchone()
print(result)
result = cursor.fetchone()
print(result)
result = cursor.fetchone()
print(result)

结果：

(1, '刘德华', 'liudehua')
(3, '郭德纲', 'guodegang')
(4, '胡歌', 'huge')

fetchone可以单次查询，也可以连续单次查询，连续单次查询时，会有类似于指针一样的东西，当你查询一次后，指针自动跳往下一行数据。

2.2.fetchall

fetchall在未指定limit时，默认查询所有数据
代码如下：

sql = "select * from userinfo"
cursor.execute(sql)
result = cursor.fetchall()
print(result)

结果：

((1, ‘刘德华’, ‘liudehua’), (3, ‘郭德纲’, ‘guodegang’), (4, ‘胡歌’, ‘huge’), (5, ‘周杰伦’, ‘zhoujielun’), (7, ‘那英’, ‘naying’), (8, ‘王菲’, ‘wangfei’), (11, ‘张学友’, ‘zhangxueyou’), (14, ‘林俊杰’, ‘linjunjie’))

2.3.fetchmany

fetchmany(n)查询指定前n条数据
代码如下：

sql = "select * from userinfo"
cursor.execute(sql)
result = cursor.fetchmany(3)
print(result)

结果：

((1, ‘刘德华’, ‘liudehua’), (3, ‘郭德纲’, ‘guodegang’), (4, ‘胡歌’, ‘huge’))

2.4.fetch小结

1.fetchone查询单次数据，fetchall查询所有数据，fetchmany(n)查询指定n词数据。
2.如果想要实现分页的话，要先在查询语句中limit 10数据，再用fetchall查询指定的10条数据；而不能先fetchall所有数据，再一部分一部分的显示。
3.相对来说，用的最多的是fetchone和fetchall

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

cursor.scroll(1,mode=’relative’) # 相对当前位置移动
cursor.scroll(2,mode=’absolute’) # 相对绝对位置移动

2.5.查询数据字典形式显示

为了方便查看，我们会将数据以字典格式显示key和value
代码如下：

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='root', database='test')
cursor = conn.cursor(cursor=pymysql.cursors.dictcursor)
sql = "select * from userinfo"
cursor.execute(sql)
result = cursor.fetchmany(3)
print(result)

结果：

[{‘id’: 1, ‘username’: ‘刘德华’, ‘password’: ‘liudehua’}, {‘id’: 3, ‘username’: ‘郭德纲’, ‘password’: ‘guodegang’}, {‘id’: 4, ‘username’: ‘胡歌’, ‘password’: ‘huge’}]

2.6.新增数据的自增id

获取新增数据的自增id:cursor.lastrowid,直接用
如果插入的为多行数据，则显示的为最后一个自增的id

print(cursor.lastrowid)

3.pymysql删改

增和查的注意点可能多一点，而pymysql删与改的操作重点在于sql语句，其他只需要连接，执行，关闭连接即可。

3.1.改

代码如下：

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='root', database='test')
cursor = conn.cursor()

sql = "update userinfo set name='李玉刚' where id=1"
cursor.execute(sql)
conn.commit()

cursor.close()
conn.close()

4.pymysql删

4.1.删

代码如下：

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='root', database='test')
cursor = conn.cursor()

sql = "delete from userinfo where id=1"
cursor.execute(sql)
conn.commit()

cursor.close()

视图

为某个查询语句设置别名，方便使用，即为视图；

1.创建视图

创建语句：

create view viewname as sql

示例：

create view v1 as select * from userinfo where id>5;
create view v1 as select id,name from userinfo where id>5;

2.修改视图

修改语句：alter view viewname as sql
eg：

alter view v1 as select id,name from userinfo where id >5;

3.删除视图

删除语句：drop view viewname;
eg：

drop view v1;

4.查看视图

查看语句：select from viewname;
eg:

select from v1;
select id,name from v1;

5.视图小结

给一个临时表设置别名的过程就叫做创建视图，别名就是视图的名称；实际存在的表比如userinfo就是物理表，而一个视图就是虚拟表，虚拟表动态的从物理表中取数据，所以在物理表中插入数据后虚拟表也可能会随之改变，但不能向虚拟表中插入数据；创建，修改，删除视图。

触发器

对某个表进行【增/删/改】操作的前后触发一些操作即为触发器，如果希望触发增删改的行为之前或之后做操作时，可以使用触发器，触发器用于自定义用户对表的行进行【增/删/改】前后的行为。

1.创建触发器

delimiter //   # 修改终止符
create trigger tri_before_insert_userinfo before insert on userinfo for each row
begin
insert into userinfolog(type,newname) values('insert','姓名');
end//
delimiter ;    # 结束后将终止符修改回来

2.多次触动触发器

insert into userinfo(name,password) values('石超','shichao'),('林殊','linshu');

因为each row的存在，在一次sql中插入两行数据，会触发两次触发器，即向userinfo插入数据前，会分别两次向user插入数据。

3.自定义触发器新插入数据

在前面我们写的触发器里，只能插入固定的数据，那么我们可不可以在user中插入即将插入userinfo的数据呢？答案是可以的，如下：

delimiter //
create trigger tri_after_delete_userinfo after delete on userinfo for each row
begin
insert into userinfolog(type,oldname) values(‘delete’,old.name);
end //
delimiter ;

delimiter //
create trigger tri_after_update_userinfo after update on userinfo for each row
begin
insert into userinfolog(type,oldname,newname) values('update',old.name,new.name);
end //
delimiter ;

注意：触发器无法被修改，如果想要修改的话，可以先drop，再重新创建

delimiter //
create trigger tri_after_update_userinfo after insert on userinfo for each row
begin
if new.operate = 'insert' then
    insert into userinfolog(type,newname) values('insert',new.name);
elseif new.operate = 'delete' then
    insert into userinfolog(type,newname) values('delete',old.name);
else
    insert into userinfolog(type,oldname,newname) values('update',old.name);
end if;
end //
delimiter ;

函数

1、内置函数

mysql中提供了许多内置函数，例如：
curdate(),可以查看当前时间；
使用：

select curdate()

2、自定义函数

delimiter \\
create function f1(
    i1 int,
    i2 int)
returns int
begin
    declare num int;
    set num = i1 + i2;
    return(num);
end \\
delimiter ;

3、删除函数

drop function func_name();

4、执行函数

# 获取返回值
set @i1=1;
set @i2=2;
select f1(@i1,@i2) into @j;
select @j;

select f1(11,nid) ,name from tb2; # 在查询中使用,对列的值函数计算后返回。

5、查看函数

show function status;

6、查看函数构建语句

show create function func_name\g

存储过程

存储过程是一个sql语句集合，当主动去调用存储过程时，其中内部的sql语句会按照逻辑执行。

1、创建存储过程

无参数存储过程

– 创建存储过程

delimiter //　　#修改结束符号，为//
create procedure p1()
begin
    select * from t1;
end//
delimiter ;

– 执行存储过程

call p1()

对于存储过程，可以接收参数，其参数有三类：

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值

关于变量设置，对于调用存储过程或者函数时，外部传入参数或者获取参数，需加符号@，例如set @t=1；在本次会话内这些带@的变量都可以被获取到。断开连接后变量失效。

有参数存储过程

– 创建存储过程

delimiter \\
create procedure p1(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
begin
    declare temp1 int;
    declare temp2 int default 0; 
    set temp1 = 1;
    set r1 = i1 + i2 + temp1 + temp2;  
    set i3 = i3 + 100;
end\\
delimiter ;

– 执行存储过程

set @t2=3;
call p1 (1, 2 ,@t1, @t2);
select @t1,@t2;

delimiter //　 将结束符号;修改为//
declare　　声明变量。如果没有default子句，初始值为null。用于内部变量申明。
set　　　　  变量赋值。用于内部变量赋值，和传参数时参数赋值。

2、删除存储过程

drop procedure proc_name;

3、执行存储过程

– 无参数

call proc_name();

– 有参数，全in

call proc_name(1,2);

– 有参数，有in，out，inout

set @t1=3;
call proc_name(1,2,@t1,@t2);


import pymysql

conn = pymysql.connect(host='127.0.0.1',user='root', password='root', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.dictcursor)

cursor.callproc('p1', args=(1, 22, 3, 4)) # 执行存储过程

cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") # 获取执行完存储的参数
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()


print(result)

4、查看存储过程

列出所有的存储过程

show  procedure  status;

5、查看存储过程生成语句

查看存储过程

show create procedure 存储过程名\g

事务

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

定义存储过程：

delimiter \\
drop procedure if exists p1;
create procedure p1(
    out p_return_code tinyint
)
begin 
  declare exit handler for sqlexception     -- 定义错误处理
  begin 
    -- error 
    set p_return_code = 1; 
    rollback; -- 回滚
  end; 

  declare exit handler for sqlwarning     -- 定义告警处理
  begin 
    -- warning 
    set p_return_code = 2; 
    rollback; 
  end; 

  start transaction; -- 开始事务，使下面的多条sql语句操作变成原子性操作
        update tb7 set licnese=(licnese-5) where nid=21; 
        update tb7 set licnese=(licnese+5) where nid=22; 

  commit; 

  -- success 
  set p_return_code = 0; 

end\\
delimiter ;

执行存储过程：

call p1(@p);
select @p;

sql防注入之动态sql

在高级语言的db api不提供防注入的参数化查询功能时，可以使用这种方法来防止sql注入。在pymysql中的调用点这里。

定义存储过程：

delimiter \\
drop procedure if exists proc_sql \\
create procedure proc_sql (
    in nid1 int,
    in nid2 int,
    in callsql varchar(255)
    )
begin
    set @nid1 = nid1;
    set @nid2 = nid2;
    set @callsql = callsql;
        prepare myprod from @callsql;
--     prepare prod from 'select * from tb2 where nid>? and  nid<?';    传入的值为字符串，？为占位符
--     用@nid1，和@nid2填充占位符
        execute myprod using @nid1,@nid2;
    deallocate prepare myprod; 

end\\
delimiter ;

调用存储过程

set @nid1=12;
set @nid2=15;
set @callsql = 'select * from tb7 where nid>? and nid<?';
call proc_sql(@nid1,@nid2,@callsql)

上一篇：同样都是开国之君曹丕和孙权相比谁更出色呢

下一篇：古代女子的化妆品是如何制成的？香味又是怎么来的？

数据库之MySQL高级

pymysql安装

sql注入

1.简单模拟登录

2.sql注入漏洞

3.sql注入原理

4.避免sql注入

5.模拟登陆代码

pymysql操作数据库

1.pymysql增

2.pymysql查

3.pymysql删改

4.pymysql删

视图

1.创建视图

2.修改视图

3.删除视图

4.查看视图

5.视图小结

触发器

1.创建触发器

2.多次触动触发器

3.自定义触发器新插入数据

函数

1、内置函数

2、自定义函数

3、删除函数

4、执行函数

5、查看函数

6、查看函数构建语句

存储过程

1、创建存储过程

2、删除存储过程

3、执行存储过程

4、查看存储过程

5、查看存储过程生成语句

事务

sql防注入之动态sql

MySQL创建带特殊字符的数据库名称方法示例

MySQL慢查询之开启慢查询

用命令创建MySQL数据库(de1)的方法

MySQL慢查询之pt-query-digest分析慢查询日志

Mysql数据库表定期备份的实现详解

PHP开发环境配置(MySQL数据库安装图文教程)

Python连接mysql数据库及python使用mysqldb连接数据库教程

mysql的innodb数据库引擎详解

Mysql数据库如何开启远程访问权限？

MYSQL基础之连接MYSQL、修改密码、添加用户