본문 바로가기
독서/데이터베이스

SQL 메모 정리 [작성중]

ujumeonji 2022. 1. 5.
728x90

SQL (Structured Query Language)

데이터베이스에서 데이터를 정의, 조작, 제어하기 위해 사용되는 언어로 크게 세 가지로 구분될 수 있다.

속성 설명 주요 명령어
DDL 데이터베이스, 테이블 등을 생성, 삭제 또는 구조 변경을 위한 명령어 CREATE, ALTER, DROP
DML 데이터베이스의 데이터를 조회, 저장, 삭제, 수정하기 위한 명령어 INSERT, UPDATE, DELETE, SELECT 등
DCL 저장된 데이터를 관리하기 위하여 데이터의 보안성 및 무결성 등을 제어하기 위한 명령어 GRANT, REVOKE 등

관계형 데이터베이스란?

관계형 데이터베이스는 키와 값으로 이뤄진 테이블로 이루어져 있으며, 이처럼 데이터의 종속성을 관계로 표현하는 것을 의미한다. 일관된 테이블 구조와 인덱스를 통해 데이터의 분류, 정렬, 탐색 속도가 빠르고 오랫동안 사용된 만큼 신뢰성이 높다. 또 다양한 기법을 통해 데이터의 무결성을 보장해주지만, 기존 스키마의 수정이 어렵고 데이터베이스의 부하를 분석하는 것이 어렵다.

제약 조건

NOT NULL

CREATE TABLE 테이블이름
(
    필드이름 필드타입 NOT NULL,
    ...
)
//
ALTER TABLE 테이블이름 ADD 필드이름 필드타입 NOT NULL
//
ALTER TABLE 테이블이름 MODIFY COLUMN 필드이름 필드타입 NOT NULL

UNIQUE

CREATE TABLE 테이블이름
(
    필드이름 필드타입,
    ...,
    [CONSTRAINT 제약조건이름] UNIQUE (필드이름)
)

ALTER TABLE 테이블이름 ADD [CONSTRAINT 제약조건이름] UNIQUE (필드이름)

ALTER TABLE 테이블이름 MODIFY COLUMN [CONSTRAINT 제약조건이름] UNIQUE (필드이름)

UNIQUE 제약 조건을 걸면 해당 필드는 자동으로 인덱스를 만들게 되는데 그래서 UNIQUE 제약 조건을 제거할 땐 다음과 같이 제거할 수 있다.

ALTER TABLE Reservation DROP INDEX reservedRoom;

PRIMARY KEY

PRIMARY KEY(기본키) 제약 조건을 설정하면 해당 필드는 NOT NULL, UNIQUE 제약 조건을 모두 가지게 된다. 이렇게 생성된 기본키는 테이블당 오직 하나의 필드에만 설정할 수 있으며, 이 점이 UNIQUE와의 차이점이라 할 수 있다. 기본키 제약 조건을 설정하게 되면 인덱스의 역할인 데이터를 쉽고 빠르게 찾도록 도와준다.

CREATE TABLE 테이블이름
(
    필드이름 필드타입,
    ...,
    [CONSTRAINT 제약조건이름] PRIMARY KEY (필드이름)
)

ALTER TABLE 테이블이름
   ADD [CONSTRAINT 제약조건이름] PRIMARY KEY (필드이름)

ALTER TABLE 테이블이름
   MODIFY COLUMN [CONSTRAINT 제약조건이름] PRIMARY KEY (필드이름)
   
ALTER TABLE 테이블이름 DROP PRIMARY KEY

PRIMARY KEY 제약 조건은 테이블 당 유일한 필드를 나타내기 때문에 삭제 시 별다른 이름을 지정할 필요가 없다.

FOREIGN KEY

FOREIGN KEY(이하, 외래 키)는 한 테이블을 다른 테이블과 연결해주는 역할을 한다. 외래 키가 설정된 테이블에 레코드를 입력하면, 기준이 되는 테이블의 내용을 참조해서 레코드가 입력된다. 즉, 외래 키는 하나의 테이블을 다른 테이블에 의존하게 만든다. 주의한 점은 참조되는 테이블의 필드는 반드시 UNIQUE 또는 PRIMARY KEY 제약 조건이 설정되어 있어야 한다.

