设计表
通过设计经典的学生管理系统，熟悉数据库的使用。以下是根据课程，成绩，老师这几者的关系设计表结构。
学生表：
|
Student |
||||
|
字段名 |
类型 |
是否为空 |
主键 |
描述 |
|
stdid |
int |
否 |
是 |
学生ID |
|
stdname |
varchar(100) |
否 |
|
学生姓名 |
|
gender |
enum('M', 'F') |
是 |
|
性别 |
|
age |
int |
是 |
|
年龄 |
课程表：
|
Course |
||||
|
字段名 |
类型 |
是否为空 |
主键 |
描述 |
|
couid |
int |
否 |
是 |
课程ID |
|
cname |
varchar(50) |
否 |
|
课程名字 |
|
tid |
int |
否 |
|
老师ID |
成绩表
|
字段名 |
类型 |
是否为空 |
主键 |
描述 |
|
sid |
int |
否 |
是 |
分数ID |
|
stdid |
int |
否 |
|
学生id |
|
couid |
int |
否 |
|
课程id |
|
grade |
int |
否 |
|
分数 |
教师表：
|
Teacher |
||||
|
字段名 |
类型 |
是否为空 |
主键 |
描述 |
|
tid |
int |
否 |
是 |
老师ID |
|
tname |
varcher(100) |
否 |
|
老师名字 |
有了表结构，创建表
#!/usr/bin/env python #coding:utf8 import MySQLdb def connect_mysql(): db_config = { "host":"172.16.61.158", "port":3306, "user":"root", "passwd":"123456", "db":"stu", "charset":"utf8", } try: cnx = MySQLdb.connect(**db_config) except Exception as e : raise e return cnx student = '''create table student( stdid int not NULL , stdname varchar(100) not null, gender enum('M','F'), age int);''' 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 );''' if __name__ == "__main__": cnx = connect_mysql() print(cnx) cus = cnx.cursor() try : print student cus.execute(student) print course cus.execute(course) print score cus.execute(score) print teacher cus.execute(teacher) cus.close() cnx.commit() except Exception as e : cnx.rollback() print("error,{0}".format(e)) raise e finally: cnx.close()
执行结果：
<_mysql.connection open to '172.16.61.158' at 27b6e48> create table student( stdid int not NULL , stdname varchar(100) not null, gender enum('M','F'), age int); create table course( couid int not null, cname varchar(50) not null , tid int not null ); create table score( sid int not null, stdid int not null, couid int not null, grade int not null ); create table teacher( tid int not null, tname varchar(100) not null );
有了表，下步添加数据：
#!/usr/bin/env python #coding:utf8 import MySQLdb def connect_mysql(): db_config = { "host":"172.16.61.158", "port":3306, "user":"root", "passwd":"123456", "db":"stu", "charset":"utf8", } try: cnx = MySQLdb.connect(**db_config) except Exception as e : raise e return cnx student = '''set @i:= 10000; insert into student select @i:=@i +1,substr(concat(sha1(rand()),sha1(rand())),1,5+floor(rand() +100)),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; ''' if __name__ == "__main__" : cnx = connect_mysql() try : print student cus_students = cnx.cursor() cus_students.execute(student) cus_students.close() print course cus_course = cnx.cursor() cus_course.execute(course) cus_course.close() print score cus_score = cnx.cursor() cus_score.execute(score) cus_score.close() print teacher cus_theacher = cnx.cursor() cus_theacher.execute(teacher) cus_theacher.close() print("OK") cnx.commit() except Exception as e : cnx.rollback() print('error') raise e finally: cnx.close()
运行结果：
set @i:= 10000; insert into student select @i:=@i +1,substr(concat(sha1(rand()),sha1(rand())),1,5+floor(rand() +100)),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; 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; 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; 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; OK
验证：
查询数据
#!/usr/bin/env python #coding:utf8 import MySQLdb import codecs def connect_mysql(): db_config = { "host":"172.16.61.158", "port":3306, "user":"root", "passwd":"123456", "db":"stu", "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;''' print sql try: cus = cnx.cursor() cus.execute(sql) result = cus.fetchall() for res in result: print res cus.close() cnx.commit() except Exception as e: cnx.rollback() print('error') raise e finally: cnx.close()
运行结果：
select * from student where stdname in (select stdname from student group by stdname having count(1)>1 ) order by stdname; (19001L, u'e03fd17e980627e67c2e1b583f611e4a0855e46e176237ea5e6ba7c2c6a992787447ad57d0a4597d', u'F', 21L) (19021L, u'e03fd17e980627e67c2e1b583f611e4a0855e46e176237ea5e6ba7c2c6a992787447ad57d0a4597d', u'M', 25L) (19028L, u'e03fd17e980627e67c2e1b583f611e4a0855e46e176237ea5e6ba7c2c6a992787447ad57d0a4597d', u'F', 23L)