DDL

 

DDL문장과 딕셔너리

 

■ CREATE

이 명령은 새로운 오브젝트나 스키마를 생성할 때 사용하는 명령어

 

CREATE TABLE 한글테이블

( 컬럼1 NUMBER,

컬럼2 VARCHAR2(10),

컬럼3 DATE );

select * from user_tables

where table_name='한글테이블';

 

 

* 한글로도 테이블명을 선언해서 사용할 수 있음!

 

* 테이블 생성시 테이블명 주의사항

1.반드시 문자로 시작해야함

2. 최대 30byte 한글테이블일 경우 15글자

3. 테이블 한 계정에서 동일 명으로 사용 못함 (다른 계정시 중복가능) 

 

 

문법예제 CREATE TABLE ddl_test (no NUMBER(3,1), --3자리인데 한자리는 소수자리 (ex: 10.3) name VARCHAR2(10), --BYTE수 birth DATE [DEFAULT SYSDATE]) TABLESPACE users; --테이블들을 저장하고 있는 단위

 

SELECT * FROM DBA_TABLESPACES;

   

-- SCOTT USER의 DEFAULT TABLESPACE 확인

SELECT USERNAME, DEFAULT_TABLESPACE

FROM DBA_USERS

WHERE USERNAME='SCOTT';

 

--테이블의 테이블스페이스 확인

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME

FROM DBA_TABLES

WHERE TABLE_NAME='DDL_TEST';

 

--SCOTT 계정의 DEFAULT TABLESPACE를 EXAMPLE로 변경

ALTER USER SCOTT DEFAULT TABLESPACE EXAMPLE;

 

테이블 복사하기 (CTAS 구문)

CREATE TABLE DEPT3

AS

SELECT * FROM DEPT2;

 

기존 컬럼명 변경후 특정컬럼 가져오기

CREATE TABLE DEPT4

AS

SELECT DCODE AS "DCODE_1",DNAME AS "DNAME_1"

FROM DEPT2;

 

테이블 구조만 생성 (데이터없이)

CREATE TABLE DEPT5

AS

SELECT DCODE,DNAME

FROM DEPT2

WHERE 1=2;

 

 

가상 컬럼 생성(버츄얼컬럼)

CREATE TABLE VT001

( NO1 NUMBER,

NO2 NUMBER,

NO3 NUMBER GENERATED ALWAYS AS (NO1 +NO2) VIRTUAL);

   

DESC VT001;

 

INSERT INTO VT001(NO1,NO2)

VALUES(1,2);

 

SELECT * FROM VT001; 

 

     

--EMP2 테이블의 NAME,BIRTHDAY,PAY을 가지고 GRADE_A, GRADE_B, GRADE_C 테이블을 각각 생성하고

--EMP2 테이블에서 PAY가 30000000 미만이면 'C', 30000000이상 50000000 이하 'B', 50000000보다 크면 'A'가 되도록 하여

--열 이름을 GRADE로 지정하고 이름, 생일, PAY와 함께 등급별로 테이블을 분류하여 데이터를 INSERT 하여라

 

CREATE TABLE GRADE_A

AS SELECT NAME,BIRTHDAY,PAY,'ABC' AS "GRADE"

FROM EMP2

WHERE 1=2;

 

CREATE TABLE GRADE_B

AS SELECT NAME,BIRTHDAY,PAY,'ABC' AS "GRADE"

FROM EMP2

WHERE 1=2;

 

CREATE TABLE GRADE_C

AS SELECT NAME,BIRTHDAY,PAY,'ABC' AS "GRADE"

FROM EMP2

WHERE 1=2;

 

INSERT ALL

WHEN PAY < 30000000 THEN

INTO GRADE_C VALUES(NAME,BIRTHDAY,PAY,'C')

WHEN PAY BETWEEN 30000000 AND 50000000 THEN

INTO GRADE_B VALUES(NAME,BIRTHDAY,PAY,'B')

WHEN PAY > 50000000 THEN

INTO GRADE_A VALUES(NAME,BIRTHDAY,PAY,'A')

SELECT NAME,BIRTHDAY,PAY

FROM EMP2;

SELECT * FROM GRADE_C;

 

 

--PROFESSOR 테이블에서 홈페이지 주소가 없는 교수들은 ID로 된 홈페이지 주소로 변경하여라

