课程设计名称：《简易教务系统数据库》
数据库环境：mysql8.0.19
开发环境：win10的dos命令行，没有使用任何数据库管理软件
PS：有大牛若发现bug或不足之处希望能点出，拜托啦

# 一、定义
### 1 注释
```sql
# ENGINE=INNODB：将数据库的引擎设置为InnoDB
# （mysql中两种数据库引擎 ：MyISAM 、InnoDB）
# AUTO_INCREMENT=1：自动增长的起始值为1
# DEFAULT CHARSET=utf8：设置数据库的默认字符集为utf8
```

### 2 删库、删表、删视图、删除存储过程、删除触发器
```sql
# drop database 数据库名;
# drop table 表名;
# drop view 视图名;
# drop procedure 存储过程名;
# drop trigger 触发器名;
```

### 3 创库
```sql
create database if not exists mydb;
use mydb;
```

### 4 辅导员表
```sql
create table if not exists admin(
a_name varchar(32) not null,
password varchar(32) not null,
primary key(a_name))
ENGINE=InnoDB default charset=utf8;
```

### 5 学生表
```sql
create table if not exists student(
s_no varchar(32) not null,
s_name varchar(32) not null,
password varchar(32) not null,
clazz varchar(32) not null,
sex varchar(5) not null default '男',
phone varchar(12) default null,
qq varchar(18) default null,
primary key(s_no))
ENGINE=InnoDB default charset=utf8;
```

### 6 教师表
```sql
create table if not exists teacher(
t_no varchar(32) not null,
t_name varchar(32) not null,
password varchar(32) not null,
sex varchar(5) not null default '男',
phone varchar(12) default null,
qq varchar(18) default null,
primary key(t_no))
ENGINE=InnoDB default charset=utf8;
```

### 7 课程表
```sql
create table if not exists course(
c_name varchar(32) not null,
t_no varchar(32) not null,
date date not null,
place varchar(32) not null,
selected_num int(5) not null default '0',
max_num int(5) not null default '50',
primary key(c_name))
ENGINE=InnoDB default charset=utf8;
```

### 8 选课表
```sql
create table if not exists selected_course(
sc_id int(5) not null auto_increment,
s_no varchar(32) not null,
c_name varchar(32) not null,
primary key(sc_id))
ENGINE=InnoDB auto_increment=1 default charset=utf8;
```

### 9 成绩表
```sql
create table if not exists score(
score_id int(5) not null auto_increment,
s_no varchar(32) not null,
c_name varchar(32) not null,
score double(5,2) not null,
primary key(score_id))
ENGINE=InnoDB auto_increment=1 default charset=utf8;
```

### 10 请假表
```sql
create table if not exists leaves(
leave_id int(5) not null auto_increment,
s_no varchar(32) not null,
info varchar(512) default null,
status int(5) not null default '0',
answer varchar(512) default null,
primary key(leave_id))
ENGINE=InnoDB auto_increment=1 default charset=utf8;
```

# 二、数据测试
### 1 添加辅导员
```sql
insert into admin(a_name,password) values
('admin','admin');
```

### 2 添加学生
```sql
insert into student(s_no,s_name,password,clazz,sex,phone,qq) values
('192020406','顾家明','123','计算机1班','男','13729836748','228364917'),
('182020235','叶灵静','123','计算机2班','女','18936745273','126484923'),
('192020226','柳怀沙','123','计算机1班','女','19927394537','73528364');
```

### 3 添加老师
```sql
insert into teacher(t_no,t_name,password,sex,phone,qq) values
('10001','陈时权','000','男','18523974628','323574522'),
('10002','刘光世','000','男','13583648527','237499453'),
('10003','林宗吾','000','男','13663929263','658538472');
```

### 4 添加课程
```sql
insert into course(c_name,t_no,date,place,selected_num,max_num) values
('Linux系统与应用','10001','2020-11-15','2实102',0,50),
('HTML5编程技术','10002','2020-11-15','2实103',0,80),
('数据库原理与应用','10003','2020-11-15','3实207',0,60);
```

### 5 添加选课
```sql
insert into selected_course(s_no,c_name) values
('192020406','HTML5编程技术'),('192020406','Linux系统与应用'),
('182020235','Linux系统与应用'),('182020235','数据库原理与应用'),
('192020226','数据库原理与应用'),('192020226','HTML5编程技术');
```

### 6 添加成绩
```sql
insert into score(s_no,c_name,score) values
('192020406','HTML5编程技术','91'),('192020406','Linux系统与应用','71'),
('182020235','Linux系统与应用','54'),('182020235','数据库原理与应用','58'),
('192020226','数据库原理与应用','89'),('192020226','HTML5编程技术','73');
```

### 7 添加请假条
注：status 值，0 为正在审核，1 为已通过，2 为未通过；answer 为老师批复
```sql
insert into leaves(s_no,info,status,answer) values
('192020406','因感冒发烧暂时被隔离于学校招待所，故申请3天病假，时间为11月15日至11月18日',1,'注意保暖'),
('182020235','肚子疼不舒服申请病假',2,'没有申请时间'),
('192020226','11月15日要到广州参加企业面试，故申请一天事假',0,'');
```