CREATE TABLE 테이블이름
(
    필드이름 필드타입,
    ...,
    [CONSTRAINT 제약조건이름]
    FOREIGN KEY (필드이름)
    REFERENCES 테이블이름 (필드이름)
)

외래 키 제약 조건은 참조되는 테이블에서 데이터의 수정이나 삭제가 발생하면 참조하고 있는 테이블의 데이터도 같이 영향을 받는다. 이것은 데이터의 무결성을 위한 작업으로 ON DELETE, ON UPDATE로 삭제 시, 수정 시 동작을 지정할 수 있다. 다음은 해당 동작이 발생 시 어떻게 처리할지에 대한 동작을 지정할 수 있다.

  설명
CASCADE 참조되는 테이블 테이블에서 데이터를 삭제하거나 수정하면 참조하는 테이블에서도 삭제와 수정이 같이 이루어진다.
SET NULL 참조되는 테이블에서 데이터를 삭제하거나 수정하면 참조하는 테이블의 데이터는 NULL로 변경된다.
NO ACTION 참조되는 테이블에서 데이터를 삭제하거나 수정해도, 참조하는 테이블의 데이터는 변경되지 않는다.
SET DEFAULT 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 필드의 기본값으로 설정된다.
RESTRICT 참조하는 테이블에 데이터가 남아 있으면, 참조되는 테이블의 데이터를 삭제하거나 수정할 수 없다. 
CREATE TABLE Test2
(
    ID INT,
    ParentID INT, 
    FOREIGN KEY (ParentID)
    REFERENCES Test1(ID) ON UPDATE CASCADE ON DELETE RESTRICT
);

위 코드는 참조하는 테이블은 참조되는 테이블의 데이터를 수정 시 CASCADE로 동작하고 삭제 시 RESTRICT로 동작한다.

다중 테이블 연산

JOIN은 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현해주며 SELECT 문과 함께 자주 사용된다. 표준 SQL에서는 레코드를 조합하는 방식에 따라 INNER JOIN, LEFT JOIN, RIGHT JOIN으로 구분된다. 그 외에 MySQL에서는 JOIN, INNER JOIN, CROSS JOIN 등이 추가적으로 있다.

INNER JOIN

SELECT *
FROM Reservation
INNER JOIN Customer
ON Reservation.Name = Customer.Name;

SELECT *
FROM Reservation
JOIN Customer
ON Reservation.Name = Customer.Name;

INNER JOIN은 ON 절과 함께 사용되며, ON 절의 조건을 만족하는 데이터만을 가져온다. 그 외에 MySQL에서는 쉼표(,)를 통해서 표현할 수 있다.

SELECT *
FROM Reservation AS r, Customer AS c
WHERE r.Name = c.Name;

위 코드에서 추가적으로 AS 키워드로 별칭을 붙여 테이블 이름을 간략히 할 수 있다.

LEFT JOIN

LEFT JOIN은 왼쪽 테이블을 기준으로 오른쪽 테이블을 조합하는 JOIN이다. 이때 ON 절의 조건을 만족하지 않는 경우에는 첫 번째 테이블의 필드 값은 그대로 가져오지만 오른쪽 테이블의 필드 값은 모두 NULL로 표시된다.

RIGHT JOIN

RIGHT JOIN은 LEFT JOIN의 반대로 오른쪽 테이블을 기준으로 JOIN을 한다.  마찬가지로 ON 절의 조건을 만족하지 않는 경우 왼쪽 테이블의 모든 필드는 NULL로 표시된다.

서브 쿼리(subquery)

서브 쿼리란 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미한다. 서브 쿼리를 포함하고 있는 쿼리를 외부쿼리(outer query)라고 부르며, 서브쿼리는 내부 쿼리(inner query)라고도 부릅니다. 이때 서브쿼리는 반드시 괄호(())로 감싸져 있어야만 한다.

MySQL에서 서브 쿼리를 포함할 수 있는 외부 쿼리는 SELECT, INSERT, UPDATE, DELETE, SET, DO 문이 있다. 또한 이러한 서브 쿼리는 또다시 다른 서브쿼리 안에 포함될 수 있다.

SELECT ID, ReserveDate, RoomNum
  FROM Reservation
WHERE Name IN (SELECT Name
               FROM Customer
               WHERE Address = '서울')

