2. 테이블 생성,변경,삭제 / 자료형 / 제약조건

사용자 계정 생성하기 

-- 생성
 CREATE USER user1 IDENTIFIED BY 1234;
 
-- 권한부여 
 GRANT CONNECT, RESOURCE TO user1;
 
 -- CONNECT: 데이터베이스 접근 권한 
 -- RESOURCE: 특정 객체(CREATE, SELECT, INSERT 등)를 생성하고 조작할 권한
 
 -- user1의 권한 조회 
 SELECT * FROM dba_role_privs WHERE upper(grantee) = 'USER1';

 

테이블 생성 및 값 입력(CREATE, INSERT) 

각 컬럼들을 괄호로 묶어 지정하며 반드시 데이터타입도 지정해야 한다. 

테이블 내 컬럼은 같은 이름을 가질 수 없다

-- 형식: CREATE TABLE 테이블이름(속성1  데이터형, 속성2  데이터형...);
 CREATE TABLE newbook
(bookid NUMBER,
 bookname VARCHAR2(20));
 
 
 -- 형식: INSERT INTO 테이블이름(속성1, 속성2...) VALUES(값1, 값2..);
 --              INTO 테이블이름(속성..)은 모든 속성에 값을 줄 때는 생략이 가능하다. 
INSERT INTO newbook (bookid, bookname) VALUES(10, 'Harry Poter');

 

데이터형

데이터형		설명
숫자형	NUMBER	1. 숫자형 데이터 저장 2. 음수, 0, 양수 저장 가능 3. 연산이 필요한 컬럼에 지정 4. 가변길이 데이터타입 5. 소수점이 지정되지 않았는데 입력 혹은 지정될 경우, 지정된 자릿수에서 반올림
문자열	LONG	가변길이 데이터 타입(2GB까지 가능)
	CAHR(size)	1. 고정적(입력된 크기와 상관 없이 저장공간의 크기 명시)  2 .size: byte(데이터크기) char(문자 개수)로 지정 -- 기본은 byte
	VARCHAR2(size)	1. 가변적(입력된 크기만큼 공간 할당, 최대 크기 명시)  2. size: byte(데이터크기) char(문자 개수)로 지정 -- 기본은 byte
날짜	DATE	1. 날짜 및 시간 저장(세기, 년, 월, 일, 시간, 분, 초)  2. 날짜와 시간의 연산이 빈번한 경우 사용   * SYSDATE: 현재 날짜와 시간

 

테이블 / 컬럼의 이름 규칙

1. 문자로 시작해야 함

2. 1~30자까지 가능

3. A~Z까지의 대/소문자, 0~9까지의 숫자, 특수기호는 [ _ $ # ] 만 가능

4. 예약어, 다른 객체명과 중복 불가 

5. 공백 불가(공백은 _로 대체) 

 

 

제약조건

- 테이블에 제약조건을 걸어 부적절한 데이터(중복값 등)가 입력되는 것을 막음

- 테이블의 무결성 유지 

- CREATE TABLE에서 지정할 수도 있고, 테이블 생성 후 ALTER TABLE ~ ADD CONTSRAINTS 명령어로 추가 가능

 

1. 컬럼 레벨에서 작성

CREATE TABLE emp_table(
 empno NUMER PRIMARY KEY​,
 empname VARCHAR(20));

CREATE TABLE emp_table(
 empno NUMER CONSTRAINT emp_table_empno_pk PRIMARY KEY,  -- 제약조건 이름 설정(emp~pk)
 empname VARCHAR(20));
 
 -- 만약, 제약조건의 이름을 지정하지 않으면 자동으로 sys_xxx로 저장됨

 

2. 테이블 레벨에서 작성

 * 테이블 레벨에서는 NOT NULL은 작성할 수 없음 -> 컬럼레벨에서 작성하거나 생성 이후 ALTER 사용하여 추가 

CREATE TABLE emp_table(
 empno NUMER,
 empname VARCHAR(20),​
 job VARCHAR(20),
 deptno NUMER(2),
 PRIMARY KEY(empno),
 UNIQUE(job),
 FOREIGM KEY(deptno) REFERNCES dept(deptno));

​

CREATE TABLE emp_table(
 empno NUMER,
 empname VARCHAR(20),​
 job VARCHAR(20),
 deptno NUMER(2),
 CONSTRAINTS emp_table_empno_pk PRIMARY KEY(empno),
 CONSTRAINTS emp_table_job_uq UNIQUE(job),
 CONSTRAINTS emp_table_deptno_fk FOREIGM KEY(deptno) REFERNCES dept(deptno));
 
 -- 만약, 제약조건의 이름을 지정하지 않으면 자동으로 sys_xxx로 저장됨

 

제약조건의 유형

이름	설명
NOT NULL	해당 컬럼에  NULL 값이 입력되지 않도록 함
UNIQUE	데이터의 유일성 보장, 중복 데이터가 저장될 수 없음, 자동 인덱슷 ㅐㅇ성
PRIMARY KEY	1. 기본키(UNIQUE, NOT NULL 포함)  2. 행을 대표하는 컬럼으로 외래키들이 참조할 수 있음 3. 자동으로 인덱스 생성(기본키 제약조건의 이름과 같은 이름 가짐)
CHECK	컬럼 값을 특정 범위로 제한
FOREGIN KEY	1. 기본키를 참조하는 컬럼 혹은 컬럼의 집합 2. 참조하고 있는 기본키의 데이터타입과 일치해야 함 3. 외래키에 참ㅈ되고 있는 기본키는 삭제할 수 없는 제약이 걸림

 

테이블 구조 변경(ALTER) 

테이블 구조를 변경하기 위한 DDL로, 컬럼과 제약조건을 ADD, RENAME, MODIFY, DROP 할 수 있다. 

 

1. ALTER TALBE ~ ADD

 : 기존 테이블에 새로운 컬럼 추가(위치 지정 가능) 

-- 형식: ALTER TABLE 테이블명 ADD(컬럼이름 데이터형);
ALTER TABLE newbook ADD('price', NUMBER);

* 제약조건 추가하기 

 : 제약조건의 이름을 없이 생성한다면 CONSTRAINT 키워드는 생성 가능( sys_xxx로 자동 생성) 

-- 형식: ALTER TABLE 테이블명 RENAME COLUMN 前컬럼명 TO 後컬럼명);
ALTER TABLE newbook RENAME COLUMN bookid TO bookno;

 

