혼자 공부하는 sql

chap04 SQL 고급 문법

miffy짱 2023. 1. 18. 00:02
반응형

Chapter 04 SQL 고급 문법

MySQL의 데이터 형식

  • 데이터 형식
    • MySQL 에서 제공하는 데이터 형식은 수십 종류이고, 그 중 자주 쓰는 것만 살펴본다.
  • 정수형
    • 소수점 없는 숫자.
      • TINYINT - 1바이트 -128 ~ 127
      • SMALLINT - 2바이트 -32768 ~ 32767
      • INT - 4바이트 ..
      • BIGINT - 8바이트
    • Out of range : 입력값을 벗어났다는 의미
      • Error Code : Out of range value from ....
  • UNSIGNED 예약어 : 값의 범위가 0부터 시작함.
    • 기존 -128 ~ 127 이 0 ~ 255로 표현 되는것. 모두 256개를 표현하는 방법이다.

 

  • 문자형
    • 글자를 저장하기 위해 사용, 입력할 최대 글자의 개수를 지정해야 함.
      • CHAR - 1~255
      • VARCHAR - 1 ~ 16383
      • CHAR는 고정길이 문자형으로 CHAR(10) 처럼 10자리를 고정하고, 3글자만 사용하면 뒤의 7자리는 낭비되는 자료형이다.
      • 반면 VARCHAR 의 경우는 가변길이 문자형으로 VARCHAR(10) 이라 써놓고 앞 3글자만 사용했을 때, 3자리만 사용한다.
      • 공간적으로 보았을 때 varchar 가 char 보다 공간을 효율적으로 운영하지만, 내부적으로는 CHAR 이 속도가 더 빠르다.

 

  • 대량의 데이터 형식
    • CHAR, VARCHAR는 각각 255, 16383 자 까지 지정이 가능한데, 이는 가끔 열의 길이를 너무 크게 설정했다는 오류를 발생시키기도 함.
      • Error Code : 1074. Column length too big for column ....
    • 더 큰 데이터는 다음과 같은 형식 사용
      • TEXT 형식
        • TEXT : 1~65535 바이트
        • LONGTEXT : 1 ~ 4294967295 바이트
      • BLOB 형식
        • BLOB : 1~65535 바이트
        • LONGBLOB : 1 ~ 4294967295 바이트
    • TEXT 와 BLOB (binary long object) 의 차이
      • TEXT 는 글자를 저장함. BLOB 는 이미지, 동영상 등의 데이터를 저장.

 

  • 실수형
    • FLOAT : 4바이트 : 소수점 아래 7자리
    • DOUBLE : 8바이트 : 소수점 아래 15자리
  • 날짜형
    • DATE : 3 바이트 : 날짜만 저장, YYYY_MM_DD
    • TIME : 3 바이트 : 시간만 저장, HH:MM:SS
    • DATETIME : 8 바이트 : 날짜,시간 저장, YYYY_MM_DD HH:MM:SS

변수의 사용

 

SQL 도 다른 일반적 프로그래밍 언어처럼 변수를 선언하고 사용 가능.


사용법

  • SET @변수이름 = 변수의 값; -> 변수의 선언 및 값 대입
  • SELECT @변수이름; -> 변수의 값 출력

 

  • 변수는 MySQL 워크벤치를 재시작할 때 까지 유지되지만, 종료하면 없어짐. 임시로 사용한다고 생각하자!

다음은 한번 작성해본 SQL 문이다.

 

SET @txt = '가수 이름 ==> ';
SET @height = 166;
SELECT @txt, mem_name, height FROM member
WHERE height > @height
ORDER BY height
DESC
LIMIT 2;

변수를 통해서 값을 참조할 수 있었다. 그런데, 여기서 LIMIT 에 들어갈 2라는 값
도

SET @lim = 3;
~
~
~
LIMIT @lim;

이렇게 쓰면 될까? 다른 SELECT, WHERE 구문에서 된 것 처럼 돼야할 것 같지만 LIMIT 에는 변수를 사용할 수 없기 때문에 문법상 오류가 발생한다.

이를 해결하기 위한 방법이 있다.

  • PREPARE, EXECUTE
    • PREPARE 는 실행하지 않고 SQL 만 준비 해놓는다.
    • EXECUTE에서 실행한다.

예시

SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @count;
  • '?'는, 현재에는 알 수 없지만 나중에 채워지는 부분이다. 라고 이해하면 될 듯 하다.
  • EXECUTE 로 mySQL에 저장된 SELECT 문을 실행할 때, USING 으로 물음표(?) 에 @count 변수의 값을 대입하는 것이다.

 

데이터 형 변환

