Flush PRIVILEGES; -- 刷新权限并生效
6)查看授权
- 查看当前用户: show grants;
-查看其他用户: show grants for '用户名'@'客户端';
7)吊销权限(取消用户的某个权限)
- 语法: revoke 权限列表 on 库名.表名 from '用户名'@'客户端地址'
- 示例: 吊销Daniel用户所有库,所有列表的插入数据权限
revoke insert on *.* from 'bank_user'@'%';
revoke insert on bank.* from 'bank_user'@'%';
4. 数据库事务(重点)
- 事务(Transacyion):
- 数据库执行一系列操作时，全都执行，全都不执行
保证数据的一致性、正确性
- 例如在一笔转账中,执行需要三个操作,
从转出账户减去相应金额
在转入账户增加相应金额
登记一笔转账明细
以上三个操作,要么全部成功,要么全部失败
3)使用事务的情况
- 涉及到多表的增删改操作
- 执行这些操作时需要保证一致性,正确性
4)启用事务条件要求:必须是Innodb存储引擎
5)事务的额特征(ACID)：
- 原子性(Atomicity)：事务是一个整体,要么全部执行,要么全部不执行
- 一致性(Consis.
tency):事务执行完成后,从一个一致状态变成另一个一致状态
- 隔离性(Isolation)：不同的事务不会相互影响,干扰
- 持久性(Durability):一旦事务提交,对数据库的修改就必须永久保留下来
6)如何进行事务操作
- 开启: start transaction
- 提交: commit
- 回滚:rollback
- 示例：在两个账户间进行转账
第一步:减去转出账户余额
第二步:在转入账号上加上相等的余额
提交事务
start transaction;
update acct_new set balance = balance - 100
where acct_no = '6223450001';
update acct_new set balance = balance + 100
where acct_no ='622456000020';
commit;---或rollback
----在提交事务前,重新登录一个客户端
----查看数据是否变更
7)SQL语句分类
- 数据查询语言(DQL):查询数据,不改变数据
- 数据定义语言(DDL):定义数据结构,如建表/库,删除库/表,创建/删除索引,修改表结构
- 数据操作语言(DML):对数据进行增删改
- 数据控制语言(DCL):权限管理,事务操作,数据库监视...
-------------------------------------------------day25.new---------------------------------------------------
1.锁
1）锁：对数据库的控制权
2）目的：解决多个工作单元并发操作数据的正确性
3）分类：
a)锁类型
- 读锁（共享锁）：加锁后，可以读取，但不能修改
- 写锁（排它锁）：加锁后，不能进行读写
锁粒度
- 行级锁（细粒度）：锁定一行，并发效率高
控制较为复杂，消耗资源多
- 表级锁（粗粒度）：锁定整张表，并发效率低
控制简单，消耗资源较少
2.存储引擎
1)什么是存储引擎:表的存储方式,包括存储机制,索引机制,锁定方式等
2)基本操作:
- 查看:show engines;
- 查看某个表的存储引擎方式:show create table 表名
create table t1(id int primary key)engine=InnoDB default charset=utf8;
engine 指定存储
- 修改存储引擎方式
alter table t1 engine = MyISAM;
3)常见存储引擎
a)InooDB
特点:支持事务,行级锁,外键
共享表空间
*.frm 表结构和索引
*.ibd 表记录
show global variables like '%datadir'命令查看数据存储位置
sudo -i切换到root用户,进入目录查看
- 使用场合
更新操作密集的表
有数据库事务支持的要求
自动灾备,恢复
有外键约束要求
支持自增长字段(auto_increment)
- 支持表级锁定、不支持事务、不支持外键
不支持行锁定，访问速度较快
- 独享表空间
表名.frm: 表结构
表名.myd: 表数据
表名.myi: 表索引
- 适用场合
查询操作较多
数据一致性要求较低（不支持事务）
没有外键约束
Memory(内存表):
- 表结构存储在磁盘上(硬盘)，数据存储在内存
访问速度很快
- 文件：表名.frm 存储表结构
- 服务（或机器）重启后，表中的数据丢失
- 适用场合：
数据量小、访问速度要求极快、访问频率高、
数据丢失后不会造成影响
- 示例:
1:创建memory引擎的表
create table t2(id int)engine=memory;
2:插入数据并查询
3:重启服务,再查询,数据消失
sudo /etc/init.d/mysql restart
a)实体(Entry): 现实中可以区分的事物称之为实体
b)关系(Relationship): 实体之间的联系
- 一对一
- 一对多
- 多对多
c)属性：实体所具有的数据特征
d)E-R图：用图形化方式，表达实体间的关系
矩形：表示实体
椭圆：实体的属性
菱形：实体间的关系
一对一：菱形左右各写一个1
一对多：菱形左右分别用1，N来表示
多对多：菱形左右分别用M, N来表示
e）练习：画出账户,交易明细,客户实体之间的E-R图（见ER图.pptx）
4. 使用Python访问MySQL
1）pymysql库：Python语言访问MySQL接口
- 安装：
在线：sudo pip3 install pymysql
离线：
第一步：下载安装包，下载地址：
https://pypi.org/project/PyMySQL/ PyMySQL-0.9.3.tar.gz
第二步：解压包，并执行安装
tar -zxvf PyMySQL-0.9.3.tar.gz
cd PyMySQL-0.9.3
第三步:进入目录,安装
python3 setup.py install
- 验证：进入Python交互模式，执行导入包操作
import pymysql
如果不报错，则说明安装成功
# 第一步：导入pymysql模块
import pymysql
host = 'localhost'#服务器地址
user = 'root'#用户名
passwd = '123456'#密码
dbname = 'bank'#库名称
#第二步：建立到数据库服务器的连接，创建连接对象
conn = pymysql.connect(host,user,passwd,dbname)
#第三步：获取游标对象(cursor)，通过调用数据库连接 对象获得游标
cursor = conn.cursor()
#第四步：利用cursor对象提供的方法，执行SQL语句
cursor.execute("select * from acct_new")
result = cursor.fetchall()#取查询到的数据
for r in result:
tmp = "帐号：%s,户名：%s,金额:%s"%(r[1],r[1],r[6])
print(tmp)
#第五步：提交事务（如果需要）
#第六步：关闭游标对象
cursor.close()
#第七步：关闭数据库连接对象
conn.close()
2）PyMySQL访问MySQL的流程
第一步：导入pymysql模块
第二步：建立到数据库服务器的连接，创建连接对象
第三步：创建游标对象(cursor)，通过调用数据库连接
对象获得游标
第四步：利用cursor对象提供的方法，执行SQL语句
第五步：提交事务（如果需要）
第六步：关闭游标对象
第七步：关闭数据库连接对象
3）PyMySQL模块的主要方法
- connect: 连接数据库
参数：host 服务器地址
port 服务器端口(省略连默认端)
user 用户名
passwd 密码
db 数据库名称
charset 指定编码格式
- connection连接对象支持的方法
cursor() 获取游标
commit() 提交事务
rollback() 回滚事务
close() 关闭游标
- cursor游标对象支持的方法
execute(sql) 执行SQL语句
fetchall() 取得查询结果集中的所有数据
fetchone() 取查询结果集中一笔数据
fetchmany(size) 取得指定笔数数据
close() 关闭游标对象
只读属性：
rowcount 返回修改影响的笔数
4）示例：
示例1：查询
# pymysql查询示例
import pymysql
from db_conf import * #导入配置
#建立到数据库服务器的连接，创建连接对象
conn = pymysql.connect(host, user, \
password, dbname)
#创建游标对象(cursor)，通过调用数据库连接
#对象获得游标
cursor = conn.cursor()
#利用cursor对象，执行SQL语句
sql = "select * from acct"
cursor.execute(sql) #执行SQL语句
#取出查询结果,并打印
result = cursor.fetchall() #result是元组
for r in result: #遍历result
acct_no = r[0] #账号
acct_name = r[1] #户名
if r[6]: #判断是否为空值
balance = float(r[6]) #余额
else:
balance = 0.00 #余额为空设置为0
print("账号:%s, 户名:%s, 余额:%.2f" % \
(acct_no, acct_name, balance))
#关闭游标对象
cursor.close()
#关闭数据库连接对象
conn.close()
示例2：插入
# pymysql的插入示例
import pymysql
from db_conf import * #导入配置
try:
conn = pymysql.connect(host, user, \
password, dbname) #连接数据库
cursor = conn.cursor() #获取游标
#执行SQL语句
sql = '''insert into
acct(acct_no, acct_name, cust_no,
acct_type, reg_date, status, balance)
values('622345000010','Robert', 'C0010',
1, date(now()), 1, 33.00)'''
print(sql)
cursor.execute(sql) #执行
conn.commit() #提交事务
print("Insert OK")
except Exception as e:
print("数据库插入异常")
print(e)
finally:
cursor.close() #关闭游标
conn.close() #关闭连接
示例3：修改
import pymysql
from db_conf import * #导入配置
try:
conn = pymysql.connect(host, user, \
password, dbname) #连接数据库
cursor = conn.cursor() #获取游标
#执行SQL语句
sql = '''update acct
set balance = balance + 1000
where acct_no='622345000010'
'''
cursor.execute(sql) #执行
conn.commit() #提交事务
print("修改笔数:%d" % cursor.rowcount)
except Exception as e:
print("数据库修改异常")
print(e)
finally:
cursor.close() #关闭游标
conn.close() #关闭连接
示例4：删除
# pymysql删除示例
import pymysql
from db_conf import * #导入配置
try:
conn = pymysql.connect(host, user, \
password, dbname) #连接数据库
cursor = conn.cursor() #获取游标
#执行SQL语句
sql = '''delete from acct
where acct_no='622345000010'
'''
cursor.execute(sql) #执行
conn.commit() #提交事务
print("删除笔数:%d" % cursor.rowcount)
except Exception as e:
print("数据库删除异常")
print(e)
finally:
cursor.close() #关闭游标
conn.close() #关闭连接
课堂作业：账户管理修改、删除功能添加上
5. SQL语句优化
1）在经常查询、排序的字段上建立索引
2）尽量少使用不等于条件判断，不等于会放弃使用索引
3）少使用null值判断，null值判断会放弃使用索引
4）条件判断少使用or连接，in，not in
5）模糊查询避免%前置
6）查询时如果select *，直接写成字段名会提高效率
7）尽量少在where子句中，使用字段进行运算。例如：
select * from acct where balance / 2 > 5000
8）数值类型的字段，进行条件比较时，一般比
字符串比较效率更高
作业：完善账户管理系统的修改、删除功能
账户管理系统启动时，增加登录功能
（原理：打印菜单前，先输入用户名、密码
并到数据库自建的用户表中查询
如果用户名、密码匹配，则登录成功）
1 #账户类
2 class Acct:
3 def __init__(self,acct_no,acct_name,acct_type,balance):
4 self.acct_no=acct_no
5 self.acct_name=acct_name
6 self.acct_type = acct_type
7 self.balance = balance
8 def __str__(self):
9 ret = "账号：%s,户名：%s,类型：%d,余额：%.2f"%(self.acct_no,self.acct_name,self.acct_type,self.balance)
10 return retacct.py
1 #账户管理类(业务逻辑层)
2 #实现账户新增,修改,删除等逻辑处理
3 #账户类，仅用于数据传输
4 from db_oper import *
5 from acct import *
6 class AccMange:#账户管理类
7 def __init__(self,db_oper):
8 self.db_oper = db_oper#数据访问对象
9 #查询所有庄户信息
10 def query_all_acct(self):
11 accts =[]#返回的Acct对象列表,可能有多个对象
12 #拼装所需要的SQL
13 sql = "select * from acct_new"
14 #执行查询
15 result = self.db_oper.do_query(sql)
16 if not result:
17 print("查询结果为空")
18 return None
19 #返回结果：实例化一个Acct对象列表返回
20 for r in result:#便利数据集
21 acct_no = r[0]#账户
22 acct_name = r[1]#姓名
23 acct_type =int(r[3])#类型
24 balance = r[6]#金额
25 accts.append(Acct(acct_no,acct_name,acct_type,balance))
26 return accts#返回对象列表
27 #根据账户查询,最多返回一个Ａcct对象
28
29 def query_by_id(self,acct_no):
30 sql = "select * from acct_new where acct_no = %s" % acct_no
31 result = self.db_oper.do_query(sql)#执行查询
32 if not result:
33 print("查询返回空对象")
34 return None
35 #提取查询结果,实例化一个Acct对象返回
36 r = result[0]#取得第一行数据
37 acct_no = r[0]#账号
38 acct_name = r[1]#户名
39 acct_type = int(r[3])#类型
40 balance = r[6]#余额
41 return Acct(acct_no,acct_name,acct_type,balance)
42
43 def insert_acct(self,acct_new):#插入
44 sql = '''insert into acct_new(acct_no,acct_name,acct_type,balance)values('%s','%s',%d,%.2f)'''%(acct_new.acct_no,acct_new.acct_name,acct_new.acct_type,acct_new.balance)
45 print("\nsql:%s\n"%sql)
46 result =self.db_oper.do_update(sql)
47 print("执行结构,影响行数：%d"%result)
48 return
49
50 def update_acct(self,acct_new):#更新
51 sql="update acct set acct_type =%d,balance = %.2f where acct_no='%s'"%(acct_new.acct_type,acct_new.balance,acct_new.acct_no)
52 print("\nsql:%s\n"%sql)
53 result = self.db_oper.do_update(sql)
54 print("执行结束,影响行数:%d"%result)
55 return
56
57 def query_by_name(self,acct_name):#模糊？
58 if not acct_name:
59 print("户名对象非法")
60 return None
61 if acct_name =="":
62 print("用户名不能为空")
63 return None
64
65 accts =[]
66 sql = "select * from acct_new where acct_name ='%s'" % acct_name
67
68 result = self.db_oper.do_query(sql)
69 if not result:
70 print("查询返回空对象")
71 return None
72
73 for acct_info in result:
74 acct_no = acct_info[0]
75 acct_name = acct_info[1]
76 acct_type = int(acct_info[3])
77 balance = acct_info[6]
78 accts.append(Acct(acct_no,acct_name,acct_type,balance))
79 return accts
80
81 if __name__=="__main__":
82 db_oper = DBOper()#实例化访问对象
83 db_oper.open_conn()#打开数据库连接
84
85 am = AccMange(db_oper)#实例化AcctMange对象
86 db_oper.close_conn()
87acct_manger.py
1 #ui
2 from db_oper import *
3 from acct_manager import *
4 from acct import *
5
6 #全局变量
7 db_oper = None#打印主菜单
8 am =None
9 def print_menu():#打印主菜单
10 #根据用户输入,进行不同操作
11 menu = '''－－－－－－－－－－请选取要执行的操作－－－－－－－－－
12 １-查询所有 ４-新增账户
13 ２-根据用户查询 ５-修改账户
14 ３-根据用户名查询 ６-删除账户
15 '''
16 print(menu)
17 def query_all():#查询所有账户
18 accts = am.query_all_acct()
19 for a in accts:
20 print(a)
21
22 def query_by_id():
23 acct_no = input("请输入要查询的账号：")
24 if not acct_no:
25 print("输入账号不合法")
26 return
27
28 acct= am.query_by_id(acct_no)
29 print("\n查询结果：")
30 print(acct)#打印账户信息
31 print("\n")
32
33 def query_by_name():#根据用户名查询
34 acct_name = input("请输入要查询的户名：")
35 if not acct_name:
36 print("输入账号不合法")
37 return
38
39 accts =am.query_by_name(acct_name)
40 print("\n查询结果：")
41 for acct in accts:
42 print(acct)#打印账户信息
43 print("\n")
44
45 def add_acct():
46 try:
47 acct_no = input("请输入账号：")
48 acct_name = input("请输入户名:")
49 acct_type = int(input("请输入账户类型(1-借记卡 2-理财卡 3-代缴代扣卡)："))
50 balance = float(input("请输入开户金额:"))
51 assert acct_type in [1, 2, 3]
52 assert balance >= 0.000001
53
54 new_acct =Acct(acct_no,acct_name,acct_type,balance)
55 am.insert_acct(new_acct)
56 except Exception as e:
57 print("操作错误")
58 print(e)
59 return
60
61 def update_acct():
62 try:
63 acct_no = input("请输入账号：")
64 acct_name = input("请输入户名:")
65 acct_type = int(input("请输入账户类型(1-借记卡 2-理财卡 3-代缴代扣卡)："))
66 balance = float(input("请输入开户金额:"))
67
68 assert acct_type in [1, 2, 3]
69 assert balance >= 0.000001
70
71 new_acct = Acct(acct_no,acct_name,acct_type,balance)
72 am.update_acct(new_acct)
73 except Exception as e:
74 print("操作错误")
75 print(e)
76 return
77 #主函数
78 def main():
79 global db_oper
80 global am
81 db_oper = DBOper()#实例化数据访问对象
82 db_oper.open_conn()#打开数据库连接
83 am = AccMange(db_oper)#实例化AccMange对象
84
85 while True:
86 print_menu()
87 oper = input("请选则要执行的操作:")
88 if oper == "1": #查询所有账户
89 query_all()
90 elif oper == "2": #根据账号查询
91 query_by_id()
92 elif oper == "3": #根据户名查询
93 query_by_name()
94 elif oper == "4": #新增账户
95 add_acct()
96 elif oper == "5": #修改账户
97 update_acct()
98 elif oper == "6": #删除账户
99 pass
100 else:
101 break
102 db_oper.close_conn()
103
104 if __name__ == "__main__":
105 main()acct_manger_ui.py
1 #数据库配置
2 host = 'localhost'#服务器地址
3 user = 'root'#用户名
4 passwd = '123456'#密码
5 dbname = 'bank'#库名称db_conf.py
1 #数据库访问类
2 import pymysql
3 import db_conf
4 class DBOper:
5 #构造方法
6 def __init__(self):
7 self.host =db_conf.host
8 self.user =db_conf.user
9 self.passwd =db_conf.passwd
10 self.dbname =db_conf.dbname
11 self.db_conn = None#数据库的连接对象
12
13 #连接数据库
14 def open_conn(self):
15 try:
16 self.db_conn = pymysql.connect(self.host,self.user,self.passwd,self.dbname)
17 except Exception as e:
18 print("连接数据库失败")
19 print(e)
20 else:
21 print("连接数据库成功")
22 #关闭连接
23 def close_conn(self):
24 try:
25 self.db_conn.close()
26 except Exception as e:
27 print("连接数据库失败")
28 print(e)
29 else:
30 print("关闭数据库成功")
31
32 #执行查询返回结果集
33 def do_query(self,sql):
34 if not sql:#参数合法性判断
35 print("sql语句不合法")
36 return None
37 if sql == "":#参数合法性判断
38 print("sql语句不合法")
39 return None
40
41 try:
42 cursor = self.db_conn.cursor()#获取游标
43 cursor.execute(sql)#执行sql语句
44 result = cursor.fetchall()#获取数据
45 cursor.close()#关闭游标
46 return result#返回查询数据集
47 except Exception as e:
48 print("查询出错")
49 print(e)
50 return None
51
52
53 #执行增删改等变更操作
54 def do_update(self,sql):
55 if not sql:#参数合法性判断
56 print("sql语句不合法")
57 return None
58 if sql == "":#参数合法性判断
59 print("sql语句不合法")
60 return None
61
62 try:
63 cursor = self.db_conn.cursor()#获取游标
64 result = cursor.execute(sql)#执行ＳＱＬ语句
65 self.db_conn.commit()#提交事物
66 cursor.close()
67 return result#返回受影响的笔数
68 except Exception as e:
69 print("执行sql语句出错")
70 print(e)
71 return None
72
73 #测试
74 if __name__=="__main__":
75 dboper = DBOper()
76 dboper.open_conn()
77 ret = dboper.do_query("select * from acct_new")
78 for x in ret:
79 print(x)
80
81 ret = dboper.do_update("update acct_new set balance = balance + 100 where acct_no = '6223456000 '")
82
83 print(ret)
84 #查询
85 # result = dboper.do_query("select * from acct_new")
86 # for x in result:
87 # print(x)
88
89 # dboper.close_conn()#关闭数据库连接
90
91 #修改数据测试
92 # sql = '''update acct_new set balance = balance +1000 where acct_no ="6223456000"'''
93 # ret = dboper.do_update(sql)
94 # if not ret:#返回空对象,出错
95 # print("执行修改错误")
96 # #非空对象,执行成功
97 # else:
98 # print("影响笔数：%d"% ret)
99 # dboper.close_conn()#关闭数据库连接
100
101
102
103db_oper.py