서브쿼리를 사용하면 쿼리를 구조화하고, 각 쿼리를 명확하게 구분해줘 읽기 편하고, 복잡한 JOIN이나 UNION과 같은 동작을 수행할 수 있다는 다른 방법을 제공한다.

서브 쿼리는 WHERE 절뿐만 아니라 SELECT 문의 FROM 절에서도 사용이 가능하며, 이때 사용된 서브쿼리는 AS를 이용해 반드시 이름을 정의해야 한다.

서브쿼리는 앞서 설명했듯 쿼리를 독립적으로 작성해서 가독성을 높일 수 있다. 대표적으로 SELECT, FROM, WHERE 절에 사용되며, 사용되는 위치별로 어떻게 최적화되는지 살펴볼 필요가 있다.

SELECT 절에 사용된 서브쿼리

SELECT 절에 사용된 서브쿼리는 내부적으로 임시 테이블을 만들거나 쿼리를 비효율적으로 실행하게 만들지는 않기 때문에 적절히 인덱스만 사용한다면 크게 신경 쓸 일은 없다.

일반적으로 SELECT 절에 서브쿼리를 사용하면 그 서브쿼리는 항상 칼럼과 레코드가 하나인 결과를 반환해야 한다. 즉 NULL이든 상관없이 레코드가 1건이 존재해야 하는 스칼라 서브쿼리만 사용할 수 있다.

SELECT 절에서 서브쿼리를 사용할 때 JOIN으로 할 수 있는 작업을 서브쿼리로 작성하는 경우가 있는데, JOIN을 활용할 수 있는 경우 JOIN을 활용하는 방향으로 쿼리를 작성하는게 성능상으로 더 좋으니 참고하자.

FROM 절에 사용된 서브쿼리

이전 버전의 MySQL의 경우 FROM 절에서 사용된 서브쿼리를 처리하기 위해서 결과를 임시 테이블에 저장하고 필요할 때 다시 임시 테이블을 읽는 방식으로 처리됐다. 5.7 버전 이후로 옵티마이저가 FROM 절의 서브쿼리를 외부 쿼리로 병합하는 최적화를 수행하도록 해 개선됐다. 하지만 이 경우에 모든 서브쿼리가 외부 쿼리로 병합될 수 있는 것은 아닌데 다음과 같은 상황에선 병합될 수 없다.

  • 집합 함수 사용
  • DISTINCT
  • GROUP BY 또는 HAVING
  • LIMIT
  • UNION(UNION DISTINCT) 또는 UNION ALL
  • SELECT 절에 서브쿼리가 사용된 경우
  • 사용자 변수 사용

외부 쿼리와 병합되는 FROM 절의 서브쿼리가 ORDER BY 절을 가진 경우에 외부 쿼리가 GROUP BY나 DISTINCT 같은 기능을 사용하지 않는다면 서브쿼리의 정렬 조건을 외부 쿼리로 같이 병합한다. 만약 외부 쿼리가 GROUP나 DISTINCT와 같은 기능을 사용되고 있다면, 서브쿼리의 정렬 작업은 무의하기 때문에 서브쿼리의 ORDER BY 절은 무시 된다.

WHERE 절에 사용된 서브쿼리

WHERE 절의 서브쿼리는 다양한 형태로 사용될 수 있는데 MySQL 옵티마이저가 최적화하는 형태를 기준으로 크게 3가지로 구분해볼 수 있다.

  • 동등 또는 작다 비교
  • IN 비교
  • NOT IN 비교
SELECT * FROM dept_emp de
WHERE de.emp_no=(SELECT e.emp_no
                 FROM employees e
                 WHERE e.first_name='Georgi' AND e.last_name='Facello' LIMIT 1);

동등 또는 크고 작다 비교의 경우의 이전 버전 MySQL에서는 서브쿼리를 나중에 실행하는 형태로 취했지만 5.5 버전부터는 서브쿼리부터 처리하여 처리된다. 동등 비교 대신 대소 비교도 동일한 실행 계획을 사용하지만, 단일 값이 아닌 튜플을 비교하는 경우 서브쿼리가 상수값이 되어도 풀 테이플 스캔을 실행하게 된다.

IN 비교가 WHERE 절에서 테이블의 레코드가 다른 테이블의 레코드를 이용한 표현식과 일치하는지를 체크하는 형태를 세미 조인이라고 한다. 이는 JOIN은 사용하지 않았지만 JOIN과 비슷한 효과를 내는데 5.5 버전까지 세미 조인은 풀 테이플 스캔을 사용해 안티 패턴 중 하나였지만 이후 버전에서 많은 개선을 됐다.

