MLP) [Oracle] SQL 문장의 종류 및 DDL

SQL 문장의 종류

1. 데이터 정의 언어(DDL: Data Definition Language)
2. 데이터 조작 언어(DML: Data Manipulation Language)
3. 데이터 제어 언어(DCL: Data Control Language)
4. 데이터 쿼리 언어(DQL: Data Query Language)
5. 트랜젝션 제어 언어(TCL: Transaction Control Language)

SQL 문장	설명	종류
DDL	테이블과 컬럼을 정의하는 명령어 데이터 전체 골격을 결정	CREATE, ALTER, DROP, RENAME, TRUNCATE
DML	데이터베이스의 내부 데이터를 관리 데이터를 조회, 추가, 변경, 삭제 등의 작업 수행	INSERT, UPDATE, DELETE
DCL	데이터를 보호하기 위한 데이터 보안의 역할 권한을 정의하거나 삭제함	GRANT, REVOKE
DQL	데이터를 검색하기 위한 명령어	SELECT
TCL	데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위	COMMIT, ROLLBACK, SAVEPOINT

 

 

---

DDL (Data Definition Language): 데이터 정의 언어

테이블과 컬럼을 정의하는 명령어로, 데이터 전체 골격을 결정한다.

명령어	내용
CREATE	테이블을 생성하는 역할
ALTER	테이블의 구조를 수정하는 역할
DROP	테이블을 삭제하는 역할
RENAME	테이블의 이름을 변경하는 역할
TRUNCATE	테이블을 초기화하는 역할

 

CREATE

테이블이란?

• 데이터를 저장하기 위한 가장 기본적인 저장 단위
• 행(row)과 열(column)으로 구성된다.
• 행(row)은 보통 레코드라고 부른다.
• 열(column)은 행(row)을 이루는 각각의 데이터 항목이 되며, 필드라고 부르기도 한다.
• 하나 이상의 테이블은 서로 특정한 관계를 가질 수 있으며 이런 테이블간의 관계를 바탕으로 만들어진 데이터 모델을 관계형 데이터 모델이라고 한다.

 

테이블 생성(CREATE문)

제약조건(CONSTRAINT)

• 제약조건이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러 가지 규칙을 적용하는 것이다. 즉, 테이블 안에서 데이터의 성격을 정의하는 것이 바로 제약조건이다.
• 데이터의 무결성 유지를 위해 사용자가 지정할 수 있는 성질이다.
• 모든 CONSTRAINT는 데이터 사전(DICTIONARY)에 저장된다.
• 의미 있는 이름을 부여하면 CONSTRAINT를 쉽게 참조할 수 있다.
• 통상적인 제약조건의 이름: 테이블명_컬럼명_제약조건
• 제약조건은 테이블을 생성하는 당시에 지정할 수도 있고, 테이블 생성 후 구조변경(ALTER) 명령어를 통해서도 추가가 가능하다.
• NOT NULL 제약조건은 반드시 컬럼 레벨에서만 정의가 가능하다.

데이터 사전 조회 방법

SELECT * FROM user_constraints; --USER가 가지고 있는 제약조건들 출력
SELECT * FROM user_constraints WHERE TABLE_NAME='테이블명'; --특정 테이블의 제약조건 출력

 

제약조건의 종류

제약조건	설명
PRIMARY KEY (PK)	유일하게 테이블의 각 행을 식별. NOT NULL + UNIQUE 조건을 만족
FOREIGN KEY (FK)	컬럼과 참조된 컬럼 사이의 외래키 관계를 적용하고 설정
UNIQUE KEY (UK)	테이블의 모든 행을 유일하게 하는 값을 가진 열. NULL을 허용
NOT NULL (NN)	컬럼은 NULL을 포함할 수 없음
CHECK (CK)	참이어야 하는 조건을 지정함. 대부분 업무 규칙을 설

 

제약조건 정의 방법

 

[1] PRIMARY KEY 제약조건

<1> 컬럼 수준

CREATE TABLE TEST_TAB1(
    ID NUMBER(2) CONSTRAINT TEST_TAB1_ID_PK PRIMARY KEY, --컬럼수준의 제약
    NAME VARCHAR2(10)
);

 

<2> 테이블 수준

CREATE TABLE TEST_TAB2(
    ID NUMBER(2),
    NAME VARCHAR2(10),
    --테이블 수준의 제약
    CONSTRAINT TEST_TAB2_ID_PK PRIMARY KEY (ID)
);

 

제약조건 삭제	ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명
제약조건 추가	ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건유형 (컬럼명)
제약조건명 변경	ALTER TABLE 테이블명 RENAME OLD_제약조건명 TO NEW_제약조건명

--TEST_TAB2의 TEST_TAB2_ID_PK 제약조건을 삭제하세요
ALTER TABLE TEST_TAB2 DROP CONSTRAINT TEST_TAB2_ID_PK;

--TEST_TAB2의 NAME 컬럼에 PRIMARY 제약조건을 추가하세요
ALTER TABLE TEST_TAB2 ADD CONSTRAINT TEST_TAB2_NAME_PK PRIMARY KEY(NAME);

