[코딩] Circle Square

[목차]

10. DDL

11. DML

12. 제약조건

13. Sequence

[내용]

10. DDL(Date Definition Language) : 테이블 생성, 수정, 삭제, 자동 commit

    1) 테이블 생성 

create table 테이블명(
     칼럼명 데이터 타입(크기)
      ....            );

create table board( 
no number, 
id varchar2(20), 
title varchar2(20), 
content varchar2(2), 
hit number, 
regdate date);

create table myemp(
empno char(5), ename varchar2(10), hiredate date, sal number(10), memo varchar2(10));

    2) 테이블 변경 - 추가

alter table 테이블명
add (칼럼명 데이터타입(크기));

alter table board
add (email varchar2(20));

    3) 테이블 변경 - 수정

alter table 테이블명
modify (칼럼명 데이터타입(크기));

alter table board
modify (content varchar2(20));

    4) 테이블 삭제

drop table 테이블명;

drop table emp2;

  
11. DML(Data Manipulation Language) : 데이터 조작어

    1) insert into : data 추가

insert into 테이블명
values(값,...);

insert into board values(1,'jang','test','test1',0,sysdate,null);

insert into board values(2,'kim','title','ccocnocnco',0,sysdate,'kwdd@naver.com');

insert into board values(6,'choi','oracle','안녕하세요?????',0,sysdate, null);

insert into myemp values('00001', 'scott', sysdate, 3000,'신입');

     - 선택 칼럼의 데이터만 입력하기

insert into emp (empno, ename, hiredate) values(1111,'이이이',sysdate);
insert into emp (empno, ename, hiredate, deptno) values(7777,'율곡', sysdate, 100);

    2) update

update 테이블
set
where

update board
set title ='oracle'
where no=1;

update board
set title='oracle', hit=hit+1
where no=4;

    - set절 서브쿼리 이용하여 조건에 맞는 값 update

update myemp
set sal=(select sal
         from emp
         where empno=7934)
where empno=7369;

update myemp
set sal=(select avg(sal)
		from emp
		where mgr=7698)
where ename is null;

update myemp
set sal =sal+300
where sal<(select avg(sal)
           from emp
           where mgr=7698);
-> where절 서브쿼리 이용 조건에 맞는 값 update

    3) delete

delete (into) from 테이블명
where

delete from board
where no=5;

delete board
where id is null;

    - where절 서브쿼리 이용 조건에 맞는 값 delete

delete from myemp
where sal>(select avg(sal)
           from myemp);

    4) 삽입된 데이터 저장하기

commit;

12. 제약조건

    - 테이블 생성시 제약조건 추가

create table 테이블명(
칼럼명 데이터타입(크기) 제약조건,
  ....                 );

create table emp2(id varchar2(10) primary key,
                  name varchar2(10) not null,
                  pass varchar2(10));

create table emp2(
    empno number(5),
    ename varchar2(10) not null,
    deptno varchar2(10),
    sal number,
    tel varchar2(10),
    job varchar2(10),
    constraints emp2_empno_pk primary key(empno));

    - 테이블에 적용된 제약 조건 확인

desc user_contraints

select OWNER,CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
from user_constraints;

OWNER
---------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME                                              CO TABLE_NAME
------------------------------------------------------------ -- -----------------------------------------------------
SCOTT
SYS_C006997                                                  P  EMP

SCOTT
SYS_C007004                                                  C  EMP2

SCOTT
BOARD_ID_PK                                                  P  BOARD

SCOTT
EMP2_ID_FK                                                   R  EMP2

SCOTT
EMP2_SAL_CK                                                  C  EMP2

SCOTT
SYS_C007013                                                  P  DEPT2

SCOTT
EMP2_JOB_CK                                                  C  EMP2

     - 제약 조건 변경 (primary key)

alter table board
add constraint board_no_pk primary key(no);

     - 제약 조건 변경 (unique key)

alter table emp2
add constraint emp2_tel_uk unique(tel);

     - 제약 조건 변경 (check)

alter table 테이블
add constraint 제약조건명 check(조건);

alter table emp2
add constraint emp2_sal_ck check(sal between 5000 and 7000);

alter table emp2
add constraint emp2_job_ck check(job in('developer', 'siger', 'teacher'));

     - 제약 조건 변경 (foreign key)

alter table 테이블명
add constraiont 제약조건명 foreign key(칼럼) references 테이블명2(칼럼2);
                        primary key(칼럼);

alter table emp2
add constraint emp2_deptno_fk foreign key(deptno) references dept2(decode);

alter table emp2
add constraint emp2_id_fk foreign key(id) references board(id);

    - 제약조건 삭제

alter table emp2
drop constraint emp2_empno_pk;

13. Sequence
    - 시퀀스 생성

create sequence 시퀀스명;

create sequence board_seq;

    - 시퀀스 시작값 설정

create sequence tb_order_seq
start with 2020111100;

    - 현재 시퀀스 확인.

select 시퀀스명.currval form dual;

select tb_order_seq.currval from dual;
->현재 sequence 확인.

    - 다음 시퀀스

select board_seq.nextval from dual;

insert into board(no, title,regdate,hit)
            values(board_seq.nextval, 'text', sysdate,0);

14. on delete cascade :  미사용 권장

   - on delete cascade추가하여 foreign 제약조건 추가.

alter table tb_order
add constraint order_fk foreign key(id) references tb_customer(id) on delete cascade;

- on delete cascade foreign키 삭제 시 error 발생

drop table tb_customer;

- on delete cascade constraint 삭제

drop table tb_customer cascade constraints;

