MySQL 案例-教学管理信息系统

@TOC

一、数据库设计及创建

对教学管理信息系统，在需求分析阶段，收集到以下信息。
学生的信息：学生的学号、姓名、性别、出生日期、电话（11位）、所在学院、年级、籍贯、民族；学院的信息：学院名称、地址、办公室电话（格式为4位区号-8位电话号码）、联系人、学院简介、所在校区（呈贡/东陆）；课程信息：课程号、课程名称、开课学期、周学时、学分、课程性质（选修/必修）、上课教师。此外还有每个学生选修课程的成绩信息。

• 需求：（1）根据以上信息，请各个同学独立设计一个教学管理数据库，用于存储学生信息、课程信息、学生选课信息及学生所在的学院信息。请用表格的方式，列出你所设计的数据库中各表的字段名，含义，数据类型，宽度。要求：数据库名称、表名称及字段名称均用英文字符定义。

以下为需求（1）解决方案

数据库：StudentScoreDB

字段名	含义	数据类型	宽度
studentNo	学号	字符型	10
name	姓名	字符型	20
gender	性别	字符型	2
birthday	出生日期	日期型	——
telnumber	电话（11位）	字符型	11
college	所在学院	字符型	50
grade	年级	字符型	6
place	籍贯	字符型	20
natiom	民族	字符型	30

学院信息表：CollegeTb

字段名	含义	数据类型	宽度
college	学院名称	字符型	40
address	地址	字符型	50
telnumber	办公室电话（格式为4位区号-8位电话号码）	字符型	13
contacts	联系人	字符型	20
introduction	学院简介	字符型	50
campus	所在校区（呈贡/东陆）	字符型	4

课程信息表：CourseTb

字段名	含义	数据类型	宽度
courseNo	课程号	字符型	6
courseName	课程名称	字符型	40
term	开课学期	字符型	5
weekNumber	周学时	数值型	——
credit	学分	数值型	——
courseType	课程性质（选修/必修）	字符型	5
teacher	上课教师	字符型	20

选课信息表：ScoreTb

字段名	含义	数据类型	宽度
studentNo	学号	字符型	10
courseNo	课程号	字符型	6
term	开课学期	字符型	5
score	成绩	数值型	——

• 需求（2）请写出创建数据库的SQL语句代码。要求指定字符集为简体中文，指定字符集的校对规则，使数据库能够处理中文。

方案：
创建数据库：StudentScoreDB

create database if not exists StudentScoreDB 
default character set GB2312 
default collate GB2312_chinese_ci;
show databases;
use StudentScoreDB;

• 需求（3）请写出创建数据库中各表的SQL语句代码。要求以下5点：
  ①为表定义字段，字段名称用英文字符，为字段选择合适的数据类型（宽度）。
  ②为各表定义合适的主键约束。
  ③为表定义合适的外键，并定义参照完整性约束。
  ④为存储课程信息的表设计一个合理的用户自定义约束。
  ⑤设置存储学生信息的表的学院字段的默认值为你所在的学院。

方案：
创建学生信息表：StudentTb

create table StudentTb (
studentNo    char(10)  primary key  comment '学号', 
name  varchar(20)   not null  comment '姓名',
gender char(2)  not null check(gender in ('男','女'))  comment '性别', 
birthday  date  comment '出生日期',
telnumber char(11)   comment '电话',
college  varchar(50)  default '工商管理与旅游管理'  comment '所在学院',
grade  char(6)  comment '年级',
place varchar(20) comment '籍贯',
natiom  varchar(30) default '汉'  comment '民族'
) ;

创建学院信息表：CollegeTb

create table CollegeTb (
college varchar(40)   primary key  comment '学院名称', 
address  varchar(50)   comment '地址',
telnumber  char(13)  comment '办公室电话',
contacts  varchar(20)    comment '联系人', 
introduction  varchar(50)  comment '学院简介', 
campus  char(4) check(campus in ('呈贡','东陆')) comment '所在校区'
) ;

创建课程信息表：CourseTb

