Java/python实现学生选课/学生信息管理系统（连接数据库）

说明：Python实现版请参考以下连接：

/m_target/article/details/101369346?spm=1001..3001.5502

本文是在上面的基础上进行扩展：功能更加完善！

Java实现

Java/python实现学生选课/学生信息管理系统（连接数据库）需求代码运行截图附sql

需求

一、需求

管理员：

添加教师：一个教师作为一个班级的班主任，同时也作为一个或多个课程的代课老师

添加课程：指定课程名，学分，任课老师。

添加班级：指定班级名称，班主任

老师：

添加学生：作为一个班级的班主任，该老师为其班级添加学生

录入成绩：作为一门课程的任课老师，该老师给出他所带课程的学生成绩

查看学生成绩：作为一门课程的任课老师，查看学生本门课程的成绩

学生：

学生可以自己选课

查看自己成绩单

代码

二 、代码

包：Contorl下面的utilss类

package Contorl;import java.sql.*;//该类用来连接数据库public class utilss {String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";String DB_URL = "jdbc:mysql://localhost:3306/pet?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";String USER = "root";String PASS = "123";public Connection getconn() throws ClassNotFoundException, SQLException {Class.forName(JDBC_DRIVER);//注册数据库驱动Connection con1= DriverManager.getConnection(DB_URL,USER,PASS);//DriverManager类的主要功能是获取url对应数据库的连接(获取Connection对象)return con1;}public void colse(Statement stm,Connection con) throws SQLException {stm.close();con.close();}}

包：Model下面有Login、Manager、Student、Teacher4个类

Login.java

package Model;import Contorl.*;import java.sql.*;//登录类public class Login {public ResultSet userLogin(String username,String passwrod,String role) throws SQLException, ClassNotFoundException {ResultSet res;utilss ut1=new utilss();Connection conn=ut1.getconn();Statement stmt=conn.createStatement();String sql="select * from user where name='"+username+"' and password='"+passwrod+"' and role='"+role+"'";ResultSet RS=stmt.executeQuery(sql);return RS;}}

Manager.java

package Model;//管理员/*添加教师：一个教师作为一个班级的班主任，同时也作为一个或多个课程的代课老师添加课程：指定课程名，学分，任课老师。添加班级：指定班级名称，班主任*/import java.sql.*;import java.util.*;import Contorl.*;public class Manager {public Manager() throws SQLException, ClassNotFoundException {}static Scanner rader = new Scanner(System.in);utilss tool1=new utilss();String teacherName,teacherSex,teacherPasswd;String teacherRole="2";//为请输入权限老师代号2String classname,classTeacher;String courseName,courseTeacher;//课程名，代课老师int courseScore;//课程学分//管理员添加老师Connection Mconn=tool1.getconn();//Mconn代表管理员专用连接Statement Mstmt=Mconn.createStatement();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;}*/String sql= "insert into user (name,sex,password,role)values ('"+teacherName+"','"+teacherSex+"','"+teacherPasswd+"','"+teacherRole+"')";res=Mstmt.executeUpdate(sql);if (res>=1){System.out.println("添加老师成功");}else {System.out.println("添加失败");}}//注意 如果图省事只定义一个全局的连接对象conn,那么在对数据库进行增删改差等方法中就不要Close连接，要不然绝壁报错，只要最后关闭一下就行了public void addClass() throws SQLException {//增加班级//班级名，班主任，添加班主任的时候判断老师表中是否存在该老师ResultSet resQ;//查询操作返回值int res;//增删改操作返回值// while(true){System.out.print("请输入班级名：");classname =rader.next();//("请输入班级名：")System.out.print("请输入班主任：");classTeacher =rader.next();// input('请输入班主任')String sql="select userid from user where name='"+classTeacher+"'and role=2";resQ=Mstmt.executeQuery(sql);if (resQ.next()==false){System.out.println("该老师不存在，请重新输入");}else {//注意：班级表的列字段classteacher是相对于表user（userid）的外键，也就是说真实的上的classteacher=userid，// 但此时classteacher=user中的name;所以我们要想办法获取name(classteacher)对应的userid，也就是下面这行代码int trueClassTeacher=resQ.getInt("userid");sql="insert into classes (classname,classteacher) values ('"+classname+"','"+trueClassTeacher+"')";res=Mstmt.executeUpdate(sql);if (res>=1){System.out.println("添加班级成功!");}}/* System.out.print("按q退出本步骤:");if(rader.next()=="q"){break;}break;}*/}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表的useridcourseTeacher=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 Mcolse() throws SQLException {Mstmt.close();Mconn.close();}}

