[AIVLE_AI] SQL 2일차

[AIVLE_AI] SQL 1일차

 

---

 

데이터 변경

 

DML 문: INSERT / UPDATE / DELETE

DML: Data Manipulation Language 

데이터 조작 언어 = 데이터를 변경하는 쿼리문 

잘못 변경된 것에 대한 즉각적인 복구가 어려울 수 있음 → 항상 조심하고 또 조심 

우선 SELECT문으로 변경 대상을 확인하고 또 확인하는 습관 필요 

UPDATE문과 DELETE문의 경우 정확한 조건을 지정해야 함 

필요한 경우 DML 문을 /* 주석처리 */ 해서 실수로 수행되지 않게 방지

 

INSERT 문 1

INSERT INTO 테이블 (열1, 열2,…) 
VALUES (값1, 값2,…) ;

 

열 이름 지정한 예

INSERT INTO department(dept_id, dept_name, unit_id, start_date) 
VALUES('PRD', '상품', 'A', '2018-10-01');

 

열 이름 생략한 예

INSERT INTO department 
VALUES('DBA', 'DB관리', 'A', '2018-10-01');

모든 열이 원래 순서로 나열될 경우 열 이름 생략이 가능하나, 가독성 향상을 위해 생략하지 않기를 권고

 

INSERT 문 2

NSERT INTO 테이블 (열1, 열2,…) 
VALUES (값1, 값2,…), (값1, 값2,…), … ;

 

콤마로 구분해 여러 행을 동시에 추가할 수 있음

INSERT INTO department 
VALUES ('PRD', '상품', 'A', '2018-10-01'), ('DBA', 'DB관리', 'A', '2018-10-01');

 

INSERT문 3

INSERT INTO 테이블 (열1, 열2,…) 
SELECT 열1, 열2,…
FROM 테이블
WHERE 조건;

 

SELECT 문 수행 결과를 테이블에 INSERT 할 수 있음 

SELECT 문 결과와 같은 개수의 열이 테이블에 있어 함(대상 열을 별도로 지정하지 않은 경우) 

추가될 값과 입력 받을 열의 데이터 형식이 서로 같거나 호환되어야 함

INSERT INTO retired_employee
SELECT * FROM employee WHERE retired_date IS NOT NULL;

INSERT INTO retired_employee
SELECT emp_id, emp_name, hire_date, retire_date
FROM employee WHERE retired_date IS NOT NULL;

 

UPDATE 문

UPDATE 테이블
SET 열1 = 값1, 열2 = 값2,… 
WHERE 조건 ;

 

조건에 맞는 행 값 바꾸기

UPDATE employee
SET phone = '010-1239-1239'
WHERE emp_id = 'S0001';

 

 

DELETE 문

DELETE FROM 테이블
WHERE 조건 ;

 

조건에 맞는 행 지우기

DELETE FROM vacation 
WHERE end_date <= '2013-12-31';

 

테이블 모든 행 지우기

DELETE FROM vacation;
-- 또는
TRUNCATE TABLE vacation;

 

모든 행을 지울 때는 TRUNCATE TABLE문 사용을 권고(더 빠르게 처리)

 

 

---

다중 테이블 조회

 

 

INNER JOIN 

가장 일반적인 JOIN 문 형태

양쪽 테이블에서 비교되는 값이 일치하는 행만 가져옴

일반적으로 PK와 FK가 ON 절에서 서로 비교됨

OUTER JOIN 

비교되는 값이 일치하지 않는 행도 기준 테이블에서 가져옴

LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 으로 구분 

단, MySQL은 FULL OUTER JOIN 이 없음

 

CROSS JOIN 

일반적인 비즈니스 응용프로그램에서 사용되지 않음

ON 절이 없어 모든 경우의 수 만큼 결과 행을 얻음

대량의 테스트 데이터를 만드는 목적으로 많이 사용됨

 

JOIN 문 작성 3단

1단계: 테이블 연결

SELECT emp_id, emp_name, employee.dept_id,department.dept_name, phone, email
FROM employee 
JOIN department ON employee.dept_id = department.dept_id;

 

2단계: 테이블 별칭 지정 → 쿼리문이 간결해짐

SELECT emp_id, emp_name, e.dept_id, d.dept_name, phone, email
FROM employee AS e
JOIN department AS d ON e.dept_id = d.dept_id;

 

3단계: 테이블 별칭 채우기 → 쿼리문 이해에 도움을 줌

SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name, e.phone, e.email
FROM employee AS e
JOIN department AS d ON e.dept_id = d.dept_id;

 

