[SELECT] 기초와 함수

Ⅰ. SELECT 기초

 

SELECT란? 데이터를 조회할 때 사용하는 명령어

RESULT SET : SELECT로 조회된 데이터의 집합.

SELECT * FROM TABLENAME;
SELECT COLUMN1, COLUMN2 FROM TABLENAME;

DISTINCT : 중복은 한번만 조회, SELECT 절에 DISTINCT는 하나만 가능.

' ' :  리터럴 값.

" " : 별칭. 나는 주로 AS 별칭 형태로 쌍따옴표를 생략하곤 하지만,

          별칭에 띄어쓰기나 특수문자가 포함된 경우 오류가 날 수 있으므로, " " 를 사용하는 것이 좋다.

|| : 연결. 추후에 기술할 CONCAT 함수와 비슷

---

WHERE : 조회하는 테이블에 조건 걸기

SELECT COLUMN1, COLUMN2
FROM TABLENAME
WHERE COLUMN1='LITERAL';

---

논리연산자 AND, OR

SELECT COLUMN1, COLUMN2
FROM TABLENAME
WHERE COLUMN1='LITERAL' AND/*OR*/ COLUMN2>30;

범위 비교 BETWEEN AND

SELECT COLUMN1, COLUMN2
FROM TABLENAME
WHERE COLUMN1 BETWEEN 50 AND 100;

---

 

IN : 같은 도메인에 해당하는 동등비교가 여러개인 경우 AND 없이 사용할 수 있는 방법

--둘은 같은 RESULT SET을 가진다.
SELECT COLUMN1, COLUMN2
FROM TABLENAME
WHERE COLUMN1='LITERAL' AND COLUMN1='ANOTHER';

SELECT COLUMN1, COLUMN2
FROM TABLENAME
WHERE COLUMN1 IN('LITERAL','ANOTHER');

LIKE : CHARACTER의 경우 비슷한 도메인 값을 조회. '_'와 '%'를 사용하여 빈칸을 만들어 줄 수 있다.

'_' : 1글자

'%' : 0글자 이상

SELECT COLUMN1, COLUMN2
FROM TABLENAME
WHERE COLUMN1 LIKE '_밍_';

SELECT COLUMN1, COLUMN2
FROM TABLENAME
WHERE COLUMN1 LIKE '%밍%';

와일드 카드 :  ESCAPE를 사용하여 특정 문자를 특수문자로 인식시켜, _나 %를 사용할 수 있는 방법.

SELECT STUDENT_ID, STUDENT_NAME
FROM MEMBER
WHERE STUDENT_ID LIKE '_$_%' ESCAPE '$';
--$뒤의 '_'는 _라는 문자로 인식되어 두번째 자리가 _인 ID를 검색한다.

---

IS NULL

IS NOT NULL

SELECT COLUMN1, COLUMN2
FROM TABLENAME
WHERE COLUMN1 IS /*NOT*/ NULL;

ORDER BY 

기본값 ASC(오름차순), 내림차순은 DESC.

SELECT COLUMN1, COLUMN2
FROM TABLENAME
ORDER BY COLUMN1 DESC, COLUMN;

---

Ⅱ. 함수

산술연산하는 과정에 NULL 값이 존재할 경우 산술연산 결과마저도 NULL이 된다.

TEST용 기본 테이블은 DUAL을 이용하면 편하다.

 

1. 단일행 함수

1-1. 문자(CHAR)

LENGTH : 문자 수

LENGTHB : 바이트 수(영어, 숫자, 특수문자 = 1byte, 한글 = 3byte 로 계산)

SELECT LENGTH('가나다-123') FROM DUAL;--7개
SELECT LENGTHB('가나다-123') FROM DUAL;--3*3+4=13개

---

INSTR(문자열, 찾을 문자, 그 문자들 중 '몇번째'로 조회할까?/*-면 역순*/)

SELECT INSTR('abc123-1234abc', 'a',1) FROM DUAL;

---

SUBSTR(문자열, 시작점/*역순이면 뒤에서부터*/, 시작부터 몇개를 반환할까?)

SELECT SUBSTR('abc123-1234abc', 2, 3) FROM DUAL;

---

LPAD / RPAD

LPAD/*RPAD*/(문자열, 전체 바이트, 채울 문자)

영어, 숫자, 특수문자는 1byte, 한글은 2byte로 계산.

SELECT LPAD(EMAIL, 20, '*'/*생략하면 공백*/) FROM STUDENT;

---

