데이터베이스 (집공부6)

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

 -> DDL을 이용해 새롭게 생성, 수정 삭제 해보자.

-------------------------------------------------------------------------------------------------------------------

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

 -> CREATE TABLE 문의 기본 형식

ex) CREATE TABLE table_name (column_name, data_tupe expr, ...);

ex) create table test

(no NUMBER(3), name VARCHAR2(10), birth DATE default sysdate)

tablespace users;

-------------------------------------------------------------------------------------------------------------------

테이블 생성시 주의사항

1. 테이블 이름은 반드시 문자로 시작해야 합니다.

2. 테이블 이름이나 컬럼 이름은 최대 30 bytes 까지 가능합니다. 한글로 테이블 이름을 생헝할경우 15글자 까지만 가능하다.

3. 테이블 이름은 한 명의 사용자가 다른 오브젝트들의 이름과 중복으로 사용할 수 업삳.

4. 테이블 이름이나 오브젝트 이름을 오라클이 사용하는 키워드를 사용하지 않기를 권장합니다.

-------------------------------------------------------------------------------------------------------------------

1.1 데이터 형

자료형 - 사용자 정의, 내장

내장 - 스칼라, 커렉션, 관계

스칼라 : CHAR(N), NCHAR(N), VARCHAR2(N), NVARCHAR2(N), NUMBER(P, S), DATE, RAW(N), BLOB, CLOB, NCLOB, BFILE, LONG, LONG RAW, ROWID

컬렉션 - VARRAY, TABLE

관계 - REF

이 름	비 고
CHAR(size)	고정 길이 문자 데이터, VARCHAR2와 동일한 형태의 자료를 저장할 수 있고, 입력된 자료의 길이와는 상관없이 정해진 길이만큼 저장 영역 차지. 최소 크기는 1
VARCHAR2(size)	Up to 2000 Bytes 가변 길이 문자 데이터. 실제 입력된 문자열의 길이만큼 저장 영역을 차지. 최대 크기는 명시해야 하며, 최소 크기는 1
NUMBER	Internal Nubmer Format 최고 40자리까지의 숫자를 저장할 수 있다. 이때 소수점이나 부호는 길이에 포함되지 않는다.
NUMBER(w)	W자리까지의 수치로 최대 38자리까지 가능하다. (38자리가 유효 숫자이다.)
NUMVER(w, d)	w는 전체 길이, d는 소수점 이하 자릿수이다. 소수점은 자릿수에 포함되지 않는다.
DATE	BC 4712년 1월 1일 ~ AD 4712년 12월 31일까지의 날짜
LONG	가변 길이의 문자형 데이터 타입, 최대 크기는 2GB
LOB	2G까지의 가변 길이 바이너리 데이터를 저장시킬 수 있습니다. 이미지 문서, 실행 파일을 저장할 수 있습니다.
ROWID	ROWID는 Tree-piece Format을 갖음 ROWID는 DB에 저장되어 있지 않으며, DB Data도 아니다.
BFILE	대용량의 바이너리 데이터를 파일 형태로 저장 최대 4GB
TIMESTAMP(n)	DATA 형의 확장된 형태
INTERVAL YEAR TO MONTH	년과 월을 이용하여 기간을 저장
INTERVAL DAY TO SECOND	일, 시, 분, 초를 이용하여 기간을 저장 두 날짜 값의 정확한 차이를 표현하는데 유용하다.

-------------------------------------------------------------------------------------------------------------------

1.1.1 LOB (Large Object)

 -> 텍스트, 그래픽 이미지, 동영상, 사운드와 같이 구조화되니 않은 대용량의 텍스트나 데이터를 저장하기 위한 데이터 형이다.

 -> 최대 4GB까지 저장 가능하다. 오라클에서 제공되는 LOB 데이터 형은 BLOB, CLOB, NCLOB, BFILE 등이 있습니다.

 -> BLOB는 그래픽 이미지, 동영상, 사운드와 같은 구조화되지 않은 데이터를 저장하기 위해 사용된다.

 -> CLOB는 e-BOOK과 같은 대용량의 텍스트 데이터를 저장하기 위해서 사용한다.

 -> NCLOB은 국가별 문자셋 데이터를 저장하고, BFILE 은 바이너리 데이터를 파일 형태로 저장한다.

-------------------------------------------------------------------------------------------------------------------

