一、设计表结构
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) | 否 | 老师名字 |
二、创建mysql表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
import
MySQLdb
def
connect_mysql():
db_config
=
{
"host"
:
"192.168.2.230"
,
"port"
:
3306
,
"user"
:
"root"
,
"passwd"
:
"123456"
,
"db"
:
"python"
,
"charset"
:
"utf8"
}
try
:
cnx
=
MySQLdb.connect(
*
*
db_config)
except
Exception as e:
raise
e
return
cnx
if
__name__
=
=
'__main__'
:
cnx
=
connect_mysql()
cus
=
cnx.cursor()
# sql = '''insert into student(id, name, age, gender, score) values ('101', 'huang', 27, 'M', 99), ('102', 'zhao', 27, 'M', 90), ('103', 'li', 26, '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,
CouID 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(teacher)
cus.execute(tmp)
cus.close()
cnx.commit()
except
Exception as e:
cnx.rollback()
print
(
'error'
)
raise
e
finally
:
cnx.close()
|
说明：
1）mysql中变量不用事前申明，直接使用“@变量名”；set是mysql中设置变量的特殊用法，当@i需要在select中使用的时候，必须加“:”
2）如上，从information_schema.tables表中取10条数据（任何表都可以），然后把变量@i作为id列的值，分10次不断输出，最后根据select的结果，创建表tmp（用于后面增删改查）
运行结果：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
mysql> show tables;
+
------------------+
| Tables_in_python |
+
------------------+
| Course |
| Score |
| Student |
| Teacher |
| employees |
| test |
| tmp |
+
------------------+
mysql>
select
*
from
tmp;
+
------+
| id |
+
------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+
------+
|
三、添加数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
import
MySQLdb
def
connect_mysql():
db_config
=
{
"host"
:
"192.168.2.230"
,
"port"
:
3306
,
"user"
:
"root"
,
"passwd"
:
"123456"
,
"db"
:
"python"
,
"charset"
:
"utf8"
}
try
:
cnx
=
MySQLdb.connect(
*
*
db_config)
except
Exception as e:
raise
e
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;
'''
teacher
=
'''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(teacher)
cus_teacher.close()
cnx.commit()
except
Exception as e:
cnx.rollback()
print
(
'error'
)
raise
e
finally
:
cnx.close()
|
运行结果：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
mysql>
select
count
(*)
from
Course;
+
----------+
|
count
(*) |
+
----------+
| 10 |
+
----------+
1 row
in
set
(0.08 sec)
mysql>
select
count
(*)
from
Score;
+
----------+
|
count
(*) |
+
----------+
| 10000 |
+
----------+
1 row
in
set
(0.01 sec)
mysql>
select
count
(*)
from
Student;
+
----------+
|
count
(*) |
+
----------+
| 10000 |
+
----------+
1 row
in
set
(0.00 sec)
mysql>
select
count
(*)
from
Teacher;
+
----------+
|
count
(*) |
+
----------+
| 100 |
+
----------+
1 row
in
set
(0.00 sec)
|
说明：
Student表
| 字段名 | 类型 | 是否为空 | 主键 | 描述 |
| StdID | int | 否 | 是 | 学生ID |
| StdName | varchar(100) | 否 | 学生姓名 | |
| Gender | enum('M', 'F') | 是 | 性别 | |
| Age | tinyint | 是 | 年龄 |
语句分析：
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;
3.1.StdID字段：select @i:=@i+1
解释：表示id号，@i从上一个sql语句中的10000开始
3.2.StdName字段：substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75))
解释：表示姓名是随机生成一个字符串
1）函数rand()
产生0到1之间的浮点数
例子：
|
1
2
3
4
5
6
7
|
mysql>
select
rand();
+
---------------------+
| rand() |
+
---------------------+
| 0.23552189044237312 |
+
---------------------+
1 row
in
set
(0.00 sec)
|
2）函数sha1()
加密，转化成字符串
例子：
|
1
2
3
4
5
6
7
|
mysql>
select
sha1(rand());
+
------------------------------------------+
| sha1(rand()) |
+
------------------------------------------+
| 4e56e84d74f4a526eb5debe8221810b260503dde |
+
------------------------------------------+
1 row
in
set
(0.00 sec)
|
3）函数floor（浮点数）
取整（去掉小数部分）
例子：
|
1
2
3
4
5
6
7
|
mysql>
select
floor(rand() * 75);
+
--------------------+
| floor(rand() * 75) |
+
--------------------+
| 26 |
+
--------------------+
1 row
in
set
(0.00 sec)
|
4）函数concat（字符串1，字符串2）
用于拼接两个字符串
例子：
|
1
2
3
4
5
6
7
|
mysql>
select
concat(sha1(rand()), sha1(rand()));
+
----------------------------------------------------------------------------------+
| concat(sha1(rand()), sha1(rand())) |
+
----------------------------------------------------------------------------------+
| 0d6ba94bdecc5f328f8bf3e6e141063cabb5960ca0109cccce8f7e040e352865dc0bdd5c7feffac2 |
+
----------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
|
5）函数substr（字符串，起始位置，长度）
用于截取字符串
例子：
|
1
2
3
4
5
6
|
mysql>
select
substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75));
+
-----------------------------------------------------------------------+
| substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75)) |
+
-----------------------------------------------------------------------+
| 9a91c6627be63546576ca7d500cecba34231e |
+
-----------------------------------------------------------------------+
|
3.3.Gender字段：case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end
解释：表示0-9取得的随机数除以2的余数，为1则性别M；为0则性别为F
3.4.Age字段：25-floor(rand() * 5)
解释：表示年龄在25+0=25岁至25+4=29岁之间的随机数
3.5.语句select * from tmp a, tmp b, tmp c
解释：a，b，c都是tmp表的别名，相当于每个表都循环了一遍，最终10的3次方个数据
Course表、Score表、Teacher表...
四、查询数据
需求：10000条数据，可能会有同学的名字是一样的，在数据库中查出来所有名字有重复的同学的所有信息，然后写入到文件中。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
import
codecs
import
MySQLdb
def
connect_mysql():
db_config
=
{
"host"
:
"192.168.2.230"
,
"port"
:
3306
,
"user"
:
"root"
,
"passwd"
:
"123456"
,
"db"
:
"python"
,
"charset"
:
"utf8"
}
try
:
cnx
=
MySQLdb.connect(
*
*
db_config)
except
Exception as e:
raise
e
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)
# 将游标得到的结果集中所有行（tuple形式）取出，并写进select.txt文件中
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文件
(18559L, u'167', u'F', 24)
(19579L, u'167', u'F', 21)
(13281L, u'3d3', u'M', 22)
(15806L, u'3d3', u'M', 22)
(16476L, u'4dd', u'F', 24)
(18134L, u'4dd', u'F', 24)
(12116L, u'db3', u'M', 22)
(14153L, u'db3', u'F', 25)
五、删除数据
需求：开除及格率最差的5名老师
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
import
MySQLdb
def
connect_mysql():
db_config
=
{
"host"
:
"192.168.2.230"
,
"port"
:
3306
,
"user"
:
"root"
,
"passwd"
:
"123456"
,
"db"
:
"python"
,
"charset"
:
"utf8"
}
try
:
cnx
=
MySQLdb.connect(
*
*
db_config)
except
Exception as e:
raise
e
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()
|
运行结果：
无报错信息
六、修改数据
需求：分数低于5分的成绩所有都加60分
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
import
MySQLdb
def
connect_mysql():
db_config
=
{
"host"
:
"192.168.2.230"
,
"port"
:
3306
,
"user"
:
"root"
,
"passwd"
:
"123456"
,
"db"
:
"python"
,
"charset"
:
"utf8"
}
try
:
cnx
=
MySQLdb.connect(
*
*
db_config)
except
Exception as e:
raise
e
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()
|
运行结果：
374
0
七、添加索引
需求：创建Course的CouID的字段为主键，Score的SID字段为主键，Student的StdID字段为主键，Teacher的TID字段为主键
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
import
MySQLdb
def
connect_mysql():
db_config
=
{
"host"
:
"192.168.2.230"
,
"port"
:
3306
,
"user"
:
"root"
,
"passwd"
:
"123456"
,
"db"
:
"python"
,
"charset"
:
"utf8"
}
try
:
cnx
=
MySQLdb.connect(
*
*
db_config)
except
Exception as e:
raise
e
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;''' delete index
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', 1L, None),)
本文转自 huangzp168 51CTO博客，原文链接：http://blog.51cto.com/huangzp/2043384，如需转载请自行联系原作者
