SQL

MySQL 다시보기 04 - 중첩쿼리/ON DELETE/TRIGGER/ERD

H-V 2021. 10. 4. 19:54

유투브 'freeCodeCamp.org' 참조

 

 

01 Nested Queries (중첩 질의)

  • WHERE 절에서 사용하는 서브쿼리. WHERE 절에서 사용한다는 것은 조건절의 일부로 사용된다는 뜻. 즉, 메인쿼리 테이블의 특정 컬럼 값과 비교한 값을 반환하는 용도로 사용
SELECT works_with.emp_id FROM works_with
WHERE works_with.total_sales > 30000;

↓

SELECT employee.first_name, employee.last_name
FROM employee
WHERE employee.emp_id IN(
SELECT works_with.emp_id FROM works_with
WHERE works_with.total_sales > 30000
);

SELECT client.client_name FROM client WHERE client.branch_id = (
SELECT branch.branch_id FROM branch WHERE branch.mgr_id = 102
);

*결과값이 여러개일경우 '=' 대신 'IN'을 쓰는것이 좋다.

 

 

02 ON DELETE

  • PK를 기반으로 FK를 설정했으면 테이블끼리 연결이 되어있는 상태이다. 이때 서로 연관이 있는 키를 가진 테이블끼리의 데이터를 지우게되면 어떻게 될까?
  • ON DELETE 의 기능이 바로 서로 연관이 있는 키들과 테이블끼리 수정 혹은 삭제시 ON DELETE로 제약을 걸어놓은 상태로 세팅을 해준다. 
  • 테이블을 생성하였던 쿼리를 보자
CREATE TABLE branch (
  branch_id INT PRIMARY KEY,
  branch_name VARCHAR(40),
  mgr_id INT,
  mgr_start_date DATE,
  FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

→ 여기 쿼리에서 현재 branch/employee 테이블들이 연관이 있는 관계이다. 삭제해보자

DELETE FROM employee WHERE emp_id = 102;

SELECT * FROM employee;

  • 102번과 연관이 있던 데이터들이 삭제 되면 'SET NULL'이 적용 되는 모습이다.

 

  • 다음 쿼리를 보자
CREATE TABLE branch_supplier (
  branch_id INT,
  supplier_name VARCHAR(40),
  supply_type VARCHAR(40),
  PRIMARY KEY(branch_id, supplier_name),
  FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);
  • ON DELETE CASCADE는 어떤 처리를 할까?
DELETE FROM branch WHERE branch_id = 2;

SELECT * FROM branch_supplier;

→ ON DELETE CASCADE는 FK가 참조하는 테이블의 행이 날라가면 FK 테이블의 데이터도 함께 날아 가게 된다. 
    (FK가 참조하는 테이블의 branch_id = 2 를 삭제했으니 FK테이블의 관련 데이터행도 싹 날라간다)

  • 여기서 중요 포인트는 PK는 절대로 NULL값을 가질 수 없으므로 처음 생성시에 PK를 준 branch_id는 FK를 가질 수 있되 SET NULL은 못하므로 삭제밖에 못한다는것을 염려해 두어야 한다.

 

 

 

 

03 Trigger

  • SQL 프로그램을 사용하면서 세팅을 해놓을 수 있는 기능 중 하나로 특정테이블에 어떤 변화가 일어나면 그 테이블과 연관 있는 모든 테이블의 내용도 자동으로 변경하도록 하는 것
  • 트리거는 다음 링크를 참조 하자 (https://www.mikedane.com/databases/sql/triggers/)

 

 

 

04 ER-Diagram

  • Entity Relationship의 약자로 요구사항을 그림으로 그려내어 그 관계를 도출 하는 것.

※ ERD를 구성할때 알아야할 요소들 

  1. Entity - 개체(Entity)라고 불리며 시스템화 하고자 하는 사물이나 사건, 객체 등을 일 컫음 (사각형으로 표시)
  2. Attribute - 속성(Attribute)이라고 불리며 Entity에 대한 정의, 표기법, 값, 속성등을 나타 냄 (줄과 마름모로 표시)
  3. PK - 기본키로 테이블의 구분하기위한 고귀한 식별자 (Student 테이블의 student_id)
  4. Composite Attribute - 두개이상의 속성으로 구성된것을 일컫음 (국가의 도시와 번지)
  5. Multi-valued Attribute - 하나의 개체(Entity)가 여러값을 가질 수 있는 속성을 말함. (한사람의 여러가지 취미)
  6. Derived Attribute - 다른 속성으로부터 값이 결정되는 속성 (주민등록번호와 그 사람의 나이)
  7. Multiple Entities - 다이어그램에 여러개의 개체를 설정 할 수 있음
  8. Relationship Attribute - 연관이 있는 속성을 표시 (시험 점수를 원하면 시험을 쳐야함)

 

※ Relationship Cardinality 

  1. ERD의 목적은 Entity 유형 정의하고 Entity 유형간의 관계를 표시하기 위함
  2. Attributes를 통해 Entity 유형을 정의했다면 Relationship으로 유형간의 관계를 표시 해야함

기본적으로 일대일(1:1) / 일대다(1:N) / 다대다(N:N)의 관계가 있음 

  1. 1:1 - 두 개 Entity Type의 개체들은 서로 일대일로 대응 
  2. 1:N - 하나의 개체가 다른 Entity Type의 많은 개체들과 관련되지만, 그 역은 성립하지 않음
  3. N:N - 하나의 개체가 다른 Entity Type의 많은 개체들과 관련되며, 역이 성립

* 테이블간의 관계를 지을때 약한 개체(Weak Entity)가 발생할 수 도 있다. 약한 개체란 자신의 Key Attribute가 없는 Entity Type. 예로 '학사 관리 시스템'에서 강의번호 101는 101-1, 101-2... 이런식으로 여러개의 분반이 있을 수 있음. 이때 분반이라는 Entity는 자신의 Key Attribute는 없고 '실제 반' 개체가 있어야 존재 가능하기때문에 약한 개체라 불림

 

 

05 ERD 만들어 보기

  • 위에서 언급 했듯이 ERD는 요구사항을 그림으로 나타낸 것이다. 요구사항은 다음과 같다.
    1. The company is organized into branches. Each branch has a unique number, a name, and a particular employee who manages it.
    2. The company makes it’s money by selling to clients. Each client has a name and a unique number to identify it.
    3. The foundation of the company is it’s employees. Each employee has a name, birthday, sex, salary and a unique number.
    4. An employee can work for one branch at a time,
      and each branch will be managed by one of the employees that work there. We’ll also want to keep track of when the current manager started as manager.
    5. An employee can act as a supervisor for other employees at the branch, an employee may also act as the supervisor for employees at other branches. An employee can have at most one supervisor.
    6. A branch may handle a number of clients, with each client having a name and a unique number to identify it. A single client may only be handled by one branch at a time.
    7. Employees can work with clients controlled by their branch to sell them stuff. If nescessary multiple employees can work with the same client. We’ll want to keep track of how many dollars worth of stuff each employee sells to each client they work with.
    8. Many branches will need to work with suppliers to buy inventory. For each supplier we’ll keep track of their name and the type of product they’re selling the branch. A single supplier may supply products to multiple branches.

 

06 ERD 기반으로 DB 세팅

 

1. 가장 먼저 몇개의 테이블이 나오고 거기에 필요한 기초 컬럼이 무엇인가를 봐야 한다.

2. 테이블 개수 및 컬럼이 정해지면 테이블처럼 세팅 한다

3. 테이블끼리의 관계를 생각해 본 후 FK를 정한다. 1:1 → 1:N → N:N순으로 한다.