### 8 在student表和selected_course表上创建名为v_sc的视图,用于查询学生的选课情况
要求：视图保留学号、学生名、课程，按学生学号升序排序
```sql
create view v_sc
as select student.s_no, student.s_name, selected_course.c_name
from student join selected_course on
student.s_no=selected_course.s_no
order by s_no;
```

### 9 在student表和score表上创建名为v_score的视图,用于查询每个课程所有学生的成绩
要求：视图保留课程、学生名和期末分数,按课程名和分数的降序排序
```sql
create view v_score
as select score.c_name, student.s_name, score.score
from student join score on
student.s_no=score.s_no
order by score.c_name,score.score desc;
```

### 10 在student表和leaves表上创建名为v_leaves的视图,查询所有学生的请假条
要求：视图保留学号、学生名、申请信息和申请状态,按申请状态排序
```sql
create view v_leaves
as select student.s_no, student.s_name, leaves.info, leaves.status
from student join leaves on
student.s_no=leaves.s_no
order by leaves.status;
```

### 11 在teacher表和coures表上创建名为v_tc的视图,查询所有老师教授的课程
要求：视图保留工号、老师名、课程名,按工号排序
```sql
create view v_tc
as select teacher.t_no, teacher.t_name, course.c_name
from teacher join course on
teacher.t_no=course.t_no
order by teacher.t_no;
```

### 12 创建存储过程p_queryScoreAll,根据学生学号查询该学生的所有课程成绩
```sql
delimiter $$
create procedure p_queryScoreAll(in x char(32))
begin
select student.s_name, score.c_name, score.score
from score join student on
score.s_no=student.s_no
where score.s_no=x;
end $$
delimiter ;
call p_queryScoreAll('192020406');
```

### 13 创建存储过程p_queryScore,根据输入的学生学号和课程名查询该学生成绩
```sql
delimiter $$
create procedure p_queryScore(in x char(32), y char(32))
begin
select student.s_name, score.c_name, score.score
from score join student on
score.s_no=student.s_no
where score.s_no=x and score.c_name=y;
end $$
delimiter ;
call p_queryScore('192020406','数据库原理与应用');
```

### 14 创建存储过程p_addCourse,根据输入的课程名、老师编号、时间、地点、最大人数
要求：往course表中增加一门新课程
```sql
delimiter $$
create procedure p_addCourse(in a char(32), b char(32), t date, p char(32), m int(8))
begin
insert into course(c_name,t_no,date,place,selected_num,max_num) values
(a, b, t, p, 0, m);
end $$
delimiter ;
call p_addCourse('软件测试与质量保证','10004','2020-11-16','2实305',50);
```

### 15 创建存储过程p_addSc,根据输入的学生学号和课程名
要求：往selected_course表中增加一条选课记录,并更新course表中该课程的已选人数
```sql
delimiter $$
create procedure p_addSc(in x char(32), y char(32))
begin
insert into selected_course(s_no, c_name) values(x,y);
update course set selected_num = selected_num+1 where c_name=y;
end $$
delimiter ;
call p_addSc('192020406','软件测试与质量保证');
```

### 16 创建触发器t_updateCName,当更改course表中某门课的课程名时
要求：同时将score表、selected_course表的课程名全部更新
```sql
delimiter $$
create trigger t_updateCName
after update on course for each row
begin
update score set c_name=new.c_name
where c_name=old.c_name;
update selected_course set c_name=new.c_name
where c_name=old.c_name;
end $$
delimiter ;
```

### 17 创建触发器t_updateSNo,当更改student表中某个学生的学号时
要求：同时将score表、selected_course表的学生学号全部更新
```sql
delimiter $$
create trigger t_updateSNo
after update on student for each row
begin
update score set s_no=new.s_no
where s_no=old.s_no;
update selected_course set s_no=new.s_no
where s_no=old.s_no;
end $$
delimiter ;
```

### 18 创建触发器t_updateTNo,当更改teacher表中某个老师的工号时
要求：同时将course表的老师工号全部更新
```sql
delimiter $$
create trigger t_updateTNo
after update on teacher for each row
begin
update course set t_no=new.t_no
where t_no=old.t_no;
end $$
delimiter ;
```

### 19 将student表中的s_no字段定义为主码,即对student表的主键s_no添加唯一约束
```sql
alter table student add unique (s_no);
```

### 20 将course表和teacher表建立参照完整性,course表的外键t_no为teacher表的主键
```sql
alter table course add foreign key (t_no) references teacher(t_no);
```

### 21 成绩表分数限制在0~100之间
```sql
alter table score
add check
(score>=0 and score<=100);
```

### 22 修改数据
```sql
update student set password='66666' where s_name='顾家明';
```

### 23 删除数据
```sql
delete from leaves where s_no='192020406';
```

### 24 创建用户登陆账号和密码
```sql
create user 'testuser'@'localhost' identified by '123456';
```

### 25 授予用户数据库操作权限
```sql
use mydb;
grant select on *.* to 'testuser'@'localhost';
```
### 26 在dos命令行的F盘下导出数据库表格
```sql
cd /d F:
F:\>mysqldump -uroot -p --databases 数据库名 (--tables 表名) > 自定义名称.sql
```

MYSQL数据库原理与应用课程课程设计

总共已有 0 条评论