SQL - Day2

•   JOIN  

: 원하는 데이터가 여러개의 테이블에 나눠져있을때 연결하는 방법

 

- inner join : 테이블을 연결할때 일치하는 것들만 나오도록 하는 것

- outer join: 일치하지 않아도 나오게 하는 것

 

-   오라클 조인  

 

 - cartesian product

: 조인 조건을 생략하거나 잘못된 경우

 -> 결과는 나오지만 데이터로 활용은 못함

 -> 전체를 합친 결과나 나옴 - 모든 데이터가 연결됨

 

 - equi 조인

 : 반드시 조건이 일치할때 (동등연산자로)

 

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 , 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼;    //동등연산자 사용

==> primary key 와 foreign key를 많이 사용

 

  * primary key를 갖고있는 곳이 master

  foreign key인 곳이 slave

  ==>반드시 master의 pk, uk(unique), null 만 참조할 수 있다.

 

  * fk 저장가능한 값 

   1) master의 값이어야함 

   2) null을 가질 수 있다.

 

  *테이블 이름에 alias를 사용할 수 있는데, 만약 alias를 사용했다면 테이블 이름대신 alias로 접근해야한다.

 

  - 검색조건 추가 가능

  WHERE emp.department_id = dept.department_id   ==> 조인조건
  AND last_name='Whalen';  ==> 검색조건

 

 - non-equi 조인

 : 정확히 일치하지 않아도 범위에 포함이 되는 것들 => 비교연산자 사용

 

SELECT last_name, salary, grade_level
FROM employees e, job_grades g
WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal;

 

 - outer 조인

 : 일치하지 않는 것들까지 나오는 조인

 

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 , 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼 (+);

 => +를 붙이는 쪽이 null이 생성됨

 -> 조인조건과 일치하는 않는 테이블1의 데이터도 결과에 포함되어 출력된다.

 * 한쪽만 가능함 / 둘다 +는 안됨

 

 - self 조인

 : 자기자신을 조인함 ex) 사원의 관리자 이름 - 둘다 같은 테이블일때

 

SELECT e.last_name 사원명, m.last_name 관리자명
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

 

 

-  ANSI 조인 

: from절에 조인조건을 씀 * 오라클 조인은 where절에 씀

 

 - cross 조인

 : 조인하는 각 테이블의 행 개수를 서로 곱한 결과가 반환

 

SELECT last_name,department_name, e.department_id
FROM employees e CROSS JOIN departments d;

 

 - natural 조인

 : 같은 이름을 가진 컬럼에 기반하여 동작이 된다 => 공통컬럼이 있어야함

 

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 NATURAL JOIN 테이블2
[WHERE 검색조건];

 

*alias를 사용해서 select하면 에러남 -> 알아서 찾는 것이기 때문에

 

 - using(컬럼)

 : 명시적으로 어떤 컬럼으로 조인할지를 지정

 

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 [INNER] JOIN 테이블2 USING(공통컬럼)
[WHERE 검색조건];

*alias 사용불가

 

- join ~ on절

: 동등/부등호 가능 - 특정 조건 가능

 

SELECT 테이블1.컬럼 , 테이블2.컬럼
FROM 테이블1 [INNER] JOIN 테이블2 ON 조인조건
[WHERE 검색조건];

 

  - 3개의 테이블 조인

SELECT e.last_name 사원명, d.department_name 부서명,
g.grade_level 등급
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id     //여기서 나온 결과로 밑과 조인 -> using(department_id)로도 변경가능
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

 

- LEFT OUTER | RIGHT OUTER | FULL OUTER 조인

: left -> table1에 있는 모든 것을 출력 /  right -> table2에 있는 모든 것을 출력 / full -> 양쪽

 

SELECT e.last_name 사원명, m.last_name 관리자명
FROM employees e LEFT OUTER JOIN employees m
ON e.manager_id = m.employee_id;  

=> oracle의 outer join과 같음

 

•   subquery  

: 하나의 SELECT 만으로 원하는 데이터를 조회할 수 없을 때 사용하는 방법

 

SELECT select_list     ===> main query
FROM 테이블
WHERE 컬럼명 연산자 (SELECT select_list
FROM 테이블);    ===> subquery

 

- subquery가 먼저 실행되고, 그 결과를 가지고 main query가 실행됨

*서브쿼리는 select, from, where 에 다 있을 수 있다.

 

-   단일행 서브쿼리 
 : 서브쿼리 실행 결과가 한 개의 행을 반환한다.
=, >, >=, <, <= ,!= 와 같은 비교 연산자

 

ex) WHERE salary >= (SELECT AVG(salary)
      FROM employees);  => 단일행

-  복수행 서브쿼리 
 : 서브쿼리 실행 결과가 복수 개의 행을 반환한다.
IN, ANY, ALL, EXIST 연산자

 

- IN

:동등 연산자(=) 방식

 