1.1.2 ROWID

 -> ROWID 데이터 형은 테이블에서 행의 위치를 지정하는 논리적인 주소값이다.

 -> ROWID 는 데이터베이스 전체에서 중복되지 않는 유일한 값으로 테이블에 새로운 행이 삽입되면 테이블 내부에서 의사 컬럼 형태로 자동적으로 생성된다.

 -> ROWID는 테이블의 특정 레코드를 랜덤하게 접근하기 위해서 주로 사용된다.

ex) SELECT ROWID, EMPNO, ENAME FROM EMP;

-------------------------------------------------------------------------------------------------------------------

1.1.3 TIMESTAMP

 -> DATE 형의 확장된 형태로서 백만분의 일초 단위까지 표현할 수 있다.

 -> INTERVAL YEAR TO MONTH

 -> 년과 월을 사용하여 두 날짜 사이의 기간을 저장하기 위한 데이터 형이다.

ex) INTERVAL YEAR(년도에 대한 자릿수) TO MONTH (달에 대한 자릿수)

 -> 자릿수를 지정하지 않으면 기본적으로 2자리 잡힌다.

-------------------------------------------------------------------------------------------------------------------

1.1.4. INTERVAL YEAR TO MONTH

 -> INTERVAL YEAR TO MONTH 형은 년과 월을 사용하여 두 날짜 사이의 기간을 저장하기 위한 데이터 형이다.

ex) INTERVAL YEAR(년도에 대한 자릿수) TO MONTH(달에 대한 자릿수)

 -> 자릿수를 지정하지 않으면 기본적으로 2자리 잡힙니다.

-------------------------------------------------------------------------------------------------------------------

/*

실습

INTERVAL YEAR TO MONTH 형으로 컬럼을 생성하여 3년이란 기간을 저장해보자.

*/

--1. SAM02란 테이블을 새롭게 생성하는 명령어이다.

create TABLE SAM02(

YEAR01 INTERVAL YEAR(3) TO MONTH);

--2. 생성된 테이블에 기간을 36개월로 저장합니다.

INSERT INTO SAM02 VALUES(INTERVAL '36' MONTH(3));

--3. 오늘 날짜를 출력하고 테이블 SAM02의 YEAR01 이라는 컬럼에 저장된 날짜만큼 지난 날짜를 계산하여 출력해 봅시다.

SELECT YEAR01, SYSDATE, SYSDATE+YEAR01 FROM SAM02;

-------------------------------------------------------------------------------------------------------------------

1.1.5. INTERVAL DAY TO SECOND

 -> INTERVAL DAY TO SECOND 형은 일, 시, 분, 초를 사용하여 두 날짜 사이의 기간을 저장하기 위한 데이터 형이다.

ex) INTERVAL DAY(일수에 대한 자릿수) TO SECOND(초에 대한 자릿수)

 -> 자릿수를 지정하지 않으면 기본적으로 2자리 잡힙니다.

-------------------------------------------------------------------------------------------------------------------

/*

실습

INTERVAL DAY TO SECOND 형으로 컬럼을 생성하여 100일이란 기간을 저장해보자

*/

--1. 다음은 SAM03란 이름의 테이블을 새롭게 생성하는 명령어이다.

CREATE TABLE SAM03(DAY01 INTERVAL DAY(3) TO SECOND);

--2. 생성된 테이블에 기간을 100일을 저장한다.

INSERT INTO SAM03 VALUES(INTERVAL '100' DAY(3));

--3. 오늘 날짜를 출력하고 테이블 SAM03의 DAY01 이라는 컬럼에 저장된 날짜만큼 지난 날짜를 계산하여 출력해 보자.

SELECT DAY01, SYSDATE, SYSDATE+DAY01 FROM SAM03;

-------------------------------------------------------------------------------------------------------------------

2.1 서브쿼리로 테이블 생성하기

 -> CREATE TABLE 명령어 다음에 컬럼을 일일이 정의하는 대신 AS 절을 추가하여 EMP 테이블과 동일한 내용과 구조를 갖는 테이블이 생성가능하다.

ex) CREATE TABLE EMP02 AS SELECT * FROM EMP:

2.2 원하는 컬럼으로 구성된 복제 테이블 생성하기

 -> 서브쿼리문의 SELECT 절에 *대신 원하는 컬럼명을 명시하면 기존 테이블에서 일부의 컬럼만 복사 할 수 있다.

