头歌数据库 实验八（必做）

        第1关：创建数据表

        

---------- BEGIN ---------- 
-- 创建试题表
CREATE TABLE subject (
  subject_id int8 PRIMARY KEY NOT NULL,
  title varchar(255) NOT NULL,
  optiona varchar(255),
  optionb varchar(255),
  optionc varchar(255),
  optiond varchar(255),
  correct_answer int4,
  answer_explanation varchar(255),
  type int4,
  difficulty_level int4
--constraint exam_paper_subject_id_fkey foreign key (subject_id) references subject(subject_id),
--constraint record_subject_id_fkey foreign key (subject_id) references subject(subject_id)
);

-- 创建学员表
CREATE TABLE t_user (
  user_id int8 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 int8 PRIMARY KEY,
  create_time timestamp,
  name varchar(255) NOT NULL,
  duration int4,
  type int4
);

-- 创建考试表
CREATE TABLE exam (
  exam_id int8 PRIMARY KEY,
  start_time timestamp,
  end_time timestamp,
  user_id int8,
  paper_id int8,
  score int4
);

-- 创建试卷试题表
CREATE TABLE exam_paper (
  paper_id int8,
  subject_id int8,
  number int4,
  value int4,
  PRIMARY KEY (paper_id, subject_id)
);

-- 创建试卷记录表
CREATE TABLE record (
  exam_id int8,
  subject_id int8,
  user_answer int4,
  point int4,
  PRIMARY KEY (exam_id, subject_id)
);
ALTER TABLE exam_paper
ADD CONSTRAINT exam_paper_subject_id_fkey
FOREIGN KEY (subject_id)
REFERENCES subject(subject_id);

ALTER TABLE record
ADD CONSTRAINT record_subject_id_fkey
FOREIGN KEY (subject_id)
REFERENCES subject(subject_id);

ALTER TABLE exam
ADD CONSTRAINT exam_user_id_fkey
FOREIGN KEY (user_id)
REFERENCES t_user(user_id);
ALTER TABLE exam
ADD CONSTRAINT exam_paper_id_fkey
FOREIGN KEY (paper_id)
REFERENCES paper(paper_id);

ALTER TABLE exam_paper
ADD CONSTRAINT exam_paper_paper_id_fkey
FOREIGN KEY (paper_id)
REFERENCES paper(paper_id);

ALTER TABLE record
ADD CONSTRAINT record_exam_id_fkey
FOREIGN KEY (exam_id)
REFERENCES exam(exam_id);


---------- END ---------- 

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

---------- BEGIN ---------- 
-- 在试题表（subject）中插入数据
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);

-- 在学员表（t_user）中插入数据
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');

-- 在试卷表（paper）中插入数据
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);

-- 在考试表（exam）中插入数据
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);

-- 在试卷试题表（exam_paper）中插入数据
INSERT INTO exam_paper (paper_id, subject_id, number, value)
VALUES 
  (1, 1, 3, 5),
  (2, 2, 2, 10),
  (3, 3, 1, 5);

-- 在试卷记录表（record）中插入数据
INSERT INTO record (exam_id, subject_id, user_answer, point)
VALUES 
  (1, 3, 2, 5),
  (2, 1, 3, 0),
  (3, 2, 1, 10);

---------- END ---------- 

第3关：数据查询操作

注*：以下的关卡的代码都将补全的类替换成所给代码

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;
    }

第4关：数据插入操作

  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 (" + subject_id + ", '" + title + "', " + type + ")";
    try {
        stmt = connection.createStatement();
        stmt.executeUpdate(sqlScript);
     
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

第5关：数据删除操作

 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;
    }

第6关：数据更新操作

public int updateAnswerById(Connection connection, int id, String tableName, int answer) {
    Statement stmt = null;
    int updatedNum = 0;
    String sqlScript = "UPDATE " + tableName + " SET correct_answer = ? WHERE subject_id = ?";
    try {
        PreparedStatement pstmt = connection.prepareStatement(sqlScript);
        pstmt.setInt(1, answer);
        pstmt.setInt(2, id);
        updatedNum = pstmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return updatedNum;
}