Python的MySQL操作

Python的DB-API,为大多数的数据库实现了接口,使用它连接各数据库后,就可以用相同的方式操作各数据库。

Python DB-API使用流程:

• 引入API模块。
• 获取与数据库的连接。
• 执行SQL语句和存储过程。
• 关闭数据库连接。

一、安装MySQL客户端　　

　　MySQLdb 是用于Python链接Mysql数据库的接口，它实现了 Python 数据库 API 规范 V2.0，基于 MySQL C API 上建立的。

　　如果是windows系统：登录https://pypi.python.org/pypi/MySQL-python/1.2.5找到.exe结尾的包，下载安装就好了，然后在cmd中执行：

　　

　　如果结果如上图所示，则说明安装成功了。

　　如果是Linux系统，可以下载源码包进行安装，https://pypi.python.org/pypi/MySQL-python/1.2.5中下载zip包，然后安装：

yum install –y python-devel
yum install –y mysql-devel
yum install –y gcc
unzip MySQL-python-1.2.5.zip
cd MySQL-python-1.2.5
python setup.py build
python setup.py install
python
>>> import MySQLdb

 

二、数据库连接

　　MySQLdb提供了connect方法用来和数据库建立连接,接收数个参数,返回连接对象：代码如下:

　　首先在mysql的数据库中建立python库

create database python;

conn=MySQLdb.connect(host="192.168.203.12",user="momo",passwd="123456",db="python",charset="utf8")

　　比较常用的参数包括:

　　host:数据库主机名.默认是用本地主机

　　user:数据库登陆名.默认是当前用户

　　passwd:数据库登陆的秘密.默认为空

　　db:要使用的数据库名.没有默认值

　　port:MySQL服务使用的TCP端口.默认是3306，数字类型

　　charset:数据库编码

　　推荐把所有数据库的配置写在一个字典中，如下所示：

def connect_mysql():
    db_config = {
        'host': '192.168.203.12',
        'port': 3306,
        'user': 'momo',
        'passwd': '123456',
        'db': 'python',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
return cnx

　　

三、MySQL事物　　

　　MySQL 事务主要用于处理操作量大，复杂度高的数据。比如，你操作一个数据库，公司的一个员工离职了，你要在数据库中删除他的资料，也要删除该人员相关的，比如邮箱，个人资产等。这些数据库操作语言就构成了一个事务。

　　在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务，所以很多情况下我们都使用innodb引擎。

　　事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

　　一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

　　1、事务的原子性：一组事务，要么成功；要么撤回。

　　2、稳定性 ： 有非法数据（外键约束之类），事务撤回。

　　3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

　　4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

　　而mysql在默认的情况下，他是把每个select，insert，update，delete等做为一个事务的，登录mysql服务器，进入mysql，执行以下命令：

mysql> show variables like 'auto%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
| autocommit               | ON    |
| automatic_sp_privileges  | ON    |
+--------------------------+-------+
4 rows in set (0.00 sec)

　　如上所示： 有一个参数autocommit就是自动提交的意思，每执行一个msyql的select，insert，update等操作，就会进行自动提交。

　　如果把改选项关闭，我们就可以每次执行完一次代码就需要进行手动提交，connect对象给我们提供了两种办法来操作提交数据。

　　a)        mysql事务的方法

　　commit():提交当前事务，如果是支持事务的数据库执行增删改后没有commit则数据库默认回滚，白操作了

　　rollback():取消当前事务

　　下面我们来看个例子：

　　我们先创建一个员工表：

  create table employees (
        emp_no int not null auto_increment,
        emp_name varchar(16) not null,
        gender enum('M', 'F') not null,
        hire_date date not null,
        primary key (emp_no)
        );

　　其中，emp_no为员工id，为主键且唯一

　　emp_name为：员工的名字

　　fender为：性别，只有M和F两种选择

　　hire_date为：雇佣的时间。

