데이터베이스 조작, 제약조건
90번 부서에 근무하는 사원의 이름과 급여를 저장하는 dept90이라는 테이블을 생성하시오
1. create table dept90 (last_name char(5), salary number(5));
2. select last_name, salary from employees where department_id=90;
3. insert into
4. commit;
-> 이를 서브쿼리를 통해 간략하게 가능하다
create table dept90 as select last_name, salary from employees
where department_id=90;
-> 서브쿼리의 결과를 table화
column_name, 구조(데이터타입, 길이)를 테이블과 같게
부서별 급여의 총합을 저장하는 deptsum이라는 테이블을 생성하시오
부서는 deptno로, 급여총합은 sumsal로 표현하시오
create table deptsum as select department_id deptno, sum(salary) sumsal
from employees group by department_id;
데이터를 가져와서 만들긴 하지만 기존테이블과 독립적이기 때문에 수정시 서브쿼리가 관계없는 테이블이 되어버린다.
-alter table
새열 추가
기존 열 수정 -> 데이터 타입변경, 길이를 키우거나 줄일때
컬럼을 삭제
-add 컬럼을 추가할때
-modify 컬럼을 수정할때(데이터 타입을 변경하거나 길이를 변경)
-drop 데이터 삭제뿐만아니라 데이터 딕셔너리 정보도 삭제가 이루어지고, 저장공간도 회수를 한다.
alter table dept
2 add(job_id varchar2(15)); -> job_id는 null값이 나온다
modify로 데이터타입변경, 길이를 키우거나 줄이거나 할수있다.
데이터가 없을경우 데이터타입변경, 길이를 키우거나 줄이거나 할수있다.
데이터가 있을경우 데이터타입변경 불가 길이를 줄일때는 기존 데이터보다 커야한다.
DDL명령어는 table전체에 lock이 걸린다. -> 동시성이 떨어진다.
drop은 저장공간을 회수하는데 시간이 너무 오래걸려서 동시성이 떨어진다.
-> set unused(컬럼명); 삭제가 빨라서 동시성이 보장되지만, 데이터와 저장했던 공간이 회수되지 않고 남아있다
-> drop unused columns
truncate -> delete from where에서 where절 없이 삭제하는 경우랑 같다
delete DML data를 삭제 저장했던 공간이 남아있다. rollback으로 복구가능
(where절 있으므로 -> 특정 행 삭제에 주로쓴다)
truncate DDL data를 삭제 저장한 공간만 회수한다. (table구조 정보와 공간은 남아있다.) 실행과 동시에 auto commit으로 백업본 없으면 복구불가
(전체삭제, table의 초기화 처음 테이블을 만들었을때로 돌아간다)
drop DDL data와 data dictionary의 정보도 같이 삭제한다. (모두날아감) 실행과 동시에 auto commit으로 백업본 없으면 복구불가
여태 만들었던 테이블들은 PK와 같은 제약조건이 빠져있는 테이블이여서 문제점을 가지고있다.
--10장 제약조건: 테이블에 정확한 data를 저장하기 위한 조건: 무결성 보장을 위한 제약 조건
PK,FK -> 테이블이 아니라 테이블을 구성하고있는 컬럼마다 제약조건을 설정한다.
(컬럼마다 특성이 다르기 때문에 테이블 전체에 제약조건을 설정하지 않는다.)
-not null : 반드시 값이 있어야 한다
-unique : 중복된 값을 허용하지 않는다, null값은 허용
-primary key : not null+unique
-foreign key : 다른 테이블에 있는 컬럼을 참조할때 설정, 관계, 참조대상인 컬럼이 PK or unique의 조건이 있어야하고 /참조대상과 데이터 타입, 길이는 같아야한다.
-check : 사용자 정의조건 ex) salary>0 ->salary에는 0보다 큰값만 들어간다.
제약조건은 하나의 컬럼에 여러 제약조건을 동시에 부여가능
-> 따로 제약조건의 이름을 설정하지 않으면 오라클에서 SYS_Cn형식의 이름을 부여하니까 알기쉬운 네이밍을 하는게 좋다.
제약조건의 생성시기
-> 테이블이 생성될때 create table 시에 같이 제약조건을 생성 (주로사용: 데이터의 무결성을 유지하기위해)
테이블이 생성된 후에 alter table 시에 추가로 제약조건 생성
dept테이블의 did,dname
num(3) char(5)
pk
제약조건 선언시 (create절의 컬럼명+구조 뒤에 ,를 꼭 써주고 제약조건 선언해야한다)
constraint 제약조건이름 제약조건의유형(설치할 컬럼명) 제약조건이름은 주로: table_column_제약조건
제약조건 선언 ex) pk ex) dept_did_pk
create table dept(did number(3),dname char(5),
constraint dept_did_pk primary key(did));
-> 컬럼을 선언후에 제약조건을 선언하는 테이블 level에서의 선언방법
-> 하지만 not null은 컬럼선언 뒤에 컬럼 level에서 선언해야만한다.
did number(3) not null : 그래서 not null은 제약조건의 이름을 쓰지않고 컬럼선언 바로 뒤에한다.
(constraint+제약조건이름 -> 생략가능)
하나의 테이블에 하나의 PK를 지정하는게 가장 이상적(데이터 무결성을 위해)
하지만 복합PK가 있는데
여러 컬럼을 합해서 행단위로 중복을 체크(컬럼별로는 중복을 허용)
PK -> 유일성(중복허용X) , 최소성(컬럼이 1개)
복합PK(슈퍼키) -> 유일성O(여러컬럼의 행별로 중복을 검사) , 최소성만족X
references 를 동해서 fk가 참조할 곳을 설정해준다
references+테이블명(컬럼명)으로
references 절만 쓰면 fk가 참조하는 pk키의 종속적인 삭제를 막는 현상이 일어난다
on delete cascade 명령어를 쓰면 fk가 참조하는 pk의 데이터 행이 지워지면서 fk의 데이터 행도 같이 지워진다
on delete set null 명령어를 쓰면 fk가 참조하는 pk의 데이터 행이 지워지면서 fk의 데이터만 같이 지워지고 null값이 들어간다
create table test(a number(3),b number(5) not null,c number(5),
constraint test_a_pk primary key(a));
create table test1(
d number(5),
e number(5) not null,
f number(5),
a number(3),
constraint test1_d_pk primary key(d), constraint test1_f_ck check(f in(0,1)),
constraint test1_a_fk foreign key(a) references test(a));
스크립트파일 만들기 & 사용
.sql 파일을 스크립트파일이라고한다.
Created file test.sql로 스크립트 파일을 만들고
save 파일명.sql
여러 명령문을 저장하고 원할때 @test.sql 로 불러와서 사용할수있다.
스크립트파일 처음엔 drop table 컬럼명 으로 저장해놓은것과 겹치지않게 제거해준다.
column salary format a7; ---> a는 문자와 날짜를 바꿀때만사용
이렇게 숫자를 잘못넣어서 오류나면 clear로 해결.
column salary format 99999; -> 9하나가 숫자자리수 하나를 의미.