constraint 수업정리_2021-02-04

--정규화 Normalization
--이상현상 방지(anormaly)

select*
from employee;

select *
from department;

--삭제 옵션
--on delete restricted : 기본값. 참조하는 자식행이 있는경우, 부모행 삭제불가
                        --자식행을 먼저 삭제후, 부모행을 삭제
--on delete set null : 부모행 삭제시 자식컬럼은 null로 변경 
--on delete casecade : 부모행 삭제시 자식행 삭제

delete from shop_buy
where member_id='honggd';

delete from shop_member
where member_id='honggd';

--식별관계  / 비식별관계
--비식별관계 : 참조하고있는 부모컬럼값을 pk로 사용하지 않는경우.
--식별관계 : 참조하고있는 부모컬럼을 pk로 사용하는경우 

create table shop_nickname(
    member_id varchar2(20),
    nickname varchar2(100),
    constraints fk_member_id foreign key(member_id) references shop_member(member_id),
    constraints pk_member_id primary key(member_id)
);

insert into shop_nickname
values('sinsa','신솨112');


---------------------------------------------------------
--CHECK
---------------------------------------------------------
--해당 컬럼의 값의 범위를 지정
--null 입력 가능 

--drop table tb_cons_ck
create table tb_cons_ck(
    gender char(1),
    num number,
    constraints ck_gender check(gender in ('M','F')),
    constraints ck_num check(num between 0 and 100)
);

insert into tb_cons_ck
values('M',50);

insert into tb_cons_ck
values('F',100);

---------------------------------------------------------
--DML
---------------------------------------------------------
--Data Manipulation Language 데이터 조작어
--CRUD // R = retrieve 테이블 행에 대한 명령어
--insert
--update
--delete
--select(DQL)

---------------------------------------------------------
--insert
---------------------------------------------------------
--1. insert into 테이블 values(컬럼1값, 컬럼2값,...)모든 컬럼을 빠짐없이 순서대로 작성해야 함.
--2. insert into 테이블(컬럼1값, 컬럼2값,...)values(컬럼1값, 컬럼2값,...)
-- 컬럼을 생략가능, 컬럼순서도 자유롭다.
-- not null 컬럼이면서, 기본값이 없다명 생략이 불가하다.

create table dml_sample(
    id number,
    nick_name varchar2(100) default '홍길동',
    name, varchar2(100) not null,
    enroll_date date default sysdate not null
);

select * from deml _sample;

--타입1 (중요)

insert into dml_sample
values(100, default,'신사임당',default);

insert into dml_sample
values(100, default,'신사임당',default);

insert into dml_sample
values(100, default,'신사임당',default);

--타입2 (중요)
insert into dml_sample(id, nick_name, name, enroll_date)
values(200, '제임스','이황',sysdate);

insert into dml_sample(name, enroll_date)
values('세종',sysdate);--nullable한 컬럼은 생략가능하다, 기본값있을경우 기본값 적용

--not null이면서 기본값이 지정안된 경우 생략할 수 없다. 
insert into dml_sample (id, enroll_date)
values(300, sysdate);

insert into dml_sample (name)
values('윤봉길');

--서브쿼리를 이용한 insert

create table emp_copy
as 
select*
from employee
where 1 = 2; --table 구조만 복사해서 테이블을 생성

select * from emp_copy;

insert into emp_copy(
    select*
    from employee
);
rollback;

insert into emp_copy(emp_id, emp_name,emp_no)(
     select emp_id, emp_name, emp_no
     from employee
);

select * 
from user_cons_columns
where;

--emp_copy데이터 추가
select* from emp_copy;

--기본값 확인 data_default
select*
from user_tab_cols
where table_name = 'EMP_COPY';

--기본값 추가
alter table emp_copy
modify quit_yn default 'N'
modify hire_date default sysdate;

insert into emp_copy (emp_id,emp_name,emp_no,email,phone,dept_code,job_code,sal_level,salary,bonus,manager_id)
values (100,'홍길동','123456-7890000','naver.com','01000000000','D5','J3','S4',2520000,0.25,204);
values (150,'jay','523456-5890000','google.com','01050005000','D1','J1','S1',1520000,0.50,205);

--insert all을 이용한 여러테이블에 동시에 데이터 추가 
--서브쿼리를 이용해서 2개이상 테이블에 데이터를 추가. 조건부 추가도 가능

--입사일 관리 테이블
create table emp_hire_date
as
select emp_id, emp_name, hire_date
from employee
where 1=2;

select*from emp_hire_date

