데이터베이스 프로그래밍 기초(3) : SQL 기초

SQL은 Structured Query Language의 약자로서 데이터베이스로부터 데이터를 조회하고 삭제하는 등의 작업을 수행할 때 사용하는 언어이다.

1. 주요 SQL 타입

먼저 SQL 타입에 대해 알아보자. 자바에 int, long, double, String과 같이 데이터 종류에 따라 알맞은 타입이 존재하는 것처럼 SQL도 저장할 데이터 종류에 따라 다양한 타입을 제공한다.

표준 SQL의 주요 타입

SQL 타입	설명
CHAR	확정 길이의 문자열을 저장한다. 표준의 경우 255글자까지만 저장할 수 있다.
VARCHAR	가변 길이의 문자열을 저장한다. 표준의 경우 255글자까지만 저장할 수 있다.
LONG VARCHAR	긴 가변 길이의 문자열을 저장한다.
NUMERIC	숫자를 저장한다.
DECIMAL	십진수를 저장한다.
INTEGER	정수를 저장한다.
TIMESTAMP	날짜와 시간을 저장한다.
TIME	시간을 저장한다.
DATE	날짜를 저장한다.
CLOB	대량의 문자열 데이터를 저장한다.
BLOB	대량의 이진 데이터를 저장한다.

표준 SQL 타입이 존재하긴 하지만, DBMS는 표준 SQL 타입 뿐만 아니라 DBMS 자체적으로 확장 타입을 추가 제공한다. 예를 들어, 오라클은 VARCHAR2라는 타입을 제공하는데 이 타입은 4,000바이트까지 저장할 수 있다. 사실 오라클에서 VARCHAR와 VARCHAR2는 동일하므로 VARCHAR를 사용해도 4,000바이트까지 저장할 수 있다. MySQL도 비슷하게 TimeStamp 타입 뿐만 아니라 DATETIME이라는 타입을 제공하고 있다. 이렇듯 DBMS가 제공하는 확장 타입은 표준 SQL에 정의된 타입이 아니기 때문에 DBMS마다 서로 호환되지 않고 표준 SQL 타입과 다른 제약을 갖는다. 심지어 오라클의 VARCHAR처럼 표준 SQL과 이름은 같지만 완전히 다른 경우도 존재한다. 따라서, DBMS가 제공하는 타입에 대해 기본적인 제약사항을 알고 타입을 사용해야 한다.

2. 테이블 생성 쿼리

데이터가 실제로 저장되는 공간은 테이블이므로, 데이터베이스 프로그래밍을 하려면 테이블을 생성해야 한다. 테이블을 사용할 때 사용하는 쿼리 형식은 다음과 같다.

CREATE table TABLENAME (
    COL_NAME1	COL_TYPE1(LEN1),
    COL_NAME2	COL_TYPE2(LEN2),
    ...
    COL_NAMEn	COL_TYPEn(LENn)
)

여기서 각 요소는 다음과 같다.

• TABLENAME : 테이블을 식별할 때 사용할 이름
• COL_NAME : 각 칼럼의 이름
• COL_TYPE : 각 칼럼에 저장될 값의 타입
• LEN : 저장될 값의 최대 길이

예를 들어, 위의 그림에 표시한 테이블의 이름을 MEMBER라고 하면, MEMBER 테이블을 생성할 때 다음과 같은 쿼리를 사용한다.

create table MEMBER (
    MEMBERID VARCHAR(10),
    PASSWORD VARCHAR(10),
    NAME VARCHAR(20),
    EMAIL VARCHAR(80)
)

위와 같이 테이블을 생성하면 주요키에 대한 정보가 표시되지 않는다. 또한, 필수 값에 대한 여부도 표시되어 있지 않다. 주요키 칼럼을 표시할 때에는 칼럼 타입 뒤에 'PRIMARY KEY'라는 문장을 추가하며, 필수 값에 대해서는 'NOT NULL'을 추가한다. 예를 들어, MEMBERID 칼럼이 주요키이고, PASSWORD, NAME 칼럼이 필수 요소라고 생각해보자. 이 경우 위 SQL 쿼리는 다음과 같이 변경된다.

