MySQL数据库综合运用

1、需求设计
学校的日常事务管理涉及很多方面，其中课程管理是一个很重要的方面，比较体系化，流程化，适合使用计算机系统管理，该数据库需求分析如下：
学院有若干班级、教师、学生，一个学院设有若干班级，每个班级有若干名学生；每个学生选修若干门课程，一名教师可教授多门课程。每门课程可有若干名学生选修；每门课可以被安排在不同的教室上课，每门课程有固定的学时修完以后有相应学分，考试通过后可获得学分。
2、数据库设计
2.1确定联系集
根据前面对系统进行的分析，确定有关的各个实体及其相互联系如下：
学生(学号，姓名，性别，所在班级，电话，生日，住址)
教师（教师编号，姓名，职称）
课程（课程号，课程名，学时，学分，授课教师）
班级（班级编号，班级名称）
教室（教室编号，所在位置）
①、标示实体集：班级，课程，学生，教师，教室。
②、标示联系集：
学生和班级：一个班由多个学生组成，一个学生只能归属于某个班；
学生和课程：一个学生可以选修多门课程，一门课程提供给多个学生选修；
教室和班级及课程：一间教室同一时间只能有一个班级上一门课，一间教室可供多个班级上多门课。
③、标示属性集：
学生(学号，姓名，性别，所在班级，电话，生日，住址)
教师（教师编号，姓名，职称）
课程（课程号，课程名，学时，学分，授课教师）
班级（班级编号，班级名称）
教室（教室编号，所在位置）
选课表（学号，课程号，成绩）
排课表（课程号，班级编号，教室编号，上课时间,课号）
3、逻辑数据库设计阶段
逻辑结构是独立于任何一种数据模型的信息结构。逻辑结构的任务是把概念结构设计阶段设计好的基本E-R图转化为宜选用的DBMS所支持的数据模型相符合的逻辑结构，并对其进行优化。
E-R图向关系模型转化要解决的问题是如何将实体型和实体间的联系转化为关系模式，如何确定这些关系模式的属性和码。
设计学生成绩管理数据库，包括学生、教师、课程、班级、教室、选课、排课七个关系, 其关系模式中对每个实体定义的属性如下：

学生(学号，姓名，性别，所在班级，电话，生日，住址)
教师（教师编号，姓名，职称）
课程（课程号，课程名，学时，学分，授课教师）
班级（班级编号，班级名称）
教室（教室编号，所在位置）
选课表（学号，课程号，成绩）
排课表（课程号，班级编号，教室编号，上课时间,课号）。
4、建表
4.1创建数据库

CREATE DATABASE CourseDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci；

4.2创建数据表
1.创建班级表
CREATE TABLE class(
d_no char(8) PRIMARY KEY,
d_name char(12) NOT NULL
)ENGINE=INNODB;

2创建学生表
CREATE TABLE student(
S_no char(12) PRIMARY KEY,
S_name char(4) NOT NULL,
gender char(2) NOT NULL,
d_no char(8),
birthday char(20) NULL,
phone varchar(11) NULL,
address char(20) NULL,
CONSTRAINT FK_1 FOREIGN KEY (d_no) REFERENCES class (d_no)
)ENGINE=INNODB;

3、创建教师表
CREATE TABLE teacher(
t_no char(12) primary key,
t_name char(10) NOT NULL,
t_title varchar(50) NULL
)ENGINE=INNODB;

4创建教室表
CREATE TABLE room(
r_no char(12) primary key,
t_name char(10) NOT NULL,
t_title varchar(50) NULL
)ENGINE=INNODB;

5创建课程表
CREATE TABLE course(
c_no char(12) NOT NULL,
c_name char(10) not NULL,
hours int not NULL,
credit int not NULL,
t_no char(12) references teachar(t_no)
)ENGINE=INNODB;

6创建选课表
CREATE TABLE choose(
c_no char(12) references course(c_no),
s_no char(12) references students(s_no),
score int NULL,
PRIMARY KEY (c_no,s_no)
)ENGINE=INNODB;

7创建排课表
CREATE TABLE arrange(
c_no char(12) references course(c_no),
d_no char(8) references class(d_no),
r_no char(8) references room(r_no),
dt_date date NOT NULL,
kh int not NULL,
primary key(c_no,d_no,r_no,dt_date,kh)
)ENGINE=INNODB;

4.3基本表的删除
DROP table arrange;

5数据操作
1插入数据
INSERT INTO course VALUES (‘1001’,‘高等数学’,90,6,‘t003’);

Mysql修改结束符
DELIMITER #
改回分号
DELIMITER ;
DELIMITER #
INSERT INTO course VALUES (‘1002’,‘英语’,90,6,‘t004’);
INSERT INTO course VALUES (‘1003’,‘计算机基础’,70,4,‘t001’);
INSERT INTO course VALUES (‘1004’,‘数据库运用’,60,4,‘t001’);
INSERT INTO course VALUES (‘1005’,‘会计学’,100,6,‘t002’);
INSERT INTO course VALUES (‘1006’,‘经济学’,80,5,‘t003’);
INSERT INTO course VALUES (‘2001’,‘网页设计’,32,1,‘t001’);#

INSERT class values(‘d001’,‘软件1班’);
INSERT class values(‘d002’,‘软件2班’);
INSERT class values(‘d003’,‘软件3班’);#

INSERT student values(‘s0001’,‘武松’,‘男’,‘d001’,‘1992-10-18’,‘13696332100’,‘梁山’);
INSERT student values(‘s0002’,‘张青’,‘男’,‘d001’,‘1992-10-30’,NULL,‘梁山’);
INSERT student values(‘s0003’,‘孙二娘’,‘女’,‘d002’,‘1992-02-03’,NULL,‘梁山’);
INSERT student values(‘s0004’,‘宋江’,‘男’,‘d002’,‘1985-01-01’,NULL,‘郡城’);#

