函数有且只有一个返回值，而存储过程不能有返回值。函数只能有普通参数，而存储过程可以有in,out,inout多个类型参数。存储过程中的语句功能更丰富，实现更复杂的业务逻辑，可以理解为一个按照预定步骤调用的执行过程，而函数中不能展示查询结果集语句，只是完成查询的工作后返回一个结果，功能针对性比较强。存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

3.13 事务控制

3.13.1 事务概述

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，如果操作就必须同时操作成功，如果有一个不成功则所有数据都不动。这时候数据库操作语句就构成一个事务。事务主要处理数据的增删改操作。

定义

一件事从开始发生到结束的过程

作用

确保数据操作过程中的数据完整和使用安全。

3.13.2 事务操作

开启事务

mysql>begin;

开始执行事务中的若干条SQL命令（增删改）终止事务，若begin之后使用commit提交事务或者使用rollback进行事务回滚。

mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束!

mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!

注意：事务操作只针对数据操作。rollback不能对数据库，数据表结构操作恢复。

3.13.3 事务四大特性

原子性（atomicity）

一个事务必须视为一个不可分割的最小工作单元，对于一个事务来说，不可能只执行其中的一部分操作,整个事务中的所有操作要么全部提交成功，要么全部失败回滚

一致性（consistency）

事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏。

隔离性（isolation）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，而多个事务相互独立。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

持久性（durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

3.13.4 事务隔离级别

事务四大特性中的隔离性是在使用事务时最为需要注意的特性，因为隔离级别不同带来的操作现象也有区别

隔离级别

读未提交：read uncommitted

事物A和事物B，事物A未提交的数据，事物B可以读取到

这里读取到的数据叫做“脏数据”

这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

读已提交：read committed

事物A和事物B，事物A提交的数据，事物B才能读取到

这种隔离级别高于读未提交

换句话说，对方事物提交之后的数据，我当前事物才能读取到

这种级别可以避免“脏数据”

这种隔离级别会导致“不可重复读取”

可重复读：repeatable read

事务A和事务B，事务A提交之后的数据，事务B读取不到

事务B是可重复读取数据

这种隔离级别高于读已提交

MySQL默认级别

虽然可以达到可重复读取，但是会导致“幻像读”

串行化：serializable

事务A和事务B，事务A在操作数据库时，事务B只能排队等待

这种隔离级别很少使用，吞吐量太低，用户体验差

这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

3.14 数据库优化

3.14.1 数据库设计范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

各种范式呈递次规范，越高的范式数据库冗余越小。但是范式越高也意味着表的划分更细，一个数据库中需要的表也就越多，此时多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。所以通常数据库设计遵循第一第二第三范式，以避免数据操作异常,又不至于表关系过于复杂。

范式简介：

第一范式： 数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等组合的数据项。简单来说要求数据库中的表示二维表，每个数据元素不可再分。

例如： 在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。

第二范式： 第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分，所有属性依赖于主属性。即选取一个能区分每个实体的属性或属性组，作为实体的唯一标识，每个属性都能被主属性筛选。其实简单理解要设置一个区分各个记录的主键就好了。

第三范式： 在第二范式的基础上属性不传递依赖，即每个属性不依赖其他非主属性。要求一个表中不包含已在其它表中包含的非主关键字信息。其实简单来说就是合理使用外键，使不同的表中不要有重复的字段就好了。

3.14.2 MySQL存储引擎

定义： mysql数据库管理系统中用来处理表的处理器基本操作

1. 用root用户登录mysql

mysql -u root -p

2. 添加用户 % 表示自动选择可用IP

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

3. 权限管理

# 增加权限

grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;

# 删除权限

revoke insert,update,select on 库.表 from 'user'@'%';

4. 刷新权限

flush privileges;

5. 删除用户

drop user "用户名"@"%"

权限列表

all privileges ，select ，insert ，update，delete，alter，create，drop等。

库.表 ： *.* 代表所有库的所有表

示例

1. 创建用户

mysql>create user 'work'@'%' identified by '123';

2. 添加授权用户work,密码123,对所有库的所有表有所有权限

mysql>grant all privileges on *.* to 'work'@'%' identified by '123' with grant option;

