python做学生管理系统数据库模型_数据库建模：学生信息系统搭建实验

首先必须理清学生信息系统有哪些实体，这些实体之间的关系又是如何的，他们之间是怎么进行关联的

实体有哪些：

学生表(student)

专业表(major)

课程表(course)

成绩表(grade)

班级表(class)

老师表(teacher)

宿舍表(dorm)

考勤表(checking)

这里我用Visio画的数据库ER图来分析：

大致实体ER图：

详细ER图：

二、数据库建模(建表)：

1、创建专业表(major):

create table major(id int primary key auto_increment,name char(20)) charset=utf8;

数据表结构如下:

2、创建课程表(course)

create table course(id int primary key auto_increment,name char(20)) charset=utf8;

数据表结构如下:

3、创建专业和课程的中间表(major_mid_course)

create table major_mid_course(

id int primary key auto_increment,major_id int,

course_id int,

foreign key(major_id)references major(id),

foreign key(course_id) references course(id)

);

数据表结构如下:

4、创建成绩表(grade)

create table grade(

id int primary key auto_increment,

score int,stu_id int,course_id int,

foreign key(course_id) references course(id),

foreign key(stu_id) references student(id)

) charset=utf8;

数据表结构如下:

5、班级表(class)

create table class(

id int primary key auto_increment,

name char(20),

major_id int,foreign key(major_id) references major(id)

) charset=utf8;

数据表结构如下：

6、老师表(teacher)

create table teacher(id int primary key auto_increment,name char(20)) charset=utf8;

数据表结构如下：

7、班级和老师的中间表(class_teacher)

create table class_teacher(

id int primary key auto_increment,

class_id int,

tea_id int,

foreign key(class_id) references class(id),

foreign key(tea_id) references teacher(id)

);

数据表结构如下：

8、课程和老师的中间表(course_teacher)

create table course_teacher(

id int primary key auto_increment,

course_id int,

tea_id int,

foreign key(course_id) references course(id),

foreign key(tea_id) references teacher(id)

);

数据表结构如下：

9、宿舍表(drom)

create table drom(id int primary key auto_increment,name char(20)) charset=utf8;

数据表结构如下：

10、学生表(student)

create table student(

id int primary key auto_increment,

stu_num char(24),

name char(24),

age int,

gender char(20),

phone char(20),

major_id int,

class_id int,

drom_id int,

foreign key(major_id) references major(id),

foreign key(class_id) references class(id),

foreign key(drom_id) references drom(id)

) charset=utf8;

数据表结构如下：

11、学生和老师的中间表(student_teacher)

create table student_teacher(

id int primary key auto_increment,

stu_id int,

tea_id int,

foreign key(stu_id) references student(id),

foreign key(tea_id) references teacher(id)

);

数据表结构如下：

12、创建考勤表(checking)

create table checking(

id int primary key auto_increment,

date datetime,

status char(1),

stu_id int,foreign key(stu_id) references student(id)

) charset=utf8;

数据表结构如下:

二、数据插入：

1、插入专业表(major)数据

专业(major)

id

name

1

python

2

java

3

ui

4

php

5

linux

使用python来插入数据

2、插入课程表(course)数据

课程(course)

id

name

1

计算机基础

2

python基础

3

java编程

4

ps

5

html

6

数据库

7

linux基础

3、插入专业和课程关系表(major_mid_course)数据

专业—课程中间表(major_mid_course)

id

major_id

course_id

1

1

1

2

1

2

3

1

5

4

1

6

5

1

7

6

2

1

7

2

3

8

2

5

9

2

6

10

3

1

11

3

4

12

4

1

13

4

5

14

5

1

15

5

7

4、插入班级表(class)数据

班级(class)

id

name

major_id

1

py0506

1

2

ui0304

3

5、插入老师表(teacher)数据

老师(teacher)

id

name

1

张老师

2

边老师

3

申老师

6、插入班级-老师关系表(class_teacher)数据

班级-老师中间表(class_teacher)

id

class_id

tea_id

1

1

2

2

1

3

3

2

1

7、插入课程-老师关系表(course_teacher)数据

课程-老师中间表(course_teacher)

id

course_id

tea_id

1

1

3

2

2

3

3

5

2

4

6

2

5

4

1

8、插入宿舍表(drom)数据

宿舍(drom)

id

name

1

101

2

102

3

103

9、插入学员表(student)数据

学员(student)

id

stu_num

name

age

gender

phone

major_id

class_id

drom_id

1

1001

小李

19

男

13333331001

1

1

2

2

1002

小张

20

男

13333331002

1

1

2

3

1003

小王

18

男

13333331003

1

1

2

4

1004

小东

19

男

13333331004

1

1

2

5

1005

小丽

17

女

13333331005

1

1

3

6

1006

小花

19

女

13333331006

1

1

3

7

1007

小夏

20

女

13333331007

1

1

3

8

1008

小美

18

女

13333331008

1

1

3

9

