使用pymysql模块（python3中），python2中为MySQLdb

使用pymysql流程类似用python操作文件：打开、读写、关闭

查询

from pymysql import *

def main():
    # 创建连接，connect是一个类
    conn = connect(host='127.0.0.1', port=3306, user='root', password = '', database='jing_dong', charset="utf8")

    # 获得cursor(游标)对象
    csl = conn.cursor()

    # 执行select语句，返回受影响的行数：查询一条数据
    count = csl.execute('select id, name from goods where id >= 4')

    #打印受影响的行数
    print("查询到%d条数据："%count)

    for i in range(count):
        #获取查询的结果，从游标里面
        result = csl.fetchone();    # fetchone每次取一条记录，取了一次之后，再取就是下一条
        #fetchmany(整数)：取出参数条数据，以元组的形式
        #fetchall()：取出所有的实际上
        # 打印查询的结果
        print(result)

    # 关闭cursor对象
    csl.close()
    # 必须先关闭游标，才能关闭连接
    conn.close()


if __name__ == "__main__":
    main()

改进：使用面向对象的思想

from pymysql import connect

class JD(object):
    def __init__(self):
        self.conn = connect(host="localhost", port=3306, user="root", password="", database="jing_dong", charset="utf8")
        self.csl = self.conn.cursor()
    def run(self):
        while(True):
            opt = self.print_menu()
            if opt == "1":
                self.show_all_items()
            elif opt == "2":
                self.show_cates()
            elif opt == "3":
                self.show_brands()
            else:
                print("输入有误，请重新输入...")

    @staticmethod
    def print_menu():
        print("京东商城".center(50, '-'))
        print("1.所有的商品")
        print("2.所有的商品分类")
        print("3.所有的商品品牌分类")
        opt = input("请输入您的操作：")
        return opt

    def show_brands(self):
        sql = "select c_name from goods_brands"
        self.execute_sql(sql)

    def execute_sql(self, sql):
        self.csl.execute(sql)
        for temp in self.csl.fetchall():
            print(temp)

    def show_all_items(self):
        sql="select * from goods"
        self.execute_sql(sql)

    def show_cates(self):
        sql = "select name from goods_cates"
        self.execute_sql(sql)

    def __del__(self):
        self.csl.close()
        self.conn.close()

def main():
    jd = JD()
    jd.run()


if __name__ == "__main__":
    main()

添加、修改与删除

1. 与查询时相同，把sql语句传给cursor执行execute即可
2. 修改数据时，虽然execute会使auto_increment增加，但是数据不会真正地添加到数据库中，如果要使数据真正地添加到数据库中，还必须执行连接.commit()
3. 如果execute了错误的操作，可以用连接.rollback()撤销更改（回滚），但是increment仍然是继续增加
4. 修改数据时，是通过游标.execute(sql语句)直接执行

sql注入

即：在查询数据时，通过特殊的语句，查询数据库中的全部信息

eg：
程序中的某查询代码为：(cursor为游标)

self.cursor.execute("select * from goods_brands where name='%s';"%input("请输入要查询的品牌："))

结果用户输入了：

'  or 1=1 or 1'

用户就可以获得该表中的所有数据

为什么会出现这种情况？

分析：用户输入中的第一个'匹配程序中s%前面的'，使之为空，即不满足但是用户输入中又添加了or语句，且保证其一定为真，用户输入中的最后一个'匹配程序中s%后面的一个'，所以在查询时，哪怕是普通用户也能够查询到表中的所有信息

且由于程序员的疏忽，sql注入几乎是不可避免的

在python中如何避免sql注入

在python的pymysql提供了一定机制避免sql注入

params = [find_name]    #将用户输入的要查询的内容放到列表中
count = cursor.execute("select * from goods where name=s%", params) #列表中有几个要查询的信息，第一个参数中就必须有几个s%。注意：s%前后没有引号

视图

通俗的讲，视图就是一条select语句执行后返回的结果。

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据，视图中的数据都是来自基本表（即所谓的：基本表数据发生了改变，视图也会跟着改变）

之所以使用视图，是为了保证数据库发生改变时，用户对数据库的操作语句不用改变（即应用程序不必改变），相当于隔离了用户与真正的数据库。

视图往往是为了查数据，而不是为了改数据（即：视图不可更新）

视图的创建

create view 视图的名称 as select语句;

即把后面的select语句的查询结果定义为一个视图

为了便于区分，视图的名字常常以v-开头

