python全栈开发第八天（MySQL数据库，数据备份，权限管理，索引，视图，触发器，事务的基本操作，使用python连接数据库，ORM，关系型数据库三范式 ，数据库优化 ）

数据备份

先退出mysql 环境
数据的导出:
    备份表：
    格式 mysqldump -uroot -p 库名 表名>备份文件地址\表名.sql
    备份库：
     mysqldump -uroot -p 库名>备份文件地址\备份文件名.sql

数据的导入：
    mysql -uroot -p 库名<备份文件地址\备份文件名.sql

   注：表可以直接导入，
        库要先创建一个空的库，在将数据导入。

权限管理

用户：连接的账户
权限：语序用户操作的范围
组：权限的集合

1、修改密码
        mysql 有一个默认的mysql 库有一个user表，这个表中存放的就是咱们用户信息
        切换到mysql库中，执行 update user set authentication_string=pasword(‘1234’) where User=‘root’ and host=‘localhost’;
        重新启动服务
        然后再去连接数据库 此时会发现不输入密码连接失败，需要使用密码登录

	如果忘记密码怎么办
	
      1.修改配置文件 在my.cnf 配置文件中的mysqld 最后一行添加 
      skip-grant-tables 跳过权限认证
      2.重启服务
      		不用输入密码可以直接登录到数据库
      		执行修改密码操作
      3.把配置文件修改回来
      4.重启服务 使用新密码登录即可
		

2、创建用户
        格式：create user ‘用户名’@'ip地址’identified by ‘密码’

1.创建用户名为lisi 任何位置都可以登录
create user 'lisi'@'%' identified by '12345'；

2.创建用户wangwu 只能在本地登录的用户
create user 'wangwu'@'localhost'identified by '12345';

3.创建用户wq只能在ip地址10.10.107.85 的设备上连接数据库
create user 'wq'@'10.10.103.85' identified by '123456';



window:
     ipconfig 查看ip

3、给用户授权允许远程连接
        格式：grant 权限 on 库名.表名 to 用户名@‘地址’

1.给lisi 添加  所有库所有表的 查询权限
grant  select on *.* to lisi @'%';

2.给远程连接的zhaoliu 用户 添加 demo1 数据库下所有表的权限
grant all on demo1.* to 'zhaoliu'@'%'

4、删除用户
        格式：drop user ‘用户名’@‘ip地址’

索引

一种高效获取数据的存储结构，类似于子字典的目录
(1:0f000001)
为什么要使用索引：可以提升查询数据的效率 减少io的操作

创建索引时，数据库会额外的创建一张表，来维护索引，会占用磁盘 的资源，一般索引的空间比数据大

索引一般支持的数据结构，b+tree b-tree 哈希

b+tree的基本结构

B+tree的特点：
有三层 分别是 根节点，子节点和叶子节点
对称
根节点不存储数据，数据存储在叶子节点
叶子节点同时保存了写一个数据节点的地址

1、索引的优缺点
        提升数据的查询速度，减少io操作
        缺点维护成本高，会降低其他操作的执行速度

添加索引可以增加查询的速度，但不是索引越多越好。
添加索引一般是给经常用作where 条件的字段添加
最左原则：Select * from user where username=’zhansgan’ and age=18;

2、索引分类
普通索引：index 可以为空 可以重复 当我们使用物理外键时就默认给该字段添加了普通索引
唯一索引：unique 可以为空 不可以重复
主键索引：primary key 不能为空且唯一
多列索引：index() 将多个字段绑定到一起添加一个索引

3、添加索引
格式：alter table 表名 add 索引类型 索引名(字段名);

	注：如果不添加索引名，默认的索引名以字段名命名
	
		删除主键索引的格式：
			删除主键索引要先将主建索引的自增属性删除 然后才能删除主键索引
			Alter table 表名 drop primary key;

			删除主键索引和删除其他索引方式不一样

			添加外键：alter table 表名 add foreign key(当前表外键字段) references 关联的表名(关联的字段);

4、查看索引
格式：show index from 表名;

5、删除索引
格式;drop index 索引名 on 表名;

视图

view

1、定义：当查询的复杂度很高时，指多字段，多关系查询，会导致sql语句混乱
是一张虚拟的表，有字段和数据，只存放查询语句
视图有字段有行，但是视图基于源表

2、特点：
            简单：视图构建了一个虚拟的表，表里的数据是来源于复杂的查询语句，我们将复杂的查询语句存入视图，使用是直接调用视图
            安全：数据据有对库和表的权限管理，但是没有对字段权限，可以通过视图来实现权限的功能
            数据的独立性：视图基于源表，当源表的结构发生变化时，不会对视图产生影响

使用视图时，一般存的都是复杂的查询，如果存的是简单的查询，在使用视图时，会作为复杂查询来去执行，会降低查询效率
会增加数据库的维护和管理成本，会对数据迁移造成很大影响