alter table tb_order
add constraint id_notnull not null(id);
-> error

alter table tb_order
modify id varchar2(10) not null;
-> not null 조건 추가

create table myemp
as
select empno, ename,sal, mgr, hiredate
from emp
where 1=1;
-> 테이블 생성
-> 데이터 들어감

create table myemp2
as
select empno, ename,sal, mgr, hiredate
from emp
where 1=2;
-> 테이블 생성
-> 데이터 안들어감

insert all
     into myemp values(7777,'aaaaa',1000,7475,sysdate)
     into myemp2 values(7777,'aaaaa',1000,7475,sysdate)
select * from dual;
-> myemp, myemp2에 date insert

insert all
     into myemp2 values(1111,'bbbb',1000,7475,sysdate)
     into myemp2 values(2222,'ccccc',1000,7475,sysdate)
select * from dual;
-> myemp2에 2개 date insert

   - Data Dictionary : 읽기전용
   - rollback;

   - 모든 테이블 삭제

SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;

9. 서브쿼리(subquery) : 여러개의 select 문장을 하나로 합쳐있는것
 1) 서브쿼리
  - select문에 삽입된 select문, sql문에 삽입된 다른 sql문
  - select문에만 사용되지않고 insert, update, delete, select문 모두 사용가능
  - 바깥쪽의 쿼리를 메인쿼리 or 기본쿼리라 한다.
  - sql문에 삽입된 쿼리를 서브쿼리라 하며 반드시 괄호로 묶어 주어야한다.
  - 서브쿼리가 메인쿼리 실행전에 한번 싱행된다.
  - 서브쿼리의 결과가 메인쿼리에서 실행된다.
  - select문에서는 select, from, where, having절 모두에서 삽입가능.
  
 2) 서브쿼리의 종류
     ① 단일행 서브쿼리 : 서브쿼리의 실행결과가 행하나, 열 하나로 리턴되는 것, 메인쿼리에서 비교할 때 = 연산자를

      이용하는 것이 가능

select ename, sal, hiredate, deptno
from emp
where sal>=(select max(sal)
            from emp
            where deptno=20);

select *
from emp
where job=(select job
           from emp
           where ename='SMITH');

select ename, deptno, sal
from emp
where sal>(select max(sal)
           from emp
           where deptno=20);

select employee_id, first_name||' '||last_name name, hire_date
from employees
where hire_date like '05%'
       and hire_date < (select hire_date
       			from employees
       			where first_name = 'Lisa');

	   
or
where to(hire_date,'YYYY')='2005'

select e.first_name, e.salary, d.department_name
from employees e, departments d
where e.department_id = d.department_id
      and d.department_name = 'Sales'
	  and salary < (select avg(salary)
	                 from employees
	                 where department_id=100
	                 group by department_id);

select m.employee_id, m.last_name, m.hire_date, m.salary
from employees e, employees m
where e.last_name = 'De Haan'
      and e.manager_id = m.employee_id;
	  
	  
select employee_id, last_name, hire_date, salary
from employees
where e.last_name = (select manager_id
                     from employees
                     where last_name = 'De Haan');

select d.department_name, avg(e.salary)
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name
having avg(e.salary) <= (select avg(salary)
	                     from employees
	                     where department_id = 30
	                     group by department_id);

     ② 다중행 서브쿼리 : 서브쿼리의 실행결과가 열은 하나지만 행이 여러개 반환되는 것. = 연산자를 이용할 수 없다.
         1) in 연산자

select ename, sal, deptno
from emp
where sal in(select sal
             from emp
             where job='MANAGER');

select *
from emp
where deptno in (select deptno
              from emp
              where job='MANAGER');

select ename, empno
from emp
where deptno in (select deptno
                from emp
                where ename like '%T%');

select *
from emp
where sal in (select min(sal)
             from emp
              group by deptno);

         2) all 연산자 : 모두 만족해야한다.
         >all(서브쿼리) : 서브쿼리의 모든 값보다 커야함. 서브쿼리의 최대값보다 큰값.

select ename, sal, deptno
from emp
where sal >all(select sal
             from emp
             where job='MANAGER')
order by sal;

         <all(서브쿼리) : 서브쿼리의 최소값보다 작은 값.

select ename, sal, deptno
from emp
where sal <all(select sal
             from emp
             where job='MANAGER')
order by sal;

   
         3) any : 결과 값 중 한개만 만족하면 됨.
         >any(서브쿼리) : 서브쿼리의 어느값중 하나만 만족하면 됨. 서브쿼리의 최소값보다 큰 값.

select ename, sal, deptno
from emp
where sal >any (select sal
             from emp
             where job='MANAGER')
order by sal;

         <any(서브쿼리) : 서브쿼리의 최대값보다 작은 값.

select ename, sal, deptno
from emp
where sal <any (select sal
             from emp
             where job='MANAGER')
order by sal;

     ③ 다중 열(컬럼) 서브쿼리 : 서브쿼리의 칼럼을 다수로 가져가 비교

select *
from emp
where (deptno, sal) in (select deptno, min(sal)
             from emp
              group by deptno);

select employee_id, last_name, salary, department_id
from employees
where (department_id, salary) in (select department_id, max(salary)
                 from employees
                 group by department_id)
