SQL 고급

숫자 데이터 형식(많이 쓰게 될)

데이터 형식	바이트 수	설명
INT	4	정수
FLOAT	4	소수점 아래 7자리까지 표현
DOUBLE	8	소수점 아래 15자리까지 표현

 

변수의 사용

SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있음

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

변수는 HeidiSQL을 재시작할 때까지는 계속 유지되지만, HeidiSQL을 닫았다가 재시작하면 소멸됨

 

데이터 형식과 형 변환

CAST(), CONVERT() 함수를 사용, 두 함수는 거의 비슷한 기능을 함

CAST( expression AS 데이터형식 [ (길이) ] )
CONVERT( expression , 데이터형식 [ (길이) ] )

데이터 형식 중에 가능한 것은 BINARY, CHAR, DATE, DATETIME, DECIMAL, JSON, SIGNED INTEGER, TIME, UNSIGNED INTER 등

USE sqldb;
SELECT AVG(amount) AS '평균 구매 개수' FROM buytbl;

'개수'이므로 정수로 보기 위해 CAST()나 CONVERT() 함수 사용

SELECT CAST(AVG(amount) AS SIGNED INTEGER) AS '평균 구매 개수' FROM buytbl;

결과를 보면 반올림한 정수의 결과 확인

 

다양한 구분자를 날짜 형식으로 변경 가능

SELECT CAST('2022$12$12' AS DATE);
SELECT CAST('2022/12/12' AS DATE);
SELECT CAST('2022%12%12' AS DATE);
SELECT CAST('2022@12@12' AS DATE);

모두 2022-12-12 형태로 변환됨

 

암시적인 형 변환

형 변환 방식에는 명시적인 변환과 암시적인 변환 두 가지가 있음

위에 사용한 CAST(), CONVERT() 함수를 이용해 데이터 형식을 변환하는 것은 명시적인 변환(Explicit conversion)

암시적인 변환(Implicit conversion)은 CAST()나 CONVERT() 함수를 사용하지 않고 형이 변환되는 것

SELECT '100' + '200' ;          -- 문자와 문자를 더함(정수로 변환되어 연산됨)
SELECT CONCAT('100', '200');    -- 문자와 문자를 연결(문자로 처리)
SELECT CONCAT(100, '200');      -- 정수와 문자를 연결(정수가 문자로 변환되어 처리)
SELECT 1 > '2mega';             -- 정수 2로 변환되어서 비교
SELECT 3 > '2MEGA';             -- 정수 2로 변환되어서 비교
SELECT 0 = 'mega2';             -- 문자는 0으로 변환됨

False(0), True(1)

 

MariaDB 내장 함수

 

제어 흐름 함수

프로그램의 흐름 제어

 

· IF(수식, 참, 거짓)

수식이 참 또는 거짓인지 결과에 따라 2중 분기

SELECT IF (100>200, '참', '거짓');

 

· IFNULL(수식1, 수식2)

수식1이 NULL이 아니면 수식1 반환, 수식1이 NULL이면 수식2 반환

SELECT IFNULL(NULL, '널'), IFNULL(100, '널');

 

· CASE ~ WHEN ~ ELSE ~ END

CASE는 내장 함수는 아니고 연산자로 분류되지만 다중 분기에 사용될 수 있으므로 내장 함수와 함께 공부

SELECT    CASE 10
          WHEN 1  THEN '일'
          WHEN 5  THEN '오'
          WHEN 10 THEN '십'
          ELSE '모름'
      END;

 

문자열 함수

문자열 조작, 활용도 높음

 

· ASCII(아스키코드), CHAR(숫자)

문자의 아스키 코드값을 돌려주거나, 숫자의 아스키 코드 값에 해당하는 문자를 돌려줌

SELECT ASCII('A'), CHAR(65);

 

· BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)

할당된 Bit 크기, 또는 문자 크기 반환, CHAR_LENGTH()는 문자의 개수를 반환, LENGTH()는 할당된 Byte 수 반환

SELECT BIT_LENGTH('abc'), CHAR_LENGTH('abc'), LENGTH('abc');
SELECT BIT_LENGTH('가나다'), CHAR_LENGTH('가나다'), LENGTH('가나다');

 

· CONCAT(문자열1, 문자열2, ⋯.), FIELD(찾을 문자열, 문자열1, 문자열2, ⋯), FIND_IN_SET(찾을 문자열, 문자열 리스트), INSTR(기준 문자열, 부분 문자열), LOCATE(부분 문자열, 기준 문자열)

ELT()는 위치 번째에 해당하는 문자열 반환FIELD()는 찾을 문자열의 위치를 찾아 반환, 매치되는 문자열이 없으면 0 반환FIND_IN_SET()은 찾을 문자열을 문자열 리스트에서 찾아 위치 반환, 문자열 리스트는 콤마(,)로 구분되어 있어야 하고 공백이 없어야 함INSTR()은 기준 문자열에서 부분 문자열을 찾아 그 시작 위치를 반환LOCATE()는 INSTR()과 동일하지만 파라미터의 순서가 반대로 되어 있음(POSITION()과 동일한 함수)

SELECT ELT(2, '하나', '둘', '셋'), FIELD('둘', '하나', '둘', '셋'), FIND_IN_SET('둘', '하나, 둘, 셋'),
       INSTR('하나둘셋', '둘'), LOCATE('둘', '하나둘셋');

 

· BIN(숫자), HEX(숫자), OCT(숫자)

순서대로 각각 2진수, 16진수, 8진수의 값을 반환

SELECT BIN(31), HEX(31), OCT(31);

 

· INSERT(기준 문자열, 위치, 길이, 삽입할 문자열)

기준 문자열의 위치부터 길이만큼을 지우고 삽입할 문자열을 끼워 넣음

SELECT INSERT('abcdefghi', 3, 4, '@@@@'), INSERT('abcdefghi', 3, 2, '@@@@');

· LEFT(문자열, 길이), RIGHT(문자열, 길이)

왼쪽 또는 오른쪽에서 문자열의 길이만큼 반환

SELECT LEFT('abcdefghi', 3), RIGHT('abcdefghi', 3);

 

· UPPER(문자열), LOWER(문자열)

UCASE(), LCASE()와 동일한 함수

소문자 > 대문자, 대문자 > 소문자 변경

SELECT LOWER('abcdEFGH'), UPPER('abcdEFGH');

· LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열)

문자열을 길이만큼 늘린 후, 빈 곳을 채울 문자열로 채움

SELECT LPAD('이것이', 5, '##'), RPAD('이것이', 5, '##');

 

· LTRIM(문자열), RTRIM(문자열)

문자열의 왼쪽/오른쪽 공백 제거, 중간의 공백은 제거되지 않음

SELECT LTRIM('    이것이'), RTRIM('이것이     ');

 

· TRIM(문자열), TRIM(방향 자를_문자열 FROM 문자열)

TRIM(문자열)은 앞뒤 공백을 모두 없앰

TRIM(방향 자를_문자열 FROM 문자열)에서 방향은 LEADING(앞), BOTH(양쪽), TRAILING(뒤)이 나올 수 있음

