서브쿼리:delimiter/procedure/선언(declare)/호출(CALL)

UPDATE 뷰_이름 SET 컬럼명='값' WHERE 조건

아이디, 주소 넣어서 VIEW

INSERT 시, NULL 허용하지 않는 컬럼이라면, 반드시 데이터를 작성해야함.
기본 테이블에서 NULL 바꿔줘야함.

 

 

 

 

 

GROUP_CONCAT

-서로 다른 결과를 한 줄로 보여줄 때 사용

 

SELECT

*

FROM

  test_table;

SELECT

    음식분류

,   GROUP_CONCAT(음식이름)

FROM

    test_table

GROUP BY 음식분류;

 

GROUP_CONCAT(음식이름 SEPARATOR '&')

-->구분자가 쉼표에서 &로 변경

 

중복문자열 제거

GROUP_CONCAT(DISTINCT음식이름)

,

FROM

    test_table

GROUP BY 음식분류;

 

구매자 별 구매 이력 중, 구매 상품 수량 별 구매 수량이 20개 이상인 상품의 목록을 추출하여

다음과 같이 조회하시오

-CTE 사용하여 조회

-GROUP_CONCAT 사용

-GROUP BY, HAVING 사용

 

 

 

 

 

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

Stored Program

-내장식 프로그램, 프로그램 내장 방식, 저장 프로그램

-My SQL 전용언어.

*SQL 전용 언어: SQL 문에 변수, 제어문, 입출력 등 프로그래밍 기능을 추가해서 SQL문으로 처리하기 어려운 문제를 해결 할 수 있다.

 

특징

1)MySQL 성능 향상: 긴 쿼리의 내용을 전송하지 않고 프로시저의 이름과 매개 변수등 전송하여 네트워크 부하를 줄일 수 있다.

2) 유지 관리가 간편: 직접 SQL문을 작성하지 않고, 생성한 이름만 호출하여 일관된 작업을 진행 할 수 있다.

3)모듈식 프로그래밍: 저장 프로그램을 한번 생성해서 쿼리 수정, 삭제 등 관리가 수월하고, 무듈처럼 자주 사용하는 구문을 재사용할 수 있다.

4)보안 강화: 사용자 별 테이블에 접근 권한을 주지 않고, 저장 프로그램에 접근 권한을 줘서 보안을 강화한다.

 

저장 프로그램은 우리가 작성하는 프로그램 로직을 프로시저로 구현해서 사용한다. 일반적인 프로그래밍 언어에서 사용하는 함수와 비슷한 개념. 작업 순서가 정해진 독립된 프로그램의 수행 단위.

DBMS에 저장되기 때문에저장 프로그램이라고 부른다,

 

종류

1.저장 루틴

-프로시저

-함수

2. 트리거

3. 커서

 

1.스토어드 프로시저(Procedure)

Procedure: 절차

SQL문을 하나로 묶어서 편리하게 사용하는 기능

어떤 동작을 일괄 처리하기 위한 용도로 사용.

SQL문을 하나씩 매번수행하는 것이 아닌, 프로시저로 만들어 놓은 후, 호출하는 방식을 주로 사용한다.

 

생성

CREATE PROCEDURE 프로시저_이름

프로시저 이름도 관례적으로 sp_프로시저_이름

 

v_뷰이름

 

구성

선언부와 실행부로 구성.(BEGIN~END)

선언부 : 변수와 매개 변수를 선언한다.

실행부 : 프로그램 로직을 작성한다.

 

변수: 저장 프로시저나 트리거 안에서 사용되는 값

매개 변수(Parameter): 저장 프로시저가 호출될때, 호출되는 프로시저로 전달되는 값.

 

주석을 작성.

한줄: 이중 대쉬(--)

여러 줄: /**/

 

프로시저 형식

 

프로시저 묶어주기

DELIMITER $$

...

DELIMITER;

DELIMITER:구분문자

SQL문 작성했을 때, 종결 문자;

