SQL - 6 (View & Sequence & Index)

■ view를 시작하기 이전에

 

□ 테이블 삭제

 

DELETE FROM -- 자료를 삭제, recover가 가능

TRUNCATE TABLE tel77; -- 내용이 지워짐(구조는 남는다)
DESC tel77;
SELECT * FROM tel77; -- 아무것도 없음을 확인

DROP TABLE tel77;

 

SELECT * FROM TAB; -- BIN$...확인

FLASHBACK TABLE tel77 TO BEFORE DROP;
-- 휴지통에 있는 테이블을 살린 것

SELECT * FROM TAB;

-- 완전히 지우는 방법은?

DROP TABLE tel77 PURGE; -- 완전삭제
DROP TABLE tel77 CASCADE CONSTRAINTS;

 

□ Dictionary

 

SELECT * FROM DICTIONARY;

 

USER_ : 소유권이 사용자인 정보
ALL_ : 소유권자가 SYSTEM, 단 사용자가 접근은 가능함.
DBA_ : DBA 권한자만이 접근 
V$ : DBA, 현재 DB 상태정보

 

-- Q) employees 찾기

 

SELECT * FROM DICTIONARY
WHERE TABLE_NAME LIKE 'USER_%';

-- 너무 많은 데이터가 나온다.

 

DESC USER_OBJECTS;

 

-- 사용자가 OWNER인 TABLE 다 나와.

SELECT OBJECT_NAME FROM USER_OBJECTS 
WHERE OBJECT_TYPE = 'TABLE';

 

-- Q) employees 테이블의 제약조건을 조회 출력하시오.

 

DESC USER_CONSTRAINTS;

SELECT OWNER FROM USER_CONSTRAINTS
WHERE table_name = 'EMPLOYEES';

 

SELECT * FROM USER_CONSTRAINTS
WHERE table_name = 'EMPLOYEES';

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE table_name = 'EMPLOYEES';

 

 

-- P : PK / U : UNIQUE / C : CHECK / R : FK가 참조하는 제약조건 이름

 

-- 컬럼의 제약조건을 조회출력 (USER_CONS_COLUMNS)

 

SELECT * FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES';

 

 

 

SELECT * 
FROM user_cons_columns
WHERE table_name = 'MEMTABLE2';

 

-- 컬럼 변경하기

 

CREATE TABLE modi_col(
id NUMBER(5),
name VARCHAR22(20),
ipsail DATE DEFAULT sysdate
);

 

 

-- 부서 칼럼을 추가
-- buseo varchar2(20)
-- 부서는 insa, jajae 부서만 입력

-- hint alter table 테이블명

-- add (컬럼을 추가, 추가하는 김에 제약조건도)

 

ALTER TABLE modi_col
ADD(buseo VARCHAR2(20)
CONSTRAINT modi_col_buseo_ck CHECK(buseo IN('insa','jajae')));
DESC modi_col;

 

-- name 컬럼을 irum으로 변경하시오

 

ALTER TABLE modi_col
RENAME COLUMN name TO irum;

 

-- irum 컬럼 삭제

 

ALTER TABLE modi_col
DROP COLUMN irum;

 

-- id컬럼을 7칸으로 늘리시오

 

ALTER TABLE modi_col
MODIFY(id NUMBER(7));

 

-- 제약조건을 추가/삭제하기

 

-- 컬럼의 제약조건을 조회출력 (user_cons_columns)

 

SELECT * FROM user_cons_columns
WHERE table_name = 'MODI_COL';
DESC modi_col;

 

-- 제약조건을 추가

 

ALTER TABLE modi_col
ADD CONSTRAINT modi_col_id_uk UNIQUE(id);

 

-- 제약조건을 삭제

 

ALTER TABLE modi_col
DROP CONSTRAINT modi_col_id_uk;

 

--  pk 삭제하는 데 관련된 fk 제약조건까지 삭제하고 싶을 때

 

ALTER TABLE modi_col
DROP PRIMARY KEY CASCADE;

 

-- NOT NULL 제약조건 추가하기

 

(참고) not null의 특징 : 테이블 레벨 방식 사용 x

 

ALTER TABLE modi_col
ADD CONSTRAINT modi_col_buseo_nn NOT NULL(buseo);
    
ALTER TABLE modi_col
MODIFY( buseo VARCHAR2(20)
CONSTRAINT modi_col_buseo_nn NOT NULL);

 

■ VIEW

 

□ RDB Object 종류

 

table, index, view, sequence

 

1) 테이블의 일부 추출개념
2) 테이블 내용 x, 문장 o
3) 보안효과

 

