[패스트캠퍼스 DA 부트캠프 11기]7주차 학습

👨🏻‍👩🏻‍👧🏻‍👦🏻 여러 테이블 한번에 다루기

• 집합연산
  • 참고로 JOIN은 가로로, 집합은 세로로 합치는 거라고 생각하면 편함!
• UNION, UNION ALL
  • UNION 은 동일한 값은 중복제외하고 보여주며, UNION ALL은 동일한 값도 포함
  • [쿼리 A] UNION [쿼리 B]
  • [쿼리 A]와 [쿼리 B] 의 컬럼의 개수가 같아야 함
  • ORDER BY는 쿼리 가장 마지막에 작성 가능하며, [쿼리 A]에서 가져온 컬럼으로만 가능

SELECT [컬럼 이름]
FROM [테이블A 이름]
:
UNION
SELECT [컬럼 이름]
FROM [테이블B 이름]
: ;

• 교집합, 차집합
  • 다른 DBMS에는 INTERSECT, MINUS 키워드가 있지만, MySQL에는 없어서 JOIN으로 구현

# 교집합
SELECT [컬럼 이름]
FROM [테이블A 이름] AS A
INNER JOIN [테이블B 이름] AS B
ON A.[컬럼1 이름] = B.[컬럼1 이름] AND ... AND A.[컬럼n 이름] = B.[컬럼n 이름];

# 차집합
SELECT [컬럼 이름]
FROM [테이블A 이름] AS A
LEFT JOIN [테이블B 이름] AS B
ON A.[컬럼1 이름] = B.[컬럼1 이름] AND ... AND A.[컬럼n 이름] = B.[컬럼n 이름]
WHERE B.[컬럼 이름] IS NULL;

#################################### EXAMPLE ##########################################
DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
	number  int,
       name	varchar(20),
       type	varchar(10),
       attack int,
       defense int
);
CREATE TABLE friendpokemon (
	number  int,
       name	varchar(20),
       type	varchar(10),
       attack int,
       defense int
);
INSERT INTO mypokemon (number, name, type, attack, defense)
VALUES (10, 'caterpie', 'bug', 30, 35),
	   (25, 'pikachu', 'electric', 55, 40),
	   (26, 'raichu', 'electric', 90, 55),
	   (133, 'eevee', 'normal', 55, 50),
	   (152, 'chikoirita', 'grass', 49, 65);

INSERT INTO friendpokemon (number, name, type, attack, defense)
VALUES (26, 'raichu', 'electric', 80, 60),
	   (125, 'electabuzz', 'electric', 83, 57),
       (137, 'porygon', 'normal', 60, 70),
       (153, 'bayleef', 'grass', 62, 80),
       (172, 'pichu', 'electric', 40, 15),
       (470, 'leafeon', 'grass', 110, 130);

/*
MISSION (1)
내 포켓몬과 친구의 포켓몬에 어떤 타입들이 있는지 중복 제외하고 같은 타입은 
한 번 씩만 가져와 주세요.
*/
SELECT type
FROM mypokemon
UNION
SELECT type
FROM friendpokemon;

/*
MISSION (2)
내 포켓몬과 친구의 포켓몬 중에 풀(grass) 타입 포켓몬들의 포켓몬 번호와 이름을 
중복 포함하여 전부 다 가져와 주세요.
*/
SELECT number, name, 'my' AS whose
FROM mypokemon
WHERE type = 'grass'
UNION ALL
SELECT number, name, "friend's" AS whose
FROM friendpokemon
WHERE type = 'grass';
#################################### EXAMPLE ##########################################

#################################### EXAMPLE ##########################################
/*
MISSION (1)
나도 가지고 있고, 친구도 가지고 있는 포켓몬의 이름을 가져와 주세요.
*/
SELECT mypokemon.name
FROM mypokemon INNER JOIN friendpokemon
ON mypokemon.number = friendpokemon.number AND mypokemon.name = friendpokemon.name; 

/*
MISSION (2)
나만 가지고 있고, 친구는 안 가지고 있는 포켓몬의 이름을 가져와 주세요.
A (내가 가지고 있는 포켓몬) - B (친구가 가지고 있는 포켓몬)
*/
SELECT mypokemon.name
FROM mypokemon LEFT JOIN friendpokemon
ON mypokemon.number = friendpokemon.number AND mypokemon.name = friendpokemon.name
WHERE friendpokemon.number IS NULL;
#################################### EXAMPLE ##########################################

