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

柔光的暖阳◎ 2023-10-04 23:21 21阅读 0赞

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

*  一、数据库设计及创建
 *  二、向表中插入数据、修改数据
 *  三、完成以下查询设计及SQL代码
 *  四、完成如下数据库应用设计

## 一、数据库设计及创建 ##

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

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

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

数据库：StudentScoreDB

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

学院信息表：CollegeTb

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

课程信息表：CourseTb

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

选课信息表：ScoreTb

*  需求（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<=100) comment '成绩',
    constraint PK_student foreign key (studentNo)  references StudentTb(studentNo),
    constraint PK_course   foreign key (courseNo)   references CourseTb(courseNo)
    ) ;
    
    show tables;

上面代码最后一行`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) <= 
    datediff(CURDATE(),(select birthday from StudentTb where studentNo='2021110105' ))  
    and  college in (select college from StudentTb where studentNo='2021110105' );

*  需求：（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图可以更方便的展示数据库结构，请自行了解。