Day31 Database

22/12/05

📌 7. 서브쿼리(Subquery)

📂다중컬럼 서브쿼리

: 서브쿼리로부터 메인쿼리로 여러 컬럼을 기준으로 값이 반환되는 경우
  메인쿼리의 좌변에도 다중컬럼을 작성해야 함(좌변과 우변이 짝이 맞아야 함!)

• (예제) 다중컬럼서브쿼리 + 다중행서브쿼리

✍ 자기 자신이 매니저가 아닌 직원 출력하시오.

select last_name
from employees
where employee_id not in (select manager_id
							from employees);

❌ [결과가 나오지 않는 원인? ] -- 오류가 아니라 결과가 0개
: 서브쿼리로부터 반환되는 값리스트에 null값이 포함되어 있는 경우,
  메인쿼리에 and의 성격을 가지는 비교연산자 사용시 메인쿼리 결과도 null임.

• 서브쿼리만 돌려보자 -> null값 하나 있음(사장은 매니저가 없음)
  -> null값때문에 다 걸려서 true가 나오지 않음.
  

⭕ [해결] 다중행 서브쿼리인 경우 메인쿼리에 and의 성격을 가지는 비교연산자를 사용해야 하는 경우,
              서브쿼리로부터 반환되는 null값을 제외시켜줘야 함.

select last_name
from employees
where employee_id not in (select manager_id
							from employees
                            where manager_id is not null); // 89개

---

✏️ <연습문제>

1. employees 테이블에서 Abel과 동일한 부서에 소속된 사원들의 last_name과 hire_date를 출력하되 비교의 대상인 Abel은 제외하시오.
2. employees 테이블에서 평균 이상의 급여를 받는 사원들의 employee_id, last_name, salary를 출력하되 급여를 기준으로 오름차순 하시오.
3. employees 테이블에서 last_name에 ‘u’가 포함된 사원과 같은 부서에 근무하는 모든 사원의
   employee_id, last_name을 출력하시오.
4. employees 테이블과 departments 테이블을 사용하여 구문을 작성하시오. location_id가 1700인 부서에 소속된 사원들의 employee_id, last_name, department_id, job_id를 출력하시오.
5. employees 테이블에서 평균 이상의 급여를 받으면서 last_name에 ‘u’가 포함된 사원과 동일
   한 부서에 소속된 사원들의 employee_id, last_name, salary를 출력하시오.
6. employees 테이블에서 본인이 매니저의 역할을 하는 사원들의 employee_id, last_name을
   출력하시오.
7. employees 테이블과 departments 테이블을 사용하여 구문을 작성하시오. 직원이 소속되어 있지 않은 빈 부서의 department_id, department_name을 출력하시오.

---

📌 8. 데이터조작어(DML)

• <SQL구문의 유형>
  데이터질의어(DQL) : select
  데이터조작어(DML) : insert, update, delete
  데이터정의어(DDL) : create, alter, drop, truncate
  데이터제어어(DCL) : grant, revoke
  트랜잭션제어어(TCL) : commit, rollback, savepoint

---

📂 데이터 삽입 (insert)

: 테이블에 새로운 행을 추가하는 명령어

[문법] insert into 테이블명 [(컬럼명1, 컬럼명2, 컬럼명3, ...)]
         values (값1, 값2, 값3, ... );

• 테이블의 컬럼명을 작성하지 않아도 됨.
  하지만 생략했다면 values절에서 값리스트를 작성할 때 컬럼명을 순서대로 빠짐없이 적어야함

desc departments;

select *
from departments;

🔹 (insert 예제1) insert 작업시 테이블명 뒤에 컬럼리스트 생략시에는
                       values절에 테이블의 기본 컬럼 순서대로 모든 값을 나열해야 함.

insert into departments
values (280, 'Java', 108, 1700);

🔹 (insert 예제2) insert 작업시 테이블명 뒤에 컬럼리스트를 작성한 경우에는
                       values절에 컬럼 순서, 컬럼 개수를 맞춰서 값 나열해야 함.

insert into departments(DEPARTMENT_NAME, LOCATION_ID, MANAGER_ID, DEPARTMENT_ID)
values ('Java', 1700, 201, 290);

🔹 (insert 예제3) null값을 암시적(자동)으로 삽입하는 방법

insert into departments(department_id, department_name)
values (300, 'Mysql');

🔹 (insert 예제4) null값을 명시적(수동)으로 삽입하는 방법

insert into departments
values (310,'Oracle',null,null);

🔹 (insert 예제5) insert + 서브쿼리(subquery)
      : 다른 테이블로부터 데이터를 복사하는 작업이므로 한번에 여러 행을 삽입할 수 있음.

💻 sales_reps 예제 테이블 생성하기

create table sales_reps
(id int, 
 name varchar(20),
 salary int,
 commission_pct double(20,2));
 
 desc sales_reps;
 
insert into sales_reps(id, name, salary, commission_pct)
	select employee_id, last_name, salary, commission_pct
	from employees
    where job_id like '%rep%';
    
select *
from sales_reps;

🔹 (insert 예제6) insert + 서브쿼리(subquery)
      : employees 테이블과 구조가 동일한 copy_emp 테이블 생성 후,
        employees 테이블의 107명의 사원 정보를 copy_emp 테이블에 복사할 예정

💻 copy_emp 테이블 생성

