8. DDL

jedo5000·2023년 11월 28일
0

DataBase

목록 보기
8/13

8.1 DDL이란?

DDL(Data Definition Language)

  • 데이터 정의어
  • 테이블과 같은 데이터 저장소 객체의 생성/수정/삭제

8.2 테이블의 자료형

자료형 = DATA TYPE = 데이터를 저장하는 형식
ex) 2(숫자) , 글(문자형), 2023/11/28(날짜형)

자료형을 지정하는 이유?

  • 가장 큰 이유는 효율성 때문이다. 자료형에 따라서 테이블의 저장 공간을 효율적으로 관리할 수 있다.

  • 그 다음으로는 일관성이 있다. 사람은 23, 스물셋, 이십삼과 같이 다르게 표기된 것을 알아먹을 수 있지만 컴퓨터는 파악할 수 없다. 따라서 미리 자료형들을 정해두면 넣은 값을 컴퓨터가 파악 가능하여 연산을 위한 알고리즘도 최적화할 수 있다.

8.2.1 VARCHAR2(n)

  • VARCHAR2(n): 문자형 값을 n 바이트까지 입력 받을 수 있는 가변형 문자열VAR(variable)CHAR(character)

  • CHAR(n) : 문자형 값을 n 바이트까지 입력 받을 수 있는 고정형 문자열

차이점: VARCHAR2(n)은 남은 공간이 다시 메모리로 반환되어 활용이 가능하지만, CHAR(n)은 공간이 변하지 않아 활용이 불가능하다!(차이점을 물어보는 문제 가능)

8.2.2 NUMBER(n , m)

  • 숫자 값을 n자리만큼 입력 받고 소수점 m자리만큼 입력 받는다.
    NUMBER로 실수와 정수 모두 표현 가능
    NUMBER 뒤에 n,m 은 생략 가능
    < TIP > 보통 n,m을 생략하고 NUMBER로 사용

8.2.3 DATE

  • 날짜 값을 입력 받는다
    이 외에도 TIMESTAMP라는 자료형도 존재
    (둘 다 날짜 자료형이며 TIMESTAMP가 좀 더 구체적인 시간을 저장)

8.3 테이블 생성(CREATE)

CREATE

  • 새로운 객체(OBJECT)를 생성할 때 사용하는 명령어
    CREATE (TABE,USER,SEQUENCE,VIEW)

[문법] CREATE TABLE 테이블명 ( 컬럼명 + 자료형 + DEFAULT로 지정할 값(생략가능) + 제약조건(생략가능)) ;
NOT NULL(제약조건),
DEFAULT 'O'(DEFAULT로 지정할 값)

8.4 테이블 수정(ALTER)

ALTER

  • 이미 만들어진 객체(OBJECT)를 수정할 때 사용하는 명령어

    ADD (테이블에 컬럼 추가)

  • ALTER TABLE 테이블명 ADD 컬럼명 자료형 [default][not null];

DROP

  • ALTER TABLE 테이블명 DROP COLUMN 컬럼명 ;

MODIFY

  • ALTER TABLE 테이블명 MODIFY (컬럼명 자료형 [DEFAULT][NOT NULL] );

RENAME

  • ALTER TABLE 테이블명
    RENAME COLUMN 컬럼명 TO 바꿀컬럼명

8.5 제약조건

테이블에 입력 가능한 데이터를 조건으로 제약하는 것

NOT NULL
UNIQUE KEY (UK)
CHECK
★ PRIMARY KEY (PK) ★
★ FOREIGN KEY (FK) ★

NOT NULL

컬럼이 비어 있거나 NULL 값이 들어오지 않도록 하는 조건 정보가 꼭 필요한 컬럼에 사용 ex) 직원 테이블의 사원 이름

[문법1] ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 + 자료형 + NOT NULL ;
ex) ALTER TABLE QUIZ_TABLE MODIFY COLUMN Q_ANSWER VARCHAR(100) NOT NULL ;
-- 해당 컬럼에 NULL값이 오지 못하도록 하겠다.
[문법2] CREATE TABLE 테이블명 ( 컬럼명 + 자료형 + NOT NULL )
ex) CREATE TABLE QUIZ_TABLE(
Q_ID NUMBER(3, 0) PRIMARY KEY ,
Q_CONTENT VARCHAR2(200) UNIQUE ,
Q_ANSWER VARCHAR(100) NOT NULL, // 이 부분에 사용
REG_DATE DATE DEFAULT SYSDATE ,
Q_BINGO VARCHAR(100) DEFAULT ‘O’
) ;