3、创建视图
            CREATE VIEW 视图名 AS(查询语句);

4、查看视图
            SHOW TABLE STATUS WHERE COMMENT=’view’;

5、使用视图
            SELECT * FROM 视图名;

6、删除视图
            DROP VIEW 视图名;

触发器

trigger

由谁触发：触发器是一个特殊的存储过程，不需要手动触发
什么时候会触发：当我们在做 添加 删除 修改操作时会自动触发触发器
修改数据的默认结束符 \d ||

1.创建触发器

            创建完触发器记得把结束符改回来
CREATE TRIGGER 触发器名字 触发时机 触发事件
ON 表名
FOR EACH ROW
BEGIN
触发器要执行的sql语句
EDN;

	给user表创建一个触发器，当向user表中添加数据时 给userinor也添加一条数据
CREATE TRIGGER add_user AFTER INSERT ON user FOR EACH ROW
BEGIN
INSERT INTO userinfo(name) values(‘沾士干’);
END;

数据备份 当我们删除user表中的数据时 将删除的数据备份的 back_user表中
create trigger back_data before delete on user for each row
begin
insert into back_user values(old.id,old.name,old.age,old.gender,old.phone,old.address);
end

注：for each row 是多次执行的意思，如user添加多个数据，触发器会执行多次给userinor添加数据

2.使用触发器

3.查看触发器
Select * from information_schema.trigger;

4.删除触发器
Drop trigger 触发器名字

数据备份 当我们删除user表中的数据时 将删除的数据备份的 back_user表中

事务的基本操作

begin 开启一个事物
commit 提交事物
rollback 回滚

是针对数据的

使用python连接数据库

pymysql：是一个第三方的模块，并且要求你的python版本为3.5以上
Python2中使用的是 mysqldb

1.安装： pip install pymysql（在cmd中的数据库bin目录下中执行）

2.使用：
            1.连接数据库
            2.创建游标
            3.定义sql语句
            4.执行sql语句
            5.关闭连接
获取返回内容的方法
fetchone() 获取一条数据
Fetchall() 获取返回的所有的数据
Rowcount 属性 获取操作影响的行数

第一种方法

# 导包
import pymysql

# 1.连接数据库
db=pymysql.connect(host='localhost',user='root',password='123',database='demo',cursorclass=pymysql.cursors.DictCursor)
# 2.创建游标对象
cursor=db.cursor()

# 3.定义sql语句
# sql='select version()'
# 定义查询的sql语句
# sql='select * from user'
# 定义添加数据数据
# sql = 'insert into user(id,name) values(25,"明明")'
# 定义更新数据
# sql = 'update user set age=16 where name="明明"'
# 删除数据
# sql = "delete from user where name='靠脸'"

# 4.执行sql语句
cursor.execute(sql)
# 获取返回的结果
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchall())
print(cursor.rowcount)

# 如果操作对数据库中的数据产生了影响必须执行提交(即查询不需要提交，增删改都需要提交不然数据库里的数据不会改变)
# db.commit()

# 5.断开连接
cursor.close()
db.close()




面向对象

import pymysql

class MyDB:
    def __init__(self,h='localhost',u='root',p=None,db=None):
        # 连接数据库 创建游标对象
        self.db=pymysql.connect(host=h,user=u,password=p,database=db,cursorclass = pymysql.cursors.DictCursor)
        self.cursor=self.db.cursor()

    # 做查询
    def query(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    # 做添加 删除 修改
    def change(self,sql):
        self.cursor.execute(sql)
        self.db.commit()
        return self.cursor.rowcount

    # 析构函数
    def __del__(self):
        # 关闭数据库连接
        self.cursor.close()
        self.db.close()

# 测试用的，只支持当前里面用，别的地方导入pysql2这个模块下面不会执行

if __name__ == '__main__':
    #  实例化对象
    database = MyDB(p='123',db='demo')
    res = database.query('select * from user')
    print(res)




from pysql2 import MyDB

# 实例化数据库对象
database = MyDB(h='localhost',u='root',p='123',db='demo')
#  插入数据库
sql = 'insert into user(id,name) values(5,"小白")'
print(database.change(sql))
# 查询数据
sql= 'select * from user'
print(database.query(sql))

ORM：（Object Relational Mapping）对象关系映射

ORM是一个操作数据库的框架
ORM会将python的代码翻译成对应数据库的sql语句
ORM会将数据库中的表映射成 python中的类
            一张表就是一个类
            将表中的字段映射成类的属性

优点：
降低程序和数据库之间的耦合
所以我们在使用ORM 时，可以不关心你用的是什么数据库，只关心我们的业务逻辑，即使开发人员不会sql语句 也能和数据库进行交互
即使开发人员不会sql语句 也能和数据库进行交互

缺点：
降低查询速度
ORM 生成的sql不是最优的sql语句 执行效率会比较低

python 中有一个 sqlalchemy,仿照的是Django的框架的orm
flask 有一个orm 插件 flask-sqlalchemy

安装：pip install sqlalchemy

使用：
            1.导包
            2.创建连接
            3.声明一个基类
            4.创建类 数据模型
            5.做操作

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
# 类似于pymysql 中的游标
from sqlalchemy.orm import sessionmaker

# 1.创建连接
# 数据库类型+数据库操作的包：//用户名:密码@主机地址/你要操作的数据库
# 安装mysqlclient   在sqlalchemy是不需要+pymysql 即  数据库类型：//用户名:密码@主机地址/你要操作的数据库
db=sqlalchemy.create_engine('mysql+pymysql://root:123@localhost/sqlorm')

# 2.创建基类
base = declarative_base(db)

# 3.创建类 必须继承基类  创建模型
class User(base):
   #表名
    __tablename__='user'
    id=sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String(32))#varchar(32)
    age = sqlalchemy.Column(sqlalchemy.Integer)

