SQL 데이터베이스에 대해 제대로 알자(5) - 추가, 삭제, 갱신

지난포스팅 목록

 

1편(데이터베이스)

2편(조건조합)
3편(정렬)

4편(연산)에 이어서 5편에서는 데이터의 추가, 삭제, 갱신부터 이어서 배워보려고 한다.

 

 

<INSERT, DELETE, UPDATE>

이번에는 데이터를 추가하는 방법에 대해 알아보자

 

데이터베이스에서 테이블에 행을 추가하기 위해서는 INSERT 명령을 사용한다.

 

INSERT INTO 테이블명 VALUES (값1, 값2, ...)

 

지금까지 배운 SELECT 명령은 데이터를 검색하기 위한 것으로 질의를 하면 데이터베이스 서버가 클라이언트로 결과를 반환하는 형식으로 처리되었다.

 

데이터를 추가할 경우에는 이와 반대로 클라리언트에서 서버로 데이터를 전송하는 형식을 취하며 서버 측은 전송받은 데이터를 데이터베이스에 저장한다.

 

웹 페이지에서 '신규등록'이나 '추가'와 같은 버튼을 클릭했을 때 처리되는 데이터 추가 기능이라 생각하면 이해하기 쉽다. 그럼 지금부터 데이터를 추가하는 데 필요한 INSERT 명령에 대해 알아보자

 

INSERT로 행 추가하기

RDBMS에서는 INSERT 명령을 사용해서 테이블의 행 단위로 데이터를 추가한다.

 

데이터베이스를 생성하면 테이블에는 아무런 데이터도 저장되어 있지 않은 상태가 된다.

 

이후 INSERT 명령으로 데이터를 추가해 데이터베이스에 구축한다.

 

INSERT명령을 실행하기 전에 기존의 테이블에 어떤 데이터가 저장되어 있는지 SELECT 명령으로 확인해보자

 

SELECT * from testsql;

이렇게 3개의 열이 정의되어 있지만 저장된 데이터가 아무것도 없어서 표시된 것은 없다.

 

INSERT명령을 통해 행을 추가하려면 각 열의 값을 지정해야한다. 

 

각 열에 어떤 유형의 데이터를 저장할 수 있는지 DESC 명령으로 확인해보자.

 

DESC testsql;

 

no 열은 int(11)이므로 수치형 데이터를 저장할 수 있다.

a 열은 varchar(30)이므로 최대길이가 30인 문자열을 저장할 수 있다.

b 열은 날짜시간형 데이터를 저장할 수 있다.

 

그럼 INSERT 명령으로 행을 추가해보겠다. 먼저 INSERT INTO 뒤에 추가할 테이블을 지정한다.

 

INSERT INTO testsql

 

저장할 데이터를 지정하지 않았기 때문에 아직은 INSERT 명령문이 완성되지 않은 상태이다. 행의 데이터는 다음과 같이 VALUES 구를 사용해 지정한다.

 

INSERT INTO testsql VALUES(1, 'ABC', '2014-01-25');

값을 지정할 때는 해당 열의 데이터 형식에 맞도록 지정해야한다. 앞에서 본 명령을 실행하면 testsql 테이블에 행이 추가된다.

 

다만, INSERT 명령을 실행해도 처리상태만 표시될 뿐 SELECT 명령을 실행했을 때 처럼 결과를 출력하지는 않는다.

 

SELECT 명령의 경우 실행하면 그 결과가 클라이언트에게 반환되지만, INSERT 명령은 데이터가 클라이언트에서 서버로 전송되므로 반환되는 결과가 없는 것이다. 그럼 이번에는 실제로 행이 추가되었는지 SELECT 명령을 이용해 확인해보자

 

SELECT * from testsql;

기존에는 없던 행이 추가된 것을 확인 할 수 있다.

값을 저장할 열 지정하기

INSERT 명령으로 행을 추가할 경우 값을 저장할 열을 지정할 수 있다. 열을 지정할 경우에는 테이블명 뒤에 괄호로 묶어 열명을 나열하고,

VALUES 구로 값을 지정한다. VALUES 구에 값을 지정할 경우에는 지정한 열과 동일한 개수로 값을 지정해야 한다.

 

INSERT INTO testsql(열1, 열2, ...)VALUES(값1, 값2, ....)

 

