단일,복수,group, join,subquery,dml,ddl,constraint,index,view,sequence,synonym,hierachical

단일 함수

-연산자
연결연산자 : column||'comment'||column
in(a,b,c)
 
-distinct 중복된 값 제거
select distinct column

like 특정패턴
ex)where name like '김%'
'ㅈ'자로 시작하는
where name between '자%' and '차&'

---------------------------------------------------------------------

문자함수

initcap()
lower()
length()
lengthb()
contact('A','B') 결합해서 출력
substr('ABC',1,2) AB
substrb('한글',1,2) 한
lpad('love',6,'*') **love
ltrim('*love','*') love
replace('AB','A','E') EB

-union 중복값제거 안함
-union all 중복값 제거
-intersect 교집합
-minus where 조건

ex)지역번호 출력
substr(column,1,instr(tel,')') -1)
ex)이름 중간바꾸기
select replace(name,substr(name,2,1),'#')
ex)지역번호 다음자리 바꾸기
replace(column,substr(tel,instr(tel, ')', 1)+1,3), '###')
-> 051)###-1700

---------------------------------------------------------------------

정규식

regexp_count(column,'a')
regexp_substr(column,패턴,위치)

---------------------------------------------------------------------

형변환함수

yyyy, yy, year
mm, mon, month
dd, day, ddth

alter session set nls_date_format='yyyy-mm-dd'

ex)3월달 출력
where to_char(birthday,'MM') = '03'

ex) 1990년 이전, 현재연봉 10%인상, 연봉(bonus제외)
select to_char(pay*12, '99,999')
       to_char((pay*12)*1.1, '99,999')
where to_char(hiredate'yyyy') <'1990'

ex)근속일수 차이
round(months_between(sysdate,hiredate),2)
round(((sysdate-hiredate) / 31),2)

---------------------------------------------------------------------

nvl

ex)bonus가 null이 아니면 pay*12 , null이면 0
nvl2(bonus,pay*12,pay*0)

---------------------------------------------------------------------

decode(null 포함)

ex) 101부서중 이름 조인형이면 석좌교수, 다른교수면 후보아님. 101 교수 아니면 비고란
selece decode(deptno,101,decode(name,'조인형','석좌교수후보','후보아님') )
ex) jumin 이용 남자/여자
select decode(substr(jumin,7,1),1,'남자',2,'여자')
ex) 지역번호
select decode(substr(tel,1,instr(tel,')')-1), '02','서울', , '기타')

---------------------------------------------------------------------

case

ex) 급여액수 기준 200미만 4급, 201~300 3급
case when pay < 200 then '4급'
     when pay between 201 and 300 then '3급'
     end '등급'
---------------------------------------------------------------------

그룹함수

count
sum
avg
max
min
rollup
cube

ex) 평균 소수점 첫째
round(avg(pay+nvl(bonus,0)),1)

ex)급여와 보너스를 합친 급액이 가장 많은 경우, 그리고 평균. 보너스 없으면 0
max(nvl2(bonus,pay+bonus,pay*0))
round(avg(nvl2(bonus,pay+bonus,0)),1)

ex)월별 태어난 인원수
count(decode(to_char(birthday,'MM'),'01',0)) "1월"

ex)지역별 인원수
count(decode(substr(tel,1,instr(tel,')')-1), '02', 0)) "서울"
---------------------------------------------------------------------

 rank

집계용
rank(조건값) within group (order by 칼럼명)

분석용
ex) 급여순위
rank() over (order by pay)

ex) 부서별 급여순위
rank() over (partition by deptno
             order by sal)
ex) calender
select min(decode(day,'일',num_day)) SUN
 group by week
 order by week
--------------------------------------------------------------------- 

누적합계

-누계판매 금액
sum(column) over (order by column)

group
-having
-group by rollup()
-group by cube()
-group by grouping sets(column,column)
-select column, listagg(column,'**') within group(order by column)

---------------------------------------------------------------------

JOIN

1.등가조인
ex) stu+pro, 학생이름, 교수번호, 교수이름 출력
where s.profno=p.profno / on (  ) ;

ex) stu+department+pro, 학생이름, 학과이름, 지도교수이름
where s.deptno1=d.deptno and s.profno=p.profno (oracle)
from student s join department d (ANSI)
on ~
join professor p
on ~ ;

