인덱스의 장점

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

인덱스의 단점
- 인덱스를 위한 추가적인 공간이 필요하다.
- 인덱스를 생성하는데 시간이 걸린다.
- 데이터의 변경 작업(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)이 발생됩니다.

-- 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);

--------- ALER TABLE
-- 테이블 삭제 : 저장공간을 삭제, 저작되어있는 데이터도 모두 삭제
drop table test_tbl;

-- 모든 로우를 제거하는 truncate : 바로삭제!
create table emp06
as
select * from emp;
select * from emp06;

truncate table emp06;


---------- 테이블 이름변경
-- rename old_name to new_name;
rename emp06 to new_emp;




----------  ALTER TABLE : 테이블 구조의 변경
-- alter table table_name 
-- add (컬럼추가) | modify(컬럼변경) | drop(컬럼삭제) ();

-- 기존 테이블에 속성을 추가 : 각 행의 컬럼 데이터는 null값
-- emp01에  job 컬럼을 추가. (varchar2(10)
desc emp01;

alter table emp01 
add(JOB varchar2(10));
alter table emp01 
add(deptno number(2));


-- 기존 테이블의 컬럼 변경 modify 
-- 새롭게 정의된 컬럼으로 교체하는 것

alter table emp01
modify (deptno number(10));

-- 기존 테이블의 컬럼을 삭제 : 데이터도 사라진다
alter table emp01
drop (deptno);

---------- 테이블 생성 : 
-- create table table_name
-- (
--     column_name domain [constraint] 제약/선택적,
--     column_name domain [constraint],
--     column_name domain [constraint],
--     ...
--   )

create table test_tbl (
        no              number(4),
        user_name       varchar2(10),
        user_id         varchar2(16),
        user_password   varchar2(12),
        reg_date        Timestamp DEFAULT sysdate
    );

desc test_tbl;

-- 사원 테이블과 유사한 구조의 사원번호, 사원이름, 급여 3개의 칼럼으로 구성된
-- emp01 테이블을 생성해 보자.
create table emp01(
        empno number(4),
        ename varchar2(10),
        sal   number(7,2)
    );

---------- create table ~as ~ : 테이블 복사! but 제약조건은 복사되지 않는다.

-- CREATE TABLE 명령어 다음에 컬럼을 일일이 정의하는 대신 
-- AS 절을 추가하여 EMP 테이블과 동일한 내용과 구조를 갖는
-- EMP02 테이블을 생성해 봅시다.
CREATE TABLE EMP02 AS SELECT * FROM EMP;
desc emp02;
desc emp;


--서브 쿼리문의 SELECT 절에 * 대신
--원하는 컬럼명을 명시하면 기존 테이블에서 일부의 컬럼만 복사할 수 있습니다.
create table emp03
as
select empno, ename, sal from emp
;

desc emp03;

--서브 쿼리문의 SELECT 문을 구성할 때
-- WHERE 절을 추가하여 원하는 조건을 제시하면 기존 테이블에서 일부의 행만 복사합니다.

create table emp04
as
select * from emp where deptno=10
;
select * from emp04;


-- 테이블의 구조만 복사하는 것은 별도의 명령이 있는 것이 아닙니다. 
--이 역시 서브 쿼리를 이용해야 하는데 WHERE 조건 절에 항상 거짓이 되는 조건을 지정하게 되면 
--테이블에서 얻어질 수 있는 로우가 없게 되므로 빈 테이블이 생성되게 됩니다

create table emp05
as
select * from emp where 1=2
;
select * from emp05;

-- 부속질의

-- 스칼라 부속질의 / select 부속질의,
-- 단일행, 단일열, 단일값의 결과가 나와야한다.

select name from customer where custid=1;

select custid, (select name from customer where customer.custid=orders.custid),
        saleprice, orderdate
from orders
;


--마당서점의 고객별 group by
-- 판매액을 보이시오 orders
-- (결과는 고객이름과 고객별 판매액을 출력).

select c.custid, c.name, sum(o.saleprice)
from customer c, orders o
where c.custid=o.custid(+)
group by c.custid, c.name
order by sum(o.saleprice)
;

select o.custid, (
    select name from customer c where o.custid=c.custid),
sum(o.saleprice)
from orders o
group by o.custid
;

-- 인라인 뷰 / from 부속질의

--질의 고객번호가 2 이하인 고객의 판매액을 보이시오 
--(결과는 고객이름과 고객별 판매액 출력)
select custid, name
from customer
where custid<=2
; -- 가상테이블

select cs.name, sum(o.saleprice)
from (select custid, name
from customer
where custid<=2) cs , orders o
where cs.custid=o.custid
group by cs.name
;

-- 중첩질의 – WHERE 부속질의
-- 평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오.
select orderid, saleprice
from orders
where saleprice < (select avg(saleprice) from orders)
;

-- 각 고객의 평균 주문금액보다 
-- 큰 금액의 주문 내역에 대해서
-- 주문번호, 고객번호, 금액을 보이시오.
select avg(saleprice) 
from orders
group by custid
;


select o.orderid, o.custid, o.saleprice, c.name
from orders o, customer c
where saleprice >
(select avg(saleprice) 
from orders
o2 where o.custid=o2.custid)
AND (o.custid=c.custid)
;


--대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오.

select sum(saleprice)
from orders
where custid in (select custid from customer where address LIKE '%대한민국%')
;

--3번 고객이 주문한 도서의 최고 금액보다 
--더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오.

select orderid, saleprice
from orders
-- where saleprice > (select max(saleprice) from orders where custid=3);
where saleprice > all(select saleprice from orders where custid=3)
;

-- EXISTS 연산자로 대한민국에 거주하는 고객에
-- 판매한 도서의 총 판매액을 구하시오.  

select * from customer where address LIKE '%대한민국%';

select sum(saleprice)
from orders o
where EXISTS (select * from customer c where address LIKE '%대한민국%' AND o.custid=c.custid)
;

-- 중첩질의 : 단순비교 

--평균 급여를 구하는 쿼리문을 서브 쿼리로 사용하여
--평균 급여보다 더 많은 급여를 받는 사원을 검색

select *
from emp        -- ↓ 평균급여
where sal > (select avg(sal)from emp)    
;

-- SCOTT 사원의 같은 부서에 근무하는 사원의 리스트를 출력
select deptno from emp where ename='SCOTT'; --20

select ename, deptno from emp 
where deptno=(select deptno from emp where ename='SCOTT');

-- sal>3000이상 받는 사원이 소속된 부서
select distinct deptno from emp where sal >=3000;

select *
from emp
--where deptno=10 or deptno=20
where deptno in (select distinct deptno from emp where sal >=3000)
;

--30번 소속 사원들 중에서 급여를 가장 많이 받는 사원보다
--더 많은 급여를 받는 사람의 이름, 급여를 출력하는 쿼리문을 작성해 봅시다.
select sal from emp where deptno=30;

select ename, sal from emp 
where sal> all (select sal from emp where deptno=30);
-- 모든게 다 만족해야할 때

--다음은 부서번호가 30번인 사원들의 급여 중
--가장 작은 값(950)보다 많은 급여를 받는 사원의 이름,
--급여를 출력하는 예제를 작성해 봅시다.
select ename, sal from emp 
where sal> any (select sal from emp where deptno=30);

-- ROWNUM #입력순 (로우넘 날짜순X)
select rownum, empno, ename, hiredate
from emp
order by hiredate
;

-- 기준에 맞춰 몇개 뽑아내기
select rownum, empno, ename, hiredate
from emp
where rownum<4
order by hiredate
;

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

-- ROWNUM 날짜순 O
select rownum, empno, ename, hiredate
from (select rownum, empno, ename, hiredate from emp order by hiredate desc)
where rownum<4 -- 입사일이 제일 최근인 세명 (desc)
;

-- 가장 급여를 많이 받는 사원 3명을 출력하자
select rownum, ename, sal from emp order by sal desc;
select rownum, ename, sal 
from(select rownum, ename, sal from emp order by sal desc)
where rownum<4
;

-- CROSS JOIN 크로스 조인 : 조건 없이 테이블과 테이블을 붙여준다
-- 단순하게 붙여주는 것이라 아무 의미가 없다.
select *
from emp, dept;

-- EQUI JOIN
-- 동일 칼럼을 기준으로 조인합니다.
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

-- SCOTT의 사원번호와 이름, 부서이름, 지역을 출력하자
SELECT empno, ename, dname, loc, dept.deptno -- deptno만 쓰면 오류남 or emp.deptno
-- emp.empno, emp.ename, dept.dname, dept.loc, dept(emp).deptno로 쓰는 것이 사실 맞음
FROM emp, dept
where ename='SCOTT' AND emp.deptno=dept.deptno
;

-- 별칭
-- 테이블의 이름이 긴경우.
SELECT e.empno, e.ename, d.dname, d.loc, d.deptno
FROM emp e, dept d
where ename='SCOTT' AND e.deptno=d.deptno
;

-- NON-EQUI JOIN : 동등비교가 아닌 비교연산이 가능
-- 급여 등급을 5개로 나누어 놓은 salgrade에서 정보를 얻어 와서 
-- 각 사원의 급여 등급을 지정해보도록 합시다. 
-- 이를 위해서 사원(emp) 테이블과 급여 등급(salgrade) 테이블을 조인하도록 합시다.
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal
;

-- SELF JOIN : 자신의 테이블을 조인
-- SMITH의 매니저 이름이 무엇인지 알아내려면 어떻게 구해야 할까요?

select ename, mgr
from emp
where ename='SMITH'
;

-- +
select ename, mgr
from emp
where empno=7902
;


-- =

select e.ename, e.mgr, m.ename as MGRNAME
from emp e, emp m
where e.mgr=m.empno AND e.ename='SMITH'
;

-- outer join : 정보가 부족해도 출력하도록 하는 join

select e.ename, e.mgr, m.ename as MGRNAME
from emp e, emp m
where e.mgr=m.empno(+)
;

-- ANSI JOIN : CROSS JOIN
select *
from emp cross join dept
;

-- INNER JOIN / ON : 조인의 조건

--SELECT ename, dname
--FROM emp, dept
--where ename='SCOTT' AND emp.deptno=dept.deptno
--;

SELECT ENAME, DNAME 
FROM EMP INNER JOIN DEPT 
ON EMP.DEPTNO=DEPT.DEPTNO 
WHERE ENAME='SCOTT';

-- USING
select *
from emp join dept
-- on emp.deptno=dept.deptno
using (deptno) 
-- 공통 컬럼은 하나만 가지게 해준다. 공통 컬럼을 구별할 필요가 없다
;

-- NATURAL JOIN : 동일 컬럼 조인의 조건을 생략
-- 같은 이름의 컬럼은 하나씩 존재해야 한다.
select *
from emp NATURAL JOIN dept
;

-- ansi outer join
-- table1 [LEFT | RIGHT | FULL ] outer join table2

select *
from emp e left outer join emp m
on (e.mgr=m.empno)
;

:

-- 회원 구매
-- customer, orders

select * from customer;
select * from orders order by custid;

select *
from customer c left outer join orders o
on (c.custid=o.custid)
;

-- 회원별 구매횟수
select c.name, count(o.orderid), sum(o.saleprice), round(avg(o.saleprice))
from customer c, orders o
where c.custid=o.custid(+)
group by c.name
;

-- 그룹함수 : 하나의 행의 컬럼을 대상이 아닌 행의 그룹의 컬럼들을 묶어 그룹화하고 연산하는 함수
-- SUM, AVG, COUNT, MAX, MIN

select 
    sum(sal) as "월 급여 총액",
    to_char(sum(sal), 'L99,999') as "월 급여 총액2",
    round(avg(sal)) as "월 급여 평균",
    count(*) as "사원 수",
    count(comm) as "커미션을 받는 사원수", -- null은 세지 않는다. 0은 셈
    max(sal) as "가장 큰 급여"
from emp
;

select
    sum(comm),
    avg(comm),
    count(comm),
    max(comm),
    min(comm)    
from emp
where comm is not null and comm>0
;

-- 전체행을 구한다
select count(*) as from emp; -- 전체 사원의 수
select count(*) as from emp where job='MANAGER'; -- 매니저의 수
-- 매니저의 수, 평균급여, 최고급여
select count(*), avg(sal), max(sal) from emp where job='MANAGER';

-- 직무의 개수
select count(distinct job) -- 중복제거
from emp
;

------------- 특정 컬럼을 기준으로 그룹핑: Group by 컬럼

select deptno
from emp
group by deptno --order by deptno와 다르다.
;


-- 부서별 평균 월급
select deptno, avg(sal)
from emp
group by deptno
;


-- 각 부서별 인원을 부서별 인원이 많은 수대로 정렬
select deptno, count(*)
from emp
group by deptno
order by count(*) desc
;

-- 각 부서별 인원 및 커미션을 받는 사람.
select deptno, count(*)as "부서인원", count(comm) as"커미션받는사람 수"
from emp
-- where comm is not null and comm>0
group by deptno
;

------------------  HAVING : 그룹함수에 조건/ 그룹의 결과를 제한
-- 부서별로 그룹지은 후(GROUP BY), 그룹 지어진 부서별 평균 급여가 2000 이상인(HAVING) 
-- 부서번호와 부서별 평균 급여를 출력하는 경우
select deptno, round(avg(sal))
from emp
GROUP by deptno
HAVING avg(sal)>=2000  
-- having not avg(sal)>=2000
;

-- 부서별 급여의 최대값과 최소값을 구하되
-- 최대 급여가 2900이상인 부서만 출력합니다.
select deptno, max(sal), min(sal)
from emp
group by deptno
having max(sal)>=2900
order by deptno
;

 
-- 직무별 지표 : 사원의 수, 급여의 총합, 평균 급여, 최대최소
select job, count(*) || '명' as "사원 수",
            to_char(sum(sal)*1100, 'L99,999,999') as "급여 총합",
            to_char((avg(sal))*1100, 'L99,999,999') as "급여 평균", 
            to_char(max(sal)*1100, 'L99,999,999')as "최대 급여", 
            to_char(min(sal)*1100, 'L99,999,999') as "최소 급여"
from emp
where job != 'PRESIDENT'  
GROUP by job
order by job
;

-- 숫자함수
select ABS(-15.5) from dual; -- 절대값
select floor(15.7) from dual; -- 소숫점 절삭
select round(-15.8) from dual; -- 정수타입으로 반올림
select round(-15.693, 2) from dual; -- 몇번째 자리까지 표시할 것인지 선택
select log(10, 100 ) from dual; -- 로그값(지수)구해줌
select power(3, 2) from dual; -- 제곱

-- 문자함수

select concat('나는', '손흥민입니다.') from dual; -- 두개붙여줌
select concat('저의 이름은', ename) from emp where job='MANAGER'; -- 응용

select LOWER('ABCD') "Lowercase" from dual;
select lower(ename) from emp;

select lpad('page 1',15,'*') from dual; -- 별표로 가려줌
select rpad('910119-2',14,'*') from dual;

select substr('ABCEDFG',3,4) from dual; -- 3번째부터 4개 자른다
select rpad(substr('910119-2000000',1,8),14,'*') from dual; -- 응용

select Ltrim(' =from=','a') from dual; -- 제거
select Rtrim('    =from=====           ',' ') from dual;
select trim('=' from '=======from=======') from dual;
select trim(' ' from '   =====from====   ') from dual;

select REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL; -- J를 BL로 변경
select REPLACE('뽀준','준','선') "Changes" FROM DUAL;

-- 날짜함수
select sysdate from dual; -- 오늘날자
select sysdate+21 from dual; --14일 뒤

select add_months(sysdate, 4) from dual; -- 4개월 더해줌
select last_day(sysdate) from dual; -- 이달의 마지막날 알려줌..

-- 날짜 >문자
select sysdate from dual;
select sysdate, to_char(sysdate, 'YYYY-MM-DD') "바뀐날짜" from dual;
select sysdate, to_char(sysdate, 'YYYY-MM-DD Day dy')from dual;
select sysdate, to_char(sysdate, 'YYYY-MM-DD Day dy AM PM HH HH24 mi ss') from dual;
select to_char(sysdate, 'YYYY.MM.DD.  HH24:mi:ss') from dual; -- 네이버st

-- 숫자 > 문자
SELECT TO_CHAR (1230000) FROM DUAL;
select to_char(10000.123, 'L000,000.00') from dual;
select to_char(10000, 'L999,999.99') from dual;

select sal, to_char(sal*1100, 'L00,000,000') from emp;
select sal, to_char(sal*1100, 'L99,999,999') from emp;
select ename,sal, to_char(sal*1100*12+nvl(comm,0)*1100, 'L99,999,999') from emp order by sal desc; 

-- 문자 > 날짜
-- 2020.11.01
select TO_DATE('2019/09/05', 'YYYY/MM/DD') from dual;
select TO_DATE('2020.11.01.', 'YYYY.MM.DD.') from dual;

-- 오늘이 2020년 1월 1일에서 몇일이 지났는지 확인해보자 :)
select trunc(sysdate - to_date('20-01-01','YY-MM-DD')) from dual;

