--정규화 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';