이제 정리한 문법을 적용하면 지정한 열에 값을 넣어 행을 추가할 수 있다. 

그럼 지금부터 testsql 테이블에 no열과 a열만 지정해서 행을 추가해보도록 하자

 

INSERT INTO testsql(a, no)VALUES('XYZ',2);

SELECT * from testsql;

2번째 행이 추가되었다.

 

특히 별도의 값을 지정하지 않았던 b열에는 기본값인 NULL(b 열의 default 값)이 저장된 것을 알 수 있다.

 

이에 관해서는 뒤에서 다시한번 상세하게 다룰 예정이다.

 

NOT NULL 제약

행을 추가할 때 유효한 값이 없는 상태(NULL)로 두고 싶을 경우에는 VALUES구에서 NULL로 값을 지정할 수 있다.

실제로 testsql에 대한 모든 열의 값이 NULL인 행을 추가해보자

 

INSERT INTO testsql(no,a,b)VALUES(NULL,NULL,NULL);

하지만 이렇게 명령을 실행하면 에러가 발생한다. 이는 no 열에 대해 NULL값을 허용하지 않는 NOT NULL 제약이 걸려있기 때문이다.

 

이와 같이 테이블에 저장하는 데이터를 설정으로 제한하는 것을 통틀어 '제약' 이라고 부른다. NOT NULL 제약은 그중 하나로 이 외에도 다양한 제약이 있다.

 

그렇다면 앞의 INSERT 명령에서 no 열 값을 '3'으로 변경하여 다시 실행해보겠다. 이때는 NULL 값이 아니므로 문제없이 실행될 것이다.

INSERT INTO testsql(no,a,b)VALUES(3,NULL,NULL);

SELECT * from testsql;

정상적으로 의도한대로 데이터가 들어가졌다.

 

NULL은 여러 측면에서 주의할 필요가 있다. 지금까지 알아본 것 처럼 NULL의 조건을 비교할 때는 IS NULL을 이용해야 하며 

NULL을 포함한 연산 결과는 모두 NULL이 되어버리기도 한다. NULL을 허용하고 싶지 않다면 NOT NULL 제약을 걸어두는 편이 좋다.

 

DEFAULT

 

DESC 명령으로 열 구성을 살펴보면 Default라는 항목을 찾을 수 있다.

 

Default는 명시적으로 값을 지정하지 않았을 경우 사용하는 초깃값을 말한다.

 

Default 값은 테이블을 정의할 때 지정할 수 있다. 열을 지정해 행을 추가할 때 지정하지 않은 열은 Default 값을 사용하여 저장된다.

 

실제로 지금까지 앞에서 살펴보았던 testsql의 경우, Default는 모두 NULL이었다. 그래서 값을 생략하면 초깃값으로 NULL을 저장하는 것이다. 더 구체적인 예를 들어보면 먼저 DESC 커맨드로 testsql의 열 구성을 살펴보자 

 

DESC testsql;

c라는 컬럼을 추가했는데 c컬럼 즉 c열에는 디폴트값으로 0이 설정되어 있다. 이때 c 열에 다음과 같이 숫자를 지정해 행을 추가해보겠다. 그러면 테이블에 지정된 값을이 문제없이 저장되는 것을 확인할 수 있다.

 

INSERT INTO testsql(no, c) VALUES(1,1);

SELECT * from testsql;

이렇게 값이 잘 들어간 것을 확인 할 수 있다.

 

다음으로는 디폴트값을 지정해 행을 추가해보자 VALUES 구에서 DEFAULT 키워드를 사용하면 디폴트 값이 저장된다.

 

이처럼 디폴트값을 지정하는 것을 'DEFAULT를 명시적으로 지정하는 방법'이라고 한다.

 

INSERT INTO testsql(no,c) VALUES (2, DEFAULT);

SELECT * from testsql;

이렇게 no 2번에 c에는 디폴트값 0이 INSERT 되었다.

암묵적으로 디폴트 저장

한편 앞에서 살펴본 것 처럼 암묵적으로 지정하는 방법도 있다.

여기서 암묵적인 방법이란,

 

디폴트값으로 저장할 열을 INSERT 명령문에서 별도 지정하지 않는 것을 말한다.

