DB query

[jisu3817]

목적

데이터베이스 초기화 대비 참고용

CREATE DATABASE dongurami_db;

USE dongurami_db;

CREATE TABLE students (
    id VARCHAR(11) NOT NULL,
    password VARCHAR(128) NOT NULL, 
    name VARCHAR(20) NOT NULL,
    email VARCHAR(128) NOT NULL,
    in_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    password_salt VARCHAR(128) NOT NULL,
    phone_number VARCHAR(20),
    grade TINYINT(1),
    gender TINYINT(1),
    admin_flag BOOLEAN NOT NULL DEFAULT FALSE,
    profile_image_url VARCHAR(255),
    major VARCHAR(20) NOT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE board_categories (
    no INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,

    PRIMARY KEY (no)
);

CREATE TABLE clubs (
    no INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    category VARCHAR(20) NOT NULL,
    logo_url TEXT,
    avg_score FLOAT(2,1),
    leader VARCHAR(11) NOT NULL,
    introduce TEXT,

    PRIMARY KEY (no),
    CONSTRAINT clubs_fk1 FOREIGN KEY (leader) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE boards (
    no INT NOT NULL AUTO_INCREMENT,
    student_id VARCHAR(11) NOT NULL,
    club_no INT NOT NULL DEFAULT 1,
    board_category_no INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description MEDIUMTEXT NOT NULL,
    in_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    hit INT NOT NULL DEFAULT 0,
    writer_hidden_flag TINYINT(1) NOT NULL DEFAULT 0,

    PRIMARY KEY (no),
    CONSTRAINT club_boards_fk1 FOREIGN KEY (board_category_no) REFERENCES board_categories (no) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT club_boards_fk2 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT club_boards_fk3 FOREIGN KEY (club_no) REFERENCES clubs (no) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE comments (
    no INT NOT NULL AUTO_INCREMENT,
    board_no INT NOT NULL,
    student_id VARCHAR(11) NOT NULL,
    description VARCHAR(255) NOT NULL,
    in_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    depth TINYINT(1) NOT NULL DEFAULT 0,
    group_no INT NOT NULL DEFAULT 0,
    reply_flag BOOLEAN NOT NULL DEFAULT FALSE,
    writer_hidden_flag TINYINT(1) NOT NULL DEFAULT 0,
    emotion_hit INT(11) NOT NULL DEFAULT 0,

    PRIMARY KEY (no),
    CONSTRAINT comments_fk1 FOREIGN KEY (board_no) REFERENCES boards (no) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT comments_fk2 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE images (
    no INT NOT NULL AUTO_INCREMENT,
    board_no INT NOT NULL,
    url VARCHAR(255) NOT NULL,

    PRIMARY KEY (no),
    CONSTRAINT images_fk1 FOREIGN KEY (board_no) REFERENCES boards (no) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE notification_categories (
   no TINYINT(1) NOT NULL, 
   name VARCHAR(20) NOT NULL,

  PRIMARY KEY (no)
);

CREATE TABLE notifications (
    no INT NOT NULL AUTO_INCREMENT,
    sender VARCHAR(20) NOT NULL,
    recipient VARCHAR(20) NOT NULL,
    recipient_id VARCHAR(11) NOT NULL, 
    url VARCHAR(255) NOT NULL,
    in_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    notification_category_no TINYINT(1) NOT NULL ,
    content VARCHAR(255) NOT NULL, 
    title VARCHAR(255) NOT NULL, 
    reading_flag TINYINT(1) NOT NULL default 0, 

    PRIMARY KEY (no),
    CONSTRAINT notifications_fk1 FOREIGN KEY (notification_category_no) REFERENCES notification_categories (no) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT notifications_fk2 FOREIGN KEY (recipient_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE members (
    no INT NOT NULL AUTO_INCREMENT,
    student_id VARCHAR(11) NOT NULL,
    club_no INT NOT NULL,
    join_admin_flag TINYINT(1) NOT NULL  DEFAULT 0,
    board_admin_flag TINYINT(1) NOT NULL DEFAULT 0,

    PRIMARY KEY (no),
    CONSTRAINT members_fk1 FOREIGN KEY (club_no) REFERENCES clubs (no) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT members_fk2 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE questions (
    no INT NOT NULL AUTO_INCREMENT,
    club_no INT NOT NULL,
    description VARCHAR(255) NOT NULL,

    PRIMARY KEY (no),
    CONSTRAINT questions_fk1 FOREIGN KEY (club_no) REFERENCES clubs (no) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE answers (
    no INT NOT NULL AUTO_INCREMENT,
    question_no INT NOT NULL,
    student_id VARCHAR(11) NOT NULL,
    description TEXT,

    PRIMARY KEY (no),
    CONSTRAINT answers_fk1 FOREIGN KEY (question_no) REFERENCES questions (no) ON DELETE CASCADE,
    CONSTRAINT answers_fk2 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE applicants (
    no INT(11) NOT NULL AUTO_INCREMENT,
    club_no INT(11) NOT NULL, 
    student_id VARCHAR(11) NOT NULL,
    reading_flag TINYINT(1) NOT NULL DEFAULT 0,
    in_date DATE NOT NULL DEFAULT CURRENT_TIMESTAMP(),

    PRIMARY KEY (no),
    CONSTRAINT applicants_fk1 FOREIGN KEY (club_no) REFERENCES clubs (no) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT applicants_fk2 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE reviews (
    no INT NOT NULL AUTO_INCREMENT,
    club_no INT NOT NULL,
    student_id VARCHAR(11) NOT NULL,
    description VARCHAR(255) NULL,
    in_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
    score TINYINT(1) NOT NULL,

    PRIMARY KEY (no),
    CONSTRAINT reviews_fk1 FOREIGN KEY (club_no) REFERENCES clubs (no) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT reviews_fk2 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE schedules (
    no INT NOT NULL AUTO_INCREMENT,
    club_no INT NOT NULL,
    student_id VARCHAR(11) NOT NULL,
    color_code VARCHAR(20) NOT NULL,
    title VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    important TINYINT(1) NOT NULL DEFAULT 0,

    PRIMARY KEY(no),
    CONSTRAINT schedules_fk1 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT schedules_fk2 FOREIGN KEY (club_no) REFERENCES clubs (no) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE scraps (
    no INT NOT NULL AUTO_INCREMENT,
    student_id VARCHAR(11) NOT NULL,
    club_no INT(11) NOT NULL,
    title  VARCHAR(255) NOT NULL,
    scrap_description MEDIUMTEXT,
    board_description MEDIUMTEXT NOT NULL,
    in_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    file_url VARCHAR(255),

    PRIMARY KEY (no),
    CONSTRAINT scraps_fk1 FOREIGN KEY (club_no) REFERENCES clubs (no) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT scraps_fk2 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE letters (
    no INT NOT NULL AUTO_INCREMENT,
    sender_id VARCHAR(11) NOT NULL,
    recipient_id VARCHAR(11) NOT NULL,
    host_id VARCHAR(11) NOT NULL ,
    description VARCHAR(255) NOT NULL,
    group_no INT(11) NOT NULL DEFAULT 0,
    in_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    writer_hidden_flag TINYINT(1) NOT NULL, 
    recipient_hidden_flag TINYINT(1) NOT NULL,
    reading_flag TINYINT(1) NOT NULL DEFAULT 0,
    delete_flag TINYINT(1) NOT NULL DEFAULT 0,

    PRIMARY KEY (no), 
    CONSTRAINT letters_fk1 FOREIGN KEY (sender_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE, 
    CONSTRAINT letters_fk2 FOREIGN KEY (recipient_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT letters_fk3 FOREIGN KEY (host_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE sns_info (
    no INT NOT NULL AUTO_INCREMENT,
    student_id VARCHAR(11) NOT NULL,
    sns_id VARCHAR(255) NOT NULL,

    PRIMARY KEY (no),
    CONSTRAINT sns_info_fk1 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE board_emotions (
    no INT NOT NULL AUTO_INCREMENT,
    student_id VARCHAR(11) NOT NULL,
    board_no INT(11) NOT NULL,

    PRIMARY KEY (no),
    CONSTRAINT board_emotions_fk1 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT board_emotions_fk2 FOREIGN KEY (board_no) REFERENCES boards (no) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE comment_emotions (
    no INT NOT NULL AUTO_INCREMENT,
    student_id VARCHAR(11) NOT NULL,
    comment_no INT(11) NOT NULL,

    PRIMARY KEY (no),
    CONSTRAINT comment_emotions_fk1 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT comment_emotions_fk2 FOREIGN KEY (comment_no) REFERENCES comments (no) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE reply_comment_emotions (
    no INT NOT NULL AUTO_INCREMENT,
    student_id VARCHAR(11) NOT NULL,
    reply_comment_no INT(11) NOT NULL,

    PRIMARY KEY (no),
    CONSTRAINT reply_comment_emotions_fk1 FOREIGN KEY (student_id) REFERENCES students (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT reply_comment_emotions_fk2 FOREIGN KEY (reply_comment_no) REFERENCES comments (no) ON UPDATE CASCADE ON DELETE CASCADE
);

[rrgks6221]

hashtag 테이블 name 칼럼 tagname으로 변경