국비 2일차, [오라클] SQL WHERE(비교연산자 BETWEEN, IN, IS NULL, LIKE,논리 연산자), 정렬, 함수(INITCAP, LOWER, UPPER, CONCAT, LENGTH, INSTR, LPAD, RPAD, SUBSTR, REPLACE)

2021.01.06 국비교육 2일차

WHERE

비교 연산자 part 2

연산자	의미
BETWEEN AND	두 값이 범위에 포함되는
IN(set)	괄호 안의 값과 일치하는
LIKE	문자의 조합이 같은
IS NULL	널 값

BETWEEN A AND B

SELECT EMPNO, ENAME, SAL FROM EMP 
WHERE SAL BETWEEN 1000 AND 2000; -- SAL이 1000에서 2000사이인 사원정보

SELECT EMPNO, ENAME, SAL FROM EMP 
WHERE HIREDATE BETWEEN '81/01/01' AND '81/12/12';
-- 81년도에 입사한 사원의 정보

• BETWEEN A AND B에서 A값과 B값은 범위에 포함이 된다.

IN

SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO IN(7838, 7844, 7876);
-- 1.사원번호가 IN안의 값과 같다면 출력

SELECT ENAME FROM EMP WHERE SAL IN(1250, 5000);
-- 2.월급이 1250, 5000인 사원 출력

SELECT ENAME FROM EMP WHERE SAL = 1250 or SAL = 5000;
-- 2번 명령문과 출력이 같다

SELECT ENAME, JOB, DEPTNO FROM EMP
WHERE(JOB, DEPTNO) IN (('MANAGER',20),('CLERK', 30));
-- 다중리스트 활용을 통해 직업과 사원번호 조건을 함께 걸어 출력

SELECT DEPTNO FROM EMP
WHERE HIREDATE IN('81/04/02', '81/09/08', '81,12,03');
--입사일이 81/04/02, 81/09/08, 81/12/03인 사원번호 출력

SELECT * FROM EMP WHERE NVL(COMM, 1) IN (1);

IS NULL

SELECT COMM FROM EMP WHERE COMM = NULL;
-- 널 값은 비교연산자 불가

SELECT COMM FROM EMP WHERE COMM IS NULL;
--COMM 값이 NULL인 로우 출력

SELECT COMM FROM EMP WHERE COMM IS NOT NULL;
--COMM 값이 NULL이 아닌 로우 출력

LIKE

• 게시판같은 곳에서 검색시에 주로 LIKE 쿼리를 사용한다.

와일드 카드 문자	설명
%	0 글자 이상의 임의 문자를 대표한다
_	1 글자의 임의 문자를 대표한다.

SELECT EMPNO, ENAME, JOB FROM EMP WHERE ENAME LIKE 'A%'
-- 대문자 A로 시작하는 모든 사원의 정보 출력

SELECT EMPNO, ENAME, JOB FROM EMP WHERE ENAME LIKE '%S'
-- 대문자 S로 끝나는 모든 사원의 정보 출력

SELECT EMPNO, ENAME, JOB FROM EMP WHERE ENAME LIKE '%A%'
-- 대문자 A가 포함된 모든 사원의 정보 출력

SELECT EMPNO, ENAME, JOB FROM EMP WHERE ENAME LIKE '_L%'
-- 이름의 두번째 글자가 L인 사원 출력

• ESCAPE 옵션
  • 검색하고자 하는 문자에 패턴 매칭연산자가 포함되어 있는 경우에 사용
  • 이스케이프 뒤의 단 한글자만 일반 글자로 인식

SELECT EMPNO, ENAME, JOB FROM EMP
WHERE ENAME LIKE '%$_%' ESCAPE '$';
-- 언더바(_)가 포함된 정보 출력 // $는 이스케이프 이외에 아무 의미 없음

SELECT DATA FROM TABLE
WHERE NUM LIKE '%70$%%' ESCAPE '$';
-- 70%가 포함된 문자열 출력

? 이스케이프 활용

논리 연산자

조건A	조건B	A and B	A or B
True	True	True	True
True	False	False	True
False	True	False	True
False	False	False	False

SELECT EMPNO, ENAME, JOB, SAL FROM EMP
WHERE JOB = 'SALESMAN' AND SAL >= 1500;
-- 직업이 'SALESMAN' 이면서 월급이 1500 이상인 사원 출력 (동시 만족)