실제로 c 열을 제외하고 no 열만 지정해서 INSERT 명령을 실행해보자.

INSERT INTO testsql (no) VALUES(3);

SELECT * from testsql;

 

그 결과 명시적 방 법과 암묵적 방법 중 어떤 것을 사용해도 c열의 값이 디폴트로 저장됨을 확인할 수 있다.

 

삭제하기-DELETE

 

이번에는 데이터를 삭제하는 방법에 대해 배워보자

데이터베이스에서 데이터를 삭제하기 위해서는 DELETE 명령을 사용한다.

 

DELETE FROM 테이블명 WHERE 조건식

 

데이터베이스는 하드디스크와 같은 저장장치에 데이터를 저장해 관리한다. 하지만 저장장치에는 저장용량이 정해져 있는 만큼 데이터를 무제한으로 저장할 수는 없다. 그러다 보면 저장공간이 모자라 데이터를 삭제해야 하는 경우도 자주 발생한다.

 

실제로 시스템에서 '식제'나 '취소'버튼을 이용해 데이터를 삭제하는 경우가 많을 것이다.

 

이때 사용하는 DELETE명령에 관래 알아보자

 

DELETE로 행 삭제하기

 

RDBMS에서 데이터를 삭제할 경우에는 행 단위로 DELETE 명령을 수행한다.

지금부터 테이블에서 행을 삭제하는 DELETE 명령의 사용 방법에 대해 알아보자

 

DELETE FROM 테이블명 WHERE 조건식

 

DELETE명령을 실행하기 전에 기존 테이블에 어떤 데이터가 저장되어 있는지 SELECT 명령으로 확인한다.

 

SELECT * from testsql;

여기서 만약 DELETE FROM 테이블명; 으로 DELETE명령을 실행하면 해당 테이블에 있는 모든 데이터가 삭제된다.

 

DELETE명령에는 WHERE 구를 지정할 수 있으나 SELECT 명령처럼 WHERE 구를 생략할 경우에는 모든 행을 대상으로 동작하기 때문이다. 한편 WHERE 구를 지정한 경우에는 해당 조건식에 맞는 행만 삭제 대상이 된다.

 

그럼 WHERE 구에 조건을 지정하여 no열이 3인 행만 삭제해보자

 

DELETE FROM testsql WHERE no =3;

SELECT * from testsql;

 

실행결과, no 열의 값이 3인 행이 삭제되었다. 이처럼 삭제는 행 단위로 수행된다.

 

SELECT 명령과 같이 열을 지정할 수는 없다. 즉, DELETE no FROM testsql과 같이 열을 지정하여 그 열만 삭제할 수는 없다는 것이다.

 

DELETE 명령을 실행할 때는 재확인을 위한 대화창 같은 것은 표시되지 않는다.

 

즉, WHERE 구에서 조건식을 잘못 지정하면 의도하지 않은 데이터마저 삭제된다. 따라서 DELETE 명령을 실행할 때는 주의를 기울여야 한다.

 

Point -> DELETE 명령은 WHERE 조건에 일치하는 '모든 행'을 삭제한다!

 

DELETE 명령 구

 

WHERE 구에서 대상이 되는 행을 검색하는 것은 SELECT 명령에서도 DELETE 명령에서도 똑같다. 

 

단지 SELECT 명령에서는 조건에 맞는 행의 결괏값이 클라이언트에게 반환되지만, DELETE 명령에서는 조건에 맞는 행이 삭제된다는 점만 다르다. 

 

앞선 예시에서 WHERE no=3으로 조건을 지정하여 no 열이 3인 행을 삭제했지만 조건식을 변경하여 삭제할 행을 바꿀 수 있다.

 

예를 들어 WHERE no =1 OR no =2 로 바꾸면 열이 1이나 2인 행을 삭제할 수 있다.

 

이처럼 DELETE 명령에서도 SELECT 명령처럼 WHERE 구를 지정할 수 있다. 하지만 ORDER BY 구는 사용할 수 없다.

어떤 행부터 삭제할 것인지는 중요하지 않으며 의미가 없기 때문이다.

 

데이터 갱신하기 - UPDATE

이번에는 데이터를 갱신하는 방법을 배워보자

 

