[MySQL]데이터베이스 업데이트하는 여러가지 예제(Update)

 

목차

 

 

 

 

테이블 생성 및 데이터베이스 생성

 

앞서 포스트에서 만든 테이블(Table)을 사용해서 업데이트를 실습해보겠습니다. 앞서 포스트를 확인하기 귀찮으신 분은 아래 예제 코드를 참조하시길 바랍니다.

 

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  password TEXT NOT NULL,
  birthdate DATE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username, email, password, birthdate, created_at)
VALUES
  ('JohnDoe', 'johndoe@example.com', 'mypassword', '1990-05-10', '2023-06-16 10:30:00'),
  ('JaneSmith', 'janesmith@example.com', 'secret123', '1988-12-15', '2023-06-16 11:45:00'),
  ('AliceJohnson', 'alicejohnson@example.com', 'abc123', '1995-07-22', '2023-06-16 14:20:00'),
  ('BobWilliams', 'bobwilliams@example.com', 'qwerty456', '1992-03-05', '2023-06-16 15:55:00'),
  ('EmilyBrown', 'emilybrown@example.com', 'p@ssw0rd', '1998-09-30', '2023-06-16 17:10:00');

 

결과>>

mysql> SELECT * FROM users;
+----+--------------+--------------------------+-----------+------------+---------------------+
| id | username     | email                    | password  | birthdate  | created_at          |
+----+--------------+--------------------------+-----------+------------+---------------------+
|  1 | JaneSmith    | janesmith@example.com    | secret123 | 1988-12-15 | 2023-06-16 11:45:00 |
|  2 | AliceJohnson | alicejohnson@example.com | abc123    | 1995-07-22 | 2023-06-16 14:20:00 |
|  3 | BobWilliams  | bobwilliams@example.com  | qwerty456 | 1992-03-05 | 2023-06-16 15:55:00 |
+----+--------------+--------------------------+-----------+------------+---------------------+
3 rows in set (0.00 sec)

 

 

 

 

 

 

 

데이터베이스 업데이트: 특정 열 값만 바꾸기

mysql> UPDATE users SET username = 'NewUsername' WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM users;
+----+--------------+--------------------------+-----------+------------+---------------------+
| id | username     | email                    | password  | birthdate  | created_at          |
+----+--------------+--------------------------+-----------+------------+---------------------+
|  1 | NewUsername  | janesmith@example.com    | secret123 | 1988-12-15 | 2023-06-16 11:45:00 |
|  2 | AliceJohnson | alicejohnson@example.com | abc123    | 1995-07-22 | 2023-06-16 14:20:00 |
|  3 | BobWilliams  | bobwilliams@example.com  | qwerty456 | 1992-03-05 | 2023-06-16 15:55:00 |
+----+--------------+--------------------------+-----------+------------+---------------------+
3 rows in set (0.00 sec)

1번 id의 값의 username만을 위와 같이 변경하였습니다.

 

 

 

 

 

 

 

 

데이터베이스 업데이트: 여러 열 값 바꾸기

mysql> UPDATE users SET email = 'newemail@example.com', birthdate = '1992-08-20' WHERE id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM users;
+----+--------------+-------------------------+-----------+------------+---------------------+
| id | username     | email                   | password  | birthdate  | created_at          |
+----+--------------+-------------------------+-----------+------------+---------------------+
|  1 | NewUsername  | janesmith@example.com   | secret123 | 1988-12-15 | 2023-06-16 11:45:00 |
|  2 | AliceJohnson | newemail@example.com    | abc123    | 1992-08-20 | 2023-06-16 14:20:00 |
|  3 | BobWilliams  | bobwilliams@example.com | qwerty456 | 1992-03-05 | 2023-06-16 15:55:00 |
+----+--------------+-------------------------+-----------+------------+---------------------+
3 rows in set (0.00 sec)

2번 아이디의 email열과 birthdate 열의 값을 업데이트 하였습니다.

 

 

 

 

 

 

 

 

데이터베이스 업데이트:  계산식 적용

mysql> UPDATE users SET birthdate = birthdate + INTERVAL 1 YEAR WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM users;
+----+--------------+-------------------------+-----------+------------+---------------------+
| id | username     | email                   | password  | birthdate  | created_at          |
+----+--------------+-------------------------+-----------+------------+---------------------+
|  1 | NewUsername  | janesmith@example.com   | secret123 | 1988-12-15 | 2023-06-16 11:45:00 |
|  2 | AliceJohnson | newemail@example.com    | abc123    | 1992-08-20 | 2023-06-16 14:20:00 |
|  3 | BobWilliams  | bobwilliams@example.com | qwerty456 | 1993-03-05 | 2023-06-16 15:55:00 |
+----+--------------+-------------------------+-----------+------------+---------------------+
3 rows in set (0.00 sec)

id 3의 birthdate 값에서 년도(year) 값에 1년을 추가하는 수식(Math)을 넣었습니다.

 

 

 

 

 

 

 

 

데이터베이스 업데이트: 조건식 업데이트

mysql> UPDATE users SET password = 'newpassword' WHERE created_at < '2023-06-16 11:46:00';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM users;
+----+--------------+-------------------------+-------------+------------+---------------------+
| id | username     | email                   | password    | birthdate  | created_at          |
+----+--------------+-------------------------+-------------+------------+---------------------+
|  1 | NewUsername  | janesmith@example.com   | newpassword | 1988-12-15 | 2023-06-16 11:45:00 |
|  2 | AliceJohnson | newemail@example.com    | abc123      | 1992-08-20 | 2023-06-16 14:20:00 |
|  3 | BobWilliams  | bobwilliams@example.com | qwerty456   | 1993-03-05 | 2023-06-16 15:55:00 |
+----+--------------+-------------------------+-------------+------------+---------------------+
3 rows in set (0.00 sec)

 

위와 같이 생성 날짜에 조건을 걸어서 조건을 만족하는 DB값의 password를 변경하였습니다.

 

 

 

위 코드들을 아래 txt 파일에서 참조하실 수 있습니다.

ex_db_update.txt

0.00MB