Student.java

package Model;//学生类import Contorl.utilss;import java.sql.*;import java.util.LinkedList;import java.util.Scanner;public class Student {Scanner rader = new Scanner(System.in);utilss tool1 = new utilss();LinkedList<Integer> c_id = new LinkedList<Integer>();//c_id表示课程idLinkedList<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 = tool1.getconn();Statement stmt = conn.createStatement();//sql发射器LinkedList<Integer> s_id = new LinkedList<Integer>();//s_id表示学生idLinkedList<String> s_nm = new LinkedList<String>();//c_nm表示学生名int stuNum=getStuNum(username);//获取学生学号stuNumSystem.out.println("新的一学期选课开始选课了,每学期只能选择一次课，请慎重选择!");//先获取所有课程集合String sql="select courseid,coursename,coursescore from course";ResultSet allCourse=stmt.executeQuery(sql);while (allCourse.next()) {//将课程id和课程名存进列表c_id,c_nm中int cid = allCourse.getInt("courseid");int sco = allCourse.getInt("coursescore");String cnm = allCourse.getString("coursename");c_id.add(cid);c_nm.add(cnm);allCourseScore.add(sco);}sql="select socreid,score from socre";//获取分数id,ResultSet allScorid=stmt.executeQuery(sql);while (allScorid.next()) {//将学分id存入集合中int scoid = allScorid.getInt("socreid");scorid.add(scoid);}int realScoreid=scorid.get(scorid.size()-1)+1;//realScoreid为socreid 设置这个值是为了让分数id自增，在学生自我选课的时候方便向socre表中插入数据for (int i = 0; i < c_id.size(); i++) {System.out.println("是否选择:"+c_nm.get(i));System.out.println("选择请按1,不选择请按2:");if (rader.nextInt()==1){sql="insert into socre values ('"+(realScoreid++)+"','"+stuNum+"','"+c_id.get(i)+"',null)";int res= stmt.executeUpdate(sql);if (res>=1){System.out.println("选择成功！");}}else{System.out.println("操作成功,请继续选课!");}}}//打印学生成绩单 包括 所修科目 学分 及其对应的成绩//首先根据学生姓名找到 学生id（stuNum）,然后根据学生id，在表score中找到对应的课程id 和成绩 然后根据课程id在course中找到课程名和课程学分//主要涉及4个集合 课程id 课程名 课程学分 成绩public void showScore(String username) throws SQLException, ClassNotFoundException {LinkedList<String> stuChooseCourseed = new LinkedList<String>();//学生所选择的课程名LinkedList<Integer> courseScoreed = new LinkedList<Integer>();//学生所选课程的学分LinkedList<Integer> courseChengji = new LinkedList<Integer>();//学生所选课程的成绩LinkedList<Integer> c_ided = new LinkedList<Integer>();// c_ided表示学生所选课程id c_id是所有的课程idint stuNum=getStuNum(username);//学生idint totalScore=0;//初始学分为0int totalChengji=0;//初始总成绩为0Connection conn = tool1.getconn();Statement stmt = conn.createStatement();//sql发射器String sql="select courseid,score from socre where userid='"+stuNum+"' ";//获取分数id,ResultSet allscoretable=stmt.executeQuery(sql);while (allscoretable.next()){int cid=allscoretable.getInt("courseid");int score =allscoretable.getInt("score");c_ided.add(cid);//记住学生所选课程的idcourseChengji.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);//记住学生所选课程的学分}}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));}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("恭喜,满足升学条件");}}public int getStuNum(String username) throws SQLException, ClassNotFoundException {Connection conn = tool1.getconn();Statement stmt = conn.createStatement();//sql发射器String sql="select userid from user where name='"+username+"' ";//获取该学生的学号idResultSet stu=stmt.executeQuery(sql);int stuNum=0;while (stu.next()){stuNum=stu.getInt("userid");//获取学生学号stuNum}return stuNum;}}

Teacher.java

