개요
- QueryDSL 로 쿼리를 짜던중, 기본적인 SQL 쿼리 문법을 정확히 숙지하고 있지 않은 것 같아, SQL 쿼리 문법을 공부해 보려고 한다.
그룹 제어
SELECT [GROUP BY 에서 사용된 컬럼명들, 집계함수]
FROM [테이블명]
WHERE [조건들]
GROUP BY [컬럼명들]
HAVING [GROUP BY 절에 해당하는 조건들]
- 위의 내용은 where 절에서 만족하는 데이터들을 그룹핑하고 having 절로 조건을 걸어서 데이터를 가져온다.
- select 절에 사용될 컬럼들은 group by 에 명시된 컬럼들만 사용할 수 있다.
- group by 에 명시된 컬럼 외에 다른 컬럼을 사용하고 싶다면? -> 집계함수를 통해서 사용할 수 있다.
- where 절과 having 절의 가장 큰 차이점은 having 절은 집계함수를 사용해서 조건을 표현할 수 있다는 것이다.
예시
SELECT NAME, COUNT(ADDR)
FROM Cutomer
GROUP BY ADDR
HAVING COUNT(ADDR) > 2
분기문
SIMPLE_CASE_EXPRESSION
SELECT
(CASE [컬럼명] WHEN [비교값1] THEN[반환값1]
WHEN [비교값2] THEN[반환값2]
WHEN [비교값3] THEN[반환값3]
WHEN [비교값4] THEN[반환값4]
WHEN [비교값5] THEN[반환값5]
ELSE [WHEN 절 이외의 조건일때 반환될 값]
END AS [별칭 컬럼명])
FROM [테이블명]
예시
SELECT NAME
, NUMBER
, (CASE NUMBER WHEN '1001' THEN '문구생산부'
WHEN '2001' THEN '가구생산부'
ELSE '부서없음'
END) as "부서명"
FROM EMPLOYEE
ORDER BY NMAE
SEARCH_CASE_EXPRESSION
SELECT
(CASE WHEN [조건문1] THEN[반환값1]
WHEN [조건문2] THEN[반환값2]
WHEN [조건문3] THEN[반환값3]
WHEN [조건문4] THEN[반환값4]
WHEN [조건문5] THEN[반환값5]
ELSE [WHEN 절 이외의 조건일때 반환될 값]
END AS [별칭 컬럼명])
FROM [테이블명]
예시
SELECT NAME
, NUMBER
, (CASE WHEN NUMBER = '1001' THEN '문구생산부'
WHEN NUMBER = '2001' THEN '가구생산부'
ELSE '부서없음'
END) as "부서명"
FROM EMPLOYEE
ORDER BY NMAE
집합연산
SELECT [컬럼 1]
,[컬럼 2]
,[컬럼 3]
FROM [테이블명 1]
UNION
SELECT [컬럼 1]
,[컬럼 2]
,[컬럼 3]
FROM [테이블명 2]
SELECT [컬럼 1]
,[컬럼 2]
,[컬럼 3]
FROM [테이블명 1]
UNION ALL
SELECT [컬럼 1]
,[컬럼 2]
,[컬럼 3]
FROM [테이블명 2]
- select 문의 결과를 합칠때 사용된다.
- 컬럼의 갯수, 순서, 타입이 일치(호환)해야 한다.
- union -> 중복 제거
- union all -> 중복 제거 x
예시
SELECT ID, NAME
FROM UN1
UNION
SELECT ID, NAME
FROM UNI2
SELECT ID, NAME
FROM UN1
UNION ALL
SELECT ID, NAME
FROM UNI2
집계 함수
- MAX([컬럼명]) : 컬럼 내 값들 중 최대값을 반환한다.
- MIN([컬럼명]) : 컬럼 내 값들 중 최소값을 반환한다.
- COUNT([컬럼명]) :컬럼 내 값의 전체 행 수를 반환한다. (NULL 값은 제외)
- SUM([컬럼명]) : 컬럼의 데이터 타입이 숫자일 경우, 해당 컬럼 내 모든 데이터의 합을 반환한다.
- AVG([컬럼명]) : 컬럼의 데이터 타입이 숫자일 경우, 해당 컬럼 내 모든 데이터의 평균을 반환한다.
문자열 함수
- SUBSTRING(string, int, int)
- 첫 번째 명시된 문자열의 부분 문자열을 잘라온다.
- LTRIM(string), LTRIM(string,string)/RTRIM(string), RTRIM(string,string)
- 명시된 문자열의 좌측/우측 공백을 제거한다. 특정문자 제거한다.
- LPAD(string,n,string)/RPAD(string,n,string)
- 첫 번쨰 명시한 문자열에 길이가 n 이 되도록 좌측/우측부터 세 번째 명시한 문자열로 채운 표현식을 반환한다.
- REPLACE(string,string_pattern,string_replacement)
- 첫 번째 명시된 문자열중 string_pattern에 해당하는 문자열을 string_replacement 문자열로 변환한다.
- LENGTH(string)
날짜 함수
- NOW()
- AGE(timestamp,timestamp) / AGE(timestamp)
- 두 날짜 사이의 시간차이를 계산 / 현재 날짜와 첫 번째 명시한 날짜의 시간 차이를 계산한다.
- DATE_PART(text, timestamp)
- 두 번째 명시한 timestamp 에서 첫 번째 명시한 날짜 키워드 인자에 해당하는 값을 추출한다.
- DATE_TRUNC(test, tiomestamp)
- 두 번째 명시된 timestamp 에서 첫 번째 명시한 날짜 키워드 인자에 해당하는 값 이하의 날짜 데이터를 Default 처리하고 반환한다.
Group By + Having
SELECT tpr.ENumber AS 직원번호, SUM(tpr.PCount) AS 총_생산량
FROM tProduction AS tpr
WHERE tpr.PDate BETWEEN CAST('2020-01-01' as timestamp) and CAST('2020-02-01' as timestamp)
GROUP BY tpr.ENumber
HAVING SUM(tpr.PCount) >= 500
- tpr.ENumber 로 묶어서 tpr.PCount 로 합쳐라, 그리고 합친값을 Having 으로 조건 검사해라
서브 쿼리
SELECT (SELECT tde.DName FROM tDepartment AS tde WHERE tde.DNumber = tem.DNumber) AS 부서명
, tem.EName AS 직원명
, (SELECT tra.RName FROM tRank AS tra WHERE tra.RNumber = tem.RNumber) AS 직급명
, tem.EAddr AS 직원주소
FROM tEmployee AS tem
SELECT tem.EName AS 직원명
FROM tEmployee AS tem
WHERE tem.ENumber IN (
SELECT tpr1.ENumber
FROM tProduction AS tpr1
WHERE tpr1.PCount > (
SELECT AVG(tpr2.PCount)
FROM tProduction AS tpr2
)
)
SELECT tBase.INumber AS 제품번호, tBase.PCount AS 총_생산량
FROM
(
SELECT tpr.INumber, SUM(tpr.PCount) AS PCount
FROM tProduction AS tpr
WHERE tpr.PDate BETWEEN CAST('2020-01-01' as timestamp) AND CAST('2021-01-01' as timestamp)
GROUP BY tpr.INumber
) AS tBase
ORDER BY tBase.PCount desc