ex) CREATE TABLE EMP03 AS SELECT EMPNO, ENAME FROM EMP;

2.3 원하는 행으로 구성된 복제 테이블 생성하기

 -> 서브 쿼리문의 SELECT 문을 구성할 때 WHERE 절을 추가하여 원하는 조건을 제시하면 기존 테이블에서 일부의 행만 복사한다.

ex) CREATE TABLE EMP05 AS SELECT * FROM EMP WHERE DEPTNO=10;

2.4 테이블의 구조만 복사하기

 -> 테이블의 구조만 복사하는 것은 별도의 명령이 있는 것이 아니다. 이 역시 서브쿼리를 이용해야 하는데 WHERE 조건 절에 항상 거짓이 되는

조건을 지정하게 되면 테이블에서 얻어질 수 있는 로우가 없게 되므로 빈 테이블이 생성되게 됩니다.

ex) CREATE TABLE EMP06 AS SELECT * FROM EMP WHERE 1=0;

 -> WHERE 1=0; 조건은 항상 거짓입니다. 이를 이용하여 테이블의 데이터는 가져오지 않고 구조만 복사하게 된다.

-------------------------------------------------------------------------------------------------------------------

3. ALTER

 -> 테이블의 구조를 변경하기 위한 DDL 명령문이다.

 -> ADD COLUMN 절을 사용하여 새로운 칼럼을 추가한다.

 -> MODIFY COLUMN 절을 사용하여 기존 칼럼을 수정한다.

 -> DROP COLUMN 절을 사용하여 기존 칼럼을 삭제한다.

-------------------------------------------------------------------------------------------------------------------

3.1 새로운 칼럼 추가하기

 -> 새로운 컬럼은 테이블 맨 마지막에 추가되므로 자신이 원하는 위치에 만들어 넣을 수 없습니다.

 -> 또한, 이미 이전에 추가해 놓은 로우가 존재한다면 그 로우에도 컬럼이 추가되지만, 컬럼 값은 NULL값으로 입력됩니다.

ex) ALTER TABLE table_name ADD(column_name, data_yupe expr, ...);

-------------------------------------------------------------------------------------------------------------------

3.2 기존 칼럼 속성 변경하기

 -> 테이블에 이미 존재하는 컬럼을 변경 할 수 있게 된다.

 -> 컬럼을 변경한다는 것은 컬럼에 대해서 데이터 타입이나, 크기, 기본 값들을 변경한다는 의미이다.

ex) ALTER TABLE table_name MODIFY (column_name, data_type expr, ...);

-------------------------------------------------------------------------------------------------------------------

3.3 기존 칼럼 삭제하기

ex) ALTER TABLE table_name DROP COLUMN column_name;

-------------------------------------------------------------------------------------------------------------------

3.4 SET UNUSED 옵션 적용하기

 -> 사용을 논리적으로 제한할 뿐 실제로 컬럼을 삭제하지 않기 때문에 작업 시간이 오래 걸리지 않습니다. 그렇기 때문에 락이 걸리는 일도 일어나지 않게된다.

ex1) ALTER TABLE EMP02 SET UNUSED(JOB);

ex2) ALTER TABLE EMP02 DROP UNUSED COLUMNS;

-------------------------------------------------------------------------------------------------------------------

4. 테이블 구조 삭제하는 DROP TABLE

 -> 기존 테이블을 제거한다.

ex) DROP TABLE_name;

-------------------------------------------------------------------------------------------------------------------

5. 테이블의 모든 로우를 제거하는 TRUNCATE

 -> 기존에 사용하던 테이블의 모든 로우를 제거하기 위한 명령어로 TRUNCATE 가 제공된다.

ex) TRUCATE table_name;

-------------------------------------------------------------------------------------------------------------------

6. 테이블 명을 변경하는 RENAME

 -> 기존에 사용하던 테이블의 이름을 변경하기 위한 명령어이다.

ex1) RENAME old_name TO new_name;

ex2) RENAME EMP02 TO TEST;

-------------------------------------------------------------------------------------------------------------------

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

1. 데이터베이스 자원을 효율적으로 관리하기 위한 다양한정보를 저장하는 시스템 데어 ㄴ=

-------------------------------------------------------------------------------------------------------------------

