- MySQL에서 지원하는 데이터 형식의 종류
☞ 숫자 데이터 형식
> 숫자형 데이터 형식은 정수, 실수 등의 숫자를 표현한다. 자주 사용되는 것은 이름 앞에 별표를 해 놓았으니 유심히 살펴보자.
> DECIMAL 데이터 형식은 정확한 수치를 저장하게 되고 FLOAT, REAL은 근사치의 숫자를 저장한다. 대신 FLOAT, REAL은 상당히 큰 숫자를 저장할 수 있단느 장점이 있다.
> 그러므로 소수점이 들어간 실수를 저장하려면 되도록 DECIMAL을 사용하는 것이 바람직하다. 예로 -999999.99부터 +999999.99까지의 숫자를 저장할 경우에는 DECIMAL(9, 2)로 설정하면 된다.
> 또 MySQL은 부호 없는 정수를 지원하는데 부호 없는 정수로 지정하면 TINYINT는 0~255, SMALLINT는 0~65535, MEDIUMINT는 0~16777215, INT는 0~약 42억, BIGINT는 0~약 1800경으로 표현할 수 있다.
> 부호 없는 정수를 저장할 때는 UNSIGHED 예약어를 붙여주면 된다. FLOAT, DOUBLE, DECIMAL도 UNSIGNED 예약어를 사용할 수 있지만 자주 사용되지는 않는다.
☞ 문자 데이터 형식
> CHAR 형식은 고정길이 문자형으로 자릿수가 고정되어 있다. 예를 들어 CHAR(100)에 'ABC' 3글자만 저장해도 100자리를 모두 확보한 후에 앞에 3자리를 사용하고 뒤의 97자리는 낭비하게 되는 결과가 나온다.
> VARCHAR 형식은 가변길이 문자형으로 VARCHAR(100)에 'ABC' 3글자를 저장할 경우에 3자리만 사용하게 된다. 그래서 공간을 효율적으로 운영할 수 있다.
> 하지만, CHAR 형식으로 설정하는 것이 INSERT/UPDATE 시에 일반적으로 더 좋은 성능을 발휘한다.
> BINARY와 VARBINARY는 바이트 단위의 이진 데이터 값을 저장하는데 사용된다.
> TEXT 형식은 대용량의 글자를 저장하기 위한 형식으로 필요한 크기에 따라서 TINYTEXT, TEXT, MIDIUMTEXT, LONGTEXT 등의 형식을 사용할 수 있다.
> BLOB(Binary Large Object)은 사진 파일, 동영상 파일, 문서 파일 등의 대용량의 이진 데이터를 저장하는데 사용될 수 있다. BLOB도 필요한 크기에 따라서 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB 등의 형식을 사용할 수 있다.
> ENUM은 열거형 데이터를 쓸 때 사용될 수 있는데 예로 요일(월, 화, 수, 목, 금, 토, 일)을 ENUM 형식으로 설정할 수 있다.
> SET은 최대 64개를 준비한 후에 입력은 그 중에서 2개씩 세트로 데이터를 저장시키는 방식을 사용한다.
☞ 날짜와 시간 데이터 형식
> 날짜와 시간형 테이터에 대해서는 간단한 예를 통해서 그 차이를 확인하자.
SELECT CAST('2020-10-19 12:35:29.123' AS DATE) AS 'DATE';
SELECT CAST('2020-10-19 12:35:29.123' AS TIME) AS 'TIME';
SELECT CAST('2020-10-19 12:35;29.123' AS DATETIME) AS 'DATETIME';
☞ 기타 데이터 형식
☞ LONGTEXT, LONGBLOB
> MySQL은 LOB(Large Oject : 대용량 데이터)을 저장하기 위해서 LONGTEXT, LONGBlOB 데이터 형식을 지원한다.
> 지원되는 데이터 크기는 약 40GB의 파일을 하나의 데이터로 저장할 수 있다. 예로 장편 소설과 같은 큰 텍스트 파일이라면 그 내용을 전부 LONGTEXT 형식으로 지정된 하나의 컬럼에 넣을 수 있고 동영상 파일과 같은 큰 바이너리 파일이라면 그 내용을 전부 LONGBLOB 형식으로 지정된 하나의 컬럼에 넣을 수 있다.
> 예로 아래 그림과 같은 구성이 가능하다.
> 위 그림을 보면 영화 대본 열에는 영화 대본 전체가 들어가고, 영화동영상 열에는 실제 영화 파일 전체가 들어갈 수 있다.
- 변수의 사용
> SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다. 변수의 선언과 값의 대입은 다음과 같은 형식을 따른다.
SET @변수이름 = 변수의 값; -- 변수의 선언 및 값 대입
SELECT @변수이름; -- 변수의 값 출력
※ 스토어드 프로시저나 함수 안에서의 변수를 선언하는 방법은 DECLARE문으로 선언한 후에 사용할 수 있다. 또한 스토어드 프로시저나 함수 안에서는 @변수명 형식이 아닌 그냥 변수명만 사용한다. 구분하자면 @변수명은 '전역 변수'처럼, DECLARE 변수명은 스토어드 프로시저나 함수 안에서 '지역 변수'처럼 사용된다.
> 변수는 Workbench를 재시작할 때까지는 계속 유지되지만 Workbench를 닫았다가 재시작하면 소멸된다.
> Workbench를 종료하고 새로 실행한 후, 저장해 놓은 sqlDB.sql을 이용해서 sqlDB 데이터베이스를 초기화하자.
> 쿼리 창을 하나 열자. 변수를 몇 개 지정하고 변수에 값을 대입한 후에 출력해 본다. (전체를 한 번에 선택해서 실행한다.)
USE sqlDB;
SET @myVar1 = 5;
SET @myVar2 = 3;
SET @myVar3 = 4.25;
SET @myVar4 = '가수 이름 ==> ';
SELECT @myVar1;
SELECT @myVar2 + @myVar3;
SELECT @myVar4, Name FROM userTbl WHERE height > 180;
> LIMIT에는 원칙적으로 변수를 사용할 수 없으나 PREPARE와 EXECUTE를 활용해서 변수를 활용할 수 있다.
SET @myVar1 = 3;
PREPARE myQuery
FROM 'SELECT Name, height FROM userTbl ORDER BY height LIMIT ?';
EXECUTE myQuery USING @myVar1;
> LIMIT는 LIMIT 3과 같이 직접 숫자를 넣어야 하며 LIMIT @변수 형식으로 사용하면 오류가 발생하기 때문에 다른 방식을 사용해야 한다.
> PREPARE 쿼리이름 FROM '쿼리문'은 쿼리이름에 '쿼리문'을 준비만 해놓고 실행하지는 않는다.
> 그리고 EXECUTE 쿼리이름을 만나는 순간에 실행이 된다. EXECUTE는 USING @변수를 이용해서 '쿼리문'에서 ?으로 처리해 놓은 부분에 대입이 된다. 결국 LIMIT @변수 형식으로 사용된 것과 동일한 효과를 갖는다.
- 데이터 형식과 형 변환
☞ 데이터 형식 변환 함수
> 가장 일반적으로 사용되는 데이터 형식 변환과 관련해서는 CAST(), CONVERT() 함수를 사용한다. CAST(), CONVERT()는 형식만 다를 뿐 거의 비슷한 기능을 한다.
CAST (expression AS 데이터형식 [ (길이) ]);
CONVERT (expression , 데이터형식 [ (길이) ]);
> 데이터 형식 중에서 가능한 것은 BINARY, CHAR, DATA, DATETIME,DECIMAL, JSON, SIGNED INTEGER, TIME, UNSIGNED INTEGER 등이다.
> 다음은 sqlDB의 구매 테이블(buyTbl)에서 평균 구매 개수를 구하는 쿼리문이다.
USE sqlDB;
SELECT AVG(amount) AS '평균 구매 개수' FROM buyTbl;
> 결과는 2.8167개가 나왔다. 그런데, 개수이므로 정수로 보기 위해서 다음과 같이 CAST() 함수나 CONVERT() 함수를 사용할 수 있다.
SELECT CAST(AVG(amount) AS SIGNED INTEGER) AS '평균 구매 개수' FROM buyTbl;
SELECT CONVERT(AVG(amount), SIGNED INTEGER) AS '평균 구매 개수' FROM buyTbl;
> 반올림한 정수의 결과를 확인할 수 있다.
> 다양한 구분자를 날짜 형식으로도 변경할 수 있다.
SELECT CAST('2020$12$12' AS DATE);
SELECT CAST('2020/12/12' AS DATE);
SELECT CAST('2020%12%12' AS DATE);
SELECT CAST('2020@12@12' AS DATE);
> 쿼리의 결과를 보기 좋도록 처리할 때도 사용된다. 단가와 수량을 곱한 실제 입금액을 표시하는 쿼리는 다음과 같이 사용할 수 있다.
SELECT num, CONCAt(CAST(price AS CHAR(10)), 'X', CAST(amount AS CHAR(4)), '=') AS '단가X수량',
price * amount AS '구매액'
FROM buyTbl;
☞ 암시적인 형 변환
> 형 변환 방식에는 명시적인 변환과 암시적인 변환, 두 가지가 있다. 명시적인 변환이란 CAST() 또는 CONVERT() 함수를 이용해서 데이터 형식을 변환하는 것을 말한다. 암시적인 형 변환이란 CAST()나 CONVERT() 함수를 사용하지 않고 형이 변환되는 것을 말한다.
> 다음의 예를 보자.
(1) SELECT '100' + '200';
(2) SELECT CONCAT('100', '200');
(3) SELECT CONCAT(100, '200');
(4) SELECT 1 > '2mega';
(5) SELECT 3 > '2MEGA'
(6) SELECT 0 = 'mega2';
> (1) 문자열+문자열은 더하기 연산자 때문에 문자열이 숫자로 변경되어서 계산되었다.
(2) 문자열을 연결해주는 CONCAT() 함수이기에 문자열이 그대로 문자열로 처리되었다.
(3) CONCAT() 함수 안의 숫자는 문자열로 변환되어 처리되었다.
(4) 앞에 '2'가 들어간 문자열이 숫자로 변경되어서 결국 '1 > 2'의 비교가 된다. 결과는 false(0)으로 나왔다.
(5) 마지막 'mega2' 문자열은 숫자로 변경되어도 그냥 0이 되기 때문에 결국 '0 = 0'이 되어 true(1)의 결과가 나왔다.
- MySQL 내장 함수
☞ 제어 흐름 함수
> 제어 흐름 함수는 프로그램의 흐름을 제어한다.
* IF(수식, 참, 거짓)
> 수식이 참 또는 거짓인지 결과에 따라서 2중 분기한다.
SELECT IF(100>200 '참이다', '거짓이다');
> '거짓이다'가 출력된다.
* IFNULL(수식1, 수식2)
> 수식이 NULL이 아니면 수식1이 반환되고, 수식1이 NULL이면 수식2가 반환된다.
SELECT IFNULL(NULL, '널이군요'), IFNULL(100, '널이군요');
> 첫 번째는 '널이군요'가 출력되고 두 번째는 100이 출력된다.
* NULLIF(수식1, 수식2)
> 수식1과 수식2가 같으면 NULL을 반환하고, 다르면 수식1을 반환한다.
SELECT NULLIF(100,100), NULLIF(200,100);
> 첫 번째는 NULL이, 두 번째는 200이 반환된다.
* CASE ~ WHEN ~ ELSE ~ END
> CASE는 내장 함수는 아니며 연산자로 분류된다. 다중 분기에 사용될 수 있으므로 내장 함수와 함께 알아두자.
SELECT CAST 10
WHEN 1 THEN '일'
WHEn 5 THEN '오'
WHEN 10 THEN '십'
ELSE '모름'
END;
> CASE 뒤의 값이 10이므로 세 번쨰 WHEN이 수행되어 '십'이 반환된다. 만약 해당하는 사항이 없다면 ELSE 부분이 반환된다.
☞ 문자열 함수
> 문자열 함수는 문자열을 조작한다.
* ASCII(아스키 코드), CHAR(숫자)
> 문자의 아스키 코드값을 돌려주거나 숫자의 아스키 코드값에 해당하는 문자를 돌려준다.
SELECT ASCII('A'), CHAR(65);
> 65와 'A'를 돌려준다.
> Workbench의 버그로 CHAR(65)의 결과가 'BLOB'으로 보일 수 있다. 그러면 'BLOB' 글자에서 마우스 오른쪽 버튼을 클릭한 후 [Open Value In Viewer]를 선택하면 된다.
* 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('가나다');
> MySQL 5.7은 기본적으로 UTF-8 코드를 사용하기 때문에 영문은 3Byte를, 한글은 3 x 3 = 9Byte를 할당한다.
* CONCAT(문자열1, 문자열2 ...), CONCAT_WS(문자열1, 문자열2 ...)
> 문자열을 이어준다. CONCAT()_WS는 구분자와 함께 문자열을 이어준다.
SELECT CONCAT_WS('/', '2020', '01', '01);
> 구분자 '/'를 추가해서 '2020/01/01'이 반환된다.
* ELT(위치 문자열1, 문자열2, ...), FIELD(찾을 문자열, 문자열1, 문자열2, ...), FIND_IN_SET(찾을 문자열, 문자열 리스트), INSTR(기준 분자열, 부분 문자열), LOCATE(부분 문자열, 기준 문자열)
> ELT()는 위치 번째에 해당하는 문자열을 반환한다.
> FIELD()는 찾을 문자열의 위치를 찾아서 반환한다. 매치되는 문자열이 없으면 0을 반환한다.
> FIND_IN_SET()은 찾을 문자열을 문자열 리스트에서 찾아서 위치를 반환한다.
> INSTR()는 기준 문자열에서 부분 문자열을 찾아서 그 시작 위치를 반환한다.
> LOCATE()는 INSTR()과 동일하지만 파라미터의 순서가 반대로 되어 있다.
SELECT ELT(2, '하나', '둘', '셋'),
FIELD('둘', '하나', '둘', '셋'),
FIND_IN_SET('둘', '하나,둘,셋'),
INSTR('하나둘셋', '둘'),
LOCATE('둘', '하나둘셋');
> 각각 '둘', 2, 2, 3, 3을 반환한다.
※ LOCATE()와 POSITION()은 동일한 함수다.
* FORMAT(숫자, 소수점 자릿수)
> 숫자를 소수점 아래 자릿수까지 표현한다. 또한 1000 단위마다 콤마를 표시해 준다.
SELECT FORMAT(123456,123456, 4);
> '123,456.1234'를 반환한다.
* BIN(숫자), HEX(숫자), OCT(숫자)
> 각각 2진수, 16진수, 8진수의 값을 반환한다.
SELECT BIN(31), HEX(31), OCT(31);
> 2진수 11111, 16진수 1F, 8진수 37을 반환한다.
* INSERT(기준 문자열, 위치, 길이, 삽입할 문자열)
> 기준 문자열의 위치부터 길이만큼 지우고 삽입할 문자열을 끼워 넣는다.
SELECT INSERT('abcdefghi', 3, 4, '@@@@'), INSERT('abcdefghi', 3, 2, '@@@@');
> 'ab@@@@ghi'와 'ab@@@@efghi'를 반환한다.
* LEFT(문자열 길이), RIGHT(문자열 길이)
> 왼쪽 또는 오른쪽에서 문자열의 길이만큼 반환한다.
SELECT LEFT('abcdefghi', 3), RIGHT('abcdefghi', 3);
> 'abc'와 'ghi'를 반환한다.
* UCASE(문자열), LCASE(문자열)
> 소문자를 대문자로, 대문자를 소문자로 변경한다.
SELECT LCASE('abcdEFGH'), UCASE('abcdEFGH');
> 'abcdefgh'와 'ABCDEFGH'를 반환한다.
* UPPER(문자열), LOWER(문자열)
> 소문자를 대문자로, 대문자를 소문자로 변경한다.
SELECT LOWER('abcdEFGH'), UPPER('abcdEFGH');
> 'abcdefgh'와 'ABCDEFGH'를 반환한다.
* LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열)
> 문자열을 길이만큼 늘린 후에 빈 곳에 채울 문자열로 채운다.
SELECT LPAD('이것이', 5, '##'), RPAD('이것이', 5, '##');
> '##이것이'와 '이것이##'를 반환한다.
* TRIM(문자열), TRIM(방향 자를_문자열 FROM 문자열)
> TRIM(문자열)은 문자열의 앞뒤 공백을 모두 없앤다. TRIM(방향 자를_문자열 FROM 문자열)에서 방향은 LEADING(앞), BOTH(양쪽), TRAILING(뒤)이 나올 수 있다.
SELECT TRIM(' 이것이 '), TRIN(BOTH 'ㅋ' FROM 'ㅋㅋㅋ재밌어요.ㅋㅋㅋ');
> '이것ㅇ'와 '재밌어요.'를 반환한다.
* REPEAT(문자열, 횟수)
> 문자열을 횟수만큼 반복한다.
SELECT REPEAT('이것이', 3);
> '이것이이것이이것이'를 반환한다.
* REPLACE(문자열, 원래 문자열, 바꿀 문자열)
> 문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꿔준다.
SELECT REPLACE('이것이 MySQL이다', '이것이', 'This is');
> 'This is MySQL이다.'를 반환한다.
* REVERSE(문자열)
> 문자열의 순서를 거꾸로 만든다.
SELECT REVERSE('MySQL');
> 'LQSyM'을 반환한다.
* SPACE(길이)
> 길이만큼 공백을 반환한다.
SELECT CONCAT('이것이', 'SPACE(10), 'MySQL이다');
> '이것이 MySQL이다'를 반환한다.
* SUBSTRING(문자열, 시작위치, 길이) 또는 SUBSTRING(문자열 FROM 시작위치 FOR 길이)
> 시작위치부터 길이만큼 문자를 반환한다. 길이가 생략되면 문자열의 끝까지 반환한다.
SELECT SUBSTRING('대한민국만세', 3, 2);
> '민국'을 반환한다.
※ SUBSTRING(), SUBSTR(), MID()는 모두 동일한 함수다.
* SUBSTRING_INDEX(문자열, 구분자, 횟수)
> 문자열에서 구분자가 왼쪽부터 횟수 번째까지 나오면 그 이후의 오른쪽은 버린다. 횟수가 음수면 오른쪽부터 세고 왼쪽을 버린다.
SELECT SUBSTRING_INDEX('cafe.naver.com', '.', 2), SUBSTRING_INDEX('cafe.naver.com', '.', -2);
> 'cafe.naver'와 'naver.com'을 반환한다.
☞ 수학 함수
* ABS(숫자)
> 숫자의 절댓값을 계산한다.
SELECT ABS(-100);
> 절댓값인 100을 반환한다.
* ACOS(숫자), ASIN(숫자), ATAN(숫자), ATAN2(숫자1, 숫자2), SIN(숫자), COS(숫자), TAN(숫자)
> 삼각 함수에 관련된 함수를 제공한다.
* CEILING(숫자), FLOOR(숫자), ROUND(숫자)
> 올림, 내림, 반올림을 계산한다.
SELECT CEIling(4.7), FLOOR(4.7), ROUND(4.7);
> 5, 4, 5를 반환한다.
※ CEILING()과 CEIL()은 동일한 함수다.
* CONV(숫자, 원래 진수, 변환할 진수)
> 숫자를 원래 진수에서 변환할 진수로 계산한다.
SELECT CONV('AA', 16, 2), CONV(100, 10, 8);
> 16진수 AA를 2진수로 변환한 10101010과 100을 8진수로 변환한 144가 반환된다.
* DEGREES(숫자), RADIANS(숫자), PI()
> 라디안 값을 각도값으로, 각도값을 라디안 값으로 반환한다. PI()는 파이값인 3.141592를 반환한다.
SELECT DEGREES(PI()), RADIANS(180);
> 파이의 각도값인 180과 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;
> 모두 157을 10으로 나눈 나머지 값 7을 반환한다.
* POW(숫자1, 숫자2), SORT(숫자)
> 거듭제곱값 및 제곱근을 구한다.
SELECT POW(2, 3), SORT(9);
> 2의 3제곱값 8과 루드 9의 값 3을 반환한다.
※ POW()와 POWER()는 동일한 함수다.
* RAND()
> RAND()는 0 이상 1미만의 실수를 구한다. 만약 'm <= 임의의 정수 < n'을 구하고 싶다면 RAND(m + (RAND() * (n-m)을 사용하면 된다.
SELECT RAND(), FLOOR(1 * (RAND() * (6-1)));
> 0~1 미만의 실수와 1~6 사이의 숫자를 구한다.
* SIGN(숫자)
> 숫자가 양수, 0, 음수인지를 구한다. 결과는 1, 0, -1 셋 중에 하나를 반환한다.
SELECT SIGN(100), SIGN(0), SIGN(-100.123);
> 1, 0, -1을 반환한다.
* TRUNCATE(숫자, 정수)
> 숫자를 소수점을 기준으로 정수 위치까지 구하고 나머지는 버린다.
SELECT TRUNCATE(12345.12345, 2), TRUNCATE(12345.12345, -2);
> 12345.12와 12300을 반환한다.
☞ 날짜와 시간 함수
* ADDDATE(날짜, 차이), SUBDATE(날짜, 차이)
> 날짜를 기준으로 차이를 더하거나 뺀 날짜를 구한다.
SELECT ADDDATE('2020-01-01', INTERVAL 31 DAYS), ADDDATE('2020-01-01', INTERVAL 1 MONTH);
SELECT SUBDATE('2020-01-01', INTERVAL 31 DAYS), SUBDATE('2020-01-01', INTErVAL 1 MONTH);
> 31일 후 또는 한 달 후인 '2020-02-01'과 31일 전 또는 한 달 전인 '2019-12-01'을 반환한다.
※ ADDDATE()와 DATE_ADD()는 동일한 함수이며 SUBDATE()와 DATE_SUB()도 동일한 함수이다.
* ADDTIME(날짜/시간, 시간), SUBTIME(날짜/시간, 시간)
> 날짜/시간을 기준으로 시간을 더하거나 뺀 결과를 구한다.
SELECT ADDTIME('2020-01-01 23:59:59', '1:1:1'), ADDTIME('15:00:00', '2:10:10');
SELELCT SUBTIME('2020-01-01 23:59:59', '1:1:1'), SUBTIME('15:00:00', '2:10:10');
> 1시간 1분 1초 후인 '2020-01-02 01:01:00과 2시간 10분 10초 후인 '17:10:10'을 반환한다. 또 1시간 1분 1초 전인 '2020-01-01 22:58:58'과 2시간 10분 10초 전인 '12:49:50'을 반환한다.
* CURDATE(), CURTIME(), NOW(), SYSDATE()
> CURDATE()는 현재 연-월-일을, CURTIME()은 현재 시:분:초를 구한다. NOW()와 SYSDATE는 현재 '연-월-일 시:분:초'를 구한다.
※ CURDATE(), CURRENT_DATE(), CURRENT_DATE는 모두 동일하며 CURTIME(), CURRENT_TIME(), CURRRENT_TIME도 모두 동일하다. NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP()도 모두 동일하다.
* YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시간), MOCROSECOND(시간)
> 날짜 또는 시간에서 연, 월, 일, 시, 분, 초, 밀리초를 구한다.
SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAYOFMONTH(CURDATE);
SELECT HOUR(CURTIME()), MINUTE(CURTIME()), SECOND(CURRENT_TIME), MICRSECOND(CURRENT_TIME);
> 현재 연, 월 일, 몇 시, 분, 초, 밀리초를 구한다.
※ DAYOFMONTH()와 DAY()는 동일한 함수다.
* DATE(), TIME()
> DATETIME 형식에서 연-월-일 몇 시:분:초만 추출한다.
SELECT DATE(NOW()), TIME(NOW());
* DATEDIFF(날짜1, 날짜2), TIMEDIFF(날짜1 또는 시간1, 날짜2 또는 시간2)
> DATEDIFF()는 날짜1-날짜2의 일수를 결과로 구한다. 즉, 날짜2에서 날짜1까지 몇 일이 남았는지 구한다. TIMEDIFF()는 시간1-시간2의 결과를 구한다.
SELECT DATEDIFF('2020-01-01', NOW()), TIMEDEFF('23:23:59', '12:11:10');
> 2020년 1월 1일에서 오늘의 날짜를 뺀 일자와 11:12:49가 반환된다.
* DAYOFWEEK(날짜), MONTHNAME(), DAYOFYEAR(날짜)
> 요일(1:일 2:월~7:토) 및 1년 중 몇 번째 날짜인지를 구한다.
SELECT DAYOFWEEK(CURDATE()), MONTHNAME(CURDATE()), DAYOFYEAR(CURDATE());
> 현재 요일과 월 이름 그리고 1년 중 몇 일이 지났는지를 반환한다.
* LAST_DAY(날짜)
> 주어진 날짜의 마지막 날짜를 구한다. 주로 그 달이 몇 일까지 있는지 확인할 때 사용한다.
SELECT LAST_DAY('2020-02-01');
> '2020-02-29'를 반환한다.
* MAKEDATE(연도, 정수)
> 연도에서 정수만큼 지난 날짜를 구한다.
SELECT MAKEDATE(2020, 32);
> 2020년의 32일이 지난 날짜인 '2020-02-01'을 반환한다.
* MAKETIME(시, 분, 초)
> 시, 분, 초를 이용해서 '시:분:초'의 TIME 형식을 만든다.
SELECT MAKETIME(12, 11, 10);
> '12:11:10'의 TIME 형식을 반환한다.
* PERIOD_ADD(연월, 개월수), PEIOD_DIFF(연월1, 연월2)
> PERIOD_ADD()는 연월에서 개월만큼의 개월이 지난 연월을 구한다. 연월은 YYYY 또는 YYYYMM 형식을 사용한다. PERIOD_DIFF()는 연월1-연월2의 개월수를 구한다.
SELECT RERIOD_ADD(202001, 11), PERIOD_DIFF(202001, 201812);
> 2020 12월과 13개월을 반환한다.
* QUARTER(날짜)
> 날짜가 4분기 중에서 몇 분기인지를 구한다.
SELECT QUARTER('2020-07-07');
> 7월 7일에 해당하는 3분기를 반환한다.
* TIME_TO_SEC(시간)
> 시간을 초 단위로 구한다.
SELECT TIME_TO_SEC('12:11:10');
> 43870초가 반환된다.
☞ 시스템 정보 함수
* USER(), DATABASE()
> 현재 사용자 및 현재 선택된 데이터베이스를 구한다.
SELECT CURRENT_USER(), DATABASE();
※ USER(), SESSION_USER(), CURRENT_USER()는 모두 동일하다. DATABASE()와 SCHEMA()도 동일한 함수다.
* FOUND_ROWS()
> 바로 앞의 SELECT문에서 조회된 행의 개수를 구한다.
USE sqlDB;
SELECT * FROM userTBL;
SELECT FOUND_ROWS();
> 고객 테이블의 10개 행을 조회했으므로 10이 반환된다.
* ROW_COUNT()
> 바로 앞의 INSERT, UPDATE, DELETE문에서 입력, 수정, 삭제된 행의 개수를 구한다. CREATE, DROP문은 0을 반환하고 SELECT문은 -1을 반환한다.
USE sqlDB;
UPDATE buyTBL SET price=price+2;
SELECT ROW_COUNT();
> 구매 테이블의 12개 행을 변경했으므로 12가 반환된다.
* VERSION()
> 현재 MySQL의 버전을 구한다.
* SLEEP(초)
> 쿼리의 실행을 잠깐 멈춘다.
SELECT SLEEP(5);
SELECT '5초후에 이게 보여요';
☞ TEXT 데이터 형식을 이용해서 대용량 데이터를 입력해 보자.
> max형의 데이터를 정의한다.
USE sqlDB;
CREATE TABLE maxTbl(col1 LONGTEXT, col2 LONGTEXT);
> REPEAT() 함수를 활용해서 백만 개 문자의 대량 데이터를 입력하자.
INSERT INTO maxTbl VALUES(REPEAT('A', 1000000), REPEAT('가', 1000000);
> 입력된 값의 크기를 확인해 보자.
SELECT LENGTH(col1), LENGTH(col2) FROM maxTBL;
> 약 1M와 3M의 크기가 입력되었다. 크기가 다른 이유는 영문자는 1Byte를 차지하지만, 한글은 utf-9 코드이므로 3Byte를 차지하기 때문이다.
> 좀더 큰 값을 입력해 보자. 각각 10,000, 000개 문자의 대량 데이터를 입력하자.
INSERT INTO maxTbl VALUES (REPREAT('A', 10000000), REPEAT('가' 10000000));
> 오류가 발생한다. 메시지를 보면 max_allowed_packet의 최대값이 약 4백만 바이트이기 때문이다.
> max_allowed_packet 시스템의 변수값을 변경해서 다시 입력해 보자.
> Workbench를 종료하고 명령 프롬프트를 관리자 모드로 연 후 다음 명령어로 my.ini 파일을 확인하자.
CD %PROGRAMDATA%
CD MySQL
CD "MySQL Server 5.7"
DIR
> NOTEPAD my.ini 명령으로 파일을 열고 메뉴의 [보기] >> [상태 표시줄]을 선택에서 체크를 하자. 그러면 오른쪽 아래에 행 번호가 보일 것이다.
> [Ctrl + F]를 눌러서 max_all로 검색하면 대략 287행쯤에 보일 것이다. 1G(1000M)로 변경하자.
max_allowed_pachet=1000M
> 저장하고 노트패드를 종료하자. MySQL 서버를 재시작 해야 하므로 컴퓨터를 재부팅하자.
* 다른 방법은 명령 프롬프트를 관리자 권한으로 열고 다음 명령을 차례로 수행해도 된다.
NET STOP MySQL
NET START MySQL
> 다시 각각 10,000, 000개 문자의 대량 데이터를 입력하자. 이번에는 잘 입력될 것이다.
INSERT INTO maxTbl VALUES (REPEAT('A', 10000000), REPEAT('가', 10000000));
> 입력된 값의 크기를 확인해 보자. 예상대로 약 10M와 30M이 확인된다.
> SHOW Variables 명령으로 MySQL의 시스템 변숫값을 확인해 보자. 시스템 변수의 종류가 너무 많으므로 LIKE를 함께 활용하자.
SHOW variables LIKE 'max%';
> my_ini에서 설정했던 1,000M 값이 확인된다.
> 쿼리의 결과를 파일로 저장하거나 저장한 내용을 다시 테이블로 입력해 보자.
> 허용된 파일의 경로를 추가하자. Workbench를 종료한다. 그리고 my.ini를 열고, secure-file-priv 옵션을 찾아서 그 아래 한 줄을 추가한 후 저장하자. MySQL 서비스를 재시작 한다.
secure-file-priv="C:/TEMP"
> Workbench를 실행해서 시스템 변수를 확인해 보자. 지정한 C:/TEMP 경로가 설정되어 있을 것이다.
SHOW variables LIKE 'secure%';
> 파일 탐색기 에서 C:/TEMP 폴더를 확인하고 다음 쿼리를 사용해 결과를 파일로 저장해 보자.
USE sqlDB;
SELECT * INTO OUTFILE 'C;/TEMP/userTBL.txt' FROM userTBL;
> 파일을 열어보자 UNIX 형태의 줄바꿈 때무넹 메모장에서는 한 줄로 이어져서 보이지만, 데이터는 잘 있는 것이므로 상관 없다.
> 이번에는 userTBL과 동일한 구조의 memberTBL을 만들고 파일의 내용을 memberTBL에 한 번에 입력시켜 보자.
CREATE TABLE memberTBL LIKE userTBL; -- 테이블 구조만 복사
LOAD DATA LOCAL INFILE 'C:/TEMP/userTBL.txt' INTO TABLE memberTBL;
> 이러한 방식은 기존의 대량의 데이터를 엑셀이나 텍스트 파일로 가지고 있을 때 한꺼번에 테이블에 입력하기 위한 방법으로 활용된다.
☞ 피벗의 구현
> 피벗은 한 열에 포함된 여러 값을 출력하고, 이를 여러 열로 변환하여 테이블 변환식을 회전하고 필요하면 집계까지 수행하는 일을 한다.
> 다음 예를 보자.
> 왼쪽은 판매자 이름, 판매 재질, 판매 수량으로 구성된 테이블이다. 이를 각 판매자가 계절별로 몇 개 구매했는지 표로 나타내고 싶을 때 SUM()과 IF()함수를 활용해서 피벗 테이블을 만들 수 있다.
> 우선 위 그림과 같은 샘플 테이블을 만든다.
USE sqlDB;
CREATE TABLE pivotTest (
uName CHAR(3),
season CHAR(2),
amount INT
);
> 위 그림의 왼쪽과 동일하게 데이터를 9건 입력한다.
INSERT INTO pivotTest VALUES
('김범수', '겨울', 10), ('윤종신', '여름', 15), ('김범수', '가을', 25), ('김범수', '봄', 3), ('김범수', '봄', 37), ('윤종신', '겨울', 40), ('김범수', '여름', 14), ('김범수', '겨울', 22), ('윤종신', '여름', 64);
SELECT * FROM pivotTest;
> SUM()과 IF() 함수, 그리고 GROUP BY를 활용해 보자. 위 그림의 오른쪽과 같은 결과가 나온다.
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;
> 피벗 테이블은 한 눈에 테이블 내용을 파악할 수 있는 장점이 있으므로 종종 유용하게 사용된다.
☞ JSON 데이터
> JSON(JavaScript Object Notation)은 현대의 웹과 모바일 응용프로그램 등과 데이터를 교환하기 위한 개방형 표준 포맷을 말하는데, 속성과 값으로 쌍을 이루며 구성되어 있다.
> JSON은 비록 JavaScript 언어에서 파생되었지만 특정한 프로그래밍 언어에 종속되어 있지 않은 독립적인 데이터 포맷이라고 생각하며 된다. 즉, 그 포맷이 단순하고 공개되어 있기에 거의 대부분의 프로그래밍 언어에서 쉽게 읽거나 쓸 수 있도록 코딩할 수 있다.
> JSON의 가장 단순한 형태의 예를 들면 다음과 같다. 다음은 한 명의 사용자를 JSON 형태로 표현한 것이다. 속성과 값으로 쌍을 이루는 것을 확인할 수 있다.
{
"아이디" : "BBK",
"이름" : "바비킴",
"생년" : 1973,
"지역" : "서울",
"국번" : "010",
"전화번호" : "00000000",
"키" : 178,
"가입일" : "2013.5.5"
}
> MySQL은 JSON과 관련된 다양한 내장 함수를 제공해서 다양한 조작이 가능하다. 우선 테이블의 데이터를 JSON 데이터로 표현하면 다음과 같다.
> 우선 위 그림의 왼쪽 테이블은 userTBL에서 키가 180 이상인 사람의 이름과 키를 나타내며 이것을 JSON으로 변환하려면 JSON_OBJECT()나 JSON_ARRAY() 함수를 이용하면 된다.
USE sqlDB;
SELECT JSON_OBJECT('name', name, 'hegiht', height) AS 'JSON 값'
FROM userTBL
WHERe height >= 180;
> 결과 값은 JSON 형태로 구성되었다. 이렇게 구성된 JSON을 MySQL에서 제공하는 다양한 내장 함수를 사용해서 운영할 수 있다. JSON 관련 함수의 사용법을 확인해 보자.
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 변수에 JSON 데이터를 우선 대입하면서 테이블의 이름은 userTBL로 저장했다.
> JSON_VALID() 함수는 문자열이 JSON 형식을 만족하면 1을, 그렇지 않으면 0을 반환한다. 예에서 "@json"은 JSON 형식을 만족하므로 1을 반환한다.
> JSON_SEARCH() 함수는 세 번째 파라미터에 주어진 문자열의 위치를 반환한다. 두 번째 파라미터는 'one'과 'all' 중 하나가 올 수 있다. 'one'은 처음으로 매치되는 하나만 반환하면 'all'은 매치된 모든 것을 반환한다. 결과를 보면 '성시경'은 userTBL의 두 번째인 name에 해당하는 부분에 위치하는 것을 확인할 수 있다.
> JSON_EXTRACT()는 JSON_SEARCH()와 반대로 저장된 위치의 값을 추출한다.
> JSON_INSERT()는 새로운 값을 추가한다. 결과를 보면 userTBL의 첫 번째(0)에 mDate를 추가했다.
> JSON_REPLACE()는 값을 변경한다. 이 예에서는 첫 번째(0)의 name 부분을 '홍길동'으로 변경했다.
> JSON_REMOVE()는 지정된 항목을 삭제한다. 이 예에서는 첫 번째(0)의 항목을 통째로 삭제했다.
◎ 조인
> 조인이란 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것을 말한다.
> 데이터베이스의 테이블은 중복과 공간 낭비를 피하고 데이터의 무결성을 위해서 여러 개의 테이블로 분리하여 저장한다. 그리고 이 분리된 테이블들은 서로 관계를 맺고 있다.
> 그 중에서 간단하지만 가장 많이 사용되는 보편적인 관계가 '1대다'의 관계이다.
- INNER JOIN(내부 조인)
> INNER JOIN은 조인 중에서 가장 많이 사용되는 조인이다. 대개의 업무에서 조인은 INNER JOIN을 사용한다. 일반적으로 JOIN이라고 얘기하는 것이 이 INNER JOIN을 지칭하는 것이다.
> INNER 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';
※ ON 구문과 WHERE 구문에는 '테이블이름.열이름'의 형식으로 되어 있다. 그렇게 해야 하는 이유는 두 개의 테이블(buyTbl, userTbl)에 동일한 열 이름이 모두 존재하기 때문이다. 그래서 두 개의 테이블을 결합하는 경우에 동일한 열 이름이 있다면 꼭 테이블명.열이름 형식으로 표기해줘야 한다.
> 위 결과를 생성하기 위해서 다음 그림과 같은 과정을 거친다. 우선 구매 테이블의 userID(buyTbl.userID)인 'JYP'를 추출한다. 그리고 'JYP'와 동일한 값을 판매 테이블의 userID(userTbl.userID)열에서 검색한 후, 'JYP'라는 아이디를 찾으면 구매 테이블과 판매 테이블의 두 행을 결합한다.
> 만약 WHERE buyTbl.userID = 'JYP'를 생략하면 buyTbl의 모든 행에 대해서 위와 동일한 방식으로 반복하게 된다.
> 그러면 열의 항목이 너무 많은 것 같아서 복잡해 보이므로 필요한 열만 추출해 보자. 아이디/이름/구매물품/주소/연락처만 추출하자.
SELECT userID, name, prodName, addr, mobile1 + mobile2 AS '연락처'
FROM buyTbl
INNER JOIN userTbl
ON buyTbl.userID = userTbl.userID;
> 하지만 열 이름 userID가 불확실하다는 오류 메시지가 나왔다. userID의 경우에는 두 테이블 모두에 들어 있어서 어느 테이블의 userID를 추출할지 명시해줘야 한다.
> 이 경우에는 어느 테이블의 userID를 추출할지 선택해야 한다. 동일한 값이지만 지금은 buyTbl을 기준으로 하는 것이므로 buyTbl의 userID가 더 정확하다.
SELECT buyTbl.userID, ...
> 코드를 좀더 명확히 하기 위해서 SELECT 다음의 컬럼 이름에도 모두 '테이블이름.열이름'식으로 붙여주자.
SELECT buyTbl.userID, userTbl.name, userTbl.prodName, userTbl.addr,
userTbl.mobile1 + mobile2 AS '연락처'
FROM buyTbl
INNER JOIN userTbl
ON buyTbl.userID = userTbl.userID;
> 각 열이 어느 테이블에 속한 것인지는 명확해졌지만, 코드가 너무 길어져 오히려 복잡해 보인다. 이를 간편하게 하기 위해서 다음과 같이 각 테이블에 별칭을 줄 수 있다. 다음 코드는 위와 동일하지만 훨씰 간결하다.
SELECT B.userID, U.name, U.prodName, U.addr, 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, U.mobile1 + U.mobile2 AS '연락처'
FROM userTbl U
INNER JOIN buyTbl B
ON U.userID = B.userID
WHERE B.userID = 'JYP';
> 이번에는 전체 회원들이 구매한 목록을 모두 출력해 보자. 위의 쿼리문에서 WHERE 조건만 빼면 된다. 그리고 결과를 보기 쉽게 회원ID순으로 정렬하도록 하자.
SELECT U.userID, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS '연락처'
FROM userTbl U
INNER JOIN buyTbl B
ON U.userTD = B.userID
ORDER BY U.userID;
> 구매 테이블의 목록 12건이 이상 없이 잘 나왔을 것이다. 하지만 '전체 회원'이 아닌 '구매한 기록이 있는 회원들'의 결과이다. 한 번도 구매하지 않은 회원인 이승기, 김경호, 임재범, 윤종신, 조관우는 나타나지 않았다.
> 여기서는 구매한 회원의 기록도 나오면서 더불어 구매하지 않았어도 회원의 이름/주소 등은 나오도록 조인할 필요도 있을 것이다. 이렇게 조인해주는 방식이 OUTER JOIN이다.
> 결국 INNER JOIN은 양쪽 테이블에 모두 내용이 있는 것만 조인되는 방식이고 OUTER JOIN은 INNER 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);
> 세 개의 테이블을 테스트하기 위한 예를 보자. 학생과 동아리의 관계를 생각해 보자. 한 학생은 여러 개의 동아리에 가입해서 활동을 할 수 있고, 하나의 동아리에는 여러 명의 학생이 가입할 수 있으므로 두 개는 서로 '다대다'의 관계라고 표현할 수 있다.
> 다대다 관계는 논리적으로는 구성이 가능하지만 이를 물리적으로 구성하기 위해서는 두 테이블의 사이에 연결 테이블을 둬서 이 연결 테이블과 두 테이블이 일대다 관계를 맺도록 구성해야 한다.
> 위 그림의 구조는 테이블의 복잡성을 없애려고 학생의 이름 및 동아리명을 Primary Key로 설정했다.
※ 실제로는 학생 이름으로 Primary Key를 설정하지 않는다. 왜냐하면 이름이 같은 학생이 있다면 한 명만 빼고 나머지는 자퇴해야 하는 웃지 못할 상황이 생길 수도 있다.
> 이 구조를 보면 학생 테이블과 동아리 테이블은 서로 직접적인 관련이 없다. 하지만 중간의 학생_동아리 테이블이 두 테이블의 연관관계를 맺어주고 있다.
> 테이블을 생성하고 데이터를 입력하는 쿼리문을 작성하자.
CREATE TABLE stdTbl (
stdName VARCHAR(10) NOT NULL PRIMARY KEy,
addr CHAR(4) NOT NULL
);
CREATE TABLE clupTbl (
clupName VARCHAR(10) NOT NULL PRIMARY KEY,
roomNo CHAR(4) NOT NULL
);
CREATE TABLE stdclupTbl (
num int AUTO_INCREMENT NOT NULL PRIMARY KEY,
stdName VARCHAR(10) NOT NULL,
clupName VARCHAR(10) NOT NULL,
FOREIGN KEY(stdName) REFERENCES stdTbl(stdName),
FOREIGN KEY(clupName) REFERENCES clupTbl(clupName)
);
INSERT INTO stdTbl VALUES ('김범수', '경남'), ('성시경', '서울'), ('조용필', '경기'), ('은지원', '경북'), ('바비킴', '서울');
INSERT INTO clupTbl VALUES ('수영', '101호'), ('바둑', '102호'), ('축구', '103호'), ('봉사', '104호');
INSERT INTO stdclupTbl VALUES (NULL, '김범수', '바둑'), (NULL, '김범수', '축구'), (NULL, '조용필', '축구'), (NULL, '은지원', '축구'), (NULL, '은지원', '봉사'), (NULL, '바비킴', '봉사');
> 학생 테이블, 동아리 테이블, 학생-동아리 테이블을 이용해서 학생을 기준으로 학생 이름/지역/가입한 동아리/동아리 이름을 출력하자.
SELECT S.stdName, S.addr, C.clupName, C.roomNo
FROM stdTbl S
INNER JOIN stdclupTbl SC
ON S.stdName = SC.stdName
INNER JOIN clupTbl C
ON SC.clupName = C.clupName
ORDER BY S.stdName;
> 이 쿼리문은 학생동아리 테이블과 학생 테이블이 일대다 관계를 INNER JOIN하고, 또한 학생-동아리 테이블과 동아리 테이블의 일대다 관계를 INNER JOIN한다.
> 위 그림에 나와 있듯이 세 개의 테이블이 조인되는 쿼리를 만드는 순서는 처음에 실선 박스로 표시된 stdTbl과 stdclupTbl이 조인되고, 그 후에 점선 박스로 표시된 stdclupTbl과 clupTbl이 조인되는 형식으로 쿼리문을 작성하면 된다.
> 이번에는 동아리를 기준으로 가입한 학생의 목록을 출력하자.
SELECT C.clupName, C.roomNo, S.stdName, S.addr
FROM stdTbl S
INNER JOIN stdclupTbl SC
ON SC.stdName = S.stdName
INNER JOIN clupTbl C
ON C.clupName = SC.clupName
ORDER BY C.clupName;
- OUTER JOIN(외부 조인)
> OUTER JOIN은 조인의 조건이 만족되지 않는 행까지도 포함시키는 것이라고 얘기했다. 자주 사용되지는 않지만 가끔 유용하게 사용되는 방식이므로 알아둘 필요가 있다.
> 구문은 다음과 같은 형식을 가진다.
SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
<LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)
ON <조인될 조건>
[WHERE 검색조건];
> '전체 회원의 구매 기록을 보자. 단 구매 기록이 없는 회원도 출력되어야 한다.'의 쿼리문을 통해 살펴보자.
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;
> INNER 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(전체 조인 또는 전체 외부 조인)에 대해서 살펴보자. FULL OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN이 합쳐진 것이라고 보면 된다. 그냥 줄여서 FULL JOIN이라고 부른다.
> 즉, 한쪽의 기준으로 조건과 일치하지 않는 것을 출력하지 않는 것이 아니라, 양쪽 모두에 조건이 일치하지 않는 것을 모두 출력하는 개념이다. 활용도는 낮으므로 다음 실습에서 간단히 확인만 해두면 된다.
> 앞의 실습에서 3개의 테이블을 가지고 INNER JOIN 했던 결과를 OUTER JOIN으로 고려하자. 또 두 개의 조인을 고려한 FULL JOIN을 테스트하자.
> 앞에서 했던 실습의 학생을 기준으로 출력된 결과를 보면, 동아리에 가입하지 않은 학생 성시경은 출력이 안됐다. OUTER JOIN으로 동아리에 가입하지 않은 학생도 출력하도록 수정하자. 간단히 INNER JOIN을 LEFT OUTER JOIN으로 변경하면 된다.
SELECT S.stdName, S.addr, C.clupName, C.roomNo
FROM stdTbl S
LEFT OUTER JOIN stdclupTbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clupTbl C
ON SC.clupName = C.clupName
ORDER BY S.stdName;
<<<<<<<<< [ 275 결과 저장해놈 ] >>>>>>>>
> 이번에는 동아리를 기준으로 가입한 학생을 출력하되, 가입 학생이 하나도 없는 동아리도 출력되게 하자.
SELECT C.clupName, C.roomNo, S.stdName, S.addr
FROM stdTbl S
LEFT OUTER JOIN stdclupTbl SC
ON SC.stdName = S.stdName
RIGHT OUTER JOIN clupTbl C
ON SC.clupName = C.clupName
ORDER BY C.clupName;
> 클럽을 기준으로 조인을 해야 하므로 두 번째 조인은 RIGHT OUTER JOIN으로 처리해서 clupTbl이 조인의 기준이 되도록 설정하면 된다.
> 위의 두 결과를 하나로 합쳐보자. 즉, 동아리에 가입하지 않은 학생도 출력하고 학생이 하나도 없는 동아리도 출력하게 하자. 앞의 두 쿼리를 UNION으로 합쳐주면 된다.
SELECT S.stdName, S.addr, C.clupName, C.roomNo
FROM stdTbl S
LEFT OUTER JOIN stdclupTbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clupTbl C
ON SC.clupName = C.clupName
UNION
SELECT S.stdName, S.addr, C.clupName, C.roomNo
FROM stdTbl S
LEFT OUTER JOIN stdclupTbl SC
ON SC.stdName = S.stdName
RIGHT OUTER JOIN clupTbl C
ON SC.clupName = C.clupName;
- CROSS JOIN(상호 조인)
> CROSS JOIN은 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인하는 기능을 한다. 그래서 CROSS JOIN의 결과 개수는 두 테이블의 개수를 곱한 개수가 된다.
> 아래 그림과 같은 조인이 발생한다. 회원 테이블의 첫 행이 구매 테이블의 모든 행과 조인되고, 그것을 회원 테이블의 모든 행이 반복하는 것이다. 그러므로 회원 테이블의 개수인 10개와 구매 테이블의 개수인 12개가 곱해져서 120개의 결과가 된다. 이러한 CROSS JOIN을 카티션곱이라고도 부른다.
> 회원 테이블과 구매 테이블의 CROSS JOIN 구문은 다음과 같다.
SELECT *
FROM buyTbl
CROSS JOIN userTbl;
> CROSS JOIN에는 ON 구문을 사용할 수 없다. CROSS JOIN의 용도는 테스트로 쓸 많은 용량의 데이터를 생산할 때 주로 사용한다.
> 예를 들어, employees DB에서 약 30만 건이 있는 employees 테이블과 약 44만 건이 있는 titles을 CROSS JOIN시키면 30만X44만=약 1,300억 건의 데이터를 생성할 수 있다. 진짜로 데이터를 생성하면 시스템이 다운되거나 디스크 용량이 모두 찰 수 있으므로 COUNT(*) 함수로 개수만 카운트 해보자.
USE employees;
SELECT COUNT(*) AS '데이터개수'
FROM employees
CROSS JOIN titles;
- SELF JOIN(자체 조인)
> SELF JOIN은 별도의 구문이 있는 것이 아니라 자기 자신과 자기 자신이 조인한다는 의미이다.
> SELF JOIN을 활용하는 경우의 대표적인 예가 조직도와 관련된 테이블이다. 아래 그림과 같은 조직도를 살펴보자.
> 위의 조직도를 테이블로 나타내 보자.
(실제라면 사번을 기본 키로 해야겠지만, 테이블의 단순화와 이해의 명확성을 위해서 직원 이름을 기본 키로 했다.)
> 이부장을 보면 이부장은 직원이므로 직원 이름 열에 존재한다. 그러면서 동시에 우대리와 지사원의 상관이어서 상관 이름 열에도 존재한다. 만약, 우대리 상관의 구내번호를 알려면 FMP열과 MANAGER열을 조인해야 이부장의 구내 번호를 알 수 있다.
> 우선 조직도 테이블을 정의하고 테이블을 입력하자.
USE sqlDB;
CREATE TABLE empTbl (emp CHAR(3), manager CHAR(3), empTel VARCHAR(8));
INSERT INTO empTbl VALUES('나사장', NULL, '0000');
INSERT INTO empTbl VALUES('김재무', '나사장', '2222');
INSERT INTO empTbl VALUES('김부장', '김재무', '2222-1');
INSERT INTO empTbl VALUES('이부장', '김재무', '2222-2');
INSERT INTO empTbl VALUES('우대리', '이부장', '2222-2-1');
INSERT INTO empTbl VALUES('지사원', '이부장', '2222-2-2');
INSERT INTO empTbl VALUES('이영업', '나사장', '1111');
INSERT INTO empTbl VALUES('한과장', '이영업', '1111-1');
INSERT INTO empTbl VALUES('최정보', '나사장', '3333');
INSERT INTO empTbl VALUES('윤차장', '최정보', '3333-1');
INSERT INTO empTbl VALUES('이주임', '윤차장', '3333-1-1');
> 우대리 상관의 연락처를 확인하고 싶다면 다음과 같이 사용할 수 있다.
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 시켜서 정보를 확인할 수 있다.
- UNION/UNION ALL/NOT IN/IN
> UNION은 두 쿼리의 결과를 행으로 합치는 것을 말한다. 아래 그림을 보면 쉽게 이해될 것이다.
> UNION의 형식과 사용 예는 다음과 같다.
SELECT 문장1
UNION [ALL]
SELECT 문장2
> 대신 SELECT 문장1과 SELECT 문장2의 결과 열의 개수는 같아야 하고 데이터 형식도 각 열 단위로 같거나 서로 호환되는 데이터 형식이어야 한다. 또한 열 이름은 문장1의 열 이름을 따른다.
> UNION만 사용하면 중복된 열은 제거되고 데이터만 정렬되어 나오며, UNION ALL을 사용하면 중복된 열까지 모두 출력된다.
SELECT stdName, addr FRom stdTbl
UNION ALL
SELECT clupName, roomNo FROM clupTbl;
> NOT IN은 첫 번째 쿼리의 결과 중에서 두 번째 쿼리에 해당하는 것을 제외하기 위한 구문이다. 예로 sqlDB의 사용자를 모두 조회하되 전화가 없는 사람을 제외하고자 한다면 다음과 같이 사용하면 된다.
SELECT name, CONCAT(mobile1, mobile2) AS '전화번호' FROM userTbl
WHERE name NOT IN (SELECT name FROM userTbl WHERE mobile1 IS NULL);
> NOT IN과 반대로 첫 번째 쿼리의 결과 중에서 두 번째 쿼리에 해당되는 것만 조회하기 위해서는 IN을 사용하면 된다. 예로 전화가 없는 사람만 조회하고자 할 때 다음과 같이 사용한다.
SELECT name, CONCAT(mobile1, mobile2) AS '전화번호' FROM userTbl
WHERE name IN (SELECT name FROM userTbl WHERE mobile1 IS NULL);
◎ SQL 프로그래밍
> SQL 프로그래밍을 진행하기 전에 우선 스토어드 프로시저를 만들고 사용하는 방법을 간단히 요약하고 넘어가자.
DELIMITER $$
CREATE PROCEDURE 스토어드 프로시저이름()
BEGIN
이 부분에 SQL 프로그래밍 코딩
END $$
DELIMITER ;
CALL 스토어드 프로시저이름();
> DELIMITER $$ ~ END $$ 부분까지는 스토어드 프로시저의 코딩할 부분을 묶어준다고 보면 된다.
> MySQL의 종료 문자는 세미콜론인데 CREATE PROCEDURE 안에서도 세미콜론이 종료 문자이므로 어디까지 스토어드 프로시저인지 구별이 어렵다. 그래서 END $$가 나올 때까지를 스토어드 프로시저로 인식하게 하는 것이다.
> 그리고 다시 DELIMITER ; 와 같이 종료 문자를 세미콜로능로 변경해 놓아야 한다.
> CALL 스토어드 프로시저이름();은 CREATE PRODEDURE로 생성한 스토어드 프로시저를 호출(=실행)한다.
- IF...ELSE
> 조건에 따라 분기한다. 한 문장 이상이 처리되어야 할 때는 BEGIN, END와 함꼐 묶어줘야만 하며, 습관적으로 실행할 문장이 한 문장이라도 BEGIN, END로 묶어주는 것이 좋다.
IF <부울 표현식> THEN
SQL문장들1
ELSE
SQL문장들2
END IF;
> <부울 표현식> 부분이 참이라면 'SQL문장들1'을 수행하고, 거짓이라면 'SQL문장들2'를 수행한다. 거짓일 경우면서 아무것도 할 것이 없다면 ELSE 이하는 생략하면 된다.
DROP PROCEDURE IF EXISTS ifProc;
DELIMITER $$
CREATE PROCEDURE ifProc()
BEGIN
DECLARE var1 INT;
SET var1 = 100;
IF var = 100 THEN
SELECT '100입니다.';
ELSE
SELECT '100이 아닙니다.';
END IF;
END $$
DELIMITER ;
CALL ifProc();
※ MySQL은 사용자 정의 변수를 만들 때 앞에 @를 붙인다고 배웠다. 하지만 스토어드 프로시저나 함수 등에서는 DECLARE문을 사용해서 지역변수를 선언할 수 있다. 이 지역변수 앞에는 @를 붙이지 않고, 일반 프로그래밍 언어의 변수처럼 사용하면 된다.
> 이번에는 employeesDB의 employees 테이블을 사용해 보자. 열에는 입사일(hire_date)열이 있는데, 직원 번호에 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
FROM employees.employees
WHERE emp_no = 10001;
SET curDATE = CURRENT_DATE();
SET days = DATEDIFF(curDATE, hireDATE);
IF (days/365) >= 5 THEN
SELECT CONCAT('입사한지', days, '일이나 지났습니다. 축하합니다.');
ELSE
SELECT CONCAT('입사한지', days, '일밖에 안되었네요. 열심히 일하세요.');
END IF;
END $$
DELIMITER ;
CALL ifProc2();
> 결과 값으로 '입사한지 10840일이나 지났습니다. 축하합니다.'가 출력될 것이다.
※ 'SELECT 열이름 INTO 변수이름 FROM 테이블이름' 구문은 조회된 열의 결과 값을 변수에 대입한다.
- CASE
> IF 구문은 2중 분기라는 용어를 사용한다. 즉, 참 아니면 거짓 두 가지만 있기 때문이다.
> 점수와 학점을 생각해 보자. 90점 이상은 A, 80점 이상은 B, 70점 이상은 C, 60점 이상은 D, 60점 미만은 F로 분할할 수 있다. 이때 5가지의 경우에 따라 달라지므로 '다중 분기'라는 용어를 사용한다.
> IF문으로 작성해 보자.
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문을 사용해서 학점 계산 프로그램을 만들었다. 위 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이 여러 개더라도 먼저 조건이 만족하는 WHEN이 처리된다. 그리고 CASE를 종료한다.
> CASE문의 활용은 SELECT문에서 더 많이 사용된다.
> sqlDB의 구매 테이블(buyTbl)에 구매액(priceamount)이 1500원 이상인 고객은 '최우수 고객', 1000원 이상인 고객은 '우수 고객', 1원 이상인 고객은 '일반 고객'으로 출력하자. 또 전혀 구매 실적이 없는 고객은 '유령고객'이라고 출력하자.
> Workbench를 종료하고 새로 실행한 후, 저장해 놓은 sqlDB.sql을 이용해서 sqlDB 데이터베이스를 초기화 시키자.
> 먼저 buyTbl에서 구매액(priceamount)을 사용자 아이디(userID)별로 그룹화 한다. 또 구매액이 높은 순으로 정렬한다.
USE sqlDB;
SELECT userID, SUM(price * amount) AS '총구매액'
FORM buyTbl
GROUP BY userID
ORDER BY SUM(price * amount) DESC;
> 사용자 이름이 빠졌으므로 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;
> 그런데, buyTbl에서 구매한 고객의 명단만 나왔을 뿐 구매하지 않은 고객의 명단은 나오지 않았다. 오른쪽 테이블(buyTbl)의 내용이 없더라도 구매하지 않은 고객의 명단이 나오도록 하기 위해 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;
> 그런데 결과를 보니 name은 제대로 나왔으나 구매한 기록이 없는 고객은 userID 부분이 null로 나왔다. 또 메시지 탭을 보면 경고도 나왔다. 이유는 SELECT 창에서 B.userID를 출력하기 때문이다. buyTbl에는 윤종신, 김경호 등이 구매한 적이 없으므로 아예 해당 아이디가 없다. 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;
> 이제는 총 구매액에 따른 고객 분류를 처음에 제시했던 대로 CASE문을 고려해 보자.
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;
- WHILE과 ITERATE/LEAVE
> WHILE문은 해당 <부울 식>이 참인 동안에는 계속 반복되는 반복문이다.
WHILE <부울 식> DO
SQL 명령문들...
END WHILE;
> 1에서 100까지의 값을 모두 더하는 간단한 기능을 구현해 보자.
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 hap;
END $$
DELIMITER ;
CALL whileProc();
> 그런데, 1에서 100까지의 합계에서 7의 배수는 제외시키려면 어떻게 해야 할까? 또 더하는 중간에 합계가 1000이 넘으면 더하는 것을 그만두고 출력을 하고 싶다면? 그런 경우에는 ITERATE문과 LEAVE문을 사용할 수 있다.
※ ITERATE문은 다른 프로그래밍 언어의 CONTINUE와, LEAVE는 BREAK문과 비슷한 역할을 한다.
> 다음 코드를 보자.
DROP PROCEDURE IF EXISTS whileProc2;
DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
DECLARE i INT;
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;
END IF;
SET i = i + 1;
END WHILE;
SELECT hap;
END $$
DELIMITER ;
CALL whileProc2();
> ITERATE문을 만나면 바로 WHILE문으로 이동해서 비교(@i <= 100)를 다시 하고, LEAVE문을 만나면 WHILE문을 빠져나온다. 여기서는 WHILE문을 myWhile이라는 label로 명명했다.
- 오류 처리
> MySQL은 오류가 발생할 경우 직접 오류를 처리하는 방법을 제공한다. 우선 그 형식을 살펴보자.
DECLARE 액션 HANDLER FOR 오류조건 처리할_문장
* 액션 : 오류 발생 시에 행동을 정의하는데 CONTINUE와 EXIT 둘 중 하나를 사용한다. CONTINUE가 나오면 제일 뒤의 '처리할_문장' 부분이 처리된다.
* 오류 조건 : 어떤 오류를 처리할 것인지를 지정한다. 여기에는 MySQL의 오류 코드 숫자가 오거나 SQLSTATE '상대코드', SQLEXCEPTION, SQLWARNING, NOT FOUND 등이 올 수 있다. SQLSTATE에서 상태 코드는 5자리 문자열로 되어 있다. SQLEXCEPTION은 대부분의 오류를, SQLWARNING은 경고 메시지를, NOT FOUND는 커서나 SELECT...INTO에서 발생하는 오류를 의미한다.
* 처리할 문장 : 처리할 문장이 하나라면 한 문장이 나오면 되며, 처리할 문장이 여러 개일 경우에는 BEGIN-END로 묶어줄 수 있다.
> 다음의 예는 테이블이 없을 경우에 오류를 직접 처리하는 코드이다. DECLARE행이 없다면 MySQL이 직접 오류 메시지를 발생시키지만, DECLARE 부분이 있어서 사용자가 지정한 메시지가 출력된다.
DROP PROCEDURE IF EXISTS errorProc;
DELIMITER $$
CREATE PROCEDURE errorProc()
BEGIN
DECLARE CONTINUE HANDLER FOR 1146 SELECT '테이블이 없어요ㅠㅠ' AS '메시지';
SELECT * FROM naTable;
END $$
DELIMITER ;
CALL errorProc();
> 위의 코드에서 1146 대신에 SQLSTATE '42S02'로 써줘도 된다. 둘다 테이블이 없을 경우를 의미한다.
> sqlDB의 userTbl에 이미 존재하는 'LSG'라는 아이디를 생성시켜 보도록 하자. userID열은 기본키로 작성되어 있으므로 같은 ID를 입력할 수 없으므로 오류가 발생할 것이다.
DROP PROCEDURE IF EXISTS errorProc2;
DELIMITER $$
CREATE PROCEDURE errorProc2()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SHOW ERRORS;
SELECT '오류가 발생했네요. 작업은 취소시켰습니다.' AS '메시지';
ROLLBACK;
END;
INSERT INTO userTbl VALUES('LSG', '이상구', 1982, '서울', NULL, NULL, 170, CURRENT_DATE());
END $$
DELIMITER ;
CALL errorProc2();
> SHOW ERRORS문은 오류에 대한 코드와 메시지를 출력한다. ROLLBACK은 진행중인 작업을 취소시킨다.
※ SHOW COUNT(*) ERRORS문은 발생된 오류의 개수를 출력해주며 SHOW WARNINGS문은 경고에 대한 코드와 메시지를 출력한다.
- 동적 SQL
> PREPARE는 SQL을 실행하지는 않고 미리 준비만 해놓고, EXECUTE문은 준비한 쿼리문을 실행한다. 그리고 실행 후에는 DEALLOCATE PREPARE로 문장을 해제해 주는 것이 바람직하다.
PREPARE myQuery FROM 'SELECT * FROM userTbl WHERE userID = "EJW"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;
> 즉, SELECT * FROM userTbl WHERE useriD = "EJW" 문장을 바로 실행하지 않고, myQuery에 입력시켜 놓는다. 그리고 EXECUTE문으로 실행할 수 있다.
> 이렇게 미리 쿼리문을 준비한 후에 나중에 실행하는 것을 '동적 SQL'이라고도 부른다.
> 또한, PREPARE문에 ?으로 향후에 입력될 값을 비워놓고, EXECUTE문에서 USING을 이용해 값을 전달하여 사용할 수 있다. 다음 코드는 쿼리를 실행하는 순간의 날짜와 시간이 입력되는 기능을 한다.
DROP TABLE IF EXISTS myTable;
CREATE TABLE myTable (id INT AUTO_INCREMENT PRIMARY KEY, mDate DATETIME);
SET @curDATE = CURRENT_TIMESTAMP();
PREPARE myQuery FROM 'INSERT INTO myTable VAlues(null, ?)';
EXECUTE myQuery USING @curDATE;
DEALLOCATE PREPARE myQuery;
SELECT * FROM myTable;
MySQL 대용량 DBMS 개선 사례
