신한DS 금융 SW 아카데미 - 4일차

데이터 조회

1. 어디에서

2. 어떻게

3. 무엇을

 

select 컬럼(값, 함수, 서브쿼리. . .:값)

from 테이블

where 조건(비교, 논리. . .: true/false)

group by 

having 조건(그룹핑후)

order by

 

조인(join)

- inner / outer(left, right)

 

서브쿼리(subquery)

- from 뒤에 테이블(inline view)

- where

- select절(스칼라 서브쿼리)

 

DML(조회, 등록, 수정, 삭제)

DDL

DCL

TCL

 

등록 : insert

수정 : update

삭제 : delete

 

update 테이블명 set

delete from 테이블명

 

GUI (Graphic User Interface)

CLI (Command Line Interface)

 

truncate, delete 차이점

- truncate : DDL, 데이터 삭제+테이블 초기화(용량같은것들), 트랜잭션X

- delete : DML, 데이터만 삭제, 트랜잭션O

 

CRUD - Create, Read, Update, Delete

 

인덱스

- 숫자, 날짜, char 타입 적용

- 가변문자열은 미적용 가능성

create table dept_temp as select * from dept;

insert into dept_temp(deptno, dname, loc) values('50', 'database', 'seoul');

select * from dept_temp;

insert into dept_temp(deptno, dname, loc)
values(600, 'network','busan');

select * from emp where deptno='20';

-- 컬럼 생략(테이블에 정의된 컬럼 순으로 values)
insert into dept_temp
values(70, 'devel','busan');

-- 컬럼 순서 변경
insert into dept_temp(loc, dname, deptno)
values ('busan', 'network', 80);

-- 컬럼명 c1 ~ c10
insert into 테이블명 (
    c1, c2, c3, c4, c5,
    c6, c7, c8, c9, c10
)values(
    1,2,3,4,5,
    6,7,8,9,10
);

-- null 입력방법
insert into dept_temp(deptno, dname, loc)
values(90, 'network', null);

insert into dept_temp(deptno, dname)
values(91, 'network');

insert into dept_temp(deptno, dname, loc)
values(92, 'network', '');

create table emp_temp as select * from emp where 1 <> 1;

insert into emp_temp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(9999, '홍길동', 'president', null, '2001/01/01', 5000, 1000, 10);
insert into emp_temp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(1111, '성춘향', 'manager', 9999, '2001-01-05', 4000, null, 20);
select * from emp_temp;
insert into emp_temp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(3111, '심청이', 'manager', 9999, sysdate, 4000, null, 30);
select * from emp_temp;

insert into emp_temp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno
        from emp e, salgrade s
        where e.sal between s.losal and s.hisal
            and s.grade = 1;

create table dept_temp2 as select * from dept;

update dept_temp2 set loc='seoul';
commit;
select * from dept_temp2;
rollback;

update dept_temp2
set dname = 'database', loc='seoul'
where deptno = 40;
select * from dept_temp2;

update dept_temp2 
set loc = 'seoul' 
where deptno = (select deptno from dept_temp2 where dname='operations');
select * from dept_temp2;

-- emp_temp 사용
-- research부서의 급여를 10% 인상
update emp_temp 
set sal = sal*1.1
where deptno = (select deptno from dept where dname='RESEARCH');

delete from emp_temp where job='manager';
delete from emp_temp;

create table dept_tcl as select * from dept;
select * from dept_tcl;
insert into dept_tcl values(50, 'DATABASE', 'SEOUL');
update dept_tcl set loc='BUSAN' where deptno=40;
delete from dept_tcl where dname='RESEARCH';
select * from dept_tcl;
rollback;                                        

-- 50 -> JEJU 
update dept_tcl set loc='JEJU' where deptno=50;
savepoint p1;
-- 30 -> SEOUL
update dept_tcl set loc='SEOUL' where deptno=30;
rollback to p1;
select * from dept_tcl;
-- 50 -> SEOUL
update dept_tcl set loc='XXX' where deptno=50;
select * from dept_tcl;
commit;

select * from testuser.dept_tcl;

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 emp_alter as select * from emp;
alter table emp_alter add hp varchar(20);

select * from emp_alter;

alter table emp_alter rename column hp to tel;
select * from emp_alter;

alter table emp_alter modify empno number(5);
alter table emp_alter drop column tel;

rename emp_alter to emp_rename;
alter table emp_rename rename to emp_rename2;

truncate table emp_rename2;
select * from emp_rename2;

drop table emp_rename2;

explain plan for select * from emp where ename = 'SMITH';
select * from table(dbms_xplan.display);

create index empindex on emp(ename);