[데브코스][5-3] CLI로 DB 생성해보기, mysql 날짜 및 시간 타입 종류, JOIN

단·2024년 5월 13일

데브코스

목록 보기
16/20

1. DB 테이블 생성 실습

  1. “게시글” 스키마 생성

    MariaDB [(none)]> CREATE DATABASE Board;
Query OK, 1 row affected (0.003 sec)

MariaDB [(none)]> USE Board;
Database changed

  2. 사용자 테이블 생성

    CREATE TABLE users
(
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR (30) NOT NULL,
	job VARCHAR (100),
	birth DATE,
	PRIMARY KEY (id)
);

    id는 숫자, 값이 없으면 안되니까 NOT NULL, 데이터 추가됨에 따라 자동으로 하나씩 오르도록 AUTO_INCREMENT

    name은 글자, 값이 없으면 안되어서 NOT NULL

    birth는 date 타입

    PK는 id

    DESC 테이블; → 테이블 속성 확인

    //결과
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.010 sec)

  3. 사용자 데이터 삽입

    //1
INSERT INTO users (name, job, birth)
	VALUES ("gongu", "actor", "800123");

//2
INSERT INTO users (name, job, birth)
	VALUES ("gongu", "actor", "1980-01-23");

    1번처럼 삽입해도 자동으로 birth 형태를 변경해줌

    MariaDB [Board]> INSERT INTO users (name, job, birth)
    -> VALUES ("gongu", "actor", "800123");
Query OK, 1 row affected (0.010 sec)

MariaDB [Board]> SELECt * FROM users;
+----+-------+-------+------------+
| id | name  | job   | birth      |
+----+-------+-------+------------+
|  1 | gongu | actor | 1980-01-23 |
+----+-------+-------+------------+
1 row in set (0.004 sec)

    하지만, 1900년대인지 2000년대인지 확실하게 구분하기 위해서는(더 나아가 다른 테이블에서도 연도 확실히 해야 하니까) 2번과 같이 풀형태에 맞게 넣는게 더 좋음

  4. 게시글 테이블 생성

    CREATE TABLE posts
(
	id INT NOT NULL AUTO_INCREMENT,
	title VARCHAR (100) NOT NULL,
	content VARCHAR (2000),
	created_at TIMESTAMP DEFAULT NOW(),
	PRIMARY KEY (id)
);
    
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                |                |
| created_at | timestamp     | YES  |     | current_timestamp() |                |
+------------+---------------+------+-----+---------------------+----------------+
4 rows in set (0.006 sec)

  5. 게시글 데이터 삽입

    INSERT INTO posts (title, content)
	VALUES ("title", "content1");
    MariaDB [Board]> SELECT * FROM posts;
+----+-------+----------+---------------------+
| id | title | content  | created_at          |
+----+-------+----------+---------------------+
|  1 | title | content1 | 2024-05-08 19:19:18 |
+----+-------+----------+---------------------+
1 row in set (0.002 sec)

  6. 게시글 테이블에 수정일자 추가

    테이블 구조(테이블, 컬럼, 제약 조건 등)을 수정하는데에는 ALTER, 테이블의 데이터를 수정하는데는 UPDATE

    ALTER 이후 확인하려면 DESC

    UPDATE 이후 확인하려면 SELECT

    ALTER TABLE posts
ADD COLUMN updated_at DATETIME
DEFAULT NOW()
ON UPDATE NOW();
  • (+) 데이터 삽입
    MariaDB [Board]> INSERT INTO posts (title, content)
    -> VALUES ("title2", "content2");
  • 삽입 결과
    MariaDB [Board]> SELECT * FROM posts;
+----+--------+----------+---------------------+---------------------+
| id | title  | content  | created_at          | updated_at          |
+----+--------+----------+---------------------+---------------------+
|  1 | title  | content1 | 2024-05-08 19:19:18 | 2024-05-08 19:24:57 |
|  2 | title2 | content2 | 2024-05-08 19:27:13 | 2024-05-08 19:27:13 |
+----+--------+----------+---------------------+---------------------+
2 rows in set (0.001 sec)

  1. 게시글 테이블 id 2의 content 수정

    UPDATE posts
SET content = "updated!"
WHERE id = 2;
    • 결과
    MariaDB [Board]> SELECT * FROM posts;
+----+--------+----------+---------------------+---------------------+
| id | title  | content  | created_at          | updated_at          |
+----+--------+----------+---------------------+---------------------+
|  1 | title  | content1 | 2024-05-08 19:19:18 | 2024-05-08 19:24:57 |
|  2 | title2 | updated! | 2024-05-08 19:27:13 | 2024-05-08 19:32:10 |
+----+--------+----------+---------------------+---------------------+
2 rows in set (0.001 sec)

  2. 게시글 테이블에 사용자 칼럼 FK 추가

    ALTER TABLE posts
