DDL (데이터정의어)
- 오라클 데이터베이스 객체 생성, 변경, 삭제에 사용되는 명령어
- DDL의 종류에는 CREATE, ALTER, DROP, TRUNCATE
주요 데이터베이스 객체
| 테이블 | 데이터의 기본 저장단위, 행과 열로 구성되어 있다 |
| 뷰 | 하나 이상의 테이블을 사용해서 만든 가상의 테이블 |
| 시퀀스 | 일련번호 생성기 |
| 인덱스 | 검색성능을 향상시키기 위해 데이터에 대한 색인을 가지고 있다 |
| 동의어 | 객체에 대한 다른 이름을 제공한다 |
테이블
- 테이블과 컬럼의 이름규칙
- 문자로 시작, 30자까지 가능(A~Z, a~z, 0~9, _, $, #만 허용)
- 오라클 예약어도 이름에 사용할 수 없다
테이블 생성하기 ( CREATE )
-- 테이블 생성 예시
CREATE TABLE 테이블명 (
컬럼명 데이터타입(크기) [DEFAULT 기본값],
...
)
테이블 삭제 ( DROP )
-- 테이블 삭제 예시
DROP TABLE 테이블명
테이블 변경종류 명령어 ( ALTER, RENAME, TRUNCATE )
컬럼추가
ALTER TABLE 테이블명 ADD (컬럼명 데이터타입(크기) [DEFAULT 기본값]);
컬럼 삭제
ALTER TABLE 테이블명 DROP 컬럼명;
테이블 이름 변경
RENAME 테이블명 TO 새테이블명
테이블 절단하기
TRUNCATE TABLE 테이블명;
-- truncate는 테이블에서 모든 행을 제거한다.
-- 해당 테이블이 사용하는 저장공간을 해제한다.
-- truncate로 제거된 행은 rollback할 수 없다.
시퀀스
- 일련번호를 자동으로 생성하는 데이터베이스 객체다
- 기본키 값(행을 대표하는 값)을 생성하는데 주로 사용됩니다
시퀀스 생성
CREATE SEQUENCE 시퀀스명;
-- 1부터 1씩 증가하는 번호가 발행된다.
CREATE SEQUENCE 시퀀스명
[INCREMENT BY a] a씩 증가한다.
[START WITH b] b부터 시작한다.
[{MAXVALUE c | NOMAXVALUE}] c가 최대값이다.
[{MINVALUE d | NOMINVALUE}] d가 최소값이다.
[{CYCLE | NOCYCLE}] 최대값/최소값이 도달했을 때 반복여부를 결정한다.
[{CACHE e | NOCACHE}] 일련번호를 e개 만큼 미리 할당해서 메모리에 캐시하거나, 캐시를 활용하지 않는다.
시퀀스의 삭제
DROP SEQUENCE 시퀀스명
시퀀스의 수정
ALTER SEQUENCE 시퀀스명
[INCREMENT BY a]
[{MAXVALUE c | NOMAXVALUE}]
[{MINVALUE d | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE e | NOCACHE}]
-- START WITH를 제외한 나머지 설정을 변경할 수 있다.
시퀀스의 사용
- 시퀀스명.NEXTVAL
- 새로운 일련번호를 반환한다
- 시퀀스명.CURRVAL
- 현재 연결된 세션에서 nextval를 통해서 발행했던 번호를 다시
- CURRVAL을 사용하려면 해당 시퀀스에 대한 NEXTVAL이 실행된 후에만 가능한다
-- 새로운 일련번호 조회하기
select product_seq.nextval from dual;
-- 테이블에 새로운 행을 추가할 때 일련번호 활용하기
insert into sample_products
(product_no, product_name, product_company, product_price)
values
(product_seq.nextval, '아이패드 프로', '애플', 1200000);
뷰
- 테이블 혹은 다른 뷰를 기반으로 하는 가상의 테이블(논리적인 테이블)이다
뷰의 특징
- 물리적인 저장공간을 가지지 않는다
- INSERT, UPDATE, DELETE 작업의 수행이 불편하다
뷰의 목적
- 복잡한 SQL문을 간단하게 작성하기 위해서 사용
- 데이터의 엑세스를 제한하기 위해서 사용(데이터에 대한 보안성 강화)
- 동일한 데이터로부터 다양한 결과를 얻기 위해서 사용
뷰의 종류
단순 뷰
- 한 테이블에서만 데어트를 가져온다
- 함수 또는 데이터 그룹을 사용하지 않았음
- DML(INSERT, UPDATE, DELETE가 가능하다)
복합뷰
- 여러 테이블에 데이터를 가져온다
- 함수 또는 데이터 그룹을 포함하고 있음
- DML 거의 불가능하다
뷰 생성
CREATE [OR REPLACE] VIEW 뷰이름
AS 서브쿼리
WITH READ ONLY
-- 서브쿼리는 가상의 테이블이 포함할 데이터를 조회하는 SELECT문
뷰 수정
CREATE OR REPLACE VIEW 뷰이름
AS 서브쿼리
-- 뷰이름을 수정하려는 기존 뷰이름과 동일하게 지정하면 된다.
뷰 삭제
DROP VIEW 뷰이름
뷰 사용예시
인라인 뷰
- SELECT문의 FROM절에 서브쿼리를 정의하고, 이 서브쿼리로 조회된 결과를 가상의 테이블로 취급하고, 별칭을 부여한 것
- 데이터베이스 객체가 아니다
인라인 뷰 형식
SELECT A.column, A.column, A.column
FROM (SELECT column, column, column, ...
FROM table1
WHERE 조건식) A
WHERE 조건식
인라인 뷰 예시
인덱스(색인)
- 데이터 행의 검색 속도를 향상시키기 위해서 사용되는 데이터베이스 객체다
- 데이터의 위치를 빠르게 찾는 신속한 경로 엑세스 방법을 사용하여 데이터I/O를 줄여 준다
- 인덱스는 테이블과 독립적으로 존재한다
- 한 번 생성된 인덱스는 Oracle이 자동으로 유지 관리한다
- 테이블이 삭제되면 그 테이블의 데이터를 색인화하고 있는 인덱스도 같이 삭제된다
인덱스 생성
- 자동생성 : 테이블 정의할 때 Primary key 제약조건, Unique 제약조건이 정의된 컬럼의 값들은 자동으로 인덱스가 생성된다
- 수동생성 : 사용자가 행에 대한 엑세스 시간이 줄이기 위해서 특정 열을 대상으로 인덱스를 생성할 수 있다
인덱스 생성하기
CREATE INDEX 인덱스명
ON 테이블명 (컬럼명, ....)
인덱스 삭제하기
DROP INDEX 인덱스명
함수 기반 인덱스 생성하기
CREATE INDEX 인덱스명
ON 테이블명 (오라클함수)
인덱스 생성이 필요한 경우
- WHERE절이나 조인조건에 자주 사용되는 경우
- 컬럼이 매우 다양한 값을 포함하고 있는 경우
- 데이터가 아주 많은 테이블을 대상으로 조회작업을 했을 때 대부분의 조회작업에서 검색되는 행이 전체 데이터의 2% ~ 4%미만인 경우
인덱스 생성이 필요하지 않은 경우
- 테이블이 작은 경우
- 테이블이 자주 갱신되는 경우
- 인덱스화된 열이 표현식의 일부로 사용되는 경우
- WHERE절의 조회 조건으로 자주 사용되지 않는 경우
- 대부분의 조회작업에서 전체 데이터의 2%~4%이상 검색되는 경우
트리거
- 특정 테이블에 INSERT, UPDATE, DELETE 작업이 수행될 때 자동으로 실행되는 것
- 트리거는 데이터베이스 객체
트리거의 종류
- 행 트리거 : 테이블의 데이터가 변경될 때 실행되는 트리거
- 문장 트리거 : 영향을 받는 행이 없더라도 실행되는 트리거 / 특정 시간에 실행되는 트리거
트리거 형식
CREATE OR REPLACE TRIGGER 트리거명
{BEFORE | AFTER} -- 트리거 실행 싯점
{INSERT, UPDATE, DELETE} ON 테이블명 -- 이벤트종류
FOR EACH ROW -- 데이터 행의 변화가 생길 때 마다 실행된다
BEGIN
실행할 SQL
END;
트리거의 접두어( OLD, NEW )
- OLD, NEW 접두어는 행 트리거에서만 사용가능하다
- 트리거 수행문에서 변경이 발생한 행의 이전값, 변경된(추가된 값)을 사용할 수 있다
| 작업 | OLD 값 | NEW 값 |
| INSERT | NULL | 추가된 값 |
| UPDATE | 갱신전의 값 | 갱신후의 값 |
| DELETE | 삭제전의 값 | NULL |
트리거 작성예시
-- 주문정보가 추가되거나, 주문상태가 변경될 때마다 그 이력을 저장하기
-- 주문정보 이력을 저장하는 테이블 생성하기
create table tb_order_history (
order_no number(7),
cust_no number(5),
order_status varchar2(20),
order_update_date date
);
-- 주문정보 추가/변경시 실행될 트리거 생성하기
CREATE OR REPLACE TRIGGER order_history_trigger
AFTER
INSERT OR UPDATE ON tb_orders
FOR EACH ROW
BEGIN
insert into tb_order_history
(order_no, cust_no, order_status, order_update_date)
values
(:new.order_no, :new.cust_no, :new.order_status, sysdate);
END;
트리거 활성화 / 비활성화 하기
ALTER TRIGGER 트리거명 ENABLE;
ALTER TRIGGER 트리거명 DISABLE;
트리거 삭제하기
DROP TRIGGER 트리거명;
데이터베이스 정규화
- 관계형 데이터베이스의 설계에서 중복을 최소화하게 데이터를 구조화하는 프로세스를 정규화라고 한다
- 정규화의 목표는 관련이 없는 함수 종속성은 별개의 릴레이션으로 표현하는 것이다
- 정규화된 결과를 정규형이라고 하며, 정규형은 기본 정규형, 고급 정규형으로 구분된다
- 기본 정규형 : 제1정규형, 제2정규형, 제3정규형, BCNF(보이스/코드 정규형)
- 고급 정규형 : 제4정규형, 제5정규형
제 1 정규형
- 릴레이션에 속한 모든 속성의 도메인이 더 이상 분해되지 않는 원자값으로만 구성된 정규형이다
제 2 정규형
- 릴레이션이 제 1 정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제 2 정규형이다
- 제 1 정규형에 속하는 릴레이션이 제 2 정규형을 만족하게 하려면, 부분 함수 종속을 제거하고, 모든 속성이 기본키에 완전 함수 종속 되도록 릴레이션을 분해하는 정규화 과정을 거쳐야 한다
- 완전 함수 종속 : 어떤 속성이 기본키에 대해 완전히 종속일 때
- 부분 함수 종속 : 어떤 속성이 기본키가 아닌 다른 속성에 종속되거나, 기본키가 여러 속성으로 구성되어 있을 경우, 기본키를 구성하는 속성 중 일부만 종속일 때
제 3 정규형
- 릴레이션이 제 2 정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지않으면 제 3 정규형
- 이행적 함수 종속
- A -> B, B-> C인 경우 A -> C가 성립될 때
- 즉, A를 알면 B를 알고 이를 통해 C를 할 수 있는 경우를 의미
'SQL' 카테고리의 다른 글
| SQL_TOP-N분석과 분석함수, 집합연산자, 계층형쿼리 (1) | 2023.12.20 |
|---|---|
| SQL_시퀀스, 트랜잭션, 무결성제약조건 (0) | 2023.12.19 |
| SQL_서브쿼리 (1) | 2023.12.17 |
| SQL_그룹함수(GROUP BY, HAVING) (0) | 2023.12.14 |
| SQL_Join (0) | 2023.12.13 |