-- 문자 > 숫자
select TO_NUMBER('100.00', '999.99') * to_number('10.00', '99.99') from dual; 

--사용법

DECODE(컬럼,조건,TRUE 결과값,FALSE 결과값)


--예제

--TYPE 1이면 정규직 2면 비정규직
SELECT 
DECODE(TYPE,1,'정규직','비정규직') AS TYPE
FROM
EMP_TABLE


--주민등록번호(MY_NUM)이(가) 1또는3으로 시작하면 남자 아니면 여자
SELECT 
DECODE(SUBSTRING(MY_NUM,0,1),1||3,'남','여') AS GENDER
FROM
EMP_TABLE

-- DECODE 함수 : switch 문과 비슷
-- 사원 이름, 부서번호, 부서이름 출력
select ename, deptno,
decode(deptno,
        10, 'ACCOUTING',
        20, 'RESEARCH',
        30, 'SALES',
        40, 'OPERATIONS')
        as dname
from emp;


-- 8. 직급에 따라 급여를 인상하도록 하자. 
-- 직급이 'ANAlYST'인 사원은 5%, 'SALESMAN'인 사원은 10%, 'MANAGER'인 사원은 15%, 
-- 'CLERK'인 사원은 20%인 인상한다.

select ename, job, sal,
decode(job,
    'ANALYST', sal*1.05,
    'SALESMAN', sal*1.1,
    'MANAGER', sal*1.15,
    'CLERK', sal*1.2)   
     as sal2
