SQL - DDL 데이터 정의어 - 테이블 생성, 변경, 삭제, 뷰

DDL

(Data Definition Language)

 

테이블 생성 (CREATE)

CREATE TABLE test(
    no NUMBER(3) DEFAULT 0,
    name VARCHAR2(20) DEFAULT 'noname',
    hiredate DATE DEFAULT sysdate
    );

DEFAULT 는 데이터 삽입시 데이터 지정하지 않으면 삽입되는 기본 데이터 값

 

test

 

 

데이터 추가 (INSERT)

INSERT INTO TEST VALUES(1,'홍길동','2022 03 03');
INSERT INTO TEST (NAME) VALUES('비비빅');
INSERT INTO TEST (NO, NAME) VALUES(2,'aaa');

모든 속성 값에 데이터가 추가될 경우 속성 지정없이 VALUES 에 파라미터 값을 지정하면 된다.

만약 속성 수(차수) 미만의 데이터를 추가할 경우 컬럼명을 지정해야한다.

 

INSERT INTO TEST (NAME) VALUES('비비빅');
INSERT INTO TEST (NO, NAME) VALUES(2,'aaa');

 

데이터 추가

입력 데이터가 없는 경우 디폴트(기본)값이 추가되었다.

 

 

 

 

TCL

COMMIT / ROLLBACK

COMMIT은 결과를 물리적으로 영구히 저장하는 것이고

ROLLBACK 은 결과를 취소하는 것이다.

 

 

그래서 여기서 ROLLBACK 을 하게 되면 트랜잭션 결과가 물리적으로

저장되지 않았기 때문에 최종 물리적 저장상태 전으로 돌아간다.

 

ROLLBACK 결과

 

 

 

만약 데이터 추가후

COMMIT 과 ROLLBACK 을 하게되면

COMMIT 이후 상태로 남게된다.

 

 

 

 

---

테이블 수정 (UPDATE)

새로운 테이블로 테이블 수정을 해볼까요.

 

CREATE TABLE test1(
    NO NUMBER(5),
    NAME VARCHAR2(20),
    address VARCHAR2(50),
    tel VARCHAR2(20)
);

test1

 

INSERT INTO test1 (NO, NAME) VALUES(1,'aaa');
INSERT INTO test1 (NO, NAME ,address) VALUES(4, 'bbb','울산');

데이터 추가 결과

 

 

---

1개의 속성 값 수정

UPDATE test1
SET NAME='홍길동'
WHERE NO=1;

2개의 속성 값 수정

UPDATE test1
SET NAME='test', address='서울'
WHERE NO =4;

 

 

 

---

테이블 삭제

DELETE / TRUNCATE / DROP

 

DELETE

DELETE FROM test1
WHERE NO=1;

 

TRUNCATE

데이터 지우고 구조만 남기기

TRUNCATE TABLE test1;

 

 

 

DROP TABLE

DROP TABLE test1;​

 

 

 

 

---

고급

 

테이블을 이용한 테이블 생성

기존의 테이블의 구조와 데이터 값을 그대로 가져와 테이블 생성

CREATE TABLE dept_temp
AS 
SELECT * FROM dept;

기존 dept 테이블의 값들을 그대로 가져온다.

dept_temp

 

하지만 여기서 주의해야될 점은

테이블에 걸려있는 제약 조건은 가져오지 않는다.

dept (기존 테이블)

 

dept_temp (새 테이블)

 

 

 

---

테이블 구조만 복사

이를 이용하여 테이블 구조만 복사 가능하다.

WHERE 조건절에 거짓 조건 부여한다.

CREATE TABLE emp_temp
AS SELECT * FROM emp
WHERE 1<>1; -- 거짓 조건 부여

emp_temp (구조만 가져온 테이블)

 

 

 

 

---

 

 

 

테이블을 이용한 테이블 수정

 

deptno=40 인 dname, loc 의 값으로

dept_temp2 테이블의 deptno=40인 dname, loc 를 수정하는 쿼리

UPDATE dept_temp2 
SET dname =(
            SELECT dname
            FROM dept
            WHERE deptno=40),
    loc =(
            SELECT loc
            FROM dept
            WHERE deptno=40)
    WHERE deptno=40;

하나하나 서브쿼리를 두는 것보다

 

UPDATE dept_temp2 
SET (dname, loc)=(
    SELECT dname, loc
    FROM dept
    WHERE deptno=40)
WHERE deptno=40;

SET 으로 다중값을 받는 것이 효율적인 쿼리문으로 보인다.

 

 

 

 

구조 확인

DESCRIBE dept_temp2;

 

 

---

ALTER TABLE

 

속성 추가

--속성 추가하기
ALTER TABLE dept_temp
ADD(LOCATION VARCHAR2(50));

 

속성 수정

ALTER TABLE dept_temp
MODIFY(LOCATION VARCHAR2(70));

 

 

속성 삭제

--속성 삭제
ALTER TABLE dept_temp
DROP COLUMN LOCATION;

 

 

 

 

---

VIEW

기본 테이블로부터 유도된 가상 테이블

뷰 생성 (CREATE VIEW)

 

가상테이블로 물리적으로 구현되어 있지 않다.

삽입, 삭제, 갱신 연산에 제약이 따른다.

-- 뷰 생성하기
CREATE VIEW vw_emp20
AS
(SELECT empno, ename, JOB,deptno
FROM emp
WHERE deptno>20
);

 

뷰가 이미 생성돼 있을 수 있기 때문에

CREATE OR REPLACE VIEW 형태로 생성한다.

CREATE OR REPLACE VIEW vw_emp20
AS
(SELECT empno, ename, JOB, deptno
FROM emp
WHERE deptno >20);

뷰 테이블 열

뷰 테이블 데이터

 

 

WITH READ ONLY

읽기만 가능한 뷰 생성

CREATE OR REPLACE VIEW v_emp1
AS
SELECT * FROM emp
WITH READ ONLY; -- 읽기만

 

 

---

뷰 삭제

DROP VIEW

DROP VIEW vw_emp20;