创建后，也可以使用show tables查看到视图的存在，然后在查询数据的时候，就可以把视图当作一张普通的表来使用

删除视图

drop view 视图的名称

视图的作用

1. 提高了重用性，就像一个函数
2. 对数据库重构，却不影响程序的运行
3. 提高了安全性能，可以对普通用户隐藏部分敏感信息
4. 让数据更加清晰

事务

所谓事务，它是一个操作序列，这些操作序列要么全做，要么全不做，是一个不可分隔的工作单位

事务的四大特性（简称ACID）

1. 原子性（Atomicity）：一个事务要么全做，要么全不做，不可分隔
2. 一致性（Consistency）：事务使数据库从一个一致状态到另一个一致状态（比如银行转账过程中，一个账户转出了多少钱，必然有一个账户转进了多少钱，而不会说一个账户转出了，另外一个账户却没有转进）
3. 隔离性（Isolation）：多个事务之间互不影响，相互隔离（通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的）
4. 持久性（Durability）：事务事务一旦被提交，其对数据库的修改是永久性的

用python操作数据库的时候，已经默认开启了事务（必须commit）

事务的流程：

1. 使用：start transaction或begin开启事务
2. 使用sql语句修改数据
3. 如果发现操作出错，可以使用rollback回滚
4. 操作完毕之后，使用commit提交事务

索引

索引是一种特殊的文件（innoDB数据表上的索引是表空间的一个组成部分），他们包含着对数据表里所有记录的引用指针

更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

ps：mysql中开启时间统计(单位为s)功能：

1. set profiling=1;
2. 执行sql语句
3. show profiles;

索引的使用

• 查看索引：show indes from 表名

• 创建索引：create index 索引名称 on 表名(字段名称(长度))

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

• 删除索引：drop index 索引名称 on 表名;

账户管理

在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud

MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种

• 服务实例级账号：启动了一个mysql，即为一个数据库实例，：如果某用户如root，拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库，连同这个数据库中的表
• 数据库级别账号：对特定数据库执行增删改查的所有操作
• 数据表级别账号：对特定表执行增删改查等所有操作
• 字段级别的权限：对某些表的特定字段进行操作
• 存储程序级别的账号：对存储程序进行增删改查的操作

账户的操作主要包括创建账户、删除账户、修改密码、授权权限等

注意：

• 进行账户，需要使用root账户登录，这个账户拥有最高的实例级权限
• 通常都使用数据库级操作权限

查看所有用户

所有用户及权限信息都存储在mysql数据库的user表中

查看user表的结构：desc user;

主要字段：

• host表示允许访问的主机，%表示任意一台主机，localhost表示只能本地登录
• user表示用户名
• authentication_string表示密码，为加密后的值

查看所有用户：selectt host,user,authentication_string from user;

创建账户、授权

• 需要使用实例级账号登录后操作，eg：root
• 常用权限主要包括：create alter drop insert update delete select
• 如果分配所有权限，可以使用all privilleges

流程：

1. 以实例级账户登录
2. 授权

grant 权限列表 on 数据库.* to '用户名'@'访问主机' identified by '密码';

定义权限的时候，必须到表，.*表示该数据库下的所有表

修改权限

grant 权限列表 on 数据库.* to '用户名'@'访问主机' with grant option;

修改了权限之后，必须用flush privileges刷新权限

修改密码

使用password对密码进行加密处理

update user set authentication_string=password('新密码') where user='用户名';

远程登录数据库

略：危险慎用

但是可以使用ssh远程登录进行操作

删除用户

drop user '用户名'@'主机';

MySQL的主（master）从（slave）

为了保证数据的安全性，数据往往会在至少另外一台服务器上有备份

主从的作用：

1. 备份
2. 读写分离、负载均衡，给用户提供更快速的响应

配置主从同步的基本步骤

• 在主服务器上，必须开启二进制日志机制和配置一个独立的id
• 在每一台从服务器上，配置一个唯一的id，创建一个用来专门复制主服务器数据的账号
• 在开始复制进程前，在主服务器上记录二进制文件的位置信息
• 如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导入数据库，或者直接复制数据文件）
• 配置从服务器要连接的主服务器的ip地址和登录授权，二进制日志文件名和位置

数据的备份

-- 如果是备份所有数据库，数据库名可以写为：--all-databases --lock-all-tables
mysqldump -uroot -p 数据库名 > 备份的文件名.sql;

数据的恢复

创建一个新的数据库

-- <从定向输入
mysql -uroot 新数据库名 < 备份文件名.sql;

配置主从的具体流程略