数据库系统设计——实验一
polarDB云数据库创建和数据操作
目的和意义
熟悉阿里巴巴的polarDB云数据库产品的使用，通过对比，思考云数据库的先进性。学会基本的DDL和DML使用方法，学会主键的定义和外键的识别，学会数据库访问工具的使用，掌握数据库的基本操作，学会数据库的创建。
实验内容
在阿里云注册一个实名账号，和登录polarDB云数据，创建数据库用户，给用户赋权，登录数据库服务器，创建数据库，创建表，定义主键和外键，修改表的Schema；往表中添加数据，查询表中的数据，修改表中数据，删除表中数据。
阿里云平台中DMS数据库实验室的进入
-
在[阿里云开发者社区]（https://developer.aliyun.com/）, 免费注册一个实名认证的云账号;
-
打开DMS数据库实验室，进入的界面如下：
![]()
-
DMS是Database Management Service的缩写。点击界面中间的免费开通数据库栏中的第一项“polarDB-MySQL”的【立即开通】。即进入polarDB-MySQL数据库。界面左侧背景色为黑色的框中为数据库服务器实例的目录，双击polarDB for MySQL这一项，就可看到该实例下有一个名为 polardb_mysql_9051uav的数据库，再双击该数据库，右侧就会显示控制台。接下来就可输入SQL语句，并提交执行。
-
DMS数据库实验室入门介绍请看：https://help.aliyun.com/document_detail/172126.html?spm=5176.10695662.1996646101.searchclickresult.3e8f6cf92FluCp
-
DMS其实就是一个数据库管理工具。
具体任务
建表
写出教务管理数据库中如下8张表的DDL语句， 要求标识出每张表的主键，包含的外键，明确每个字段的域定义。然后在数据库中创建出这8张表来。
建表内容
其中学号的构成为：yyyyddsscnn。
其中yyyy为入学年级，dd为所在学院编号，ss为专业代码，c为班号，nn为序号。
建表语句
/*
author : haonan
Date: 18/11/2020 20:25:49
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cno` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程编号',
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程名称',
`spno` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '专业代码（外键）',
`ctno` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '课程类型编号（外键）',
`hours` smallint NULL DEFAULT NULL COMMENT '实验时数',
`experiment` smallint NULL DEFAULT NULL COMMENT ' 授课学时数',
`credit` smallint NULL DEFAULT NULL COMMENT '课程学分',
`semester` smallint NULL DEFAULT NULL COMMENT ' 开课学期',
`duty_tno` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' 课程责任老师的工号',
`dno` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' 负责开课的学院编号',
PRIMARY KEY (`cno`) USING BTREE,
INDEX `ctno`(`ctno`) USING BTREE,
INDEX `spno`(`spno`) USING BTREE,
CONSTRAINT `ctno` FOREIGN KEY (`ctno`) REFERENCES `coursetype` (`ctno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `spno` FOREIGN KEY (`spno`) REFERENCES `speciality` (`spno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for coursetype
-- ----------------------------
DROP TABLE IF EXISTS `coursetype`;
CREATE TABLE `coursetype` (
`ctno` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '类型编号',
`ctname` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '类型名称',
PRIMARY KEY (`ctno`) USING BTREE,
INDEX `ctno`(`ctno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`dno` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学院编号',
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学院名称',
`dean` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '院长（外键：引用教师表的主键）',
`addr` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '院系办公地址',
`phone` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系电话',
PRIMARY KEY (`dno`) USING BTREE,
INDEX `dean`(`dean`) USING BTREE,
CONSTRAINT `dean` FOREIGN KEY (`dean`) REFERENCES `teacher` (`tno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for enroll
-- ----------------------------
DROP TABLE IF EXISTS `enroll`;
CREATE TABLE `enroll` (
`sno` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学生学号',
`cno` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程编号',
`year_semeste` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学年学期',
`tno` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '教师编号（外键）',
`classno` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '上课的班级号',
`score` float NULL DEFAULT NULL COMMENT '学生成绩',
PRIMARY KEY (`sno`, `cno`, `year_semeste`) USING BTREE,
INDEX `e_tno`(`tno`) USING BTREE,
CONSTRAINT `e_tno` FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for speciality
-- ----------------------------
DROP TABLE IF EXISTS `speciality`;
CREATE TABLE `speciality` (
`spno` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT ' 专业代码',
`spname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '专业名称',
`dno` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '所属院系编号',
PRIMARY KEY (`spno`) USING BTREE,
INDEX `spno`(`spno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sno` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT ' 学生学号',
`sname` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学生姓名',
`sex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' 学生性别',
`nation` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' 民族',
`birthday` date NULL DEFAULT NULL COMMENT ' 学生出生日期',
`classno` char(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '行政班级号',
`dno` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' 学生所在院系编号',
`spno` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '专业代码（外键）',
`entime` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '学生入校年纪',
`home` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' 学生家庭住址',
`tel` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '学生联系电话',
PRIMARY KEY (`sno`) USING BTREE,
INDEX `st_spno`(`spno`) USING BTREE,
CONSTRAINT `st_spno` FOREIGN KEY (`spno`) REFERENCES `speciality` (`spno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for teach
-- ----------------------------
DROP TABLE IF EXISTS `teach`;
CREATE TABLE `teach` (
`cno` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程编号（外键）',
`year_semester` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学年学期',
`tno` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '教师编号（外键）',
`classno` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '上课的班级',
PRIMARY KEY (`cno`, `year_semester`, `tno`, `classno`) USING BTREE,
INDEX `t_tno`(`tno`) USING BTREE,
CONSTRAINT `t_cno` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `t_tno` FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tno` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '教师工号',
`name` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '教师姓名',
`sex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '教师性别',
`birthday` date NULL DEFAULT NULL COMMENT '教师出生日期',
`rank` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '教师职称',
`salary` int NULL DEFAULT NULL COMMENT '月工资',
`dno` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '教师所在学院编号',
`home` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '教师家庭住址',
`zipcode` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' 邮政编码',
`tel` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' 联系电话',
`email` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '电子邮件',
PRIMARY KEY (`tno`) USING BTREE,
INDEX `dno`(`dno`) USING BTREE,
CONSTRAINT `dno` FOREIGN KEY (`dno`) REFERENCES `department` (`dno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
插入数据
插入数据内容
插入数据语句
- department
INSERT INTO `department`(`dno`, `name`, `dean`, `addr`, `phone`) VALUES ('24', '信息科学与工程学院', '2011120', '软件楼', '8305424');
INSERT INTO `department`(`dno`, `name`, `dean`, `addr`, `phone`) VALUES ('27', '数学院', '2007004', '数学楼', '8304551');
INSERT INTO `department`(`dno`, `name`, `dean`, `addr`, `phone`) VALUES ('28', '工商管理学院', '2009007', 'MBA楼', '8304530');
INSERT INTO `department`(`dno`, `name`, `dean`, `addr`, `phone`) VALUES ('29', '会计学院', '1995008', '逸夫楼', '8304578');
INSERT INTO `department`(`dno`, `name`, `dean`, `addr`, `phone`) VALUES ('31', '政治理论学院', '20011024', '红叶楼', '8304517');
INSERT INTO `department`(`dno`, `name`, `dean`, `addr`, `phone`) VALUES ('32', '机械学院', '20001021', '宇航楼', '8304534');
INSERT INTO `department`(`dno`, `name`, `dean`, `addr`, `phone`) VALUES ('33', '工业设计学院', '19971004', '设计大楼', '8305204');
- teacher
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('1995008', '陈浪', '男', '1979-03-30', '教授', 7880, '29', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('1997030', '刘小明', '女', '1979-03-30', '教授', 9700, '24', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('19971004', '赵子龙', '男', '1979-03-30', '教授', 9650, '33', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('20001021', '张国荣', '男', '1973-04-12', '教授', 8950, '32', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('2000121', '李平', '男', '1973-04-12', '助教', 7500, '24', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('2001009', '周曼玲', '女', '1972-09-09', '副教授', 8600, '24', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('20011024', '刘亚平', '女', '1972-09-09', '教授', 10800, '31', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('20041024', '朱喜', '男', '1985-01-09', '副教授', 8500, '24', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('2004124', '刘东妮', '女', '1985-01-09', '助教', 7200, '24', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('2007004', '张中华', '男', '1972-09-09', '教授', 9850, '27', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('20081003', '马千里', '男', '1979-03-30', '副教授', 8850, '24', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('2009007', '马力', '女', '1973-04-12', '教授', 8650, '28', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('20111104', '王林林', '女', '1962-12-11', '助教', 7900, '24', NULL, NULL, NULL, NULL);
INSERT INTO `teacher`(`tno`, `name`, `sex`, `birthday`, `rank`, `salary`, `dno`, `home`, `zipcode`, `tel`, `email`) VALUES ('2011120', '万世平', '男', '1962-12-11', '教授', 9900, '24', NULL, NULL, NULL, NULL);
- student
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401101', '李毅', '男', '汉', '1985-01-02', '软件1班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401102', '厉超', '男', '苗', '1984-03-08', '软件1班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401103', '梁冰龙', '男', '壮', '1984-07-09', '软件1班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401104', '罗煦', '女', '维吾尔', '1985-10-12', '软件1班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401105', '马晓睿', '男', '汉', '1983-11-01', '软件1班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401106', '聂立业', '男', '壮', '1984-06-03', '软件1班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401107', '王实', '男', '汉', '1984-02-11', '软件1班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401108', '伍卉', '女', '汉', '1985-01-03', '软件1班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401109', '伍琼', '男', '汉', '1985-12-11', '软件1班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401110', '曾文超', '男', '汉', '1984-07-05', '软件1班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401201', '黄文杰', '男', '汉', '1984-07-05', '软件2班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182401202', '龙淼', '男', '汉', '1985-12-11', '软件2班', '24', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182709101', '何晓明', '男', '汉', '1984-08-13', '营销1班', '27', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182709102', '刘寅', '男', '汉', '1984-05-07', '营销1班', '27', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182911101', '邢晓静', '女', '汉', '1984-08-13', '电算1班', '28', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182911102', '李明伟', '女', '汉', '1985-03-09', '电算1班', '28', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182911103', '童熙', '女', '汉', '1985-10-16', '电算1班', '28', NULL, NULL, NULL, NULL);
INSERT INTO `student`(`sno`, `sname`, `sex`, `nation`, `birthday`, `classno`, `dno`, `spno`, `entime`, `home`, `tel`) VALUES ('20182911104', '朱俊华', '男', '汉', '1985-12-03', '电算1班', '28', NULL, NULL, NULL, NULL);
- course
INSERT INTO `course`(`cno`, `name`, `spno`, `ctno`, `hours`, `experiment`, `credit`, `semester`, `duty_tno`, `dno`) VALUES ('C24020005', '面向对象程序设计', NULL, '2', 32, 24, 2, 5, '20111104', '24');
INSERT INTO `course`(`cno`, `name`, `spno`, `ctno`, `hours`, `experiment`, `credit`, `semester`, `duty_tno`, `dno`) VALUES ('C31010001', '毛z东思想概论', NULL, '1', 32, NULL, 2, 6, NULL, '31');
INSERT INTO `course`(`cno`, `name`, `spno`, `ctno`, `hours`, `experiment`, `credit`, `semester`, `duty_tno`, `dno`) VALUES ('C31010002', '邓x平理论', NULL, '1', 62, NULL, 2, 1, NULL, '31');
INSERT INTO `course`(`cno`, `name`, `spno`, `ctno`, `hours`, `experiment`, `credit`, `semester`, `duty_tno`, `dno`) VALUES ('H24030006', '数据结构', NULL, '2', 64, 24, 4, 4, '2004124', '24');
INSERT INTO `course`(`cno`, `name`, `spno`, `ctno`, `hours`, `experiment`, `credit`, `semester`, `duty_tno`, `dno`) VALUES ('H24030007', '计算机组成原理', NULL, '2', 64, NULL, 4, 5, '2011120', '24');
INSERT INTO `course`(`cno`, `name`, `spno`, `ctno`, `hours`, `experiment`, `credit`, `semester`, `duty_tno`, `dno`) VALUES ('H24030008', '数据库系统', NULL, '3', 48, 24, 3, 5, '2001009', '24');
INSERT INTO `course`(`cno`, `name`, `spno`, `ctno`, `hours`, `experiment`, `credit`, `semester`, `duty_tno`, `dno`) VALUES ('H24030009', '操作系统', NULL, '3', 48, 24, 3, 4, '2000121', '24');
INSERT INTO `course`(`cno`, `name`, `spno`, `ctno`, `hours`, `experiment`, `credit`, `semester`, `duty_tno`, `dno`) VALUES ('H24030010', '计算机网络', NULL, '3', 48, 16, 3, 6, '1997030', '24');
INSERT INTO `course`(`cno`, `name`, `spno`, `ctno`, `hours`, `experiment`, `credit`, `semester`, `duty_tno`, `dno`) VALUES ('X24040019', '数据挖掘', '2', '4', 32, NULL, 2, 7, NULL, '24');
INSERT INTO `course`(`cno`, `name`, `spno`, `ctno`, `hours`, `experiment`, `credit`, `semester`, `duty_tno`, `dno`) VALUES ('X24040022', '网络安全概论', '2', '4', 32, NULL, 2, 7, NULL, '24');
INSERT INTO `course`(`cno`, `name`, `spno`, `ctno`, `hours`, `experiment`, `credit`, `semester`, `duty_tno`, `dno`) VALUES ('X27020012', '高等数学', NULL, '1', 64, NULL, 4, 2, '2007004', '27');
- teach
INSERT INTO `teach`(`cno`, `year_semester`, `tno`, `classno`) VALUES ('C31010001', '2019-01-01', '20011024', 'A');
INSERT INTO `teach`(`cno`, `year_semester`, `tno`, `classno`) VALUES ('C31010001', '2019-01-01', '20011024', 'B');
INSERT INTO `teach`(`cno`, `year_semester`, `tno`, `classno`) VALUES ('C24020005', '2019-02-01', '2009007', 'A');
- enroll
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182401101', 'C31010001', '2019-01-01', '20011024', 'A', 88);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182401101', 'H24030006', '2019-01-01', '2004124', 'A', 89);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182401101', 'H24030008', '2020-01-01', '2001009', 'A', 83);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182401102', 'H24030006', '2019-01-01', '2004124', 'A', 98);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182401102', 'H24030008', '2020-01-01', '2001009', 'A', 66);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182401102', 'X27030019', '2019-02-01', '2007004', 'B', 90);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182401103', 'H24030006', '2019-01-01', '2004124', 'A', 86);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182401103', 'H24030008', '2020-01-01', '2001009', 'A', 55);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182401104', 'H24030006', '2019-01-01', '2004124', 'B', 76);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182401104', 'H24030008', '2020-01-01', '2001009', 'B', 87);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182401202', 'C31010001', '2019-01-01', '20011024', 'A', 88);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182709101', 'C31010001', '2019-01-01', '20011024', 'A', 90);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182709102', 'C24020005', '2019-02-01', '2009007', 'A', 83);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182911101', 'C24020005', '2019-02-01', '2009007', 'A', 66);
INSERT INTO `enroll`(`sno`, `cno`, `year_semeste`, `tno`, `classno`, `score`) VALUES ('20182911101', 'C31010001', '2019-01-01', '20011024', 'B', 87);
- speciality
INSERT INTO `speciality`(`spno`, `spname`, `dno`) VALUES ('1', '软件工程', '24');
INSERT INTO `speciality`(`spno`, `spname`, `dno`) VALUES ('11', '电算会计', '29');
INSERT INTO `speciality`(`spno`, `spname`, `dno`) VALUES ('2', '人工智能', '24');
INSERT INTO `speciality`(`spno`, `spname`, `dno`) VALUES ('3', '物联网', '24');
INSERT INTO `speciality`(`spno`, `spname`, `dno`) VALUES ('4', '网络安全', '24');
INSERT INTO `speciality`(`spno`, `spname`, `dno`) VALUES ('8', '工商管理', '28');
INSERT INTO `speciality`(`spno`, `spname`, `dno`) VALUES ('9', '市场营销', '28');
- coursetype
INSERT INTO `coursetype`(`ctno`, `ctname`) VALUES ('1', '公共课');
INSERT INTO `coursetype`(`ctno`, `ctname`) VALUES ('2', '专业基础课');
INSERT INTO `coursetype`(`ctno`, `ctname`) VALUES ('3', '专业课');
INSERT INTO `coursetype`(`ctno`, `ctname`) VALUES ('4', '选修课');
SQL语句练习
对于如下业务需求，写出获取所须数据的SQL语句：
- 输出信息科学与工程学院（学院编号“24”）2018级学生“李毅”的课程选修成绩单，输出项包括课程名称，学分，成绩三项；
SELECT
c.`name`,
c.credit,
e.score
FROM
course c,
enroll e
WHERE
c.dno = '24' AND c.ctno = (SELECT coursetype.ctno FROM coursetype WHERE coursetype.ctname = '选修课') AND e.sno = (SELECT student.sno FROM student WHERE student.sname = '李毅' AND student.sno LIKE '2018%');
2. 找出学生李毅（学号为 ‘20182401101’）的电话；
SELECT
tel
FROM
student
WHERE
sname = '李毅';
3. 输出信息科学与工程学院（学院编号为’24’)的学生清单，输出项包括姓名，学号，班级三列，按照班级和学号排序；
SELECT
sname,
sno,
classno
FROM
student
WHERE
dno='24';
- 输出所有学院的清单，包括学院编号，名称，地址，电话，院长姓名；
SELECT
department.dno,
department.`name`,
department.addr,
department.phone,
teacher.`name`
FROM
department
LEFT JOIN teacher ON teacher.tno = department.dean;
5. 在2020-1学期选修了周曼玲老师（工号为”2001009”)开设的“数据库系统”课程的学生名单，输出项为五项：教学班，姓名、学号、性别，行政班。要求先按照教学班，再按行政班排序；（注：一个老师在某个学期开的某门课，完全有可能有多个教学班）；
SELECT
enroll.classno,
student.sname,
student.sno,
student.sex,
student.classno
FROM
enroll,
student
WHERE
student.sno = ANY (
SELECT
enroll.sno
FROM
enroll
WHERE
year_semeste LIKE '2020-01%'
AND tno = '2001009');
6. 对于在2020-1学期选修了“数据库系统”课程的各行政班，求其平均分，并按照平均分从高到低排序；
SELECT DISTINCT
enroll.classno,
AVG( enroll.score ) AS AVG_SCORE
FROM
enroll
WHERE
enroll.cno = ( SELECT course.cno FROM course WHERE course.`name` = '数据库系统' )
GROUP BY
enroll.classno
ORDER BY
AVG_SCORE;
- 对于信息科学与工程学院（学院编号“24”）在2020-1学期开设的每门课程，输出其选修人数，输出项包括课程名称，选修人数；
SELECT
course.`name`,
COUNT( DISTINCT enroll.sno ) AS COUNT
FROM
course,
enroll
WHERE
course.dno = '24'
AND course.cno = ANY ( SELECT enroll.cno FROM enroll WHERE enroll.year_semeste LIKE '2020-01%' )
GROUP BY
course.`name`;
8. 在2020-1学期，对于开设的“数据库系统”这门课程，在信息科学与工程学院（学院编号“24”）的2018级学生中，有哪些学生没有选修该课程，输出姓名，学号，行政班三项信息；
SELECT
sname,
sno,
classno
FROM
student s
WHERE
s.dno = '24' AND
NOT EXISTS (
SELECT
*
FROM
enroll e
WHERE
e.year_semeste LIKE '2020-01%'
AND s.sno = e.sno
AND e.cno = ( SELECT c.cno FROM course c WHERE c.`name` = '数据库系统' ));
9. 对于在2020-1学期开设的每门课程，输出其最高分、最低分，平均分；
SELECT
MAX( score ) AS MAX,
MIN( score ) AS MIN,
AVG( score ) AS AVG
FROM
enroll
WHERE
year_semeste LIKE '2020-01%';
- 对于在2020-1学期开设的“数据库系统”课程，求选修了该课，但是没有及格的学生名单，输出姓名，学号，行政班，课程成绩四项信息；
SELECT
sname,
sno,
classno
FROM
student s
WHERE
EXISTS (
SELECT
*
FROM
enroll e
WHERE
e.year_semeste LIKE '2020-01%'
AND e.score < '60'
AND s.sno = e.sno
AND e.cno = ( SELECT c.cno FROM course c WHERE c.`name` = '数据库系统' ));
11. 对于信息科学与工程学院（学院编号“24”）的每个教师，统计出其在2019年度的教学工作量；（提示：课程表中，每门课有课时量hours字段）
SELECT
teacher.`name`,
SUM( course.hours ) AS SUM
FROM
teacher,
course
WHERE
course.cno = ANY (
SELECT
enroll.cno
FROM
enroll
WHERE
enroll.year_semeste LIKE '2019%'
AND enroll.tno = ANY ( SELECT teacher.tno FROM teacher WHERE teacher.dno = '24' ))
GROUP BY
teacher.`name`;
12. 对于信息科学与工程学院（学院编号“24”）的每个教师，统计在2019年度教学工作量未达到要求（小于150学时）的教师，输出教师姓名，工号，教学工作量三项；
SELECT
teacher.`name`,
teacher.tno,
SUM( course.hours ) AS SUM
FROM
teacher,
course
WHERE
course.cno = ANY (
SELECT
enroll.cno
FROM
enroll
WHERE
enroll.year_semeste LIKE '2019%'
AND enroll.tno = ANY ( SELECT teacher.tno FROM teacher WHERE teacher.dno = '24' ))
GROUP BY
teacher.`name`
HAVING SUM( course.hours ) < '150';
13. 统计各个学院在2019年度的教学工作量；
SELECT
department.`name`,
SUM( course.hours )
FROM
department,
course
WHERE
department.dno = ALL (
SELECT
course.dno
FROM
course
WHERE
course.cno = ALL ( SELECT enroll.cno FROM enroll WHERE enroll.year_semeste LIKE '2019%' ))
GROUP BY
department.`name`;
14. 对于2016级学生，即将毕业，求出输出所得总学分不足172的学生，输出项为所属学院名称，学号，姓名，已得学分数这四项；
SELECT
department.`name`,
student.sno,
student.sname,
SUM( course.credit ) AS SUM
FROM
department,
student,
course
WHERE
department.dno = student.dno
AND course.cno = ALL (
SELECT
enroll.cno
FROM
enroll
WHERE
enroll.sno = ALL ( SELECT student.sno FROM student WHERE student.sno LIKE '2016%' ))
GROUP BY
student.sno
HAVING
SUM( course.credit ) < '172';
浙公网安备 33010602011771号