테이블의 셀에 저장되어 있는 값을 갱신하려면 UPDATE 명령을 사용해야 한다.

 

UPDATE 테이블명 SET 열1=값1, 열2=값2, ... WHERE 조건식

데이터 갱신 작업은 시스템을 다루는 과정에서 자주 발생한다. 데이터를 잘못 입력하여 수정해야 하는 경우도 빈번하다.

이때 실제 시스템에서 '등록'이나 '갱신'버튼을 클릭하면 데이터 갱신이 이루어진다고 이해하면 된다.

 

지금부터 이런 경우에 사용하는 UPDATE 명령에 대해 알아보자

 

UPDATE로 데이터 갱신하기

RDBMS에서는 UPDATE 명령으로 데이터를 갱신할 수 있다. UPDATE 명령은 테이블의 셀 값을 갱신하는 명령이다.

 

UPDATE 테이블명 SET 열명=값 WHERE 조건식

 

 

UPDATE 명령을 실행하기 전에 기존 테이블에 어떤 데이터가 저장되어 있는지 SELECT 명령으로 확인해보자

 

SELECT * from testsql;

 

DELETE와 달리 UPDATE는 셀 단위로 데이터를 갱신할 수 있다. WHERE 구에 조건을 지정하면 그에 일치하는 행을 갱신할 수 있다.

WHERE 구를 생략한 경우에는 DELETE의 경우와 마찬가지로 테이블의 모든 행이 갱신된다.

 

UPDATE 명령에서는 SET 구를 사용하여 갱신할 열과 값을 지정한다.

 

문법은 'SET 열명 = 값' 이다.

 

이때 = 은 비교 연산자가 아닌, 값을 대입하는 대입 연산자이다. 같은 = 이지만 연산 방법이 다르다는 점에서 주의해야한다.

 

또한 테이블에 존자하지 않는 열을 지정하면 에러가 발생하여 UPDATE명령은 실행되지 않는다. 당연한 말이지만 열이 존재하지 않으므로

값도 갱신 할 수 없기 때문이다.

 

값은 상수로 표기한다. 앞에서 설명한 INSERT 명령과 마찬가지로 자료형에 맞는 값을 지정해야 한다. 수치형 열에는 수치형 리터럴로, 문자열형에는 문자열형 리터럴(데이터 그 자체)로 표기한다.

 

갱신해야 할 열과 값이 복수인 경우에는 '열=값'을 콤마(,)로 구분하여 리스트 형식으로 지정할 수 있다. SET 구에 지정한 갱신 내용은 처리 대상이 되는 모든 행에 적용된다.

 

 

그럼 testsql의 셀 값을 UPDATE 명령으로 갱신해보자.

 

여기서는 b열이 NULL인 행의 값을 UPDATE 명령으로 갱신해보자

 

먼저, 행 지정은 WHERE no=2로 한다. 물론 WHERE b IS NULL로 동일한 행을 지정할 수 있다.

갱신할 값은 SET b= '2014-09-07'로 하겠다. 이때 b열은 날짜형이므로 날짜의 리터럴로 값을 표기한다.

 

그리고 UPDATE 명령을 실행하면 b 열의 데이터가 갱신되는 것을 확인 할 수 있다.

 

SELECT * from testsql;

UPDATE testsql SET b= '2014-09-07' WHERE no =2;

SELECT * from testsql;

 

이렇게 no = 2 에 해당하는 2번과 4번의 데이터가 갱신되어서 내가 작성한 값으로 바뀌게 되었다.

 

Point -> UPDATE 명령으로 행의 셀 값을 갱신할 수 있다!

 

UPDATE 명령의 WHERE 조건문 역시 DELETE명령과 마찬가지로 조건에 일치하는 모든 행이 그 대상이 된다.

 

그리고 WHERE 구를 생략하면 테이블의 모든 행이 갱신 대상이 된다.

 

즉, WHERE 구를 생략하거나 잘못 지정할 경우 DELETE 명령에서 언급한 것처럼 의도하지 않은 처리가 발생할 수 있으므로

주의해야 한다.

 

UPDATE로 갱신할 경우 주의사항

SET 구에서 = 은 대입 연산자라는 점을 앞서서 설명했다. 일단 테이블을 생성하면 INSERT 명령을 이용해 행을 추가하기 전까지는

