[Basic] SQL 테이블 관리

1 테이블 생성

1-1 그냥 생성

create [global temporary] table [schema] address(이름)
(id number(3), (열 데이터타입) 
name varchar2(50) default 'No Name', [디폴트 값] 
addr varchar2(100), [무결성 제약 조건]
phone varchar2(30), 
email varchar2(100));

• global temporary: 임시 테이블 만드는 키워드.

• schema: 데이터베이스 사용자 계정과 같은 의미

• default expression: 입력값 생략될 때 Null 대신 입력되는 기본 값 지정. 위의 경우 'No Name'이 들어감.

  • 칼럼, 의사칼럼(nextcal, currval))은 사용 불가능.
  • 리터럴, 표현식, sql 함수, sysdate, user 사용 가능

• 무결성 제약 조건: 추후에 알려줌

• DESC 테이블이름 테이블 구조 확인. 칼럼 이름, 데이터 타임, 크기, NOTNULL 무결성 제약조건

1-2 다른 테이블 들고오기

• 다른 테이블의 구조 + 값까지 가져와서 초기 데이터로 삽입

CREATE TABLE addr_second(id, name, addr, phone, e_mail)
AS SELECT id, name, addr, phone, mail 
FROM address;

• 기존 테이블 구조만 복사 => where 절로 항상 거짓인 걸 추가

CREATE TABLE addr_second(id, name, addr, phone, e_mail)
AS SELECT id, name, addr, phone, mail 
FROM address
WHERE 0=1;

2 테이블 구조 변경

2-1 칼럼 추가

ALTER TABLE 테이블명
ADD (comments VARCHAR2(200) DEFAULT 'No Comment');

• 하면 이 테이블에 comments라는 이름의 컬럼이 VARCHAR2(200)라는 datatyp에 'No Comment'라는 디폴트값으로 추가.

2-2 칼럼 삭제

ALTER TABLE 테이블명
DROP COLUMN comments(칼럼 이름);

• 2개 이상의 칼럼이 존재하는 테이블에서만 가능.

2-3 칼럼 변경

ALTER TABLE 테이블명
MODIFY phone VARCHAR2(50);

• 칼럼의 타입, 크기, 기본 값(deffault) 변경 가능
• 여기서 기존 데이터가 존재하는 경우 => 타입 변경은 CHAR, VARCHAR2만 가능. 변경한 경우의 칼럼 크기가 저장된 데이터 크기보다 같거나 큰 경우만(손실은 안됨). / 숫자 타입은 정밀도 증가 가능.
• 기존 데이터 없으면 크기 변경 자유로움.

2-4 테이블 이름 변경

RENAME 기존이름 TO 새이름

• DDL(Data definition Language). 데이터 정의어. 객체 이름 변경.

2-5 테이블 삭제

2-5-1 DROP

DROP TABLE [schema.] 이름 [cascade constraints]

• 테이블 데이터 모두 삭제(delete의 경우 데이터만 삭제).
• 데이터, 저장공간, 테이블 정의까지 모두 삭제.
  칼럼에 대해 생성된 인덱스,CONSTRAINT, TRIGGER도 함께 삭제.
• DDL로 롤백 불가능.
• primary key나 unique key를 참조하고 있는 경우 불가능. but cascade constraints옵션 사용하면 무결성 제약조건 동시 삭제.

2-5-2 TRUNCATE

TRUNCATET TABLE [schema.] 이름

• 테이블 구조 유지하고, 데이터와 할당된 공간만 삭제.
  데이터, 저장공간은 삭제, 테이블 정의 유지.
• 테이블에 생성된 제약조건과 연관된 인덱스, 뷰, 동의어는 유지. 권한에 영향 주지 않는다.
• TRIGGER 실행 안된다.
• DDL 문으로 롤백 불가능.

2-5-3 DELETE

• 행을 삭제하는 것. 행이 많으면 자원 많이 소모되고, TRIGGER 걸려있으면 각 행 삭제될 때마다 실행.
• 데이터는 삭제되지만, 저장공간, 테이블 정의 모두 유지된다.
  이전에 할당되어 있던 영역 삭제되어 빈 TABLE이나 CLUSTER에 그대로 남는다.

2-6 주석 추가

• 테이블에 주석 추가

COMMENT ON TABLE 테이블이름
	IS '내용'

