[데이터베이스 시리즈] SQL 문법 정리

프로그래밍/데이터베이스

[데이터베이스 시리즈] SQL 문법 정리

강민재02 2024. 10. 1. 17:42

728x90

SQL이란

Structured Query Language의 줄임말으로, 구조화된 질의어라는 뜻이다. 1970년대 초, IBM 사 San Jose의 연구실에서 System R이라는 DBMS 개발 프로젝트에서 최초로 구현되었으며, 그것이 다듬어져 현대의 SQL이 되었다. SQL은 ANSI와 ISO로 부터 표준화되어 관리되고 있으며, 제품마다 차이는 있으나 SQL을 사용하는 모든 DBMS가 이 표준을 따른다.

SQL 자료형

1) 문자 자료형

자료형	설명
char(n)	길이가 n인 고정 할당된 문자열
varchar(n)	최대 길이가 n인 가변 할당된 문자열

2) 숫자 자료형

자료형	설명
int, integer	정수
numeric(p, d)	d번째 자리에서 소숫점이 찍히는 p자리의 실수
float(n)	실수
real double-precision	double; 더 큰 범위의 실수

3) 날짜 자료형

자료형	설명
date	YYYY-MM-DD 형식의 날짜
time	00:00:00 형식의 시간
datetime	YYYY-MM-DD 00:00:00 (8byte) 형식의 날짜와 시간
timestamp	YYYY-MM-DD 00:00:00 (4byte) 형식의 날짜와 시간

4) 큰 숫자 자료형

자료형	설명
blob	binary large object
clob	character large object

DDL (Data Definition Language) 문법 정리

1-1) CREATE

테이블을 생성한다.

CREATE TABLE table_name (
    column_name Datatype [NOT NULL | NULL] [DEFAULT value]
    [PRIMARY KEY(column_list)]
    [UNIQUE (column_list)]
    [FOREIGN KEY(column_list) REFERENCES table_name(column_list)]
    [ON DELETE option] [ON UPDATE option ]
    [CONSTRAINT name] [CHECK(condition)]
);

ON DELETE Options

NO ACTION : 튜플이 삭제되는 것을 막는다.
CASCADE : 연관된 튜플을 모두 삭제한다.
SET NULL : 연관된 외래키 값들을 모두 NULL로 만든다.
DEFAULT : 연관된 외래키 값들을 사전 정의된 default 값으로 변경한다.

ON UPDATE Options

NO ACTION : 튜플이 수정되는 것을 막는다.
CASCADE : 연관된 튜플을 모두 변경한다.
SET NULL : 연관된 외래키 값들을 모두 NULL로 만든다.
DEFAULT : 연관된 외래키 값들을 사전 정의된 default 값으로 변경한다.

1-2) ALTER

테이블의 속성을 변경한다.

ALTER TABLE table_name
    ADD column_name datatype [NOT NULL | NULL] [DEFAULT value];
    MODIFY column_name datatype [NOT NULL | NULL] [DEFAULT value];
    MODIFY column_name datatype [FIRST | AFTER column_name];
    DROP column_name ;
    RENAME COLUMN old_column_name to new_column_name;
    ADD constraint_name;
    DROP constraint_name;

1-3) RENAME

테이블을 재명명한다.

방법 1.
RENAME TABLE old_table TO new_table;

방법 2.
ALTER TABLE old_table RENAME new_table;

방법 3 (여러개의 테이블을 한번에 변경).
RENAME TABLE old_table1 TO new_table1,
             old_table2 TO new_table2,
             old_table3 TO new_table3

1-4) DROP

테이블을 삭제한다.

DROP TABLE table_name;

2) VIEW

물리적으로 저장되지 않는 가상의 테이블이다.

뷰 생성

뷰를 생성하는 2가지 방법이며, 아래 방법은 ALTER 연산을 대체하여 많이 사용된다.

CREATE VIEW table_name [column_list] AS (
    subquery;
);

-- 뷰를 drop 한 후 다시 create 한다.
CREATE VIEW OR REPLACE view_name [column_list] AS (
    subquery;
);

뷰 삭제

DROP VIEW view_name;

뷰 수정

뷰는 참조 테이블이기 때문에 INSERT, UPDATE, DELETE 연산 수행 시 원본 테이블에도 영향을 미친다. 또한, 뷰는 단일 테이블을 참조하며 계산된 열을 가지지 않고 제약 조건을 준수하는 뷰일 경우에만 위와 같은 연산이 가능하다.

쿼리문 작성 방식은 테이블을 수정할 때와 동일하다.

DML (Data Manipulation Language) 문법 정리

1-1) SELECT

SELECT [ALL | DISTINCT] column_name [as alias_name]
FROM table_name
[JOIN other_table_name ON condition]
[WHERE conditions]
[GROUP BY column_name]
[HAVING conditions]
[ORDER BY column_name [ASC|DESC]]
[LIMIT n];