ex) emp2 + p_grade, 사원이름(e.name), 직급(e.position), 현재연봉(e.pay), 하한금액(p.s_pay), 상한금액(p.e_pay)
where e.position=position;

ex) stu+pro, (dept=101) 학생이름, 지도교수이름
where s.profno=p.profno
and s.deptno1=101;

2. 비등가
ex) gogak+gift, (마일리지 포인트별) 고객이름(go.gname), 포인트(go.point), 상품명(gi.gname)
where go.point between gi.g_start and gi.g_end;

-->ex) 상품명(gi.gname), 필요수량(count(*))
where go.point between gi.g_start and g_end
group by gi.gname;

ex) stu+exam_01+hakjum, 학생이름(s.name), 점수(e.total), 학점(h.grade)
where s.studno=e.studno
and e.total between h.min_point and h.max_point;

ex) gogak+gift, (자기보다 포인트 낮은 포인트일때 산악자전거인 고객) 고객명(go.gname), 포인트(go.point), 상품명(gi.gname)
where gi.g_start <= go.point
and gi.gname='산악용자전거';

ex) emp2+p_grade, (예상직급, 나이=sysdate기준 trunc 소수점이하 절삭)
이름(e.name), 현재나이(trunc((sysdate-e.birthday)/365,0), 현재직급(e.position), 예상직급(g.position)
where trunc(~) between g.s_age and g.e_age;
---------------------------------------------------------------------
 

OUTER 조인

set null *****
ex) stu+pro,(지도교수 결정안된 학생이름) 학생이름(s.name), 지도교수이름(p.name)
where s.profno=p.profno(+) (oracle)

from student s left outer join professor p (ansi)
on s.profno=p.profno

ex) stu_pro, (지도학생 결정안된) 반대

ex) stu+pro, (지도학생, 지도교수 결정안된)
union ( oracle)
from student s full outer join professor p ( ansi)
---------------------------------------------------------------------
 

SELF 조인

ex) dept2 (상위부서) 부서명(a.dname), 상위부서명(b.dname)
where a.pdept=b.dcode;

ex) professor (자기 입사일보다 빠른사람수)
교수번호(a.profno), 교수명(a.name), 입사일(a.hiredate), 빠른사람 (count(b.hiredate))
where b.hiredate(+) < a.hiredate
group by a.profno, a.name, a,hiredate
order by 4;

---------------------------------------------------------------------
 

SUB QUERY

ex) emp, (scott보다 sal 많은, sal) 이름(ename), 급여(sal)
where sal > ( select sal from emp where ename='SCOTT') ;

1. 단일행

ex) stu+dept, (이윤나 학생과 deptno1같은, deptno1) 학생이름(s.name), 전공명(d.dname)
from ~
where s.deptno1=d.deptno
and s.deptno1= ( select deptno1 from student where name='이윤나');

ex) prof+department (송도권 교수보다 hiredate 큰사람, hiredate) 교수명(p.name), 입사일(p.hiredate), 학과명( d.dname)
 where p.deptno=d.deptno
and hiredate > ( select hiredate from professor where name='송도권');

ex) stu (deptno1=101 학과의 평균 몸무게보다 많은 학생, avg(weight) ) 이름, 몸무게
where weight > ( select avg(weight) from student where deptno1=101);

ex) prof (심슨교수와 같은 hiredate & pay가 조인형 교수보다 낮은, hiredate, pay) 이름, 급여, 입사일
where hiredate = ( select hiredate from professor where name='심슨')
and pay < ( select pay from professor where name='조인형');

2. 다중행
in 같은값
>any 최솟값
<any 최댓값
<all 최솟값
>all 최댓값
exist 값이 잇을경우

ex) emp2+dept2 (근무지역이 서울지사,dcode) 사번empno, 이름name, 부서deptno
where deptno in ( select dcode from dept2 where area='서울지사');

ex) emp2 (과장 직급 최소 연봉보다 높음 사람, pay) 이름, 직급, 연봉 to_char(pay,'999,999,999')|| ' 원'
where pay > any (select pay from emp2 where position='과장');

ex) student ( 4학년 최소체중보다 적은 학생, weight) 이름, 학년, 몸무게
where weight > any (select weight from student where grade=4);

