인천일보아카데미/- 학습일지
[학습일지]JAVA교육일지 46일차
w1z
2022. 6. 19. 00:38
1. Case
2. Order by
3. Function
1. Case
- 컬럼 리스트, 조건절, 정렬에서 사용.(모든곳에서 사용가능)
- 자바의 if / switch와 비슷한 행동
- 조건을 만족하지 못하는 컬럼은 'NULL'을 반환
예제1
SELECT
CASE
WHEN GENDER = 'm' THEN '남자'
WHEN GENDER = 'f' THEN '여자'
END AS GENDER
FROM EX1;
--> GENDER 캘럼안에 m -> 남자, f -> 여자
예제2 ELSE
SELECT
CASE
WHEN CONTINENT = 'AS' THEN '아시아'
WHEN CONTINENT = 'EU' THEN '유럽'
ELSE '기타' --그 외 나머지값은 NULL이 되므로, '기타'로 표시
END AS CONTINENT
FROM EX2;
--> CONTINENT 캘럼안에 AS -> 아시아, EU -> 유럽
예제3 BETWEEN
SELECT
CASE
WHEN WEIGHT BETWEEN 50 AND 80 THEN '정상체중'
WHEN WEIGHT BETWEEN 90 AND 100 THEN '과체중'
ELSE '비정상체중'
END AS WEIGHT
FROM EX3;
--> WEIGHT가 60~80 -> 정상체중 90~100 -> 과체중, 그외에 비정상체중
예제4 IS NULL
SELECT
CASE
WHEN CHECK IS NULL THEN '완료'
WHEN CHECK IS NOT NULL THEN '미완료'
END AS STATE
FROM EX4;
--> CHECK가 NULL이면 완료, 아니라면 미완료
예제5 IN
SELECT
CASE
WHEN POSITION IN ('과장', '부장') THEN '세단'
WHEN POSITION IN ('대리', '사원') THEN '경차'
END AS POSITION
FROM EX5;
--> POSITION이 (과장, 부장) -> 세단, (대리, 사원) -> 경차
2. Order by
- 정렬, Sort
- 레코드 순서 정렬
- 원본 테이블의 레코드 정렬이 아닌, SELECT한 테이블의 결과 테이블(결과셋)의 정렬이다.
- 오름차순(ASC), 내림차순(DESC)
- 특정 컬럼값을 기준으로 레코드를 정렬한다.
기본적인 문법>
SELECT 컬럼리스트
FROM 테이블명
WHERE 조건
ORDER BY 정렬기준;
정렬 기준 자료형>
1. 숫자형
2. 문자형
3. 날짜시간형
SELECT의 실행순서>
FROM -> WHERE -> SELECT -> ORDER BY예제1
SELECT WEIGHT FROM EX1 ORDER BY WEIGHT ASC; --오름차순 정렬, 생략가능
SELECT WEIGHT FROM EX1 ORDER BY WEIGHT DESC; --내림차순 정렬
예제2 다중정렬
SELECT
NAME, AGE, HEIGHT
FROM EX2
--ORDER BY NAME ASC; --1차 정렬(기준 1개)
--ORDER BY NAME ASC, AGE ASC; --2차 정렬(기준 2개)
ORDER BY NAME ASC, AGE ASC, HEIGHT ASC;--3차 정렬(기준 3개)
--> 이름, 나이, 키 오름차순 정렬
예제3 IN
SELECT
NAME, POSITION, SALARY
FROM EX3
ORDER BY CASE
WHEN POSITION IN ('부장', '과장') THEN SALARY * 2
WHEN POSITION IN ('사원', '대리') THEN SALARY * 1.5
END DESC;
--> 이름, 직급(부장, 과장), 월급 * 2 내림차순정렬
--> 이름, 직급(사원, 대리), 월급 * 1.5 내림라순정렬
예제4 직위별로 정렬(부장 - 과장 - 대리 - 사원)
SELECT
NAME, POSITION
FROM EX4
ORDER BY CASE
WHEN POSITION = '부장' THEN 1
WHEN POSITION = '과장' THEN 2
WHEN POSITION = '대리' THEN 3
WHEN POSITION = '사원' THEN 4
END ASC;
--> 이름, 직급별(부장 - 과장 - 대리 - 사원 순으로..)3. Function
[ 집계 함수, Aggregation Function ]
1. COUNT()
- 결과셋의 레코드 갯수를 반환
- NUMBER COUNT(컬럼명)
- NULL은 카운트에서 제외한다.(*****)
--예제1
SELECT COUNT(*) FROM EX1;
--모든 레코드 갯수 반환
SELECT COUNT(NAME) FROM EX1;
--NAME 총 갯수
SELECT COUNT(NAME), COUNT(AGE) FROM EX1;
--NAME, AGE 각각 총 갯수
SELECT COUNT(NAME, AGE) FROM EX1;
--ERROR 컬럼리스트에 집계함수, 단일컬럼을 동시에 할 수 없다.
--예제2, 남자, 여자, 전체 인원수 구하기
SELECT
COUNT(*) AS "전체 인원수"
COUNT(CASE
WHEN GENDER 'm' THEN '남자'
END) AS "남자 인원수"
COUNT(CASE
WHEN GENDER 'f' THEN '여자'
END) AS "여자 인원수"
FROM EX2;
--모든 레코드 갯수, 남자 인원수, 여자 인원수 반환
2. SUM()
- NUMBER SUM(컬럼명)
- 해당 컬럼값들의 합을 구한다.
- 숫자형만 대상(문자형X, 날짜X)
SELECT SUM(WEIGHT) FROM EX1;
SELECT
--방법1
SUM(WEIGHT) + SUM(HEIGHT),
--방법2
SUM(WEIGHT + HEIGHT)
FROM EX2;
3. AVG()
- NUMBER AVG(컬럼명)
- 해당 컬럼값들의 평균값을 반환한다.
- 숫자형만 가능하다.
- 해당 컬럼이 NULL을 가진 항목은 제외한다.
SELECT
--방법1
SUM(WEIGHT) / COUNT(*),
--방법2
AVG(WEIGHT)
FROM EX1;
4. MAX()
- OBJECT MAX(컬럼명) : 최댓값 반환
- 숫자형,문자형,날짜형
5. MIN()
- OBJECT MIN(컬럼명) : 최솟값 반환
- 숫자형,문자형,날짜형
--숫자형 최대값, 최솟값
SELECT MAX(WEIGHT), MIN(WEIGHT) FROM EX1;
--문자형 최대값, 최솟값 (가, 나, 다, 라, ...)
SELECT MAX(NAME), MIN(NAME) FROM EX2;
--날짜형 최근날짜, 과거날짜
SELECT MAX(DATE), MIN(DATE) FROM EX3;
[ 숫자 함수, Numeric Function ]
1. ROUND()
- 반올림 함수
- NUMBER ROUND(컬럼명) : 정수 반환
- NUMBER ROUND(컬럼명, 소수이하 자릿수) : 실수 반환
- 숫자형, 날짜형
SELECT ROUND(123.456) FROM DUAL; --123
SELECT ROUND(123.456, 1) FROM DUAL; --123.4
SELECT ROUND(123.456, 2) FROM DUAL; --123.45
2. FLOOR(), TRUNC()
- 무조건 내림 함수
- 자바의 정수/정수 --> 몫
- NUMBER FLOOR(컬럼명)
- NUMBER TRUNC(컬럼명 [, 소수이하 자릿수])
SELECT FLOOR(123.456) FROM DUAL; --123
SELECT FLOOR(123.999999999) FROM DUAL; --123
SELECT TRUNC(123.456, 1) FROM DUAL; --123.4
3. CEIL()
- 무조건 올림 함수
- NUMBER CEIL(컬럼명)
SELECT CEIL(123.456) FROM DUAL; --124
SELECT CEIL(123.0000000001) FROM DUAL; --124
4. MOD()
- 나머지 함수
- NUMBER MOD(피제수, 제수)
SELECT MOD(10, 3) FROM DUAL; --1
SELECT -- 100분을 1시간 40분으로 만들기
FLOOR(100/60) AS 시,
MOD(100, 60) AS 분
FROM DUAL;
[ 문자열 함수, String Function ]
1. upper(), lower(), initcap()
- 대문자, 소문자 변환
- varchar2 upper(컬럼명)
- varchar2 lower(컬럼명)
- varchar2 initcap(컬럼명)
SELECT UPPER('a, b, c') FROM DUAL; -- A, B, C
SELECT LOWER('A, B, C') FROM DUAL; -- a, b, c
SELECT INITCAP('abc, ABC, aBc') FROM DUAL; -- Abc, Abc, Abc
2. SUBSTR()
- 문자열 추출 함수
- VARCHAR2 SUBSTR(컬럼명, 시작위치, 가져올 문자 개수
- VARCHAR2 SUBSTR(컬럼명, 시작위치)
- 서수를 1부터 시작(***)
SELECT
SUBSTR('가나다라마바사아자차카타파하', 5), -- 마바사아자차카타파하
SUBSTR('가나다라마바사아자차카타파하', 5, 3) -- 마바사
FROM DUAL;
3. LENGTH()
- 문자열 길이
- NUMBER LENGTH(컬럼명)
SELECT LENGTH('TEST') FROM DUAL; -- 4
4. INSTR()
- 검색어의 위치를 반환 (자바의 INDEXOF()랑 비슷)
- ONE-BASED INDEX(서수가 1부터 시작)
- NUMBER INSTR(컬럼명, 검색어)
- NUMBER INSTR(컬럼명, 검색어, 시작위치)
SELECT
INSTR('안녕하세요. 홍길동님', '홍길동'), -- 8
INSTR('안녕하세요. 홍길동님', '아무개'), -- 0
INSTR('안녕하세요. 홍길동님. 잘가세요. 홍길동님', '홍길동', 11) -- 20
FROM DUAL;
5. LPAD(), RPAD(), LEFT PADDING, RIGHT PADDING
- 원하는 자리수(정렬, 단위) 맞추기
- VRACHAR2 LPAD(컬럼명, 개수, 문자)
- VRACHAR2 RPAD(컬럼명, 개수, 문자)
SELECT
LPAD('1', 3, '0'), -- 001
LPAD('1', 3, '@'), -- @@1
RPAD('1', 3, 0), -- 100
RPAD('1', 3, '+') -- 1++
FROM DUAL;
6. TRIM(), LTRIM(), RTRIM()
- 공백제거
- VARCHAR2 TRIM(컬럼명)
- VARCHAR2 LTRIM(컬럼명)
- VARCHAR2 RTRIM(컬럼명)
SELECT
TRIM(' 하나 둘 셋 '), -- 하나 둘 셋
LTRIM(' 하나 둘 셋 '), -- 하나 둘 셋( )
RTRIM(' 하나 둘 셋 ') -- ( )하나 둘 셋
FROM DUAL;
7. REPLACE()
- 문자열 치환
- VARCHAR2, REPLACE(컬럼명, 찾을문자, 바꿀 문자열)
SELECT REPLACE('테스트입니다123', '123', '000') FROM DUAL; -- 테스트입니다000
8. DECODE()
- 문자열 치환
- REPLACE() 유사
- VARCHAR2 DECODE(컬럼명, 찾을문자열, 바꿀문자열[, 찾을문자열, 바꿀문자열] X N)
--남자, 여자 구분 하기
SELECT
DECODE(GENDER, 'm', '남자'),
DECODE(GENDER, 'f', '여자')
FROM EX1;MEMO>
# 오라클은 자바와 비슷하면서 다른점이 분명히 있는거같다
# 오라클을 3일 배웠지만 느낀것은 한 영어의 문장처럼 자연스럽고, 가장 중요한 SELECT 같은 경우 실행 순서를 먼저 파악하면서 공부하는게 좋을거같다.
# 결과셋을 테스트 해보고싶을때는 DUAL(시스템)테이블을 이용하면된다.