数据库实验报告八（头歌）

小稚（可露丽版）

已于 2023-11-29 20:32:15 修改

阅读量1.4k

点赞数 3

文章标签：数据库 sql mysql

于 2023-11-29 18:08:10 首次发布

本文链接：https://blog.csdn.net/Coisioi/article/details/134678698

版权

前言：

1.在进行评测的时候，如果出现“error: could not connect to server: could not connect to server:”等服务未启动的错误提醒，请进入“命令行”窗口，执行“sys_ctl start -l logfile”语句，再进行评测。

2.在进行第一关的创建数据库时，要将int8改写为BIGINT，并且不再后面加精度，int4改写为INTEGER也不加精度，其他的不用改，下面的代码是查看实训时系统自动转换的问题。不改的话会一直报错！！！

3.其他问题可私信尝试解决。

第1关：创建数据表

CREATE TABLE subject(
    subject_id BIGINT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    optiona VARCHAR(255),
    optionb VARCHAR(255),
    optionc VARCHAR(255),
    optiond VARCHAR(255),
    correct_answer INTEGER,
    answer_explanation VARCHAR(255),
    type INTEGER,
    difficulty_level INTEGER
);

CREATE TABLE t_user(
    user_id BIGINT PRIMARY KEY,
    create_time TIMESTAMP,
    password VARCHAR(64) NOT NULL,
    name VARCHAR(30) NOT NULL,
    sex CHAR(3),
    telephone CHAR(11),
    email VARCHAR(20),
    identification CHAR(28)
);

CREATE TABLE paper(
    paper_id BIGINT PRIMARY KEY,
    create_time TIMESTAMP,
    name VARCHAR(255) NOT NULL,
    duration INTEGER,
    type INTEGER
);

CREATE TABLE exam(
    exam_id BIGINT PRIMARY KEY,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    user_id BIGINT,
    paper_id BIGINT,
    score INTEGER,
    FOREIGN KEY(user_id) REFERENCES t_user(user_id),
    FOREIGN KEY(paper_id) REFERENCES paper(paper_id)
);

CREATE TABLE exam_paper(
    paper_id BIGINT,
    subject_id BIGINT,
    number INTEGER,
    value INTEGER,
    PRIMARY KEY(paper_id,subject_id),
    FOREIGN KEY(subject_id) REFERENCES subject(subject_id),
    FOREIGN KEY(paper_id) REFERENCES paper(paper_id)
);

CREATE TABLE record(
    exam_id BIGINT,
    subject_id BIGINT,
    user_answer INTEGER,
    point INTEGER,
    PRIMARY KEY(exam_id,subject_id),
    FOREIGN KEY(exam_id) REFERENCES exam(exam_id),
    FOREIGN KEY(subject_id) REFERENCES subject(subject_id)
);

第2关：在各个表中录入相应数据内容

insert INTO subject(subject_id,title,optiona,optionb,optionc,optiond,correct_answer,answer_explanation,type,difficulty_level)
VALUES(1,'驾驶机动车应当随身携带哪种证件？','身份证','职业资格证','工作证','驾驶证',4,'驾驶机动车时，应当随身携带机动车驾驶证。',1,1),
(2,'机动车驾驶人初次申领驾驶证后的实习期是多长时间？','12个月','16个月','18个月','6个月',1,'机动车驾驶人初次取得汽车类准驾车型或者初次取得摩托车类准驾车型后的12个月为实习期。',2,3),
(3,'在实习期内驾驶机动车的，应当在车身后部粘贴或者悬挂哪种标志？','注意避让标志','统一式样的实习标志','注意车距标志','注意新手标志',2,'在实习期内驾驶机动车的，应当在车身后部粘贴或者悬挂统一式样的实习标志。',3,3);
insert INTO t_user(user_id,create_time,password,name,sex,telephone,email,identification)
VALUES(1,'2022-05-20 17:26:00','123456','李欣','男','18712345123','lixin@163.com','12345'),
(2,'2022-05-20 09:31:00','123456','赵玲','女','15212345123','zhaoling@163.com','13524'),
(3,'2022-05-21 11:01:00','123456','王立','男','13512345123','wangli@163.com','14235');
insert into paper(paper_id,create_time,name,duration,type)
VALUES(1,'2022-07-19 17:00:00','第一套',60,1),
(2,'2022-07-19 17:00:00','第二套',60,2),
(3,'2022-07-19 17:00:00','第三套',60,3);
insert into exam(exam_id,start_time,end_time,user_id,paper_id,score)
VALUES(1,'2022-09-10 09:00:00','2022-09-10 10:00:00',1,1,86),
(2,'2022-09-10 09:00:00','2022-09-10 10:00:00',2,2,92),
(3,'2022-09-10 09:00:00','2022-09-10 10:00:00',3,3,90);
insert into exam_paper(paper_id	,subject_id,number,value)
VALUES(1,1,3,5),
(2,2,2,10),
(3,3,1,5);
insert into record(exam_id,subject_id,user_answer,point)
VALUES(1,3,2,5),
(2,1,3,0),
(3,2,1,10);

第3关：数据查询操作

package step1;

import java.sql.*;

public class QueryRecord {
        static final String JDBC_DRIVER = "com.kingbase8.Driver";
        static final String DB_URL = "jdbc:kingbase8://127.0.0.1:54321/EXAM_DB";
        static final String USER = "system";
        static final String PASS = "123456";

        /**1】
         * @param connection 数据库连接
         * @param tableName  表名
         * @param number     试题号
         * @return
         */

