[MySQL & PHP] 9장 데이터 편집하기

정리

1. 지정한 칼럼의 모든 값 변경하기

1. 데이터 수정하기

1.1 칼럼의 모든 데이터를 한 번에 수정하기

먼저, 데이터를 수정하는 방법입니다. 데이터를 수정할 때에는 UPDATE 명령을 사용합니다.

칼럼에 모든 데이터 수정하기

UPDATE 테이블_이름 SET 칼럼_이름 = 설정할_값;

특정 칼럼을 지정한 값으로 변경합니다. 몇천 건, 몇만 건의 데이터도 이 명령을 실행하면 칼럼의 모든 데이터를 한 번에 변경할 수 있습니다.

일반적으로 UPDATE 명령은 8장의 WHERE로 조건을 설정해서 특정 레코드만을 대상으로 실행합니다. WHERE 등으로 조건을 설정하지 않으면 모든 칼럼의 값이 변경되기 때문에 이 명령을 실행할 때에는 신중해야 합니다.

그러면 WHERE를 사용해 보겠습니다. 칼럼의 값을 변경하면 원래대로 되돌리기가 어렵기 때문에 이번에는 별도의 칼럼을 새로 만들어 데이터를 추가하겠습니다.

현재, 매출정보 테이블 tb에는 사원번호 number, 매출 sales, 매출월 month 칼럼만 있습니다. 여기에 새로운 칼럼 note를 추가하고, 자료형을 VARCHAR(100)으로 설정합니다. 그리고 나서 이 칼럼에 note에 UPDATE 명령을 사용해서 모든 레코드에 '특이사항 없음'이라고 입력하고서 모든 레코드를 표시해 보겠습니다.

다음 명령을 실행한다.

ALTER TABLE tb ADD note VARCHAR(100);

다음 명령을 실행한다.

UPDATE tb SET note='특이사항 없음';

다음 명령을 실행한다.

SELECT * FROM tb;

UPDATE 명령을 실행하면 '✕ ✕ rows affected'라고 표시됩니다. 이것은 '✕ ✕ 개의 레코드에 영향을 미쳤다.'라는 의미입니다. 이 예에서는 WHERE 조건을 설정하지 않았기 때문에 칼럼 note의 모든 값이 '특이사항 없음'으로 변경됩니다.

의도치 않은 UPDATE나 DELETE를 막으려면!

업무상 MySQL을 사용할 경우, 칼럼의 모든 내용을 한꺼번에 변경하는 일은 그리 많지 않습니다. 의도치 않은 조작으로 모든 칼럼의 값을 같은 값으로 변겨하는 일은 없어야 합니다. 이런 사고를 방지하려면 MySQL 콘솔창을 실행할 때 --safe-updates 옵션을 설정하는 방법이 있습니다. 이 옵션을 설정하면 WHERE 조건 없이 UPDATE나 DELETE 명령을 실행할 수 없게 됩니다. 데이터베이스 초보 관리자에게 도움이 되는 기능입니다.

2. 조건에 일치하는 레코드의 칼럼 변경하기

1.2 조건에 일치하는 레코드만 수정하기

모든 칼럼을 수정하는 것은 간단하지만, 실제로는 모든 칼럼을 수정할 일은 그리 많지 않습니다. 일반적으로는 조건에 일치하는 레코드만 수정하는 경우가 많습니다.

WHERE로 지정한 조건에 일치하는 레코드만 수정할 때에는 다음 명령을 사용합니다.

조건에 일치하는 레코드만 수정하기

UPDATE 테이블_이름 SET 칼럼_이름 = 설정할_값 WHERE 조건;

UPDATE에 WHERE로 조건을 설정한 단순한 명령문 입니다. 여기에서는 'WHERE 조건'이라고 설정했지만, ORDER BY나 LIMIT등으로 조건을 설정해도 됩니다.

바로 전에 칼럼 note의 모든 레코드에 '특이사항 없음' 이라고 입력했습니다. 매출정보 테이블 tb를 보면 매출이 100 이상인 우수한 성적이 여러 개 있습니다. 그러면 칼럼 sales가 100 이상인 레코드의 비고란(칼럼 note)에 '우수'라고 입력해 보겠습니다.

다음 명령을 실행한다.

UPDATE tb SET note='우수' WHERE sales>=100;

다음 명령을 실행한다.

SELECT * FROM tb;

'우수'라고 변경되지 않은 레코드는 이전 그대로 '특이사항 없음'이라고 되어 있는 것을 확인할 수 있습니다.

