SQL:select...into문

 

select...into문

IN, OUT

 

OUT 매개변수 사용 시, 출력 매개변수의 값을 대입하기 위해서 SELECT...INTO문

MySQL에서 사용자가 정의한 변수에 SELECT에서 조회할 열에 값을 대입하기 위해 사용하며,

주로 프로시저에서 사용한다.

 

BEGIN

   DECLARE num1 INT;

   SELECT

        member_age INTO num1

--태ㅔ스트 테이블에서 조회할 member_age을 위해서 선언한 num1이라는 변수에 대입하겠다.

   FROM

         tb_test

END

 

OUT

goods table 평균 가격

ROUND(AVG(g.g_price),0)

========================

CASE

-IF문 참 또는 거짓만 존재. 이중분기라고 부른다.

CASE문은 여러조건을 비교할 수 있다. 그래서 다중 분기라는 용어를 사용한다.

 

구매자 별 주문 금액에 따라 회원 등급을 분류하고, 구매자 아이디, 구매자 이름, 총 주문 금액, 해당 구매자의 드급을 조회하시오. 그리고 총 주문 금액 내림 차순으로 정렬하시오.

 

등급 분류

총 구매액 >= 3천만원 VIP 등급 고객

총 구매액 >= 2천만원 Diamond 등급 고객

총 구매액 >= 1천만원 Gold 등급 고객

총 구매액 >= 800만원 Silver 등급 고객

총 구매액 >= 500만원 Bronze 등급 고객

기타: 일반 회원입니다.

 

그냥 SELECT

(CASE~END) AS '조회할 열 이름'

 

 

==============================================

WHILE

-여러 번 반복하는 WHILE문

-WHILE은 해당 조건식이 참일 경우, 반복.

 

구문

WHILE(조건식) DO

   SQL문

END WHILE;

 

1~100까지 숫자를 더하는 WHILE

 

===============

스토어드 함수(Stored Function)

-사용자가 직접 함수를 만들어서 사용한다.

-내장함수가 사용자를 만족하는 모든 함수를 제공하지 않는다. 필요에 의해 사용자가 직접 함수를 만들어 사용한다.

-형태와 사용 용도에서 스토어드 프로시저와 차이가 있다.

 

단점

-유지보수 복잡성 증가: 각 기능을 담당하는 프로그램 코드가 자바와 MySQL스토어드 프로그램으로 분산되어 관리되기 때문에 애플리케이션의 설치나 배포가 복잡해진다.

 

스토어드 프로시저와 스토어드 함수의 차이

-스토어드 함수는 IN,OUT파라미터를 사용할 수 없다. 스토어드 함수의 파라미터는 모두 입력 파라미터로 사용된다.

-함수는 RETURNS예약어를 통해 반환할 값의 데이터 형식을 지정하고, 본문 안에서 RETURN문으로 하나의 값을 반환해야 한다.

-프로시져 CALL로 호출하는 독립적인 프로그램, 함수는 SELECT문 안에서 호출, 값을 제공하는 용도로 사용.

 

스토어드 프로시저 안에서 SELECT문을 사용했지만, 함수는 집합 결과를 반환하는 SELECT문을 사용할 수 없다. 단, 하나의 결과만 반환하는 SELECT는 사용가능.

-어떤 계산을 통해 하나이 값을 반환하는데 주로 사용한다.

 

정의 및 호출

CREATE FUNCTION 스토어드_함수_이름(매개 변수)

-> 모두 입력 매개 변수로 사용하니까, IN생략

 

RETURNS 반환_형식(type)

->RETURNS문으로 반환할 값의 데이터 형식 지정

 

BEGIN

   프로그래밍 코드...

   RETURN 반환 값;

END $$

 

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

->스토어드 함수는 SELECT문 안에서 호출;

 

삭제

DROP FUNCTION 스토어드_함수_이름;

 

*sf_이름;

 

함수 사용 전, 생성 권한을 허용해야함.

SET GLOBAL log_bin_trust_function_creators =1;

 

만나이 함수

 

======================

Trigger

트리거 방아쇠, 방아쇠 당기면 총알 나가죠

트리거는 해당 테이블에 어떤 테이블(데이터 변경문) 발생하면 자동으로 실행된다.

 

트리거: 테이블에 부착한다.