NOT IN 비교는 IN 비교와 크게 다르지 않을 것 같지만 안티 세미 조인으로 이 경우는 사용을 피해야 한다. RDBMS에서 Not-equals 비교는 인덱스를 제대로 사용하지 못하는데 이 경우도 같다. 

 

기본 문법

기본 문법에 앞서 MySQL에서 키워드와 구문, 문자열은 대소문자를 구분하지 않지만 테이블 명과 필드의 이름은 대소문자를 구분하니 주의해야 한다.

CREATE

  • CREATE DATABASE [데이터베이스 이름]
  • CREATE TABLE [테이블 이름]

MySQL에서 데이터베이스와 테이블을 만들기 위해서는 CREATE문을 사용한다. 데이터베이스에 데이터가 저장되기 위해선 데이터베이스와 테이블을 먼저 만들어 저장될 데이터를 의미 있게 분리할 수 있다.

CREATE DATABASE를 통해 생성된 데이터베이스는 USE [데이터베이스 이름]을 통해 지정하면 그 이후 명령(CREATE TABLE, SELECT,...)등은 해당 데이터베이스에 대해서 적용된다.

CREATE TABLE 테이블이름
(
    필드이름1 필드타입1,
    필드이름2 필드타입2,
    ...
)

테이블을 생성하기 위해서는 CREATE TABLE 이후에 필드 이름과 타입과 이 외의 정보를 작성해 표현된다. 테이블을 생성할 때 스키마 외에 제약 조건을 걸 수 있는데, 제약 조건이란 데이터의 무결성을 지키기 위해 데이터를 입력받을 때 수행되는 검사 규칙을 의미한다.

  설명
NOT NULL 해당 필드는 NULL 값을 저장할 수 없게 된다.
UNIQUE 해당 필드는 동일한 테이블 내에서 유일한 값을 가져야 한다.
PRIMARY KEY 해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 된다.
FOREIGN KEY 하나의 테이블을 다른 테이블에 의존하게 만든다.
DEFAULT 해당 필드의 기본값을 설정한다.

ALTER

앞서 생성한 데이터베이스 또는 테이블에 변경사항으로 인한 수정이 필요한 경우 ALTER 명령어를 통해서 수정이 가능하다.

ALTER DATABASE 데이터베이스이름 CHARACTER SET=문자집합이름
ALTER DATABASE 데이터베이스이름 COLLATE=콜레이션이름

대표적으로 데이터베이스 문자 집합을 ALTER 명령으로 변경할 수 있다. 

ALTER TABLE 테이블이름 (ADD | MODIFY COLUMN) 필드이름 필드타입
ALTER TABLE 테이블이름 DROP 필드이름

테이블의 경우 필드 추가, 삭제, 수정을 수행하기 위해서 사용될 수 있다.

DROP

DROP DATABASE Hotel;
DROP TABLE Reservation;

MySQL에서 데이터베이스와 테이블을 삭제할 수 있다. 단, 이 작업을 수행하면 포함된 데이터도 모두 삭제되니 주의해야 한다. 특히 데이터베이스를 삭제하는 경우 안의 테이블도 모두 삭제된다.

TRUNCATE TABLE 테이블이름

만약 테이블 삭제 시 테이블이 아닌 데이터만 삭제하기를 원할 때 TRUNCATE가 유용하다.

DROP DATABASE IF EXISTS Hotel;
DROP TABLE IF EXISTS Reservation;

IF EXISTS를 이용하면 개발 시 테스트 목적으로 데이터베이스 및 테이블을 생성할 때 중복 생성과 항상 동일한 상태를 얻기 위해서 기존에 존재하면 삭제하고 다시 생성할 때 유용하다.

참고자료

http://tcpschool.com/mysql/intro

'독서 > 데이터베이스' 카테고리의 다른 글

정규화  (0) 2022.01.11
함수적 종속성  (0) 2022.01.09
트랜잭션과 잠금  (0) 2022.01.01
쿼리 작성 및 최적화  (0) 2021.12.30
실행 계획 분석  (0) 2021.12.29

'독서/데이터베이스' 관련글

티스토리툴바