create table CourseTb (
courseNo  char(6)  primary key   comment '课程号',
courseName   varchar(40)  not null comment '课程名称',
term  char(5)  comment '开课学期',
weekNumber  tinyint  comment '周学时',
credit  tinyint  comment '学分',
courseType   char(4)  check(courseType in ('选修', '必修'))  comment '课程性质',  
teacher   varchar(20)    comment '上课教师'
);

创建选课信息表：ScoreTb

create table ScoreTb (
studentNo   char(10)  not null   comment '学号',
courseNo   char(6) not null  comment '课程号',
term   char(5)  comment '开课学期',
score float check(score>=0  and score

上面代码最后一行show tables;为查看该数据库中所有表格。

二、向表中插入数据、修改数据

根据前面设计的数据库和表，向表中插入数据。

• 需求（1）向存储学生信息的表中插入你自己的信息。并设计至少三条记录，插入到存储学生信息的表中。请写出代码。

方案：

show columns from StudentTb;

insert into  StudentTb  values
('2018110101', '张晓勇', '男', '1997-12-11', '15736411111',  '信息学院', '2018级','北京','汉'),
('2017110102', '赵婷婷', '女', '1996-11-17',  '13624722222',  '信息学院', '2017级', '上海', '汉'),
('2022110103', '王一敏', '女', '2000-01-02',  '18875033333', '新闻学院', '2022级','云南','苗'),
('2022110104', '李明',    '男', '2001-06-21',  '19925644444', '建规学院', '2022级','贵州','维吾尔'),
('2022040231', '白浩云 ', '男', '2003-08-01',   '13848655555', '工商管理与旅游管理 ', '2022级','云南','傈僳' );

select * from StudentTb;

• 需求：（2）请自定义两条以上的记录信息，一次性插入到存储课程信息的表中，要求只插入两个字段的值，并且其中一门课程为“MySQL数据库程序设计”，请写出代码。

方案：

show columns from CourseTb;

insert into  CourseTb(courseNo, courseName)  values
('202201' ,'MySQL数据库程序设计'),
('202202' ,'Python程序设计'),
('202203' ,'Java程序设计'),
('202204' ,'PHP程序设计'),
('202205' ,'HTML网页设计');

select * from CourseTb;

• 需求（3）请自定义两条以上的记录信息，一次性插入所有字段到存储学院信息的表中。要求其中一个学院为你所在的学院，并设计其中某字段的值空缺。请写出代码。

方案：

show columns from CollegeTb;

insert into  CollegeTb(college,address, telnumber, introduction, campus)  values
('信息学院', '云大呈贡1号','1111-11111111','信息与技术学院简介：...','呈贡'),
('工商管理与旅游管理学院', '云大呈贡2号','2222-22222222','工商管理与旅游管理简介：...','呈贡'),
('新闻学院', '云大呈贡3号','3333-33333333','新闻与传播学院简介：...','呈贡'),
('文学学院', '云大东陆1号','4444-44444444','文学学院简介：....','东陆'),
('建规学院', '云大呈贡1号','5555-55555555','建设与规划学院简介：....','呈贡');

select * from CollegeTb;

假定前面设计的数据库各表中已存储了数据。

• 需求（4）假定学院信息已存储在相应表中，若要将你所在的学院的联系人修改为你自己。请写出修改代码。

方案：

update CollegeTb set contacts='白浩云' where college='工商管理与旅游管理学院';

select * from CollegeTb;

• 需求：（5）若要将你的“MySQL数据库程序设计”这门课程的成绩提高5分。请写出代码。

方案：

insert into  ScoreTb  values
('2018110101','202201', '20222',70),
('2017110102','202201', '20222',80),
('2022110103','202201', '20222',65),
('2022110104','202204', '20222',88),
('2021110105','202202', '20222',78);

select * from ScoreTb;

update  ScoreTb set score = score + 5 
where courseNo=(select courseNo from CourseTb where courseName='MySQL数据库程序设计');

select * from ScoreTb;

三、完成以下查询设计及SQL代码

假定前面设计的数据库各表中已存储了数据。

• 需求（1）请设计一个带AND和OR的多条件查询，并将查询结果排序（降序），只要求输出前10条记录。写出所设计的查询要求，并写出代码。

答：order by排序列尽可能的选择数值，更具有实际意义。
方案：

select  studentNo,name, gender, birthday from StudentTb 
where grade= '2017级'  or  natiom='汉'  and gender='男'   
order by studentNo desc limit 10;

• 需求：（2）请设计一个分组聚合的查询，要求分组后进行计算，并采用合适的方式输出计算结果。写出所设计的查询要求，并写出代码。

答：所设计的查询要求：select、from、where、group by、having、order by、limit 以上语法顺序是不能前后互换的，否则报错。比如我们不能在 group by 之后添加 where 查询语句，否则会出现错误。

方案：

select avg(score), count(score)  from ScoreTb group by  courseNo;

• 需求：（3）请设计一个涉及三张表的连接查询。写出所设计的查询要求并分析设计的查询思路，最后写出SQL代码。

答：查询期末分数在80分以上的学生姓名以及课程名称和分数;
思路：从选课信息表出发，设置连接条件，学号连接到学生信息表。课程号连接到课程信息表。

方案：

select  s.name, c.courseName, e.score  from ScoreTb as e, CourseTb as c, StudentTb as s
where e.score>=80  and e.studentNo=s.studentNo and e.courseNo=c.courseNo;

• 需求：（4）如果要在数据库中查找所有比你所在的学院的所有同学年龄小的同学，该如何设计查询，请写出代码。

方案：

select * from StudentTb  
where  datediff(CURDATE(),birthday) 

• 需求：（5）在数据库中基于存储学院信息的表创建一个查询，要求查找与你所在的学院在同一个校区的所有学院信息。写出代码。

方案：

select * from  CollegeTb  
where campus in (select campus from CollegeTb 
where college= (select college from StudentTb where studentNo='2021110105'));

四、完成如下数据库应用设计

假定前面设计的数据库各表中已存储了数据，完成以下要求。

• 需求：（1）在存储学院信息的表上创建一个普通索引，索引字段自选，要求创建基于字段值前三个字符建立降序索引。写出代码。

方案：

create index  index_college on CollegeTb(college(3) DESC);

show index from CollegeTb;

• 需求：（2）在数据库中基于存储学院信息的表创建一个视图，名称自拟，要求该视图包含所有呈贡校区的学院信息，并且保证今后对该视图数据的修改都有必须符合校区为“呈贡”这个条件。请写创建视图的代码。

方案：

create or replace  view v_College 
AS 
select  *  from CollegeTb where  campus='呈贡' 
with check option;

show create view v_College;

• 需求：（3）将所有选修了课程的同学的课程及成绩信息定义为一个视图，名称自定，然后从该视图中检索选修了“MySQL数据库程序设计”这门课程的学生信息。请写出完成要求的SQL语句。

方案：

create or replace  view v_ScoreTb(studentNo ,name ,courseName, score)
AS 
select  s.studentNo, s.name,   c.courseName, e.score  from StudentTb as s, CourseTb as c, ScoreTb as e 
where  e.studentNo=s.studentNo;

show create view v_ScoreTb;

select * from v_ScoreTb where  courseName='MySQL数据库程序设计';

• 需求：（4）基于存储课程信息的表创建一个触发器，要求每次从该表删除一行数据时，将删除的数据存入另一张表中（假定表已存在，表结构和存储课程信息的表相同）。

方案：

create table trigger_CourseTb (
courseNo  char(6)  primary key   comment '课程号',
courseName   varchar(40)  not null comment '课程名称',
term  char(5)  comment '开课学期',
weekNumber  tinyint  comment '周学时',
credit  tinyint  comment '学分',
courseType   char(4)  check(courseType in ('选修', '必修'))  comment '课程性质',  
teacher   varchar(20)    comment '上课教师'
);

delimiter $$ 
create trigger trigger_course
before delete on CourseTb
for each row
begin
insert into trigger_CourseTb
select * 
from CourseTb
where courseNo=old.courseNo;
end;$$

用ER图可以更方便的展示数据库结构，请自行了解。

本文由mdnice多平台发布

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net