개발공부 기록지

<스토어드 프로시저 (stored procedure)>

DELIMITER $$

CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
	'SQL 프로그래밍 코딩'
END -- 책에는 END $$ 이지만 나의 MySQL 8.0.0 버전에서 에러 발생

DELIMTER ;

CALL 스토어드_프로시저_이름(); -- 호출

IF 문

DROP PROCEDURE IF EXISTS ifProc1;

DELIMITER $$

CREATE PROCEDURE ifProc1()
BEGIN
	IF 100 = 100 THEN
    	SELECT '100은 100과 같습니다.'; -- 문자 출력
    END IF;
END

DELIMITER ;

CALL ifProc1();

▶ IF ~ ELSE

DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER $$

CREATE PROCEDURE ifProc2()
BEGIN
	DECLARE myNum INT;
    SET myNum = 200;
	IF myNum = 100 THEN
    	SELECT '100입니다.';
    ELSE
    	SELECT '100이 아닙니다.';
    END IF;
END 

DELIMITER ;

CALL ifProc2();

▶ IF 문 활용

DROP PROCEDURE IF EXISTS ifProc3;

DELIMITER $$

CREATE PROCEDURE ifProc3()
BEGIN
	-- 	변수 3개
	DECLARE debutDate DATE;
    DECLARE curDate DATE;
    DECLARE days INT; 
    
    SELECT debut_date INTO debutDate 
		FROM market_db.member
		WHERE mem_id = 'APN';
        
	SET curDate = CURRENT_DATE(); -- 현재 날짜 저장
    SET days = DATEDIFF(curDate, debutDate); -- 일 단위 날짜 차이
    
	IF (days/365) >= 5 THEN -- 5년 지났다면
    	SELECT CONCATE('데뷔한 지', days, '일이나 지났습니다. APN 축하합니다!');
    ELSE
    	SELECT '데뷔한 지' + days + '일빡에 안되었네요. APN 화이팅~';
    END IF;
END

DELIMITER ;

CALL ifProc3();

< 날짜 관련 함수>

• CURRNET_DATE() : 오늘 날짜를 알려주는 함수
• CURRENT_TIMESTAMP() : 오늘 날짜 와 시간을 알려주는 함수
• DATEDIFF(날짜1, 날짜2) : 날짜2 부터 날짜1 까지 몇 일 인지 알려주는 함수

CASE 문

DROP PROCEDUER IF EXISTS caseProc;

DELIMITER $$

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

DELIMITER ;

CALL caseProce();

▶ case 문 활용

    
SELECT m.mem_id, m.mem_name, SUM(b.price * b.amount) '총구매액' 
	CASE
    	WHEN (SUM(b.price * b.amount) >= 1500) THEN '최우수고객'
        WHEN (SUM(b.price * b.amount) >= 1000) THEN '우수고객'
        WHEN (SUM(b.price * b.amount) >= 1) THEN '일반고객'
        ELSE '유령고객'
    END '회원등급'
        
    FROM buy b
        RIGHT OUTER JOIN members m
        ON b.mem_id = m.mem_id
    GROUP BY m.mem_id
    ORDER BY SUM(price* amount) DESC ;

WHILE 문

DROP PROCEDURE IF EXISTS whileProc;

DELIMITER $$

CREATE PROCEDURE whileProc()
BEGIN
	DECLARE i INT;
    DECLARE hap INT;
    SET i = 1;
    SET hap = 0;

    WHILE (i <= 100) DO
        SET hap = hap + i;
        SET i = i + 1;
    END WHILE;
    
    SELECT '1 부터 100까지의 합 => ' , hap;
END

DELIMITER ;

CALL whileProc();

▶ while 문 활용

DROP PROCEDURE IF EXISTS whileProc2;

DELIMITER $$

CREATE PROCEDURE whileProc2()
BEGIN
	DECLARE i INT;
    DECLARE hap INT;
    SET i = 1;
    SET hap = 0;
    
    myWhile: -- WHILE 문을 myWhile이라는 레이블로 지정
    WHILE (i <= 100) DO
    	IF (i%4 = 0) THEN
        	SET i = i + 1;
            ITERATE myWhile; -- 지정한 레이블(label)인 myWhile로 가서 계속 진행
        END IF;
        SET hap = hap + i;
        IF (hap > 1000) THEN
            LEAVE myWhile; -- 지정한 레이이블 myWhile을 빠져나간다. WHILE 종료
        END IF;
        SET i = i + 1;
    END WHILE;
    
    SELECT '1 부터 100 까지의 합(4의 배수 제외), 1000 넘으면 종료 => ' , hap;
