数据库的操作

在操作设计数据库之前，我们先要设计数据库表结构，我们就来分析分析经典的学生，课程，成绩，老师这几者他们之间的关系，我们先来分析各个主体他们直接有什么属性，并确定表结构，在实际开发过程中，根据自己的业务需要和属性，设计不同的表结构。

练习设计表结构：

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)

否

　

老师名字

 

2.创建表

数据库键表，直接在python代码中执行

import MySQLdbdef connect_mysql():    db_config = {        'host': '192.168.1.110',        'port': 3306,        'user': 'zk',        'passwd': '123456',        'db': 'python',        'charset': 'utf8'    }    cnx = MySQLdb.connect(**db_config)    return cnxif __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] ERROR 3: Error writing file ‘./test/foo.frm’ (Errcode 28) 
一般是磁盘空间不够了，注意检查磁盘空间

结果：

mysql> show tables;+------------------+| Tables_in_python |+------------------+| Course           || Score            || Student          || Teacher          || tmp              |+------------------+3rows in set (0.00 sec)

没有异常，说明已经创建完成。

3.增加数据

#! /usr/bin/python  # -*- coding: utf-8 -*-  #@Time      : 2017/11/22 23:56  #@Author    : zhoukai-fx#@File      : zengjiashuju.pyimport MySQLdbdef connect_mysql():    db_config = {        'host': '192.168.1.110',        'port': 3306,        'user': 'zk',        'passwd': '123456',        'db': 'python',        'charset': 'utf8'    }    cnx = MySQLdb.connect(**db_config)    return cnxif __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()

返回结果：

mysql> select count(*) from Student;+----------+| count(*) |+----------+|    10000 |+----------+1 row in set (0.01 sec)mysql> select count(*) from Course; +----------+| count(*) |+----------+|       10 |+----------+1 row in set (0.00 sec)mysql> select count(*) from Score;+----------+| count(*) |+----------+|    10000 |+----------+1 row in set (0.00 sec)mysql> select count(*) from Teacher;+----------+| count(*) |+----------+|      100 |+----------+1 row in set (0.00 sec)

如图所示，在Student的表中增加了10000条数据，id是从10000开始的。count函数时用来统计个数的。

我们知道Student有四个字段，StdID，StdName，Gender，Age；我们先来看这个select语句：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;

StdID字段：@i就代表的就是，从10000开始，在上一句sql中设置的；

StdName字段：substr(concat(sha1(rand()), sha1(rand())), 1, floor(rand() * 80))就代表的是，

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的一个整数

4.查询数据

#! /usr/bin/python  # -*- coding: utf-8 -*-  #@Time      : 2017/11/23 0:20#@Author    : zhoukai-fx#@File      : chashuju.pyimport codecsimport MySQLdbdef connect_mysql():    db_config = {        'host': '192.168.1.110',        'port': 3306,        'user': 'zk',        'passwd': '123456',        'db': 'python',        'charset': 'utf8'    }    cnx = MySQLdb.connect(**db_config)    return cnxif __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.txt文件，内容如下：

(19844L, u'315', u'F', 24)(17156L, u'315', u'F', 25)(14349L, u'48f', u'F', 25)(17007L, u'48f', u'F', 25)(12629L, u'afd', u'F', 25)(13329L, u'afd', u'F', 24)(10857L, u'e31', u'F', 23)(14476L, u'e31', u'M', 21)(16465L, u'ee5', u'M', 22)(18570L, u'ee5', u'M', 21)(17056L, u'ef0', u'M', 23)(16946L, u'ef0', u'F', 24)

1. 我们先来分析一下select查询这个语句：

select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'

2. 我们先来看括号里面的语句：select StdName from Student group by StdName having count(1)>1；这个是把所有学生名字重复的学生都列出来，

3. 最外面select是套了一个子查询，学生名字是在我们（）里面的查出来的学生名字，把这些学生的所有信息都列出来。

4. result = cus.fetchall()列出结果以后，我们通过fetchall()函数把所有的内容都取出来，这个result是一个tuple

5. 通过文件写入的方式，我们把取出来的result写入到select.txt文件中。得到最终的结果。

5.删除数据

有些老师不好好上课，导致课程的及格率太低，最后名最差的5名老师将会被开除。

import MySQLdbdef connect_mysql():    db_config = {        'host': '192.168.1.110',        'port': 3306,        'user': 'zk',        'passwd': '123456',        'db': 'python',        'charset': 'utf8'    }    cnx = MySQLdb.connect(**db_config)    return cnxif __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()

1. 先查询出Course表中的Course.TID和Course.TID

2. left join 是关联Score表，查出Score.Grade > 59，并且，课程ID和课程表的CouID要对应上

3. left join Teacher 是关联老师表，课程中的了老师ID和老师表中的老师ID对应上

4. select中加上老师的名字Teacher.Tname和count(Teacher.TID)

5. group by Course.TID，在根据老师的的TID进行分组

6. oder by 最后对count_teacher进行排序，取前5行，

7. 在通过套用一个select子查询，把所有的TID搂出来

8. 然后delete from Teacher 最后删除TID在上表中的子查询中。

6.修改数据

我们把分数低于5分的成绩所有都加60分

import MySQLdbdef connect_mysql():    db_config = {        'host': '192.168.1.110',        'port': 3306,        'user': 'zk',        'passwd': '123456',        'db': 'python',        'charset': 'utf8'    }    cnx = MySQLdb.connect(**db_config)    return cnxif __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()

返回结果：

3210

1. 刚开始，我们可以查到分数小于5分的总个数有321个

2.select *, (grade+60) as newGrade from Score where Grade <5;这个sql是把所有的成绩小于5的都列出来，然后最后加一列分数加60分的结果。

3.update Score set grade = grade + 60 where grade < 5;是把分数小于5的所有成绩都加60分

4.最后在检查分数小于5的个数为0，说明所有低于5分的分数都发生了改变。

7.索引

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

1．普通索引

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

2．唯一索引

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

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

3．主索引

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

4．外键索引

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

5．复合索引

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

创建Course的CouID的字段为主键   

Score的SID字段为主键    

Student的StdID字段为主键  

Teacher的TID字段为主键

import codecsimport MySQLdbdef connect_mysql():    db_config = {        'host': '192.168.1.110',        'port': 3306,        'user': 'zk',        'passwd': '123456',        'db': 'python',        'charset': 'utf8'    }    cnx = MySQLdb.connect(**db_config)    return cnxif __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索引的。