SELECT TRIM('    이것이   '), TRIM(BOTH 'ㅋ' FROM 'ㅋㅋㅋMariaDBㅋㅋㅋ');

 

· REPEAT(문자열, 횟수)

문자열을 횟수만큼 반복

SELECT REPEAT('이것이', 3);

· REPLACE(문자열, 원래 문자열, 바꿀 문자열)

문자열에서 원래 문자열을 찾아 바꿀 문자열로 바꿔 줌

SELECT REPLACE('이것이 MariaDB', '이것이', 'This is');

 

· REVERSE(문자열)

문자열의 순서를 거꾸로

SELECT REVERSE('MariaDB');

 

· SPACE(길이)

길이만큼의 공백을 반환

SELECT CONCAT('이것이', SPACE(10), 'MariaDB');

· SUBSTRING(문자열, 시작위치, 길이) 또는 SUBSTRING(문자열 FROM 시작위치 FOR 길이)

서식 위치부터 길이만큼 문자 반환, 길이가 생략되면 문자열의 끝까지 반환

SELECT SUBSTRING('대한민국만세', 3, 2);

 

· SUBSTRING_INDEX(문자열, 구분자, 횟수)

문자열에서 구분자가 왼쪽부터 횟수 번째 나오면 그 이후의 오른쪽은 버림, 횟수가 음수면 오른쪽부터 세고 왼쪽을 버림

SELECT SUBSTRING_INDEX('cafe.naver.com', '.', 2), SUBSTRING_INDEX('cafe.naver.com', '.', -2);

 

수학 함수

 

· ABS(숫자)

절대값 계산

SELECT ABS(-100);

 

· ACOS(숫자), ASIN(숫자), ATAN(숫자), ATAN2(숫자1, 숫자2), SIN(숫자), COS(숫자), TAN(숫자)

삼각 함수와 관련된 함수 제공

 

· CEILING(숫자), FLOOR(숫자), ROUND(숫자)

올림, 내림, 반올림을 계산

SELECT CEILING(4.7), FLOOR(4.7), ROUND(4.7);

 

· CONV(숫자, 원래 진수, 변환할 진수)

SELECT CONV('AA', 16, 2), CONV(100, 10, 8);

 

· DEGREES(숫자), RADIANS(숫자), PI()

라디안 값을 각도로, 각도값을 라디안 값으로 변환

파이는 파이 값인 3.141592 반환

SELECT DEGREES(PI()), RADIANS(180);

 

 

· EXP(X), LN(숫자), LOG(숫자), LOG(밑수, 숫자), LOG2(숫자), LOG10(숫자)

지수, 로그 관련된 함수 제공

 

· MOD(숫자1, 숫자2) 또는 숫자1 %숫자2 또는 숫자1 MOD 숫자2

숫자1을 숫자2로 나눈 나머지 값

SELECT MOD(157, 10), 157 % 10, 157 MOD 10;

 

· POW(숫자1, 숫자2), SQRT(숫자)

거듭제곱값 및 제곱근

POW()는 POWER()와 동일한 함수

SELECT POW(2, 3), SQRT(9);

 

· RAND()

RAND()는 0 이상 1 미만 실수를 구함

만약 'm<= 임의의 정수 < n'을 구하고 싶다면 FLOOR(m + (RAND() * (n-m))

SELECT RAND(), FLOOR(1 + (RAND() * (7-1)) );

 

0~1 미만의 실수와 주사위 숫자

 

· SIGN(숫자)

숫자가 양수, 0, 음수인지 구하고 결과를 1, 0, -1 셋 중 하나 반환

SELECT SIGN(100), SIGN(0), SIGN(-100.123);

 

· TRUNCATE(숫자, 정수)

숫자를 소수점 기준으로 정수 위치까지 구하고 나머지는 버림

SELECT TRUNCATE(12345.12345, 2), TRUNCATE(12345.12345, -2);

 

날짜 및 시간 함수

 

· ADDDATE(날짜, 차이), SUBDATE(날짜, 차이)

ADDDATE()와 DATE_ADD()는 동일 함수, SUBDATE()와 DATE_SUB()도 동일한 함수

SELECT ADDDATE('2022-01-01', INTERVAL 31 DAY), ADDDATE('2022-01-01', INTERVAL 1 MONTH);
SELECT SUBDATE('2022-01-01', INTERVAL 31 DAY), SUBDATE('2022-01-01', INTERVAL 1 MONTH);

 

· ADDTIME(날짜/시간, 시간), SUBTIME(날짜/시간, 시간)

SELECT ADDTIME('2022-01-01 23:59:59', '1:1:1'), ADDTIME('15:00:00', '2:10:10');
SELECT SUBTIME('2022-01-01 23:59:59', '1:1:1'), SUBTIME('15:00:00', '2:10:10');

 

· CURDATE(), CURTIME(), NOW(), SYSDATE()

CURDATE()는 현재 연-월-일, CURTIME()은 현재 시:분:초

NOW()와 SYSDATE()는 현재 '연-월-일 시:분:초'

 

CURDATE()=CURRENT_DATE(), CURRENT_DATE

CURTIME() = CURRENT_TIME(), CURRENT_TIME

NOW() = LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP()

 

· YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시간), MICROSECOND(시간)

날짜 또는 시간에서 연, 월, 일, 시, 분, 초, 밀리초

SELECT YEAR(CURDATE()), MONTH(CURRENT_DATE()), DAYOFMONTH(CURRENT_DATE);
SELECT HOUR(CURTIME()), MINUTE(CURRENT_TIME()), SECOND(CURRENT_TIME),
MICROSECOND(CURRENT_TIME);

DAYOFMONTH()와 DAY()는 동일한 함수

 

· DATE(), TIME()

DATETIME 형식에서 연-월-일 및 시:분:초만 추출

SELECT DATE(NOW()), TIME(NOW());

 

· DATEDIFF(날짜1, 날짜2), TIMEDIFF(날짜1또는시간1, 날짜1또는시간2)

DATEDIFF()는 날짜2에서 날짜1까지 며칠이 남았는지 구함

TIMEDIFF()는 시간1-시간2의 결과

SELECT DATEDIFF('2022-01-01', NOW()), TIMEDIFF('23:23:59', '12:11:10');

 

· DAYOFWEEK(날짜), MONTHNAME(), DAYOFYEAR(날짜)

요일(1:일, 2:월~7:토) 및 1년 중 몇 번째 날짜인지 구함

SELECT DAYOFWEEK(CURDATE()), MONTHNAME(CURDATE()), DAYOFYEAR(CURDATE());

 

· LAST_DAY(날짜)

주어진 날짜의 마지막 날짜, 주로 그 달이 며칠까지 있는지 확인할 때 사용

SELECT LAST_DAY('2022-02-01');

 

· MAKEDATE(연도, 정수)

연도에서 정수만큼 지난 날짜 구함

SELECT MAKEDATE(2022, 32);

 

· MAKETIME(시, 분, 초)

시, 분, 초를 이용해 '시:분:초'의 TIME 형식을 만듦