order by salary desc

     => deptno도 같아야 할 경우

     => 칼럼 수가 하나면 deptno가 달라도 조회된다.

     ④ 상호연관 서브쿼리
     - 메인쿼리의 칼럼을 서브쿼리에서 사용해야하는 경우.
     - 메인쿼리의 row가 어떤 칼럼값을 가지고 있느냐에 따라서 서브쿼리의 결과가 달라진다.
     [실행순서]
     1) 메인쿼리에서 현재 작업중인 row의 칼럼값을 가져온다.
     2) 메인쿼리에서 가져온 값을 이용해서 서브쿼리를 진행.
     3) 서브쿼리에서 실행한 결과를 다시 메인쿼리로 전달 후 메인쿼리를 실행.
     4) 메인쿼리에서 사용하는 테이블의 전체 row에 대해서 1~3번까지의 작업을 반복수행.

select ename, sal, deptno
from emp main
where sal > (select avg(sal)
from emp e
where e.deptno = main.deptno);

    ⑤ View

    - view생성 권한부여

conn system/manager
grant create view to scott;

    - view생성

create view myempview
as
select d.dname, e.ename, e.sal, e.hiredate
from emp e, dept d
where e.deptno= d.deptno;

    - view생성시에는 컬럼에 alias가 꼭 필요.

create view maxsaldata
as
select deptno, max(sal) maxsal
from emp
group by deptno;

create view avgsal
as
select deptno, avg(sal) 평균급여
from emp
group by deptno;

    - view 사용

select e.deptno, e.empno, e.ename, e.sal, s.평균급여
from emp e, avgsal s
where e.deptno=s.deptno(+);

    ⑥ inline view
     - from절에 정의하고 사용하는 서브쿼리.
     - 서브쿼리를 이용해서 from절에서 사용할 가상의 테이블을 정의할 수 있는 데, 이를 inline view라 한다.
     - 하나의 테이블에 데이터가 많이 있는 경우 from정에 정의하면 비효율적일 수 있으므로 필요한 행과 열만

        from절에서 사용할 수 있도록 한다.
     [정의방법]

select
from (select
      from
      where
      group by
      having      )alias

select e.deptno, e.empno, e.ename, e.sal, avgt.avgsal
from emp e, (select deptno, avg(sal) avgsal
             from emp
             group by deptno) avgt
where e.deptno = avgt.deptno and
      e.sal>avgt.avgsal;

     => from절에 삽입되는 서브쿼리를 사용하는 경우 반드시 서브쿼리 뒤에 alias를 정의해야한다.(테이블명으로 사용)

          서브쿼리안에서도 계산의 결과로 만들어지는 칼럼은 반드시 alias를 정의해야한다.(칼럼명으로 사용)
 

    ⑦ ton-n 서브쿼리
    - row num을 활용해서 순위와 연관있는 서브쿼리.
  

    - rownum사용

select rownum, ename, sal
from emp;

select 월, 입사자수
from (select substr(hire_date,4,2) 월, count(*) 입사자수
      from employees
      group by substr(hire_date,4,2)
      order by 입사자수 desc)
where rownum<4
order by 월;


select to_char(hire_date,'MM') 월, count(employee_id) 입사자수
from employees
group by to_char(hire_date,'MM')
order by 입사자수 desc

select first_name, salary, department_name
from (select e.first_name, e.salary, d.department_name
from employees e, departments d
where department_name = 'IT'
       and e.department_id = d.department_id
order by salary desc)
where rownum<4;

select rownum, ename, sal
from emp
where rownum<4
order by sal desc;

    ->원하는 결과 X

select rownum, ename, sal
from (select *
      from emp
      order by sal desc)
where rownum <4;

    ->원하는 결과 O

    1) row_number() over (order by '순위 기준 칼럼' desc) 
     -> 동률자 순차적 순번

select row_number() over (order by sal desc) 순위, ename, sal, deptno
from emp;

      순위 ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
         1 KING                       5000         10
         2 FORD                       3000         20
         3 SCOTT                      3000         20
         4 JONES                      2975         20
         5 BLAKE                      2850         30
         6 CLARK                      2450         10
         7 ALLEN                      1600         30
         8 TURNER                     1500         30
         9 MILLER                     1300         10
        10 ADAMS                      1300         20
        11 WARD                       1250         30
        12 MARTIN                     1250         30
        13 JAMES                       950         30
        14 SMITH                       800         20
        15 율곡                        500         90
        16 이이이                      500

    2) rank() over (order by '순위 기준 칼럼' desc)

    -> 동률자 같은 순번 + 다음 순서 동률자 계산O

select rank() over (order by sal desc) 순위, ename, sal, deptno
from emp; 

     순위 ENAME                       SAL     DEPTNO
--------- -------------------- ---------- ----------
        1 KING                       5000         10
        2 FORD                       3000         20
        2 SCOTT                      3000         20
        4 JONES                      2975         20
        5 BLAKE                      2850         30
        6 CLARK                      2450         10
        7 ALLEN                      1600         30
        8 TURNER                     1500         30
        9 MILLER                     1300         10
        9 ADAMS                      1300         20
       11 WARD                       1250         30
       11 MARTIN                     1250         30
       13 JAMES                       950         30
       14 SMITH                       800         20
       15 율곡                        500         90
       15 이이이                      500

    3) dense_rank() over (order by '순위 기준 칼럼' desc) 
    -> 동률자 같은 순번 + 다음 순서 동률자 계산

select dense_rank() over (order by sal desc) 순위, ename, sal, deptno
from emp; 

      순위 ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
         1 KING                       5000         10
         2 FORD                       3000         20
         2 SCOTT                      3000         20
         3 JONES                      2975         20
         4 BLAKE                      2850         30
         5 CLARK                      2450         10
         6 ALLEN                      1600         30
         7 TURNER                     1500         30
         8 MILLER                     1300         10
         8 ADAMS                      1300         20
         9 WARD                       1250         30
         9 MARTIN                     1250         30
        10 JAMES                       950         30
        11 SMITH                       800         20
        12 율곡                        500         90
        12 이이이                      500

    4) ntile('그룹 갯수') over (order by '순위 기준 칼럼' desc) 
     -> 그룹갯수의 수로 균등한 숫자로 순위 줌. 전체 레코드/그룹개수 
     -> 나머지는 위에 부터 순차적으로 들어감.

