[SQL] DDL, DML

DDL: 테이블 구조 생성, 변경 및 삭제

DML: 테이블 내용 추가, 수정, 삭제

DDL

//CREATE

CREATE TABLE 테이블

( 컬럼1 number ,

컬럼2 varchar2(10) ,

컬럼3 date ) ;

//데이터 형

CHAR(size): 고정 길이 문자 데이터

VARCHAR2(size):      가변 길이 문자 데이터.Up to 2000 Bytes

LONG 가변 길이의 문자형 데이터 타입, 최대 크기는 2GB

LOB 2GB까지의 가변 길이 바이너리 데이터를 저장.이미지 문서, 실행

파일을 저장

NUMBER 최고 40자리까지의 숫자를 저장

NUMBER(w) W자리까지의 수치로 최대 38자리까지 가능

NUMBER(w, d) W는 전체 길이, d는 소수점 이하 자릿수

//CREATE TABLE + AS

(1)전체 복사

CREATE TABLE EMP02

AS

SELECT * FROM EMP;

(2)부분 복사(가능)

CREATE TABLE EMP02

AS

SELECT EMPNO, ENAME, SAL FROM EMP;

(3)조건 사용 가능

CREATE TABLE EMP02

AS

SELECT * FROM EMP WHERE DEPTNO = 30;

(4)구조만 있는 빈 테이블 만들기_WHERE 거짓 조건

CREATE TABLE EMP02

AS

SELECT * FROM EMP WHERE 1=0;

EMP 테이블과 동일한 내용과 구조를 갖는 EMP02 테이블 생성

주의!) EMP 제약조건은 가져오지 않는다

//ALTER TABLE

테이블에 대한 구조 변경은 컬럼의 추가, 삭제, 컬럼의 타입이나 길이를 변경할 때 사용

ADD COLUMN 새로운 칼럼을 추가

MODIFY COLUMN 기존 칼럼을 수정

DROP COLUMN 기존 칼럼을 삭제

/ALTER TABLE ADD _ 새컬럼 추가

ALTER TABLE table_name

ADD (column_name, data_type expr, …);

ex)  새컬럼 추가

      ALTER TABLE EMP01

      ADD(JOB VARCHAR2(9));

/ALTER TABLE MODIFY _컬럼 타입/크기/default값 변경

ALTER TABLE table_name \

MODIFY (column_name, data_type expr, …);

ex) 칼럼 길이 변경

ALTER TABLE EMP01

MODIFY(JOB VARCHAR2(30));

/ALTER TABLE DROP _ 컬럼 삭제

ALTER TABLE table_name

DROP COLUMN column_name;

ex) 컬럼 삭제

ALTER TABLE EMP01

DROP COLUMN JOB;

//DROP TABLE

기존 테이블 제거

DROP TABLE table_name;

//TRUNCATE __ 주의해서 써야 함(메모리까지 삭제)

기존에 사용하던 테이블의 모든 로우를 제거하기 위한 명령어

TRUCATE table_name ;

주의!) truncate는 DDL명령어 / delete는 DML명령어

    delete는 메모리 상에 존재, rollback 가능.

    truncate는 메모리 상 내용도 삭제, rollback 불가능.

//RENAME

테이블의 이름을 변경

RENAME old_name TO new_name ;

ex) RENAME EMP02 TO TEST;

제약 조건

//제약 조건 **

데이터 무결성 제약 조건(Data Integrity Constraint Rule)이란 테이블에 부적절한 자료가 입력되는 것을 방지

NOT NULL NULL을 허용하지 않는다

UNIQUE (컬럼 값 안에 )중복된 값을 허용하지 않는다

PRIMARY KEY NULL을 허용하지 않고 중복된 값을 허용하지 않는다. NOT NULL

조건과 UNIQUE 조건을 결합한 형태. 식별 키.

FOREIGN KEY* 참조되는 테이블(부모 테이블)의 칼럼의 값이 존재하면 허용.부모

테이블의 PK.

CHECK* 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 을

허용한다.

DEFAULT* default 값 설정. 값이 없으면 default 값 출력.

*foreign key:  부모 테이블 삭제 시 어떻게 할꺼냐의 설정이 중요.

*check: 예를 들어, check age>20; 이면 age 데이터는 20 초과만 가능함.

*default: 예를 들어, sysdate();

?) 도메인 무결성 / 참조 무결성 / 개체 무결성

// 제약 조건 설정 및 확인

/제약 조건 설정방법:  1. 컬럼 레벨과  2.테이블 레벨

1.컬럼 레벨

column_name  data_type constraint_type

ex) ename varchar2(10) not null

2.테이블 레벨

테이블 물리적으로 설정 시, 제약조건 선택 설정.

1.컬럼 레벨

/ DESC TABLENAME  : 테이블 구조와 NOT NULL 확인 가능

/제약조건 에러 확인

1행에 오류:

ORA-00001: 무결성 제약 조건(SCOTT.PK_DEPT)*에 위배됩니다.

*PK_DEPT: dept 테이블 안에 있는 pk 제약조건에 위배.

                  pk 제약 조건 = not null + unique

/NOT NULL 제약 조건

설정 방법: only 컬럼 레벨로만 정의 가능.

ex) CREATE TABLE EMP02(

EMPNO NUMBER(4) NOT NULL,

ENAME VARCHAR2(10) NOT NULL,

JOB VARCHAR2(9),

DEPTNO NUMBER(2)

      );

     INSERT INTO EMP02 VALUES(“SCOTT”, NULL, NULL, ‘10’);*  실행 오류 발생

     INSERT INTO EMP02 (ENAME, JOB) VALUES(“EMMA”, ‘DOCTOR’) 실행 오류 발생

     *INSERT INTO values 순서 주의

         INSERT INTO TABLE (C1, C2, C3) VALUES (V1, V2, V3);

         INSERT INTO TABLE VALUES(V1,V2,V3,V4);

/UNIQUE 제약 조건

: 주로, index, 주민 번호, 전화 번호 등에 적용

ex) CREATE TABLE EMP02(

EMPNO NUMBER(4) UNIQUE,

ENAME VARCHAR2(10) NOT NULL,

JOB VARCHAR2(9),

DEPTNO NUMBER(2)

      );

/제약 조건 이름 설정__ 에러 확인 시 유용

column_name  data_type CONSTRAINT  constraint_name  constraint_type

/권장 제약 조건 이름

[테이블명]_[칼럼명]_[제약 조건 유형]

/PRIMARK KEY 제약조건

UNIQUE + NOT NULL

ex)

CREATE TABLE EMP05(

EMPNO NUMBER(4) CONSTRAINT EMP05_EMPNO_PK PRIMARY KEY ,

ENAME VARCHAR2(10) CONSTRAINT EMP05_ENAME_NN NOT NULL,

JOB VARCHAR2(9),

DEPTNO NUMBER(2)

);

/FOREIGN KEY 제약조건_참조 무결성

참조 무결성이란?

부모 키가 되기 위한 칼럼은 반드시 부모 테이블의 기본 키(PRIMARY KEY)나 유일키(UNIQUE)로 설정되어 있어야 한다.

ex)

CREATE TABLE EMP05(

EMPNO NUMBER(4) CONSTRAINT EMP05_EMPNO_PK PRIMARY KEY ,

ENAME VARCHAR2(10) CONSTRAINT EMP05_ENAME_NN NOT NULL,

JOB VARCHAR2(9),

DEPTNO NUMBER(2) CONSTRAINT EMP05_DEPTNO_FK REFERENCES DEPT(DEPTNO)*

);

*REFERENCES 참조하고자 하는 테이블(PK)

/CHECK 제약 조건

조건으로 데이터의 값의 범위나 특정 패턴의 숫자나 문자 값을 설정할 수 있습니다.

ex)

  (1)사원 테이블에 급여 컬럼을 생성하되 급여 컬럼 값은 500에서 5000사이의 값만 저장할

   수 있도록

  (2)성별을 저장하는 컬럼으로 GENDER 를 정의하고, 이 컬럼에는 남자는 M, 여자는 F 둘      

   중의 하나만 저장할 수 있도록

CREATE TABLE EMP07(

EMPNO NUMBER(4)

CONSTRAINT EMP07_EMPNO_PK PRIMARY KEY ,

ENAME VARCHAR2(10)

CONSTRAINT EMP07_ENAME_NN NOT NULL,

SAL NUMBER(7, 2)

CONSTRAINT EMP07_SAL_CK CHECK(SAL BETWEEN 500 AND 5000)*,

GENDER VARCHAR2(1)

CONSTRAINT EMP07_GENDER_CK CHECK (GENDER IN('M', 'F')) );

* check(조건): 조건은 between, in 등 다양하게 사용 가능

/DEFAULT 제약조건

ex)

CREATE TABLE DEPT01(

DEPTNO NUMBER(2) PRIMARY KEY,

DNAME VARCHAR2(14),

LOC VARCHAR2(13) DEFAULT 'SEOUL'

);

지역을 정의하지 않으면 default로 SEOUL을 줌.

2.테이블 레벨

//테이블 레벨 방식으로 제약 조건 지정

CREATE TABLE table_name (

column_name1 datatype1,

column_name2 datatype2, . . .

[CONSTRAINT constraint_name] constraint_type (column_name) )

주의!) 테이블 레벨에서는 NOT NULL조건 정의 불가

        -> 컬럼 레벨에서 NOT NULL 정의 가능

ex)

CREATE TABLE EMP02(

EMPNO NUMBER(4),

ENAME VARCHAR2(10) NOT NULL,

JOB VARCHAR2(9),

DEPTNO NUMBER(4),

PRIMARY KEY(EMPNO),

UNIQUE(JOB),

FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO) *

);

