5장~7장

Book/혼자 공부하는 SQL

5장~7장

kinggora 2022. 10. 5. 00:35

05. 테이블과 뷰

테이블 만들기

테이블: 표 형태의 2차원 구조

행: row, record

열: column, field

테이블 설계: 열 이름, 데이터 형식, 널 허용 여부(NN), 기타(PK, FK, 자동 증가 옵션(AI), unsigned 등)

외래키로 설정한 칼럼을 작성하기 위해서는 매핑된 다른 테이블의 기본키 칼럼을 작성해야 한다.

일대다 관계에서 '다'는 '일'이 있어야 데이터 입력 가능

-- DROP DATABASE IF EXISTS sample_db;
CREATE DATABASE sample_db;

USE sample_db;
-- DROP TABLE IF EXISTS member;
CREATE TABLE member
( mem_id		CHAR(8) NOT NULL PRIMARY KEY,
  mem_name		VARCHAR(10) NOT NULL,
  addr			CHAR(2) NOT NULL,
  phone1		CHAR(3) NULL,
  phone2		CHAR(8) NULL,
  height		TINYINT UNSIGNED NULL,
  debut_date		DATE NULL
);

CREATE TABLE buy
(  num		INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   mem_id	CHAR(8),
   prod_name	CHAR(6) NOT NULL,
   price		INT UNSIGNED NOT NULL,
   amount		SMALLINT UNSIGNED NOT NULL,
   FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

`FOREIGN KEY(외래키_칼럼) REFERENCES 연관_테이블(기본키_칼럼)`

테이블 제약조건

기본 키, 외래 키, 고유 키, 체크, 기본값, NOT NULL 등

제약조건은 데이터의 무결성을 보장한다.

기본 키 제약조건

수많은 행 데이터 중에 데이터를 구분할 수 있는 식별자.
기본 키의 조건: 다른 행과 중복 값X, NOT NULL
테이블마다 기본 키는 1개만 가질 수 있다. (단 하나의 칼럼만 지정)
기본 키는 클러스터형 인덱스를 자동 생성한다.

기본 키 지정 방법

테이블 생성시

1. 칼럼에 PRIMARY KEY 옵션 지정

2. PRIMARY KEY(칼럼명) 명시

3. 위 방법을 사용하지 않고 테이블 생성 후

ALTER TABLE ``` ADD CONSTRAINT PRIMARY KEY(```)

외래 키 제약조건

두 테이블을 연결하는 역할
외래 키가 설정된 열은 다른 테이블의 기본 키와 연결된다.
기본 키 테이블: 기준 테이블(일), 외래 키 테이블: 참조 테이블(다)
외래 키와 기본 키의 이름은 똑같이 쓰는 것을 권장

외래 키 지정 방법

테이블 생성시

1. FOREIGN KEY(외래키_칼럼) REFERENCES 연관_테이블(기본키_칼럼)

2. 위 방법을 사용하지 않고 테이블 생성 후

ALTER TABLE ``` ADD CONSTRAINT FOREIGN KEY(```) REFERENCES ```(```)

ON UPDATE CASCADE, ON DELETE CASCADE

외래 키와 연결된 기본 키는 데이터의 무결성을 보장하기 위해 수정 및 삭제가 불가하다.

ON UPDATE CASCADE: 기본 키 데이터가 수정되면 외래 키도 똑같이 수정되는 기능

ON DELETE CASCADE: 기본 키 데이터가 삭제되면 외래 키도 똑같이 삭제되는 기능

ALTER TABLE ```

ADD CONSTRAINT FOREIGN KEY(```) REFERENCES ```(```)

ON UPDATE CASCADE

ON DELETE CASCADE;

*물론 테이블을 생성할 때도 설정 가능

고유 키 제약조건 (UNIQUE)

중복 불가, NULL 허용
기본 키는 아니지만 중복을 허용하지 않는 값에 설정

체크 제약조건 (CHECK)

입력 데이터를 제한, 점검
CHECK( 조건 )

CREATE TABLE member(
    ...,
    height    TINYINT  UNSIGNED  NULL  CHECK(height >= 100),
    phone1  CHAR(3)  NULL  CHECK(phone1 IN ('02', '031', '032', '054', '055', '061'))

);