예약어	설명
SELECT	조회할 속성을 선택한다.
ALL	중복된 값을 포함하여 출력한다.
DISTINCT	중복된 값을 제거하여 출력한다.
*	모든 속성을 선택한다.
AS	속성명이나 테이블명을 임시로 바꾼다.
FROM	데이터를 불러올 테이블을 선택한다.
WHERE	조건을 설정한다.
GROUP BY	주어진 column을 기준으로 묶는다.
HAVING	묶어진 column에 대해 조건을 설정한다.
ORDER BY	주어진 column들을 기준으로 정렬한다.
ASC / DESC	오름차순 / 내림차순 지정
LIMIT	위에서부터 n번째까지만 행을 출력한다.

WHERE문에 사용되는 술어

예약어	설명
=, <>, <, <=, >, >=	같다, 다르다, 미만, 이하, 초과, 이상
AND, OR, NOT	그리고, 또는, 아닌
LIKE	'%문자열%' 과 일치하는
BETWEEN a AND b	a와 b 사이의 값
IN, NOT IN	집합에 포함된, 포함되지 않는
IS NULL, IS NOT NULL	NULL인, NULL이 아닌

LIKE에 사용되는 와일드카드 문

예약어	설명
%	길이가 0 이상인 임의의 문자열
_	길이가 1인 임의의 한 문자

1-2) INSERT

새로운 튜플을 삽입한다.

INSERT INTO table_name([column list]) VALUES (value list);
-- 행 전체를 value list에 작성한 경우 column list 생략 가능

INSERT INTO table_name([column list])
Subquery;

1-3) UPDATE

튜플을 갱신한다.

UPDATE table_name
SET column=value1 [, column2=value2,...]
[WHERE condition];

1-4) DELETE

튜플을 삭제한다.

DELETE FROM table_name
[WHERE condition];

2) 서브 쿼리

위치에 따른 분류

종류	위치	설명
Nested Subquery	WHERE 절	WHERE 절에서 조건문으로 사용되는 서브쿼리를 의미한다.
Scalar Subquery	SELECT 절	SELECT 절에서 단일 값을 반환하는 서브쿼리를 의미한다.
Inline View	FROM 절	FROM 절에서 View 형태로 반환되는 서브쿼리를 의미한다.

수행에 따른 분류

종류	설명
Correlated Subqueries (상관관계 서브쿼리)	서브쿼리가 메인쿼리의 특정 열 값을 참조하는 경우 메인 쿼리가 먼저 실행된 후 서브쿼리가 수행된다. 보통 서브쿼리에서 조건을 충족하는지 확인하기 위해 사용된다.
Non-correlated Subqueries (비상관관계 서브쿼리)	서브쿼리가 먼저 실행된다 보통 서브쿼리의 실행 결과를 메인 쿼리에 전달하기 위해 사용된다.

반환 형태에 따른 분류

종류	설명
단일 행 서브쿼리	서브쿼리의 실행결과가 1개 이하인 경우이다. 이항 연산자의 사용이 가능하다.
다중 행 서브쿼리	서브쿼리의 실행결과가 2개 이상인 경우이다. 다중 행 비교 연산자 사용이 가능하다. 이항 연산자는 사용 불가능하다.
다중 열 서브쿼리	서브쿼리의 실행 결과가 여러 열을 반환하는 경우이다. 메인 쿼리의 조건절에서 여러 열을 동시에 비교할 수 있다. 비교되는 열의 개수와 순서가 동일해야 한다.

다중 행 비교 연산자

연산자	설명
IN, NOT IN	서브쿼리에 존재하는가, 존재하지 않는가
SOME or ANY (둘 다 같다)	서브쿼리 내 조건을 만족하는 최소 하나 이상의 값이 존재하는가
ALL	서브쿼리 내 모든 값이 조건을 만족하는가
EXISTS, NOT EXISTS	서브쿼리 내 값이 존재하는가, 존재하지 않는가

3) CTE (Common Table Expression)

WITH AS

서브 쿼리를 임시 테이블처럼 사용할 수 있는 기능이다.

VIEW와 다른 점은 쿼리가 끝나면 사라진다.

WITH alies_name AS (
    subquery
);

4) 집합 연산자

columns의 이름과 도메인이 같은 두 테이블에 대해서만 적용 가능하다.

종류	설명
UNION	합집합
UNION ALL	두 집합의 Cartesian product
INTERSECT	교집합
EXCEPT	차집합 * MySQL은 지원하지 않음

5) JOIN

두 테이블을 하나의 테이블로 합친다.

두 테이블에 동일한 이름의 열이 있을 경우 어떤 테이블에서 가져올 것인지 선택해야 한다.

5-1) Cross Join

Cartesian product로 가능한 모든 조합의 행이 존재하는 테이블이 생성된다.

SELECT * FROM table1, table2

SELECT * FROM table1 CROSS JOIN table2;

5-2) INNER JOIN (=Equi Join)

지정된 열이 일치하는 행끼리 연결한다.

SELECT column1
FROM table1
[INNER] JOIN table2 ON table1.col = table2.col

USING을 이용한 Inner Join

