SQL 고급 (1)

Preface

 

이번 장에선 MySQL에서 사용하는 데이터 형식과 다양한 내장 함수에 대해 공부했다.

 

데이터 형식도 그렇고, 내장 함수도 그렇고 python과 비슷한 부분이 많아 쉽게 이해하며 넘어갈 수 있었다.

 

다만, 변수의 생성과 사용 방법은 python과 조금 달라 익숙해지려면 시간이 조금 걸릴 것 같다.

 

또, MySQL의 함수를 공부하며 이렇게 불편하게 Workbench에서 함수를 작성하여 사용할 바에 프로그밍 언어를 사용하는 IDE와 DBMS를 연결하여 사용하는 것이 훨씬 편할 것 같다는 생각을 했다.

---

 

1. MySQL의 데이터 형식

 

 

- Data Type: 데이터 형식, 데이터형, 자료형, 데이터 타입 etc.

 

 

- 숫자 데이터 형식

데이터 형식	바이트 수	숫자 범위	설명
SMALL INT	2	-32,768 ~ 32,767	정수
INT	4	-21억 ~ +21억	정수
BIGINT	8	-900경 ~ +900경	정수
FLOAT	4	-3.40E+38 ~ -1.17E-38	소수점 아래 7자리까지 표현
DECIMAL	5 ~ 17	-10의 38승+1 ~ 10의 38승-1	전체 자릿수와 소수점 이하 자릿수

→ DECIMAL: 정확한 수치 저장

→ FLOAT, DOUBLE: 근사치의 숫자를 저장

→ 부호 없는 정수를 지정할 때는 UNSIGNED 예약어를 뒤에 붙어줘야 한다.

 

 

- 문자 데이터 형식

데이터 형식	바이트 수	설명
CHAR(n)	1 ~ 255	고정길이 문자형
VARCHAR(n)	1 ~ 65535	가변길이 문자형
LONGTEXT	1 ~ 4294967295	대용량의 글자를 저장하기 위한 형식
LONGBLOB	1 ~ 4294967295	대용량 이진 데이터를 저장하기 위한 형식

 

 

- 날짜와 시간 데이터 형식

데이터 형식	바이트 수	설명
DATE	3	'YYYY-MM-DD' 형식
DATETIME	8	'YYYY-MM-DD HH:MM:SS' 형식

 

 

- 기타 데이터 형식

데이터 형식	바이트 수	설명
GEOMETRY	N/A	공간 데이터 형식으로 선, 점 및 다각형 같은 공간 데이터 개체를 저장하고 조작
JSON	8	JSON 문서를 저장

 

 

- LOB(Large Object): 대량의 데이터

 

 

- MySQL의 변수 선언 방식

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

 

 

- 스토어드 프로시저나 함수 안에서는 DECLARE문으로 선언한 후에 변수를 사용할 수 있다.

1) '@변수명'은 전역 변수처럼, 'DECLARE 변수명'은 지역 변수처럼 사용된다.

2) 변수는 Workbench를 닫았다가 재시작하면 소멸된다.

 

 

- 변수 사용 예시

set @myVar1 = 5;
set @myVar2 = 3;
set @myVar3 = 4.25;
set @myVar4 = '가수 이름 ==> ';

select @myVar1;
select @myVar2 + @myVar3;

select @myVar4, username from usertbl where height > 180;

 

 

- LIMIT에는 원칙적으로 변수를 사용할 수 없지만, PREPARE와 EXECUTE문을 활용해서 변수를 사용할 수 있다.

1) prepare 쿼리이름 from '쿼리문'으로 쿼리 이름에 쿼리문을 준비만 해놓고 실행하지 않는다.

2) execute문을 만나는 순간 실행된다. (using @변수를 통해 쿼리문에서 '?'로 처리해 놓은 부분에 대입)

→ limit @변수 형식으로 사용된 것과 동일한 효과

set @myVar5 = 3;
prepare myQuery
	from 'select username, height from usertbl order by height limit ?';  -- limit에 변수를 넣지 못하는 것을 해결하는 방법
execute myQuery using @myVar5;

 

 

- 데이터 형식 변환 방법

cast ( expression as 데이터형식 [ (길이) ] )
convert ( expression , 데이터형식 [ (길이) ] )

→ 변환 가능한 데이터 형식: binary, char, date, datetime, decimal, json, signed interger, time, unsugned interger etc.

 

 

- 데이터 형식 변환 예시

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 num, concat(cast(price as char(10)), 'X', cast(amount as char(4)), '=') as '단가 X 수량',
		price * amount as '구매액' 
    from buytbl;

 

 

