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', '花样多');

三、模型图和ER图

 

四、DQL