MySQL练习sql脚本(某校学生管理系统版本)

原创

红目香薰 2021-12-24 14:05:20 博主文章分类：MySQL ©著作权

文章标签 mysql 表名二级 sql语句 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者红目香薰的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、环境以及脚本

1.1、编码格式如下：

使用工具：【Navicat】

练习库名：【mytest】

编码格式：【utf-8】

MySQL练习sql脚本(某校学生管理系统版本)_mysql

1.2、脚本引入

MySQL练习sql脚本(某校学生管理系统版本)_二级_02

复制一下sql语句。

USE `mytest`;

DROP TABLE IF EXISTS `tb_class`;
CREATE TABLE `tb_class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT COMMENT '班级编号',
  `cname` varchar(100) NOT NULL COMMENT '班级名称',
  `qq` varchar(20) DEFAULT NULL COMMENT '班级QQ群',
  `school` varchar(100) DEFAULT NULL COMMENT '学校名称',
  `headmaster` varchar(50) DEFAULT NULL COMMENT '班主任',
  `tel` varchar(50) DEFAULT NULL COMMENT '班主任电话号码',
  `declaration` varchar(200) DEFAULT NULL COMMENT '班级宣言',
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='班级表';

-- ----------------------------
-- Records of tb_class
-- ----------------------------
INSERT INTO `tb_class` VALUES ('1', '2020级数学 ', '6666666', '河北大学', '王王姑娘', '15388088011', '自强不息');
INSERT INTO `tb_class` VALUES ('2', '2020级数学', '66666661', '河北科技大学', '王灵雨', '15388018011', '仁爱世人');
INSERT INTO `tb_class` VALUES ('3', '2020级外语', '66666662', '河北农垦大学', '李晓燕', '15388028011', '飞过海洋');
INSERT INTO `tb_class` VALUES ('4', '2020级计算机', '66666663', '河北中央大学', '王佳乐', '15383088011', '乐在人生');
INSERT INTO `tb_class` VALUES ('5', '2020级生物化工', '66666664', '河北民族大学', '王语嫣', '15348088011', '琅嬛福地');
INSERT INTO `tb_class` VALUES ('6', '2020级软件设计', '66666665', '河北化工大学', '张敏', '15388588011', '简简单单');
INSERT INTO `tb_class` VALUES ('7', '2020级动漫', '66666666', '河北影视大学', '滕紫荆', '15388086011', '永攀高峰');
INSERT INTO `tb_class` VALUES ('8', '2020级UI设计', '66666667', '河北影视大学', '闫梓佳', '15388788011', '自强不息');
INSERT INTO `tb_class` VALUES ('9', '2020级早教', '66666668', '河北民族大学', '杜春娜', '15388088011', '育儿天地');
INSERT INTO `tb_class` VALUES ('10', '2020级云计算', '66666669', '河北科技大学', '米粒儿', '15388089011', '飞上云霄');

-- ----------------------------
-- Table structure for `tb_college`
-- ----------------------------
DROP TABLE IF EXISTS `tb_college`;
CREATE TABLE `tb_college` (
  `collegeId` int(11) NOT NULL AUTO_INCREMENT COMMENT '二级学院编号',
  `collegeName` varchar(20) DEFAULT NULL COMMENT '二级学院名称',
  `collegeRoom` varchar(20) DEFAULT NULL COMMENT '二级学院办公室',
  `collegeDeanID` int(11) DEFAULT NULL COMMENT '院长编号',
  `collegeTel` varchar(11) DEFAULT NULL COMMENT '二级学院电话',
  `collegeEmail` varchar(20) DEFAULT NULL COMMENT '二级学院邮箱',
  `schoolId` int(11) DEFAULT NULL COMMENT '学校id',
  PRIMARY KEY (`collegeId`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='专业表';

-- ----------------------------
-- Records of tb_college
-- ----------------------------
INSERT INTO `tb_college` VALUES ('1', '农业学院', '备课处1', '1', '8060002', '8060002@qq.com', '2');
INSERT INTO `tb_college` VALUES ('2', '化工学院', '备课处2', '2', '8060002', '8060002@qq.com', '3');
INSERT INTO `tb_college` VALUES ('3', '影视学院', '备课处3', '3', '8060002', '8060002@qq.com', '4');
INSERT INTO `tb_college` VALUES ('4', '数学学院', '备课处4', '4', '8060002', '8060002@qq.com', '1');
INSERT INTO `tb_college` VALUES ('5', '计算机学院', '备课处5', '5', '8060002', '8060002@qq.com', '5');
INSERT INTO `tb_college` VALUES ('6', '软件学院', '备课处6', '6', '8060002', '8060002@qq.com', '6');

-- ----------------------------
-- Table structure for `tb_course`
-- ----------------------------
DROP TABLE IF EXISTS `tb_course`;
CREATE TABLE `tb_course` (
  `courseId` int(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
  `coutseName` varchar(20) DEFAULT NULL COMMENT '课程名称',
  `teacherId` int(11) DEFAULT NULL COMMENT '任课老师编号',
  `courseRoom` varchar(20) DEFAULT NULL COMMENT '课程教室',
  `maxstudentNum` int(11) DEFAULT NULL COMMENT '最大可选人数',
  `collegeId` int(11) DEFAULT NULL COMMENT '课程分类',
  PRIMARY KEY (`courseId`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='课程表';

-- ----------------------------
-- Records of tb_course
-- ----------------------------
INSERT INTO `tb_course` VALUES ('1', 'C#高级', '1', 'B-212', '40', '2');
INSERT INTO `tb_course` VALUES ('2', 'spring高级', '2', 'B-205', '40', '3');
INSERT INTO `tb_course` VALUES ('3', 'PR高级视频处理', '3', 'A-502', '40', '1');
INSERT INTO `tb_course` VALUES ('4', '农业基础科学', '4', 'B-202', '40', '3');
INSERT INTO `tb_course` VALUES ('5', '数据结构与算法', '5', 'B-212', '40', '6');
INSERT INTO `tb_course` VALUES ('6', 'SQL高级', '6', 'B-207', '40', '5');

-- ----------------------------
-- Table structure for `tb_school`
-- ----------------------------
DROP TABLE IF EXISTS `tb_school`;
CREATE TABLE `tb_school` (
  `schoolId` int(11) NOT NULL AUTO_INCREMENT COMMENT '学校id',
  `schoolName` varchar(20) DEFAULT NULL COMMENT '学校名称',
  `schoolPrincipal` varchar(20) DEFAULT NULL COMMENT '校长',
  `schoolAdd` varchar(200) DEFAULT NULL COMMENT '学校地址',
  `schoolEmain` varchar(200) DEFAULT NULL COMMENT '学校邮箱',
  `schoolTel` varchar(11) DEFAULT NULL COMMENT '学校电话',
  `schoolWebsite` varchar(30) DEFAULT NULL COMMENT '学校官方网址',
  PRIMARY KEY (`schoolId`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='学校表';

-- ----------------------------
-- Records of tb_school
-- ----------------------------
INSERT INTO `tb_school` VALUES ('1', '河北科技大学', 'admin1', '石家庄长安区', 'keji@163.com', '8403000', 'http://www.hbkeji.com');
INSERT INTO `tb_school` VALUES ('2', '河北农垦大学', 'admin2', '石家庄裕华区', 'nongken@163.com', '8403001', 'http://www.hbnongken.com');
INSERT INTO `tb_school` VALUES ('3', '河北中央大学', 'admin3', '石家庄桥西区', 'zhongyang@163.com', '8403002', 'http://www.hbzhongyang.com');
INSERT INTO `tb_school` VALUES ('4', '河北民族大学', 'admin4', '石家庄丰台区', 'minzu@163.com', '8403003', 'http://www.hbminzu.com');
INSERT INTO `tb_school` VALUES ('5', '河北化工大学', 'admin5', '石家庄长安区', 'huagong@163.com', '8403004', 'http://www.hbhuagong.com');
INSERT INTO `tb_school` VALUES ('6', '河北影视大学', 'admin6', '石家庄桥西区', 'yingshi@163.com', '8403005', 'http://www.hbyingshi.com');

-- ----------------------------
-- Table structure for `tb_score`
-- ----------------------------
DROP TABLE IF EXISTS `tb_score`;
CREATE TABLE `tb_score` (
  `scoreId` int(11) NOT NULL AUTO_INCREMENT COMMENT '成绩id',
  `studentId` bigint(11) DEFAULT NULL COMMENT '学生id',
  `courseId` int(11) DEFAULT NULL COMMENT '课程id',
  `score` float DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`scoreId`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='成绩表';

-- ----------------------------
-- Records of tb_score
-- ----------------------------
INSERT INTO `tb_score` VALUES ('1', '910513201419', '2', '96');
INSERT INTO `tb_score` VALUES ('2', '910513201420', '2', '96');
INSERT INTO `tb_score` VALUES ('3', '910513201421', '2', '56');
INSERT INTO `tb_score` VALUES ('4', '910513201422', '2', '96');
INSERT INTO `tb_score` VALUES ('5', '910513201423', '2', '100');
INSERT INTO `tb_score` VALUES ('6', '910513201424', '2', '96');
INSERT INTO `tb_score` VALUES ('7', '910513201422', '3', '92');
INSERT INTO `tb_score` VALUES ('8', '910513201419', '3', '96');
INSERT INTO `tb_score` VALUES ('9', '910513201420', '3', '96');
INSERT INTO `tb_score` VALUES ('10', '910513201421', '3', '56');
INSERT INTO `tb_score` VALUES ('11', '910513201422', '4', '96');
INSERT INTO `tb_score` VALUES ('12', '910513201423', '4', '100');
INSERT INTO `tb_score` VALUES ('13', '910513201424', '3', '96');
INSERT INTO `tb_score` VALUES ('14', '910513201422', '1', '92');

-- ----------------------------
-- Table structure for `tb_student`
-- ----------------------------
DROP TABLE IF EXISTS `tb_student`;
CREATE TABLE `tb_student` (
  `stuNo` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `password` varchar(200) NOT NULL COMMENT '密码',
  `idCard` varchar(18) DEFAULT NULL COMMENT '身份证',
  `sex` char(2) DEFAULT NULL COMMENT '性别',
  `education` varchar(8) DEFAULT NULL COMMENT '学历  0:专科 1:本科 2:硕士 3:博士',
  `schoolId` int(100) DEFAULT NULL COMMENT '毕业学校',
  `professional` varchar(100) DEFAULT NULL COMMENT '专业',
  `acceptanceDate` date DEFAULT NULL COMMENT '入学日期',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `status` varchar(20) DEFAULT NULL COMMENT '状态',
  `tel` varchar(50) DEFAULT NULL COMMENT '联系电话号码',
  `secondTel` varchar(50) DEFAULT NULL COMMENT '第二联系电话号码',
  `qq` varchar(20) DEFAULT NULL COMMENT 'QQ',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `address` varchar(200) DEFAULT NULL COMMENT '地址',
  `classId` int(11) DEFAULT NULL COMMENT '班级编号',
  `isAdmin` int(11) DEFAULT '0' COMMENT '是否是管理员 0:表示学生  1:管理员 0:学生',
  `photoPath` varchar(200) DEFAULT NULL COMMENT '头像文件名',
  PRIMARY KEY (`stuNo`)
) ENGINE=InnoDB AUTO_INCREMENT=910513201430 DEFAULT CHARSET=utf8 COMMENT='学生表';

-- ----------------------------
-- Records of tb_student
-- ----------------------------
INSERT INTO `tb_student` VALUES ('910513201419', '邹智', '123456', '430181198612113330', '男', '1', '1', '数学', '2013-09-05', '1994-08-16', '在读', '15211141999', null, null, null, null, '1', '0', null);
INSERT INTO `tb_student` VALUES ('910513201420', '王小艺', '123456', '430181198612113331', '女', '3', '1', '数学', '2020-09-05', '2020-06-17', '在读 ', '15111141991', null, null, null, null, '1', '0', null);
INSERT INTO `tb_student` VALUES ('910513201421', '买买提', '123456', '430181198612113332', '男', '3', '2', '计算机', '2020-09-05', '2020-02-11', '在读 ', '15111141992', null, null, null, null, '2', '0', null);
INSERT INTO `tb_student` VALUES ('910513201422', '万科', '123456', '430181198612113333', '男', '3', '2', '软件', '2020-09-05', '2019-08-12', '在读 ', '15111131999', null, null, null, null, '1', '0', null);
INSERT INTO `tb_student` VALUES ('910513201423', '褒科一', '123456', '430181198612113334', '男', '3', '3', '设计', '2020-09-05', '2021-08-22', '在读 ', '15111141999', null, null, null, null, '2', '0', null);
INSERT INTO `tb_student` VALUES ('910513201424', '张柏浩', '123456', '430181198612113335', '男', '3', '3', '影视', '2020-09-05', '2020-02-21', '在读 ', '15111151999', null, null, null, null, '1', '0', null);
INSERT INTO `tb_student` VALUES ('910513201425', '李成刚', '123456', '430181198612113336', '男', '3', '2', '影视', '2020-09-05', '2020-12-26', '在读 ', '15111161999', null, null, null, null, '3', '0', null);
INSERT INTO `tb_student` VALUES ('910513201426', '左冷禅', '123456', '430181198612113337', '男', '3', '4', '设计', '2020-09-05', '2020-03-16', '在读 ', '15111171999', null, null, null, null, '4', '0', null);
INSERT INTO `tb_student` VALUES ('910513201427', '令狐冲', '123456', '430181198612113338', '男', '3', '5', '软件开发', '2020-09-05', '2020-08-16', '在读 ', '15181141999', null, null, null, null, '1', '0', null);
INSERT INTO `tb_student` VALUES ('910513201428', '赵灵儿', '123456', '430181198612113339', '女', '3', '6', '软件', '2020-09-05', '2021-08-25', '在读 ', '15111191999', null, null, null, null, '5', '0', null);
INSERT INTO `tb_student` VALUES ('910513201429', '刘国梁', '123456', '430181198612113310', '男', '3', '2', '化工', '2020-09-05', '2020-07-06', '在读 ', '15111101999', null, null, null, null, '1', '0', null);

-- ----------------------------
-- Table structure for `tb_teacher`
-- ----------------------------
DROP TABLE IF EXISTS `tb_teacher`;
CREATE TABLE `tb_teacher` (
  `teacherid` int(11) NOT NULL AUTO_INCREMENT COMMENT '教师编号',
  `teacherName` varchar(20) DEFAULT NULL COMMENT '教师名称',
  `password` varchar(20) DEFAULT NULL COMMENT '登录密码',
  `idCard` varchar(18) DEFAULT NULL COMMENT '身份证编号',
  `sex` varchar(2) DEFAULT NULL COMMENT '性别',
  `teacherTitle` varchar(8) DEFAULT NULL COMMENT '教师职称，博士、硕士',
  `entryDate` date DEFAULT NULL COMMENT '入职日期',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `status` varchar(8) DEFAULT NULL COMMENT '状态',
  `tel` varchar(11) DEFAULT NULL COMMENT '电话',
  `secondTel` varchar(11) DEFAULT NULL COMMENT '第二联系号码',
  `qq` varchar(20) DEFAULT NULL COMMENT 'qq',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `address` varchar(200) DEFAULT NULL COMMENT '地址',
  `schoolid` int(11) DEFAULT NULL COMMENT '学校id',
  `collegeId` int(11) DEFAULT NULL COMMENT '专业id',
  `isAdmin` int(11) DEFAULT NULL COMMENT '是否管理员',
  `photoPath` varchar(200) DEFAULT NULL COMMENT '头像地址',
  PRIMARY KEY (`teacherid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='教师表';

