关于人工智能:MySQL-案例教学管理信息系统

@TOC

一、数据库设计及创立

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

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

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

数据库：StudentScoreDB

学院信息表：CollegeTb

课程信息表：CourseTb

选课信息表：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 图能够更不便的展现数据库构造，请自行理解。

本文由 mdnice 多平台公布