oracle sql문 정리

           1. FROM

         2. WHERE

         3. GROUP BY

         4. HAVING

         5. SELECT

         6. ORDER BY

---

table 생성

create table guestbook(
    no number primary key,
    name varchar2(20) not null,
    password varchar2(20) not null,
    input_date date,
    title varchar2(50),
    contents varchar2(200))

---

조회

select ename as 이름, sal, job  from emp where sal>=1000 and sal<=2000; 

//sal 값이 설정 조건에 적합한 데이터를 ename 을 이름으로 별칭으로 설정한뒤 sal, job 정보와 함께 조회

select ename from emp where ename like '%S%'; //사원명 중 S가 포함되는 사원명을 조회

select ename from emp where ename like 'S%'; //사원명 중 S로 시작 되는 사원명을 조회

select ename from emp where ename like '_A%'; //두번째 글자가 A 인 사원명을 조회

select ename from emp where ename like '__R%';//세번째 글자가 R 인 사원명을 조회

select ename, job, sal, deptno from emp where (job='CLECK' or sal>2000) and deptno=10; 

//job 이 CLECK 또는 SAL 2000을 넘는 사원 중에서 DEPTNO 가 10인 사원 정보를 조회 : ()안을 우선순위로 처리

where salary between 1000 and 2000;

           //BETWEEN AND 연산은 =<, => 과 같은 말 >, <와 같은 미만,초과와 같은 표현은 AND연산으로 비교해야함

where commission_pct IS NOT NULL;  //IS NULL일 경우에는 값이 있는 것을 조회

---

연산

select avg(sal) from emp; //평균 

select round(avg(sal)) from emp;//반올림

select ceil(avg(sal)) from emp; //올림 123.12 ->124

select floor(avg(sal)) from emp; //내림 123.67-> 123

select deptno,round(avg(sal))as 평균월급 from emp group by deptno order by 평균월급 desc;

//내림 차순 <-> 오름 차순 = asc

select max(no) from board; //no 컬럼에 해당하는 최대값 <-> min(no)

select count(no) from board;//no 컬럼의 데이터 수를 카운팅

---

시간

select sysdate from dual; //sysdate : 현재시간

select sysdate,hiredate,months_between(sysdate,hiredate) from emp; //두 날짜 사이가 몇개월인지 반환

select hiredate,add_months(hiredate,3) from emp; //날짜의 3개월 후 : add_month(date,3)

select hiredate,trunc(hiredate,'month') from emp; //date 를 월 기준으로 날짜 버리고 반환

select sysdate,last_day(sysdate) from dual;// date 월의 마지막 날짜

select hiredate, to_char(hiredate,'yyyy/mm/dd') from emp; //to_char() to_date() 문자형 <-> 날짜형

---

데이터 관리

select ename,comm,nvl(comm,0) from emp;//NVL : null 값 처리

alter table test add(tel varchar2(30) not null);// 컬럼 추가

alter table test modify(tel varchar2(20) null);// 컬럼 정보 변경 not null->null

alter table test add(unique(ssn));//제약조건을 추가 --unique : null 을 허용, 중복인정 x

alter table test drop column tel;//기존 컬럼을 삭제

rename test to test1; //테이블 명 변경

truncate table test1; //테이블 데이터를 전체 삭제

update board set count=count+1 where no=5; //데이터 수정

insert into board(no,title,writer,password,content,w_date) 

values(board_seq.nextval,'테스트','박지성','13','맨체스터',sysdate); //데이터 삽입 (시퀀스 사용)

DELETE FROMEMP WHERE EMPNO = 7902 ; //사원번호가 7902번인 사람의 데이터가 삭제 되었습니다. 

DELETE FROMEMP WHERESAL < (SELECT AVG(SAL) FROM EMP) ; //평균급여보다 적게 받는 사원 삭제

DELETE FROM EMP ; //테이블의 모든 행이 삭제 됩니다.

---

시퀀스

create sequence s_empno; //생성
create sequence s_empno nocache;
drop sequence s_empno; //삭제

insert into test1(no,ssn,name,address) values(s_empno.nextval,'80','아이유','종로'); //사용 예 : 차례로 증가

---

Group by 와 having 절


SQL> select deptno,count(*) from emp group by deptno;

    DEPTNO   COUNT(*)

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

        30          6

        20          5

        10          3

이 중인 인원수가 4이상인 부서를 출력한다. 즉 group 함수에 의해 계산된 값에 대한 조건이다. 

SQL> select deptno,count(*) from emp group by deptno having count(*) > 4;

    DEPTNO   COUNT(*)

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

        30          6

        20          5

아래의 SQL은 group by 에 의해 만들어진 결과에서 deptno가 20 이상인 부서만 출력한다. 

SQL> select deptno,count(*) from emp group by deptno having deptno > 20;

    DEPTNO   COUNT(*)

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

        30          6

