【Mysql】【基础综合题解】大一下学期数据库综合训练题解

一、创建数据库

问题

使用SQL语句或者Navicat工具创建一个学生管理系统数据库，要求：数据库名为 myschool+学号最后两位，例如01号同学创建的数据库名应为myschool01。（试一试写出UTF8的和GBK的字节集）。

代码

create database myschool01;

GBK

create database myschool01 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

UTF8

create database myschool01 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

二、创建数据表

问题

创建数据表，表的结构如下图表所示：

表1 student（学生信息表）

字段名 字段说明 字段类型 长度 属性
Snumber 学号 varchar 10 非空
Sname 姓名 varchar 20 非空
sex 性别 char 2 非空
sid 身份证号码 varchar 20 非空
birthday 出生日期 datetime 允许空
address 住址 varchar 30 允许空

表2 course（课程信息表）

字段名 字段说明 字段类型 长度 属性
Cnumber 课程号 varchar 20 非空
Cname 课程名称 varchar 20 非空
hours 学时 int 允许空
credit 学分 decimal(3,1) 允许空

表3 score（学生成绩表）

字段名 字段说明 字段类型 长度 属性
Snumber 学号 varchar 10 非空
Cnumber 课程号 varchar 20 非空
total 成绩 decimal(4,1) 非空

代码

CREATE TABLE `student`  (

  `snumber` varchar(10)  NOT NULL,

  `sname` varchar(20)  NOT NULL,

  `sex` char(2) NOT NULL,

  `sid` varchar(20) NULL DEFAULT NULL,

  `birthday` datetime NULL DEFAULT NULL,

  `address` varchar(30)  NULL DEFAULT NULL

);



CREATE TABLE `course`  (

  `cnumber` varchar(20) NOT NULL,

  `cname` varchar(20)  NOT NULL,

  `hours` int(11) NULL DEFAULT NULL,

  `credit` decimal(3, 1) NULL DEFAULT NULL,

);



CREATE TABLE `score`  (

  `snumber` varchar(10) NOT NULL,

  `cnumber` varchar(20) NOT NULL,

  `total` decimal(4, 1) NOT NULL

);

三、修改表和添加约束

问题

1、 使用SQL语句，为student添加national（民族）字段，char(10)，允许为空。

2、 使用SQL语句，将student表的birthday字段的字段类型改为date。

3、 分析以上三张表，确定三张表的主键，分别为三张表添加主键约束。

4、 请为student表添加默认约束DF_student_national和唯一约束IX_sid。（说明：民族默认值为“汉族”；身份证为唯一值）。

5、 请为score表添加外键约束FK_snumber、FK_cnumber。

代码

-- 第一空： 
alter table student add national char(10) null;

-- 第二空： 
alter table student modify birthday date;

-- 第三空： 
alter table student add primary key(snumber);

alter table course add primary key(cnumber);

alter table score add primary key(snumber,cnumber);



-- 第四空： 
-- 修改默认值

alter table student modify national char(10) CHARACTER set utf8;

alter table student alter national set default '汉';



-- 添加唯一约束

alter table student modify sid varchar(20) unique;

-- 或者

alter table student add unique(sid);

-- 删除唯一约束 alter table student drop index sid



-- 第五空： 
alter table score add CONSTRAINT FK_snumber FOREIGN key(snumber) references student(snumber);

alter table score add CONSTRAINT FK_cnumber FOREIGN key(cnumber) references course(cnumber);

四、添加数据

问题

请使用SQL语句为三张表添加以下数据。

student表数据

Snumber Sname sex sid birthday address national
0102202001 朱三贵 男 440121199911022354 1999-11-1 21栋207 壮族
0102202002 夏怡芳 男 440121200010022224 2000-10-2 21栋209 彝族
0102202003 周敏浩 女 440223199911280026 1999-11-28 20栋606 汉族
0102202004 李萌萌 女 440222199909281124 1999-9-28 20栋606 回族

course表数据

Cnumber Cname hours credit
001 java面向对象程序设计 54 3
002 应用数学1 74 4
003 大学英语 54 3
004 数据库应用基础 54 3
005 网页设计基础 72 4

score表数据

Snumber Cnumber total
0102202001 001 90
0102202001 002 85
0102202001 003 88
0102202002 001 85
0102202002 003 96
0102202003 002 88
0102202004 001 78
0102202004 002 90

代码

-- 第一空： 
INSERT INTO `student` VALUES ('0102202001', '朱三贵', '男', '440121199911022354', '1999-11-01', '21栋207', '壮族');

INSERT INTO `student` VALUES ('0102202002', '夏怡芳', '男', '440121200010022224', '2000-10-02', '21栋209', '彝族');

INSERT INTO `student` VALUES ('0102202003', '周敏浩', '女', '440223199911280026', '1999-11-28', '20栋606', '汉族');

INSERT INTO `student` VALUES ('0102202004', '李萌萌', '女', '440222199909281124', '1999-09-28', '20栋606', '回族');



