这是基于数据库用Java语言实现的学生选课管理系统，采用控制台输出形式，完成管理员，教师，学生得增删改查功能。
开发工具:IDEA，mysql数据库，Navicat
一.界面展示
1.登陆界面
2.管理员登陆后台
3.教师登陆后台
4.学生登陆后台
5.个别功能演示
(1)学生端查看选课示例
（2）老师端查看学生选课情况示例
（3）管理员端查看课程，课程学分以及任课教师示例
二.部分代码
1.登陆菜单代码
public class view {
// 定义全局变量
String username; // 存储用户输入的用户名
String password; // 存储用户输入的密码
// 静态导入Scanner类，用于获取用户输入
static Scanner rader = new Scanner(System.in);
// 创建各个角色的实例
Manager man = new Manager(); // 管理员类实例
Teacher tea = new Teacher(); // 教师类实例
Login logi = new Login(); // 登录类实例
Student stu = new Student(); // 学生类实例
utilss u1 = new utilss(); // 假设utilss是工具类，创建实例
// 构造函数，可能用于初始化操作，这里没有实现
public view() throws SQLException, ClassNotFoundException {
}
public void mainUi() {//系统主界面
System.out.println("============欢迎使用学生信息管理系统==========");
System.out.println("|\t\t\t 管理员登录:1\t\t\t\t|");
System.out.println("|\t\t\t 老师登录: 2\t\t\t\t\t|");
System.out.println("|\t\t\t 学生登录: 3\t\t\t\t\t|");
System.out.println("=============退出系统请输入:0===============");
}
public void managerUi() { // 管理员主界面
System.out.println("===============管理员后台==================");
System.out.println("|\t\t\t 添加老师:1 \t\t\t\t\t|");
System.out.println("|\t\t\t 添加班级: 2\t\t\t\t\t|");
System.out.println("|\t\t\t 添加课程: 3\t\t\t\t\t|");
System.out.println("|\t\t\t 添加学生:4 \t\t\t\t\t|");
System.out.println("|\t\t\t 删除课程:5 \t\t\t\t\t|");
System.out.println("|\t\t\t 查看课程，课程学分以及任课教师:6 \t\t\t\t\t|");
System.out.println("|\t\t\t 查看学生列表:7 \t\t\t\t\t|");
System.out.println("|\t\t\t 查看老师列表:8 \t\t\t\t\t|");
System.out.println("===============退出请输入:0================");
}
private void teacherUi() {// 老师主界面
System.out.println("===============教师后台==================");
System.out.println("|\t\t\t 添加学生: 1 \t\t\t\t|");
System.out.println("|\t\t\t 录入成绩: 2\t\t\t\t|");
System.out.println("|\t\t\t 查看学生成绩:3\t\t\t\t|");
System.out.println("|\t\t\t 查看学生选课情况:4\t\t\t\t|");
System.out.println("|\t\t\t 查看自己的任课情况:5\t\t\t\t|");
System.out.println("|\t\t\t 删除学生成绩:6\t\t\t\t|");
System.out.println("|\t\t\t 修改学生成绩:7\t\t\t\t|");
System.out.println("=========退出请输入:0=========================");
}
private void studentUi() {//学生主界面
System.out.println("===============学生后台==================");
System.out.println("|\t\t\t 自主选课:1 \t\t\t\t|");
System.out.println("|\t\t\t 打印成绩单:2 \t\t\t\t|");
System.out.println("|\t\t\t 查看已选课程:3 \t\t\t\t|");
System.out.println("|\t\t\t 修改已选课程:4\t\t\t\t|");
System.out.println("=========退出请输入:0====================");
}
//选择登陆身份
public void chooseRole() throws SQLException, ClassNotFoundException {
int role = rader.nextInt(); // 读取用户选择的角色
// 根据用户选择的角色，执行相应的登录逻辑
if (role == 1) {//1代表管理员权限
System.out.print("请输入账号:");
username = rader.next();
System.out.print("请输入密码:");
password = rader.next();
// 调用Login类的userLogin方法尝试登录
ResultSet res = logi.userLogin(username, password, "1");
// 检查ResultSet对象是否有更多数据行，即检查登录是否成功
if (res.next() == false) {
// 如果res.next()返回false，说明没有找到匹配的用户名和密码，即登录失败
System.out.println("对不起，用户名或密码错误!"); // 这里不抛出异常，而是打印错误信息并结束当前方法
} else {
// 如果登录成功，调用managerFram方法进入管理员界面
managerFram();
}
} else if (role == 2) {//2代表老师权限
System.out.print("请输入账号:");
username = rader.next();
System.out.print("请输入密码:");
password = rader.next();
ResultSet res = logi.userLogin(username, password, "2");
if (res.next() == false) {
System.out.println("对不起，用户名或密码错误!");
} else {
teacherFram(username);//进入教师界面
}
} else if (role == 3) {//3代表学生权限
System.out.print("请输入账号:");
username = rader.next();
System.out.print("请输入密码:");
password = rader.next();
ResultSet res = logi.userLogin(username, password, "3");
if (res.next() == false) {
System.out.println("对不起，用户名或密码错误!");
} else {
studentFram(username);//进入学生界面
}
} else if (role == 0) {
System.exit(0);// System.exit(0)调用用于立即终止程序，并返回状态码0给操作系统，表示正常退出
} else {
System.out.println("输入有误，请重新输入!");// 如果用户输入的角色不是1、2、3或0，即输入了无效的角色选项
}
}
}2.管理员部分功能实现代码
public class Manager {
public Manager() throws SQLException, ClassNotFoundException {// Manager类的构造函数，可能会抛出SQLException和ClassNotFoundException异常
}
static Scanner rader = new Scanner(System.in);// 静态导入Scanner类用于获取用户输入
utilss tool1=new utilss();// 实例化utilss工具类，用于数据库连接等操作
// 定义管理员操作中可能用到的变量
String teacherName,teacherSex,teacherPasswd;
String teacherRole="2";//为请输入权限老师代号2
String classname,classTeacher;
String courseName,courseTeacher;//课程名，代课老师
int courseScore;//课程学分
// 管理员添加老师的方法
Connection Mconn=tool1.getconn();// Mconn代表管理员专用连接
Statement Mstmt=Mconn.createStatement();// 创建Statement对象用于执行SQL语句
public void addTeacher() throws SQLException, ClassNotFoundException {
int res;// 用于存储增删改操作的返回值
// while(true){
System.out.print("请输入老师姓名：");
teacherName =rader.next();//("请输入老师名字：")
System.out.print("请输入老师性别：");
teacherSex =rader.next();// input('请输入老师性别:')
System.out.print("请输入老师登陆密码：");
teacherPasswd =rader.next();// input('请输入教师登录密码:')
/* System.out.print("按q退出本步骤:");
if(rader.next()=="q"){
break;
}
break;
}*/
// 构建SQL语句
String sql= "insert into user (name,sex,password,role)values ('"+teacherName+"','"+teacherSex+"','"+teacherPasswd+"','"+teacherRole+"')";
// 执行SQL语句并检查结果
res=Mstmt.executeUpdate(sql);
if (res>=1){
System.out.println("添加老师成功");
}else {
System.out.println("添加失败");
}
}
//添加班级
public void addClass() throws SQLException, ClassNotFoundException {
Scanner reader = new Scanner(System.in);
String classname;
String classTeacher;
System.out.print("请输入班级名：");
classname = reader.nextLine().trim();
System.out.print("请输入班主任：");
classTeacher = reader.nextLine().trim();
utilss utils = new utilss(); // 创建 utilss 类的实例以使用其 getconn() 方法
try (Connection conn = utils.getconn()) { // 使用 try-with-resources 自动关闭连接
// 查询班主任是否存在
String sql = "select userid from user where name=? and role=2";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, classTeacher);
ResultSet resQ = pstmt.executeQuery();
if (!resQ.next()) {
System.out.println("该老师不存在，请重新输入");
return; // 如果老师不存在，退出方法
}
int trueClassTeacher = resQ.getInt("userid"); // 获取老师的用户ID
// 插入班级信息
sql = "INSERT INTO classes (classname, classteacher) VALUES (?, ?)";
try (PreparedStatement pstmtInsert = conn.prepareStatement(sql)) {
pstmtInsert.setString(1, classname);
pstmtInsert.setInt(2, trueClassTeacher);
int res = pstmtInsert.executeUpdate();
if (res > 0) {
System.out.println("添加班级成功!");
}
}
}
} catch (SQLException e) {
throw e; // 向外抛出 SQLException
}
}
//添加课程
public void addCourse() throws SQLException {//增加课程
ResultSet resQ;//查询操作返回值
int res;//增删改操作返回值
// while (true){
System.out.print("请输入课程名：");
courseName = rader.next();//("请输入课程名：")
System.out.print("请输入课程学分：");
courseScore = rader.nextInt();// input('请输入课程学分')
System.out.print("请输入任课老师：");//
//courseTeacher同样是课程表的外键，courseTeacher的真实值是user表的userid
courseTeacher = rader.next();
String sql = "select userid from user where name='" + courseTeacher + "'and role=2";
resQ = Mstmt.executeQuery(sql);
if (resQ.next() == false) {
System.out.println("添加课程失败！");
} else {
int trueCourseTeacher = resQ.getInt("userid");
sql = "insert into course (coursename,coursescore,teacher) values ('" + courseName + "','" + courseScore + "','" + trueCourseTeacher + "')";
res = Mstmt.executeUpdate(sql);
if (res >= 1) {
System.out.println("添加课程成功!");
}
}
}
/* System.out.print("按q退出本步骤:");
if(rader.next()=="q"){
break;
}
break;
}
}
*/
// 修改学生信息
public void updateStudent() throws SQLException, ClassNotFoundException {
System.out.print("请输入要修改的学生姓名：");
String studentName = rader.next();
System.out.print("请输入新的姓名：");
String newName = rader.next();
System.out.print("请输入新的性别（例如：男/女）：");
String newSex = rader.next(); // 获取新的性别
System.out.print("请输入新的密码：");
String newPassword = rader.next(); // 获取新的密码
// 确保加载了数据库驱动类，这可能会抛出ClassNotFoundException
Class.forName("com.mysql.cj.jdbc.Driver");
// 建立数据库连接，这可能会抛出SQLException
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu", "username", "password");
// 使用PreparedStatement来防止SQL注入
String sql = "UPDATE user SET name = ?, sex = ?, password = ? WHERE name = ? AND role = 3";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) { // 使用try-with-resources自动关闭PreparedStatement
// 设置参数值
pstmt.setString(1, newName);
pstmt.setString(2, newSex);
pstmt.setString(3, newPassword);
pstmt.setString(4, studentName);
int res = pstmt.executeUpdate();
if (res > 0) {
System.out.println("学生信息修改成功！");
} else {
System.out.println("未找到该学生或修改失败！");
}
} // try-with-resources结束，PreparedStatement自动关闭
// 确保数据库连接被关闭
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
throw new SQLException("Error closing connection", e);
}
}
}
3.教师部分功能实现代码
public class Teacher {
public Teacher() throws SQLException, ClassNotFoundException {
}
Scanner rader = new Scanner(System.in);
utilss tool1 = new utilss();
String studentRole = "3";//学生权限为3
String studentName, studentSex, studentPasswd;
public void addStudent() throws SQLException, ClassNotFoundException {//增加学生
Connection conn = tool1.getconn();
Statement stmt = conn.createStatement();
int res;
while (true) {
System.out.print("请输入学生姓名：");
studentName = rader.next();//("请输入学生名字：")
System.out.print("请输入学生性别：");
studentSex = rader.next();// input('请输入学生性别:')
System.out.print("请输入学生登陆密码：");
studentPasswd = rader.next();//
String sql = "insert into user (name,sex,password,role)values ('" + studentName + "','" + studentSex + "','" + studentPasswd + "','" + studentRole + "')";
res = stmt.executeUpdate(sql);
if (res >= 1) {
System.out.println("添加学生成功");
tool1.colse(stmt, conn);
break;
} else {
System.out.println("添加学生失败");
}
}
}
//录入成绩
//总体思路 找到该老师带的课 再继续找每门课的所有学生，然后遍历，给每个学生的每门课录入分数
public void addScore(String teacherNm) throws SQLException, ClassNotFoundException {
Scanner scanner = new Scanner(System.in); // 用于获取用户输入
LinkedList<Integer> c_id = new LinkedList<>(); // 课程id
LinkedList<String> c_nm = new LinkedList<>(); // 课程名
Connection conn = null;
try {
conn = tool1.getconn(); // 确保这个方法声明抛出了ClassNotFoundException
if (conn == null) {
throw new SQLException("数据库连接初始化失败。");
}
// 获取老师所教的课程
String sqlGetCourses = "SELECT courseid, coursename FROM course WHERE teacher = (SELECT userid FROM user WHERE name = ?)";
try (PreparedStatement pstmtGetCourses = conn.prepareStatement(sqlGetCourses)) {
pstmtGetCourses.setString(1, teacherNm);
try (ResultSet rsCourses = pstmtGetCourses.executeQuery()) {
while (rsCourses.next()) {
c_id.add(rsCourses.getInt("courseid"));
c_nm.add(rsCourses.getString("coursename"));
}
}
}
// 为每个课程遍历并录入成绩
for (Integer courseId : c_id) {
// 获取该课程下所有学生
String sqlGetStudents = "SELECT userid, name FROM user WHERE role = 3"; // 假设role=3是学生
try (PreparedStatement pstmtGetStudents = conn.prepareStatement(sqlGetStudents);
ResultSet rsStudents = pstmtGetStudents.executeQuery()) {
Set<Integer> scoredStudentIds = new HashSet<>();
// 检查每个学生是否已录入成绩
while (rsStudents.next()) {
int studentId = rsStudents.getInt("userid");
String sqlCheckScore = "SELECT COUNT(*) FROM socre WHERE userid = ? AND courseid = ?";
try (PreparedStatement pstmtCheckScore = conn.prepareStatement(sqlCheckScore)) {
pstmtCheckScore.setInt(1, studentId);
pstmtCheckScore.setInt(2, courseId);
try (ResultSet rsCheckScore = pstmtCheckScore.executeQuery()) {
if (rsCheckScore.next() && rsCheckScore.getInt(1) == 0) {
// 未录入成绩，可以录入
System.out.println("请输入学生 " + rsStudents.getString("name") + " 的 " + c_nm.get(c_id.indexOf(courseId)) + " 成绩：");
int score = scanner.nextInt();
scanner.nextLine(); // 处理换行符
// 执行成绩录入
String sqlInsertScore = "INSERT INTO socre (userid, courseid, score) VALUES (?, ?, ?)";
try (PreparedStatement pstmtInsertScore = conn.prepareStatement(sqlInsertScore)) {
pstmtInsertScore.setInt(1, studentId);
pstmtInsertScore.setInt(2, courseId);
pstmtInsertScore.setInt(3, score);
int rowsAffected = pstmtInsertScore.executeUpdate();
if (rowsAffected > 0) {
System.out.println("成绩录入成功。");
} else {
System.out.println("成绩录入失败，可能该成绩已存在。");
}
}
} else {
// 已录入成绩，跳过
scoredStudentIds.add(studentId);
}
}
}
}
}
}
} catch (ClassNotFoundException e) {
throw new ClassNotFoundException("数据库驱动类未找到: " + e.getMessage(), e);
} finally {
// 确保数据库连接被关闭
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
System.out.println("关闭数据库连接时发生错误: " + e.getMessage());
}
}
}
}
4.学生部分功能实现代码
public class Student {
Scanner reader = new Scanner(System.in);
utilss tooll = new utilss();
LinkedList<Integer> c_id = new LinkedList<Integer>(); // c_id表示课程id
LinkedList<String> c_nm = new LinkedList<String>(); // c_nm表示全部课程名
LinkedList<Integer> allCourseScore = new LinkedList<Integer>(); // 所有课程学分的集合
LinkedList<Integer> scorid = new LinkedList<Integer>(); // 所有课程分数id（scoreid）的集合
public Student() throws SQLException, ClassNotFoundException {
// 学生选课，思路每个学生每学期只能选择一次课程。先获取所有课程集合
}
//学生选课
public void chooseCourse(String username) throws SQLException, ClassNotFoundException {
Connection conn = tooll.getconn();
Statement stmt = conn.createStatement();
int stuNum = getStuNum(username); // 获取学生学号stuNum
// 检查学生是否已经选过课，并获取已选课程数量
String checkSql = "SELECT COUNT(*) FROM socre WHERE userid=" + stuNum;
ResultSet resultSet = stmt.executeQuery(checkSql);
int alreadySelectedCount = 0;
if (resultSet.next()) {
alreadySelectedCount = resultSet.getInt(1);
}
resultSet.close();
System.out.println("请完成选课，每名学生必须选择5门课程。");
if (alreadySelectedCount > 5) {
System.out.println("您已选超过5门课程，无法继续选课。");
} else if (alreadySelectedCount == 5) {
System.out.println("您已选足5门课程。");
} else {
// 假设c_id, c_nm是已经根据配置文件初始化好的集合
List<Integer> c_id = Arrays.asList(3, 4, 5, 6, 9, 10, 11); // courseid
List<String> c_nm = Arrays.asList("数据库", "操作系统", "编译原理", "计算机网络", "化学", "大学物理", "大学语文", "数学高等");
// 用于记录用户选择的课程
List<Integer> selectedCourses = new ArrayList<>();
for (int i = 0; i < c_id.size(); i++) {
if (selectedCourses.size() >= 5 - alreadySelectedCount) {
break; // 已经选足5门课程
}
boolean alreadySelected = selectedCourses.contains(c_id.get(i));
if (!alreadySelected) {
System.out.println((i + 1) + ". " + c_nm.get(i));
System.out.println("是否选择此课程 (1. 是, 2. 否):");
int choice = reader.nextInt();
if (choice == 1) {
selectedCourses.add(c_id.get(i));
System.out.println("已选择课程: " + c_nm.get(i));
}
}
}
// 为用户选择的课程执行SQL插入操作
for (Integer courseId : selectedCourses) {
String sql = "INSERT INTO socre (userid, courseid) VALUES ("
+ stuNum + ", '"
+ courseId + "')";
int res = stmt.executeUpdate(sql);
if (res < 1) {
System.out.println("课程选择失败，请重新选择！");
return; // 如果有插入失败，退出循环
}
}
System.out.println("课程选择成功，您已选择" + selectedCourses.size() + "门课程。");
}
// 关闭资源
stmt.close();
conn.close();
}
//打印成绩
public void showScore(String username) throws SQLException, ClassNotFoundException {
LinkedList<Integer> c_ided = new LinkedList<Integer>(); // 定义c_ided
LinkedList<String> stuChooseCourseed = new LinkedList<String>(); // 学生所选择的课程名
LinkedList<Integer> courseScoreed = new LinkedList<Integer>(); // 学生所选择课程的学分
LinkedList<Integer> courseChengji = new LinkedList<Integer>(); // 学生所选择课程的成绩
Connection conn = tooll.getconn();
Statement stmt = conn.createStatement(); // 定义stmt
int stuNum = getStuNum(username);
String sql = "select courseid, score from socre where userid = " + stuNum;
ResultSet allscoretable = stmt.executeQuery(sql);
while (allscoretable.next()) {
int cid = allscoretable.getInt("courseid");
int score = allscoretable.getInt("score");
c_ided.add(cid); // 记住学生所选课程的id
courseChengji.add(score); // 记住学生所选课程的成绩
}
for (int i = 0; i < c_ided.size(); i++) {
sql = "select coursename, coursescore from course where courseid = " + c_ided.get(i);
ResultSet allCoursetable = stmt.executeQuery(sql);
while (allCoursetable.next()) {
String kechengming = allCoursetable.getString("coursename");
int kechengXuefen = allCoursetable.getInt("coursescore");
stuChooseCourseed.add(kechengming); // 记住学生所选课程的名
courseScoreed.add(kechengXuefen); // 记住学生所选课程的学分
}
allCoursetable.close();
}
System.out.println(username + "同学您好，您所选课程的成绩及学分情况如下：");
for (int i = 0; i < stuChooseCourseed.size(); i++) {
System.out.println("课程: " + stuChooseCourseed.get(i) + "\t\t成绩:" + courseChengji.get(i) + "\t学分:" + courseScoreed.get(i));
}
int totalScore = 0;
int totalChengji = 0;
for (int i = 0; i < courseScoreed.size(); i++) {
totalScore += courseScoreed.get(i);
}
for (int i = 0; i < courseChengji.size(); i++) {
totalChengji += courseChengji.get(i);
}
System.out.println("总学分:" + totalScore);
if (totalChengji == 0 && totalScore == 0) {
System.out.println("您还未选课，请您先选课！");
} else if (totalChengji == 0 && totalScore > 0) {
System.out.println("您的成绩还没有被录入，请所有代课老师帮你录入成绩！");
} else if (totalChengji > 0 && totalScore < 5) {
System.out.println("学分少于5，不及格,建议留级！");
} else if (totalChengji > 0 && totalScore >= 5) {
System.out.println("恭喜,满足升学条件");
}
allscoretable.close();
stmt.close();
conn.close();
}
//查看已选课程
public void viewSelectedCourses(String username) throws SQLException, ClassNotFoundException {
// 获取学生学号
int stuNum = getStuNum(username); // 确保这个方法返回的是学生学号的正确值
// 构建SQL查询语句，查询学生所选课程
// 确保 "courses" 表名和列名与数据库中的实际情况一致
String sql = "SELECT course.coursename " + // 注意这里应该是 "courses" 而不是 "course"
"FROM course " +
"JOIN socre ON course.courseid = socre.courseid " + // 移除了别名，因为只有一个 courseid 列
"WHERE socre.userid = " + stuNum;
// 执行查询
Connection conn = tooll.getconn();
Statement stmt = conn.createStatement();
ResultSet resultSet = stmt.executeQuery(sql); // 只执行一次查询
// 检查查询结果并展示
boolean hasCourses = false; // 用于标记是否检索到课程
System.out.println("您选择的课程如下：");
while (resultSet.next()) { // 遍历结果集
hasCourses = true; // 至少检索到一门课程
String courseName = resultSet.getString("coursename");
System.out.println(courseName);
}
// 关闭资源
resultSet.close();
stmt.close();
conn.close();
// 如果没有检索到任何课程，打印提示信息
if (!hasCourses) {
System.out.println("您还没有选择任何课程。");
}
}
5.数据库设计
CREATE TABLE `classes` (
`classid` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
`classname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级名字',
`classteacher` int(0) NULL DEFAULT NULL COMMENT '班主任',
`classnum` int(0) NULL DEFAULT NULL COMMENT '班级人数',
PRIMARY KEY (`classid`) USING BTREE,
INDEX `classteacher`(`classteacher`) USING BTREE,
CONSTRAINT `classes_ibfk_1` FOREIGN KEY (`classteacher`) REFERENCES `user` (`userid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of classes
-- ----------------------------
INSERT INTO `classes` VALUES (1, '软工', 24, NULL);
INSERT INTO `classes` VALUES (2, '计科', 25, NULL);
INSERT INTO `classes` VALUES (3, '大数据', 33, NULL);
INSERT INTO `classes` VALUES (4, '食品1班', 35, NULL);
INSERT INTO `classes` VALUES (7, 'ceshibanji', 35, NULL);
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`courseid` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
`coursename` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '课程名',
`coursescore` int(0) NULL DEFAULT NULL COMMENT '课程学分',
`teacher` int(0) NULL DEFAULT NULL COMMENT '任课老师',
PRIMARY KEY (`courseid`) USING BTREE,
INDEX `teacher`(`teacher`) USING BTREE,
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`teacher`) REFERENCES `user` (`userid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (3, '数据库', 3, 24);
INSERT INTO `course` VALUES (4, '操作系统', 4, 24);
INSERT INTO `course` VALUES (5, '编译原理', 2, 25);
INSERT INTO `course` VALUES (6, '计算机网络', 5, 33);
INSERT INTO `course` VALUES (7, '生物', 5, 36);
INSERT INTO `course` VALUES (8, '化学', 3, 36);
INSERT INTO `course` VALUES (9, '大学物理', 3, 35);
INSERT INTO `course` VALUES (10, '大学语文', 2, 35);
INSERT INTO `course` VALUES (11, '数学高等', 4, 36);
-- ----------------------------
-- Table structure for socre
-- ----------------------------
DROP TABLE IF EXISTS `socre`;
CREATE TABLE `socre` (
`socreid` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
`userid` int(0) NOT NULL COMMENT '学生id',
`courseid` int(0) NULL DEFAULT NULL COMMENT '课程id',
`score` int(0) NULL DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`socreid`) USING BTREE,
INDEX `userid`(`userid`) USING BTREE,
INDEX `courseid`(`courseid`) USING BTREE,
CONSTRAINT `socre_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `socre_ibfk_2` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of socre
-- ----------------------------
INSERT INTO `socre` VALUES (4, 34, 5, 65);
INSERT INTO `socre` VALUES (9, 30, 4, 78);
INSERT INTO `socre` VALUES (10, 31, 4, 98);
INSERT INTO `socre` VALUES (11, 30, 3, 78);
INSERT INTO `socre` VALUES (12, 31, 3, 45);
INSERT INTO `socre` VALUES (14, 29, 5, 77);
INSERT INTO `socre` VALUES (15, 34, 6, 45);
INSERT INTO `socre` VALUES (16, 34, 5, 65);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`studentid` int(0) NOT NULL,
`studentclass` int(0) NOT NULL,
PRIMARY KEY (`studentid`, `studentclass`) USING BTREE,
INDEX `studentclass`(`studentclass`) USING BTREE,
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`studentid`) REFERENCES `user` (`userid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `student_ibfk_2` FOREIGN KEY (`studentclass`) REFERENCES `classes` (`classid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (28, 1);
INSERT INTO `student` VALUES (29, 1);
INSERT INTO `student` VALUES (30, 2);
INSERT INTO `student` VALUES (31, 2);
INSERT INTO `student` VALUES (34, 3);
-- ----------------------------
-- Table structure for user
-- ----------------------------
CREATE TABLE `user` (
`userid` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '123456',
`role` int(0) NOT NULL DEFAULT 2,
PRIMARY KEY (`userid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 46 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (24, 'teacher1', '男', 'teacher1', 2);
INSERT INTO `user` VALUES (25, 'teacher2', '女', 'teacher2', 2);
INSERT INTO `user` VALUES (28, 'stu1', '男', 'stu1', 3);
INSERT INTO `user` VALUES (29, 'stu2', '女', 'stu2', 3);
INSERT INTO `user` VALUES (30, 'stu3', '男', 'stu3', 3);
INSERT INTO `user` VALUES (31, 'stu4', '男', 'stu4', 3);
INSERT INTO `user` VALUES (32, 'admin', '男', 'admin', 1);
INSERT INTO `user` VALUES (33, 'teacher3', '女', 'teacher3', 2);
INSERT INTO `user` VALUES (34, 'stu5', '男', 'stu5', 3);
INSERT INTO `user` VALUES (35, 'sanyi', '男', '123', 2);
INSERT INTO `user` VALUES (36, 'sanmao', '女', '123', 2);
INSERT INTO `user` VALUES (44, 'stu7', 'nan', '123', 3);
INSERT INTO `user` VALUES (45, 'stu6', 'nv', '1234', 3);
INSERT INTO `user` VALUES (49, '65', '564', '45', 3);
SET FOREIGN_KEY_CHECKS = 1;
6.数据库关系图
4826
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