SELECT MAKETIME(12, 11, 10);

 

· PERIOD_ADD(연월, 개월수), PERIOD_DIFF(연월1, 연월2)

PERIOD_ADD()는 연월에서 개월만큼의 개월이 지난 연월을 구함

연월은 YYYY 또는 YYYYMM 형식 사용, PERIOD_DIFF()는 연월1-연월2의 개월수를 구함

SELECT PERIOD_ADD(202201, 11), PERIOD_DIFF(202201, 201812);

 

· QUARTER(날짜)

날짜가 4분기 중에서 몇 분기인지 구함

SELECT QUARTER('2022-07-07');

 

· TIME_TO_SEC(시간)

시간을 초 단위로 구함

SELECT TIME_TO_SEC('12:11:10');

 

시스템 정보 함수

 

· USER(), DATABASE()

현재 사용자 및 현재 선택된 데이터베이스를 구함

SELECT CURRENT_USER(), DATABASE();

 

· FOUND_ROWS()

바로 앞의 SELECT문에서 조회된 행의 개수를 구함

USE sqlDB;
SELECT * FROM usertbl;
SELECT FOUND_ROWS();

 

· ROW_COUNT()

바로 앞의 INSERT, UPDATE, DELETE문에서 입력, 수정, 삭제된 행의 개수를 구함

CREATE, DROP문은 0을 반환하고 SELECT문은 -1을 반환

USE sqlDB;
UPDATE buytbl SET price=price*2;
SELECT ROW_COUNT();

HeidiSQL 버그 때문에 UPDATE 후에 ROW_COUNT()가 0이 나올 수 있음 명령 창에서 수행하면 12개로 잘 수행됨

 

· VERSION()

현재 MariaDB의 버전을 구함

 

· SLEEP(초)

쿼리의 실행을 잠깐 멈춤

SELECT SLEEP(5);
SELECT '5초 후에 보임';

 

 

TEXT 데이터 형식을 이용해 대량의 데이터 입력

 

테이블 정의

USE sqlDB;
CREATE TABLE maxTbl ( col1 LONGTEXT, col2 LONGTEXT);

LONGTEXT는 최대 4GB까지 저장 가능

 

REPEAT 함수로 데이터 백만 개 입력

INSERT INTO maxTbl VALUES (REPEAT('A', 1000000), REPEAT('가',1000000));

SELECT LENGTH(col1), LENGTH(col2) FROM maxTbl;

한글은 utf-8 코드이므로 3Byte를 차지하기 때문에 크기가 다름

 

데이터 천만 개 입력

INSERT INTO maxTbl VALUES (REPEAT('A', 10000000), REPEAT('가',10000000));

오류

max_allowed_packet의 최대값이 약 1천6백만 바이트이기 때문

 

max_allowed_packet 시스템 변수 값 변경해 다시 입력

1. HeidiSQL 종료

2. CMD 관리자 모드로 열기

3. 파일 탐색기에서 C:\Program Files\MariaDB 10.3\data 폴더의 my.ini 파일 실행, [mysqld] 아랫 부분에 다음 내용 추가

  > max_allowed_packet=1000M

4. 저장 후 메모장 종료

5. MariaDB 서버를 재시작해야 해서 컴퓨터 재부팅

 > 재부팅하지 않고 MariaDB 서버 재시작하는 방법: cmd를 관리자 권한으로 열고 다음 명령을 차례로 수행해도 됨

    NET STOP MySQL

    NET START MySQL

 

다시 천만 개 입력 후 크기 확인

USE sqlDB;
INSERT INTO maxtbl VALUES (REPEAT('A', 10000000), REPEAT('가', 10000000));

SELECT LENGTH(col1), LENGTH(col2) FROM maxTbl;

 

SHOW WARIABLES 명령으로 MariaDB의 시스템 변수 값 확인(시스템 변수의 종료가 너무 많으므로 LIKE를 함께 활용)

SHOW VARIABLES LIKE 'max%';

 

 

파일 탐색기에서 SQL 폴더 위치 확인하고, 다음 쿼리로 결과를 파일에 저장

파일 위치는 가지고 올 폴더 찾은 뒤 폴더에 우클릭 후 경로로 복사로 가지고 오면 편함

USE sqlDB;
SELECT * INTO OUTFILE "C:/SQL/userTBL.txt" FROM usertbl; -- 폴더의 경로를 슬래쉬(/)로 사용

생성된 것 확인

 

userTBL과 동일한 구조의 memberTBL을 만들고, 파일의 내용을 memberTBL에 한 번에 입력

CREATE TABLE memberTBL LIKE userTBL; -- 테이블 구조만 복사
LOAD DATA LOCAL INFILE "C:/SQL/userTBL.txt" INTO TABLE memberTBL;
SELECT * FROM memberTBL;

 

이러한 방식은 기존의 대량 데이터를 엑셀이나 텍스트 파일로 가지고 있을 대 한꺼번에 테이블에 입력하기 위한 방법으로 활용됨

 

순위 함수

 

키 큰 순서대로 정하고 싶은 경우 ROW_NUMBER() 함수 사용

USE SQLDB;
SELECT ROW_NUMBER( ) OVER(ORDER BY height DESC) "키큰순위", name, addr, height
    FROM userTBL;

 

키가 동일한 경우 이름이 가나다순으로 정렬하도록 수정

USE SQLDB;
SELECT ROW_NUMBER( ) OVER(ORDER BY height DESC, name ASC) "키큰순위", name, addr, height
    FROM userTBL;

여기서 ASC는 생략 가능

 

전체 순위가 아닌 각 지역별 순위를 주고 싶은 경우(지역으로 나눈 후 키 큰 순위를 매기는 경우, PARTITION BY절 사용)

USE SQLDB;
SELECT addr, ROW_NUMBER( ) OVER(PARTITION BY addr ORDER BY height DESC, name)
"지역별키큰순위", name, addr, height
    FROM userTBL;

 

같은 182인데도 2등과 3등으로 나뉜 경우 두 개의 데이터를 동일한 등수로 처리하는 함수 DENSE_RANK() 함수

USE SQLDB;
SELECT DENSE_RANK( ) OVER(ORDER BY height DESC)
"키큰순위", name, addr, height
    FROM userTBL;

 

2등 두 명 나온 후 3등이 나오는데 어떤 경우 2등이 두 명이라면 2, 2, 4 식으로 3등 빼고 4등부터 순위를 매길 필요도 있음

이럴 때는 RANK() 함수 사용

USE SQLDB;
SELECT RANK( ) OVER(ORDER BY height DESC)
"키큰순위", name, addr, height
    FROM userTBL;

 

전체 인원을 키순으로 세운 후, 몇 개의 그룹으로 분할하고 싶은 경우

ex) 10명의 사용자를 키순으로 세운 후, 2개의 반으로 분반하고 싶은 경우(5명으로 나뉘면 됨)

이때는 NTILE(나눌 그룹 개수) 함

USE SQLDB;
SELECT NTILE(2) OVER(ORDER BY height DESC)
"반번호", name, addr, height
    FROM userTBL;