from emp
;

-- CASE 함수
select ename, deptno,
    case
        when deptno=10 then 'ACCOUNTING'
        when deptno=20 then 'RESEARCH'
        when deptno=30 then 'SALES'
        when deptno=40 then 'OPERATIONS'
    end as dname
from emp
order by dname
;

1. 사용방법

ORDER BY [ASC|DESC]

--QUERY문의 맨뒤에 옵니다.

EX)

SELECT * FROM EMP ORDER BY JOB

-- JOB를 오름차순(어샌딩)으로 정렬하여 보여준다.

SELECT * FROM EMP ORDER BY JOB ASC

-- JOB를 오름차순(어샌딩)으로 정렬하여 보여준다.

SELECT * FROM EMP ORDER BY JOB DESC

-- JOB를 내림차순(디샌딩)으로 정렬하여 보여준다.

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

-- sal컬럼을 기준으로 오름차순으로 정렬하고 sal컬럼의 값이 같은 레코드는 다시

-- ename컬럼을 기준으로 오름차순으로 정렬하여 출력합니다.

※ 디폴트는 ASC 입니다.

※ 참고사항

오름차순:정순정렬:ASC

내림차순:역순정렬:DESC

2. 데이타형에 따른 정렬

문자열은 ASC면 A,B,C순으로 ASC코드값이 작은것부터이며

