create.sql

来自「在线模拟选课系统」· SQL 代码 · 共 58 行

CREATE DATABASE ch07;

# -----------------------------------------------------------------------
# user
# -----------------------------------------------------------------------
DROP TABLE IF EXISTS user;

CREATE TABLE user (
username   VARCHAR(20)  NOT NULL,
password   VARCHAR(20)  NOT NULL,
rank       CHAR(1)      NOT NULL,
realname   VARCHAR(20)  NOT NULL,
PRIMARY KEY (username) );

# -----------------------------------------------------------------------
# question
# -----------------------------------------------------------------------
DROP TABLE IF EXISTS question;

CREATE TABLE question (
question_id      CHAR(10)     NOT NULL,
category_id      CHAR(2)      NOT NULL,
difficulty       CHAR(1)      NOT NULL,
subject          VARCHAR(300) NOT NULL,
choice_a         VARCHAR(100) NOT NULL,
choice_b         VARCHAR(100) NOT NULL,
choice_c         VARCHAR(100) NOT NULL,
choice_d         VARCHAR(100) NOT NULL,
answer           CHAR(1)      NOT NULL,
PRIMARY KEY (question_id) ,
INDEX (category_id, difficulty) );

# -----------------------------------------------------------------------
# category
# -----------------------------------------------------------------------
DROP TABLE IF EXISTS category;

CREATE TABLE category (
category_id      CHAR(2)     NOT NULL,
category_name    VARCHAR(20) NOT NULL,
question_number  INT         NOT NULL,
test_time        INT         NOT NULL,
PRIMARY KEY (category_id) );

# -----------------------------------------------------------------------
# test_record
# -----------------------------------------------------------------------
DROP TABLE IF EXISTS test_record;

CREATE TABLE test_record (
record_id       CHAR(10)    NOT NULL,
username        VARCHAR(20) NOT NULL,
category_id     CHAR(2)     NOT NULL,
test_time       CHAR(14)    NOT NULL,
test_result     INT         NOT NULL,
use_time        INT         NOT NULL,
PRIMARY KEY (record_id) );