1、环境以及脚本
环境下载与安装教程：【https://laoshifu.blog.csdn.net/article/details/118991473】
1.1、编码格式如下：
使用工具：【Navicat】
练习库名：【mytest】
编码格式：【utf-8】
1.2、脚本引入
复制一下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');