| 프로그래밍 언어 문법 | ||
| {{{#!wiki style="margin: -16px -11px; word-break: keep-all" | <colbgcolor=#0095c7><colcolor=#fff,#000> 언어 문법 | C( 포인터 · 구조체) · C++( 자료형 · 클래스 · 이름공간 · 상수 표현식 · 특성) · C# · Java · Python · Kotlin · MATLAB · SQL · PHP · JavaScript · Haskell |
| 마크업 문법 | HTML · CSS | |
| 개념과 용어 | 함수 · 인라인 함수 · 고차 함수 · 람다식 · 리터럴 · size_t · 상속 · 예외 · 조건문 · 참조에 의한 호출 · eval | |
| 기타 | == · === · deprecated · NaN · null · undefined · 모나드 · 배커스-나우르 표기법 | |
| 프로그래밍 언어 예제 · 목록 · 분류 | }}} | |
1. 개요
SQL의 ANSI 표준 문법을 다루는 문서다. 응용을 하려고 하다보면 복잡하기는 하지만 기본적인 명령어들은 생각보다 직관적인 편이다.2. 편집 지침
질의문을 예시를 들 때
\#!syntax sql (소스코드)
|
예시:
#!syntax sql
begin
dbms_output.put_line('hello world');
end;
Connector 사용법의 예시를 들 때에는 SQL을 호출하려는 앱 (응용 프로그램, 웹 어플리케이션 등)에 대한 프로그래밍 언어의 문법을 활용하여 소스코드를 작성하여 주시기 바랍니다.
3. SQL 내부 문법
3.1. 자료형
3.2. 질의
3.2.1. Select
데이터를 읽어오는 구문. 현업에서 사용하다 보면 가장 복잡하고 고려할 사항이 많은, 중요하고 어려운 구문이다.#!syntax sql
SELECT field1, field2, ... FROM table {WHERE 조건};
서비스시 가장 빈번하게 사용되는 구문이다. 테이블에서 지정된 값을 가져오는 구문이며, 모든 값을 가져오기 위해 으레 field 부분을 생략하고 *로 표기하기도 한다.[1] SUM이나 COUNT 같은 집계 함수가 들어가기도 한다.
#!syntax sql
SELECT field1 AS f1 FROM table {WHERE 조건};
필드명을 바꿔서 가져와야 할 경우 AS 구문을 사용한다.
#!syntax sql
SELECT * FROM document WHERE author='무명씨';
작성자가 '무명씨'인 모든 문서의 모든 항목를 가져오는 구문.
#!syntax sql
SELECT title FROM document WHERE date >= to_date('2011/09/01', 'yyyy/mm/dd/');
2011년 9월 1일 이후[2]로 작성된 모든 문서의 제목을 가져오는 구문(to_date 함수는 문자열을 시간 데이터로 바꾸는 함수로 오라클 한정).
날짜와 날짜 사이를 검색할 때는 Between A and B 형식으로도 가능하다.
#!syntax sql
SELECT title FROM document WHERE date BETWEEN ('2011/09/01', 'yyyy/mm/dd/') AND ('2023/02/26', 'yyyy/mm/dd/');
2011년 9월 1일부터 2023년 2월 26까지 작성된 모든 문서의 제목을 가져오는 구문이다.
#!syntax sql
SELECT * FROM document WHERE no BETWEEN 1 AND 10;
1 이상 10 이하의 번호를 가지는 document 테이블 내의 모든 항목을 가져오는 구문.
#!syntax sql
SELECT * FROM document WHERE author in ('무명씨','홍길동');
작성자가 무명씨, 홍길동인 문서의 모든 항목을 가져오는 구문.
#!syntax sql
SELECT * FROM document WHERE author like '김*';
작성자가 김으로 시작되는 문서의 모든 항목을 가져오는 구문. '김%' 꼴로 표현하기도 한다.
실제 업무에서는 여러 쿼리를 조합하거나 테이블을 UNION, INTERSECTION, JOIN 등으로 가공하여 사용하는 경우가 많으므로 쿼리가 매우 복잡해진다.
#!syntax sql
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라는 키워드가 모두 포함된 문서를 찾는다.
다만, 위와 같이 인라인 뷰를 사용하는 경우는 주의해야 할 점이 있다. 인라인 뷰를 통해 접근할 데이터의 수를 어떻게 해서든 최소화 해야한다는 것이다. 인라인 뷰 안의 SQL 문에서는 WHERE절에서 인덱스가 잘 적용되겠지만 인라인 뷰 바깥에서는 인라인 뷰 내부에서 조회된 데이터가 마치 엑셀 쉬트처럼 인덱스고 뭐고 없는 데이터 덩어리가 되어버리기 때문이다. 이것때문에 인라인 뷰와 비슷하게 생성되는 스냅샷의 경우 별도로 스냅샷에 인덱스를 생성하는 경우가 많다.
3.2.2. Insert
데이터를 입력하는 구문. 형식은 다음과 같다.#!syntax sql
INSERT INTO table(field1, field2, ...) VALUES (value1, value2, ...);
table에 field1=value1, field2=value2, ...와 같은 속성값을 가지는 항목을 새로 생성하여 삽입한다. 만약 테이블에 입력될 값이 문자라면 반드시 '' 으로 문자열 (String)임을 표시해 주어야 탈이 없다. 이는 UPDATE 구문에서도 마찬가지. table 이름 바로 뒤에 필드 목록을 생략해도 되지만 생략하려면 VALUES 뒤의 것의 갯수와 내용이 테이블 스키마와 정확히 일치해야 한다.
MySQL/ MariaDB에서는 SET을 사용해서 후술할 UPDATE 구문과 비슷한 꼴로 만들 수 있다. 다만 쿼리 한 번에 하나씩만 넣을 수 있어서 일장일단이 있다.
3.2.3. Update
#!syntax sql
UPDATE table SET field1=value1, field2=value2, {WHERE 조건};
데이터를 수정하는 구문으로 table의 field1에 value1, field2에 value2, ... 로 변경한다. WHERE 절이 없을 경우 테이블의 모든 항목이 바뀌므로, 대개 WHERE절과 함께 사용하여 범위를 한정해 준다.
#!syntax sql
UPDATE document SET title='무제' WHERE author='무명씨';
작성자가 '무명씨'인 모든 문서의 제목을 '무제'로 바꾸는 명령.
위 예제로는 보여주기 어렵겠지만, SELECT 문으로 특정 데이터를 쿼리해서 UPDATE 문으로 수정하는 경우가 현업에서는 의외로 자주 발생한다. 이런 경우 상관없는 데이터가 영향을 받는지, 그리고 업데이터 작업이 신속히 끝날 수 있는지 철저히 검증하고 작업을 해야 한다. 엄한 데이터에 영향을 주던가, DB 락 걸리게 하기 딱이다.
3.2.4. Delete
#!syntax sql
DELETE FROM table {WHERE 조건};
데이터를 삭제하는 구문. 말 그대로 데이터를 삭제하는 구문이다. 조건절이 없을 경우 테이블의 모든 데이터를 비우게 되므로[3], 대개 조건절과 함께 사용된다.
3.2.5. Drop
#!syntax sql
DROP TABLE table_name;
데이터베이스 내의 개체를 삭제해 버린다. 즉, 테이블 내의 데이터 뿐만 아니라 구조까지 모조리 날려버리는 궁극의 명령어이다. 실수로 사용중인 DB 내에서는 쓰지 않기를 바란다. 만약 이 명령어로 회사의 데이터를 날려먹었을 경우... 상사에게 깨지거나 회사에서 해고당하는 정도로 끝나면 다행으로, 고소장이 날아오는 경우도 많다고 한다. 참고로 DB의 DROP 명령은 트랜젝션의 보호를 받지 못해 Rollback(UNDO)이 안 된다. 오라클의 플래시백 기능 같은걸 세팅해 놓았다면 천만다행이기는 한데...
#!syntax sql
DROP DATABASE database_name;
데이터베이스 내의 모든 테이블, 스키마, 관계(Relation)를 전부 삭제한다. 워낙에 치명적인 명령어라서 슈퍼유저 권한이 아니면 명령이 먹히지 않는다. 어쨌거나 이 명령이 서비스중인 DB에서 실행되면 해당 서버를 물리적으로 파괴한 것과 동등한 위력을 발휘한다.
자매품으로 TRUNCATE 구문도 있다. 이놈은 트랜잭션 무시하고 데이터만 날려버린다. 데이터 건수가 많은 테이블의 데이터를 모두 삭제[4]해야 할 경우 사용하는데, 트랜잭션 지원이 없는 관계로 서버 리소스를 덜 먹기 때문이다.
#!syntax sql
TRUNCATE TABLE table_name;
참고로 상단의 4개(SELECT, INSERT, UPDATE, DELETE)는 데이터 조작어(DML)이지만 DROP과 TRUNCATE는 데이터 정의어(DDL)이다. 학습 용도로는 이해할 필요가 없지만 DB회사 취직 시 가끔 물어보는 경우가 있으니 참고할 것. 만약 외우기가 힘들다면 데이터 제어어(DCL)에 해당하는 GRANT(권한 부여), REVOKE(권한 회수)만 외우고 CREATE만 DDL에 속하며 나머지 6자리 글자의 명령어는 DML이라고 외우자.
3.3. 연산
#!syntax sql
(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라는 것도 나와 있는데, 변수가 들어갈 부분은 ?[5]로 먼저 넣어두고, 그 다음에 쿼리문의 JIT 컴파일 과정을 거친 뒤 진짜 변수를 넣어 쿼리 명령이 아님을 명시하는 기법이다. Prepared statement의 경우 컴파일 과정에서 실행계획(어떻게 데이터베이스를 건드려야 빨리 결과가 나올지)이 세워진다. 루프를 돌면서 동일한 SQL을 계속 실행해야 한다면 루프 밖에서 Prepared Statement를 미리 정의한 후 사용하자. 루프가 돌아가는 속도가 현격히 빨라진다. 보안을 생각한다면 100% Prepared statement를 사용하는게 좋다. 사실 기업 환경에서는 보통 이렇게 한다.
이외에도 '프로시저(Procedure)'라는 것도 있는데, 다수의 SQL을 묶어서 함수화시켜놓은 것이다. Prepared statement와 조합해서 SQL 주입을 원천적으로 차단할 수 있다.
3.4. 뷰
#!syntax sql
CREATE VIEW 뷰 이름 AS SELECT 문법;
SELECT 문으로 가져온 쿼리의 결과를 캐싱하는 데 쓰이는 문법. 보통 쿼리 자체는 복잡한데 쓰일 곳은 많을 때 사용한다.
#!syntax sql
DROP VIEW 뷰 이름;
만들었던 뷰를 삭제한다.
3.5. 권한·관리
#!syntax sql
OPTIMIZE TABLE 테이블명;
MyISAM 등 일부 DBMS 엔진에서 데이터를 수정·삭제할 때 생기는 찌꺼기(Overhead)를 제거해준다. 다만 찌꺼기가 많이 생기는 MEMORY 엔진은 이 명령어로 찌꺼기 제거가 불가능하다(...).
#!syntax sql
REPAIR TABLE 테이블명;
서버 과부하 등으로 테이블에 문제가 발생했을 경우, 일차적으로 사용할 수 있는 복구 수단이다. 이것으로 복구가 되지 않는다면 백업본 복원 등 다른 수단을 써야 한다.
4. Connector 사용법
각 프로그래밍 언어마다 특정 DBMS에 특화된 함수 모둠이 있기는 하지만, 여기서는 범용적으로 사용할 수 있는 코드만을 작성한다.4.1. Java
#!syntax java
String host = "DB호스트";
String db = "DB명";
String dbid = "DB계정명";
String dbpw = "DB비밀번호";
Connection connector = null;
Statement statement = null;
ResultSet result = null;
try {
Class.forName("DB드라이버명");
connector = DriverManager.getConnection("jdbc:DBMS명://"+host+"/"+db, dbid, dbpw);
statement = connector.createStatement();
result = statement.executeQuery("SELECT 'Hello World!';");
while (result.next()) {
System.out.println(result.getString(1));
}
}
catch(ClassNotFoundException e) {
// DB 연결 오류
}
catch(SQLException e) {
// SQL 쿼리 오류
}
4.2. PHP
#!syntax php
$host = "DB호스트";
$db = "DB명";
$dbid = "DB계정명";
$dbpw = "DB비밀번호";
$connector = new PDO("DBMS명:host=$host;dbname=$db", $dbid, $dbpw);
echo $connector->query("SELECT 'Hello World!';")->fetchColumn();
[1]
이 경우 프로그램 레벨에서 필드값의 순서를 알고 있어야 하므로 그다지 권장되는 방법은 아니다.
[2]
이후는 기준 날짜를 포함한다
[3]
데이터를 통째로 비우는 명령으로 TRUNCATE가 있는데, 조건절 없이 사용되는 DELETE 구문과 TRUNCATE 구문은 실행방식이 약간 다르다. 이를테면, TRUNCATE 구문은 테이블을 마치 갓 생성한 것처럼 최적화를 시켜 주지만(자동증가값 초기화, 테이블 오버헤드 초기화 등), DELETE 구문은 그렇지 못하다.
[4]
사실은 테이블을 생성 직후의 상태로 되돌려버린다.
[5]
PDO의 경우 변수명을 지정할 수 있다.