7 Python高级 MySQL

数据库 就是特殊的文件：

• 持久化存储
• 读写速度极高
• 保证数据的有效性
• 对程序支持行非常好 容易扩展

 当前主要是用两种类型的数据库：关系型和非关系性数据库。

关系型数据库RDBMS，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来池里数据库中的数据。

关系型数据库：

• oracle：大型项目中使用，银行 电信等项目
• mysql：web时代使用最广泛的关系性数据库
• ms sql server：微软的项目中使用
• sqlite：轻量级数据库 主要应用在移动平台

SQL

结构化查询语言 是一种用来操作RDBMS的数据库语言 当前关系型数据库丢支持SQL语言操作 。

SQL语句主要分：

DQL：数据查询语言 select

DML：数据操作语言 对数据增删改

DDL：数据定义语言 进行数据库表的管理 create drop

......

数据完整性

• 一个数据库就是一个完整的数据单元 可以包含多张表 数据被存储在表中。
• 在表中为了更加准确的存储数据 保证数据的正确有效 可以在创建表的时候 为表添加一些强制性的验证 包括数据字段的类型 约束。

数据类型

使用数据类型原则：够用就行 尽量使用取值范围小的 而不是用大的 这样可以更多的节省存储空间。

常用数据类型：

• 整数：int bit
• 小数：decimal
• 字符串：varchar char
• 日期时间：data time datatime
• 枚举类型：enum （就是东西给你列好 只能从这些之中选择）

对于图片 视频等 不存储在数据库中 而是上传到某个服务器上 然后在表中存储这个文件的报讯路径。

约束：

• 主键primary key：物理上存储的数据
• 非空not null：字段不允许填空格
• 惟一unique：此字段的值不允许重复
• 默认defalut：不填写此值会使用默认值 
• 外键foreign key（存储的别的表的主键）：对关系字段进行约束 当为关系字段填写值时 会到关联的表中查询此值是否存在 如果存在则填写成功 不存在则填写失败并抛出异常。

 数据库三范式

第一范式1NF：强调是列的原子性 即列不能再分成其他列。

第二范式：首先是1NF 另外包含两部分内容 一是表必须有一个主键 二是没有包含在主键中的列必须完全依赖于主键 而不能只依赖于主键的一部分。

第三范式：首先是2NF 另外非主键必须直接依赖于主键 不能存在传递连接 即不能存在：非主键列A依赖于非主键列B 非主键列B依赖于主键的情况。

 E-R模型：

E表示实体 设计实体就像定义一个类一样 指定从哪些方面描述对象 一个实体转换为数据库中的一个表

R表示关系 关系描述两个实体之间的对应规则 关系的类型包括一对一 一对多 多对多

关系也是一种数据 需要通过一个字段存储在表中

实体A对实体B为一对一 则在表A或者表B中创建一个字段 存储另一个表的主键值。

实体A对实体B为多对多 就建一张新表 这个表只有两个字段 一个用于存储A的主键值 一个存储B的主键值。

数据库练习：

创建数据库：

create table goods(\
    -> id int unsigned primary key auto_increment not null,\
    -> name varchar(150) not null,\
    -> cate_name varchar(40) not null,\
    -> brand_name varchar(40) not null,\
    -> price decimal(10,3) not null default 0,\
    -> is_show bit not null default 1,\
    -> is_saleoff bit not  null default 0);

sql语句：

查询每种类型中最贵的电脑信息：

select * 
from(select cate_name as c,max(price) as m from goods group by cate_name) as ca
join goods g
on ca.c=g.cate_name and ca.m=g.price;

将上面的表拆分成多表：

创建商品分类表：

create table if not exists goods_cates(\
    -> id int unsigned primary key auto_increment,\
    -> name varchar(40) not null);

