1. cmd 들어가기

2. sqlplus

3. (기본이 SYSTEM) (기본 설정암호)

​

4. create user 'user이름' identified by 'user비밀번호'; //유저 생성

5. alter user 'user이름' account unlock; //만들었을 때 user account에 lock이 걸려있을지 모르므로 unlock시켜줌

6. grant resource, connect to 'user이름'

grant dba to 'user이름' //권한 설정

이렇게만 하면 db에 user를 만들 수 있음

// 계정생성
create user [USERNAME] identified by [PASSWORD];
// 계정비밀번호 변경
ALTER USER [USERNAME] IDENTIFIED BY [NEW PASSWORD];

// 권한할당

GRANT CREATE 권한명 TO 계정;
----------------------------------------------------
시스템 권한 종류
create user : 데이터 베이스 유저 생성 권한
select any table : 모든 유저의 테이블 조회 권한
create any table : 모든 유저의 테이블 생성 권한
create session : 테이터베이스 접속 권한
create table : 테이블 생성 권한
create view : 뷰 생성 권한
create proced user : 프로시저 생성 권한
create sequence : 시퀀스 생성 권한
sysdba : 테이버베이스를 관리하는 최고 권한
sysoper : 테이터베이스를 관리하는 권한
----------------------------------------------------

// 권한제거
revoke 권한명 (시스템 명령 또는 SQL 명령) ON 테이블명 FROM 계정;

// 계정삭제
DROP USER 계정명 CASCADE;

// 현재연결된계정보기
show user

사용자명 입력: system
비밀번호 입력:

다음에 접속됨:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user [hyoseon] identified by [1234];
create user [hyoseon] identified by [1234]
            *
1행에 오류:
ORA-01935: 누락된 사용자 혹은 롤 이름


SQL> create user hyoseon identified by 1234;

사용자가 생성되었습니다.

SQL> grant create session to hyoseon
  2  ;

grant create session to 유저이름;

select * from ALL_users;    // 모든 계정에 대한 정보 확인
select * from DBA_users;    // 모든 계정에 대한 정보 확인
select username from ALL_users;    // 모든 계정에 대한 이름 확인
select username from DBA_users;    // 모든 계정에 대한 이름 확인

사용자 계정을 만들어서 사용할 때 기본 세팅 명령어

-- 사용자 계정 만들기
CREATE user 계정명 IDENTIFIED by 비밀번호;
-- 권한 부여
grant create session, create table, create sequence, create view to 계정명;
-- 사용자 계정에 테이블 공간 설정
alter user 계정명 default tablespace users;
-- 테이블 공간에 쿼터 할당
alter user 계정명 quota unlimited on users;

1. 시퀀스의 현재 값을 확인

SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TB_ZZTRACE_SQ01';

2. 시퀀스의 INCREMENT 를 현재 값만큼 빼도록 설정 (아래는 현재값이 999999 일 경우)

ALTER SEQUENCE TB_ZZTRACE_SQ01 INCREMENT BY -999999;

3. 시퀀스에서 다음 값을 가져 온다

SELECT TB_ZZTRACE_SQ01.NEXTVAL FROM DUAL;

4. 현재 값을 확인 해보면 -999999 만큼 증가 했다

SELECT TB_ZZTRACE_SQ01.CURRVAL FROM DUAL;

5. 시퀀스의 INCREMENT 를 1로 설정 한다

ALTER SEQUENCE TB_ZZTRACE_SQ01 INCREMENT BY 1;

6. 시퀀스가 1부터 다시 시작 한다.

create table phoneInfo_basic (
        idx             number(6)       primary key,
        fr_name         varchar2(20)    not null,
        fr_phonenumber  varchar2(20)    not null,
        fr_email        varchar2(20)    default '-',
        fr_address      varchar2(20)    default '-',
        fr_regdate      DATE            default sysdate
);

create table phoneInfo_univ (
        idx             number(6)       primary key,
        fr_u_major      varchar2(20)    default 'N',
        fr_u_year       number(1)       check (5>=fr_u_year AND fr_u_year>=1),
        fr_ref          number(7)       references phoneinfo_basic(idx)
);


create table phoneinfo_com(
        idx             number(6)       primary key,
        fr_c_company    varchar2(20)    default 'N',
        fr_ref          number(6)       references phoneinfo_basic(idx)
);



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