• 칼럼에 주석 추가

COMMENT ON COLUMN 테이블.칼럼이름
	IS '내용'

• 삭제할 때는 => ''로 덮어쓰기

• 테이블 주석 확인

SELECT table_name, comments
FROM USER_tab_comments
WHERE table_name = '테이블이름'

• 칼럼에 주석 확인 => 주어진 테이블의 각 칼럼에 대해 주석을 가져온다

SELECT column_name, comments
FROM USER_col_comments
WHERE column_name = '테이블이름'

3 데이터 사전(date dictionary)

3-1 개요

• 사용자와 데이터베이스 자원 효율 관리하기 위한 다양한 정보를 저장하는 시스템 테이블의 집합. => 실무에서는 테이블, 칼럼, 뷰 등과 같은 정보를 조회하는 데 사용.
• 포함되는 관리 정보
  • 데이터베이스의 물리적 구조, 객체의 논리적 구조
  • 오라클 사용자 이름, 스키마 객체 이름
  • 사용자에 부여된 접근 권한과 롤
  • 무결성 제약조건에 대한 정보
  • 칼럼별 default값
  • 스키마 객체에 할당된 공간의 이름, 사용 중인 공간 크기 정보
  • 객체 접근 및 갱신에 대한 감사 정보
  • 데이터 베이스 이름, 버전, 생성날짜, 시작모드, 인스턴트 이름 정보
• 종류
  • USER_: 객체의 소유자만 접근 가능한 데이터 사전 뷰
  • ALL_: 데이터 베이스 전체 사용자 중, 자기 소유 또는 권한을 부여받은 객체만 접근 가능한 데이터 사전 뷰
  • DBA_: 관리자만 접근 가능한 데이터 사전 뷰

3-1-1 USER_

3-1-1-1 USER_OBJECT

• 사용자가 소유한 모든 객체에 대한 정보 제공. 객체의 이름, 타입, 소유자 등 정보.

SELECT object_name, object_type, created
FROM user_objects;
WHERE objects_name LIKE 'ADDR%' and object_type = 'TABLE';

• 객체의 종류가 table이고 이름이 ADDR로 시작하는 객체의 이름, 종류, 생성날짜

3-1-1-2 USER_TABLE

• 사용자가 소유한 모든 테이블에 대한 정보 제공. 테이블 이름, 속한 열의 수, 크기 등

SELECT table_name, tablespace_name, min_extents, max_extents
FROM user_table;
WHERE table_name LIKE 'ADDR%';

ADDR로 시작하는 이름의 테이블의, 테이블이름, 테이블스페이스이름, 최소 확장영역 수, 최대 확장영역 수 출력

3-1-1-3 USER_CATALOG

• 사용자가 소유한 카탈로그 객체의 정보 제공. 카탈로그 객체는 데이터 딕셔너리 정보를 나타낸다. => 테이블, 뷰, 칼럼, 제약 조건 등 데이터베이스 메타 데이터

DESC user_catalog;

SELECT * 
FROM user_catalog;

• Table_name, Table_type으로 객체 이름, 객체 종류 등이 뜬다

3-1-2 ALL_

SELECT owner, table_name
FROM all_tables;

데이터 베이스 전체에서 자기 소유 혹은 접근 가능 권한 부여받은 테이블에 대한 정보와, 그 테이블의 owner를 조회

3-1-3 DBA_

SELECT owner, table_name
FROM dba_tables;

시스템 관리 관련 부. DBA나 SELECT ANY TABLE 시스템 권한 가진 사용자.

• 데이터 베이스 관리에 사용되는 종류
  • dictionary, dict_columns: 데이터 사전 테이블, 뷰 및 칼럼 정보
  • dba_tables, dba_objects, dba_tab_columns, dba_constraints: 테이블, 제약조건, 칼럼, 사용자 객체 관련 정보
  • dba_users, dba_sys_privs, dba_roles: 사용자 권한과 롤에 관한 정보
  • dba_extents, dba_free_space, dbasegments: 데이터 베이스 객체에 대한 공간 할당 정보
  • dba_rollback_segs, dba_data_files, dba_tablespaces: 데이터 베이스 내부 공간의 구조 정보
  • dba_audit_trail, dba_audit_object, dba_obj_audit_opts: 감사 관련 정보