3명으로 나눌 경우 동일하게 나눈 뒤, 나머지 인원을 처음 그룹부터 하나씩 배당

 

분석 함수

비집계 함수 중에서 CUME_DIST(), LEAD(), FIRST_VALUE(), LAG(), LAST_VALUE(), PERCENT_RANK() 등을 분석 함수라 부름

분석 함수를 이용하면 이동 평균, 백분율, 누계 등의 결과를 계산할 수 있음

 

userTBL에서 키가 큰 순서로 정렬한 후에, 다음 사람과 키 차이를 미리 알려면 LEAD() 함수를 사용할 수 있음

USE SQLDB;
SELECT name, addr, height AS "키",
    height -(LEAD(height, 1) OVER (ORDER BY height DESC)) AS "다음 사람과 키 차이"
        FROM userTBL;

LEAD() 함수에서 사용되는 인자는 열 이름, 다음 행 위치를 지정할 수 있음

위에서는 height 열을 사용했고 다음 1번째 행(바로 다음 행)을 비교 대상으로 함, 마지막은 다음 행이 없으므로 키 차이는 NULL 출력

OVER절에서는 키 순서로 정렬

거의 같은 용도로 LAG()함수를 사용할 수 있는데 LEAD()가 다음 행과의 차이라면, LAG()는 이전 행과의 차이를 구함

 

지역별로 가장 키가 큰 사람과의 차이를 알고 싶다면 FIRST_VALUE() 활용

SELECT addr, name, height AS "키",
       height - ( FIRST_VALUE(height) OVER (PARTITION BY addr ORDER BY height DESC) )
             AS "지역별 최대키와 차이"
   FROM userTBL;

OVER 문장의 PARTITION BY addr에 의해 지역별로 그룹화, FIRST_VALUE(height)로 각 지역별 첫 번째 값(가장 큰 키)을 추출

 

누적 합계 내기

ex) 현 지역에서 자신과 키가 같거나 큰 인원의 백분율을 구할 수 있음, CUME_DIST() 함수 사용

SELECT addr, name, height AS "키",
    CAST( (CUME_DIST() OVER (PARTITION BY addr ORDER BY height DESC)) * 100 AS
        INTEGER) AS "누적인원 백분율%"
  FROM userTBL;

경남의 결과를 보면 김범수는 전체 경남 인원 2명 중 자신보다 키가 크거나 같은 사람이 1명(자신 포함)이므로 50%

윤종신은 2명 중 자신보다 키가 크거나 같은 사람이 2명이므로 100% 출력

예시로 직원별 연봉이 소속 부서 중 몇 퍼센트 안에 드는지 확인하는 경우에 유용하게 사용될 수 있음

PERCENT_RANK()도 CUME_DIST()와 유사한 기능을 함

 

피벗과 JSON

 

피벗의 구현

피벗은 한 열에 포함된 여러 값을 출력하고, 이를 여러 열로 변환해 테이블 반환 식을 회전하고 필요하면 집계까지 수행하는 것

 

피벗 테이블 실습

USE SQLDB;
CREATE TABLE pivotTest
    ( uName CHAR(3),
      season CHAR(2),
      amount INT );

INSERT INTO pivotTest VALUES
    ('김범수', '겨울', 10), ('윤종신', '여름', 15), ('김범수', '가을', 25), ('김범수', '봄',    3),
    ('김범수', '봄',   37), ('윤종신', '겨울', 40), ('김범수', '여름', 14), ('김범수', '겨울', 22),
    ('윤종신', '여름', 64) ;
SELECT * FROM pivotTest

샘플 테이블 생성, 데이터 입력

SELECT uName,
  SUM(IF(season='봄', amount, 0)) AS '봄',
  SUM(IF(season='여름', amount, 0)) AS '여름',
  SUM(IF(season='가을', amount, 0)) AS '가을',
  SUM(IF(season='겨울', amount, 0)) AS '겨울',
  SUM(amount) AS '합계' FROM pivotTest GROUP BY uName ;

SUM()과 IF() 함수, GROUP BY 활용

 

비타민 퀴즈...

위와 같이 만들기

SELECT season,
  SUM(IF(uName='김범수', amount, 0)) AS '김범수',
  SUM(IF(uName='윤종신', amount, 0)) AS '윤종신',
  SUM(amount) AS '합계' FROM pivotTest GROUP BY season ;

 

 

JSON 데이터

JSON(JavaScript Object Notation)은 현대의 웹과 모바일 응용프로그램 등과 데이터를 교환하기 위한 개방형 표준 포맷

속성(Key)과 값(Value)으로 쌍을 이루며 구성되어 있음

JSON은 JavaScript 언어에서 파생되었지만, 특정한 프로그래밍 언어에 종속되어 있지 않은 독립적인 데이터 포맷

 

JSON의 가장 단순한 형태의 예) 한 명의 사용자를 JSON 형태로 표현, 속성과 값으로 쌍을 이루는 것 확인 가능

{
  "아이디" : "BBK" ,
  "이름" : "바비킴" ,
  "생년" : 1973 ,
  "지역" : "서울" ,
  "국번" : "010" ,
  "전화번호" : "00000000" ,
  "키" : 178 ,
  "가입일" : "2013.5.5"
}

 

테이블의 데이터 JSON 데이터로 표현하기

우선 userTBL에서 키가 180 이상인 사람의 이름과 키, 이것을 JSON으로 변환하려면 JSON_OBJECT()나 JSON_ARRAY() 함수 이용

USE SQLDB;
SELECT JSON_OBJECT('name', name, 'height', height) AS 'JSON 값'
    FROM userTBL
    WHERE height >= 180;

결과 값은 JSON 형태로 구성됨, MariaDB에서 제공하는 다양한 내장 함수를 사용해 운영할 수 있음

SET @json='{ "userTBL" :
  [
      {"name": "임재범", "height": 182},
      {"name": "이승기", "height": 182},
      {"name": "성시경", "height": 186}
  ]
}' ;
SELECT JSON_VALID(@json);
SELECT JSON_SEARCH(@json, 'one', '성시경');
SELECT JSON_EXTRACT(@json, '$.userTBL[2].name');
SELECT JSON_INSERT(@json, '$.userTBL[0].mDate', '2009-09-09');
SELECT JSON_REPLACE(@json, '$.userTBL[0].name', '홍길동');
SELECT JSON_REMOVE(@json, '$.userTBL[0]');

JSON_VALID() 함수는 문자열이 JSON 형식을 만족하면 1, 하지 못하면 0 반환

JSON_SEARCH() 함수는 세 번째 파라미터에 주어진 문자열의 위치를 반환, 두 번째 파라미터는 'one'과 'all' 중 하나가 올 수 있음

'one'은 처음으로 매치되는 하나만 반환, 'all'은 매치되는 모든 것 반환

JSON_EXTRACT()는 JSON_SEARCH()와 반대로 지정된 위치의 값을 추출

 JSON_INSERT()는 새로운 값 추가, 위에서는 mDate 추가

 

JSON_REPLACE()는 값을 변경

이름이 홍길동으로 바뀐 것 확인

 