create table fr_type(
    friendty      varchar2(5)    unique
);

insert into fr_type values ('univ');
insert into fr_type values ('com');
insert into fr_type values ('cafe');


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

create table phonebook(
    pidx            number(6)        primary key,
    friendtype      varchar2(5)      references fr_type(friendty)
);

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

인덱스의 장점

- 검색 속도가 빨라진다.
- 시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상시킨다.

인덱스의 단점
- 인덱스를 위한 추가적인 공간이 필요하다.
- 인덱스를 생성하는데 시간이 걸린다.
- 데이터의 변경 작업(INSERT/UPDATE/DELETE)이 자주 일어날 경우에는 오히려 성능이 저하된다.

Primary Key / unique

기본 키나 유일 키는 데이터 무결성을 확인하기 위해하기 위해서 수시로 데이터를 검색하기 때문에

빠른 조회를 목적으로 오라클에서 내부적으로 해당 컬럼에 인덱스를 자동으로 생성한다.

-- index : 검색을 빠르게 하기 위한 객체
-- create index index_name on targer_table_name (column_name)

-- index 확인을 하는 user_ind_columns
desc user_ind_columns;

select index_name, table_name, column_name
from user_ind_columns;
-- primary key 또는 unieq 속성은 자동으로 index가 생성이 된다.



insert into emp10 select * from emp10;
-- emp10 테이블에 500만개의 행을 만들어준 상태임
insert into emp10 (empno, ename) values (1111, 'COOL');

select empno, ename from emp10 where ename='COOL';  -- 2.765초

-- ename을 인덱스로 정의
create index index_emp10_ename
on emp10 (ename);
select empno, ename from emp10 where ename='COOL';  -- 1.26초로 단축

-- 인덱스 삭제
-- DROP INDEX index_name;

인덱스 확인방법

: 인덱스 확인

SELECT * FROM USER_INDEXES

WHERE TABLE_NAME='테이블명 ' ;

: 키가 되는 컬럼 알아보기

ELECT * FROM USER_IND_COLUMNS

WHERE TABLE_NAME='테이블명 ' ;

: 해당 인덱스의 컬럼만 알아보기

SELECT * FROM USER_IND_COLUMNS

WHERE INDEX_NAME='인덱스명';

create index index_test01 on test01(ename);
insert into test01 select * from test01;

--인덱스 확인 방법
SELECT * FROM USER_INDEXES
WHERE TABLE_NAME='TEST01';

-- 그 키가 되는 컬럼을 알아보는 방법
SELECT * FROM USER_IND_COLUMNS
WHERE TABLE_NAME='TEST01';

-- 해당 인덱스의 컬럼만 보고싶다면
SELECT * FROM USER_IND_COLUMNS
WHERE INDEX_NAME='INDEX_TEST01';

-- 내가 조회할땐 어쩐지 대문자만됐다;

출처 : coding-factory.tistory.com/419

인덱스(Index)란?

인덱스는 데이터베이스 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체이며 일종의 색인기술입니다. 테이블에 index를 생성하게 되면 index Table을 생성해 관리합니다. 인덱스는 테이블에 있는 하나이상의 컬럼으로 만들 수 있습니다. 가장 일반적인 B-tree 인덱스는 인덱스 키(인덱스로 만들 테이블의 컬럼 값)와 이 키에 해당하는 컬럼 값을 가진 테이블의 로우가 저장된 주소 값으로 구성됩니다.인덱스(Index) 사용 예시 

인덱스 생성

--문법
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)

--예제
CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 

--예제 컬럼 중복 X
CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 

위와같이 쿼리문을 작성하면 INDEX를 생성할 수 있습니다. UNIQUE 키워드를 붙이면 컬럼값에 중복값을 허용하지 않는다는 뜻입니다.

인덱스 조회

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';

인덱스를 생성하면 USER_INDEXES 시스템 뷰에서 조회할 수 있습니다.

방금 CUSTOMERS 테이블에 만들었던 EX_INDEX가 첫번째 ROW에 있군요.

인덱스 삭제

--문법
DROP INDEX [인덱스 명]
--예제
DROP INDEX EX_INDEX;