ALTER TABLE member
  ADD CONSTRAINT
  CHECK(phone1 IN ('02', '031', '032', '054', '055', '061'));

기본값(DEFAULT)

값을 지정하지 않았을 때 자동으로 입력될 값
값을 지정하지 않았다는 것은 명시적 NULL과는 다르다. 이경우 기본값이 있으면 기본값이 입력되고, 기본값이 없으면 NULL이 입력된다.

-- 기본값 지정
CREATE TABLE member(
    ...,
    height	TINYINT  UNSIGNED  NULL  DEFAULT 160,
    phone1	CHAR(3)  NULL
);

ALTER TABLE member
  ALTER COLUMN phone1 SET DEFALUT '02';
  
-- 기본값 입력
INSERT INTO member VALUES (..., default, default);

/* QUERY 1 */
INSERT INTO member (height) VALUES (NULL)
/* QUERY 2 */
INSERT INTO member (height) VALUES ()

QUERY 1 : NULL 입력

QUERY 2 : DEFAULT 가 있다면, DEFAULT 입력 (없다면 NULL 값이 넘어감)

* NOT NULL, DEFAULT *

값 지정 안함: DEFAULT 활성화
NULL 직접 입력: NOT NULL 활성화 -> 오류

가상의 테이블: 뷰

사용자 입장에서는 테이블과 거의 동일한 개체처럼 사용할 수 있다.
뷰는 테이블처럼 데이터를 가지고 있지 않다.
뷰의 실체는 SELECT 문으로 만들어져 있기 때문에 뷰에 접근하는 순간 SELECT 문이 실행되고 그 결과가 화면에 출력된다. (마치 테이블을 출력하기 위해 우리가 직접 SELECT 문을 작성하는 것처럼)
단순 뷰: 하나의 테이블과 연관된 뷰
복합 뷰: 여러 테이블과 연관된 뷰

뷰 생성

CREATE VIEW 뷰_이름

SELECT 문;

뷰 사용

SELECT 문에 테이블 대신 뷰 이름을 넣고 실행

=> 뷰의 SELECT 문 실행

뷰를 사용하는 이유

보안

클라이언트가 요청한 데이터(칼럼)만 포함하여 불필요한 정보를 노출시키지 않을 수 있다.

실제 테이블에 접근하지 않기 때문에 생기는 이점이다.

SQL 간결화

자주 사용하는 쿼리를 뷰로 생성해두고 필요할 때 사용할 수 있다.

뷰의 실제 작동

뷰의 실제 생성, 수정, 삭제

뷰를 생성하면서 뷰에서 사용될 열 이름을 테이블과 다르게 지정할 수 있다 -> 별칭 사용

CREATE VIEW v_viewtest
AS
  SELECT B.mem_id 'Member ID',
  	M.mem_name AS 'Member Name', 
        B.prod_name "Product Name",
  	CONCAT(M.phone1, M.phone2) AS "Office Phone"
  FROM buy B
  INNER JOIN member M
  ON B.mem_id = M.mem_id;
  
 SELECT DISTINCT `Member ID`, `Member Name` FROM v_viewtest; -- 백틱(`) 사용

단, 뷰를 조회할 때 열 이름(별칭)에 공백이 있으면 백틱(`)으로 묶어야 한다.

DESCRIBE 로 뷰의 정보를 확인할 때 실제 테이블의 PK 여부는 확인할 수 없다.
뷰를 통한 데이터의 수정, 삭제가 가능하다.
뷰를 통한 데이터 입력은 NOT NULL 조건에 따라 될 수도 안 될 수도 있다. 즉, 권장하지 않는다.
뷰가 참조하고 있는 테이블이 삭제되면 뷰도 사용할 수 없다. ( CHECK TABLE view_name; 으로 조회되지 않는 이유 확인 가능 )

06. 인덱스

인덱스의 개념

데이터를 빠르게 찾을 수 있도록 도와주는 도구

반드시 필요한 도구는 아니지만 데이터 양이 많을 경우 인덱스는 선택 아닌 필수

장점

SELECT 문으로 검색하는 속도가 매우 빨라진다.
그 결과 컴퓨터의 부담이 줄어들어 결국 전체 시스템의 성능이 향상된다.