-- 第二空： 
INSERT INTO `course` VALUES ('001', 'java面向对象程序设计', 54, 3.0);

INSERT INTO `course` VALUES ('002', '应用数学1', 74, 4.0);

INSERT INTO `course` VALUES ('003', '大学英语', 54, 3.0);

INSERT INTO `course` VALUES ('004', '数据库应用基础', 54, 3.0);

INSERT INTO `course` VALUES ('005', '网页设计基础', 72, 4.0);



-- 第三空： 
INSERT INTO `score` VALUES ('0102202001', '001', 90.0);

INSERT INTO `score` VALUES ('0102202001', '002', 85.0);

INSERT INTO `score` VALUES ('0102202001', '003', 88.0);

INSERT INTO `score` VALUES ('0102202002', '001', 85.0);

INSERT INTO `score` VALUES ('0102202002', '003', 96.0);

INSERT INTO `score` VALUES ('0102202003', '002', 88.0);

INSERT INTO `score` VALUES ('0102202004', '001', 78.0);

INSERT INTO `score` VALUES ('0102202004', '002', 90.0);

五、数据导出

问题

把student表的数据导出到txt文件（路径任意），设置字段间用逗号隔开，字段的值用“”括起来，每一行记录都换行。

代码

select * from myschool01.student 

into outfile 'C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\bak_myschool_student.txt' character set gbk

fields

TERMINATED BY ','

ENCLOSED BY '"'

lines

TERMINATED BY '
';

------------------- 或者 ---------------------------

mysqldump -u root -p -T "C:\ProgramData\MySQL\MySQL Server 5.7\Uploads" myschool01 student --fields-terminated-by=,  --fields-optionally-enclosed-by="  --lines-terminated-by=

六、SQL语句

问题

1、创建视图v_stuInfo，显示所有学生的学号、课程名称和成绩（只返回前5行记录）。

2、查询与“周敏浩”同性别的同学，显示姓名和性别。

3、查询所有课程的总学分和总学时数。

4、查询参加了课程号为“001”或“002”考试的学生姓名。

5、查询每个学生共修了多少学分。

6、删除学号为“0102202004”的学生成绩。

7、为course表的字段“cname”创建索引idx_CName，该索引为唯一索引。

代码

-- 第一空： 
create view v_stuinfo 

as

select student.snumber,cname,total

from student,course,score

where student.snumber = score.snumber and course.cnumber = score.cnumber

limit 5



-- 第二空： 
select sname,sex

from student 

where sex = (select sex from student where sname = '周敏浩');



-- 第三空： 
select sum(credit),sum(hours)

from course;



-- 第四空： 
select sname 

from student where snumber in(select distinct snumber from score where cnumber = '001' or cnumber = '002');



-- 第五空： 
select student.snumber,sname,sum(credit)

from student,score,course

where student.snumber = score.snumber and score.cnumber = course.cnumber

group by student.snumber;



-- 第六空： 
delete from score where snumber = '0102202004';

-- 第七空： 
create unique index idx_cname on course(cname);

七、存储过程

问题

创建一个带输出参数的存储过程p_countNum，该存储过程能根据给定的课程编号统计参加该课程考试的学生人数，并将学生人数返回给用户；执行该存储过程，输出参加课程编号为“002”考试的学生人数。

代码

create procedure p_countNum(in cnumber_temp varchar(20), out snumber int)

begin

select count(*) from score where cnumber=cnumber_temp;

end;

-- 执行p_countNum

set @snumber=0;

call p_countNum('002', @snumber);

八、触发器

问题

创建一个触发器trig_update_course，实现课程表中的学时与学分同步修改。要求如下：

当向course表中修改《数据库应用基础》课程的学时（hours）后，触发器被触发更新相应的学分（credit）。一般情况下，16学时为1学分，32学时为2学分，54学时为3学分，72学时为4学分。

代码

  1. MySQL中触发器中不能对本表进行 insert ,update ,delete操作，以免递归循环触发
  2. 对于update 只能用set进行操作，insert与delete只能借助第二张表才能实现需要的目的
    同表的更新不能在触发器里使用 update,而是直接使用set
    BEFORE与AFTER区别：
    BEFORE：(insert、update)可以对new进行修改，AFTER不能对new进行修改，两者都不能修改old数据。
    对于INSERT语句, 只有NEW是合法的；
    对于DELETE语句，只有OLD才合法；
    对于UPDATE语句，NEW、OLD可以同时使用。
delimiter//
CREATE TRIGGER trig_update_course
BEFORE UPDATE ON course FOR EACH ROW
BEGIN
 IF new.hours != old.hours THEN
 BEGIN
 CASE 
 WHEN new.hours=16 THEN SET new.credit=1;
 WHEN new.hours=32 THEN SET new.credit=2;
 WHEN new.hours=54 THEN SET new.credit=3;
 WHEN new.hours=72 THEN SET new.credit=4;
 END CASE;
 END;
 END IF;
END//

UPDATE course SET hours=16 WHERE Cname='数据库应用基础';
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/mudongyousi/p/13280031.html