LTRIM / RTRIM

: 문자열의 끝(L / R)에서 특정한 문자가 존재하고, 반복하여 연결된 그 같은 문자들을 한번에 잘라내는 방법

SELECT RTRIM('0001230456000','1')
FROM DUAL; --안잘림!
SELECT LTRIM('0001230456000','O')
FROM DUAL; --결과값 : 1230456000

TRIM

SELECT TRIM('A' FROM 'AAABCAAA')
FROM DUAL;--양쪽(기본값) : BOTH

SELECT TRIM(LEADING 'A' FROM 'AAABCAAA')
FROM DUAL;--앞쪽

SELECT TRIM(TRAILING 'A' FROM 'AAABCAAA')
FROM DUAL;--뒷쪽

---

LOWER / UPPER / INITCAP

SELECT LOWER('Welcome to My World') FROM DUAL;--welcome to my world
SELECT UPPER('Welcome to My World') FROM DUAL;--WELCOME TO MY WORLD
SELECT INITCAP('Welcome to My World') FROM DUAL;--Welcome To My World

---

CONCAT : 

SELECT CONCAT(STR1, STR2) FROM DUAL;

---

REPLACE(문자열, 찾을 문자, 바꿀 문자)

SELECT REPLACE('ABC DEF FGH', 'DE', 'XY') FROM DUAL;--ABC XYF FGH

 

---

1-2. 숫자(NUMBER)

ABS(NUMBER) : 절댓값

MOD(NUMBER1, NUMBER2):NUMBER2로  NUMBER1을 나눈 나머지 값

SELECT ABS(-10) FROM DUAL;-- 10

SELECT MOD(10,3) FROM DUAL;-- 1
SELECT MOD(-10,3) FROM DUAL;-- -1

 

---

ROUND : 반올림. 기본값은 0의자리로 반올림.

CEIL : 소숫점 전부 올림

FLOOR : 소숫점 전부 버림

TRUNC : 소숫점 지정 버림

SELECT ROUND(123.456,2) FROM DUAL;--123.46 --올림
SELECT ROUND(123.456,-2) FROM DUAL;--120

SELECT CEIL(123.426) FROM DUAL;--124--올림
SELECT CEIL(123.426*10)/10 FROM DUAL; --123.5 --소수점 둘째자리 올림

SELECT FLOOR(123.765) FROM DUAL;--123 --버림

SELECT TRUNC(123.765,2) FROM DUAL;--123.76 --소수점 둘째자리 버림

---

1-3. 날짜(DATE)

DATE 타입에는 년, 월, 일, 시, 분, 초까지 저장되어있지만, 형식에 따라 표현하는 범위가 달라, 여러 방법으로 표시된다.

 

SYSDATE

SELECT SYSDATE FROM DUAL;

---

MONTHS_BETWEEN(시작일, 종료일) : 두 날짜 사이의 개월 수 반환.

ADD_MONTHS(시작일, 개월 수) : 특정 날짜에 해당 숫자만큼의 개월수를 더한 날짜

SELECT ROUND(MONTHS_BETWEEN(SYSDATE, ENTER_DATE))
FROM STUDENT;

SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL; --현재로부터 5개월 후

---

NEXT_DAY : 특정 날짜에서 가장 가까운 해당 요일을 찾아 날짜를 반환

LAST_DAY : 특정 날짜에 해당하는 달의 마지막 날짜를 구해서 반환(DATE 타입)

SELECT NEXT_DAY(SYSDATE, '일요일') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, '일') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 1) FROM DUAL; --1 : 일요일, 2:월요일...

'일요일', '일'을 넣어도 가능한 이유 : oracle을 한국어 버전으로 받았기 때문.

sun, sunday를 사용하고 싶다면 언어팩을 교체해준다.

ALTER SESSION SET NLS_LANGUAGE = AMERICAN;--KOREAN 변경하거나 언어팩 체인지

---

EXTRACT : 년도, 월, 일 정보를 추출해서 반환(NUMBER 타입)

SELECT EXTRACT(YEAR FROM SYSDATE) AS 년도
    , EXTRACT(MONTH FROM SYSDATE) AS 월
    , EXTRACT (DAY FROM SYSDATE) AS 일
FROM DUAL;

---

2. 형변환 함수

 

TO_CHAR

SELECT TO_CHAR(SYSDATE) FROM DUAL; -- 21/11/07
SELECT TO_CHAR(SYSDATE, 'YYYY-mon-day') FROM DUAL; -- 2021-11월-일요일
SELECT TO_CHAR(SYSDATE, 'year-mon-day') FROM DUAL; -- twenty twenty-one-11월-일요일