DESC면 C,B,A순으로 ASC코드값이 큰것부터나옵니다.

정수나 실수는 ASC면 전체수가 작은것부터이며

DESC면 전체수가 큰것부터나옵니다.

날짜는 ASC면 오래된 날짜부터 정렬되며

DESC면 최근의 날짜부터 정렬됩니다.

한글은 ASC면 가,나,다.... 순으로 정렬되며

DESC면 ....다,나,가 순으로 정렬된다.

NULL값은 연산이나 비교도 안됩니다. 하지만 정렬시에는 NULL값은 ASC면

무조건 맨뒤에 나오며 DESC면 무조건 앞에 나옵니다.

※ 무조건 맨뒤나 앞에 나올때의 널값은 순서가 입력순서는 순서대로입니다.

※ ASC일때 NULL 값을 맨앞에 하는 방법이 있습니다.

ex : order by name nulls first

ex : order by nvl(name,' ')

※ DESC일때 NULL 값을 맨뒤에 하는 방법이 있습니다.

ex : order by name nulls last

3. 특별한 사용법

※ ORDER BY는 ASC, DESC 키워드를 입력하지 않으면 자동으로 오름차순 정렬됩니다.

※ ORDER BY 뒤에 컬럼명을 , 간격으로 2개이상을 사용가능합니다.

