[Oracle] DML - Create, Alter

SQL의 종류

 - DDL문장 (CREATE, DROP, ALTER, TRUNCATE)
 - DML문장 (INSERT ,UPDATE, DELETE)

 

  데이터 조작 : DML(INSERT , UPDATE, DELETE)
   - ROLLBACK OR COMMIT 가능
   
   1) INSERT 문장
       -INSERT INTO 테이블이름(컬럼명, 컬럼명,....) VALUES(값, 값,값,....);
       -INSERT INTO 테이블이름 VALUES(값, 값,값,....); -- 모든 컬럼에 순서대로 값을 넣을 때
   
   2) DELETE 문장
       DELETE [FROM] 테이블이름
       [WHERE 조건식]
   
   3) UPDATE 문장
      UPDATE 테이블이름
      SET 컬럼명=변경값 , 컬럼명=변경값,....
      [WHERE 조건식] 

 

 

제약조건의 종류

 

   1) PRIMARY KEY : 대표키, 중복X, NOT NULL, 인덱스 자동 생성

      +) PRIMARY KEY - PK, 기본키, 대표키
      : PK는 하나의 테이블에 반드시 한 개만 존재
      : 2개 이상의 컬럼을 하나로 묶어서 PK설정 가능 
          - 복합키 설정 : 사용할 때 불편하기 때문에 모델링 과정 중 복합키를 대리키로 변환하는 경우가 많다.

   2) FOREIGN KEY : 다른 테이블의 컬럼을 참조

      +) FOREIGN KEY - FK = 외래키
      : 다른 테이블의 PK를 참조하는 것.
      : 테이블에 레코드를 추가할 때 참조되는 대상의 값 이외에는 등록할 수 없다.→ 참조 무결성 원칙
      : NULL 허용, 중복 가능
      : 하나의 테이블에 여러개의 컬럼 FK 설정 가능하다.
      : 재귀적관계 설계 가능 → 자기 자신 테이블의 PK를 참조
      
      *주의사항
        INSERT 할때 : 부모키가 INSERT → 자식 INSERT 
        DELETE할때 :  참조하고 있는 자식 DELETE → 부모 DELETE 해야한다.
      
      * 이러한 불편함을 해결하기 위해서.
          FK를 설정할때 ON DELETE CASCADE 를 추가하면 부모레코드를 삭제할 때 그 부모키를 참조하는 모든 테이블의 레코드를 함께 삭제한다.
          또는 FK 설정 시 on delete set null 를 추가하면 부모 레코드 삭제될 때 참조되는 자식 레코드의 값이 null이 된다.

   3) UNIQUE : PK와 유사 (테이블에서 대표키는 될 수 없지만 유일해야 하는 속성에 설정) 

        : 중복안됨, NULL 허용 (NOT NULL을 설정하면 PK와 동일) 
        : 후보키 중 대표키가 될 수 없는 키를 UNIQUE 설정한다. 
        :  한 테이블에 여러 개의 컬럼에 설정 가능
      
   4) CHECK : DOMAIN에 들어갈 수 있는 값의 범위를 체크

        : 조건을 설정하여 조건에 만족하지 않는 정보는 INSERT할 수 없다.

   5) DEFAULT  : 기본값 설정

        : EX) 등록일, 조회수....
        : DEFAULT를 설정할때는 CONSTRAINT ALIAS는 사용하지 않는다.
        : NOT NULL 설정 시 DEFALUT를 함께 사용할때는 반드시 DEFAULT를 먼저 작성한다.

 

테이블 생성

/*
create table 테이블이름
(컬럼명 datatype [default 기본값] [ null | not null ] [ constraint 별칭 제약조건 ] ,
컬럼명 datatype [default 기본값] [ null | not null ] [ constraint 별칭 제약조건 ] ,
)
*/