SELECT TO_CHAR(SYSDATE, 'PM HH:MI:SS') FROM DUAL; -- 오후 04:31:18
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL; -- 16:31:27

SELECT TO_CHAR(1234, '00000') FROM DUAL; --'01234'
SELECT TO_CHAR(1234, '99999') FROM DUAL; --' 1234'
SELECT TO_CHAR(1234, 'L00000') FROM DUAL; --'￦01234'

TO_DATE

SELECT TO_DATE(211101) FROM DUAL; --기본형 YY/MM/DD
SELECT TO_DATE('211101', 'YYMMDD') FROM DUAL;--연도를 제외할 경우 현재 시스템의 연도로 자동 설정
SELECT TO_DATE('211101','RRMMDD') FROM DUAL;--연도가 10의 자리에서 반올림

TO_NUMBER

문자가 포함되어있는 경우 숫자로 변환 불가능

SELECT TO_NUMBR('0123') FROM DUAL; -- 123

---

3. NULL 처리 함수

 

NVL(컬럼명, 대체할 리터럴 값)

SELECT NVL(TEST_CODE,'없음') FROM STUDENT; -- null 값인 부분은 '없음'으로 교체
SELECT NVL2(TEST_CODE,'있음','없음') FROM STUDENT; -- null이 아닌 부분은 '있음', null은 '없음'

NULLIF(비교대상1, 비교대상2)
비교하는 값이 동일할 경우 NULL, 동일하지 않을 경우 첫번째 값 반환

SELECT NULLIF('ABC','123') FROM DUAL; -- ABC
SELECT NULLIF('123','123') FROM DUAL; -- (null)

---

4. 선택 함수

 

DECODE(비교값, 조건값1, 결과값1, 조건값2, 결과값2...,그 외인 경우의 결과값)

DECODE는 동등비교만 되므로, BETWEEN이나 LIKE를 사용할 수 없다.

SELECT DECODE(ST_NAME,/*'%밍%', '밍밍씨',*/ '묭', '묭묭이', '먕먕이')
FROM STUDENT;

 --'%밍%' 부분은 인식하지 못하고 마지막의 ELSE 값으로 빠진다.

CASE WHEN THEN

SELECT CASE WHEN ST_NAME LIKE '%밍%' THEN '밍밍씨'
	WHEN ST_NAME='묭' THEN  '묭묭이'
    	ELSE '먕먕이'
       END
FROM STUDENT;

위에서부터 순차적으로 계산하여 내려옴. BETWEEN 범위의 중간을 다른 값으로 할당할 경우,

그 다른 값을 위쪽에 써주어 코드를 더 간결하고 명확하게 쓸 수 있다.

SELECT CASE WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 3 AND 5 THEN '봄'
	WHEN EXTRACT(MONTH FROM SYSDATE)=7 THEN '7월'
	WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 6 AND 8 THEN '여름'
	WHEN EXTRACT(MONTH FROM SYSDATE) BETWEEN 9 AND 11 THEN '가을'
	WHEN EXTRACT(MONTH FROM SYSDATE) IN (12,1,2) THEN '겨울'
    END
FROM DUAL;

---

2. 그룹 함수

조건이 없다면 그룹 함수의 값은 단일행으로 나온다.

---

SUM(도메인) : 총 합계

AVG(도메인) : 평균

MIN(도메인) : 최솟값

MAX(도메인) : 최댓값

COUNT(도메인) : 도메인값이 null을 제외한 튜플의 수

---

GROUP BY : 해당 컬럼에 해당하는 중복 값들을 그룹으로 묶어주어, 그룹함수의 결과를 다중행으로 나눈다.

HAVING : GROUP BY의 조건을 제시. GROUP BY보다 WHERE의 연산 순서가 먼저.

SELECT GRADE, SUM(MATH)
FROM STUDENT
GROUP BY GRADE
HAVING GENDER='F';

GROUP BY를 1조건, 2조건 등 여러 도메인으로 그룹하는 경우, GROUP BY COLUMN1, COLUMN2로 나눈다.

HAVING에도 서브쿼리 사용 가능

 

---

※실행 순서 

FROM/JOIN -> WHERE -> GROUP BY/HAVING -> SELECT -> ORDER BY

---

내일은 조인과 집합연산자, 모레는 서브쿼리 정리!(예정)