DQL(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 이상

);