테이블에 관한 SQL문

1. 테이블 구조를 정의하는 CREATE TABLE

1. 데이터형

컬럼을 정의할 때 지정할 수 있는 데이터형

CHAR(size) : 고정 길이 문자 데이터. VARCHAR2와 동일한 형태로 최소 크기는 1

VARCHAR2(size) : 최대 2000 byte 가변 길이 문자 데이터. 최소 크기는 1

NUMBER : 40자리까지 숫자를 저장. (소수점 이하는 저장 불가)

NUMBER(w) : 최대 38자리까지 가능.

NUMBER(w, d) : w는 전체 길이, d는 소수점 이하 자리수

DATE : BC 4712년 1월 1일 ~ AD 4712년 12월 31일까지의 날짜

LONG : 가변 길이의 문자 데이터 타입. 최대 2GB.

LOB : 2GB까지의 가변 길이 바이너리 데이터 저장. (이미지 문서, 실행 파일)

ROWID : Tree-piece Format. DB에 저장되지 않으며 DB Data도 아님.

TIMESTAMP(n) : DATE형의 확장 형태.

2. 식별자 명명 규칙

테이블명과 컬럼명처럼 사용자가 이름을 부여하는 것을 식별자라고 함.

- 반드시 문자로 시작해야 한다.

- 1~30까지만 가능

- A~Z까지의 대소문자와 0~9까지의 숫자, 특수기호는 (_, $, #)만 포함 가능

- 오라클에서 사용되는 예약어나 다른 객체명과 중복 불가

- 공백 허용 안한다.

데이터 저장 구조

database → table → row (record)

테이블 구조, 변경, 삭제하는 DDL

1. CREATE TABLE (테이블 생성) (p264 실습)

※ 오라클에는 대소문자 구문 없음.

CREATE TABLE 테이블명( 

);

CREATE TABLE emp01(  -- 테이블 생성

EMPNO number(4), -- 자바의 int 같은 것. 4자리의 숫자

ENAME varchar2(20), -- 자바의 String 같은 것. 20자리의 문자

SAL number(7,2) -- 자바의 double 같은 것. 7자리의 정수 숫자와 2자리의 소수점 이하 숫자

);

SELECT * FROM emp01; -- 생성된 테이블의 데이터 확인.

DESC emp01;  -- 테이블 구조 보기. 이클립스에서 실행 안됨.

이클립스의 SQL Scrapbook 창에서 위와 같이 입력 후, 실행할 텍스트를 드래그하여

마우스 오른쪽 버튼 누른 뒤 Execute Selected Text(ALT-X).

2. 테이블 구조를 변경하는 ALTER TABLE

1) 컬럼 추가

ALTER TABLE table_name

ADD(column_name data_type expr, ...);

ALTER TABLE DEPT01 ADD(LOC CHAR(13));

- LOC라는 컬럼이 추가된다.

2) 컬럼 속성 변경

ALTER TABLE table_name

MODIFY(column_name data_type expr, ...);

ALTER TABLE DEPT01 MODIFY(LOC CHAR(20));

- LOC 컬럼의 CHAR 수가 13에서 20으로 변경되었다.

3) 컬럼 이름 변경

ALTER TABLE table_name

RENAME COLUMN 변경전이름 TO 변경후이름;

4) 컬럼 삭제

ALTER TABLE table_name

DROP COLUMN column_name;

ALTER TABLE DEPT01 DROP COLUMN LOC;

-- LOC라는 컬럼이 삭제된다.

3. 테이블 삭제 DROP TABLE

DROP TABLE EMP01; 

-- 테이블이 삭제된다.

4. 테이블 내용 삭제 TRUNCATE TABLE

TRUNCATE TABLE table_name; 

-- 테이블의 데이터만 삭제된다.

5. 테이블명 변경 RENAME

RENAME table_name1 to table_name2;

-- 테이블의 이름이 변경된다.

6. 테이블과 데이터 복사하기 CREATE TABLE AS

- 테이블 복사하기

CREATE TABLE 새테이블명 AS