인덱스는 조회성능을 극대화하기 위해 만든 객체인데 너무 많이 만들면 insert, delete, update시에 부하가 발생해 전체적인 데이터베이스 성능을 저하합니다. 고로 안쓰는 인덱스는 삭제시키는것이 좋습니다.

▶인덱스(Index) 리빌드(Rebuild)사용 예시 

인덱스를 리빌드하는 이유

인덱스 파일은 생성 후 insert, update, delete등을 반복하다보면 성능이 저하됩니다. 생성된 인덱스는 트리구조를 가집니다. 삽입,수정,삭제등이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어집니다. 이러한 현상으로 인해 인덱스의 검색속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거치는것이 좋습니다.

인덱스 리빌드 할 대상 조회쿼리

SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL,
       DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,'?','Rebuild'),'Check') CNF
FROM   USER_INDEXES I
WHERE   I.BLEVEL > 4
ORDER BY I.BLEVEL DESC

해당쿼리는 index 트리의 깊이가 4이상인 index를 조회하는 쿼리입니다. 해당 쿼리문을 실행하여 검색되는 index가 있다면 리빌딩을 하는것이 좋습니다. 

인덱스 리빌드

--문법
ALTER INDEX [인덱스명] REBUILD;
--예제
ALTER INDEX EX_INDEX REBUILD;

위의 쿼리를 실행시키면 인덱스가 리빌드 됩니다. 이렇게 일일히 리빌드 시키기 귀찮으신 분들은 USER_INDEXES에 있는 인덱스를 조회하여 인덱스 리빌드 쿼리를 만들어 한번에 실행시키시면 간편합니다.

전체 인덱스 리빌드 쿼리문 만들기

SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD; 'FROM USER_INDEXES;

인덱스(Index)를 남발하지 말아야 하는 이유

개발을 진행할때에 대개 개발서버와 운영서버를 나누어서 관리합니다. 대부분 개발서버에서 개발을 할때에는 적은량의 데이터를 가지고 로직검사를 하며 로직검사에 통과한 코드들이 운영서버에 업데이트가 되죠. 하지만 개발서버에는 잘 동작하던 로직들이 운영서버의 많은량의 데이터들을 처리하다보면 성능이슈가 많이 발생합니다. 그 성능이슈의 주요원인은 바로 데이터베이스에 있습니다. 데이터베이스 관리자는 성능문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성입니다. 그렇게 하여 인덱스를 하나 만들었다고 합시다. 그리고 또 다른 SQL에서문에서 성능이슈가 발생하여 또 인덱스를 만들었다고 합시다. 이렇게 문제가 발생할때마다 인덱스를 생성하면서 인덱스가 쌓여가는것은 결코 좋지않습니다. 인덱스를 만드는것은 하나의 쿼리문을 빠르게는 만들 수 있지만 전체적인 데이터베이스의 성능 부하를 초래합니다. 그렇기에 인덱스를 생성하는것 보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야합니다. 인덱스생성은 꼭 마지막 수단으로 강구해야 할 문제입니다.

시퀀스는 테이블 내의 유일한 숫자를 자동으로 생성하는 자동 번호 발생기이므로

시퀀스를 기본 키로 사용하게 되면 사용자의 부담을 줄일 수 있다.

-- Sequence : 숫자 자동 생성기
-- 시작값, 증가값, 최대값, 최소값, 반환 여부.

-- dept 테이블의 deptno가 pk로 되어있다. [deptno = 10, 20, 30..]
-- detpno에 사용할 시퀀스를 생성해보자.
------------ 시퀀스 삭제 : drop sequence 시퀀스이름
drop sequence seq_dept_deptno;

-- 시퀀스 생성
create sequence seq_dept_deptno
MINVALUE 10 -- 최소값 10
MAXVALUE 90
start with 10 -- 10부터 시작
INCREMENT BY 10 -- 10씩 증가
;

-- emp 테이블에 사용할 수 있는 시퀀스 만들어보기
create sequence sqe_emp_empno
MINVALUE 0
start with 0
increment by 1

CURRVAL / NEXTVAL

-- sequence 객체로 숫자를 생성, 현재 숫자를 읽어오는 명령
-- 숫자생성 : nextval > 새로운 숫자를 생성하고 숫자를 반환
-- currval: 현재 숫자 반환

-- 현재 숫자를 확인
select seq_dept_deptno.nextval
from dual;