select ntile(3) over (order by sal desc) 순위, ename, sal, deptno
from emp; 

      순위 ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
         1 KING                       5000         10
         1 FORD                       3000         20
         1 SCOTT                      3000         20
         1 JONES                      2975         20
         1 BLAKE                      2850         30
         1 CLARK                      2450         10
         2 ALLEN                      1600         30
         2 TURNER                     1500         30
         2 MILLER                     1300         10
         2 ADAMS                      1300         20
         2 WARD                       1250         30
         3 MARTIN                     1250         30
         3 JAMES                       950         30
         3 SMITH                       800         20
         3 율곡                        500         90
         3 이이이                      500

    5) 위에 합수 over 구문에 partition by '그룹핑' 하여 그 그룹안에서 각각 순위를 정한다.

select rank() over( partition by deptno order by sal desc) 순위, ename, sal, deptno
from emp;

      순위 ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
         1 KING                       5000         10
         2 CLARK                      2450         10
         3 MILLER                     1300         10
         1 SCOTT                      3000         20
         1 FORD                       3000         20
         3 JONES                      2975         20
         4 ADAMS                      1300         20
         5 SMITH                       800         20
         1 BLAKE                      2850         30
         2 ALLEN                      1600         30
         3 TURNER                     1500         30
         4 MARTIN                     1250         30
         4 WARD                       1250         30
         6 JAMES                       950         30
         1 율곡                        500         90
         1 이이이                      500

8. 조인(join) : 여러 테이블을 연결해서 필요한 데이터를 조회하는 방법
    - 오라클 조인 : 여러테이블 연결조건을 where절에 정의
    - ANSI 조인(표준)

    1) 개요 : 정규화된 여러 테이블의 데이터를 이용해서 데이터를 조회해야하는 경우 테이블 조인을 한다.
    - 조인은 관계형 데이터 베이스에서의 중요기능.
    - 기본키와 외래키 관계를 이용해서 테이블을 조인해야한다.
    - 조인을 하는 경우 반드시 조인 조건 정의.
  
    2) 조인 사용방법
    - from절에 테이블을 정의할 때 alias를 이용해서 정의.

select d.dname, e.ename, e.sal
from emp e, dept d
where e.deptno=d.deptno and sal>2000;

select e.empno, e.ename, e.job, e.deptno, d.deptno, d.dname
from emp e, dept d
where e.deptno=d.deptno;

select d.deptno, d.dname, l.city
from dept d, locations l
where d.loc_code=l.loc_code;

     -> deptno : 기본키

  - select절에서 두 개 이상의 테이블에 있는 칼럼을 추가하는 경우 from절에서 정의한 alias를 이용해서 칼럼의 모호성 제거.
  - where절에서는 반드시 조인조건을 정의해야한다. 조인을하면 외래키 테이블의 외래키와 기본키 테이블의 기본키를 비교하고 일치하는 레코드의 원하는 값을 가져오므로 조건을 정의하지않으면 데이터를 조회할 수 없다.
  - 사용되는 모든 테이블의 조인조건을 정의해야한다. (테이블이 세개면 조인 조건은 두개 정의)

select e.ename, d.dname, e.sal, l.city
from emp e, dept d, locations l
where e.deptno=d.deptno and d.loc_code=l.loc_code;

    - 검색 조건 추가
      where 조인조건
      and 조건추가

select e.empno, e.ename, e.sal, d.dname, d.loc_code
from emp e, dept d
where job='SALESMAN'
and e.deptno=d.deptno;

select ename, sal, hiredate
from emp e, dept d, locations l
where l.city = 'SEOUL'
        and e.deptno=d.deptno and d.loc_code=l.loc_code;

select e.ename, e.sal, e.job, e.hiredate, e.comm
from emp e, dept d, locations l
where e.deptno=d.deptno and d.loc_code=l.loc_code
and l.city='DALLAS' and e.sal>=1500;

select d.department_name, count(e.employee_id)
from employees e, departments d
where e.department_id = d.department_id
group by department_name;

select e.first_name || e.last_name || '의 연봉은 ' || e.salary
|| ' 입니다.' as 결과
from employees e,departments d
where e.department_id = d.department_id
and d.department_name = 'IT'
order by salary asc;

select e.employee_id, e.first_name, j.job_title, d.department_name
from employees e, departments d, locations l, jobs j
where e.department_id=d.department_id
and d.location_id = l.location_id
and e.job_id=j.job_id
and l.city ='Seattle';

select j.job_title job, sum(e.salary) 급여
from employees e, jobs j
where j.job_title not like '%Representative%'
      and j.job_id=e.job_id
group by j.job_title
having sum(e.salary)>30000
order by sum(e.salary);

select d.department_name 부서명, count(e.department_id) 인원수
from employees e, departments d
where e.department_id = d.department_id
      and hire_date <'2005-1-1'
group by d.department_name;

select d.department_id 부서번호, d.department_name 부서명, count(e.employee_id) 인원수,
 max(e.salary) 최고급여, min(e.salary) 최저급여, floor(avg(e.salary)) 평균급여, sum(e.salary) 급여총액
