[ORACLE] 집합연산자 / 단일행함수 / 문자함수 / 숫자 함수 / 날짜 함수 / 형변환 함수

 

※ 집합 연산자

 

 집합 연산자 사용시 주의사항  

  1) 컬럼의 개수가 일치해야 함
  2) 컬럼의 자료형이 일치해야 함
       (컬럼명은 달라도 상관 없음)

 

  ▷ union 

  - 두 집합을 더해서 결과를 출력, 중복제거, 정렬해줌 (합집합)

select id1, name1 from set1
union
select id2, name2 from set2;

  ▷ union all 

  - 두 집합을 더해서 결과를 출력, 중복제거하지 않고, 정렬해주지 않음

select id1, name1 from set1
union all
select id2, name2 from set2;

  ▷ intersect 

   - 두 집합의 교집합 결과를 출력, 정렬해줌 (교집합 : 공통부분)

select id1,name1 from set1
intersect
select id2, name2 from set2;

  ▷ minus 

   - 두 집합의 차집합 결과를 출력, 정렬해줌 (차집합 : 서로에게 속하지 않는 값) 

select id1, name1 from set1
minus
select id2 , name2 from set2;

---

※ 단일행함수

 

단일 행 함수

- 데이터가 여러 건 존재하지만 단일 행 함수에 들어가는 데이터는 한번에 한 개
- 여러 건의 데이터를 한꺼번에 처리하는 것이 아니라 한번에 하나씩 처리하는 함수

 

select ename, initcap(ename), job, length(job), sal
from emp;

 

▶ 단일행 함수의 종류

  - 입력되는 데이터의 종류에 따라 구분됨

[1] 문자함수 - 입력되는 값(매개변수)이 문자인 함수

 

  ▷ initcap() 

     - 영문 첫글자만 대문자로 바꾼다

      - 공백 뒤의 문자도 대문자로 바꿔줌

 

  ▷ upper() 

     - 대문자로 변환해줌

 

  ▷ lower() 

  - 소문자로 변환

select id, initcap(id), upper(id), lower(id)
from student;

  = > id 컬럼의 자료를 첫글자 대문자로 / 대문자로 / 소문자로 변환하여 출력해라 

 

 

  ▷ length() 

   - 문자열의 길이를 리턴해주는 함수

 

  ▷ lengthb() 

   - 문자열의 바이트수를 리턴함(한글 1글자는 2바이트나 3바이트로 처리)

   - oracle express 버전은 한글 1글자가 3바이트로 설정되어 있다

select
 length(name), lengthb(name) from student;

  = > name 컬럼의 문자열길이 / 문자열의 바이트 수가 출력됨

 

  ▷ concat('','') 

 -  두 문자열을 연결해 주는 함수

  - 3개 이상의 문자열을 연결하려면 || 연산자 이용

select concat(name,position)as "concat 이용"
from professor;

 

  ▷ ★substr() 

   - 문자열에서 특정 길이의 문자열을 추출할 때 사용

     시작위치를 - 로 하면 뒤에서부터 자리수를 계산함

substr('문자열', 시작위치, 추출할 글자개수)
select substr('abcdefghi',2,3),   // 2번째 위치에서 3개 추출
 substr('abcdefghi',6),          // 2번째 위치에서 끝까지 추출
 substr('abcdefghi',-5,2)        // 뒤에서 5번째 위치에서 2개 추출 
 from dual;

 

 

 

 

2022.06.28 프로젝트하다가 작성한 쿼리

 - @ 와 . 사이의 값만 가져오기

 - 뒤지게 검색해도 없더라 삼십분은 걸린듯

select email
	,count(*) as count
    ,substr(email,1,instr(email,'@')-1)as emailId,substr(substr(email,instr(email,'@' )+1),1,instr(substr(email,instr(email,'@' )+1),'.')-1) as domain
from users
where email = 'sss@test.com'
group by email;

 

 

 

 

 

 

 

  ▷ ★instr() 

  - 주어진 문자열이나 컬럼에서 특정 글자의 위치를 찾아주는 함수

  - java indexof 와 유사

  - 다시한번 말하지만 몇번째의 기본값은 1

instr('문자열', '찾는 글자')
instr('문자열', '찾는 글자', 시작위치, 몇번째인지)

