11. [SQL기초] 테이블 행 추가, 수정, 삭제
행 추가하기
이제 데이터를 가져오기만 하는 단계에서 더 나아가 테이블에 데이터를 추가하는 방법을 알아보겠습니다.
테이블에 새 행 데이터를 추가하려면 INSERT 문을 사용합니다.
기본 구문
INSERT INTO 테이블 이름 [열 이름1, 열 이름2, …] VALUES (컬럼 값1, 컬럼 값2, …);
INSERT 문의 열 이름은 테이블의 모든 컬럼의 값을 테이블에 정의된 컬럼 순서대로 표시하면 생략 가능합니다. 전체 컬럼 개수가 몇 개 안 되는 테이블이 아니면 열 이름은 기본적으로 명시를 한다고 생각하는 것이 좋습니다.
테이블 정의를 확인하는 방법은 “desc 테이블 이름;”으로 확인합니다.
예제 데이터베이스에서 컬럼 개수가 3개만 있는 원산지(madein) 테이블의 정의를 확인한 후 테이블에 새 행을 추가합니다.
Desc maker;
Insert into madein values(5, ‘인도네시아’, ’Indonesia’);
데이터를 추가하는 구문은 항상 INSERT INTO로 시작합니다. 저장할 값을 명시하는 VALUES 절은 항상 괄호로 감싸서 컬럼 값들을 쉼표로 구분해서 나열합니다. 컬럼 값은 테이블 정의에 정의한 컬럼의 데이터 형과 같은 값을 표시해야 합니다. 숫자 값을 넣어야 하는 컬럼에 문자열을 넣으면 에러가 발생합니다.
단, 타입 캐스팅(데이터 형 변환)이 가능한 경우에는 자동으로 값이 변환되어 입력됩니다.
insert into madein (country, engname) values ('러시아', 100);
문자열이 들어가야 할 “engname” 컬럼 값으로 숫자 100을 입력했지만, 타입 캐스팅이 가능하기 때문에 문자열 “100”으로 값이 저장됩니다. 저장 과정 중에 데이터베이스가 CAST(100 as CHAR)를 해서 저장한다고 생각하면 됩니다. CAST, 또는 CONVERT로 데이터 형 변환이 가능한 값은 저장 과정 중에 데이터베이스가 내부적으로 처리를 해줍니다.
열 이름을 생략하려면 모든 컬럼 값을 표시해야 하며, 값이 자동 증가하는 컬럼의 값까지 명시해야 합니다. 값이 자동 증가하는 컬럼에 대해서는 뒤에서 더 자세히 배웁니다. 예외적으로 SQL Server만은 열 이름을 표시하지 않아도 자동 증가 값이 자동 입력됩니다. MySQL과 오라클에서는 이렇게 INSERT문을 사용하면 에러가 발생합니다.
Insert into madein values(‘인도네시아’, ’Indonesia’); /*SQL Server에서는 사용 가능*/
에러메시지는 조금씩 다르지만 “Column count doesn't match”, 그러니까 컬럼 개수가 맞지 않는다는 에러를 표시합니다.
열 이름을 명시하면 자동 증가 컬럼과 값은 표시하지 않아도 됩니다. 데이터베이스가 행 데이터를 저장하면서 자동 증가 컬럼의 값을 현재 최대값 + 1로 자동 등록합니다.
insert into madein (country, engname) values ('스페인', 'Spain');
행들이 잘 추가되었는지 확인합니다.
Select * from madein;
열 이름을 표시하지 않는 경우에는 제약 사항이 하나 더 있습니다. 앞에서 이미 “테이블에 정의된 컬럼 순서대로”라고 설명했습니다. 이 설명은 중요한 의미를 가집니다. 원산지(madein) 테이블 정의를 보면 컬럼 정의 순서가 국가 명(country), 영문명(engname) 순으로 정의가 되어 있습니다.
다음 쿼리문은 국가 명에 “Portugal”이 저장되고, 영문명 컬럼에 “포르투갈”이 저장됩니다. 열 이름을 생략하면 컬럼 값의 순서를 임의로 변경할 수 없으며, 컬럼 값을 생략할 수도 없습니다.
Insert into madein values(20, ’Portugal’, ‘포르투갈’);
컬럼에 올바르게 데이터가 저장되도록 하려면 열 이름을 명시해서 컬럼 값과 순서가 일치하도록 해야 합니다. 사용상의 제약과 함께 올바르지 않은 컬럼 값이 저장되는 실수를 줄이기 위해서는 열 이름을 명시하는 INSERT문을 사용해야 합니다.
Insert into madein (engname, country) values (’Portugal’, ‘포르투갈’);
저장 값이 없는 행과 NOT NULL 제약 조건
원산지(madein) 테이블에는 자동 증가 컬럼을 제외하고 2개의 컬럼에 컬럼 값을 입력할 수 있습니다. 테이블 정의를 보면 원산지 국가 명(COUNTRY) 컬럼에는 Null을 사용할 수 없다고 명시되어 있습니다. 테이블을 처음 생성할 때 “NOT NULL”로 컬럼에 명시를 해서 NULL을 저장할 수 없도록 하기 때문에 “NOT NULL 제약 조건”이라고 합니다.
제약 조건에는 여러가지가 있으며, 그중 특별한 값인 NULL은 데이터를 다룰 때 여러 가지 예외 처리를 해야 하기 때문에 애초에 NULL을 넣을 수 없도록 제약 조건으로 가장 많이 사용합니다.
NOT NULL로 제약 조건이 걸린 컬럼에는 NULL을 넣을 수 없습니다. 또는, 저장 값이 없으면 INSERT문이 실행을 할 때 에러가 발생합니다. INSERT문으로 행 데이터를 저장할 때 컬럼 값이 지정되지 않은 컬럼은 기본 값으로 NULL이 지정됩니다. NULL을 넣을 수 있는 영문명(engname) 컬럼에 NULL이 들어가도록 쿼리문을 실행합니다.
Insert into madein (country) values (‘포르투갈’);
추가된 행을 확인하면 다음처럼 영문명(engname) 컬럼이 NULL이 들어있는 것을 볼 수 있습니다.
앞의 INSERT 쿼리문은 다음의 쿼리문과 같습니다.
Insert into madein (country, engname) values (‘포르투갈’, NULL);
반대로 NULL이 들어갈 수 없도록 정의한 국가 명(country) 컬럼에 값을 저장하지 않으면 에러가 발생합니다.
Insert into madein (engname) values (‘Portugal’);
에러 메시지는 데이터베이스마다 다르지만, NULL을 넣을 수 없거나, 기본 값이 지정되어 있지 않다는 식의 메시지가 표시됩니다. 국가 명(country) 컬럼에 기본 값이 설정되어 있었다면 쿼리문은 실행이 되었을 겁니다. 앞의 쿼리문은 다음과 같습니다.
Insert into madein (country, engname) values (NULL, ‘Portugal’);
NOT NULL 제약 조건이 있는 컬럼에는 반드시 값을 명시해야 합니다. 문자열 컬럼에는 빈 문자열(‘’)이라도 컬럼 값을 명시해야 저장 에러가 발생하지 않습니다.
저장 값이 없는 행의 기본값
NULL을 허용하지 않는 컬럼은 INSERT문으로 행을 추가할 때 컬럼 값을 항상 표시해야 합니다. 매번 기본값을 명시하는 것이 번거롭기 때문에 테이블을 정의할 때 컬럼에 값이 입력되지 않으면 기본으로 저장되는 값을 설정할 수 있습니다.
제조사(maker) 테이블 정의를 먼저 확인합니다.
Desc maker;
테이블 정의를 보면 “Default” 항목이 있습니다. 이 항목이 기본값을 정하는 항목입니다. LONGNAME 컬럼 정의를 보면 NULL 대신 빈 공백이 표시됩니다.
문자열 항목에 NULL 대신 기본값으로 빈 문자열(‘’)이 들어가도록 하려면 컬럼 정의를 할 때 “default ‘’” 로 명시를 해서 INSERT문으로 행을 추가할 때 컬럼 값이 명시되지 않았으면 빈 문자열이 자동으로 저장됩니다.
Insert into maker(name) values (‘프렌즈월드’);
저장된 행을 SELECT 문으로 확인하면 다음처럼 LONGNAME 컬럼에 NULL이 아닌 빈 문자열이 들어가 있는 것을 확인할 수 있습니다.
이 쿼리문은 다음 쿼리 문과 같습니다. 기본값으로 저장할 컬럼 값에 “DEFAULT” 키워드를 사용할 수 있으며, “DEFAULT”로 컬럼 값을 표시한 컬럼은 테이블 정의를 할 때 정한 기본 값이 저장됩니다.
Insert into maker(name, longname) values (‘프렌즈월드’, DEFAULT);
행 데이터 수정하기
테이블의 모든 행 데이터는 수정할 수 있습니다. 쇼핑몰 주문 정보를 저장한 테이블은 이용자가 배송 주소를 변경했을 수도 있고, 상품을 발송하면 배송 정보를 등록하면서 주문 정보가 갱신될 수도 있습니다.
테이블의 종류에 따라서는 행의 추가나 삭제보다 행의 갱신이 더 빈번하게 일어날 수 있습니다.
기본 구문
UPDATE 테이블 이름 SET 열 이름1 = 컬럼 값1, 열 이름2 = 컬럼 값2, … WHERE 조건식
UPDATE문은 INSERT, DELETE문이 행 단위로 추가, 삭제 처리를 하는 것과 달리 컬럼 단위로 값을 적용합니다. 변경하고자 하는 컬럼만 값을 적용하면 되기 때문에, 값의 변경이 없는 컬럼은 SET절에 명시를 할 필요가 없습니다.
변경할 열 이름과 컬럼 값을 SET절에 나열하고, 컬럼과 컬럼사이는 쉼표로 구분합니다. 컬럼 1개의 업데이트는 “열 이름 = 컬럼 값”으로 등호(=)로 대입하는 형태로 작성합니다.
UPDATE문은 DELETE문처럼 WHERE절 조건이 참이 되는 모든 행의 컬럼에 값이 적용됩니다. 여러 행의 컬럼에 같은 컬럼 값을 적용할 수 있습니다.
행 데이터를 수정할 때 주의할 점
행 삭제와 마찬가지로 행 데이터 수정을 할 때도 주의해야 할 점이 있습니다. 조건절이 없거나, 잘못된 조건절을 사용해 UPDATE문을 실행하면 테이블의 모든 행에 값이 적용됩니다. 수정 후에는 돌이킬 수 없으므로 UPDATE문을 사용할 때는 조건절을 사용할 때 주의해야 합니다.
주문 ID가 1인 주문 내역 1개만 삭제하려고 했는데 실수로 조건절에 USERID를 비교하면 삭제하면 안 되는 엉뚱한 주문 목록을 삭제하게 될 수 있습니다.
select * from p_order where userid = 1;
회원정보 테이블을 SELECT문으로 조회하면 일부 회원의 닉네임이 NULL인 것을 확인할 수 있습니다. NULL로 인한 예외처리 및 웹사이트에서 회원정보를 표시할 때 닉네임이 무조건 표시될 수 있도록 하기 위해 회원정보(member) 테이블에서 닉네임(nickname)이 설정되지 않은 회원의 닉네임을 이름으로 강제 적용해보겠습니다.
Update member set nickname = name where nickname is null;
대입 연산자를 사용해 값을 변경할 열 이름을 왼쪽에, 값을 가져올 열 이름을 오른쪽에 표시하면 각 행의 오른쪽 컬럼 값을 왼쪽 컬럼에 대입해 갱신합니다. 앞의 예는 닉네임이 NULL인 회원들의 닉네임 컬럼에 회원 본인의 이름을 각각 대입해 저장합니다.
UPDATE문의 SET절에서 갱신하는 컬럼은 WHERE절의 조건에 부합하는 모든 행에 대해서 각각 개별적으로 실행된다는 점을 꼭 기억해야 합니다.
여러 컬럼의 값을 갱신하려면 쉼표로 구분해 컬럼 값 대입식을 나열합니다.
주문정보 테이블(p_order)에서 상품코드 “2”의 상품정보 표시가 잘못되어서 25000원에 2500원 배송비가 책정되어야 하는 상품이 45000원에 무료배송으로 표기가 되어 주문이 등록되었습니다. 주문 정보의 판매 가격과 배송비를 수정해보겠습니다.
Update p_order set p_sell_price = 25000, dtdmoney = 2500 where p_code = 2;
SET절 컬럼 값의 실행 순서
SET절에는 여러 개의 컬럼 값을 표시해서 적용할 수 있습니다. 여러 개의 컬럼을 갱신할 때는 컬럼을 갱신하는 방식에 따라 갱신되는 값이 다를 수 있습니다. MySQL과 SQL Server, 오라클은 갱신을 하는 방식이 다릅니다. 오라클과 SQL Server는 같은 방식으로 동작합니다.
MySQL은 SET절에 명시한 열 이름이 나오는 순서대로 각각 갱신이 실행됩니다. 컬럼 한 개의 갱신이 완료되면, 다음 컬럼의 갱신을 합니다. 컬럼에 대입하는 등호 오른쪽의 계산식 또한 컬럼 단위로 순서대로 개별 실행됩니다.
SQL Server와 오라클은 다르게 실행됩니다. SET절에 있는 각 컬럼의 등호 오른쪽에 있는 계산식들의 계산을 먼저 해서 상수가 되도록 한 후 일괄로 왼쪽 컬럼에 대입을 합니다.
주문정보(p_order) 테이블에 있는 주문 중 주문 코드 4의 상품은 배송비 2,500원을 무료 배송으로 처리하기로 하고, 대신 배송비를 판매 가격에 더하기로 합니다. 택배사에 배송비 정산을 할 때 정확한 금액을 알 수 있도록 하기 위해서입니다.
먼저 SELECT문으로 금액을 확인합니다.
배송비는 2,500원이고, 상품 가격은 1,400원입니다. UPDATE문으로 다음과 같이 컬럼을 갱신하면 배송비는 0원, 판매 가격은 3,900원이 되어야 합니다.
update p_order set dtdmoney = 0, p_sell_price = p_sell_price + dtdmoney where id = 4;
SELECT문으로 실행 결과를 확인하면 예상과 다른 것을 알 수 있습니다. 배송비는 0원이 되어있지만, 판매 가격은 1,400원으로 그대로입니다.
앞서 설명한 대로 MySQL은 SET절의 컬럼 값을 갱신할 때 컬럼이 나오는 순서대로 하나씩 등호(=) 오른쪽의 계산식, 또는 대입식을 계산한 후 바로 컬럼 값을 갱신합니다. 컬럼 값의 갱신이 끝나면 그 다음 컬럼의 갱신 작업을 합니다.
따라서 앞의 UPDATE문은 앞서 나오는 dtdmoney = 0 컬럼 값 갱신에 따라 dtdmoney 컬럼 값이 0으로 바뀌고 나서 p_sell_price = p_sell_price + dtemoney 컬럼 값 갱신이 실행됩니다. p_sell_price = p_sell_price + dtemoney 계산식은 앞선 컬럼에서 dtdmoney가 0으로 갱신되었기 때문에 p_sell_price = p_sell_price + 0인 계산식이 되고, 원래의 판매가 그대로를 갱신하게 됩니다.
앞의 쿼리문을 다음과 같이 판매가 갱신과 배송비 갱신 컬럼의 순서를 바꿉니다.
update p_order set p_sell_price = p_sell_price + dtdmoney, dtdmoney = 0 where id = 4;
SELECT문으로 갱신된 결과를 확인하면 의도한 대로 배송비는 0원, 판매가는 3,900원이 되어 있습니다.
오라클과 SQL Server는 MySQL과 다르게 SET절의 모든 컬럼들의 등호(=) 오른쪽 계산식을 전부 먼저 계산해서 상수화 합니다.
앞서 처음의 쿼리문을 실행하면 MySQL과 달리 배송비는 0원, 판매가는 3,900원으로 정확하게 바뀝니다.
update p_order set dtdmoney = 0, p_sell_price = p_sell_price + dtdmoney where id = 4;
두 가지 UPDATE문 계산식 처리 방법 중 어느 것이 더 나은 방식이라고 할 수는 없습니다. 데이터베이스의 설계 개념에 차이가 있고, 사용자 편의성을 고려할 때 어떤 것에 더 중점을 둘 것이냐 차이에서 오는 구현 방식의 차이입니다.
MySQL의 SET절에 오는 컬럼 순서 별 개별 처리 방식이 경우에 따라서는 더 효율적으로 동작하기도 합니다.
컬럼에 NULL 적용하기
UPDATE문을 이용해 NULL이 아닌 컬럼 값을 NULL로 변경할 수 있습니다. 단, NOT NULL 제약 조건이 설정되지 않은 컬럼만 가능합니다.
예외적이기는 하지만, 컬럼 값이 NULL인 경우를 특정 상태를 가리키는 것으로 프로그램에서 사용하는 경우 상태를 초기화하기 위해 NULL을 적용할 필요가 있습니다.
회원정보(member) 테이블에서 이메일 주소를 빈칸(‘’)으로 등록한 회원들은 이메일 주소를 NULL로 변경해서 회원 로그인을 할 때 이메일 주소를 무조건 재 등록하도록 표시를 하기로 합니다.
Update member set email = null where email = ‘’;
행 삭제하기
테이블의 행을 삭제합니다. SELECT문처럼 WHERE 절로 조건식을 지정해서 조건이 참이 되는 행들을 모두 삭제할 수도 있습니다.
기본 구문
DELETE FROM 테이블 이름 WHERE 조건문;
앞서 원산지(madein) 테이블에 새로 추가했던 행들 중 하나를 삭제해봅니다.
Delete from madein where id = 5;
여기에 더해서 ID가 3보다 큰 행들을 모두 삭제해서 원래 내용을 복구해봅니다.
Delete from madein where id > 4;
SELECT문으로 조건식에 맞는 행 데이터가 모두 삭제되었는지 확인합니다.
컬럼 값이 NULL인 행을 찾아서 삭제하려면 다음과 같이 NULL 조건식을 작성해야 합니다. SELECT문의 WHERE절에서 NULL 체크를 하는 방식과 같습니다.
Delete from madein where LONGNAME IS NULL;
DELETE 문의 WHERE절에는 SELECT문에서 사용 가능한 모든 WHERE절의 조건식을 사용할 수 있습니다.
행 삭제의 위험성
모든 쿼리문이 그렇지만 DELETE문으로 행을 삭제하는 명령은 사용자에게 확인을 하지 않습니다. 행 추가나 수정이라면 데이터가 남아있으므로 원상 복구를 위한 단서가 있지만, DELETE문은 말 그대로 삭제를 합니다.
실수로 다음과 같은 삭제 쿼리문을 실행하면 테이블의 모든 주문 내역이 삭제됩니다.
Delete from p_order;
나는 안 하겠지 하는 이런 실수를 생각보다 많이 합니다. 뒤에서 배우겠지만, 트랜잭션 기능을 이용하면 이런 실수를 어느 정도는 방지를 할 수 있습니다.
테이블에서 데이터를 할 때는 항상 주의를 해야 합니다. 데이터 관리 기능을 구현할 때 실제 행을 삭제하는 것이 아니라 삭제했다는 표시를 하는 표시용 컬럼을 추가해 표시를 하는 방식으로 삭제한 행을 구분하는 구현 방식을 많이 사용합니다.
실수로 삭제했을 때 문제가 크게 생길 수 있는 테이블, 전체 테이블에 누적되는 데이터가 데이터베이스 스토리지 공간을 많이 차지하지 않아 매번 삭제하지 않아도 되는 경우에는 이런 방식이 아주 유용합니다.
특히 삭제했을 때의 위험성이 큰 데이터들은 필수적으로 이런 구현 방식을 사용해야 합니다. 이런 구현 방식을 소프트 삭제(Soft Delete) 디자인이라고 합니다.
웹사이트의 질문과 답변 기능을 구현한 테이블(qna)에서 이용자가 자신의 글을 지웠다고 테이블에서도 그대로 삭제해버리면 안 됩니다. 과거에 어떤 질문을 했는지 히스토리를 남겨야 할 필요가 있기 때문에 ISDELETE 컬럼으로 삭제 여부를 표시하고 이용자에게는 표시되지 않도록 하는 방식으로 데이터 관리를 할 수 있습니다.
꼭 삭제 표시가 아니어도 이런 관리 방식으로 테이블의 데이터를 유지함으로써 데이터 소실에 대한 위험성을 낮을 수 있습니다.
내가 받았던 쿠폰 목록을 가지고 있는 내 쿠폰(mycoupon) 테이블의 쿠폰 목록은 쿠폰을 사용했다고 삭제를 하면 안됩니다. 중복 쿠폰 발급이나, 부정 사용 체크, 과거 쿠폰 사용 내역을 기초로 한 마케팅 활용 등 활용할 구석이 많기 때문입니다. 사용자에게는 이미 사용한 쿠폰은 보이지 않도록 하면서 실제 발급받았던 쿠폰 목록은 남겨두려면 ISUSED 컬럼으로 Y/N 표시를 해서 관리를 할 수 있습니다.
삭제할 수 없는 행
원산지 테이블(madein)의 행 한 개를 삭제해보겠습니다.
Delete from madein where id = 1;
잘 삭제됩니다. 제조사 테이블(maker)의 행 한 개를 삭제해봅니다.
Delete from maker where id = 1;
다음과 같은 에러 메시지가 표시됩니다.
Cannot delete or update a parent row : a foreign key constraint fails
데이터베이스에 따라 메시지는 조금씩 다르지만, 외래 키(foreign key) 참조로 인해 행을 삭제할 수 없다는 메시지입니다. 외래 키에 대해서는 뒤에서 배우므로 여기서는 다른 테이블에서 이 테이블의 행을 참조하고 있어서 삭제할 수 없다는 뜻이라는 것만 알면 됩니다.
테이블의 관계를 통해 데이터를 구조화하는 관계형 데이터베이스는 삭제하려는 행의 키 값을 다른 테이블에서 참조하고 있으면 해당 행을 삭제할 수 없습니다. 데이터의 무결성을 지키는 가장 근본이 되는 원칙이기 때문에 이 규칙을 깰 수는 없습니다.
그래서 참조가 유지되는 한 이 행을 삭제할 수가 없습니다.
단, 삭제를 하는 방법이 없는 것은 아닙니다. 이렇게 일반적인 삭제 문으로 삭제할 수 없는 행을 삭제하는 방법에 대해서는 뒤에서 배웁니다.