MariaDB(MySQL)의 모든것 - 심화편

1)mariadb 다운로드 및 설치, workbench(또는 dbeaver, datagrip)설치, vscode설치

2)board 데이터베이스 생성 쿼리문
CREATE DATABASE board; USE board;

3)author 테이블 생성 및 데이터 삽입 

3-1)테이블생성
CREATE TABLE author (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) DEFAULT 'anonymous', email VARCHAR(255) NOT NULL,  password VARCHAR(20), age TINYINT UNSIGNED, gender CHAR(1), self_introduction TEXT, profile_image VARCHAR(255), role ENUM('user', 'admin') NOT NULL DEFAULT 'user', PRIMARY KEY (id), UNIQUE (email));

3-2)데이터삽입
INSERT INTO author (name, email, password) VALUES ('hong1', 'hong1@naver.com', '1234'), ('hong2', 'hong2@naver.com', '1234'), ('hong3', 'hong3@naver.com', '1234'), ('hong4', 'hong4@naver.com', '1234'), ('hong4', 'hong5@naver.com', '1234');


4)posts 테이블생성 및 데이터 삽입

4-1)테이블생성
CREATE TABLE posts (id INT NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, contents VARCHAR(3000), author_id INT, price DECIMAL(10,3), created_time DATETIME DEFAULT CURRENT_TIMESTAMP(), user_id CHAR(36) DEFAULT UUID(), PRIMARY KEY (id), KEY (author_id), CONSTRAINT post_author_fk FOREIGN KEY (author_id) REFERENCES author (id) ON DELETE CASCADE);

4-2)데이터 삽입
INSERT INTO posts (title, contents, author_id, price) VALUES ('java', 'java is ...', 1, null),('python', 'python is ...', 1, null),('java2', null, 1, 10.346);