1. 컬럼 확인 (select)
select 컬럼1,컬럼2... from 테이블명;
2. 모든 컬럼 확인 (*)
select * from 테이블명;
3. 컬럼명 변경 (as)
select 컬럼1 as 변경이름(대소문자 사용시 ""사용) from 테이블명;
4. 연결 (||)
select 컬럼명1 || 컬럼명2 from 테이블명;
5. 중복제거 (distinct, unique)
select DISTINCT 컬럼명1 from 테이블명;
select UNIQUE 컬럼명1 from 테이블명;
6. 정렬 (order)
select 컬럼명1,컬럼명2 from 테이블명 ORDER BY 컬럼명1 asc(오름차순) 또는 desc(내림차순);
※ 정렬구문은 맨마지막에 실행
※ order by 컬럼1,컬럼2 경우 컬럼1을 정렬후 컬럼2
7. 비교 (where)
select 컬럼명1,컬럼명2 from 테이블명 WHERE 컬럼명1 = 숫자 또는 문자;
※ 비교연산자사용 : >=, <=, =, !=(^=,<>), IS NULL, NULL
※ BETWEEN AND(낮은값 AND 높은값 으로 작성할것)
※ LIKE(문자검색, 와일드카드% 검색) ex) where 컬럼1 LIKE 'a%';
※ where절에 or사용시 = 는필요없음 ex) where (컬럼명1<1 or 컬럼명1>3)
※ 날짜 검색시 ''를 사용해야함 WHERE checkDate = '2021/10/04'
※ NLS_SESSION_PARAMETERS에서 날짜형식 확인할것
8. 산술연산자
select 숫자테이터 컬럼명 + 숫자 from 테이블명;
※ where 절에서도 사용가능 ex) where 컬럼1+100 = 1000;
9. Null치환
select 컬럼1+NVL(컬럼2,0) from 테이블명;
※ 컬럼2 값이 NULL일경우 0으로 치환
10. != NULL
검색할수없음. NULL은 데이터가 없는 상태(state)이기때문에
11. IN 사용하여 한번에 조회
where 컬럼1 IN ('a','b','c');
12. 논리연산자 : AND OR NOT 연산표
AND
| TRUE | FALSE | NULL | |
| TRUE | TRUE | FALSE | NULL |
| FALSE | FALSE | FALSE | FALSE |
| NULL | NULL | FALSE | NULL |
OR
| TRUE | FALSE | NULL | |
| TRUE | TRUE | TRUE | TRUE |
| FALSE | TRUE | FALSE | NULL |
| NULL | TRUE | NULL | NULL |
NOT
| TRUE | FALSE | NULL | |
| TRUE | FALSE | TRUE | NULL |
13. 문자변환함수
UPPER : 대문자 변환
LOWER : 소문자 변환
INITCAP : 첫번째 철자만 대문자
ex) select upper(칼럼1), lower(칼럼2), initcap(칼럼3) from 테이블명;
14. 추출 : SUBSTR
select SUBSTR('ABCDEFG',1,5) from dual; // 첫번째 부터 5개
결과 : ABCDE
select SUBSTR('ABCDEFG',3) from dual; // 세번째부터 끝까지
결과 : CDEFG
15. 특정 철자 위치 출력 : INSTR
select INSTR('ABCDE','C') from dual; // 숫자로 반환
결과 : 3
16. 철자 변경하기 : REPLACE
select 칼럼1, REPLACE(칼럼1, 'A', '$') from 테이블명;
17. 특정철자 오른쪽 왼쪽 채우기 : LPAD, RPAD
select LPAD(컬럼1,3,'*'), RPAD(컬럼2,1,'^') from 테이블명;
결과 : ***컬럼1값 컬럼2값^
※ 그래프로 보고싶을때 사용하면 좋음
18. 잘라내기 : TRIM, RTIM, LTIM
TRIM : 양쪽 자름
RTIM : 오른쪽 자름
LTIM : 왼쪽 자름
ex) select LTRIM('abcd','a') from 테이블명;
※ 공백제거시 사용
19. 반올림 : ROUND
select ROUND(123.456,1) from dual;
결과 : 123.5 // 소수점 두번째 자리에서 반올림함
※ 0은 소수점 첫번째, -1은 일의자리에서 반올림
20. 버림 : TRUNC
select TRUNC(123.456,1) from dual;
결과 : 123.4
select TRUNC(123.456,-2) from dual;
결과 : 100
21. 몫,나머지값 : FLOOR, MOD
select FLOOR(5,2) from dual;
결과 : 2
※ 몫이 무한일때는 맨마지막 수를 반환
select MOD(5,2) from dual;
결과 : 1
※ 짝수 홀수 구분시 사용하면 좋음
22. 개월 계산 : MONTHS_BETWEN
select MONTHS_BETWEN(최신날짜, 예전날짜) from 테이블명;
select months_between(to_date('2021-10-04','RRRR-MM-DD'),to_date('2020-10-04','RRRR-MM-DD')) from dual;
결과 : 개월수
산술연산으로도 가능
select to_date('2021-10-04','RRRR-MM-DD') - to_date('2020-10-04','RRRR-MM-DD') from dual;
결과 : 일수 // 7로나누어서 주로 결과값 계산
23. 일, 개월수 더하기 : ADD_MONTHS
일수 더하기
select to_data('2021-10-04','RRRR-MM-DD') + 365 from dual;
결과 : 22/10/04
※ 각달 일수가 다르기때문에 interval 사용하여 계산
select to_data('2021-10-04','RRRR-MM-DD') + interval '12' month from dual;
결과 : 22/10/4
※ interval 사용하여 계산하기 (연도,달,일,시분초)
1년 2개월 후 날짜출력
select to_data('2021-10-04','RRRR-MM-DD') + interval '1-2' year(1) to month from dual;
결과 : 22/12/4
개월수 더하기
select ADD_MONTHS(to_data('2021-10-04','RRRR-MM-DD'),12) from dual;
결과 : 22/10/04
※ TO_YMINTERVAL도 있음
24. 특정일 위에오는 날짜 출력 : NEXT_DAY
select '2021-10-04' NEXT_DAY('2021-10-04','월요일') from dual;
결과 : 21/10/11
25. 달의 마지막날 출력 : LAST_DAY
select LAST_DAY('2021-10-04','월요일') from dual;
결과 : 21/10/31
※ 남은 날짜 계산 이용
select LAST_DAY(sysdate) - sysdate from dual;
결과 : 27 (오늘 포함)
26. 문자형으로 변환 : TO_CHAR
select TO_CHAR(sysdate,'DAY'), TO_CHAR(1000,'999,999') from dual; //'999,999' 천단위 출력
결과 : 월요일, 1000
27. 추출 : EXTRACT
select EXTRACT(year from sysdate) from dual;
결과 : 2021
28. 날짜형으로 데이터형 변환 : TO_DATE
TO_DATE('21/10/04','RR/MM/DD')
29. NULL값 대신 다른데이터 출력 : NVL, NVL2
select NVL(컬럼1,0) from 테이블명;
※ 실제 데이터가 변경되는것은 아님
※ 산술연산시 NULL값 컬럼을 0으로 치환후 계산할때사용
NVL2
select 컬럼1, 컬럼2 NVL2(컬럼1,컬럼1+컬럼2, 컬럼2)
// 컬럼1이 NULL이 아닌경우 컬럼1+컬럼2출력, NULL인경우 컬럼2로 출력
30. SQL의 IF문 : DECODE
select 컬럼1,컬럼2, DECODE(컬럼1,1,100,2,200) from 테이블명
컬럼1의값이 1이면 100, 2이면 200으로 출력
31. CASE문
select 칼럼1 CASE WHEN 칼럼1 >=1 THEN 100
WHEN 칼럼1 >=2 THEN 200
WHEN 칼럼1 >=3 THEN 300
WHEN 칼럼1 is null THEN 400
ELSE 0 END
from 테이블명
32. 최대값, 최소값 출력 : MAX, MIN
select MAX(컬럼명) from 테이블명
select MIN(컬럼명) from 테이블명
※ 여러 컬럼을 검색할때는 GROUP BY 사용할것
ex) select 컬럼1, MAX(컬럼2)
from 테이블명
where 컬럼1 = 'A'
GROUP BY 컬럼1;
※ WHERE은 조건이 거짓이어요 결과를 항상 출력한다 그러므로 nvl을 사용해서 확인해볼것!
33. 평균값 : AVG
AVG(컬럼명)
※ NULL값을 무시하고 평균을 구하려면
select AVG(NVL(컬럼명,0)) from 테이블명
※ 하지만 그룹함수는 NULL값을 무시하니 NVL 함수를 사용해서 할필요없다 작업(시간)이 길어진다.
34. 총합 : SUM
SUM(컬럼명)
※ GROUP BY 사용시 WHERE 대신 HAVING 으로 해서 그룹함수를 사용해야함
select 컬럼1, SUM(컬럼2)
from 테이블명
GROUP BY 컬럼1
HAVING SUM(컬럼2) >1 ;
35. 카운트 : COUNT
COUNT(칼럼명)
※ NULL값은 카운트하지않는다 : NVL을 사용해서 계산양을 늘리필요가없다
36. 순위 출력1 : RANK
select 컬럼1 RANK() over (ORDER BY 컬럼1 ASC) from 테이블명 where 컬럼2 in('data1',data2');
37. PARTITION BY : GROUP BY와 동일하게 그룹지어 결과를 출력
select 컬럼1,컬럼2, RANK() over (PARTITION BY 컬럼1 ORDER BY 컬럼2 DESC) from 테이블명;
36. 순위 출력2 : DENSE_RANK
select 컬럼1, 컬럼2 DENSE_RANK() over (ORDER BY 컬럼1 DESC) from 테이블명;
결과 : 동일값이 있는경우 순위를 건너뛰지 않고 차례로 부여함
ex) RANK는 1,1,3,4,5,6
DENSE_RANK는 1,1,2,3,4,5
ex) select DENSE_RANK('21/10/05') within group (ORDER BY date ASC) from 테이블명;
within group : 그룹내에서 순위를 확인
37. 비율 등급 : NTILE
select NTILE(4) over (order by 컬럼1 desc NULL LAST) from 테이블명 where 컬럼2 in ('A','B');
결과 : 4등급으로 나누어 계산된후 1,2,3,4 로 나누어짐
※ NULL LAST : NULL값 열은 맨아래 출력
38. 가로로 출력 : LISTAGG
select 컬럼1, LISTAGG(컬럼2,'/') within group (order ny 컬럼2) from 테이블명 group by 컬럼1;
※ GROUP BY는 LISTAGG를 사용하려면 필수로 기술할것
※ LISTAGG(컬럼2,'/') '/' 구분
39. 앞행,뒤행 출력 : LAG, LEAD
select 칼럼1,칼럼2,칼럼3,
LAG(칼럼1,1) over (order by 칼럼3 asc)
LEAD(칼럼1,1) over (order by 칼럼3 asc)
from 테이블명
※ LAG(칼럼1,2) : 앞앞행 출력
40. ROW 출력 : SUM+DECODE
select sum(decode(컬럼1, 1, 컬럼2))
sum(decode(컬럼1, 2, 컬럼2))
sum(decode(컬럼1, 3, 컬럼2))
from 테이블명
결과 :
| 1 | 2 | 3 |
| 총합 | 총합 | 총합 |
41. ROW(열) 출력 : PIVOT
select *
from ( select 컬럼1, 컬럼2 from 테이블명)
PIVOT (sum(컬럼2) from 컬럼1 IN (컬럼1의데이터1,컬럼1의데이터2,컬럼1의데이터3);
결과 : 40번과 동일
ex ) 문자형일 경우 PIVOT (sum(컬럼2) from 컬럼1 IN ('A','B','C');
42. ROW(열)을 행(COLUMN)으로 출력 : UNPIVOT
select * from 테이블명 UNPIVOT (컬럼1 for 컬럼2 in (컬럼2데이터1, 컬럼2데이터2, 컬럼2데이터3));
※ NULL값도 포함시키려면 INCLUDE NULL 사용
ex) UNPIVOT INCLUDE NULL (컬럼1 for 컬럼2 in (컬럼2데이터1, 컬럼2데이터2, 컬럼2데이터3));
43. 누적데이터 출력 : SUM OVER
select 컬럼1,컬럼2, SUM(컬럼2) OVER ( ORDER BY 컬럼1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적 from 테이블명;
※ 윈도우 방식 : UNBOUNDED PRECEDING 맨첫번째 행을 가르킴
UNBOUNDED FOLLOWING 맨마직막 행을 가르킴
CURRENT ROW 현재행 가르킴
| 컬럼1 | 컬럼2 | 누적 |
| a | 1 | 0.5 |
| b | 2 | 3 |
| c | 3 | 5 |
44. 비율출력 : RATIO_TO_REPORT
select 컬럼1,컬럼2, RATIO_TO_REPORT(컬럼2) OVER () 비율 from 테이블명 where 컬럼3 ='A';
A의 중에서 자신의 비율이 얼마인지 확인하는 커리
45. 집계결과 출력1 : ROLLUP
select 컬럼1,컬럼2 from 테이블명 group by ROLLUP(컬럼1);
결과 :
| name | num |
| a | 10 |
| b | 20 |
| 30 |
※ ROLLUP(컬럼1,컬럼2) : 가각 집계후 마집막에 총집계
46. 집계결과 출력2 : CUBE
select 컬럼1,컬럼2 from 테이블명 group by CUBE(컬럼1);
ROLLUP의 반대로 상단에 집계 출력
결과 :
| name | num |
| 30 | |
| a | 10 |
| b | 20 |
※ ROLLUP과 비교하면 CUBE는 Group by 컬럼에 대하여 집계한가지가 더 출력됨
47. 집계결과 출력3 : GROUPING SETS
select 컬럼1,컬럼2,sum(컬럼3) from 테이블명 group by GROUPING SETS((컬럼1),(컬럼2),());
※ 집계하고 싶은 컬럼을 GROUPING SETS() 안에 기술
48. 출력결과 넘버링 : ROW_NUMBER
select 컬럼1, 컬럼2, 컬럼3, ROW_NUMBER() OVER(PARTITION by 컬럼1 order by 컬럼3 DESC) as 번호 from 테이블명 where 컬럼2 in (1,2);
결과 : 컬럼3에 대하여 순위부여
168
49. 출력행 제안하기 1