[SQL] 2022-02-18 개인 Project_SQL문 연습(함수, JOIN)
[1] 데이터 형식과 형 변환
1. 데이터 형식 변환 함수
- CAST( expression AS 데이터형식[길이] )
- CONVERT(expression, 데이터형식[길이])
2. 암시적인 형 변환
- SELECT '100' + '200'
-> 숫자로 변환되어 더하기 계산하여 300 출력 (다른 DBMS에서는 '100200'으로 출력될 수도 있음)
- CONCAT(str1, str2,...) : 매개변수를 문자열로 변환하여 연결
-> SELECT CONCAT('100', '200') : '100200' 출력
-> SELECT CONCAT(100, 200) : 정수를 문자로 변환하여 '100200' 출력
- SELECT 1 > '2mega' -> '2mega'가 정수 2로 변환되어 1>2 의 결과인 0(거짓) 출력
- SELECT 3 > '1MEGA' -> '1MEGA'가 정수 1로 변횐되어 3>1 의 결과인 1(참) 출력
- SELECT 0 = 'mega2' -> 'mega2'가 문자열로 인식되어 0으로 변환되고 0=0 의 결과인 1(참) 출력
[2] MariaDB 내장 함수와 윈도 함수
1. MariaDB 내장 함수
1) 제어 흐름 함수
- IF(boolean, expr1, expr2) : boolean이 참이면 expr1, 거짓이면 expr2 출력
- IFNULL(expr1, expr2) : expr1 이 NULL인지 판단하여, NULL이 아니면 expr1, NULL이면 expr2 출력
- CASE expr1 WHEN expr2 THEN result1 ELSE result2 END : expr1==expr2이면 result1, 아니면 result2
2) 문자열 함수
- ASCII(문자) : 문자를 아스키코드값으로 변환
- CHAR(아스키코드) : 아스키코드값을 문자로 변환
- BIT_LENGTH(문자열) : 할당된 bit 크기 반환 (UTF-8코드에서 영문은 8bit, 한글은 24bit)
- CHAR_LENGTH(문자열) : 문자의 개수 반환 (할당된 메모리 크기 아님)
- LENGTH(문자열) : 할당된 byte 수 반환 (UTF-8코드에서 영문은 1byte, 한글은 3byte)
- CONCAT(str1, str2,...) : 매개변수를 문자열로 변환하여 연결
- CONCAT_WS(separator, str1, str2,...) : str 사이에 separator를 넣어서 연결
- ELT(위치, str1, str2,...) : 위치값으로 str1~strN 중에 찾아서 반환
- FIELD(찾을 str, str1, str2,...) : str1~strN 중 찾을 str의 위치값을 반환, 없으면 0반환
- FIND_IN_SET(찾을 str, str리스트) : str리스트 내에서 찾을 str의 위치값을 반환, 문자열은 공백 없이 ,(콤마)로 구분
- INSTR(기준str, 부분str) : 기준str에서 부분str을 찾아 시작 위치를 반환
- LOCATE(부분str, 기준str) : 기준str에서 부분str을 찾아 시작 위치를 반환 (INSTR 같은 기능)
- FORMAT(숫자, 소수점 자리수) : 숫자를 소수점 자리수까지 표현하고, 천 단위 구분 기호( , ) 표시
- BIN(숫자), HEX(숫자), OCT(숫자) : 숫자를 각각 2진수, 16진수, 8진수로 표현
- INSERT(기준 문자열, 위치, 길이, 삽입할 문자열) : 기준 문자열의 위치에서 길이만큼 지우고 삽입할 문자열 삽입
- LEFT(문자열, 길이), RIGHT(문자열, 길이) : 각각 문자열의 왼쪽/오른쪽에서 길이만큼 문자열을 반환
- SUBSTRING(문자열, 시작위치, 길이), SUBSTRING(문자열 FROM 시작위치 FOR 길이)
: 문자열을 시작위치부터 길이만큼 반환, 길이 생략시 문자열의 끝까지 반환
- SUBSTRING_INDEX(문자열, 구분자, 숫자) : 문자열 왼쪽에서부터 구분자가 숫자만큼 나오면 그 뒤는 버리고 반환, 숫자가 음수이면 오른쪽에서부터 체크
- UPPER(문자열), LOWER(문자열) : 각각 문자열을 대분자/소문자로 변환
- LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열) : 문자열을 길이만큼 늘린 후 빈 곳을 채울
문자열로 채움
- LTRIM(문자열), RTRIM(문자열) : 각각 문자열의 왼쪽/오른쪽 공백을 제거
- TRIM(문자열) : 문자열의 양쪽 공백 제거
- TRIM([방향] 자를_문자열 FROM 문자열) : 문자열에서 선택한 방향(LEADING, BOTH, TRAILING)에 있는
자를_문자열을 제거
- REPEAT(문자열, 횟수) : 문자열을 횟수만큼 반복
- REPLACE(문자열, 원래 문자열, 바꿀 문자열) : 문자열에서 원래 문자열을 찾아 바꿀 문자열로 변경
- REVERSE(문자열) : 문자열을 거꾸로
- SPACE(길이) : 길이만큼 공백을 반환
3) 수학 함수
- ABS(숫자) : 절대값
- ACOS(숫자), ASIN(숫자), ATAN(숫자), ATAN2(숫자1, 숫자2), SIN(숫자), COS(숫자), TAN(숫자)
- CEILING(숫자) 혹은 CEIL(숫자), FLOOR(숫자), ROUND(숫자) : 각각 올림, 내림, 반올림 하여 정수로 표현
- CONV(숫자, 원래 진수[10, 2, 8, 16], 변환할 진수[10, 2, 8, 16]) : 원래 진수인 숫자를 변환할 진수로 변환
- DEGREES(숫자), RADIANS(숫자), PI() : 각각 라디안을 각도로, 각도를 라디안으로 변환, 원주율을 반환
- EXP(X), LN(숫자), LOG(숫자), LOG(밑, 숫자), LOG2(숫자), LOG10(숫자)
- MOD(숫자1, 숫자2), 숫자1 % 숫자2, 숫자1 MOD 숫자2 : 숫자1을 숫자2로 나눈 나머지
- POW(숫자1, 숫자2), SQRT(숫자) : 각각 거듭제곱, 제곱근
- RAND() : 0 이상 1 미만 실수 반환
- SIGN(숫자) : 숫자가 양수이면 1, 0이면 0, 음수이면 -1을 반환
- TRUNCATE(숫자, 정수) : 숫자를 소수점 기준으로 정수 위치까지 구하고 나머지는 버림
4) 날짜 및 시간 함수
- ADDDATE(날짜, 차이), SUBDATE(날짜, 차이) : 날짜를 기준으로 차이를 더하거나 뺀 날짜
- ADDTIME(날짜/시간, 시간), SUBTIME(날짜/시간, 시간) : 날짜/시간을 기준으로 시간만큼 더하거나 뺀 결과
- CURDATE(), CURTIME(), NOW(), SYSDATE() : 각각 현재 "연-월-일", "시:분:초", "연-월-일 시:분:초"
- YEAR(날짜), MONTH(날짜), DAY(날짜), HOUR(시간), MINUTE(시간), SECOND(시간), MICROSECOND(시간)
- DATE(), TIME() : DATETIME 형식에서 각각 연-월-일, 시:분:초 만 추출
- DATEDIFF(날짜1, 날짜2), TIMEDIFF(날짜1또는시간1, 날짜2또는시간2) : 1 - 2 한 일수, 시간 반환
- DAYOFWEEK(날짜), MONTHNAME(날짜), DAYOFYEAR(날짜)
- LAST_DAY(날짜) : 주어진 날짜의 달의 마지막 날짜
- MAKEDATE(연도, 정수) : 연도에서 정수만큼 지난 날짜
- MAKETIME(시, 분, 초) : 시:분:초 의 TIME 형식
- PEROID_ADD(연월, 개월수), PERIOD_DIFF(연월1, 연월2)
- QUARTER(날짜) : 4분기 중 몇 분기인지 반환
- TIME_TO_SEC(시간) : 시간을 초 단위로 반환
5) 시스템 정보 함수
- USER(), DATABASE() : 현재 사용자, 현재 선택된 데이터베이스
- FOUND_ROWS() : 바로 앞의 SELECT 문에서 조회된 행의 개수
- ROW_COUNT() : 바로 앞의 INSERT, UPDATE, DELETE문에서 입력, 수정, 삭제된 행의 개수
- VERSION() : 현재 MariaDB의 버전
- SLEEP(초) : 쿼리의 실행을 초 만큼 정지
2. MariaDB 윈도우 함수
1) 순위 함수
- 형식 : <순위함수이름>( ) OVER( [PARTITION BY <필드명>] ORDER BY <필드명> )
- RANK( ) : 동차 처리 가능, 동차 아래 순위는 동차 수만큼 밀림 ( 1, 2, 2, 4, 4, 6,...)
- DENSE_RANK( ) : 동차 처리 가능, 동차 아래 순위 그대로 이어서 (1, 2, 2, 3, 3, 4,...)
- ROW_NUMBER( ) : 단순 행 개수, 동차 없음
- NTILE(개수) : 개수 만큼 그룹을 나누어 줌
2) 분석 함수
- 형식 : <분석함수이름>( ) OVER( [PARTITION BY <필드명>] ORDER BY <필드명> )
- LEAD( ), LAG( ) : 각각 다음/이전 데이터와 비교시 사용
- FIRST_VALUE( ), LAST_VALUE( ) : 각각 최상위/최하위 데이터와 비교시 사용
- CUME_DIST( ), PERCENT_RANK( ) : 전체 혹은 그룹 내에서 위치의 백분율
[3] JSON 데이터
- {key1: value1, key2: value2,...} 와 같은 형식으로 표현되는 형태 (파이썬의 dictionary와 유사)
- JSON_OBJECT(key1, value1, key2, value2,...) 를 이용하여 JSON 데이터 형태로 변환
- JSON 관련 함수는 JSON을 배우고 나서 알아보자...
[4] JOIN (조인)
- 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것
1. INNER JOIN (내부 조인)
- 기본 구문
SELECT <열 목록>
FROM <첫 번째 테이블>
INNER JOIN <두 번째 테이블>
ON <조인될 조건>
- INNER JOIN 대신 JOIN 만 쓰는 것도 가능
- 조인될 조건에 맞는 데이터만 출력
2. OUTER JOIN (외부 조인)
- 기본 구문
SELECT <열 목록>
FROM <첫 번째 테이블>
<LEFT | RIGHT> OUTER JOIN <두 번째 테이블>
ON <조인될 조건>
- <LEFT | RIGHT> OUTER JOIN 대신 <LEFT | RIGHT> JOIN 으로 쓰는 것도 가능
- LEFT는 첫 번째 테이블, RIGHT는 두 번째 테이블
- 조인될 조건에 맞지 않는 데이터도 출력
-> LEFT 면 첫 번째 테이블 데이터가 조인될 조건에 맞지 않아도 출력
-> RIGHT 면 두 번째 테이블 데이터가 조인될 조건에 맞지 않아도 출력
- 양 쪽 데이터를 모두 빠짐 없이 출력하려면 FULL OUTER JOIN 이 필요(MariaDB에서는 지원X)
-> LEFT와 RIGHT 각각을 SELECT 문으로 조인시킨 뒤 둘을 UNION으로 연결하여 구현
3. CROSS JOIN (상호 조인)
- 기본 구문
SELECT <열 목록>
FROM <첫 번째 테이블>
CROSS JOIN <두 번째 테이블>
- 첫 번째 테이블의 1행에 대해서 두 번째 테이블 전체 행 조인
첫 번째 테이블의 2행에 대해서 두 번째 테이블 전체 행 조인
...
첫 번째 테이블의 마지막 행에 대해서 두 번째 테이블 전체 행 조인
- 결과적으로 두 테이블의 행 수 끼리 곱한 결과 만큼 행이 출력
- 용도 : 테스트로 사용할 많은 용량의 데이터를 생성할 때 주로 사용
4. SELF JOIN (자체 조인)
- 별도의 구문이 있는 것이 아닌, 테이블이 테이블 자기 자신을 조인
- 예시
: 회사 내 직원들은 각자 직속 상관이 있고, 특정 직원의 직속 상관의 정보를 출력하려면 자기 자신을 조인해야 가능