같은 이름을 가지는 속성으로 간단하게 Join 조건문을 작성할 수 있다.
USING으로 사용된 열은 중복되지 않는다.
*를 사용할 때 USING으로 사용된 열이 항상 테이블의 맨 앞으로 온다.

SELECT column1
FROM table1
[INNER] JOIN table2 USING(common_column)

5-3) Non-equi Join

등치를 제외한 비교 연산자를 이용한 join을 의미한다.

SELECT column1
FROM table1, tabl2
WHERE table1.col1 < table2.col2

5-4) NATURAL JOIN

중복된 이름의 열이 없도록 하는 join을 의미한다.

자동으로 equi join의 조건을 찾아 연결한다.

SELECT * FROM table1 NATURAL JOIN table2;

5-5) OUTER JOIN

한 쪽 테이블을 모두 포함하는 join을 의미한다.

outer join에도 natural을 적용할 수 있다.

SELECT col1, col2 FROM table1 
LEFT [OUTER] JOIN table2;
RIGHT [OUTER] JOIN table2;
FULL [OUTER] JOIN table2;

6) 사용자 정의 함수

CREATE FUNCTION function_name (파라미터명 데이터타입, ...)
RETURNS 반환타입
DETERMINISTIC -- (또는 NON-DETERMINISTIC) 결과가 항상 같은지 여부
BEGIN
    -- 함수 본문
    RETURN 반환값;
END;

DCL (Data Control Language) 문법 정리

1) GRANT

데이터베이스 사용자에게 특정 테이블에 대한 SELECT, INSERT, UPDATE, DELETE 권한을 개별적으로 부여한다.

GRANT { privilege_type [, ...] | ALL [ PRIVILEGES ] }
    ON { table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...]
    [ WITH GRANT OPTION ];

`WITH GRANT OPTION` : 권한을 부여받은 사용자도 해당 권한을 다른 사용자에게 부여할 수 있다.

2) REVOKE

SELECT, INSERT, DELETE, UPDATE 등 개별 사용자에게 부여된 권한을 취소한다.

REVOKE [ privilege_type [, ...] | ALL ]
    ON { table_name [, ...] | ALL }
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ];

`GRANTED BY role_specification`을 이용하여 특정 사용자에게 부여된 권한만 삭제할 수 있다.
`CASCADE` : 권한 철회 시 그에 의존하는 다른 권한이나 객체도 자동으로 삭제한다.
`RESTRICT` : 그에 의존하는 다른 권한이나 객체가 있으면 철회를 허용하지 않는다.

TCL (Transaction Control Language) 문법 정리

Transaction

트랜잭션(Transaction)은 데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위를 의미한다. 이는 한 줄의 명령일 수도 있고 여러 줄의 명령일 수도 있다.

트랜잭션의 주요 특성으로는 원자성(Atomicity), 일관성(Consistency), 격리성(Isolation), 지속성(Durability)이 있다. 이를 ACID 특성이라고 부르며, 이 특성들은 트랜잭션이 안전하게 수행되어 데이터 무결성이 보장될 수 있도록 한다.

원자성(Atomicity) : 트랜잭션 내의 모든 작업은 완전히 수행되거나 아예 수행되지 않아야 한다.
일관성(Consistency) : 트랜잭션의 실행 전과 후에 데이터베이스가 가지고 있던 제약이나 규칙들이 위반되지 않아야 한다.
격리성(Isolation) : 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않도록 각 트랜잭션은 다른 트랜잭션의 중간 결과에 접근할 수 없어야 하며, 독립적으로 실행되어야 한다.
지속성(Durability) : 트랜잭션이 성공적으로 완료되면 그 결과는 영구적으로 데이터베이스에 저장되어야 한다. 시스템 장애가 발생하더라도 커밋된 트랜잭션의 결과는 보존되어야 한다.

1) COMMIT

트랜잭션의 모든 변경사항을 데이터베이스에 영구적으로 반영한다.

COMMIT 실행 후에는 해당 트랜잭션의 변경사항을 취소할 수 없다.
rollback, ddl, dcl 명령어 실행 시 auto-commit 되어 현재 트랜잭션이 종료되고 새로운 트랜잭션이 발생한다.

COMMIT;

2) ROLLBACK

트랜잭션의 변경사항을 취소하고 데이터베이스를 이전 상태로 되돌린다. 트랜잭션 중 오류가 발생하거나 의도하지 않은 변경이 있을 때 사용된다.

데이터베이스를 현재 트랜잭션 시작 전의 상태로 되돌린다.

ROLLBACK;

3) SAVEPOINT

트랜잭션의 중간 저장점을 생성한다.

트랜잭션 내에서 여러 개의 SAVEPOINT를 생성할 수 있다.
특정 SAVEPOINT로 롤백하면 해당 지점 이후의 변경사항만 취소되며 이후 지점으로는 돌아갈 수 없다.

-- 세이브 포인트 지정
SAVEPOINT savepoint_name;

-- 세이브 포인트로 돌아가기
ROLLBACK TO savepoint_name;

728x90

저작자표시

티스토리