1.python链接数据库
import pymysql
# host 指的是数据库服务器的主机地址
# user 连接数据库的用户
# 打开一个数据库的连接,获取一个db对象
# db = pymysql.connet() # 结果是一个Connection类型的对象
# db.cursor() 获取到一个cursor对象,用它来操作数据库
# with 语句上下文管理器,会调用对象的 __enter__ 方法,获取enter方法的执行结果
# with pymysql.connect() as x # x的结果就是一个cursor对象
db = pymysql.connect(host='localhost',
user='root',
password='abcd1234',
database='python201',
port=3306,
charset='utf8')
cursor = db.cursor()
cursor.execute('select * from student')
cursor.close()
db.commit()
db.close()
for info in cursor.fetchall():
print(info)
2.sql 注入
import pymysql, hashlib
username = input('请输入用户名:')
password = input('请输入密码:')
# 进行 md5 加密
h = hashlib.md5()
h.update(password.encode('utf8'))
password = h.hexdigest()
db = pymysql.connect(host='localhost',
user='root',
password='abcd1234',
database='python201',
port=3306,
charset='utf8')
cursor = db.cursor()
sql = 'select * from user where name="%s" and password="%s"'
print(sql)
# 执行sql语句
cursor.execute(sql, (username, password))
cursor.close()
db.commit()
db.close()
result = cursor.fetchone()
if not result:
print('用户名或者密码错误')
else:
print('欢迎回来{},您的信息是{}'.format(username, result))
数据库高级特性
一、存储引擎
存储引擎就是如何存储数据、如何为数据建立索引和如何更新、查询数据等技术的实现方法。
MySQL 默认支持多种存储引擎,以适用于不同领域 的数据库应用需要,用户可以通过选择使用不同的存储引擎提高应用的效率,提供灵活的存储。
查看当前的存储引擎
show variables like '%storage_engine';
show engines;
MySQL 常用的存储引擎
-
InnoDB
事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键,InnoDB是默认的MySQL引擎。
InnoDB主要特性有:
- InnoDB 给 MySQL 提供了具有提交、回滚、崩溃恢复能力的事务安全存储引擎。
- InnoDB 是为处理巨大数据量的最大性能设计。它的 CPU 效率比其他基于磁盘的关系型数据库引擎高。
- InnoDB 存储引擎自带缓冲池,可以将数据和索引缓存在内存中。
- InnoDB 支持外键完整性约束。
- InnoDB 被用在众多需要高性能的大型数据库站点上
- InnoDB 支持行级锁
-
MyISAM
MyISAM 基于 ISAM 存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度,但不支持事物。
MyISAM主要特性有:
- 大文件支持更好
- 当删除、更新、插入混用时,产生更少碎片。
- 每个 MyISAM 表最大索引数是64,这可以通过重新编译来改变。每个索引最大的列数是16
- 最大的键长度是1000字节。
- BLOB和TEXT列可以被索引
- NULL 被允许在索引的列中,这个值占每个键的0~1个字节
- 所有数字键值以高字节优先被存储以允许一个更高的索引压缩
- MyISAM 类型表的 AUTO_INCREMENT 列更新比 InnoDB 类型的 AUTO_INCREMENT 更快
- 可以把数据文件和索引文件放在不同目录
- 每个字符列可以有不同的字符集
- 有 VARCHAR 的表可以固定或动态记录长度
- VARCHAR 和 CHAR 列可以多达 64KB
- 只支持表锁
-
MEMORY
MEMORY 存储引擎将表中的数据存储到内存中,为查询和引用其他表数据提供快速访问。
存储引擎的选择
一般来说,对插入和并发性能要求较高的,或者需要外键,或者需要事务支持的情况下,需要选择 InnoDB,
插入较少,查询较多的场景,优先考虑 MyISAM。
使用引擎
一般在建表时添加
create table abc (
name char(10)
) engine=MyISAM charset=utf8;
create table xyz (
name char(10)
) engine=InnoDB charset=utf8;
InnoDB 和 MyISAM 在文件方面的区别
-
InnoDB 将一张表存储为两个文件
- demo.frm -> 存储表的结构和索引
- demo.ibd -> 存储数据,ibd 存储是有限的, 存储不足自动创建 ibd1, ibd2
- InnoDB 的文件创建在对应的数据库中, 不能任意的移动
-
MyISAM 将一张表存储为三个文件
- demo.frm -> 存储表的结构
- demo.myd -> 存储数据
- demo.myi -> 存储表的索引
- MyISAM 的文件可以任意的移动
二、关系与外键
关系
-
一对一
- 在 A 表中有一条记录,在 B 表中同样有唯一条记录相匹配
- 比如: 学生表和成绩表
-
一对多 / 多对一
- 在 A 表中有一条记录,在 B 表中有多条记录一直对应
- 比如: 博客中的用户表和文章表
-
多对多
- A 表中的一条记录有多条 B 表数据对应, 同样 B 表中一条数据在 A 表中也有多条与之对应
- 比如: 博客中的收藏表
外键
外键是一种约束。他只是保证数据的一致性,并不能给系统性能带来任何好处。
建立外键时,都会在外键列上建立对应的索引。外键的存在会在每一次数据插入、修改时进行约束检查,如果不满足外键约束,则禁止数据的插入或修改,这必然带来一个问题,就是在数据量特别大的情况下,每一次约束检查必然导致性能的下降。
出于性能的考虑,如果我们的系统对性能要求较高,那么可以考虑在生产环境中不使用外键。
-
构造数据
-- 用户表 create table `user` ( `id` int unsigned primary key auto_increment, `name` char(32) not null ) charset=utf8; -- 商品表 create table `product` ( `id` int unsigned primary key auto_increment, `name` char(32) not null unique, `price` float ) charset=utf8; -- 用户信息表: 一对一 create table `userinfo` ( `id` int unsigned primary key auto_increment, `phone` int unsigned unique, `age` int unsigned, `location` varchar(128) ) charset=utf8; -- 用户组表: 一对多 create table `group` ( `id` int unsigned primary key auto_increment, `name` char(32) not null unique ) charset=utf8; -- 订单表: 多对多 create table `order` ( `id` int unsigned primary key auto_increment, `uid` int unsigned, `pid` int unsigned ) charset=utf8;
-
添加外键
-- 为 user 和 userinfo 建立关联的外键 alter table userinfo add constraint fk_user_id foreign key(id) references user(id); -- 建立用户与组的外键约束 alter table `user` add `gid` int unsigned; alter table `user` add constraint `fk_group_id` foreign key(`gid`) references `group`(`id`); -- 建立用户、商品、订单的外键约束 alter table `order` add constraint `fk_user_id` foreign key(`uid`) references `user`(`id`); alter table `order` add constraint `fk_prod_id` foreign key(`pid`) references `product`(`id`);
-
尝试插入数据后在删除,分别先对主表和子表进行一次删除。
-
删除外键。
alter table `表名` drop foreign key `外键名`
数据库事务及其他
一、事务
1. 事务简介
事务主要用于处理操作量大、复杂度高、并且关联性强的数据。
比如说, 在人员管理系统中, 你删除一个人员, 你即需要删除人员的基本资料, 也要删除和该人员相关的信息, 如信箱, 文章等等, 这样, 这些数据库操作语句就构成一个事务!
在 MySQL 中只有 Innodb 存储引擎支持事务。
事务处理可以用来维护数据库的完整性, 保证成批的 SQL 语句要么全部执行, 要么全部不执行。主要针对 insert, update, delete 语句而设置。
2. 事务四大特性
在写入或更新资料的过程中, 为保证事务 (transaction) 是正确可靠的, 所必须具备的四个特性 (ACID):
-
原子性 (Atomicity) :
- 事务中的所有操作, 要么全部完成, 要么全部不完成, 不会结束在中间某个环节。
- 事务在执行过程中发生错误, 会被回滚 (Rollback) 到事务开始前的状态, 就像这个事务从来没有执行过一样。
-
一致性 (Consistency):
在事务开始之前和事务结束以后, 数据库的完整性没有被破坏。 这表示写入的资料必须完全符合所有的预设规则, 这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
-
隔离性 (Isolation):
并发事务之间互相影响的程度,比如一个事务会不会读取到另一个未提交的事务修改的数据。在事务并发操作时,可能出现的问题有: 脏读:事务A修改了一个数据,但未提交,事务B读到了事务A未提交的更新结果,如果事务A提交失败,事务B读到的就是脏数据。 不可重复读:在同一个事务中,对于同一份数据读取到的结果不一致。比如,事务B在事务A提交前读到的结果,和提交后读到的结果可能不同。不可重复读出现的原因就是事务并发修改记录,要避免这种情况,最简单的方法就是对要修改的记录加锁,这回导致锁竞争加剧,影响性能。另一种方法是通过MVCC可以在无锁的情况下,避免不可重复读。 幻读:在同一个事务中,同一个查询多次返回的结果不一致。事务A新增了一条记录,事务B在事务A提交前后各执行了一次查询操作,发现后一次比前一次多了一条记录。幻读是由于并发事务增加记录导致的,这个不能像不可重复读通过记录加锁解决,因为对于新增的记录根本无法加锁。需要将事务串行化,才能避免幻读。
-
读取未提交 (Read uncommitted)
- 所有事务都可以看到其他未提交事务的执行结果
- 本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少
- 该级别引发的问题是——脏读(Dirty Read):读取到了未提交的数据
-
读提交 (read committed)
-
这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)
-
它满足了隔离的简单定义:一个事务只能看见已经提交事务做的改变
-
这种隔离级别出现的问题是: 不可重复读(Nonrepeatable Read):
不可重复读意味着我们在同一个事务中执行完全相同的 select 语句时可能看到不一样的结果。
导致这种情况的原因可能有:
- 有一个交叉的事务有新的commit,导致了数据的改变;
- 一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit
-
-
可重复读 (repeatable read)
- 这是MySQL的默认事务隔离级别
- 它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行
- 此级别可能出现的问题: 幻读(Phantom Read):当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行
- InnoDB 通过多版本并发控制 (MVCC,Multiversion Concurrency Control) 机制解决幻读问题;
- InnoDB 还通过间隙锁解决幻读问题
-
串行化 (Serializable)
- 这是最高的隔离级别
- 它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。MySQL锁总结
- 在这个级别,可能导致大量的超时现象和锁竞争
-
-
持久性 (Durability):
事务处理结束后, 对数据的修改就是永久的, 即便系统故障也不会丢失。
3. 语法与使用
-
开启事务:
BEGIN
或START TRANSACTION
-
提交事务:
COMMIT
, 提交会让所有修改生效 -
回滚:
ROLLBACK
, 撤销正在进行的所有未提交的修改 -
创建保存点:
SAVEPOINT identifier
-
删除保存点:
RELEASE SAVEPOINT identifier
-
把事务回滚到保存点:
ROLLBACK TO identifier
-
查询事务的隔离级别:
show variables like '%isolation%';
-
设置事务的隔离级别:
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
InnoDB 提供的隔离级别有
READ
UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
4. 示例
create table `abc` (
id int unsigned primary key auto_increment,
name varchar(32) unique,
age int unsigned
) charset=utf8;
begin;
insert into abc (name, age) values ('aa', 11);
insert into abc (name, age) values ('bb', 22);
-- 在事务中查看一下数据
-- 同时另开一个窗口,连接到 MySQL 查看一下数据是否一样
select * from abc;
commit;
begin;
insert into abc (name, age) values ('cc', 33);
insert into abc (name, age) values ('dd', 44);
update abc set age=77 where name='aa';
-- 在事务中查看一下数据
select * from abc;
rollback;
select * from abc; -- 事务结束后在查看一下数据
二、存储过程
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
-
优点
- 存储过程可封装,并隐藏复杂的商业逻辑。
- 存储过程可以回传值,并可以接受参数。
- 存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同。
- 存储过程可以用在数据检验,强制实行商业逻辑等。
-
缺点
- 存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。
- 存储过程的性能调校与撰写,受限于各种数据库系统。
语法
-
声明语句结束符,可以自定义:
存储过程中有很多的SQL语句,SQL语句的后面为了保证语法结构必须要有分号(;),但是默认情况下分号表示客户端代码发送到服务器执行。必须更改结束符
DELIMITER $$
-- 或者
DELIMITER //
-
声明存储过程:
CREATE PROCEDURE demo_in_parameter(IN p_in int)
-
存储过程开始和结束符号:
BEGIN .... END
-
变量赋值:
SET @p_in=1
-
变量定义:
DECLARE l_int int unsigned default 4000000;
-
创建mysql存储过程、存储函数:
create procedure 存储过程名(参数)
-
存储过程体:
create function 存储函数名(参数)
使用
-
简单用法
-- 定义 -- 如果存储过程中就一条SQL语句,begin…end两个关键字可以省略 create procedure get_info() select * from student; -- 调用 call get_info();
-
复杂一点的 (备注:只能在标准 mysql 客户端中执行,mycli 无法识别)
delimiter // -- 定义前,将分隔符改成 // create procedure foo(in uid int) begin select * from student where `id`=uid; update student set `city`='北京' where `id`=uid; end// delimiter ; -- 定义完以后可以将分隔符改回 分号 call foo(3);
-
查看存储过程
show procedure status like "%foo%"; show create procedure foo;
-
删除存储过程
drop procedure foo;
延伸阅读
三、Python操作
-
安装:
pip install pymysql
-
使用
import pymysql db = pymysql.connect(host='localhost', user='user', password='passwd', db='db', charset='utf8') try: with db.cursor() as cursor: # 插入 sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)" cursor.execute(sql, ('webmaster@python.org', 'very-secret')) # 需要手动提交才会执行 db.commit() with db.cursor() as cursor: # 读取记录 sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s" cursor.execute(sql, ('webmaster@python.org',)) result = cursor.fetchone() print(result) finally: db.close()
四、sql注入
什么是SQL注入?
SQL注入(SQLi)是一种注入攻击,,可以执行恶意SQL语句。它通过将任意SQL代码插入数据库查询,使攻击者能够完全控制Web应用程序后面的数据库服务器。攻击者可以使用SQL注入漏洞绕过应用程序安全措施;可以绕过网页或Web应用程序的身份验证和授权,并检索整个SQL数据库的内容;还可以使用SQL注入来添加,修改和删除数据库中的记录。
思考:下面的代码有没有问题?
import pymysql
db = pymysql.connect(host='localhost',user='root',password='abcd1234',db='test',charset='utf8')
name=input('请输入用户名:')
password=input('请输入密码:')
try:
with db.cursor() as cursor:
sql = 'select * from user where name="%s" and password="%s"' %(name,password)
print(sql)
cursor.execute(sql)
print(cursor.fetchone())
db.commit()
finally:
db.close()
五、数据备份与恢复
-
备份
mysqldump -h localhost -u root -p dbname > dbname.sql
-
恢复
mysql -h localhost -u root -p123456 dbname < ./dbname.sql