INSERT INTO EMP05(EMPNO,ENAME,DEPTNO) VALUES(1000,'John',10); COMMIT;
----->EMP05테이블 생성완료
ALTER TABLE DEPT05 DROP PRIMARY KEY;-->부모테이블 DEPT05삭제하러하면 에러발생
ALTER TABLE DEPT05 DROP PRIMARY KEY CASCADE;-->>CASCADE쓰면 FK제약조건 삭제가능 -->>제거된거 확인하기 위한 SQL SELECT TABLE_NAME,CONSTRAINT_TYPE, CONSTRAINT_NAME,R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME IN('DEPT05','EMP05');
제약조건 활성화 / 비활성화
ALTER TABLE 테이블명
DISABLE | ENABLE CONSTRAINT 제약조건명 [CASCADE] ;
321쪽 (예제)
--->FK제약조건 비활성화하기
ALTER TABLE EMP05 DISABLE CONSTRAINT EMP05_EMPNO_PK;
--->확인하기
SELECT TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME IN ( 'EMP05' );
--->FK제약조건 다시 활성화하고, 확인하면 ENABLED로 바뀌어있음
ALTER TABLE EMP05 ENABLE CONSTRAINT EMP05_EMPNO_PK;
11장 기타 스키마 객체.pdf
객체 : table도 객체. scott 계정에서 쓸 수 있는게 table객체, 뷰 객체, 시퀀스 객체, 시노님 객체 등등 있음
뷰랑 시노님은 이런게 있다 정도고, 시퀀스 중요하게 보기
뷰
-물리적인 테이블(EMP,DEPT)에서 필요한 것만 뽑아낸, 논리적인 테이블
-선택적으로 접근 제한
-보안과 관련된 민감한 데이터를 접근 제한
-매번 복잡한 SQL문을 사용하지 않고, 뷰를 작성하고 난 후, 뷰를 사용하면 쉬워짐
-뷰는 SYS계정에서 권한 부여받아야함 [ sys계정에서 grant creat view to scott; ]
SELECT EMPNO,ENAME,D.DNAME,D.DEPTNO FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE E.DEPTNO=20;
CREATE VIEW EMP_VIEW --EMP_VIEW라는 뷰를 생성해라. AS --~로써 SELECT EMPNO,ENAME, D.DNAME, D.DEPTNO --사원번호,사원명,부서명,부서번호를 FROM EMP E JOIN DEPT D --조인 ON E.DEPTNO=D.DEPTNO --조인 WHERE E.DEPTNO=20; --EMP의 사번이 20인 레코드의
--뷰2생성, 원래 EMP컬럼개수8개고 SAL제외7개만, 이때 EMP에서 뽑은 컬럼명들을, 그대로 뷰의 컬럼명으로 가져감
CREATE VIEW EMP_VIEW2 AS SELECT EMPNO,ENAME, JOB,MGR,HIREDATE,COMM,DEPTNO FROM EMP;
--뷰2의 컬럼 개수 확인-세부정보로 가면 썼던 SQL볼 수 있음
SELECT * FROM EMP_VIEW2;
--한 계정 안에 만든 뷰이름 보여주고 사용된 서브쿼리 보기
SELECT VIEW_NAME, TEXT FROM USER_VIEWS;
★생성된 뷰를 확인하는 방법★ [ USER_VIEW 데이터사전 조회 ] SELECT * FROM USER_VIEWS ;
뷰 수정
-뷰는 없으면 CREATE 생성되고, 뷰가 존재하면 REPLACE 덮어쓰기
-뷰는 ALTER로 수정하는게 아니고 REPLACE로 덮어써야함
-POSTGRESDB 같은 경우 기존에 뷰가 있으면 REPLACE 안될수 있어서 아래와 같은 SQL을 CREATE REPLACE 바로 위에 써준다
DROP VIEW IF EXISTS 테이블;
DROP VIEW IF EXISTS 테이블 CASCADE;
CREATE OR REPLACEVIEW뷰이름 AS SELECT ~ 서브쿼리 ;
<CTAS와 CVAS의 차이점>
TABLE : 새로운 테이블을 만들고, 원본은 변경이 안 됨
VIEW : DML을 날리면 원본 데이터가 변경됨
--생성 아니면 바꿔라 다음뷰를, 근데 이미 있으니까 바꾸는거로됨
CREATE OR REPLACE VIEW EMP_VIEW2 AS SELECT EMPNO,ENAME,JOB,MGR,COMM,DEPTNO FROM EMP; -뷰가 바뀌었는지 확인 DESC EMP_VIEW2;
뷰 종류
■ 단순뷰 : 1개의 테이블에서 필요컬럼만 뽑아와서 만듦
-DML 실행가능하고, 결과는 실제 기본 테이블에 반영됨===>그래서 꼭 ROLLBACK; 해주기
ㄴ그룹함수, GROUP BY, DISTINCT이 포함됐을 경우엔 DML 불가
-함수를 사용할 땐, 반드시 컬럼별칭을 지정해줘야함
-새로 생성된 뷰 컬럼명을 따로 명시한 컬럼별칭으로 보여줄 수 있음
CREATE VIEW EMP_VIEW3( 사원번호, 이름, 월급 ) AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO=20; SELECT * FROM EMP_VIEW3;
-->함수가 사용될 때는 반드시 컬럼별칭을 지정해야함, 안하면 에러
CREATE VIEW EMP_VIEW4 AS SELECT DEPTNO, SUM(SAL) --SUM 함수때문임 바로 뒤에 별칭 써주면 뷰생성 가능 FROM EMP GROUP BY DEPTNO;
-->또는 이렇게 뷰 뒤에 별칭을 전부 추가해도 됨
CREATE VIEW EMP_VIEW4(사번, 총합) AS SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO;
CREATE VIEW EMP_VIEW5 AS SELECT empno, ename,sal,deptno FROM EMP;
DELETE FROM EMP_VIEW5 WHERE DEPTNO=10; SELECT* FROM EMP; SELECT* FROM EMP_VIEW5; ROLLBACK;
-->뷰의 부서번호를 바꿔줄거임
CREATE VIEW EMP_VIEW6 AS SELECT empno, ename,sal,deptno FROM EMP WHERE DEPTNO=30 ; SELECT*FROM EMP_VIEW6;
UPDATE EMP_VIEW6 --뷰6의 SET deptno=40 WHERE EMPNO=7499; --사번7499 ALLEN의 부서번호를 40으로 바꿔라 SELECT* FROM EMP; SELECT* FROM EMP_VIEW6;
ROLLBACK;
■ 복합뷰 : 조인한 테이블에서 필요컬럼만 뽑아와서 만듦
~ WITH CHECK OPTION 제약조건 ;
-WHERE절 조건에 만족하는 데이터만 DML문이 가능한 제약조건 추가된 뷰생성
CREATE OR REPLACE VIEW 뷰이름
AS
SELECT ~ 서브쿼리
WHERE 조건
WITH CHECK OPTION ; --WHERE조건에 맞는 것만 DML할 수 있게
CREATE OR REPLACE VIEW EMP_VIEW6 AS SELECT empno, ename,sal,deptno FROM EMP WHERE DEPTNO=30 WITH CHECK OPTION ;
UPDATE EMP_VIEW6 SET deptno=40 WHERE EMPNO=7499; --체크옵션 안붙이고 했을땐 업뎃 됐지만 붙이면 안됨
~ WITH READ ONLY제약조건 ;
-어떠한 DML문도 불가능한 제약조건 추가된 뷰생성 ∴기본테이블 변경 불가능
CREATE OR REPLACE VIEW뷰이름
AS
SELECT~ 서브쿼리
WITH READ ONLY ;
CREATE OR REPLACE VIEW EMP_VIEW6 AS SELECT empno, ename,sal,deptno FROM EMP WITH READ ONLY ;
UPDATE EMP_VIEW6 SET ENAME='개발' WHERE DEPTNO=30; --CANNOT ... READ-ONLY VIEW 리드온니뷰 때문에 업뎃불가
344쪽
뷰 삭제
DROP VIEW 뷰이름 ;
-기본테이블의 데이터에 영향이 가지 않음
DROP VIEW IF EXISTS 테이블;
DROP VIEW IF EXISTS 테이블 CASCADE;
CREATE SEQUENCE시퀀스명 START WITH 시작값(나중에수정X) INCREMENT BY 증가값 MAXVALUE 최대값 MINVALUE 사이클될때 다시 시작하는 값 CYCLE 최대값 도달하면 MINIVALUES로 돌아갈지말지 CACHE ; 미리 20개 저장해두는데 되도록 사용X
확인은 계정들어가면 중간쯤에 '시퀀스'폴더 있음
CREATE SEQUENCE DEPT_DEPTNO_SEQ START WITH 10 INCREMENT BY 10 MAXVALUE 100 MINVALUE 5 CYCLE NOCACHE;
NEXTVAL 과 CURRVAL
NEXTVAL는 다음값이고 CURRVAL는 최근값
-해당 시퀀스의 값을 증가시키고 싶다면 testSeq.NEXTVAL
-현재 시퀀스를 알고 싶다면 testSeq.CURRVAL
-반드시 NEXTVAL 먼저 호출, 나중에 CURRVAL 호출
348쪽
SELECT DEPT_DEPTNO_SEQ.NEXTVAL, DEPT_DEPTNO_SEQ.CURRVAL FROM DUAL;
계속 클릭하면 5로 돌아가고 또 5로 돌아가고....
★★NEXTVAL는 실험 삼아 SELECT 실행하는 순간, 호출이 되어버리는 것★★
ㄴ예시는 아래 테이블에시퀀스저장에 적어둠
■ 음수값 시퀀스는 계속 호출하면 MINVALUE가 아니라 MAXVALUE로 돌아감
CREATE SEQUENCE DEPT_DEPTNO_SEQ2 START WITH 100 INCREMENT BY -10 MAXVALUE 150 MINVALUE 10 CYCLE NOCACHE; SELECT DEPT_DEPTNO_SEQ2.NEXTVAL, DEPT_DEPTNO_SEQ2.CURRVAL FROM DUAL;
352쪽
USER_SQUENCES 데이터사전
★생성된 시퀀스를 확인하는 방법★ [ USER_SEQUENCES 데이터사전 조회 ] SELECT * FROM USER_SEQUENCES WHERE sequence_name='시퀀스명' ; DESC USER_SEQUENCES ;
시퀀스 수정
■ 넘버링을 수정
-뷰는 ALTER로 변경이 안되고, REPLACE로 덮어써야함
-레코드 삭제는 DELETE 객체나 컬럼 삭제는 DROP
-START WITH 옵션은 변경 불가능, 그 외는 전부 변경가능 ( START WITH 바꾸려면 삭제하고 재생성)
alter SEQUENCE 시퀀스명 increment by 10 cycle; ---->이건 수정됨
alter SEQUENCE 시퀀스명 START WITH 5 increment by 10 cycle; ----> 이건 에러남. 왜? START WITH은 수정불가
★테이블에 시퀀스 값 저장 - 기억하기
-테이블에 시퀀스를INSERT INTO VALUES로 저장
CREATE TABLE DEPT06 --➤ DEPT06테이블 생성 ( DEPTNO NUMBER(4) PRIMARY KEY, DNAME VARCHAR2(15), LOC VARCHAR2(15) );
CREATE SEQUENCE DEPT_DEPTNO_SEQ4 --➤ SEQ4 시퀀스 생성 START WITH 10 --➤시작값는 10번부터 INCREMENT BY 10 --➤증가값 10씩. 10,20,30... NOCYCLE --➤돌지 않고 쭉 증가 NOCACHE;
근데 만약 여기서 뭐 찾겠다고 시퀀스를 넣은 SELECT문을 반환한다면?? SELECT DEPT_DEPTNO_SEQ4.NEXTVAL FROM DUAL; --➤NEXTVAL이 반환됐기때문에 40으로 넘어가고 INSERT INTO DEPT06 (DEPTNO,DNAME, LOC) VALUES( DEPT_dEPTNO_SEQ4.NEXTVAL, '관리','부산'); --➤다시 DEPTNO를 추가하면 40이 아닌 50으로 뜸
시퀀스 삭제
DROP SEQUENCE 시퀀스명 ;
-넘버링을 삭제
동의어 synonym
■ CREATE SYNONOYM 동의어 FOR 스키마.테이블명;
SELECT * FROM DEPT; CREATE SYNONYM TESTDEPT FOR SCOTT.DEPT;
■ DROP SYSNONYM 시노님명 ;
DROP SYNONYM TESTDEPT;
인덱스
-간단하게 설명만 하고 넘어감 책367-369쪽
-인덱스: 빠른 데이터 검색을 위한 것
-SYS에서 따로 권한 안 받음
-보통 테이블에서 데이터 검색 시,
① 단순히 첨부터 끝까지 찾는 FULL SCAN방법 [ROWID] - 속도가 늦음
② B tree개념으로 중간값 생성해서 찾는 INDEX방법 - 속도가 빠름
CREATE INDEX 인덱스명
ON 테이블(컬럼) ;
SELECT* FROM EMP WHERE ENAME='JONES'; --➤ F10 누르기 SELECT INDEX_NAME,TABLE_NAME --➤ F10 누르면 원래 아래 인덱스 떠야하는데 ㅋㅋ난 안됨.. FROM USER_INDEXES WHERE TABLE_NAME IN('EMP','DEPT');
SELECT* FROM EMP WHERE EMPNO=7499; --➤ F10 누르면 원래 아래... 떠야하는데 ㅋㅋ난 안됨.. SELECT* FROM EMP WHERE TO_NUMBER(EMPNO)=7499; --➤ 처리가 더 오래걸림 SELECT* FROM EMP WHERE EMPNO!=7499; --➤ NOT인 경우에는 결과가많이 나오니까 INDEX를 쓰지않음 DROP INDEX EMP_ENAME_IDX;
사용자관리 생성,권한,ROLE
데이터베이스 보안
시스템 보안 ㄴ인증 관련 ex)네이버ID하고 비번입력하여 로그인
데이터 보안 ㄴ권한 관련
사용자 생성
CREATE USER 계정 IDENTIFIED BY 비밀번호;
(PDF예제)
CREATE USER user01 --➤ 계정이름이 USER01이고 IDENTIFIED BY ORACLE; --➤ 비번이 ORACLE인, 계정생성
ALTER USER user01 --➤ USER01의 IDENTIFIED BY user01; --➤ 비번을 USER01로 변경
권한
권한종류
①시스템 권한
GRANT 시스템권한1, 시스템권한2, ... --➤[시스템권한1, 시스템권한2] 권한부여