-- 현재 숫자
select seq_dept_deptno.currval
from dual;


insert into dept01 values (seq_dept_deptno.nextval , 'test', 'test');
-- 계속하면 (90이넘으면 / 현재 maxvalue 90임)
-- ORA-08004: 시퀀스 SEQ_DEPT_DEPTNO.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다.
select * from dept01;

- 뷰의 개념

* 뷰(View)는 한마디로 물리적인 테이블을 근거한 논리적인 가상 테이블이라고 정의할 수 있습니다. 
* 가상이란 단어는 실질적으로 데이터를 저장하고 있지 않는다.

* 사용자는 마치 테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있다.
* 뷰는 기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문입니다.
* 실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있도록 합니다.
* 사용자에게 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용하게 됩니다.

* 뷰는 데이터를 물리적으로 저장하고 있지 않는다.

- CREATE OR RELPACE VIEW

그냥 CREATE VIEW를 통해 만들어진 뷰의 구조를 바꾸려면 뷰를 삭제하고 다시 만들어야 되는 반면,

CREATE OR REPLACE VIEW는 새로운 뷰를 만들 수 있을 뿐만 아니라,

기존에 뷰가 존재하더라도 삭제하지 않고 새로운 구조의 뷰로 변경(REPLACE)할 수 있다.
그래서 대부분 뷰를 만들 때는 CREATE VIEW 대신 CREATE OR REPLACE VIEW를 사용한다.

View 테이블에 insert를 사용해서 추가를 하면,

기존 테이블에도 추가가 된다. 자바로 생각하면 부모테이블(?) 에도 추가가 된다.

create table test01
as
select * from emp;


create or REPLACE view view_test01
as 
select empno, ename, job
from test01;

select * from view_test01;

insert into view_test01 VALUES (1111, 'PPO', 'SEON');
select * from view_test01;
select * from test01;

-- 전 사원에 대한 정보 출력 (사원정보, 부서정보)
select *
from emp, dept
where emp.deptno=dept.deptno
;

-- deptno가 두개라서 하나만 보이게 하려고 다써줌 ㅠ
-- 두개 테이블을 join한 것은 insert가 불가능
create or replace view view_emp_dept
as
select empno, ename, job, mgr, hiredate, sal, comm, emp.deptno, dname, loc
from emp, dept where emp.deptno=dept.deptno;

select * from view_emp_dept;
select * from view_emp_dept where deptno=10;



-- view 정보 확인 : user_views 테이블을 통해 확인이 가능
-- 로그인한 사용자의 view 인스턴스의 정보
select * from user_views;
select view_name, text from user_views;


-- view_emp30을 통해 insert
insert into view_emp30 values (9999, 'tester', 40);

desc emp;
select * from emp;
rollback;

-- view의 삭제 : 객체를 삭제하는 방식과 동일
-- drop

drop view view_emp_dept;

ROWNUM / 로우넘

-- view_hire : 입사일 기준으로 오름차순으로 정렬된 결과를 가상테이블로 정의

select empno, ename, hiredate from emp order by hiredate;

create or replace view view_hiredate
as
select empno, ename, hiredate from emp order by hiredate
;

select rownum, empno, ename, hiredate from view_hiredate;

select * from view_hiredate where rownum<=5;


-- 인라인 뷰를 이용해서 가장 최근에 입사한 사원 5명을 추출
select *
from (select * from emp order by hiredate desc)
where rownum<=5
;

select * from emp order by hiredate desc

- COMMIT 명령어

Transaction(INSERT, UPDATE, DELETE) 작업 내용을 실제 DB에 저장합니다.
이전 데이터가 완전히 UPDATE 됩니다.
모든 사용자가 변경된 데이터의 결과를 볼 수 있습니다.

- ROLLBACK 명령어

Transaction(INSERT, UPDATE, DELETE) 작업 내용을 취소합니다.
이전 COMMIT한 곳 까지만 복구합니다.

-- 트랜잭션(Transaction)
-- 여러개의 sql을 하나의 단위로 처리하는 것
-- 트랜잭션이 가지는 모든 작업이 모두 정상처리 되어야 트랜잭션이 완료되었다고 하는것

truncate table emp10;
insert into emp10
select * from emp;
commit;
 
