1.테이블생성
1)생성권한이 존재해야 함
conn scott/tiger

select * from user_sys_privs;
select * from role_sys_privs;

2)여유공간이 있어야 함.
select tablespace_name,sum(bytes) 
from user_free_space
group by tablespace_name;

conn /as sysdba
select tablespace_name,file_name
from dba_data_files;

** select table_name from dictionary
   where table_name like '%PRIV%';

3)새로운 테이블스페이스를 생성해서 그곳에 테이블을 생성

conn /as sysdba
create tablespace newtbs
datafile 'D:\oraclexe\oradata\XE\newtbs01.dbf' size 500k;
==> 물리적인 파일이 생성됨을 확인
select * from dba_tablespaces
where tablespace_name='NEWTBS';   ==>테이블스페이스의 기본속성 확인

select * from dba_data_files
where tablespace_name='NEWTBS';  ==>TS를 구성하는 파일속성 확인

select * from user_free_space
where tablespace_name='NEWTBS';   ==>여유공간 확인

** 새로운 테이블을 생성 
이름 :employees  (scott계정 소유)
컬럼 :empno   number(2)      primary key,
        ename   varchar2(20)  unique,
        sal	      number(10)    600~ 9000사이의 값만 허용,
        job	      varchar2(30)   not null,
        deptno  number(2)      foreign key로서 dept테이블의 deptno컬럼의 값을 참조할수 있도록 설정.

CREATE TABLE scott.employees
(empno NUMBER(2) CONSTRAINT employees_empno_pk PRIMARY KEY,
 ename	VARCHAR2(20) CONSTRAINT employees_ename_uk UNIQUE,
 sal 	NUMBER(10) CONSTRAINT employees_sal_ck CHECK(sal between 600 and 9000),
 job	VARCHAR2(30) CONSTRAINT employees_job_nn NOT NULL,
 deptno	NUMBER(2) CONSTRAINT employees_deptno_fk REFERENCES scott.dept(deptno))
/

select * from dba_tables
where table_name='EMPLOYEES' and owner='SCOTT';

select * from dba_users
where username='SCOTT';

drop table scott.employees;

CREATE TABLE scott.employees
(empno NUMBER(2) CONSTRAINT employees_empno_pk PRIMARY KEY,
 ename	VARCHAR2(20) CONSTRAINT employees_ename_uk UNIQUE,
 sal 	NUMBER(10) CONSTRAINT employees_sal_ck CHECK(sal between 600 and 9000),
 job	VARCHAR2(30) CONSTRAINT employees_job_nn NOT NULL,
 deptno	NUMBER(2) CONSTRAINT employees_deptno_fk REFERENCES scott.dept(deptno))
TABLESPACE newtbs;



CREATE TABLE scott.employees
(empno NUMBER(2) ,
 ename	VARCHAR2(20) ,
 sal 	NUMBER(10) CONSTRAINT employees_sal_ck CHECK(sal between 600 and 9000),
 job	VARCHAR2(30) CONSTRAINT employees_job_nn NOT NULL,
 deptno	NUMBER(2) ,
CONSTRAINT employees_empno_pk PRIMARY KEY(empno),
CONSTRAINT employees_ename_uk UNIQUE(ename),
CONSTRAINT employees_deptno_fk FOREIGN KEY (deptno) REFERENCES scott.dept(deptno))




INSERT INTO scott.employees(empno,ename,sal,job,deptno)
VALUES(1,'A',500,'ANALYST',10);   ==>?

INSERT INTO scott.employees(empno,ename,sal,job,deptno)
VALUES(1,'A',600,'ANALYST',60);    ==>?

INSERT INTO scott.employees(empno,ename,sal,job,deptno)
VALUES(1,'A',600,'ANALYST',10);

ALTER TABLE scott.employees
DISABLE PRIMARY KEY;

ALTER TABLE scott.employees
DISABLE unique(ename);

ALTER TABLE scott.employees
DISABLE CONSTRAINT employees_sal_ck;

ALTER TABLE scott.employees
DISABLE CONSTRAINT employees_job_nn;

ALTER TABLE scott.employees
DISABLE CONSTRAINT employees_deptno_fk;

INSERT INTO scott.employees(empno,ename,sal,job,deptno)
VALUES(1,'A',600,'ANALYST',10);

INSERT INTO scott.employees
SELECT * from scott.employees;    
/
/
/  ==>여러번 수행(에러가 날때까지..에러는 왜?)

select * from user_free_space;

** TS의 공간을 확장
1)기존의 파일사이즈를 증가
 ALTER DATABASE DATAFILE 'D:\oraclexe\oradata\XE\newtbs01.dbf' resize 1M;

2)새로운 파일을 추가
ALTER TABLESPACE NEWTBS 
ADD DATAFILE 'D:\oraclexe\oradata\XE\newtbs02.dbf' size 1M autoextend on;

INSERT INTO scott.employees
SELECT * from scott.employees;











** 생성된 테이블의 정보 확인
1.생성된 테이블들의 이름을 확인
   CONN scott/tiger
   SELECT * FROM tab;
   SELECT table_name FROM user_tables;
   SELECT object_name FROM user_objects
   WHERE object_type='TABLE';

2.employees테이블의 구조 확인(컬럼명,데이터타입)
  desc employees

3.employees테이블에 설정된 제약들 확인
select a.table_name,b.column_name,a.constraint_name,a.constraint_type,a.search_condition,a.status
from user_constraints a,user_cons_columns b
where a.constraint_name=b.constraint_name and a.table_name='EMPLOYEES';

