2023.12.19(화)

⌨️접속 및 실행

Docker Desktop 열고 MariaDB 컨테이너 실행
💡MariaDB CLI Basic recap

• 잘 실행되고 있는지 확인 : docker ps
• docker에서 mariadb 컨테이너 접속 : docker exec -it mariadb /bin/bash
• mariadb 실행 : mariadb -u root -p

⌨️실습

Board DATABASE > users, posts TABLE
💡MariaDB Data Types & Key recap

• CREATE DATABASE Board;

• USE Board;

• users TABLE 생성

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

• SHOW TABLES;

• 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    |                |
  +-------+--------------+------+-----+---------+----------------+

• users TABLE에 data 삽입

  INSERT INTO users (name, job, birth)
  VALUES
  ("kim", "actor", "1973-10-28"),
  ("hwang", "engineer", "2001-01-26"),
  ("cho", "singer", "1997-01-31");

  +----+-------+----------+------------+
  | id | name  | job      | birth      |
  +----+-------+----------+------------+
  |  1 | kim   | actor    | 1973-10-28 |
  |  2 | hwang | engineer | 2001-01-26 |
  |  3 | cho   | singer   | 1997-01-31 |
  +----+-------+----------+------------+

• posts TABLE 생성

  CREATE TABLE posts (
  	id INT NOT NULL AUTO_INCREMENT,
  	title VARCHAR(100) NOT NULL,
  	content VARCHAR(2000),
  	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  	PRIMARY KEY (id)
  );

• SHOW TABLES;

• DESC users;

  +------------+---------------+------+-----+---------------------+-------------------------------+
  | 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 | timestamp     | YES  |     | current_timestamp() | on update current_timestamp() |
  +------------+---------------+------+-----+---------------------+-------------------------------+

• posts TABLE에 data 삽입

  INSERT INTO posts (title, content)
  VALUES
  ("title1", "content1"),
  ("title2", "content2");

  +----+--------+----------+---------------------+---------------------+
  | id | title  | content  | created_at          | updated_at          |
  +----+--------+----------+---------------------+---------------------+
  |  1 | title1 | content1 | 2023-12-19 05:51:56 | 2023-12-19 05:51:56 |
  |  2 | title2 | content2 | 2023-12-19 05:51:56 | 2023-12-19 05:51:56 |
  +----+--------+----------+---------------------+---------------------+

• UPDATE posts SET title = "modified title1" WHERE id = 1;
UPDATE posts SET content = "modified content2" WHERE id = 2;

  ```
  +----+-----------------+-------------------+---------------------+---------------------+
  | id | title           | content           | created_at          | updated_at          |
  +----+-----------------+-------------------+---------------------+---------------------+
  |  1 | **modified title1** | content1          | 2023-12-19 05:51:56 | **2023-12-19 05:53:57** |
  |  2 | title2          | **modified content2** | 2023-12-19 05:51:56 | **2023-12-19 05:56:06** |
  +----+-----------------+-------------------+---------------------+---------------------+
  ```

  ※ TIMESTAMP의 경우 시간이 기본적으로 UTC(Universal Time Coordinated)로 찍힘
  → ⌚서버 타임존 변경
  

• posts TABLE에 user_id 열 추가

  ALTER TABLE posts
  ADD COLUMN user_id INT;

• users TABLE의 id를 posts TABLE의 user_id로 사용 (FK로 지정)

  ALTER TABLE posts
  ADD FOREIGN KEY (user_id)
  REFERENCES users (id)
  ON DELETE CASCADE;

• users(parent table)에 없는 키를 posts(child table)에 넣으려고 하면 오류 발생

  INSERT INTO posts (title, content, user_id)
  VALUES ("title4", "content4", 5);

  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`Board`.`posts`, CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE)

• posts TABLE에 data 삽입

  INSERT INTO posts (title, content)
  VALUES ("title3", "content3");

  INSERT INTO posts (title, content, user_id)
  VALUES ("title4", "content4", 2);

  +----+-----------------+-------------------+---------------------+---------------------+---------+
  | id | title           | content           | created_at          | updated_at          | user_id |
  +----+-----------------+-------------------+---------------------+---------------------+---------+
  |  1 | modified title1 | content1          | 2023-12-19 05:51:56 | 2023-12-19 05:53:57 |    NULL |
  |  2 | title2          | modified content2 | 2023-12-19 05:51:56 | 2023-12-19 05:56:06 |    NULL |
  |  3 | title3          | content3          | 2023-12-19 07:01:25 | 2023-12-19 07:01:25 |    NULL |
  |  5 | title4          | content4          | 2023-12-19 07:02:53 | 2023-12-19 07:02:53 |       2 |
  +----+-----------------+-------------------+---------------------+---------------------+---------+

• UPDATE posts SET user_id = 1 WHERE id = 1 OR id = 3;
UPDATE posts SET user_id = 3 WHERE id = 2;

  ```
  +----+-----------------+-------------------+---------------------+---------------------+---------+
  | id | title           | content           | created_at          | updated_at          | user_id |
  +----+-----------------+-------------------+---------------------+---------------------+---------+
  |  1 | modified title1 | content1          | 2023-12-19 05:51:56 | 2023-12-19 07:34:57 |       1 |
  |  2 | title2          | modified content2 | 2023-12-19 05:51:56 | 2023-12-19 07:35:49 |       3 |
  |  3 | title3          | content3          | 2023-12-19 07:01:25 | 2023-12-19 07:34:57 |       1 |
  |  5 | title4          | content4          | 2023-12-19 07:02:53 | 2023-12-19 07:02:53 |       2 |
  +----+-----------------+-------------------+---------------------+---------------------+---------+
  ```

• 여러 테이블 함께 보기 ➕ : SELECT col_name, ... FROM tbl1 [LEFT | RIGHT | INNER | OUTER | CROSS] JOIN tbl2 ON tbl1.col = tbl2.col;

  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 | modified title1 | content1          | 2023-12-19 05:51:56 | 2023-12-19 07:34:57 |       1 |    1 | kim   | actor    | 1973-10-28 |
  |  2 | title2          | modified content2 | 2023-12-19 05:51:56 | 2023-12-19 07:35:49 |       3 |    3 | cho   | singer   | 1997-01-31 |
  |  3 | title3          | content3          | 2023-12-19 07:01:25 | 2023-12-19 07:34:57 |       1 |    1 | kim   | actor    | 1973-10-28 |
  |  5 | title4          | content4          | 2023-12-19 07:02:53 | 2023-12-19 07:02:53 |       2 |    2 | hwang | engineer | 2001-01-26 |
  +----+-----------------+-------------------+---------------------+---------------------+---------+------+-------+----------+------------+

  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 | modified title1 | content1          | 2023-12-19 05:51:56 | 2023-12-19 07:34:57 | kim   | actor    | 1973-10-28 |
  |  2 | title2          | modified content2 | 2023-12-19 05:51:56 | 2023-12-19 07:35:49 | cho   | singer   | 1997-01-31 |
  |  3 | title3          | content3          | 2023-12-19 07:01:25 | 2023-12-19 07:34:57 | kim   | actor    | 1973-10-28 |
  |  5 | title4          | content4          | 2023-12-19 07:02:53 | 2023-12-19 07:02:53 | hwang | engineer | 2001-01-26 |
  +----+-----------------+-------------------+---------------------+---------------------+-------+----------+------------+

오늘은 9일차에 공부했던 기본 명령어와 10일차에 공식문서를 보고 정리했던 data type & key에 대한 내용을 강사님과 다시 복습하고 실습해 볼 수 있어서 유익했다.