---

서브 쿼리

- 단일행 서브쿼리 : select문장으로부터 오직 하나의 행만을 검색하는 query
  예) 과장중에서 최대급여를 받는 사람
       SQL>select * from sawon where sapay = (select max(sapay) from sawon

               where sajob='과장') and sajob='과장';
- 다중행 서브쿼리 : select문장으로부터 하나 이상의 행을 검색하는 query
  예) 회사의 평균 급여보다 많은 사람
      SQL>select 8 from sawon where sapay > (select avg(sapay) from sawon);
      SQL>delete bbs_memo bm where bm.id in (select distinct cb.id from

              callplus_bbs cb, callplus_bbs sb 

              where sb.id=? and cb.grp_id=sb.grp_id and

              substr(cb.depth,1,length(sb.depth))

              =substr(sb.depth,1,length(sb.depth)));
   · in을 이용한 subquery
     SQL>select ename,jicode,sal from jicode in

             (select jicode from jikcode where jicode<='M3');
   · exists를 이용한 subquery
     SQL>select ename,jicode,sal from jikwon  j where

             exists (select * from jikcode where jicode<='M3' and jicode=ji.jicode);
  * exists는 exists안에 있는 행만 찾기 때문에 in보다 빠르다(결과값은 같음)
- 다중열 서브쿼리 : select문장으로 부터 하나 이상의 컬럼을 검색하는 query
  예) 이순신과 부서가 같고 직책도 같은 사람
      SQL>select * from sawon where (depth,sajob)=

              (select deptno,sajob from sawon where saname='이순신');

- from절상의 서브쿼리(inline view) : from절상에 오는 서브쿼리로 뷰처럼 작용
- 상관관계 subquery : 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에

  이용되는 처리방식
  즉, 외부쿼리의 컬럼명이 내부 쿼리의 조건절로 쓰이는 쿼리. 주쿼리에서 서브쿼리를

  참조하고 이 값을 다시 주쿼리로 반환하는것
  예) 사원을 관리할 수 있는 사원의 평균급여보다 급여를 많이 받는 사원의 정보를 출력
      SQL>select empno,ename,sal from emp e where sal>

              (select avg(sal) sal from emp where e.empno=mgr)
  예) 업무가 종업원 7369의 업무와 같고, 급여가 종업원 7876보다 많은 종업원
     SQL>select ename,job from emp where job=(select job from emp where

              empno=7369) and sal>(select sal from emp where empno=7876)
     SQL>select job,avg(sal) from emp group by job having avg(sal)=

             (select min(avg(sal)) from emp group by job)

참조 : 소나기와 별과 참세상 블로그

  예>
  select row_num,no,title,writer,password,content,count,to_char(w_date,'yyyy.MM.DD') 

from(select rownum as row_num,ceil(rownum/5) as page,no,title,writer,password,content,count,w_date 

from(select no,title,writer,password,content,count,w_date from board order by no asc) 

order by row_num desc);

//rownum 사용 : rownum- SQL 결과 집합에 대한 순번을 정해주는 역할, SQL 내에 사용가능한 가상 컬럼

---

조인 쿼리

1. 카티시안 곱 (Cartesial Product) 
- 조인 조건이 부적합하거나 조인 조건을 완전히 생략한 경우 행의 모든 조합을 표시하는 카티시안 곱이 생성된다.
첫번째 테이블의 모든 행이 두번째 테이블의 모든 행에 조인된다.
카티시안 곱은 너무 많은 행을 생성하므로 결과가 유용하게 사용되지 않는다.
특별히 모든 테이블에 있는 모든 행을 조합해야 하는 경우가 아니라면 where절에 유효한 조인 조건을 포함시켜야 한다.
적정 양의 데이터를 시뮬레이션하기 위해 많은 수의 행을 생성해야 하는 경우 등 일부 테스트에서는 유용하게 사용된다.

SQL> select last_name, department_name
2 from employees, departments;

2. 등가 조인 (equi join) - 단순 조인 or 내부 조인(inner join)이라고도 함
- 주로 Primary Key와 Foreign Key를 보조 수단으로 사용하여 조인
- departments 테이블과 employees 테이블은 department_id를 공통된 컬럼으로 가지고 있다.
따라서 이 컬럼을 where절에서 조건으로 지정해 준다면 등가 조인을 할 수 있다.
- 등가 조인의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋다.

SQL> select e.employee_id, e.last_name, d.department_id, d.location_id
2 from employees e, departments d
3 where e.department_id = d.department_id;

3. 비등가 조인 (non-equi join)
- 동등 연산자(=)가 아닌 연산자를 포함하는 조인
- between and, is null, is not null, in, not in
- ex) employees 테이블의 급여는 jobs 테이블의 min_salary와 max_salary 사이에 있어야 한다.