ex) student (학년별 최대키) 학년, 이름, 키
where (grade, height) in ( select grade, max(height) from student group by grade)
order by 1;

ex) prof+department (학과별 hiredate 적은) 교수번호p.profno, 교수명p.name, 입사일p.hiredate, 학과명d.dname
where p.deptno=d.deptno
and (p.deptno, p.hiredate) in ( select deptno, min(hiredate) from professor group by deptno)
order by 4;

ex) emp2 (직급별 pay 많은 연봉순 오름차순) 이름name, 직급position, 연봉pay
where (position, pay) in ( select position, max(pay) from emp2 group by position)
order by 3;

ex) emp2+dept2 (부서별 연봉pay, 평균연봉 적은 부서의 평균연봉보다 적은 사람)
부서명d.dname, 사원명 e.name, 연봉 e.pay
where e.deptno=d.dcode
and e.pay < all ( select avg(pay) from emp2 group by deptno)
order by 3;

ex) emp2 (직급 평균연봉과 같거나 많이 받는 사람) 사원이름name, 직급position, 급여pay
from emp2 a
where pay >= ( select avg(pay) from emp2 b where a.position=b.position);
---------------------------------------------------------------------
 

DML

sql>alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss' ;

데이터 갯수 삭제
>delete from table
where column between number and number;
---------------------------------------------------------------------

CONSTRAINT (제약조건)

-not null, unique,
-primary key(not null+unique) 테이블당 한개,
-foreign key 무결성 검사 두개의 테이블 동시 생성
-check 설정된 값만 입력 나머지 거부

-parent table : 정보를 제공하는 쪽
-reference key : 정보를 제공하는 쪽(부모테이블)의 참조하는 컬럼의 제약조건
-child table : 정보를 받는 쪽
-foreign key : 정보를 받는 쪽(자식테이블) 칼럼에 사용되는 제약조건
-> 자식 부모 테이블에 적절한 인덱스 생성 필요.

ex)생성 및 설정
create table emp3
(no number(4) constraint emp3_no_pk primary key,
name varchar2(10) constraint emp3_name_nn not null,
jumin varchar2(13) constraint emp2_jumin_nn not null unique,
area number(1) check ( area <5 ),
deptno varchar2(10) references dept2(dcode)

ex)제약조건 추가
alter table emp4
add constraint emp4_name_uk unique(name);

ex)제약조건 추가 (not null)
alter table emp4
modify (area constraint emp4_area_nn not null);

ex)참조키 제약조건 설정
parent table의 reference key : primary key 이거나 unique key

-on delete cascade : 부모테이블 데이터 지워지면 자식테이블도 함께 지워짐.

>alter table childtable
add constraint constraintname foreign key(column)
references parenttable(column) on delete cascade;

->reference key 는 unique key 이거나 primary key이여야 한다.
아닐경우 제약조건 추가

-data 빨리 붇기. 제약조건 잠시disable 후 다시 enable
->disable novalidate :
alter table tablename
disable (novalidate constraint 에러메시지) constraintname;

->disable validate : update, insert, delete 아예 불가
alter table tablename
disable validate constraint constraintname;

->enable novalidate: 지금부터 제약조건 시작, 신규데이터만 검사

->enalbe validate: LOCK걸고 지금까지 입력된 데이터, 지금부터 입력되는 데이터 모두 검사.
exceptions이용 에러 찾기:
sys> @oracle_home/rdbms/admin/utlexcpt.sql
alter table tablename
enable validate constraint constname
exceptions into sys.exceptions;

에러내역조회:
select rowid, no
from tablename
where rowid in ( select row_id from exceptions);

정상적값 업데이트
update tablename
set
where rowid =
commit;

제약조건 조회하기
select owner, constraint_name, constraint_type, status
from user_constraints
where table_name='EMP4';

제약조건 삭제
alter table tablename
drop constraint conname;

---------------------------------------------------------------------

 INDEX (인덱스)

index 는 insert,delete update는 없음.
index=segment 안에 rowid포함
index 생성중 data lock, pga-sort area 에서 데이터 정렬
index 칼럼 두개 key, rowid
오름차순으로 정렬되서 입력
where절에 조건 rowid참고하는경우도 있음.
종류 : b-tree(oltp), bitmap(olap)
데이터 처리방법: oltp, olap

