学生信息数据库的创建

需求分析

学生信息数据库用来存储学生的一些相关信息，其中包括学生所在院系，学生基础信息，老师信息表，学生总成绩，学生各学科成绩，专业课基础信息。并构建不同的用户使其各自分配不同的权限，对整个数据库进行维护。例如：班主任进行学生总成绩的维护，各科老师对各科成绩表以及专业基础表进行管理，学生用户全部可以对学生基础信息进行填写以进行基础信息的收集

关系模型分析

表结构设计

学生基本信息表

student_table基本信息

字段	数值类型	约束
id	int	primary key
name	varchar(20)	not null
gender	set('male','female')	null
age	int	null
number	varchar(20)	unique
join_time	date	null
phone	varchar(20)	null

教师师基本信息表

teacher_table基本信息

字段	数值类型	约束
id	int	primary key
name	varchar(20)	not null
gender	set('male','female')	null
phone	varchar(20)	null
subject	set('math','chine','english')	null

专业基本信息表

major_table基本信息

字段	数值类型	约束
id	int	primary
name	varchar(20)	not null
total	int	not null
department	varchar(20)	not null

学科基本信息表

subject_table基本信息

字段	数值类型	约束
id	int	primary key
name	set('english','math','china')	null
time	int	default
total	int	not
teacher	varchar(20)	null

总成绩表

total_score_table基本信息

字段	数值类型	约束
id	int	primary key
std_id	int	foreign key
score	float	not null
ranking	int	not null

各科成绩表

score_table基本信息

字段	数值类型	约束
id	int	primary key
std_id	int	foreign key
suj_id	int	foreign key
score	float	not null

数据库物理设计与实现

创建数据库并使用

create database if not exists student_database charset utf8mb4;
use student_database;

创建数据表

学生表

create table student_table(id int primary key,
                           name varchar(20) not null,
                           gender set('female','male'),
                           age int,
                           number varchar(20) unique,
                           join_time date,
                           phone varchar(20));

教师表

create table teacher_table(id int primary key,
                           name varchar(20) not null,
                           gender set('male','female'),
                           phone varchar(20),
                           subject set('math','english','china'));

专业表

create table major_table(id int primary key,
                         name varchar(20) not null,
                         total int not null,
                         department varchar(20) not null);

学科表

create table subject_table(id int primary key,
                           name set('english','math','china'),
                           time int default 48,
                           total int,   
                           teacher varchar(20));

成绩表

总成绩表

create table total_score_table(id int primary key,
                               std_id int,        
                               score float not null,
                               ranking int not null,
                               constraint fk_std_id foreign key (std_id) references student_table(id));

各科成绩表

create table score_table(id int primary key,
                         std_id int, 
                         sub_id int,
                         score float,
                         constraint fk_std_new_id foreign key (std_id) references student_table(id),
                         constraint fk_sub_id foreign key (sub_id) references subject_table(id));

创建用户并进行授权

用户管理相关语法

查询用户

select * from mysql.user;

创建用户

create user 'user_a'@'localhost' identified by 'old_mima';

修改用户密码

alter user 'user_a'@'localhost' identified by 'new_mima';

删除用户

drop user 'user_a'@'localhost';

权限管理相关语法

查询权限

show grants for 'root'@'localhost';

授予权限

#创建新用户
create user 'user_a'@'localhost' identified by 'old_mima';
#授予权限
grant all privileges on  student_database.student_table to 'user_a'@'localhost';

撤销权限

remove all privileges on student_database.student_table from 'user_a'@'localhost';

创建相应角色

使用不同角色插入数据来模拟数据库的使用与维护

学生

此处应该创建多个学生来进行学生基础信息表的维护，但是所使用的命令相近，因此仅创建一个作为演示。

创建

create user 'student'@'localhost' identified by 'student' ;

授权

grant all privileges on student_database.student_table to 'student'@'localhost' ;

老师

此处老师也仅适用一个进行演示

create user 'teacher'@'localhost' identified by 'teacher';

GRANT ALL PRIVILEGES ON student_database.teacher_table TO 'teacher'@'localhost';
GRANT ALL PRIVILEGES ON student_database.subject_table TO 'teacher'@'localhost';
GRANT ALL PRIVILEGES ON student_database.score_table TO 'teacher'@'localhost';

班主任

创建角色

create user 'master'@'localhost' identified   by 'master';

授予权限

班主任授予全部数据表的权限，可以直接使用*代替

grant all privileges  on student_database.*  to 'master'@'localhost';

数据库的使用

学生

使用student用户登录，发现student_database中仅仅可以使用student_table

老师

使用teacher用户登录，可以使用student_database中的teacher_table,score_table和subject_table三个表

班主任

使用master用户登录，可以使用student_database中的所有表