25일차: Oracle - 연산자, SQL함수 - 2021.07.19

목차

1.     연산자와 정렬

2.     SQL 주요 함수

 

1. 연산자와 정렬                             → ★2021_07_19_01 참고

 

1) IN 연산자

• 논리 연산자 or를 사용한 복잡한 쿼리문을 대신하여 사용 가능

• 형식: 컬럼명 in(A, B, C)

• ex) Q. 커미션이 300이거나 500이거나 1400인 사원을 검색하는 SQL문 작성

       select * from emp where comm in(300, 500, 1400);

      (= select * from emp where comm=300 or comm=500 or comm=1400;)

 

2) LIKE 연산자와 와일드카드

• LIKE 연산자는 검색하고자 하는 값을 정확히 모를 경우에 와일드카드와 함께 사용하여 원하는 내용을 검색하도록 함

• 형식: 컬럼명 like pattern

• 와일드 카드:

① % : 문자가 없거나, 하나 이상의 문자에 어떤 값이 와도 상관 없음

② _ : 하나의 문자에 어떤 값이 와도 상관 없음

 

① % 와일드 카드

ex) Q. 사원테이블에서 사원명이 대문자 F로 시작하는 사원을 검색하는 SQL문을 작성

     select * from emp where ename like 'F%';

 

     Q. 사원테이블에서 사원명이 대문자 N으로 끝나는 사원을 검색하는 SQL문을 작성

     select * from emp where ename like '%N';

 

     Q. 사원테이블에서 사원명이 대문자 A를 포함하는 사원을 검색하는 SQL문을 작성

     select * from emp where ename like '%A%';

 

② _ 와일드 카드

ex) Q. 사원 이름의 두번째 글자가 A인 사원을 검색하는 SQL문 작성

     select * from emp where ename like '_A%';

 

     Q. 사원 이름의 세번째 글자가 A인 사원을 검색하는 SQL문 작성

     select * from emp where ename like '__A%';

 

     Q. 사원 이름이 끝에서 2번째 글자가 E인 사원을 검색하는 SQL문 작성

     select * from emp where ename like '%E_';

 

     Q. 사원명에 A가 포함되어 있지 않은 사원을 검색하는 SQL문 작성

     select * from emp where ename not like '%A%';

 

3) NULL 값 검색

- EMP 테이블 : MGR컬럼, COMM컬럼

- ex) Q. MGR 컬럼에서 null 값인 데이터를 검색

       select ename, job, mgr from emp where mgr is null;

       select ename, job, mgr from emp where mgr = null;     → 검색 안 됨

       select ename, job, mgr from emp where mgr = ' ';       → 검색 안 됨

 

       Q. MGR 컬럼에서 null 값이 아닌 데이터를 검색

       select ename, job, mgr from emp where mgr is not null;

 

4) 정렬을 위한 ORDER BY절

- 정렬: 크기 순서대로 나열하는 것을 의미

- 오름차순(ascending) 정렬 방식: 작은 값 → 큰 값

- 내림차순(descending) 정렬 방식: 큰 값 → 작은 값

 

 

① 숫자 데이터 정렬

Q. 사원테이블에서 급여를 기준으로 오름차순 정렬

select * from emp order by sal asc;