-index surpressing error
where절에 오는 칼럼에 인덱스 생성
ex)sal=100 sal index
   sal + 100 =200 (x) surpressing error

 b-tree : binary, balace-tree
root block->brach block->leef block(row id) 찾음
1.unique index: key값 중복 없음
->create unique index idx_tablename_columnnmae
on tablename(columnnmae asc|desc, columnname2, ....);
table에 중복된 값 insert 안됨

2.non unique index:
->create index indexname
on tablename(columnname asc|desc , columnname2, ...);

3.function based index(fbi) :함수기반 인덱스
surpressing error방지, where 절 가공해야 할때
ex) create index indexname
on tablename(pay+100);

4.descending index: 큰값부터 조회해야할때(최근날짜, 회사 매출 큰값부터)
->create index indexname
on tablename(columnname desc);

5.compisite index(결합인덱스): where절에 and와 조건 두개 올때.
두개이상의 칼럼 합칠때.
ex) 인원:50명, 남자:25명, 여자:25명. 여자중 유관순2명 찾아라.
where name='유관순' and gender='여자'
-> create index indexname
on tablename(name,gender);

 bitmap index: 데이터값종류 적고, 동일한 데이터 많을때
->create bitmap index indexname
on tablename(columnname);
문제점: 테이블에 dml일어나면 전부 수정해줘야함.

-rowid 확인
select rowid from tablename
where 조건;

insert
->index split 일어남 : 기존블록 여유공간없을때,
기존블록에 있던 내용일부를 가능한 블록에 덮어쓰기를 하는데,
이유는 index가 순서대로 정렬해서 저장하기 때문이다.
이때 기존블록에있던 내용이 split이 일어난다.

delete
->index는 데이터가 지워지지 않고 사용안한다는 표시만 해둔다.

update
->table에서 update일어나면, index에서는 delete가 일어나고 insert가 일어난다.

문제점: index 생성시 optimizer는 무분별하게 최근것을 선호하게 되 실행계획을
변경한다.

index 조회
-select table_name, index_name from user_indexes
where table_name='';

index 사용여부 모니터링
->alter index indexname monitoring|nomonitoring usage;
->select index_name, used
from v$object_usage
where index_name='';

index rebuild 고치기 : 밸런스유지목적. 지워지지 않고 재활용되기 때문.
balance확인
->analyze index indexname validate structure;
-> select (del_lf_rows_len / lf_rows_len) * 100 balance
from index_stats;

rebuild
->alter index indexname rebuild;
->analyze index indexname validate structure;

invisible index(11g부터)
->select table_name, index_name, visibility
from user_indexes
where table_name='';

->alter index indexname invisible|visible ;
---------------------------------------------------------------------
 

VIEW 뷰- 가상테이블

식당 메뉴판과 같은 개념
보안과 편의성의 목적이 있음 조회용
원본파일 테이블에서 최근 데이터 매번 가져옴
제약조건, index 생성 불가
평상시 view에는 아무런 데이터가 없다가,쿼리 수행후 데이터 가져옴

1.단순view : 1개테이블사용
->grant create view to scott;
->create or replace view viewname
as
  select column,column,column...
  from tablename;

2.복합view(complex view) : 여러개테이블 조인
ex)professor+department, 교수번호와 교수이름, 학과이름 조회하는 view생성
create or replace view v_prof_dept
as
select p.profno, p.name, d.dname
from professor p, department d
where p.deptno=d.deptno;

3.인라인view(Inline View) : 일회용, 쿼리만 from절에 적어주면 된다.
ex) student+department, 학과별 최대키, 최대몸무게, 학과이름 출력
select d.dname, s.max_height, s.max_weight from
(select deptno1, max(height) max_height, max(weight) max_weight from student
group by deptno1) s, department d
where s.deptno1 = d.deptno;

ex) student+department, 학과별 최대키 이름과 키, 학과이름
select d.dname, s.max_height, c.name, c.height from
(select deptno1, max(height) max_height from student
group by deptno1) s, student c, department d
where s.deptno1 = d.deptno
and s.max_height = c.height
and c.deptno1 = d.deptno
order by 1;
 
ex) student, 학년별 평균키보다 큰 학생의 학년, 이름,키, 평균키
select c.grade, c.name, c.height, s.avg_height from
(select grade, avg(height) avg_height from student group by grade) s, student c
where s.grade = c.grade
and c.height > s.avg_height
order by 1;