-- Q) 비상연락망을 위해 부서아이디가 100번인 사원의 이름과 전화번호만이 들어가는 table을 만드시오.

-- create subquery

 

ALTER TABLE CONSTRAINT DROP PRIMARY KEY CASCADE MODIFY
CREATE SELECT FROM WHERE
    
CREATE TABLE dept_100_table
AS SELECT first_name, phone_number
FROM employees
WHERE department_id = 100;
 
SELECT * FROM dept_100_table;
 
CREATE view dept_100_view
AS SELECT first_name, phone_number
FROM employees
WHERE department_id = 100;
    
SELECT * FROM dept_100_view;
    
SELECT * FROM DICTIONARY
WHERE TABLE_NAME LIKE 'U%'
ORDER BY 1;
    
DESC user_views;
    
SELECT view_name, TEXT
FROM user_views;

 

 

dept_100_view를 확인하고 그 옆의 text를 보니
"SELECT first_name, phone_number
FROM employees
WHERE department_id = 100"

 

-- Q) 100부서 아이디가 아닌 80번 부서 아이디로 내용변경을 원할 때

 

CREATE VIEW dept_100_view
AS SELECT first_name, phone_number
FROM employees
whWHEREere department_id = 80;

 

오류 보고 -
ORA-00955: 기존의 객체가 이름을 사용하고 있습니다.
00955. 00000 -  "name is already used by an existing object"

 

만일 수정도 하고 싶으면 처음부터 수정할수 있도록 만들어야 함

 

CREATE OR REPLACE VIEW dept_80_view
AS SELECT first_name, phone_number
FROM employees
WHERE department_id = 80;

 

-- Q) dept_80_view의 내용을 부서아이디 50으로 변경하고 싶을 때

 

CREATE or REPLACE VIEW dept_80_view
AS SELECT first_name, phone_number
FROM employees
WHERE department_id = 50;

 

 select * from dept_80_view;

-- 이름은 80번이지만 내용은 50번 부서가 들어감

 

-- WITH CHECK option

-- 조건문에 있는 칼럼은 view로 변경을 못하도록 하자

 

-- Q) manager_id가 121인 사원을 모두 출력하는 view를 작성하시오

 

SELECT *
FROM employees
WHERE manager_id = 121;
    
CREATE or replace view mana_121_view
AS SELECT *
FROM employees
where manager_id = 121;
    
SELECT * FROM mana_121_view;

UPDATE mana_121_view
SET manager_id = 150
WHERE manager_id = 130;

 

-- 이런 문제점을 아예 해결하려면

 

CREATE OR REPLACE VIEW mana2_121_view
AS SELECT *
FROM employees
WHERE manager_id = 121
WITH CHECK OPTION;

UPDATE mana2_121_view
SET manager_id = 150
WHERE manager_id = 130;

 

-- view를 수정하지 못하도록

 

CREATE or REPLACE VIEW mana2_122_view
AS SELECT *
FROM employees
WHERE manager_id = 122
WITH READ ONLY;

SELECT * FROM mana2_122_view;

UPDATE mana2_122_view
SET manager_id = 121
WHERE manager_id = 130;

 

■ SEQUENCE

 

- 일련번호

- 초기치, 목적치, 증가치

 

SELECT * FROM modi_col;

CREATE SEQUENCE sequence_이름;
CREATE SEQUENCE modi_col_seq;

SELECT modi_col_seq.NEXTVAL from dual;
SELECT modi_col_seq.CURRVAL from dual;

CREATE TABLE table211(
bun NUMBER(7),
sabun NUMBER(5)
);

CREATE SEQUENCE t211_seq;

   
INSERT INTO table211
VALUES(t211_seq.NEXTVAL, 12345);
-- values(1, 12345);
   
INSERT INTO table211
VALUES(t211_seq.NEXTVAL, 12347);

SELECT * FROM table211;

 

 

-- 10 11
CREATE SEQUENCE bunho
INCREMENT BY 5 -- 증가치
START WITH 10 -- 초기치
MAXVALUE 30000 -- 목적치
NOCYCLE
NOCACHE -- default 20

 

-- 게시판에서 일련번호, sql

 

NUM
3
2
1
SELECT MAXNUMnum)+1 FROM;

 

■ INDEX

 

-- 책의 목차와 비슷
-- 속도 유리 BUT 무조건 속도가 빠르진 않음

 

SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.salary > 2000;
   
CREATE INDEX idx_emp_sal ON employees(salary);
   
SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e , departments d
WHERE e.department_id = d.department_id
AND e.salary > 2000;
   
ALTER INDEX idx_emp_sal REBUILD;