[SQL 코테 준비] SQL 문법 정리 및 팁

 LIKE

• PARK으로 시작하는 데이터 검색

select * from tbl_board where title like 'PARK%';

• PARK으로 끝나는 데이터 검색

select * from tbl_board where title like '%PARK';

• PARK이 들어가는 데이터 검색

select * from tbl_board where title like '%PARK%';

 

 

LEFT

LEFT 함수는 받은 문자열에 대해 원하는 길이만큼 자르는 함수이다. 주민등록번호만으로 생년월일을 구해내거나 이름에서 성을 제외한 이름을 *문자로 처리하는 등 다양한 방법으로 활용 가능하다.

사용법

-- 문법 --
LEFT(문자열, 길이)
-- 사용 예시 --
LEFT(NAME, 2)

 

예제

--테이블(MY_TABLE)에서 이름(NM_KOR)을 잘라 성만 출력--
SELECT LEFT(NM_KOR,1) AS 이름 FROM MY_TABLE

 

RIGHT

이와 유사하게 RIGHT 함수는 오른쪽에서부터 문자열을 자를 수 있다.

 

SUBSTRING

SUBSTRING은 더 유연하게 사용할 수 있는데,

 

SUBSTRING(문자열, 시작자리번호, 자를문자수)

• 이처럼 자르고 싶은 구간의 시작 인덱스와 그로부터 몇개를 자를 것인지를 인자로 넘기면 그만큼 자른 문자열을 반환한다.

 

예제

--테이블(MY_TABLE)에서 날짜(DT)를 잘라 0000년00월00일 형식으로 만들기--
SELECT 
SUBSTRING(DT,1,4)+'년'+SUBSTRING(DT,5,2)+'월'+SUBSTRING(DT,7,2)+'일' AS일자
FROM MY_TABLE

 

 

 

CASE WHEN

가장 많이 쓰이는 조건문입니다. 조건에 따라 값을 지정해 주는 역할을 합니다. 

--CASE사용법--
CASE WHEN 조건절 THEN 참일때 값 ELSE 거짓일때 값 END 컬럼명

--테이블(MY_TABLE)에서 성별(GENDER)이 001이면 여, 그게아니면 남자로 검색--
SELECT DISTINCT
GENDER,
CASE WHEN GENDER = '001' THEN '여' ELSE '남' END AS 성별
FROM MY_TABLE

 

다중 CASE WHEN

--테이블(MY_TABLE)에서 성적(SCORE)별 학점을 계산
SELECT *,
   (CASE WHEN SCORE>= '90' THEN 'A학점'
        WHEN (SCORE>= '80' AND SCORE < '90') THEN 'B학점'
        WHEN (SCORE>= '70' AND SCORE < '80') THEN 'C학점' 
        WHEN (SCORE>= '60' AND SCORE < '70') THEN 'D학점'
        ELSE 'F학점'
    END) AS '학점'
FROM MY_TABLE

 

IF ELSE

CASE WHEN과 같은 조건문입니다. CASE문과 마찬가지로 조건에 따라 원하는 작업을 수행할 수 있습니다.

--IF 사용법--
IF 조건 참일때 값 ELSE 거짓일때 값 END 컬럼명

--@NUM 이 30일때 30, 40일경우 40이라고 출력, 아닐경우 아니라고 출력--
DECLARE @NUM INT
SET @NUM = 40

IF(@NUM = 30)
PRINT 'NUM은 30입니다.'
ELSE IF(@NUM=40)
PRINT 'NUM은 40입니다'
ELSE
PRINT 'NUM은 30이나 40이 아닙니다.'

 

 

 

 

REPLACE

지정된 문자열 값을 특정 문자열로 바꿔주는 함수입니다.

 

사용법

--문법--
REPLACE('문자열','치환예정문자','치환할문자')
--예시--
REPLACE('ABCDEFG','DEF','XXX')
-- 'ABCXXXFG'

 

예제

--MY_TABLE에서 이름(NM_KOR)을 이씨를 김씨으로 바꿔서 출력--
SELECT REPLACE(NM_KOR,'이','김')AS 사원명 FROM MY_TABLE

 

STUFF

지정된 문자열의 시작위치와 크기를 지정하여 원하는 문자로 치환하는 함수입니다.

 

사용법

--문법--
STUFF('문자열','시작위치','크기','치환할문자')
--예시--
STUFF('ABCDEFG',2,3,'XXX')

 

예제

--MY_TABLE에서 이름(NM_KOR)칼럼의 이순신을 이성계로 바꿔서 출력--
SELECT STUFF(NM_KOR,2,2,'성계')AS 사원명 FROM MY_TABLE

 

