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)锁类型
- 读锁(共享锁):加锁后,可以读取,但不能修改
- 写锁(排它锁):加锁后,不能进行读写
b)锁粒度
- 行级锁(细粒度):锁定一行,并发效率高
控制较为复杂,消耗资源多
- 表级锁(粗粒度):锁定整张表,并发效率低
控制简单,消耗资源较少
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)
b)MyISAM
- 支持表级锁定、不支持事务、不支持外键
不支持行锁定,访问速度较快
- 独享表空间
表名.frm: 表结构
表名.myd: 表数据
表名.myi: 表索引
- 适用场合
查询操作较多
数据一致性要求较低(不支持事务)
没有外键约束
c)Memory(内存表):
- 表结构存储在磁盘上(硬盘),数据存储在内存
访问速度很快
- 文件:表名.frm 存储表结构
- 服务(或机器)重启后,表中的数据丢失
- 适用场合:
数据量小、访问速度要求极快、访问频率高、
数据丢失后不会造成影响
- 示例:
1:创建memory引擎的表
create table t2(id int)engine=memory;
2:插入数据并查询
3:重启服务,再查询,数据消失
sudo /etc/init.d/mysql restart
3. E-R模型:实体关系模型
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 #根据账户查询,最多返回一个Acct对象 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 1-查询所有 4-新增账户 13 2-根据用户查询 5-修改账户 14 3-根据用户名查询 6-删除账户 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)#执行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