mysql>flush privileges;

3. 添加用户duty，密码123,对books库中所有表有查看，插入权限

mysql>grant select,insert on books.* to 'duty'@'%' identified by '123' with grant option;

mysql>flush privileges;

4. 删除work用户的删除权限

mysql>revoke delete on *.* from "work"@"%";

5. 删除用户duty

drop user "duty"@"%";

3.16 pymysql模块

pymysql是一个第三方库，如果自己的计算机上没有可以在终端使用命令进行安装。

sudo pip3 install pymysql

pymysql使用流程

建立数据库连接：db = pymysql.connect(…)创建游标对象：cur = db.cursor()游标方法: cur.execute(“insert …”)提交到数据库或者获取数据 : db.commit() / cur.fetchall()关闭游标对象 ：cur.close()断开数据库连接 ：db.close()

常用函数

db = pymysql.connect(参数列表)

功能: 链接数据库

host ：主机地址,本地 localhost

port ：端口号,默认3306

user ：用户名

password ：密码

database ：库

charset ：编码方式,推荐使用 utf8

cur = db.cursor()

功能： 创建游标

返回值：返回游标对象,用于执行具体SQL命令

cur.execute(sql,args_list)

功能： 执行SQL命令

参数： sql sql语句

args_list 列表，用于给sql语句传递参量

cur.executemany(sql命令,args_list)

功能： 多次执行SQL命令，执行次数由列表中元组数量决定

参数： sql sql语句

args_list 列表中包含元组 每个元组用于给sql语句传递参量，一般用于写操作。

db.commit() 提交到数据库执行，必须支持事务操作才有效

db.rollback() 回到原来的数据形态，必须支持事务操作才有效

cur.fetchone() 获取查询结果集的第一条数据，查找到返回一个元组否则返回None

cur.fetchmany(n) 获取前n条查找到的记录，返回结果为元组嵌套元组， ((记录1),(记录2))，查询不到内容返回空元组。

cur.fetchall() 获取所有查找到的记录，返回结果形式同上。

cur.close() 关闭游标对象

db.close() 关闭数据库连接

连接关闭数据库示例：

"""

数据库操作基本结构示例

"""

import pymysql

kwargs = {

"host": "localhost",

"port": 3306,

"user": "root",

"passwd": "123456",

"database": "stu",

"charset": "utf8"

}

# 链接数据库

db = pymysql.connect(**kwargs)

# 生成游标 游标：调用sql操作数据得到操作结果的对象

cur = db.cursor()

# 操作数据 读 / 写

# 关闭

cur.close()

db.close()

数据库读操作示例：

"""

数据库读操作示例

"""

import pymysql

kwargs = {

"host": "localhost",

"port": 3306,

"user": "root",

"passwd": "123456",

"database": "stu",

"charset": "utf8"

}

# 链接数据库

db = pymysql.connect(**kwargs)

# 生成游标 游标：调用sql操作数据得到操作结果的对象

cur = db.cursor()

# 操作数据 读

# sql = "select name,age,score from class where score>80;"

# cur.execute(sql) # 执行语句

# 给sql语句传值 使用第二个参数按照位置给sql语句%s传值

sql = "select name,age,score from class " \

"where score>%s and sex=%s;"

cur.execute(sql,[80,'m'])

# 一个查询结果

one = cur.fetchone()

print(one)

# 多个查询结果

many = cur.fetchmany(2)

print(many)

# 所有查询结果

all = cur.fetchall()

print(all)

# 迭代获取结果

# for row in cur:

# print(row)

# 关闭

cur.close()

db.close()

数据库读操作练习：在stu下创建一个数据表user 有三个字段 ：id user password

create table user(

id int primary key auto_increment,

user varchar(30),

password char(16));

其中有若干数据：

insert into user values

(1,"Tom",'abc'),

(2,"Abby","321");

编写一个函数，使用input分别输入用户名和密码

验证是否可以登录成功，如果可以则返回True

不可以返回False