이벤트: 테이블에 데이터 삽입, 수정, 삭제가 발생하면

해당 테이블에 부착되어 있는 트리거가 자동으로 실행.

 

회원 > 탈퇴 > 탈퇴 회원

5명 탈퇴  > 탈퇴 회원 4명만 옮겼어요.

1명 --> 데이터 오류 발생 방지 > 데이터의 무결성

 

이벤트(데이터 변경): INSERT, UPDATE, DELETE

트리거 타임(발동 시점): AFTER, BEFORE

 

특징

-프로시저와 다르게 직접 실행할 수 없다. 해당 테이블에 이벤트(DML)가 발생하면, 자동 실행.

-IN, OUT 매개 변수를 사용할 수 없다.

-이벤트 발생 후, ROLLBACK할 수 없다.

 

트리거 사용 전, root계정에서 다음 SQL문으로 실행

SET GLOBAL log_bin_trust_function_creators = 1;

 

형식

CREATE TRIGGER 트리거_이름

트리거_타임 -- AFTER, BEFORE

트리거_이벤트 -- INSERT, UPDATE, DELETE

*예시: AFTER INSERT --> INSERT후, 발동

ON 테이블_이름 -- 트리거를 부착할 테이블

ON

FOR EACH ROW

--각 행마다 적용하겠다. 트리거 사용하면 항상 작성.

 

BEGIN

   트리거 명령문

-- SET @msg ='테이블에 회원정보가 등록됨'

END

 

트리거 삭제

DROP TRIGGER 트리거_이름;

 

====================================

1.navigator

-MySQL의 관리 및 운영을 위한 도구

-대부분의 작업이 가능하고, MySQL의 명령문이나 SQL문을 몰라도 대부분 작업 수행 가능

 

Administration

1)Management

-MySQL 서버의 가동상태, 설치된 폴더 등 확인

-MySQL서버에 연결되어 있는 클라이언트 정보 확인

-사용자 생성, 삭제, 권한 관리

-데이터 내보내기 / 가져오기 등

2)Instance

-MySQL 연결 정보를 관리

-인스턴스 중지 또는 시작

-Server에 기록된 로그 조회

-옵션 파일 설정 정보 확인 및 변경 등

3)Performance

- 성능 상태 및 구성 관련 메뉴

 

Schmas

-데이터베이스 생성 및 삭제

-데이터베이스 캐체(테이블, 뷰, 인덱스, 스토어드 프로시저 등등) 생성, 관리

-데이터베이스 속성 조회

 

===================================

AFTER DELETE TRIGGER

1.member table 컬럼 4개만 가지고 테이블 복사

2.백업 > 탈퇴 > 데이터가 저장되는 테이블

3.복사한 테이블 DELTE

4. 백업 타이블에 데이터가 기록.

 

CREATE ~ SELECT

테이블을 복사해서 새로운 테이블을 만든다. 단, 기본 키에 대한 복사가 

 

========================

OLD, NEW

-테이블에 INSERT, UPDATE, DELETE작업이 수행되면, 임시로 사용되는 테이블

-OLD.m_id 사용자가 직접 생성하는 테이블 아닌, MySQL에서 알아서 생성하고 관리하주는 테이블.

 

NEW테이블

-테이블에 새로운 값이 들어가는 경우(INSERT)

 

OLD 테이블

-예전 값 DELETE 작동

-삭제 전, 예전 값이 OLD테이블에 잠시 저장.

 

UPDATE는 둘 다 사용.

======================================

트랜잭션(시험에 나옴)

-하나의 작업을 수행하기 위해 필요한 데이터베이스 연산을 모아놓은 것

-작업 수행에 필요한 SQL문의 모임

-장애 발생 시, 복구 작업이나 병행 제어 작업을 위한 중요한 단위로 사용.

-데이터베이스의 무결성과 일관성을 보장하기 위해 작업수행에 필요한 연산들을 하나의 트랜잭션으로 제대로 정의하고 관리해야 한다.

 

DBMS가 데이터베이스를 다룰때, 사용하는 작업(프로그램)의 단위.

데이터의 무결성을 유지하기 위해 원자성, 일관성, 고립성, 지속성의 성질을 갖습니다.

 

트랜잭션의 예시

최대한(국비) > 이혜영 1만원을 송금한다.

-최대한(국비)의 계좌에서 -1만원