from employees e, departments d
where e.department_id = d.department_id
group by d.department_id, d.department_name
having count(e.department_id)>=3
order by 인원수 desc;

select j.job_title job, sum(e.salary) 급여
from employees e, jobs j
where j.job_title not like '%Representative%'
      and j.job_id=e.job_id
group by j.job_title
having sum(e.salary)>30000
order by sum(e.salary);

select d.department_name, floor(avg(salary)) 평균연봉
from employees e, departments d
where e.department_id=d.department_id
group by d.department_name
having avg(salary)>= 5000
order by 평균연봉 desc; 

  3) 조인의 종류(oracle 조인)
   ① Equip 조인 : 두개 이상의 테이블에서 칼럼 값이 정확하게 일치하는 경우 조회.
    조인조건 : where 기본테이블.기본키 = 외래키테이블.외래키
        (테이블은 alias 사용가능)

select e.ename d.dname
from emp e, dept d
where e.deptno = d.deptno

   ② outer 조인
   - 조인 적용했을 때 조인 조건을 만족하지않는 데이터를 조회하고 싶을때 사용.
   - (+) 연산자를 한쪽 칼럼에 초가해서  사용.
   - 만족하지 않아도 한쪽테이블의 모든 데이터를 조회해서 볼 수 있도록 자원.
   - (+)가 추가되면 만족되지 않는 조건을 임의로 추가해서 비교하므로 (+)가 투가되지않은 테입르의 레코드가 

     출력된다.
   [구문]

select 테이블명1(alias1명).칼럼며으 테이블2 alias2
from 테이블명1 alias1, 테이블명2 alias2
where 테이블명1(alias1).칼럼명(+) = 테이블명2(alias).칼럼명(+)

    - dept table에 null을 추가하여 emp table과 비교하여 emp table의 null data를 조회한다.

    - Equip 조인은 조인문에 detno가 일치하지않거나 null인 경우 조회되지않는다.

select e.empno, d.dname, e.ename, e.sal
from emp e, dept d
where e.deptno = d.deptno(+);

select nvl(m.ename,'관리자없음') 관리자명, count(e.empno) 인원수
from emp e, emp m
where e.mgr=m.empno(+)
group by m.ename;

select j.job_title, count(e.employee_id)
from employees e, jobs j
where e.job_id=j.job_id(+)
group by j.job_title;

    - emp table에 null을 추가하여 dept table과 비교하여 dept table의 null data를 조회한다.

select e.empno, d.dname, e.ename, e.sal
from emp e, dept d
where e.deptno(+) = d.deptno;

select d.department_name, count(e.employee_id)
from employees e, departments d
where e.department_id(+)=d.department_id
group by d.department_name
order by d.department_name;

   ③ non-Equip 조인(등급표 조인)
    - 두테이블에서 비교해야하는 칼럼 값이 정확하게 일치하지않고 사이 값인 경우 조인하는 방법. =연산자를 

      사용하지않은 조인.

select e.empno, e.sal, g.grade
from emp e, salgrade g
where e.sal between g.losal and g.hisal;

   ④ self 조인
    - 같은 테이블에서 조인하는 경우.
    - 하나의 테이블의 다른 칼럼을 가지고 조인하며 서로 다른 테이블인 것처럼 작업할 수 있다.
    - 조인조건은 equip조인과 동일하게 정의

select e.empno 사원번호, e.ename 사원명, e.mgr 관리자코드, m.ename 관리자명
from emp e, emp m
where e.mgr=m.empno;

select e.employee_id, e.first_name, nvl(m.first_name,'관리자 없음') 관리자명
from employees e, employees m
where e.manager_id = m.employee_id(+)
      and e.first_name like '_t%';

select e.first_name, e.salary
from employees e, employees m
where e.manager_id = m.employee_id
      and e.salary>m.salary;

[목차]

7. 함수

    1) 문자처리 함수 
        ① length() 
        ② sum(), max(), min(), count(), avg(), count(*) 
        ③ lower(), initcap(),upper() 
        ④ dual 
        ⑤ concat(A,B)
        ⑥ instr('문자열', '찾을 문자',m번째 부터 검색, 찾은 문자의 n번째 위치)  
        ⑦ substr('문자열', m번쨰 부터 짜름, n개 글자) 
        ⑧ replace('문자열', 바꿀문자, 바뀔문자) 
        ⑨ lpad('문자열', 전체 자리수, 공백문자)  
            rpad
        ⑩ ltrim('문자열', '지울문자') 
            rtrim
            trim
    2) 숫자자리 함수  
        ① round() 
        ② ceil() 
        ③ floor() 
    3) 날짜 함수  
        ① sysdate 
        ② months_between(A,B)  
        ③ add_month(A,B)  
        ④ next_day(A,B) 
        ⑤ last_day() 
    4) 변환함수  
        ① to_char(A,'9.99')  
        ② to_char(A,'YYYY-MM-DD')  
    5) 조건함수  
        ① decode(칼럼, 값1, 동일하면 출력할 값1 
                             값2, 동일하면 출력할 값2 
                       나머지 경우 출력할 값  )  
        ② case when 조건식1 then  출력할 값1 
            when 조건식2 then 출력할 값2 
            else 나머지 경우 출력할 값 
            end 별칭 
    6) null처리 함수  
        ① nvl(A,B)  
        ② nvl2(A,B)  

[내용]

7. 함수 
    1) 문자처리 함수

        ① length()

select ename, length(ename)
where length(ename)>=6;

        ② sum(sal), max(), min(), count(), avg(), count(*)

select ename, sal, max(sal)
from emp
group by ename;