"""

import pymysql

class User:

def __init__(self):

kwargs = {

"host": "localhost",

"port": 3306,

"user": "root",

"passwd": "123456",

"database": "stu",

"charset": "utf8"

}

self.db = pymysql.connect(**kwargs)

self.cur = self.db.cursor()

def close(self):

self.cur.close()

self.db.close()

def login(self,name, passwd):

sql="select user from user " \code>

"where user=%s and password=%s;"

self.cur.execute(sql,[name,passwd])

# (name,) or None

if self.cur.fetchone():

return True

else:

return False

if __name__ == '__main__':

user = User()

# 登录验证

name=input("用户名：")

passwd=input("密 码：")

print(user.login(name,passwd))

user.close()

文件写操作示例：

"""

数据库写操作示例

pymysql进行写操作如果数据表支持事务会自动开启事务

需要commit提交才能生效

如果数据表不支持事务则直接execute执行则生效

"""

import pymysql

kwargs = {

"host": "localhost",

"port": 3306,

"user": "root",

"passwd": "123456",

"database": "stu",

"charset": "utf8"

}

# 链接数据库

db = pymysql.connect(**kwargs)

# 生成游标 游标：调用sql操作数据得到操作结果的对象

cur = db.cursor()

# 操作数据 写 insert delete update

# try:

# sql = "update class set score=%s where name=%s;"

# cur.execute(sql,[99,"Tom"])

# db.commit() # 提交事务

# except Exception as e:

# print(e)

# db.rollback()

# 批量写操作

data = [

("zhang",'19','w',70),

("wang",'18','m',71),

("li",'20','w',72)

]

try:

sql = "insert into class " \

"(name,age,sex,score) " \

"values (%s,%s,%s,%s);"

cur.executemany(sql,data) # 多次执行语句

db.commit() # 提交事务

except Exception as e:

print(e)

db.rollback()

# 关闭

cur.close()

db.close()

写操作练习：

创建数据库 dict，数据库中创建数据表 words 字段为 ： id word mean

编写一个程序，将dict.txt单词本中的单词存储到这个数据表里

库 ： dict

create database dict charset=utf8;

表 ： words --> id word mean

create table words (

id int primary key auto_increment,

word char(30),

mean varchar(512)

);

import pymysql

import re

class Dict:

def __init__(self):

self.kwargs = {

"host": "localhost",

"port": 3306,

"user": "root",

"password": "123456",

"database": "dict",

"charset": "utf8"

}

self.connect()

# 完成数据库连接

def connect(self):

self.db = pymysql.connect(**self.kwargs)

self.cur = self.db.cursor()

# 关闭

def close(self):

self.cur.close()

self.db.close()

# 插入数据 提取文件中的单词

def insert_words(self, filename):

file = open(filename)

words = [] # [(word,mean),(word,mean)...]

for line in file:

tmp = re.findall(r"(\w+)\s+(.*)", line)

words += tmp # 大列表

file.close()

self.insert_data(words)

def insert_data(self, words):

try:

sql = "insert into words (word,mean) values (%s,%s);"

self.cur.executemany(sql, words)

self.db.commit() # 提交事务

except Exception as e:

print(e)

self.db.rollback() # 事务回滚

if __name__ == '__main__':

dict = Dict()

dict.insert_words("dict.txt") # 插入单词数据

dict.close()

文件存储

存储文件路径

优点：节省数据库空间，提取方便缺点：文件或者数据库发生迁移会导致文件丢失 存储文件本身

优点：安全可靠，数据库在文件就在缺点：占用数据库空间大，文件存取效率低

静态文件存储示例：

"""

存取二进制数据

"""

import pymysql

kwargs = {

"host": "localhost",

"port": 3306,

"user": "root",

"password": "123456",

"database": "stu",

"charset": "utf8"

}

# 连接数据库

db = pymysql.connect(**kwargs)

# 创建游标 使用sql操作数据得到结果的对象

cur = db.cursor()

# 存储图片

# with open("left.png",'rb') as f:

# data = f.read()

# sql = "update class set image=%s where id=4;"

# cur.execute(sql,[data])

# db.commit()

# 取出图片

sql = "select image from class where id=4;"

cur.execute(sql)

data = cur.fetchone()[0]

with open("xxx.png",'wb') as f:

f.write(data)

# 关闭

cur.close()

db.close()