SQL

SQL(Structured Query Language)는 데이터베이스를 지원하는 언어로, 질의어지만 데이터의 정의/조작/제어기능을 모두 쓸 수 있다. SQL은 대체로 관계형 데이터베이스(Relational DBMS, RDBMS)를 다루기 위해 사용한다.

SQL의 핵심 문법은 거의 모든 DBMS가 일정한 편이나, 전체적으로 보면 데이터베이스를 다루는 서버인 DBMS마다 SQL이 미묘하게 다르다. 따라서 어떤 DBMS(예: MySQL 또는 MariaDB, PostgreSQL, 오라클 DB 등)을 쓰기로 했다면 그 DBMS만의 SQL 문법을 따로 익혀야 하며, 이런 이유로 DB 관리자(DB Administrator, DBA)를 회사에서 채용할 경우 반드시 채용 조건에 특정 DBMS을 다룰 수 있어야 한다고 조건을 건다.

또 모든 DBMS가 SQL을 사용하는 것이 아니며, SQL이 아닌 독자적인 방법으로 데이터베이스를 다루는 NoSQL DBMS란 것도 있다. 대표적으로 MongoDB나 Redis, ElasticSearch 등이 있으며, 이런 DB들은 대체로 JSON 및 그 유사품을 대체 스키마로 사용하는 편이다.

객체

데이터베이스(Database)

테이블의 모음이자 DB의 가장 큰 단위다.

스키마(schema)

데이터베이스의 구조와 제약조건에 관해 전반적인 명세를 기록한 것이다.

뷰(View)

테이블에서 유도된 가상의 테이블이다. 보통 생성/삭제 방법이 일반 데이터베이스 테이블과 같은 문법을 지니는 경우가 많다.

테이블(Table)

실제 데이터가 저장되는 공간이다.

인덱스(Index)

테이블 일부를 가져온 별도의 저장공간이다. 보통 일부 컬럼만 계속 접근하는 테이블에서 그 컬럼들만 따로 빼내어 성능 향상을 노릴 때 쓰인다. 그러나 남용하면 되려 DBMS 성능이 저하될 수 있기 때문에 꼭 필요한 부분에만 생성해야 한다.

도메인(Domain)

한 속성이 가질수있는 동일한 값들의 모음이다.

함수(Fuction)

프로그래밍의 함수와 비슷하다.

저장 프로시저(Stored Procedure)

저장 프로시저 또는 스토어드 프로시저라고 하는 DBMS의 기능은 SQL 작업들의 모음이다.

함수가 그렇듯 복잡한 쿼리를 따로 분리하는 데 쓸 수도 있지만, 몇몇 DBMS는 트리거라고 하여 프로시저를 특정 주기로 실행하게 하는 기능을 내장하는 경우도 있다. 실제로 몇몇 오래된 소규모 IT 회사는 서비스 내 이벤트 시작/종료를 트리거를 걸어둔 프로시저로 처리하는 경우가 있다.

DELIMITER $$ ... DELIMITER;로 SQL 파일 내 저장 프로시저 정의 구역을 감싸는 경우가 대부분이다.

데이터 정의어(DDL)

DDL은 스키마, 도메인, 테이블, 뷰, 인덱스등을 정의/변경/삭제시 쓰는 언어다.

CREATE

객체를 정의 하는 언어이다. 데이터베이스를 만들 땐

CREATE DATABASE (데이터베이스이름) (속성);

이렇게 하면 된다. MySQL이나 MariaDB는 속성 생략이 가능하지만, 오라클 DB의 경우는 DB 내 들어갈 데이터의 문자 인코딩, 데이터 파일 크기, 기본 스토리지 크기 속성을 필수로 넣어야 한다.

테이블을 만들 땐

CREATE TABLE (테이블이름)(
     (칼럼이름)(자료형)(속성),
     (칼럼이름2)(자료형)(속성),
     );

DB를 이용할 계정 생성의 경우 많은 DBMS들이 다음과 같은 형태로 만든다.

CREATE USER 'gildong'@'localhost' IDENTIFIED BY 'passwordstring'; -- 앞에 - 문자가 2개 붙으면 그 뒤부터 주석이다.

오라클 DBMS의 경우 혼자 문법이 튀는 귀찮은 DBMS답게 사용자 정의할 때 테이블스페이스라는 걸 추가로 지정해줘야 하는 불편이 있다.

ALTER

테이블의 정의를 변경한다. 테이블을 변경할 경우 컬럼의 자료형과 이름을 바꾸거나, 새 컬럼을 넣거나, 특정 컬럼을 삭제하는 경우가 많다. ALTER로 바꿀 수 있는 것들이 대단히 많기 때문에 이 문서에서 더 이상의 설명은 생략한다. 아래는 가장 많이 쓰이는 MySQL 혹은 MariaDB에서 기본값(Default)을 0으로 정했으며 최대 10자리 정수를 넣을 수 있는 'column4' 컬럼을 'Table1' 테이블에 추가하는 예시다.

