[3일차] DO IT 오라클 데이터베이스 / p 242 ~ 358 / 서브쿼리, 트랜잭션, 뷰, 인덱스

 

 

 

 

 

 

 

 

commit;

-- 커밋 실행 이후에 롤백 명령어를 실행하면 이미 커밋이 된 데이터가 롤백이 된다. 커밋은 변경된 데이터가 제대로 되어있는지 확실하게 확인한 후에 커밋을 해야한다.

select * from dept;

select * from dept_tcl;

-- 수정 중인 데이터 접근을 막는 역할을 하는 LOCK 조작중인 데이터를 다른 세션은 조작 할 수 없도록 접근을 보류시킴

commit;

select * from dept_tcl;

 

 

-- create 

create table emp_ddl(
    empno   number(4),
    ename   varchar2(10),
    job        varchar2(9),
    mgr       number(4),
    hiredate  date,
    sal         number(7,2),
    comm    number(7,2),
    deptno    number(2)
);


desc emp_ddl;

-- 다른 테이블 복사하여 테이블 생성 
create table dept_ddl as select * from dept;

select * from dept_ddl;

 

 

-- 다른 테이블 복사하여 테이블 생성 
create table dept_ddl as select * from dept;

select * from dept_ddl;

create table emp_ddl_30 as select * from emp where deptno = 30;

select * from emp_ddl_30;


create table empdept_ddl 
as select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, d.deptno, d.dname, d.loc
from emp e, dept d where 1 <> 1;

select * from empdept_ddl;

create table emp_alter as select * from emp;

select * from emp_alter;

 

alter table emp_alter add hp varchar2(20);

 

alter table emp_alter rename column hp to tel;

alter table emp_alter modify empno number(5);

desc emp_alter;

 

 

alter table emp_alter drop column tel;

rename emp_alter to emp_rename;

desc emp_alter;

select * from emp_rename;

 

 

-- truncate 특정 테이블의 모든 데이터를 삭제한다 데이터만 삭제하므로 테이블 구조에는 영향을 주지 않는다.

truncate table emp_rename;

select * from emp_rename;

-- 테이블 삭제 drop 명령어

drop table emp_rename;

desc emp_rename;

 

 

 

-- 테이블을 변경하는 alter 
-- add 추가 
-- rename 열이름 변경 
-- 열의 자료형을 변경 modify
-- 특정 열을 삭제 할때 drop

 

 

 

 

-- 데이터 사전 : 데이터베이스를 구성하고 운영하는데 필요한 모든 정보를 저장하는 특수한 테이블 데이터 베이스가 생성된느 시점에 자동으로 만들어짐

 

 

 

-- 데이터 사전 : 데이터베이스를 구성하고 운영하는데 필요한 모든 정보를 저장하는 특수한 테이블 데이터 베이스가 생성된느 시점에 자동으로 만들어짐

select * from dict;

select * from dictionary;


select table_name from user_tables;

select owner, table_name from all_tables;

 

select * from dba_tables;

 

select * from dba_tables;


select * from user_indexes;


select * from user_ind_columns;

 

--문법
--CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)
--예제
--CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 

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

 

 

create index idx_emp_sal on emp(sal);

select * from user_ind_columns;

-- drop index 인덱스 이름;  인덱스 삭제

drop index idx_emp_sal;

select * from user_ind_columns;


--문법
--CREATE OR REPLACE VIEW [스키마.][뷰 NAME] AS
--SELECT문;

create view vw_emp20 as (select empno, ename, job, deptno from emp where deptno = 20);

select * from user_views;

select view_name, text_length, text from user_views;

select * from vw_emp20;

 

drop view vw_emp20;

 

 

select rownum, e.* from emp e;

select rownum, e.* from emp e order by sal desc;

select rownum, e.* from (select * from emp e order by sal desc) e;

with e as (select * from emp order by sal desc) select rownum, e.* from e;

-- 서브쿼리 적용
select rownum, e.* from (select * from emp order by sal desc) e where rownum <= 3;

-- with 절 사용
with e as (select * from emp order by sal desc) select rownum, e.* from e where rownum <= 3;


--sequence 정말 중요함 왜냐 게시판에서 많이 활용된다.. select max(글번호) + 1 from 게시판 테이블

