SQL 데이터 조작 언어(DML) - 1

데이터베이스에 명령을 부여하기 위한 언어를 SQL이라고 합니다.

이 명령에는 데이터를 조작하는 언어(DML)과, 데이터를 정의하는 언어(DDL) 가 있는데 DML에는 크게 데이터베이스의 테이블에서 데이터를 조회하는 SELECT 명령, 데이터를 추가하는 INSERT 명령, 데이터를 수정하는 UPDATE 명령, 데이터를 삭제하는 DELETE 명령 으로 구분됩니다.

SQL 쿼리문에 가장 기본이 될 수 있는 SELECT 구문의 목적은 바로 데이터를 다양한 조건으로 조회하는 것 입니다. 데이터베이스의 데이터는 행(row)과 열(column)로 형태로 저장이 되어 있는데 이는 엑셀의 표 모양과 같습니다.

예제로 사용할 데이터베이스는 비디오 대여를 위해 회원 MEMBER, 대여기록 RENT, 비디오 VIDEO, 장르 TYPE 의 네개의 테이블을 가지고 있는 데이터베이스를 기준으로 설명하도록 하겠습니다.

각 테이블 모양은 아래와 같습니다.

MEMBER(회원) 테이블

PK: 회원번호
NAME : 회원이름
GRADE: 회원등급
BIRTH: 생년
TEL: 전화번호
ADDR: 주소
AMOUNT : 예치금

RENT(대여) 테이블

PK: 대여번호
MEMBER_PK : 회원번호 (MEMBER 테이블 FK)
VIDEO_PK: 비디오번호 (VIDEO 테이블 FK)
RENTDATE: 대여일자
RETDATE: 반납일자
REMARK: 비고

VIDEO(비디오) 테이블

PK: 비디오번호
NAME: 비디오이름
NUM: 비디오 갯수
COMPANY: 영화사이름
DIRECTOR: 감독이름
MAJOR: 배우이름
TYPE_NAME: 장르종류 (TYPE_TYPE 테이블 FK)

TYPE(장르) 테이블

NAME: 장르이름
PRICE: 대여가격
PERIOD: 대여기한(주일)

SELECT

이런 데이터를 조회할 때 SELECT 뒤에 자료 중 추출할 열의 이름을 기술하고 만약 모든 내용을 가져오고 싶으면 * 문자를 사용할 수 있습니다. 그 뒤에는 FROM 대상 테이블 이름을 지정하는 FROM 구문을 기술합니다.

예를 들어 MEMBER 테이블에 담겨 있는 정보를 모두 조회 할려면 다음과 같이 쿼리를 실행 시킬 수 있습니다.

SELECT *
FROM MEMBER

만약 이름과 가격 항목만 가져오고 싶다면 다음과 같이 가져오고자하는 컬럼의 이름을 쉼표로 구분하여 작성 할 수 있습니다.

SELECT NAME, ADDR
FROM MEMBER

위에처럼 특정 컬럼을 가져올때 NAME, NAME 처럼 중복하여 조회를 해도 오류는 발생하지 않습니다. 단지 같은 결과를 여러번 받는 것 뿐이겠지요.

SELECT TOP 3 *
FROM MEMBER

조회 대상 자료에서 처음 몇개의 행만 가져오고 싶을 때는 SELECT TOP 가져오고 싶은 행 갯수를 기술하여 조회 할 수 있습니다.

DISTINCT

테이블안에 같은 값을 가진 데이터행이 있을때 선택 컬럼 목록 앞에 DISTINCT 를 지정하면 중복되는 행을 제거 한 후 결과를 보여줍니다.

SELECT DISTINCT *
FROM MEMBER

ALIAS

선택 컬럼 이름을 별칭을 이용하여 바꿀 수 있습니다. 보통 AS 를 붙여서 이름을 바꾸지만 AS는 생략이 가능합니다. 컬럼에 연산자를 이용하여 계산을 하게된다면 기본적으로 컬럼 이름이 지정되지 않게 되므로 이때는 보통 별칭을 지정하게 됩니다.

SELECT NAME AS 이름 , ADDR AS 주소 , AMOUNT 예치금
FROM MEMBER

컬럼처럼 테이블에도 별칭을 지정할 수 있는데 별칭을 지정하고 나면 원래 이름을 사용하게 되면 오류가 발생하게되므로 WHERE 조건이나 컬럼 목록에서 테이블 이름을 기술할때에는 별칭을 이용하여야 합니다. 또한, 별칭을 적용하게 되면 같은 테이블이라도 다른 테이블로 인식하여 조회 할 수 있습니다.

