[패스트캠퍼스: SQL 데이터 분석 첫걸음] Week 1 - SQL과 친해지기

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

https://drive.google.com/drive/folders/1Etbo7qDoP7rGUHOfSU3SZP1yFuMJ6vWP

 

DAY 2

---

데이터:

- 함수를 통해 타입 변환 가능

• 정수형- 1,2,3,4(INT),8 Bytes
• 실수형 - 4(FLOAT),8 Bytes
• 문자형
  - 고정:CHAR(n), 변동:VARCHAR(n)
  - 문자열화 필요 (" ", ' ')
• 날짜형 - 1, 3, 4 Bytes

---

데이터베이스(database, schema, set of tables)

- 테이블(table)

- 열(column, field): 데이터 타입 필요

- 행(row, record)

다양한 명령을 통해 테이블에 대한 CRUD 조작 가능

---

FastCampus: SQL CheatSheet Day2

MISSION: pokemon

/*
 * START MISSION
 */
CREATE DATABASE IF NOT EXISTS pokemon;
USE pokemon;

/*
 * MISSION 1
 * CREATE TABLE mypokemon
 */
CREATE TABLE IF NOT EXISTS mypokemon(
  number INT,
  name VARCHAR(20),
  type VARCHAR(10)
);
TRUNCATE TABLE mypokemon;
INSERT INTO mypokemon
  (number, name, type)
VALUES
  (10, "caterpie", "bug"),
  (25, "pikachu", "electic"),
  (133, "eevee", "normal");
-- RESULT
SELECT *
FROM mypokemon;

/*
 * MISSION 2
 * CREATE TABLE mynewpokemon
 */
CREATE TABLE IF NOT EXISTS mynewpokemon(
  number INT,
  name VARCHAR(20),
  type VARCHAR(10)
);
TRUNCATE TABLE mynewpokemon;
INSERT INTO mynewpokemon
  (number, name, type)
VALUES
  (77, "포니타", "불꽃"),
  (132, "메타몽", "노말"),
  (151, "뮤", "에스퍼");
-- RESULT
SELECT *
FROM mynewpokemon;

/*
 * MISSION 3
 * ALTER TABLES
 */
ALTER TABLE mypokemon CHANGE COLUMN name eng_name VARCHAR(20);
ALTER TABLE mypokemon RENAME myoldpokemon;
ALTER TABLE mynewpokemon CHANGE COLUMN name kor_name VARCHAR(20);
-- RESULT
SELECT * FROM myoldpokemon;
SELECT * FROM mynewpokemon;

/*
 * MISSION 4
 * TRUNCATE, DROP TABLES
 */
TRUNCATE TABLE myoldpokemon;
DROP TABLE mynewpokemon;
-- RESULT
SHOW TABLES;
SELECT * FROM myoldpokemon;

/*
 * MISSION ENDED
 */
DROP DATABASE IF EXISTS pokemon;

 

 

DAY 3

---

SELECT DISTINCT AS FROM LIMIT

---

FastCampus: SQL CheatSheet Day3

MISSION: SELECT [ ] FROM pokemon;

/*
 * MISSION READY 1~3
 */
SELECT 123 * 456; 
SELECT 2310 / 30;
SELECT "피카츄" AS "포켓몬";

/*
 * START MISSION
 */
DROP DATABASE IF EXISTS pokemon;
CREATE DATABASE IF NOT EXISTS pokemon;
SHOW DATABASES;
USE pokemon;
SHOW TABLES;
CREATE TABLE mypokemon (
    number INT,
    name VARCHAR(20),
    type VARCHAR(20),
    height FLOAT,
    weight FLOAT,
    attack INT,
    defense INT,
    speed INT
);
INSERT INTO mypokemon
	(number, name, type, height, weight, attack, defense, speed)
VALUES
	(10, 'caterpie', 'bug', 0.3, 2.9, 30, 35, 45),
	(25, 'pikachu', 'electric', 0.4, 6, 55, 40, 90),
	(26, 'raichu', 'electric', 0.8, 30, 90, 55, 110),
	(133, 'eevee', 'normal', 0.3, 6.5, 55, 50, 55),
	(152, 'chikoirita', 'grass', 0.9, 6.4, 49, 65, 45);
    