Convert

개인적으로 가장 많이 사용하는 데이터 변환 함수라고 생각합니다. 날짜 변환하는데도 유용하게 사용 가능합니다. 참고로 부동 소수점 또는 숫자에서 정수로 변환할 때 CONVERT() 함수는 결과를 자르고 다른 변환일 경우에는 반올림합니다.

 

사용법

--문법--
CONVERT(data_type[(length)], expression[style])
--예시--
SELECT CONVERT(NVARCHAR(10),칼럼) AS 칼럼명 FROM MY_TABLE --VARCHAR로 변환
SELECT CONVERT(INT,칼럼) AS 칼럼명 FROM MY_TABLE --INT로 변환
SELECT CONVERT(CHAR,칼럼) AS 칼럼명 FROM MY_TABLE --CHAR로 변환

expression : 유효한 식
data_type : 대상 데이터 형식 별칭 데이터 형식은 사용할 수 없습니다.
length : 대상 데이터 형식의 길이를 지정하는 선택적 정수입니다. 기본값은 30입니다.
style : Convert함수가 식을 변환하는 방법을 지정하는 정수 식입니다. style이 Null이면 Null 값이 반환됩니다.

 

예제

--테이블(MY_TABLE)의 나이(AGE)칼럼을 INT에서 CHAR로 형변환--
SELECT CONVERT(NVARCHAR(10),AGE)+'세'AS 나이 FROM MY_TABLE

--테이블(MY_TALBE)에서 날짜(DTS)칼럼을 INT에서 DATE로 형변환--
SELECT CONVERT(DATE,SUBSTRING(DTS,1,8))AS 날짜 FROM MY_TABLE

 

Convert 날짜 변환 표

 

Cast

※ FLOAT, 또는 NUMBERIC에서 INTEGER로 변환할 때 CAST() 함수는 결과를 자릅니다.

 

사용법

--문법--
CAST(expression AS data_type(length))
--예시--
SELECT CAST(칼럼 AS INT) FROM  MY_TABLE

 

예제

--테이블(MY_TALBE)에서 가격(PRICE)칼럼을 INT에서 VARCHAR로 형변환
SELECT CAST(PRICEAS AS VARCHAR)AS 가격 FROM  MY_TABLE

 

GETDATE 

GETDATE함수는 현재 컴퓨터에 설정되어있는 시스템 시간을 불러와주는 함수입니다.


위와 같이 GETDATE() 함수는 년월일은 물론이거니와 분, 초 까지도 구해줍니다. 하지만 모든 사용자들이 위와 같은 형식으로 사용하지는 않겠죠. 여기서 우리는 우리가 표시할 시간이나 날짜만 적절히 뽑아 올 수도 있고 날짜 형태를 바꿔서 출력할 수도 있습니다.

 

사용법

--현재 날짜 출력--
SELECT GETDATE() AS 시스템일자

--현재 날짜의 연,월,일 출력--
SELECT 
YEAR(GETDATE()) AS 년,
MONTH(GETDATE()) AS 월,
DAY(GETDATE()) AS 일

 

출력 포맷 변경

--YYYY/MM/DD--
Select Convert(varchar(10),Getdate(),111)

--YYYYMMDD--
Select Convert(varchar(10),Getdate(),112)

--HH:MM:SS--
Select Convert(varchar(8),Getdate(),108)

--HH:MM:SS:mmm--
Select Convert(varchar(12),Getdate(),114)

--HHMMSS--
Select Replace(Convert(varchar(8),Getdate(),108),':','')

--HHMMSSmmm--
Select Replace(Convert(varchar(12),Getdate(),114),':','')

--YYYY/MM/DD HH:MM:SS--
Select Replace(Convert(varchar(30),Getdate(),120),'-','/')

--YYYY/MM/DD HH:MM:SS--
Select Replace(Convert(varchar(30),Getdate(),121),'-','/')

--YYYY/MM/DD HH:MM:SS--
Select Convert(varchar(10),Getdate(),111) + Space(1) + Convert(varchar(8),Getdate(),108)

--YYYYMMDDHHMMSS--
Select Convert(varchar(10),Getdate(),112) + Replace(Convert(varchar(8),Getdate(),108),':','')

 

DATEADD 

DATEADD함수는 날짜에 원하는 일수를 더해서 출력해주는 함수입니다. 100일 뒤에는 며칠이다 이런 것은 쉽게 계산하기 힘든데 DATEADD함수를 쓰면 이런 것들을 편리하게 계산할 수 있습니다. 이 함수를 좀 더 응용하면 100일 뒤에는 무슨 요일이다 이렇게도 응용 가능합니다.