형 변환에는 두가지 종류가 있다.

  • 명시적인 변환 - 직접 함수를 사용해서 변환함.
  • 암시적인 변환 - 별도의 지시 없이 자연스럽게 변환함.
  • 명시적인 변환
    • 데이터 형식을 변환하는 함수 : CAST(), CONVERT()
    • CAST (값 AS 데이터_형식 [ (길이) ] )
    • CONVERT (값, 데이터_형식 [ (길이) ])
      • ex) SELECT AVG(price) AS '평균 가격' FROM buy;
      • SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy;
  • CONCAT() -> 문자를 이어줌
  • 암시적인 변환
    • CAST, CONVERT 를 사용하지 않고 자연스럽게 형이 변환됨.

04 - 2 두 테이블을 묶는 조인

조인 : 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것.

내부 조인

  • 일대다 관계 : 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러 개의 값이 존재할 수 있는 관계
  • 조인을 위해서는 기본 키 - 외래 키 관계로 맺어져야 하고, 이를 '일대다 관계'라고 부른다.
  • 내부 조인의 형식
  • SELECT < 열 목록 > FROM < 첫 번째 테이블 > INNER JOIN <두 번째 테이블> ON <조인될 조건> [WHERE 검색 조건]
  • 두 개의 테이블에 모두 있는 열 (mem_id) 같은 경우는 어느 테이블의 mem_id 인지 정확하게 명시해 주어야 한다.
    • 예시는 다음과 같다. 
      SELECT buy.mem_id, mem_name
FROM buy
INNER JOIN member
ON buy.mem_id = member.mem_id;
  • buy 테이블의 mem_id를 선택했다고 . 을 찍어 표시해 준다.
  • 그런데, 이렇게 하면 코드가 너무 길어진다. 그래서 별칭 을 이용할 수 있다. 예시를 보자.
  • SELECT B.mem_id, M.mem_name FROM buy B INNER JOIN member M ON B.mem_id = M.mem_id ORDER BY M.mem_id;
  • 중복된 결과 1개만 출력하기.
    • DISTINCT 문을 활용해 가능하다.

외부 조인

  • 내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나옴. 외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다. 외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다.

 

  • 외부 조인의 형식
SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
  <LEFT | RIGHT | FULL> OUTER JOIN < 두 번째 테이블 (RIGHT 테이블)>
  ON <조인될 조건>
[WHERE 검색 조건];
  • LEFT OUTER JOIN 은 '왼쪽 테이블의 내용은 모두 출력되어야 한다.' 로 이해하자.
  • FULL OUTER JOIN 은 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것으로, 왼쪽이든 오른쪽이든 한쪽에 들어있는 내용이면 출력한다.

기타 조인

  • 상호 조인 : 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능.
    • 결과적으로 두 테이블의 각 행의 개수를 곱한 개수가 나오게 됨.
  • 자체 조인 : 자신이 자신과 조인한다는 의미. 1개로 조인하면 자체조인이 된다.

04 -3 SQL 프로그래밍

"스토어드 프로시저" 는 MySQL 에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체이다. SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 만들어야 한다.

기본 꼴은 다음과 같다.

DELIMITER $$
CREATE PROCEDURE 스토어드 프로시저 이름
BEGIN

    ~~sql  프로그래밍 코딩  

END $$
DELIMITER ;
CALL 스토어드 프로시저 이름();
  • 구분문자 DELIMITER 는 $$ 를 많이 사용하는데, 다른 걸 사용해도 좋다.
  • IF 문
IF <조건식> THEN
  SQL 문장
END IF;
  • SQL 문장들이 여러 문장이라면 BEGIN ~ END 로 반드시 묶어 주어야 한다.

IF ~ ELSE 예시

DROP PROCEDURE IF EXISTS ifProc2; 
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
   DECLARE myNum INT;  -- myNum 변수선언
   SET myNum = 200;  -- 변수에 값 대입
   IF myNum = 100 THEN  
      SELECT '100입니다.';
   ELSE
      SELECT '100이 아닙니다.';
   END IF;