ex) WHERE salary IN ( SELECT salary
FROM employees
WHERE last_name IN ('Whalen','Fay') );  => whalen, fay의 salary 2개가 나옴

 

- ALL

: > 또는 < 같은 비교 연산자를 사용

 

 >  ==> 서브쿼리에서 반환된 최대값보다 커야함 

EX) 1000,2000,3000 이 나왔을때 1500이면 1000보다는 크지만 3000보다는 작기때문에 안됨

 * < 는 최소값보다 작아야함

 

- ANY

: > 또는 < 같은 비교 연산자를 사용

 

>  ==> 서브쿼리에서 반환된 최소값 보다 큰값

< ==> 최대값 보다 작은값

 

- EXISTS

: 결과가 존재하면/존재하지 않으면 메인쿼리를 실행해라

* not exists

 

WHERE EXISTS ( SELECT employee_id
FROM employees
WHERE salary > 500000 );

 

- 다중컬럼 서브쿼리

: 여러 개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교

 

WHERE (department_id, salary) IN ( SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id )

 

- 인라인 뷰

: 성능때문에 사용 / 쓸데없는 것들이 참여할 필요가 없음

 -> from 절에 사용

 

SELECT select_list
FROM ( 서브쿼리 ) alias
WHERE 조건식;

 

SELECT e.department_id, 합계, 평균, 인원수
FROM ( SELECT department_id, SUM(salary) 합계, AVG(salary) 평균 ,
COUNT(*) 인원수
FROM employees
GROUP BY department_id ) e, departments d
WHERE e.department_id = d.department_id
ORDER By 1;

 

* 필요한 형태만 보여주는 것이 '뷰'

* 이건 더 많은 것이 참여함 -> 위처럼 인라인뷰 사용하면 필요없는 데이터의 참여를 줄일 수 있음

SELECT e.department_id , SUM(salary) 총합, AVG(salary) 평균, COUNT(*) 인원수
FROM employees e , departments d
WHERE e.department_id = d.department_id
GROUP BY e.department_id
ORDER BY 1;

 

•   DML  

: 테이블에 새로운 데이터를 저장(INSERT)하거나 삭제(DELETE) 또는 수정 (UPDATAE) 및 병합(MERGE)할 때 사용하는 데이터 조작어

 

*TCL

- COMMIT : sync를 맞춰줌

- ROLLBACK: 작업을 취소함 

*commit, rollback은 명시적으로 해줘야함

 

-   INSERT  

 

 - 단일행 INSERT문

: 행이 하나씩만 만들어짐

 

 - 컬렴명을 지정할 수도 있고, 지정하지 않을 수도 있음

 * 지정하지 않으면 NULL이 들어감

 * 컬럼명을 지정하면 모든 컬럼값을 지정하지 않아도 되지만 컬럼명을 지정하지 않으면 모든 컬럼명의 값을 순서대로 넣어줘야한다.

 

INSERT INTO 테이블명 [(컬럼명,컬럼명2,...)]
VALUES ( 값, 값2, ... );

 

 - 복수행 INSERT문

: 행이 하나씩만 만들어짐

 

INSERT INTO 테이블명 [(컬럼명,컬럼명2,...)]
Subquery;

 

ex)

CREATE TABLE mydept
AS
SELECT * FROM dept
WHERE 1=2;

 

 - 다중 테이블 다중행 INSERT

: 테이블을 다르개해서 삽입 가능

 

INSERT ALL
[WHEN 조건식 THEN]
INTO 테이블1 VALUES ( 컬럼명,컬럼명2,...,컬럼명n)
[WHEN 조건식2 THEN]
INTO 테이블2 VALUES ( 컬럼명,컬럼명2,...,컬럼명n)
Subquery;

 

-   UPDATE  

: 데이터를 수정하기 위해 사용

 

UPDATE mydept
SET dname='영업',loc='경기'
WHERE deptno = 50;

 

- subquery를 이용한 UPDATE

 

UPDATE mydept
SET dname= ( SELECT dname
FROM dept
WHERE deptno = 10)
,loc= ( SELECT loc
FROM dept
WHERE deptno=20)
WHERE deptno = 60;

 

-  DELETE  

: 데이터를 삭제하기 위해 사용

 

DELETE FROM mydept
WHERE deptno = 50;

 

- subquery를 이용한 DELETE

 

-  MERGE  

* 나중에.. 따로 공부해서 정리하기

 

 

•   TRANSACTION  

: 여러 DML작업을 하나의 작업처럼 실행되는 논리적인 작업

 

- 이러한 경우

 

 가. 제주도 여행

  - 비행기 예약(insert) / 숙소 예약(insert) / 렌트카 예약 등

  => 3가지 작업은 모두 성공해서 제주도 여행을 성공적으로 하던지/ 모두 취소해서 여행을 안가던지..

 

- 각각의 작업 하나하나(insert, update etc..)가 트랜잭션이 될 수 있다.

- 명시적으로 트랜잭션을 종료해줘야한다.