[패스트캠퍼스: SQL 데이터 분석 첫걸음] Week 3 - SQL 고급 기능 익히기

본 게시글은 내일배움카드로 신청한 패스트캠퍼스의 국비지원교육 강의: SQL 데이터 분석 첫걸음입니다.
본 게시글은 패스트캠퍼스의 열공 챌린지 형식을 준수하며, 개인적인 정리 목적으로 학습 내용을 정리한 글입니다.
- 제목, 본문, 해쉬태그 키워드 포함
- 사진 1장 이상, 글자수 500자 이상 (공백 포함, 코드 제외)

 

강의 자료: https://drive.google.com/drive/folders/1Etbo7qDoP7rGUHOfSU3SZP1yFuMJ6vWP

---

DAY 9

UNION (ALL)

동일한 데이터 = 모든 column 값

 

INTERSECT (A AND B)

SELECT T,col

FROM v_tname1 AS T1

INNER JOIN ON v_tname2 AS T2

ON [ conds (AND) ... ] 

 

MINUS(차집합; A - B)

SELECT A.col ...

WHERE B.col IS NULL

/*
 * READY MISSION
 */
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~2 
 */
SELECT DISTINCT type
FROM mypokemon
UNION
SELECT DISTINCT type
FROM friendpokemon;

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";

 

 

---

DAY 10

QUERY: MAIN(OUTER) > SUB(INNER)

SUB-QUERY 1. 소괄호 ( ) 안에 delimiter 없이 작성 2. GROUP BY 절에는 사용 불가

 

SELECT (Scalar SubQuery: 반드시 하나의 값이어야함)

- 이전 강의에서 테이블 출처를 만들었던 것과 같은 느낌

 

FROM (Inline-view  SubQuery: 새로운 별명을 갖는 하나의 테이블이어야 함)

- 기존 FROM 절에 테이블이 왔던 것처럼, 문법상 테이블이 오는 자리

 

WHERE (Nested SubQuery: 요청할 데이터는 Vector, Scalar 모두 가능하며, 결과값을 Boolean형으로 만들어주면 된다)

Vector형: IN, ALL, ANY

- IN: 리스트에 존재하는지 검사

- ALL / ANY: map 함수처럼 앞에 주어지는 연산을 리스트 내 모든 값에 적용하되, 연산 결과에 모두 AND / OR 적용

- " > ALL "은 " > MAX "와 같고 " < ALL "은 " < MAX "와 같다.

- EXISTS는 존재 여부만 판별하기 때문에 데이터형 무관

/*
 * READY MISSION
 */
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);
       

/*
 * START MISSION 1~3
 */

SELECT * FROM mypokemon;
SELECT * FROM ability;

/*
 * MISSION 1
 * GET number WHICH weight = MAX(weight)
 */

SELECT number, weight FROM ability
-- WRONG QUERY: WHERE weight = MAX(weight)
WHERE weight = (
	SELECT MAX(weight) FROM ability
);

/*
 * MISSION 2
 * GET number
 * WHICH speed < ANY("type:electic".attack)
 * SAME AS speed < MAX("type:electic".attack)
 */

-- SOLUTION #1
SELECT number, attack, speed, type FROM ability
WHERE speed < ANY(
	SELECT attack FROM ability
	WHERE type = "electric"
);

-- SOLUTION #2
SELECT number, attack, speed, type FROM ability
WHERE speed < (
	SELECT MAX(attack) FROM ability
	WHERE type = "electric"
);

/*
 * MISSION 3-1
 * GET ALL RECORDS OF mypokemon.name
 * IF EXIST ability.attack > ability.defense
 * MATCHED BY number
 */

-- SELECT name FROM mypokemon WHERE EXISTS ();
-- SELECT number FROM ability WHERE attack > defense;
SELECT name FROM mypokemon
WHERE EXISTS (
	SELECT number FROM ability WHERE attack > defense
);

/*
 * MISSION 3-2
 * GET mypokemon.name
 * WHICH ability.attack > ability.defense
 * MATCHED BY number
 */
SELECT name FROM mypokemon
WHERE number IN (
	SELECT number FROM ability WHERE attack > defense
);

---

DAY 11

U/D operation on records

1. DELETE FROM [table] WHERE [cond];
2. UPDATE [table] SET [col] = [value] WHERE [cond];
   - FROM이 사용되지 않는다!

** Safe Update를 해제할 수도 있지만, 이는 불분명한 연산을 방지하기 위한 안전장치이므로, 분명한 쿼리를 작성하는 습관을 들이는 것이 더 중요하다. MySQL error code: 1175 during UPDATE in MySQL Workbench - Stack Overflow 

해결책: UPDATE 쿼리의 WHERE 절에 Key 값을 포함시키면 된다.

　단, Key가 없는 테이블에서 어쩔 수 없이 연산을 수행할 때에는

　　SET SQL_SAFE_UPDATES = 0;
　　# your code SQL here
　　SET SQL_SAFE_UPDATES = 1;

　와 같은 방식으로 사용하도록 하자!

 

CONSTRAINTS

USAGE: CREATE / ALTER TABLE

• NOT NULL: col.val != NULL
• UNIQUE: col.val != ANY(col)
• DEFAULT: IF col.val = NULL THEN SET col = default
• PRIMARY KEY: PK
• FOREIGN KEY: FK는 참조하는 테이블의 PK이어야 함
  FOREIGN KEY() REFERENCES table(col)

 

SQL 분류

 

사용자와 권한 관리

USE mysql;
SHOW TABLES;
SELECT host, user FROM user;

CREATE USER dummy@"%"; -- SAME AS: CREATE USER dummy;
CREATE USER dummy@127.0.0.1;
CREATE USER dummy@localhost;

SELECT host, user FROM user WHERE user LIKE "dummy%";

DROP USER dummy;
DROP USER dummy@127.0.0.1;
DROP USER dummy@localhost;

-- GRANT ON TO
-- SHOW GRANTS FOR
-- REVOKE ON FROM
-- FLUSH PRIVILEGES

 

- 동일한 이름을 가진 user이더라도, 정의한 접속 위치에 따라 다른 id가 된다.

- 접속 위치를 생략하는 것은 @"%"와 동일하나, localhost와 127.0.0.1은 다른데, 그 이유는 localhost 접속 ip가 변경 가능하기 때문일지도 모른다. (Can I change my localhost IP address from 127.0.0.1 to something else? - Quora)

** 어차피 자주 사용하지 않는 기능이라 필요할 때마다 찾아서 쓰면 된다.

 

TCL

트랜잭션

START TRANSCATION;

SAVEPOINT [name];

ROLLBACK ( TO [name] );

COMMIT;

---

DAY 12에서는 데이터 분석 분야의 직무와 로드맵을 간단히 소개했다.

Part 1을 모두 수강하니, 학부 때 배운 내용들을 상기하고, 더불어 기초적인 뼈대를 잡는데 큰 도움이 됐다.

다만, 확실히 강사님의 내공이 부족하다는 느낌을 받았다. 다음 파트가 기대된다.