40일차 22.11.16

**제약조건
- 컬럼에 대한 조건
- 테이블의 삭제도 방지할 수 있음

*primary key 
-> 중복 허용 안됨
-> null값이 들어가지 않음
-> 가장 강한 제약 조건

*foreign key
-> 중복허용
-> null값 허용
-> pk에 없는 값은 들어갈 수 없음

*not null
-> null값을 허용하지 않음을 지정
-> primary key는 기본적으로 not null

*unique key
-> 데이터가 중복으로 들어갈 수 없음을 지정
-> ex) 주민번호
-> null값 허용

---> not null + unique key = primary key의 성질

*check
-> 특정 컬럼에 값이 들어갈 때 지정된 값만 들어갈 수 있게 지정
-> check() 로 조건 정의

 

 

*제약조건 정의
- 제약조건 이름은 생략 가능하다 -> 생략하게 되면 제약 조건 이름이 자동 생성
- 레퍼런스 -> 테이블의 어떤 컬럼을 참조한다는 것을 명시함
   ex) departments(department_id) 

- default : 값을 지정하지 않았을 때 자동으로 지정해주는 값

-- 테이블끼리 연결시 pk 와 fk 가 연결될 때 같은 데이터타입으로 해주는게 좋음


**제약조건 -> 추가, 삭제는 가능하나 수정은 불가능
- 제약조건의 추가는 add절 이용
- not null 조건은 modify 절을 사용해서 추가 가능

-----------------------------테이블의 생성과 제약조건-------------------------------
--primary key (테이블의 고유 키, 중복X, null허용X)
--unique (중복X)
--not null (null은 허용하지 않음)
--foreign key (참조하는 테이블의 pk를 저장하는 컬럼, 참조테이블의 pk에 없다면 등록X, null허용)
--check (정의된 형식만 저장되도록 허용)
--------------------------------------------------------------------------------

select * from user_constraints;
create table dept2(
    dept_no number(2)      constraint dept2_no_pk primary key,
    dept_name varchar2(15) constraint dept2_name_nn not null, 
    loca number(4)         constraint dept2_loca_locaid_fk references locations(location_id),--foreign key
    dept_date date         default sysdate, --내가 값을 지정하지 않았을 때 기본으로 지정되는 값
    dept_bonus number(10)  default 0, 
    dept_phone varchar2(20)constraint dept2_phone_uk unique,
    dept_gender char(1)    constraint dept2_gender_ck check(dept_gender in ('M', 'F'))
);

desc locations;

--열레벨 제약(constraints 생략가능)
drop table dept2;

create table dept2(
    dept_no number(2)      primary key,
    dept_name varchar2(15) not null, 
    loca number(4)         references locations(location_id),--foreign key
    dept_date date         default sysdate, --내가 값을 지정하지 않았을 때 기본으로 지정되는 값
    dept_bonus number(10)  default 0, 
    dept_phone varchar2(20)unique,
    dept_gender char(1)    check(dept_gender in ('M', 'F'))
);

--테이블 레벨(not null만 열레벨 사용)
create table dept2(
    dept_no number(2),      
    dept_name varchar2(15) not null, 
    loca number(4) ,         
    dept_date date default sysdate, --내가 값을 지정하지 않았을 때 기본으로 지정되는 값       
    dept_bonus number(10) default 0, 
    dept_phone varchar2(20),
    dept_gender char(1),    
    
    constraint dept2_no_pk primary key (dept_no/*,dept_name*/),--슈퍼키
    constraint dept2_loca_locaid_fk foreign key(loca) references locations(location_id),--foreign key
    constraint dept2_phone_uk unique (dept_phone),
    constraint dept2_gender_ck check(dept_gender in ('M', 'F'))
);


--제약조건의 위배
desc employees;

--개체 무결성 위배(null, 중복 pk에 들어가지못함)
insert into employees(employee_id, last_name, email, hire_date, job_id)
values(100, 'test', 'test', sysdate, 'test');

--참조 무결성 위배(참조테이블의 pk로 존재해야 fk로 들어갈 수 있음)
insert into employees(employee_id, last_name, email, hire_date, job_id, department_id)
values(501, 'test', 'test', sysdate, 'test',5);