        // 请补全此函数
        public ResultSet querySubiectById(Connection connection, String tableName, int number) {
            Statement stmt = null;
            ResultSet result = null;
            String sqlScript =  "SELECT * FROM " + tableName + " where subject_id = \'" + number + "\'";  //  补全
            try {
                stmt = connection.createStatement();      //  补全
                result = stmt.executeQuery(sqlScript);
            } catch (SQLException e) {
                e.printStackTrace();
            }
                return result;    
        }


        public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
            Connection connection = null;
            try {
                Class.forName(jdbc_driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            try {
                connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return connection;
        }
    }

第4关数据插入操作

package step2;


import java.sql.*;


public class InsertRecord {

    static final String JDBC_DRIVER = "com.kingbase8.Driver";
    static final String DB_URL = "jdbc:kingbase8://localhost:54321/EXAM_DB";
    static final String USER = "SYSTEM";
    static final String PASS = "123456";

    /**
     * @param connection 数据库连接
     * @param subject_id 试题号
     * @param title 题干
     * @param type 试题类型
     */

    // 请补全此函数
    public void InsertRecord(Connection connection, int subject_id, String title, int type) {
        Statement stmt = null;
        String sqlScript = "insert into subject(subject_id,title,type) values(4,'不得驾驶具有安全隐患的机动车上道路行驶',1) ";       //  补全
        try {
            stmt = connection.createStatement();
            stmt.executeUpdate(sqlScript);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
        Connection connection = null;

        try {
            Class.forName(jdbc_driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;
    }

    public void selectSubject(Connection connection) {
        try {
            String sql = " SELECT * FROM subject ";
            PreparedStatement psta = connection.prepareStatement(sql);
            ResultSet rs = psta.executeQuery();
            System.out.println("------------------------------------------");
            while (rs.next()) {
                int suject_id = rs.getInt("subject_id");
                String title = rs.getString("title");
                int type = rs.getInt("type");
                System.out.println(suject_id + "\t" + title + "\t" + type);
            }
            rs.close();
            psta.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

第5关数据删除操作

package step3;

import java.sql.*;

public class DeleteRecord {


    static final String JDBC_DRIVER = "com.kingbase8.Driver";
    static final String DB_URL = "jdbc:kingbase8://localhost:54321/EXAM_DB";

    static final String USER = "SYSTEM";
    static final String PASS = "123456";

    /**
     *
     * @param connection 数据库连接
     * @param tableName 数据库表名
     * @param number 试题号
     * @return
     */
    public int deleteRecordById(Connection connection, String tableName, int number) {
        Statement stmt = null;
        int updatedNum = 0;
        String sqlScript = "DELETE FROM"+" "+tableName+" "+"where subject_id=\'"+number+"\'";          //  补全
        try {          //  补全
            stmt = connection.createStatement() ;              //补全
            updatedNum = stmt.executeUpdate(sqlScript);          //  补全
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return updatedNum;
    }

    public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
        Connection connection = null;
        try {
            Class.forName(jdbc_driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public void selectSubject(Connection connection) {
        try {
            String sql = " SELECT * FROM subject ";//查询全部subject记录
            PreparedStatement psta = connection.prepareStatement(sql);
            ResultSet rs = psta.executeQuery();
            System.out.println("------------------------------------------");
            while (rs.next()) {
                int suject_id = rs.getInt("subject_id");
                String title = rs.getString("title");
                System.out.println(suject_id + "\t" + title );
            }

            rs.close();
            psta.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

第6关数据更新操作

package step4;

import java.sql.*;

public class UpdateRecord {

    static final String JDBC_DRIVER = "com.kingbase8.Driver";
    static final String DB_URL = "jdbc:kingbase8://localhost:54321/EXAM_DB";

    static final String USER = "SYSTEM";
    static final String PASS = "123456";

    /**
     *
     * @param connection 数据库连接
     * @param id 试题号
     * @param tableName 数据库表名
     * @param answer 答案
     * @return
     */
    public int updateAnswerById(Connection connection, int id, String tableName,  int answer) {
        Statement stmt = null;
        int updatedNum = 0;
        String sqlScript =  "update "+tableName+" set correct_answer=\'"+answer+"\'"+"where subject_id=\'"+id+"\'";           //  补全
        try {
            stmt = connection.createStatement();
            updatedNum = stmt.executeUpdate(sqlScript);
        } catch (SQLException e) {
            e.printStackTrace();
        }
         return updatedNum;
    }

    // 建立与指定数据库的连接，并返回该连接
    public Connection getConnection(String jdbc_driver, String db_url, String db_user, String db_passwd) {
        Connection connection = null;

        //注册JDBC驱动
        try {
            Class.forName(jdbc_driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        //创建于指定数据库的连接
        try {
            connection = (Connection) DriverManager.getConnection(db_url, db_user, db_passwd);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;
    }

    //查询数据库
    public void selectSubject(Connection connection) {
        try {
            String sql = " SELECT * FROM subject ";//查询全部subject记录
            PreparedStatement psta = connection.prepareStatement(sql);
            ResultSet rs = psta.executeQuery();
            System.out.println("------------------------------------------");
            while (rs.next()) {
                int suject_id = rs.getInt("subject_id");
                String title = rs.getString("title");
                String optionA = rs.getString("optionA");
                String optionB = rs.getString("optionB");
                String optionC = rs.getString("optionC");
                String optionD = rs.getString("optionD");
                int correct_answer = rs.getInt("correct_answer");
                System.out.println(suject_id + "\t" + title + "\t" + optionA+ "\t" + optionB+ "\t" + optionC+ "\t" + optionD+ "\t" + correct_answer);
            }
//        步骤6：清理资源
            rs.close();
            psta.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}