/*
 * MISSION 4~15
 */
SELECT * FROM mypokemon;
SELECT name FROM mypokemon;
SELECT name, height, weight FROM mypokemon;
SELECT DISTINCT height FROM mypokemon;
SELECT name, attack*2 AS "attack2" FROM mypokemon;
SELECT name AS "이름" FROM mypokemon;
SELECT attack AS "공격력", defense AS "방어력" FROM mypokemon;
SELECT height*100 AS "height(cm)" FROM mypokemon;
SELECT * FROM mypokemon LIMIT 1;
SELECT name AS "영문명", height AS "키(m)", weight AS "몸무게(kg)" FROM mypokemon LIMIT 2;
SELECT name, attack+defense+speed AS "total" FROM mypokemon;
SELECT name, weight / (height*height) AS "BMI" FROM mypokemon;

DROP DATABASE pokemon;

** MISSION 15: ^ (caret)을 통해 거듭제곱 연산이 가능하다고 강의에 나왔는데, 찾아보니 ^는 XOR 비트 연산자였다.
MySQL :: MySQL 8.0 Reference Manual :: 12.12 Bit Functions and Operators

MySQL :: MySQL 8.0 Reference Manual :: 12.12 Bit Functions and Operators

 

DAY 4

---

SELECT [] FROM [] WHERE []

비교, 논리 연산자

BETWEEN (from) AND (to)

IN (list)

LIKE (exp: % | _)

IS (NOT) NULL (주의! 비교 연산자 사용 불가)

 

---

FastCampus: SQL CheatSheet Day4

MISSION: SELECT [ ] WHERE[cond]

 

/*
 * START MISSION
 */
SHOW DATABASES;
USE pokemon;
SHOW TABLES;
SELECT * FROM mypokemon;
/*
 * MISSION 1~14
 */
SELECT type FROM mypokemon WHERE name='eevee';
SELECT attack, defense FROM mypokemon WHERE name='caterpie';
SELECT * FROM mypokemon WHERE weight > 6;
SELECT name FROM mypokemon WHERE height > 0.5 AND weight >= 6;
SELECT name AS "weak_pokemon" FROM mypokemon WHERE attack < 50 OR defense < 50;
SELECT * FROM mypokemon WHERE type!="normal";
SELECT name, type FROM mypokemon WHERE type IN ('normal','fire','water','grass');
SELECT name, attack FROM mypokemon WHERE attack BETWEEN 40 AND 60;
SELECT name FROM mypokemon WHERE name LIKE "%e%";
SELECT * FROM mypokemon WHERE name LIKE "%i%" AND speed <= 50;
SELECT name, height, weight FROM mypokemon WHERE name LIKE "%chu";
SELECT name, defense FROM mypokemon WHERE name LIKE "%e" AND defense < 50;
SELECT name, attack, defense FROM mypokemon WHERE abs(attack - defense) >= 10;
SELECT name, attack+defense+speed AS total FROM mypokemon WHERE attack+defense+speed >= 150;

** MISSION 13: abs 대신 논리 연산자 AND로 차를 표현할 수 있음

 

 

---

기존 프로그래밍과 비교했을 때, SQL 쿼리는 Python처럼 인터프리터 환경에서 실행하는 느낌이다.

하지만 사용해본 결과, 변수를 자유롭게 활용할 수 있는 환경은 아닌 것 같다.

SET @v_var1 = "asd"; 이렇게 v_var1이라는 변수를 선언하더라도, 조건절 등 사용 제약이 많다.

IF EXISTS도 오류 최소화를 위해 적극 활용해봤는데, ALTER TABLE 쿼리에서 TABLE 존재 여부에 대한 확인에는 적용이 안 되는 것을 확인했다. 즉, 문법이 자유롭지 않고 특정한 동작에 대한 명령이 정해져 있는 느낌이다.

일반적으로는 SELECT 문에 대한 서브 쿼리로 활용한다고 한다.
MySQL :: MySQL 8.0 Reference Manual :: 13.2.15.6 Subqueries with EXISTS or NOT EXISTS

당분간 SQL 메뉴얼을 기반으로 많이 찾아보고 학습할 예정이다.