ex : select * from table_name order by a,b;

* a를 오름차순으로 정렬한상태에서 a의 값이 중복인 레코드는 다시 b를 오름차순으로 정렬한다.

* a의 값이 중복값이 없을때는 b는 오름차순하지 않는다.

ORDER BY 뒤에 숫자가 나오면 현재 TABLE의 컬럼위치를 말합니다.

select * from emp order by 1 DESC;면 TABLE의 첫번째 컬럼으로 내림차순으로 정렬한다는 뜻입니다.

※ 0은 사용할수 없다.

ORDER BY 뒤에 앨리어스(별명)을 두어 정렬이 됩니다.

SELECT A AA,B FROM ORDER BY AA;

select name from goods order by 1;
-- 제대로된 query
-- 첫번째 출력컬럼으로 오름차순 정렬
select name from goods order by 0;
-- 잘못된 query
-- 0 이란 숫자는 order by사용할수 없습니다.

IN 연산자에 사용한 문자열 값들을 기준으로 원하는 순서로 출력하는 방법

SELECT * FROM TABLE_NAME WHERE ID IN('084', '081', '079', '077', '127')

ORDER BY DECODE(ID, '084', 1, '081', 2, '079', 3, '077', 4, '127', 5);

※ DECODE 함수로 원하는 값에 우선순위를 줄수 있습니다.