INSERT teacher values(‘t001’,‘王明’,‘讲师’);
INSERT teacher values(‘t002’,‘李三’,‘教授’);
INSERT teacher values(‘t003’,‘沈电’,‘副教授’);
INSERT teacher values(‘t004’,‘赵四’,‘讲师’);#

insert room values (‘r002’,‘电信系楼203’,‘2’);
insert room values (‘r003’,‘理学院楼809’,‘3’);#

insert choose values (‘1002’,‘s0001’,88);
insert choose values (‘1002’,‘s0002’,66);
insert choose values (‘1002’,‘s0003’,74);
insert choose values (‘1002’,‘s0004’,91);
insert choose values (‘1003’,‘s0002’,59);
insert choose values (‘1003’,‘s0003’,72);
insert choose values (‘1003’,‘s0004’,85);
insert choose values (‘1004’,‘s0003’,60);

insert arrange values(‘1002’,‘d001’,‘r001’,‘2020-12-27’,1);
insert arrange values(‘1003’,‘d001’,‘r001’,‘2020-12-27’,2);
insert arrange values(‘1003’,‘d001’,‘r002’,‘2020-12-27’,3);
insert arrange values(‘1004’,‘d002’,‘r001’,‘2020-12-27’,4);
insert arrange values(‘1005’,‘d002’,‘r001’,‘2020-12-27’,5);#

5.2查询
1单表查询
查询成绩不及格的信息
select *from choose where score<=60;#

2连接查询
查询不及格课程的学生详细信息
select
s_name,
c_name,
score
from choose cs,student s,course c
where cs.c_no=c.c_no and cs.s_no=s.s_no and score<=60;#

查询选课数量超过一门的学生学号和选课数量
select s_no as ‘学号’,count(c_no) as '选课数’from choose group by s_no having count(c_no)>1;#

按职称统计教师人数
select t_title as ‘职称’,count(t_no) as ‘人数’ from teacher group by t_title;#

3操作结果集查询
查询s0002号同学和s0003号同学共同选修的课程
select c1.c_no from choose c1,choose c2
where c1.s_no=‘s0002’ and c2.s_no=‘s0003’
and c1.c_no=c2.c_no;#

4嵌套查询
查询所有选修了‘’英语‘这门课的所有学生的详细信息
select *from student
-> where s_no in (select s_no from choose where c_no=(select c_no from course where c_name=‘英语’));#

5
5.3数据库更新操作
1插入数据
insert student values(‘s0005’,‘李逵’,‘男’,‘d002’,‘1982-10-09’,NULL,‘梁山’);#

2修改数据
update choose set score = score+5 where score<60;#

3删除数据
delete from choose where s_no=(select s_no from student where s_name=‘宋江’);#

5.4为数据库建立索引
1索引建立
CREATE INDEX index_xm ON student(s_name);#

2索引删除
DROP INDEX index_xm ON student;#

5.5存储过程 触发器 事件的创立
在数据库中，创建存储过程pr_id_avg_score，输入学号，显示choose表中该学号的平均成绩，选修课程门数
CREATE PROCEDURE pr_id_avg_score(IN st_id CHAR(10))
reads sql data
comment’显示学号，平均成绩和选修课程门数’
BEGIN
->门数
-> from score where s_no=s_mp
-> group by s_no;
-> end;
-> #
在student表上创建一个触发器，在student表中删除指定学号的学生记录之后，统计student表中改版好的人数，修改class表中的该班号的人数
CREATE TRIGGER tr_student_after_delete AFTER DELETE ON student
FOR EACH ROW
BEGIN
DECLARE n INT DEFAULT 0;
SET n=(SELECT COUNT(*) FROM student WHERE d_no=OLD.d_no);
UPDATE class SET classNUM=n WHERE d_no=OLD.d_no;
END;#

创建一个事件event ev_insert_user30，30秒后启动事件，向class表中插入一行记录
create event ev_insert_user30
-> on schedule at current_timestamp+interval 30 second
-> do
-> insert into class (d_no,d_name)values(‘d005’,‘软件1班’);#

5.6数据库的安全性
所谓计算机系统安全性，是指为计算机系统建立和采取的各种安全保护措施，以保护计算机系统中的硬件，软件及数据，防止其偶然或恶意的原因使系统遭到破坏，数据遭到更改或泄露等。为防止因用户非法使用数据库造成数据泄露、更改或破坏，必须采取一些安全性控制措施以保证数据库中数据的安全、可靠和正确有效。安全性控制的防范对象是非法用户和非法操作，防止他们对数据库数据的非法存取。主要采用用户标识及鉴定，使用用户名或用户标识号来标明用户身份。
用户想要对对数据库内容进行相关操作，必须申请一个用户名或用户标识号，每次登陆都必须输入该用户名以及相应正确的口令，然后系统对其进行验证，只有通过验证的用户方可对数据库进行操作。
5.6数据库的完整性
实体完整性，约束完整性，用户自定义完整性
在每个表上都创建了合理的主键约束
6总结与体会
需求分析阶段，考虑问题不全面，以至于需求分析迟迟做不出来，创建数据表时，为主键和外键费了很大的精力，总是不能得到满意的结果，经过多次查询各种资料，完成这种关系的确立。
在这个数据库的设计过程中，让我对MySQL有了更多的了解，更加熟练的去使用这个数据库，对MySQL语句也有了更多的理解，巩固了知识的理解和运用，养成良好的动手能力，培养一种热爱思考的思维和习惯。