UNIQUE KEY(UK) : UNIQUE

하나의 컬럼에 중복되는 튜플이 존재할 수 없도록 하는 조건

[문법1] ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 UNIQUE ( 컬럼 ) ;
ex) ALTER TABLE QUIZ_TABLE ADD CONSTRAINT UK_QUIZ_TABLE UNIQUE(Q_CONTENT) ; 
-- UK_QUIZ_TABLE이라는 제약조건명으로 Q_CONTENT컬럼에 중복되는 튜플이 존재할 수 없도록 하겠다.

[문법2] CREATE TABLE 테이블명 ( 컬럼명 + 자료형 + UNIQUE )
ex) CREATE TABLE QUIZ_TABLE(
Q_ID NUMBER(3, 0) PRIMARY KEY ,
Q_CONTENT VARCHAR2(200) UNIQUE , -- 이 부분에서 사용
Q_ANSWER VARCHAR(100) NOT NULL,
REG_DATE DATE DEFAULT SYSDATE ,
Q_BINGO VARCHAR(100) DEFAULT ‘O’
) ;

CHECK

특정 컬럼에 데이터를 입력할 때 조건에 해당하는 데이터만 입력할 수 있도록 제약한다.

CHECK 조건에 만족하는 정보만 튜플에 입력 가능 ex) 성별, O/X 퀴즈 결과, 학점 등

[문법1] ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 CHECK ( 조건 OR 범위 ) ;
ex) ALTER TABLE QUIZ_TABLE ADD CONSTRAINT Q_BINGO_CK CHECK (Q_BINGO IN (‘O’, ‘X’) ) ;
-- Q_BINGO_CK라는 제약조건명을 짓고 이 제약조건이 Q_BINGO에 O,X만 들어가게 하겠다.

[문법2] CONSTRAINT 제약조건이름 CHECK ( 조건 OR 범위 )

ex) CREATE TABLE QUIZ_TABLE(
Q_ID NUMBER(3, 0) PRIMARY KEY ,
Q_CONTENT VARCHAR2(200) UNIQUE ,
Q_ANSWER VARCHAR(100) NOT NULL,
REG_DATE DATE DEFAULT SYSDATE ,
Q_BINGO VARCHAR(100) DEFAULT ‘O’
CONSTRAINT Q_BINGO_CK CHECK (Q_BINGO IN(‘O’, ‘X’) ) -- 이부분에서 사용
) ;

PRIMARY KEY (PK) : NOT NULL + UNIQUE

ex) 핸드폰 가맹점에 가서 나의 핸드폰에 대한 정보들을 확인하려면 고유한 식별자를 사용하여 정보를 확인한다 → 휴대폰 번호!( 고유한 PRIMARY KEY로써 사용된다.)

  • 식별자 규칙을 물리적 모델링 한 것. 기본 키, 주 키, 프라이머리 키라고도 부름
  • 보통 튜플(행)이 지닌 유일하고 고유한 특징인 컬럼을 PK로 지정한다.
  • NULL값 입력 불가, 중복 불가의 특징을 가짐.
[문법1] ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 PRIMARY KEY ( 컬럼 );
ex) ALTER TABLE QUIZ_TABLE ADD CONSTRAINT PK_QUIZ_TABLE PRIMARY KEY(Q_ID) ;
-- ADD CONSTRAINT 제약조건명 PRIMARY KEY(컬럼1, 컬럼2); 
-- 여러개의 제약조건을 하나의 제약조건명으로 묶어서 사용할 수 있다! (슈퍼키)
[문법2] CREATE TABLE 테이블명 ( 컬럼명 + 자료형 + PRIMARY KEY) 

ex)
CREATE TABLE QUIZ_TABLE(
Q_ID NUMBER(3, 0) PRIMARY KEY ,
Q_CONTENT VARCHAR2(200) UNIQUE ,
Q_ANSWER VARCHAR(100) NOT NULL,
REG_DATE DATE DEFAULT SYSDATE ,
Q_BINGO VARCHAR(100) DEFAULT ‘O’
) ;

FOREIGN KEY (FK)

다른 테이블에 있는 기본 키(PRIMARY KEY)를 참조하는 컬럼을 FK로 지정
FOREIGN KEY는 외래 키라고 부르고 데이터 무결성을 지원함.(PK-FK로 묶여있으면 PK에 없는 튜플이 올 수 없으므로 데이터 무결성이 높음)
참조하는 테이블의 기본 키 값과 반드시 동일한 값으로 구성되어야 함.

