데이터 엔지니어링 과정/MySQL

[29일차] JOIN & 데이터베이스

오리는짹짹 2023. 2. 2. 17:51
목차
1.OUTER JOIN
2. CROSS JOIN (상호 조인)
3. SELF JOIN (자체 조인)
4. UNION / UNION ALL/NOT IN/IN
5, 테이블 외의 데이터베이스 개체의 활용
6. 데이터베이스 백업 및 관리

1. OUTER JOIN (외부 조인)

  • 조인의 조건에 만족되지 않는 행까지도 포함시키는 것
  • 형식
SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
<LEFT/RIGHT/FULL> OUTER JOIN <두 번째 테이블 (RIGHT 테이블)>
ON <조인될 조건>
[WHERE 검색조건];
  • 🔎 전체 회원의 구매 기록, 단 구매 기록이 없는 회원도 출력
SELECT U.userID, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM usertbl U
LEFT OUTER JOIN buytbl B
ON U.userID = B.userID
ORDER BY U.userID;

  • LEFT OUTER JOIN : 왼쪽 테이블의 것은 모두 출력되어야 함 
  • 🔎 한 번도 구매한 적이 없는 유령 회원의 목록
FROM usertbl U
LEFT OUTER JOIN buytbl B
ON U.userID = B.userID
WHERE B.prodName IS NULL
ORDER BY userID;

  • 🔎 학생 테이블, 동아리 테이블, 학생 동아리 테이블 이용햇 학생을 기준으로 학생 이름/지역/가입한 동아리/동아리방OUTER JOIN으로 동아리에 가입하지 않은 학생까지 함께 출력
SELECT S.stdName, S.addr, SC.clubName, C.roomNo
FROM stdTbl S
LEFT OUTER JOIN stdCLubTbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clubTbl C
ON SC.clubName = C.clubName
ORDER BY S.stdName

  • 🔎 동아리를 기준으로 가입된 학생을 출력하되, 가입 학생이 하나도 없는 동아리도 출력
SELECT C.clubName, S.stdName, S.addr
FROM stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON S.stdname = SC.stdName
RIGHT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName
ORDER BY C.clubName;

  • 🔎 동아리에 가입하지 않은 학생도 출력되고, 학생이 한 명도 없는 동아리도 출력
SELECT S.stdName, S.addr, SC.clubName, C.roomNo
FROM stdTbl S
LEFT OUTER JOIN stdCLubTbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clubTbl C
ON SC.clubName = C.clubName

UNION
SELECT S.stdName, S.addr, C.clubName, C.roomNo
FROM stdtbl S
LEFT OUTER JOIN stdclubtbl SC
ON SC.stdname = S.stdName
RIGHT OUTER JOIN clubtbl C
ON SC.clubName = C.clubName;

 

2. CROSS JOIN (상호 조인)

  • 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능
  • 결과 개수: 두 테이블 개수를 곱한 개수

 

3. SELF JOIN (자체 조인)

  • 별도의 구문이 있는 것이 아니라 자기 자신과 자기 자신이 조인한다는 의미
  • 🔎 조직도 테이블 정의하고 데이터 입력

  • 🔎 우대리 상관의 연락처를 확인
SELECT A.emp AS '부하직원', B.emp AS '직속상관', B.empTel '직속상관연락처'
FROM emptbl A
INNER JOIN emptbl B
ON A.manager = B.emp
WHERE A.emp = '우대리';

 

4. UNION / UNION ALL / NOT IN / IN

  • UNION 
    : 두 쿼리의 결과를 행으로 합침
    • SELECT 문장1 과 SELECT 문장2의 결과 열의 개수가 같아야 함
    • 데이터 형식도 각 열 단위로 같거나 서로 호환되는 데이터 형식이어야 함
  • NOT IN
    : 첫 번째 쿼리의 결과 중에서, 두 번째 쿼리에 해당하는 것을 제외하기 위한 구문
    • ex) squldb의 사용자를 모두 조회하되 전화가 없는 사람을 제외
SELECT name, CONCAT (mobile1, mobile2) AS '전화번호' FROM usertbl
WHERE name NOT IN 
(SELECT name FROM usertbl WHERE mobile1 IS NULL);

  • IN
    :     두 번째 쿼리에 해당하는 것만 조회하기 위해 사용
    • ex) 전화기 없는 사람만 조회
SELECT name, CONCAT (mobile1, mobile2) AS '전화번호' 
FROM usertbl
WHERE name in
(SELECT name FROM usertbl WHERE mobile1 IS NULL);

 

