5. ORACLE SQL 기초: 데이터 정의어

 

---

 

 

1. CREATE 문

테이블을 구성하고, 속성과 속성에 관한 제약을 정의하며, 기본 키 및 외래 키를 정의하는 명령어.

PRIMARY KEY는 기본 키를 정할 때 사용하며, FOREIGN KEY는 외래 키를 정할 때 사용한다.

ON UPDATE, ON DELETE는 외래 키 속성의 수정과 튜플 삭제 시 동작을 나타낸다.

CREATE 문의 기본 문법은 아래와 같다.

CREATE TABLE 테이블이름 
( { 
    속성이름 데이터타입
    [NOT NULL | UNIQUE | DEFAULT 기본값 | CHECK 체크조건]
  }
  [PRIMARY KEY (속성이름(들))]
  {
    [FOREIGN KEY (속성이름) REFERENCES 테이블이름(속성이름)]
    [ON DELETE [CASCADE┃SET NULL]
  }
)

외래 키 제약 조건을 명시할 때는 반드시 참조되는 테이블(부모 릴레이션)이 존재해야 한다.

참조되는 테이블은 기본 키여야 한다.

외래 키 사용 시, 코드 작성 방법은 다음과 같다.

FOREIGN KEY (속성이름) REFERENCES 테이블이름(속성이름)
[ON DELETE [CASCADE┃SET NULL]

 

 

 

1.1) CREATE 문 예제

● 다음과 같은 속성을 가진 NewBook 테이블을 생성하시오, 정수형은 NUMBER를, 문자형은 가변형 문자 타입인 VARCHAR2를 사용한다.

bookid(도서번호)	NUMBER
bookname(도서이름)	VARCHAR2(20)
publisher(출판사)	VARCHAR2(20)
price(가격)	NUMBER

CREATE TABLE NewBook (
	bookid 		NUMBER,
	bookname 	VARCHAR2(20),
	publisher 	VARCHAR2(20),
	price 		NUMBER
);

기본 키를 지정하고 싶다면 아래와 같이 작성한다.

CREATE TABLE NewBook (
	bookid 		NUMBER,
	bookname 	VARCHAR2(20),
	publisher 	VARCHAR2(20),
	price 		NUMBER,
	PRIMARY KEY (bookid)
);

만약 복합 키를 지정해야 한다면 아래와 같이 작성한다.

CREATE TABLE NewBook (
	bookname 	VARCHAR2(20),
	publisher 	VARCHAR2(20),
	price 		NUMBER,
	PRIMARY KEY (bookname, publisher)
);

좀 더 복잡한 제약 사항을 추가하고자 한다면 아래와 같이 작성한다.

CREATE TABLE NewBook (
	bookname 	VARCHAR(20)  NOT NULL,
	publisher 	VARCHAR(20)  UNIQUE,
	price 		NUMBER       DEFAULT 10000 CHECK(price > 1000),
	PRIMARY KEY (bookname, publisher)
);

bookname에는 NULL 값이 들어올 수 없다.

publisher에는 중복된 값이 들어올 수 없다.

price에는 1000보다 큰 값만 들어올 수 있으며, 기본 값은 10000이다.

● 다음과 같은 속성을 가진 NewCustomer 테이블을 생성하시오.

custid(고객번호)	NUMBER, PRIMARY KEY
name(이름)	VARCHAR2(40)
address(주소)	VARCHAR2(40)
phone(전화번호)	VARCHAR2(30)

CREATE TABLE NewCustomer (
	custid 		NUMBER PRIMARY KEY,
	name 		VARCHAR2(40),
	address 	VARCHAR2(40),
	phone 		VARCHAR2(30)
);

● 다음과 같은 속성을 가진 NewOrders 테이블을 생성하시오.

orderid(주문번호)	NUMBER, 기본 키
custid(고객번호)	NUMBER, NOT NULL 제약조건, 외래 키(NewCustomer.custid, 연쇄삭제)
bookid(도서번호)	NUMBER, NOT NULL 제약조건
saleprice(판매가격)	NUMBER
orderdate(판매일자)	DATE

CREATE TABLE NewOrders (
	orderid 	NUMBER,
	custid 		NUMBER NOT NULL,
	bookid 		NUMBER NOT NULL,
	saleprice 	NUMBER,
	orderdate 	DATE,
	PRIMARY KEY (orderid),
	FOREIGN KEY (custid) REFERENCES NewCustomer(custid) ON DELETE CASCADE
);

● EMPLOYEES 테이블의 구조를 기반으로 EMPLOYEES2 테이블을 생성하라. EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID만 포함시키며, 열 이름을 각각 ID, FIRST_NAME, LAST_NAME, SALARY, DEPT_ID로 지정한다. 테이블 구조만 생성하고, 데이터를 가져오지는 않는다.

CREATE TABLE EMPLOYEES2 AS 
SELECT 
    EMPLOYEE_ID AS "ID", 
    FIRST_NAME, 
    LAST_NAME, 
    SALARY, 
    DEPARTMENT_ID AS "DEPT_ID"
FROM EMPLOYEES
WHERE 1 = 2;

WHERE 조건을 사용해 항상 FALSE가 나오는 값을 추가하면 데이터를 가져오지 않는다.

WHERE 조건을 생략하면 해당 데이터를 알아서 가져온다.

 

---

 

2. ALTER 문

생성된 테이블의 속성, 속성에 대한 제약을 변경하는 데 사용한다.

기본 키와 외래 키를 변경할 수 있다.

ADD, DROP은 속성 추가 및 제거에 사용된다.

MODIFY는 속성의 기본 값을 설정하거나 삭제할 때 사용된다.

ADD <제약 이름>, DROP <제약 이름>은 제약 사항을 추가하거나, 삭제할 때 사용된다.

ALTER 문의 기본 문법은 다음과 같다.

ALTER TABLE 테이블이름
	[ADD 속성이름 데이터타입]
	[DROP COLUMN 속성이름]
	[MODIFY 속성이름 데이터타입]
	[MODIFY 속성이름 [NULL┃NOT NULL]]
	[ADD PRIMARY KEY(속성이름)]
	[[ADD┃DROP] 제약이름]

 

2.1) ALTER 문 예제