아무런 데이터도 존재하지 않는다. 하지만 행을 추가하면 되므로 아무런 문제가 되지 않는다. 한편 UPDATE 명령은 이미 존재하는 행에 대해 값을 갱신하는 것으로 이전의 값과 이후의 값의 두 가지 상태를 생각할 수 있다.

 

여기에서 다음과 같은 UPDATE 명령을 실행하면 어떻게 될지 생각해보자.

 

UPDATE testsql SET no = no +1;

 

이 명령문에는 WHERE 구가 지정되어 있지 않으므로 갱신 대상은 테이블의 모든 행이 된다. SET 구에서는 no 열의 값을 갱신하는데,

갱신 후의 값은 본래 값(갱신 전의 값)에서 1을 더한 결과이다. 마치 프로그래밍 예제에 자주 등장하는 증가 연산과 같은 형식을 취한다.

 

SELECT * from testsql;

 

실행결과 1, 2, 1, 2 였던 no 값에 모두 +1이 되어서 2, 3, 2, 3이 되었다.

 

이처럼 갱신할 값을 열이 포함된 식으로도 표현할 수 있다. 이때 해당 열이 갱신 대상이 되는 열이라 해도 상관없다.

 

앞선 실행 예시의 계산식을 간단하게 설명하면 '현재의 no 값에 1을 더한 값으로 no 열을 갱신하라'는 의미이다. 

갱신은 행 단위로 처리되므로 '현재의 no값'은 그 행이 갱신되기 전의 값에 해당한다.

 

복수열 갱신

UPDATE 명령의 SET 구에서는 필요에 따라 콤마(,)로 구분하여 갱신할 열을 여러 개 지정할 수 있다.

 

명령문이 조금 길어지긴 하지만 딱히 어려운 부분은 없다. 

 

아래에 두 구문으로 나누어서 UPDATE 명령을 실행하는 것 보다는 하나로 묶어서 실행하는 편이 더 효율적이다.

 

UPDATE testsql SET a = 'xxx' WHERE no = 2;
UPDATE testsql SET b = '2014-01-01' WHERE no = 2;

2개의 구문으로 나눠서 2번 실행하는 것

 

아래는 위의 두 구문을 하나로 합쳐서 한번에 실행하는 것

UPDATE testsql SET a='xxx', b='2014-01-01' WHERE no = 2;

 

SET 구의 실행 순서

 

여러 개의 열을 한번에 갱신할 수 있어 편리하기는 하지만, 그 전에 SET 구는 어떤 순서로 갱신 처리를 하는지 알아둘 필요가 있다.

 

예를 들어 다음과 같은 2개의 UPDATE 명령이 있을 때 어떤 순서로 처리되는지 알아보자

 

UPDATE testsql SET no = no +1, a=no;
UPDATE testsql SET a=no, no= no+1;

 

이 두 UPDATE 명령은 콤마(,)로 구분된 개인 식의 순서가 서로 다르다.

그렇다면 이들 UPDATE 명령을 각각 실행한 결과가 같을지 한번 생각해보자 

 

사실은 데이터베이스 제품에 따라서 그 처리 방식이 달라진다. 다시 말해 데이터 베이스 제품에 따라 결가가 달라지는 것이다.

 

예를 들어 MySQL에서는 서로 다른 결괏값이 나오지만 Oracle에서는 어느 명령을 실행해도 결과는 같다.

 

그렇다면 MySQL의 경우 UPDATE 명령이 어떻게 처리되는지 살펴보자 

 

그러기 위해 현재 testsql 테이블을 SELECT 명령을 통해서 확인부터해보자

 

SELECT * from testsql;

그럼 이제 코드를 실행해보자

UPDATE testsql SET no = no +1, a=no;

SELECT * FROM testsql;

여기서 이전에 작성한 UPDATE 예제 명령을 실행하면 no열과 a열의 값이 서로 같아진다.

 

no열의 값에 1을 더하여 no열에 저장한 뒤, 그 값이 다시 a열에 대입되기 때문이다.

 

 

 

다음으로는 이전에 작성한 UPDATE 두 번째 예제 명령을 실행해보자 

 

 

그러면 no 열의 값을 a열에 대입한 후, no 열의 값을 +1 한다. 따라서 a 열의 값은 'no-1'한 값이 된다.

 