5, 테이블 외의 데이터베이스 개체의 활용

1. 인덱스

  • 🔎 적정량이 있는 테이블 만들기
CREATE TABLE indexTBL (first_name varchar(14), last_name varchar(16), hire_date date);
INSERT INTO indexTBL
SELECT first_name, last_name, hire_date
FROM employees.employees
LIMIT 500;
SELECT * FROM indexTBL;
  • 🔎 (인덱스 x) indexTBL의 이름 중에서 'Mary'인 사람 조회
SELECT *
FROM indexTBL
WHERE first_name='Mary';

  • 🔎 이름(first_name) 열에 인덱스 생성
CREATE INDEX idx_indexTBL_firstname ON indexTBL(first_name);
  • 🔎 (인덱스 o) indexTBL의 이름 중에서 'Mary'인 사람 조회
SELECT *
FROM indexTBL
WHERE first_name='Mary';

2. 뷰

  • 가상의 테이블
  • 🔎 회원 이름과 주소만 존재하는 뷰 생성
CREATE VIEW uv_memberTBL
AS
SELECT memberName, memberAddress FROM memberTBL;

➡ 뷰의 실체는 SELECT문! 

  • 🔎 뷰 조회
SELECT * FROM uv_memberTBL

3. 스토어드 프로시저

  • SQL문을 하나로 묶어서 편리하게 사용하는 기능
  • 🔎 당탕이와 냉장고 찾기
SELECT * FROM memberTBL WHERE memberName = '당탕이';
SELECT * FROM productTBL WHERE productName = '냉장고';

  • 🔎 myProc() 이름의 스토어드 프로시저 생성
DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
SELECT * FROM memberTBL WHERE memberName = '당탕이';
SELECT * FROM productTBL WHERE prodictName = '냉장고';
END//
DELIMITER ;

🐰 DELEMITER은 '구분 문자'를 의미!! 뒤에 //가 나오면 기존의 세미콜론(;)을 //로 대신한다는 의미이다. 하나의 단락으로 묶어주는 효과를 갖는다. 마지막에는 ;를 배치해서 꼭 되돌려줍시다

  • 🔎 생성한 스토어드 프로시저 실행
CALL myProc();

4. 트리거

  • 테이블에 부착되어서 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생되는 실행되는 코드
  • 🔎 회원 테이블에 새로운 회원 입력
INSERT INTO memberTBL VALUES ('Figure', '연아', '경기도 군포시 당정동');
SELECT * FROM memberTBL;

  • 🔎 '연아' 회원 주소를 '서울 강남구 역삼동'으로 변경
UPDATE memberTBL SET memberAddress = '서울 강남구 역삼동' WHERE memberName = '연아';

  • 🔎 '연아'의 회원 탈퇴
DELETE FROM memberTBL WHERE memberName = '연아';

➡ 삭제된 데이터를 다시는 찾을 수 없다!

  • 🔎 지워진 데이터를 보관할 테이블 생성
CREATE TABLE deletedMemberTBL (
memberID CHAR(8),
memberName CHAR(5),
memberAddress CHAR(20),
deletedDate Date
);
  • 🔎 회원 테이블에서 DELETE 작업이 일어나면 백업 테이블에 지워진 데이터가 기록되는 트리거 생성
DELIMITER //
CREATE TRIGGER trg_deletedMemberTBL
AFTER DELETE
ON memberTBL
FOR EACH ROW
BEGIN
-- OLD 테이블의 내용을 백업 테이블에 삽입
INSERT INTO deletedMemberTBL
VALUES (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE());
END//
DELIMITER ;
  • 🔎 당탕이 삭제
DELETE FROM memberTBL WHERE memberName = '당탕이';
  • 🔎 백업 테이블 확인
SELECT * FROM deletedMemberTBL;

 

6. 데이터베이스 백업 및 관리

1. 백업과 복원

  • 🔎 파일 탐색기에 새로운 폴더 만들기
  • 🔎 데이터베이스 백업

  • 🔎 productTBL 모든 데이터 삭제
DELETE FROM productTBL;
  • 🔎 데이터 베이스를 다른 DB로 변경
USE sys;
  • 🔎 데이터 복원 시키기
    Navigator > Administartor > Data Import/Restore
    > Import from Slef-Contained File > 파일 경로 선택 & Default Target Schema 'ShopDB' 선택
    > Start Import
  • 🔎 데이터 복원 확인
USE ShopDB;
SELECT * FROM productTBL;