MySQL 存储引擎、锁、调优、失误与事务回滚、与python交互、orm

python基础

浏览数:331

2019-9-2

1、存储引擎(处理表的处理器)

1、基本操作
  1、查看所有存储引擎
    mysql> show engines;
  2、查看已有表的存储引擎
    mysql> show create table 表名;
  3、创建表指定存储引擎
    create table 表名(…)engine=myisam;
  4、已有表修改存储引擎
    alter table 表名 engine=innodb;

2、锁

1、目的 :解决客户端并发访问的冲突问题
2、锁分类
  1、锁类型
    1、读锁(共享锁)
      select :加读锁之后别人不能更改表记录,但可以进行查询
    2、写锁(互斥锁、排他锁)
      insert、delete、update
    加写锁之后别人不能查、不能改
  2、锁粒度
    1、表级锁 :myisam
    2、行级锁 :innodb
3、常用存储引擎特点
  1、InnoDB特点
    1、共享表空间
      表名.frm :表结构和索引文件
      表名.ibd :表记录
    2、支持行级锁
    3、支持外键、事务操作
  2、MyISAM特点
    1、独享表空间
      表名.frm :表结构
      表名.myd :表记录 mydata
      表名.myi :索引文件 myindex
    2、支持表级锁
4、如何决定使用哪个存储引擎
  1、执行查操作多的表用 MyISAM(使用InnoDB浪费资源)
  2、执行写操作多的表用 InnoDB

3、MySQL调优

1、选择合适的存储引擎
  1、读操作多 :MyISAM
  2、写操作多 :InnoDB
2、创建索引
  在 select、where、order by常涉及到的字段建立索引
3、SQL语句的优化
  1、where子句中不使用 != ,否则放弃索引全表扫描
  2、尽量避免 NULL 值判断,否则放弃索引全表扫描
    优化前 :
      select number from t1 where number is null;
    优化后 :
      在number列上设置默认值0,确保number列无NULL值
    select number from t1 where number=0;
  3、尽量避免 or 连接条件,否则放弃索引全表扫描
    优化前 :
      select id from t1 where id=10 or id=20 or id=30;
    优化后:
      select id from t1 where id=10
      union all
      select id from t1 where id=20
      union all
      select id from t1 where id=30;
  4、模糊查询尽量避免使用前置 % ,否则全表扫描
    select name from t1 where name like “%c%”;
  5、尽量避免使用 in 和 not in,否则全表扫描
    select id from t1 where id in(1,2,3,4);
    select id from t1 where id between 1 and 4;
  6、尽量避免使用 select * …;用具体字段代替 * ,不要返回用不到的任何字段

”’SQL语句参数化”’


 1 '''SQL语句参数化'''
 2 
 3 import pymysql
 4 
 5 # 1.创建数据库连接对象
 6 db = pymysql.connect(host="localhost",user="root",
 7                 password="123456",database="db4",
 8                 charset="utf8")
 9 # 2.创建游标对象
10 cur = db.cursor()
11 
12 s_id = input("请输入省编号:") 
13 name = input("请输入省名称:")
14         
15 try:
16     sql_insert = "insert into sheng(s_id,s_name) \
17                   values(%s,%s);"
18     cur.execute(sql_insert,[s_id,name])# 列表传参
19     print("ok")
20     db.commit()
21 except Exception as e:
22     db.rollback()
23     print("Failed",e)
24 
25 cur.close()
26 db.close()

4、事务和事务回滚

1、定义 :一件事从开始发生到结束的整个过程
2、作用 :确保数据一致性
3、事务和事务回滚应用
  1、MySQL中sql命令会自动commit到数据库
    show variables like “autocommit”;
  2、事务应用
    1、开启事务
      mysql> begin;
      mysql> …一条或多条SQL语句
    ## 此时autocommit被禁用
    2、终止事务
      mysql> commit; | rollback;
    3、案例
      1、背景
        你 :建行卡
        你朋友 :工商卡
        你在建行自动取款机给你朋友的工商卡转账5000元
      2、建表
        表1、CCB
        create table CCB(
        name varchar(15),
        money decimal(20,2)
        );
        insert into CCB values(“只手遮天”,10000);

        表2、ICBC
        create table ICBC(
        name varchar(15),
        money decimal(20,2)
        );
        insert into ICBC values(“为所欲为”,1000);
      3、开始转账
        mysql> begin;
        mysql> update CCB set money=money-5000 where name=”只手遮天”;
        mysql> update ICBC set money=money+5000 where name=”为所欲为”;
        mysql> commit;
        #### 转账成功 ####


 1 import pymysql
 2 
 3 # 1.创建数据库连接对象
 4 db = pymysql.connect(host="localhost",user="root",
 5                 password="123456",database="db4",
 6                 charset="utf8")
 7 # 2.创建游标对象
 8 cur = db.cursor()
 9 # 3.执行SQL语句
