[DBMS] SQL 고급 문법 - 데이터 타입, 내장함수

데이터 타입

• Blob 형식 : 비정형 데이터
  • LONGBLOB : LOB(Large Object, 대량 데이터) 저장 위해서 사용. 약 4GB를 하나의 데이터로 저장 가능.
  • 예: 동영상 파일(큰 바이너리 파일)
• TEXT 형식
  • LONGTEXT : LOB(Large Object, 대량 데이터) 저장 위해서 사용.
  • 예 : 장편소설(큰 텍스트 파일)
• CHAR(n) : 고정길이 문자형. 1~255 길이 지정 가능
• VARCHAR(n) : 가변길이 문자형. 1~65535까지 가능.
• FLOAT : 소수 7자리까지 표현 가능.
• BIGINT : 정수(-900경~900경)
• INT : 정수(-21억 ~ 21억)
• SMALLINT : 정수(-32768 ~ 32767)
• DECIMAL(m, [d]) : 숫자형, 전체 자릿수 m자리, 소수점 아래 d자리
• 날짜와 시간
  • DATE : YYYY-MM-DD
  • DATETIME : YYYY-MM-DD HH:MM:SS
  • TIME : HH:MM:SS
  • TIMESTAMP : YYYY-MM-DD HH:MM:SS, UTC 시간대로 변환해줌.

기타 데이터 형식

• GEOMETRY : 공간 데이터 형식. 선, 점, 다각형 같은 공간 데이터 개체를 저장 및 조작한다.
• JSON : json 문서 저장용 데이터 형식.

형 변환

• CAST()
• CONVERT()
• 변환 가능한 데이터 타입
  • BINARY, CHAR, DATE, DATETIME, DECIMAL, JSON, SIGNED INTEGER, TIME, UNSIGNED INTEGER
• 명시적 형 변환 : CAST(), CONVERT()
• CAST ( expression AS datatype [length] ) CONVERT ( expression, datatype [length] ) SELECT CAST(AVG(amount) AS SIGNED INTEGER) AS '평균 구매 개수' FROM buytbl ; SELECT CONVERT(AVG(amount) , SIGNED INTEGER) AS '평균 구매 개수' FROM buytbl ;
• 암시적 형 변환
  • CONCAT()
  •  
  • 정수와 문자 연결(정수가 문자로)
  • 비교문 (정수로 변환 비교, 문자는 0으로 변환 비교)

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

내장 함수

✅ 조건문

• IF
  • IF(조건문, 참일 때의 값, 거짓일 때의 값)
    • 예시 : SELECT IF(2 > 1, 'TRUE', 'FALSE') AS result
  • 특정 컬럼 값의 조건에 의해서, 다른 컬럼의 출력하는 분기 처리도 가능하다. 중첩 IF 사용도 가능하다.
    • 예시 : SELECT IF(column_name is null, column_1, columnj_2) AS result
• IFNULL
  • IFNULL도 중첩 가능하다.
    • IFNULL(column_name, 해당 칼럼이 null일 때 대체할 value)
• COALESCE
  • 입력된 값 중 첫 번째로 NULL이 아닌 값을 반환하는 함수
    • COALESCE(expr1, expr2, ...)
  • expr1이 null이면 expr2 출력 이런 식으로 작동해서 IFNULL처럼 쓸 수 있다. 차이는 IFNULL은 인수 2개만 받지만 COALESCE는 더 많이 인자를 받을 수 있다는 점!
• CASE WHEN
  • JS switch case문 같은 문법
  • END 빼먹지 말자!!!
  • ELSE에는 THEN 안 쓴다!
  • SELECT, FROM, WHERE 절에서 쓰일 수 있다.

CASE value 혹은 조건
	WHEN value1 
	THEN result1 
	WHEN value2 
	THEN result2 … 
	[ELSE else_result]
END

-- 예시
SELECT
	CASE
		WHEN permission_type_id = 1
		THEN '관리자'
		WHEN permission_type_id = 2
		THEN '판매자'
		ELSE '일반 유저'
END

✅ 문자열 함수

• ASCII(문자) : 문자 아스키 코드값 반환
• CHAR(숫자) : 숫자 아스키 코드값에 해당하는 문자를 반환
• BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)
  • 할당된 비트 혹은 문자 크기 반환
  • CHAR_LENGTH ⇒ 문자 개수 반환
  • LENGTH() ⇒ 할당된 Byte 수 반환
