이전에 만든 ERD를 기준으로 프로젝트를 DB에 연결하여 만들려고 한다.
SQL은 외우는게 아니다!
필요할 때 마다 구글링 해야한다
-
'board'스키마 생성
$ docker exec -it mariadb /bin/bash $ mariadb -u root -p Enter password: #기존에 root로 설정해뒀기에 그대로 입력MariaDB [(none)]> CREATE DATABASES Borad MariaDB [(none)]> USE Board -
사용자 테이블 생성
MariaDB [Board]> CREATE TABLE users( -> id INT NOT NULL AUTO_INCREMENT, -> name VARCHAR(30) NOT NULL, -> job VARCHAR(100), -> birth DATE, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.007 sec) MariaDB [Board]> SHOW TABLES -> ; +-----------------+ | Tables_in_Board | +-----------------+ | users | +-----------------+ 1 row in set (0.001 sec) MariaDB [Board]> DESC users; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | NULL | | | job | varchar(100) | YES | | NULL | | | birth | date | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 4 rows in set (0.001 sec) -
사용자 데이터 삽입
MariaDB [Board]> INSERT INTO users (name,job, birth) -> VALUES("gongu", "actor", "800123"); Query OK, 1 row affected (0.003 sec) MariaDB [Board]> SELECT * FROM users; +----+-------+-------+------------+ | id | name | job | birth | +----+-------+-------+------------+ | 1 | gongu | actor | 1980-01-23 | +----+-------+-------+------------+ 1 row in set (0.001 sec) MariaDB [Board]> INSERT INTO users (name, job, birth) VALUES("kim","student","100101"); Query OK, 1 row affected (0.002 sec) MariaDB [Board]> INSERT INTO users (name, job, birth) VALUES("lee","teacher","1930-01-02"); Query OK, 1 row affected (0.001 sec) MariaDB [Board]> SELECT * FROM users; +----+-------+---------+------------+ | id | name | job | birth | +----+-------+---------+------------+ | 1 | gongu | actor | 1980-01-23 | | 2 | kim | student | 2010-01-01 | | 3 | lee | teacher | 1930-01-02 | +----+-------+---------+------------+ 3 rows in set (0.001 sec) -
게시글 테이블 생성
MariaDB [Board]> CREATE TABLE posts( -> id INT NOT NULL AUTO_INCREMENT, -> title VARCHAR(100) NOT NULL, -> content VARCHAR(2000), -> create_at TIMESTAMP DEFAULT NOW(), -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.006 sec) MariaDB [Board]> DESC posts; +-----------+---------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | title | varchar(100) | NO | | NULL | | | content | varchar(2000) | YES | | NULL | | | create_at | timestamp | YES | | current_timestamp() | | +-----------+---------------+------+-----+---------------------+----------------+ 4 rows in set (0.002 sec) -
게시글 데이터 삽입
MariaDB [Board]> INSERT INTO posts (title, content) VALUES ("title1", "content"); Query OK, 1 row affected (0.001 sec) MariaDB [Board]> SELECT * FROM posts;; +----+--------+---------+---------------------+ | id | title | content | create_at | +----+--------+---------+---------------------+ | 1 | title1 | content | 2025-02-26 06:10:00 | +----+--------+---------+---------------------+ 1 row in set (0.000 sec) -
게시글 테이블에 수정일자 추가
ALTER TABLE posts ADD COLUMN updated_at DATETIME DEFAULT NOW() ON UPDATE NOW() -
게시글 테이블 id 2 수정
MariaDB [Board]> UPDATE posts -> SET content = "updated!" -> WHERE id = 2; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [Board]> SELECT * FROM posts;; +----+--------+----------+---------------------+---------------------+ | id | title | content | create_at | updated_at | +----+--------+----------+---------------------+---------------------+ | 1 | title1 | content | 2025-02-26 06:10:00 | 2025-02-26 06:47:52 | | 2 | title2 | updated! | 2025-02-26 08:18:06 | 2025-02-26 08:18:06 | +----+--------+----------+---------------------+---------------------+ 2 rows in set (0.001 sec) -
게시글 테이블에 작성자 칼럼 FK 추가
MariaDB [Board]> ALTER TABLE posts -> ADD COLUMN user_id INT; Query OK, 0 rows affected (0.006 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [Board]> ALTER TABLE posts -> ADD FOREIGN KEY(user_id) -> REFERENCES users(id); Query OK, 2 rows affected (0.007 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [Board]> DESC posts; +------------+---------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | title | varchar(100) | NO | | NULL | | | content | varchar(2000) | YES | | NULL | | | create_at | timestamp | YES | | current_timestamp() | | | updated_at | datetime | YES | | current_timestamp() | | | user_id | int(11) | YES | MUL | NULL | | +------------+---------------+------+-----+---------------------+----------------+ 6 rows in set (0.002 sec) MariaDB [Board]> SELECT * FROM posts;; +----+--------+----------+---------------------+---------------------+---------+ | id | title | content | create_at | updated_at | user_id | +----+--------+----------+---------------------+---------------------+---------+ | 1 | title1 | content | 2025-02-26 06:10:00 | 2025-02-26 06:47:52 | NULL | | 2 | title2 | updated! | 2025-02-26 08:18:06 | 2025-02-26 08:18:06 | NULL | +----+--------+----------+---------------------+---------------------+---------+ 2 rows in set (0.000 sec) -
조인
조인에는 LEFT와 RIGHT등등 여러가지가 있지만 보통 LEFT를 자주 쓴다.
LEFT JOIN을 SELECT - FROM [테이블명] 옆에 작성하는데, 해당 테이블을 왼쪽에 두고 조인한다고 이해하면 된다.MariaDB [Board]> SELECT * FROM posts LEFT -> JOIN users ON posts.user_id = users.id; +----+--------+----------+---------------------+---------------------+---------+------+------+---------+------------+ | id | title | content | create_at | updated_at | user_id | id | name | job | birth | +----+--------+----------+---------------------+---------------------+---------+------+------+---------+------------+ | 1 | title1 | content | 2025-02-26 06:10:00 | 2025-02-26 06:47:52 | NULL | NULL | NULL | NULL | NULL | | 2 | title2 | updated! | 2025-02-26 08:18:06 | 2025-02-26 08:18:06 | NULL | NULL | NULL | NULL | NULL | | 3 | title3 | content | 2025-02-26 08:24:44 | 2025-02-26 08:24:44 | NULL | NULL | NULL | NULL | NULL | | 5 | title4 | content4 | 2025-02-26 08:26:44 | 2025-02-26 08:26:44 | 3 | 3 | lee | teacher | 1930-01-02 | +----+--------+----------+---------------------+---------------------+---------+------+------+---------+------------+ 4 rows in set (0.001 sec)user_id와 user.id가 중복되니까, 해당 컬럼을 빼고 불러온다.
MariaDB [Board]> SELECT posts.id, title, content, created_at, updated_at, name, job, birth FROM posts LEFT -> JOIN users ON posts.user_id = users.id; +----+--------+----------+---------------------+---------------------+------+---------+------------+ | id | title | content | created_at | updated_at | name | job | birth | +----+--------+----------+---------------------+---------------------+------+---------+------------+ | 1 | title1 | content | 2025-02-26 06:10:00 | 2025-02-26 06:47:52 | NULL | NULL | NULL | | 2 | title2 | updated! | 2025-02-26 08:18:06 | 2025-02-26 08:18:06 | NULL | NULL | NULL | | 3 | title3 | content | 2025-02-26 08:24:44 | 2025-02-26 08:24:44 | NULL | NULL | NULL | | 5 | title4 | content4 | 2025-02-26 08:26:44 | 2025-02-26 08:26:44 | lee | teacher | 1930-01-02 | +----+--------+----------+---------------------+---------------------+------+---------+------------+ 4 rows in set (0.001 sec)
MariaDB 날짜 / 시간 타입
- DATE
- 날짜만(YYYY-MM-DD)
- DATETIME
- 날짜 + 시간 (YYYY-MM-DD HH:MM:SS, 24시간제)
- TIME
- 시간(HH:MM:SS)
- TIMESTAMP
- 보통 create_at과 같은 날짜가 자동으로 찍을 때 사용함,
- 날짜 + 시간 (YYYY-MM-DD HH:MM:SS, 24시간제, UTC 본초자오선 기준)
NOT NULL VS Default
- Not Null
- 직접 null 이라고 작성해도 넣는 것도 안된다.
- Defualt: 값이 안 들어올 때, 기본 값으로 셋팅
- 공란으로 insert => Default 설정해둔 기본 값에 insert
- 직접 null을 입력하면 null이 입력됨