END

DELIMITER ;

CALL whileProc2();

동적 SQL

-- 출입용 테이블 생성, id 는 자동으로 증가
DROP TABLE IF EXISTS gate_table;

CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

SET @curDate = CURRENT_TIMESTAMP();

PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)'; -- 실행할 SQL문 준비
EXECUTE myQUery USING @curDate -- @curDate에 있는 값을 ?에 전달(입력)
DEALLOCATE PREPARE myQuery; -- ? 에 입렵된 값 지우고 같은 sql 문으로 준비

SELECT * FROM gate_table;

내부 조인의 기본 ( INNER JOIN)

SELECT <열 목록>
FROM <첫 번째 테이블>
	INNER JOIN <두 번째 테이블>
    ON < 조인될 조건>
[WHERE <검색조건>]
[ORDER BY <기준>]

예시

USE market_db;
SELECT b.mem_id , m.mem_name, b.prod_name, m.addr, CONCAT(m.phone1, m.phone2) '연락처' 
FROM buy b 
INNER JOIN member m  
ON b.mem_id = m.mem_id
ORDER BY m.mem_id;

외부조인 (OUTER JOIN)

SELECT [컬럼 이름]
FROM [테이블 A 이름]
LEFT JOIN [테이블 B 이름]
ON [테이블 A 이름].[컬럼 A 이름] = [테이블 B 이름].[컬럼 B 이름] 
UNION --두 쿼리의 결과를 중복을 제외하고 합쳐서 보여주는 집합연산자
SELECT [컬럼 이름]
FROM [테이블 A 이름]
RIGHT JOIN [테이블 B 이름] ON[테이블A이름].[컬럼A이름]=[테이블B이름].[컬럼B이름];

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

• LEFT OUTER JOIN ( LEFT JOIN) : 왼쪽 테이블을 기준으로 외부 조인 (왼쪽 테이블의 내용은 모두 출력되어야 한다)
• FULL OUTER JOIN : 왼쪽이든 오른쪽이든 한쪽에 들어 있는 내용이면 출력

예시

SELECT b.mem_id , m.mem_name, b.prod_name, m.addr
	FROM `member` m 
		LEFT OUTER JOIN buy b 
		ON m.mem_id = b.mem_id 
	WHERE b.prod_name IS NULL 
	ORDER BY m.mem_id ;

기타 조인

상호조인 (CROSS JOIN):  한쪽 테이블과 다른 쪽 테이블의 모든 행을 조인 시키는 기능

SELECT *
FROM buy
CROSS JOIN memeber;

자체조인 (SELF JOIN):  자신의 테이블에서 컬럼끼리 조인

SELECT < 열 목록>
FROM <테이블> 별칭1
INNER JOIN <테이블> 별칭2
ON <조인될 조건>
[WHERE 검색 조건]

예시

SELECT A.emp "직원", B.emp "직속상관", B.phone "직속상관연락처"
	FROM emp_talbe A
    	INNER JOIN emp_table B
        ON A.manager = B.emp
    WHERE A.emp = '경리부장';

↓

명시적인 변환 (explicit conversion)

CAST( 값 AS 데이터형식(데이터길이)) -- 데이터형식 : SIGNED, UNSIGNED 도 가능
CONVERT(값, 데이터형식(데이터길이)) -- 데이터형식 : SIGNED, UNSIGNED 도 가능

예시

SELECT CAST(2023%08%04 AS DATE); # 2023-08-04
SELECT CAST(AVG(price) AS SIGNED); # 정수로 출력 예) 142.8 -> 143출력
SELECT num, CONCAT(CAST(price as CHAR), 'X', CAST(amount AS CHAR), '=')
	FROM buy; # 출력: 30x3=

• CONACT():  문자를 이어주는 함수

암시적인 변환 (implicit conversion)

SELECT '100' + '200'; # 300, 문자여도 더하기만 하면 숫자로 변환해서 연산
SELECT CONCAT('100','200') # 100200, 숫자도 문자로 변환됨