- 형 변환 방식

1) 명시적인 변환: CAST( ), CONVERT( ) 함수를 이용한 형 변환

2) 암시적인 변환: 형 변환 함수를 사용하지 않고 형이 변환되는 것

 

---

 

2. MySQL 내장 함수

 

 

1. 제어 흐름 함수: 프로그램의 흐름을 제어

---

- if(수식, 참, 거짓): 수식이 참 또는 거짓인지 결과에 따라서 2중 분기한다다.

select if(100 > 200, '참이다', '거짓이다');

 

 

- ifnull(수식1, 수식2): 수식1이 null이 아니면 수식 1이 반환되고, 수식1이 null이면 수식2가 반환된다.

select ifnull(null, 'nulll'), ifnull(100, 'nulll');

 

 

- nullif(수식2, 수식2): 수식1과 수식2가 같으면 null을 반환하고, 다르면 수식1을 반환한다.

select nullif(100, 100), nullif(200,100);

 

 

- case ~ when ~ else ~ end: 연산자로, 해당하는 사항이 없다면 else 부분이 반횐된다. 마지막 end as 뒤에는 출력될 열의 별칭을 써주면 된다.

select case 10
			when 1 then 'one'
            when 5 then 'five'
            when 10 then 'ten'
            else 'dknow'
		end as 'case_excercise'; -- 출력될 열의 별칭

---

---

2. 문자열 함수: 문자열을 조작

---

- ascii(아스키 코드), char(숫자): 문자의 아스키 코드 갑을 돌려주거나 숫자의 아스키 코드 값을 돌려준다.

select ascii('A'), char(65);

 

 

- bit_length(문자열): 할당된 bit 크기 또는 문자 크기를 반환

- char_length(문자열): 문자의 개수를 반환

- length(문자열): 할당된 byte 수를 반환

select bit_length('abc'), char_length('abc'), length('abc');
select bit_length('가나다'), char_length('가나다'), length('가나다');

 

 

- concat(문자열1, 문자열2...), concat_ws(구분자, 문자열1, 문자열2...): 문자열을 이어주거나, 구분자와 함께 문자열을 이어준다.

select concat_ws('/', '2022', '01', '01'); -- 제일 앞에 있는 문자가 구분자

 

 

- elt(위치, 문자열1, 문자열2...): 위치 번째에 해당하는 문자열을 반환

- field(찾을 문자열, 문자열1, 문자열2...): 찾을 문자열의 위치를 찾아서 반환 (매치되는 문자열이 없으면 0을 반환)

- find_in_set(찾을 문자열, 문자열 리스트): 찾을 문자열을 문자열 리스트에서 찾아 위치를 반환 (문자열 리스트는 콤마로 구분되어 있어야 하며, 공백이 없어야 함)

- instr(기준 문자열, 부분 문자열): 기준 문자열에서 부분 문자열을 찾아서 그 시작 위치를 반환

- locate(부분 문자열, 기준 문자열): instr( )와 동일하지만 파라미터의 순서가 반대

select elt(2, 'one', 'two', 'three'), field('two', 'one', 'two', 'three'), find_in_set('two', 'one,two,three'),
instr('onetwothree', 'two'), locate('two', 'onetwothree');

→ locate( )와 position( )은 동일한 함수다.

 

 

- format(숫자, 소수점 자릿수): 숫자를 소수점 아래 자릿수까지 표현한다. (1000 단위마다 콤마를 표시해 준다.)

select format(123456.123456, 4);

 

 

- bin(숫자), hex(숫자), oct(숫자): 2진수, 16진수, 8진수의 값을 반환한다.

select bin(31), hex(31), oct(31);

 

 

- insert(기준 문자열, 위치, 길이, 삽입할 문자열): 기준 문자열의 위치부터 길이만큼을 지우고 삽입할 문자열을 끼워넣는다.

select insert('abcdefghi', 3, 4, 'kkkk'), insert('abcdefghi', 3, 2, 'kkkk');

 

 

- left(문자열, 길이), right(문자열, 길이): 왼쪽 또는 오른쪽에서 문자열의 길이만큼 반환한다.

select left('12345', 3), right('12345', 2);

 

 

- upper(문자열), lower(문자열): 소문자를 대문자로, 대문자를 소문자로 변경한다.

select lower('ABCEDFG'), upper('abcdefg');

→ lower( )는 lcase( )와, upper( )는 ucase( )와 동일한 함수다.

 

 

