数据库建模：学生信息系统搭建实验
首先必须理清学生信息系统有哪些实体，这些实体之间的关系又是如何的，他们之间是怎么进行关联的
实体有哪些：
学生表（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;