select * from emp10;
select * from dept01;

-- 부서 테이블에 데이터 하나를 저장 - 정상
-- 사원 테이블에 새로운 사원을 입력 - 오류

insert into dept01 values (50, 'RD', 'SEOUL');
insert into emp10(empno, ename, job, sal) values ('ten', 'TEN', 'MANAGER', 2500);
rollback; -- 오류가 발생해서 초기화 (마지막저장단계 commit단계로 이동)

-- 새로운 트랜잭션 시작
insert into dept01 values (50, 'RD', 'SEOUL');
insert into emp10(empno, ename, job, sal) values (7777, 'TEN', 'MANAGER', 2500);
select * from emp10;
select * from dept01;
commit;

-- 새로운 트랜잭션 시작
update emp10
set mgr=(select empno from emp10 where ename='KING');
-- 잘못처리된 작업이라고 하면, rollback;
rollback;

- COMMIT 명령어과 ROLLBACK 명령어의 장점

데이터 무결성이 보장됩니다.
영구적인 변경 전에 데이터의 변경 사항을 확인할 수 있습니다.
논리적으로 연관된 작업을 그룹화할 수 있습니다.

- 자동 커밋
- DDL 문에는 CREATE, ALTER, DROP, RENAME, TRUNCATE 등이 있습니다.
- 이러한 DDL문은 자동으로 커밋(AUTO COMMIT)이 발생됩니다.

# Chapter03_03.py
# 리스트
# 자료구조에서 중요

# 리스트 자료형 (순서, 중복, 삭제 가능)



### 선언
a = []
b = list()
c = [ 70, 75, 80, 75] # print(len(c))=4
d = [1000, 10000, 'Ace', 'Base', 'Captine'] # 서로 다른 자료형도 가능
e = [1000, 10000, ['Ace', 'Base', 'Captine']] # 리스트 in 리스트
f = [21.42, 'foobar', 3, 4, False, 3.14159]



# 인덱싱
print('>>>>>>>>>')
print(' d - ', type(d), d)
print(' d - ', d[1])
print(' d - ', d[0] + d[1] + d[1])
print(' d - ', d[-1]) # 맨 오른쪽
print(' e - ', e[-1][1])
print(' e - ', list(e[-1][1])) # 문자열을 리스트 형태로 형변환 가능


# 슬라이싱
print('>>>>>>>>>')
print('d - ', d[0:3])
print('d - ', d[2:])
print('e - ', e[-1][1:3])


# 리스트 연산 (리스트+리스트=리스트)
print('>>>>>>>>>')
print('c + d =', c+d)
print('c * 3 =', c*3) #리스트*정수= 리스트, 리스트연산의 결과 = 리스트
# print("'Test' + c[0] = ", 'test'+c[0]) #type error
print("'Test' + c[0] = ", 'test'+str(c[0]))


# 값 비교
print('>>>>>>>>>')
print(c == c[:3] + c[3:])
print(c)
print(c[:3])
print(c[3:])

# 같은 identity(id)
print('>>>>>>>>>')
temp = c
print(temp, c)
print(id(temp))
print(id(c))    # 파이썬이 속도와 쾌적한 환경을 제공하기 위해 리스트도 같은값 = 같은id

# 리스트 수정, 삭제
print('>>>>>>>>>')
c[0] = 4
print('c - ', c)
print(c[1:2])  #c[1]
c[1:2] = ['a', 'b', 'c']
print('c - ', c)
c[1:2] = [['a', 'b', 'c']] # = c[1]=['a', 'b', 'c']
print('c - ', c)
c[1:3] = []
print('c - ', c)

# 제거/삭제
print('>>>>>>>>>')
del c[2]
print('c - ', c)