create table MEMBER (
    MEMBERID VARCHAR(10) NOT NULL PRIMARY KEY,
    PASSWORD VARCHAR(20) NOT NULL,
    NAME VARCHAR(20) NOT NULL,
    EMAIL VARCHAR(80)
)

create table MEMBER (
    MEMBERID VARCHAR(10) NOT NULL PRIMARY KEY,
    PASSWORD VARCHAR(20) NOT NULL,
    NAME VARCHAR(20) NOT NULL,
    EMAIL VARCHAR(80)
) engine=InnoDB default character set=utf8;

다음과 같은 SQL 쿼리를 실행한다. 이 쿼리에서 engine=InnoDB는 MySQL에서 사용되는 구문이다. 이 구문은 테이블을 InnoDB라는 저장 엔진을 사용해서 생성한다는 것을 의미한다. InnoDB 엔진을 사용하는 이유는 트랜잭션 때문인데, InnoDB 엔진을 사용하지 않으면 트랜잭션이 올바르게 처리되지 않는다.

위 테이블 생성 쿼리에서 "default character set = utf8"은 MySQL에서만 사용되는 구문으로 테이블에서 사용될 캐릭터 셋이 UTF-8임을 뜻한다. 또한, 명령 프롬프트에서 mysql 프로그램을 실행할 때 --default-character-set 옵션의 값으로 utf8을 주었는데, 이는 mysql 클라이언트가 UTF-8 캐릭터 셋을 사용한다는 것을 의미한다. 이 옵션을 주지 않고 쿼리를 실행하는 경우 UTF-8을 사용하는 테이블에 대한 글자 처리가 올바르게 되지 않는다.

3. 데이터 삽입 쿼리

테이블에 데이터를 추가할 때는 INSERT 쿼리를 사용한다. INSERT 쿼리는테이블에 레코드를 삽입할 때 사용하며 기본 문법은 다음과 같다.

insert into [테이블이름] ([칼럼1], [칼럼2], ... [칼럼n])
       values ([값1], [값2], ... [값n])

[테이블이름]은 레코드를 삽입할 테이블 이름을 나타낸다. [칼럼]에는 값을 입력할 칼럼의 이름을 입력하고, [값]에는 해당 칼럼의 값을 입력한다. 예를 들어, 앞서 생성한 MEMBER 테이블에 값을 추가하고 싶다면 아래와 같은 쿼리를 실행하면 된다.

insert into MEMBER (MEMBERID, PASSWORD, NAME)
       values ('madvirus', '1234', '최범균');

위 쿼리는 MEMBER 테이블의 MEMBERID, PASSWORD, NAME 칼럼의 값이 각각 "madvirus", "1234", "최범균"인 레코드를 삽입한다. EMAIL 칼럼의 값은 지정하지 않았는데 이처럼 값을 지정하지 않으면 널(null) 값이 들어간다.

위 쿼리에서 큰따옴표가 아닌 작은따옴표를 사용하여 값을 표현하고 있는데, SQL에서는 작은따옴표를 사용하여 문자열을 표시한다.

널(null) 값이란? 널 값은 칼럼에 어떤 값도 들어가 있지 않다는 것을 의미한다. 예를 들어, INTEGER 타입의 칼럼이 널 값을 가진 경우, 이 칼럼은 값을 가지고 있지 않은 상태가 된다. 즉, 0이나 1 등의 기본값을 사용하지 않고 아예 값이 없는 상태가 된다. 테이블 생성 쿼리에서 NOT NULL로 지정한 칼럼은 널을 값으로 갖지 못한다.

칼럼 목록을 표시하지 않으면 전체 칼럼에 대해 값을 지정해야 한다. 예를 들어, MEMBER 테이블에 레코드를 추가하려면 다음과 같이 칼럼명을 입력하지 않고 모든 칼럼의 값을 순서대로 지정해도 된다.

