210915(수) DB 7일차 - 제약조건 / 삭제, 추가, 수정, 복사 / 시퀀스
ex7) group by rollup : a, b별 집계(Subtotal 구하기)
부서별, 직무ID별 급여평균구하기(동일부서에 대한 직무별 평균급여)
조건1) 반올림해서 소수 2째자리까지 구하시오
조건2) 제목은 Job_title, Department_name, Avg_sal로 표시하시오
select department_name, job_title, round(avg(salary), 2) as "Avg_sal"
from employees
join departments using(department_id)
join jobs using(job_id)
group by rollup(department_name, job_title);
ex8) group by cube : a별 집계 또는 b별 집계
부서별, 직무ID별 급여평균구하기(부서를 기준으로 나타내는 평균급여)
select department_name, job_title, round(avg(salary), 2) as "Avg_sal"
from employees
join departments using(department_id)
join jobs using(job_id)
group by cube(department_name, job_title);
-> 업무명으로 나눈후, 또 따로 부서명으로 나눈다.
ex9) group by grouping sets
직무별 평균급여와 전체사원의 평균급여를 함께 구하시오 : 섞여나온다
select job_title, round(avg(salary), 2) as "Avg_sal"
from employees
join departments using(department_id)
join jobs using(job_id)
group by grouping sets((job_title),()); ← ( ) : All Rows의 역할
<테이블생성 , 제약조건>
create table 테이블명(컬럼명1 컬럼타입 [제약조건],
컬럼명2 컬럼타입 [제약조건],.....);
- 문자로 시작(30자이내) : 영문 대소문자, 숫자, 특수문자( _ , $ , # ), 한글
cf) 한글 가능~!!
- 중복되는 이름은 사용 안됨
- 예약어(create, table, column등)은 사용할 수 없다
- 자료형
number : number(전체자리, 소수이하), number → 숫자형(가변형)
int : 정수형 숫자(고정형)
varchar/varchar2 : 문자, 문자열(가변형) → 최대 4000byte => 적으면 clob로 쓴다!!
char : 문자, 문자열(고정형) → 2000byte
date : 날짜형
clob : 문자열 → 최대4GB 양많다!!
blob : 바이너리형(그림, 음악, 동영상..) → 최대4GB =>올리지말라~~~!!! 너무 과부하걸림, 파일명만 올린다!
- 제약조건
not null : 해당 컬럼에 NULL을 포함되지 않도록 함 (컬럼)
unique : 해당 컬럼 또는 컬럼 조합 값이 유일하도록 함 (컬럼, 테이블)
primary key : 각 행을 유일하게 식별할 수 있도록 함 (컬럼, 테이블)
=> 주키, 기본키
references table(column) :
해당 컬럼이 참조하고 있는 (부모)테이블의 특정 (컬럼, 테이블) 컬럼 값들과
일치하거나 또는 NULL이 되도록 보장함
check : 해당 컬럼에 특정 조건을 항상 만족시키도록 함 (컬럼, 테이블)
[참고] primary key = unique + not null
- 제약조건 확인
constraint_name:이름
constraint_type:유형
p : primary key
u : unique
r : reference
c : check, not null
search_condition : check조건 내용
r_constraint_name : 참조테이블의 primary key 이름
delete_rule : 참조테이블의 primary key 컬럼이 삭제될 때 적용되는 규칙
(no action, set null, cascade등)
ex) idx 일련번호 primary key
id 아이디 unique
name 이름 not null
phone 전화번호
address 주소
score 점수 check
cf) 체크: 범위 주고 이상한건 저장안하겠다!
subject_code 과목코드
hire_date 입학일 기본값(오늘날짜)
marriage 결혼 check
ex1) 테이블 : test
create table test(
id number(5),
name char(10),
address varchar2(50));
ex2) 테이블 : user1
create table user1(
idx number primary key,
id varchar2(10) unique,
name varchar2(10) not null,
phone varchar2(15),
address varchar2(50),
score number(6,2) check(score >=0 and score <= 100),
subject_code number(5),
hire_date date default sysdate,
marriage char(1) default 'N' check(marriage in('Y','N')));
ex3) 제약조건 확인 => 근데 ex2, ex3처럼 하기 보다는 ex4 처럼 제약조건에 이름을 준다!
select constraint_name, constraint_type
cf) 오라클은 컬럼명을 인식하지 못하고 다른 영문자숫자코드로 안다!
from user_constraints
where table_name='USER1';
select *
from user_constraints
where table_name='USER1';
ex4) 테이블
create table user2(
idx number constraint PKIDX primary key,
id varchar2(10) constraint UNID unique,
name varchar2(10) constraint NOTNAME not null,
phone varchar2(15),
address varchar2(50),
score number(6,2) constraint CKSCORE check(score >=0 and score <= 100),
subject_code number(5),
hire_date date default sysdate,
marriage char(1) default 'N' constraint CKMARR check(marriage in('Y','N')));
ex5) 제약조건 확인
select constraint_name, constraint_type
from user_constraints
where table_name='USER2';
select *
from user_constraints
where table_name='USER2';
ex6) 추가
insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(1,'aaa','kim','010-000-0000','서울',75,100,'2010-08-01','Y');
insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(1,'aaa','kim','010-000-0000','서울',75,100,'2010-08-01','Y');
→ 무결성제약조건에 위배(이유: idx 1 중복) -> idx 주키니까?
insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(2,'aaa','kim','010-000-0000','서울',75,100,'2010-08-01','Y');
→ 무결성제약조건에 위배(이유: id aaa 중복) -> id 유니크니까
insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(2,'bbb','','010-000-0000','서울',75,100,'2010-08-01','Y');
→ NULL을 ("HR"."USER1"."NAME") 안에 삽입할 수 없습니다 -> not null 이니까
insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(2,'bbb','lee','010-000-0000','서울',120,100,'2010-08-01','Y');
→ 체크 제약조건에 위배되었습니다(이유: score가 0~100사이의 수 이어야함) -> 체크 제약조건 범위 벗어남
insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(2,'bbb','lee','010-000-0000','서울',75,100,'2010-08-01','K');
→ 체크 제약조건에 위배되었습니다(이유:marriage가 Y 또는 N이어야함) -> 체크 제약조건 범위 벗어남
insert into user1(idx,id,name,phone,address,score,subject_code,hire_date,marriage)
values(2,'bbb','lee','010-000-0000','서울',75,100,'2010-08-01','N');
<삭제, 추가, 수정, 복사>
- 삭제 RULE
on delete cascade
: 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터도 삭제
on delete set null
: 대상 데이터를 삭제하고, 해당 데이터를 참조하는 데이터는 NULL로 바꿈
on delete restricted
: 삭제대상 데이터를 참조하는 데이터가 존재하면 삭제할 수 없음 (기본값)
- 수정 RULE
on update cascade
: 대상 데이터를 수정하면, 해당 데이터를 참조하는 데이터도 수정
[테이블수정]
- 구문 -
alter table 테이블명
add 컬럼명 데이터타입 [제약조건]
add constraint 제약조건명 제약조건타입(컬럼명)
modify 컬럼명 데이터타입
drop column 컬럼명 [cascade constraints]
drop primary key [cascade] | union (컬럼명,.....) [cascade] .... | constraint 제약조건명 [cascade]
- 이름 변경 -
alter table 기존테이블명 rename to 새테이블명
alter table 테이블명 rename column 기존컬럼명 to 새컬럼명
alter table 테이블명 rename constraint 기존제약조건명 to 새제약조건명
[테이블복사]
- 서브쿼리를 이용한 테이블 생성및 행(레코드) 복사
- 서브쿼리를 이용해서 복사한경우 not null을 제외한 제약조건은 복사 안됨
(not null제약조건도 sys_*****로 복사됨)
- 구문 -
create table 테이블명([컬럼명1,컬럼명2.....]) as 서브쿼리
- 구조만 복사 -
create table 테이블명1 as select * from 테이블명2 where 1=0
[ insert ]
: 테이블에 데이터(새로운행)추가
insert into 테이블명 [ (column1, column2, .....)] values (value1,value2,.....)
- column과 values의 순서일치
- column과 values의 개수 일치
[ update ]
: 테이블에 포함된 기존 데이터수정
전체 데이터 건수(행수)는 달라지지 않음
조건에 맞는 행(또는 열)의 컬럼값을 갱신할 수 있다
update 테이블명 set 컬럼명1=value1, 컬럼명2=value2 ..... [where 조건절]
- where 이 생략이 되면 전체행이 갱신
- set절은 서브쿼리 사용가능, default옵션 사용가능
[ delete ]
: 테이블에 포함된 기존데이터를 삭제
행 단위로 삭제되므로 전체행수가 달라짐
delete [from] 테이블명 [where 조건절];
- where을 생략하면 전체행이 삭제됨
- 데이터는 삭제되고 테이블 구조는 유지됨
[ truncate ]
: 테이블의 데이터를 전부 삭제하고 사용하고 있던 공간을 반납
해당 테이블의 데이터가 모두 삭제되지만 테이블 자체가 지워지는 것은 아님
해당 테이블에 생성되어 있던 인덱스도 함께 truncate 됨
TRUNCATE TABLE 테이블명;
DELETE 후에는 데이터만 지워지고 쓰고 있던 디스크 상의 공간은 그대로 가지고 있다.
TRUNCATE 작업은 최초 테이블이 만들어졌던 상태, 즉 데이터가 1건도 없는 상태로 모든 데이터 삭제, 칼럼 값만 남아 있다.
그리고 용량도 줄고 인덱스 등도 모두 삭제된다.
→ DELETE보다 TRUNCATE가 더 좋아 보이나 DELETE는 where를 이용하여 원하는 데이터만 골라서 삭제가 가능하나 TRUNCATE는 조건을 사용할 수 없다. 또 DDL이기 때문에 사용권한 문제도 있다.
DROP 명령어는 데이터와 테이블 전체를 삭제하게 되고
사용하고 있던 공간도 모두 반납하고 인덱스나 제약조건 등 오브젝트로 삭제 된다
[ transaction처리 ]
: 일의 시작과 끝이 완벽하게 마무리(commit)
처리도중 인터럽트(interrupt:장애)가 발생하면 되돌아옴(rollback)
ex7) 테이블 목록 확인
select * from tab;
ex8) 테이블의 내용확인
select * from user1;
select * from user2;
ex9) 테이블의 구조 확인
desc user1; (= describe user1) : not null 만 보여줘서 그다지 효용이 없다!
ex10) 시퀀스 목록 확인
select * from user_sequences;
ex11) 테이블명 변경 : test → user3
alter table test rename to user3;
ex12) 컬럼 추가 : user3 → phone varchar2(15)
alter table user3 add phone varchar2(15);
desc user3;
ex13) 제약조건 추가 : user3 → id에 unique, 제약조건명 UID2
alter table user3 add constraint UID2 unique(id);
select constraint_name, constraint_type
from user_constraints
where table_name='USER3'; => 걸기 전엔 아무것도 없다!
ex14) 제약조건 삭제 - alter table 테이블명 drop constraint 제약조건명;
alter table user3 drop constraint UID2;
alter table user3 DROP constraint SYS_C007693;
select *
from user_constraints
where table_name='USER3';
ex15) 컬럼 추가 : user3 → no number (PK 설정)
alter table user3 add no number primary key;
desc user3;
ex16) 구조 변경 : user3 → name char(10) 를 varchar2(10)로 바꿈
alter table user3 modify name varchar2(10);
desc user3;
ex17) 컬럼 삭제 : user3 → address
alter table user3 drop column address;
desc user3;
ex18) 테이블삭제 / 휴지통비우기 purge: 바로삭제 / purge recyclebin
drop table user3;
purge recyclebin; → 휴지통 비우기
drop table user1 purge; → 휴지통에 넣지 않고 바로 삭제
drop table user2;
show recyclebin; -> 휴지통 보기
flashback table user2 to before drop; → 휴지통에서 되살리기
flashback table "BIN$cEf2dC1fRhilpiULWNRf3A==$0" to before drop; -> 그냥 휴지이름을 써서 살릴 수도 있다는 의미
select * from recyclebin; → 휴지통에 테이블 정보 검색
<디벨로퍼에서 테이블 들어가서 직접 수정 가능!!>
<시퀀스>
: 시퀀스란 자동으로 순차적으로 증가하는 순번을 반환하는 데이터베이스 객체
시퀀스는 오라클에만 있음.
증가를 쉽게?!,
순차적으로 정수값을 자동으로 생성하는 객체
create sequence 시퀀스명
[increment by 증가값] [start with 시작값]
[maxvalue 최대값 | minvalue 최소값]
[cycle | nocycle]
[cache | nocache]
- increment by 증가값 : 증가/감소 간격(기본값 : 1)
- start with : 시작번호(기본값 : 1)
- maxvalue / minvalue : 시퀀스의 최대/최소값지정
- cycle/nocycle : 최대/최소값에 도달 시 반복여부결정
- cache / nocache : 지정한 수량만큼 메모리 생성여부결정 (최소값:2, 기본값:20)
ex19) 시퀀스 생성 / 삭제
create sequence idx_sql increment by 2 start with 1 maxvalue 9 cycle nocache;
select idx_sql.nextval from dual; → 다음 시퀀스값 표시(nextval)
select idx_sql.currval from dual; → 현재 시퀀스값 표시(currtval)
select * from user_sequences;
drop sequence idx_sql; -> 제거, 시퀀스 삭제시에는 휴지조각같은거 안생김
ex20) 테이블 생성과 시퀀스 적용
create table book(
no number primary key,
subject varchar2(50),
price number,
year date);
create sequence no_seq increment by 1 start with 1 nocycle nocache; 1씩증가
insert into book(no,subject,price,year)
values(no_seq.nextval, '오라클 무작정 따라하기', 10000, sysdate);
insert into book(no,subject,price,year)
values(no_seq.nextval, '자바 3일 완성', 15000, '2007-03-01');
insert into book values(no_seq.nextval, 'JSP 달인 되기', 18000, '2010-01-01');
select * from book;
<삭제, 추가, 수정, 복사 ++>
ex21) 테이블 구조만 복사 ← not null을 제외하고는 제약조건이 복사 안됨 (not null 만 복사됨!!)
← not null 제약조건도 sys_*****로 복사됨 (not null 조차도 제약조건명 그대로 복사가 안된다)
create table user3 as select * from user2 where 1=0;
desc user3;
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name='USER3';
ex22) 테이블(idx → bunho, name → irum, address → juso) 을 복사하고 id가 bbb인 레코드를 복사하시오
원본테이블 : user1 / 사본테이블 : user4
create table user4(bunho, irum, juso)
as select idx, name, address from user1 where id='bbb';
select * from user4;
--- 여기아래로 중요★ ---
기본키(프라이머리키)는 테이블당 1개
=> 프라이머리키에만 외래키로 참조가능
ex23) 테이블 생성 후 행 추가
테이블명 : dept
deptno number → 기본키, 제약조건명(DNO)
dname varcahr2(30) → 널 허용 안됨, 제약조건명(DNAME)
테이블명 : emp
empno number → 기본키, 제약조건명(ENO)
ename varchar2(30) → 널 허용 안됨, 제약조건명(ENAME)
deptno number → 외래키, 제약조건명(FKNO),
대상데이터를 삭제하고 참조하는 데이터는 NULL로 바꿈
create table dept(
deptno number constraint DNO primary key,
dname varchar2(30) constraint DNAME not null);
create table emp(
empno number constraint ENO primary key,
ename varchar2(30) constraint ENAME not null,
deptno number,
constraint FKNO foreign key(deptno) references dept on delete set null);
insert into dept(deptno, dname) values(10, '개발부');
insert into dept(deptno, dname) values(20, '영업부');
insert into dept(deptno, dname) values(30, '관리부');
insert into dept(dname) values(40, '경리부'); ->컬럼명이 하나인데 값을 두개 넣었으니까 에러
→ ORA-00913: 값의 수가 너무 많습니다
insert into dept(deptno, dname) values(40, '경리부');
insert into emp(empno, ename, deptno) values(100, '홍길동', 10);
insert into emp(empno, ename, deptno) values(101, '라이언', 20);
insert into emp(empno, ename, deptno) values(102, '튜브', 50); -> 50번이 없으니까
→ 50번부서 없음(무결성제약조건위배)-부모키가 없습니다
insert into emp(empno, ename, deptno) values(103, '어피치', 40);
insert into emp(empno, ename) values(105, '프로도');
insert into emp(ename, deptno) values('콘', 10); → primary key는 NULL허용 안함
commit;
ex24) 삭제
delete from dept;
select * from dept;
rollback; -> 되살림
select * from dept;
delete from dept where deptno=40;
select * from dept; -- 40번 부서 삭제
select * from emp; -- 40번 부서 컬럼에 (null)이 들어간다
ex25) 수정(update)
emp테이블 장동건 사원의 부서번호를 30으로 수정하시오
update emp set deptno=30 where ename='장동건';
select * from emp;
commit;