[오라클] 집합 set 과 함수 function -1

집합 set

select 로 질의된 결과를 하나의 집합으로 인식해서 집합 연산 하는 것

집합의 종류

합집합 union

교집합 intersect

차집합 minus

 

교집합

select employee_id, first_name
from employees
where employee_id in (145,147,158)

intersect

select employee_id, first_name
from employees
where first_name like 'A%';

 

차집합 (순서 중요)

[Q] employee_id 145,147, 158 중에서 A로 시작하는 직원 제외

select employee_id, first_name
from employees
where employee_id in (145,147,158)

minus

select employee_id, first_name
from employees
where first_name like 'A%';

 

[Q] A 로 시작하는 직원 중에서  145, 147, 158 의 employee_id  직원 제외

select employee_id, first_name
from employees
where first_name like 'A%'

minus

select employee_id, first_name
from employees
where employee_id in (145,147,158);

 

 

합집합

- 중복 데이터는 1개만 출력

select employee_id, first_name
from employees
where first_name like 'A%'

union

select employee_id, first_name
from employees
where employee_id in (145,147,158);

 

[Q] 합집합 중복된 데이터도 무조건 다 조회되도록

select employee_id, first_name
from employees
where first_name like 'A%'

union all

select employee_id, first_name
from employees
where employee_id in (145,147,158)

order by first_name;
-- 어떤 명령이든 항상 마지막에 ~!

 

집합에서 오류가 난 경우

1) 컬럼의 개수가 다르면 다른 집합으로 인지되므로 오류

select employee_id, first_name,salary
from employees
where first_name like 'A%'

union all

select employee_id, first_name
from employees
where employee_id in (145,147,158);

 

2) 컬럼의 개수가 같지만 타입이 다르면 오류

select employee_id, first_name,hire_date
from employees
where first_name like 'A%'

union all

select employee_id, first_name, salary
from employees
where employee_id in (145,147,158);

 

3) 실행 오류가 아니라 논리적인 오류 (타입과 개수는 맞췄지만 컬럼이 다른 경우)

: 결과는 나오지만 원하는 결과가 나오지는 않는다.

select employee_id, first_name,last_name
from employees
where first_name like 'A%'

union

select employee_id, first_name, phone_number
from employees
where employee_id in (145,147,158);

 

함수 function

질의문과 함께 사용하여 결과를 강력하게 처리할 수 있는 기능

함수의 종류

종류	의미
단일행 함수	각각의 레코드에 기능을 적용시키는 함수
그룹행 함수	특정 조건에 맞게 레코드를 묶어서 기능을 적용

 

단일행 함수의 종류

단일행 함수	역할
lower(문자열)	영문자를 소문자로 변경
upper(문자열)	영문자를 대문자로 변경
initcap(문자열)	영문자를 첫글자만 대문자, 나머지 소문자로 변경
concat(문자열1, 문자열2)	문자열 결합
substr(문자열, 위치값)	문자열에서 지정된 위치 뒤의 모든 문자열을 반환
substr(문자열, 위치1,개수)	문자열에서 지정된 위치1 ~ 개수 까지의 모든 문자열을 반환, 인덱스가 1부터 시작
length(문자열)	길이 반환
instr(문자열, 부분문자열)	문자열 내에서 부분 문자열의 위치
replace(문자열, 찾는 문자열, 바꿀 문자열)	문자열 치환

 

[Q] 이름을 소문자 / 대문자 로 조회

select lower(first_name), upper(first_name)
from employees;

 

더미 테이블 dual

- from은 필수 구문이기 때문에 table 이 존재하지 않는 데이터에 가상으로 쓰인다.

select lower('ASIAN CUP 2024'), initcap('ASIAN CUP 2024')
from dual;

 

substr(문자열, 위치) 

index가 1부터 시작

select upper(substr ('The Soap', 5))
from dual;

 

substr(문자열, 위치, 개수)

select upper(substr ('The Soap', 5, 2)) as RESULT
from dual;

 

LENGTH() : 길이 반환

select LENGTH ('The Soap')
from dual;

 

INSTR(문자열, 부분문자열): 위치값 찾기

위치 값을 찾지 못 할 경우 0 반환

select INSTR ('오라클 마스터','터')
FROM DUAL;

 

[참고] 다른 문장과 함께 사용

SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES;

SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE SUBSTR(FIRST_NAME,-1,1) = 'n';

 

[Q] 이름의 끝에서 2번째 2 개가 'en' 으로 끝나는 직원의 사원번호와 이름

SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE SUBSTR(FIRST_NAME,-2,2) = 'en';

 

CONCAT() : 두 개의 문자열을 합치는 함수 (|| 와 동일)

SELECT CONCAT('I HAVE A ', 'DREAM')
FROM DUAL;

 

[Q] CONCAT()을 테이블에 적용

SELECT 
  CONCAT(EMPLOYEE_ID, FIRST_NAME),
    CONCAT(EMPLOYEE_ID, CONCAT(':', FIRST_NAME))
FROM EMPLOYEES;


SELECT 
  CONCAT(EMPLOYEE_ID, CONCAT(' ', FIRST_NAME)),
    CONCAT(EMPLOYEE_ID, CONCAT(':', FIRST_NAME))
FROM EMPLOYEES;

 

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

SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES;

 

[Q] 입사일의 날짜 '/'를 '-' 로 변환

SELECT FIRST_NAME, REPLACE(HIRE_DATE,'/','-')
FROM EMPLOYEES;

 

[Q] 이름, 전화 번호를 조회하되, 전화번호의 '.' 을 공백으로 치환하시오

SELECT FIRST_NAME, PHONE_NUMBER
FROM EMPLOYEES;


SELECT FIRST_NAME, REPLACE(PHONE_NUMBER,'.',' ')
FROM EMPLOYEES;

 

숫자와 관련된 함수

종류	의미
abs(숫자)	숫자의 절대값 구하는 함수
ceil(숫자)	올림. 가장 가까운 양의 방향의 정수를 반환
floor(숫자)	버림. 가장 가까운 음의 방향의 정수를 반환
round(숫자,자릿수)	지정한 위치에서 반올림
trunc(숫자, 자릿수)	지정한 위치에서 절삭
mod(값 1, 값 2)	첫번째 숫자를 두 번째 숫자로 나눴을때 나머지
power(값1, 값 2)	첫번째 숫자를 두 번째 숫자만큼 곱한 값
sign(숫자)	숫자가 음수이면 -1, 0이면 0, 양수이면 1을 반환

 

abs()

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

 

ceil()

SELECT CEIL(15.3) , CEIL(-15.3) FROM DUAL;

 

floor()

SELECT FLOOR(15.7), FLOOR(-15.7) FROM DUAL;

 

round(m,n)

- '-1' 은 소수점 바깥자리
- 방향이랑 상관 없다.

SELECT ROUND(15.195, 1), ROUND(15.195, 2), ROUND(15.195, 0), ROUND(15.195, -1)
FROM DUAL;  
-- 소숫점 이하 첫째자리까지 살리겠다


SELECT ROUND(- 15.195, 1), ROUND(-15.195, 2), ROUND(-15.195, 0), ROUND(-15.195, -1)
FROM DUAL;

 

trunc(m,n)

SELECT TRUNC(- 15.195, 1), TRUNC(-15.195, 2), TRUNC(-15.195, 0), TRUNC(-15.195, -1)
FROM DUAL;

 

mod(m,n)

SELECT MOD(10,3), POWER(10, 3), SIGN(10), SIGN(-10)
FROM DUAL;