select 'A*B*C',           //찾을 문자열
instr('A*B*C', '*'),     //앞에서부터 체일 처음 만나는 *의 위치
instr('A*B*C', '*',3),    //3번째 위치 이후에 처음 만나는 *의 위치
instr('A*B*C','*',-2,-2),  // 뒤에서 2번째 위치 이후 2번째로 만나는 *의 위치
from dual;

 

  ▷ lpad(), rpad() 

  - 문자열의 남은 자릿수를 채울 문자로 채운다, 왼쪽/오른쪽 부터 채워줌

lpad('문자열' 또는 컬럼명, 자릿수, '채울문자')
select lpad(id,10,'$') from student;

  => student 테이블에서 학생들의 id 를 총 10자리로 출력하되 왼쪽 빈자리는 $ 기호로 채워라

 

  ▷ ltrim(), rtrim() 

 - 오른쪽에서 해당 문자를 제거한다

 - 제거할 문자를 생략하면 공백을 제거한다 ltrim(문자열) 형식으로만적으면 공백을 제거한다는 뜻

trim('문자열' 또는 컬럼명, '제거할 문자')

 

  ▷ reverse () 

- 어떤 문자열을 거꾸로 보여주는 것

- 한글을 reverse 하면 에러남

select reverse('oracle')from dual;

 

  ▷ replace() 

첫번째 문자열에서 문자1이 있으면 문자2로 바꾸어 출력하는 함수

replace('문자열' 또는 컬럼명, '문자1','문자2')

 

 

[2] 숫자 함수 

 

  ▷ round() 

    -  정수로 반올림(소수이하 첫째자리에서 반올림)
   

    1 : 소수이하 1자리만 남긴다(소수이하 2째자리에서 반올림)
    2 : 소수이하 2자리만 남긴다(소수이하 3째자리에서 반올림)
    -1 : 1의 자리에서 반올림(자릿수가 음수인 경우에는 소수 이상에서 처리)
    -2 : 10의 자리에서 반올림
    -3 : 100의 자리에서 반올림

 

round(숫자, 원하는 자릿수)
select round(12345.457,2) from dual; //12345.46

  = > 소수이하 2자리만 남긴다(소수이하 3째자리에서 반올림) 

 

  ▷ trunc() 

- 버림

trunc(숫자, 원하는 자릿수)
select trunc(12345.457) from dual;

 

mod(숫자, 나누는 수) - 나머지를 구하는 함수
ceil(소수점이 있는 실수) - 올림(주어진 숫자와 가장 근접한 큰 정수 출력)
floor(실수) - 내림(가장 근접한 작은 정수)
power(숫자1, 숫자2) - 숫자1의 숫자2승

select mod(13,3), ceil(12.3), floor(17.85),power(3,4) from dual;

[3] 날짜 함수

  ▷ sysdate  : 현재일자를 리턴하는 함수

select sysdate from dual; //2022-03-17 22:13:12

현재일자가 yyyy-mm-dd hh24:mi:ss 형태로 출력됨

 

 

  ▷ sysdate 만 이용해서 날짜 더하고 빼보기

  오늘부터 100일 후 (날짜에서만 더하고 뺄수있음)

 - 더하고 빼는 기준은 일수

select sysdate + 100 from dual;

 

 

  ▷ 2일 1시간 5분 10초 후 날짜 구하기

select sysdate +  2+   (1/24)+  (5/(24*60))+   10/(24*60*60) // 일 시 분 초

일

시간 = 일 / 24 시간

분 = 24시간 * 60분

초 = 24시간 * 60분 * 60초 

 

  ▷ add_months() 

   - 몇 개월후, 몇 개월전에 해당하는 날짜를 구할 수 있다

add_months(날짜, 개월수)  // 해당날짜로부터 개월수만큼 더하거나 뺀 날짜를 구한다
select add_months(sysdate,3) from dual; // 3달후

 

  ▷ 2년 4개월 1일 3시간 10분 20초 후의 날짜 구하기