ALTER TABLE Table1 ADD COLUMN column4 INT(10) DEFAULT 0;

DROP

객체를 삭제한다.

DROP TABLE DB2.Table1;
DROP DATABASE DB1;

많은 DBMS는 테이블을 삭제할 때 연관된 테이블이 있으면 같이 날리는 CASCADE 문법을 지원한다. 만약 MySQL에서 db1 데이터베이스를 사용 중(USE db1)일 때 Table2에서 Table1의 기본 키를 외래키로 가지고 있다고 할 경우 다음 구문은 DB1과 DB2 테이블을 같이 삭제한다. 이 경우 만약 CASCADE 없이 Table1만 삭제하려고 시도할 경우 Table2가 비어있지 않는 한 반드시 오류가 발생한다.

DROP TABLE Table1 CASCADE;

데이터조작어(DML)

DML은 사용자가 프로그램/언어를 통해 저장된 데이터를 실질적으로 처리하고, 인터페이스를 제공한다.

SELECT

테이블에서 데이터를 뽑아내거나 검색하는 언어이다. 일반 검색시엔

SELECT[DISTINCT] 칼럼명 
FROM 테이블명 [WHERE 조건식]  

조건식 자리에는 LIKE(~가 포함되었는가?), IN(~이거나), BETWEEN(~부터 ~까지가) 있다. 테이블의 일부를 가져와는 새 테이블을 만드는 식은

SELECT 칼럼명 INTO 테이블1 FROM 테이블2[WHERE 조건문];

그룹화 하는 식은

SELECT 컬럼명 FROM 테이블명 GROUP BY 그룹화할 컬럼명;

정렬식은

SELECT * FROM 테이블명 ORDER BY  칼럼이름 (ASC/DESC)

ASC는 오름차순으로, 기본값이라 생략 할 수 있다. desc는 내림차 순이다.

INSERT

테이블에 새 내용을 삽입하는 언어이다.

다음 형태가 MySQL/MariaDB에서 사용하는 형태며 다른 DBMS도 대체로 같은 형식을 가진다.

주의할 점으로 행을 삽입할 때 모든 속성을 다 나열할 필요는 없고, NULLABLE하거나 DEFAULT 값이 정해진 일부 속성은 뺄 수 있다. 가령, idx/column1/column2/column3 속성을 가진 테이블에서 column2가 NULL일 수 있는 경우 다음 INSERT 구문이 작동한다.

INSERT INTO table1(idx, column1, column3) VALUES  (1, 3, 'Sam');

DELETE

테이블에 기존에 있던 내용의 일부를 삭제하는 명령이다.

MySQL/MariaDB 기준으로 table1.idx의 값이 10보다 작은 모든 행을 지우려면 다음과 같이 쓴다.

DELETE FROM table1 WHERE idx < 10;

UPDATE

테이블의 일부 튜플의 내용을 변경하는 언어이다.

MySQL 및 MariaDB의 UPDATE 구문 예시, idx 컬럼이 3보다 작거나 같은 모든 행에 대해 table1 테이블에서 column1과 column2의 값을 각각 4, '홍길동'으로 바꾼다.

UPDATE table1 SET column1=4, column2='홍길동' WHERE idx <= 3;

데이터제어어(DCL)

COMMIT

트랜잭션의 결과를 디스크로 저장하고, 완료를 통보한다.

ROLLBACK

트랜잭션의 비정상적 종료시 이전 데이터를 복구한다.

GRANT

사용자에게 권한을 부여한다.

예를 들어, MySQL/MariaDB에서 'db1이라는 데이터베이스'의 '모든 테이블'에 대해 '모든 권한'을 '임의의 클라이언트에서 사용 가능'한 'Kim' 계정에게 준다면,

GRANT ALL PRIVILEGES ON db1.* TO 'Kim'@'%';

이라고 쓴다. 모든 테이블이나 모든 데이터베이스를 가리키려면 '*' 문자를 사용한다. 여기서 계정 이름 뒤의 '%'는 임의의 클라이언트에서 접근 가능하다는 뜻이며, DB 서버 내에서만 접근하게 하려면 'localhost'를 대신 사용하고, 제3자 클라이언트 컴퓨터를 지정해 거기에서만 접속하게 하려면 그 컴퓨터의 IP를 적거나 서버에 등록된 클라이언트 컴퓨터 이름을 쓰면 된다.

REVOKE

관리자의 사용 권한을 취소한다.

관계 대수

SELECT 구문의 경우 두 개 이상의 테이블에 있는 속성들을 합쳐서 새로운 테이블을 구성해 데이터를 꺼낼 수 있다.