# 리스트 함수
print('>>>>>>>>>')
a = [5, 2, 3, 1, 4]
print('a - ', a)
#a[5] = 10 에러, 추가시엔 append 함수!
a.append(10)
print('a - ', a)
a.sort() # 파이썬 리스트 오름차순 정렬
print('a - ', a)
a.reverse()  #역순으로 정렬 (내림차순 X)
print('a - ', a)
print('a - ', a.index(4), a[4]) # 인덱스로 가져오기
a.insert(2,7) #0,1,2번째(숫자론세번째)  자리에 7을 넣고 나머지는 뒤로 민다 (삽입)
print('a - ', a)
a.remove(1) #del a[6]와 같다. 인덱스 제거가 아닌 값으로 제거.
print('a - ', a)
print('a - ', a.pop())
#기존의 리스트에서 마지막에 있던 값을 꺼내오고 그 값을 삭제
# LAST IN FIRST OUT
# 접시를 쌓아놓고 맨 위에 쌓인(마지막에 놓은) 것을 먼저 쓰는 것을 생각하면 이해가 쉽다.
# ex) 웹 브라우저에서 뒤로가기를 누르면 바로 전 페이지로 가는 것과 같다
print('a - ', a)
print('a - ', a.count(4)) # 리스트 안에 값 '4'가 몇개인지, 있는지 없는지 확인할때 (=값 0)


ex=[8,9]
a.extend(ex) #뒤에 다른 리스트를 붙여줌
print('a - ', a)

#### 삭제 : remove, del, pop

# 반복문을 이용한 pop

while  a:
    data =a.pop()
    print(data)

-- dept01 테이블 생성 (구조만가져올것)
drop table dept01;
create table dept01
as
select * from dept where 1=2
;
desc dept01;
select * from dept01;

insert into dept01 (deptno, dname, loc) values (10, '마케팅', '서울');
insert into dept01 values (40, '디자인', '보스턴');

-- null값의 입력
insert into dept01 (deptno, dname) values (30, 'QC');
insert into dept01 values (30, 'QC', null);

-- 서브쿼리를 이용해서 데이터 입력
drop table dept02;
create table dept02 as select * from dept where 1=2;

select * from dept02;

insert into dept02
select * from dept;

-- 데이터의 수정
-- update 테이블이름 set 칼럼이름 = value, ..., where 행을 찾기 위한 조건

-- 1. 모든 사원의 부서를 30번으로 수정하자.
drop table emp10;
create table emp10 as select * from emp;
update emp10 set deptno=30;
select * from emp10;

-- 모든 사원의 급여를 10% 인상시키는 업데이트문
update emp10
set sal=sal*1.1;

-- 모든 사원이 입사일을 오늘로 수정
update emp10
set hiredate=sysdate;

-- 급여가 3000 이상인 사원만 급여를 10프로 인상
update emp10
set sal=sal*1.1
where sal>=3000;

-- 1987년에 입사한 사원의 입사일을 오늘로 수정

update emp10
set hiredate=sysdate
where substr(hiredate,1,2)=87;

-- 전체 데이터를 삭제하고 다시 emp넣기 리셋 [하도 이거저거해서 리셋]
truncate table emp10;
insert into emp10
select * from emp;


-- SCOTT 사원의 부서는 20번으로 직급은 manager로 수정
update emp10
set deptno=20, job='MANAGER'
where ename='SCOTT';

-- SCOTT 사원의 입사일자는 오늘로,
-- 급여를 50 으로 커미션을 4000 으로 수정합시다.

update emp10
set hiredate=sysdate, comm=4000, sal=50
where ename='SCOTT';

--- 서브쿼리를 이용한 업데이트
-- 10번 부서의 지역명을 40번 부서의 지역명으로 변경
update dept01
set loc=(select loc from dept01 where deptno=40)
where dept=10;

update dept01
--set dname=(select dname from dept01 where deptno=40),
--    loc=(select loc from dept01 where deptno=40)
set (dname, loc) = (select dname, loc from dept01 where deptno=40)
where deptno=20;


--dept01 테이블의 모든 데이터(데이터만)를 삭제
select * from dept01;
delete from dept01;

-- emp10
select * from emp10;

-- emp10 테이블의 데이터중 부서번호가 10번인 부서의 사원들을 삭제
delete from emp10
where deptno=10;

-- 부서명이 sales인 사원을 모두 삭제해봅시다.
delete from emp10
where deptno=(select deptno from dept where dname='SALES');

--dept01 테이블의 모든 데이터(데이터만)를 삭제
select * from dept01;
delete from dept01;

-- 무결성제약 조건에 위배됩니다
desc emp01;
insert into emp01 values(null, null, 1000, 'tester');
select * from emp01;

-- emp01 삭제
drop table emp01;


