[MYSQL] 자바 개발자 양성과정 13일차 - JOIN,서브쿼리, CREATE, INSERT

지난시간 복습하면서 질문한 것🌟

[MYSQL] ESCAPE - 쿼리문에서 LIKE에 반응하지 않도록 단순 문자열로 읽기게 하기

---

수업시작🌈

[MYSQL] WITH ROLLUP - 결과값의 소계/총계(부분합) 출력하기

[MYSQL] WITH ROLLUP - 결과값의 소계/총계(부분합) 출력하기

이런 편한 기능이 있었다니 ㄴㅇㄱ

---

식별관계/비식별 관계

부모의 PK키가 자식의 PK키가 되는 경우를 식별관계라고한다.

예로는 사원테이블과 사원의 건강기록부 테이블의 관계가 있겠다.

 

반대로 부모의 PK키가 자식의 PK키가 아니라 FK키인 경우 비식별관계라고 한다.

예로는 회원테이블과 회원 주문테이블의 관계가 있겠다.

---

비표준 등가 조인

SELECT empno, ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;

-- KING . 사원이름 의 부서이름과 근무지를 출력하시오
SELECT empno, ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND ename = 'KING';

1. FROM에 테이블을 두개 작성했을때

WHERE절을 안쓰면 단순 CROSS JOIN이 되지만,

WHERE절로 필터링을 해준다면 INNER JOIN과 동일한 기능으로 사용할 수 있다.

2. 모든 DBMS에서 오류가 나지 않는 코드이다.

3. 표준 조인이 되지 않은 이유는 WHERE절이 계속해서 길어지기 때문에 표준 조인에서는 제외되었다.

---

테이블 NATURAL JOIN(테이블) WHERE ~

- 표준 내부 조인

위 방법과 동일한 결과를 낼수 있는 방법 중 하나다.

1. 두개 테이블 사이에 공통컬럼을 조인의 조건으로 사용한다.

2. 두개 테이블 사이에 공통컬럼(이름/성질이 같아야함)이 있을때만 사용 가능하다.

-- NATURAL JOIN 사용
SELECT empno, ename, dname
FROM emp NATURAL JOIN dept
WHERE ename = 'KING';

-- 비표준 (위 쿼리와 결과값 같음)
SELECT empno, ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND ename = 'KING';

위 쿼리의 결과는 같다.

NATURAL JOIN = emp.deptno = dept.deptno

---

테이블 JOIN 테이블 UESING(컬럼) WHERE ~

- 표준 내부 조인

위 방법과 동일한 결과를 낼수 있는 방법 중 하나다.

1. USEING뒤 괄호 안에 있는 컬럼을 조인의 조건으로 사용한다.

2. 두개 테이블 사이에 공통컬럼(이름/성질이 같아야함)이 있을때만 사용 가능하다.

-- JOIN USING 사용
SELECT empno, ename, dname
FROM emp JOIN dept USING (deptno)
WHERE empno = 7900;

-- 비표준 (위 쿼리와 결과값 같음)
SELECT empno, ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND ename = 'KING';

---

테이블 JOIN 테이블 ON(컬럼 조건) 

- 표준 내부 조인

위 방법과 동일한 결과를 낼수 있는 방법 중 하나다.

1. ON 뒤에 괄호에 있는 조건으로 조인한다.

-- JOIN ON 사용 [INNER]은 생략 가능 
-- 일반적으로 JOIN만 적혀있으면 INNER JOIN이라 생각함
SELECT empno, ename, dname
FROM emp [INNER] JOIN dept
ON (emp.DEPTNO = dept.DEPTNO)
WHERE empno = 7900;

-- 비표준 (위 쿼리와 결과값 같음)
SELECT empno, ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno AND ename = 'KING';

---

내부 조인의 주의할점

공통칼럼을 출력할 경우 두개의 테이블 중 어느 테이블에 속한 컬럼인지 정확히 명시해줘야한다.

-- 오류 발생 ) 공통 컬럼의 deptno가 어느 테이블인지 명시 안됨
SELECT empno, ename, dname, deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno;