조회조건없는 쿼리 실행시 출력 정렬상태

SELECT IDX, NAME, AGE FROM TABLE_NAME; 이라고 할때 입력순서대로 출력되며

IDX가 PK라면 IDX를 기준으로 오름차순으로 정렬됩니다.

100% 입력된 자료 순서라고 장담할수 없습니다. 
별다른 실행계획이 없이 풀스켄 했다면 
rowid 순서로 조회가 됩니다. 
보통은 입력 순서대로 되지만 삭제작업이 빈번하게 일어나는 경우 나중에 입력된 자료가 먼저 나오기도 하는 것으로 알고 있습니다.. 

출처: https://aboutitdev.tistory.com/25 [About IT Dev]

-- 숫자

-- 급여의 오름차순 정렬 / 작은수>>>큰수
SELECT *
FROM emp
ORDER BY sal -- asc; 생략 가능 / 오름차순이 default
;

-- 급여의 내림차순 정렬 / 큰수>>>작은수
SELECT *
FROM emp
ORDER BY sal desc
;

SELECT *
FROM emp
ORDER BY comm -- null 값은 맨 아래로 내려간다 (가장큰수마냥..)
;




-- 문자 [a>z가 디폴트]
SELECT *
FROM emp
ORDER BY ename 
;



-- 날짜 [최근> 오래됨이 디폴트]
SELECT *
FROM emp
ORDER BY hiredate desc
;