CREATE TABLE EMP
    (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

 

테이블 구조 확인

DESC MEMBER;

 

레코드 등록

/*
INSERT INTO 테이블이름(컬럼명, 컬럼명,....) VALUES(값, 값,값,....);
INSERT INTO 테이블이름 VALUES(값, 값,값,....);
*/

-- 모든 열에 값 삽입하는 경우
INSERT INTO DEPT VALUES
	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
	(30,'SALES','CHICAGO');
    
-- 특정 열에만 삽입하는 경우
insert into users (user_seq, name, email, phone) values (111, '홍길동', 'hong@gildong@com', '010-1111-1111');
insert into users (user_seq, name, email, phone) values (222, '이길동', 'lee@gildong@com', '010-2222-2222');
insert into users (user_seq, name, email, phone) values (333, '삼길동', 'sam@gildong@com', '010-3333-3333');

 

테이블 삭제

/*
DROP TABLE 테이블이름;
*/

DROP TABLE MEMBER;

 

복합키로 PK 설정

CREATE TABLE MEMBER(
  ID VARCHAR2(20) ,
  NAME VARCHAR2(10) NOT NULL,
  JUMIN CHAR(13) ,
  AGE NUMBER(2) ,
  ADDR VARCHAR2(10 CHAR) ,
  REG_DATE DATE ,
  CONSTRAINT MEM_ID_JUMIN_PK PRIMARY KEY(ID,JUMIN)	-- 괄호 이용해 묶어서 복합키
);

 

Foreign Key

CREATE TABLE  EMP(
  EMP_NO NUMBER(3) CONSTRAINT EMP_NO_PK PRIMARY KEY,
  ENAME VARCHAR2(10) NOT NULL,
  SAL NUMBER(5),
  DEPT_CODE CHAR(3) CONSTRAINT EMP_CODE_FK REFERENCES DEPT(DEPT_CODE),  -- FK
  HIREDATE DATE DEFAULT SYSDATE -- 기본값 설정
);

 

ON DELETE CASCADE vs. ON DELETE SET NULL

CREATE TABLE  EMP(
  EMP_NO NUMBER(3) CONSTRAINT EMP_NO_PK PRIMARY KEY,
  DEPT_CODE CHAR(3) CONSTRAINT EMP_CODE_FK REFERENCES DEPT(DEPT_CODE) ON DELETE CASCADE,
);

CREATE TABLE  EMP(
  EMP_NO NUMBER(3) CONSTRAINT EMP_NO_PK PRIMARY KEY,
  DEPT_CODE CHAR(3) CONSTRAINT EMP_CODE_FK REFERENCES DEPT(DEPT_CODE) on delete set null,
);

 

레코드 삭제 방법

    1) DML - ROLLBACK 가능
      DELETE [FROM] 테이블이름
      [WHERE 조건식]
      
        * FROM 생략가능, WHERE절이 없으면 모든 레코드가 삭제된다.
          
    2) DDL - ROLLBACK 불가
     TRUNCATE TABLE 테이블이름; --모든레코드를 삭제
      

DELETE FROM EMP; --모든레코드 삭제

DELETE DEPT WHERE DEPT_CODE='A02';

 

  테이블 수정

    ① 컬럼추가
     alter table 테이블이름 add 
        (컬럼명 자료형 [제약조건] , 컬럼명 자료형 [제약조건] , ....)
 
    ② 컬럼삭제
     alter table 테이블이름 drop column 컬럼이름
 
    ③ datatype변경
       alter table 테이블이름 modify 컬럼이름 변경자료형
    
   ④ 컬럼이름 변경
    alter table 테이블이름 rename column 기존컬럼명 to 변경컬럼명
 
    ⑤ 제약조건 추가
     alter table 테이블이름 ADD CONSTRAINT 별칭 제약조건종류 ;
  
    -제약조건 삭제
     ALTER TABLE 테이블이름 DROP CONSTRAINT 별칭;

SELECT * FROM TEST;
ALTER TABLE  TEST ADD (PHONE VARCHAR2(10) , ETC VARCHAR2(20) DEFAULT '기타' NOT NULL );
alter table TEST ADD CONSTRAINT PHONE_CK CHECK(PHONE='1111' OR PHONE='2222');
ALTER TABLE TEST DROP CONSTRAINT PHONE_CK;

 

모든 컬럼, 모든 레코드 복사하기

CREATE TABLE COPY_EMP
AS SELECT *FROM EMP;

SELECT * FROM COPY_EMP;

 

원하는 컬럼, 원하는 레코드만 복사하기

CREATE TABLE COPY_EMP2
AS SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP WHERE DEPTNO=20;

SELECT * FROM COPY_EMP2;

 

테이블의 구조만 복사하기 

CREATE TABLE COPY_EMP3
AS SELECT * FROM EMP WHERE 1=0;

SELECT * FROM COPY_EMP3;
DESC COPY_EMP3;