DATA BASE 4

2023-07-20

 

SMALL

1. TCL

 

• 명령어

COMMIT : 작업 내역을 원본에 반영

 

ROLLBACK [TO SAVEPOINT이름]: 트랜잭션이 만들어지는 지점이나 SAVEPOINT 지점으로 복구

 

SAVEPOINT 이름 : ROLLBACK 할 지점을 생성

 

-- SELECT 구문 실행 : 트랜잭션과 아무런 연관성이 없음
select * from DEPT;

-- dept 테이블에 데이터를 1개 삽입 : 이전 트랜잭션이 없어서 트랜잭션이 생성
insert into DEPT(deptno,dname, loc) values(50, '회계', '서울');

SELECT * FROM DEPT;

-- 철회 : SAVEPOINT를 입력하지 않으면 트랜잭션 시작 전으로 복구
ROLLBACK;

SELECT * FROM DEPT;


-- 삽입 - 트랜잭션이 생성
insert into DEPT(deptno,dname, loc) values(50, '회계', '서울');



-- DEPT 테이블의 모든 내용을 가진 DEPTCOPY 테이블을 생성
-- DDL(CREATE, DROP, ALTER, TRUNCATE, RENAME), DCL(GRANT, REVOKE) 를 수행하면 AUTO COMMIT
-- COMMIT 수행 : 트랜잭션은 변경 내용을 반영하고 종료
CREATE TABLE DEPTCOPY
AS
SELECT *
FROM DEPT;


-- 철회
ROLLBACK;

SELECT *
FROM DEPT; -- ROLLBACK을 해도 결과는 그대로!

SELECT *
FROM DEPTCOPY



-- 트랜잭션이 생성
insert into DEPT(deptno,dname, loc) values(60, '회계', '서울');

SAVEPOINT SV1;

insert into DEPT(deptno,dname, loc) values(70, '회계', '서울');

SAVEPOINT SV2;

insert into DEPT(deptno,dname, loc) values(80, '회계', '서울');

SELECT * FROM DEPT;


ROLLBACK; -- 트랜잭션 생성자리로 감

ROLLBACK TO SV1; -- SV1 위치로 이동
SELECT * FROM DEPT;


-- COMMIT;

2. VIEW

2.1)개요

• 가상의 테이블
• 물리적으로 존재하지는 않지만, 테이블 처럼 사용이 가능하기 때문
• 목적

속도 : VIEW를 생성하는 구문은 메모리에 적재시켜 두고 실행

보안 : 사용자에게 VIEW를 제공하면 사용자는 테이블의 구조를 알 필요가 없습니다.

 

2.2) VIEW의 종류

• FROM 절에 작성한 서브 쿼리를 INLINE VIEW 라고 합니다.

하나의 SELECT 구문은 테이블 구조의 RESULT SET을 리턴

 

SELECT * FROM (SELECT 구문) 이름;

SQL 구문이 복잡해질 때 메모리 부담을 줄이기 위해서 사용

 

SELECT *

FROM EMP,DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO AND JOB = 'CLERK'

• SELECT 구문으로 만든 일반적인 VIEW

 

 

2.3) 뷰 생성 구문

CREATE [OR REPLACE] VIEW 뷰이름
AS
SELECT 구문
[WITH CHECK OPTION]
[WITH READ ONLY]

 

• MySQL은 버전에 따라서 OR REPLACE를 사용하지 못하는 경우가 있습니다.
• WITH CHECK OPTION : 뷰를 가지고 DML 작업을 할 때 SELECT 구문에서 조회된 데이터만 가능하도록 하는 옵션인데 데이터베이스에 직접 접속했을 때만 가능

데이터베이스에서 제공하지 않는 접속 도구를 사용하면 이 옵션은 수행되지 않습니다.

 

• WITH READ ONLY는 읽기 전용의 뷰를 생성하는 것

SELECT *

FROM EMP,DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO AND JOB = 'CLERK'


