SQL 기초 문법 정리

SQL(Structured Query Language)이란?

SQL은 관계형 데이터베이스의 조작과 관리에 사용되는 데이터베이스 질의용 언어입니다. 1970년대 IBM 회사에 의하여 개발되었고, IBM의 관계형 DBMS에서 처음 사용됐습니다. 이후 1986년 미국표준협회(ANSI)가 SQL 표준을 채택함에 따라 현재는 IBM의 DB2, 마이크로소프트의 Access와 SQL Server를 비롯하여 Oracle, Sybase, Informix 등에서 구조화 질의어로 널리 사용되고 있습니다. SQL은 원하는 데이터가 무엇(What)인지만 기술하고, 검색 방법(How)은 기술하지 않으므로 비절차적 언어입니다. 이러한 특성 때문에 SQL은 상대적으로 배우기 쉽고 자연어에 가까운 구문을 사용해 질의를 표현하기 때문에 관계형 대수나 관계형 해석보다 표현력이 우수합니다.

SQL은 아래와 같이 3가지 기능을 제공합니다.

1. 데이터 정의 기능 : 데이터 정의어(DDL)를 이용하여 릴레이션의 생성 및 제거, 속성의 추가 및 삭제, 뷰의 생성 및 제거, 인덱스의 생성 및 제거 등의 작업을 수행합니다. 또한 릴레이션 생성 시 여러 가지 무결성 제약 조건들을 기술합니다.

2. 데이터 조작 기능 : 데이터 조작어(DML)를 이용하여 데이터의 검색, 삽입, 삭제, 수정 등의 연산을 수행합니다.

3. 데이터 제어 기능 : 데이터 제어어(DCL)를 이용하여 트랜잭션의 시작, 철회, 완료 등을 명시하고, 릴레이션에 대해 권한을 부여하거나 취소합니다.

SQL 설명

이 직관적인 언어는 어느 데이터베이스에서나 기본으로 제공하기 때문에 배워두면 여러 곳에서 쓸데가 많습니다. 다만, NoSQL 계열에서는 SQL 문을 사용하지 않습니다. 그러나 DBMS 벤더에 따라 사용하는 SQL에는 다소 차이가 있습니다. 그래서 ANSI SQL이라고 하는 표준 SQL 구문이 있으나 DBMS 시장을 독식하고 있는 오라클이 잘 지키지 않기 때문에 현실은 힘든 상황이라고 볼 수 있습니다. 최근 인기를 얻고 있는 PostgreSQL은 표준 SQL을 잘 지키고 있습니다. 일반적으로 사용하는 데이터베이스는 RDB(관계형 데이터베이스)이며, RDB에서 데이터 처리는 데이터 모음인 테이블을 기준으로 이루어지기 때문에 SQL 역시 테이블을 염두에 두고 읽으면 이해하기 쉽습니다.

SQL은 대소문자를 구분하지 않으므로 ‘SELECT’와 ‘select’는 같은 것으로 취급됩니다. 물론 따옴표로 인용된 ‘값’은 대소문자를 구분합니다. 문자열을 대소문자를 구분하지 않게 하려면 명령 맨 뒤에 ‘COLLATE NOCASE’를 붙입니다. 또한 대부분의 DBMS는 세미콜론(;)을 입력하지 않으면 명령어를 계속 입력하고 있다고 간주합니다. GUI를 사용할 경우 GUI프론트엔드에서 알아서 세미콜론을 붙여 주기 때문에 잘 모르고 넘어갈 수 있는데 터미널로 작업할 경우에는 이것 때문에 셸이 먹통이 되는 경우가 있습니다.

