mysql岗位实习----教务系统管理

教务管理系统

一、DDL

CREATE TABLE `users` (`user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',`username` varchar(50) NOT NULL COMMENT '用户名',`password` varchar(255) NOT NULL COMMENT '密码',`gender` enum('男','女') NOT NULL COMMENT '性别',`email` varchar(100) DEFAULT NULL COMMENT '邮箱',PRIMARY KEY (`user_id`),UNIQUE KEY `username` (`username`),UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;CREATE TABLE `userroles` (`user_id` int(11) NOT NULL COMMENT '用户ID',`role_id` int(11) NOT NULL COMMENT '角色ID',`created_at` datetime NOT NULL,PRIMARY KEY (`user_id`),KEY `role_id` (`role_id`),CONSTRAINT `userroles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,CONSTRAINT `userroles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `teachers` (`teacher_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '教师ID',`user_id` int(11) NOT NULL COMMENT '关联的用户ID',`subject` varchar(100) NOT NULL COMMENT '教学科目',`qualification` varchar(255) DEFAULT NULL COMMENT '教师资质',PRIMARY KEY (`teacher_id`),KEY `user_id` (`user_id`),CONSTRAINT `teachers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;CREATE TABLE `students` (`student_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生ID',`user_id` int(11) NOT NULL COMMENT '关联的用户ID',`class` varchar(50) NOT NULL COMMENT '班级',`year_of_entry` year(4) NOT NULL COMMENT '入学年份',PRIMARY KEY (`student_id`),KEY `user_id` (`user_id`),CONSTRAINT `students_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;CREATE TABLE `roles` (`role_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色ID',`role_name` varchar(50) NOT NULL COMMENT '角色名称',`created_at` datetime NOT NULL,PRIMARY KEY (`role_id`),UNIQUE KEY `role_name` (`role_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;CREATE TABLE `grades` (`grade_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '成绩ID',`student_id` int(11) NOT NULL COMMENT '学生ID',`course_id` int(11) NOT NULL COMMENT '课程ID',`grade` decimal(5,2) NOT NULL COMMENT '成绩',PRIMARY KEY (`grade_id`),KEY `student_id` (`student_id`),KEY `course_id` (`course_id`),CONSTRAINT `grades_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,CONSTRAINT `grades_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;CREATE TABLE `courses` (`course_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '课程ID',`course_name` varchar(100) NOT NULL COMMENT '课程名称',`course_code` varchar(50) NOT NULL COMMENT '课程代码',`description` text COMMENT '课程描述',PRIMARY KEY (`course_id`),UNIQUE KEY `course_name` (`course_name`),UNIQUE KEY `course_code` (`course_code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

二、DML

INSERT INTO `users` VALUES (1, '喜羊羊', '123', '男', 'xiyangyang@example.com');
INSERT INTO `users` VALUES (2, '美羊羊', '123', '女', 'meiyangyang@example.com');
INSERT INTO `users` VALUES (3, '灰太狼', '123', '男', 'huitailang@example.com');INSERT INTO `userroles` VALUES (1, 1, '2020-1-1 00:00:00');
INSERT INTO `userroles` VALUES (2, 2, '2020-1-1 00:00:00');
INSERT INTO `userroles` VALUES (3, 3, '2020-1-1 00:00:00');INSERT INTO `teachers` VALUES (1, 2, '数学', '高级教师');
INSERT INTO `teachers` VALUES (2, 2, 'mysql', '特级教师');
INSERT INTO `teachers` VALUES (3, 2, 'web', '特级教师');INSERT INTO `students` VALUES (1, 2, '一班', '2020');
INSERT INTO `students` VALUES (2, 2, '二班', '2021');
INSERT INTO `students` VALUES (3, 2, '三班', '2022');INSERT INTO `roles` VALUES (1, '管理员', '2020-1-1 00:00:00');
INSERT INTO `roles` VALUES (2, '教师', '2020-1-1 00:00:00');
INSERT INTO `roles` VALUES (3, '学生', '2020-1-1 00:00:00');INSERT INTO `grades` VALUES (1, 2, 2, 95.00);
INSERT INTO `grades` VALUES (2, 1, 3, 92.00);
INSERT INTO `grades` VALUES (3, 3, 1, 100.00);
INSERT INTO `grades` VALUES (4, 2, 3, 98.00);
INSERT INTO `grades` VALUES (5, 3, 3, 100.00);
INSERT INTO `grades` VALUES (6, 1, 2, 100.00);INSERT INTO `courses` VALUES (1, '数学', '0000', '计算量大');
INSERT INTO `courses` VALUES (2, 'mysql', '1111', '代码数量复杂');
INSERT INTO `courses` VALUES (3, 'web', '6666', '花样多');