10 # 在sheng表中插入1条记录,云南省
11 try:
12     sql_insert = "insert into sheng values\
13                   (19,300002,'西藏');"
14     cur.execute(sql_insert)
15     # 把云南省的 id 号改为 666
16     sql_update = "update sheng set id=666 where id=17;"
17     cur.execute(sql_update)
18     # 把台湾省在 sheng 表中删除
19     sql_delete = "delete from sheng where s_name='台湾省';"
20     cur.execute(sql_delete)
21     print("ok")
22     db.commit()
23 except Exception as e:
24     db.rollback()
25     print("出现错误,已回滚",e)
26 
27 
28 # 5.关闭游标对象
29 cur.close()
30 # 6.断开数据库连接
31 db.close()

5、与python交互

1、交互类型
  1、python3
    模块名 :pymysql
    安装:
    在线 :sudo pip3 install pymysql
    离线 :pymysql-0.7.11.tar.gz
    $ tar -zxvf pymyql-0.7.11.tar.gz
    $ cd pymysql-0.7.11
    $ sudo python3 setup.py install
  2、python2
    模块名 :MySQLdb
    安装 :sudo pip install mysql-python
2、pymysql使用流程
  1、建立数据库连接(db = pymysql.connect(…))
  2、创建游标对象(c = db.cursor())
  3、游标方法: c.execute(“insert ….”)
  4、提交到数据库 : db.commit()
  5、关闭游标对象 :c.close()
  6、断开数据库连接 :db.close()
3、connect对象
  1、db = pymysql.connect(参数列表)
    1、host :主机地址,本地 localhost
    2、port :端口号,默认3306
    3、user :用户名
    4、password :密码
    5、database :库
    6、charset :编码方式,推荐使用 utf8
  2、数据库连接对象(db)的方法
    1、db.close() 关闭连接
    2、db.commit() 提交到数据库执行
    3、db.rollback() 回滚
    4、cur = db.cursor() 返回游标对象,用于执行具体SQL命令
  3、游标对象(cur)的方法
    1、cur.execute(sql命令,[列表]) 执行SQL命令
    2、cur.close() 关闭游标对象
    3、cur.fetchone() 获取查询结果集的第一条数据
      (1,100001,”河北省”)
    4、cur.fetchmany(n) 获取n条
      ((记录1),(记录2))
    5、cur.fetchall() 获取所有记录
  错误:
    1、root@”localhost” denied,Using password:YES
    2、”localhostt”
    3、connect object has no attribute “rollbake”
    4、pymysql has no attribute “connect”


 1 import pymysql
 2 
 3 # 1.创建与数据库连接对象
 4 db = pymysql.connect(host="localhost",user="root",
 5               password="123456",database="db4",
 6               charset="utf8")
 7 # 2.利用db方法创建游标对象
 8 cur = db.cursor()
 9 
10 # 3.利用游标对象的execute()方法执行SQL命令
11 cur.execute("insert into sheng values\
12              (16,300000,'台湾省');")
13 
14 # 4.提交到数据库执行
15 db.commit()
16 print("ok")
17 # 5.关闭游标对象
18 cur.close()
19 # 6.断开数据库连接
20 db.close()

6、orm(Object Relation Mapping 对象关系映射)

1、定义
  把对象模型映射到MySQL数据库中
2、sqlalchemy安装:
  在线 :sudo pip3 install sqlalchemy
  离线 :
  $ tar -zxvf SQLAlchemy-1.2.10.tar.gz
  $ cd SQLAlchemy-1.2.10
  $ sudo python3 setup.py install
  验证:
  $ python3
  >>> import sqlalchemy
  >>>