select add_months (sysdate,28) +1+  (3/24)+  10/(24*60)+  20/(24*60*60)  // 개월,일,시,분,초
from dual;

 

  ▷ to_yminterval() 

  - ym 몇년몇개월 후 (year month)

  ▷ to_dsinterval() 

  - 일,시,분,초 지정해서 간격을 구할 수 있음

 

== > 두개를 사용하면 n년 n개월 n일 n시 n분 n초 전의 일시를 구할 수 있다

 

7년 3개월 5일 2시간 30분 15초 전

select sysdate - to_yminterval('07-05') -  to_dsinterval('5 02:30:15')
from dual;

= > 현재일자에서 7년 5개월 전 5일 2시간 30분 15초 전 을 구해라

 

  ▷ to date(문자) 

  - 문자를 날짜형태로 변환해주는 함수

  - sysdate 로 오늘 날짜를 불러오는 것이 아니라

    특정 날짜에서 특정 날짜까지의 간격을 알고싶다면 문자열 -> 날짜형으로 형변환을 해줘야 한다

select to_date ('2022-03-17') - to_date('2022-03-14') from dual;

 

  ▷ trunc(날짜) 

  시간을 제외한 두 날짜 사이의 일수를 구함

  함수를 이용하면 시분초 버림, 제거하고 해당 날짜를 리턴해줌

  두 날짜사이 일 수를 구할때 이 함수를 사용하면된당

select to_date ('2022-03-18') -trunc(sysdate) from dual;

 

 

  ▷ months_between() 

두 날짜 사이의 개월수를 구해줌

select months_between('2022-05-10','2022-01-17')from dual;

 

  ▷ next_day() 

주어진 날짜를 기준으로 돌아오는 가장 최근 요일의 날짜를 리턴해주는 함수
요일명 대신 숫자를 입력할 수도 있다.

1:일, 2:월, 3:화 ... 7:토

select next_day(sysdate,'월'),     // 돌아오는 월요일 
next_day(sysdate,'화요일'),        // 돌아오는 화요일 
next_day(sysdate,1),              // 돌아오는 일요일 (1=일) 
next_day('2022-05-01','금')       // 5월 1일때 돌아오는 금요일
from dual;

 

  ▷ last_day() 

주어진 날짜가 속한 달의 가장 마지막 날을 리턴해주는 함수

select last_day('2021-06-15') from dual; // 2021-06-30 00:00:00

  ▷ round()

정오(낮 12시)기준으로 그 이전에는 오늘 날짜를 리턴하고, 그 이후에는 그 다음 날짜를 리턴함

  ▷ trunc()

무조건 오늘 날짜를 리턴, 시간은 제외됨

  - round, trunc 모두 시분초는 0으로 초기화되어 출력됨 (00:00:00)

select sysdate, round(sysdate), trunc(sysdate) from dual;

[4] 형변환 함수

▶ 오라클의 자료형

문자 - char(고정길이형), varchar2(가변길이형)
숫자 - number
날짜 - date

 

형변환

1) 자동형변환

select 1+'2',
2+'003'from dual;

=> 숫자형태의 문자를 연산하면 해당 문자를 자동형변환한 후 연산함

2) 명시적 형변환
 ▷ to_char()

 - 숫자, 날짜를 문자로 변환

       - 날짜를 패턴이 적용된 문자로 변환한다.

to_char(날짜, 패턴)
to_char(sysdate,'mm')

'mm'

'dd'

'd' = 요일 : 1:일 2:월, ...7:토
'year'

'mon'

'month'

'ddd' = 1년중 며칠인지

'day' = 요일을 한글로 : 월요일, 화요일

'dy' = 요일을 한글로 :  월,화,수

'q' = 분기

'yyyy-mm-dd'      ||      'yyyy-mm-dd hh24:mi:ss'     ||      'yyyy-mm-dd hh:ss am d'       ||     

'yyyy-mm-dd hh::mi:ss pm day'     등의 형태로 사용할 수 있다

 

  ▷ to_date() - 문자를 날짜로 변환

  ▷ to_number() - 문자를 숫자로 변환

select 1+to_number('2') from dual;

  ▷ extract()함수
 - 날짜 유형의 데이터로부터 날짜 정보를 분리하여 새로운 컬럼의 형태로 추출해 주는 함수

select extract('날짜요소' from 컬럼X) as 별칭
from 테이블A;