-- 필수 입력을 위한 제약 조건 :NOT null 
-- emp01 테이블 생성 : empno, ename에 null값이 들어가지 않도록 제약
create table emp01(
        empno   number(4)       not null,
        ename   varchar2(10)    not null,
        job     varchar(9),
        deptno  number(2)
        );
insert into emp01 values(null, null, 'tester', '10');
-- ORA-01400: NULL을 ("SCOTT"."EMP01"."EMPNO") 안에 삽입할 수 없습니다

-- 데이터의 중복 금지 : UNIQUE
drop table emp02;
create table emp02(
    empno  number(4)    unique,
    ename  varchar2(9),
    deptno number(2)
);
insert into emp02 values(1, 'tester', '10');
insert into emp02 values(1, 'tester2', '10');
-- ORA-00001: 무결성 제약 조건(SCOTT.SYS_C0011060)에 위배됩니다.

-- 기본키 제약 : 기본키 설정 > not null, unique
drop table emp04;
create table emp04(
        empno number(4) primary key,
        ename varchar2(10) not null
    );
    
insert into emp04 values(null, 'test'); -- null 에러
insert into emp04 values(1, 'test');
insert into emp04 values(1, 'test'); -- 무결성제약조건 에러

-- 외래키 제약 : 참조하는 테이블과 컬럼을 정의 (reference's')
drop table emp05;
create table emp05(
    empno number(4) primary key,
    ename varchar2(10) not null,
    deptno number(2) references dept(deptno)
);
-- emp05의 deptno테이블에는 emp테이블의 dept에 있는 값만 들어갈 수있다
-- 즉 10,20,30만 들어갈 수 있다. (40,50 X)

insert into emp05 values(1, 'test', 10);
insert into emp05 values(1, 'test', 40); -- 40X 무결성 에러

-- check : 특정 범위 제한 
create table emp06 (
    empno   number(4)       primary key,
    ename   varchar2(10)    not null,
    sal     number(7,2)     check (sal>=800)
);

insert into emp06 values (1, 'test', 1000);
insert into emp06 values (2, 'test', 100); -- 체크 제약조건 위배

-- default :  insert 시에 자동으로 등록되는 데이터 정의
drop table emp07;
create table emp07(
    empno       number(4)       primary key,
    ename       varchar2(10)    not null,
    sal         number(7,2)     check (sal>=500),
    comm        number(7,2)     default 0, -- number와 타입은 맞춰줘야한다.
    hiredate    date            default sysdate
);

insert into emp07 (empno,ename,sal) values (1,'test',1000);
select * from emp07;

/*
    EMPNO ENAME             SAL       COMM HIREDATE
---------- ---------- ---------- ---------- --------
         1 test             1000          0 20/11/12
*/

두 개의 제약조건 (not null과 unique는 primary key로 사용가능하지만 예시로 만듦)

-- empno가 not null과 unique 제약을 동시에 적용
drop table emp03;
create table emp03(
    empno   number(4)   not null unique,
    ename  varchar2(9) not null
);

insert into emp03 values(null, 'test1');
insert into emp03 values(1, 'test1');
insert into emp03 values(2, 'test1');
select * from emp03;

-- 제약조건에 이름 부여
drop table emp08;
create table emp08(
    empno       number(4)    constraint emp08_empno_pk   primary key,
    ename       varchar2(10) constraint emp08_ename_nn   not null,
    sal         number(7,2)  constraint emp08_sal_CK500  check (sal>=500),
    comm        number(7,2)     default 0, -- number와 타입은 맞춰줘야한다.
    hiredate    date            default sysdate
);

insert into emp08 (empno,ename,sal) values (1,'test',1000);

-- 테이블 레벨에서 제약조건 정의
drop table emp09;
create table emp09(
    empno number(4),  -- not null은 컬럼레벨에서 해야함
    ename varchar2(10) not null,
    job  varchar2(9),
    deptno number(10), -- 컬럼정의끝
    constraint emp09_empno_pk primary key (empno),
    constraint emp09_job_uk unique(job),
    constraint emp09_depno_fk foreign key (deptno) references dept(deptno)
    );

insert into emp09 values (null, null, 'job1', 50);
insert into emp09 values (1, null, 'job1', 40);
insert into emp09 values (1, 'tester', 'job1', 40);
insert into emp09 values (1, 'tester', 'job1', 40);