--문법
--CREATE SEQUENCE [시퀀스명]
--INCREMENT BY [증감숫자] --증감숫자가 양수면 증가 음수면 감소 디폴트는 1
--START WITH [시작숫자] -- 시작숫자의 디폴트값은 증가일때 MINVALUE 감소일때 MAXVALUE
--NOMINVALUE OR MINVALUE [최솟값] -- NOMINVALUE : 디폴트값 설정, 증가일때 1, 감소일때 -1028 
---- MINVALUE : 최소값 설정, 시작숫자와 작거나 같아야하고 MAXVALUE보다 작아야함
--NOMAXVALUE OR MAXVALUE [최대값] -- NOMAXVALUE : 디폴트값 설정, 증가일때 1027, 감소일때 -1
---- MAXVALUE : 최대값 설정, 시작숫자와 같거나 커야하고 MINVALUE보다 커야함
--CYCLE OR NOCYCLE --CYCLE 설정시 최대값에 도달하면 최소값부터 다시 시작 NOCYCLE 설정시 최대값 생성 시 시퀀스 생성중지
--CACHE OR NOCACHE --CACHE 설정시 메모리에 시퀀스 값을 미리 할당하고 NOCACHE 설정시 시퀀스값을 메로리에 할당하지 않음


--예제
--CREATE SEQUENCE EX_SEQ --시퀀스이름 EX_SEQ
--INCREMENT BY 1 --증감숫자 1
--START WITH 1 --시작숫자 1
--MINVALUE 1 --최소값 1
--MAXVALUE 1000 --최대값 1000
--NOCYCLE --순한하지않음
--CACHE; --메모리에 시퀀스값 미리할당

 

 

create table dept_sequence as select * from dept where 1 <> 1;

select * from dept_sequence;

create sequence seq_dept_sequence 
increment by 10
start with 10
maxvalue 90
minvalue 0
nocycle 
cache 2;


select * from user_sequences;


insert into dept_sequence (deptno, dname, loc) values (seq_dept_sequence.nextval, 'DATABASE', 'SEOUL');

select * from dept_sequence order by deptno;

-- currval 마지막으로 생성된 순번 표시
select seq_dept_sequence.currval from dual;


insert into dept_sequence (deptno, dname, loc) values (seq_dept_sequence.nextval, 'DATABASE','SEOUL');

select * from dept_sequence order by deptno;


----문법
--ALTER SEQUENCE [시퀀스명]
--INCREMENT BY [증가값]
--NOMINVALUE OR MINVALUE [최솟값] 
--NOMAXVALUE OR MAXVALUE [최대값]
--CYCLE OR NOCYCLE [사이클 설정 여부]
--CACHE OR NOCACHE [캐시 설정 여부]



alter sequence seq_dept_sequence 
increment by 3
maxvalue 99
cycle;


select * from user_sequences;

insert into dept_sequence (deptno, dname, loc) values (seq_dept_sequence.nextval, 'DATABASE','SEOUL');

select * from dept_sequence order by deptno;

drop sequence seq_dept_sequence;

select * from user_sequences;



--문법
--CREATE OR REPLACE [PUBLIC / PRIVATE] SYNOYM [사노님명]
--FOR [객체명]
--예제
--CREATE OR REPLACE SYNONYM EX_SYNONYM
--FOR CUSTOMERS;


create synonym e for emp;

select * from e;

drop synonym e;

 

 

 

사용자명 입력: scott
비밀번호 입력:
마지막 성공한 로그인 시간: 목 1월  21 2021 15:19:15 +09:00

다음에 접속됨:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from dept_tcl;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     BUSAN
        30 SALES                        CHICAGO
        50 NETWORK                      SEOUL

SQL> delete from dept_tcl where deptno = 50;

1 행이 삭제되었습니다.

SQL> select * from dept_tcl;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     BUSAN
        30 SALES                        CHICAGO

SQL> commit;

커밋이 완료되었습니다.

SQL> select * from dept_tcl;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     BUSAN
        30 SALES                        CHICAGO

SQL> select * from dept_tcl;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     BUSAN
        30 SALES                        CHICAGO

SQL> update dept_tcl set loc = 'SEOUL' where deptno = 30;

1 행이 업데이트되었습니다.

SQL> select * from dept_tcl;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     BUSAN
        30 SALES                        SEOUL

SQL> update dept_tcl set dname = 'DATABASE' where deptno = 30;

1 행이 업데이트되었습니다.

SQL> select * from dept_tcl;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     BUSAN
        30 DATABASE                     SEOUL

SQL> select * from dept_tcl;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     BUSAN
        30 DATABASE                     SEOUL

SQL> commit;

커밋이 완료되었습니다.