--(홈페이지 주소 : HTTP://WWW.NAVER.COM/ID)

 

SELECT * FROM PROFESSOR;

 

 

 

UPDATE PROFESSOR

SET HPAGE = 'HTTP://WWW.NAVER.COM/'||ID

WHERE HPAGE IS NULL;

 

 

■ ALTER문

CREATE TABLE DEPT6

AS

SELECT DCODE, DNAME

FROM DEPT2

WHERE DCODE IN(1000,1001,1002);

 

-- 디폴트넣은거와 안넣은거의 차이 

ALTER TABLE DEPT6 ADD (LOC VARCHAR2(10));

 

--컬럼 추가시 DEFAULT 설정

ALTER TABLE DEPT6 ADD (LOC2 VARCHAR2(10) DEFAULT 'AAA');

 

--컬럼 추가후 DEFAULT 설정

ALTER TABLE DEPT6 ADD (LOC3 VARCHAR2(10));

ALTER TABLE DEPT6 MODIFY (LOC3 DEFAULT 'BBB');

 

 

SELECT * FROM DEPT6;

 

--CTAS DEPT7 생성 -> ADD COLUMN -> DEPT6 RENAME ;

 

 

--RENAME

ALTER TABLE DEPT6 RENAME COLUMN LOC2 TO AREA;

 

--DEPT6 은 DEPT7로

RENAME DEPT6 TO DEPT7;

 

--컬럼의 데이터 크기를 변경

ALTER TABLE DEPT7 MODIFY (DCODE VARCHAR2(10)); --이미 4크기의 데이터가 들어가 있기 때문에 작게는 변경 불가능

 

DESC DEPT7;

 

SELECT MAX(LENGTH(DCODE)) FROM DEPT7;

 

ALTER TABLE DEPT7 DROP COLUMN LOC3; -- 부하가 걸릴 수 있음 고려

 

SELECT * FROM DEPT7;

읽기 전용 테이블로 변경(읽기 O , 변경 X) [위험!!!!]

10G - 트리거를 사용 OR 제약조건 DISABLE/NOVALIDATE 등을 사용했어야함

 

CREATE TABLE T_READ

(NO NUMBER, NAME VARCHAR2(10));

 

INSERT INTO T_READ

VALUES (1,'AAA');

 

COMMIT;

 

ALTER TABLE T_READ READ ONLY;

 

SELECT TABLE_NAME, READ_ONLY

FROM USER_TABLES

WHERE TABLE_NAME = 'T_READ'

 

(SELECT 만 가능)

 

ALTER TABLE T_READ ADD (TEL NUMBER DEFAULT 111);

ALTER TABLE T_READ MODIFY (NAME VARCHAR2(15)); (UPDATE OPERATION ~~ 오류 뜸)

 

ALTER TABLE T_READ READ WRITE; ( READ ONLY 해제 ) [위험!!!!]

 

DROP TABLE T_READ; [ READ ONLY 상태에서 DROP 됨 DROP > READ ONLY ]

 

 

--1) 다음은 웹 사이트의 게시판을 사용하는 회원을 관리하기 위한 테이블 레이아웃이다. 회원테이블(member)을 생성하여라.

 

CREATE TABLE MEMBER2(

USERID VARCHAR2(10),

USERNAME VARCHAR2(10),

PASSWD VARCHAR2(10),

IDNUM VARCHAR2(13),

PHONE NUMBER(13),

ADDRESS VARCHAR2(20),

REGDATE DATE,

INTEREST VARCHAR2(15));

SELECT * FROM MEMBER2;

 

COMMENT ON TABLE MEMBER2 IS '게시판회원테이블';

COMMENT ON COLUMN MEMBER2.USERID IS '사용자아이디';

COMMENT ON COLUMN MEMBER2.USERNAME IS '회원이름';

COMMENT ON COLUMN MEMBER2.PASSWD IS '비밀번호';

COMMENT ON COLUMN MEMBER2.IDNUM IS '주민등록번호';

COMMENT ON COLUMN MEMBER2.PHONE IS '전화번호';

COMMENT ON COLUMN MEMBER2.ADDRESS IS '주소';

COMMENT ON COLUMN MEMBER2.REGDATE IS '가입일';

COMMENT ON COLUMN MEMBER2.INTEREST IS '관심분야';

 

SELECT * FROM USER_TAB_COMMENTS

WHERE TABLE_NAME='MEMBER2';

 

SELECT * FROM USER_COL_COMMENTS

WHERE TABLE_NAME ='MEMBER2';

 

SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES;

 

--2) 회원테이블에 다음 회원 정보를 입력하여라.

-- (sunshinLee, 이순신, ssl000, 8701011120200, 02)333-2123, 서울, 2015/07/05, 컴퓨터)

 

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD'; -- CREATE 문 안에 TO_DATE를 사용하면 부하

 

INSERT INTO MEMBER2(USERID,USERNAME,PASSWD,IDNUM,PHONE,ADDRESS,REGDATE,INTEREST)

VALUES('sunshinLee','이순신','ssl000','8701011120200','023332123','서울', '2015/07/05', '컴퓨터');

 

SELECT PHONE, TO_CHAR(PHONE,'00000000000') FROM MEMBER2;

 

SELECT * FROM MEMBER2;

 

--3) 회원 테이블의 구조와 데이터 집합이 같은 member_second 테이블을 생성하여라.

 