JOIN 사용 예

INNER JOIN: 직원 정보 조회 시 부서 이름 포함

SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name, e.phone
FROM employee AS e
INNER JOIN department AS d ON e.dept_id = d.dept_id
WHERE e.hire_date BETWEEN '2014-01-01' AND '2015-12-31'
AND e.retire_date IS NULL;

 

OUTER JOIN: 모든 부서 정보 조회 시 본부 이름 포함

SELECT d.dept_id, d.dept_name, d.unit_id, u.unit_name
FROM department AS d 
LEFT OUTER JOIN unit AS u ON d.unit_id = u.unit_id;

 

여러 테이블 조인

테이블 간의 관계를 알고 작성하면 쉽게 작성할 수 있음

SELECT e.emp_id, e.emp_name, d.dept_name, u.unit_name, v.begin_date, v.duration
FROM employee AS e 
INNER JOIN department AS d ON e.dept_id = d.dept_id
LEFT OUTER JOIN unit AS u ON d.unit_id = u.unit_id
INNER JOIN vacation AS v ON e.emp_id = v.emp_id
WHERE v.begin_date BETWEEN '2017-01-01' AND '2017-03-31' 
ORDER BY e.emp_id ASC;

 

하위 쿼리 (Sub-auery)

괄호 안에 또다른 쿼리문이 있는 쿼리문

대부분 JOIN 문으로 작성해서 같은 결과를 얻을 수 있음

JOIN 문보다 작성하기가 쉬움

SELECT emp_id, emp_name, salary
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);

 

일반적인 하위 쿼리에 비해 상관 하위 쿼리를 이해하기가 다소 어려움

SELECT emp_id, emp_name, email
FROM employee AS e
WHERE EXISTS (SELECT *
FROM vacation
WHERE emp_id = e.emp_id);

 

하위 쿼리 사용 예

가장 먼저 입사한 직원정보 조회

SELECT emp_id, emp_name, dept_id, phone, email, salary
FROM employee
WHERE hire_date = (SELECT MIN(hire_date) FROM employee);

 

휴가를 간 적이 있는 정보시스템 직원정보 조회

SELECT emp_id, emp_name, dept_id, phone, email
FROM employee
WHERE dept_id = 'SYS' AND emp_id IN (SELECT emp_id FROM vacation);

 

휴가를 간 적이 없는 정보시스템 직원정보 조회

SELECT emp_id, emp_name, dept_id, phone, email
FROM employee
WHERE dept_id = 'SYS' AND emp_id NOT IN (SELECT emp_id FROM vacation);

 

---

뷰(Views)

 

뷰 만들기

우선 뷰가 보여줄 데이터에 대한 SELECT 문을 작성

CREATE VIEW 문으로 작성한 SELECT 문을 갖는 뷰를 만듦

 

사용 예: 2018년 주문을 보여주는 뷰 만들기

CREATE VIEW orders_2018
AS
SELECT oh.order_id, ct.customer_name, oh.customer_id, 
DATE(oh.order_date) AS order_date, oh.total_due
FROM order_header AS oh
INNER JOIN customer AS ct ON oh.customer_id = ct.customer_id
WHERE order_date >= '2018-01-01' AND order_date < '2019-01-01';

 

뷰를 만들었으면 테이블처럼 조회하면 됨

SELECT * FROM orders_2018;

 

뷰 변경

ALTER VIEW 문으로 기존 뷰가 갖는 SELECT문 수정

ALTER VIEW orders_2018
AS
SELECT oh.order_id, ct.customer_name, oh.customer_id, 
DATE(oh.order_date) AS order_date, oh.sub_total, 
(…생략…)

 

CREATE OR REPLACE VIEW 문으로 생성 및 변경을 처리할 수 있음

CREATE OR REPLACE VIEW orders_2018
AS
SELECT oh.order_id, ct.customer_name, oh.customer_id, 
DATE(oh.order_date) AS order_date, oh.sub_total, 
(…생략…)

 

WITH CHECK OPTION

뷰가 보여주는 데이터만 변경 가능하게 설정

뷰를 사용한 데이터 변경은 권고하지 않음 (데이터를 보는 용도로만 사용)

CREATE OR REPLACE VIEW high_score
AS
SELECT id, name, score1, score2, score3,
ROUND((score1 + score2 + score3) / 3, 2) AS avg_score
FROM member
WHERE (score1 + score2 + score3) / 3 >= 85
WITH CHECK OPTION;