JSON_REMOVE()는 지정된 항목 삭제

예시에서는 첫 번째(0)의 항목 통째로 삭제

 

조인

 

두 개 이상의 테이블을 서로 묶어 하나의 결과 집합으로 만들어 내는 것

 

INNER JOIN(내부 조인)

조인 중 가장 많이 사용되는 조인, 대개의 업무에서 조인은 INNER JOIN 사용

일반적으로 JOIN이라고 얘기하는 게 INNER JOIN을 지칭하는 것

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

위 형식에서 INNER JOIN을 그냥 JOIN이라고만 써도 INNER JOIN으로 인식함

 

구매 테이블 중 JYP라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해 이름/주소/연락처 등을 조인해 검색하려면 다음과 같이 사용

USE SQLDB;
SELECT *
  FROM buytbl
    INNER JOIN usertbl
      ON buytbl.userID = usertbl.userID
  WHERE buytbl.userID = 'JYP';

만약 WHERE buytbl.userID = 'JYP' 구문을 생략하면 buytbl의 모든 행에 대해 위와 동일한 방식으로 반복하게 되는 것

WHERE 뺀 결과

필요한 열만 추출하기: 아이디/이름/구매물품/주소/연락처

SELECT userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
  FROM buyTBL
    INNER JOIN userTBL
      ON buyTBL.userID = userTBL.userID ;

이렇게 입력하면 에러 발생

열 이름 userID가 불확실

SELECT buytbl.userID, name, prodName, addr, CONCAT(mobile1, mobile2) AS '연락처'
  FROM buyTBL
    INNER JOIN userTBL
      ON buyTBL.userID = userTBL.userID ;

 

코드를 명확하게 하기 위해 SELECT 다음의 컬럼 이름(열 이름)에도 모두 '테이블이름.열이름' 식으로 붙여 줌

SELECT buytbl.userID, userTBL.name, buyTBL.prodName, userTBL.addr,
        CONCAT(usertbl.mobile1, usertbl.mobile2) AS '연락처'
  FROM buyTBL
    INNER JOIN userTBL
      ON buyTBL.userID = userTBL.userID ;

 

코드가 길어져 복잡해 보이므로 간편하게 하기 위해 각 테이블에 별칭(Alias)을 줄 수 있음

SELECT B.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
  FROM buyTBL B
    INNER JOIN userTBL U
      ON B.userID = U.userID ;

테이블에 별칭을 주기 위해서는 FROM절에 나오는 테이블의 이름 뒤에 별칭을 붙여 주면 됨

 

앞서 JYP 아이디의 사용자가 구매했던 것과 조인한 것을 다시 생각해 아이디/이름/물품/주소/연락처 출력, 코드 간결하게 수정 

SELECT B.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
  FROM buyTBL B
    INNER JOIN userTBL U
      ON B.userID = U.userID
  WHERE B.userID = 'JYP';

구매 테이블의 JYP라는 아이디가 구매한 물품을 배송하기 위해 회원 테이블에서 JYP에 해당하는 이름/주소/연락처를 가지고 옴

 

usertbl 기준으로 JYP라는 아이디가 구매한 물건의 목록

SELECT U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
  FROM userTBL U
    INNER JOIN buyTBL B
      ON U.userID = B.userID
  WHERE B.userID = 'JYP';

구매 테이블 기준으로 한 것에서 순서 정도만 바꿨을 뿐 큰 차이는 X, 결과도 방금 전과 동일

 

전체 회원들이 구매한 목록 모두 출력, 보기 쉽게 회원ID 순으로 정렬

SELECT U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
  FROM userTBL U
    INNER JOIN buyTBL B
      ON U.userID = B.userID
  ORDER BY U.userID;

위의 결과는 '전체 회원들'이 아닌 '구매한 기록이 있는 회원들'의 결과

한 번도 구매하지 않은 회원들은 나타나지 않음

구매한 회원의 기록도 나오면서 구매하지 않아도 회원의 이름/주소 등은 나오도록 조인할 필요가 있을 수 있음

> 이렇게 조인해 주는 방식이 OUTER JOIN

 

INNER JOIN은 양쪽 테이블에 모두 내용이 있는 것만 조인되는 방식

OUTER JOIN은 양쪽에 내용이 있으면 조인되고, 한쪽에만 내용이 있어도 결과가 표시되는 조인 방식

 

INNER JOIN이 유용한 경우: 쇼핑몰에서 한 번이라도 구매한 기록이 있는 회원들에게 감사의 안내문 발송

> DISTINCT문을 활용해 회원의 주소록 뽑을 수 있음

SELECT DISTINCT U.userID, U.name, U.addr
  FROM userTBL U
    INNER JOIN buyTBL B
      ON U.userID = B.userID
  ORDER BY U.userID ;

 

위 결과를 EXISTS문을 사용해 동일한 결과를 낼 수 있음

SELECT U.userID, U.name, U.addr
  FROM userTBL U
  WHERE EXISTS (
      SELECT *
      FROM buyTBL B
      WHERE U.userID = B.userID );

 

세 개의 테이블 조인 예시

학생과 동아리의 관계, 한 학생은 여러 개의 동아리에 가입해 활동할 수 있고, 하나의 동아리에는 여러 명의 학생이 가입할 수 있음

> 두 개는 서로 '다대다'(many-to-many) 관계

두 테이블의 사이에 연결 테이블을 둬 이 연결 테이블과 두 테이블이 일대다 관계를 맺도록 구성

 

테이블 생성, 데이터 입력

USE sqlDB;
CREATE TABLE stdTBL
( stdName    VARCHAR(10) NOT NULL PRIMARY KEY,
  addr       CHAR(4) NOT NULL
);

CREATE TABLE clubTBL
( clubName   VARCHAR(10) NOT NULL PRIMARY KEY,
  roomNo     CHAR(4) NOT NULL
);

CREATE TABLE stdclubTBL
(  num int AUTO_INCREMENT NOT NULL PRIMARY KEY,
   stdName   VARCHAR(10) NOT NULL,
   clubName  VARCHAR(10) NOT NULL,
FOREIGN KEY(stdName) REFERENCES stdTBL(stdName),
FOREIGN KEY(clubName) REFERENCES clubTBL(clubName)
);

INSERT INTO stdTBL VALUES (N'김범수', N'경남'), (N'성시경', N'서울'), (N'조용필', N'경기'),
        (N'은지원', N'경북'), (N'바비킴', N'서울');
INSERT INTO clubTBL VALUES (N'수영', N'101호'), (N'바둑', N'102호'), (N'축구', N'103호'),
        (N'봉사', N'104호');
INSERT INTO stdclubTBL VALUES (NULL, N'김범수', N'바둑'), (NULL, N'김범수', N'축구'),
        (NULL, N'조용필', N'축구'), (NULL, N'은지원', N'축구'), (NULL, N'은지원', N'봉사'),
        (NULL, N'바비킴', N'봉사');

 

학생 테이블, 동아리 테이블, 학생동아리 테이블을 이용해 학생 기준으로 학생 이름/지역/가입한 동아리/동아리방 출력

