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

一、数据库设计及创建

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

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

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

数据库：StudentScoreDB
字段名含义数据类型宽度studentNo学号字符型10name姓名字符型20gender性别字符型2birthday出生日期日期型——telnumber电话（11位）字符型11college所在学院字符型50grade年级字符型6place籍贯字符型20natiom民族字符型30
学院信息表：CollegeTb
字段名含义数据类型宽度college学院名称字符型40address地址字符型50telnumber办公室电话（格式为4位区号-8位电话号码）字符型13contacts联系人字符型20introduction学院简介字符型50campus所在校区（呈贡/东陆）字符型4
课程信息表：CourseTb
字段名含义数据类型宽度courseNo课程号字符型6courseName课程名称字符型40term开课学期字符型5weekNumber周学时数值型——credit学分数值型——courseType课程性质（选修/必修）字符型5teacher上课教师字符型20
选课信息表：ScoreTb
字段名含义数据类型宽度studentNo学号字符型10courseNo课程号字符型6term开课学期字符型5score成绩数值型——

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

方案：
创建数据库：StudentScoreDB

createdatabaseifnotexists StudentScoreDB 
defaultcharacterset GB2312 
defaultcollate GB2312_chinese_ci;showdatabases;use StudentScoreDB;

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

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

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

创建学院信息表：CollegeTb

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

创建课程信息表：CourseTb

createtable CourseTb (
courseNo  char(6)primarykeycomment'课程号',
courseName   varchar(40)notnullcomment'课程名称',
term  char(5)comment'开课学期',
weekNumber  tinyintcomment'周学时',
credit  tinyintcomment'学分',
courseType   char(4)check(courseType in('选修','必修'))comment'课程性质',  
teacher   varchar(20)comment'上课教师');

创建选课信息表：ScoreTb

createtable ScoreTb (
studentNo   char(10)notnullcomment'学号',
courseNo   char(6)notnullcomment'课程号',
term   char(5)comment'开课学期',
score floatcheck(score>=0and score<=100)comment'成绩',constraint PK_student foreignkey(studentNo)references StudentTb(studentNo),constraint PK_course   foreignkey(courseNo)references CourseTb(courseNo));showtables;

上面代码最后一行

show tables;

为查看该数据库中所有表格。

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

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

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

方案：

showcolumnsfrom StudentTb;insertinto  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数据库程序设计”，请写出代码。

方案：

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

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

方案：

showcolumnsfrom CollegeTb;insertinto  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分。请写出代码。

方案：

insertinto  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 +5where 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='男'orderby studentNo desclimit10;

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

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

方案：

selectavg(score),count(score)from ScoreTb groupby  courseNo;

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

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

方案：

select  s.name, c.courseName, e.score  from ScoreTb as e, CourseTb as c, StudentTb as s
where e.score>=80and 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）在存储学院信息的表上创建一个普通索引，索引字段自选，要求创建基于字段值前三个字符建立降序索引。写出代码。

方案：

createindex  index_college on CollegeTb(college(3)DESC);showindexfrom CollegeTb;

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

方案：

createorreplaceview v_College 
ASselect*from CollegeTb where  campus='呈贡'withcheckoption;showcreateview v_College;

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

方案：

createorreplaceview v_ScoreTb(studentNo ,name ,courseName, score)ASselect  s.studentNo, s.name,   c.courseName, e.score  from StudentTb as s, CourseTb as c, ScoreTb as e 
where  e.studentNo=s.studentNo;showcreateview v_ScoreTb;select*from v_ScoreTb where  courseName='MySQL数据库程序设计';

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

方案：

createtable trigger_CourseTb (
courseNo  char(6)primarykeycomment'课程号',
courseName   varchar(40)notnullcomment'课程名称',
term  char(5)comment'开课学期',
weekNumber  tinyintcomment'周学时',
credit  tinyintcomment'学分',
courseType   char(4)check(courseType in('选修','必修'))comment'课程性质',  
teacher   varchar(20)comment'上课教师');delimiter $$ 
createtrigger trigger_course
before deleteon CourseTb
for each rowbegininsertinto trigger_CourseTb
select*from CourseTb
where courseNo=old.courseNo;end;$$

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

标签： mysql 数据库

本文转载自: https://blog.csdn.net/AdamCY888/article/details/129466366
版权归原作者 高山莫衣 所有，如有侵权，请联系我们删除。

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