[MySQL] DDL 작성과 EQUI JOIN, OUTER JOIN, SELF JOIN

> 🦔 DDL(Data Definition Language)문 작성시 Primary key, Foreign key 설정은 따로 빼둔다.

WHY
DDL을 작성한 후, INSERT문을 통해 테스트용 데이터를 넣을 때, 여러 제약이 걸려있으면 번거롭다.
테스트할 때는, 따로 빼둔 ALTER문을 주석처리하고 전체 스크립트를 돌리고,
데이터가 유효한지 검사할 때, 주석을 풀고 전체 스크립트를 돌려본다.

  CREATE TABLE emp {
      empno INT(4) NOT NULL,
      ename VARCHAR(10),
      job VARCHAR(9),
      mgr INT(4),
      hiredate DATETIME,
      sal DECIMAL(7, 2),
      comm DECIMAL(7, 2),
      deptno INT(2)
  }

  CREATE TABLE dept (
      deptno INT(2),
      dname VARCHAR(14),
      loc VARCHAR(13)
  );

  CREATE TABLE salgrade (
    grade INT,
    losal INT,
    hisal INT
  );

  # CONTRAINT 제약조건 이름 설정
  # ALTER TABLE emp ADD CONTRAINT pk_emp PRIMARY KEY(empno);
  # ALTER TABLE dept ADD CONTRAINT pk_dept PRIMARY KEY (deptno);

  # ALTER TABLE emp ADD CONTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES dept(deptno);
  # ALTER TABLE emp ADD CONTRAINT fk_emp_emp FOREIGN KEY(mgr) REFERENCES emp(empno);

  INSERT INTO emp VALUES
  (7369, 'SMITH', 'CLERK', 7902, STR_TO_DATE('17-12-1980', '%d-%m-%Y'), 800, NULL, 20),
  (7499, 'ALLEN', 'SALESMAN', 7698, STR_TO_DATE('20-02-1981', '%d-%m-%Y'), 1600, 300, 30),
  (7521, 'WARD', 'SALESMAN', 7698, STR_TO_DATE('22-02-1981', '%d-%m-%Y'), 1250, 500, 30),
  (7566, 'JONES', 'MANAGER', 7839, STR_TO_DATE('02-04-1981', '%d-%m-%Y'), 2975, NULL, 20),
  (7654, 'MARTIN', 'SALESMAN', 7698, STR_TO_DATE('28-09-1981', '%d-%m-%Y'), 1250, 1400, 30),
  (7698, 'BLAKE', 'MANAGER', 7839, STR_TO_DATE('01-05-1981', '%d-%m-%Y'), 2850, NULL, 30),
  (7782, 'CLARK', 'MANAGER', 7839, STR_TO_DATE('09-06-1981', '%d-%m-%Y'), 2450, NULL, 10),
  (7788, 'SCOTT', 'ANALYST', 7566, STR_TO_DATE('09-12-1982', '%d-%m-%Y'), 3000, NULL, 20),
  (7839, 'KING', 'PRESIDENT', NULL, STR_TO_DATE('17-11-1981', '%d-%m-%Y'), 5000, NULL, 10),
  (7844, 'TURNER', 'SALESMAN', 7698, STR_TO_DATE('08-09-1981', '%d-%m-%Y'), 1500, NULL, 30),
  (7876, 'ADAMS', 'CLERK', 7788, STR_TO_DATE('12-01-1983', '%d-%m-%Y'), 1100, NULL, 20),
  (7900, 'JAMES', 'CLERK', 7698, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 950, NULL, 30),
  (7902, 'FORD', 'ANALYST', 7566, STR_TO_DATE('03-12-1981', '%d-%m-%Y'), 3000, NULL, 20),
  (7934, 'MILLER', 'CLERK', 7782, STR_TO_DATE('23-01-1982', '%d-%m-%Y'), 1300, NULL, 10);

  INSERT INTO dept VALUES
  (10, 'ACCOUNTING', 'NEW YORK'),
  (20, 'RESEARCH', 'DALLAS'),
  (30, 'SALES', 'CHICAGO'),
  (40, 'OPERATIONS', 'BOSTON');

  INSERT INTO salgrade VALUES
  (1, 700, 1200),
  (2, 1201, 1400),
  (3, 1401, 2000),
  (4, 2001, 3000),
  (5, 3001, 9999);

⭐ STR_TO_DATE('10-08-2023', '%d-%m-%Y')

  SELECT STR_TO_DATE('10-08-2023', '%d-%m-%Y') FROM DUAL; # (DUAL: Mysql의 가짜테이블)
  # 2023-08-10

타입	표현1	의미	표현2	의미
정수	INT(4)	4는 4바이트를 의미 (레거시적 의미)	INT	기본 4바이트 고정크기
문자열	VARCHAR(10)	가변길이 문자열, 10은 문자열의 최대길이	CHAR(10)	고정길이 문자열, 남은 문자는 공백문자가 대체
날짜	VARCHAR(10)	고정된 8바이트, 타임존 정보 ❌	CHAR(10)	보통 4바이트, 현재 타임존 변환 저장 자동 업데이트⭕
숫자	DECIMAL(7, 2)	전체 7자리 중 2자리 소수점 아래 할당	NUMERIC(7, 2)	별다른 차이❌

𐂂 EQUI JOIN, 한 레코드당 연결된 테이블의 모든 레코드를 검사해야한다

⚠️ 레코드가 많아지면 퍼포먼스적으로는 JOIN이 좋지 않다.

📑 JOIN하는 3️⃣가지 방법

#1.
SELECT *
FROM emp e, dept d
ON e.deptno = d.deptno;

#2. 🎓 ANSI(미국 국립 표준 협회) SQL 표준
SELECT *
FROM emp e
JOIN dept d
ON e.deptno = d.deptno;

#3.
SELECT *
FROM emp
JOIN dept
USING(deptno);

⭐ 참조되는 테이블의 Foreign key Column명과
   참조하는 테이블의 Primary key Column명이 동일할 때,
   간결하게 USING절을 사용하여 나타낼 수 있다.

𖠃 Mysql은 FULL OUTER JOIN을 지원하지 않는다.

⭐ FULL OUTER JOIN과 동일한 기능 구현

  SELECT e.ename, d.dname # 컬럼명이 같아야한다
  FROM emp e
  ON d.deptno = e.deptno
  UNION
  SELECT e.ename, d.dname # 컬럼명이 같아야한다
  FROM dept d
  ON d.deptno = e.deptno;

𖠃 SELF JOIN

  SELECT e1.empno, e1.name, e2.mgr, e2.name
  FROM emp e1
  LFET OUTER JOIN emp e2
  ON e1.mgr = e2.empno;

🏓 모든 사원들의 이름, 부서명, 매니저명, 연봉등급을 출력하시오

  SELECT e.ename, d.dname, m.ename, s.grade
  FROM emp e
  LEFT OUTER JOIN dept d ON e.deptno = d.deptno
  LEFT OUTER JOIN emp m ON e.mgr = m.empno
  JOIN salgrade s ON e.sal BETWEEN s.lowsal AND s.hisal;