　　为了试验的效果，我们插入几条数据：

insert into employees(emp_no, emp_name, gender, hire_date) values(1001, 'lingjiang', 'M', '2015-04-01');
insert into employees(emp_no, emp_name, gender, hire_date) values(1002, 'xiang', 'M', '2015-04-01');
insert into employees(emp_no, emp_name, gender, hire_date) values(1003, 'shang', 'M', '2015-04-01');
mysql> select * from employees;
+--------+-----------+--------+------------+
| emp_no | emp_name  | gender | hire_date  |
+--------+-----------+--------+------------+
|   1001 | lingjiang | M      | 2015-04-01 |
|   1002 | xiang     | M      | 2015-04-01 |
|   1003 | shang     | M      | 2015-04-01 |
+--------+-----------+--------+------------+
e)    rows in set (0.00 sec)

 

 四、MySQL游标

　　

游标（cursor）

 　　游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由python进一步处理，一组主变量一次只能存放一条记录，仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求。

　　

　　游标和游标的优点：在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。

　　常用方法:

　　cursor():创建游标对象
       close():关闭此游标对象
       fetchone():得到结果集的下一行
       fetchmany([size = cursor.arraysize]):得到结果集的下几行
       fetchall():得到结果集中剩下的所有行
       excute(sql[, args]):执行一个数据库查询或命令
       executemany (sql, args):执行多个数据库查询或命令

　　程序例子： 

　　1.创建游标对象

import MySQLdb
 db_config = {
        'host': '192.168.203.12',
        'port': 3306,
        'user': 'momo',
        'passwd': '123456',
        'db': 'python',
        'charset': 'utf8'
    }
cnx = MySQLdb.connect(**db_config)
cus = cnx.cursor()

　　2.对游标的基本操作

import MySQLdb