사용법

--2개월 후 출력--
SELECT DATEADD(MM,2,GETDATE()) AS '2개월후'

--30일전 출력--
SELECT CONVERT(NVARCHAR(8),DATEADD(DAY,-30,'20180124'),112)AS '30일전'

--1달 뒤 요일계산
SELECT DATENAME(WEEKDAY,DATEADD(MM,1,GETDATE())) AS'요일계산'

 

 

DATEPART 

DATEPART함수는 날짜에서 지정한 날짜 형식의 부분만 출력해주는 함수입니다. 아주 다양하게 활용이 가능한 함수입니다.

사용법

--현재 년도 구하기
SELECT DATEPART(YEAR,GETDATE()); 

--현재 월 구하기--
SELECT DATEPART(MONTH,GETDATE())

--현재 일 구하기--
SELECT DATEPART(DAY,GETDATE())

--현재 분기 구하기--
SELECT DATEPART(QQ,GETDATE())

--올해의 몇번째 날인지 구하기--
SELECT DATEPART(DAYOFYEAR,GETDATE())

--올해의 몇째 주인지 구하기--
SELECT DATEPART(WEEK,GETDATE())

--이번주의 몇번째 날인지 구하기--
SELECT DATEPART(WEEKDAY,GETDATE())

--오늘이 무슨요일인지 구하기--
SELECT
CASE DATEPART(WEEKDAY, GETDATE()) 
  WHEN '1' THEN '일요일'
  WHEN '2' THEN '월요일'
  WHEN '3' THEN '화요일'
  WHEN '4' THEN '수요일'
  WHEN '5' THEN '목요일'
  WHEN '6' THEN '금요일'
  ELSE '토요일'
END AS '요일'

 

DATEDIFF 

DATEDIFF함수는 지정한 두 날자 간의 간격을 계산해주는 함수입니다. 정해준 날짜 형식에 맞춰 리턴 값이 다르므로 적절하게 잘 설정해주셔야 합니다.

사용법

--지정일과의 현재와의 년도차이 계산--
SELECT DATEDIFF(YY,'2000-01-01',GETDATE())

--지정일과의 현재와의 월차이 계산--
SELECT DATEDIFF(MM,'2000-01-01',GETDATE())

--지정일과 현재와의 일차이 계산--
SELECT DATEDIFF(DD,'2000-01-01',GETDATE())

 

LTRIM

해당 문자열의 왼쪽에 있는 공백을 제거해주는 함수

 

사용법

--문법--
LTRIM(문자열)
--예제--
DECLARE @SQL NVARCHAR(200)
SET @SQL = ' SPACE'
LTRIM(@SQL)

 

문자열을 합칠경 우 왼쪽에 공백이 생기는데 이를 제거할 때 요긴하게 사용됨

DECLARE @NUM INT, @STR NVARCHAR(300) --변수선언
SET @NUM = 1
SET @STR = ''

WHILE @NUM<10
BEGIN
SET @STR = @STR +'['+ LTRIM(STR(@NUM))+'번째]' --문자열합치기 &왼쪽공백제거
SET @NUM += 1
END
PRINT @STR

 

RTRIM

해당 문자열의 오른쪽에 있는 공백을 제거해주는 함수

 

사용법

--문법--
LTRIM(문자열)
--예제--
DECLARE @SQL NVARCHAR(200)
SET @SQL = 'SPACE '
RTRIM(@SQL)

 

LPAD : 왼쪽에 특정문자를 원하는 자리수만큼 채워서 반환

사용법 : LPAD(원본문자열 , 원하는 자리수, 채울 문자열)

ex ) SELECT LPAD('ABC',10,'0')  FROM DUAL;

결과 : 0000000ABC

 

 

RPAD : 오른쪽에 특정문자를 원하는 자리수만큼 채워서 반환

사용법 : RPAD(원본문자열 , 원하는 자리수, 채울 문자열)

ex ) SELECT RPAD('ABC',10,'0')  FROM DUAL;

결과 : ABC0000000

#ex.1)
 mysql> SELECT LPAD('test', 6, '#');
      -> ##test

 mysql> SELECT LPAD('test', 9, '#');
      -> #####test

 mysql> SELECT LPAD('test', 2, '#');
      -> te

 

RTRIM

해당 문자열의 오른쪽에 있는 공백을 제거해주는 함수

 

사용법

--문법--
LTRIM(문자열)
--예제--
DECLARE @SQL NVARCHAR(200)
SET @SQL = 'SPACE '
RTRIM(@SQL)