SQL 함수와 데이터 타입

▶ 데이터 타입

1. 데이터 타입 (숫자 데이터 형식)

데이터 형식	바이트 수	숫자범위	설  명
BIT	N/8		Bit 표현
TINYINT	1	-128~127	정수
SMALLINT	2	-32,768~32,767	정수
MEDIUMINT	3	-8,388,608~8,388,607	정수
INT	4	약 -21억~+21억	정수
BIGINT	8	약 -900경~+900경	정수
FLOAT	4	-3.40E+38~1.79E-38	소수점 7자리
DOUBLE	8	-1.22E-308~1.79E+308	소수점 15자리
DECIMAL(m, [d])	5~17	〖-10〗^38+1 ~ 〖10〗^38-1	소수점

 

2. 데이터 타입 (문자 데이터 형식)

데이터 형식	바이트 수	설  명
CHAR	1~255	고정길이 문자형
VARCHAR	1~65535	가변길이 문자형
BINARY	1~255	고정길이의 이진 데이터 값
VARBINARY	1~255	가변길이의 이진 데이터 값
TEXT	1~65535	N 크기의 TEXT 데이터 값
BLOB	1~65535	N 크기의 BLOB 데이터 값
ENUM	1 또는 2	최대 65535개의 열거형 데이터 값
SET	1,2,3,4,8	최대 64개의 서로 다른 데이터 값

 

3. 데이터 타입 (날짜 데이터 형식)

데이터 형식	바이트 수	설  명
DATE	3	날짜: 1001-01-01~9999-12-31까지 저장 날짜형식만 사용  ‘YYYY-MM-DD’
TIME	3	시간: -838:59:59.000000~838:59:59.000000까지 저장 형식: ‘HH:MM:SS’
DATETIME	8	날짜: 1001-01-01 00:00:00~9999-12-31 23:59:59 저장 형식: ‘YYYY-MM-DD HH:MM:SS’
TIMESTAMP	4	날짜: 1970-01-01 00:00:01~2038-01-19 03:14:07 저장 형식: ‘YYYY-MM-DD HH:MM:SS’ Time_zone 시스템 변수와 관련 있으며 UTC 변환 저장
YEAR	1	날짜: 1901~2155까지 저장. 형식: ‘YYYY’

---

▶ 함수

- 집계 함수

함수명	함수표기	설  명
SUM	SUM()	합계를 구한다.
AVG	AVG()	평균을 구한다.
MIN	MIN()	최솟값을 구한다.
MAX	MAX()	최댓값을 구한다.
COUNT	COUNT()	행의 개수를 센다.
COUNT DISTINCT	COUNT(DISTINCT)	행의 개수를 센다. (중복은 1개만 인정)

SELECT
  MAX(인수), -- 최대값 계산
  MIN(인수), -- 최소값 계산
  SUM(인수), -- 총합 계산
  AVG(인수), -- 평균 계산
  ROUND(AVG(인수),1), -- 소수점 1번째 자리까지 반올림하여 표현
  TRUNCATE(AVG(인수),0) -- 소수점 표현 안함, 해당 번째에서 절삭
FROM
  조회할 테이블 명;

- 윈도우 함수 (SQL 8 버전 이상)

SELECT
   WINDOW_FUNCTION(ARGUMENTS) OVER (PARTITION BY 컬럼 ORDER BY 컬럼 오름차순|내림차순)
 FROM 
   테이블명;

1. 집계 함수

SELECT
	MAX(인수) over (PARTITION BY 묶고자 하는 속성),
	MIN(인수) over (PARTITION BY 묶고자 하는 속성),
	SUM(인수) over (PARTITION BY 묶고자 하는 속성),
	AVG(인수) over (PARTITION BY 묶고자 하는 속성),
	ROUND(AVG(인수) over (PARTITION BY 묶고자 하는 속성), 자르고 싶은 소수점 위치),
	TRUNCATE(AVG(인수) over (PARTITION BY 묶고자 하는 속성), 자르고 싶은 소수점 위치)
FROM
	조회할 테이블 명;

 

2. 순위 함수

SELECT
	RANK() OVER (ORDER BY 속성 DESC),
	-- 중복 값들에 대해서 동일 순위로 표시하고,
	-- 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력
	DENSE_RANK() OVER (ORDER BY 속성 DESC), 
    	-- 중복 값들에 대해서 동일 순위로 표시하고, 
    	-- 중복 순위 다음 값에 대해서는 중복 값 개수와 상관없이 순차적인 순위 값을 출력
        ROW_NUMBER() OVER (ORDER BY 속성 DESC), -- 중복 값들에 대해서도 순차적인 순위를 표시하도록 출력
	ROUND((PERCENT_RANK() OVER (ORDER BY 속성 DESC)*100),1) -- 값들에 대한 현재 값의 상대적 백분위
FROM
	조회할 테이블 명;

출력)

 

- 데이터 형식 변환 함수

1. CAST

SELECT CAST('2022-10-19 12:35:29.123' AS DATE) AS 'DATE' ; -- DATE 타입으로 변환 (년, 월, 시만)
SELECT CAST('2022-10-19 12:35:29.123' AS TIME) AS 'TIME' ; -- TIME 타입으로 변환 (시, 분, 초만)
SELECT CAST('2022-10-19 12:35:29.123' AS DATETIME) AS 'DATETIME' ; -- 날짜 시간 등 모두 변환

-- date나 time은 위와 같은 형식의 입력이 들어와야만 함

 