SELECT S.stdName, S.addr, C.clubName, C.roomNo
    FROM stdTBL S
        INNER JOIN stdclubTBL SC
            ON S.stdName = SC.stdName
        INNER JOIN clubTBL C
            ON SC.clubName = C.clubName
    ORDER BY S.stdName;

 

동아리 기준으로 가입한 학생의 목록 출력

SELECT C.clubName, C.roomNo, S.stdName, S.addr
    FROM stdTBL S
        INNER JOIN stdclubTBL SC
            ON SC.stdName = S.stdName
        INNER JOIN clubTBL C
            ON SC.clubName = C.clubName
    ORDER BY C.clubName;

 

OUTER JOIN(외부 조인)

자주는 아니지만 가끔 유용하게 사용되는 방식

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

 

전체 회원의 구매 기록 보기(단, 구매 기록이 없는 회원도 출력)

USE sqlDB;
SELECT U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
    FROM userTBL U
        LEFT OUTER JOIN buyTBL B
            ON U.userID = B.userID
    ORDER BY U.userID;

LEFT OUTER JOIN문의 의미를 왼쪽 테이블(userTBL)의 건 모두 출력되어야 한다 정도로 해석하면 기억하기 쉬울 것

LEFT OUTER JOIN을 줄여 LEFT JOIN이라고만 써도 됨

 

위와 동일한 결과를 위해 구문을 RIGHT OUTER JOIN으로 바꾸려면 왼쪽, 오른쪽 테이블 위치만 바꿔 주면 됨

SELECT U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
    FROM buyTBL B
        RIGHT OUTER JOIN userTBL U
            ON U.userID = B.userID
    ORDER BY U.userID;

 

OUTER JOIN에서 한 번도 구매한 적 없는 유령 회원 목록 뽑기

SELECT U.userID, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
    FROM userTBL U
        LEFT OUTER JOIN buyTBL B
            ON U.userID = B.userID
	 WHERE B.prodName IS NULL
	 ORDER BY U.userID;

 

FULL OUTER JOIN(전체 또는 전체 외부 조인)은 LEFT OUTER JOIN과 RIGHT OUTER JOIN이 합쳐진 것

> 줄여서 FULL JOIN이라고 부름, 활용도 낮음

 

LEFT/RIGHT/FULL OUTER JOIN 실습

앞서 했던 학생과 동아리 관련된 테이블 가지고 INNER JOIN했던 결과를 OUTER JOIN으로 고려, 두 개의 조인을 고려한 FULL JOIN 테스트

학생, 동아리 관련 테이블에서 학생을 기준으로 출력된 결과를 보면, 동아리에 가입하지 않은 학생(성시경)은 출력되지 않음

> 출력

USE sqlDB;	 
SELECT S.stdName, S.addr, C.clubName, C.roomNo
    FROM stdTBL S
        LEFT OUTER JOIN stdclubTBL SC
            ON S.stdName = SC.stdName
        LEFT OUTER JOIN clubTBL C
            ON SC.clubName = C.clubName
    ORDER BY S.stdName;

 

동아리를 기준으로 가입된 학생을 출력하되, 가입 학생이 하나도 없는 동아리 또한 출력

SELECT  C.clubName, C.roomNo, S.stdName, S.addr
    FROM stdTBL S
        LEFT OUTER JOIN stdclubTBL SC
            ON SC.stdName = S.stdName
        RIGHT OUTER JOIN clubTBL C
            ON SC.clubName = C.clubName
    ORDER BY C.clubName;

클럽을 기준으로 조인해야 하므로 두 번째 조인은 RIGHT OUTER JOIN으로 처리해 clubTBL이 조인의 기준이 되도록 설정

 

위 두 결과 합치기, 앞의 두 쿼리를 UNION으로 합쳐 주면 됨

SELECT S.stdName, S.addr, C.clubName, C.roomNo
    FROM stdTBL S
        LEFT OUTER JOIN stdclubTBL SC
            ON S.stdName = SC.stdName
        LEFT OUTER JOIN clubTBL C
            ON SC.clubName = C.clubName
UNION     
SELECT  S.stdName, S.addr, C.clubName, C.roomNo
    FROM stdTBL S
        LEFT OUTER JOIN stdclubTBL SC
            ON SC.stdName = S.stdName
        RIGHT OUTER JOIN clubTBL C
            ON SC.clubName = C.clubName;

동아리에 가입하지 않은 학생과 가입한 학생이 없는 동아리 모두 출력됨

 

CROSS JOIN(상호 조인)

CROSS JOIN은 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능

CROSS JOIN의 결과 개수는 두 테이블 개수를 곱한 개수가 됨

/* 회원 테이블과 구매 테이블의 CROSS JOIN 구문 */
USE sqlDB;
SELECT *
	FROM buyTBL
		CROSS JOIN userTBL;

회원 테이블 개수 10 * 구매 테이블 개수 12 = 결과 120개

CROSS JOIN에는 ON 구문 사용할 수 없음 (테스트로 사용할 많은 용량의 데이터를 생성할 때 사용)

 

SELF JOIN(자체 조인)

별도의 구문 X, SELF JOIN을 활용하는 경우의 대표적인 예: 조직도 관련 테이블

/* 조직도 테이블 정의, 데이터 입력 */
USE sqlDB;
CREATE TABLE emptbl (emp CHAR(3), manager CHAR(3), empTel VARCHAR(8));

INSERT INTO empTBL VALUES(N'나사장',NULL,'0000');
INSERT INTO empTBL VALUES(N'김재무',N'나사장','2222');
INSERT INTO empTBL VALUES(N'김부장',N'김재무','2222-1');
INSERT INTO empTBL VALUES(N'이부장',N'김재무','2222-2');
INSERT INTO empTBL VALUES(N'우대리',N'이부장','2222-2-1');
INSERT INTO empTBL VALUES(N'지사원',N'이부장','2222-2-2');
INSERT INTO empTBL VALUES(N'이영업',N'나사장','1111');
INSERT INTO empTBL VALUES(N'한과장',N'이영업','1111-1');
INSERT INTO empTBL VALUES(N'최정보',N'나사장','3333');
INSERT INTO empTBL VALUES(N'윤차장',N'최정보','3333-1');
INSERT INTO empTBL VALUES(N'이주임',N'윤차장','3333-1-1');

/* SELF JOIN 활용
우대리 상관의 연락처를 확인하고 싶은 경우 */
SELECT A.emp AS '부하직원', B.emp AS '직속상관', B.empTel AS '직속상관연락처'
	FROM empTBL A
		INNER JOIN empTBL B
			ON A.manager = B.emp
	WHERE A.emp = '우대리';

 

/* SELF JOIN 활용
'김재무' 부하직원(이부장 부하직원 2명 포함) 4명 출력하고 싶은 경우
UNION 활용 */

SELECT A.emp AS '부하직원', B.emp AS '직속상관', B.empTel AS '직속상관연락처'
	FROM empTBL A
		INNER JOIN empTBL B
			ON A.manager = B.emp
	WHERE B.emp = '김재무'