--매니져 관리 테이블 
create table emp_manager
as 
select emp_id, 
        emp_name, 
        manager_id, 
        emp_name manager_name
from employee
where 1=2;

drop data emp_manager

select*from emp_hire_date;
select*from emp_manager;

--manager_name을 null로 변경
alter table emp_manager
modify manager_name null;

--from테이블과 to테이블의 컬럼명이 같아야 한다.
insert all
into emp_hire_date values(emp_id, emp_name, hire_date)
into emp_manager values(emp_id, emp_name, manager_id, manager_name)
select E.*,
        (select emp_name from employee where emp_id = E.manager_id) manager_name
from employee E;

--insert all을 이용한 여러행 한번에 추가하기
--오라클 다음문법은 지원하지 않는다.
--insert into dml_sample 
--values (1, '치킨','홍길동',)

insert all
into dml_sample values (1, '치킨','홍길동',default)
into dml_sample values (2, '고구마','장발장',default)
into dml_sample values (3, '베베','유관순',default)
select * from dula; --더미 쿼리 

---------------------------------------------------------
--update
---------------------------------------------------------
--update실행후에 행의 수에는 변화가 없다.
--0행, 1행이상을 동시에 수정한다.
--DML 처리된 행의 수를 반환 

select * from emp_copy;

update emp_copy
set dept_code = 'D1'
where emp_id = '101';

commit; --메모리상 변경내역을 실제파일에 저장 (commit이후 rollback 안됌)
rollback; --마지막 커밋시점으로 돌리기 

update emp_copy
set salary = salary + 500000 -- +=복합대입연산자 사용불가 
where dept_code = 'D5'

--서브쿼리를 이용한 update
update emp_copy
set salary=(select salary from emp_copy where emp_name='홍길동')
where emp_name = 'jay';

--임시환 사원의 직급을 과장, 부서를 해외영업3부로 수정
--emp_copy

update emp_copy
set job_code = (select job_code from job where job_name = '과장'),
    dept_code = (select dept_id from department where dept_title = '해외영업3부')
where emp_name = '임시환';

---------------------------------------------------------
--delete
---------------------------------------------------------

select * from emp_copy;

delete from emp_copy
where emp_id= '100';

delete from emp_copy;

---------------------------------------------------------
--truncate
---------------------------------------------------------
--테이블의 행을 자르는 명령어. 
--DDL 명령어. 자동commit support
--before image생성 작업이 없으므로, 실행속도가 빠름 

truncate table emp_copy;

select * from emp_copy;

insert into emp_copy
(select * from employee);



---------------------------------------------------------
--DDL
---------------------------------------------------------
--Data definition Language 데이터 정의어
--데이터베이스 객체를 생성/수정/삭제할 수 있는 명령어 
--create
--alter
--drop
--truncate

--객체종류
--table, view, squence, index, package, procedure, function, trigger, synonym, scheduler, user....

--주석 comment
--테이블, 컬럼에 대한 주석을 달 수 있다. (필수)

select*
from user_tab_comments

select *
from user_col_comments
where table_name = 'DEPARTMENT'

select * from 

desc tbl_files;

--테이블 주석
comment on table tbl_files is '파일경로 테이블'; 

--컬럼주석 
comment on column tbl_files.fileno is '파일 고유번호';
comment on column tbl_files.fileno is '파일 경로';-- ''null과 동일

--수정/삭제 명령은 없다. 
--....is ''; --삭제 


---------------------------------------------------------
--제약조건 constraint 
---------------------------------------------------------
--테이블 생성 수정시 컬럼값에 대한 제약조건 설정할 수 있다.
--데이터의 대한 무결성(intrgrity)을 보장하기 위한 것.
--무결성은 데이터를 정확하고, 일관되게 유지하는것

/*
1. not null :  null을 허용하지 않음. 필수값
2. unique : 중복값을 허용하지 않음.
3. primary key : not null + unique 레코드식별자로써, 테이블당 하나
4. foreign key : 데이터 참조무결성 보장. 부모테이블의 데이터만 허용 
5. ckeck : 저장가능한 값의 범위/ 조건을 제한

일절 허용하지 않음. 
*/

--제약 조건 확인
--user_constraints(컬럼명이 없음)
--user_cons_columns

select *
from user_constraints
where table_name = 'EMPLOYEE';

--C check | not null
--U unique
--P primary key
--R foreign key

select*
from user_cons_columns
where table_name = 'EMPLOYEE';

--제약조건 검색
select constraint_name,
        uc.table_name,
        ucc.column_name,
        uc.constraint_type,
        uc.search_condition
