[SQL 5일차] 데이터베이스 서브쿼리와 제약조건, 뷰, 트랜잭션

본 포스팅은 서브쿼리와 제약조건, 뷰, 트랜잭션에 대해 알아보겠습니다.

---

먼저 서브쿼리에 대해 알아보겠습니다.

쿼리는 외부쿼리 - 내부쿼리 (서브 쿼리)로 나뉘어 집니다.

SELECT t.toy, b.boy

FROM toys AS t

 INNER JOIN

 boys AS b

    ON t.toy_id = b.toy_id

WHERE t.toy IN (SELECT toy FROM toys)

GROUP BY boy

ORDER BY boy;

SELECT toy

FROM toys

WHERE toy_id = (SELECT toy_id FROM boys WHERE boy LIKE ('d%'));

서브 쿼리는 IN, NOT IN 내부에 위치 할 수 있습니다.

서브 쿼리는 항상 괄호 안에 있습니다.

서브 쿼리는 하나의 값을 반환 해야하며, 하나의 COLUMN을 반환해야하지만 IN은 예외입니다.

SELECT t.toy, (SELECT boy FROM boys WHERE b.boy = boy) AS boy

FROM toys AS t

 INNER JOIN

    boys AS b

    WHERE t.toy_id = b.boy_id;

서브 쿼리는 SELECT 문에 위치 할 수 있습니다.

서브 쿼리에 자연 조인을 사용할 수 있습니다.

서브 쿼리가 외부 쿼리보다 먼저 실행 됩니다.

비상관 쿼리란 외부 쿼리의 어떤 것도 참조하지 않고 단독으로 사용되는것을 의미합니다.

가장 중요한 조인이 서브 쿼리 보다 훨씬 빠릅니다.

상관 쿼리란 외부 쿼리에 의존할 경우를 말합니다. 별명은 외부 쿼리를 공유합니다.

SELECT toy, boy

FROM toys AS t

INNER JOIN 

boys AS b

WHERE NOT EXISTS (select * from toys where t.toy_id <> b.toy_id);

EXIST, NOT EXISTS 와 상관 서브쿼리를 통해 유용하게 검색할 수 있습니다.

다음으로 제약조건에 대해 알아보겠습니다.

NOT NULL : NULL 값 허용 여부를 결정합니다.

PRIMARY KEY : 기본키를 선언합니다.

FOREIGN KEY : 외래키, 참조키를 선언합니다.

UNIQUE : 중복 값 허용 여부를 결정합니다.

CHECK : CHECK에 허용한 값이 아닐 경우 에러를 반환합니다.

CREATE TABLE piggy_back

(

 id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

 coin CHAR(1) CHECK (coin IN('P','N','D','Q'))

);

다음으로 VIEW(뷰)에 대해 알아보겠습니다.

[TEST 환경]

CREATE TABLE piggy_back

(

 id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

 coin CHAR(1) CHECK (coin IN('P','N','D','Q')),

    coin_year CHAR(4)

);

INSERT INTO piggy_back

VALUES

(1,'Q','1950'),

(2,'D','1920'),

(3,'A','1940'),

(4,'Y','1951'),

(5,'K','1991'),

(6,'T','2001'),

(7,'T','1183'),

(8,'A','1988'),

(9,'D','2015'),

(10,'Q','1950');

CREATE VIEW pb_quarters AS

SELECT * FROM piggy_back

WHERE coin='Q';

SELECT * FROM piggy_back;

SELECT * FROM pb_quarters;

먼저 뷰를 생성해 보도록 하겠습니다.

CREATE VIEW piggy_select AS 

SELECT * FROM piggy_back;

뷰도 마찬가지로 다음과 같이 조회가 가능합니다.

SELECT * FROM piggy_select;

뷰는 서브쿼리처럼 동작합니다.

그렇다면 뷰를 사용하는 이유는 무엇인지 확인해 볼 필요가 있습니다.

1. 데이터베이스의 구조를 변경하여도 테이블에 의존하는 어플리케이션을 변경할 필요가 없다.

2. 뷰는 복잡한 쿼리를 간단한 명령으로 단순하게 만들 수 있어 편리하고 실수를 줄인다. 

(가상 테이블 개념으로 매우 복잡한 쿼리도 미리 만들어 놓으면 손쉽게 사용이 가능하다.)

3. 사용자에게 필요없는 정보를 숨기는 뷰를 만들 수 있다.

CREATE VIEW pb_dimes AS

SELECT * FROM piggy_back

WHERE coin='Q' WITH CHECK OPTION;

INSERT INTO pb_dimes

VALUES

(13,'A','1950');

INSERT의 경우 WITH CHECK OPTION 여부로 WHERE 절의 조건을 검색하여 수행합니다. 이를 통해 CHECK 제약조건을 흉내 낼 수 있습니다.

뷰를 통해 INSER, UPDATE, DELETE를 하는 경우는 드뭅니다. UPDATE, DELETE의 경우 WHERE 절의 조건을 검색하여 수행합니다.

마지막으로 트랜잭션에 대해 알아보겠습니다.

ACID 테스트 (원자성, 일관성, 독립성, 지속성)를 기반으로 TRANSACTION이 수행됩니다.

START TRANSACTION;

/*

DROP TABLE piggy_back;

DROP VIEW pb_quarters;

DROP VIEW pb_dimes;

*/

CREATE TABLE piggy_back

(

 id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

 coin CHAR(1) CHECK (coin IN('P','N','D','Q')),

    coin_year CHAR(4)

);

INSERT INTO piggy_back

VALUES

(1,'Q','1950'),

(2,'F','1920'),

(3,'F','1940'),

(4,'Y','1951'),

(5,'K','1991'),

(6,'M','2001'),

(7,'M','1183'),

(8,'A','1988'),

(9,'D','2015'),

(10,'Q','1950');

CREATE VIEW pb_quarters AS

SELECT * FROM piggy_back

WHERE coin='Q';

INSERT INTO pb_quarters

VALUES

(11,'A','1950');

CREATE VIEW pb_dimes AS

SELECT * FROM piggy_back

WHERE coin='M' OR coin='F' WITH CHECK OPTION;

INSERT INTO pb_dimes

VALUES

(12,'M','1983');

COMMIT;

ROLLBACK; 

START TRANSACTION 시점부터 모든 쿼리가 성공하면 COMMIT, 하나라도 실패하면 ROLLBACK을 통해 복원하는 과정을 거치게 됩니다.

이는 2PC의 중요한 개념을 바탕으로 이루어지며 데이터의 일관성을 유지하게 합니다.

이번시간에는 서브쿼리와 제약조건, 뷰, 트랜잭션에 대해 알아보았습니다.

다음시간에는 마지막으로 데이터베이스 보안 및 derbyDB 간략 사용법에 대해 알아보도록 하겠습니다.

고맙습니다.