1.3 하위 3건의 매출만 '힘내!'라고 수정하기

앞에서는 매출이 100 이상인 레코드의 비고란에 '우수'라고 입력했습니다. 이번에는 매출이 적은 레코드에 응원의 메시지를 입력해 보겠습니다. 모든 매출을 비교해서 하위 3건에만 '힘내'라고 입력합니다. 이때, '하위 3건'이라는 조건은 어떻게 설정해야 할까요?

얼핏 생각해보면 까다로워 보이지만, 칼럼 sales를 ORDER BY를 이용하여 오름차순으로 정렬한후, LIMIT 3으로 최초의 3건만 SELECT해서 칼럼 note에 '힘내'라고 입력하는 방법으로 UPDATE를 실행합니다.

다음 명령을 실행한다.

UPDATE tb

SET note='힘내!'

ORDER BY sales

LIMIT 3;

정말로 하위 3건의 성적만 변경되었는지 다음 명령을 실행해서 확인해 보겠습니다. 칼럼 sales로 정렬해서 표시해 보겠습니다.

다음 명령을 실행한다.

SELECT * FROM tb ORDER BY sales ASC;

매출이 적은 순으로 3건만 변경된 것을 확인할 수 있습니다.

여기에 OFFSET(8장 5.3 표시할 레코드의 범위 지정하기)을 사용하면 '◯ 번에서 ✕번까지~'로 지정해서 변경할 수도 있습니다.

마지막으로 매출정보 테이블의 칼럼 note를 삭제해서 테이블 tb를 원래 상태로 되돌려 놓습니다. 다음 명령을 실행하면 칼럼 note가 삭제됩니다.

ALTER TABLE tb DROP note;

2. 조건에 일치하는 레코드 복사하기

2.1 특정 레코드만 복사하기

7장에서는 모든 레코드를 통째로 복사하는 3가지 방법을 소개했습니다. 이번에는 조건에 일치하는 레코드를 선택하여 다른 테이블에 복사하는 방법에 대해 알아보겠습니다. 예를 들어, '성적이 80점 이상인 레코드만' 또는 '문소리의 레코드만'을 모아서 다른 테이블을 생성하는 방법입니다.

조건을 설정해서 SELECT한 레코드를 CREATE TABLE로 만든 새로운 테이블에 삽입 합니다. 즉, (7장 3. 다른 테이블의 레코드 복사하기)의 'CREATE TABLE 새로운 테이블 이름 SELECT * FROM 기존 테이블 이름'에 WHERE로 조건을 설정 합니다.

매출정보 테이블 tb에서 베테랑 사원 A101의 레코드만 추출하여, 테이블 tb_A101을 만들어 보겠습니다. 즉, 테이블 tb의 칼럼 구조와 칼럼 number가 A101인 레코드만 복사해서 새로운 테이블 tb_A101을 만듭니다. 그리고 나서 새로운 테이블 tb_A101의 모든 데이터를 표시해 보겠습니다.

다음 명령을 실행한다.

CREATE TABLE tb_A101

SELECT *

FROM tb

WHERE number LIKE 'A101';

다음 명령을 실행한다.

SELECT * FROM tb_A101;

이미 존재하는 테이블에 레코드를 추가하는 경우에는 다음과 같이 CREATE TABLE 대신 INSERT INTO를 사용합니다.

INSERT INTO 존재하는_테이블_이름 SELECT * FROM tb WHERE number LIKE 'A101';

2.2 순서대로 정렬하여 복사하기

값이 가장 작은 레코드 3개만 복사하거나 상위 5번부터 101번까지의 레코드만 복사하는 것과 같이 값이 작은 순서에 따라 레코드를 복사하는 방법을 설명하겠습니다. 앞에서 설명한 대로 CREATE TABLE ~ SELECT ~를 실행할 때 ORDER BY로 정렬하고 나서, LIMIT나 OFFSET을 사용해서 복사할 레코드의 수와 시작 위치를 지정합니다.

그러면 매출정보 테이블 tb에서 매출이 가장 좋은 레코드를 제외하고 상위 2번째부터 5번째 레코드까지만 추출해 보겠습니다. 이전의 매출 하위 3건만 '힘내!'라고 수정한 내용을 참고로 합니다.

구체적으로는 테이블 tb의 칼럼 구조와 칼럼 sales의 값이 상위 2번째부터 5번째까지인 4개의 레코드만 복사하여 새로운 테이블 tb_2to5를 생성합니다.

다음 명령을 실행한다.