• JOIN, UNION문 실행순서 찾아보기

FROM - > ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY

---

➕ 조건에 조건 더하기(SubQuery)

• 서브쿼리 특징
  • 하나의 쿼리 내 포함된 또 하나의 쿼리를 의미
  • 서브 쿼리는 반드시 괄호 안에 있어야 한다
  • SELECT, FROM, WHERE, HAVING, ORDER BY 절에 사용가능
  • INSERT, UPDATE, DELETE문에서도 사용 가능
  • 서브쿼리에는 세미콜론(;)을 붙이지 않아도 됨!
• SELECT절의 서브쿼리
  • **스칼라 서브쿼리**라고도 함
  • 반드시 결과값이 하나의 값이어야 함
• FROM절의 서브쿼리
  • **인라인 뷰 서브쿼리**라고도 함
  • 반드시 결과값이 하나의 테이블이어야 함
  • 반드시 별명을 가져야 함
• WHERE절의 서브쿼리
  • **중첩 서브쿼리**라고도 함
  • 반드시 결과값이 하나의 컬럼이어야 함(EXISTS 제외)
  • 연산자와 함께 사용
    • 보통 **WHERE [컬럼이름] [연산자] [서브쿼리]** 형식으로 사용함
    • 비교 연산자 : 비교 연산자만 사용시, WHERE절의 서브쿼리는 반드시 하나의 값이어야 함연산자 활용 의미

      =	A =[서브쿼리]	A와 [서브쿼리]의 결과값이 같다
      !=	A!=[서브쿼리]	A와 [서브쿼리]의 결과값이 같지 않다
      >	A > [서브쿼리]	A가 [서브쿼리]의 결과값보다 크다
      >=	A>=[서브쿼리]	A가 [서브쿼리]의 결과값보다 크거나 작다
      <	A < [서브쿼리]	A가 [서브쿼리]의 결과값보다 작다
      <=	A<=[서브쿼리]	A가 [서브쿼리]의 결과값보다 작거나 작다

    •  
    • 주요 연산자: 주요 연산자 사용시, WHERE절의 서브쿼리는 반드시 하나의 컬럼이어야 함
      • 단, EXISTS는 단독 사용하며, 결과값이 여러 컬럼이어도 됨
      연산자 활용 의미

      IN	A IN( [서브 쿼리] )	A가 [서브 쿼리]의 결과값 내에 있다
      ALL	A < ALL( [서브 쿼리] )	A가 모든 [서브 쿼리]의 결과값보다 작다
      	A > ALL( [서브 쿼리] )	A가 모든 [서브 쿼리]의 결과값보다 크다
      ANY	A < ANY( [서브 쿼리] )	A가 [서브 쿼리]의 결과값보다 하나라도 작다
      	A > ANY( [서브 쿼리] )	A가 [서브 쿼리]의 결과값보다 하나라도 크다
      EXISTS	EXISTS( [서브쿼리] )	[서브 쿼리]의 결과값이 존재하면 True
      	NOT EXISTS( [서브쿼리] )	[서브 쿼리]의 결과값이 존재하지 않으면 True

DROP DATABASE IF EXISTS pokemon;

CREATE DATABASE pokemon;
USE pokemon;
CREATE TABLE mypokemon (
number INT,
name VARCHAR(20)
);
INSERT INTO mypokemon (number, name)
VALUES (10, 'caterpie'),
(25, 'pikachu'),
(26, 'raichu'),
(133, 'eevee'),
(152, 'chikoirita');
CREATE TABLE ability (
number INT,
type VARCHAR(10),
height FLOAT,
weight FLOAT,
attack INT,
defense INT,
speed int
);
INSERT INTO ability (number, type, height, weight, attack, defense, speed)
VALUES (10, 'bug', 0.3, 2.9, 30, 35, 45),
(25, 'electric', 0.4, 6, 55, 40, 90),
(26, 'electric', 0.8, 30, 90, 55, 110),
(133, 'normal', 0.3, 6.5, 55, 50, 55),
(152, 'grass', 0.9, 6.4, 49, 65, 45);