package Model;//老师类import java.sql.*;import java.util.*;import Contorl.*;public class Teacher {public Teacher() throws SQLException, ClassNotFoundException {}Scanner rader = new Scanner(System.in);utilss tool1 = new utilss();String studentRole = "3";//学生权限为3String 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 {//teacherNm表示代课老师，因为只有代课老师才能给分数啊LinkedList<Integer> c_id = new LinkedList<Integer>();//c_id表示课程idLinkedList<String> c_nm = new LinkedList<String>();//c_nm表示课程名LinkedList<Integer> s_id = new LinkedList<Integer>();//s_id表示学生idLinkedList<String> s_nm = new LinkedList<String>();//c_nm表示学生名HashSet <Integer> ok_ss_id=new HashSet<Integer>();//ok_ss_id为去重后的ss_id集合Connection conn = tool1.getconn();Statement stmt = conn.createStatement();//sql发射器//Statement u_stmt = conn.createStatement();//用户表的sql发射器//下面这条语句将①②合二为一了 ①在User中按照teachernm查找userid ②在course中按照teacher(userid)查找老师teachernm带的课程名、课程idString sql = "select courseid,coursename from course,user where user.userid=teacher and user.name='" + teacherNm + "' ";ResultSet allCourse = stmt.executeQuery(sql);//allCourse为返回该老师带的所有课的记录while (allCourse.next()) {//将课程id和课程名存进列表c_id,c_nm中int cid = allCourse.getInt("courseid");String cnm = allCourse.getString("coursename");c_id.add(cid);c_nm.add(cnm);}for (int i = 0; i < c_id.size(); i++) {//该老师带所有课//下面这条语句将①②合二为一了 ①在score中按照课程id查找学生的userid ②在user中按照学生的userid查找学生名和学生idsql = "select user.name,user.userid from user,socre where user.userid=socre.userid and socre.courseid='" + c_id.get(i) + "' ";ResultSet allStudent = stmt.executeQuery(sql);//找到本门课的所有学生while (allStudent.next()) {//将学生sid和学生名snm存进列表s_id,s_nm中int sid = allStudent.getInt("userid");String snm = allStudent.getString("name");s_id.add(sid);s_nm.add(snm);ok_ss_id.addAll(s_id);//去重。}if (c_id.size()==0){System.out.println(teacherNm+"老师您好，您没有代课，谢谢！");}else if (c_id.size()!=0 && s_nm.size()==0){System.out.println(teacherNm+"老师您好，您的课程目前没有学生选课！，谢谢！");}for (int j = 0; j < ok_ss_id.size(); j++) {//该老师带1门课System.out.println("请输入学生" + s_nm.get(j) + "的" + c_nm.get(i) + "成绩:");int s_score = rader.nextInt();//s_score为学生成绩sql = "update socre SET score='" + s_score + "' where userid='" + s_id.get(j) + "'and courseid='" + c_id.get(i) + "' ";int res = stmt.executeUpdate(sql);if (res >= 1) {System.out.println("学生" + s_nm.get(j) + "的" + c_nm.get(i) + "成绩添加成功!");} else {System.out.println("学生" + s_nm.get(j) + "的" + c_nm.get(i) + "成绩添加失败!");}}}}//查看学生的成绩//思路：先通过老师名找到该老师带的课，然后找到每门课所有的学生，并查看他们的成绩public void showStuScore(String teacherNm) throws SQLException, ClassNotFoundException {Connection conn = tool1.getconn();Statement stmt = conn.createStatement();//课程表的sql发射器//将某个老师带的课的课程id 课程名 以及每门课程所有学生的学生id和学生名存入下面四个集合中LinkedList<Integer> c_id = new LinkedList<Integer>();//c_id表示课程idLinkedList<String> c_nm = new LinkedList<String>();//c_nm表示课程名LinkedList<Integer> ss_id = new LinkedList<Integer>();//ss_id表示学生idHashSet <Integer> ok_ss_id=new HashSet<Integer>();//ok_ss_id为去重后的ss_id集合LinkedList<String> s_nm = new LinkedList<String>();//c_nm表示学生名LinkedList<Integer> s_cor = new LinkedList<Integer>();//s_cor表示学生的分数String sql = "select courseid,coursename from course,user where user.userid=teacher and user.name='" + teacherNm + "' ";ResultSet allCourse = stmt.executeQuery(sql);//allCourse为返回该老师带的所有课的记录while (allCourse.next()) {//将课程id和课程名存进列表c_id,c_nm中int cid = allCourse.getInt("courseid");String cnm = allCourse.getString("coursename");c_id.add(cid);c_nm.add(cnm);}for (int i = 0; i <c_id.size() ; i++) {//下面这条语句将①②合二为一了 ①在score中按照课程id查找学生的userid ②在user中按照学生的userid查找学生名和学生idsql = "select user.name,user.userid from user,socre where user.userid=socre.userid and socre.courseid='" + c_id.get(i) + "' ";ResultSet allStudent = stmt.executeQuery(sql);//找到本门课的所有学生while (allStudent.next()) {//将学生sid和学生名snm存进列表s_id,s_nm中 //一定要注意，不同课程中的学生可能是重复的，所以向集合中增加学生的时候一定要判断是不是重复值int sid = allStudent.getInt("userid");String snm = allStudent.getString("name");ss_id.add(sid);s_nm.add(snm);ok_ss_id.addAll(ss_id);//去重。}}if (c_id.size()==0){System.out.println(teacherNm+"老师您好，您没有代课，谢谢！");}else if (c_id.size()!=0 && s_nm.size()==0){System.out.println(teacherNm+"老师您好，您的课程目前没有学生选课！，谢谢！");}for (int i = 0; i < c_id.size(); i++) {//该老师带所有课for (int j = 0; j < ok_ss_id.size(); j++) {//该老师带1门课//找到每门课所有学生的成绩sql = "select score from socre where courseid='" + c_id.get(i) + "'and userid='" + ss_id.get(j) + "' ";ResultSet allScore = stmt.executeQuery(sql);while (allScore.next()) {int scor = allScore.getInt("score");s_cor.add(scor);}System.out.println("学生" + s_nm.get(j) + "的" + c_nm.get(i) + "成绩为:" + s_cor.get(j));}}}}