SELECT * FROM 복사할테이블명 [WHERE 절]

-  테이블 구조만 복사하기

CREATE TABLE 새테이블명 AS

SELECT * FROM 복사할테이블명 WHERE 1=2 [WHERE에다가 참이 아닌 조건을 넣음]

CREATE TABLE EMP_HIR

AS

SELECT EMPNO, ENAME, HIREDATE FROM EMP

WHERE 1 = 0;

- SELECT문에 EMP의 특정 컬럼을 선택하여 이것으로 해당하는 테이블을 새로 생성.

- 테이블은 이미 생성되어 있고 데이터만 복사

INSERT INTO 복사할테이블명 SELECT * FROM 복사할테이블명 [WHERE 절]

- 테이블 이름 변경

ALTER TABLE 구테이블명 RENAME TO 신테이블명

7. 데이터 딕셔너리와 데이터 딕셔너리 뷰

사용자의 정보나 사용자가 생성한 객체의 정보를 저장하는 시스템 테이블이 데이터 딕셔너리.

데이터 딕셔너리 뷰의 종류

DBA_XXXX : 데이터베이스 관리자만 접근이 가능한 객체 등의 정보 조회

ALL_XXXX : 자신의 계정이 소유하거나 권한을 부여받은 객체 등에 관한 정보 조회

USER_XXXX : 자신의 계정이 소유한 객체 등에 관한 정보

1) USER_ 데이터 딕셔너리

자신의 계정이 소유한 객체에 관한 정보를 조회

DESC USER_TABLES; -- 현재 접속한 사용자의 계정이 소유한 모든 테이블 정보를 조회

SHOW USER -- 현재 사용자가 누구인지를 조회

SELECT TABLE_NAME FROM USER_TABLES; --데이터 딕셔너리상의 테이블들이 표시된다.

2) ALL_ 데이터 딕셔너리

현재 접근할 수 있는 객체. 자기 계정의 소유이거나 접근 권한을 부여받은 타 계정의 객체를 조회

DESC ALL_TABLE;

3) DBA_ 데이터 딕셔너리

DBA에서 접근할 수 있는 객체 등을 조회할 수 있는 뷰. DBA에서 접근 불가능한 정보는 없다. DBA 데이터 딕셔너리 뷰는 DBA 시스템 권한을 가진 사용자만 접근할 수 있다.

테이블에 데이터를 추가, 변경, 삭제하는 DML

데이터를 조작하는 DML(Data Manipulation Language)

1. INSERT : 테이블에 새로운 행을 추가 

[ 컬럼명을 지정한 INSERT ]

INSERT INTO table_name 

(column_name, ...)

VALUES(column_value, ...);

-- 지정한 컬럼명에 해당하는 데이터를 VALUES에 넣어주면 된다.

-- INSERT시 지정하지 않은 컬럼에는 NULL값이 데이터로 들어가게 된다.

[ 컬럼명을 생략한 INSERT ]

INSERT INTO table_name 

VALUES(column_value, ...);

-- 컬럼명을 생략하면 테이블 기본 컬럼 순서대로 VALUES 값을 넣어준다.

-- NULL값을 명시적으로 넣어줄 때에는 ''를 써주면 된다.

(실습)

1) 간략하게 테이블 생성

CREATE TABLE DEPT01

AS

SELECT * FROM DEPT WHERE 1=0;

2) 데이터 입력하기

INSERT INTO DEPT01

(DEPTNO, DNAME, LOC)

VALUES(10, 'ACCOUNTING', 'NEW YORK');

3) 확인해보기

select * from dept01;

4) Display result in Single Tab 눌러보면 DEPTNO, DNAME, LOC가 만들어진 것 확인 가능.

[ 여러행 입력 ]

방법1)

INSERT ALL

INTO table (column1, column2)

VALUES (value1, value2)

INTO table (column1, column2)

VALUES (value1, value2)

...

SELECT * FROM DUAL;

방법2)

INSERT INTO table (column1, column2)

SELECT value1, value2 FROM DUAL UNION ALL