단점

인덱스도 공간을 차지해서 데이터베이스 안에 추가 공간이 필요하다. (대략 테이블 크기의 10% 정도)
처음 인덱스를 생성하는 데 시간이 오래 소요될 수 있다.
SELECT가 아닌 데이터 변경 작업(INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능이 저하될 수 있다.

종류

클러스터형 인덱스(Clustered Index) : PRIMARY KEY > UNIQUE NOT NULL. 테이블당 1개만 지정 가능. 기본 키로 설정한 칼럼에 대해 클러스터형 인덱스가 자동 생성 -> 기본키 기준으로 데이터 자동 정렬
보조 인덱스(Secondary Index) : UNIQUE, UNIQUE NULL. 여러 개 지정 가능. 데이터와 인덱스를 다른 위치에 저장

*인덱스 정보 확인: SHOW INDEX FROM 테이블

key_name 가 primary 라면 클러스터형 인덱스. 칼럼명 이라면 보조 인덱스

인덱스의 내부 작동

클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리(Balenced Tree, B-Tree) 구조로 구성된다.

root node - internal node - leaf node

In database, node = page

각 페이지에 데이터가 여러 건 들어간다.

만약 데이터를 균형 트리에 적재하지 않는다면, 리프 페이지만 있으므로 모든 데이터를 처음부터 끝까지 검색하는 Full Table Scan 방법 밖에 없다.

*Full Table Scan: SELECT 과정 중에 데이터를 처음부터 끝까지 검색하는 방법.

균형 트리 는 무조건 루트 페이지부터 검색한다. 데이터가 정렬된 상태를 유지하고 있으므로 탐색을 더 빠르게 할 수 있다.

그러나 정렬된 상태를 유지하기 위해 데이터 갱신 시 균형을 재정립하는 작업이 필요하다. 이것을 페이지 분할이라고 하는데 성능 저하의 원인이 된다.

클러스터형 인덱스

루트 페이지: 데이터 탐색의 시작. 리프 페이지의 참조를 저장한다. 정렬O
리프 페이지=데이터 페이지: 실제 데이터의 참조값. 정렬O

보조 인덱스

루트 페이지: 데이터 페이지에 바로 연결되지 않고 리프 페이지에 매핑되는 참조 값을 가진다. 정렬O
리프 페이지: 데이터의 참조를 저장한다. (데이터 페이지 위치 + 페이지 내에서 위치) 정렬O
데이터 페이지: 실제 데이터의 참조값. 어떤 처리도 하지 않는다. 정렬X

인덱스 실제 사용

인덱스 생성 (간소화)

CREATE [UNIQUE] INDEX 인덱스명

ON 테이블명 (칼럼명) [ASC | DESC] (기본값: ASC)

ANALYZE TABLE 테이블명; -> 인덱스 생성 후 인덱스를 적용시키는 과정

UNIQUE INDEX: 지정한 칼럼의 데이터가 중복을 허용하지 않는다. 현재도 중복이 없고, 앞으로도 중복될 일이 없을 데이터에만 적용할 것

인덱스 제거

DROP INDEX 인덱스명 ON 테이블명

기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다.

해당 제약조건을 제거하면 인덱스 또한 자동으로 제거된다.

외래 키 제거: ALTER TABLE 테이블명 DROP FOREIGN KEY 외래키;
기본 키 제거: ALTER TABLE 테이블명 DROP PRIMARY KEY;

*INDEX in Table Status*

SHOW TABLE STATUS LIKE '테이블명';

Non_unique: 중복 허용 여부. (UNIQUE) 1이면 중복 허용, 0이면 비허용

Data_length: 한 페이지의 크기

Index_length: 보조 인덱스 크기

인덱스 사용

Full Table Scan 외에는 모두 인덱스를 사용한 탐색 방법이다.
SELECT 절의 조회 데이터는 인덱스와 무관하다. (칼럼 데이터를 전부 가져오는 것일 뿐 필터링의 개념이 아님)
WHERE 절에서 인덱스를 생성한 칼럼에 대한 조건절만 인덱스를 사용한다. 그러나, 무조건 인덱스를 사용하여 탐색하는 것은 아니다. 인덱스를 사용할 때 더 비효율적일 경우 MySQL 서버는 인덱스를 사용하지 않는다.
인덱스를 생성한 칼럼에 연산, 함수 등 가공이 들어가면 인덱스를 사용하지 않는다.

WHERE num*2 >= 14 -> 인덱스 사용X (Full Table Scan)

WHERE num >= 14/2 -> 인덱스 사용

07. 스토어드 프로시저(stored procedure)

스토어드 프로시저 사용 방법

SQL + 프로그래밍 -> 스토어드 프로시저

C, 자바, 파이썬 등 프로그래밍과는 조금 차이가 있지만 MySQL 내부에서 사용할 때 적절한 프로그래밍 기능 제공

쿼리문의 집합을 일괄 처리하기 위한 용도로 사용

(4장의 SQL 프로그래밍 참고)

스토어드 프로시저 생성

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN
  이 부분에 SQL 프로그래밍 코드 작성
END $$
DELIMITER ;

$$ 의 $은 1개만 사용해도 됨. 명확성을 위해 2개 사용

##, %%, &&, // 등으로 바꾸어 써도 됨

스토어드 프로시저 호출

CALL 스토어드_프로시저_이름();

스토어드 프로시저 삭제

DROP PROCEDURE 스토어드_프로시저_이름;

매개변수 사용

입력 매개변수

CREATE PROCEDURE user_prod( IN 변수명 데이터타입 ) ...

CALL user_prod( 전달값 );

출력 매개변수

CREATE PROCEDURE user_prod( OUT 변수명 데이터타입 ) ...

CALL user_prod( @변수명 ); -> 참조변수 전달

SELECT a INTO b FROM : 조회 결과 a를 변수 b에 대입한다.

참고: 프로시저 생성 시점에 프로시저에서 데이터를 저장하는 테이블이 존재하지 않더라도 오류가 발생하지 않는다.

실제로 프로시저가 실행되는 시점에 테이블이 존재하면 된다.

변수 선언 및 초기화

스토어드 프로시저 안에서 변수 선언은 `DECLARE` 을 사용한다.

DECLARE 변수명 데이터타입;

SET 변수명 = 값;

변수 선언과 초기화 동시에

SET @변수명 = 값;

동적 쿼리 사용 예제

DROP PROCEDURE IF EXISTS dynamic_prod;
 DELIMITER $$
 CREATE PROCEDURE dynamic_prod(IN tableName VARCHAR(20))
 BEGIN
    SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);
    PREPARE myQuery FROM @sqlQuery;
    EXECUTE myQuery;
    DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;
 