-- WHERE 절의 조건을 먼저 사용하여 걸러내었다!
-- INLINE VIEW 를 이용한 JOIN
SELECT  *
FROM (SELECT * FROM EMP WHERE JOB = 'CLERK') TEMP,DEPT
WHERE TEMP.DEPTNO = DEPT.DEPTNO;

 

 

2.4) 특징

 

 

• VIEW는 SELECT 구문을 가지고 있다가 호출하면 SELECT 구문을 수행해서 결과를 리턴합니다.

-- EMP 테이블에서 EMPNO, ENAME, SAL, COMM만으로 구성된 뷰를 생성
create VIEW KIM
AS
SELECT EMPNO, ENAME, SAL, COMM
FROM EMP;


-- VIEW는 테이블 처럼 사용한다
SELECT *
FROM KIM;

• VIEW에 DML 작업이 가능

VIEW를 만들 때 2개 이상의 테이블을 가지고 생성하거나 NOT NULL 컬럼을 제외하고 만든 경우에는 DML 작업이 안됩니다.

VIEW에 DML 작업을 수행하면 원본 테이블에 작업이 수행됩니다.

VIEW에 DML 작업을 수행하지 못하도록 할려면 만들 때 READ ONLY 옵션을 추가해야 합니다.

• 뷰는 구조 변경이 안된다. -> ALTER를 사용할 수 없다.

 

2.5) 뷰 삭제

 DROP VIEW 뷰이름;

 

 

3. 임시 테이블

3.1) 임시 테이블

 

• 일시적으로 테이블을 만들어서 사용하는 것인데 현재 세션 내에서만 접근이 가능하고 세션이 종료되면 소멸됩니다.
• 생성

CREATE TEMPORARY TABLE 테이블 이름(내용);

• 기존에 존재하는 테이블 이름과 동일한 이름을 사용할 수 있는데 이 경우 기존 테이블은 임시 테이블이 소멸될 때까지 사용할 수 없습니다.

-- 임시테이블 생성
-- 현재 세션에서만 존재하고, 세션 종료시 임시 테이블은 바로 삭제됨. 갖고놀기 좋음
CREATE TEMPORARY TABLE TEMP(
	NAME CHAR(20)	
);

SELECT * FROM TEMP;

 

 

3.2) CTE(Common Table Expressions)

 

• 쿼리 실행 중에 메모리에 존재하는 테이블
• 임시 테이블은 세션을 종료할 때 까지 존재하기 때문에 중간에 내용을 변경해서 사용할려면 삭제하고 다시 만들고 하는 작업을 수행해야 하고 뷰는 실제 데이터를 갖는 것이 아니고 select 구문을 가지고 있어서 연산의 중간 결과를 가지는 형태로 사용하기에는 속도가 느리다.
• SQL 작업 중간에 임시 결과를 저장하기 위한 용도 CTE를 사용

SQL 쿼리 수행 중에 데이터를 일시적으로 저장했다가 수행이 종료되면 자동으로 소멸됩니다.

• 기본 형식

WITH 테이블이름(컬럼 이름 나열) AS (SELECT 구문)
테이블 사용
                     컬럼 이름 나열 하는 부분을 생략하면 SELECT 구문의 컬럼 이름을 그대로 사용

• SELECT 구문이 복잡한 경우 동일한 SELECT 구문을 여러 번 입력해야 하는 경우가 있는데 이 경우 CTE를 사용하면 편
• 2개 이상의 CTE를 사용하고자 하는 경우 ,로 구분

-- CTE : SQL 수행 중에만 일시적으로 메모리 공간을 할당받아서 사용하는 테이블
-- SELECT 구문의 결과를 일시적으로 TEMP라는 테이블 이름을 부여해서 보관
-- CTE를 생성하는 구문은 가장 먼저 수행된다.
-- 이 와 유사한 작업을 INLINE VIEW를 할 수 있을 것 처럼 보이지만 
-- INLINE VIEW는 SUB Query 보다 늦게 수행되기 때문에 INLINE VIEW 는 Sub Query에 사용할 수 없음
WITH TEMP AS 
(SELECT NAME, SALARY, SCORE FROM tStaff WHERE DEPART ='영업부' AND GENDER = '남')
SELECT * FROM TEMP WHERE SALARY >= (SELECT AVG(SALARY) FROM TEMP);

 

 

