2. DDL(DATA DEFINITION LANGUAGE)
: 객체들을 새로이 생성(CREATE), 수정(ALTER), 삭제(DROP)하는 구문(데이터 정의언어)
1) ALTER
: 객체 구조를 수정하는 구문
<제약조건 수정>
ALTER TABLE 테이블명 수정할내용;
-수정할 내용-
(1) 추가/수정/삭제
(2) 제약조건 추가/삭제 -> 수정은 불가(수정하고자 한다면 삭제한 후 새로이 추가)
(3) 테이블명/컬럼명/제약조건명 변경
(2)-1 제약조건 추가
테이블 다 생성한 후 뒤늦게 제약조건 추가 (ALTER TABLE 테이블명 XXXX)
(1) PRIMARY KEY : ADD PRIMARY KEY(컬럼명);
(2) FOREIGN KEY : ADD FOREIGN KEY(컬럼명) REFERENCES 참조할 테이블명(참조할 컬럼명);
(3) UNIQUE : ADD UNIQUE(컬럼명);
(4) CHECK : ADD CHECK(컬럼에 대한 조건);
(5) NOT NULL : MODIFY 컬럼명 NOT NULL ( 또는 NULL);
나만의 제약조건명을 부여하고자 한다면 : [CONSTRAINT 제약조건명] 제약조건
주의사항 : 제약조건명은 현재 계정내에 고유한 값으로 부여해주어야 함
(2)-2 제약조건 삭제
-- DEPT_COPY 테이블에
-- DEPT_ID 컬럼에 PRIMARY KEY 제약조건 추가
-- DEPT_TITLE컬럼에 UNIQUE 제약조건 추가
-- LNAME 컬럼에 NOT NULL 제약조건 추가
ALTER TABLE DEPT_COPY
ADD CONSTRAINT DCOPY_PK PRIMARY KEY(DEPT_ID)
ADD CONSTRAINT DCOPY_UQ UNIQUE(DEPT_TITLE)
MODIFY LNAME CONSTRAINT DCOPY_NN NOT NULL;
/*
2_2) 제약조건 삭제
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK : DROP CONSTRAINT 제약조건명
NOT NULL : MODIFY 컬럼명 NULL */
-- DCOPY_PK 제약조건 지우기
ALTER TABLE DEPT_COPY DROP CONSTRAINT DCOPY_PK;
-- DCOPY_UQ 제약조건 지우기
-- LNAME 제약조건 지우기 NOT NULL => NULL
ALTER TABLE DEPT_COPY
DROP CONSTRAINT DCOPY_UQ
MODIFY LNAME NULL;
-- 3) 컬럼명/ 제약조건명/ 테이블명 변경 (RENAME)
-- 3_1) 컬럼명 변경 : RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명
ALTER TABLE DEPT_COPY RENAME COLUMN DEPT_TITLE TO DEPT_NAME;
-- 32) 제약조건명 변경 : RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명
ALTER TABLE DEPT_COPY RENAME CONSTRAINT SYS
-- 3_3) 테이블명 변경 : RENAME (기존테이블명) TO 바꿀테이블명
ALTER TABLE DEPT_COPY RENAME TO DEPT_TEST;
/*
## 2) DROP
: 객체를 삭제하는 구문
<제약조건 수정>
DROP TABLE 테이블명 삭제할 내용;
**※ 단, 어딘가에서 참조되고 있는 부모테이블들은 함부로 삭제 안 됨!**
만약 삭제하고 싶다면?
1. 자식테이블 먼저 삭제한 후 부모테이블 삭제하는 방법
DROP TABLE 자식테이블;
DROP TABLE 부모테이블;
2. 부모테이블만 삭제하는데 맞물려있는 제약조건 함께 삭제하는 방법
부모테이블만 삭제하고 싶음
DROP TALBE 부모테이블 CASCADE CONSTRAINT;2) DROP
: 객체를 삭제하는 구문
<제약조건 수정>
DROP TABLE 테이블명 삭제할 내용;
※ 단, 어딘가에서 참조되고 있는 부모테이블들은 함부로 삭제 안 됨!
3. DCL(DATA CONTROL LANGUAGE)
: 데이터 제어 언어
계정에서 시스템권한 또는 객체접근권한을 부여(GRANT)하거나 회수(REVOKE)하는 언어
1) 시스템권한
: 특정 DB에 접근하는 권한
<종류>
- CREATE SESSION : 계정에 접속할 수 있는 권한
- CREATE TABLE : 테이블을 생성할 수 있는 권한
- CREATE VIEW : 뷰 생성할 수 있는 권한
- CREATE SEQUENCE : 시퀀스 생성할 수 있는 권한
- CREATE USER : 계정 생성할 수 있는 권한
- ......
[표현법]
GRANT 권한1, 권한2, .... TO 계정명;
-
(1) SAMPLE 계정 생성
CREATE USER SAMPLE IDENTIFIED BY SAMPLE; -
(2) SAMPLE 계정에 접속하기 위한 CREATE SESSION 권한 부여
GRANT CREATE SESSION TO SAMPLE; -
(3)-1 SAMPLE 계정에 테이블 생성할 수 있는 CREATE TABLE 권한 부여
GRANT CREATE TABLE TO SAMPLE; -
(3)-2 SAMPLE 계정에 테이블스페이스 할당해주기 (SAMPLE 계정 변경이기 때문에 ALTER)
ALTER USER SAMPLE QUOTA 2M ON SYSTEM; -
(4) SAMPLE 계정에 뷰를 생성할 수 있는 CREATE VIEW 권한 부여\
GRANT CREATE VIEW TO SAMPLE;
2) 객체권한
: 특정 객체들을 조작(SELECT, INSERT, UPDATE, DELETE,...) 할 수 있는 권한
[표현법]
GRANT 권한종류 ON 특정객체 TO 계정명;
-
(5) SAMPLE 계정에 KH.EMPLOYEE 테이블을 조회할 수 있는 권한부여
GRANT SELECT ON KH.EMPLOYEE TO SAMPLE; -
(6) SAMPLE 계정에 KH.DEPARTMENT 테이블에 삽입할 수 있는 권한부여
GRANT INSERT ON KH.DEPARTMENT TO SAMPLE;
3) CONNECT, RESOURCE
: 최소한의 권한을 부여하고자할 때 CONNECT, RESOURCE 부여
[표현법]
GRANT CONNECT, RESOURCE TO 계정명;
<롤 ROLE>
: 특정 권한들을 하나의 집합으로 모아놓은 것
- CONNECT : CREATE SESSION (데이터베이스에 접속할 수 있는 권한)
- RESOURCE : CREATE TABLE, CREATE SEQUENCE ... (특정 객체들을 생성 및 관리할 수 있는 권한)
-- 사용자에게 부여할 권한 : CONNECT, RESOURCE
-- 권한을 부여받을 사용자 : MYMY
-- CREATE USER MYMY IDENTIFIED BY MYMY;
-- GRANT CONNECT, RESOURCE TO MYMY;TCL(TRANSACTION CONTROL LANGUAGE)
: 트랜잭션을 제어하는 언어
*트랜잭션 ( TRANSACTION)
-
데이터베이스의 논리적 연산단위
-
데이터의 변경사항 (DML)들을 하나의 트랜잭션에 묶어서 처리
COMMIT(확정) 하기 전까지의 변경사항들을 하나의 트랜잭션에 담게 됨
(실제 DB에 반영되기 전에 변경사항들을 저장해두는 하나의 장소 느낌) -
트랜잭션의 대상이 되는 SQL : INSERT, UPDATE, DELETE (DML)
-
COMMIT(트랜잭션 종료 처리 후 확정), ROLLBACK (트랜잭션을 취소), SAVEPOINT(임시저장점 잡기)
- COMMIT; 진행
: 하나의 트랜잭션에 담겨있는 변경사항들을 실제 DB에 반영하겠다걸 의미 - ROLLBACK; 진행
: 하나의 트랜잭션에 담겨있는 변경사항들을 삭제한 후 마지막 COMMIT 시점으로 돌아감 - SAVEPOINT 포인트명; 진행
: 현재 이 시점에 임시저장점을 정의해두는 것 - ROLLBACK 포인트명; 진행
: 전체 변경사항들을 삭제하는 게 아니라 해당 포인트 지점까지의 트랜잭션만 롤백함
- COMMIT; 진행
<예시>
-- 사번이 901인 사원 지움
DELETE FROM EMP_01
WHERE EMP_ID = 901;
-- 사번이 900인 사원 지움
DELETE FROM EMP_01
WHERE EMP_ID = 900;
ROLLBACK; -- 트랜잭션에 담긴 내용 적용시키지 않고 다시 되돌림.
---------------------------------------------------------------------------
-- 200번 사원지움
DELETE FROM EMP_01
WHERE EMP_ID = 200;
-- 800, 홍길동, 총무부 사원추가
INSERT INTO EMP_01
VALUES(800, '홍길동', '총무부');
COMMIT; -- 트랜잭션에 담긴 내용 적용(저장)시킴.
SELECT * FROM EMP_01; -- 트랜잭션에 있던 내용 적용완료
※ 주의사항
(1) 트랜잭션 -> (2) DDL 생성,수정 등 -> (3) ROLLBACK
아무리 ROLLBACK 해도 트랜잭션에 있던 내용이 DDL 실행하는 순간 자동 COMMIT 되어 실행
DDL(CREATE, ALTER, DROP)을 실행하는 순간
기존 트랜잭션에 있던 모든 변경사항들을 무조건 실제 DB에 반영시킨 후에 DDL이 수행됨
즉, DDL 수행 전 변경사항들이 있었다면 정확히 픽스(COMMIT, ROLLBACK) 하고 해라
사용자가 게시글(첨부파일이 존재하는) 추가하는 요청서
=> INSERT INTO 첨부파일테이블
=> INSERT INTO 게시글 테이블
둘 다 잘 INSERT가 될 시 성공 => COMMIT(확정)
둘 중 하나라도 잘 못 INSERT시 실패 => ROLLBACK (기존에 잘 INSERT했던 것도 돌려놓기)1. VIEW 뷰
SELECT문 (쿼리문)을 저장해둘 수 있는 객체
(자주 쓰는 긴 SELECT 문을 저장해두면 긴 SELECT 문을 매번 다시 기술 할 필요 없음)
임시 테이블같은 존재(실제 데이터가 담겨있는건 아님)*/
-- '한국'에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무국가명
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARYY, NATIONAL_NAME, JOB_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
JOIN NATIONAL USING (NATIONAL_CODE)
JOIN JOB USING (JOB_CODE)
WHERE NATIONAL_NAME = '한국';
-- 만약, 한국 뿐 아닌, 러시아, 중국등 같은 셀렉문이 계속 반복된다면? 저 긴 코드를 계속 써야하는 번거로움!
-- 저 긴 셀렉문을 VIEW로 한번 만들고 재사용하면 편함
--
1) VIEW 생성 방법
[표현법]
CREATE VIEW 뷰명
AS 서브쿼리;