● NewBook 테이블에 VARCHAR2(13)의 자료형을 가진 isbn 속성을 추가하시오.

ALTER TABLE NewBook ADD isbn VARCHAR2(13);

● NewBook 테이블의 isbn 속성의 데이터 타입을 NUMBER형으로 변경하시오.

ALTER TABLE NewBook MODIFY isbn NUMBER;

● NewBook 테이블의 isbn 속성을 삭제하시오.

ALTER TABLE NewBook DROP COLUMN isbn;

● NewBook 테이블의 bookid 속성에 NOT NULL 제약조건을 적용하시오.

ALTER TABLE NewBook MODIFY bookid NUMBER NOT NULL;

● NewBook 테이블의 bookid 속성을 기본 키로 변경하시오.

ALTER TABLE NewBook ADD PRIMARY KEY(bookid);

● 존재하지 않는 부서(DEPT3)에 사원이 할당되지 않도록 하는 외래 키 참조를 EMP3 테이블에 추가한다. 제약 조건 이름은 my_emp_dept_id로 한다. 부서가 삭제되면, 해당 부서에 소속되었던 사원의 소속 부서의 정보는 NULL로 설정되도록 하라.

ALTER TABLE EMP3 ADD CONSTRAINT my_emp_dept_id 
FOREIGN KEY(DEPT_ID) REFERENCES DEPT3(ID) 
ON DELETE SET NULL;

제약 조건을 추가하고자 한다면 ADD CONSTRAINT를 사용한다.

사용자가 직접 이름을 작성해주면 해당 이름으로 제약 조건이 설정된다.

따로 설정하지 않으면 임의로 자동 설정된다.

ON DELETE SET을 사용하면 값이 삭제되었을 때 어떤 값으로 설정되도록 만들 것인지 지정해줄 수 있다.

여기서는 NULL로 지정했다.

● EMP3 테이블을 수정한다. 데이터 유형이 NUMBER이고 전체 자릿수 2, 소수점 이하 자릿수가 2인 COMMISSION 열을 추가한다. COMMISSION 열 값은 항상 0보다 크거나 같도록 제한 조건을 설정한다.

ALTER TABLE EMP3 ADD COMMISSION NUMBER(2, 2) CHECK (COMMISSION >= 0);

CHECK를 사용하면, 추가되는 값이 해당 조건을 만족해야 추가된다는 조건을 추가해줄 수 있다.

● EMP3의 LOC 컬럼 이름을 LOC2로 변경하라.

ALTER TABLE EMP3 RENAME COLUMN LOC TO LOC2;

 

---

 

3. DROP 문

테이블을 삭제한다.

테이블의 구조, 데이터를 모두 삭제하기 때문에 사용할 경우 주의해야 한다.

데이터만 삭제하고자 한다면 DELETE 문을 사용한다.

DROP 문의 기본 문법은 다음과 같다.

DROP TABLE 테이블이름

 

3.1) DROP 문 예제

● NewBook 테이블을 삭제하시오.

DROP TABLE NewBook;

● NewCustomer 테이블을 삭제하시오. 만약 삭제가 거절된다면 원인을 파악하고 관련된 테이블을 같이 삭제하시오. NewOrders 테이블이 NewCustomer를 참조하고 있다.

DROP TABLE NewCustomer;

 

---

 

---

수고하셨습니다!

---