[문법1] ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 FOREIGN KEY ( 참조받을 컬럼 ) REFERENCES 참조할 테이블(참조할 컬럼) ;
ex) ALTER TABLE QUIZ_TABLE2 ADD CONSTRAINT TABLE1_TABLE_2_FK FOREIGN KEY(Q_ID) REFERENCES QUIZ_TABLE (Q_ID) ;

[문법2] CREATE TABLE 테이블명 ( 생성할 컬럼 정보 , CONSTRAINT 제약조건명 FOREIGN KEY (참조받을 컬럼) REFERENCES 참조할 테이블(참조할 컬럼) ) ;
ex)
CREATE TABLE QUIZ_TABLE2 (
Q_ID NUMBER(3,0) ,
Q_REAL VARCHAR2(200) ,
CONSTRAINT TABLE1_TABLE2_FK FOREIGN KEY (Q_ID)
REFERENCES QUIZ_TABLE (Q_ID)
) ;

8.6 DROP

테이블 및 제약조건을 삭제할 때 사용하는 명령어

DROP TABLE .. 	-- 테이블을 삭제
DROP TABLE .. CASCADE CONSTRAINTS -- 테이블의 제약조건까지 함께 삭제

8.7 SEQUENCE

연속적인 사건이라는 뜻이며, 자동으로 증가하는 값을 만들어주는 객체

CREATE SEQUENCE 회원ID_SEQ
( INCREMENT BY 1 -- 증가할 시퀀스 폭
START WITH 1 -- 시작할 시퀀스 값
MINVALUE 1 -- 시퀀스 최소 값
MAXVALUE 9999 -- 시퀀스 최대 값 )

SEQUENCE 값 확인하는 방법

SELECT 회원ID_SEQ.NEXTVAL -- [시퀀스명.NEXTVAL] 형식으로 시퀀스 값 호출
FROM DUAL ;

SEQUENCE를 사용하는 이유

  1. PRIMARY KEY가 될 만한 컬럼이 존재하지 않을 때
  2. 회원ID, 직원ID 처럼 규칙적으로 증가하는 값에 사용
    → NEXTVAL 키워드를 통해 이전 사원의 번호를 조회하지 않아도 다음 번호 사용 가능

[SEQUENCE 삭제하기]

DROP SEQUENCE 회원ID_SEQ ;

8.8 VIEW

일종의 “가상테이블” 을 의미, 테이블과 다르게 물리적으로 존재 X

BUT!!
사용자에게는 있는 것으로 간주됨
정보 보안 측면에서 유리하다는 장점
보통 자주 사용하는 쿼리를 뷰(VIEW)에 저장하여 사용

VIEW를 생성하려면 권한이 필요함.
RUN SQL COMMAND LINE에서
GRANT CREATE VIEW TO SERVICE;

ex) 만약 부서별 최고 연봉을 검색하는 쿼리를 자주 사용한다면 
    → VIEW에 저장해두고 필요할 때 불러서 사용
    
CREATE VIEW 부서별최고연봉_VIEW AS
SELECT 부서ID , MAX(연봉) AS 부서별최고연봉
FROM 직원
GROUP BY 부서ID
ORDER BY 부서ID ;   

VIEW는 실제 테이블과 JOIN도 가능하다.
ex) 부서별로 가장 높은 연봉을 가진 직원들의 정보를 출력해 주세요

SELECT A.이름, A.연봉, B.부서별최고연봉
FROM 직원 A , 부서별최고연봉_VIEW B
WHERE A.부서ID = B.부서ID
AND A.연봉 = B.부서별최고연봉 ;

VIEW 사용 이유

(1) 자주 사용하는 쿼리를 저장해 놓고 이용할 수 있으므로 명령어 입력양 감소

CREATE VIEW 부서별최고연봉_VIEW AS
SELECT 부서ID , MAX(연봉) AS 부서별최고연봉
FROM 직원
GROUP BY 부서ID
ORDER BY 부서ID ;SELECT *
FROM 부서별최고연봉_VIEW ;

(2) 원하는 데이터만 보여줄 수 있게 해 보안 목적으로 사용 가능

VIEW 삭제하기

DROP VIEW 부서별최고연봉_VIEW ;

0개의 댓글