2. ALTER TABLE  ~ RENAME

 : 컬럼명 변경 시 사용 

-- 형식: ALTER TABLE 테이블명 RENAME COLUMN 前컬럼명 TO 後컬럼명);
ALTER TABLE newbook RENAME COLUMN bookid TO bookno;

* 제약조건 이름 변경하기 

-- 형식: ALTER TABLE 테이블명 RENAME CONSTRAINT 前제약조건이름 TO 後제약조건이름;
ALTER TABLE newbook RENAME CONSTRAINT newbook_bookid_pk TO nbook_bookid_pk;

 

3. ALTER TABLE ~ MODIFY

 : 테이블에 이미 존재하는 컬럼 수정(해당 컬럼 데이터 유무에 따라 다르게 동작) 

	자료 無	자료 有
데이터 타입 변경	O	X(조건부)
컬럼 크기 변경	O	작은 크기로 불가

* 자료가 있는 경우 CHAR, VARCHAR2의 경우 변경이 가능하나 데이터 손실이 없도록 최댓값을 고려하여 변경 

-- 형식: ALTER TABLE 테이블명 MODIFY (컬럼명 데이터형(크기));
ALTER TABLE newbook MODIFTY(bookname, VARCHAR(25));

* 제약조건 변경 

 : NOT NULL만 가능하고 나머지는 삭제 후 다시 생성해야 함

-- 형식: ALTER TABLE 테이블명 MODIFY 컬럼명 CONSTRAINTS 제약조건이름 NOT NULL;
ALTER TABLE emp_table MODIFY empno CONSTRAINT emptable_empno_nn NOT NULL;

 

4. ALTER TABLE ~ DROP

 : 테이블에 존재하는 컬럼 삭제 

-- 형식: ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
ALTER TABLE newbook DROP COLUMN price;

* 제약조건 삭제

-- 형식: 기본적으로 제약조건명을 제시하여 삭제한다. 
--       ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름;
ALTER TABLE newbook DROP CONSTRAINT newbook_bookid_pk;

-- 혹은
ALTER TABLE newbook DROP PRIMARY KEY;

 

5. ALTER TABLE ~ SET UNUSED 

 : 컬럼을 삭제하지 않고 제한(비활성화)

   컬럼을 무작정 삭제하는 것은 위험하며 데이터가 많은 경우엔 삭제하는 데 너무 많은 시간이 소요된다. 

   단, 이 구문을 사용하는 경우에도 데이터는 복구할 수 없다. 

-- 형식: ALTER TABLE 테이블명 SET UNUSED 컬럼명;
ALTER TABLE newbook SET UNUSED price;

 

6. ALTER TABLE ~ RENAME TO 

-- 형식: ALTER TABLE 테이블명 RENAME TO 새 테이블명;
ALTER TABLE newbook RENAME TO nesbooks;

 

테이블의 데이터를 삭제하는 방법

이름	설명	Rollback
DELETE	1. DML  2. 데이터는 지워지나, 테이블 용량이 줄어들지는 않음 3. TRRIGER(자동 잠금 함수)가 있다면 각 행이 삭제될 때마다 실행  4. 이전에 할당되었던 영역은 삭제되어 빈 table, cluster에 그대로 남음 (형식) DELETE FROM 테이블명 WHERE 조건; (예시) DELETE FORM newbook WHERE bookid = 1;	O
TRUNCATE	1. DDL 2. 사용하던 테이블의 모든 행을 제거(테이블의 기본 구조는 유지함) 3. 데이터가 지워지고, 테이블 용량도 줄어듦 4. table과 관련된 구조와 권한에는 영향을 주지 않음 5. table에 걸려있는 TRIGGER는 실행되지 않음 (형식 ) TRUNCATE TABLE 테이블명;	X
DROP	1. DDL 2. 테이블 자체가 삭제(모든 관련된 index, constraint, trigger 등 삭제)	X