WHERE

WHERE 절에는 행을 선택하는 조건식을 기술하고 JOIN시에는 조선식으로 테이블끼리의 관계를 기술할때 사용합니다.
조건에 적합한 행만을 결과로 얻고 싶을 때 WHERE 절을 사용하여 검색조건을 지정할 수 있습니다. 회원 테이블에서 생일이 1981년 생인 회원들만 조회하고 싶다면 아래와 같은 쿼리를 사용할 수 있습니다.

SELECT *
FROM MEMBER WHERE BIRTH = 1981

JOIN

FROM 절에는 하나의 테이블뿐 만아니라 여러 테이블을 JOIN 하여 다른 테이블의 컬럼을 같이 조회 할 수 있습니다. 일반적인 데이터베이스 시스템은 보통 여러 복수개의 테이블로 구성되어 있기 때문에 테이블 끼리는 설계자가 정한 임의의 키값으로 관계를 나타낼 수 있습니다.

JOIN에는 크게 내부조인, 외부조인으로 분류 할 수 있는데 내부조인일 경우 아래처럼 INNER JOIN 이라고 기술하거나 INNER 를 생략한 후 JOIN 만 기술 할 수 있습니다. 이후 조건식은 ON 절 뒤에 지정하여 복수 조건을 기술 할 경우 AND 를 사용하여 연결 할 수 있습니다.

SELECT MEMBER.PK, NAME,RENTDATE
FROM MEMBER
INNER JOIN RENT ON RENT.MEMBER_PK = MEMBER.PK

위의 쿼리는 회원의 PK 컬럼(일련번호) 값과 대여기록의 MEMBER_PK 컬럼 값이 같은 데이터를 조회함으로써 대여기록의 대여일자를 같이 조회하고 있습니다. 단, PK처럼 같은 이름을 가진 컬럼이 RENT 테이블에도 있고 MEMBER 테이블에도 있고 PK 컬럼을 선택열에서 조회하고자하면 테이블이름.컬럼명 처럼 명시적으로 기술해주어야 합니다.

일반적인 JOIN에서는 허느 한쪽 테이블에 조건을 만족시키는 행이 존재하지 않을 경우, 전체 결과에서 제외됩니다. 이럴 때, 외부조인인 LEFT JOIN 혹은 RIGHT JOIN으로 왼쪽, 혹은 오른쪽 테이블을 기준으로 조건을 만족시키지 않더라도 한쪽 테이블의 전체 행을 조회 할 수 있습니다. MEMBER LEFT JOIN RENT 라고 하면 왼쪽에 있는 MEMBER 테이블의 모든 행이 남게 되며 반대로 MEMBER RIGHT JOIN RENT 라고 하면 오른쪽에 있는 RENT 테이블의 모든 행이 남습니다. 외부조인을 강조하기 위해 다음과 같이 LEFT 나 RIGHT 구문 뒤에 OUTER를 기술해도 상관없습니다.

SELECT NAME,RENTDATE
FROM MEMBER
RIGHT OUTER JOIN RENT ON RENT.MEMBER_PK = MEMBER.PK

GROUP BY

보통 집계라고 하면 단순히 합을 계산하는 것으로 생각되지만 투표 집계 처럼 그룹별로 합을 계산해야 되는 경우가 있습니다.

SELECT BIRTH AS 생년, COUNT(*) AS 회원수
FROM MEMBER
GROUP BY BIRTH

위에 쿼리처럼 MEMBER 테이블의 회원의 생년을 그룹화하여 생년별로 COUNT 함수를 사용해 몇명이 존재하는지 조회 할 수 있습니다. 만일 FROM 절에 JOIN 구문이 있고 그룹화 할려는 컬럼명이 JOIN 테이블에도 존재 한다면 GROUP BY 뒤에도 JOIN에서와 같이 테이블이름.컬럼명 으로 그룹화 하여야 합니다.

집계한 결과의 값을 조건으로 지정하고 싶을 때는 WHERE 절을 쓰는게 아니라 HAVING 구문을 실행합니다. 아래는 위의 쿼리 결과에서 각 생년에 회원이 한명이상 있을 경우를 조회하는 쿼리입니다.