--도메인 무결성 위배(컬럼에 정의된 값만 들어갈 수 있음)
insert into employees(employee_id, last_name, email, hire_date, job_id, salary)
values(501, 'test', 'test', sysdate, 'test',-10);

--------------------------------------------------------------------------------
--제약조건 추가, 삭제(변경 불가)
drop table dept2;
create table dept2(
    dept_no number(2)      ,
    dept_name varchar2(15) , 
    loca number(4)         ,
    dept_date date         default sysdate, 
    dept_bonus number(10)  default 0, 
    dept_phone varchar2(20),
    dept_gender char(1)   
);
--pk추가
alter table dept2 add constraints dept_no_pk primary key(dept_no);
--fk 추가
alter table dept2 add constraints dept_loca_fk foreign key(loca) references locations(location_id);
--unique추가
alter table dept2 add constraints dept_phone_uk unique(dept_phone);
--check 추가 -dept_gender
alter table dept2 add constraints dept_gender_ck check(dept_gender in ('M', 'F'));
--not null -컬럼 변경문으로
alter table dept2 modify dept_name varchar2(15) not null;

--제약조건 삭제(제약조건명)
alter table dept2 drop constraints dept_loca_fk;


--문제 1
create table members(
    m_name varchar2(20) constraint test1_name_nn not null,
     m_num number(5)     constraint mem_memnum_pk primary key,
    reg_date date       constraint mem_regdate_uk unique not null ,
    gender char(1)      constraint mem_gender_ck check(gender in ('M', 'F')),
    loca number         constraint mem_number_fk 
                        references locations(location_id)
);

desc members;
insert into members values('AAA', 1, '2018-07-01', 'M', 1800);
insert into members values('BBB', 2, '2018-07-02', 'F', 1900);
insert into members values('CCC', 3, '2018-07-03', 'M', 2000);
insert into members values('DDD', 4, sysdate, 'M', 2000);

select * from members order by m_num;


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

***뷰***
- 개념 잘 알아두기
- 테이블 또는 다른 뷰를 기초로 하는 논리적 테이블 -> 실제로는 존재하지 않는 가상의 테이블
- 조회를 간단하게 하도록 해줌
- 원하는 데이터를 선택, 가짜테이블을 만든 것
*단순 뷰 : 한 개의 원본테이블 사용해서 생성
*복합 뷰 : 조인을 통해 생성된 테이블에 의해 생성

*뷰를 이용한 DML 연산
- 조건이 까다롭

 

/*
뷰는 제한적인 자료만 보기 위해 사용할 수 있는 가상테이블의 개념이다
뷰는 기본테이블로 유도된 가상테이블이기 때문에 필요한 컬럼만 저장해두면 관리가 용이해진다
뷰는 가상테이블로 실제 데이터가 물리적으로 저장된 형태는 아니다
뷰를 통해서 데이터에 접근하면 원본데이터는 안전하게 보호할 수 있다.
*/

select * from emp_details_view;

--단순뷰
--뷰의 컬럼 이름은 함수같은 가상 표현식이면 안됨
create or replace view view_emp
as(select employee_id,
          first_name || ' ' || last_name as name,
          job_id,
          salary
    from employees
    where department_id = 60
    );
    
--복합 뷰
--여러 테이블을 조인하여 필요한 데이터만 저장하고 빠른 확인을 위해서 사용
create or replace view view_emp_dept_job
as(select e.employee_id,
             first_name || ' ' || last_name as name,
             d.department_name,
             e.salary,
             j.job_title
    from employees e
    left outer join departments d
    on e.department_id = d.department_id
    left outer join jobs j
    on e.job_id = j.job_id)
    order by employee_id;
    
select * from view_emp_dept_job;
   

--뷰의 수정(동일이름으로 만들면 수정된다)
create or replace view view_emp_dept_job
as (select
        e.employee_id,
        first_name || ' ' || last_name as name,
        e.salary, -- 추가
        d.department_id,
        j.job_title
     from employees e
     left outer join departments d
     on e.department_id = d.department_id
     left outer join jobs j
     on e.job_id = j.job_id)
     order by employee_id;   
    
    