SELECT EMPNO, ENAME, JOB, SAL FROM EMP
WHERE JOB = 'SALESMAN' or SAL >= 1500;
-- 직업이 'SLAESMAN' 이거나 월급이 1500 이상인 사원 출력(둘 중 하나라도 만족)

SELECT EMPNO, ENAME, JOB, SAL FROM EMP
WHERE (JOB, SAL) IN(('SALESMAN', > 1500));
-- IN에서는 비교연산이 안되기 때문에 사용 목적을 잘 확인하자

SELECT SAL FROM EMP WHERE NOT SAL <= 3000;
-- 월급이 3000 이하가 아닌 사람 출력 = 월급이 3000보다 높은사람 출력

SELECT DISTINCT JOB FROM EMP WHERE NOT JOB ='SALESMAN';
--SALESMAN을 제외한 직업 출력

SELECT EMPNO, ENAME, JOB FROM EMP WHERE SAL > 1000 AND SAL < 3000;
-- 1000 < SAL < 3000인 사원 정보 출력

SELECT * FROM EMP WHERE SAL NOT IN(800, 900);
-- SAL 값이 800, 900이 아닌 데이터 출력

SELECT * FROm EMP WHERE ENAME NOT LIKE 'J%';
-- 이름이 J로 시작하지 않는 사원 출력

SELECT EMPNO FROM EMP WHERE SAL NOT BETWEEN 1000 AND 2000;
-- 월급이 1000에서 2000 사이가 아닌 사원번호 출력

SELECT * FROM EMP WHERE HIREDATE NOT BETWEEN '81/01/01' AND '81/12/31';
-- 81년도에 입사하지 않은 사원

SELECT ENAME, MGR FROM EMP WHERE MGR IS NOT NULL;
-- MGR이 있는 사원의 이름과 MGR 번호 출력

연산자 우선순위	설명
1	괄호( )
2	NOT 연산자
3	비교 연산자
4	AND
5	OR

SELECT ENAME, JOB, SAL, COMM FROM EMP
WHERE JOB = 'CLERK' OR JOB = 'ANALYST'
AND COMM IS NULL
AND SAL >= 1000
AND SAL <= 3000;

-- 검색순서 COMM 확인, SAL 확인, JOB 확인

정렬

• ORDER BY 정렬 기본은 ASC
• 기본식 ORDER BY COLUMN [ASC or DESC]
• 오라클은 NULL 값을 가장 큰 값으로 간주한다.
• ORDER BY 뒤 COLUMN자리에 숫자 사용 가능

SELECT * FROM EMP ORDER BY EMPNO ASC;
-- 사원 정보를 오름차순(ASC)로 정리(ORDER BY)

SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC;
--이름과 월급을 월급을 많이 받는 순서대로(= 높은 값부터) 정렬

SELECT EMPNO, ENAME, HIREDATE FROM EMP ORDER BY HIREDATE DESC;
--사번, 이름, 입사일을 입사일이 최근인 사람순으로정렬

SELECT EMPNO, ENAME, HIREDATE FROM EMP ORDER BY 3 DESC;
-- ORDER BY 뒤에 컬럼명 혹은 SELECT 컬럼 번호(1 ,2,3 ...) 사용 가능

SELECT ENAME, DEPTNO, SAL "월급" FROM EMP ORDER BY SAL --ASC(생략가능);
-- 이름, 부서번호, SAL(월급 별칭부여)를 월급이 낮은순(ASC)으로 정렬

SELECT ENAME, SAL, SAL *12 "연봉" FROM EMP ORDER BY 3 DESC;
-- 연봉이 높은순으로 배열

SELECT ENAME, DEPTNO, SAL "월급" FROM EMP ORDER BY 2,3;
--ORDER BY에 칼럼을 두개 입력하면 순서대로 정렬 이루어짐

함수

SQL 함수의 특징

• 데이터 계산 수행
• 개별적인 데이터 항목 수정 가능
• 결과 조작 가능

함수의 종류

• 단일 행 함수

  모든 행에 대해서 각각 적용되어 행의 개수와 동일한 개수의 결과를 리턴

• 다중 행 함수( 그룹 함수)

  검색되는 모든 행에 대해서 한번만 적용되고 한건의결과만을 리턴

INITCAP

• 각 단어의 첫 문자를 대문자로, 나머지는 소문자로 변경

  ex) INICAP('ORACLE SERVER') ---> Oracle Server

SELECT INITCAP(DNAME) FROM DEPT;
--DEPT에 있는 DNAME 이름 형태 변경