select deptno, count(empno)
from emp
group by deptno;

        ③ lower(), initcap(첫글자만 대문자),upper()

select empno, ename, lower(job), deptno
from emp
where ename = 'SCOTT'

        ④ dual : test 1행짜리 테이블.

select *
from dual; 

        ⑤ concat(A,B) : || 문자열 연결. 
            concat(A, concat(B,C))

select concat(ename,concat('의 급여',concat(sal,'만원')))
from emp
where sal<1000;

        ⑥ instr('문자열', '찾을 문자',m번째 부터 검색, 찾은 문자의 n번째 위치) 
           -> 위치반환

        ⑦ substr('문자열', m번쨰 부터 짜름, n개 글자) 
           -> 글자 추출 -1은 맨뒤.

select ename, hiredate
from emp
where substr(hiredate,1,2)=81;

select empno, ename, job, sal, deptno
from emp
where substr(ename,1,1) > 'K' and substr(ename,1,1) < 'Y';

        ⑧ replace('문자열', 바꿀문자, 바뀔문자)

        ⑨ lpad('문자열', 전체 자리수, 공백문자) 
           -> 왼쪽에 공백문자를 가짐, 전체 자리수에서 부족한 공백은 설정한 공백문자로 채운다.

            rpad : 오른쪽에 공백문자를 가짐.

select ename, job, lpad(sal,5,'*') as sal
from emp
where sal<=2000;

          ⑩ ltrim('문자열', '지울문자') : 왼쪽 문자를 지움

              rtrim('문자열', '지울문자') : 오른쪽 문자를 지움 
              trim(''from'문자열') : 양쪽 문자를 지움 

select ename, job, ltrim(lpad(sal,5,'*'),'*') as sal
from emp
where sal<=2000;

select ltrim(job,'A'), ltrim(sal,1)
from emp
where deptno=10;

    2) 숫자자리 함수 
        ① round() : 반올림

select deptno, round(avg(sal)) avg
from emp
where job<>'PRESIDENT'
group by deptno
having avg(sal)>1800
order by deptno;

        ② ceil()  : 올림 
        ③ floor   : 버림 

    3) 날짜 함수 
        ① sysdate : 오늘날짜. 연산가능 
        ② months_between(최신날짜, 먼날짜) 
        ③ add_month(,더할달수) 
        ④ next_day(지정일,'금') 요일 
        ⑤ last_day(지정일) : 마지막날

select e.first_name, e.salary, e.hire_date, d.department_name
from employees e, departments d
where e.department_id=d.department_id
      and months_between(sysdate,hire_date) >12*18;

    4) 변환함수 
        ① to_char(   ,'9.99') 
        ② to_char(   ,'YYYY-MM-DD')

select to_char(hiredate,'MM')월, count(*)입사자수
from emp
group by to_char(hiredate,'MM')
order by to_char(hiredate,'MM');

    5) 조건함수 
        ① decode(칼럼, 값1, 동일하면 출력할 값1

                             값2, 동일하면 출력할 값2

                       나머지 경우 출력할 값  )

select ename, deptno, decode(deptno, 10, '전산실',
                              20, '총무과',
                              30, '기획실',
                              '신입')
from emp;

        ② case when 조건식1 then  출력할 값1
            when 조건식2 then 출력할 값2
            else 나머지 경우 출력할 값
            end 별칭

select ename, sal, case when sal>=5000 then '1등급'
                 when sal>=2000 and sal<5000 then '2등급'
                 when sal>=1000 and sal<2000 then '3등급'
                 else '신입'
                 end  등급표
from emp;

    6) null처리 함수 
        ① nvl(칼럼or표현식, null인 경우 적용할 값 or 표현식(연산,함수호출))

select nvl(to_char(department_id),'No Department')부서번호, round(avg(salary),0)평균급여
from employees
group by department_id
having avg(salary) >6000;

        ② nvl2(칼럼 or 표현식, null이 아닌 경우 적용할 값, null인 경우 적용할 값)

select ename, mgr, nvl2(mgr,'담당','상위자') 관리자
from emp
order by 관리자;

[목차]

5. Query 

    1) select from

    2) where

    3) order by

6. 그룹화 (group by, having)

[내용]

5. Query 
    1) 기본 select 
    [형식] 
    ① 전체데이터 조회

select * 
from 테이블명; 

    - SQL문은 대소문자 구분하지않음. 
    - ; 문장의 종료를 의미. 여러줄로 명령입력 가능. 
    - select문에서 * 사용시 모든 칼럼표시의미 
   
    ② 원하는 칼럼만 조회

select 칼럼명1, 칼럼명2 as alias, 칼럼명3 alias 
from 테이블명 

    - 칼럼명 대신 alias 사용가능. 
       as와 함께 쓰거나 한칸 띄고 정의. 
       alias에 공백사용을 원할 경우 ""큰따옴표 사용."급여의 합계" 

select (sal*12)+comm "급여의 합계" 
select ename'짱' 
select ename||'의 급여는'||sal||'입니다'as Info 

    - select 문에서 칼럼을 연산의 결과로 정의가능.

    - 여러개의 칼럼을 연결하여 하나의 칼럼 정의가능. 
      || 연산자를 이용하여 표현가능. 

  - 칼럼의 값으로 null 저장가능. 
    null은 0이나 공백이 아닌 지정되지 않은 값. null은 연산 불가. 연산 후도 null. 
  - ''작은 따옴표는 오라클에서 문자열이나 날짜 데이터를 표현할때 사용.

  - 중복된 데이터를 제거하기 위해서 [distinct]를 select절에 추가할 수 있다. 
  

    2) select문에 조건 추가하기 (where)
    [형식]