-- 중복

SELECT *
FROM emp
ORDER BY hiredate desc, sal -- sal은 작은 순서로 [jame/ford\
;

-- 특정 데이터를 추출하는 where 절
-- select 컬럼명 from 테이블명 where 조건


-- 급여가 3000 이상인 사원의 리스트를 출력하자
select ename, job, sal
from emp
where sal >=3000
;

select ename, job, sal
from emp
where sal>4000
;

1)

2)

-- 10번 부서에 소속된 사원들의 리스트 출력
SELECT    *
FROM emp
where deptno=10
;

-- 문자 데이터 조회 '' 안의 대문자 소문자 구분함
SELECT empno, ename, sal
FROM emp
where ename = 'FORD'
;

-- 날짜 데이터 조회
SELECT *
FROM emp
where hiredate='81/11/17'
;

-- AND 연산자
-- 10번 부서 소속인 사원들 중에서
-- 직급이 MANAGER인 사람을 검색하여 사원명, 부서번호, 직급을 출력
SELECT ename, deptno, job
FROM EMP
WHERE DEPTNO=10 AND JOB='MANAGER';

-- OR 연산자
-- 10번 부서에 소속된 사원이거나
-- 직급이 MANAGER인 사람을 검색하여 사원명, 부서번호, 직급을 출력합시다.
SELECT ename, deptno, job
FROM emp
where deptno=10 OR JOB='MANAGER';

-- NOT 연산자
-- 부서번호가 10번이 아닌 사원의 이름, 부서번호, 직급을 출력
SELECT ename, deptno, job
FROM emp
WHERE NOT deptno=10;

-- BETWEEN 연산자 (column_name BETWEEN A AND B)
-- 2000에서 3000 사이의 급여를 받는 사원
SELECT *
FROM EMP
WHERE SAL>=2000 AND SAL<=3000;
--1987년에 입사한 사원을 출력
SELECT *
FROM EMP
-- WHERE HIREDATE >= '1987/01/01' AND HIREDATE <= '1987/12/31';
WHERE HIREDATE BETWEEN '1987/01/01' AND '1987/12/31';

-- IN 연산자
-- 커미션이 300이거나 500 이거나 1400인 사원 검색
SELECT *
FROM emp
-- where comm=300 or comm=500 or comm=1400
where comm in(300,500,1400);

-- LIKE연산자 ( column_name LIKE pattern )
-- 와일드카드(%) 사용하기
-- F로 시작하는 사람 찾기
SELECT *
FROM emp
WHERE ename LIKE 'F%';
-- D로 끝나는 사람 찾기
SELECT *
FROM emp
WHERE ename LIKE '%D';
-- 이름중에 A가 들어간 사람 찾기
SELECT *
FROM emp
WHERE ename LIKE '%A%';

-- 와일드카드(_) 사용하기
-- _는 한문자에 대해서만 와일드카드 역할을 한다.
-- 이름의 두번째 글자가 A인 사람
SELECT *
FROM emp
WHERE ename LIKE '_A%';


