DAY29
Q1) 테이블 수정 - 이름변경
CREATE TABLE TB_EXAM(
COL1 CHAR(3) PRIMARY KEY,
ENAME VARCHAR2(20),
FOREIGN KEY (COL1) REFERENCES EMPLOYEE); -> EMPLOYEE의 PK를 참조하게 된다.
Q2) 컬럼이름을 조회하자
SELECT COLUMN_NAME
FROM USER_TAB_COLS -> 생성된 테이블의 정보를 가진 테이블
WHERE TABLE_NAME = 'TB_EXAM';
DESC USER_TAB_COLS;
Q3) 제약 조건의 현황을 조회해보자
SELECT CONSTRAINT_NAME AS 이름,
CONSTRAINT_TYPE AS 유형,
COLUMN_NAME AS 컬럼,
R_CONSTRAINT_NAME AS 참조,
DELETE_RULE AS 삭제규칙
FROM USER_CONSTRAINTS
JOIN USER_CONS_COLUMNS USING(CONSTRAINT_NAME,TABLE_NAME)
WHERE TABLE_NAME = 'TB_EXAM';
Q4) Q1에서 생성한 테이블의 컬럼이름을 변경해보자 COL1 -> EMPID
ALTER TABLE TB_EXAM
RENAME COLUMN COL1 TO EMPID;
Q5) 제약 조건 이름 변경 SYS_C007042(항상 이름이 달라지므로 확인) -> PK_EID
ALTER TABLE TB_EXAM
RENAME CONSTRAINTS SYS_C007042 TO PK_EID;
Q6) 참조 제약 조건의 이름을 FK_EID로 변경해보자.
ALTER TABLE TB_EXAM
RENAME CONSTRAINTS SYS_C007043 TO FK_EID;
Q7) 테이블 이름 변경
ALTER TABLE TB_EXAM RENAME TO TB_SAMPLE;
RENAME TB_EXAM TO TB_SAMPLE;
Q8) 테이블 삭제 - ,로 나열해서 여러개 삭제 불가
DROP TABLE table_name[CASCADE CONSTRAINTS];
- 포함된 데이터 및 테이블과 관련된 데이터 딕셔너리 정보까지 모두 삭제
- 삭제 작업은 복구할 수 없다.
- CASCADE CONSTRAINTS : 삭제 대상 테이블의 PK 또는 U 제약 조건을 참조하는 다른 제약 조건을 삭제하는 옵션
참조중인 제약조건이 있는 경우 옵션이 미 사용시 삭제할 수 없다.
CREATE TABLE MY_DEPT( - 주테이블
DID CHAR(2) PRIMARY KEY,
DNAME VARCHAR2(10));
CREATE TABLE MY_EMP02( - 종테이블
COL1 CHAR(3) PRIMARY KEY,
ENAME VARCHAR2(20),
DID CHAR(2) REFERENCES MY_DEPT);
- 강한 주종관계
- 약한 주종관계
DROP TABLE MY_DEPT;
-> ORA-02449: unique/primary keys in table referenced by foreign keys // 옵션을 주어야 테이블 삭제 가능
DROP TABLE MY_DEPT CASCADE CONSTRAINTS;
INSERT INTO MY_DEPT VALUES(10,'10');
INSERT INTO MY_DEPT VALUES(20,'10');
INSERT INTO MY_DEPT VALUES(30,'10');
INSERT INTO MY_DEPT VALUES(40,'10');
INSERT INTO MY_EMP02 VALUES(1,'1',10);
INSERT INTO MY_EMP02 VALUES(2,'2',40);
Q9) 컬럼 삭제 : 삭제되는 컬럼을 참조하고 있는 다른 컬럼에 설정된 제약조건까지 함께 삭제된다.
CREATE TABLE TB1(
PK NUMBER PRIMARY KEY,
FK NUMBER REFERENCES TB1,
COL1 NUMBER,
CHECK (PK > 0 AND COL1 >0));
SELECT CONSTRAINT_NAME AS 이름,
CONSTRAINT_TYPE AS 유형,
COLUMN_NAME AS 컬럼,
R_CONSTRAINT_NAME AS 참조,
DELETE_RULE AS 삭제규칙
FROM USER_CONSTRAINTS
JOIN USER_CONS_COLUMNS USING(CONSTRAINT_NAME,TABLE_NAME)
WHERE TABLE_NAME ='TB1';
ALTER TABLE TB1
DROP (PK); -> 오류 : PK를 참조하고 있는 FK가 있어서
ALTER TABLE TB1
DROP (COL1); -> 오류 : CHECK 제약조건때문에
ALTER TABLE TB1
DROP (PK) CASCADE CONSTRAINTS;
ALTER TABLE TB1
DROP (COL1) CASCADE CONSTRAINTS;Q10) VIEW
- 다른테이블이나 뷰에 포함된 데이터의 맞춤 표현
- STORED QUERY, VIRTUAL TABLE로 간주되는 데이터 베이스 객체
- 하나 이상의 (1 MORE) 테이블/뷰에 포함된 데이터 부분 집합을 나타내는 논리적인 객체
- 자체적으로 데이터를 포함하지 않는다.
- 베이스 테이블에 있는 데이터를 조건이나 또는 조인 등을 이용해서 참조하는 형식
[사용목적 및 장점]
- Restricted data access : 뷰에 접근하는 사용자는 미리 정의된 결과만 볼 수 있다. (데이터 보호)
- Hide data complexity : 여러 테이블을 조인하게 되면 복잡한 sql을 숨길 수 있다.
- Simplify statement for the user : sql구문을 몰라도 간단한 select 구문만으로도 원하는 결과를 조회할 수 있다.
- Present the data in a different perspective : 뷰에 포함되는 컬럼은 참조 대상 테이블에 영향을 주지 않고
다른 이름으로 참조가 가능하다.
- Isolate applications from changes in definitions of base tables : 베이스 테이블에 포함된 여러개 컬럼중
일부만 사용하도록 뷰를 생성할 경우 뷰가 참조 되지 않는 나머지 컬럼이 변경되어도 뷰를 사용하는
다른 프로그램은 영향을 받지 않는다.
- Save complex queries : 자주 사용하는 복잡한 sql문을 뷰 형태로 저장하면 반복적으로 사용가능
[생성 구문]
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [(alias[,alias,,,,])]
AS subquery
[WITH CHECK OPTION[CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint_name]];
[구문 설명]
CREATE [ON REPLACE]:지정한 뷰가 없으면 새로 생성, 동일이 존재하면 수정 (over_write)
[FORCE | NOFORCE] : 원본 테이블이 존재하지 않아도 뷰 생성가능 | 존재하는 경우에만 뷰생성가능
alias : 뷰에서 사용할 이름
subquery : 뷰에서 표현하는 데이터를 생성하는 select 구문
제약조건 :
WITH CHECK OPTION : 뷰를 통해 접근 가능한 데이터 베이스에 대해서만 DML 작업 허용
WITH READ ONLY : 뷰를 통해 DML 작업 허용 안하겠다.
Q11) 사원테이블에서 부서번호가 90번 데이터를 가진 V_EMP인 VIEW를 생성해보자.
CREATE OR REPLACE VIEW V_EMP
AS SELECT EMP_NAME,DEPT_ID
FROM EMPLOYEE
WHERE DEPT_ID = '90';
SELECT * FROM V_EMP;
SELECT COLUMN_NAME, DATA_TYPE,NULLABLE
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'V_EMP';
Q12) 사원의 이름, 부서명, 직급을 출력하는 V_EMP_DEPT_JOB 이름의 VIEW 를 생성해보자
=> EMPLOYEE,DEPARTMENT, JOB
CREATE OR REPLACE VIEW V_EMP_DEPT_JOB
AS SELECT EMP_NAME, DEPT_NAME, JOB_TITLE
FROM EMPLOYEE LEFT JOIN DEPARTMENT USING(DEPT_ID)
LEFT JOIN JOB USING (JOB_ID) // INNER JOIN X (NULL값을 가지고 있는 염정하 출력 안됨)
WHERE JOB_TITLE = '사원';
SELECT * FROM V_EMP_DEPT_JOB;
=> 뷰는 REPLACE로 인해서 덮어쓰기 가능
Q13) 별칭을 사용한 VIEW 생성
CASE1)
CREATE OR REPLACE VIEW V_EMP_DEPT_JOB (ENM,DNM,TITLE)
AS SELECT EMP_NAME, DEPT_NAME, JOB_TITLE
FROM EMPLOYEE LEFT JOIN DEPARTMENT USING(DEPT_ID)
LEFT JOIN JOB USING (JOB_ID)
WHERE JOB_TITLE = '사원';
SELECT * FROM V_EMP_DEPT_JOB;
CASE2)
CREATE OR REPLACE VIEW V_EMP_DEPT_JOB
AS SELECT EMP_NAME AS A1, DEPT_NAME D1, JOB_TITLE T1
FROM EMPLOYEE LEFT JOIN DEPARTMENT USING(DEPT_ID)
LEFT JOIN JOB USING (JOB_ID)
WHERE JOB_TITLE = '사원';
Q14) 조건 함수 사용 VIEW 생성
CREATE OR REPLACE VIEW V_EMP ("ENM","Gender","Years")
// 조건 함수(DECODE, ROUND 등..) 사용하는 VIEW는 컬럼의 별칭(alias) 필수!!
AS
SELECT EMP_NAME,
DECODE (SUBSTR(EMP_NO,8,1),'1','남자','3','남자','여자'),
ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12,0)
FROM EMPLOYEE;
=> 별칭이 없을 경우 오류 발생
CASE1)
CREATE OR REPLACE VIEW V_EMP
AS
SELECT EMP_NAME,
DECODE (SUBSTR(EMP_NO,8,1),'1','남자','3','남자','여자'),
ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12,0)
FROM EMPLOYEE;
CASE2)
CREATE OR REPLACE VIEW V_EMP ("Gender","Years") // 별칭의 개수와 컬럼의 개수가 달라도 오류
AS
SELECT EMP_NAME,
DECODE (SUBSTR(EMP_NO,8,1),'1','남자','3','남자','여자'),
ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12,0)
FROM EMPLOYEE;
별칭을 선택적으로 선언할 수 있다.
CREATE OR REPLACE VIEW V_EMP
AS
SELECT EMP_NAME,
DECODE (SUBSTR(EMP_NO,8,1),'1','남자','3','남자','여자') AS Gender,
ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12,0) AS Years
FROM EMPLOYEE;
Q15) 뷰 생성 제약조건 : WITH READ ONLY
CREATE OR REPLACE VIEW V_EMP
AS
SELECT * FROM EMPLOYEE
WITH READ ONLY; -> DML 허용하지 않는다.
UPDATE V_EMP
SET PHONE = NULL; -> READ ONLY라서 오류
INSERT INTO V_EMP(EMP_ID,EMP_NAME,EMP_NO)
VALUES ('777','홍길동','666666-6666666'); -> READ ONLY라서 오류
DELETE FROM V_EMP;
Q16) 뷰 생성 제약조건 : WITH CHECK OPTION - 조건에 따라 INSERT / UPDATE 작업 제한 (DELETE는 제한 없음)
CREATE OR REPLACE VIEW V_EMP
AS
SELECT EMP_ID,EMP_NAME,EMP_NO , MARRIAGE
FROM EMPLOYEE
WHERE MARRIAGE = 'N'
WITH CHECK OPTION;
INSERT INTO V_EMP(EMP_ID,EMP_NAME,EMP_NO,MARRIAGE)
VALUES ('777','홍길동','666666-6666666','Y'); -> CHECK OPTION 오류
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS
WHERE VIEW_NAME = 'V_EMP';
Q17) 뷰 - 데이터 조회 절차
뷰를 사용한 SQL 구문 해석 -> 데이터 딕셔너리 "USER_VIEWS"에서 뷰 정의 검색
-> SQL 구문을 실행한 계정이 관련된 베이스 테이블 (원본) 테이블에 접근하여 SELECT 권한 확인
-> 뷰 대신 원본 테이블을 기반으로 하는 동등한 작업으로 변환
-> 베이스 테이블 (원본)을 대상으로 데이터를 조회
Q18) 뷰 삭제 -> DROP VIEW view_name;Q19) 시퀀스 개념 : 순차적으로 정수 값을 자동으로 생성하는 객체 .nextval , .currval
CREATE SEQUENCE user_name
INCREMENT BY n ----> 시퀀스 번호 증가 / 감소 (default 1)
START WITH n ----> 시퀀스 시작 값
{ MAXVALUE n | NOMAXVALUE } ----> 시퀀스 최대값 (NOMAXVALUE : 10의 27승을 기점으로 사용된다)
{ MINVALUE n | NOMINVALUE } ----> 시퀀스 최소값 (NOMINVALUE : -10의 26승을 기점으로 사용된다)
{ CYCLE | NOCYCLE } ----> 최대/최소 값 도달하게 되면 반복 여부 결정
{ CACHE n | NOCACHE } ----> 지정한 수량 만큼 미리 메모리에 생성여부 결정 (최소값2 , 기본값 20)
Q20) 300부터 310번까지 5개씩 증가되는 SEQ_EMPID라는 객체를 만들자
CREATE SEQUENCE SEQ_EMPID
START WITH 300
INCREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;
SELECT SEQ_EMPID.NEXTVAL FROM DUAL;
SELECT SEQ_EMPID.CURRVAL FROM DUAL; //NEXTVAL을 실행하지 않으면 CURRVAL(현재값?)은 실행할 수 X
=> STATIC
Q21) 5부터 15까지 5개씩 증가되는 SEQ_EMPID02라는 객체를 만들자
CREATE SEQUENCE SEQ_EMPID02
START WITH 5
INCREMENT BY 5
MAXVALUE 15
CYCLE
NOCACHE;
SELECT SEQ_EMPID02.NEXTVAL FROM DUAL;
=> MAXVALUE(15)까지 가게되면 1로 돌아감
INSERT INTO TEST(COL1) VALUES(SEQ_EMPID02.NEXTVAL);
INSERT INTO TEST(COL1) VALUES(SEQ_EMPID02.NEXTVAL);
SELECT SEQ_EMPID02.CURRVAL FROM DUAL;
SELECT * FROM TEST;
Q22) 시퀀스를 수정해보자 단 시퀀스는 START WITH는 수정할 수 없다.
SEQ_EMPID02의 3씩 증가하면서 10까지, 반복하지 않는 시퀀스로 변경해보자
ALTER SEQUENCE SEQ_EMPID02
INCREMENT BY 3
MAXVALUE 10
NOCYCLE
NOCACHE;
CREATE SEQUENCE SEQ01
START WITH 1
INCREMENT BY 3
NOCYCLE
NOCACHE;
SELECT SEQ01.NEXTVAL FROM DUAL;
SELECT SEQ01.CURRVAL FROM DUAL;
DESC USER_SEQUENCES; -> 시퀀스 딕셔너리?
SELECT * FROM USER_SEQUENCES;Q23) INDEX : 책의 목차와 같은 색인을 의미 한다. ex) INDEX(키워드) -------- 128P (위치)
- 키워드와 해당 내용의 위치가 정렬된 상태로 구성된다.
- 키워드를 이용해서 내용을 빠르게 찾는 목적을 가진다.
- 데이터 베이스에서 인덱스는 컬럼값을 이용해서 원하는 행을 빠르게 찾기 위한 목적
DEPT테이블에 DEPTNO가 있다라고 생각하자
20 10 50 60 80 90 30
-> 60번을 찾는다
INDEX를 지정하지 않을 경우 : 20 10 50 60 80 90 30로 나열된 데이터의 (ROWID로 정렬을 한 다음)
데이터를 찾게된다.
-> 60
10 90
20 50 30 80
INDEX를 DEPTNO로 지정할 경우 : 20 10 50 60 80 90 30를 10 20 30 50 60 80 90 정렬을 한 다음
해당 ROWID를 위치로 삼아 찾게된다
SELECT ROWID,DEPT_ID FROM DEPARTMENT;
SELECT ROWID,EMPNO FROM EMP;
[생성]
CREATE [UNIQUE] INDEX index_name ON table_name (column_list | function, expr);
ex)
CREATE UNIQUE INDEX IDX_DNM ON DEPARTMENT (DEPT_NAME);
CREATE INDEX IDX_JID ON EMPLOYEE(JOB_ID);
UNIQUE : 중복값 포함될 수 없다.
오라클은 PK제약조건을 생성하면 자동으로 해당 컬럼에 UNIQUE INDEX를 생성
PK를 사용하게 되면 access를 하는데 성능 효과를 가진다.
Non_UNIQUE : 빈번하게 사용되는 일반 컬럼을 대상으로 생성함
USER_INDEXES -> 정보를 가진 테이블
USER_IND_COLUMNS -> 인덱스가 지정된 컬럼의 정보
Q24) EMPLOYEE 테이블의 EMP_NAME 컬럼에 IDX_ENM 이름의 UNIQUE INDEX를 생성하시오
CREATE UNIQUE INDEX IDX_ENM ON EMPLOYEE(EMP_NAME); -> UNIQUE 제약조선 수행
INSERT INTO EMPLOYEE (EMP_ID,EMP_NO,EMP_NAME)
VALUES ('400','800000-0000000','감우섭'); -> ORA-00001 오류 (중복값 설정할 수 없다 오류)
CREATE UNIQUE INDEX IDX_DID ON EMPLOYEE (DEPT_ID); -> ORA-01452 오류
DROP INDEX IDX_ENM;
Q25) EMPLOYEE에 생성된 인덱스 조회
SELECT INDEX_NAME,COLUMN_NAME,INDEX_TYPE,UNIQUENESS
FROM USER_INDEXES
JOIN USER_IND_COLUMNS USING(INDEX_NAME,TABLE_NAME)
WHERE TABLE_NAME = 'EMPLOYEE';
Q26) DML (Data Manipulation Language) : update, insert, delete, transaction, lock
- UPDATE table_name
SET column_name = value [,column_name = value,,,,] or subquery, default 옵션
[WHERE condition]; -> where가 생략되면 전체 행이 갱신된다.
90번 부서의 부서명을 '전략기획팀'으로 변경해보자
SELECT * FROM DEPARTMENT;
UPDATE DEPARTMENT
SET DEPT_NAME = '전략기획팀'
WHERE DEPT_ID = '90';
ROLLBACK; // 방금전에 한 DML만 되돌릴수 있음
Q27) EMPLOYEE 테이블에서 '심하균'의 직업과 월급을 성해교랑 같은 직업과 월급으로 변경해라
CASE1)
UPDATE EMPLOYEE
SET (JOB_ID,SALARY) = (SELECT JOB_ID,SALARY
FROM EMPLOYEE
WHERE EMP_NAME = '성해교')
WHERE EMP_NAME = '심하균';
CASE2)
UPDATE EMPLOYEE
SET JOB_ID= (SELECT JOB_ID
FROM EMPLOYEE
WHERE EMP_NAME = '성해교'),
SALARY = (SELECT SALARY
FROM EMPLOYEE
WHERE EMP_NAME = '성해교')
WHERE EMP_NAME = '심하균';
SELECT EMP_NAME,JOB_ID,SALARY
FROM EMPLOYEE
WHERE EMP_NAME IN ('성해교','심하균');
Q28) DELETE - 테이블 전체 데이터 삭제
- DELETE FROM TEST;
- TRUNCATE TABLE table_name : 조건없이 테이블 전체 삭제 -> ROLLBACK이 불가능, 제약조건이 있는 테이블은 삭제가 불가능
ex) TRUNCATE TABLE 명령을 사용해서 전체 데이터를 삭제할 때는
1) 참조되는 테이블의 제약조건을 DISABLE로 지정한다.
ALTER TABLE EMPLOYEE
DISABLE CONSTRAINTS FK_DEPTID;
2) 전체 내용을 삭제한다.
TRUNCATE TABLE DEPARTMENT;
Q29) 다양한 삭제 룰을 실행해보자.
DELETE
FROM JOB
WHERE JOB_ID = 'J2'; ->오류 ORA-02292: integrity constraint (TEST.FK_MGRID) violated - child record found
DELETE
FROM EMPLOYEE
WHERE EMP_ID = '141'; ->오류 ORA-02292: integrity constraint (TEST.FK_MGRID) violated - child record found
Q30) 주종관계가 있을 때 삭제할 수 없는 상태가 된다. 만일 삭제를 하고 싶다면 삭제 룰에서 SET NULL을 사용한다.
SELECT CONSTRAINT_NAME AS 이름,
CONSTRAINT_TYPE AS 유형,
COLUMN_NAME AS 컬럼,
R_CONSTRAINT_NAME AS 참조,
DELETE_RULE AS 삭제규칙
FROM USER_CONSTRAINTS
JOIN USER_CONS_COLUMNS USING(CONSTRAINT_NAME,TABLE_NAME)
WHERE TABLE_NAME = 'EMPLOYEE';
1) 기존 제약 조건을 삭제한다.
ALTER TABLE EMPLOYEE DROP CONSTRAINTS FK_MGRID;
2) 제약조건을 추가하되 옵션을 지정한다.
ALTER TABLE EMPLOYEE
ADD CONSTRAINTS FK_MGRID FOREIGN KEY(MGR_ID)
REFERENCES EMPLOYEE ON DELETE SET NULL;
3) 데이터를 삭제한다
DELETE
FROM EMPLOYEE
WHERE EMP_ID = '141';
Q31) 다양한 삭제룰을 실행해보자.
주종테이블에서 주테이블의 내용이 삭제될 때 종 테이블의 데이터도 삭제하자
SELECT EMP_ID,EMP_NAME,JOB_ID
FROM EMPLOYEE
WHERE JOB_ID = 'J2';
SELECT * FROM JOB;
1) 기존 제약 조건을 삭제한다.
ALTER TABLE EMPLOYEE DROP CONSTRAINTS FK_JOBID;
2) 제약조건을 추가하되 옵션을 지정한다.
ALTER TABLE EMPLOYEE
ADD CONSTRAINTS FK_JOBID FOREIGN KEY(JOB_ID)
REFERENCES JOB ON DELETE CASCADE;
3) 데이터를 삭제한다
DELETE
FROM JOB
WHERE JOB_ID = 'J2';