1009

小韩

19

男

13333331009

3

2

1

10

1010

小吴

22

男

13333331010

3

2

1

11

1011

小牛

19

男

13333331011

3

2

1

12

1012

小朱

18

男

13333331012

3

2

1

10、插入学员-老师关系表(student_teacher)数据

学员-老师关系表(student_teacher)

id

stu_id

tea_id

1

1

2

2

2

2

3

3

2

4

4

2

5

5

2

6

6

2

7

7

2

8

8

2

9

9

1

10

10

1

11

11

1

12

12

1

13

1

3

14

2

3

15

3

3

16

4

3

17

5

3

18

6

3

19

7

3

20

8

3

11、插入成绩表(grade)数据(这里只插入python基础课程成绩)

成绩(grade)

id

score

stu_id

course_id

1

87

1

2

2

98

2

2

3

88

3

2

4

65

4

2

5

77

5

2

6

66

6

2

7

89

7

2

8

78

8

2

12、插入考勤表(checking)数据

考勤(checking)

id

date

status

stu_id

1

2019-6-14 9:05:34

N

2

2

2019-6-14 8:40:55

Y

3

3

2019-6-14 8:59:55

N

4

4

2019-6-13 8:30:22

Y

4

三、数据库优化：

1、给student表的name字段创建索引，(由于学生查询频繁)，加快查询速度：

2、给student表创建视图，实现安全性(虚表)

四、用户授权：

1、创建用户并设置密码

create user nhkj@'10.10.21.%' identified by '123';

2、授权用户访问student_view表的查询权限

五、查询一下条件语句

1、查询所有学员

select name from student;

2、查询所有男生

select name from student where gender='男';

3、查询所有女生

select name from student where gender='女';

4、查询所有缺勤学员

select name from student where id in (select stu_id from checking where status='N');

5、查询所有python专业的学员

select s.name,m.name from student as s inner join major as m on s.major_id=m.id and m.id=1;

6、查询所有python专业上过python基础的学员

为演示效果，我将‘小花’这个学生取消和‘申老师’关系，由于‘申老师’是教python基础课程的，所有‘小花’就是所有python专业学生里唯一没有上过python基础课程的学生，剩下的7位都上过小花的id为6

delete from student_teacher where stu_id=6;

SELECT

s.id AS s_id,

s.name AS s_name,

c.id AS c_id,

c.name AS c_name

FROM

student AS s

INNER JOIN student_teacher AS st

INNER JOIN teacher AS t

INNER JOIN course_teacher AS ct

INNER JOIN course AS c

WHERE

s.id=st.stu_id

AND

st.tea_id=t.id

AND

t.id=ct.tea_id

AND

ct.course_id=c.id

AND

c.id=2;

7、查询所有python 0506班所有学员

select s.id as s_id,s.name as s_name,c.id as c_id,c.name as c_name from student

as s inner join class as c on s.class_id=c.id and c.id=1;

8、查询所有python 0506班所有学员python基础课成绩

select s.name as s_name,c.name as c_name,g.score from student as s inner join

course as c inner join grade as g where s.id=g.stu_id and g.course_id=c.id;

9、查询python 0506班任课老师

select c.id as c_name,c.name as c_name,t.id as t_name,t.name as t_name from

class as c inner join class_teacher as ct inner join teacher as t where c.id=ct.class_id and ct.tea_id=t.id and c.id=1;

10、查询边老师教过的学员

select t.id as t_id,t.name as t_name,s.id as s_id,s.name as s_name from teacher as t inner join

student_teacher as st inner join student as s where t.id=st.tea_id and st.stu_id=s.id and t.name='边老师';

11、查询边老师教过的班级

select t.id as t_id,t.name as t_name,c.id as c_id,c.name as c_name

from class as c inner join class_teacher as ct inner join teacher as t

where c.id=ct.class_id and ct.tea_id=t.id and t.name='边老师';

12、查询小王同学python基础课程的成绩

select s.id as s_id,s.name as s_name,c.name as c_name,g.score

from student as s inner join grade as g inner join course as c

where c.id=g.course_id and s.id=g.stu_id and g.course_id=2 and s.id=3;

13、查询所有python基础课程的学员成绩

select s.id as s_id,s.name as s_name,c.name as c_name,g.score

from student as s inner join grade as g inner join course as c

where c.id=g.course_id and s.id=g.stu_id and g.course_id=2;

14、查询python 0506班所有的宿舍

select c.id as c_id,c.name as c_name,d.id as d_id,d.name as d_name

from class as c inner join student as s inner join drom as d

where c.id=s.class_id and s.drom_id=d.id and c.id=1;

15、查询python 0506班 102宿舍的所有学生的成绩

select c.name as c_name,d.name as d_name,s.name as s_name,g.score

from class as c inner join student as s inner join drom as d inner join grade as g

where c.id=s.class_id and s.drom_id=d.id and g.stu_id=s.id and c.id=1 and d.id=2;