Do it 책

1~15장 - 10장 마무리
11. 트랜잭션 제어와 세션 병행제어 회복
12. 데이터 정의어(DDL)
CREATE, ALTER, DROP, RENAME, TRUNCATE
13. 객체 종류
- 데이터 사전
- 빠른 검색을 위한 인덱스
- 테이블처럼 사용하는 뷰(테이블의 거울)
- 규칙에 따라 순번을 생성하는 시퀸스(번호표)
- 공식 별칭을 지정하는 동의어(시노임)
14. 제약조건
- NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT 
15. 사용자 생성, 권한, 롤 관리

데이터 정의어(DDL)

• 데이터를 보관(저장), 관리를 위한 여러 객체의 생성, 변경 삭제 기능 수행

데이터 정의어 특징

• 명령어를 수행하면 바로 데이터베이스에 수행한 내용이 반영(Auto commit)

CREATE TABLE SCOTT.DEPT1( -- SCOTT 접속시 SCOTT부분 생략가
 열 이름 데이터 타입.
);

⚡ 테이블 이름, 열 이름 지정 규칙

• 문자로 반드시 시작, 숫자로 시작할 수 없음
• 테이블 이름 30byte이하로 지정
• 같은 사용자가 같은 이름의 테이블 생성 불가, 중복 불가
• 영문자, 한글, 숫자, 특수문자($, #, _) 가능
• 예약된 명령어는 변수명으로 사용 불가

테이블을 수정하는 명령어 : ALTER

• ALTER 명령어는 테이블에 새 열을 추가 또는 삭제, 열의 자료형 또는 길이를 변경할 때 테이블의 구조 변경

SELECT * FROM DEPT WHERE DEPTNO=10;

CREATE TABLE DEPT_10 AS SELECT * FROM DEPT WHERE DEPTNO = 10;

SELECT * FROM DEPT_10;

-- 서브쿼리를 이용한 빈 테이블 
CREATE TABLE EMPDEPT_TEMP AS 
        SELECT e.empno, e.ename, e.sal, e.comm, e.job, d.deptno, d.dname
            FROM EMP e, DEPT d 
                WHERE 1<>1 AND e.deptno= d.deptno;
                
desc EMPDEPT_TEMP;
SELECT * FROM EMPDEPT_TEMP;

-- LOC라는 컬럼을 추가
ALTER TABLE EMPDEPT_TEMP ADD LOC VARCHAR2(20);

-- SAL 속성명 SALARY로 변경[RENAME COLUMN]
ALTER TABLE EMPDEPT_TEMP RENAME COLUMN SAL TO SALARY;

-- EMPNO 컬럼의 자료형의 변경이나 열의 길이 변경 : MODIFY
ALTER TABLE EMPDEPT_TEMP 
    MODIFY EMPNO NUMBER(5);
    
-- 특정 컬럼이 필요없을 때(삭제) : DROP COLUMN    
-- DDL은 auto commit으로 rollback 적용이 안됨
ALTER TABLE EMPDEPT_TEMP
    DROP COLUMN COMM;

-- 테이블에 대한 이름 변경 : RENAME 명령어
RENAME EMPDEPT_TEMP TO EMPDEPT;

DESC EMPDEPT;

SELECT * FROM EMPDEPT;

-- 서브쿼리로 값 넣기
INSERT INTO EMPDEPT(EMPNO, ENAME, SALARY, JOB, DEPTNO, DNAME, LOC) 
       SELECT  E.EMPNO, E.ENAME, E.SAL, E.JOB, D.DEPTNO, D.DNAME, D.LOC
            FROM EMP E, DEPT  D
            WHERE E.DEPTNO=D.DEPTNO;
            
ROLLBACK;

COMMIT;

-- 테이블의 데이터만을 삭제하여 초기화 시키는 명령어 : TRUNCATE
DELETE EMPDEPT;
SELECT * FROM EMPDEPT;
ROLLBACK;
SELECT * FROM EMPDEPT;

TRUNCATE TABLE EMPDEPT;
ROLLBACK; -- 롤백 안됨
SELECT * FROM EMPDEPT;

-- DROP TABLE 테이블명 => 테이블삭제
DROP TABLE EMPDEPT;

예제

-- P.324 #1 다음 열 구조를 가지는 EMP_HW 테이블을 만들어 보세요
CREATE TABLE EMP_HW(
    EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10) NOT NULL,
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL DECIMAL(7, 2),
    COMM DECIMAL(7, 2),
    DEPTNO NUMBER(2),
    PRIMARY KEY(EMPNO)
);