def connect_mysql():
    db_config = {
        'host': '192.168.203.12',
        'port': 3306,
        'user': 'momo',
        'passwd': '123456',
        'db': 'python',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __name__ == '__main__':
    cnx = connect_mysql()
    cus = cnx.cursor()
    sql  = '''select * from employees;'''
    try:
        cus.execute(sql)
        result1 = cus.fetchone()
        print('result1:')
        print(result1)
        result2 = cus.fetchmany(1)
        print('result2:')
        print(result2)
        result3 = cus.fetchall()
        print('result3:')
        print(result3)        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()

　　结果：　

result1:

(1001L, u'lingjiang', u'M', datetime.date(2015, 4, 1))

result2:

((1002L, u'xiang', u'M', datetime.date(2015, 4, 1)),)

result3:

((1003L, u'shang', u'M', datetime.date(2015, 4, 1)),)

　　解释：

　　  先通过MySQLdb.connect(**db_config)建立mysql连接对象

　　在通过 = cnx.cursor()创建游标

　　fetchone():在最终搜索的数据中去一条数据

　　fetchmany(1)在接下来的数据中在去1行的数据，这个数字可以自定义，定义多少就是在结果集中取多少条数据。

　　fetchall()是在所有的结果中搞出来所有的数据。

 

　　3.执行多行SQL语句

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Time    : 2017/9/18 22:17
# @Author  : lingxiangxiang
# @File    : domon3.py

from demon2 import connect_mysql


import MySQLdb


def connect_mysql():
    db_config = {
        "host": "192.168.203.12",
        "port": 3306,
        "user": "momo",
        "passwd": "123456",
        "db": "python",
        "charset": "utf8"
    }
    try:
        cnx = MySQLdb.connect(**db_config)
    except Exception as e:
        raise e
    return cnx
if __name__ == "__main__":
    sql = "select * from tmp;"
    sql1 = "insert into tmp(id) value (%s);"
    param = []
    for i in xrange(100, 130):
        param.append([str(i)])
    print(param)
    cnx = connect_mysql()
    cus = cnx.cursor()
    print(dir(cus))
    try:
        cus.execute(sql)
        cus.executemany(sql1, param)
        # help(cus.executemany)
        result1 = cus.fetchone()
        print("result1")
        print(result1)

        result2 = cus.fetchmany(3)
        print("result2")
        print(result2)

        result3 = cus.fetchall()
        print("result3")
        print(result3)
        cus.close()
        cnx.commit()

    except Exception as e:
        cnx.rollback()
        raise e
    finally:
        cnx.close()

 

五、数据库连接池

　　python编程中可以使用MySQLdb进行数据库的连接及诸如查询/插入/更新等操作，但是每次连接mysql数据库请求时，都是独立的去请求访问，相当浪费资源，而且
访问数量达到一定数量时，对mysql的性能会产生较大的影响。因此，实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的。
　　

　　

python的数据库连接池包 DBUtils：

DBUtils是一套Python数据库连接池包，并允许对非线程安全的数据库接口进行线程安全包装。DBUtils来自Webware for Python。

DBUtils提供两种外部接口：

* PersistentDB ：提供线程专用的数据库连接，并自动管理连接。

* PooledDB ：提供线程间可共享的数据库连接，并自动管理连接。

PooledDB的参数：

1. mincached，最少的空闲连接数，如果空闲连接数小于这个数，pool会创建一个新的连接

2. maxcached，最大的空闲连接数，如果空闲连接数大于这个数，pool会关闭空闲连接

3. maxconnections，最大的连接数，

4. blocking，当连接数达到最大的连接数时，在请求连接的时候，如果这个值是True，
请求连接的程序会一直等待，直到当前连接数小于最大连接数，如果这个值是False，会报错，

5. maxshared 当连接数达到这个数，新请求的连接会分享已经分配出去的连接

在uwsgi中，每个http请求都会分发给一个进程，连接池中配置的连接数都是一个进程为单位的（即上面的最大连接数，都是在一个进程中的连接数），
而如果业务中，一个http请求中需要的sql连接数不是很多的话（其实大多数都只需要创建一个连接），配置的连接数配置都不需要太大。

连接池对性能的提升表现在：

1.在程序创建连接的时候，可以从一个空闲的连接中获取，不需要重新初始化连接，提升获取连接的速度

2.关闭连接的时候，把连接放回连接池，而不是真正的关闭，所以可以减少频繁地打开和关闭连接

 六、数据库的操作

1.建表

　　各个表的结构如下：

　　student表：

字段名	类型	是否为空	主键	描述
StdID	int	否	是	学生ID
StdName	varchar(100)	否		学生姓名
Gender	enum('M', 'F')	是		性别
Age	tinyint	是		年龄

　　course表：

字段名	类型	是否为空	主键	描述
CouID	int	否	是	课程ID
Cname	varchar(50)	否		课程名字
TID	int	否		老师ID

 

　　　Score表：

字段名	类型	是否为空	主键	描述
SID	int	否	是	分数ID
StdID	int	否		学生id
CouID	int	否		课程id
Grade	int	否		分数

 

　　teacher表：

字段名	类型	是否为空	主键	描述
TID	int	否	是	老师ID
Tname	varcher(100)	否		老师名字

 

　　在Linux中MySQL建立student表，然后在Python代码中执行：

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.203.12',
        'port': 3306,
        'user': 'momo',
        'passwd': '123456',
        'db': 'student',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __name__ == '__main__':
    cnx = connect_mysql()
    cus = cnx.cursor()
    # sql  = '''insert into student(id, name, age, gender, score) values ('1001', 'ling', 29, 'M', 88), ('1002', 'ajing', 29, 'M', 90), ('1003', 'xiang', 33, 'M', 87);'''
    student = '''create table Student(
            StdID int not null,
            StdName varchar(100) not null,
            Gender enum('M', 'F'),
            Age tinyint
    )'''
    course = '''create table Course(
            CouID int not null,
            CName varchar(50) not null,
            TID int not null
    )'''
    score = '''create table Score(
                SID int not null,
                StdID int not null,
                CID int not null,
                Grade int not null
        )'''
    teacher = '''create table Teacher(
                    TID int not null,
                    TName varchar(100) not null
            )'''
     tmp = '''set @i := 0;
            create table tmp as select (@i := @i + 1) as id from information_schema.tables limit 10;
        '''
    try:
        cus.execute(student)
        cus.execute(course)
        cus.execute(score)
        cus.execute(thearch)
           cus.execute(tmp)
        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()

　　结果：

mysql> show tables;

+------------------+

| Tables_in_python |

+------------------+

| Course           |

| Score            |

| Student          |

| Teacher          |

| tmp              |

+------------------+

1        rows in set (0.00 sec)

information_schema数据库表说明:

 

SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

 

TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。

 

COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。

 

STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。

 

USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。

 

SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

 

TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

 

COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

 

CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。

COLLATIONS表：提供了关于各字符集的对照信息。

 

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。

 

TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。

 

KEY_COLUMN_USAGE表：描述了具有约束的键列。

 

ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

 

VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。

 

TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表

 

而TABLES在安装好mysql的时候，一定是有数据的，因为在初始化mysql的时候，就需要创建系统表，该表一定有数据。

set @i := 0;
create table tmp as select (@i := @i + 1) as id from information_schema.tables limit 10;

mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。set这个是mysql中设置变量的特殊用法，当@i需要在select中使用的时候，必须加:，这样就创建好了一个表tmp，查看tmp的数据：

mysql> select * from tmp;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

|    6 |

|    7 |

|    8 |

|    9 |

|   10 |

+------+

10 rows in set (0.00 sec)

我们只是从information_schema.tables表中取10条数据，任何表有10条数据也是可以的，然后把变量@i作为id列的值，分10次不断输出，依据最后select的结果，创建表tmp。

 

2.增加数据

substr是一个字符串函数，从第二个参数1,开始取字符，取到3+ floor(rand() * 75)结束

floor函数代表的是去尾法取整数。

rand()函数代表的是从0到1取一个随机的小数。

rand() * 75就代表的是：0到75任何一个小数，

3+floor(rand() * 75)就代表的是：3到77的任意一个数字

concat()函数是一个对多个字符串拼接函数。

sha1是一个加密函数，sha1(rand())对生成的0到1的一个随机小数进行加密，转换成字符串的形式。

       concat(sha1(rand()), sha1(rand()))就代表的是：两个0-1生成的小数加密然后进行拼接。

substr(concat(sha1(rand()), sha1(rand())), 1, floor(rand() * 80))就代表的是：从一个随机生成的一个字符串的第一位开始取，取到（随机3-77）位结束。

Gender字段：case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end,就代表的是，

       floor(rand()*10)代表0-9随机取一个数

       floor(rand()*10) mod 2 就是对0-9取得的随机数除以2的余数，

case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end,代表：当余数为1是，就取M，其他的为F

Age字段：25-floor(rand() * 5)代表的就是，25减去一个0-4的一个整数

代码如下：

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.203.12',
        'port': 3306,
        'user': 'momo',
        'passwd': '123456',
        'db': 'student',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __name__ == '__main__':
    cnx = connect_mysql()


    students = '''set @i := 10000;
            insert into Student select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75)), case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end, 25-floor(rand() * 5)  from tmp a, tmp b, tmp c, tmp d;
        '''
    course = '''set @i := 10;
            insert into Course select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 40)),  1 + floor(rand() * 100) from tmp a;
        '''
    score = '''set @i := 10000;
            insert into Score select @i := @i +1, floor(10001 + rand()*10000), floor(11 + rand()*10), floor(1+rand()*100) from tmp a, tmp b, tmp c, tmp d;
        '''
    theacher = '''set @i := 100;
            insert into Teacher select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 80)) from tmp a, tmp b;
        '''
    try:
        cus_students = cnx.cursor()
        cus_students.execute(students)
        cus_students.close()

        cus_course = cnx.cursor()
        cus_course.execute(course)
        cus_course.close()

        cus_score = cnx.cursor()
        cus_score.execute(score)
        cus_score.close()

        cus_teacher = cnx.cursor()
        cus_teacher.execute(theacher)
        cus_teacher.close()

        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()

 

3.查数据

在数据库中查出来所有名字有重复的同学的所有信息，然后写入到文件中，代码如下：

import codecs

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.203.12',
        'port': 3306,
        'user': 'momo',
        'passwd': '123456',
        'db': 'student',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __name__ == '__main__':
    cnx = connect_mysql()

    sql = '''select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'''
    try:
        cus = cnx.cursor()
        cus.execute(sql)
        result = cus.fetchall()
        with codecs.open('select.txt', 'w+') as f:
            for line in result:
                f.write(str(line))
                f.write('\n')
        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()

解释:

• 我们先来分析一下select查询这个语句：select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'
• 我们先来看括号里面的语句：select StdName from Student group by StdName having count(1)>1；这个是把所有学生名字重复的学生都列出来，
• 最外面select是套了一个子查询，学生名字是在我们（）里面的查出来的学生名字，把这些学生的所有信息都列出来。
• result = cus.fetchall()列出结果以后，我们通过fetchall()函数把所有的内容都取出来，这个result是一个tuple
• 通过文件写入的方式，我们把取出来的result写入到select.txt文件中。得到最终的结果

4.删除数据

　　删除课程成绩最差的5名老师，删除之前要先进行查询。代码如下：

import codecs

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.203.12',
        'port': 3306,
        'user': 'momo',
        'passwd': '123456',
        'db': 'student',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __name__ == '__main__':
    cnx = connect_mysql()

    sql = '''delete from Teacher where TID in(
    select TID from (select Course.CouID, Course.TID, Teacher.TName, count(Teacher.TID) as count_teacher from Course
    left join Score on Score.Grade < 60 and Course.CouID = Score.CouID
    left join Teacher on Course.TID = Teacher.TID
    group by Course.TID
    order by count_teacher desc
    limit 5)  as test )
    '''
    try:
        cus = cnx.cursor()
        cus.execute(sql)
        result = cus.fetchall()
        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()

　　　解释：

• 先查询出Course表中的Course.TID和Course.TID
• left join 是关联Score表，查出Score.Grade > 59，并且，课程ID和课程表的CouID要对应上
• left join Teacher 是关联老师表，课程中的了老师ID和老师表中的老师ID对应上
• select中加上老师的名字Teacher.Tname和count(Teacher.TID)
• group by Course.TID，在根据老师的的TID进行分组
• oder by 最后对count_teacher进行排序，取前5行，
• 在通过套用一个select子查询，把所有的TID搂出来
• 然后delete from Teacher 最后删除TID在上表中的子查询中

5.修改数据

　　把分数低于5分的成绩加上60分，代码如下：

import codecs

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.203.12',
        'port': 3306,
        'user': 'momo',
        'passwd': '123456',
        'db': 'student',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __name__ == '__main__':
    cnx = connect_mysql()

    sql = '''select *, (grade+60) as newGrade from Score where Grade <5;'''
    update = '''update Score set grade = grade + 60 where grade < 5;  '''
    try:
        cus_start = cnx.cursor()
        cus_start.execute(sql)
        result1 = cus_start.fetchall()
        print(len(result1))
        cus_start.close()

        cus_update = cnx.cursor()
        cus_update.execute(update)
        cus_update.close()

        cus_end = cnx.cursor()
        cus_end.execute(sql)
        result2 = cus_end.fetchall()
        print(len(result2))
        cus_end.close()

        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()

解释：

•  刚开始，我们可以查到分数小于5分的总个数有321个
• select *, (grade+60) as newGrade from Score where Grade <5;这个sql是把所有的成绩小于5的都列出来，然后最后加一列分数加60分的结果。
•  update Score set grade = grade + 60 where grade < 5;是把分数小于5的所有成绩都加60分
• 最后在检查分数小于5的个数为0，说明所有低于5分的分数都发生了改变。

6.索引

　　MySQL索引的概念

　　索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

　　索引类别

　　普通索引

　　普通索引（由关键字 KEY 或 INDEX 定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column =）或排序条件（ORDER BY column）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

　　唯一索引

　　普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE 把它定义为一个唯一索引。这么做的好处：一是简化了 MySQL 对这个索引的管理工作，这个索引也因此而变得更有效率；二是 MySQL 会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL 将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

　　主索引

　　在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的“主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是 PRIMARY 而不是 UNIQUE。

　　外键索引

　　如果为某个外键字段定义了一个外键约束条件，MySQL 就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

　　复合索引

　　索引可以覆盖多个数据列，如像 INDEX (columnA, columnB) 索引。这种索引的特点是 MySQL 可以有选择地使用一个这样的索引。如果查询操作只需要用到 columnA 数据列上的一个索引，就可以使用复合索引 INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX (A，B，C) 可以当做 A 或 (A,B) 的索引来使用，但不能当做 B、C 或 (B,C) 的索引来使用。

 

 

mysql主键和索引的区别:

 主键一定是唯一性索引，唯一性索引并不一定就是主键。

所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。数据库在设计时，主键起到了很重要的作用。主键可以保证记录的唯一和主键域非空，数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。

一个表中可以有多个唯一性索引，但只能有一个主键。

 主键列不允许空值，而唯一性索引列允许空值。

 索引可以提高查询的速度。

　　创建Course的CouID的字段为主键   Score的SID字段为主键    Student的StdID字段为主键  Teacher的TID字段为主键，代码如下：

import codecs

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.203.12',
        'port': 3306,
        'user': 'momo',
        'passwd': '123456',
        'db': 'student',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx

if __name__ == '__main__':
    cnx = connect_mysql()

    sql1 = '''alter table Teacher add primary key(TID);'''
    sql2 = '''alter table Student add primary key(StdID);'''
    sql3 = '''alter table Score add primary key(SID);'''
    sql4 = '''alter table Course add primary key(CouID);'''
    sql5 = '''alter table Score add index idx_StdID_CouID(StdID, CouID);'''
    # sql6 = '''alter table Score drop  index idx_StdID_CouID;'''   删除索引
    sql7 = '''explain select * from Score where StdID = 16213;'''
    try:
        cus = cnx.cursor()
        cus.execute(sql1)
        cus.close()

        cus = cnx.cursor()
        cus.execute(sql2)
        cus.close()

        cus = cnx.cursor()
        cus.execute(sql3)
        cus.close()

        cus = cnx.cursor()
        cus.execute(sql4)
        cus.close()

        cus = cnx.cursor()
        cus.execute(sql5)
        cus.close()

        cus = cnx.cursor()
        cus.execute(sql7)
        result = cus.fetchall()
        print(result)
        cus.close()

        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()

结果：

((1L, u'SIMPLE', u'Score', u'ref', u'idx_StdID_CouID', u'idx_StdID_CouID', u'4', u'const', 4L, None),)

解释：

Sql1， sql2， sql3， sql4是添加主键，sql5是增加一个索引，我们也可以在mysql的客户端上执行sq7，得到如下的结果：

mysql> explain select * from Score where StdID = 16213;              

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+

| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra |

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+

|  1 | SIMPLE      | Score | ref  | idx_StdID_CouID | idx_StdID_CouID | 4       | const |    4 | NULL  |

+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+

1 row in set (0.00 sec)

这个说明，我们在搜索StdID的时候，是走了idx_StdID_CouID索引的。