2. CONVERT

SELECT
  CONVERT (인수, 변환할 타임) -- 인수를 다른 타입으로 변환한다.
FROM
  조회할 테이블명;

 

※ 정수값으로 변환시 SINGED

  → 양수, 음수 모두 표현할 수 있다.

 

- 문자열 연결함수

SELECT
	CONCAT(인수, ' : ', 인수) -- 가운데는 중간에 삽입할 문자열 혹은 문자
FROM
	조회할 테이블 명;

예시)

※ GROUP_CONCAT

  → CONCAT와 비슷하지만 CONCAT를 사용하여 열간에 값을 결합하는 방식이 다른 반면 GROUP_CONCAT 함수는 대부분 행간에 값을 연결

SELECT 
	col1, col2, ..., colN GROUP_CONCAT ( [DISTINCT] col_name1 [ORDER BY clause] [SEPARATOR str_val] ) 
    FROM 
    	table_name GROUP BY col_name2;

→ group_concat에 distinct와 order by 사용이 가능하다.

 

※ 암시적 형변환

SELECT '100' + '200' ; -- 문자와 문자를 더함 (정수로 변환되서 연산됨)

SELECT CONCAT('100', '200'); -- 문자와 문자를 연결 (문자로 처리)

SELECT CONCAT(100, '200'); -- 정수와 문자를 연결 (정수가 문자로 변환되서 처리)
 
SELECT 1 > '2mega'; -- 정수인 2로 변환되어서 비교

SELECT 3 > '2MEGA'; -- 정수인 2로 변환되어서 비교 

SELECT 0 = 'mega2'; -- 문자는 0으로 변환됨

 

- 제어 흐름 함수

1. IF문

SELECT IF(조건식, 'true', 'false'); -- 조건이 참이면 2번째 인수 출력, 거짓이면 3번째 인수 출력

SELECT IFNULL(NULL, '널'), IFNULL(100, '널'); -- 첫번째 인수가 null이면 2번 인수 출력

SELECT NULLIF(10,10), NULLIF(30,10); -- 첫번째 인수와 두번째 인수가 같으면 null 아니면 첫번째 인수 출력

 

2. CASE문

SELECT
    CASE 입력 (없어도 됨)
    -- 조건에 맞으면 해당 구문의 출력 값을 도출
    WHEN 조건 1  THEN  출력 값1
    WHEN 조건 2  THEN  출력 값2
    WHEN 조건 3  THEN  출력 값3
    -- 모든 조건에 맞지 않으면 else 뒤의 값 도출
    ELSE NULL
    END;

  → if 조건식 보다 많이 활용됨

 

- 문자열 함수

SELECT ASCII('A'), CHAR(65); --문자를 아스키 코드로 바꾸거나 아스키 코드를 문자로 변경

SELECT TRIM('   KSMART44   ') AS result; -- 양끝 공백 제거

SELECT REPEAT('KSMART', 3) AS result; -- 두번째 인수만큼 반복

SELECT REPLACE ('KSMART45기', 'KSMART' , '한국스마트정보교육원') AS result;
-- 1번재 인수에 2번째 인수가 포함되면 해당 부분을 3번째 인자로 교체

SELECT REVERSE ('KSMART44') AS result; --인수 문자열을 뒤집는다.

- 날짜 함수

SELECT 
   ADDDATE('2022-12-18', INTERVAL 숫자 (DAY, MONTH, YEAR));
   -- 지정일로 부터 지정한 년, 월, 일 이후 날짜 출력
SELECT 
   SUBDATE('2022-12-18', INTERVAL 숫자 (DAY, MONTH, YEAR));
   -- 지정일로 부터 지정한 년, 월, 일 이전 날짜 출력

SELECT 
   ADDTIME('2022-12-18 23:59:59', '1:1:1'),
   ADDTIME('15:00:00', '2:10:10');
   -- 지정일로 이후 시간 출력
  
SELECT 
   SUBTIME('2022-12-18 23:59:59', '1:1:1').
   SUBTIME('15:00:00', '2:10:10');
   -- 지정일로 이전 시간 출력
   
SELECT 
   YEAR(CURDATE()), -- 입력날짜의 년도 출력
   MONTH(CURDATE()); -- 입력날짜의 월 출력

SELECT 
   HOUR(CURTIME()),
   MINUTE(CURRENT_TIME()),
   SECOND(CURRENT_TIME()),
   MICROSECOND(CURRENT_TIME());
   -- 입력날짜의 시간, 분, 초, 마이크로초 출력
   
SELECT DATE(NOW()), TIME(NOW());
   -- 입력날짜의 날짜, 시간 출력
   
SELECT 
   DATEDIFF('2022-12-06', NOW()),
   TIMEDIFF('17:30:01', CURRENT_TIME());
   -- 입력날짜로 부터 남은 일수, 시간 출력
   
SELECT 
   DAYOFWEEK(CURDATE()), -- 입력날짜로 부터 요일(1~7) 출력
   MONTHNAME(CURDATE()), -- 입력날짜의 달(ex.September) 출력
   DAYOFMONTH(CURDATE()), -- 입력날짜로 일(1~31) 출력
   DAYOFYEAR(CURDATE()); -- 입력날짜의 일(1~365) 출력
   
SELECT LAST_DAY('2022-12-18');
-- 입력날짜의 달의 마지막일(28, 29, 30, 31 중 1) 출력

 

- 시스템 함수

SELECT SLEEP(3);

  → 3초 뒤에 실행