SELECT value1, value2 FROM DUAL UNION ALL

...etc...

SELECT value1, value2 FROM DUAL;

INSERT INTO GOODSINFO (CODE, NAME, PRICE, MAKER)

SELECT '10001', '디지털TV', 350000, 'LG'  FROM DUAL UNION ALL

SELECT '10002', 'DVD 플레이어', 250000, 'LG' FROM DUAL UNION ALL

SELECT '10003', '디지털 카메라', 210000, '삼성' FROM DUAL UNION ALL

SELECT '10004', '전자사전', 180000, '아이리버' FROM DUAL UNION ALL

SELECT '10005', '벽걸이 에어컨', 400000, '삼성' FROM DUAL;

[ 다중 테이블 다중행 입력 ]

INSERT ALL

SELECT * FROM 테이블명

다른 테이블에서 데이터를 가져와서 입력

CREATE TABLE EMP_HIR

AS

SELECT EMPNO, ENAME, HIREDATE FROM EMP

WHERE 1 = 0;

-- EMP 테이블의 EMPNO, ENAME, HIREDATE를 복사하여 EMP_HIR 테이블 생성

CREATE TABLE EMP_MGR

AS

SELECT EMPNO, ENAME, MGR FROM EMP

WHERE 1 = 0;

-- EMP 테이블의 EMPNO, ENAME, MGR를 복사하여 EMP_MGR 테이블 생성

INSERT ALL

INTO EMP_HIR VALUES(EMPNO, ENAME, HIREDATE)

INTO EMP_MGR VALUES(EMPNO, ENAME, MGR)

SELECT EMPNO, ENAME, HIREDATE, MGR FROM EMP

WHERE DEPTNO = 20;

-- EMP 테이블에서 DEPTNO가 20인 데이터들을 EMP_HIRE와 EMP_MGR에 한꺼번에 INSERT

INSERT ALL 명령에 조건 WHEN으로 다중 테이블에 다중행 입력하기

EMP_HIR02은 EMPNO, ENAME, HIREDATE만 있는  빈 테이블.

EMP_SAM은 EMPNO, ENAME, SAL만 있는 빈 테이블.

INSERT ALL

WHEN HIREDATE > '1982/01/01' THEN 

INTO EMP_HIR02 VALUES(EMPNO, ENAME, HIREDATE)

WHEN SAL > 2000 THEN

INTO EMP_SAL VALUES(EMPNO, ENAME, SAL)

SELECT EMPNO, ENAME, HIREDATE, SAL FROM EMP;

2개의 빈 테이블에 

1982/01/01보다 HIREDATE가 크면 EMP로부터 EMP_HIR02에 EMPNO, ENAME, HIREDATE 값를 삽입

SAL이 2000보다 크면 EMP로부터 EMP_SAL에 EMPNO, ENAME, SAL 값을 삽입.

WHEN 조건, THEN INTO...형식으로 써주면 된다.

PIVOTING으로 다중 테이블에 다중행 입력하기 (p309 참조)

-- 한주차 월~금까지 매일의 판매 실적을 기록하는 테이블 생성

CREATE TABLE SALES(

SALES_ID NUMBER(4),

WEEK_IN NUMBER(4),

MON_SALES NUMBER(8,2),

TUE_SALES NUMBER(8,2),

WED_SALES NUMBER(8,2),

THU_SALES NUMBER(8,2),

FRI_SALES NUMBER(8,2));

-- PIVPTING INSERT문의 결과를 저장할 테이블 생성

CREATE TABLE SALES_DATA(

SALES_ID NUMBER(4),

WEEK_IN NUMBER(4),

DAILY_ID NUMBER(4),

SALES NUMBER(8,2));

SALES 테이블에 주간 판매 실적 추가

INSERT INTO SALES VALUES(1001, 1, 200, 100, 300, 400, 500);

INSERT INTO SALES VALUES(1002, 2, 100, 300, 200, 500, 350);

INSERT ALL

INTO SALES_DATA VALUES(SALES_ID, WEEK_ID, 1, MON_SALES)