7. 데이터 닉셔너리 종류

접두어	의미
DBA_XXXX	데이터베이스 관리자만 접근 가능한 객체 둥의 정보 조회(DBA는 모두 접근 가능)
ALL-XXXX	자신 계정 소유 또는 권한을 부여 받은 객체 등에 관한 정비 ㅊㅌㄹㄹ
USER-XXXX	자신의 계정이 소한 객등에 관한 정ㅂ저집[

-------------------------------------------------------------------------------------------------------------------

8. USER_ 데이터 딕셔너리

 -> 자신의 계정이 소유한 객체 등에 관한 정보를 조회합니다.

-------------------------------------------------------------------------------------------------------------------

/*

USER_TABLES 데이터 딕셔너리 뷰 살피기

*/

--1. DESC 명령어로 데이터 딕셔너리 뷰 USER_TABLES의 구조를 살펴봅시다.

DESC USER_TABLES;

--2. USER_TABLES 데이터 딕셔너리 뷰는 현재 접속한 사용자 계정이 소유한 모든 테이블 정보를 조회 할 수 있는 뷰이기에 현재 사용자가 누구인지를 살펴봅시다.

show user;

--3. 데이터 딕셔너리 USER_TABLES의 구조를 살펴보면 무수히 많은 컬럼으로 구성되었음을 알 수 있습니다. 이중에서 테이블의 이름을 알려주는 

--TABLE_NAME 컬럼의 내용을 살펴봅시다. 현재 사용자 계정이 SCOTT 이므로 SCOTT이 사용가능한 테이블의 이름만 알 수 있습니다.

SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME desc;

-------------------------------------------------------------------------------------------------------------------

9. ALL_데이터딕셔너리

 -> 사용자 계정이 소유한 객체는 자신의 소유이므로 당연히 접근이 가능하다.

 -> 그러나 만일 자신의 계정이 아닌 다른계정 소유의 테이블이나 시퀀스 등은 어떻게 할까?

 -> 오라클에서는 타계정의 객체는 원칙적으로 접근 불가능합니다.

 -> 하지만 그 객체의 소유자가 접근할 수 있도록 권한을 부여하면 타 계정의 객체에도 접근이 가능하다.

 -> ALL_ 데이터 딕셔너리 뷰는 현재 게정이 접근 가능한 객체, 즉 자신 계정의 소유이거나 접근 권한을 부여 받는 타계정의 객체 등을 조회 할 수 있는 데이터 딕셔너리 뷰입니다.

 -> 현재 계정이 접근 가능한 테이블의 정보 조회하는 뷰이다.

-------------------------------------------------------------------------------------------------------------------

/*

ALL_TABLES 데이터 딕셔너리 뷰 살피기

*/

--1. DESC 명령어로 데이터 딕셔너리 뷰 ALL_TABLES의 구조를 살펴보자.

DESC ALL_TABLEs;

--2. 많은 값들 중 OWNER, TABLE_NAME 컬럼값만 봐보자

select OWNER,TABLE_NAME FROM ALL_TABLES;

-------------------------------------------------------------------------------------------------------------------

10. DBA_데이터 딕셔너리 뷰

 -> DBA_ 데이터 딕셔너리는DBA가 접근 가능한 객체 등을 조회할 수 있는 뷰이다.

 -> 앞서도 언급했지만 DBA가 접근 불가능한 정보는 없기에 데이터베이스에 있는 모든 객체 등의 의미라 할 수 있다.

 -> USER_와 ALL_와 달리 DBA_데이터 딕셔너리뷰는 DBA 시스템 권한을 가진 사용자만 접근 할 수 있다.

-------------------------------------------------------------------------------------------------------------------

/*

DBA_TABLES 데이터 딕셔너리 뷰 살피기

*/

--1. 다음은 테이블 정보를 살펴보기 위해서 DBA_TABLES 데이터 딕셔너리 뷰의 내용을 조회해 봅시다.

SELECT TABLE_NAME, OWNER FROM DBA_TABLES;

--2. DBA 권한을 가진 SYSTEM 계정으로 접속하여 다시한번 DBA_TABLES 데이터 딕셔너리 뷰의 내용을 조회해보자. 사용자 계정과 암호를 소문자로 입력해야 인식한다.

conn system/admin

select TABLE_NAME, OWNER FROM DBA_TABLES;