[본과정][20121025] 4일차 (insert, 자료형, 테이블 객체관리, 제약조건, 제약조건 추가 삭제, Primary Key, Foreign Key, Unique, Check, Default, NOT NULL)
Oracle/기본문법 2012. 10. 25. 18:00DQL(Data Query Lenguage) - 데이터 질의어, SELECT
->
DML (Data Manipulation Language) - 데이터 조작어, INSERT, UPDATE, DELETE
DDL 정의
DCL 컨트롤
INSERT
1. INSERT 문은 테이블에 새 행(row)을 추가하는데 이용하며, single table insert 나 multi table insert를 수행할 수 있다.
single table insert : 오직 하나의 테이블이나 뷰에 오직 하나의 행(row)의 값들을 삽입할 수 있다.
multi table insert : 하나 이상의 테이블로부터 스브쿼리로 얻은 여러 행(row)을 삽입하는 경우이다.
2.
insert into 테이블명 (컬럼명1, 컬럼명2, ...) values (값1, 값2, ...);
-> 컬럼명과 값은 1:1 매칭이 되어야 한다. 자료형, 순서, 갯수.
insert into 테이블 values (값1, 값2, ...);
-> 테이블에 있는 모든 컬럼을 대상으로 값을 입력하는 경우 컬럼명 부분을 생략할 수 있다.
자료형
1. VARCHAR2, NVARCHAR2 : 문자열형. 길이 지정. 최대 4000자.
2. NUMBER : 숫자형(정수, 실수). 길이 지정. 최대 38자리
3. DATE : 날짜형. 날짜, 시간이 저장됨.
NUMBER 자료형 자료 입력 테스트
CREATE TABLE test1 (
col1 number -- 38자리까지 입력가능
,col2 number(3) -- 3자리까지만 입력가능
,col3 number(5, 2) -- 5자리인데, 소수이하 2자리 입력가능. (소수이하 자리 포함해서 5자리)
);
insert into test1 (col1, col2, col3) values (123, 123, 123); -- 123, 123, 123
insert into test1 (col1, col2, col3) values (123.45, 123.45, 123.45); -- 123.45, 123, 123.45
insert into test1 (col1, col2, col3) values (123.456, 123.456, 123.456); -- 123.456, 123, 123.46
insert into test1 (col1, col2, col3) values (1234, 1234, 1234); -- 입력시도하는 정수가 크므로 아예 입력 불가
DATE 자료형 자료 입력 테스트
CREATE TABLE test2 (
col1 date --날짜, 시간 저장
);
insert into test1 (col1) values (SYSDATE); -- 2012-10-25 10:05:30
insert into test1 (col1) values ('20121025'); -- 2012-10-25 00:00:00
insert into test1 (col1) values ('2012-10-25'); -- 2012-10-25 00:00:00
insert into test1 (col1) values ('2012/10/25'); -- 2012-10-25 00:00:00
VARCHAR2, NVARCHAR2 자료형 자료 입력 테스트
CREATE TABLE test3 (
col1 VARCHAR2(1) -- 1글자만 허용.
,col2 VARCHAR2(10) -- 10글자까지 허용. 영어는 1글자, 한글은 한글자당 3글자 차지
,col3 NVARCHAR2(10) -- 10글자까지 허용. 영어, 한글 상관없이 한글자당 1글자 차지
);
insert into test3(col1, col2, col3) values ('A', 'B', 'C'); --A, B, C
SELECT * FROM test3;
insert into test3(col1, col2, col3) values ('TEST', 'TEST', 'TEST'); -- 허용 글자수 오버로 입력 불가.
insert into test3(col2, col3) values ('ORACLEDATA', 'ORACLEDATA'); -- 널, ORACLEDATA, ORACLEDATA
insert into test3(col2, col3) values ('ORACLEDATABASE', 'ORACLEDATABASE'); -- 허용 글자수 오버로 입력 불가.
insert into test3(col2, col3) values ('오라클', '오라클'); -- 널, 오라클, 오라클,
insert into test3(col2, col3) values ('오라클데이터베이스', '오라클데이터베이스'); -- 허용 글자수 오버로 입력 불가.
insert into test3(col3) values ('오라클데이터베이스'); -- 널, 널, 오라클데이터베이스
insert into test3(col2) values ('오라클데'); -- 한글 하나당 3자리 차지하므로 입력 불가.
테이블 객체 관리
1. 새 테이블 생성
create table 테이블명 (
컬럼명 자료형 [제약조건]
,...
);
2. 기존 테이블 구조 확인
desc 테이블명;
3. 기존 테이블 구조 복사해서 새 테이블 생성
create table 새테이블명
as
select 컬럼리스트 from 기존테이블명 where 1=0;
4. 기존 테이블 구조 및 자료 복사해서 새 테이블 생성
create table 새테이블명
as
select 컬럼리스트 from 기존테이블명 [where 조건식]; -- 조건식이있을 경우 조건식에 만족하는 행만 복사
-- insa 테이블 복사해서 test4 테이블 생성. 구조와 자료 전체 복사.
create table test4 as
select * from insa;
5. 기존 테이블에 새 열(column) 추가
alter table 기존테이블명
add (열이름 자료형, ....);
6. 기존 테이블에 기존 열(column) 자료형 변경
alter table 기존테이블명
modify 열이름 새로운자료형;
7. 기존 테이블에 기존 열(column) 이름 변경
alter table 기존테이블명
rename column 기존열이름 to 새열이름;
8. 기존 테이블에 기존 열(column) 삭제
alter table 기존테이블명
drop column (열이름, ....);
9. 기존 테이블의 이름 변경
rename 기존테이블명 to 새로운테이블명;
10. 기존 테이블 삭제 (휴지통 기능)
drop table 테이블명; -- 휴지통으로 이동한 상태
-- 휴지통에 있는 객체 확인.
select *
from recyclebin;
-- 휴지통에 있는 객체 복원.
flashback table 테이블명 to before drop;
-- 휴지통 비우기
purge recyclebin;
-- 완전 삭제(휴지통을 거치지 않고 바로 삭제)
drop table 테이블명 PURGE;
제약 조건
1. Primary Key(PK), Unique(UK), NOT NULL, Check(CK), Foreign Key(FK)
2. 무결성 유지를 위한 조건 추가. 개체 무결성, 참조 무결성, 도메인 무결성.
3. 제약조건 지정방법
- 컬럼레벨
CREATE TABLE 테이블명 (
컬럼명 자료형 [CONSTRAINT 제약명] 제약조건명 // [] 은 제약조건명이 제약명을 가진 객체로 오라클이 가지고 있게 하여, 추후 수정할때 사용함.
,...
);
- 테이블레벨 (권장)
CREATE TABLE 테이블명 (
컬럼명 자료형
,...
,CONSTRAINT 제약명 제약조건명 (컬럼지정)
);
4. 제약조건 확인
select * from user_constraints;
-- 특정 테이블에 있는 제약조건 확인 (제약 종류만 확인)
select * from user_constraints
where table_name = 'TEST7'; -- 테이블명 대문자로 표기
-- 특정 테이블에 있는 제약조건이 지정된 컬럼 정보 확인 (제약조건 걸린 컬럼명만 확인)
select *
from user_cons_columns
where table_name = 'TEST7';
-- 제약종류와 컬럼 정보를 동시에 확인. constraint_name 컬럼을 기준으로 join
select uc.owner, uc.table_name, column_name, constraint_type, uc.constraint_name
from user_constraints uc, user_cons_columns ucc
where uc.constraint_name = ucc.constraint_name AND uc.table_name = '테이블명';
****
-> 자주 사용하는 select 쿼리는 view 객체로 등록
create view constraint_check
as
select uc.owner, uc.table_name as table_name, column_name, constraint_type, uc.constraint_name
from user_constraints uc, user_cons_columns ucc
where uc.constraint_name = ucc.constraint_name;
-> view 객체를 이용한 제약조건 확인
select * from constraint_check
where table_name ='테이블명';
-- 특정 테이블의 NOT NULL 제약 여부 확인
DESC 테이블명;
-- DEFAULT 제약 확인
SELECT *
FROM user_tab_columns
where table_name = '테이블명';
-- 제약 확인용 VIEW 객체 수정 (CHECK 제약 확인 추가)
create or replace view constraint_check
as
select uc.owner, uc.table_name as table_name, column_name, constraint_type, uc.constraint_name, uc.search_condition
from user_constraints uc, user_cons_columns ucc
where uc.constraint_name = ucc.constraint_name;
◆ Primary Key(PK)
* 특성
- PK가 지정된 컬럼에는 중복된 데이터 입력 불가.
- 테이블에는 PK를 한번만 지정가능.
- PK가 지정된 컬럼에는 NULL 값을 허용하지 않는다.
- PK가 지정된 컬럼에는 UNIQUE INDEX가 자동 생성된다.
- PK가 지정된 컬럼이 두 개 이상인 경우는 묶어서 하나로 처리해야 한다.
-- 컬럼지정시 컬럼을 다중으로 기재하면 복합키 처리가 됨.
* 형식
CREATE TABLE 테이블명 (
컬럼명 자료형
,...
,CONSTRAINT 제약명 PRIMARY KEY (컬럼지정)
);
--PK 지정전
CREATE TABLE test6 (
col1 NUMBER
,col2 VARCHAR2(10)
);
INSERT INTO test6(col1, col2) values (1, 'hong'); -- 1, hong
INSERT INTO test6(col1, col2) values (2, 'kim'); -- 2, kim
INSERT INTO test6(col1, col2) values (2, 'park'); -- 2, park
INSERT INTO test6(col2) values ('choi'); -- NULL, choi
INSERT INTO test6(col1, col2) values (NULL, NULL); -- NULL, NULL
--PK 지정후
CREATE TABLE test7 (
col1 NUMBER
,col2 VARCHAR2(10)
,CONSTRAINT test7_col1_pk PRIMARY KEY (col1)
);
INSERT INTO test7(col1, col2) values (1, 'hong'); -- O
INSERT INTO test7(col1, col2) values (2, 'kim'); -- O
INSERT INTO test7(col1, col2) values (2, 'park'); -- X
INSERT INTO test7(col2) values ('choi'); -- X
INSERT INTO test7(col1, col2) values (NULL, NULL); -- X
◆ Unique(UK)
* 특성
- UK가 지정된 컬럼에는 중복된 데이터 입력 불가.
- 테이블에 UK를 여러번 지정 가능.
- UK가 지정된 컬럼은 NULL 값을 허용한다.
* 형식
CREATE TABLE 테이블명 (
컬럼명 자료형
,...
,CONSTRAINT 제약명 UNIQUE (컬럼지정)
);
-- UK 지정전
CREATE TABLE test8 (
col1 NUMBER -- PK 지정대상
,col2 VARCHAR2(10)
,col3 VARCHAR2(20) -- UK 지정대상
);
INSERT INTO test8 (col1, col2, col3) VALUES (1, 'kim', 'test@nate.com'); - 입력 가능
INSERT INTO test8 (col1, col2, col3) VALUES (2, 'park', 'test@nate.com'); - 입력 가능
--UK 지정 후
CREATE TABLE test9 (
col1 NUMBER
,col2 VARCHAR2(10)
,col3 VARCHAR2(20)
,CONSTRAINT test9_col1_pk PRIMARY KEY (col1)
,CONSTRAINT test9_col2_uk UNIQUE (col2)
);
INSERT INTO test8 (col1, col2, col3) VALUES (1, 'kim', 'test@nate.com'); - 입력 가능
INSERT INTO test8 (col1, col2, col3) VALUES (2, 'park', 'test@nate.com'); - 입력 불가
◆ NOT NULL 키워드
1. 특정 컬럼이 NULL 값이 가지지 못하도록 막는다. PK가 지정된 컬럼은 자동으로 NOT NULL 특성을 가진다.
2. 형식
CREATE TABLE 테이블명 (
컬럼명 자료형 NOT NULL
,...
);
-- NOT NULL 지정
CREATE TABLE test10 (
col1 VARCHAR2(10) NOT NULL --NULL 허용안함
,col2 VARCHAR2(10) -- NULL 허용
);
INSERT INTO test10 (col1, col2) VALUES ('test', 'test'); --O
INSERT INTO test10 (col1, col2) VALUES (NULL, NULL); --X
◆ DEFAULT 키워드
1. 입력시 미리 약속된 값을 자동 삽입 하는 경우. 단, 명시적으로 값을 입력하는 경우는 무시됨.
2. 형식
CREATE TABLE 테이블명 (
컬럼명 자료형 DEFAULT 값
,...
);
CREATE TABLE test11 (
col1 NUMBER
,col2 VARCHAR2(10)
,col3 DATE DEFAULT SYSDATE
);
INSERT INTO test11 (col1, col2, col3) values (1, 'hong', '20120101'); -- DEFAULT 값 무시됨
INSERT INTO test11 (col1, col2) values (2, 'kim');
INSERT INTO test11 (col1, col2) values (3, 'park', DEFAULT); -- DEFAULT값 자동 삽입.
-- DEFAULT 제약 확인
SELECT *
FROM user_tab_columns
where table_name = '테이블명';
◆ CHECK
1. 특정 컬럼에 입력되는 데이터에 대한 허용 범위를 지정할 때 사용.
2. 형식
CREATE TABLE 테이블명 (
컬럼명 자료형
,...
,CONSTRAINT 제약명 CHECK (조건식)
);
CREATE TABLE test12 (
col1 VARCHAR2(10)
,col2 NUMBER(3) --점수 입력 컬럼(0~100)
,CONSTRAINT test12_col2_ck CHECK (col2 between 0 and 100)
);
INSERT INTO test12 (col1, col2) values ('hong', 100); --O
INSERT INTO test12 (col1, col2) values ('park', 200); --X
-- 제약 확인용 VIEW 객체 수정 (CHECK 제약 확인 추가)
create or replace view constraint_check
as
select uc.owner, uc.table_name as table_name as table_name, column_name, constraint_type, uc.constraint_name, uc.search_condition
from user_constraints uc, user_cons_columns ucc
where uc.constraint_name = ucc.constraint_name;
-- NOT NULL 지정을 CHECK 제약으로 하는 방법
CREATE TABLE test13 (
col1 VARCHAR2(10) --NULL 허용안함
,col2 VARCHAR2(10) -- NULL 허용
,CONSTRAINT test13_col1_NN CHECK (col1 IS NOT NULL)
);
INSERT INTO test10 (col1, col2) VALUES ('test', 'test'); --O
INSERT INTO test10 (col1, col2) VALUES (NULL, NULL); --X
◆ FOREIGN KEY
1. 관계 설정시 사용하는 제약.
- FK가 지정된 컬럼에는 자료 입력이 참조를 하고 있는 PK 또는 UK 컬럼의 값들로 제한된다.
- FK가 지정된 컬럼에 자료입력하려면 먼저 PK 또는 UK컬럼에 해당 자료가 존재해야 한다.
- FK가 지정되고 나면 참조 당하는 PK 또는 UK 컬럼의 테이블 또는 자료는 삭제가 안된다.
2. 형식
CREATE TABLE 테이블명 (
컬럼명 자료명
,...
,CONSTRAINT 제약명 FOREIGN KEY (컬럼지정)
REFERENCES 참조테이블명(참조컬럼지정)
);
-- PK 제약 지정된 테이블
CREATE TABLE test14 (
col1 NUMBER -- PK
,col2 VARCHAR2(10)
,CONSTRAINT test14_col1_PK PRIMARY KEY (col1)
);
-- FK 제약 지정된 테이블
CREATE TABLE test15 (
col3 NUMBER -- PK
,col4 VARCHAR2(10)
,col1 NUMBER -- FK
,CONSTRAINT test15_col3_PK PRIMARY KEY (col3)
,CONSTRAINT test15_col1_FK FOREIGN KEY (col1) REFERENCES test14 (col1)
);
-- PK 자료 입력
INSERT INTO test14 (col1, col2) values (1, 'kim');
INSERT INTO test14 (col1, col2) values (2, 'park');
INSERT INTO test14 (col1, col2) values (3, 'hong');
-- FK 자료 입력
insert into test15 (col3, con4, col1) values (1, 'kim', 1);
insert into test15 (col3, con4, col1) values (2, 'park', 3);
insert into test15 (col3, con4, col1) values (3, 'hong', 4);
-- PK 자료 삭제 테스트
delete from test14
where col1 = 1; --X
delete fron test14
where col1 = 3; --O
제약 조건 관리
1. 테이블 생성 후 제약 조건 추가
ALTER TABLE 테이블명
ADD (CONSTRAINT 제약명 제약조건 (컬럼지정)
, ...);
2. 제약 조건 삭제
ALTER TABLE 테이블명
DROP CONSTRAINT 제약명;
3. 제약 조건 확인
SELECT uc.owner --소유자
, uc.constraint_name --제약명
, uc.table_name AS table_name --테이블명
, uc.constraint_type --제약조건 종류(P, R, U, C)
, uc.search_condition --CHECK 제약 조건식
, ucc.column_name --지정된 컬럼
, (SELECT table_name
FROM user_constraints
WHERE constraint_name=uc.r_constraint_name)
AS r_table_name --참조하는 테이블
, (SELECT column_name
FROM user_constraints
WHERE constraint_name=uc.r_constraint_name)
AS r_conlumn_name --참조하는 컬럼
FROM user_constraints uc, user_cons_columns ucc
WHERE uc.constraint_name = ucc.constraint_name
4. NOT NULL, DEFAULT 지정은 별도 확인
--NOT NULL 확인
DESC 테이블명;
--DEFAULT 확인
SELECT *
FROM user_tab_columns
WHERE table_name='테이블명';
문제) emp, city, buseo, jikwi 테이블에서 제약 조건 추가할 것.
아래 형태의 테이블 생성이 된 후에 제약 조건 추가 명령을 내린다고 가정함.
--city 테이블 생성
CREATE TABLE city (
city_id NUMBER --PK
, city VARCHAR2(10) --CK. 서울,충남,인천,부산,제주,전북,강원,경북,전남,경기 한정
);
--buseo 테이블 생성
CREATE TABLE buseo (
buseo_id NUMBER --PK
, buseo VARCHAR2(15) --CK. 총무부,개발부,영업부,기획부,인사부,자재부,홍보부 한정
);
--jikwi 테이블 생성
CREATE TABLE jikwi (
jikwi_id NUMBER --PK
, jikwi VARCHAR2(15) --CK. 과장,대리,부장,사원,사장,이사 한정
);
--emp 테이블 생성
CREATE TABLE emp (
num NUMBER --PK
, name VARCHAR2(20) -- NOT NULL
, ssn VARCHAR2(14) -- NOT NULL
, ibsaDate DATE
, city_id NUMBER --FK
, tel VARCHAR2(15)
, buseo_id NUMBER --FK
, jikwi_id NUMBER --FK
, basicPay NUMBER(10) --CK. 0 이상
, sudang NUMBER(10) --CK. 0 이상
);
'Oracle > 기본문법' 카테고리의 다른 글
| [본과정][20121029] 6일차(view, 인라인뷰, PL/SQL, function) (0) | 2012.11.01 |
|---|---|
| [본과정][20121026] 5일차 (시퀀스, UPDATE, SELECT) (0) | 2012.10.26 |
| [본과정][20121024] 3일차 (관계(Relationship), join, equi join, 3개이상 join, self join, outer join, 데이터베이스 객체) (0) | 2012.10.25 |
| [본과정][20121023] 2일차(서브쿼리, case~end, group by, order by, having, rank() over(), avg() over(), sum() over() ) (0) | 2012.10.25 |
| [본과정][20121022] 1일차 (0) | 2012.10.25 |
WRITTEN BY
