합집합
union :테이블과 테이블의 데이터를 합치는 것 (중복값 배제)
union all : 합치는 데 (중복값 허용)
union 조건
1. 대응 되는 컬럼의 타입이 동일 해야한다.
위에 적히는 놈의 컬럼명을 따라감
select empno , ename from emp
union
select dname , deptno from dept;
--dname, empno가 타입이 일치하지 않아 실행 오류
--ORA-01790: expression must have same datatype as corresponding expression
select empno , ename from emp
union
select deptno , dname from dept;2. 대응 되는 컬럼의 갯수가 동일 해야한다.
select empno , ename , job ,sal from emp
union
select deptno , dname , loc from dept;
--컬럼의 갯수가 일치 하지 않음
--ORA-01789: query block has incorrect number of result columns
select empno , ename , job ,sal from emp
union
select deptno , dname , loc , null from dept;
--null로 채워준다
1) 문자형 함수 : 문자를 입력 받고 문자와 숫자 값 모두를 RETURN 할 수 있다.
| 함수명 | 예시 | 결과 |
| initcap | initcap('the super man') | The Super Man |
| lower | lower('AAA') | aaa |
| upper | upper('aaa') | AAA |
| length | length('abcd'), length('홍길동') | 4 , 3 |
| concat (2개의 매개변수) | concat('a','b') | ab |
| || (여러개의 매개변수) | 'a' || 'b' || 'c' | abc |
| substr(문자열 , 시작, 몇개) | substr('ABCDE',2,3) | BCD |
| ㄴ3번째부터 쭉나옴 | substr('dsfasdfgdsagdsagsdagasdfgva',3) | fasdfgdsagdsagsdagasdfgva |
| lpad(문자열,길이,반복문자) | lpad('ABC',10,'*') | *******ABC |
| rpad(문자열,길이,반복문자) | rpad(substr('hong1004',1,2),length('hong1004'),'*') | ho****** |
| rtrim | rtrim('MILLER','ER') | MILL |
| ltrim | ltrim('MILLLLLLLLLLLLLER','MIL') | ER |
| replace | replace(ename,'A','와우') | ALLEN -> 와우LLEN |
2) 숫자형 함수 : 숫자를 입력 받고 숫자를 RETURN 한다.
round , trunc, mod
| X | X | X | . | X | X | X |
| -3 | -2 | -1 | 0 | 1 | 2 | 3 |
round : 지정된 자리에서 반올림
trunc : 지정된 자리에서 절삭
mod : 나머지를 구함
나누기는 / 로 해결
--round
select round(12.345,0) as r from dual;
select round(12.567,0) as r from dual; --13
select round(12.345,1) as r from dual; --12.3
select round(12.567,1) as r from dual; --12.6
select round(12.345,-1) as r from dual; --10
select round(15.345,-1) as r from dual; --20
select round(15.345,-2) as r from dual; --0
---trunc
select trunc(12.345,0) as r from dual; --12
select trunc(12.567,0) as r from dual; --12
select trunc(12.345,1) as r from dual; --12.3
select trunc(12.567,1) as r from dual; --12.5
select trunc(12.345,-1) as r from dual; --10
select trunc(15.345,-1) as r from dual; --10
select trunc(15.345,-2) as r from dual; --0
--mod
select 12/10 from dual;
select mod(12,10) from dual;
select mod(0,0) from dual;
3) 날짜형 함수 : 날짜형에 대해 수행하고 숫자를 RETURN 하는 MONTHS_BETWEEN 함수를
제외하고 모두 날짜 데이터형의 값을 RETURN 한다.
계산 주의!!!
1. Date + Number >> Date
2. Date - Number >> Date
3. Date - Date >> Number
months_between(날짜 , 날짜) : 두 날짜 사이의 개월수를 구한다.
select months_between(sysdate,'2020-01-01') from dual;결과 : 42.50968301971326164874551971326164874552
소숫점아래는 절삭
select trunc(months_between(sysdate,'2020-01-01'),0) from dual;결과: 42
주의!
select '2023-01-01' + 100 from dual;
--ORA-01722: invalid number
--문자열과는 계산불가능하다
select to_date('2023-01-01') + 100 from dual;
--날짜 데이터로 변경해서 연산한다
4) 변환형 함수 : 어떤 데이터형의 값을 다른 데이터형으로 변환한다.
to_char() 숫자 -> 형식문자: 100000 -> $100,000 >> format 출력형식정의
to_char() 날짜 -> 형식문자: '2023-05-05' -> 2023년05월05일 >> format 출력형식정의
select to_date('2023-12-12') + 100 from dual;
| 변환형 함수 | 기능 |
| to_char | 문자열로 변환, 출력형식 지정가능 |
| to_date | 날짜 형식으로 변환 |
| to_number | 숫자형식으로 변환 |
날짜 표시
select sysdate , to_char(sysdate,'YYYY') || '년' as yyyy
, to_char(sysdate,'YEAR')
, to_char(sysdate,'MM')
, to_char(sysdate,'DD')
, to_char(sysdate,'DAY')
from dual;
입사월 비교
where to_char(hiredate,'MM') = '12';
출력 형식
select '>' || to_char(12345,'999999999999999') || '<' from dual;
남은 숫자 빈칸역시 trim으로 잘라낼 수 있다.
select '>' || ltrim(to_char(12345,'999999999999999')) || '<' from dual;
숫자 문자형식
select '>' || to_char(12345,'$999,999') || '<' from dual;
숫자형식으로 변환
select to_number('100') + 100 from dual;
5) 일반적인 함수 : NVL, DECODE
프로그래밍 성격이 강하다
1. nvl() :null 처리하는 함수
2. decode(): java if 문
3. case() : java switch
decode
decode를 통해 id 값에 따라 부서 이름 컬럼을 생성해서 이름을 달리 붙일 수 있다.
select id, decode(id,100,'아이티',
200,'영업팀',
300,'관리팀',
'기타부서') as 부서이름
from t_emp;
/*
응용문제 : hint) java에서는 if문 안에 if문 올수 있죠
그럼 중첩 if 을 생각하면 아래 문제 풀어 보세요
부서번호가 20번인 사원중에서 SMITH 라는 이름을 가진 사원이라면 HELLO 문자 출력하고
부서번호가 20번인 사원중에서 SMITH 라는 이름을 가진 사원이 아니라면 WORLD 문자 출력하고
부서번호가 20번인 사원이 아니라면 ETC 라는 문자를 출력하세요
EMP 테이블에서요 ....
*/
select empno , ename , deptno ,
decode(deptno,20,decode(ename,'SMITH','HELLO','WORLD'),
'ETC')
from emp;
case
1)
CASE 조건식(컬럼명) WHEN 결과1 THEN 출력1
WHEN 결과2 THEN 출력2
WHEN 결과3 THEN 출력3
WHEN 결과4 THEN 출력4
ELSE 출력5
END 컬럼별칭
select '0' || to_char(zipcode),
case zipcode when 2 then '서울'
when 31 then '경기'
when 41 then '제주'
else '기타'
end 지역이름
from t_zip;
2)
조건식 필요하다면
case when 조건비교( 컬럼명 > 1 ) then 출력값
when 조건비교( 컬럼명 > 1 ) then 출력값
else 출력값
end 컬럼별칭
select case when sal <= 1000 then '4급'
when sal between 1001 and 2000 then '3급'
when sal between 2001 and 3000 then '2급'
when sal between 3001 and 4000 then '1급'
else '특급'
end 급수 , empno , ename
from emp;
그날 수업 sQL문 전체
--문자열 검색
--주소검색 >> 역삼 >> 역삼단어가 포함된 모두 주소 ....
--LIKE 문자열 패턴 검색
--와일드 카드 ( %(모든것) , _(한문자) )
select sysdate from dual;
select *
from emp
where ename like '%A%';
select *
from emp
where ename like 'A%'; --A로 시작하는 이름을 가진 사원을 찿으세요
--where ename like '김%';
select *
from emp
where ename like '%E'; --BLAKE
select *
from emp
where ename like '%LL%'; --ALLEN , MILLER
select *
from emp
where ename like '%A%A%'; --ADAMS
select *
from emp
where ename like '_A%'; --첫번째 어떤것이 와도 상관없고 두번째 A이고 ....
--정규표현식
regexp_like()
regexp_like(ename,'[A-C]');
--조당 5개 씩 만들어서 올리세요 (금주 금요일) 카페
-----------------------------------------------------
--데이터 정렬하기
--order by 컬럼명: 대상>> 문자열 , 숫자 , 날짜 정렬가능
--오름차순 : asc 낮은순 default
--내림차순 : desc 높은순
--내부적으로 정렬 알고리즘 >> DB입장에서는 비용 (cost)
select *
from emp
order by sal; --default asc
select *
from emp
order by sal desc; --급여많이 받는순
--입사일이 가장 늦은순으로 정렬해서 사번 , 이름 , 급여 , 입사일을 출력하세요
--가장 최근에 입사한 순으로
select empno, ename, sal, hiredate
from emp
order by hiredate desc;
/*
select 3
from 1
where 2
order by 4 (select 결과를 정렬)
*/
select empno , ename , sal ,hiredate
from emp
where job='MANAGER'
order by hiredate desc;
select job ,deptno
from emp
order by job asc , deptno desc;
--그룹핑 .... grouping
--게시판
--ref , step , depth
--order by ref desc , step asc
--연산자
--합집합(union) : 테이블과 테이블의 데이터를 합치는 것 (중복값 배제)
--합집합(union all): 테이블과 테이블의 데이터를 합치는 것 (중복값 허용)
create table uta(name varchar2(20));
insert into uta(name) values('AAA');
insert into uta(name) values('BBB');
insert into uta(name) values('CCC');
insert into uta(name) values('DDD');
commit;
create table ut(name varchar2(20));
insert into ut(name) values('AAA');
insert into ut(name) values('BBB');
insert into ut(name) values('CCC');
commit;
select * from uta;
select * from ut;
delete from ut;
commit;
--연산자 합집합 (union)
select * from ut
union
select * from uta;
--ut 집에 uta 식구들이 놀러가는 것 (중복 데이터 제거)
select * from ut
union all
select * from uta;
--union
--1. [대응]되는 [컬럼]의 [타입]이 동일
select empno , ename from emp
union
select dname , deptno from dept;
select empno , ename from emp
union
select deptno , dname from dept;
--나중에 subquery (가상테이블)
select *
from (
select empno , ename from emp
union
select deptno , dname from dept
) m
order by m.empno desc; --in line view
--2. [대응] 대는 컬럼의 [개수]가 동일
select empno , ename , job ,sal from emp
union
select deptno , dname , loc from dept;
------------------------------------------------------
--코린이 개발자가 의무적으로 해야하는 코드
--오라클.pdf 47 page 까지-------------------------------
--오라클.pdf 48 page 4장
/*
1) 문자형 함수 : 문자를 입력 받고 문자와 숫자 값 모두를 RETURN 할 수 있다.
2) 숫자형 함수 : 숫자를 입력 받고 숫자를 RETURN 한다.
3) 날짜형 함수 : 날짜형에 대해 수행하고 숫자를 RETURN 하는 MONTHS_BETWEEN 함수를
제외하고 모두 날짜 데이터형의 값을 RETURN 한다.
4) 변환형 함수 : 어떤 데이터형의 값을 다른 데이터형으로 변환한다.
5) 일반적인 함수 : NVL, DECODE
*/
--문자열 함수
select initcap('the super man') from dual; --The Super Man
select lower('AAA') , upper('aaa') from dual; --aaa AAA
select ename , lower(ename)as lowerename from emp;
select * from emp where lower(ename) ='king'; --소문자로 만들어서 비교
select length('abcd') from dual; -- 문자의 개수 --결과: 4
select length('홍길동') from dual; --개수 --결과 : 3
select length(' 홍 길 동a') from dual; --결과 : 13
select concat('a','b') from dual; --ab
--select concat('a','b','c') from dual;
select 'a' || 'b' || 'c' from dual; --abc
select concat(ename, job) from emp;
select ename || ' ' || job from emp;
--java substring
--oracle substr
select substr('ABCDE',2,3) from dual; --BCD
select substr('ABCDE',1,1) from dual; --A
select substr('ABCDE',3,1) from dual; --C
select substr('dsfasdfgdsagdsagsdagasdfgva',3) from dual;
--시작점
/*
사원테이블에서 ename 컬럼의 데이터에 대해서 첫글자는 소문자로 나머지 글자는 대문자로
출려하되 하나의 컬럼으로 만들어서 출력하시고 컬럼의 별칭은 fullname 하고 첫글자와 나머지
문자 사이에는 공백 하나를 넣으세요
--바로 위에서 배운 함수만 사용
-- SMITH >> s MITH
*/
select lower(substr(ename,1,1)) from emp;
select upper(substr(ename,2)) from emp;
select substr(ename,2,length(ename)) from emp;
select lower(substr(ename,1,1)) || ' ' || upper(substr(ename,2)) as fullname
from emp;
select lpad('ABC',10,'*') from dual; --*******ABC
select rpad('ABC',10,'*') from dual; --ABC*******
--사용자 비번 : hong1004 , k1234
--화면에 시작 2글자만 보여주고 나머지 특수문자
select rpad(substr('hong1004',1,2),length('hong1004'),'*') from dual;
select rpad(substr('k1234',1,2),length('k1234'),'*') from dual;
--emp 테이블에서 ename 컬럼의 데이터를 출력하되 첫글자만 출력하고 나머지 글자는
-- * 로 출력하세요
select rpad(substr(ename,1,1),length(ename),'*') from emp;
--rtrim 함수
--오른쪽 문자를 지워라
select rtrim('MILLER','ER') from dual;--MILL
select ltrim('MILLLLLLLLLLLLLER','MIL') from dual;--ER
--공백제거
select '>' || 'MILLER ' || '<' from dual;
select '>' || rtrim('MILLER ',' ') || '<' from dual;
--치환함수
select ename , replace(ename,'A','와우') from emp;
--------------------------------------------------------------
--숫자함수
--round(반올림함수)
--trunc(버림함수 , 절사함수)
--mod(나머지 구하는 함수)
-- -3 -2 -1 0(정수) 1 , 2 , 3
select round(12.345,0) as r from dual;
select round(12.567,0) as r from dual; --13
select round(12.345,1) as r from dual; --12.3
select round(12.567,1) as r from dual; --12.6
select round(12.345,-1) as r from dual; --10
select round(15.345,-1) as r from dual; --20
select round(15.345,-2) as r from dual; --0
---
select trunc(12.345,0) as r from dual; --12
select trunc(12.567,0) as r from dual; --12
select trunc(12.345,1) as r from dual; --12.3
select trunc(12.567,1) as r from dual; --12.5
select trunc(12.345,-1) as r from dual; --10
select trunc(15.345,-1) as r from dual; --10
select trunc(15.345,-2) as r from dual; --0
--
select 12/10 from dual;
select mod(12,10) from dual;
select mod(0,0) from dual;
---------------------------------------------------------------
--날짜 함수(연산)
select sysdate from dual;
--POINT
--1. Date + Number >> Date
--2. Date - Number >> Date
--3. Date - Date >> Number
select sysdate + 1000 from dual;
select sysdate - 50 from dual;
select hiredate from emp;
select trunc(months_between(sysdate,'2020-01-01'),0) from dual;
select trunc(months_between('2022-09-17','2020-01-01'),0) from dual;
--주의사항
select '2023-01-01' + 100 from dual;
--'2023-01-01' 문자데이터 인지
select to_date('2023-01-01') + 100 from dual;
--사원테이블에서 사원들의 입사일에서 현재날짜 까지의 근속월수 구하세요
--단 근속월수는 정수부만 출력(반올림 하지 마세요)
select hiredate , trunc(months_between(sysdate,hiredate),0)
from emp;
--한달이 31일이라고 가정하고 기준에서 근속월수를 구하세요
--months_between 함수 사용금지 (반올림 하지 마세요)
select trunc((sysdate-hiredate)/31,0)
from emp;
----------------------------------------------------------------
--문자함수 , 숫자함수 , 날짜함수 END-------------------------------
--변환함수 Today Point
--오라클 데이터 유형 : 문자열 , 숫자 , 날짜
--$1,000,000
--to_char() 숫자 -> 형식문자: 100000 -> $100,000 >> format 출력형식정의
--to_char() 날짜 -> 형식문자: '2023-05-05' -> 2023년05월05일 >> format 출력형식정의
--select to_date('2023-12-12') + 100 from dual;
select sysdate , to_char(sysdate,'YYYY') || '년' as yyyy
, to_char(sysdate,'YEAR')
, to_char(sysdate,'MM')
, to_char(sysdate,'DD')
, to_char(sysdate,'DAY')
from dual;
--page 68-71 표참조
--입사일이 12월인 사원의 사번 , 이름 , 입사일 , 입사년도 , 입사월을 출력하세요
select empno , ename , hiredate , to_char(hiredate,'YYYY'),to_char(hiredate,'MM')
from emp
where to_char(hiredate,'MM') = '12';
select '>' || to_char(12345,'999999999999999') || '<' from dual;
select '>' || ltrim(to_char(12345,'999999999999999')) || '<' from dual;
select '>' || to_char(12345,'$999,999') || '<' from dual;
--to_date()
select to_date('2023-01-01') + 100 from dual;
to_number()
select '100' + 100 from dual;
select to_number('100') + 100 from dual;
---------------------------------------------------------------------
--HR 계정으로 이동
show user;
--USER이(가) "HR"입니다.
select * from employees;
/*
사원테이블(employees)에서 사원의 이름은 last_name , first_name 합쳐서 fullname 별칭
부여해서 출력하고 입사일은 YYYY-MM-DD 형식으로 출력하고 연봉(급여 *12)을 구하고
연봉의 10%(연봉 * 1.1)인상한 값을
출력하고 그 결과는 1000단위 콤마 처리해서 출력하세요
단 2005년 이후 입사자들만 출력하세요 그리고 연봉이 높은 순으로 출력하세요
*/
select employee_id,
first_name || ' ' || last_name as fullname,
TO_CHAR(HIRE_DATE, 'YYYY-MM-DD') as 입사일,
salary * 12 as 연봉,
TO_CHAR(((salary * 12) * 1.1), '$999,999,999') as 인상된연봉
from employees
where TO_CHAR(hire_date, 'YYYY')>='2005'
order by 연봉 desc;
--다시 kosa 전환
show user;
---------------------------------------------------------------------
--문자, 숫자 , 날짜 , 변환함수 (to_)
----------------------------------------------------------------------
--일반함수
--(프로그래밍 성격이 강하다)
--1. nvl() null 처리하는 함수
--2. decode() >> java if 문 >> 통계데이터 (분석) >> pivot() , cube , rollup
--3. case() >> java switch
create table t_emp(
id number(6), --정수 6자리
job nvarchar2(20) --unicode 영문자 , 특수문자 , 공백 , 한글 2byte : 20글자
);
desc t_emp;
insert into t_emp(id,job) values(100,'IT');
insert into t_emp(id,job) values(200,'SALES');
insert into t_emp(id,job) values(300,'MANAGER');
insert into t_emp(id) values(400);
insert into t_emp(id,job) values(500,'MANAGER');
commit;
delete from t_emp
commit;
select * from t_emp;
select id, decode(id,100,'아이티',
200,'영업팀',
300,'관리팀',
'기타부서') as 부서이름
from t_emp;
select empno , ename , deptno , decode(deptno,10,'인사팀',
20,'관리팀',
30,'회계팀',
40,'외주팀',
'ETC') as 부서이름
from emp;
create table t_emp2(
id number(2),
jumin char(7) --고정길이 문자열
);
--데이터 타입
--char(10) 고정길이 문자열 >> 'A' >> 10byte >> 주민번호 , 성별 , 이름(x)
--varchar2(10) 가변길이 문자열 >> 'A' >> 1byte >> 이름(0)
-- nchar() , nvarchar2();
desc t_emp2;
insert into t_emp2(id,jumin) values(1,'1234567');
insert into t_emp2(id,jumin) values(2,'2234567');
insert into t_emp2(id,jumin) values(3,'3234567');
insert into t_emp2(id,jumin) values(4,'4234567');
insert into t_emp2(id,jumin) values(5,'5234567');
commit;
/*
t_emp2 테이블에서 id, jumin 데이터를 출력하되 jumin 앞자리가 1이면
남성 , 2이면 여성 ,3이면 중성 그 외는 기타라고 출력하세요 (컬럼명은 성별)
*/
select * from t_emp2;
select id, decode(substr(jumin,1,1) , 1 ,'남성' ,
2 , '여성' ,
3 , '중성' ,
'기타') as 성별
from t_emp2;
--if안에 if 올수 있어요
--decode(decode())
/*
응용문제 : hint) java에서는 if문 안에 if문 올수 있죠
그럼 중첩 if 을 생각하면 아래 문제 풀어 보세요
부서번호가 20번인 사원중에서 SMITH 라는 이름을 가진 사원이라면 HELLO 문자 출력하고
부서번호가 20번인 사원중에서 SMITH 라는 이름을 가진 사원이 아니라면 WORLD 문자 출력하고
부서번호가 20번인 사원이 아니라면 ETC 라는 문자를 출력하세요
EMP 테이블에서요 ....
*/
select empno , ename , deptno ,
decode(deptno,20,decode(ename,'SMITH','HELLO','WORLD'),
'ETC')
from emp;
--CASE 문
/*
CASE 조건식 WHEN 결과1 THEN 출력1
WHEN 결과2 THEN 출력2
WHEN 결과3 THEN 출력3
WHEN 결과4 THEN 출력4
ELSE 출력5
END "컬러명"
*/
create table t_zip(
zipcode number(10)
);
desc t_zip;
insert into t_zip(zipcode) values(2);
insert into t_zip(zipcode) values(31);
insert into t_zip(zipcode) values(32);
insert into t_zip(zipcode) values(41);
commit;
select * from t_zip;
select '0' || to_char(zipcode),
case zipcode when 2 then '서울'
when 31 then '경기'
when 41 then '제주'
else '기타'
end 지역이름
from t_zip;
/*
case 컬럼명 when 결과1 then 출력1
조건식 필요하다면
case when 조건비교( 컬럼명 > 1 ) then
when 조건비교( 컬러명 > 1 ) then
/*
사원테이블에서 사원급여가 1000달러 이하면 4급
1001달러 2000달라 이하면 3급
2001달러 3000달라 이하면 2급
3001달러 4000달라 이하면 1급
4001달러 이상이면 '특급'이라는 데이터를 출력하세요
*/
select case when sal <= 1000 then '4급'
when sal between 1001 and 2000 then '3급'
when sal between 2001 and 3000 then '2급'
when sal between 3001 and 4000 then '1급'
else '특급'
end 급수 , empno , ename
from emp;
---------------------------------------------------------------------------
--문자함수 , 숫자함수 , 날짜함수 , 변환함수(to_) , 일반함수(nvl, decode , case)
----------------------------------------------------------------------------
--교재 : 171 page , 오라클 pdf 75page
'오라클 데이터베이스' 카테고리의 다른 글
| sQL7일차(0718) (0) | 2023.07.18 |
|---|---|
| sQL 6일차(0717) (0) | 2023.07.17 |
| sql 5일차(0714) (0) | 2023.07.14 |
| sql 4일차(0713) (0) | 2023.07.13 |
| sql 3일차(0712) (0) | 2023.07.12 |