将goods表中的商品种类写入goods_cates(不用写values）：

insert into goods_cates(name) select cate_name from goods group by cate_name;

同步两表的数据(将两个表关联起来）：

update goods as g\
    -> join goods_cates as go\
    -> on g.cate_name = go.name\
    -> set g.cate_name = go.id;

修改cate_name类型和goods_cates中的一样：

alter table goods change cate_name cate_id int unsigned not null;

设置外键  让两个表关联起来：

alter table goods add foreign key(cate_id) references goods_cates(id);

取消外键约束：

先获取外键约束名称：show crete table goods;

删除：alter table goods drop foreign key 外键名称;

实际开发中 很少用到外键约束 会降低更新的效率。

Python操作mysql数据库：

查询数据过程：

from pymysql import *
def main():
    #创建connection连接
    conn = connect(host='localhost',port=3306,user='root',password='25257758Mn',database='jing_dong',charset='utf8')
    #获得cursor游标对象
    cls = conn.cursor()
    #执行select语句 并返回受影响的行数 查询一条数据
    count = cls.execute('select id,name from goods where id>4')
    #打印受影响的行数
    print('查询到%d条数'% count)

    for i in range(count):
        #获得查询结果
        result= cls.fetchone()#逐条取
        #fetchmany和fetchall取多条
        print(result)
    #关闭cursor对象
    cls.close()
    conn.close()

if __name__=='__main__':
    main()

根据要求查询数据：

from pymysql import *

class JD(object):
    def __init__(self):
        # 创建connection连接
        self.conn = connect(host='localhost', port=3306, user='root', password='25257758Mn', database='jing_dong',
                       charset='utf8')
        # 获得cursor游标对象
        self.cls = self.conn.cursor()

    def __del__(self):#最后肯定会被调用
        # 关闭cursor对象
        self.cls.close()
        self.conn.close()
    def execute_sql(self,sql):
        self.cls.execute(sql)
        for temp in self.cls.fetchall():
            print(temp)
    def show_all_item(self):
         sql = 'select * from goods;'
         self.execute_sql(sql)
    def show_cates(self):
        sql = 'select name from goods_cates;'
        self.execute_sql(sql)
    def show_brand_name(self):
        sql = 'select brand_name from goods group by brand_name;'
        self.execute_sql(sql)
    def add_good(self):
        item_name = input("输入新商品分类：")
        sql = 'insert into goods_cates(name) values("%s");' % item_name
        self.cls.execute(sql)
        self.conn.commit()
    def find_good(self):
        find_name = input('输入查询商品的名字：')
        #sql = """select * from goods where name = '%s'""" % find_name
        #sql注入 如果输入' or 1=1 or '1 所有数据就会都查询出来
        #print("----->%s<------" % sql)
        #self.execute_sql(sql)

        sql = 'select * from goods where name=%s'
        params = [find_name]
        self.cls.execute(sql,params)#execute自己填充%s
        print(self.cls.fetchall())

    @staticmethod#静态方法
    def print_menu():
        print('---京东---')
        print('1 所有商品')
        print('2 所有商品分类')
        print('3 所有商品品牌分类')
        print('4 添加商品分类')
        print('5 根据名字查询商品')
        return input('输入对应功能序号：')
    def run(self):
        while True:
            num = self.print_menu()
            if num == '1':
                #查询所有商品
                self.show_all_item()
            elif num == '2':
                #查询分类
                self.show_cates()
            elif num =='3':
                #查询品牌分类
                self.show_brand_name()
            elif num == '4':
                self.add_good()
            elif num == '5':
                self.find_good()
            else:
                print('输入有错误 重新输入')

def main():
    #创建一个京东商城对象
    jd = JD()
    #调用run方法让其运行
    jd.run()

if __name__ == '__main__':
    main()

python操作sql：增删改

from pymysql import *
def main():
    #创建connection连接
    conn = connect(host='localhost',port=3306,user='root',password='25257758Mn',database='jing_dong',charset='utf8')
    #获得cursor游标对象
    cls = conn.cursor()
    #执行insert语句 并返回受影响的行数 查询一条数据
    count = cls.execute('insert into goods_cates(name) values ("光盘");')
    #打印受影响的行数
    print(count)
    #提交之前的操作 如果之前已经执行多次的execute 那么就都进行提交
    conn.commit()
    #关闭cursor对象
    cls.close()
    conn.close()

if __name__=='__main__':
    main()

防止sql注入：（之前例子包含有sql注入的问题）

SQL注入，也称SQL注入或SQL注码，是发生于应用程序与数据库层的安全漏洞。简而言之，是在输入的字符串之中注入SQL指令，在设计不良的程序当中忽略了字符检查，那么这些注入进去的恶意指令就会被数据库服务器误认为是正常的SQL指令而执行，因此遭到破坏或是入侵。

视图：就是虚拟的表 主要用来查询数据的时候来用的

show tables可以查看表以及视图

create view 视图名称 as select语句-----创建视图

 drop view 视图名称---- 删除视图

事务：一个或者一组sql语句组成一个执行单元 这个执行单元要么全部执行 要么都不执行。

事务acid属性：原子性 一致性 隔离性 持久性

set autocommit = 0；

start transaction；开启事务

begin；也是开启事务

commit；提交事务

rollback；回滚

索引：

是一种特殊的文件 他们包含着对数据表里所有记录的引用指针。通俗的说数据库索引好比一本书前面的目录 能加快数据库的查询速度。

索引的使用：

查看索引：show index from 表名

创建索引：

• 如果指定字段是字符串 需要指定长度 建议长度与定义字段时的长度一样
• 字段类型如果不是字符串 可以不填长度部分

create index 索引名称 on 表名（字段名称（长度））

删除索引：

drop index 索引名称 on 表名；

但是建立太多索引将会影响更新和插入的速度 因为需要同样更新每个索引文件；建立索引会占用磁盘空间。

mysql主从服务器：

主从同步使得数据可以从一个数据库服务器复制到其他服务器上 在复制数据时 一个服务器充当主服务器 其余服务器充当从服务器  因为复制时异步进行的 所以从服务器不需要一直连接着主服务器。

主从同步的好处：

• 通过增加从服务器来提高数据库的性能 在主服务器上执行写入和更新 从服务器向外提供读功能 可以动态调整从服务器的数量 从而调整整个数据库的性能
• 提高数据安全 数据已复制到从服务器 可以在从服务器上备份而不影响主服务器的相应数据
• 在主服务器上生成实时数据 而在从服务器上分析这些数据 从而提高从服务器的性能。

（备份 恢复数据）：

• 备份： mysqldump -uroot -p 数据库名 > 保存的文件名（如python.sql）
• 恢复：如果不小心删除了原始数据
• 连接mysql 创建新的数据库
• mysql -uroot -p 新数据库名 < python.sql