python开发day04MySQL 数据的备份 数据恢复 事务和事务回滚 存储引擎以及存储引擎特点 锁 MySQL调优 MySQL 与python交互

数据备份

方法	数据备份（mysqldump,在Linux终端中操作）
命令格式	mysqldump -u用户名 -p 源库名 > 路径/XXX.sql
源库名的表示方式	--all-databases   备份所有库 库名              备份单个库 -B 库1 库2 ...    备份多个库 库名 表1 表2 ...  备份指定表

示例：

1、备份所有库为 all.sql , /home/tarena/mydata/

      mysqldump -uroot -p --all-databases > all.sql

2、备份MOSHOU库中 sheng、city、xian三张表 MOSHOUscx.sql

      mysqldump -uroot -p MOSHOU sheng city xian > MOSHOUscx.sql

3、备份2个库

      mysqldump -uroot -p -B MOSHOU db4 > MSdb4.sql

 

数据恢复

方法	数据备份（mysqldump,在Linux终端中操作）
命令格式	mysql -u用户名 -p 目标库名 < xxx.sql
注意	1、如果要恢复的库不存在则必须先创建空库 2、恢复库时 原库中表中记录会被覆盖 新增的变不会删除

示例：

1、恢复MOSHOU库,MOSHOU.sql

      mysql -uroot -p MOSHOU < MOSHOU.sql

    2、恢复MOSHOU库,all.sql

      ## --one-database

      mysql -uroot -p --one-database MOSHOU < all.sql

    3、在MOSHOU库中：

      1、新建一张表 t888 (id)

      2、sheng表中新增一条记录 : 台湾

注意：

恢复库时，原库中表中记录会被覆盖，新增的变不会删除

 

事务和事务回滚

意义	一件事从开始发生到结束的整个过程
作用	确保数据一致性
事务应用	开启事务 Mysql > begin; ###autocommit 被禁用，SQL命令不会提交到数据库执行
	终止事务 Mysql > commit；| rollback；
注意	事务和事务回滚只针对表记录操作：增加、删除、修改有效，对建库建表无效

 

示例

 

   1、背景

      你 ：建行卡

      朋友 ：工商卡

      你在建行取款机给你朋友转5000元

    2、过程

      表1、CCB

        create table CCB(

name varchar(20),

money int

);

insert into CCB values("有钱人",10000);

      表2、ICBC

        create table ICBC(

name varchar(20),

money int

);

insert into ICBC values("没钱",0);

    3、开始转账

      mysql> begin;

      mysql> update CCB set money=money-5000 where name="有钱人";

      mysql> update ICBC set ;

      mysql> rollback;

 

 

MySQL存储引擎（处理表的处理器）

基本操作	1、查看所有存储引擎 Show engines；
	2、查看表存储引擎 Show create table 表名；
	3、指定存储引擎 Create table 表名（…）engine=innodb,character set utf8；
	4、修改表存储引擎 alter table 表名 engine=myisam；
	5、工作中常用 InnoDB MyISAM
修改表默认存储引擎	1、 sudo –i 2、 cd /etc/mysql/mysql.conf.d/ 3、 cp mysql.cnf mysqld.cnf.bak 4、 vi mysql.cnf [mysqld] default-storage-engine=myisam 5、/etc/init.d/mysql restart

 

 

锁

加锁的目的	解决客服端的并发访问的冲突问题
分类	1、锁类型	读锁（select）共享锁： 加读锁后其他用户只能查询，不能修改
		写锁（增删改）互斥锁、排他锁： 其他用户不能做任何操作
	2、锁粒度	表级锁：加读锁或者写锁
		行级锁：加读锁或者写锁

 

 

 

 

 

 

存储引擎特点

MyISAM特点	1、 独享表空间 表名.frm 表结构 表名.frm 表记录 表名.frm 索引文件
	2、支持表级锁
InnoDB特点	1、 共享表空间 表名.frm 表结构和索引信息 表名.frm 表记录
	2、 支持行级锁 不会锁住整张表