from user_constraints uc
    join user_cons_columns ucc
        using(constraint_name)
where uc.table_name ='EMPLOYEE';


---------------------------------------------------------
--제약조건 constraint 
---------------------------------------------------------
--필수입력 컬럼에 not null 제약조건을 지정한다.
--default값 다음에 컬럼레벨에 작성한다.
--보통 제약조건명을 지정하지 않는다.
create table tb_cons_nn (
    id varchar2(20) not null, --컬럼레벨
    name varchar2(100)
    --테이블레벨
);

insert into tb_cons_nn values (null, '홍길동');
insert into tb_cons_nn values ('honggd, '홍길동');

select * from tb_cons_nn;
update tb_cons_nn
set id =''
where is 'honggd';

---------------------------------------------------------
--unique
---------------------------------------------------------
--이메일,주민번호, 닉네임
--전화번호는 uq사용하지 말것
--중복을 허용하지 않음
create table tb_cons_uq(
    no number not null,
    email varchar2(50),
    --테이블레벨
    constraint uq_email unique(email)
);

insert into tb_cons_uq values(1, 'abc@naver.com');
insert into tb_cons_uq values(2, '가나다@naver.com');
insert into tb_cons_uq values(3, 'abc@naver.com');

insert into tb_cons_uq values(4, null); --null허용 
select *  from tb_cons_uq;

---------------------------------------------------------
--primary key
---------------------------------------------------------
--레코드(행) 식별자 
--not null + unique기능을 가지고 있으며, 테이블당 한개만 설정가능
create table tb_cons_pk(
    id varchar2(50),
    name varchar2(100) not null,
    email varchar2(200),
    constraint pk_id primary key(id),
    constraint uq_email2 unique(email)
);

insert into tb_cons_pk
values('honggd','홍길동','hgd@google.com');

select*from tb_cons_pk;

select constraint_name,
        uc.table_name,
        ucc.column_name,
        uc.constraint_type,
        uc.search_condition
from user_constraints uc
    join user_cons_columns ucc
        using(constraint_name)
where uc.table_name ='TB_CONS_PK';

--복합 기본키(main key = primary ket / pk)
--여러컬럼을 조합해서 하나의 pk로사용
--사용된 컬럼 하나라도 unll이어서는 안된다.
create table tb_order_pk (
    user_id varchar2(50),
    order_date date,
    amount number default 1 not null,
    constraint pk_user_id_order_date primary key(user_id, order_date)
);

insert into tb_order_pk
values('honggd', sysdate,3);

select*
from tb_order_pk

---------------------------------------------------------
--foreign key
---------------------------------------------------------
--참조 무결성을 유지하기 위한 조건
--참조하고 있는 부모테이블의 지정 컬럼값 중에서만 값을 취할 수 있게 하는것
--참조하고 있는 부모테이블의 지정컬럼은 PK, UQ제약조건이 걸려있어야 한다.
--departent.dept_id(부모테이블) <-----employee.dept_code(자식테이블)
--자식테이블의 컬럼에 foreign key제약조건을 지정 

create table shop_member(
    member_id varchar2(20),
    member_name varchar2(30) not null,
    constraint pk_shop_member_id primary key(member_id)
);

insert into shop_member values('honggd','홍길동');
insert into shop_member values('sara','사라');
insert into shop_member values('min','민');

select * from shop_member;

create table shop_buy(
    buy_no number,
    member_id varchar2(20),
    product_id varhar2(50),
    buy_date date default sysdate,
    constraints pk_shop_buy_no primary key(buy_no),
    constraints fk_shop_buy_member_id foreign key(member_id)
                                        references shop_member(member_id)
);

insert into shop_buy
values(1,'honggd', 'soccer_shoes', default);

insert into shop_buy
values(2,'sara', 'basketball_shoes', default);

insert into shop_buy
values(3,'k12345', 'basketball_shoes', default);

select*from shop_buy;

select buy_no, member_id, member_name, product_id, buy_date
from shop_buy join shop_member
    using(member_id);
    
--정규화 Normalization
--이상현상 방지(anormaly)

select*
from employee;

select *
from department;

--삭제 옵션
--on delete restricted : 기본값. 참조하는 자식행이 있는경우, 부모행 삭제불가
                        --자식행을 먼저 삭제후, 부모행을 삭제
--on delete set null : 부모행 삭제시 자식컬럼은 null로 변경 
--on delete casecade : 부모행 삭제시 자식행 삭제

delete from shop_buy
where member_id='honggd';

delete from shop_member
where member_id='honggd';