SQL 고급 문법

yunssup·2025년 3월 7일
post-thumbnail

적절한 데이터 형식 지정 = 효율적인 SQL

📁 변수 선언

SET @변수이름 = 변수의 값;
SELECT @변수이름;

변수는 워크벤치 종료 시 사라짐.
LIMIT에서는 변수 사용 불가능 -> PREPARE과 EXECUTE로 해결 가능

SET @COUNT = 3;
PREPARE MYSQL FROM 'SELECT MEM_NAME, HEIGHT FROM MEMBER ORDER BY HEIGHT LIMIT ?';
EXECUTE MYSQL USING @COUNT;

❗동적 SQL

  • PREPARE : 'SELECT ~~ LIMIT'문을 실행하지 않고 MYSQL로 준비만 해둠. ?는 현재는 모르지만 나중에 채워질 내용이라고 생각하기
  • EXECUTE : MYSQL에 저장된 SELECT문을 실행할 때, USING으로 ?에 @COUNT 변수의 값을 대입하는 것
--결론적으로
SELECT MEM_NAME, HEIGHT FROM MEMBER ORDER BY HEIGHT LIMIT 3

📁 데이터 형 변환

명시적 형 변환

CAST (값 AS 데이터_형식 [길이])
CONVERT (값, 데이터_형식 [(길이))
USE MARKET_DB;
SELECT CAST(AVG(PRICE) AS SIGNED) '평균 가격' FROM BUY;
SELECT CONVERT(AVG(PRICE) , SIGNED) '평균 가격' FROM BUY;
  • CAST 혹은 CONVERT를 이용하여 변환 가능!
  • CAST와 CONVERT 내에 올 수 있는 데이터 형식 = CHAR, SIGNED, UNSIGNED, DATE, TIME, DATETIME
  • SIGNED는 부호가 있는 정수를 의미. UNSIGNED는 부호가 없는 정수 의미
SELECT NUM, 
       CONCAT(CAST(PRICE AS CHAR), 'X', CAST(AMOUNT AS CHAR), '=') AS `가격*수량`, 
       PRICE * AMOUNT AS `구매액`
FROM BUY;
  • 가격과 수량은 정수지만 CAST() 함수를 이용해 문자로 변경
  • CONCAT() 함수로 문자 이어주기
  • AS 별칭에서 한글이나 특수문자를 사용할 땐 따옴표가 아닌 백틱(`) 사용하기 !

암시적 형 변환

  • CONCAT() 함수 이용

📁 내부 조인

  • 두 테이블을 연결할 때 가장 많이 사용되는 것
  • 두 테이블에 모두 있는 내용만 출력
  • 일대다 관계로 연결하기
    • 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러 개의 값이 존재할 수 있는 관계
    • 주로 기본 키(PK)와 외래 키(FK) 관계
  • 내부 조인 형식
SELECT 열 목록
FROM 첫번째 테이블
	INNER JOIN 두번째 테이블
    ON 조인될 조건
WHERE 검색 조건

예시

USE MARKET_DB;
SELECT * 
FROM BUY
INNER JOIN MEMBER
ON BUY.MEM_ID = MEMBER.MEM_ID
WHERE BUY.MEM_ID='GRL';

🖱️코드 실행 과정
1) BUY 테이블에서 MEM_ID가 GRL을 추출하기
2) GRL과 동일한 값을 MEMBER 테이블의 MEM_ID 열에서 검색하기
3) GRL 아이디를 찾으면 두 테이블을 조인하기

❗주의 사항 : 두 개의 테이블을 조인하는 경우 동일한 열 이름이 존재한다면 꼭 테이블 이름.열_이름 형식으로 표기하기

📁 외부 조인

  • 필요한 내용이 한 쪽에만 있어도 결과 추출 가능
  • 외부 조인 형식
SELECT 열 목록
FROM 첫번째 테이블(LEFT 테이블) 
	LEFT|RIGHT|FULL OUTER JOIN 두번째 테이블(RIGHT 테이블)
    ON 조인될 조건
WHERE 검색 조건

예시)

SELECT *
FROM MEMBER M
	LEFT OUTER JOIN BUY B
    ON M.MEM_ID = B.MEM_ID
ORDER BY M.MEM_ID;
  • 왼쪽에 있는 회원 테이블을 기준으로 외부 조인
  • LEFT OUTER JOIN은 왼쪽 테이블의 내용은 모두 출력되어야 한다로 해석!

📁 기타 조인

상호 조인 (카티션 곱)

  • 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능.
  • 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수
SELECT *
FROM BUY
	CROSS JOIN MEMBER;

❗주의 사항 : ON 구문 사용 불가. 랜덤으로 조인하기에 결과의 내용은 의미가 없음. 테스트를 위한 대용량 데이터 생성 시 사용

자체 조인

  • 자신이 자신과 조인. 따라서 1개의 테이블 사용
SELECT 열 목록
FROM 테이블 별칭 A
	INNER JOIN 테이블 별칭 B
    ON 조인될 조건
WHERE 검색 조건

📁 스토어드 프로시저

  • 기본 형식
DELIMITER $$
CREATE PROCEDURE
BEGIN

END $$
DELIMITER;
CALL

📁 IF 문

  • 기본 형식
IF 조건식 THEN
	SQL 문장들
END IF;

SQL 문장이 한 문장이라면 위와 같이 쓰지만, 두 문장 이상일 경우 BEGIN ~ END로 묶어주기 

USE MARKET_DB;
DROP PROCEDURE IF EXISTS IFPROC1; -- 기존에 IFPROC1()을 만들었다면 삭제
DELIMITER $$
create PROCEDURE IFPROC1() -- 스토어드 프로시저 이름 설정
BEGIN 
	IF 100=100 THEN
    select '100은 100과 같습니다.';
    END IF;
END $$
DELIMITER ;
CALL IFPROC1();

📁 IF~ELSE 문

USE MARKET_DB;
DROP PROCEDURE IF EXISTS IFPROC1; -- 기존에 IFPROC1()을 만들었다면 삭제
DELIMITER $$
create PROCEDURE IFPROC1() -- 스토어드 프로시저 이름 설정
BEGIN 
	declare MYNUM INT; -- declare 예약어로 변수 선언, 데이터 형식도 지정
    SET MYNUM = 200; -- SET 예약어로 변수에 200 대입
    IF MYNUM = 100
		THEN SELECT '100입니다.';
	ELSE
		SELECT '100 아닙니다.';
	END IF;
END $$
DELIMITER ;
CALL IFPROC1();

IF문 활용 예시

USE market_db;

DROP PROCEDURE IF EXISTS IFPROC3;
DELIMITER $$

CREATE PROCEDURE IFPROC3()
BEGIN 
    DECLARE DEBUTDATE DATE; -- 데뷔 날짜
    DECLARE CUR_DATE DATE; -- 오늘 날짜
    DECLARE DAYS INT; -- 총 활동 일수

    -- 특정 회원의 데뷔 날짜 조회
    SELECT DEBUT_DATE INTO DEBUTDATE -- INTO 변수를 통해 DEBUT_DATE를 추출해서 DEBUTDATE에 저장
    FROM market_db.MEMBER
    WHERE MEM_ID = 'APN';

    -- 현재 날짜 및 경과 일수 계산
    SET CUR_DATE = CURRENT_DATE();
    SET DAYS = DATEDIFF(CUR_DATE, DEBUTDATE);

    -- 조건문 수정 및 문자열 연결 방식 변경
    IF (DAYS / 365) >= 5 THEN
        SELECT CONCAT('데뷔한 지 ', DAYS, '일 밖에 안되었네요.') AS MESSAGE;
    ELSE
        SELECT CONCAT('데뷔한 지 ', DAYS, '일 되었습니다.') AS MESSAGE;
    END IF;
END $$

DELIMITER ;

-- 프로시저 실행
CALL IFPROC3();

📌 CURRENT_DATE() : 오늘 날짜
📌 CURRENT_TIMESTAMP() : 오늘 날짜 및 시간
📌 DATEDIFF(날짜1, 날짜2): 날짜 2부터 날짜 1까지 일수로 몇일인지 표시

📁 CASE 문

  • 2가지 이상의 여러 가지 경우일 때 사용
  • 기본 형식
 CASE
 	WHEN 조건 1 THEN
    	SQL 문장들1
    WHEN 조건 2 THEN
    	SQL 문장들2
    WHEN 조건 3 THEN
    	SQL 문장들3
    ELSE
    	SQL 문장들4
END CASE;

case문 활용 예시

USE MARKET_DB;
DROP PROCEDURE IF EXISTS IFPROC1; -- 기존에 IFPROC1()을 만들었다면 삭제
DELIMITER $$
create PROCEDURE IFPROC1() -- 스토어드 프로시저 이름 설정
BEGIN 
	declare point int;
    declare credit char(1);
    set point = 78;
    
    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 IFPROC1();

📁 while 문

  • 같은 문장 계속 반복
  • 기본 형식
while 조건식 D0
	sql 문장들
end while;

📌 iterate[레이블] :지정한 테이블러 가서 계속 진행 (CONTINUE와 비슷)
📌 leave[레이블] : 지정한 레이블 빠져 나오기 (BREAK와 비슷)

profile
yunssup
이전 포스트

📖 데이터 읽기의 기술

다음 포스트

테이블과 뷰

0개의 댓글