SELECT BIRTH AS 생년, COUNT(*) AS 회원수
FROM MEMBER
GROUP BY BIRTH
HAVING COUNT(*) > 1

ORDER BY

SQL에서 정렬하는 방법은 매우 단순합니다. 쿼리문 마지막에 ORDER BY 절을 추가하기만 하면 됩니다. ORDER BY 에는 정렬하고자 하는 컬럼 혹은 식을 기술하는데 열지정은 ORDER BY A,B,C 와 같이 여러개를 한꺼번에 지정할 수 있고 ASC 또는 DESC 로 오름차순, 내림차순으로 정렬할 수 있습니다.

SELECT *
FROM MEMBER
ORDER BY AMOUNT DESC

위 쿼리는 예치금이 많은 순서대로 회원목록을 조회하고 있습니다. ORDER BY 가 없는 SELECT 쿼리 결과는 행의 순서가 일정하지 않기 때문에 항상 같은 순서로 결과를 얻고 싶을 때는 ORDER BY를 지정하도록 합니다.

Sub Query

SELECT 로 얻은 결과 값을 SELECT 식으로 기술해서 복잡한 조회를 실행하는 것을 서브쿼리 혹은 부속질의라고 합니다.

SELECT *
FROM MEMBER
WHERE PK = (SELECT TOP 1 MEMBER_PK FROM RENT ORDER BY RENTDATE DESC )

위에 쿼리는 대여 테이블에서 가장 최근에 비디오를 대여한 회원정보를 찾기 위하여 WHERE절에 대여 테이블 정보에서 대여일자 기준 가장 최근의 대여정보로 회원 테이블의 PK 값으로 조회하는 쿼리입니다.

어떤 테이블과 관계를 가지고 있는 다른 테이블에 대응하는 행의 유무에 따라 조건을 붙이고 싶은 경우, EXISTS 또는 NOT EXISTS 라는 서브 쿼리를 사용합니다.

SELECT * FROM VIDEO WHERE EXISTS ( SELECT * FROM RENT WHERE VIDEO.PK = RENT.VIDEO_PK)

이처럼 현재 대여중인 비디오들의 비디오 정보를 대여 테이블의 VIDEO_PK 값으로 조회 할 수 있습니다. 반대로 대여 중이지 않은 비디오들을 조회 할려면 EXISTS 대신 NOT EXISTS를 사용 할 수 있습니다.

SELECT * FROM VIDEO WHERE NOT EXISTS ( SELECT * FROM RENT WHERE VIDEO.PK = RENT.VIDEO_PK)

참고로 EXISTS에서는 각 행의 실행되는 SELECT의 결과를 돌려주면 되기 때문에, 테이블의 모든 행을 볼 필요가 없지만, NOT EXISTS에서는 모든 행을 살펴보고 조건에 맞는 행이 없는 것을 확인해야 되기 때문에 느린 쿼리가 될 수 도 있습니다.

UNION

SELECT NAME FROM VIDEO UNION SELECT NAME FROM MEMBER

UNION을 사용하면 복수의 SELECT 문을 연결시켜 마치 하나의 명령문으로 처리할 수 있습니다. 위의 결과는 NAME이라는 컬럼에 영화이름과 회원이름을 모두 합쳐서 나타내고 있습니다. UNION을 여러번 기술하여 3개 혹은 그 이상의 SELECT문을 연결하는 것도 가능하며 결과에서 중복되는 행은 하나의 행으로 정리됩니다. 만약 중복하지 않고 모든 결과를 보고 싶다면 UNION ALL 을 사용하면 됩니다.

다만 UNION에서는 각 SELECT 절의 조회하는 열 갯수가 일치하여야 합니다. 예를 들어서 아래와 같은 쿼리는 동일한 개수의 식이 있어야 한다는 오류를 돌려줍니다.

SELECT NAME,COMPANY FROM VIDEO UNION SELECT NAME,ADDR FROM MEMBER

이럴 경우 MEMBER 테이블 SELECT 절 NAME을 NAME,NULL로 기술하여 열 개수를 맞추어 오류를 없애는 방법도 있습니다.

일반적으로 ORDER BY는 맨 마지막 SELECT 절 뒤에 기술해주어야 하며 ORDER BY에 지정할 컬럼 이름은 맨 앞 SELECT 문 컬럼명을 기준으로 하여야 합니다.

SQL 데이터 조작 언어(DML) – 1