MySQL 기초 DML 과정

류예린·2022년 8월 1일
DMLDatabaseWeCodedbmysql위코드
0

1. DML(Data Manipulation Language)

✅ DML이란?

DML은 Data Manipulation Language의 약자로써 데이터를 조작하는 쿼리문들을 뜻한다. INSERT, UPDATE, DELETE 문이 대표적인 쿼리문들이다. 쿼리문의 이름에서도 알 수 있듯이 INSERT 는 데이터를 테이블에 넣는 것을 의미, UPDATE 는 이미 테이블에 존재하는 데이터를 수정, DELETE 는 데이터를 테이블에서 삭제하는 작업을 수행한다.

✅ MySQL의 DML 문법 표기

데이터를 추가, 수정, 삭제하는데 사용되는 DML 사용 방법과 예제를 살펴보기에 앞서, MySQL 8.0 Reference Manual에 명시된 DML 문법 표기를 찾아보자. 모든 RDBMS가 DML을 이용해 데이터 작업을 수행하지만, RDMBS의 종류와 각 버전에 따라서 DML 문법이 다를 수 있기 때문에 공식문서에서 제공하는 메뉴얼의 SQL 문법을 참조하는 것이 좋다.

📍 INSERT Statement

INSERT는 기존 테이블에 새 데이터(row)를 추가한다. INSERT SQL문을 작성할 때에는 아래와 같이 키워드나 표현식이 표기된 순서로 작성해야 한다. 아래 표기법에서 대문자로 표현된 모든 단어는 모두 키워드를 의미한다. 앞으로 자주 참고하게 될 INSERT 표기법을 살펴보자.

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ... }
    [AS row_alias[(col_alias [, col_alias] ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

assignment:
    col_name =
          value
        | [row_alias.]col_name
        | [tbl_name.]col_name
        | [row_alias.]col_alias

assignment_list:
    assignment [, assignment] ...

먼저 대괄호(”[]”)는 해당 키워드나 표현식 자체가 선택 사항임을 의미한다. 파이프(”|”)는 해당 키워드나 표현식 중에서 단 하나만 선택해서 사용할 수 있음을 의미한다. 중괄호(”{}”)는 괄호 내의 아이템 중에서 반드시 하나를 사용해야하는 필수 사항임을 의미한다. 마지막으로 “...” 표기는 앞에 명시된 키워드나 표현식의 조합이 반복될 수 있음을 의미한다.

  • [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] LOW_PRIORITY, DELAYED, HIGH_PRIORITY 키워드 중 하나를 선택할 수 있다. 다중 클라이언트의 요청을 처리하는 경우에 테이블에 락(lock)을 걸어주는 키워드다. 이는 데이터의 정합성을 위해서 다른 클라이언트가 테이블에서 읽지 않을 때까지 INSERT 실행을 지연시켜준다. MySQL의 SQL문을 실행해서 테이블에 정보를 기록해주는 MySQL 스토리지엔진에는 Innodb, MyISAM, MEMORY, 그리고 MERGE 등이 있는데, 해당 키워드는 MyISAM, MEMORY, MERGE와 같이 테이블 수준의 잠금을 사용하는 스토리지 엔진에만 영향을 준다. 쉽게 정리하면, MySQL 8.0 버전부터는 Innodb 스토리지 엔진을 기본으로 사용하기 때문에 설정하지 않아도 되는 키워드다.
  • [IGNORE] 중복 키 에러가 발생했을 때 신규로 입력되는 레코드를 무시하는 키워드다.
  • [INTO] INSERT INTO와 INSERT는 동일하게 동작. INSERT INTO를 기본적으로 사용.
  • [PARTITION (partition_name [, partition_name] ...)] 파티션 기능을 사용해, 테이블을 논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리해서 관리하는 경우에 사용하는 키워드.
  • [ON DUPLICATE KEY UPDATE assignment_list] ON DUPLICATE KEY UPDATE 키워드를 사용하면 데이터를 추가할 때, UNIQUE 인덱스 또는 PRIMARY KEY에서 중복 값이 발생할 경우 기존 행을 업데이트할 수 있다.

📍 UPDATE Statement

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

📍 DELETE Statement

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]





2. 데이터 추가

초보 개발자에게는 공식문서에서 표기법만 참고하여 실제 적용하기란 쉽지 않은 얘기다. 그래서 전체 문법 중 기본적으로 알아야 하는 키워드 위주로 축소해보자.

# Basic INSERT statement
INSERT INTO tbl_name (col_name, col_name) VALUES (value, value)

실제로 INSERT 문을 적용해보기 위해 INSERT문이 필요한 대표적인 경우로, “Westagram” 온라인 서비스에 새로운 사용자가 등록하는 상황을 가정해 보자.

[그림1] 예제 사용자 테이블

웹에서 사용자가 회원가입시에 입력한 이름(name), 이메일(email), 비밀번호(password), 나이(age) 정보를 users 테이블에 INSERT 문을 사용해서 데이터를 저장한다.

# INSERT INTO 뒤에는 테이블 명과 칼럼값을 소괄호로 감싸 준다.
# VALUES 뒤에는 실제로 넣을 값을 소괄호로 감싸주고 앞서 테이블의 칼럼을 지정한 순서대로 데이터를 넣으면 된다.

mysql> INSERT INTO users (name, email, password, age) VALUES ("Rebekah	Johnson", "Glover12345@email.com", "password", 30);
mysql> INSERT INTO users (name, email, password, age) VALUES ("Fabian Predovic", "O'Connell12345@email.com", "password", 31);
mysql> INSERT INTO users (name, email, password, age) VALUES ("Elenor	Gottlieb", "Skiles12345@email.com", "password", 22);
mysql> INSERT INTO users (name, email, password, age) VALUES ("Madge	Ledner", "Quitzon12345@email.com", "password", 23);
...

데이터가 정상적으로 추가되었는지 확인하기 위해서 테이블 전체를 조회하는 쿼리문(DQL)은 다음과 같다. SQL의 DQL은 이후 자세히 다룰 예정이다.

# * 는 와일드카드로써 테이블의 모든 칼럼을 조회하겠다는 의미와 같다.
mysql> SELECT * FROM users;

+----+-------------------+-----------------------------+------------+---------------------+------------+-----+
| id | name              | email                       | password   | created_at          | updated_at | age |
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+
|  1 | Rebekah Johnson   | Glover12345@email.com       | abcdeft123 | 2022-04-22 09:45:58 | NULL       |  21 |
|  2 | Fabian Predovic   | Connell12345@email.com      | abcdeft123 | 2022-04-22 09:45:58 | NULL       |  22 |
|  3 | Elenor Gottlieb   | Skiles12345@email.com       | abcdeft123 | 2022-04-22 09:45:58 | NULL       |  23 |
|  4 | Madge Ledner      | Quitzon12345@email.com      | abcdeft123 | 2022-04-22 09:45:58 | NULL       |  20 |
|  5 | Zelma Kunde       | Kozey12345@email.com        | abcdeft123 | 2022-04-22 09:45:58 | NULL       |  19 |
|  6 | Destiney Skiles   | Homenick12345@email.com     | abcdeft123 | 2022-04-22 09:45:58 | NULL       |  30 |
|  . | ..                | ...                         | ..         | ..                  | ..         |  .. |
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+

확인 결과 “Rebekah Johnson”, “Fabian Predovic” 등 여러명의 사용자 정보가 정상적으로 users 테이블에 저장된 것을 확인할 수 있다. 이제 사용자 정보가 데이터베이스에 물리적으로 저장되었기 때문에, 데이터베이스에서 정보가 삭제되지 않는 이상 “Rebekah Johnson” 사용자가 “Westagram” 서비스의 회원임을 영구적으로 저장 및 보존할 수 있다.

이번에는 “Westagram” 서비스에서 “Rebekah Johnson”가 게시물 3개를 작성하는 경우, posts 테이블에 정보를 저장하는 쿼리문을 살펴보자. posts 테이블에 게시물을 저장하기 위해서는 제목(title), 내용(content), 작성자(user_id) 정보가 필요하다.

[그림2] 1:M 관계를 갖는 사용자 테이블과 게시글 테이블

여기서, posts 테이블이 users 테이블을 참조하고 있기 때문에, users 테이블에 데이터를 추가할 때와는 다르게 주의해야될 점이 있다. posts 테이블의 user_id 열(column)이 users 테이블의 pk를 참조하고 있는 FK(Foreign Key)이므로 user_id는 반드시 users 테이블에 존재하는 행(row)을 참조해야 된다는 것이다.

# INSERT INTO 뒤에는 테이블 명과 칼럼값을 소괄호로 감싸 준다.
# VALUES 뒤에는 실제로 넣을 값을 소괄호로 감싸주고 앞서 테이블의 칼럼을 지정한 순서대로 데이터를 넣으면 된다.
mysql> INSERT INTO posts (title, content, user_id) VALUES ("위코드 1일차", "HTML과 CSS 익숙해지기..", 1);
mysql> INSERT INTO posts (title, content, user_id) VALUES ("위코드 2일차", "Javascript 기본 문법 학습..", 1);
mysql> INSERT INTO posts (title, content, user_id) VALUES ("위코드 3일차", "웹서비스의 역사와 발전 세션을 듣고..", 1);
mysql> INSERT INTO posts (title, content, user_id) VALUES ("자료구조 1번", "BigO Notation이란 무엇인가?", 2);
mysql> INSERT INTO posts (title, content, user_id) VALUES ("자료구조 2번", "시간 복잡도와 공간 복잡도에 대해서..", 2);

데이터가 정상적으로 추가되었는지 확인하기 위해 테이블 전체를 조회하는 쿼리문(DQL)은 다음과 같다.

# * 는 와일드카드로써 테이블의 모든 칼럼을 조회하겠다는 의미와 같습니다.
mysql> SELECT * FROM posts;

+----+-------------------+-----------------------------+----------+---------------------+------------+
| id | title             | content                     | user_id  | created_at          | updated_at |
+----+-------------------+-----------------------------+----------+---------------------+------------+
|  1 | 위코드 1일차         | HTML과 CSS 익숙해지기..        |       1  | 2022-04-22 11:41:36 | NULL       |
|  2 | 위코드 2일차         | Javascript 기본 문법 학습..    |       1  | 2022-04-22 11:41:38 | NULL       |
|  3 | 위코드 3일차         | 웹서비스의 역사와 발전 세션을 듣고..|       1  | 2022-04-22 11:41:39 | NULL       |
|  4 | 자료구조 1번         | BigO Notation이란 무엇인가?..  |       2  | 2022-04-22 11:41:39 | NULL       |
|  5 | 자료구조 2번         | 시간 복잡도와 공간 복잡도에 대해서..|       2  | 2022-04-22 11:41:39 | NULL       |
+----+-------------------+-----------------------------+----------+---------------------+------------+

만약에 아래와 같이 users 테이블에 존재하지 않는 user 데이터를 참조하는 경우에는 아래와 같은 에러가 발생하게 되니 참조 관계에 있는 테이블에 데이터를 추가할 때에는 주의해야 한다.

# 현재 users 테이블에는 1번~40번까지 유저만 등록되어 있는 상황.
# user_id = 100을 참조하도록 INSERT문을 실행.
mysql> INSERT INTO posts (title, content, user_id) VALUES ("위코드 1일차", "HTML과 CSS 익숙해지기..", 100);
Query 1 ERROR: Cannot add or update a child row: a foreign key constraint fails..





3. 데이터 수정

일반적으로 UPDATE 문은 주로 하나의 테이블에 대해 한 건 또는 여러 건의 레코드를 수정하기 위해서 사용된다. 실제로 기존에 저장되어 있는 데이터를 수정하는 작업(회원정보 수정, 작성한 게시물 내용 수정 등) 또는 데이터를 잘못 입력하여 수정해야 하는 작업이 서비스 운영중에 자주 발생하게 된다.

[그림3] 인스타그램에서 게시물을 수정하는 과정

지금부터 위에서 ”Rebekah Johnson” 사용자가 등록했던 첫번째 게시물의 내용을 "HTML과 CSS 익숙해지기.."에서 “위코드 3개월 과정을 시작하며...”로 수정한다고 가정하고, UPDATE 문을 살펴보자.

# Basic UPDATE statement
UPDATE table_reference SET column='' [WHERE where_condition]

UPDATE 명령에서는 SET column=’’을 사용하여 변경하고자 하는 열과 값을 지정한다. 이때 =은 비교 연산자가 아닌, 값을 대입하는 대입 연산자다. 갱신해야 할 열이 복수인 경우에는 column=’value’, column=’value’의 리스트 형식으로 지정할 수 있다. 여기서, value는 column의 자료형에 맞는 값을 지정해야 한다. 그리고 WHERE 구에 조건을 지정하면 그에 일치하는 행을 셀 단위로 변경할 수 있다. WHERE 문은 프로그래밍 언어의 if 문이라고 생각하면 된다. 조건을 주는 쿼리문이다.

이제 아래 UPDATE 명령으로 posts 테이블의 record를 변경해보자. posts 테이블에서 어떤 행을 변경하는지는 WHERE user_id = 1 AND id=1구문으로 지정한다. 여기서, user_id = 1은 ”Rebekah Johnson”의 pk 값이다. 수정할 열과 값은 SET content = ‘위코드 3개월 과정을 시작하며...’으로 지정한다. 전체 쿼리문은 다음과 같다.

mysql> UPDATE posts SET content='위코드 3개월 과정을 시작하며...' WHERE user_id=1 AND id=1;
# where 구에서 id는 posts 테이블의 pk값 입니다.

다시 한 번 테이블의 전체 데이터를 조회하는 쿼리문으로 데이터가 잘 수정되었는지 확인.

# 위 UPDATE 명령 실행 이후
mysql> SELECT * FROM posts;

+----+-------------------+-----------------------------+----------+---------------------+------------+
| id | title             | content                     | user_id  | created_at          | updated_at |
+----+-------------------+-----------------------------+----------+---------------------+------------+
|  1 | 위코드 1일차         | 위코드 3개월 과정을 시작하며...   |       1  | 2022-04-22 11:41:36 | NULL       |
|  2 | 위코드 2일차         | Javascript 기본 문법 학습..    |       1  | 2022-04-22 11:41:38 | NULL       |
|  3 | 위코드 3일차         | 웹서비스의 역사와 발전 세션을 듣고..|       1  | 2022-04-22 11:41:39 | NULL       |
|  4 | 자료구조 1번         | BigO Notation이란 무엇인가?..  |       2  | 2022-04-22 11:41:39 | NULL       |
|  5 | 자료구조 2번         | 시간 복잡도와 공간 복잡도에 대해서..|       2  | 2022-04-22 11:41:39 | NULL       |
+----+-------------------+-----------------------------+----------+---------------------+------------+

게시물의 id=1인 행(row)의 ‘HTML과 CSS 익숙해지기..’ 에서 ‘위코드 3개월 과정을 시작하며...’로 잘 수정되었음을 확인할 수 있다. 마지막으로 UPDATE 명령의 WHERE 키워드 뒤에 where_condition에 일치하는 모든 행이 그대로 변경 대상이 되기 때문에, 수정, 삭제 명령은 주의해야 한다.






4. 데이터 삭제

마지막으로 자주 사용하는 DML 중에 하나인 DELETE 명령에 대해서 살펴보자. 데이터베이스는 하드디스크와 같은 저장장치에 데이터를 저장해서 관리한다. 데이터를 삭제하는 작업 역시 추가나 수정 만큼 자주 요청되는 작업이다. 예를 들어서, 회원 탈퇴 요청, 등록한 게시물 삭제 요청, database 내의 저장공간 확보를 위한 삭제 요청 등이 있다.

[그림4] 인스타그램에서 게시물을 삭제하는 과정

지금부터 위에서 ”Rebekah Johnson” 사용자가 등록했던 모든 게시물을 삭제하는 요청을 받았다고 가정하고, DELETE 문을 살펴보자.

# Basic DELETE statement
DELETE FROM tbl_name [WHERE where_condition]

DELETE 명령은 하나의 테이블에 대해 한 건 또는 여러 건의 행(row) 자체를 삭제한다. 그렇기 때문에 UPDATE와 INSERTE와 같이 column을 별도로 명시해 그 열만 삭제할 수는 없다. DELETE 역시 WHERE 구를 지정할 수도 있고, 생략할 수도 있다. DELETE 문을 사용해서 특정 데이터를 삭제해 보자.

mysql> DELETE FROM posts WHERE user_id = 1;

테이블의 전체 데이터를 조회하는 쿼리문으로 데이터가 잘 수정되었는지 확인.

mysql> SELECT * FROM posts
+----+-------------------+-----------------------------+----------+---------------------+------------+
| id | title             | content                     | user_id  | created_at          | updated_at |
+----+-------------------+-----------------------------+----------+---------------------+------------+
|  4 | 자료구조 1번         | BigO Notation이란 무엇인가?..  |       2  | 2022-04-22 11:41:39 | NULL       |
|  5 | 자료구조 2번         | 시간 복잡도와 공간 복잡도에 대해서..|       2  | 2022-04-22 11:41:39 | NULL       |
+----+-------------------+-----------------------------+----------+---------------------+------------+

“Rebekah Johnson” (user_id = 1인 사용자)가 작성했던 “위코드 1일차”, “위코드 2일차”, “위코드 3일차” 포스팅이 모두 삭제되고 user_id = 2인 사용자가 등록한 “자료구조 1번”, “자료구조 2번” 게시물만 남아 있음을 확인한다. 마지막으로 DELETE 명령 역시 WHERE 키워드 뒤에 where_condition에 일치하는 모든 행이 그대로 변경 대상이 되기 때문에 주의해서 사용해야 함을 명시하자.






5. Summary

  • DML은 Data Manipulation Language의 약자로써 데이터를 조작하는 쿼리문들을 뜻한다.
  • 테이블에 데이터를 넣을 때 사용하는 명령어는 INSERT INTO {table_name} (column1, column2, ... ) VALUES (data1-1, data1-2, ... ), (data2-1, data2-2, ... ); 의 형태로 사용.
  • 이미 입력된 데이터를 수정하는 명령어는 UPDATE {table_name} SET {column_name}={new_data} WHERE {condition}; 다.
  • 기존에 입력되어 있던 데이터를 지우는 명령어는 DELETE FROM {table_name} WHERE {condition}; 다.
profile
류예린
helloworld
이전 포스트

MySQL 기초 DDL 과정

다음 포스트

MySQL 기초 DQL 과정

0개의 댓글