-- 정상 ) 공통 컬럼의 deptno가 어느 테이블인지 명시됨
SELECT empno, ename, dname, emp.deptno
FROM emp, dept
WHERE emp.deptno = dept.deptno;

---

결론

아래 쿼리는 모두 같은 결과를 출력한다.

SELECT ename, dname
FROM emp , dept
WHERE emp.deptno = dept.deptno AND ename LIKE '%A%';

SELECT ename, dname
FROM emp join dept on emp.deptno = dept.deptno
WHERE ename LIKE '%A%';

SELECT ename, dname
FROM emp NATURAL JOIN (dept)
WHERE ename LIKE '%A%';

SELECT ename, dname
FROM emp JOIN dept USING(deptno)
WHERE ename LIKE '%A%';

---

비등가 조인

1. 관계가 없는 두개의 테이블을 연결할때 사용한는 관계. (= 두개의 테이블사이에 직접 대응하는 열이 없다.)

2. 이 관계는 =을 제외한 연산자 를 사용하여 형성

// 직원의 임금이 몇등급에 해당하는지 확인한는 쿼리
// PK/FK가 없지만 이런 식으로도 연결할 수 있다.
SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

---

서브쿼리

SELECT 컬럼 FROM 테이블명 WHERE 비교연산자 (SELECT 컬럼 FROM 테이블명);

 

Guideline
1)서브쿼리는 괄호로 묶는다 .
2)서브쿼리는 비교 연산자 오른쪽에 넣는다 .
3)서브쿼리에서는 ORDER BY 를 사용할 수 없다.
4)ORER BY절은 메인 SELECT 문 마지막에 넣는다.
5)서브쿼리에서 사용되는 비교연산자는 단 일 행 연산자 및 여러 행 연산자를 모두 사용가능

 

 

단일행 서브쿼리

예시 :  사번 의 급여보다 많이 받는 사원의 이름

만약 서브쿼리를 모른다면 

-- 7566사원의 급여를 알기위한 작업
SELECT sal 
FROM emp
WHERE empno = 7566 --2975

-- 7566사원의 급여보다 많이 받는 사람 조회
SELECT sal 
FROM emp
WHERE empno = 2975

이렇게 두번의 과정을 거쳐야하지만 2975를 서브쿼리로 대체한다면 한줄로 해결할 수있고

이러한 방식이 더울 효율적이다.

-- 7566사원의 급여보다 많이 받는 사람 조회
SELECT sal 
FROM emp
WHERE empno = (SELECT sal 
FROM emp
WHERE empno = 7566)

이렇게 작성되고, 서브쿼리의 행의 갯수 1개만 나오는 경우 '단일행 서브쿼리'라고 부른다.

 

다중행 서브쿼리

IN() 모두 비교해서 값이 존재하면 TRUE

= ANY() 한개라도 TRUE면 TRUE다.

= ALL() 모두 TRUE면 TRUE다.

 

다중 컬럼 서브쿼리

SELECT a.ename, a.sal, a.deptno, b.salavg
FROM emp a, (SELECT deptno, AVG(sal) salavg
FROM emp
GROUP BY deptno) b
WHERE a.deptno = b.deptno
AND a.sal > b.salavg;

FROM절에 있는 서브 쿼리(SELECT deptno, AVG(sal) salavg FROM emp GROUP BY deptno) ()를 뷰 라고 부르고, 가상테이블이다.

쿼리를 돌릴때만 만들어진다.

---

테이블 스키마(구조)만 복사하기

CREATE table dept_copy
AS
SELECT *
FROM dept
WHERE 0>1;

값도 복사하고 싶을때는 WHERE절을 제외하면 된다.

 

단, 제약조건은 복사되지 않는다.,

---

INSERT시 유의사항

INSERT할때 값을 넣지 않으면 NULL값을 가진다.

단, NULL을 허용하지 않는 컬럼인 경우, 값을 생략하면 오루가 발생한다.

---

DML은 트렉젝션이 되는 범위다.

DML - INSERT DELETE UPDATE SELECT

DDL - DROP CREAT ALTER RENAME

DCL - GRANT REVOKE

TCL - COMMIT ROLLBACK