DDL 이란?
DDL(Data Definition Language)
데이터 정의 언어로 객체(OBJECT)를 만들고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문을 말함
ALTER - 테이블에 정의된 내용을 수정할 때 사용하는 데이터 정의어로 컬럼의 추가/삭제,
제약조건의 추가/삭제, 컬럼의 자료형변경, DEFAULT 값 변경,
테이블 명/ 컬럼 명/ 제약조건 명 변경 등을 할 수 있음
DROP - 데이터베이스 객체를 삭제하는 구문
제약조건 추가 / 삭제
제약조건 추가 :
ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명] 제약조건(컬럼명) [REFERENCES 테이블명 [(컬럼명)];
제약조건 삭제 :
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;
컬럼추가/ 수정/ 삭제
컬럼 추가 : ALTER TABLE 테이블명 ADD(컬럼명 데이터타입 [DEFAULT '값']);
컬럼 수정 : ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입; (데이터 타입 변경)
ALTER TABLE 테이블명 MODIFY 컬렴명 DEFAULT '값'; (기본값 변경)
** 데이터 타입 수정 시 컬럼에 저장된 데이터 크기 미만으로 변경할 수 없다.
컬럼 삭제 : ALTER TABLE 테이블명 DROP(삭제할컬럼명);
ALTER TABLE 테이블명 DROP COLUMN 삭제할컬럼명;
테이블 삭제
[작성법]
DROP TABLE 테이블명 [CASCADE CONSTRAINTS];
컬럼, 제약조건, 테이블 이름변경(RENAME)
1) 컬럼명 변경 : ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 변경명;
2) 제약조건명 변경 : ALTER TABLE 테이블명 RENAME CONSTRAINT 제약조건명 TO 변경명;
3) 테이블명 변경 : ALTER TABLE 테이블명 RENAME TO 변경명;
VIEW 란?
VIEW
논리적 가상 테이블
-> 테이블 모양을 하고는 있지만, 실제로 값을 저장하고 있진 않음.
VIEW 작성법
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름 [컬럼 별칭]
AS 서브쿼리(SELECT문)
[WITH CHECK OPTION]
[WITH READ ONLY];
1) OR REPLACE 옵션 :
기존에 동일한 이름의 VIEW가 존재하면 이를 변경 / 없으면 새로생성
2) FORCE | NOFORCE 옵션 :
FORCE : 서브쿼리에 사용된 테이블이 존재하지 않아도 뷰 생성
NOFORCE(기본 값) : 서브쿼리에 사용된 테이블이 존재해야만 뷰 생성
3) 컬럼 별칭 옵션 : 조회되는 VIEW의 컬럼명을 지정
4) WITH CHECK OPTION 옵션 :
옵션을 지정한 컬럼의 값을 수정 불가능하게 함.
5) WITH READ ONLY 옵션 :
뷰에 대해 SELECT만 가능하도록 지정.
VIEW를 생성하기 위해서는 권한이 필요하다.
EX) ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE; - VIEW 생성 권한부여
SEQUENCE 란?
SEQUENCE ( 순서, 연속 )
- 순차적으로 일정한 간격의 숫자(번호)를 발생시키는 객체(번호 생성기)
SEQUENCEF를 사용하는 이유 - PRIMARY KEY(기본키) : 테이블 내 각 행을 구별하는 식별자 역할
NOT NULL + UNIQUE의 의미를 가짐
[작성법]
CREATE SEQUENCE 시퀀스이름
[START WITH 숫자] -- 처음 발생시킬 시작값 지정, 생략하면 자동 1이 기본
[INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1이 기본
[MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정 (10의 27승 -1)
[MINVALUE 숫자 | NOMINVALUE] -- 최소값 지정(-10의 26승)
[CYCLE | NOCYCLE] -- 값 순환여부 지정
[CACHE 바이트크기 | NOCACHE] -- 캐쉬메모리 기본값은 20바이트, 최소값은 2바이트
-- 시퀀스의 캐시 메모리는 할당된 크기만큼 미리 다름 값들을 생성해 저장해둠
--> 시퀀스 호출 시 미리 저장되어진 값을 가져와 반환하므로
-- 매번 시퀀스를 생성해서 반환하는 것보다 DB속도가 향상됨.
** 사용법 **
1) 시퀀스명.NEXTVAL : 다음 시퀀스 번호를 얻어옴.
(INCREMENT BY 만큼 증가된 수)
단, 생성 후 처음 호출된 시퀀스인 경우
START WITH에 작성된 값이 반환됨.
2) 시퀀스명.CURRVAL : 현재 시퀀스 번호를 얻어옴.
단, 시퀀스가 생성되자마자 호출할 경우 오류발생.
== 마지막으로 호출한 NEXTVAL 값을 반환
SEQUENCE 변경(ALTER)
[작성법]
ALTER SEQUENCE 시퀀스이름
[INCREMENT BY 숫자] -- 다음 값에 대한 증가치, 생략하면 자동 1이 기본
[MAXVALUE 숫자 | NOMAXVALUE] -- 발생시킬 최대값 지정 (10의 27승 -1)
[NIMVALUE 숫자 | NOMINVALUE] -- 최소값 지정 (-10의 26승)
[CYCLE | NOCYCLE] -- 값 순환 여부 지정
[CACHE 바이트크기 | NOCACHE] -- 캐쉬메모리 기본값은 20바이트, 최소값은 2바이트INDEX 란?
INDEX - SQL 구문 중 SELECT 처리속도를 향상시키기 위해
컬럼에 대하여 생성하는 객체
인덱스 내부 구조는 B* 트리 형식으로 되어있음
* INDEX의 장점과 단점
| INDEX의 장점 | INDEX의 단점 |
| 이진 트리형식으로 구성되어 자동정렬 및 검색속도 증가. - 조회 시 테이블의 전체내용을 확인하며 조회하는 것이 아닌 인덱스가 지정된 컬럼만을 이용해서 조회하기 때문에 시스템의 부하가 낮아짐. |
데이터 변경(INSERT, UPDATE, DELETE) 작업 시 이진 트리 구조에 변형이 일어남 -> DML 작업이 빈번한 경우 시스템 부하가 늘어 성능이 저하됨 인덱스도 하나의 객체이다 보니 별도 저장공간이 필요(메모리 소비) 인덱스 생성시간이 필요함 |
*INDEX 작성법
[작성법]
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명 (컬럼명[, 컬럼명 | 함수명]);
DROP INDEX 인덱스명;
** 인덱스가 자동 생성되는 경우 **
-> PK 또는 UNIQUE 제약조건이 설정된 컬럼에 대해
UNIQUE INDEX가 자동 생성된다.
DCL 이란??
DCL(Data Control Language) :데이터 제어언어
- 계정별로 DB 또는 객체에 대한접근권한을 부여(GRANT), 회수(REVOKE)하는 언어접근(제어) 권한을 부여(GRANT), 회수(REVOKE)하는 언어
계정(사용자)
* 관리자 계정 : 데이터베이스의 생성과 관리를 담당하는 계정.
모든 권한과 책임을 가지는 계정.
ex) sys(최고관리자), system(sys에서 권한 몇개 제외된 관리자)
* 사용자 계정 : 데이터베이스에 대하여 질의, 갱신, 보고서 작성 등의
작업을 수행할 수 있는 계정으로
업무에 필요한 최소한의 권한만을 가지는 것을 원칙으로 한다.
ex) KH계정(각자 이니셜 계정), workbook 등
권한의 종류
1) 시스템 권한 : DB접속, 객체 생성 권한
CRETAE SESSION : 데이터베이스 접속 권한
CREATE TABLE : 테이블 생성 권한
CREATE VIEW : 뷰 생성 권한
CREATE SEQUENCE : 시퀀스 생성 권한
CREATE PROCEDURE : 함수(프로시져) 생성 권한
CREATE USER : 사용자(계정) 생성 권한
DROP USER : 사용자(계정) 삭제 권한
DROP ANY TABLE : 임의 테이블 삭제 권한
2) 객체 권한 : 특정 객체를 조작할 수 있는 권한
권한 종류 설정 객체
SELECT TABLE, VIEW, SEQUENCE
INSERT TABLE, VIEW
UPDATE TABLE, VIEW
DELETE TABLE, VIEW
ALTER TABLE, SEQUENCE
REFERENCES TABLE
INDEX TABLE
EXECUTE PROCEDURE
5 : SELECT -- 조회할 컬럼을 지정
1 : FROM 테이블 -- 조회할 테이블
(+JOIN)
2 : WHERE 조건식 -- 조회하려는 행만 골라내기
3 : GROUP BY -- 같은 컬럼값을 지닌 행 끼리 그룹화
4 : HAVING -- 조건에 맞는 그룹만 조회
6 : ORDER BY -- 조회결과 정렬기준 작성
-----------------------------------------------------------------
INSERT : 테이블에 행을 삽입하는 구문
INSERT INTO 테이블명
VALUES(모든 컬럼에 대입할 값 순서대로 작성);
INSERT INTO 테이블명(컬럼1, 컬럼2, 컬럼3....)
VALUES(컬럼 1값, 컬럼2 값, 컬럼3 값...);
--> 값이 삽입되지 않은 컬럼은 NULL
* 서브쿼리 결과를 지정된 테이블에 모두 삽입
INSERT INTO 테이블명
AS(서브쿼리)
-----------------------------------------------------------------
UPDATE : 테이블에서 지정된 행의 컬럼값을 수정하는 구문
UPDATE 테이블명
SET
컬럼명 : 수정할 값,
컬럼명 : 수정할 값,
....
WHERE
수정하려는 행을 찾는 조건;
-----------------------------------------------------------------
DELETE : 테이블의 행을 삭제하는 구문
DELETE
FROM 테이블명
WHERE 행을 지정하는 조건;
-----------------------------------------------------------------
* DML(Data Manipulation Language) : 데이터 조작언어
- SELECT, INSERT, UPDATE, DLELTE, MERGE
* DQA(Data query Language) : 데이터 질의언어
- SELECT
-----------------------------------------------------------------
* TCL(Transaction Control Language) : 트랜잭션 제어 언어
- COMMIT : 데이터 변경사항(DML)을 삭제
-> 이전 마지막 COMMIT 상태로 돌아감
-SAVEPOINT : 트랜잭션에 저장지점 설정
-> 원하는 저장 지점까지만 ROLLBACK 가능
* DDL(Data Definition Language) : 데이터 정의 언어
- 객체(OBJECT)를 만들고(CREATE), 수정(ALTER)하고, 삭제(DROP)
[테이블 생성 구문]
CREATE TABLE 테이블명(
컬럼명 자료형 [컬럼레벨 제약조건],
컬럼명 자료형 [[CONSTTRAINT 제약조건명] 제약조건종류(어떤컬럼?)],
...,
-- [테이블 레벨 제약조건]
[[CONSTRAINT 제약조건명] 제약조건 종료(대상컬럼)]
);
------------------------------------------------------------------
** ORACLE 에서 사용하는 자료형 **
NUMBER : 정수/실수
CHAR(크기) : 고정길이 문자열 최대 2000BYTE
VARCHAR2(크기) : 가변길이 문자열 최대 4000BYTE
DATE : 날짜 (년, 월, 일, 요일 , 시, 분 , 초)
TIMESTAMP : 날짜 (년, 월, 일, 요일, 시, 분, 초, ms, UTC 시차)
BLOB : 바이트 형태로 최대 4GB
CLOB : 문자 형태로 최대 4GB
* 문자 인코딩 UTF-8의 특징
- 영어, 숫자, 키보드 특수문자 : 1BYTE
- 나머지 문자, 특수문자 : 3BYTE
*** 제약 조건 ***
- 조건의 데이터만 유지하기 위해서 특정 컬럼에 설정하는 제약
-> 데이터 무결성(신뢰도 확보) 보장을 목적으로 함
1. NOT NULL : 지정된 컬럼에 무조건 값이 존재해야함
2. UNIQUE : 지정된 컬럼에 중복된 값이 존재할 수 없다
-> 단, NULL은 가능
왜? 값이 없어서 비교가 안됨
3. PRIMARY KEY : 각 행을 구분하기 위한 식별 역할의 컬럼을 지정
-> 컬럼에 저장된 값만 알면 모든행을 구별할 수 있음
-> 중복 X(UNIQUE) + 무조건 값이 존재(NOT NULL)
-> PK는 테이블에 1개만 존재가능
(한 컬럼X, PK 제약조건이 1개)
4. FOREUIGN KEY : 자식 테이블의 한 컬럼에 작성될 수 있는 값은
부모 테이블의 한 컬럼(PK, UNIQUE)에 작성된 값만 쓸 수 있다.
-> 자식 테이블이 부모 테이블을 참조
- 부모 - 자식 관계형성
-> 두 테이블에 같은 종류의 데이터를 지닌 컬럼이 있다!
-> JOIN의 연결기준이 될 수 있다!
[삭제옵션]
1) ON DELETE SET NULL
- 부모행을 삭제하면
참조하던 값을 가지고 있는 자식 행의 컬럼값을
NULL 변경(SET)
2) ON DELETE CASCADE
- 부모행을 삭제하면
참조하던 값을 가지고 있는 자식 행을 삭제
5. CHECK : 컬럼에 지정된 값만 저장할 수 있게 하는 제약조건
- FK 제약조건의 하위호환