MariaDB#11 : 조인 join(내부, 외부), 스토어드 프로시저 (221229)7

조인

 

- 두 개 이상의 테이블을 묶어 연관된 데이터를 가지고 오는 것.

 

---

내부 조인(INNER JOIN)

- 가장 많이 사용

- 교집합으로 두 키 값이 매칭 될 경우 선택된 모든 열을 출력하는 방법

- 일반적으로 JOIN 은 내부 조인

-    FROM 테이블1

     INNER JOIN 테이블2 ON 조건

         : 조건에 맞춰 테이블1과 테이블2를 합치는 것

SELECT <열목록>
FROM <첫번쨰table>
	INNER JOIN <두번째table>
    ON <조인될조건>
[WHERE 검색조건]

USE ssdb;
SELECT U.userID, U.userName, B.prodName, CONCAT(U.mobile1, U.mobile2) AS '핸드폰번호'
FROM userT U
INNER JOIN buyT B ON U.userID = B.userID
ORDER BY U.userID;

- 관계형성이 (외래키로 인하여) 되어야만 INNER JOIN 가능 -> 자식쪽 userID가 삭제됨(중복이니까)

USE sqlDB;
SELECT DISTINCT U.userID, U.userName, U.addr
FROM usertbl U
INNER JOIN buytbl B ON U.userID = B.userID
ORDER BY U.userID;

- SELECT문에 DISTINCT를 사용하여 중복을 없애준다.(회원 명단을 간단히하는데 사용)

- 3개 테이블 조인 : 다대다 관계. 두 테이블 사이에 연결 테이블을 두어서 1:N 조인을 할 수 있도록 처리

                              결국 2번의 내부 조인

 

---

 

외부 조인(OUTER JOIN)

출처 : [SQL 기초] 벤다이어그램으로 보는 JOIN : 네이버 포스트 (naver.com)

- 내부조인에서 추가로 조인 조건을 만족하는 row와 만족하지 않는 row까지 가지고 오는 방법 (합집합)

SELECT <열 목록>
FROM <첫번째테이블(LEFT테이블)>
	<LEFT | RIGHT | FULL> OUTER JOIN <두번째테이블(RIGHT테이블)>
    	ON <조인조건>
[WHERE 검색조건] ;

- LEFT, RIGHT, FULL

- LEFT OUTER JOIN : 왼쪽 테이블의 데이터를 기준으로 키 값에 맞는 오른쪽 테이블에 있는 데이터를 붙여 데이터를 출력

- RIGHT OUTER JOIN은 오른쪽 테이블을 기준으로 왼쪽 테이블의 데이터가 붙도록 출력

- FULL OUTER JOIN은 두 테이블의 합집합과 같으며 왼쪽 테이블과 오른쪽 테이블의 모든 열들을 나열하여 출력

      UNION예약어로 FULL JOIN

 

---

 

스토어드 프로시저

DELIMITER $$
CREATE PROCEDURE 스토어드프로시저이름()
BEGIN
	SQL코딩...
END $$
DELIMITER ;
CALL 스토어드프로시저이름() ;

- DELIMITER $$ ~ END $$까지 코딩할 부분..

- 문의끝(종료문자)를 $$로 설정하여 구별 편하게한것

- 프로시저 안에서 변수선언은 '@' 없이 DECLARE로 대체하여 진행.

 

IF ... ELSE ...

- bool표현식에 따라 분기

IF <bool표현식> THEN
	SQl문1...
ELSE
	SQL문2...
END IF;

- 한 문장 이상이 처리되어야 할 때는 BEGIN... END와 함께 묶어줄 것

- IF로 열면 반드시 END IF;로 닫아주어야 한다.

 

DROP PROCEDURE IF EXISTS proFunc;

DELIMITER $$
	CREATE PROCEDURE proFunc()
	BEGIN
        DECLARE var1 INT; 	-- var1 변수선언
        SET var1 = 10; 		-- 변수에 값 대입
        IF var1 = 10 THEN 	-- 만약 @var1이 10이라면,
    		SELECT '10!!!';
        ELSE
        	SELECT '10이 아님';
        END IF;
	END $$
DELIMITER ;
CALL proFunc();

- 다중if-else구문에서 elseif는 붙여서 사용하기 IF... THEN... ELSEIF... THEN... ... END IF;

 

CASE문

DROP PROCEDURE IF EXISTS caseProc;

DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
	DECLARE POINT INT;
	DECLARE credit CHAR(1);
	SET POINT = 77;
	
	CASE
	WHEN POINT >= 90 THEN
		SET credit = 'A';
	WHEN POINT >= 80 THEN
		SET credit = 'B';
	WHEN POINT >= 70 THEN
		SET credit = 'C';
	WHEN POINT >= 60 THEN
		SET credit = 'D';
	ELSE
	SET credit = 'F';
	END CASE;
	
	SELECT CONCAT('취득점수 : ', POINT), CONCAT('학점 : ', credit);
END $$
DELIMITER ;
CALL caseProc();

- CASE WHEN ... THEN ... ... ELSE ... END CASE;

 

WHILE 문

- bool식이 true이면 계속 반목.

WHILE <bool식> DO
	SQL문...
END WHILE;

- ITERATE문 : CONTINUE

- LEAVE문 : BREAK

 

오류처리

DECLARE 액션 HANDLER FOR 오류조건 처리할문장 ;

- 액션 : 오류 발생 시의 행동 정의

            CONTINUE와 EXIT 둘 중 하나 사용

            CONTINUE가 나오면 제일 뒤 처리할문장 이 처리된다.

- 오류조건 : 어떤 오류를 처리?

             MariaDB의 오류코드숫자

             SQLSTATE '상태코드' -> 5자리의 문자열

             SQLEXCEPTION : 대부분의 오류

             SQLWARNING : 경고 메세지

             NOT FOUND : 커서나 SELECT ... INTO에서 발생

- 처리할문장 : 문장이 여러개면 BEGIN ... END로 묶어준다. 

             SHOW ERRORS문 : 오류에 대한 코드와 메세지 출력

             ROLLBACK : 진행중인 작업 취소

             COMMIT 은 작업 완전히 확정

 

동적 SQL

- 미리 쿼리문을 준비하고, 나중에 실행

- PREPARE : 미리준비

- EXECUTE : 준비한 쿼리 실행

- DEALLOCATE PREFARE : 실행하고 문장 해제