뷰 (View)
- 가상의 테이블
- 실체 : SELECT문 (SELECT 실행 결과가 화면에 출력되는 방식)
- 보안에 도움이 되고, 복잡한 SQL을 단순하게 만들 수 있다.
단순 뷰 - 하나의 테이블과 연관된 뷰
복합 뷰 - 2개 이상의 테이블과 연관된 뷰 (읽기 전용. 데이터 입력/수정/삭제 불가능)
기본 형태
뷰의 생성은 CREATE, 수정은 ALTER, 삭제는 DROP을 이용한다.
뷰를 통한 데이터 수정은 UPDATE ~ SET ~, 입력은 INSERT INTO ~ VALUES(), 삭제는 DELETE FROM ~을 이용한다.
접근은 SELECT문의 FROM절에서 뷰 이름을 호출하면 된다.
뷰의 정보 확인은 DESCRIBE을 이용한다.
# 뷰 생성
CREATE VIEW view_name
AS
SELECT문;
CREATE OR REPLACE VIEW view_name -- 기존에 뷰가 있어도 덮어쓰기
AS
SELECT문;
# 뷰 접근 (열 이름에 공백이 있으면 백틱(`)으로 묶어줘야 한다)
SELECT column_name FROM view_name [WHERE 조건];
# 뷰 수정
ALTER VIEW view_name
AS
SELECT문;
# 뷰 삭제
DROP VIEW view_name;
# 뷰 정보 확인
DESCRIBE view_name;
# 뷰의 소스 코드 확인
SHOW CREATE VIEW view_name;
# 뷰의 데이터 수정
UPDATE view_name SET __ = __ [WHERE __];
# 뷰의 데이터 삭제
DELETE FROM view_name [WHERE __];
# 뷰의 데이터 입력
INSERT INTO view_name VALUES(value, ..);
# 뷰의 데이터 입력 2 - 뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않도록 예약어 이용
ALTER VIEW view_name
AS
SELECT * FROM table_name WHERE __
WITH CHECK OPTION;
INSERT INTO view_name VALUES(value, ..);
# 뷰가 참조하는 테이블의 삭제
DROP TABLE IF EXISTS table_name;
# 뷰 상태 확인
CHECK TABLE view_name;인덱스
- 데이터를 빠르게 찾을 수 있도록 도와주는 도구
- SELECT문의 출력 속도가 빨라진다.
# 인덱스 정보 확인
SHOW INDEX FROM table_name;클러스터형 인덱스 (Clustered Index)
- 기본 키로 지정하면 자동 생성
- 테이블에 1개만 생성 가능
- 기본 키로 지정한 열을 기준으로 자동 정렬
Key_name=PRIMARY
보조 인덱스 (Secondary Index)
- 고유 키로 지정하면 자동 생성
- 테이블에 여러 개 생성 가능
- 자동 정렬 되지 않음
고유 인덱스
- 값이 중복되지 않는 인덱스
- 기본 키나 고유 키로 지정하면 값이 중복되지 않아서 고유 자동 생성됨
인덱스의 내부 작동
인덱스는 균형 트리의 자료 구조로 구성되어 있다.
노드 node=페이지 page: 균형트리 구조에서 데이터가 저장되는 공간루트 노드 root node: 가장 상위 노드중간 노드 internal node리프 노드 leaf node: 가장 하위 노드
- 전체 테이블 검색 : 데이터를 처음부터 끝까지 검색하는 것 (인덱스가 없을 때)
- 페이지 분할 : 데이터를 입력할 때, 입력할 페이지에 공간이 없어서 2개 페이지로 데이터가 나눠지는 것
- 인덱스 검색 : 클러스터형/보조 인덱스를 이용하여 데이터를 검색하는 것
인덱스 실제 사용
# 인덱스 생성 (보조 인덱스 생성)
CREATE [UNIQUE] INDEX index_name
ON table_name (column_name) [ASC|DESC]
# 인덱스 적용
ANALYZE TABLE table_name; -- 지금까지 만든 인덱스를 모두 적용
# 인덱스 제거
DROP INDEX index_name ON table_name- 인덱스는 열 단위에 생성된다.
- WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.
- WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
- 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없다.
- 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
- 사용하지 않는 인덱스는 제거해야 한다.
MySQL 워크벤치에서 SQL 실행 후, 실행 계획에서 인덱스의 사용 여부를 확인할 수 있다.
스토어드 프로시저
- Stored Procedure (저장 프로시저)
- SQL + 프로그래밍 기능
# 프로시저 생성
DELIMITER $$ -- 스토어드 프로시저를 묶어주는 기능 (##, %%, &&, // 등으로 대체 가능)
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN
SQL_프로그래밍_코딩
END $$
DELIMITER;
# 프로시저 호출
CALL 스토어드_프로시저_이름()
# 프로시저 삭제
DROP PROCEDURE name;매개변수 사용
- 스토어드 프로시저에는 실행 시 입력 매개변수를 지정 가능하고, 처리된 결과를 출력 매개변수를 통해 얻을 수도 있다.
# 입력 매개변수 지정
IN 입력_매개변수_이름 데이터_형식
# 입력 매개변수가 있는 스토어드 프로시저 실행
CALL 프로시저_이름(전달_값);
# 출력 매개변수 지정
OUT 출력_매개변수_이름 데이터_형식
# 출력 매개변수가 있는 스토어드 프로시저 실행
CALL 프로시저_이름(@변수명);
SELECT @변수명;# 입력 매개변수 활용
USE db_name;
DROP PROCEDURE IF EXISTS sp_name;
DELIMITER $$
CREATE PROCEDURE sp_name(IN userName VARCHAR(10)) -- 2. userName 매개변수에 대입
BEGIN
SELECT * FROM table_name WHERE column_name = userName; -- 3. "홍길동"에 대한 조회 수행
END $$
DELIMITER;
CALL userName("홍길동") -- 1. "홍길동"을 입력 매개변수로 전달# 출력 매개변수 활용
DROP PROCEDURE IF EXISTS sp_name;
DELIMITER $$
CREATE TABLE IF NOT EXISTS table_name(
id INT AUTO_INCREMENT PRIMARY KET,
txt CHAR(10) );
CREATE PROCEDURE sp_name(
IN userName VARCHAR(10),
OUT outValue INT ) -- 1. 출력 매개변수 outValue 지정
BEGIN
INSERT INTO table_name VALUES(value, ..);
SELECT MAX(id) INTO outValue FROM table_name; -- 2. id열의 최대값 저장
END $$
DELIMITER;
CALL sp_name (@myValue)
SELECT @myValueSQL 프로그래밍
SQL 프로그래밍은 스토어드 프로시저 안에 만들어야 한다.
IF문
DELIMITER $$
CREATE PROCEDURE if_else()
BEGIN
IF 조건식 THEN
SQL_문장들
ELSE
SQL_문장들
END IF;
END $$
DELIMITER;
CALL if_else()CASE문
DELIMITER $$
CREATE PROCEDURE case_when()
BEGIN
CASE
WHEN 조건n THEN
SQL_문장들
ELSE
SQL_문장들
END CASE;
END $$
DELIMITER;
CALL case_when()WHILE문
조건식이 참인 동안 SQL 문장들 계속 반복
ITERATE 레이블: 지정한 레이블로 가서 계속 진행LEAVE 레이블: 지정한 레이블을 빠져나감 (종료)
DELIMITER $$
CREATE PROCEDURE while_roof()
BEGIN
label_name :
WHILE 조건식 DO
IF 조건식 THEN
SET i += 1
ITERATE label_name;
END IF;
IF 조건식 THEN
SET i += 1
LEAVE label_name;
END IF;
END WHILE;
SQL_문장들
END $$
DELIMITER;
CALL while_roof()동적 SQL
USE db_name;
PREPARE query_name FROM 'SELECT * FROM table_name WHERE x = 1;
EXECUTE query_name; -- 필요한 시점에 실행
DEALLOCATE PREFARE query_name; -- 실행 후 문장 해제FROM 'SELECT * FROM table_name WHERE x = 1; 내용을 실행하지 않고 query_name에 입력만 시켜 놓는다.
실행이 필요한 시점에 EXECUTE query_name;문으로 실행시킨다.
스토어드 함수
DELIMITER $$
CREATE FUNCTION function_name(parameter) -- parameter : 입력 매개변수
RETURNS 반환형식
BEGIN
Coding
RETURN 반환값;
END $$
DELIMITER ;
SELECT function_name() [INTO @변수명]; -- 함수의 반환값을 @변수명에 저장커서
- 테이블에서 한 행씩 처리하기 위한 방식
- 커서 선언 >> 반복 조건 선언 >> 커서 열기 >> (데이터 가져오기 >> 데이터 처리하기) * 반복 >> 커서 닫기
USE db_name;
DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
# 1. 사용할 변수 준비하기
DECLARE number INT;
DECLARE end0fRow BOOLEAN DEFAULT FALST; -- 행의 끝을 파악하기 위함
# 2. 커서 선언
DECLARE cursor_name CURSOR FOR -- 커서 이름 설정
SELECT number_column FROM table_name;
# 3. 반복 조건 선언
DECLARE CONTINUE HANDLER -- 반복 조건을 준비하는 예약어
FOR NOT FOUND SET end0fRow = TRUE; -- 더이상 행이 없을 때 SET ~ 수행
# 4. 커서 열기
OPEN cursor_name;
# 5. 행 반복 (데이터 가져오기 >> 데이터 처리하기)
cursor_loop: LOOP -- 반복할 부분의 이름 지정
-- 이 사이 부분을 반복
FETCH cursor_name INTO number; -- 한 행씩 읽어오기
IF end0fRow THEN
LEAVE cursor_loop; -- 빠져나가기
END IF;
SET number += 1;
-- 이 사이 부분을 반복
END LOOP corsor_loop
SELECT number AS 'NO.';
# 6. 커서 닫기
CLOSE cursor_name;
END $$
DELIMITER;
CALL cursor_proc();트리거
- 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수 방지
- INSERT, UPDATE, DELETE 작업이 발생하면 실행되는 코드
- 트리거 작동 >> 데이터 무결성
DROP TRIGGER IF EXISTS myTrigger;
DELIMITER $$
CREATE TRIGGER myTrigger -- 1. 트리거 이름 지정
AFTER DELETE -- 2. DELETE문이 발생된 이후 작동하라
ON trigger_table -- 3. 트리거를 부착할 테이블 지정
FOR EACH ROW -- 4. 각 행마다 적용시킴
BEGIN -- 5. 트리거에서 실제로 작동할 부분
SET @var_name = 'action'; -- 트리거 실행 시 작동되는 코드들
END $$
DELIMITER;트리거는 행 데이터가 삭제/수정되면 기존 데이터를 백업 테이블에 저장하도록 할 수 있다.
트리거에서 기존 데이터는 OLD TABLE에, 새로운 데이터는 NEW TABLE에 잠깐 저장된다.
planning design development with data