select[distinct] 칼럼명1, 칼럼명2...[별칭] 
from 테이블명 
where 조건식1(칼럼명 연산자 적용할 값) and(or) 조건식2 

    - 검색결과 제한 
    - from절 다음에 정의. 
    - where절은 조건식이 true인 데이터만 조회. 
    - where절에 사용할 수 있는 비교연산자. 
      =, >, >=, <, <=, (<>, !=, ^=) 
    - where절에 날짜나 문자열 사용시 ''작은 따옴표 사용. 
    - 오라클은 1970년 부터 Date를 셈. 
    - 나머지는 1900년 부터 Date를 셈. 
    - 조건을 여러개 정의 시 where절에 and, or 연산자 함께사용.

    - and : 모든 조건이 모두 만족할 경우(모두 true)

select empno,ename,job,sal
from emp
where job='SALESMAN' and sal>=1500;

select deptno, ename, job, sal, hiredate, deptno
from emp
where sal>=2800 and job='MANAGER';

   - or : 조건 중 하나만 만족할 경우(하나라도 true)

select empno, ename, sal, hiredate
from emp
where hiredate>='81/1/1' or sal >=5000;

    - and 조건 대신 사용할 수 있는 연산자. 
      칼럼명 between A and B : A와 B 사이값 
      하나의 칼럼에 여러 조건을 적용할 경우.

select ename||':'||job as "name : job"
from emp
where hiredate between '81/1/1' and '81/12/31' and job='MANAGER';

select ename, job, sal, deptno
from emp
where sal between 1300 and 1700;

    - or 조건 대신 사용할 수 있는 연산자. 단, 하나의 칼럼에 여러 조건을 줄 경우. 
      칼럼명 in (값1, 값2, 값3) 
      부서가 10, 20 부서인 조회

select ename, sal, deptno
from emp
where deptno in (10,20) and sal >=2000;

select deptno, ename, job, sal, hiredate
from emp
where empno in (7902, 7788, 7566);

select deptno, ename, job, sal, deptno
from emp
where job not in ('MANAGER', 'CLERK', 'ANALYST');

    - 조건으로 정의한 값과 정확하게 일치하지 않은 조건을 적용하고 싶은 경우 like 연산자 이용하며 
      대표문자를 함께 사용해서 조회해야한다. 
      _ : 한글자 대표 
      % : 0 or 하나이상의 문자 대표

select * from emp
where ename like '%A%' 

where ename like 'A%' 

where ename like '%A' 

where ename like '_A__' 

    - null 데이터 검색하기 
      is null : null인 데이터

      is not null : null이 아닌 데이터

select ename, mgr, case when mgr is null then '상위자'
					else '담당'
					end 관리자
from emp;

select first_name, last_name, salary, commission_pct, salary+commission_pct
from employees
where commission_pct is not null
order by salary+commission_pct desc;

    - not : 조건에 대한 부정 
      not in (' ', ' ')

select department_id, avg(salary)
from employees
where department_id not in(40,50)
group by department_id
order by avg(salary) desc;

    3) 데이터 정렬 (order by)
    [형식]

select * from 테이블명 where 조건식

order by 정렬하고 싶은 칼럼 desc|asc 

    - desc(내림차순 정렬) : 데이터의 크기가 큰 데이터부터 출력 (10~1, ㅎ~ㄱ, Z~A, z~a)
    - asc(오름차순 정렬) : 데이터의 크기가 작은 데이터부터 출력 (1~10, ㄱ~ㅎ, A~Z, a~z)
    - 생략 시(default) 오름차순(asc)

select empno, ename, sal
from emp
where sal between 2000 and 3000
order by sal desc;

    - 정렬기준은 여러개로 정의 가능.

select * from 테이블명 where 조건식

order by 칼럼1 desc, 칼럼2 asc; 

  - order by절은 무조건 select문의 마지막절에 정의한다. 
  - alias 사용가능.

6. 그룹화 (group by, having)
     [구문]

select 칼럼명 그룹함수()  
칼럼의 동일한 값끼지 그룹함수를 연산한다. 
from
where
group by 칼럼명(alias사용불가) 
having 조건(그룹함수를 이용한 조건이 필요한 경우)(alias사용불가)

select deptno, count(deptno)
from emp
where sal>2000
group by deptno
having count(deptno)>=2
order by deptno;

select deptno, job, count(empno)
from emp
group by deptno, job
order by deptno;

select department_id, avg(salary)
from employees
group by department_id
having avg(salary)>=10000;

      - 테이블에 저장된 데이터를 그룹으로 나누어서 그룹별 합계, 평균, 최대값, 최소값, 레코드 개수등을 구할 수 

        있는 기능 (데이터의 집계)
      - group by절에 그룹화하고 싶은 컬럼 정의 
      - group by절을 추가하지않고 일반 칼럼명과 그룹함수를 같이 사용할 수 없다. 
      - group by를 하는 경우 select문에 group by절에 명시한 칼럼이외의 칼럼을 정의할 수 없다. 
      - where절에 그룹함수를 사용할 수 없다. 
      - where절에는 그룹화하기 전에 적용할 조건을 정의 
      - 그룹화하고 난 후 결과를 조건으로 사용하는 경우 having절을 사용 
       => 그룹함수를 써서 조건을 정의해야하는 경우는 무조건 having절에 정의. 
      - 실행순서 : from -> where -> group by -> having -> select -> order by 
      - group by 2개 이상 가능 
       => select절 칼럼순서와 group by절 순서가 일치해야함. 