LOWER

• 대소문자가 혼합되어 있거나 대문자인 문자열을 소문자로변경

SELECT EMPNO, DEPTNO, LOWER(JOB) FROM EMP;
--사원의 직업을 소문자로 출력

SELECT EMPNO, DEPTNO, JOB FROM EMP WHERE LOWER(ENAME) = 'king';
--문자열의 대소문자를 구별하지 않기 위해 조건에서 소문자로 바꿔버림

UPPER

• 대문자가 혼합되어 있거나 소문자인 문자열을 대문자로 변경

SELECT EMPNO, DEPTNO, JOB FROM EMP WHERE ENAME = UPPER('king');
-- LOWER때와 달리 문자열 부분에 함수를 적용시켜 검색

CONCAT

• 두 개의 문자열을 합성합니다.(= ||)
• 두 개의 매개변수만 사용 가능
• Concatenation의 약자

SELECT EMPNO, ENAME,JOB, CONCAT(ENAME, JOB) FROM EMP WHERE EMPNO = '7369';

LENGTH

• 문자열의 길이를 반환하는 함수

SELECT LENGTH('ORACLE') FROM DUAL;
-- 'ORACLE'의 문자열 길이 출력 : 6  (공백 또한 포함된다.)

SELECT LENGTH(ENAME), ENAME FROM EMP ORDER BY 1 DESC, 2 ;
-- 사원의 이름, 이름의 길이를 이름이 긴 순서대로 출력, 알파벳 순으로 정렬

INSTR

• 표현식 INSTR(문자열, 검색값, m , n)
• 문자열에서 검색값이 m번 자리부터 n번째에 나오는지 출력

SELECT INSTR('MILLER', 'L',1,2) FROM DUAL;
-- MILLER라는 문자열에서 L이라는 글자가, 1번째자리(M)에서 시작하여 2번째로 나타나는 위치
-- 출력값은 4

?주로 어떤 상황에서 사용하는지

LPAD

• 왼쪽에 문자열을 끼워넣는 역할
• 표현식 LPAD(문자열, 문자열 길이수, 채워넣을 문자)

SELECT LPAD('MILLER', 10, '*') FROM DUAL;
-- 출력값 '****MILLER'

SELECT ENAME, LPAD(ENAME, 15, '*'), SAL, LPAD(SAL, 10, '*') FROM EMP 
WHERE DEPTNO = 10;

RPAD

• 오른쪽에 문자열을 끼워넣는 역할
• 표현식 RPAD(문자열, 문자열 길이수, 채워넣을 문자)

SELECT RPAD('MILLER', 10, '*') FROM DUAL;
-- 출력값 'MILLER****'

SUBSTR

• 중요함
• m번째 자리부터 길이가 n개인 문자열을 반환
• 주민번호 같은거에서 주로 사용
• SUBSTR(문자열, M(시작 자리), N(뽑아오는 개수))

SELECT SUBSTR('000101-123432' , 8 ,1) FROM DUAL;
--성별 확인하기 : 출력값 1

SELECT SUBSTR('000101-123432' ,8) FROM DUAL;
-- N값을 넣지않으면 M부터 문자열 끝까지 출력 : 예제 출력값 -> 123432

SELECT HIREDATE "입사 일", SUBSTR(HIREDATE, 1, 2) "입사 년도" FROM EMP;
-- 입사 일에서 앞의 두자리(년도)를 가져와 입사 년도 칼럼 생성 후 출력

SELECT RPAD(SUBSTR('000101-123432' , 1 ,8), 14, '*') "주민번호" FROM DUAL;
SELECT SUBSTR('000101-123432',1 , 8)||'*****' FROM DUAL;
-- 주민등록번호 뒷자리 *표시로 변환 출력값 : 000101-1*****

REPLACE

• 특정 문자열을 치환하는 함수
• REPLACE(문자열, A, B) 문자열 A를 B로 치환

SELECT REPLACE('JACK and JUE', 'J', 'BL') FROM DUAL;
-- JACK and JUE에서 J를 BL로 치환하여 출력

SELECT REPLACE(SUBSTR('000101-123432',1,9), 2, '*****') FROM DUAL; 
-- 2가 또있으면 이렇게 안됨 주의
SELECT REPLACE('000101-123432', SUBSTR('000101-123432',9), '*****') "주민 번호" FROM DUAL; 
--주민번호 SUBSTR자리 추출 후 REPLACE로 * 변경