*(1)FOREIGN KEY(DEPTNO) (2)REFERENCES DEPT(DEPTNO):

(1)은 FK 조건 지정. (2)FK의 참조 PK와 PK의 기본 키 지정.

*이름 지정은 이렇게!

CONSTRAINT EMP03_EMPNO_PK PRIMARY KEY(EMPNO)

DML

//INSERT INTO

INSERT INTO tablename VALUES(v1, v2, v3…);   

INSERT INTO tablename (c1, c2, c3) VALUES(v1, v2, v3);   

/NULL 값 들어가는 경우

NULL값을 의도적으로 삽입하는 경우는 매우 희박.

(1)암시적 방법: 칼럼 선택 시 , 칼럼 생략

 ex) INSERT INTO tablename (c1, c2) VALUES(v1, v2);   

     => 이 row에서 c3는 null

(2) 명시적 방법: insert 시, 직접 null 값 입력

 ex)INSERT INTO tablename VALUES(v1, v2, null/(’’));  

/AS (서브쿼리)

CREATE TABLE DEPT02 AS SELECT * FROM DEPT WHERE 1=0;

INSERT INTO DEPT02 SELECT * FROM DEPT;

주의!) 테이블의 컬럼 개수, 데이터 타입이 INSERT 서브쿼리에서 가져온 것과 동일 해야 함

/다중 테이블 동시 입력 _ Little using

여러 테이블에 동시에 입력하기 위한 명령문

ex)

INSERT ALL

INTO EMP_HIR VALUES(EMPNO, ENAME, HIREDATE)

INTO EMP_MGR VALUES(EMPNO, ENAME, MGR)

SELECT EMPNO, ENAME, HIREDATE, MGR

FROM EMP

WHERE DEPTNO=20;

EMP 테이블에서 EMPNO, ENAME, HIREDATE, MGR을 모두(ALL) 불러와

EMP_HIR 테이블과 EMP_MGR 테이블에 삽입

!) 다중 테이블 동시 입력 시, 조건 설정 가능

ex) INSERT ALL

      WHEN HIREDATE > '1982/01/01' THEN

      INTO EMP_HIR02 VALUES(EMPNO, ENAME, HIREDATE)

      WHEN SAL > 2000 THEN

      INTO EMP_SAL VALUES(EMPNO, ENAME, SAL)

      SELECT EMPNO, ENAME, HIREDATE, SAL

      FROM EMP;

//UPDATE  SET

UPDATE table_name

SET column_name1 = value1, column_name2 = value2, …

WHERE conditions;

주의!) WHERE 설정X -> 테이블의 모든 행 수정됨.

ex)모든 사원의 급여를 10% 인상

UPDATE EMP01

SET SAL = SAL * 1.1;

ex Where 조건 사용) 급여가 3000 이상인 사원만 급여를 10% 인상

UPDATE EMP01

SET SAL = SAL * 1.1

WHERE SAL >= 3000;

ex Where 조건 사용)1987년에 입사한 사원의 입사일이 오늘로 수정

UPDATE EMP01

SET HIREDATE = SYSDATE

WHERE SUBSTR(HIREDATE, 1, 2)='87';

ex 컬럼 2개 이상 동시 변경)

SCOTT 사원의 입사일자는 오늘로, 급여를 50 으로 커미션을 4000 으로 수정

UPDATE EMP01

SET HIREDATE = SYSDATE, SAL=50, COMM=4000

WHERE ENAME='SCOTT';

/서브 쿼리 이용 데이터 변경

!) 서브 쿼리 이용 2개 이상의 칼럼 값 변경 가능

(1)

UPDATE table_name

SET (column_name1, column_name2, …) = (sub_query)

WHERE 조건

(2)

UPDATE table_name

SET column_name1 = (sub_query1), column_name2 = (sub_query2), …

WHERE 조건

ex)20번 부서의 지역명을 40번 부서의 지역명으로 변경

UPDATE DEPT01

SET LOC=(SELECT LOC

       FROM DEPT01

       WHERE DEPTNO=40)

WHERE DEPTNO=20;

ex) 부서번호가 20인 부서의 부서명과 지역명을 부서 번호가 40번인 부서와 동일하게 변경하기

UPDATE DEPT01

SET (DNAME, LOC)=(SELECT DNAME,LOC *

  FROM DEPT

    WHERE DEPTNO=40)

WHERE DEPTNO=20;

*서브 쿼리의 select 대상과 set의 대상은 일치 시켜줘야 함.

//DELETE FROM(주의!)

테이블에 저장되어 있는 데이터를 삭제

DELETE FROM table_name

WHERE conditions;

/서브 쿼리 사용 가능

DELETE FROM EMP01

WHERE DEPTNO=(SELECT DEPTNO

FROM DEPT

WHERE DNAME='SALES');