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;