-- 찾으려는 이름이 F로 시작 하는 것은 알지만 그 뒤의 문자는 모를 경우
select *
from emp
-- where ename like 'F%' --F로 시작하고 뒤는 어떤 문자가 와도 상관 없음
-- where ename like '%S' --F로 시작하고 뒤는 어떤 문자가 와도 상관 없음
-- where ename like '%A%'   -- 이름에 A문자를 포함하는 이름을 검색
-- where ename like '_A%' -- 첫 번째 문자는 어떤 문자든 상관없고 두 번째 자리는 반드시 A가 오는 이름 검색
where ename like '__R%' -- 첫번째 세번째 문자는 아무거나 상관없고 세 번째 자리에 R이 오는 이름 검색.
-- 000000-1000000 >  '_______1%' = 주민등록번호에서 남자찾기
-- '1987/12/18' > '_____12%' = 생일이 12월인 사람 찾기




-- NOT LIKE
-- 이름에 A가 들어가지 않는 사람 찾기
SELECT *
FROM emp
WHERE ename NOT LIKE '%A%';

-- 커미션을 받지 않는 사원 검색
SELECT *
from emp
where comm is null or comm=0
;

-- 커미션을 받는 사원 검색
SELECT *
from emp
where comm is not null and comm>0
;

-- SELECT 절의 컬럼의 사칙연산
-- 임시테이블 dual : 컬럼은 x를 가지는 테이블
select 100+200, 200-100, 100*10, 100/10
from dual
;
SELECT ename, sal, sal*12
FROM emp
;


-- 연봉의 계산을 sal*12+comm으로 계산하자
SELECT ename, sal, sal*12, comm, sal*12+comm
FROM emp
;

-- null : 값은 존재하나 정해지지 않은 값
-- 연산이 불가 : 사칙/비교 연산

-- nvl 함수 : 컬럼의 값이 nulㅣ 일때 대체값으로 치환해준다.
-- nvl(컬럼, 대체값) 컬럼과 같은 타입의 대체값

select ename, comm, sal*12+comm,nvl(comm, 0), sal*12+nvl(comm, 0)
from emp;

-- 데이터 베이스 sql 내부의 문자열 처리는 대부분 ''
-- 별칭 처리는 "" but 에러날 수 있으니 영문처리 하자

select ename, comm, sal*12+comm sall2, nvl(comm, 0) as com, sal*12+nvl(comm, 0) "12개월 연봉"
from emp;

-- Concatenation 연산자의 정의와 사용
select ename || ' is a ' || job as "||연산자"
from emp;


-- DISTINCT 중복되는것을 생략해준다
-- DISTINCT 뒤에는 다른 컬럼을 쓸 수 없다!
select distinct deptno
from emp;

--2020.11.09
--select 기본

-- scott 계정으로 로그인
-- scott 계정이 소유한 테이블 객체를 확인
-- : tab > 테이블의 정보를 알려주는 데이터 딕셔너리
select*from tab;

-- DESC : 테이블 구조 확인
-- 테이블의 데이터를 검색하기 전 구조를 확인할 때
DESC emp;
desc dept;

select hiredate from emp;


-- 데이터의 조회 명령 : select
-- SELECT의 컬럼명,... FROM 테이블 이름(조회의대상) > 기본 문법
-- SELECT의 결과는 > TABLE이다.

SELECT
    *         -- 컬럼 이름들을 기술해준다. 기술하는 순서에 따라 결과도 순서에 맞게 출력이 된다.
FROM emp      -- 조회하고자 하는 테이블 이름을 기술 
;             --- SQL의 종료



-- 부서 테이블의 모든 데이터를 조회하자
SELECT  *
FROM dept
;


-- 부서테이블에서 > FROM
-- 부서의 이름과 위치를 출력 > SELECT

SELECT  dname, loc
FROM dept
;

SELECT  loc, dname
FROM dept
;

-- 모든 사원의 정보를 출력하자
SELECT    *
FROM emp
;

-- 사원의 이름, 사번, 직급(직무)를 포함하는 리스트 출력
SELECT ename, empno, job
FROM emp
;