UNION
SELECT A.emp AS '부하직원', B.emp AS '직속상관', B.empTel AS '직속상관연락처'
	FROM empTBL A
		INNER JOIN empTBL B
			ON A.manager = B.emp
	WHERE B.emp = '이부장';
    
/* SELF JOIN 활용
'김재무' 부하직원(이부장 부하직원 2명 포함) 4명 출력하고 싶은 경우
~를 포함하는: % 활용 */

SELECT A.emp AS '부하직원', B.emp AS '직속상관', B.empTel AS '직속상관연락처'
	FROM empTBL A
		INNER JOIN empTBL B
			ON A.manager = B.emp
	WHERE B.empTel LIKE '2222%';

 

 

UNION / UNION ALL / NOT IN / IN

· UNION

    · 두 쿼리의 결과를 행으로 합치는 것

    · 두 테이블의 열의 개수가 같아야 함

    · 열의 데이터 형식도 호환되거나 같아야 함

    · 중복된 열 제거되어 출력

    · UNION ALL을 쓰면 중복된 열까지 모두 출력

USE sqlDB;
SELECT stdName, addr FROM stdTBL
	UNION ALL
SELECT clubName, roomNo FROM clubTBL;

 

· NOT IN

    · 첫 번째 쿼리의 결과 중 두 번째 쿼리에 해당하는 것을 제외

/* sqlDB의 사용자 전부 조회, 전화가 없는 사람 제외 */
SELECT name, CONCAT(mobile1, mobile2) AS '전화번호' FROM userTBL
	WHERE name NOT IN ( SELECT name FROM userTBL WHERE mobile1 IS NULL);

 

· IN 

    · NOT IN과 반대로 첫 번째 쿼리의 결과 중에서 두 번째 쿼리에 해당되는 것만 조회

/* 전화가 없는 사람만 조회 */
SELECT name, CONCAT(mobile1, mobile2) AS '전화번호' FROM userTBL
	WHERE name IN ( SELECT name FROM userTBL WHERE mobile1 IS NULL);

 

SQL 프로그래밍

 

스토어드 프로시저 만들기

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

	이 부분에 SQL 프로그래밍 코딩... 

END $$
DELIMITER ;
CALL 스토어드 프로시저이름();

 

IF... ELSE...

DROP PROCEDURE IF EXISTS ifProc; -- 기존에 만든 적 있다면 삭제
DELIMITER $$
CREATE PROCEDURE ifProc()
BEGIN
	DECLARE var1 INT;    -- var1 변수 선언
	SET var1 = 100;    -- 변수에 값 대입
	
	IF var1 = 100 THEN    -- 만약 @var1이 100이라면,
		SELECT '100입니다.';
	ELSE
		SELECT '100이 아닙니다.';
	END IF;
END $$
DELIMITER ;
CALL ifProc();

/* 직원 번호 10001번에 해당하는 직원의 입사일이 5년이 넘었는지 확인 */
DROP PROCEDURE IF EXISTS ifProc2;
USE employees;

DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
	DECLARE hireDATE DATE;	-- 입사일
	DECLARE curDATE DATE;	-- 오늘
	DECLARE days INT;	-- 근무한 일수
	
	SELECT hire_date INTO hireDATE -- hire_date열의 결과를 hireDATE에 대입
		FROM employees.employees
		WHERE emp_no = 10001;
		
	SET curDATE = CURRENT_DATE(); -- 현재 날짜
	SET days = DATEDIFF(curDATE, hireDATE); -- 날짜의 차이, 일 단위
	
	IF (days/365) >= 5 THEN	-- 5년이 지났다면
		SELECT CONCAT('입사한 지 ', days, '일이나 지났습니다. 축하합니다!');
	ELSE
		SELECT '입사한 지 ' + days +'일밖에 안되었네요. 열심히 일하세요.';
	END IF;
END $$
DELIMITER ;
CALL ifProc2();

 

CASE

DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
	DECLARE point INT ;
	DECLARE credit CHAR(1);
	SET point = 77 ;
	
	IF point >= 90 THEN
		SET credit = 'A';
	ELSEIF point >= 80 THEN
		SET credit = 'B';
	ELSEIF point >= 70 THEN
		SET credit = 'C';
	ELSEIF point >= 60 THEN
		SET credit = 'D';
	ELSE
		SET credit = 'F';
	END IF;
	SELECT CONCAT('취득점수 ==> ', point), CONCAT('학점 ==> ', credit);
END $$
DELIMITER ;
CALL ifProc3();

/* 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문 활용하는 SQL 프로그래밍 작성

sqlDB의 buyTBL에 구매액(price * amount)이 1500원 이상인 고객은 '최우수고객', 1000원 이상인 고객은 '우수고객', 1원 이상인 고객은 '일반고객' 출력, 구매 실적이 전혀 없는 고객은 '유령고객' 출력

/* 1. buyTBL에서 구매액(price * amount)을 사용자 아이디(userID)별로 그룹화,
구매액이 높은 순으로 정렬 */
USE sqlDB;
SELECT userID, SUM(price * amount) AS '총구매액'
	FROM buyTBL
	GROUP BY userID
	ORDER BY SUM(price * amount) DESC;

/* 2. userTBL과 조인해 사용자명 출력 */
SELECT B.userID, U.name, SUM(price * amount) AS '총구매액'
	FROM buyTBL B
		INNER JOIN userTBL U
			ON B.userID = U.userID
	GROUP BY B.userID, U.name
	ORDER BY SUM(price*amount) DESC;

/* 3. 구매하지 않은 고객 명단이 없음,
오른쪽 테이블(userTBL)의 내용이 없더라도 나오도록 하기 위해 RIGHT OUTER JOIN으로 변경 */
SELECT B.userID, U.name, SUM(price*amount) AS '총구매액'
	FROM buyTBL B
		RIGHT OUTER JOIN userTBL U
			ON B.userID = U.userID
	GROUP BY B.userID, U.name
	ORDER BY SUM(price*amount) DESC;

/* 4. 구매 내역이 없는 고객은 userID 부분이 NULL로 출력됨
userID 기준을 buyTBL이 아닌 userTBL로 변경 */
SELECT U.userID, U.name, SUM(price*amount) AS '총구매액'
	FROM buyTBL B
		RIGHT OUTER JOIN userTBL U
			ON B.userID = U.userID
	GROUP BY U.userID, U.name
	ORDER BY SUM(price*amount) DESC

/* 5. 총구매액에 따른 고객 분류 CASE 구문을 SELECT에 추가 */
SELECT U.userID, U.name, SUM(price*amount) AS '총구매액',
		CASE
			WHEN (SUM(price*amount) >= 1500) THEN '최우수고객'
			WHEN (SUM(price*amount) >= 1000) THEN '우수고객'
			WHEN (SUM(price*amount) >= 1) THEN '일반고객'
			ELSE '유령고객'
		END AS '고객등급'
	FROM buyTBL B
		RIGHT OUTER JOIN userTBL U
			ON B.userID =U.userID
	GROUP BY U.userID, U.name
	ORDER BY SUM(price*amount) DESC;