SQL> select e.last_name, e.salary, j.job_title
2 from employees e, jobs j
3 where e.salary between min_salary and max_salary;

4. 포괄 조인 (outer join)
- outer join은 조인 조건을 만족하지 않는 행도 반환한다.
- 연산자는 더하기 기호 (+) 이고, 조인시킬 값이 없는 조인측에 위치시킨다. 
- outer join 연산자는 표현식의 where절에서 한 편에만 올 수 있고, in, or 연산자는 사용할 수 없다.
- 아래 쿼리문은 사원이 없는 부서의 테이블 이름도 반환한다.

SQL> select e.last_name, e.department_id, d.department_name
2 from employees e, departments d
3 where e.department_id(+) = d.department_id;

- 4.1 Left Outer Join : 왼쪽 테이블에 조인시킬 컬럼의 값이 없는 경우 사용한다. (아래의 SQL문은 같은 의미)

SQL> select e.last_name, e.department_id, d.department_name
from employees e
left outer join departments d
on (e.department_id = d.department_id)

SQL> select e. last_name, e.department_id, d.department_name
2 from employees e, departments d
3 where d.department_id (+) = e.department_id;

- 4.2 Right Outer Join : 오른쪽 테이블에 조인시킬 컬럼의 값이 없는 경우 사용한다.

SQL> select e.last_name, e.department_id, d.department_name
2 from employees e
3 right outer join departments d
4 on (e.department_id = d.department_id);

SQL> select e.last_name, e.department_id, d.department_name
from employees e, departments d
where d.department_id = e.department_id (+)

- 4.3 Full Outer Join : 양쪽 테이블에 모두 Outer Join을 하는 것으로 Two-way Outer Join 이라고도 한다.

SQL> select e.last_name, e.department_id, d.department_name
from employees e
full outer join departments d
on (e.department_id = d.department_id)

5. 자체 조인 (self join)
- Equi Join과 동일하지만 하나의 테이블 안에서 조인이 일어난다. 
- 같은 테이블을 마치 2개의 테이블을 사용하는 것처럼 조인한다.
- ex) 각 사원의 관리자 이름을 찾으려면 Employees 테이블을 자체 조인해야 한다.
즉, 각 사원의 매니저 ID와 사원 ID가 동일한 사원을 찾아야 한다.

SQL> select worker.last_name || ' works for ' || manager.last_name
2 from employees worker, employees manager
3 where worker.manager_id = manager.employee_id;

6. 교차 조인 (cross join)
- cross join은 두 테이블의 상호간의 조합을 생성한다. 
- 두 테이블 사이의 카티시안 곱(cartesian product)의 결과와 동일하다.

SQL> select last_name, department_name
from employees
cross join departments

7. 자연 조인 (Natural Join)
- 두 테이블의 하나 이상의 공통된 컬럼(데이터 타입과 이름이 일치해야함)을 기반으로 조인연산을 수행한다.
- DEPARTMENTS 테이블과 LOCATIONS 테이블은 공통적으로 NUMBER(4) 타입의 LOCATION_ID 속성이 존재한다.
따라서 두 테이블은 LOCATION_ID 컬럼을 통해서 자연 조인이 가능하다.
- 일반적으로 등가 조인 (equi join)과 동일하다고 보면 된다. 
단지 where절로 조건을 거느냐 natural join 키워드로 조인을 거느냐의 차이? 
(자연 조인안에서 다시 where절로 조건을 걸수 있음)

SQL> select department_id, department_name, location_id, city
2 from departments
3 natural join locations;

- 7.1 Using절을 포함하는 조인
-- Using절을 사용하면 equi join에 사용될 열만을 지정할 수 있다.
-- Using절 안에 포함되는 컬럼에 alias를 지정하면 오류가 발생한다. 

SQL> select e.employee_id, e.last_name, d.location_id
from employees e join departments d
using (department_id)

- 7.2 On 절을 이용한 조인 
-- 자연 조인의 기본 조인 조건은 기본적으로 같은 이름을 가진 모든 열의 equi join 이다.
-- 임의 의 조건을 지정하거나 조인할 열을 지정하려면 on절을 사용해야 한다. 

SQL> select e.last_name emp, m.last_name mgr
from employees e join employees m
on (e.manager_id = m.employee_id)

테이블 Alias를 사용하면 데이터베이스의 액세스 속도를 높이고, SQL 코드의 양이 작아지므로 메모리 사용이 줄어든다

참조 : 레필리아의 잡동사니 블로그

---

데이터 베이스 출력시 순위 설정을 도와주는 함수 :  rank() over

SELECT empno, ename, sal, RANK() OVER (ORDER BY sal DESC ) as rk FROM emp;

-> 결과 : 랭크 컬럼 rk 가 중복 값일 경우 동점자 처리, ex> 1위가 2명일 경우 다음 순위는 3위로 바로 책정