db 9일차

`1. view에 대한 개념 아예놓쳤음 문제가될까?/..

 

일반 뷰를봤을때 든생각은 이거 select 로 조건뽑이서 확인하는거랑 머가 다른거지??였다

 

뷰는 복합뷰에서 장점이 나오는것같다 하지만 이것도아직 애매하다

 

복합뷰를 만들기위해서 역시 테이블을 조인해야하지만 조인한데이터들만 모아놓은상태에서 또 검색을하는경우에

복합뷰가 의미가 있어지는게 아닐까라는 생각을했다`1. view에 대한 개념 아예놓쳤음 문제가될까?/..

 

 

 

일반 뷰를봤을때 든생각은 이거 select 로 조건뽑이서 확인하는거랑 머가 다른거지??였다

 

 

 

뷰는 복합뷰에서 장점이 나오는것같다 하지만 이것도아직 애매하다

 

 

 

복합뷰를 만들기위해서 역시 테이블을 조인해야하지만 조인한데이터들만 모아놓은상태에서 또 검색을하는경우에

 

복합뷰가 의미가 있어지는게 아닐까라는 생각을했다

 

 

 

-----

위에 일정부분 홀라당 날려먹음

------

 

create table MEMBER(
    M_NAME VARCHAR2(10) NOT NULL, 
    M_NUM NUMBER(2) PRIMARY KEY,
    REG_DATE DATE NOT NULL DEFAULT sysdate,
    GENDER CHAR(1),
    LOCA NUMBER(5)
);

create table MEMBER(
    M_NAME VARCHAR2(20) NOT NULL, 
    M_NUM NUMBER(2) ,
    REG_DATE DATE NOT NULL,
    GENDER CHAR(1),
    LOCA NUMBER(5)
);

alter table member add constraint mem_num_pl primary key (m_num); --PK
alter table member add constraint mem_regdate_uk unique (reg_date); --UK
alter table member add constraint mem_loca_loc_locid_fk foreign key (loca) references locations(location_id);

select * from user_constraints where table_name = 'MEMBER';
select * from member;

insert into member values ( 'AAA', 1, to_date('2018/07/01', 'YYYY/MM/DD'), 'M', 1800);

--문제2
select m.m_name,
       m_num,
       l.street_address,
       l.location_id
from member m
join locations l
on m.loca = l.location_id
order by m_num;

-- View는 제한적인 자료만 보기위해서 사용하는 가상의 테이블입니다.
-- 뷰는 물리적테이블(원본테이블)을 이용한 가상테이블이기 때문에 필요한 데이터만 저장해두면 조회에 이점을 가집니다,.
--뷰를 이용해서 데이터에 접근하면 원본데이터는 안전하게 보호할 수 잇습니다.
--뷰는 계정에 생성권한이 있어야 만들 수 있습니다.

select * from user_role_privs; --사용자 권한 확인 << 이거설명 이해못함 구글링꼭해라

--단순뷰(하나의 테이블에서 필요한 데이터만 추출한 뷰)
create /*or replace view*/ view view_emp
as (select employee_id,
            first_name || ' ' || last_name as name, --뷰의 컬럼명은 함수같은 가상표현형식은 안됩니다.
            job_id,
            salary
    from employees
    where department_id = 60);
    
select * from view_emp;

--복합뷰(여러테이블을 조인해서 필요한 데이터만 저장한 뷰)
create or replace view view_emp_dept_job
as (select e.employee_id,
        first_name || ' ' || last_name as name,
        e.salary,
        d.department_name,
        j.job_title
from employees e
join departments d on e.department_id = d.department_id
join jobs j on e.job_id = j.job_id);

select * from view_emp_dept_job order by employee_id;

select e.employee_id,
        first_name || ' ' || last_name as name,
        e.salary,
        d.department_name,
        j.job_title
from employees e
join departments d on e.department_id = d.department_id
join jobs j on e.job_id = j.job_id
order by employee_id;

--뷰의 수정(create or replace view~)
--동일한 이름으로 만들면 데이터가 변경됩니다.
create or replace view view_emp_dept_job
as ( select e.employee_id,
            first_name || ' ' || last_name as name,
            e.hire_date, --추가
            e.salary,
            d.department_name,
            j.job_title
     from employees e
     join departments d on e.department_id = d.department_id
     join jobs j on e.job_id = j.job_id);
    
select * from view_emp_dept_job;

select job_title,avg(salary)
from view_emp_dept_job
group by job_title; -- 뷰활용

/*
 단순뷰에서 DML연산을 수행가능
 뷰가 다음을 포함한다면 행을 제거할수 없습니다
 -그룹함수
 -group by 절
 -distinct키워드
 뷰가다음을 포함한다면 데이터를 수정할 수 없습니다.
 -위의 행 제거 할 수 없는 조건
 -표현식으로 정의된 열
 -rownum 의사열
 뷰가 다음을 포함한다면 데이터를 추가할수없습니다
 - 위의 행 제거와 수정 할 수 없는 조건
 -뷰에 의해 선택되지 않은 NOT NULL열이 기본 테이블에 있을때 
*/

