SQL의 ANSI 표준 문법을 다루는 문서다.

질의문을 예시를 들 때

문법을 활용하여 소스코드를 작성하여 주시기 바랍니다.

예시:

begin
    dbms_output.put_line('hello world'); 
end;

Connector 사용법의 예시를 들 때에는 SQL을 호출하려는 앱 (응용 프로그램, 웹 어플리케이션 등)에 대한 프로그래밍 언어의 문법을 활용하여 소스코드를 작성하여 주시기 바랍니다.

데이터를 읽어오는 구문. 현업에서 사용하다 보면 가장 복잡하고 고려할 사항이 많은, 중요하고 어려운 구문이다.

SELECT field1, field2, ... FROM table {WHERE 조건};

서비스시 가장 빈번하게 사용되는 구문이다. 테이블에서 지정된 값을 가져오는 구문이며, 모든 값을 가져오기 위해 으레 field 부분을 생략하고 *로 표기하기도 한다.[1][2]

SELECT * FROM document WHERE author='무명씨';

작성자가 '무명씨'인 모든 문서의 모든 항목를 가져오는 구문.

SELECT title FROM document WHERE date > to_date('2011/09/01', 'yyyy/mm/dd/');

2011년 9월 1일 이후로 작성된 모든 문서의 제목을 가져오는 구문(to_date 함수는 문자열을 시간 데이터로 바꾸는 함수로 오라클 한정).

SELECT * FROM document WHERE no between 1 and 10;

1부터 10의 번호를 가지는 document 테이블 내의 모든 항목을 가져오는 구문.

SELECT * FROM document WHERE author in ('무명씨','홍길동');

작성자가 무명씨, 홍길동인 문서의 모든 항목을 가져오는 구문.

SELECT * FROM document WHERE author like '김*';

작성자가 김으로 시작되는 문서의 모든 항목을 가져오는 구문. '김%' 꼴로 표현하기도 한다.

실제 업무에서는 여러 쿼리를 조합하거나 테이블을 UNION, INTERSECTION, JOIN 등으로 가공하여 사용하는 경우가 많으므로 쿼리가 매우 복잡해진다.

SELECT document FROM (
  SELECT document, COUNT(document) cnt
  FROM rel_document_keyword
  WHERE keyword IN (
    SELECT id FROM keyword WHERE keyword IN ('key1', 'key2', 'key3', 'key4')
  ) GROUP BY document
) WHERE cnt=4;

역 인덱스 문서 검색 쿼리인데 특정 키워드가 포함된 문서를 고속으로 찾는 쿼리이다. key1, key2, key3, key4라는 키워드가 모두 포함된 문서를 찾는다.

데이터를 입력하는 구문. 형식은 다음과 같다.

INSERT INTO table(field1, field2, ...) VALUES (value1, value2, ...);

table에 field1=value1, field2=value2, ...와 같은 속성값을 가지는 항목을 새로 생성하여 삽입한다. 만약 테이블에 입력될 값이 문자라면 반드시 '' 으로 문자열 (String)임을 표시해 주어야 탈이 없다. 이는 UPDATE 구문에서도 마찬가지. table 이름 바로 뒤에 필드 목록을 생략해도 되지만 생략하려면 VALUES 뒤의 것의 갯수와 내용이 테이블 스키마와 정확히 일치해야 한다.

MySQL/MariaDB에서는 SET을 사용해서 후술할 UPDATE 구문과 비슷한 꼴로 만들 수 있다. 다만 쿼리 한 번에 하나씩만 넣을 수 있어서 일장일단이 있다.

UPDATE table SET field1=value1, field2=value2, {WHERE 조건};

데이터를 수정하는 구문으로 table의 field1에 value1, field2에 value2, ... 로 변경한다. WHERE 절이 없을 경우 테이블의 모든 항목이 바뀌므로, 대개 WHERE절과 함께 사용하여 범위를 한정해 준다.

UPDATE document SET title='무제' WHERE author='무명씨';

작성자가 '무명씨'인 모든 문서의 제목을 '무제'로 바꾸는 명령.

