Oracle DB7_DDL, DML, 제약조건, EXCEL문서 SQL에 삽입하기

Goals

• CREATE TABLE, INSERT, COMMIT
• DDL, DML, 제약 조건 확인, 제약 조건, 제약조건에 이름 지정
• CHECK, Default, Foreign Key
• 제약조건 설정방법 2 - 밑에 몰아넣기, - FOREIGN KEY 만들기
• 두 개 이상의 칼럼으로 PK 만들기
• INSERT SELECT, CREATE ~ AS SELECT
• EXCEL 문서 SQL로 삽입하기
• UPDATE, ROLLBACK, DELETE

Notes

CREATE TABLE 

CREATE TABLE 테이블이름 ( 

컬럼1 데이터타입 CONSTRAINT 제약조건이름 제약조건내용-[NOT NULL UNIQUE PRIMARY KEY]제약조건만드는방법1, 

컬럼2 데이터타입 [NOT NULL UNIQUE PRIMARY KEY], 

..., 

[CONSTRAINT 제약조건이름 제약조건내용(컬럼이름)] 제약조건만드는방법2

); 

[ ]  : 선택사항 

➢ 테이블 객체를 생성 

➢ 데이터 타입 

○ NUMBER, VARCHAR2, DATE, TIMESTAMP, CLOB, BLOB 등 

➢ 제약 조건 

○ NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY 

○ CHECK

ex)  create table ex1 (

    ex_id number,

    ex_text varchar2(10),

    ex_date date

);

INSERT (1) 

➢ 테이블의 컬럼 순서대로 해당 컬럼 값을 입력함 

SQL> INSERT INTO member VALUES (‘guest’, ‘guest1234’, ‘guest@test.com’); 

INSERT INTO 테이블이름 (컬럼1, 컬럼2, 컬럼3, … ) 

VALUES (값1, 값2, 값3, … ); 

insert into ex1(ex_id, ex_text, ex_date)

values(1,'Hello',sysdate);

➢ 데이터를 입력할 테이블, 해당 컬럼, 값들을 순서대로 기술하는 형태 

SQL> INSERT INTO member (id, pw) VALUES (‘guest’, ‘guest1234’);

※ 테이블의 모든 컬럼에 값을 저장할 때

- insert into 구문에서 컬럼 이름을 생략할 수 있다. 단, 테이블 컬럼 순서를 준수해야 한다.

insert into ex1

values(11,'data',sysdate);

COMMIT 명령어 -- 변경된 데이터를 DBMS에 영구 저장

>> commit;

또는 아래 아이콘 클릭

DDL (Data Definition Language) : 데이터 정의 언어

CREATE, ALTER, DROP, TRUNCATE ( 테이블, 사용자 )

DDL 명령은 실행이 되면 자동으로 Commit 됨.

DML (Data Manipulation Language) : 데이터 조작 언어

INSERT, UPDATE, DELETE ( 행 , 레코드를 조작 )

DB에 접속한 사용자가 commit 명령을 수행해야만 DMS에 영구적으로 반영됨!

제약 조건(Constratint) 확인

select constraint_name, constraint_type, table_name

from user_constraints;

R : Relation : Foreign key

P : Primary Key

U : Unique

C : Not null

제약 조건

not null : 

null 값을 허용하지 않는 조건

unique : 

중복된 값이 저장될 수 없음

Primary Key : 

고유 키 : 테이블에서 유일한 한 개의 행(레코드)을 검색할 수 있는 컬럼

Foreign Key : 

외래키. 외부키. 관계를 맺고 있는 다른 테이블의 PK.

Check : 

조건(Condition)을 체크하는 제약 조건.

Default :

컬럼의 기본값(insert를 하지 않아도 자동으로 설정되는 값)을 설정. 

테이블 생성시 제약조건에 이름을 주는 방법

CONSTRAINT 제약조건 이름 제약조건

EX) create table ex04(

    컬럼1 데이터타입 col1 number constraint ex04_unq unique,

    컬럼1 데이터타입 col2 varchar2(20) constraint ex04_nn not null

);

CHECK 제약 조건

create table ex06 (

    col varchar2(20) constraint check(length(col)>=8)

);

Default

>> 컬럼1 데이터타입 default 디폴트값

create table ex07 (

    ex_id number default 0,

    ex_date date default sysdate

);

Foreign Key

create table ex_dept(

    deptno number constraint ex_dptno_PK primary key,

    dname varchar2(100)

);

>> Constraint 제약조건이름 REFERENCES 테이블명(칼럼명)

create table ex_emp(

    empno number                                            constraint pk_ex_emp primary key,

    ename varchar2(20),

    deptno number                                            constraint fk_ex_dept references ex_dept(deptno)

);

>> ex_emp에는 ex_dept 테이블에 생성돼 있지 않은 부서 번호는 insert 할 수 없다 !

