自考上海交通大学

2024-07-14 00:51| 来源: 网络整理| 查看: 265

04736数据库系统原理（实践）考试创建数据库建立一个名为School的数据库创建表的SQL语句。创建学生表创建班级表创建专业表数据铺底更新表查看表信息各种操作数据库授权其他操作游标视图总结

创建数据库建立一个名为School的数据库 create database school; -- 切换数据库 use school;

注：

drop database if exists school; create database if not exists school; create database if not exists school default character set utf8 collate utf8_general_ci; 创建表的SQL语句。创建学生表 -- auto_increment 自增属性 create table studentinfo ( studentId varchar(20) primary key comment '学生编号'， studentName varchar(50) comment '学生姓名', studentSex varchar(10) comment '学生性别', studentAge int comment '学生年龄', studentMark double comment '学生成绩', entranceDate date comment '入学时间', classId varchar(20) comment '班级编号' )engine=InnoDB default charset=utf8; 创建班级表 create table classinfo ( classId varchar(20) primary key comment '班级编号', classNumber int comment '班级人数', majorId varchar(20) comment '专业编号' )engine=InnoDB default charset=utf8; 创建专业表 create table majorinfo ( majorId varchar(20) primary key comment '专业编号', major varchar(50) comment '专业名称' )engine=InnoDB default charset=utf8; 数据铺底

为方便后面验证，初始化部分数据