--NAME가상열을 지니고 있는 경우 삽입불가
select * from view_emp;
insert into view_emp values(108, 'TEST', 'IT_PROG', 5000);
--LAST_NAME or hire_date 같이 원본테이블의 null을 허용하지 않는 컬럼이 있다면 삽입 불가.
insert into view_emp(employee_id, job_id, salary) values(108, 'IT_PROG', 5000);
--복합뷰의 경우에 한번에 여러 테이블에 대해 삽입이 불가.
select * from view_emp_dept_job;
insert into view_emp_dept_job(employee_id,name,hire_date,salary,department_name,job_title)
values(300, 'TEST', sysdate, 8000, 'TEST', 'TEST');

--with check option (조건절 컬럼의 수정을 막는 제약) --이거확인해라 
create or replace view view_emp_test
as (select employee_id, first_name, last_name, email, job_id, department_id
    from employees
    ) with check option constraint view_emp_test_ck;

--department_id에 제약조건이 걸리고, 변경할 수 없습니다.
select * from view_emp_test;
update view_emp_test set department_id = 10 where employee_id = 110;

--with read only (읽기 전용 뷰)

create or replace view view_emp_test
as (select employee_id, 
           first_name || ' '|| last_name as name
    ffrom employees) with read only

select * from departmente

--시퀀스 (순차적으로 증가하는 값)
select * from user_sequences;

--시퀀스 생성문
--create sequence dept2_seq; --자동생성
create sequence dept2_seq
    increment by 1
    start with 1 --시작값
    maxvalue 10 --최대값
    nocache --캐시x
    nocycle; --사이클x
drop table dept2;  
create table dept2(
    dept_no number(2) primary key,
    dept_name varchar2(20),
    loca number(4)
);

--시퀀스의 사용
insert into dept2 values(DEPT2_SEQ.nextval, 'TEST', 300);
insert into dept2 values(DEPT2_SEQ.nextval, 'TEST', 300);
insert into dept2 values(DEPT2_SEQ.nextval, 'TEST', 300);
select * from dept2;

select dept2_seq.currval from dual;
select dept2_seq.nextval from dual;

--시퀀스 변경 alter
alter sequence dept2_seq maxvalue 100000;
alter sequence dept2_seq increment by 100;
alter sequence dept2_seq minvalue 1; --최소값 1변경

-- alter table dept2 MODIFY dept_no number(5);
--시퀀스 삭제 drop
drop sequence dept2_seq;

--시퀀스를 다시 처음으로 되돌리는 방법
create sequence dept2_seq
    increment by 10
    nocache
    nocycle;
    
select dept2_seq.nextval from dual;
--1. 현재 시퀀스 확인
select dept2_seq.currval from dual;
--2. 증가값을 현재시퀀스 -1만큼 으로 변경 -
alter sequence dept2_seq increment by -30;
--3. 한번 nextval
select dept2_seq.nextval from dual;
--4. 증가값을 1로 변경
alter sequence dept2_seq increment by 1;

--시퀀스 활용 20210408-시퀀스-참조값
drop table dept3;
create table dept3(
    dept_no varchar2(30) PRIMARY KEY,
    dept_name varchar2(30)
);
create sequence dept3_seq
    increment by 1
    nocache
    nocycle;
    
insert into dept3 values( to_char(sysdate, 'YYYYMMDD') || '-' || LPAD(dept2_seq.nextval,5,0) , 'TEST');
select * from dept3;

select to_char(sysdate, 'YYYYMMDD') || '-' || LAPD(dept2_seq.nextval, 5, 0) from dual;

--인덱스
--인덱스 primary key, unique 제약조건을 사용할 때 자동으로 생성되고, 또는 수동으로 직접 생성할 수 있습니다.
--인덱스는 인덱스를 저장하는 추가적인 공간을 ㄱ가지고 생성되고,조회를 빠르게 합니다.
--다만 수정,삭제,변경이 빈번하게 일어나는 컬럼에 적용하면 오히려 성능부하를 일으킬수 있습니다.

select * from emps where first_name = 'Nancy'; --찾는속도 나오던데 나는 왜 오류?

--emps에 first_name에 인덱스 부여
create index emp_first_name_idx on emps(first_name);

--인덱스 삭제
drop index emp_first_name_idx; --인덱스는 삭제되더라도, 테이블에 전~혀 영향을 미치지 않!습!니!다~

 

---인덱스부터는 배운내용이 아니라 구글링

 

sas-study.tistory.com/104

 

gent.tistory.com/306

[Oracle] 오라클 인덱스(Index) 힌트 사용방법 (주석, 튜닝)

 

--뷰

 

gent.tistory.com/361