ADD COLUMN user_id INT;

ALTER TABLE posts
ADD FOREIGN KEY(user_id)
REFERENCES users(id);
    • 결과
    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                |                               |
| created_at | timestamp     | YES  |     | current_timestamp() |                               |
| updated_at | datetime      | YES  |     | current_timestamp() | on update current_timestamp() |
| user_id    | int(11)       | YES  | MUL | NULL                |                               |
+------------+---------------+------+-----+---------------------+-------------------------------+
    ⭐ MUL vs FK

    FK인데 여러 키 값 중복해서 사용 가능하면 MUL

    • user_id 추가해보기
    MariaDB [Board]> SELECT * FROM posts;
+----+--------+----------+---------------------+---------------------+---------+
| id | title  | content  | created_at          | updated_at          | user_id |
+----+--------+----------+---------------------+---------------------+---------+
|  1 | title  | content1 | 2024-05-08 19:19:18 | 2024-05-08 19:24:57 |    NULL |
|  2 | title2 | updated! | 2024-05-08 19:27:13 | 2024-05-08 19:32:10 |    NULL |
|  3 | title3 | content3 | 2024-05-08 19:57:56 | 2024-05-08 19:57:56 |    NULL |
|  6 | title3 | content3 | 2024-05-08 20:03:50 | 2024-05-08 20:03:50 |       1 |
+----+--------+----------+---------------------+---------------------+---------+
4 rows in set (0.001 sec)

cf) MySQL 날짜, 시간 타입

  1. DATE
    1. 날짜만
    2. YYYY-MM-DD
  2. DATETIME
    1. 날짜 + 시간
    2. YYYY-MM-DD HH:MM:SS(24시간제)
  3. TIME
    1. 시간
    2. HH:MM:SS
  4. TIMESTAMP (자동 입력 기능)(이 부분이 DATETIME과의 다른 점)
    1. 날짜 + 시간
    2. YYYY-MM-DD HH:MM:SS(24시간제)
    3. Default → current_timestamp(), 시스템 시간대 정보에 맞게 일시를 저장함

2. JOIN

테이블 합치는 명령어

  • JOIN 종류 4가지
    • Inner join

      • 두 테이블 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 존재해야 함

        SELECT <열 목록>
FROM <첫 번째 테이블>
    INNER JOIN <두 번째 테이블>
    ON <조인 조건>
[WHERE 검색 조건]
    • outer join

      • 두 테이블 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나옴

      • left outer join

      • right outer join

      • full outer join

        SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
    <LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
     ON <조인 조건>
[WHERE 검색 조건]
    • cross join

      • 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인

        SELECT *
FROM <첫 번째 테이블>
    CROSS JOIN <두 번째 테이블>
    • self join
  • 코드
SELECT * FROM posts LEFT 
JOIN users ON posts.user_id = users.id;
  • 결과
MariaDB [Board]> SELECT * FROM posts LEFT 
    -> JOIN users ON posts.user_id = users.id;
+----+--------+----------+---------------------+---------------------+---------+------+-------+-------+------------+
| id | title  | content  | created_at          | updated_at          | user_id | id   | name  | job   | birth      |
+----+--------+----------+---------------------+---------------------+---------+------+-------+-------+------------+
|  1 | title  | content1 | 2024-05-08 19:19:18 | 2024-05-08 19:24:57 |    NULL | NULL | NULL  | NULL  | NULL       |
|  2 | title2 | updated! | 2024-05-08 19:27:13 | 2024-05-08 19:32:10 |    NULL | NULL | NULL  | NULL  | NULL       |
|  3 | title3 | content3 | 2024-05-08 19:57:56 | 2024-05-08 19:57:56 |    NULL | NULL | NULL  | NULL  | NULL       |
|  6 | title3 | content3 | 2024-05-08 20:03:50 | 2024-05-08 20:03:50 |       1 |    1 | gongu | actor | 1980-01-23 |
+----+--------+----------+---------------------+---------------------+---------+------+-------+-------+------------+

데이터 삽입이 실패했어도 id가 계속 상승하는 모습을 볼 수 있음

→ AUTO_INCREMENT으로 설정한 것 때문임, 이 경우 디폴트 값을 AUTO_INCREMENT_LOCK_MODE로 하기

profile
단
이전 포스트

[데브코스][5-2] DBMS, RDBMS, PK, FK, 테이블 정규화

다음 포스트

DB 모듈화, socket hang up?, 코드 리팩토링

0개의 댓글