제약조건 설정방법 2 ___ NOT NULL 사용할 수 없음

제약조건 밑에 몰아넣기.

CREATE TABLE 테이블이름 ( 

컬럼1 데이터타입 CONSTRAINT 제약조건이름 제약조건내용

컬럼2 데이터타입 [NOT NULL UNIQUE PRIMARY KEY], 

..., 

CONSTRAINT 제약조건이름 제약조건내용(컬럼이름),

CONSTRAINT 제약조건이름 제약조건내용(컬럼이름),

... 

); 

create table ex08 (

    col1 number,

    col2 number,

    constraint pk_ex08 primary key(col1),

    constraint uq_ex08 unique (col2) 

);

>> Foreign Key 만들기

create table ex_emp2(

    empno number constraint pk_ex_emp2 primary key

    ename varchar2(20),

    deptno number,    

    constraint fk_ex_dept2 foreign key (deptno) references ex_dept (deptno)

    

);

두 개 이상의 칼럼으로 PK 만들기

 >> 방법1로 생성 불가능

create table ex08(

    col1 number primary key,

    col2 varchar2(20) primary key,

    constraint

);

>> 방법2로 생성

create table ex09(

    col1 number,

    col2 varchar2(20),

    constraint  pk_ex09 primary key (col1, col2)

);

>> 확인

INSERT into ex09 values (1,'a');    --가능

INSERT into ex09 values (1,'b');    --가능

INSERT into ex09 values (2,'a');    --가능

INSERT into ex09 values (1,null);   --null 삽입 불가능

• INSERT SELECT  다른 컬럼에서 SELECT한 내용을 테이블에 INSERT하는 방법

INSERT INTO 테이블1 (컬럼1, 컬럼2, ...) 

SELECT 컬럼2_1, 컬럼2_2, ... FROM 테이블2 WHERE 조건;

EX)

INSERT INTO EX_EMP2 (EMPNO, ename, deptno)

        SELECT empno, ename, deptno FROM EMP;

• CREATE ~ AS SELECT 구문 : 테이블 복사

CREATE TABLE 테이블명

AS (SELECT * FROM 복사할 테이블명);

EX)

CREATE TABLE EX_EMP3

AS (SELECT * FROM EMP

WHERE DEPTNO = 10 ) ;

--> WHERE절, SELECT절에 조건 추가 가능

테이블 >> 데이터 임포트 >> 로컬파일 선택 후 따라가면 된다

UPDATE

UPDATE 테이블이름 

SET 컬럼1 = 값1, 컬럼2 = 값2, …              // '=' 표시로 변경

WHERE 조건;

EX)

UPDATE EX_EMP3

SET SAL = 2000

WHERE EMPNO = 7369;

update customers

set customer_email = 'portal@cau.ac.kr'

where customer_email is null;

※ 주의 : UPDATE에서 WHERE 절을 생략하면 테이블의 모든 행을 수정하게 됨!

ROLLBACK  : 가장 최근의 COMMIT 상태로 돌아가는 것. 바로 이전으로 돌아가는 것 아님!

>> ROLLBACK;

DELETE : 조건에 맞는 테이블 내용을 삭제

>> DELETE FROM 테이블 WHERE 조건;

EX) DELETE FROM EX_EMP3 where deptno = 10;

delete from emp2

where sal between (select losal from salgrade2  where grade=5)

            and   (select hisal from salgrade2 where grade=5);

create table customers (                                                          //NOT NULL, DEFAULT를 윗부분에 써줄 것

    customer_id              varchar2(20),                             

    customer_pw             varchar2(20)      constraint nn_pw not null,                               //NOT NULL은 위에 쓸 것

    customer_email         varchar2(100),

    customer_gender      number(1),  

    customer_age            number(3) default 0,                               //default는 이름 지정하지 않음

     

     constraint cust_id_chk      check(length(customer_id)>=8),    //같은 컬럼에 대한 제약이라도 매 줄마다 ','(콤마)로 구분해야 함                   constraint pk_cst               primary key(customer_id),                                   

     constraint cust_pw_chk     check(length(customer_pw)>=8),                                

     constraint gen_cst             check (customer_gender in (0,1,2)),

     constraint age_lmit            check(customer_age<200)

);

create table customers (

    customer_id             varchar2(20)            constraint cust_id_chk     check(length(customer_id)>=8)

                                                                       constraint pk_cst             primary key,

    customer_pw            varchar2(20)           constraint cust_pw_chk     check(length(customer_pw)>=8)  

                                                                                                                 not null,

    customer_email        varchar2(100),

    customer_gender      number(1)              constraint gen_cst            check (customer_gender in (0,1,2)),

    customer_age           number(3)                                                        default 0  --> Default를 Constraint보다 앞에 위치

                                                                       constraint age_lmit          check(customer_age<200)

);