CREATE TABLE EMP_HW AS SELECT * FROM EMP WHERE 1<>1;
SELECT * FROM EMP_HW;

-- #2
-- EMP_HW 테이블에 BIGO 열을 추가해 보세요. BIGO 열의 자료형은 가변형 문자열이고, 
-- 길이는 20 입니다.
ALTER TABLE EMP_HW ADD BIGO VARCHAR2(20);

-- #3. EMP_HW 테이블의 BIGO열 크기를 30으로 변경해 보세요
ALTER TABLE EMP_HW MODIFY BIGO VARCHAR2(30);

-- #4. EMP_HW 테이블의 BIGO 열 이름을 REMARK로 변경해 보세요
ALTER TABLE EMP_HW RENAME COLUMN BIGO TO REMARK;

DESC EMP;
DESC DEPT;
-- #5.  EMP_HW 테이블에 EMP테이블의 데이터를 모두 저장해 보세요. 단, REMARK 열은 NULL로 삽입합니다.
INSERT INTO EMP_HW(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 
       SELECT  E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM ,E.DEPTNO
            FROM EMP E;
SELECT * FROM EMP_HW;

-- #6. 지금까지 사용한  EMP_HW 테이블을 삭제해 보세요
DROP TABLE EMP_HW;

13장

중요내용

• USER 접두어 의미, ALL 데이터 자진 뷰의 의미와 사용방법에 대해 알아보자
• 인덱스 의미, 사용방법
• 뷰 생성 사용방법
• 시퀸스 생성, 사용방법

오라클 데이터베이스 테이블(2가지)

• 사용자 테이블(USER TABLE)
• 데이터 사전(데이터베이스를 통한 관리할 데이터를 저장한 테이블)
  • 데이터 베이스를 구성하는데 운영하는데 필요한 모든 정보를 저장하는 특수 테이블
  • 데이터베이스가 생성되는 시점에 자동으로 만들어진다.
• 용도에 따라 이름 앞에 접두어가 지정되어 분류되어 진다.
• ALL 접두어
  • 데이터사전은 오라클데이터베이스에 접속해 있는 사용자가 사용할 수 있는 공용 객체들
• SYSTEM

SELECT *
FROM DBA_TABLES;

• 등록된 사용자에 대한 정보는 DBA_USERS 테이블에 저장되어 있다.
• USERNAME이 SCOTT

-- SYSTEM으로
SELECT  *
FROM DBA_USERS
WHERE USERNAME='SCOTT';

인덱스란?

• 테이블에 보관된 특정 열 (데이터의 주소), 위치 정보를 목록을 따로 만들어 놓은 것
• 인덱스란 객체이다.
• SCOTT 계정을 통해서 INDEX를 생성하게 되면 SCOTT이 OWNER이 되고 인덱스의 정보를 열람하려면
  • 데이터 사전은 USER_INDEXS, USER_IND_COLUMNS 생성
• 인덱스는 사용자가 직접 특정 테이블의 열에 지정가능하지만 PK이거나 유니크 열 일 경우에 자동으로 인덱스가 생성
• 사용 시 직접 인덱스 생성

CREATE INDEX 인덱스이름 ON 테이블 이름(열 이름 정렬, ...열 나열);

SELECT *
FROM DICT;

SELECT *
FROM DICTIONARY;

-- SCOTT이 만든 테이블 이름만 뽑아서 보여줌, 내가 사용할 수 있는 테이블(USER)
SELECT TABLE_NAME
FROM USER_TABLES;

-- OWNER 남이 만든 테이블도 보여줌
SELECT OWNER, TABLE_NAME
FROM ALL_TABLES; -- ALL 접두어 데이터사전은 오라클데이터베이스에 접속해 있는 사용자가 사용할 수 있는 공용 객체들

-- SCOTT 계정이 소유하고 있는 INDEX 정보 살펴보기
SELECT * FROM USER_INDEXES;

-- SCOTT 계정이 소유하고 있는 인덱스 컬럼 정보 살펴보기
SELECT * FROM USER_IND_COLUMNS;

-- EMP_IND 테이블 서브쿼리로 생성해주세요
CREATE TABLE EMP_IND AS SELECT * FROM EMP;

-- EMP_IND 테이블에 SAL열에 인덱스를 생성
-- CREATE INDEX 인덱스이름 ON 테이블명(열 이름);
CREATE INDEX INDEX_EMP ON EMP_IND(SAL);

-- INDEX 컬럼 조회 :USER_IND_COLUMNS 테이블 조회
SELECT * FROM USER_IND_COLUMNS;

-- 인덱스 INDEX_EMP 삭제
DROP INDEX INDEX_EMP;

인덱스 사용 여부에 따라

• Table Full Scan
• Index Scan

뷰 - VIEW(Virtual Table)

• 하나 이상의 테이블을 조회하는 SELECT 문을 저장한 객체를 의미
• 뷰가 가장 필요한 이유는 전체 스키마 구조를 다 알 필요가 없다.
• 개발에 필요한 구조만 알고, 이에 필요한 데이터 구조를 뷰로 생성하여 데이터를 정보화 하고 처리하여 구조화한다.
• SQL문의 복잡도 완화하고, 메인 쿼리에 집중 할 수 있도록 하는데 목적
• 보안성에도 효과적

뷰 생성

• 뷰를 생성하려면, DBA에게 CREATE VIEW 권한을 부여를 받아야 한다.

GRANT CREATE VIEW TO SCOTT;

• 뷰 생성
• REPLACE
  • 기존의 것을 대체 할 것인가
  • 뷰를 수정 할 때 DROP 없이 수정이 가능하다.

-- EMP 테이블에서 DEPTNO = 20인 사원의 정보를 조회
SELECT * FROM (SELECT *
FROM EMP
WHERE DEPTNO = 20);

CREATE VIEW EMPDEPT20 AS (SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE DEPTNO = 20); 

-- VIEW의 데이터사전 USER_VIEWS 테이블
SELECT *
FROM USER_VIEWS;

SELECT *
FROM DEPT;
-- 부서번호가 20번인 사원의 사번, 사원이름, 직무, 부서명, 부서지역 저장한 뷰 EMPDEPT20 생성
-- 위에 이미 EMPDEPT20이 있어서 EMPDEPT20_1으로
CREATE VIEW EMPDEPT20_1 AS (SELECT e.EMPNO, e.ENAME, e.JOB, e.DEPTNO, d.LOC
FROM EMP e, DEPT d
WHERE e.DEPTNO = 20 AND e.deptno = d.deptno);

DROP VIEW EMPDEPT20_1;

-- 덮어쓰기 위한 REPLACE
CREATE OR REPLACE VIEW EMPDEPT20 AS (SELECT e.EMPNO, e.ENAME, e.JOB, d.deptno, d.DNAME, d.LOC
FROM EMP e JOIN DEPT d
ON e.deptno = d.deptno);
-- FORCE 옵션 사용
CREATE OR REPLACE FORCE VIEW V_EMP
AS
    SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP;
    
CREATE OR REPLACE FORCE VIEW V1_EMP
AS
    SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP WHERE DEPTNO = '10' WITH CHECK OPTION CONSTRAINT emp_ck;
    
INSERT INTO V1_EMP(empno, ename, job, deptno) VALUES(1000, 'kimjiyound', 'MANAGER', 10); -- 20이면 들어가지 않음

-- 문제 1
CREATE VIEW EMP_20 AS (SELECT *
FROM EMP
WHERE DEPTNO = 20);

SELECT *
FROM DEPT;

SELECT *
FROM EMP;

-- 문제 2
CREATE OR REPLACE VIEW DEPT_SUM AS (SELECT e.deptno, MIN(e.SAL) as 최소급여, MAX(e.SAL) as 최대급여, AVG(e.SAL) AS 평균급여
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno
GROUP BY e.deptno);

• FORCE | NOFORCE: 뷰가 테이블을 강제 생성할 것인가 말 것인가

CREATE VIEW  [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰이름(열이름1..)
AS (저장할 SELECT 문)
[WITH CHECK OPTION 제약조건]
[WITH READ ONLY 제약조건]

• DML 사용 시 (INSERT, DELETE, UPDATE)
  • 단순 뷰는 INSERT, DELETE, UPDATE가 자유롭다.(단, NOT NULL 컬럼 주의)
  • 복합뷰, 함수, UNION, GROUP BY 경우 INSERT, UPDATE, DELETE 불가능
  • 조인만 사용한 경우 제한적 가능
• 대부분의 view는 with read only로 사용하는 것이 좋다.

CREATE OR REPLACE VIEW V2_EMP
AS(
    SELECT a.empno, a.ename, a.job, to_char(d.deptno,'YYYY-MM-DD') AS hiredate, d.dname, d.loc
        FROM EMP a, DEPT d
        WHERE a.deptno = d.deptno
)with read only; -- 대부분의 view는 with read only로 사용하는 것이 좋다.

SELECT *
FROM emp;

UPDATE v2_emp SET deptno=10 WHERE empno = 7902;
-- deptno 컬럼은 dept 테이블의 컬럼을 사용했으므로 조인이기에 접근이 불가능
INSERT INTO v2_emp(empno, ename) VALUES(9999, 'test'); -- 제한적으로 허용
commit;

update v2_emp
    set hiredate = sysdate
    where empno = 9999; -- 가상 열은 사용할 수 없다. 함수를 사용해서 (hiredate) update 불가능

인라인 뷰를 이용한 TOP_N SQL 문

-- 인라인 뷰를 이용한 TOP_N SQL 문
-- ROWNUM 컬럼
-- CREATE 문을 이용해서 만들어지는 뷰 이외에 SELECT문을 이용해서 일회성으로 만들어서 사용하는 뷰 : 인라인 뷰

-- 인라인 뷰 + ROWNUM + ORDER BY 정렬된 결과 중 최상위 몇 개 데이터만 출력이 가능
SELECT ROWNUM, E.* FROM EMP E ORDER BY SAL DESC;

-- ROWNUM : 특수열 persudo column : 테이블에 저장된 행이 조회된 순서대로 매겨진 일련번호

-- 서브쿼리 사용한 인라인 뷰
SELECT ROWNUM, E.* FROM (
    SELECT *
    FROM EMP E
    ORDER BY SAL DESC
)E;

-- WITH 절을 이용한 인라인 뷰
WITH E AS (SELECT * FROM EMP E ORDER BY SAL DESC)
        SELECT ROWNUM, E.* FROM E;
        
-- 인라인 뷰로 TOP-N 추출(서브쿼리)
SELECT ROWNUM, E.*
    FROM(
    SELECT * FROM EMP E ORDER BY SAL DESC
    )E WHERE ROWNUM <=3;
    
SELECT ROWNUM, E.*
    FROM(
    SELECT ENAME, JOB, MGR, HIREDATE, NVL(SAL, 0) AS SAL, COMM, DEPTNO FROM EMP E ORDER BY SAL DESC
    )E WHERE ROWNUM <=3;  
    
select rownum,e.* from(
select * from emp e order by nvl(sal,0) desc)e where rownum <=3;    
    
-- WITH 절
WITH E AS (SELECT ENAME, JOB, MGR, HIREDATE,NVL(SAL, 0)AS SAL, COMM, DEPTNO FROM EMP E ORDER BY SAL DESC)
SELECT ROWNUM, E.*
   FROM E 
      WHERE ROWNUM <= 3;

시퀸스

• 번호 생성기, 연속해서 새로운 번호를 생성하는 일 + 1

-- 시퀸스(SEQUENCE) : 번호 생성기, 연속해서 새로운 번호를 생성하는 일 + 1
-- SELECT MAX(글번호) + 1 FROM 게시판 테이블;
-- CREATE SEQUENCE 시퀸스 이름
-- INCREMENT BY N 기본값 1
-- START WITH N 기본값 1
-- MAX VALUE N
-- MIN VALUE N
--CYCLE | NOCYCLE
--CACHE N NOCACHE

-- DEPT 테이블을 사용하여 DEPT_SEQUENCE 테이블 생성
CREATE TABLE DEPT_SEQUENCE AS SELECT * FROM DEPT WHERE 1<>1;

-- 1. SEQ_DEPT 생성
CREATE SEQUENCE SEQ_DEPT
    INCREMENT BY 10
    START WITH 0
    MAXVALUE 100
    MINVALUE 0
    NOCYCLE
    CACHE 2;
    
-- 2. SEQUENCE 데이터 사전 확인 : USER_SEQUENCES 테이블 확인   
SELECT * FROM USER_SEQUENCES;

-- 3. 사용
INSERT INTO DEPT_SEQUENCE(DEPTNO, DNAME, LOC) VALUES (SEQ_DEPT.NEXTVAL,'CONSERTING', 'BUSAN');
INSERT INTO DEPT_SEQUENCE(DEPTNO, DNAME, LOC) VALUES (SEQ_DEPT.NEXTVAL,'IT', 'INCHON');
INSERT INTO DEPT_SEQUENCE(DEPTNO, DNAME, LOC) VALUES (SEQ_DEPT.NEXTVAL,'ACCOUNTING', 'SEOUL');
SELECT * FROM DEPT_SEQUENCE;

-- 4. 현재 값 확인 시
SELECT SEQ_DEPT.CURRVAL FROM DUAL;

-- 시퀸스 수정 ALTER
ALTER SEQUENCE SEQ_DEPT INCREMENT BY 20 MAXVALUE 1000 CYCLE;

동의어

• 권한을 주어야 함
• 동의어(SYNONYM) 테이블, 뷰, 시퀸스 객체들에게 객체이름 대신 부르는 별명: 되도록 짧고 부르기 쉽고 단번에 알아듣기 쉬운 별명

GRANT CREATE PUBLIC SYNONYM TO SCOTT;

CREATE SYNONYM E FOR EMP;

SELECT * FROM E;

예제

-- #1. 도서테이블(BOOK)
CREATE TABLE BOOK(
    BOOK_NO NUMBER(10) NOT NULL,
    BOOK_NAME VARCHAR2(20) NOT NULL,
    BOOK_PUB VARCHAR2(20),
    BOOK_PRICE NUMBER(20),
    PRIMARY KEY(BOOK_NO)
);

-- #2. 고객테이블(CONSUMER), SEQUENCE CONSUMER_NO 시퀀스를 사용하여 부여
CREATE TABLE CONSUMER (
  CONSUMER_NO NUMBER(10) NOT NULL,
  CONSUMER_NAME VARCHAR2(50) NOT NULL,
  CONSUMER_EDDRESS VARCHAR2(100) NOT NULL,
  CONSUMER_PHONE VARCHAR2(20) NOT NULL,
  PRIMARY KEY (CONSUMER_NO)
);
 

-- #3. SEQUENCE_CONSUMER_NO 시퀀스 생성 
CREATE SEQUENCE SEQUENCE_CONSUMER_NO 
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 100
    MINVALUE 0
    NOCYCLE
    CACHE 2;

-- #4. 주문테이블
CREATE TABLE ORDERS(
    ORDER_NO NUMBER(10) NOT NULL,
    CONSUMER_NO NUMBER(10) NOT NULL,
    BOOK_NO NUMBER(20) NOT NULL,
    ORDER_DAY DATE,
    ORDER_PRICE NUMBER(20),
    PRIMARY KEY(ORDER_NO),
    CONSTRAINT FK_ORDERS_CONSUMER FOREIGN KEY (CONSUMER_NO) REFERENCES CONSUMER(CONSUMER_NO),
    CONSTRAINT FK_ORDERS_BOOK FOREIGN KEY (BOOK_NO) REFERENCES BOOK(BOOK_NO)
);

-- #5. SEQUENCE _ORDER_NO시퀀스 생성
CREATE SEQUENCE SEQUENCE_ORDER_NO
    INCREMENT BY 10
    START WITH 0
    MAXVALUE 100
    MINVALUE 0
    NOCYCLE 
    CACHE 2;    
    
-- 6. 도서 상품 (BOOK) 테이블에 3가지 상품 등록(입력)
INSERT INTO BOOK VALUES(123, '열의의 힘', '한빛출판사', 30000);
INSERT INTO BOOK VALUES(124, '자바 코딩 인터뷰', '미루출판사', 15000);
INSERT INTO BOOK VALUES(130, '세상에서 가장 한국사', '뻥출판사', 17000);

COMMIT;

SELECT *
FROM BOOK;

-- 7. 고객 2명 등록 (CONSUMER) 
INSERT INTO CONSUMER (CONSUMER_NO, CONSUMER_NAME, CONSUMER_EDDRESS, CONSUMER_PHONE) 
VALUES (SEQ_CONSUMER_NO.NEXTVAL, '이금주', '서울시 서대문구', '010-2035-6118');
INSERT INTO CONSUMER (CONSUMER_NO, CONSUMER_NAME, CONSUMER_EDDRESS, CONSUMER_PHONE) VALUES(SEQUENCE_CONSUMER_NO.NEXTVAL, '김신지', '전라남도 강진', '010-6533-3333');
COMMIT;

SELECT *
FROM CONSUMER;

-- 8. 주문 2건 이상 등록(Orders) 
INSERT INTO ORDERS(ORDER_NO , CONSUMER_NO, BOOK_NO, ORDER_DAY, ORDER_PRICE) VALUES(SEQUENCE_ORDER_NO.nextval, 1, 123, '2023-07-01', 30000);
INSERT INTO ORDERS (ORDER_NO , CONSUMER_NO, BOOK_NO, ORDER_DAY, ORDER_PRICE) VALUES(SEQUENCE_ORDER_NO.nextval, 2, 130, SYSDATE, 17000);
COMMIT;

SELECT *
FROM ORDERS;

회고

개인적인 TMI이지만 요즘.. 다이어트도 같이 병행중인데... 진짜 너무 배로 피곤하다... 힘이 없는 느낌? 맛있는거 먹고 싶다아아아