[목차]

1. intro 

2. 계정 생성

3. 기본개념

    1) 데이터베이스

    2) DBMS(DataBase Management System)

    3) 관계형 데이터 베이스 
    4) 테이블(Table)
    5) 컬럼(Column)
    6) 레코드(Record)
    7) 기본키(Primary key)
    8) 외래키(Foreign Key)

    9) 부모 테이블(Parent table)
    10) 자식 테이블(Child table)

4. SQL(Structured Query Language)

    ① DDL (Data Definition Language)

    ② DML (Data Manipulation Language)

    ③ DCL (Data Control Language)

    ④ TCL(Transaction Control Language)

    ⑤ Query

1. intro
    1) Oracle 설치(11g, 64bit)

    2) 컴퓨터 이름을 영문으로 변경해야함.
 
    3) DBMS(DataBase Management System) 데이터베이스 관리 시스템
    정형화된 Data format (ANSI : American National Standards Institute)

        ex) Oracle, MSSQL, MYSQL, DB2 
    비정화된 NOSQL

        ex) Mogo DB

    4) Run SQL Command Line 실행.
 
    5) 문제발생 시 확인사항
    Serivce - OracleXETNSListener - 재실행
              - OracleServiceXE
 
2. 계정 생성

conn system/manager

 - id가 system, password가 manager인 계정에 연결.

create user scott identified by tiger; 

 - id가 scott, password가 tiger인 계정 생성.
 - SQL문은 ;으로 종결
 - System문은 Enter

grant connect, resource to scott; 

 - scott계정에 권한 추가.

alter user 아이디 identified by 패스워드; 

 - 기존 유저의 비밀번호 변경 

alter user 아이디 account unlock; 
alter user 아이디 account lock; 

 - lock은 계정 정보 변경을 못하도록 막는다. lock 상태 시 unlock후 가능하다. unlock -> lock 자동 변경시간은 default 30일이다.

show user

 - 접속 중인 계정확인

commit; 

 - 변경사항 적용

select * from tab; 

 - 모든 테이블 확인

set linesize 300 

 - 화면 표시 linesize 수정

set pagesize 300 

 - 화면 표시 pagesize 수정

desc 테이블명 

 - 테이블 구조 확인

3. 기본개념 
    1) 데이터베이스 : 기업, 개인이 운영하는 시스템에서 발생하는 데이터를 정해진 형태로 저장.

    2) DBMS(DataBase Management System) : 효과적으로 데이터를 관리, 유지보수하기 쉽게 만들어진 프로그램.
     : 데이터 조회, 수정, 삽입, 삭제관리.
     : 기존사용 File System의 단점을 보완하기 위해 개발.
     : 데이터 중복 최소화, 데이터 일관성 유지.
     : 데이터 공유가능.
     : DBMS 종류 - 오라클, MYSQL,MSSQL, DB2...

    3) 관계형 데이터 베이스
     : 예로 오라클.
     : 데이터 구성시 2차원 테이블로 구성.
     : 데이터 처리를 위해 SQL사용.
    

   
    4) 테이블(Table) : 데이터 저장 단위(Relation)
    5) 컬럼(Column) : 데이터 구성항목(attribute)
    6) 레코드(Record) : 컬럼 집합으로 구분할 수 있는 최소한의 데이터(tuple)
    7) 기본키(Primary key) : 테이블내에서 레코드를 구분하기 위한 식별키. 중복불가. 공백없어야함.
      : 테이블 내 중복 데이터는 테이블 정규화를 통해 데이터를 분리
      : 정규화된 테이블 이용하여 원하는 데이터 참조 가능해야함.

      : 정규화된 테이블내 데이터 레코드를 구분하는 키를 기본키라 함. 이 기본키를 다른 테이블에서 참조가능.

                   ex)아이디, 사번, 게시번호

    8) 외래키(Foreign Key) : 다른 테이블의 기본키를 이용해서 사용하는 칼럼을 외래키라한다.

    9) 부모 테이블(Parent table) : 기본키를 가진 테이블을 부모 테이블이라 한다.
    10) 자식 테이블(Child table) : 부모테이블의 외래키를 정규화한 테이블을 자식 테이블이라 한다.

 
4. SQL(Structured Query Language) : 데이터 베이스에서 원하느 데이터를 조회, 삽입, 수정, 삭제 시  사용하는 언어.
    1) 종류

    ① DDL (Data Definition Language) : 정의 (테이블 제작, 변경, 삭제, 초기화)

    - create, alter, drop, truncate

    ② DML (Data Manipulation Language) : 조작 (테이블 내 데이터의 조회, 삽입, 수정, 삭제)

    - select, insert, update, delete

    ③ DCL (Data Control Language) : 제어(권한, 제어 비정상/ 완료시 처리 )

    - grant, revoke, commit, rollback

    ④ TCL(Transaction Control Language) : DCL 중 트랜잭션 Control 언어

    - commit, rollback

    ⑤ Query : 데이터에 다양한 조건을 적용하여 검색하는 기능 (DML 중 select문)

    2) DB 작업을 위한 관리
    ① 관리자 계정접속
    ② 계정생성
    ③ 생성계정에 권한부여. 롤(role)적용(connect,resource)
    ④ 생성계정 접속
    ⑤ 데이터를 추가

<tip>
 - 붙여넣기 : 오른쪽 마우스 클릭
 - [] : 생략가능
 - 속성 -> 줄 바꿈 선택사용 uncheck
 - oracle은 1번 부터 시작한다.
 - error발생 시 error code를 구글검색.