数据库建模:学生信息系统搭建实验

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

实体有哪些:
学生表(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;

在这里插入图片描述

posted @ 2019-06-17 20:43  Python及时行乐  阅读(966)  评论(0编辑  收藏  举报