- lpad(문자열, 길이, 채울 문자열), rpad(문자열, 길이, 채울 문자열): 문자열을 길이만큼 늘린 후에, 빈 곳을 문자열로 채운다.

select lpad('abc', 5, '*'), rpad('abc', 5, '*');

 

 

- ltrim(문자열), rtrim(문자열): 문자열의 왼쪽/오른쪽 공백을 제거한다. 중간의 공백은 제거되지 않는다.

select lpad('abc', 5, '*'), rpad('abc', 5, '*');

 

 

- trim(문자열): 앞뒤 공백을 모두 없앤다.

- trim(방향 자를_문자열 from 문자열): leading(앞), both(양쪽), trailing(뒤) 중 하나를 선택해서 공백을 지운다.

select trim('   abc   '), trim(both 'h' from 'hhhlolhhh');

 

 

- repeat(문자열, 횟수): 문자열을 횟수만큼 반복한다.

select repeat('kkk', 3);

 

 

- replace(문자열, 원래 문자열, 바꿀 문자열): 문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꿔준다.

select replace('이것이 MySQL이다', '이것이', 'This is');

 

 

- reverse(문자열): 문자열의 순서를 거꾸로 만든다.

select reverse('abc');

 

 

- space(길이): 길이만큼의 공백을 반환한다.

 select concat('This is', space(10), 'MySQL');

 

 

- substring(문자열, 시작위치, 길이), substring(문자열 from 시작위치 for 길이): 시작 위치부터 길이만큼 문자를 반환한다.

select substring('abcdefg', 3, 2);

→ substring( ), substr( ), mid( ) 는 모두 동일한 함수다.

 

 

- substring_index(문자열, 구분자, 횟수): 문자열에서 구분자가 왼쪽부터 횟수 번째 나오면 그 이후의 오른쪽은 버린다. 횟수가 음수면 오른쪽부터 세고 왼쪽을 버린다.

select substring_index('ab.cd.ef', '.', 2), substring_index('ab.cd.ef', '.', -2);

---

---

3. 수학 함수: 다양한 수학적 기능을 제공

---

- abs(숫자): 숫자의 절댓값을 계산한다.

select abs(-100);

 

 

- ceiling(숫자), floor(숫자), round(숫자): 올림, 내림, 반올림을 계산한다.

select ceiling(4.7), floor(4.7), round(4.7);

→ ceiling( )과 ceil( )은 동일한 함수다.

 

 

- conv(숫자, 원래 진수, 변환할 진수): 숫자를 원래 진수에서 변환할 진수로 계산한다.

select conv('aa', 16, 2), conv(100, 10, 8);

 

 

- mod(숫자1, 숫자2) / 숫자1 % 숫자2 / 숫자1 mod 숫자2: 숫자1을 숫자2로 나눈 나머지 값을 구한다.

select mod(157, 10), 157 % 10, 157 mod 10;

 

 

- pow(숫자1, 숫자2), sqrt(숫자): 거듭제곱값 및 제곱근을 구한다.

select pow(2, 3), sqrt(9);

→ pow( )와 power( )는 동일한 함수다.

 

 

- 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);

---

---

4. 시스템 정보 함수: 시스템의 정보를 출력

---

- user( ), database( ): 현재 사용자 및 현재 선택된 데이터베이스를 구한다.

select current_user(), database();

 

 

- found_rows( ): 바로 앞의 select문에서 조회된 행의 개수를 구한다.

 

 

- row_count( ): 바로 앞의 insert, update, delete문에서 입력, 수정, 삭제된 행의 개수를 구한다. create, drop문은 0을 반환하고, select문은 -1을 반환한다.

 

 

- version( ): 현재 MySQL의 버전을 구한다.

---

 

 

- MySQL의 나머지 내장 함수: https://dev.mysql.com/doc/refman/8.0/en/function.html 

 

---

 

3. 데이터 입력, 피벗, JSON

 

 

- 파일을 데이터로 입력하려면 load_file( )함수를 사용하면 된다.

insert into movietbl values(1, '쉰들러 리스트', '스필버그', '리암 니슨',
	load_file('파일 경로'), load_file('파일 경로'));

 

 

- max_allowed_packet: 최대 패킷 크기(최대 파일 크기)가 설정된 시스템 변수

show variables like 'max_allowed_packet';

 

 

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

 

 

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

→ 속성과 값으로 쌍을 이루며 구성되어 있다.

 

 

- 테이블의 값을 JSON형식으로 변환하는 방법: json_object( )나 json_array( ) 함수를 이용

select json_object('username', username, 'height', height) as
	'json 값' from usertbl
    where height >= 180;