CREATE TABLE MEMBER_SECOND

AS

SELECT * FROM MEMBER2;

 

SELECT * FROM MEMBER_SECOND;

 

--4) 회원 테이블의 userid, username, passwd 컬럼만 복사해서 member_third 테이블을 생성하여라.

 

CREATE TABLE MEMBER_THIRD

AS SELECT USERID,USERNAME,PASSWD FROM MEMBER2;

 

SELECT * FROM MEMBER_THIRD;

 

--5) 회원 테이블과 구조는 동일하고 데이터를 가지지 않는 member_forth 테이블을 생성하여라.

 

CREATE TABLE MEMBER_FORTH

AS SELECT * FROM MEMBER2

WHERE 1=2;

 

SELECT * FROM MEMBER_FORTH;

 

--6) 회원 테이블에 email 컬럼을 추가하여라. 단, email 컬럼 데이터 타입은 varchar2(50)이다.

ALTER TABLE MEMBER2 ADD(EMAIL VARCHAR2(50));

 

DESC MEMBER2;

 

--7) 회원 테이블에 국적을 나타내는 country 컬럼을 추가하고 기본값을 ‘Korea’로 지정하여라.

ALTER TABLE MEMBER2 ADD(COUNTRY VARCHAR2(10) DEFAULT 'Korea');

/*OR*/ALTER TABLE MEMBER2 MODIFY COUNTRY DEFAULT 'Korea';

 

DESC MEMBER2;

 

--8) 회원 테이블에서 email 컬럼을 삭제하여라.

ALTER TABLE MEMBER2 DROP( EMAIL );

 

DESC MEMBER2;

--9) 회원 테이블의 address 컬럼의 데이터 크기를 30으로 증가시켜라.

ALTER TABLE MEMBER2 MODIFY( ADDRESS VARCHAR2(30) );

DESC MEMBER2;

 

--10) member_second 테이블 이름을 potential_member로 변경하여라.

RENAME MEMBER_SECOND TO POTENTIAL_MEMBER;

 

SELECT * FROM POTENTIAL_MEMBER;

 

TRUNCATE

TRUNCATE TABLE TABLE명;

DROP

DROP TABLE TABLE명;

--테스트용 테이블 생성

CREATE TABLE SCOTT.TEST_TBL1

AS SELECT * FROM SCOTT.STUDENT;

CREATE TABLE SCOTT.TEST_TBL2

AS SELECT * FROM SCOTT.STUDENT;

--테스트용 인덱스 생성

CREATE INDEX SCOTT.TEST_IND1

ON SCOTT.TEST_TBL1(STUDNO);

CREATE INDEX SCOTT.TEST_IND2

ON SCOTT.TEST_TBL2(STUDNO);

--테이블에 인덱스 존재여부 확인

SELECT INDEX_NAME, TABLE_NAME, STATUS,VISIBILITY

FROM DBA_INDEXES

WHERE TABLE_NAME LIKE 'TEST_%'

AND INDEX_NAME LIKE 'TEST_%';

--테이블 DROP

DROP TABLE SCOTT.TEST_TBL1;

SELECT * FROM SCOTT.TEST_TBL1;

** 차이점 DELETE : 디스크 상 공간은 그대로 가지고 있음 --> REORG 해야함

TURCATE : 용량은 줄고 구조는 살아있음

DROP : DROP TABLE을 하게되면 몽땅 삭제

--테이블 DROP 후 인덱스 재 확인 -> 테이블 TRUNCATE 시 해당 테이블의 인덱스도 같이 삭제됨

--인덱스 UNUSABLE ****INDEX TRUNCATE시 VALID로 다시바뀜

ALTER INDEX SCOTT.TEST_IND2 UNUSABLE;

TRUNCATE TABLE SCOTT.TEST_TBL2;

/

 

============== 차이 지웠을 때 구조적인 차이, 메모리 차이 HWM 하이워터마크=====================

--인덱스 UNUSABLE : 테이블 변경시 INDEX 반영이 안됨(그래서 리빌드가 필요함)

ALTER INDEX SCOTT.TEST_IND2 UNUSABLE;

TRUNCATE TABLE SCOTT.TEST_TBL2;

--인덱스 INVISIBLE ; 테이블 변경시 INDEX에 반영

ALTER INDEX SCOTT.TEST_IND2 INVISIBLE;

--인덱스 VISIBLE

ALTER INDEX SCOTT.TEST_IND2 VISIBLE;

--프로시저로 만든 TEST01 테이블에 인덱스 생성

CREATE INDEX SCOTT.TEST03_IND1 ON SCOTT.TEST03(NO);

--PLAN확인

SELECT NO

FROM SCOTT.TEST03

WHERE NO = 800; -- CTRL+E 실행계획

--인덱스 invisible 후 플랜 재 확인

ALTER INDEX SCOTT.TEST03_IND1 VISIBLE;