-- ----------------------------
-- Records of tb_teacher
-- ----------------------------
INSERT INTO `tb_teacher` VALUES ('1', '老师张', '123456', '123456789012345671', '男', '博士', '2000-09-01', '1980-05-05', '在职', '8606001', '8706001', '1000001', '1000001@qq.com', '河北石家庄', '1', '6', '6', 'teacherwang.jpg');
INSERT INTO `tb_teacher` VALUES ('2', '老师孟', '123456', '123456789012345672', '男', '博士', '2000-09-02', '1980-05-01', '在职', '8606002', '8706002', '1000002', '1000002@qq.com', '河北石家庄', '2', '5', '5', 'teacher1.jpg');
INSERT INTO `tb_teacher` VALUES ('3', '老师付', '123456', '123456789012345673', '男', '博士', '2000-09-03', '1980-05-02', '在职', '8606003', '8706003', '1000003', '1000003@qq.com', '河北石家庄', '3', '4', '4', 'teacher2.jpg');
INSERT INTO `tb_teacher` VALUES ('4', '老师刘', '123456', '123456789012345674', '女', '博士', '2000-09-04', '1980-05-03', '在职', '8606004', '8706004', '1000004', '1000004@qq.com', '河北石家庄', '4', '3', '3', 'teacher3g.jpg');
INSERT INTO `tb_teacher` VALUES ('5', '老师郭', '123456', '123456789012345675', '男', '博士', '2000-09-05', '1980-05-04', '在职', '8606005', '8706005', '1000005', '1000005@qq.com', '河北石家庄', '5', '2', '2', 'teacher4.jpg');
INSERT INTO `tb_teacher` VALUES ('6', '老师李', '123456', '123456789012345676', '男', '博士', '2000-09-06', '1980-05-05', '在职', '8606006', '8706006', '1000006', '1000006@qq.com', '河北石家庄', '6', '1', '1', 'teacher5.jpg');