包：View下面有view.java 1个类

package View;//视图类作用：import Model.*;import Contorl.utilss;import com.mysql.cj.log.NullLogger;import java.sql.ResultSet;import java.sql.SQLException;import java.util.*;//作用：前端操作界面public class view {String username;String password;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();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("===============退出请输入: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("===============退出请输入: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("=========退出请输入: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();ResultSet res = logi.userLogin(username, password, "1");if (res.next() == false) {//账户密码输入错误的情况System.out.println("对不起，用户名或密码错误!");} else {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);}else {System.out.println("输入有误，请重新输入!");}}private void studentFram(String username) throws SQLException, ClassNotFoundException {Boolean isChooseCourse=false;//用来判断是否已经选课 false没选 true选了while (true){studentUi();System.out.print("请根据需要输入:");//0 退出 1 自主选课 2 查看成绩单 3查看以修学分 4查看课表int res=rader.nextInt();if(res==1){//1 自主选课while (isChooseCourse==false){stu.chooseCourse(username);isChooseCourse=true;}System.out.println("本学期选课已结束，无法再次选课!");}else if (res==2){//2 查看成绩单stu.showScore(username);}else if (res==0){//0 退出系统break;}else {System.out.println("输入有误,请重新输入:");}}}//管理员界面public void managerFram() throws SQLException, ClassNotFoundException {while (true){managerUi();System.out.print("请根据需要输入:");//0 退出 1 添加老师 2 添加班级 3添加课程int res=rader.nextInt();if(res==1){//1 添加老师man.addTeacher();}else if (res==2){//2 添加班级man.addClass();}else if (res==3){//3 添加课程man.addCourse();}else if (res==0){//3 退出man.Mcolse();//在这里关闭管理员的数据库连接对象break;}else {System.out.println("输入有误,请重新输入:");}}}//老师界面private void teacherFram(String teacherNm) throws SQLException, ClassNotFoundException {while (true){teacherUi();System.out.print("请根据需要输入:");//0 退出 1 添加学生 2 录入成绩 3查看学生成绩 3给学生选课 0 退出int res=rader.nextInt();if(res==1){//1 添加学生tea.addStudent();}else if (res==2){//2 录入成绩tea.addScore(teacherNm);}else if (res==3){//3 查看学生成绩tea.showStuScore(teacherNm);}else if (res==0){//0 退出break;}else {System.out.println("输入有误,请重新输入:");}}}public static void main(String[] args) throws SQLException, ClassNotFoundException {view v1=new view();while (true){v1.mainUi();//主界面System.out.print("请根据需要输入:");v1.chooseRole();}}}

运行截图

主界面：

管理员界面：

学生界面：

学生选课：

学生打印成绩单：

附sql

/*Navicat Premium Data TransferSource Server : 65Source Server Type : MySQLSource Server Version : 80028Source Host : localhost:3306Source Schema : petTarget Server Type : MySQLTarget Server Version : 80028File Encoding : 65001Date: 13/04/ 12:53:01*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for classes-- ----------------------------DROP TABLE IF EXISTS `classes`;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-- ----------------------------DROP TABLE IF EXISTS `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;