$$말고 다른 문자도 가능하지만, 다른 기호와 중복되지 않도록하고, 2개 연속해서 사용하는 것이 좋다.

 

형식

DELIMITER $$,@@,//
CREATE PROCEDURE sp_프로시저_이름(IN 또는 OUT Parameter)

BEGIN

SQL문 --> 프로시저 내용

END $$
DELIMITER ;

매개변수의 사용

IN입력매개변수 이름 데이터형식(IN은 생략 가능)

OUT 출력매개변수이름 데이터형식(OUT은 생략 불가xx ->작성하지 않으면 IN으로 인식됨)

 

프로시저 호출

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

 

OUT

OUT매개 변수는 출력 매개 변수에 값을 대입하기 위해 SELECT...INTO 구문을 사용한다.

SELECT...INTO구문은 MySQL에서 사용자가 정의한 변수에 SELECT에서 조회할 열에 값을 대입하기 위해 사용.

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

 

회원의 아이디와 비밀번호를 입력받아서 회원의 이메일을 조회하는 프로시저를 만드록, CALL해봐요

DROP PROCEDURE if EXISTS sp_if_test;

delimiter $$
CREATE PROCEDURE sp_if_test()
BEGIN
	DECLARE num INT; -- num이라는 변수 선언
	SET num := 300; -- 기호는 비교 연산 대입 연산 SET 다음에 작성하는 이퀄 기호는
	
	if num = 300 then 
		SELECT '300이 맞습니다 데헷!';
	ELSE
		SELECT '300이 아닙니다 뿌엥ㅠㅁㅠ';
	END if;
END $$
delimiter;

CALL sp_if_test;

회원의 아이디를 입력 받아 권한을 조회하는 프로시저를 작성해보세요.

입력값: 회원의 아이디

 

변수 선언 > 입력 값 = 변수에 대입하는 값을 비교

분기처리 분기처리(IF, ELSEIF THEN, ELSE...END IF)

 

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

1.JOIN으로 m.*member table의 내용만 확인 가능

 

2DELETE시, 컬럼 이름을 명시할 필요없는데, tb_goods의 내용을 지운다 >AS g

컬럼 자리에 g라고 명시하면, g에 해당하는 내용을 삭제할 수 있다. 컬럼 이름적으면 에러 발생

 

3. 가장 중요한 내용은 UPDATE DELETE 전, SELECT 를 반드시 진행한다.

 

IF조건문을 사용하여, 회원의 아이디를 입력받으면 모든 테이블의 내용이 삭제되도록 프로시저를 작사ㅓㅇ하시오. 단, 회원 레벨 1은 삭제 할 수 없음

DROP PROCEDURE IF EXISTS sp_mem_all_delete;

DELIMITER $$
CREATE PROCEDURE sp_mem_all_delete(IN memId VARCHAR(50))
BEGIN
    DECLARE memLv INT;

    SELECT
        m.m_level INTO memLv -- tb_member에서 조회한 회원 레벨을 memLv에 대입
    FROM
        tb_member AS m
    WHERE
        m.m_id = memId;

    IF(memLv <> 1) THEN -- 1이 아니라면
        IF(memLv = 2) THEN -- 2인 경우(판매자)
            DELETE
                o
            FROM
                tb_order AS o
                INNER JOIN
                tb_goods AS g
            WHERE
                g.g_seller_id = memId;

            DELETE
                g
            FROM
                tb_goods AS g
            WHERE
                g.g_seller_id = memId;
        END IF;

        IF(memLv = 3) THEN -- 3인 경우(구매자)
            DELETE
                o
            FROM
                tb_order AS o
            WHERE
                o.o_id = memId;
        END IF;

        -- member 테이블을 삭제하기 위해 로그인 테이블을 먼저 삭제
        DELETE
            l
        FROM
            tb_login AS l
        WHERE
            l.login_id = memId;

        DELETE
            m
        FROM
            tb_member AS m
        WHERE
            m.m_id = memId;
    END IF;
END $$
DELIMITER ;

-- 프로시저 호출
CALL sp_mem_all_delete('id008');