MySQL 다시보기 02 - 테이블 생성 및 데이터 삽입

유투브 'freeCodeCamp.org' 참조

 

 

 

01 기본 키 & 외래 키란?

 

1) 기본 키 (PK - Primary Key)

• 한 테이블에서 유일한 값(NULL 제외)을 가진 컬럼 (ID, 사번 등)
• PK 조건은 NULL 값 안됨, 중복값이 절대로 있을 수 없음, PK데이터는 절대로 변하면 안됨

2) 외래 키 (FK - Foregin Key)

• 다른 테이블의 기본키와 연결해주는 컬럼
• 외래키를 사용하면 외래키를 참조하는 테이블에 외래키에 해당하는 값이 있는지를 체크 할 수 있음

※ 기본 키 ↔ 외래 키를 사용하는 이유?

1. 강의 데이터 테이블이 하나 있음

2. 이 테이블은 '재생목록코드', '강의코드' 등을 가지고 있음

3. 중복이 없고 널값이 없는 '강의코드'를 PK로 설정

4. 또 다른 재생목록 데이터 테이블이 있음

5. 이 테이블은 '재생목록코드', '재생목록명' 등의 데이터를 있음

6. 중복이 없고 널값이 없는 '재생목록코드'를 PK로 설정

7. 개발자가 강의 데이터 테이블과 재생목록 테이블의 관계를 알고 싶음

8. 두 테이블 모두가 재생목록코드를 들고 있으므로 재생목록 데이터 테이블의 재생목록코드를 FK로 설정하는데 이때 강의코드(PK)를 참조하여 설정함

9. 이렇게 되면 강의코드만 호출해보면 재생목록코드를 볼 수 있게되고 거기에 대한 재생목록명, 강의수 등을 같이 볼 수 있게 됨

 

 

 

02 테이블 생성

• 총 5개의 테이블을 생성할 예정 (회사 데이터 DB)
• Employee, Branch, Client, Works_With, Branch Supplier

1) employee 

CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  birth_day DATE,
  sex VARCHAR(1),
  salary INT,
  super_id INT,
  branch_id INT
);

 

 

2) branch

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
);

1. 'employee' 테이블의 'emp_id'를 참조하여 'mrg_id'를 외래키로 설정
2. 'ON DELETE SET NULL'은 외래키를 만들때 걸 수 있는 제약조건들중 하나 → ON DELETE/ON UPDATE 두개가 있는데 삭제/수정시에 뒤에 구문을 실행함. 즉 삭제시 삭제된 빈공간은 NULL값으로 처리.

 

 

3) employee + branch 테이블을 이용 외래키 설정

ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;

ALTER TABLE employee
ADD FOREIGN KEY(super_id)
REFERENCES employee(emp_id)
ON DELETE SET NULL;

1. 'employee' 테이블에 컬럼명 'branch_id'를 외래키로 등록하는데 참조는 'branch' 테이블의 'branch_id'
2. 'employee' 테이블에 컬럼명 'super_id'를 외래키로 등록하는데 참조는 'employee' 테이블의 'emp_id'
3. 서로 다른 테이블 참조시에는 이해가 쉽지만 두번째 같은 테이블에서 참조를 하는 경우를 보자
   'super_id'는 슈퍼바이저용 ID값으로 일반 사원의 ID(emp_id)를 참조하게 된다. 즉 한 회사에는 슈퍼바이저들이 있고 이 슈퍼바이저들은 각각의 사원들을 한사람씩 맡게된다는 뜻. 
   예로 'super_id = 100' 이면 'emp_id = 100'인 사람을 맡는다는 뜻. 
   

   

 

 

4) client

CREATE TABLE client (
  client_id INT PRIMARY KEY,
  client_name VARCHAR(40),
  branch_id INT,
  FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
);

 

 

5) works_with

CREATE TABLE works_with (
  emp_id INT,
  client_id INT,
  total_sales INT,
  PRIMARY KEY(emp_id, client_id),
  FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
  FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
);

1. PK는 중복/NULL값등 제약조건에 안벗어나면 여러개 지정 가능
2. 'emp_id' + 'client_id' 는 PK 이자 FK 로 'employee' 테이블과 'client' 테이블을 참조하게 되고 총 세개의 테이블끼리 연결을 시켜 관계를 볼 수 있음
3. ON DELETE CASDACE → FK 로 연결된 데이터들이 일관성을 유지할 수 있도록 하기 위해서 부모 테이블의 row 에 DELETE 또는 UPDATE 명령어를 적용할 때, 자동적으로 자식 테이블의 매치되는 row 에도 똑같이 DELETE 또는 UPDATE 를 반영하는 것을 의미

 

 

6) branch_supplier

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
);

 

 

 

03) 데이터 넣기

• 데이터는 'branch'에 따라 넣는다. 

1) Corporate 브랜치

-- Corporate
INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M', 250000, NULL, NULL);

INSERT INTO branch VALUES(1, 'Corporate', 100, '2006-02-09');

UPDATE employee
SET branch_id = 1
WHERE emp_id = 100;

INSERT INTO employee VALUES(101, 'Jan', 'Levinson', '1961-05-11', 'F', 110000, 100, 1);

 

 

2) Scranton 브랜치

-- Scranton
INSERT INTO employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL);

INSERT INTO branch VALUES(2, 'Scranton', 102, '1992-04-06');

UPDATE employee
SET branch_id = 2
WHERE emp_id = 102;

INSERT INTO employee VALUES(103, 'Angela', 'Martin', '1971-06-25', 'F', 63000, 102, 2);
INSERT INTO employee VALUES(104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 102, 2);
INSERT INTO employee VALUES(105, 'Stanley', 'Hudson', '1958-02-19', 'M', 69000, 102, 2);

 

 

3) Stamford 브랜치

-- Stamford
INSERT INTO employee VALUES(106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 100, NULL);

INSERT INTO branch VALUES(3, 'Stamford', 106, '1998-02-13');

UPDATE employee
SET branch_id = 3
WHERE emp_id = 106;

INSERT INTO employee VALUES(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, 106, 3);
INSERT INTO employee VALUES(108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 106, 3);

 

 

• 나머지 테이블 데이터 넣기

1) branch supplier

-- BRANCH SUPPLIER
INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Patriot Paper', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'J.T. Forms & Labels', 'Custom Forms');
INSERT INTO branch_supplier VALUES(3, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(3, 'Stamford Lables', 'Custom Forms');

 

2) client

-- CLIENT
INSERT INTO client VALUES(400, 'Dunmore Highschool', 2);
INSERT INTO client VALUES(401, 'Lackawana Country', 2);
INSERT INTO client VALUES(402, 'FedEx', 3);
INSERT INTO client VALUES(403, 'John Daly Law, LLC', 3);
INSERT INTO client VALUES(404, 'Scranton Whitepages', 2);
INSERT INTO client VALUES(405, 'Times Newspaper', 3);
INSERT INTO client VALUES(406, 'FedEx', 2);

 

 

 

3) works_with

-- WORKS_WITH
INSERT INTO works_with VALUES(105, 400, 55000);
INSERT INTO works_with VALUES(102, 401, 267000);
INSERT INTO works_with VALUES(108, 402, 22500);
INSERT INTO works_with VALUES(107, 403, 5000);
INSERT INTO works_with VALUES(108, 403, 12000);
INSERT INTO works_with VALUES(105, 404, 33000);
INSERT INTO works_with VALUES(107, 405, 26000);
INSERT INTO works_with VALUES(102, 406, 15000);
INSERT INTO works_with VALUES(105, 406, 130000);

 

굿!