DELETE FROM table {WHERE 조건};

데이터를 삭제하는 구문. 말 그대로 데이터를 삭제하는 구문이다. 조건절이 없을 경우 테이블의 모든 데이터를 비우게 되므로[3], 대개 조건절과 함께 사용된다.

DROP TABLE table_name;

데이터베이스 내의 개체를 삭제해 버린다. 즉, 테이블 내의 데이터 뿐만 아니라 구조까지 모조리 날려버리는 궁극의 명령어이다. 실수로 사용중인 DB 내에서는 쓰지 않기를 바란다. 만약 이 명령어로 회사의 데이터를 날려먹었을 경우... 상사에게 깨지거나 회사에서 해고당하는 정도로 끝나면 다행으로, 고소장이 날아오는 경우도 많다고 한다. 참고로 DB의 DROP 명령은 트랜젝션의 보호를 받지 못해 Rollback(UNDO)이 안 된다.

DROP DATABASE database_name;

데이터베이스 내의 모든 테이블, 스키마, 관계(Relation)를 전부 삭제한다. 워낙에 치명적인 명령어라서 슈퍼유저 권한이 아니면 명령이 먹히지 않는다. 어쨌거나 이 명령이 서비스중인 DB에서 실행되면 해당 서버를 물리적으로 파괴한 것과 동등한 위력을 발휘한다.

(SELECT * FROM `table_a`) UNION (SELECT * FROM `table_b`);
SELECT a.*, b.* FROM `table_a` a LEFT OUTER JOIN `table_b` b;

둘 이상의 테이블을 묶어서 가져온다. UNION의 경우는 아래쪽에 붙이고, JOIN은 옆에 붙인다는 차이점이 있다. UNION의 경우 ALL, JOIN의 경우 FULL, INNER, OUTER, LEFT, RIGHT 등 보조적인 명령어를 사용하여 원하는 대로 조합할 수 있다.

한편 UNION은 테이블의 열 개수가 다르면 에러를 뱉어내고(...), JOIN은 열 이름이 같은 게 있을 경우 애로사항이 꽃핀다는 단점이 있다. UNION은 SQL 주입(SQL injection)이라는 해킹 기법으로 악용될 수 있다. 테이블 정보를 알고 있다면 이를 이용해서 DB상에 있는 회원 정보를 빼오거나, 그냥 DB 테이블 자체를 날려버릴 수 있으니 로그인 폼을 포함한 모든 곳에서 이를 제대로 필터링해야만 한다. 꼭 해라 두 번 해라. 클라이언트에서 한번 서버에서 한번.

이를 방지하기 위해 Prepared statement라는 것도 나와 있는데, 변수가 들어갈 부분은 ?[4]로 먼저 넣어두고, 그 다음에 쿼리문의 JIT 컴파일 과정을 거친 뒤 진짜 변수를 넣어 쿼리 명령이 아님을 명시하는 기법이다. Prepared statement의 경우 컴파일 과정에서 실행계획(어떻게 데이터베이스를 건드려야 빨리 결과가 나올지)이 세워진다. 루프를 돌면서 동일한 SQL을 계속 실행해야 한다면 루프 밖에서 Prepared Statement를 미리 정의한 후 사용하자. 루프가 돌아가는 속도가 현격히 빨라진다. 보안을 생각한다면 100% Prepared statement를 사용하는게 좋다. 사실 기업 환경에서는 보통 이렇게 한다.

이외에도 '프로시저(Procedure)'라는 것도 있는데, 다수의 SQL을 묶어서 함수화시켜놓은 것이다. Prepared statement와 조합해서 SQL 주입을 원천적으로 차단할 수 있다.

CREATE VIEW 뷰 이름 AS SELECT 문법;

SELECT 문으로 가져온 쿼리의 결과를 캐싱하는 데 쓰이는 문법. 보통 쿼리 자체는 복잡한데 쓰일 곳은 많을 때 사용한다.

DROP VIEW 뷰 이름;

만들었던 뷰를 삭제한다.