CREATE TABLE tb_2to5

SELECT *

FROM tb

ORDER BY sales

DESC

LIMIT 4

OFFSET 1;

다음 명령을 실행한다.

SELECT * FROM tb_2to5;

상위 2번째부터 5번째까지 4개의 레코드만 있는 테이블 tb_2to5가 생성되었습니다.

3. 조건에 일치하는 레코드만 삭제하기

3. 조건에 일치하는 레코드 삭제하기

3.1 모든 레코드 삭제하기 - 복습

9장 2.1 특정 레코드만 복사하기에서는 레코드를 선택해서 복사했습니다. 이번에는 레코드를 삭제해 보겠습니다. 삭제 명령은 DELETE FROM 테이블_이름;이었습니다. (7장 모든 레코드 삭제하기)

DELETE 명령을 실행하면 레코는 삭제되지만 테이블(테이블의 칼럼 구조)은 그대로 남아 있습니다. 참고로, 테이블 자체를 삭제할 때에는 DROP TABLE... 명령을 사용합니다. (7장 테이블 삭제하기)

3.2 특정 레코드 삭제하기

조건에 일치하는 레코드만 삭제하는 방법에 대해 설명하겠습니다. 이제까지 WHERE로 조건을 설정해서 SELECT를 실행하는 방법에 대해 배웠는데, 이 SELECT 대신에 DELETE를 사용한다고 생각하면 됩니다. DELETE FROM ~으로 레코드를 삭제 할 때 WHERE로 조건을 설정합니다.

조건에 일치하는 레코드 삭제하기

DELETE FROM 테이블_이름 WHERE 조건;

사원정보 테이블 tb1을 보면 나이는 23~40세입니다. 나이가 30 미만인 레코드만 삭제해 보겠습니다.

레코드 삭제를 연습할 때에는 7장을 참고로 해서 미리 연습용 테이블을 준비해 둡니다. 여기에서는 사원정보 테이블 tb1과 같은 내용의 테이블 tb1K가 있다는 가정하에 설명하겠습니다.

테이블 tb1K에서 칼럼 age의 값이 30 미만인 레코드 3개만 삭제하겠습니다. 그러고 나서 레코드를 삭제한 테이블 tb1K의 모든 레코드를 표시해 보겠습니다.

다음 명령을 실행한다.

CREATE TABLE tb1K SELECT * FROM tb1;

다음 명령을 실행한다.

DELETE FROM tb1K WHERE age<30;

다음 명령을 실행한다.

SELECT * FROM tb1K;

칼럼 age의 값이 30 미만인 레코드가 삭제되고 30 이상인 레코드만 남았습니다.

4. 오름차순(내림차순)으로 정렬한 레코드 중 지정한 건수만큼의 레코드만 변경하기

3.2 순서대로 정렬하여 삭제하기

이번에는 순서대로 정렬해서 삭제하는 방법입니다. 상위 4번째까지의 레코드를 삭제하는 예를 설명하겠습니다. ORDER BY를 사용해서 순서대로 정렬하고 나서, LIMIT를 사용해서 삭제할 레코드의 수를 지정합니다.

매출정보 테이블 tb를 복사한 테이블 tb_copy에서 상위 4번째까지의 매출 정보만 삭제해 보겠습니다. 즉, 테이블 tb_copy의 칼럼 sales의 값 중에서 상위 4개의 레코드를 삭제합니다. 그러고 나서 삭제한 테이블 tb_copy의 모든 레코드를 표시해 보겠습니다.

참고로, 테이블 tb와 같은 내용의 테이블 tb_copy를 만들 때에는 CREATE TABLE tb_copy SELECT * FROM tb; 명령을 사용합니다.

다음 명령을 실행한다.

DELETE FROM tb_copy

ORDER BY sales

DESC

LIMIT 4;

다음 명령을 실행한다.

SELECT * FROM tb_copy;

실행 전의 레코드와 비교해 보면 칼럼 sales의 상위 4개의 레코드만 삭제된 것을 확인할 수 있습니다.

체크!

★ UPDATE 명령을 사용해서 칼럼의 값을 변경할 수 있다.

★ WHERE를 사용해서 조건에 일치하는 레코드의 칼럼만 변경할 수 있다.

★ WHERE를 사용해서 조건에 일치하는 레코드만 복사할 수 있다.

★ ORDER BY와 LIMIT를 사용해서 지정한 레코드만 변경할 수 있다.

★ WHERE를 사용해서 조건에 일치하는 레코드만 삭제할 수 있다.