기존 값

 

UPDATE testsql SET a=no, no= no+1;

SELECT * FROM testsql;

 

변경된 값

 

먼저 no열의 값을 a에 대입한 후, no열의 값을 +1 한다. 

 

따라서 a의 값은 no열 -1 한 값이 된다.

 

 

 이번에는 Oracle에서 UPDATE 명령을 실행한 결과를 살펴보자

 

이때 테이블의 데이터는 MySQL에서 UPDATE 명령을 실행하기 전과 동일한 상태라고 가정한다.

 

 

데이터값은 초기상태로 만들어둔 상태에서 시작한다.

 

UPDATE testsql SET no=no+1, a= no;

SELECT * FROM testsql;

 

MySQL에서는 이렇게 입력하면

 

 이런 결과가 나와야하지만

 

Oracle같은 경우는 

 

 

이렇게 갱신된다. (이해하기 쉽게 편의상 Oracle 결괏값이 나오도록 직접 수정했다. Oracle이 아닌 MySQL로 작업중)

 

그럼 다른 명령문도 작성해보자

 

UPDATE testsql SET no = no +1, a=no;

SELECT * FROM testsql;

 

 두 UPDATE 명령을 연속으로 실행하다보면 값이 자꾸 증가해버려서 이해하기 어려울 수 있다.

 

하지만 no 열과 a 열의 관계만 알면 이해할 수 있을 것이다.

 

MySQL에서는

UPDATE testsql SET no = no +1, a=no;

해당 명령을 실행했을 경우에는

 

no열과 a열의 값은 같아진다.

 

하지만

UPDATE testsql SET a=no, no= no+1;

 

해당 코드를 실행했을 경우에는 a열과 no열의 값은 달라진다.

 

한편 Oracle에서는

UPDATE testsql SET no = no +1, a=no;

을 실행해도

UPDATE testsql SET a=no, no= no+1;

을 실행해도

 

a열의 값은 'no -1' 상태를 유지한다.

 

그 이유는 Oracle에서는 SET 구에서 기술한 식의 순서가 처리에 영향을 주지 않기 때문이다.

 

갱신식의 오른쪽에 위치한 no 열의 값이 항상 갱신 이전의 값을 반환하기 때문이다.

한편 MySQL에서는 SET 구에 기술된 순서로 갱신처리가 일어나므로 no열의 값은 갱신 이전의 값이 아닌 갱신 이후의 값을 반환한다.

 

따라서 MySQL의 경우, 갱신식 안에서 열을 참조할 때는 처리 순서를 고려할 필요가 있다.

 

NULL로 갱신하기

 

UPDATE 명령으로 셀 값을 NULL로 갱신할 수 있다. 따로 거창한 문법이 정해져 있는 것은 아니다.

 

UPDATE 테이블명 SET b = NULL과 같이 갱신할 값으로 NULL을 지정해주면 된다.

 

이처럼 NULL로 값을 갱신하는 것을 보통 'NULL 초기화' 라고 부르기도 한다.

UPDATE testsql SET a = NULL;

SELECT * FROM testsql;

이렇게 변경되었다.

 

다만 NOT NULL 제약이 설정되어 있는 열은 NULL이 허용되지 않는다.

 

UPDATE 명령에 있어서도 NOT NULL 제약은 유효하다.

 

no 열에는 NOT NULL 제약이 설정되어 있으므로 no열의 셀을 NULL로 갱신할 수 없다.

 

즉, UPDATE testsql SET no = NULL 을 실행하면 NOT NULL 제약에 위반되어 에러가 발생한다.

 

진짜인지 해보자

 

 

음 역시 에러가 발생하는군 좋아좋아!

 

SELECT * FROM testsql;

이렇게 변경되지 않고 기존 데이터를 유지한다.

 

이것으로 INSERT, DELETE , UPDATE 정리를 마치고 

 

6편에서 물리삭제와 논리삭제에 대해 알아보자

 

여기까지 읽으면서 공부한 사람이 있다면 수고하셨다고 말해드리고 싶다.



모든 포스팅에 대한 내용은 도서 SQL첫걸음에서 익히고 배운 것들에 대한 내용을 직접 타이핑 하였음을 밝힙니다.