select * from view_emp_dept_job;
--뷰를 이용하면 데이터를 손쉽게 조회 가능
--job_title별 salary 평균
select j.job_title,avg(e.salary)as 평균 from jobs j left join employees e on j.job_id = e.job_id group by job_title order by 평균;

select job_title, avg(salary) as 평균 from(select * from employees e left join jobs j on e.job_id = j.job_id)
group by job_title
order by 평균;

--뷰를 통한 조회
select job_title, avg(salary) as 평균
from view_emp_dept_job
group by job_title
order by 평균;

--뷰의 삭제
drop view 뷰이름;

--------------------------------------------------------------------------------
--뷰를 통한 DML은 제한이 많음
--가상열 컬럼이 있다면 허용되지 않음
insert into view_emp_dept_job(name, employee_id) values('xxx',300);
--원본 테이블의 null을 허용하지 않는 경우도 안됨
insert into view_emp_dept_job(employee_id, salary) values(300, 10000);
-- join된 뷰의 경우도 허용되지 않음
insert into view_emp_dept_job(employee_id, job_title) values(300, 'xxx');

--------------------------------------------------------------------------------
--뷰의 옵션
--with check option -조건 컬럼 제약
create or replace view view_emp_test
as(select employee_id, first_name, department_id
from employees where department_id in (60,70,80))
with check option;

select * from view_emp_test;

--with read only-읽기 전용뷰(조회만 가능)
create or replace view view_emp_test
as(select employee_id, first_name, department_id
from employees where department_id in (60,70,80))
with read only;

select * from view_emp_test;

 

 

*******************************시퀀스*******************************
- 기본 키 값을 생성하기 위해 사용
- maxvalue : 시퀀스의 최대값
- minvalue : 시퀀스의 최소값

- currval : 현재 시퀀스 값 조회*********************************
-> nextval로 하나의 값이 들어간 상태에서 사용 가능

- nextval : 다음 시퀀스 값 조회*********************************

--시퀀스(순차적으로 증가하는 값 - pk에 많이 사용된다)
select * from user_sequences;


--테이블 
create table dept3 (
    dept_no number(2) primary key,
    dept_name varchar2(20),
    loca varchar2(20),
    dept_date date
); 

--시퀀스 생성
create sequence dept3_seq
    increment by 1
    start with 1
    maxvalue 10
    minvalue 1
    nocycle
    nocache;
--시퀀스 삭제
drop sequence dept3_seq; 

--시퀀스 사용 currval, nextval
select dept3_seq.currval from dual; --currval는 nextval최소 한번 이후에 사용가능
select dept3_seq.nextval from dual;

insert into dept3(dept_no, dept_name, loca, dept_date)
values(Dept3_seq.nextval, 'test', 'test', sysdate);

select * from dept3;

--시퀀스 수정
alter sequence dept3_seq nocache;
alter sequence dept3_seq maxvalue 1000;
alter sequence dept3_seq increment by 1;

--시퀀스가 테이블에서 사용되고 있다면 drop불가 
--시퀀스 값을 초기화 하려면
--1. 현재시퀀스 확인
select dept3_seq.currval from dual;
--2. 증가값을 -현재 시퀀스
alter sequence dept3_seq minvalue 0;
alter sequence dept3_seq increment by -14;
--3. nextval로 실행
select dept3_seq.nextval from dual;
--4. 증가값을 1로 변경 
alter sequence dept3_seq increment by 1;
--5. 실행
select dept3_seq.nextval from dual;

--------------------------------------------------------------------------------
--시퀀스 사용의 응용--

create table dept4(
    dept_no varchar2(30) primary key,
    dept_name varchar2(30)
);
drop table dept4;
drop sequence dept4_seq;
create sequence dept4_seq nocache;
--lpad('값', '맥스길이', '채울값')를 이용해서 pk에 적용하는 값을 (년-0000시퀀스) 형태로 insert

insert into dept4 values(to_char(sysdate, 'YYYY')|| '-'|| lpad(dept4_seq.nextval,5,'0'),'test');
select * from dept4;
select to_char(sysdate, 'YYYY') from dual;