INTO SALES_DATA VALUES(SALES_ID, WEEK_ID, 2, TUE_SALES)

INTO SALES_DATA VALUES(SALES_ID, WEEK_ID, 3, WED_SALES)

INTO SALES_DATA VALUES(SALES_ID, WEEK_ID, 4, THU_SALES)

INTO SALES_DATA VALUES(SALES_ID, WEEK_ID, 5, FRI_SALES)

SELECT SALES_ID, WEEK_ID, MON_SALES, TUE_SALES, WED_SALES, THU_SALES, FRI_SALES

FROM SALES;

2. UPDATE : 테이블 내용을 수정

UPDATE table_name

SET column_name1 = value1, column_name2 = value2 ...

WHERE conditions;

어떤 행을 수정할 지 WHERE문을 쓰지 않으면 테이블에 있는 모든 행이 수정되므로 주의.

UPDATE DEPT01

SET LOC = 'NEW YORK'

WHERE DEPNO = 20;

-- DEPT01 테이블에서 DEPNO가 20인 LOC를 NEW YORK으로 변경

UPDATE EMP_HIR

SET ENAME = 'LEE', HIREDATE = SYSDATE

WHERE EMPNO = 10;

-- EMP_HIR 테이블에서 ENAME은 LEE, HIREDATE는 현재 날짜로 변경

[ 서브쿼리를 활용한 UPDATE ]

UPDATE DEPT01

SET (DNAME, LOC) = (SELECT DNAME, LOC 

                    FROM DEPT 

                    WHERE DEPTNO = 40)

WHERE DEPTNO = 40;

-- DEPT01 테이블에 (DEPT 테이블에서 DEPTNO가 40인 DNAME과 LOC)를 DEPTNO가 40인 곳에 업데이트.

3. DELETE : 테이블 행을 삭제

DELETE FROM table_name

WHERE conditions;

DELETE FROM DEPT01

WHERE DEPTNO = 20;

[ 서브쿼리를 이용한 데이터 삭제 ]

DELETE FROM DEPT01

WHERE DEPTNO = (

SELECT DEPTNO FROM DEPT WHERE DNAME = 'ACCOUNTING');

-- DEPT01에서 DEPTNO가 DEPT에서 DNAME이 'ACCOUNTING'인 DEPTNO와 같은 곳을 삭제.

4. 테이블을 합병하는 MERGE

다음과 같이 예제를 위한 EMP01과 EMP02 테이블을 생성한다.

CREATE TABLE EMP01

AS

SELECT * FROM EMP;

예제를 위한 새로운 EMP01이 완성.

CREATE TABLE EMP02

AS

SELECT * FROM EMP

WHERE JOB = 'MANAGER'; -- JOB이 매니저인 것만 가져와 테이블 생성.

UPDATE EMP02

SET JOB = 'TEST'; -- JOB은 전부 TEST로 변경.

INSERT INTO EMP02

VALUES(8000, 'SYJ', 'TOP', 7566, '2009/01/12', 1200, 10, 20); -- 새로운 로우 추가

예제를 위한 새로운 EMP02이 완성.

MERGE INTO EMP01 USING EMP02

ON(EMP01.EMPNO = EMP02.EMPNO)

WHEN MATCHED THEN

  UPDATE SET

  EMP01.ENAME = EMP02.ENAME,

  EMP01.JOB = EMP02.JOB,

  EMP01.MGR = EMP02.MGR,

  EMP01.HIREDATE = EMP02.HIREDATE,

  EMP01.SAL = EMP02.SAL,

  EMP01.COMM = EMP02.COMM,

  EMP01.DEPTNO = EMP02.DEPTNO

WHEN NOT MATCHED THEN

INSERT VALUES(EMP02.EMPNO, EMP02.ENAME, EMP02.JOB, 

              EMP02.MGR, EMP02.HIREDATE, EMP02.SAL,

              EMP02.COMM, EMP02.DEPTNO);

EMP01 테이블에서 JONES, BLAKE, CLARK의 JOB이 EMP02 테이블과 병합되어 TEST로 변경되었다.