memory （*了解）	表结构存储在硬盘里，表记录存储在内存中 表名.frm 服务重启后表结构还在，表记录都消失
如何决定表使用什么存储引擎	1、 主要用来查询的表用MyISAM 2、 写操作多的表用InnoDB

 

 

MySQL调优

方式	1、 选择合适的存储引擎
	2、创建索引 在SELECT EHERE ORDER BY 常涉及的字段建立索引
	3、SQL语句优化！！！ （1）Where 条件判断尽量不使用 ！= ，否则放弃索引全表扫描 （2）尽量使用空值null判断，否则放弃索引全表扫描 优化前：select id from t1 where id is null； 优化后：在id 字段上设置默认值0，确保id字段没有null值        Select id from t1 where id=0 （3）尽量避免用or 来连接条件，否则放弃索引全表扫描      优化前：select id from t1 where id=10 or id=20；      优化后：            Select id from t1 where id=20            Union all            Select id from t1 where id=10 （4）模糊查询尽量避免使用前置%，否则放弃索引全表扫描 Select name from t1 where name like “%c%”; （5）尽量避免使用in 和 not in，否则放弃索引全表扫描 优化前：select id from t1 where id in（1,2,3,4）； 优化后：…where id between 1 and 4； （6）尽量使用*，用具体的字段代替*，不要返回当前用不到的任何字段

MySQL与python 交互

交互类型

Python3 ：pymysql $ sudo pip3 install pymysql

Python2 : MySQLdb $ sudo pip insta;; mysql-python

 

Connect对象

1、 创建与数据库连接的对象（调用connect（）方法）

Conn = pymysql.connect（）                                                 

参数列表：

1、 host ： 主机地址

2、 port ： 端口3306

3、 db  ： 数据库名

4、 passwd： 密码

5、 charset：编码方式，推荐使用utf8

6、 user ：用户名

示例：

Conn =

 pymysql.connect（host=”localhost”,user=”root”,passwd=”123456”,db=”db4”,charset=”utf8”）

 

2、 连接对象（conn）的方法

1、 close（）关闭连接

2、 commit（）提交到数据库执行

3、 rollback（）回滚

4、 cursor（）创建游标对象，用于执行sql语句

3、 游标对象

1、 作用：执行sql 语句

 

2、示例：cur.cursor（）

      Cur.execute（”delete from sheng;”）

2、 常用方法

1、 execute（SQL命令）执行SQL命令

2、 fetchone（）获取结果集第一条记录

3、 fetchmany（n）获取n条记录

4、 fetchall（）获取所有记录

pymysql使用流程

 1、建立数据库连接 conn

      2、创建游标对象 cur = conn.cursor()

      3、cur.execute("...")

      4、提交：conn.commit()

      5、关闭游标：cur.close()

      6、断开连接: conn.close()

示例

在db3库中的sheng表添加内容“新疆”

pymysql 使用流程

 import pymysql

 

#1.创建数据库链接对象

conn = pymysql.connect(host="localhost",

                user="root",passwd="123456",

                db="db3",charset="utf8")

#2.创建游标对象

cur = conn.cursor()

#3.利用游标对象的execute方法执行sql语句

cur.execute("insert into sheng values \

                (33,999999,'新疆');")

#４.提交到数据库执行

conn.commit()

#5.关闭游标

cur.close()

#6．关闭数据库链接

conn.close()

 

在db3库中sheng表删除修改1和2

import pymysql

 

db = pymysql.connect(host="localhost",

                user="root",passwd="123456",

                db="db3",charset="utf8")

cur = db.cursor()

 

try:

    sql_delete="delete from sheng where id=1;"

    cur.execute(sql_delete)

 

    sql_update="update sheng set id=200 where id=2;"

    cur.execute(sql_update)

 

    db.commit()

    print("ok")

except Exception as e:

    db.rollback()

    print("Failed",e)

 

cur.close()

db.close()

 

 

ER模型（Entry-Relationship）

定义	实体-关系模型，用于数据库设计
实体	矩形框
属性	椭圆形
关系	实体之间的关系 （1） 一对一关系（1:1） （2） 一对多关系（1：n） （3） 多对多关系（n:n）