--TEST_TAB2의 NAME 컬럼에 준 제약조건명을 변경하세요
ALTER TABLE TEST_TAB2 RENAME CONSTRAINT TEST_TAB2_NAME_PK TO TEST_TAB2_NAME_PK_RENAME;

 

 

[2] FOREIGN KEY 제약조건

참조되는 테이블(부모 테이블, MASTER TABLE)과 참조하는 테이블(자식 테이블, DETAIL TABLE)의 관계를 나타냄

--부모 테이블 - MASTER TABLE
--DEPT_TAB
CREATE TABLE DEPT_TAB(
    DEPTNO NUMBER(2),
    DNAME VARCHAR2(15),
    LOC VARCHAR2(20),
    CONSTRAINT DEPT_TAB_DEPTNO_PK PRIMARY KEY(DEPTNO)
);

--자식 테이블 - DETAIL TABLE
--EMP_TAB
CREATE TABLE EMP_TAB(
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(10),
    --컬럼 수준에서 FK 제약 주기
    MGR NUMBER(4) CONSTRAINT EMP_TAB_MGR_FK REFERENCES EMP_TAB(EMPNO),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2),
    --테이블 수준에서 FK, PK 제약 주기
    CONSTRAINT EMP_TAB_DEPTNO_FK FOREIGN KEY (DEPTNO) REFERENCES DEPT_TAB (DEPTNO),
    CONSTRAINT EMP_TAB_EMPNO_PK PRIMARY KEY (EMPNO)
);

부모 테이블인 DEPT_TAB의 DEPTNO는 자식 테이블인 EMP_TAB의 DEPTNO가 참조하고 있다.

 

※ 외래키로 참조되는 자식 레코드가 있을 경우, 부모 레코드는 삭제가 불가능하다.

예를 들어 부모 테이블인 DEPT_TAB의 부서번호(deptno)가 20인 레코드를 삭제하려고 시도할 때, 자식 테이블인 EMP_TAB의 deptno가 20인 레코드가 존재하면 삭제가 되지 않는다.

자식 테이블(오른쪽)에 DEPTNO가 20인 레코드가 2개 존재하므로, 삭제가 불가능하다.

--DEPT_TAB에서 20번 부서 삭제
DELETE FROM DEPT_TAB
WHERE DEPTNO=20;

실행 결과: child record found 오류

 

해결 방법1: 자식 테이블인 EMP_TAB에서 부서가 20인 직원들을 다른 부서로 부서이동시키면 된다.

--부서이동: EMP_TAB에서 20번 부서 직원들의 부서번호를 10번으로 수정하세요
UPDATE EMP_TAB
SET DEPTNO = 10
WHERE DEPTNO = 20;
SELECT * FROM EMP_TAB;
COMMIT;

DELETE FROM DEPT_TAB
WHERE DEPTNO=20; --[O]
SELECT * FROM DEPT_TAB;
--자식 레코드를 변경(20번 부서 직원들을 10번으로 수정)한 뒤에는 삭제 가능

실행 결과

---

해결방법2: ON DELETE CASCADE

ON DELETE CASCADE 옵션을 적용하면 부모 테이블에서 행을 삭제할 경우, 연결된 자식 테이블의 행이 함께 삭제된다. 연결된 데이터를 한 번에 지울 수 있어 데이터의 관리가 편리해지고 일관성을 유지할 수 있다.

 

부모 테이블: JAVA_MEMBER자식 테이블: BBS (게시판)JAVA_MEMBER에 있는 회원들만 BBS 게시판에 글을 쓸 수 있다. 자식 테이블인 BBS의 작성자(writer)는 부모 테이블인 JAVA_MEMBER의 id를 참조한다.

CREATE TABLE BBS(
    NO NUMBER(4) CONSTRAINT BBS_NO_PK PRIMARY KEY,
    TITLE VARCHAR2(200) NOT NULL,
    WRITER VARCHAR2(20) CONSTRAINT BBS_WRITER_FK REFERENCES JAVA_MEMBER(ID) ON DELETE CASCADE,
    CONTENT VARCHAR2(2000),
    WDATE DATE DEFAULT SYSDATE
);

부모 테이블(JAVA_MEMBER)

자식 테이블(BBS)

--ON DELETE CASCADE: hong 아이디 회원 삭제 가능
--hong 아이디 회원을 삭제하세요
DELETE FROM java_member WHERE id='hong';
select * from java_member; --'hong'삭제
select * from bbs; --'hong'이 쓴 게시글 삭제됨

부모 테이블(JAVA_MEMBER)에서 'hong'이 삭제됨

자식 테이블(BBS)에서 'hong'이 쓴 게시글이 삭제됨

 

 

[3] UNIQUE 제약조건

UNIQUE: 유일한 값을 갖도록 제한하지만, NULL은 허용된다.

CREATE TABLE UNI_TAB(
    DEPTNO NUMBER(2) CONSTRAINT UNI_TAB_DEPTNO_UK UNIQUE,
    DNAME CHAR(14),
    LOC CHAR(10)
);
SELECT * FROM UNI_TAB;
INSERT INTO UNI_TAB
VALUES(10, '노무부', '서울');
INSERT INTO UNI_TAB
VALUES(NULL, '행정부', '서울'); --NULL[O]
INSERT INTO UNI_TAB
VALUES(20, '기획부', '서울');

 

 