-이혜영 계좌에 +1만원

---------------------------------------------------------

구문

START TRANSACTION

    1. 최대한(국비)의 계좌에서 -1만원 UPDATE문

    2. 이혜영 계좌에 +1만원 UPDATE문

COMMIT

최대한(국비)> UPDATE > 다른 UPDATE같이 실행 > 인출 여러 번 진행.

1번과 2번 모두 수행되거나, 또는 아예 수행되지 않아야 한다. 

 

개념

보통 단일 SQL문을 다루기도 하지만, 여러 개의 SQL문을 순차적으로 수행하기도 한다. 왜 정의하느냐?

1.데이터베이스에서 데이터를 조작할 때, 장애가 발생하는 경우가 있다. 트랜잭션은 장애 발생시, 데이터를 복구하는 작업 단위가 된다.

 

2.데이터베이스에서 여러 작업이 동시에 같은 데이터를 다루며 진행될 때, 트랜잭션은 이 작업을 서로 분리하는 단위가 된다.

트랜잭션의 특징 - ACID

원자성 - Automicity

일관성 - Consistency

독립성(격리성) - Isolation

영속성(지속성) Durability

 

원자성 - Automicity

-트랜젝션이 더 이상 쪼개지지 않는 하나의 프로그램 단위로 동작해야 한다.

-일부만 수행되는 일이 없도록 전부 수행 또는 아예수행 하지 않아야 한다.

일관성 - Consistency

-트랜젝션은 데이터베이스의 일관성을 유지해야 한다.

독립성(격리성) - Isolation

-여러 트랜잭션이 동시에 수행될때, 각 트랜잭션은 다른 트랜잭션의 방해를 받지않고, 독립접으로 작업을 수행해야한다.

 

영속성(지속성) Durability

트랜잭션이 정상적으로 완료 또는 부분 완료한 데이터는 반드시 데이터베이스에 기록되어야한다.

 

수행 과정

성공:수행 시작 > 수행 > 부분 완료 > COMMIT > 완료

실패:수행 시작 > 수행 > 실패 > ROLLBACK > 철회

 

트랜잭션의 상태

활동 상태: 트랜잭션이 현재 수행 중인 상태

부분 완료: 트랜잭션의 마지막 연산이 실행을 종료한 직후의 상태

 

완료: 수행 > DBMS가 데이터베이스에 기록(COMMIT)을 완료해야 정상 수행. 문제가 발생해서, DBMS가 변경 내용을 데이터베이스에 기록하지 못하면, 실패 상태.

 

3)완료: 트랜잭션이 성공적으로 완료되어 COMMIT 연산을 실행한 상태. > 데이터베이스에 최종 결과를 반영한 상태

 

4)실패

트랜잭션을 중간에 중단하였거나, 부분 완료 상태에서 변경 내용을 데이터베이스에 저장하지 못한 상태. > 수행한 작업을 모두 복구 진행.

 

5)철회

트랜잭션의 수행 실패 ROLLBACK 연산을 진행한 상태.

 

기본 구조

START TRANSACTION

          ...여기 작성하는 SQL문이 하나의 명령어처럼 실행된다.

(정상적으로 완료) COMMIT; / ROLLBACK;

 

 

 

 

 

UPDATE진행

TRIGGER > UPDATE TRIGGER

 

SAVEPOINT 이름;

 

//1.회원 정보 INSERT

..SQL..

 

//2.회원 누구 정보 삭제

..SQL..

 

//3.

succ: commit

 

R

 

SAVEPOINT는 여러 개 작성할 수 있음

-- autocommit 확인
SELECT @@AUTOCOMMIT;

--1(ON)이라면, 0으로 바꿔준다.SET @@AUTOCOMMIT :=0;

START TRANSACTION;
    SELECT
      *
    UPDATE
      tb_member2;
    DELETE
      tb_member2
    SET
 --savepoint 지정하기
    SAVEPOINT A;
    DELETE
    FROM
      tb_member2
    WHERE
      m_id = 'id013'
    ROLLBACK TO SAVEPOINT A; -- 부분철회
    COMMIT;
--트랜잭션 작업 완료 후, autocommit을 다시 1로 변경(true)
SET @@AUTOCOMMIT := 1; -- trueSELECT @@AUTOCOMMIT;