4. Mview(Materialized view) 구체화된 실체화된: 데이터를 가지고 있는 view
단점 : 동기화 문제

sys>grant query rewrite to scott;
sys>grant create materialized view to scott;
create materialized view viewname
build immediate
refresh
on demand
complete
enable query rewrite
as
select column, column, column
from table
where ~;

index 생성 가능
create index indexname
on viewname(column);

동기화 dbms_view
begin
dbms_mview.refresh('MV_PROF');
end;
/

dbms_mview.refresh_dependent('tablename') 이테이블에 해당되는 mview모두 동기화
dbms_mview.refresh_all_mviews 해당사용자가 만든 모든mview 동기화

mview 조회
select mview_name, query
from user_mviews
where mview_name='MV_PROF';

---------------------------------------------------------------------

SEQUENCE

연속적인 번호 생성

create sequence sequence_name
increment by n  --- 증가값 기본값은 1
start with n  --- 시퀀스 시작번호 기본값은 1
maxvalue ---  생성가능한 시퀀스 최대값
minvalue ---  cycle일 경우 새로 시작되는 값
cycle | nocycle ---  시퀀스 번호 순환 여부
cache n | no cache ---  시퀀스 생성 속도 개선하기위해 캐싱여부 지정

ex) 시작번호 : 1000, 끝번호 : 1010, 증가값 : 1, 반복 캐싱=2
create sequence seq_no
increment by 1
start with 1000
maxvalue 1010
minvalue 990
cycle
cache 2;

curval : 현재까지 생성된 번호
nextval : 다음에 나올 번호
cache : 번호 대기표 미리 메모리상에 뽑아 놓고 즉시 번호 할당 하는것

사용방법
insert into tablename
values(seqname.nextval|curval);

select seqname.CURRVAL from dual;

loop 돌리기
begin
for i in 1..n loop
insert into tablename values(seqname.nextval, dbms_random.string('A',4);
end loop;
commit;
end;
/

감소하는 sequence 생성
create sequence seqname
increment by -1
minvalue 0
maxvalue 10
start with 5;

조회
select sequence_name, min_val from user_sequences
where sequence_name='';

수정 : START WITH는 수정 불가
alter sequence seq_jumun2_no
maxvalue 110
cache 5;

---------------------------------------------------------------------

 SYNONYM (동의어)

테이블 별명
스키마 오브젝트
만든사람만 사용
public 키워드 사용하면 다른 사용자 사용가능

sys>grant create synonym to scott;
sys>grand create public synonym to scott;

create (public) synonym synonymname for schema;

조회
select synonym_name, table_owner, table_name
from user_synonyms
where table_name='EMP2';
---------------------------------------------------------------------

Hierachical Query 계층형 쿼리

상하관계 표시
계층형 쿼리 수행순서: start with -> connect by -> where

ex)
select lpad(dname, level*6, '*')
from dept2
connect by prior dcode=pdept --- dcode의 이전값을 찾아라 것이 pdept와 같다
start with dcode=0001;

ex) emp2+dept2, 부서와 직급별로 사원명,부서, 직급, 직급없는사람 사원으로 출력
select lpad(e.name||' '||nvl(e.position,'사원'),level*22,'-') "이름과 직급"
from emp2 e, (select dname, dcode, pdept from dept2) d
where e.deptno = d.dcode
connect by prior e.empno = e.pempno
start with e.empno = 19900101;

ex) emp2+dept2, 일지매 기술부장 아래 부하직원 이름과 직급 부서
select
lpad(e.name||' '||d.dname||'  '||nvl(e.position,'사원'), level*18, '-')
from emp2 e, (select dname, dcode, pdept from dept2) d
where e.deptno = d.dcode
connect by prior e.empno = e.pempno
start with e.empno = 19966102;

 

ex) emp2+dept2, 박지리 사원 상사
select
lpad(e.ename||'  '||d.dname||'  '||nvl(position,'사원'), level*19, '-')
from emp2 e, (select dname, dcode, pdept from dept2) d
where e.deptno = d.dcode
connect by e.empno = prior e.pempno
start with e.empno = 20000334;
---------------------------------------------------------------------