create table copy_emp
as select*
	from employees
    where 1 = 2;
    
desc copy_emp;

select *
from copy_emp;

💻 copy_emp 테이블에 데이터 복사하기
: copy_emp 테이블에 employees 테이블에 있는 107명의 사원 정보 동일하게 삽입하기

insert into copy_emp
	select *
    from employees;
    
select*
from copy_emp;

🔹 (insert 예제7) 다중행 insert

insert into departments
values (320,'HTML',126,1700),
	   (330,'CSS',127,null);
select *
from departments;

📂데이터 수정 (update)

: 테이블의 특정행을 수정하는 명령어

[문법] update 테이블명
        set 컬럼명1 = 값1[, 컬럼명2 = 값2, ...]
        [where 조건문];

🔸 (update 예제1)

update employees
set department_id = 50
where employee_id = 113;

select employee_id, last_name, department_id
from employees
where employee_id = 113;

-> 변경됐는지 확인 (department_id : 100->50)

🔸 (update 예제2)

💥 자동커밋 해제하기 : [Query] - [Auto-Commit Transactions] 체크 해제

• where절 없이 update한 결과 모든행이 변경됨. (작업을 실수함)

update copy_emp
set department_id = 110;

• 작업 결과 확인(미리보기, 자동저장 기능이 꺼져있기 때문에 저장 X)

select*
from copy_emp;

• 작업 취소하기

rollback;

• 작업 취소 확인

select*
from copy_emp;

💥 자동커밋 설정하기 : [Query] - [Auto-Commit Transactions] 체크하기

🔸 (update 예제3) update + 서브쿼리(subquery)

: copy_emp 테이블의 113번 사원의 job_id와 salary를 employees 테이블에 있는
  205번 사원의 job_id, salary와 동일하게 변경하는 작업

update copy_emp
set job_id =(select job_id
			 from employees
             where employee_id = 205),		-- 205번의 job_id 찾아 와
	salary =(select salary
			 from employees
             where employee_id = 205) 		-- 205번의 salary 찾아 와
where employee_id = 113;

select employee_id, last_name, job_id, salary
from copy_emp
where employee_id = 113;

🔸 (update 예제4) update + 서브쿼리(subquery)
     location_id가 1800인 부서에 소속된 사원들의 급여를 10% 인상하시오.

update copy_emp
set salary = salary * 1.1
where department_id = (select department_id
					   from departments
                       where location_id = 1800);

select employee_id, last_name, salary, department_id
from copy_emp
where department_id = (select department_id
					   from departments
                       where location_id = 1800);

📂 데이터 삭제 (delete)

: 테이블의 특정행을 삭제하는 명령어

[문법] delete from 테이블명
        [where 조건문];
=> where절 생략 시! 모든 행이 삭제되는 명령어

🔹 (delete 예제1)

delete from departments
where department_name = 'html';

select*
from departments;

🔹 (delete 예제2)

💥 자동커밋 해제하기 : [Query] - [Auto Commit] 체크 해제

<세트>

delete from copy_emp;  -- where절 생략 시 모든 행 삭제됨.

select *
from copy_emp;   -- 작업 결과 확인(저장 X, 미리보기)

rollback;  -- 작업 취소

select *
from copy_emp;  -- 작업 취소 결과 확인

<세트>

delete from copy_emp
where employee_id = 105;   -- 특정 행 삭제

select *
from copy_emp
where employee_id = 105;  -- 작업 결과 확인(저장 X, 미리보기)

commit;   -- 작업 저장

💥 자동커밋 설정하기 : [Query] - [Auto Commit] 체크 설정

🔹 (delete 예제3) delete + 서브쿼리(subquery)

: copy_emp 테이블로부터 location_id가 1800인 부서에 소속된 사원들을 삭제.

delete from copy_emp
where department_id = (select department_id
					   from departments
					   where location_id = 1800);

💻 결과 확인

select *
from copy_emp
where department_id = (select department_id
					   from departments
					   where location_id = 1800);

---

📂 트랜잭션

트랜잭션이란? 하나의 논리적인 작업 단위로 여러개의 DML이 모여서 구성이 됨.

• 트랜잭션제어어(TCL) : commit(저장), rollback(취소), savepoint(저장점)

-- 트랜잭션 시작 : 첫번째 DML이 실행될때
-- 트랜잭션 종료 : commit, rollback 명령어가 실행될때

❗ DML(insert, update, delete) 명령어 하나가 하나의 트랜잭션임!

[상황1] Auto-Commit 설정된 상태

(트랜잭션 시작) insert -----;
(트랜잭션 종료) (autocommit 바로 발생됨)
(트랜잭션 시작) update -----;
(트랜잭션 종료) (autocommit 바로 발생됨)

[상황2] Auto-Commit 해제된 상태

(트랜잭션 시작) insert -----;
              update -----;
              update -----;
(트랜잭션 종료) commit;
(트랜잭션 시작) delete -----;
              delete -----;
(트랜잭션 종료) rollback;

[상황3] Auto-Commit 해제된 상태

(트랜잭션 시작) insert -----;
              update -----;
              savepoint 포인트명1; 
              update -----;       ==> 작업을 실수함!
              savepoint 포인트명2;
              delete -----;  
              rollback to 포인트명1;
              작업 다시하기
(트랜잭션 종료) commit;