MySQL-J4-2 《学生管理系统》选课管理模块

试题编号：J4-2 《学生管理系统》选课管理模块

（1）任务描述

《选课管理》模块的E-R图如图2.1所示，逻辑数据模型如图2.2所示，物理数据模型如图2.3 所示，数据表字段名定义见表2.1。请按以下设计完成数据库创建、数据表创建和数据操作任务：

图2.1 E-R 图

图2.3 物理数据原型

表2.1 字段名定义表

字段名	字段说明	字段名	字段说明
Stud_id	学号	Reserve	备注
Stud_name	姓名	Course_id	课程编号
Stud_sex	性别	Course_name	课程名称
Birth_date	出生日期	Teacher_name	教师姓名
Entry_Date	入学日期	Score_id	成绩编号
Mobile	手机号码	Score	成绩
Birth_place	籍贯

任务一：创建数据库（10 分）

创建数据库 StudentDB。

任务二：创建数据表（25 分）

根据图2.2、图2.3和表 2.1，创建数据表 T_student、T_course、T_score。

任务三：创建数据表间的关系及约束（15 分）

1.   根据物理数据原型，创建外键约束；

2.   籍贯（birth_place）的默认值为“湖南株洲”；

3.   成绩（score）的默认值为0；

4.   性别（Stud_sex）的默认值为“男”；

5.   教师姓名（Teacher_name）的默认值为“王老师”。

任务四：数据操作（30 分）

用 SQL 语句完成如下操作：

1.   向t_student表插入数据：“1,夏迪,女, 1999-07-23 08:35:29, 2017-09-01 08:35:58, 12345678910,湖南省邵阳市,无”；“2,张明,男, 1999-04-25 09:35:30, 2017-09-01 09:40:40, 13487653456,湖南省长沙市,体育特长生”；“3,刘美,女, 1999-08-15 07:40:10, 2017-09-02 09:30:58, 15786647896,湖南省株洲市,无”；

INSERT INTO t_student VALUES
('1','夏迪','女','1999-07-23 08:35:29','2017-09-01 08:35:58','12345678910','湖南省邵阳市','无'),
('2','张明','男','1999-04-25 09:35:30','2017-09-01 09:40:40','13487653456','湖南省长沙市','体育特长生'),
('3','刘美','女','1999-08-15 07:40:10','2017-09-02 09:30:58','15786647896','湖南省株洲市','无');

2.   向t_course表插入数据：“1, 数据库原理, 王老师”；“2, 日语, 李老师”；“3, 英语, 刘老师”；

INSERT INTO t_course VALUES('1','数据库原理','王老师'),('2','日语','李老师'),('3','英语','刘老师');

3.   向 t_score表插入数据：“1, 1, 1, 95”；“2, 2, 2, 85”；“3, 3, 3, 90”；

INSERT INTO t_score VALUES(1,'1','1',95),(2,'2','2',85),(3,'3','3',90);

4.   查询出备注为“体育特长生”的学生学号、姓名和籍贯；

SELECT Stud_id,Stud_name,Birth_place FROM t_student WHERE Reserve='体育特长生';

5.   查询出“数据库原理”这门课程的最低成绩。

SELECT MIN(Score) FROM t_score,t_course WHERE Course_name='数据库原理';

任务五：数据备份（10 分）

备份数据库，存放在答题文件夹中。

studentdb(备份).sql文件数据

DROP TABLE IF EXISTS `t_course`;
CREATE TABLE `t_course` (
  `Course_id` varchar(10) NOT NULL,
  `Course_name` varchar(100) DEFAULT NULL,
  `Teacher_name` varchar(30) DEFAULT '王老师',
  PRIMARY KEY (`Course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_course` VALUES ('1', '数据库原理', '王老师');
INSERT INTO `t_course` VALUES ('2', '日语', '李老师');
INSERT INTO `t_course` VALUES ('3', '英语', '刘老师');

DROP TABLE IF EXISTS `t_score`;
CREATE TABLE `t_score` (
  `Score_id` bigint(20) NOT NULL,
  `Course_id` varchar(10) DEFAULT NULL,
  `Stud_id` varchar(10) DEFAULT NULL,
  `Score` decimal(5,2) DEFAULT '0.00',
  PRIMARY KEY (`Score_id`),
  KEY `fk1` (`Course_id`),
  KEY `fk2` (`Stud_id`),
  CONSTRAINT `fk1` FOREIGN KEY (`Course_id`) REFERENCES `t_course` (`Course_id`),
  CONSTRAINT `fk2` FOREIGN KEY (`Stud_id`) REFERENCES `t_student` (`Stud_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_score` VALUES ('1', '1', '1', '95.00');
INSERT INTO `t_score` VALUES ('2', '2', '2', '85.00');
INSERT INTO `t_score` VALUES ('3', '3', '3', '90.00');

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `Stud_id` varchar(10) NOT NULL,
  `Stud_name` varchar(20) DEFAULT NULL,
  `Stud_sex` char(2) DEFAULT '男',
  `Birth_date` datetime DEFAULT NULL,
  `Entry_Date` datetime DEFAULT NULL,
  `Mobile` varchar(11) DEFAULT NULL,
  `Birth_place` varchar(25) DEFAULT '湖南株洲',
  `Reserve` text,
  PRIMARY KEY (`Stud_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_student` VALUES ('1', '夏迪', '女', '1999-07-23 08:35:29', '2017-09-01 08:35:58', '12345678910', '湖南省邵阳市', '无');
INSERT INTO `t_student` VALUES ('2', '张明', '男', '1999-04-25 09:35:30', '2017-09-01 09:40:40', '13487653456', '湖南省长沙市', '体育特长生');
INSERT INTO `t_student` VALUES ('3', '刘美', '女', '1999-08-15 07:40:10', '2017-09-02 09:30:58', '15786647896', '湖南省株洲市', '无');