그리고 쿼리를 연습할 때 주의할 점이 한 가지 있는데 SQL 구문은 탐욕스럽습니다(Greedy). SQL은 가능한 넓은 범위에 걸쳐 작업하려고 합니다. 따라서 ‘WHERE’절이나 ‘LIMIT’ 구문을 생략하면 ‘SELECT’의 경우 끝도 없이 출력하는 레코드 열을 보게 될 것이고 UPDATE와 DELETE의 경우 사보타주에 가까운 광역 변조(파괴)가 일어납니다. 또한 대부분의 DBMS에서는 트랜잭션 BEGIN을 먼저 걸고 작업하지 않는 한 작업을 취소(UNDO)할 수 없습니다. 다시 말하여 ‘BEGIN’을 입력하지 않고 ‘DELETE from table;’을 입력한 경우 ‘ROLLBACK’ 명령을 입력해도 소용이 없습니다. 가장 치명적인 오타로 ‘WHERE’절 입력 직전에 따옴표를 닫는다는 게 바로 옆의 세미콜론을 잘못 쳐서 오타를 낸 경우가 그 예입니다. 이 경우 앞의 명령어를 모든 레코드에 대해 수행해서 테이블을 파괴해 버린 뒤에 뒤쪽 명령어를 ‘문법 오류’로 출력합니다.

따라서 ‘UPDATE’, ‘DELETE’를 실행해보기 전에는 반드시 ‘BEGIN’을 입력하여 트랜잭션을 시작한 뒤에 해당 쿼리를 실행하고, 실행 결과가 의도한 대로인지를 SELECT로 검증한 뒤에 의도한 결과대로 쿼리가 수행되었음을 확인하고 나서, ‘COMMIT’을 입력하여 트랜잭션을 적용하는 습관을 들이는 게 좋습니다. 만약 뭔가가 잘못되었을 경우 즉시 ‘ROLLBACK’ 명령을 입력하면 ‘BEGIN’을 입력한 뒤에 행한 모든 작업이 취소됩니다. GUI의 경우에는 여러 쿼리를 입력했을 경우 마지막 ‘SELECT’의 결과를 보여주는 기능이 탑재되어 있다는 전제 아래 처음부터 ‘BEGIN; ~ ROLLBACK;’ 블록을 미리 입력해 두고 작업을 하는 게 안전합니다.

일단 SQL을 입력할 때에는 절대 함부로 엔터키를 눌러선 안 됩니다. 지적확인을 하는 게 도움이 될 수도 있습니다. 특히 ‘WHERE’절 앞뒤를 매우 꼼꼼하게 살펴야 하고 ‘WHERE’절이 없는 쿼리는 무조건 틀렸다고 간주하는 것이 좋습니다. SQL은 기본값이 ‘rm -rf /’라고 생각하는 게 편합니다. 아예 날쿼리를 함부로 넣지 말라고 프로시저(Procedure)라는 쿼리 템플릿 기능을 제공합니다. 참고로 SQL과 PL/SQL은 다른 것인데, PL/SQL이 SQL을 포함하고 있기는 하지만 SQL을 자유자재로 쓸 정도의 실력이 되지 않았는데 PL/SQL을 공부하면 안 됩니다. 금융계에서는 PL/SQL을 널리 쓰니까 은행에 개발자로 취업하려면 꼭 배워야 하지만 배울 때 배우더라도 SQL을 자신 있게 다룰 수 있게 된 다음의 일입니다. 진도를 빨리 빼고 싶은 욕심에 SQL도 모르는 채로 PL/SQL에 도전했다간 기존에 공부한 데이터베이스의 이론체계에 혼동을 일으켜 오히려 진도가 뒤로 후퇴합니다.

SQL의 역사와 설계

SQL은 IBM에서 1970년대 초에 도널드 D. 챔벌린과 레이먼드 F. 보이스가 처음 개발하였습니다. 초기에는 SEQUEL(Structured English Query Language – 구조 영어 질의어)라는 이름으로 시작하였으며, IBM의 준 관계형 데이터베이스 관리 시스템 시스템 R에 저장된 데이터를 조작하고 수신하기 위해 고안되었습니다. SEQUEL은 나중에 SQL로 바뀌었는데, 그 까닭은 SEQUEL이 영국의 호커 시들리 항공사의 상표였기 때문입니다. SQL은 관계형 모델과 그것의 튜플 해석이라는 이론적 기초로부터 파생되었습니다. 해당 모델에서 테이블은 튜플의 집합이지만, SQL에서는 테이블과 쿼리 결과는 행(row)의 목록입니다. 같은 행은 여러 번 발생할 수 있고 행의 순서는 쿼리에 의해 나타납니다. 그 예로는 ‘LIMIT’ 절 등이 있습니다.