select * from emp order by sal;     → asc 생략 가능(기본 정렬 방식이 오름차순이기 때문

 

Q. 사원 테이블에서 급여를 기준으로 내림차순 정렬

select * from emp order by sal desc; → 정렬방식(asc) 생략 가능

 

② 문자 데이터 정렬

Q. 사원 테이블에서 사원명을 기준으로 오름차순 정렬

select * from emp order by ename asc;

select * from emp order by ename;   → asc 생략 가능

 

Q. 사원 테이블에서 사원명을 기준으로 내림차순 정렬

select * from emp order by ename desc;

 

③ 날짜 데이터 정렬

Q. 사원 테이블에서 입사일을 기준으로 오름차순 정렬(과거 날짜순 정렬)

select hiredate from emp order by hiredate asc;

 

Q. 사원 테이블에서 입사일을 기준으로 내림차순 정렬(최근 날짜순 정렬)

select hiredate from emp order by hiredate desc;

 

④ NULL 값을 포함하는 데이터 정렬

Q. 사원 테이블에서 MGR 컬럼을 기준으로 오름차순 정렬

select mgr from emp order by mgr asc;   → null 값이 가장 마지막에 출력

 

Q. 사원 테이블에서 MGR 컬럼을 기준으로 내림차순 정렬

select mgr from emp order by mgr desc;  → null 값이 가장 먼저 출력

 

⑤ 여러 번 정렬하기

- 한 번 정렬했을 때 동일한 결과가 나오는 데이터가 있을 경우에 한 번 더 정렬

- 두 번째 정렬 조건은 첫 번째 정렬에서 동일한 결과가 나온 데이터에 한해 적용됨

- 댓글 게시판을 만들 경우에 주로 사용

 

Q. 사원 테이블에서 급여를 기준으로 내림차순 정렬을 한다. 이때, 동일한 급여를 받는 사원들은 사원명을 기준으로 오름차순 정렬해서 출력하는 SQL문 작성

select ename, sal from emp order by sal desc;    → 3000(2명), 1250(2명)

select ename, sal from emp order by sal desc, ename asc;

 

2. SQL 주요 함수                            → ★2021_07_19_02 참고

 

1) DUAL 테이블

 

- 계정 소유의 테이블로 공개 동의어로 설정되어 있음

- dual 테이블은 공개가 되어있기 때문에 누구나 사용 가능

- dual 테이블은 데이터가 1개 밖에 없기 때문에, 연산 결과를 1번만 출력

- desc dual;

- select * from dual;            → X 데이터 1개 존재

- select 10+20 from dual;     → 1개 출력

- select 10+20 from sys.dual;

 

2) 숫자 함수

① abs()

- 절대값을 구해주는 함수

ex) select -10, abs(-10), ABS(-20) from dual;            → 함수명은 대소문자를 구분하지 않음

 

② floor()

- 소수점 이하를 버리는 함수

ex) select 34.5678, floor(34.5678) from dual;

 

③ round()

- 특정 자리에서 반올림을 하는 역할

- round( 대상값, 자리수 )

ex) select 34.5678, round(34.5678) from dual;         → 35 출력 : 소수 첫번째 자리에서 반올림

     select 34.5678, round(34.5678, 2) from dual;      → 34.57 출력 : 3자리에서 반올림

 

- 자릿수에 음수를 지정하면, 소수점 왼쪽인 일단위, 십단위, 백단위 순으로 거슬러 올라가게 되고, 인자 값이 양수일 때와는 달리 해당 자리에서 반올림이 일어나게 됨

ex) select 34.5678, round(34.5678, -1) from dual;     → 30 출력

     select 34.5678, round(36.5678, -1) from dual;     → 40 출력

 

④ trunc()

- 지정한 자리 수 이하를 버린 결과를 구해주는 함수

ex) select trunc(34.5678, 2), trunc(34.5678, -1), trunc(34.5678) from dual;

→                     34.56                   30                   34

 

⑤ mod()

- 나머지를 구해주는 함수

ex) select mod(27, 2), mod(27, 5), mod(27, 7) from dual;

→                     1               2               6

 

3) 문자 함수

① upper()

- 대문자로 변환해주는 함수

ex) select 'Welcome to Oracle', upper('Welcome to Oracle') from dual;

 

② lower()

- 소문자로 변환해주는 함수

ex) select 'Welcome to Oracle', lower('Welcome to Oracle') from dual;

 

③ initcap()

- 첫 글자(이니셜)를 대문자로 변환해주는 함수

ex) select 'Welcome to Oracle', initcap('welcome to oracle') from dual;

 

④ length()

- 문자의 길이(글자수)를 구해주는 함수

ex) select length('Oracle'), length('오라클') from dual;

 

⑤ lengthb ()

- 문자열의 길이를 바이트로 구해주는 함수

- 영문 1글자: 1 byte, 한글 1글자: 3 byte (한글은 오라클의 버전에 따라 2 byte 또는 3 byte)

ex) select lengthb('Oracle'), lengthb('오라클') from dual;