전에 입력한 데이터로는 '우수고객'이 나오지 않아 buyTBL에서 'JYP'이 구매한 금액만 1000으로 임의로 수정해 우수고객 출력되는지 확인함

> 위에서는 1600이 출력되는 것이 맞으나 마지막 코드 입력 전에 'JYP'의 구매 금액을 수정했기 때문에 마지막 사진에서만 총구매액 1000

 

WHILE과 ITERATE/LEAVE

다른 프로그래밍 언어의 WHILE과 동일한 개념

해당 <BOOL 식>이 참인 동안에는 계속 반복되는 반복문

 ITERATE문 = CONTINUE, LEAVE문은 BREAK문과 같은 역할

/* 형식 */
WHILE <BOOL 식> DO
	SQL 명령문들...
END WHILE;

/* 1부터 100까지의 합 구하기
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 hap;
END $$
DELIMITER ;
CALL whileProc();

/* 1에서 100까지 합계에서 7의 배수는 합계에서 제외시키고,
합계가 1000이 넘으면 반복문 종료 및 합계 출력하고 싶은 경우
ITERATE문과 LEAVE문 사용 */
DROP PROCEDURE IF EXISTS whileProc2;
DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
	 DECLARE i INT; -- 1에서 100까지 증가할 변수
	 DECLARE hap INT; -- 더한 값을 누적할 변수
	 SET i = 1;
	 SET hap = 0;
	 
	 myWhile: WHILE (i <= 100) DO -- while문에 label 지정
	 	IF (i%7 = 0) THEN
	 		 SET i = i + 1;
	 		 ITERATE myWhile; -- 지정한 label로 가서 계속 진행
	 END IF;
	 
	 	SET hap = hap + i;
	 	IF (hap > 1000) THEN
	 		 LEAVE myWhile; -- 지정한 label문을 떠남, while 종료
	 END IF;
	 	  SET i = i + 1;
	 END WHILE;
	 
	 SELECT hap;
END $$
DELIMITER ;
CALL whileProc2();

ITERATE문을 만나면 바로 WHILE문으로 이동해 비교(@i <= 100)를 다시 하고, LEAVE문을 만나면 WHILE문을 빠져나옴

 

/* 사번 10001~10010까지 출력, IF ELSE, WHILE 이용한 스토어드 프로시저 연습 1 */
DROP PROCEDURE IF EXISTS whilePrac1;
USE employees;
DELIMITER $$
CREATE PROCEDURE whilePrac1()
BEGIN
	DECLARE hireDATE DATE;
	DECLARE curDATE DATE;
	DECLARE days INT;
	DECLARE i INT;
	SET i = 10001;
	
	WHILE (i <= 10010) DO
		SELECT hire_date INTO hireDate
			FROM employees.employees
			WHERE emp_no = i;
	SET curDATE = CURRENT_DATE(); -- 현재 날짜 
	SET days = DATEDIFF(curDATE, hireDATE);
	
	IF (days/365) >= 5 THEN	-- 5년이 지났다면
		SELECT CONCAT('입사한 지 ', days, '일이나 지났습니다.');
	ELSE
		SELECT '입사한 지 ' + days +'일밖에 안되었네요. 열심히 일하세요.';
	END IF;
		SET i = i + 1;
	END WHILE;
	
END $$
DELIMITER ;
CALL whilePrac1();

/* 시작 사번: 10500, 반복 횟수: 5, 근무 년수: 3을 넣어 출력, 스토어드 프로시저 연습 2 */
DROP PROCEDURE IF EXISTS whilePrac2;
USE employees;
DELIMITER $$
CREATE PROCEDURE whilePrac2(IN startValue INT, IN maxIterations INT, IN period INT)
BEGIN
	DECLARE hireDATE DATE;
	DECLARE curDATE DATE;
	DECLARE days INT;
	DECLARE rep INT;
	SET rep = startValue + maxIterations;
	
	back: WHILE (startValue < rep) DO
		SELECT hire_date INTO hireDate
			FROM employees.employees
			WHERE emp_no = startValue;
	SET curDATE = CURRENT_DATE();
	SET days = DATEDIFF(curDATE, hireDATE);
	
		IF (days/365) >= period THEN
			SELECT CONCAT('입사한 지 ', period, '년이 지났고, 총', days, '일이나 지났습니다.');
		ELSE
			SELECT '입사한 지 ' + days +'일밖에 안되었네요. 열심히 일하세요.';
		END IF;
	
	SET startValue = startValue + 1;
		IF (startValue >= rep) THEN
			LEAVE back;
		END IF;
		
	END WHILE;
	
END $$
DELIMITER ;
CALL whilePrac2(10500, 5, 3); -- 시작 사번: 10500, 반복 횟수: 5, 근무 년수: 3

 

오류 처리

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

· 액션

    - 오류 발생 시의 행동 정의

    - CONTINUE와 EXIT 둘 중 하나 사용

    - CONTINUE가 나오면 제일 뒤의 '처리할_문장' 부분이 처리

 

· 오류 조건: 어떤 오류를 처리할 것인지 지정

    - MariaDB의 오류 코드 숫자

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

    - SQLEXCEPTION: 대부분의 오류

· 처리할_문장: 처리할 문장이 하나라면 한 문장이 나오고, 여러 개일 경우 BEGIN... END로 묶어 줄 수 있음

 

MariaDB의 오류 코드(Error Code)는 1000~1885, 1900~1981까지 정의되어 있음

ex) SELECT * FROM noTable;을 실행할 때 noTabel이 없을 경우에는 오류 코드는 1146, 상태 코드는 '42S02' 발생

https://mariadb.com/kb/en/library/mariadb-error-codes/ 

MariaDB Error Codes

DROP PROCEDURE IF EXISTS errorProc;
DELIMITER //
CREATE PROCEDURE errorProc()
BEGIN
	DECLARE CONTINUE HANDLER FOR 1146 SELECT '테이블이 없어요 ㅠ' AS '메시지';
	SELECT * FROM noTable; -- noTable은 없음
END //
DELIMITER ;
CALL errorProc();

위 오류 코드 자리에 상태 코드를 입력해도 됨

 

SHOW ERRORS문은 오류에 대한 코드와 메시지 출력

ROLLBACK은 진행 중인 작업을 취소시킴(원래대로)

COMMIT은 작업을 완전히 확정시키는 구문

 

SHOW COUNT(*) ERROR문은 발생된 오류의 개수를 출력, SHOW WARNINGS문은 경고에 대한 코드와 메시지 출력

 

동적 SQL

PREPARE는 SQL문을 실행하지는 않고 미리 준비만 해놓고, EXECUTE문은 준비한 쿼리문을 실행

> 실행 후 DEALLOCATE PREPARE로 문장을 해제해 주는 것이 바람직함

PREPARE에 입력한 쿼리문을 준비한 후 나중에 EXECUTE로 실행

> 미리 준비한 후 나중에 실행하는 것을 '동적 SQL'이라고 함

PREPARE문에서 ?으로 향후에 입력될 값을 비워놓고 EXECUTE에서는 USING을 이용해 값을 전달할 수 있음