명령어 문법 정리

데이터 정의 언어

CREATE [데이터베이스 개체(테이블, 인덱스, 제약조건 등)의 정의]

DROP(데이터베이스 개체 삭제)

ALTER(데이터베이스 개체 정의 변경)

데이터 정의 언어는 테이블과 인덱스 구조를 관리합니다. DDL의 가장 기본적인 요소는 CREATE, ALTER, RENAME, DROP과 TRUNCATE 구문입니다.

데이터 조작 언어

INSERT INTO(행 데이터 또는 테이블 데이터의 삽입)

UPDATE ~ SET(표 업데이트)

DELETE FROM(테이블에서 특정 행 삭제)

SELECT ~ FROM ~ WHERE(테이블 데이터의 검색 결과 집합의 취득

데이터 제어 언어

GRANT(특정 데이터베이스 사용자에게 특정 작업을 수행 권한 부여)

REVOKE(특정 데이터베이스 이용자로부터 이미 준 권한을 박탈)

SET TRANSACTION {트랜잭션 모드 설정[동시 트랜잭션 격리 수준(ISOLATION MODE) 등]}

BEGIN(트랜잭션 시작)

COMMIT(트랜잭션의 실행)

ROLLBACK(트랜잭션 취소)

SAVEPOINT(무작위로 롤백 지점을 설정)

LOCK(TABLE 등의 자원을 차지)

커서 정의 및 사용

‘커서’는 SELECT 문장 등에 의한 데이터베이스 검색에 의한 검색 실행 결과를 한 줄씩 검색하고, 처리하기 위해 데이터베이스 서버 측의 결과 집합과 행 획득 위치를 나타내는 개념을 말합니다. 커서의 정의와 그 작업은 주로 응용 프로그램 등의 절차적 언어에서의 SQL 실행 시 사용합니다.

DECLARE CURSOR(커서 정의)

OPEN(커서 열기)

FETCH(커서 포인터가 가리키는 위치의 행 데이터를 검색하고 포인터를 일행 분 진행)

UPDATE(커서 포인터가 가리키는 위치의 행 데이터 업데이트)

DELETE(커서 포인터가 가리키는 위치의 행 데이터 삭제)

CLOSE(커서 닫기)

연산자 설명

연산자	설명
=	같음
<> 또는 !=	같지 않음
>	보다 큼
<	보다 작음
>=	보다 크거나 같음
<=	보다 작거나 같음
BETWEEN	일정 범위 사이
LIKE	패턴 검색
IN	컬럼의 여러 가능한 값들을 지정

조건 표현

SQL은 case/when/then/else/end 표현을 가지고 있으며, 이것은 SQL-92에서 도입되었습니다. 일반적인 형식에서, 이것은 SQL 표준에서 “searched case”라고 불리며, 다른 프로그램 언어에서 ‘else if’와 같은 역할을 수행합니다.

‘WHEN’ 조건은 소스에서 등장하는 순서에서 시험됩니다. 아무런 ‘ELSE’ 표현식이 지정되지 않으면, ‘ELSE NULL’을 기본값으로 하게 됩니다. switch statement를 미러링 하는 약어 구문도 존재합니다. 이것은 SQL 표준에서 “simple case”라고 불립니다.

마지막 값은 ‘default’입니다. 아무것도 지정되지 않으면, ‘NULL’이 기본값이 됩니다. 그러나 표준 “simple case”와는 달리 오라클의 ‘DECODE’는 2개의 NULL을 서로 동일한 것으로 간주합니다.

SQL 자료형

SQL 테이블에서 각 컬럼은 컬럼이 포함하는 자료형(Data Type)을 선언합니다. ANSI SQL은 아래와 같은 데이터형을 포함하고 있습니다.

문자열

CHARACTER(n) 또는 CHAR(n) : 고정폭 n-문자열, (필요한 만큼 공백으로 채워집니다.)

CHARACTER VARYING(n) 또는 VARCHAR(n) : 가변폭 문자열 (n 문자의 최대 크기를 가진)

NATIONAL CHARACTER(n) 또는 NCHAR(n) : 국제 문자셋을 지원하는 고정폭 문자열

NATIONAL CHARACTER VARYING(n) 또는 NVARCHAR(n) : 가변폭 NCHAR 문자열

비트열

BIT(n) : n 비트의 배열

BIT VARYING(n) : n 비트까지의 배열

수

INTEGER와 SMALLINT

FLOAT, REAL과 DOUBLE PRECISION

NUMERIC(precision, scale) 또는 DECIMAL(precision, scale)

예를 들어, 숫자 123.45는 5라는 precision(정밀도, 자리값)과 2라는 scale(소수점 이하 자릿수)을 포함하고 있습니다. precision은 특정 진법(이진법 또는 십진법)에서 중요한 10 자릿수를 결정하는 양의 정수 값입니다. scale은 음이 아닌 정수이다. 0의 scale은 그 수가 정수임을 지시하는 숫자입니다. S 자릿수를 가진 10진법에서, 정확한 숫자 값은 10S로 나눈 중요한 10진법 정수 값입니다. SQL은 숫자, 날짜를 반올림해주는 TRUNC (인포믹스, DB2, PostgreSQL, 오라클 그리고 MySQL에서) 또는 ROUND (인포믹스, SQLite, Sybase, Oracle, PostgreSQL and Microsoft SQL Server) 함수를 제공합니다.

날짜와 시간

DATE : 날짜 값 (예, 2011-05-03)

TIME : 시간 값 (예, 15:51:36). 시간 값은 보통 tick (100 nanoseconds)입니다.

TIME WITH TIME ZONE or TIMETZ : TIME과 같지만, 해당 지역의 시간대 정보를 포함하고 있습니다.

TIMESTAMP : 이것은 DATE와 TIME이 하나의 변수로 결합된 것입니다. (예, 2011-05-03 15:51:36).

TIMESTAMP WITH TIME ZONE or TIMESTAMPTZ : TIMESTAMP와 동일하지만, 해당 지역의 시간대에 대한 상세 정보를 포함하고 있습니다.

SQL은 날짜 / 시간 변수를 생성하는 여러 개의 함수를 date / time 열 (TO_DATE, TO_TIME, TO_TIMESTAMP)로부터 제공합니다. 또한 그러한 각각의 변수 항목 (예를 들면, 초)을 통해 추출할 수도 있습니다. 현재 데이터베이스 서버 시스템의 날짜 / 시간은 NOW와 같은 함수를 통해 호출할 수 있습니다.

동적 SQL

: 동적 SQL은 일반적으로 SQL문을 RDBMS에 보낼 때마다 데이터베이스 엔진에서 실행 가능한 내부 중간 코드로 번역하는 작업을 미리 수행하여 변환된 SQL 코드를 재사용하여, SQL 분석 오버헤드를 줄이고, SQL 문을 소스 코드로 고정하지 않고 데이터베이스에 액세스 할 때마다 구문을 다시 할 경우 유용합니다. 데이터 조작 언어(DML)도 물론 수행할 수 있지만, 데이터 정의 언어 (DDL)와 같이 데이터베이스 제품의 기능 업데이트에 의해 새로운 명령이 추가되는 것은 전처리 해당 작업이 부담이 되기 때문에, 대부분의 데이터베이스 제품에서는 DDL 문은 동적 SQL에서 실행하는 것이 일반적입니다.

PREPARE (문자열로 준 SQL 문을 해석, 번역)

EXECUTE (PREPARE로 번역한 SQL 문을 실행)

임베디드 SQL

: 임베디드 SQL(또는 내장 SQL)은 원래 커서가 포함된 SQL에서 호스트 언어에서 결과 집합을 얻기 위해 더 편리한 방법으로 고안된 것입니다. 데이터베이스와 통신하기 위한 자원 할당 확보와 개방한 줄에 호스트 언어의 반복으로 가져오기 위한 명령(FETCH) 등이 있습니다.

ALLOCATE (DEALLOCATE) DESCRIPTOR (데이터베이스 및 호스트 언어 간 통신 영역의 확보와 개방)

WHENEVER (오류 발생 시의 동작을 정의)

SQLSTATE (SQL 문 실행 후 상태가 저장되는 영역)

3 값 논리

: SQL에서 사용되는 논리 값은 컴퓨터 세계에서 가장 널리 이용되는 ‘2 값 논리(TRUE, FALSE)’ 대신 ‘3 값 논리(TRUE, FALSE, UNKNOWN)’이 있습니다.

SQL 언어 요소

아래는 많은 SQL문에 공통되는 SQL 및 언어 요소의 기본 구문을 정의합니다.

[문자] :

SQL 언어에서 키워드와 조작의 기본 기호는 IBM 관계형 데이터베이스 제품이 지원하는 모든 문자 세트의 일부인 단일 바이트 문자입니다.

[토큰] :

언어의 기본 구문 단위를 토큰이라고 부릅니다. 토큰은 공백, 제어 문자 및 스트링 상수 내의 문자나 분리 ID를 제외하고 하나 이상의 문자로 구성되어 있습니다. (이러한 용어는 나중에 정의됩니다.)

[ID] :

ID는 이름을 형성하는 데 사용되는 토큰입니다. SQL문의 ID는 SQL ID, 시스템 ID 또는 호스트 ID입니다.

[이름 지정 규칙] :

이름 형성 규칙은 이름과 이름 지정 옵션이 지정하는 오브젝트의 유형에 따라 다릅니다(*SQL 또는 *SYS). 이름 지정 옵션은 CRTSQLxxx, RUNSQLSTM 및 STRSQL 명령에 지정됩니다. SET OPTION 명령문은 삽입된 SQL을 포함하는 프로그램의 소스 내에 이름 지정 옵션을 지정하는 데 사용할 수 있습니다. 구문 다이어그램은 다른 유형의 이름에 대해 다른 용어를 사용합니다.

[별명] :

별명은 테이블의 대체 이름, 테이블의 파티션, 보기 또는 데이터베이스 파일의 멤버로 간주될 수 있습니다. SQL문의 테이블 또는 보기는 해당 이름 또는 별명에 의해 참조될 수 있습니다. 별명은 동일 또는 원격 관계형 데이터베이스 내에서 테이블, 테이블의 파티션, 보기 또는 데이터베이스 파일 멤버를 가리킬 수 있습니다.

[권한 부여 ID 및 권한 부여 이름] :

권한 부여 ID는 데이터베이스 관리 프로그램과 애플리케이션 프로세스 또는 프로그램 준비 프로세스 간의 연결을 설정할 때 데이터베이스 관리 프로그램에서 확보하는 스트링으로서, 권한 세트를 지정합니다. 또한 사용자나 사용자 그룹을 지정할 수도 있지만, 이 특성은 데이터베이스 관리 프로그램에서 제어하지는 않습니다.

[프로시저 해결] :

프로시저 호출 시 DB2는 동일한 이름의 프로시저 중 실행 가능한 프로시저를 결정해야 합니다.

[데이터 유형] :

SQL에서 조작할 수 있는 가장 작은 데이터 단위는 값입니다.

[데이터 유형 승격] :

데이터 유형은 관련 데이터 유형 그룹으로 분류할 수 있습니다. 이런 그룹 내에서 임의의 데이터 유형이 다른 데이터 유형 앞에 고려되는 우선순위의 순서가 존재합니다. 이 우선순위를 사용하여 데이터베이스 관리자는 임의의 데이터 유형을 우선순위 순서에서 나중에 표시되는 다른 데이터 유형으로 승격할 수 있습니다. 예를 들어, 데이터 유형 CHAR이 VARCHAR로 승격될 수 있습니다. INTEGER는 DOUBLE PRECISION으로 승격할 수 있지만 CLOB는 VARCHAR로 승격할 수 없습니다.

[데이터 유형 사이의 캐스팅] :

제공된 데이터 유형을 가지는 값이 다른 데이터 유형 또는 다른 길이, 정밀도나 스케일을 가지는 동일한 데이터 유형으로 캐스트(변경)되어야 하는 많은 경우가 있습니다.

[지정 및 비교] :

SQL의 기본 조작은 지정 및 비교입니다. 지정 조작은 CALL, INSERT, UPDATE, FETCH, SELECT, SET 변수 및 VALUES INTO 명령문의 실행 중에 수행됩니다. 비교 조작은 MAX, MIN, DISTINCT, GROUP BY 및 ORDER BY 등과 같은 술부 및 기타 언어 요소를 포함하는 명령문의 실행 중에 수행됩니다.

[결과 데이터 유형의 규칙] :

결과의 데이터 유형은 연산에서 피연산자에 적용되는 규칙으로 판별됩니다. 이 섹션에서는 해당 규칙에 대해 설명합니다.

[스트링 결합 연산을 위한 변환 규칙] :

스트링을 결합하는 연산은 연결, UNION, UNION ALL, EXCEPT 및 INTERSECT입니다. (이들 규칙은 MAX, MIN, VALUE, COALESCE, IFNULL 및 CONCAT 스칼라 함수와 CASE 표현식에도 적용됩니다.) 각각의 경우에 결과의 CCSID는 바인드 시 판별되고 연산의 실행에는 스트링을 CCSID로 식별되는 코드화 문자 세트로 변환하는 것이 포함됩니다.

[상수] :

상수(리터럴이라고도 불림)는 값을 지정합니다. 상수는 스트링 상수 또는 숫자 상수로 분류됩니다. 스트링 상수는 추가로 문자 또는 그래픽으로 분류됩니다. 숫자 상수는 추가로 정수, 부동 소수점 또는 십진으로 분류됩니다.

[특수 레지스터] :

특수 레지스터는 데이터베이스 관리자에 의해 애플리케이션 프로세스에 대해 정의되는 스토리지 영역이고, SQL문에 참조될 수 있는 정보를 저장하기 위해 사용됩니다. 특수 레지스터에 대한 참조는 현재 서버가 제공한 값에 대한 참조입니다. 값이 스트링인 경우, 해당 CCSID는 현재 서버의 디폴트 CCSID입니다.

[열 이름] :

열 이름의 의미는 콘텍스트에 따라 다릅니다.

[변수] :

SQL문의 변수는 SQL문이 실행될 때 변경할 수 있는 값을 지정합니다.

[함수] :

함수는 괄호 안에 들어 있는 하나 이상의 피연산자가 뒤에 붙는 함수 이름이 표시하는 조작입니다. 함수는 입력 값 세트와 결과 값 세트 간의 관계를 나타냅니다. 함수에 대한 입력 값을 인수라고 합니다. 예를 들어, 함수에 날짜 및 시간 데이터 유형을 갖는 두 개의 입력 인수를 전달하면 시간소인 데이터 유형으로 된 값이 결과로 리턴됩니다.

[표현식] :

표현식은 값을 지정합니다.

[술부] :

술부는 주어진 값, 행 또는 그룹에 대해 참, 거짓 또는 알 수 없음 조건을 지정합니다.

[검색 조건] :

검색 조건은 해당 행 또는 그룹에 대한 true, false 또는 알 수 없음 조건을 지정합니다.