END $$
DELIMITER ;
CALL ifProc2();
  • SQL 이 여러 문장이라 BEGIN ~ END 로 묶어주었음을 확인할 수 있다.
  • 변수 선언 : DECLARE 변수명 타입
  • 변수 값 할당 : SET 변수명 = value
  • IF ~~ THEN
  • ELSE
  • ELSE IF : 끝냄.
  • IF 문 고급 예시
  • DROP PROCEDURE IF EXISTS ifProc3; DELIMITER $$ CREATE PROCEDURE ifProc3() BEGIN DECLARE debutDate DATE; -- 데뷰일 DECLARE curDate DATE; -- 오늘 DECLARE days INT; -- 활동한 일수 SELECT debut_date INTO debutDate -- debut_date 결과를 hireDATE에 대입 FROM market_db.member WHERE mem_id = 'APN'; SET curDATE = CURRENT_DATE(); -- 현재 날짜 SET days = DATEDIFF(curDATE, debutDate); -- 날짜의 차이, 일 단위 IF (days/365) >= 5 THEN -- 5년이 지났다면 SELECT CONCAT('데뷔한지 ', days, '일이나 지났습니다. 핑순이들 축하합니다!'); ELSE SELECT '데뷔한지 ' + days + '일밖에 안되었네요. 핑순이들 화이팅~' ; END IF; END $$ DELIMITER ; CALL ifProc3();
  • 지금까지 활용한 테이블을 이용해 본다.
  • 우선 DECLARE 로 변수명들을 선언해 주었고,
  • 기존 SELECT 와 달리 "INTO" 가 있음을 확인할 수 있다.
    • INTO가 붙은 경우 SELECT의 결과를 INTO 뒤의 변수에 저장한다. 즉 이 문장에서는 APN 아이디의 멤버를 마켓 디비에서 찾아 그의 debut_date 를 debutDate 에 저장한다.
  • curDate 에는 오늘 날짜가 저장되고,
  • DATEDIFF 함수가 있는데 이를 이용해 두 날짜간의 차이를 계산해준다.
  • 여기서부터 IF 문이다. 두 날짜간 차이를 365로 나눈 몫이 5 이상이라면 데뷔후 5년이지난 것이니 SELECT 문이 호출 된다.
  • SELECT CONCAT('데뷔한지 ', days, '일이나 지났습니다. 핑순이들 축하합니다!');
  • 잘 몰랐지만 SELECT 로 출력할 때 저렇게 따옴표로 감싸서 출력할 수도 있는가보다.

CASE문

  • 여러 가지 조건 중 선택해야 하는 경우 case 문을 사용한다.
  • 여러 가지 경우를 처리 가능하기 때문에 '다중 분기' 라고도 부른다.
CASE
  WHEN 조건1 THEN
    SQL 문장
  WHEN 조건2 THEN
    SQL 문장
  WHEN 조건3 THEN
    SQL 문장
  ELSE
    SQL 문장
END CASE;
DROP PROCEDURE IF EXISTS caseProc; 
DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
    DECLARE point INT ;
    DECLARE credit CHAR(1);
    SET point = 88 ;

    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();
  • 다음 caseProc() 를 살펴보자. 우선 첫줄은 만약 이미 caseProc 이 존재한다면 삭제해 주겠다는 의미이다.
  • 점수별로 CASE 문에서 credit 변수가 다르게 설정이 되고, 위의 조건에 다 걸리지 않는다면 F 를 할당받게 된다.
  • c 같은거에서 한 switch 문이랑 다르다!

WHILE 문

필요한 만큼 계속 같은 내용을 반복할 수 있음.

while <조건식> DO
  SQL 문장
END WHILE;

기본 예시를 보자.

DROP PROCEDURE IF EXISTS whileProc; 
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE i INT; -- 1에서 100까지 증가할 변수
    DECLARE hap INT; -- 더한 값을 누적할 변수
    SET i = 1;
    SET hap = 0;

    WHILE (i <= 100) DO
        SET hap = hap + i;  -- hap의 원래의 값에 i를 더해서 다시 hap에 넣으라는 의미
        SET i = i + 1;      -- i의 원래의 값에 1을 더해서 다시 i에 넣으라는 의미
    END WHILE;

    SELECT '1부터 100까지의 합 ==>', hap;   
END $$
DELIMITER ;
CALL whileProc();
  • while 구문을 보면 while 조건이 i 가 100 이하일 동안이고, set 을 통해 i 값을 while 내부에서 증가시켜 주고 있음을 볼 수 있다.

동적 SQL

이전에 PREPARE, EXECUTE 를 기억해본다.

  • PREPARE : SQL 문을 실행하지 않고 준비만 해놓는다.
  • EXECUTE : 준비한 SQL 문을 실행한다.
  • 이후 DEALLOCATE PREPARE 로 해제 해준다.
  • ? 로 향후에 입력될 값을 비워 두고, EXECUTE 에서 USING 을 활용 해 ? 에 들어갈 값을 지정해 줄 수 있다.

 

sql 프로그래밍에서 어떻게 활용되는지 알아보자.

 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, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;
  • 우선, gate_table 을 만든다. (auto_increment 를 활용해 자동으로 아이디가 증가되도록 한다.)
  • SET 변수를 사용해 @curDate 에 현재 날짜,시간을 담는다.
    • CURRENT_TAIMESTAMP : 연,월,일 시,분,초
  • ? 를 통해 entry_time에 들어갈 곳을 비워둔다.
  • using 문을 사용해 @curData 를 entry_time 에 넣고 실행해준다.

DELIMITER 로 감싸서 함수처럼 만들고 CALL 로 호출할 수 있고, 그냥 SQL 문법 내에서 활용 바로 할 수 있다. 확인해보자.