INSERT INTO studentinfo (studentId, studentName, studentSex, studentAge, studentMark, entranceDate, classId) values ('S001', '张三', '男', 18, 35.0, '2010-01-01', 'C001'), ('S002', '张三01', '男', 19, 68.0, '2011-01-01', 'C001'), ('S003', '张三03', '男', 20, 90.0, '2013-02-01', 'C001'), ('S004', '张三04', '女', 23, 45.0, '2014-02-01', 'C001'), ('S005', '张三05', '男', 23, 300.0, '2011-01-01', 'C002'), ('S006', '张三06', '女', 34, 67.0, '2015-01-01', 'C002'), ('S007', '张三08', '男', 47, 45.0, '2013-01-01', 'C002'), ('S008', '张三09', '男', 22, 78.0, '2018-01-01', 'C002'), ('S009', '张三10', '男', 33, 24.0, '2012-01-01', 'C002'), ('S010', '张三11', '女', 10, 99.0, '2015-01-01', 'C002'), ('S011', '张三12', '女', 14, 45.0, '2017-01-01', 'C002'), ('S012', '张三13', '男', 25, 68.0, '2013-01-01', 'C002'), ('S013', '张三14', '男', 26, 78.0, '2010-01-01', 'C002'), ('S014', '李三141', '男', 23, 23.0, '2011-01-01', 'C003'), ('S015', '张三15', '女', 28, 67.0, '2015-02-01', 'C003'), ('S016', '李三16', '男', 42, 45.0, '2013-04-01', 'C003'), ('S017', '李三17', '男', 22, 78.0, '2018-05-01', 'C003'), ('S018', '张三18', '男', 22, 24.0, '2012-06-01', 'C003'), ('S019', '张三19', '女', 23, 99.0, '2015-07-01', 'C004'), ('S02', '李三22', '男', 66, 78.0, '2010-02-01', 'C004'), ('S020', '张三20', '女', 11, 45.0, '2017-08-01', 'C004'), ('S021', '张三21', '男', 27, 102.0, '2013-09-01', 'C004'), ('S022', '李四02', '男', 25, 28.0, '2014-09-01', 'G0208'), ('S023', '李四03', '男', 22, 65.0, '2015-09-01', 'G0208'), ('S024', '李四04', '男', 37, 68.0, '2013-09-01', 'G0208'), ('S025', '李四05', '男', 23, 91.0, '2012-09-01', 'G0208'), ('S026', '李四06', '男', 18, 44.0, '2011-09-01', 'G0208'), ('S027', '李四07', '男', 25, 92.0, '2012-09-01', 'G0208'), ('S126', '李四30', '男', 20, 94.0, '2013-09-01', 'C004'), ('S127', '李四29', '男', 20, 94.0, '2013-09-01', 'C004'), ('S128', '李四28', '男', 20, 94.0, '2013-09-01', 'C004'), ('S129', '李四27', '男', 20, 94.0, '2013-09-01', 'C004'), ('S130', '李四26', '男', 20, 94.0, '2017-09-01', 'C004'), ('S131', '李四25', '男', 20, 94.0, '2016-09-01', 'C004'), ('S132', '李四24', '男', 20, 94.0, '2015-09-01', 'C004'), ('S133', '李四23', '男', 20, 94.0, '2014-09-01', 'C004'), ('S134', '李四22', '男', 20, 94.0, '2013-09-01', 'C004'), ('S135', '李四21', '男', 20, 94.0, '2013-09-01', 'C004'); INSERT INTO classinfo(classId, classNumber, majorId) VALUES ('C001', 0, 'M001'), ('C002', 0, 'M001'), ('C003', 0, 'M002'), ('C004', 0, 'M002'), ('G0208', 0, 'M002'); INSERT INTO majorinfo(majorId, major) VALUES ('M001', '信息化综合部'), ('M002', '信息管理与信息系统'); 更新表 -- 往studentinfo表里新增一个studentAddress字段,位于studentMark字段后面 alter table studentinfo add column studentAddress varchar(100) default 'XXX' after studentMark; -- change：修改字段名称和类型 -- alter: 修改或删除字段的默认值 -- modify: 修改字段类型 alter table studentinfo [change|alter] column studentSex sex varchar(5) default 'F'; --drop：删除字段 alter table studentinfo drop column studentAddress; --rename [to] 为表重新赋予一个表名 alter table studentinfo rename to studentInfo; -- 重命名表 rename table studentInfo to studentinfo; 查看表信息 -- 查看表名 show tables; -- 显示指定表结构 show columns studentinfo; 各种操作写出创建studentinfo表的Sg!语句。(要求写出约束相关语句) -- 给StudentInfo表的ClassId字段创建一个外键约束。 alter table studentinfo add foreign key s_cid_c_id(classId) references classinfo(classId); -- 这里需要记住约束的语法：add constraint 约束名称约束(约束字段) ...... -- mysql 会忽略check? alter table studentinfo add constraint chk_mark check(studentAge>18); -- 给StudentInfo创建一个check约束，设置成绩在0-100分之间。 alter table studentinfo add constraint chk_mark check(studentMark>=0 and studentMark '2001-01-01'；查询专业名称（Major）为‘信息管理与信息系统’的所有男生学生姓名、年龄及成绩、班级编号及专业名称，输出按照年龄从小到大排列。 select stu.studentName,stu.studentAge,stu.studentMark,stu.classId,ma.major inner join classinfo cls on cls.classId = stu.classId inner join major ma on ma.majorId = cls.majorId where stu.studentSex='男' and ma.major='信息管理与信息系统' order by stu.studentAge asc; 查询班级编号（ClassId）为‘G0208’的小于该班级平均分的学生姓名、年龄和成绩信息。 select studentName,studentAge,studentMark from studentinfo where classId='G0208' and studentMark 90 group by classId having count(studentId) > 10 ;

6、对每个班级，求该班学生的人数，并将结果存入到该班级信息表中的班级人数字段中。（使用Update语句）

update classinfo set classNamber = ( select count(classId) from studentinfo where classinfo.classId = studentinfo.classId group by studentinfo.classId );

7、将所有专业名称为“信息管理与信息系统”的年龄大于30的学生成绩加上2%。

update studentinfo stu set stu.studentMark = stu.studentMark*1.02 where stu.studentAge > 30 and stu.classId in ( select cls.classId from classinfo cls inner join majorinfo ma on ma.majorId = cls.classId where ma.major='信息管理与信息系统' );

8、写出查询如下结果的SQL语句。 ![[images/Pasted image 20230419181351.png]]

select stu.studentSex as '性别', sum(case when stu.studentMark >= 60 then 1 else 0 end) as '及格', sum(case when sti.studentMark 90 then signal sqlstate 'HY000' set MESSAGE_TEXT='大于90分的学生不允许修改成绩'; end if; end;

10、编写触发器，年龄小于30岁的学生不允许被删除。

drop trigger if exists tr_student_age_delete; create trigger tr_student_age_delete before delete on studentinfo for each row begin if studentinfo.studengAge = startDate and entranceDate

【本文地址】

自考上海交通大学

自考上海交通大学

今日新闻

推荐新闻