3、示例
  class User(Base):
    __tablename__ = “t1” #声明要创建的表名
  id = Column(Integer,primary_key=True)
  name = Column(String(20))
  解释:
    一个类User –> 一张表 t1
    表中有两个字段 :id 和 name


1 select = "select password from user where\
2 username=%s;"
3 
4 print(select)


 1 from mysqlpython import Mysqlpython
 2 
 3 # 创建数据库连接对象
 4 sqlh = Mysqlpython("db4")
 5 
 6 # sql_update = "update sheng set s_name='辽宁省' \
 7 #               where s_name='云南省';"
 8 # sqlh.zhixing(sql_update)
 9 
10 sql_select = "select * from sheng where id=%s;"
11 data = sqlh.all(sql_select,[1])
12 print(data)

实例一:创建一张表


 1 # 连接数据库的模块
 2 from sqlalchemy import create_engine
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import Column,Integer,String
 5 
 6 engine = create_engine("mysql+pymysql://root:123456@localhost/db4",encoding="utf8")
 7 Base = declarative_base() # orm基类
 8 
 9 class User(Base): # 继承Base基类
10     __tablename__ = "t123"
11     id = Column(Integer,primary_key=True)
12     name = Column(String(20))
13     address = Column(String(40))
14 
15 Base.metadata.create_all(engine)


 1 from mysqlpython import Mysqlpython
 2 from hashlib import sha1
 3 
 4 uname = input("请输入用户名:")
 5 pwd = input("请输入密码:")
 6 # 用sha1给pwd加密
 7 
 8 s1 = sha1()  # 创建sha1加密对象
 9 s1.update(pwd.encode("utf8"))  # 指定编码
10 pwd2 = s1.hexdigest()  # 返回16进制加密结果
11 
12 sqlh = Mysqlpython("db4")
13 select = "select password from user where \
14           username=%s;"
15 result = sqlh.all(select,[uname])
16 # print(result)
17 # (('7c4a8d09ca3762af61e59520943dc26494f8941b',),)
18 
19 if len(result) == 0:
20     print("用户名不存在")
21 elif result[0][0] == pwd2:
22     print("登录成功")
23 else:
24     print("密码错误")

加密

 1 from pymysql import *
 2 
 3 class Mysqlpython:
 4     def __init__(self,database,
 5                  host="localhost",
 6                  user="root",
 7                  password="123456",
 8                  port=3306,
 9                  charset="utf8"):
10         self.host = host 
11         self.user =user 
12         self.password = password
13         self.port = port
14         self.charset = charset
15         self.database = database
16 
17     def open(self):
18         self.db = connect(host=self.host,
19                           user=self.user,
20                           port=self.port,
21                           database=self.database,
22                           password=self.password,
23                           charset=self.charset)
24         self.cur = self.db.cursor()
25 
26     def close(self):
27         self.cur.close()
28         self.db.close()
29 
30     def zhixing(self,sql,L=[]):    # pymysql.execute(sql)
31         try:
32             self.open()
33             self.cur.execute(sql,L)
34             self.db.commit()
35             print("ok")
36         except Exception as e:
37             self.db.rollback()
38             print("Failed",e)
39         self.close()
40 
41     def all(self,sql,L=[]):
42         try:
43             self.open()
44             self.cur.execute(sql,L)
45             result = self.cur.fetchall()
46             return result
47         except Exception as e:
48             print("Failed",e)
49         self.close()


 1 import pymysql
 2 
 3 # 1.创建数据库连接对象
 4 db = pymysql.connect(host="localhost",user="root",
 5                 password="123456",database="db4",
 6                 charset="utf8")
 7 # 2.创建游标对象
 8 cur = db.cursor()
 9 
10 try:
11     sql_select = "select * from sheng;"
12     cur.execute(sql_select)
13 
14     data1 = cur.fetchone()
15     print(data1)
16     print("*******************")
17 
18     data2 = cur.fetchmany(3)
19     for m in data2:
20         print(m)
21     print("*******************")
22 
23     data3 = cur.fetchall()
24     for m in data3:
25         print(m)
26     print("*******************")
27 
28     db.commit()
29 except Exception as e:
30     print(e)
31 
32 cur.close()
33 db.close()

 

作者:凌逆战