insert into MEMBER values('eral3', '5678', '최범균', 'madvirus.net');

값에 작은따옴표가 있을 때는 어떻게?
SQL에서는 작은따옴표를 사용해서 문자열을 표현한다. 그래서, 값 자체에 작은따옴표가 있으면 문제가 발생한다. 예를 들어, 다음의 SQL 쿼리를 보자.
insert into MEMBER values('era13', '5678', '최'범균', 'madvirus2@gmail.com');
세 번째에 삽입하는 값은 "최'범균"인데, 실제로 쿼리는 '최'부분만 값으로 인식하고 나머지 부분은 값으로 인식하지 않기 때문에 SQL 에러가 발생하게 된다. 값에 작은따옴표를 포함되어 있으면, 작은따옴표를 연달아 두 번 사용해서 작은따옴표를 표현해야 에러가 발생하지 않는다. 예를 들어 "최'범균"이라는 문자열은 다음과 같이 표시한다.
'최''범균'

4. 데이터 조회 쿼리 - 조회 및 조건

테이블에 저장된 데이터를 조회할 때에는 SELECT 쿼리를 사용한다. SELECT 쿼리의 기본 문법은 다음과 같다.

select [칼럼1], [칼럼2], ..., [칼럼n] from [테이블이름]

여기서 [칼럼]은 읽어오고자 하는 칼럼의 목록이다. 다음은 앞서 생성했던 MEMBER 테이블에서 MEMBERID 칼럼과 NAME 칼럼을 읽어오는 쿼리를 실행한 결과 화면이다.

select MEMBERID, NAME from MEMBER;

만약 전체 칼럼을 모두 읽고 싶으면 전체 칼럼을 적는 대신 다음과 같이 별표('*')를 사용하면 된다.

select * from MEMBER;

SELECT 쿼리는 기본적으로 모든 레코드의 목록을 읽어온다. 그런데, 보통 특정 조건을 충족하는 레코드만 필요한 경우가 많기 때문에, 특정 조건을 충족하는 레코드만 조회하는 방법이 필요하다. 예를 들어, 회원 아이디가 'madvirus'인 회원 정보를 보고 싶으면, 전체 회원 정보를 읽어온 뒤 그중에서 아이디가 'madvirus'인 레코드를 찾기보다는, 회원 아이디가 'madvirus'인 레코드만 조회해서 보고 싶을 것이다. 이럴 때 WHERE 절을 사용한다. WHERE 절은 FROM 부분 뒤에 다음과 같이 사용된다.

select * from MEMBER where NAME = '최범균';

위 코드는 NAME 칼럼의 값이 '최범균'인 레코드의 목록만 읽어온다. AND와 OR를 사용하면 한 개 이상의 조건을 동시에 부여할 수도 있다.

select * from MEMBER where NAME = '최범균' and EMAIL = 'madvirus@madvirus.net';

자바의 조건 연산자와 마찬가지로 AND를 사용하면 양쪽 조건을 모두 충족해야 하고, OR를 사용하면 두 조건 중의 하나만 충족하면 된다. 위 쿼리는 NAME 칼럼 값이 '최범균'이고 EMAIL 칼럼 값이 'madvirus@madvirus.net'인 레코드만 검색한다.

같지 않음을 표현할 때에는 '<>' 연산자를 사용한다. 예를 들어, EMAIL 칼럼의 값이 빈 문자열('')이 아닌 레코드를 검색하고 싶다면 다음과 같은 쿼리를 사용하면 된다.

select * from MEMBER EMAIL <> '';

칼럼 값이 NULL이거나 NULL이 아닌 레코드를 구하고 싶다면 다음과 같이 NULL과 관련된 전용 쿼리를 사용할 수도 있다.

select * from MEMBER where EMAIL is NULL;
select * from MEMBER where EMAIL is not NULL;

'IS NULL' 연산자는 해당 칼럼이 NULL인지 여부를 판단하며, 'IS NOT NULL' 연산자는 해당 칼럼이 NULL이 아닌지 여부를 판단한다.

부등호 기호를 사용할 수도 있다. 예를 들어, 숫자 칼럼에서 값의 범위가 1부터 100 사이에 있는 레코드를 읽어오고 싶다고 해보자. 이 경우 다음과 같이 '<', '>', '<=', '>=' 등의 연산자를 사용하면 된다.

where SALARY >= 1000 and SALARY <= 2000

LIKE 조건을 사용해서 특정 문장을 포함하고 있는지 검사할 수 있다. 예를 들어, NAME 칼럼의 값이 '최'로 시작하는 레코드를 찾고 싶다면 다음과 같이 LIKE 조건을 사용할 수 있다.

where NAME like '최%';

여기서 '%'는 모든 것을 의미하는 것으로서 위 조건은 NAME 칼럼의 값이 '최*****'의 형태를 갖는지 여부를 판단할 때 사용한다. 만약 '%범%'과 같이 조건을 주면 앞뒤로 모든 문장이 오고 중간에 '범'이 포함된 값인지의 여부를 판단하게 된다.

LIKE 검색시 주의 사항
LIKE 검색은 편리하지만, 검색 속도가 매우 느리다. 예를 들어 NAME 칼럼 값에 '은'이 포함된 레코드를 검색하고자 할 경우 다음과 같은 쿼리를 사용한다.
select ... from member where name like '%은%'
레코드 수가 수천 개 이내라면 이 쿼리를 수행하는데 시간이 많이 걸리지 않지만 수백만이나 수천만 개라면 (포털의 회원수를 생각해보자) 검색 속도가 참을 수 없을 만큼 느려진다. 따라서, 빠른 검색 속도가 필요하다면 LIKE 검색 대신 별도의 검색 엔진을 사용해야 한다.

5. 데이터 쿼리 조회 - 정렬

게시판이나 회원 목록 등을 출력할 때 이름 순서나 아이디 오름차순 또는 번호 순으로 정렬하는 것이 보통이다. SQL 쿼리에서는 ORDER BY 절을 사용해서 데이터 정렬을 처리한다. ORDER BY 절은 WHERE 절 뒤에(WHERE 절이 없으면 FROM 절 뒤) 붙으며 다음과 같이 사용된다.

select .. from [테이블이름] where [조건절] order by [칼럼1] asc, [칼럼2] desc, ...;

[칼럼]은 정렬하고 싶은 칼럼의 이름이고 칼럼 이름 뒤에 'asc'나 'desc'를 붙인다. 'asc'를 붙이면 오름차순으로 정렬하고 'desc'를 붙이면 내림차순으로 정렬한다. 여러 개의 칼럼을 사용하면 지정한 칼럼 순서대로 정렬한다. 예를 들어, 다음 쿼리를 보자.

select * from MEMBER order by NAME asc, MEMBERID asc;

이 쿼리는 NAME 칼럼을 기준으로 오름차순으로 먼저 정렬하고, 그 상태에서 MEMBERID 칼럼에 대해서 오름차순으로 정렬하게 된다. 이때 주의할 점은 일단 NAME 칼럼으로 정렬한 상태가 되면, NAME 칼럼이 같은 값을 갖는 것들에 대해서만 MEMBERID 칼럼으로 정렬한다는 점이다.

6. 데이터 쿼리 조회 - 집합

집합 관련 쿼리에는 sum(), max(), min(), count() 등의 함수가 있다. 이들 함수는 각각 총합, 최대, 최소, 개수를 구할 때 사용된다. 예를 들어, 테이블의 SALARY 칼럼의 가장 큰 값과, 가장 작은 값 그리고 전체 레코드의 칼럼 합을 구할 때는 다음과 같은 쿼리를 사용한다.

select max(SALARY), min(SALARY), sum(SALARY) from ...;

전체 레코드 개수는 다음 쿼리를 사용해서 구할 수 있다.

select count(*) from MEMBER;

특정 조건에 해당하는 레코드에 대해서만 집합 관련 함수를 실행하고 싶다면 where 조건문을 붙이면 된다. 예를 들어, NAME 칼럼 값이 '최'로 시작하는 레코드의 개수를 구하고 싶다면 다음과 같은 쿼리를 실행하면 된다.

select count(*) from MEMBER where NAME like '최%';

7. 데이터 수정 쿼리

데이터를 수정할 때에는 UPDATE 쿼리를 사용한다. UPDATE 쿼리의 문법은 다음과 같다.

update [테이블이름] set [칼럼1]=[값1], [칼럼2]=[값2], ... where [조건절];

UPDATE 쿼리를 실행할 때 WHERE 절을 사용해서 조건을 명시하지 않으면 모든 레코드의 값을 변경한다. 예를 들어, 다음 쿼리는 모든 레코드의 NAME 값을 '최범균'으로 변경한다.

update MEMBER set NAME='최범균';

따라서, UPDATE 쿼리를 실행할 때에는 WHERE 절을 알맞게 사용해서 전체 레코드를 변경하지 않도록 주의해야 한다.

8. 데이터 삭제 쿼리

DELETE 쿼리를 실행해서 레코드 단위로 데이터를 삭제할 수 있으며 문법은 다음과 같다.

delete from [테이블이름] where [조건절]

UPDATE 쿼리와 마찬가지로 DELETE 쿼리로 WHERE 절에 조건을 입력하지 않으면 전체 레코드를 삭제한다. 즉, 다음 쿼리는 MEMBER 테이블에 있는 모든 레코드를 삭제한다.

delete from MEMBER

따라서 특정 조건의 레코드를 삭제할 때에는 반드시 WHERE 절을 사용해서 삭제 범위를 지정해야 한다. 예를 들어, 특정 회원 아이디에 대한 정보를 삭제하고 싶다면 다음과 같이 WHERE 절을 사용한다.

delete from MEMBER where MEMBERID = 'era13'

9. 조인

조인(Join)은 두 개 이상의 테이블에서 관련 있는 데이터를 함께 읽어올 때 사용된다. 조인의 기본 형식은 다음과 같다.

select A. 칼럼1, A. 칼럼2, B. 칼럼3, B. 칼럼4
from [테이블1] as A, [테이블2] as B
where A.[칼럼x] = B.[칼럼y]

위 쿼리에서 "[테이블1] as A"는 테이블1을 A로 표시한다는 것을 의미한다. 마찬가지로 B는 [테이블2]를 나타낸다. 위 쿼리는 테이블1의 칼럼x와 테이블2의 칼럼y의 값이 같은 레코드를 하나의 행(row)으로 읽어온다.

조인의 예를 들기 위해서 다음과 같은 테이블을 생성해보자.

create table MEMBER_ETC (
  MEMBERID	VARCHAR(10) NOT NULL PRIMARY KEY,
  BIRTHDAT	CHAR(8)
)

MEMBER_ETC 테이블에 여러 레코드를 삽입한 후 다음과 같이 쿼리를 실행해보자.

select * from MEMBER as A, MEMBER_ETC as B where A.MEMBERID = B.MEMBERID;

위 쿼리는 두 테이블의 MEMBERID 칼럼이 같은 레코드를 함께 읽어옫록 하고 있다. 결과를 보면 좌측의 네 칼럼-MEMBERID, PASSWORD, NAME, EMAIL-은 MEMBER 테이블의 칼럼이고 우측의 두 칼럼-MEMBERID, BIRTHDAY-은 MEMBER_ETC 테이블의 칼럼임을 알 수 있다. 이렇게 두 개 이상의 테이블로부터 같은 값을 갖는 칼럼을 사용하여 함께 레코드를 묶어서 읽어오는 것을 조인이라고 한다.

조인을 사용하면 관련된 테이블로부터 필요한 칼럼을 한꺼번에 읽어올 수 있기 때문에, 여러 테이블에 분산해서 저장된 정보를 읽어올 때 유용하게 사용할 수 있다.

참고

• 최범균의 JSP2.3 웹 프로그래밍