• CONCAT(문자열1, 문자열, …) : 문자열 연결
• CONCAT_WS(구분자, 문자열들 … ) : 구분자로 문자열 연결
• GROUP_CONCAT(문자열 SEPERATOR ',') : GROUPBY 항목의 개별 내용을 하나의 문자열로 연결
• ELT(위치, 문자열1, 문자열2, …) : 위치 번째에 해당하는 문자열 반환
• FIELD(찾을 문자열, 문자열1, 문자열2) : 찾을 문자열 위치를 찾아서 반환, 없으면 0
• FIND_IN_SET(찾을 문자열, 문자열 리스트) : 찾을 문자열의 위치를 문자열 리스트에서 찾아서 반환. 이때 문자열 리스트는 콤마로 구분되고 공백 없음
• INSTR(기준 문자열, 부분 문자열) : 기준 문자열에서 부분 문자열 시작 위치 반환
• LOCATE(부분 문자열, 기준 문자열) : INSTR()와 같고 파라미터 순서만 반대
• FORMAT(숫자, 소수점 자릿수) : 숫자를 소수점 아래 자릿수까지 표시. 1,000단위 콤마 표시.
• BIN(숫자), HEX(숫자), OCT(숫자) : 2/16/8진수 값 반환
• INSERT(기준 문자열, 위치, 길이, 삽입할 문자열) : 기준 문자열 위치부터 길이만큼 지우고 삽입할 문자열 끼워 넣기(위치는 1부터 시작!)
• LEFT(문자열, 길이), RIGHT(문자열, 길이) : 왼/오에서 문자열 길이만큼 반환
• UPPER(문자열), LOWER(문자열): 대소문자로 변경
• LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열): 문자열을 길이만큼 늘린 후에 빈 곳을 채울 문자열로 채우기
• LTRIM(문자열), RTRIM(문자열) : 문자열 왼/오 공백 제거, 중간 공백은 제거 불가
• TRIM(문자열), TRIM(방향 자를문자열 FROM 문자열) : 문자열 앞뒤 공백 모두 없앰(일반 TRIM), 방향 지정 시 LEADING(앞), BOTH(양쪽), TRAILING(뒤)로 표시
• REPEAT(문자열, 횟수) : 문자열을 횟수만큼 반복
• REPLACE(문자열, 원래 문자열, 바꿀 문자열) : 문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꾸기
• REVERSE(문자열) : 순서 뒤집기
• SPACE(길이) : 길이만큼의 공백 반환
• SUBSTRING(문자열, 시작위치, 길이) / SUBSTRING(문자열 FROM 시작위치 FOR 길이) : 시작위치~길이만큼 문자 반환. 길이 생략 시 문자열 끝까지 반환.
• SUBSTRING_INDEX(문자열, 구분자, 횟수) : 문자열에서 구분자가 왼쪽부터 횟수 번째까지 나오면 그 이후 오른쪽은 전부 버림. 횟수가 음수면 오른쪽부터 세서 왼쪽을 버린다.

✅ 수학 함수

• ABS(숫자) : 절댓값 계산
• ACOS(숫자), ASIN(숫자), ATAN(숫자), ATAN2(숫자1, 숫자2), SIN(숫자), COS(숫자), TAN(숫자) : 삼각 함수 관련 함수
• CEILING(숫자), FLOOR(숫자), ROUND(숫자) : 올림, 내림, 반올림 계산
• CONV(숫자, 원래 진수, 변환할 진수) : 숫자를 원래 진수에서 변환할 진수로 계산
• DEGREES(숫자), RADIANS(숫자), PI()
  • 라디안 값을 각도로, 각도를 라디안 값으로 변환
  • PI()는 3.141592 반환
• EXP(x), LN(숫자), LOG(숫자), LOG(밑수, 숫자), LOG2(숫자), LOG10(숫자) : 지수, 로그 관련 함수
• MOD(숫자1, 숫자2) 또는 숫자1 % 숫자2 또는 숫자1 MOD 숫자2 : 숫자1을 숫자2로 나눈 나머지 값
• POW(숫자1, 숫자2), SQRT(숫자) : 거듭제곱값, 제곱근
• RAND()
  • m~n 임의 정수 구하고 싶다면
  • FLOOR(m + (RAND() * (n - m))) 사용
  • 0 이상 1 미만의 실수 구함
• SIGN(숫자)→ 결과는 1, 0, -1 중 하나 반환
  • 숫자가 양수, 0, 음수인지 판별
• TRUNCATE(숫자, 정수)
  • 숫자를 소수점 기준으로 정수 위치까지 구하고 나머지는 버림

✅ 날짜 및 시간 함수

• 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) : DATEDIFF()는 날짜1 - 날짜2의 일수를 결과로 구함
• DAYOFWEEK(날짜), MONTHNAME(), DAYOFYEAR(날짜) : 요일(1:일 ~ 7:토) 및 1년 중 몇 번째 날짜인지 구함
• LAST_DAY(날짜) : 주어진 날짜의 마지막 날짜를 구함
• MAKEDATE(연도, 정수) : 연도에서 정수만큼 지난 날짜 구함
• MAKETIME(시, 분, 초) : 시, 분, 초를 이용해 '시:분:초'의 TIME 형식 생성
• PERIOD_ADD(연월, 개월수), PERIOD_DIFF(연월1, 연월2)
  • PERIOD_ADD()는 연월에서 개월만큼 지난 연월 구함
  • PERIOD_DIFF()는 연월1 - 연월2의 개월수 구함
• QUARTER(날짜)
  • 날짜가 4분기 중에서 몇 분기인지 구함
• TIME_TO_SEC(시간)
  • 시간을 초 단위로 변환

 

 

---

참고자료

 

이것이 MySQL이다

2016년 출간 후 데이터베이스 도서 분야 부동의 베스트셀러 1위를 지켜오던 『이것이 MySQL이다』가 MySQL 8.0 버전을 반영하여 개정되었다. 특히 ‘파이썬 기초 및 파이썬과 데이터베이스의 연동’, ‘ 파이썬으로 공간 데이터 응용 프로그래밍 작성하기’ 등의 내용을 추가하여 더욱 실무 밀착형 교재로 업그레이드되었다

저자

우재남

출판

한빛미디어

출판일

2020.05.10