[Day20 - DB] FK(FOREIGN KEY), DML : 데이터 조작어

제약조건

- FOREIGN KEY : 2개 이상의 테이블이 관계를 맺는 것

CREATE TABLE USER(   
id VARCHAR(20) PRIMARY KEY,   
name VARCHAR(20) NOT NULL); -- 테이블 생성 시 설정

CREATE TABLE ARTICLE(   
num INT AUTO_INCREMENT PRIMARY KEY,   
title VARCHAR(50),   
content VARCHAR(1000),   
writer VARCHAR(20) REFERENCES USER(id) -- 이름만 가지고는 작성자를 명확히 파악할 수 없어 USER 테이블의 FK를 참조하도록 설정 );

-- 추후 설정
ALTER TABLE article ADD CONSTRAINT 지정할 칼럼명(생략가능)
FOREIGN KEY 참조받을 칼럼명REFERENCES 참조할테이블명(칼럼명) ON DELETE CASCADE;

CREATE TABLE user(
   id VARCHAR(100) PRIMARY KEY,
   NAME VARCHAR(100)
);

CREATE TABLE user(
   id VARCHAR(100),
   NAME VARCHAR(100),
   PRIMARY KEY(id)
);

CREATE TABLE article(
   num INT AUTO_INCREMENT PRIMARY KEY,
   title VARCHAR(500),
   content VARCHAR(1000),
   writer VARCHAR(100) REFERENCES user(id)
);

CREATE TABLE article(
   num INT AUTO_INCREMENT PRIMARY KEY,
   title VARCHAR(500),
   content VARCHAR(1000),
   writer VARCHAR(100),
   PRIMARY KEY(num),
   FOREIGN KEY(writer) REFERENCES user(id)
);

CREATE TABLE tcons(
   NO INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   jumin VARCHAR(13) NOT NULL UNIQUE,
   AREA INT,
   deptno VARCHAR(6)
);

ALTER tcons ADD CONSTRAINT tcons_no_pk PRIMARY KEY(NO);
ALTER tcons ADD CONSTRAINT tcons_deptno_fk FOREIGN KEY(deptno) REFERENCES dept2(dcode);

---

DML(Data Manipulation Language)

[INSERT] 생성

INSERT INTO 테이블명 (칼럼명1, 칼럼명2, ...) VALUES (값1, 값2, ...);
INSERT INTO 테이블명 VALUES (전체 칼럼에 대한 값);

[예시]

-- article 데이터 삽입
-- 1. 'title1', 'content1'
INSERT INTO article VALUES(NULL, 'title1', 'content1', NULL);
INSERT INTO article (title, content) VALUIS('title1', 'content1');

-- 2. 'title2'
INSERT INTO article VALUES(NULL, 'title2', NULL, NULL);
INSERT INTO article (title) VALUES('title2');

-- 3. 'content3'
INSERT INTO article VALUES(NULL, NULL, 'content3', NULL);
INSERT INTO article (title) VALUES('content3');

-- 4. 'title4', 'content4', 'hong'
INSERT INTO article VALUES(NULL, 'title4', 'content4', 'hong');
INSERT INTO article(title, content, writer) VALUES('title4', 'content4', 'hong');

-- 5. 'title6', 'cha'
INSERT INTO article VALUES('title5', NULL, 'cha');

-- 6 . 'content6', 'park'
INSERT INTO article VALUES(NULL, 'content6', 'park');


-- emp 테이블 데이터 삽입
-- 사번: 9999, 이름: hong, 담당업무 : SALESMAN, 담당 매니저: 7369, 입사일:오늘, 급여:1800, 부서번호:40
INSERT INTO emp VALUES(9999, 'hong', 'SALESMAN', 7369, CURDATE(), 1800, NULL, 40);


-- emp 테이블에서 empno, ename을 가져와 emp_sub에 저장
-- 가지고 올 칼럼명과 저장할 칼럼의 이름은달라도 되지만 형식이나 범위는 동일해야 함
INSERT INTO emp_sub (id, name)
SELECT empno, ename FROM emp WHERE deptno=10;

-- 생성과 동시에 가져오기도 가능
CREATE TABLE emp_sub AS (SELECT empno, ename FROM emp WHERE deptno=10);

---

[UPDATE] 수정

UPDATE 테이블명 SET 칼럼명1=변경값1, 칼럼명2=변경값2, ... WHERE 조건

-- emp 테이블에서 hong의 담당업무를 CLERK로 변경, 담당매니저가 7782로 변경
UPDATE emp SET job='CLERK', mgr='7782' WHERE ename='hong';

-- emp 테이블에서 커미션이 없는 사람은 100을 추가한다 
UPDATE emp SET comm=100 WHERE comm IS NULL OR comm=0;

-- deptno이 10인 부서만 comm을 급여의 10% 더 준다.
UPDATE emp SET comm=comm+sal*0.1 WHERE deptno=10; 

-- smith와 같은 업무를 담당하는 사람들의 급여를 30% 인상
UPDATE emp SET sal = sal*1.3 WHERE job = (SELECT job FROM emp WHERE ename='SMITH');

---

[DELETE] 삭제

DELETE FROM 테이블명 (WHERE 조건);

-- emp에서 이름이 hong인 데이터 삭제
DELETE FROM emp WHERE ename = 'hong';

-- emp에서 부서번호가 40인 데이터 삭제
DELETE FROM emp WHERE deptno=40;

-- emp 테이블의 모든 정보 삭제
DELETE FROM emp;

 

* DELETE와 TRUNCATE의 차이점

DELETE FROM 테이블명 : DML, 테이블명에 해당하는 테이블 내의 모든 데이터를 삭제, 메모리 공간은 유지

TRUNCATE TABLE 테이블명 : DDL, 테이블명에 해당하는 테이블 내의 모든 데이터를 삭제, 메모리 공간까지삭제