call dynamic_prod('member');

스토어드 함수

스토어드 함수는 스토어드 프로시저와 비슷하지만, 사용 방법이나 용도가 조금 다르다.

스토어드 함수 생성

DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
    RETURN 반환형식
BEGIN
  이 부분에 SQL 프로그래밍 코드 작성
  RETURN 반환값;
END $$
DELIMITER ;

스토어드 프로시저와 다르게 입력 매개변수만 있다. (변수명 데이터타입) 으로 정의

반환형식+반환값이 출력 매개변수를 대신한다.

스토어드 함수 호출

SELECT 스토어드_함수_이름();

스토어드 함수는 SELECT 쿼리에서 많이 사용한다.

커서(Cursor)

커서는 스토어드 프로시저에서 모든 행을 한 번씩 처리할 때 사용한다. (Loop)

일종의 포인터?

커서의 작동 순서

커서 선언 -> 반복 조건 선언 -> 커서 열기 -> [데이터 가져오기 -> 데이터 처리하기] -> 커서 닫기

*[반복]

커서 사용 예제

1. 사용할 변수 준비

DECLARE memNumber INT;			-- 회원의 인원수
DECLARE cnt INT DEFAULT 0;		-- 읽은 행의 수
DECLARE totalNumber INT DEFAULT 0;	-- 전체 인원의 합계
DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부

2. 커서 선언

DECLARE memberCursor CURSOR FOR
	SELECT mem_number FROM member;

커서는 결국 SELECT 문이다. SELECT 쿼리를 FOR 이하에 선언한다.

3. 반복 조건 선언

DECLARE CONTINUE HANDLER
	FOR NOT FOUND SET endOfRow = TRUE;