save cons

ed cons

@cons

** CTAS를 이용한 테이블생성
: 기존의 테이블의 구조 및 데이터를 이용하여 새로운 테이블을 생성,일종의 데이터 복사
CREATE TABLE 새로운테이블이름
AS SELECT ~      ;

예) EMP테이블에서 10번 부서원들의 사번,이름,업무,급여,입사일자를 포함하는 새로운 EMP_10이라는 테이블을 생성하시오.
create table emp_10
as select empno,ename,job,sal,hiredate
	from emp
	where deptno=10;
select * from tab;
desc emp_10
select * from emp_10;

문제1) EMP 테이블에서 부서별로 부서번호, 인원수,평균 급여,급여의 합,최소 급여,최대 급여를 포함하는 EMP_DEPTNO 테이블을 생성하여라.
create table emp_deptno(deptno,cnt,avgsal,sumsal,minsal,maxsal)
as select deptno,count(*),avg(sal),sum(sal),min(sal),max(sal)
   from emp
   group by deptno;

** 컬럼,테이블명 naming rule
1.영어,숫자,한글,_,#,$ 가능
2.예약어 불가능(v$reserved_words)
3.시작은 영어,한글로만 시작
4.1~ 30자 이내

문제2) EMP 테이블에서 사원번호,이름,업무,입사일자,부서번호만 포함하는 EMP_TEMP 테이블을 생성하는데 자료는 포함하지 않고 구조만 생성하여라.
create table emp_temp
as select empno,ename,job,hiredate,deptno
    from emp
    where 1=2;

** 테이블의 구조 수정
1)테이블 이름 수정
	RENAME a TO b;
2)컬럼 이름 수정
	ALTER TABLE 테이블명 RENAME a to b;
3)컬럼의 데이터 타입 및 최대길이 변경
	ALTER TABLE 테이블명
	MODIFY 컬럼명 datatype(최대길이);
4)컬럼의 추가 삭제
	ALTER TABLE 테이블명
	ADD 컬럼명 datatype(최대길이);

	ALTER TABLE 테이블명
	DROP COLUMN 컬럼명; (즉시 삭제) 또는

	ALTER TABLE 테이블명
	SET UNUSED COLUMN 컬럼명; ==>이후 
	ALTER TABLE 테이블명
	DROP UNUSED COLUMNS;
5)제약조건의 추가 및 삭제
	ALTER TABLE 테이블명
	ADD CONSTRAINT 제약명 제약유형(컬럼명);

	ALTER TABLE 테이블명
	DROP PRIMARY KEY/UNIQUE(컬럼명)/CONSTRAINT 제약명              CASCADE;   ==>cascade는 pk제약을 삭제하려고 하는데 pk제약을 참조하는 fk제약이 만약이 존재한다면 이 옵션을 사용함으로서 pk제약이란 fk제약이 함께 삭제

6)제약의 비활성화 및 활성화  ==>다량의 BATCH업무의 성능향상을 위해 비활성화
ALTER TABLE 테이블명
DISABLE PRIMARY KEY/UNIQUE(컬럼명)/CONSTRAINT 제약명 CASCADE;

ALTER TABLE 테이블명
enable PRIMARY KEY/UNIQUE(컬럼명)/CONSTRAINT 제약명;

** 테이블 삭제
1)DROP TABLE 테이블명 CASCADE CONSTRAINTS;

- DEPT테이블을 삭제
DROP TABLE dept CASCADE CONSTRAINTS;

 - oracle 10g에서는 drop table을 시키면 테이블을 recyclebin으로 일단 저장후 이후에 삭제시킴
show recyclebin

flashback table dept to before drop;

2)TRUNCATE TABLE 테이블명;
 truncate table employees;

 select * from employees;
 desc employees 

** 결론)
drop table은 완전삭제,truncate table은 모든 데이터를 완전삭제,구조는 존재함, 저장공간은 해제됨.



** 테이블에 데이터를 입력,수정,삭제 작업,DML(Date Manipulation Language)  ==>DML작업은 트랜잭션을 구성

1) 입력
INSERT INTO 테이블명(컬럼1,컬럼2...)
VALUES(값1,값2....);
1.emp테이블에 새로운 사원의 정보를 입력하시오.
(사번 : 8000,이름:홍길동,업무:MANAGER,입사일:현재날짜및 시간,
 급여:3000,부서코드:10,기타:null)

INSERT INTO emp(empno,ename,job,hiredate,sal,deptno)
VALUES(8000,'홍길동','MANAGER',SYSDATE,3000,10);

2.emp테이블에 새로운 사원의 정보를 입력하시오.
(사번 : 8001,이름:김길동,업무:CLERK,입사일:2001년도 8월 1일 10시,
 급여:2000,부서코드:20,기타:null)
INSERT INTO emp(empno,ename,job,hiredate,sal,deptno)
VALUES(8001,'김길동','CLERK',TO_DATE('2001/08/01:10','RRRR/MM/DD:HH24'),2000,20);

SELECT empno,ename,to_char(hiredate,'rrrr/mm/dd:hh24:mi:ss')
FROM emp;

2)수정
UPDATE 테이블명
SET 컬럼명1 = 값1,컬럼명2=값2...
(WHERE 조건절);

3)삭제
DELETE (FROM) 테이블명
(WHERE 조건절);