/*
MISSION (1)
내 포켓몬 중에 몸무게가 가장 많이 나가는 포켓몬의 번호를 가져와 주세요.
*/
SELECT number FROM mypokemon
WHERE number = (SELECT number FROM ability ORDER BY weight DESC LIMIT 1);

SELECT number FROM ability
WHERE weight = (SELECT MAX(weight) FROM ability);

/*
MISSION (2)
속도가 모든 전기 포켓몬의 공격력보다 하나라도 작은 포켓몬의 번호를 가져와 주세요.
*/
SELECT number FROM ability
WHERE speed < ANY(SELECT attack FROM ability WHERE type = 'electric');

/*
MISSION (3)
공격력이 방어력보다 큰 포켓몬이 있다면 모든 포켓몬의 이름을 가져와 주세요.
*/
SELECT name FROM mypokemon
WHERE EXISTS(SELECT * FROM ability WHERE attack > defense);

/*
MISSION (1)
이브이의 번호 133을 활용해서, 이브이의 영문 이름, 키, 몸무게를 가져와 주세요.
이 때, 키는 height, 몸무게는 weight이라는 별명으로 가져와 주세요. 
*/
SELECT (SELECT name FROM mypokemon WHERE number = 133) AS name, height, weight
FROM ability
WHERE number = 133;

/*
MISSION (2)
속도가 2번째로 빠른 포켓몬의 번호와 속도를 가져와 주세요.
*/
SELECT number, speed
FROM (SELECT number, speed, RANK() OVER(ORDER BY speed DESC) AS speed_rank
FROM ability) AS A
WHERE A.speed_rank = 2;

/*
MISSION (3)
방어력이 모든 전기 포켓몬의 방어력보다 큰 포켓몬의 이름을 가져와 주세요.
*/
SELECT name
FROM mypokemon
WHERE number IN (
SELECT number 
FROM ability
WHERE defense > ALL(SELECT defense FROM ability WHERE type = 'electric')
    );

# cf) 3번은 JOIN을 이용하면 더 쉽게 풀수 있다
SELECT name FROM mypokemon
INNER JOIN ability
ON mypokemon.number = ability.number
WHERE defense > ALL(SELECT defense FROM ability WHERE type = 'electric');

 

---

❤️ 알아두면 좋은 고급 기능

• DELETE, UPDATE
  • WHERE문 조건식에 맞는 데이터 삭제 및 수정
  • ERROR 1175 : 테이블에 key가 없을 때 발생, Preference > SQL editor > Safe Updates 해제 후 OK 클릭 해 적용 > 다시 Local Instance 재접속
• CONSTRAINTS
  • 제약조건 : 데이터 입력규칙
  • 테이블을 만들거나 변경할 때 설정 → CREATE TABLE, ALTER TABLE 구문에서!
  제약 조건 의미

  NOT NULL	이 컬럼에는 NULL값을 저장할 수 없다
  UNIQUE	이 컬럼의 값들은 서로 다른 값을 가져야 한다
  DEFAULT	이 컬럼에 입력값이 없을 시 기본값을 설정한다
  PRIMARY KEY	이 컬럼은 테이블의 기본 키다
  NOT NULL과 UNIQUE 특징을 모두 가진다
  FOREIGN KEY	이 컬럼은 테이블의 외래 키다
  이 컬럼은 다른 테이블의 특정 컬럼을 참조한다

CREATE TABLE new_mypokemon(
	number INT PRIMARY KEY,
	name VARCHAR(20) UNIQUE,
	type VARCHAR(10) NOT NULL,
	attack INT DEFAULT 0,
	defense INT DEFAULT 100,
	FOREIGN KEY(number) REFERENCES mypokemon(number)
);
# FOREIGN KEY는 참조하는 테이블의 PRIMARY KEY이여야 함

 

• COMMIT, ROLLBACK, SAVEPOINT
  • 트랜잭션

# 트랜잭션 시작하기
START TRANSACTION;

# 트랜잭션 확정하기
COMMIT;

# 트랜잭션 이전으로 돌아가기
ROLLBACK;

# 세이브포인트 만들기
SAVEPOINT [세이브포인트 이름];

# 세이브포인트로 돌아가기
ROLLBACK TO [세이브포인트 이름];