DECLARE CONTINUE HANDLER : 반복 조건을 준비하는 예약어

FOR NOT FOUND : 더이상 행이 없을 때의 수행 지정

4. 커서 열기

OPEN memberCursor;

5. 행 반복하기

한 행씩 접근해서 SELECT 문을 실행

cursor_loop: LOOP
  -- 반복하는 부분
  FETCH memberCursor INTO memNumber;  -- 다음 행 이동
  
  IF endOfRow THEN
    LEAVE cursor_loop;
  END IF;
  
  SET cnt = cnt + 1;
  SET totalNumber = totalNumber + memNumber; 
END LOOP cursor_loop;

cursor_loop: LOOP의 이름
FETCH memberCursor INTO memNumber: FETCH 는 한 행씩 읽어오는 명령으로, 커서가 가르키는 데이터 (mem_number 행) 를 변수 memNumber 에 저장한다.
반복문을 빠져나갈 조건: 반복 조건 선언에서 마지막 행에 도달하면 endOfRow 를 TRUE 로 변경하도록 했다. LEAVE 를 통해 endOfRow 이 참이면 루프를 빠져나가게 하자.

6. 커서 닫기

CLOSE memberCursor;

트리거

테이블에 INSERT, UPDATE, DELETE 작업이 발생하면 자동으로 실행되는 코드

트리거는 사용자가 추가 작업을 잊어버리는 실수를 방지한다. 데이터 변경 전 백업하는 작업 등 -> 데이터의 무결성 보장

트리거의 기본 작동

DML(Data Manipulation Language) 이벤트가 발생할 때 작동. 임의로 호출하여 작동하는 것은 불가능하다.

테이블에 부착(attach)되는 프로그램 코드라고 생각하자

참고: 여기서 설명하는 트리거는 AFTER 트리거이다. 일반적으로 AFTER 트리거를 많이 사용하며 BEFORE 트리거와는 작동방식이 많이 다르다.

트리거 생성

DELIMITER $$
CREATE TRIGGER 트리거_이름
    AFTER INSERT|UPDATE|DELETE	-- 어떤 DML 후에 작동시킬지 지정
    ON 테이블명			-- 트리거 부착할 테이블
    FOR EACH ROW		-- 각 행마다 적용시킴
BEGIN
    트리거 실행시 작동되는 코드
END $$
DELIMITER ;

트리거 활용 - 데이터 백업

테이블 하나에 트리거는 여러 개 부착할 수 있다. ex) Update 트리거, Delete 트리거

CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);
DROP TABLE IF EXISTS backup_singer;
CREATE TABLE backup_singer
(   mem_id		CHAR(8) NOT NULL,
    mem_name 	VARCHAR(10) NOT NULL,
    mem_number	INT NOT NULL,
    addr		CHAR(2) NOT NULL,
    modType		CHAR(2),			-- 변경된 타입. '수정' or '삭제'
    modDate		DATE,				-- 변경된 날짜
    modUser		VARCHAR(30)			-- 변경한 사용자
);

DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg
	AFTER UPDATE
        ON singer
	FOR EACH ROW
BEGIN
	INSERT INTO backup_singer VALUE (OLD.mem_id, OLD.mem_name, OLD.mem_number,
		OLD.addr, '수정', curdate(), current_user());
END $$
DELIMITER ;

DROP TRIGGER IF EXISTS singer_deleteTrg;
DELIMITER $$
CREATE TRIGGER singer_deleteTrg
	AFTER DELETE
        ON singer
	FOR EACH ROW
BEGIN
	INSERT INTO backup_singer VALUE (OLD.mem_id, OLD.mem_name, OLD.mem_number,
		OLD.addr, '삭제', curdate(), current_user());
END $$
DELIMITER ;

UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 7;

SELECT * FROM singer;
SELECT * FROM backup_singer;

singer: 트리거 부착 테이블
backup_singer: singer의 백업 테이블 (변경 타입, 날짜, 사용자 칼럼 추가)
singer_updateTrg, singer_deleteTrg: singer에 부착된 트리거
singer에 UPDATE, DELETE 가 실행되면, 트리거가 backup_singer에 데이터를 INSERT 한다.