4. Stored Procedure

 

4.1) 개요

 

• 자주 사용하는 구문을 함수처럼 하나로 묶어두고 이름만으로 사용하는 것
• 함수와 다른 점은 함수는 반드시 리턴을 해야 하지만 Stored Procedure는 리턴을 하지 않을 수도 있음
• 목적

속도 : 프로시저는 한 번 호출되면 메모리에 적재된 상태로 다음 수행을 하기 때문에 실행 속도가 빠름

보안 : 외부에서는 데이터베이스 내부 구조를 알지 못하더라도 작업을 수행하는 것이 가능

 

• 단점

데이터베이스 종류마다 생성 방식이 다름

• 포트폴리오를 만들때 ORM을 사용하지 않는다면 프로시저를 이용하는 것이 좋습니다.

 

4.2) 생성

DELIMITER 기호기호

             CREATE [OR REPLACE] 프로시저 이름()

             BEGIN

                               SQL 작성

             END 기호기호

DELIMITER;

 

 

4.3) 호출

CALL 프로시저 이름(매개변수 나열)

-- USERTBL 테이블 존재 여부 확인
SHOW TABLES;

DESC usertbl;

SELECT *
FROM usertbl;

DROP PROCEDURE myproc;
-- DELIMITER는 PROCEDURE 종료를 알리기 위한 기호를 설정하는 것인데 2개로 만드는 이유는 하나였을 경우에 데이터로 사용되는 것과 혼동이 올 수 있어서 이다.
-- DBeaver 에서 수행할 경우 SQL 스크립트 실행으로 실행.
DELIMITER //
CREATE PROCEDURE myproc(vuserid char(15), vname varchar(20), vbirthday int, vaddr char(100), vmobile char(11), vmdate date)
	begin
		insert into usertbl
		values(vuserid, vname, vbirthday, vaddr, vmobile, vmdate);
	end//
DELIMITER ;

CALL myproc('MANSIK', '정만식', 1974, '목포', '01011112222', '1974-12-11');

SELECT *
from usertbl;

 

 

 

5. Trigger

DML 작업이 발생했을 때 이 작업을 수행하지 않거나 이 작업이 수행되기 전이나 수행된 후에 다른 작업을 수행하도록 만드는 것

 

 

출처 : 송준호

• 생성

CREATE TRIGGER 이름
timing[BEFORE | AFTER] event[INSERT | UPDATE | DELETE]
ON 테이블이름

 

[FOR EACH ROW] -- 행단위로 수행인지 아니면 한번만 수행

[WHEN] 조건

BEGIN
                 수행할 내용
END;

• 내용 안에서 삽입되는 데이터나 수정될 값을 사용하고자 하는 경우는 NEW.컬러이름을 사용하면 되고 수정되기 전의 값이나 삭제되는 값을 사용하고자는 경우는 OLD.컬럼이름을 사용하면 됩니다.

 

• 권한 문제로 트리거 생성이 안되면 관리자 계정으로 접속해서 명령 수행하거나 관리자 계정에서 set global log_bin_trust_function_creators=on; 명령을 수행

-- EMP01 테이블에 데이터를 추가하면 SAL01 테이블에 데이터가 자동으로 추가되는 트리거

DELIMITER //
CREATE TRIGGER trg_01
AFTER INSERT ON EMP01
FOR EACH ROW
BEGIN 
	INSERT INTO SAL01(SAL, EMPNO) VALUES(100, NEW.EMPNO);
END //
DELIMITER ;

INSERT INTO EMP01 VALUES(1, '애덤', '프로그래머');

SELECT *
FROM EMP01;

SELECT *
FROM SAL01;