class Userinfo(base):
    __tablename__='userinfo'
    id=sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
    phone=sqlalchemy.Column(sqlalchemy.String(32))

class Shop(base):
    __tablename__='shop'
    id=sqlalchemy.Column(sqlalchemy.Integer,primary_key=True)
    name=sqlalchemy.Column(sqlalchemy.String(32))


if __name__ == '__main__':
    #执行数据库迁移 创建表
    base.metadata.create_all(db)
    #绑定一个实例
    s=sessionmaker(bind=db)
    #创建会话对象 类似于游标
    session=s()

    #添加
    # 添加单个数据
    # user = User(name='hello',age=17)
    # session.add(user)
    # session.commit()
    #添加多条数据
    # session.add_all([
    #     User(name='world',age=1),
    #     User(name='python',age=28),
    #     User(name='PHP',age=24),
    # ])
    # session.commit()


    # 查询
    #查询所有的数据 返回一个列表
    # res=session.query(User).all()
    # for i in res:
    #     print(i.name,i.age)
    #通过主键查询一条数据 返回一个对象
    res=session.query(User).get(2)
    print(res.name,res.age)
    #条件查询返回的是一个列表
    res=session.query(User).filter_by(name='hello').all()
    print(res)
    res=session.query(User).filter(User.name=='hello').all()
    print(res)


    #修改数据
    res=session.query(User).get(1)
    print(res.name)
    res.name='HELLO'
    session.commit()


    #删除数据
    # res=session.query(User).get(1)
    # session.delete(res)
    # session.commit()

关系型数据库三范式

第一范式：字段要具有原子性，不可以继续拆分，一般根据实际需求来去决定

id	name	age	address
1	张三	18	北京市昌平区回龙镇

id	name	age	sheng	shi

第二范式：建立在第一范式基础上，每一列数据必须被唯一区分，依赖于主键，每一个主键至少要有一个主键

第三范式：建立在第二范式的基础上，一般应用于设计多表关系中，要求一个数据表中不包含已在其他表中已包含的非主键字段，因为会出现冗余，表的信息如果能被推导出来就不应该单独设计一个字段来储存，可以使用外键来关联，而不是将另一张表中的非主键属性直接写在当前表中

id	name	price	Type_id	shi
1	超短裙	200	1	女装
2	雪纺衫	200	1	女装

id	tname	描述
1	女装
2	男装

反三范式：允许部分字段冗余

三范式：只是我们设计表时的一个参考规则，实际表的设计要根据实际业务来设计

数据库优化

1、库和表结构优化
            分库分表： 当单个库或者表中的数据量大时 数据库的性能会变慢

                        垂直拆分
                              垂直拆分表：当一个表中的数据量比较大字段比较多时，创建一个附属表，将表中不常用的字段存入附属表，通过创建外检进行关联
垂直拆分库；根绝不同的业务需求，将不同的表放入不同的库中，一般会放到多个服务器上

                       水平拆分
                             水平分库分表：单表数据量太大 将数据水平拆分成多个表，多个表组合在一起才能组成一个完成的数据；将拆分的表放到不同的库中

水平拆分面临的问题：
主键如何保证唯一性
《1》制定每张表的id取值范围
《2》通过时间或者地理位置
《3》通过趋势递增 雪花算法
水平分库 会面临 多表查询会受到影响 事物也会受到影响
目前没有人能解决这些问题，我们可以使用开源的框架产品来解决
但是不同的开源产品，所解决的问题也不相同，所以根据自己的需求来去选择

2、结构优化
主从复制(读写分离) 添加缓存 一般使用非关系数据库做为缓存数据库 将数据存到内存中

3、sql 语句优化
允许部分字段冗余，使用逻辑外键避免使用物理外键
添加索引：给查询频繁的条件添加索引，使用索引最左原则
查询时 select 后面不使用*
减少数据库的查询的次数
sql关键字尽量大写
使用关联查询替代嵌套子查询
使用where条件过滤 避免全表查询
Update修改时，避免修改索引字段所在的列
避免修改where后面字段