SQL 수업 (5/25 오후)
서브쿼리 (계속)
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
=> 실행 가능
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees
GROUP BY department_id);
=> 실행 불가
SELECT employee_id, last_name, salary
FROM employees
WHERE salary IN (SELECT AVG(salary) FROM employees
GROUP BY department_id);
=> 실행 가능
* 서브쿼리의 결과값이 null일 때, 비교연산자를 사용하면 결과를 반환하지 않는다.
SELECT employee_id, last_name, manager_id, salary
FROM employees
WHERE manager_id = (SELECT manager_id FROM employees
WHERE last_name = 'Adam');
※ Adam이라는 사람이 존재하지 않을 때(null)
* Multiple-Row Subqueries : 여러 행 서브쿼리
>> ANY : 아무거나(어느 것 중 하나라도)
ex)
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > ANY (SELECT AVG(salary) FROM employees
GROUP BY department_id);
※ = ANY와 IN은 같다.
ex)
SELECT employee_id, last_name, salary
FROM employees
WHERE salary IN (SELECT AVG(salary) FROM employees
GROUP BY department_id);
=
SELECT employee_id, last_name, salary
FROM employees
WHERE salary = ANY (SELECT AVG(salary) FROM employees
GROUP BY department_id);
>> ALL : 모두
ex)
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > ALL (SELECT AVG(salary) FROM employees
GROUP BY department_id);
>> NOT IN : 같지 않다.
※ NOT IN으로 검색할 때, 서브쿼리 값에 NULL이 포함되어 있으면 결과를 반환하지 않는다.
왜냐하면, IN은 OR 연산인데 앞에 NOT이 붙으면 부정이 되어 AND 연산으로 바뀌기 때문.
따라서, WHERE절에 IS NOT NULL(등 NOT NULL 조건)을 습관적으로 써 주는 것이 규칙.
SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN (SELECT mgr.manager_id
FROM employees mgr
WHERE mgr.manager_id IS NOT NULL);
* Set Operators : 집합 연산자
>> 집합 연산은 기본적으로 결과값의 중복을 제거하고, 자동으로 행을 기준으로(행 단위로) 정렬해서 보여준다.
>> MINUS : 교환 법칙이 성립하지 않는다.
>> 데이터 타입이 서로 다른 테이블끼리의 합집합 방법
ex)
SELECT employee_id, hire_date, TO_CHAR(null) AS department_name
FROM employees
UNION
SELECT department_id, TO_DATE(null), department_name
FROM departments;
>> 집합을 정렬하는 방법(편법)
1) 순서를 정하기 위해 행을 추가한다. (한글, 영문, 숫자 등)
ex)
SELECT employee_id, hire_date, TO_CHAR(null) AS department_name, 'B'
FROM employees
UNION
SELECT department_id, TO_DATE(null), department_name, 'A'
FROM departments
ORDER BY 4;
2) 행에 별칭을 준다. (아무거나)
ex)
SELECT employee_id, hire_date, TO_CHAR(null) AS department_name, 'B' A_D
FROM employees
UNION
SELECT department_id, TO_DATE(null), department_name, 'A'
FROM departments
ORDER BY 4;
3) 추가한(의미없는) 행을 출력하지 않는 방법
ex)
COLUMN A_D NOPRINT
SELECT employee_id, hire_date, TO_CHAR(null) AS department_name, 'B' A_D
FROM employees
UNION
SELECT department_id, TO_DATE(null), department_name, 'A'
FROM departments
ORDER BY 4;
Manipulating Data
* Data Manipulation Language : DML (데이터 조작어)
>> SELECT / INSERT / UPDATE / DELETE
- Transaction(트랜잭션) 단위로 동작.
※ Transaction(트랜잭션)의 개념
conn hr/hr | → | Session 시작 | |
SELECT |
|
|
Transaction 단위 |
INSERT |
→ |
Transaction 시작 |
|
UPDATE |
|
|
|
DELETE |
|
|
|
... |
|
|
|
COMMIT / ROLLBACK |
→ |
Transaction 종료 |
>> INSERT문
ex)
INSERT INTO departments
VALUES(70,'Public Relations', 100, 1700);
- DB는 엑셀과 다르게, 행의 순서를 DBMS가 알아서 정한다(제어하지 못함). 논리적 구조.
- Session 단위로 작업하므로, Commit 하지 않으면 공유하지 않는다.
- NULL값 입력 방법
1) 암시적(Implicit) 방법
INSERT INTO departments(department_id, department_name)
VALUES (30, 'Purchasing');
2) 명시적(Explicit) 방법
INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);
- 치환 변수를 이용한 Insert
INSERT INTO departments(department_id, department_name, location_id)
VALUES (&deptno, '&dept_name', &loc_id);
>> Session 접속 중 (같은 유저로) 다시 접속하는 경우, 자동으로 commit.
>> 테이블 만들기(DDL 명령어)
ex)
CREATE TABLE sales_reps
AS
SELECT employee_id id, last_name name, salary, commission_pct
FROM employees
WHERE 1=2; => WHERE절을 일부러 틀리게 적으면, 데이터는 제외하고 테이블 구조만 가져와서 생성
※ CREATE와 같은 DDL 명령어는 자동 커밋.
>> UPDATE문
- WHERE절을 잘못 작성하면 아무것도 적용되지 않음.
- WHERE절을 작성하지 않으면 테이블의 모든 데이터가 업데이트됨. 주의!
* 트랜잭션의 특징
- 서로 다른 유저가 같은 테이블의 서로 다른 행을 수정하는 것은 가능
- 같은 테이블의 같은 행을 수정할 경우, 먼저 접속한 유저가 우선권을 가짐.
※ 자동 커밋되는 경우가 엄청 많이 존재한다. 툴에 따라 다르고 등등.
>> DELETE문
- UPDATE문과 비슷한 특징을 가짐.
- WHERE절을 잘못 작성하면 아무것도 적용되지 않음.
- WHERE절을 작성하지 않으면 테이블의 모든 데이터가 삭제됨. 주의!