[4] NOT NULL 제약조건

• null값을 허용하지 않는다.
• 컬럼 수준에서만 제약이 가능하다.

CREATE TABLE NN_TAB(
    DEPTNO NUMBER(2) CONSTRAINT NN_TAB_DEPTNO_NN NOT NULL,
    DNAME CHAR(14)
);
INSERT INTO NN_TAB VALUES(1, '인사부');
INSERT INTO NN_TAB VALUES(NULL, '인사부');
--ERROR: cannot insert NULL into ("SCOTT"."NN_TAB"."DEPTNO")
SELECT * FROM NN_TAB;

 

 

[5] CHECK 제약조건

행이 만족해야 할 조건을 기술

CREATE TABLE CK_TAB(
    DEPTNO NUMBER(2) CONSTRAINT CK_TAB_DEPTNO_CK CHECK(DEPTNO> 10 AND DEPTNO <=20),
    DNAME CHAR(16)
);
INSERT INTO CK_TAB VALUES(11, '기획부');
INSERT INTO CK_TAB VALUES(12, '기획부');
INSERT INTO CK_TAB VALUES(20, '기획부');
INSERT INTO CK_TAB VALUES(21, '기획부'); --check constraint violated
SELECT * FROM CK_TAB;

 

---

서브쿼리를 이용한 테이블 생성

CREATE TABLE 테이블명(컬럼명1, ...)
AS
SUBQUERY(SELECT FROM...)

--emp에서 30번 부서에 근무하는 사원 정보만 가져와서 emp_30 테이블을 생성하세요
CREATE TABLE emp_30(eno, ename, job, hdate, sal, comm, dno)
AS 
SELECT empno, ename, job, hiredate, sal, comm, deptno
FROM emp
WHERE deptno=30;

emp_30 테이블

 

--[문제1]
-- EMP테이블에서 부서별로 인원수, 평균 급여, 급여의 합, 최소 급여,
-- 최대 급여를 포함하는 EMP_DEPTNO 테이블을 생성하라.
CREATE TABLE emp_deptno(deptno, cnt, avg_sal, sum_sal, min_sal, max_sal)
AS
SELECT deptno, COUNT(empno), ROUND(AVG(sal), 1), SUM(sal), MIN(sal), MAX(sal)
FROM emp
GROUP BY deptno;

 

--[문제2] emp 테이블에서 사번, 이름, 업무, 입사일자, 부서번호만 포함하는
--emp_temp 테이블을 생성하는데, 자료는 포함하지 않고 구조만 생성하여라.
CREATE TABLE emp_temp
AS
SELECT empno, ename, job, hiredate, deptno
FROM emp
WHERE 1=0;
--조건절에 거짓 조건을 걸면 자료는 포함되지 않고 구조만 생성됨

emp_temp 테이블

테이블 구조만 복사하기

--WHERE절에 거짓 조건을 넣어주면 테이블 구조만 복사된다.
CREATE TABLE 테이블명
AS
SELECT * FROM 원테이블명 WHERE 1=2;

 

 

---

ALTER

테이블의 구조를 수정하는 역할

 

컬럼 추가/수정/삭제

컬럼 추가	ALTER TABLE 테이블명 ADD 추가할 컬럼정보(컬럼명 자료형 기본값)
컬럼 정보 수정	ALTER TABLE 테이블명 MODIFY 수정할 컬럼정보(컬럼명 자료형 기본값)
컬럼명 수정	ALTER TABLE 테이블명 RENAME COLUMN OLD_컬럼명 TO NEW_컬럼명
컬럼 삭제	ALTER TABLE 테이블명 DROP COLUMN 삭제할 컬럼명

 

예제

CREATE TABLE SAMPLE_TAB(
    no NUMBER(4)
);

--<1> sample_tab에 name VARCHAR2(20)을 추가하세요
ALTER TABLE sample_tab ADD name VARCHAR2(20) NOT NULL;

--<2> no 컬럼의 자료형을 CHAR(4)로 변경하세요
ALTER TABLE sample_tab MODIFY no CHAR(4);

--<3> no 컬럼명을 num으로 수정하세요
ALTER TABLE sample_tab RENAME COLUMN no TO num;

--<4> name 컬럼을 삭제하세요
ALTER TABLE sample_tab DROP COLUMN name;

 

 

---

DROP

테이블을 삭제하는 역할

DROP TABLE 테이블명 [CASCADE CONSTRAINT]
--CASCADE 옵션을 줄 경우 외래키(FK)로 물린 하위 테이블들까지 함께 삭제된다.

--temp_tab 테이블을 삭제하세요.
DROP TABLE temp_tab;

 

 

---

RENAME

테이블의 이름을 변경하는 역할

RENAME old_name TO new_name;

--sample_tab 테이블명을 temp_tab으로 변경하세요
RENAME sample_tab TO temp_tab;