[패스트캠퍼스] SQL로 시작하는 데이터 분석 첫걸음 / 3주차

3주차 SQL로 시작하는 데이터 분석 첫걸음

함수 조건은 다른 언어랑 비슷한 IF, CASE라서 이해가 금방 되었는데... 함수 만들기 실습은 좀 힘들긴 했다. 남의 꺼 쓰기도 어렵고 만들기는 더더욱 어렵고.. 느낌은 알겠는데 명확히가 어렵다. 연습만이 살길..! DAY8까지 오니 점점 낯선 키워드들이 생긴다. 내 언어로 이해하고 사용할 수 있도록 해봐야겠다. 길어질수록 쿼리 작성이 복잡해지는건 어쩔 수 없는 거 같다. 그래도 강사님이 설명 잘 해줘서 어느 정도 SQL 기본은 다 잡은 것 같다. 추후 실습을 많이 하면서 공부를 많이 해야겠다.

DAY 7. 규칙 만들기

01. 조건 만들기 (IF)

SELECT [컬럼 이름], IF(조건식, 참일때 값, 거짓일때 값)  FROM ~ : 조건식에 따른 결과를 새로운 컬럼으로 반환

SELECT [컬럼 이름], IFNULL([컬럼 이름], NULL 일때 값) FROM ~ : 컬럼이 NULL일 때 새로운 값으로 반환

 

02. 여러 조건 한번에 만들기 (CASE)

! 조건을 여러 개 만들 때 사용하는 문법

CASE 

     WHEN 조건식1 THEN 결과값1

     WHEN 조건식2 THEN 결과값2

     ELSE 결과값3
END;

: 조건식에 해당하면 결과값을 새로운 컬럼으로 반환

CASE [컬럼 이름]

     WHEN 조건값1 THEN 결과값1

     WHEN 조건값2 THEN 결과값2

     ELSE 결과값3
END;

: 해당 컬럼에서의 조건값과 일치하면 결과값을 새로운 컬럼으로 반환

ELSE 생략 시 NULL 반환

 

03. 함수 만들기 (CREATE FUNCTION)

SET GLOBAL log_bin_trust_function_creators = 1; : 사용자 계정에 함수 만들기 권한 생성

DELIMITER//

CREATE FUNCTION [함수 이름] ([입력값 이름][데이터 타입],...)

     RETURNS [결과값 데이터 타입]

BEGIN

     DECLARE [임시값 이름][데이터 타입];

     SET [임시값 이름] = [입력값 이름];

     쿼리; (SELECT ~ INTO ~)

     RETURN 결과값;

END

//

DELIMITER;

: 함수 만들기

DROP FUNCTION [함수 이름]; : 함수 지우기\

 

04. [실습] 함수를 만들고 사용해보자 (CREATE FUNCTION)

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
);
INSERT INTO mypokemon (number, name, type, attack, defense)
VALUES (10, 'caterpie', 'bug', 30, 35),
(25, 'pikachu', 'electric', 55, 40),
(26, 'raichu', 'electric', 90, 55),
(125, 'electabuzz', 'electric', 83, 57),
(133, 'eevee', 'normal', 55, 50),
(137, 'porygon', 'normal', 60, 70),
(152, 'chikoirita', 'grass', 49, 65),
(153, 'bayleef', 'grass', 62, 80),
(172, 'pichu', 'electric', 40, 15),
(470, 'leafeon', 'grass', 110, 130);

SET GLOBAL log_bin_trust_function_Creators = 1;
DELIMITER //
CREATE FUNCTION isStrong(attack int, defense int)
	RETURNS VARCHAR(20)
BEGIN
	DECLARE a int;
    DECLARE b int;
    DECLARE isstrong VARCHAR(20);
    SET a = attack;
    SET b = defense;
    SELECT CASE
			WHEN a+b > 120 THEN 'very strong'
			WHEN a+b > 90 THEN 'strong'
			ELSE 'not strong'
			END INTO isstrong;
    RETURN isstrong;
END
// DELIMITER ;

SELECT name, isStrong(attack, defense) AS isStrong
FROM mypokemon;

DAY 8. 테이블 합치기

01. 테이블 합치기 (JOIN)

INNER JOIN

LEFT JOIN

RIGHT JOIN

OUTER JOIN

CROSS JOIN

SELF JOIN

 

02. 기준으로 테이블 합치기 (INNER JOIN)

JOIN ( = INNER JOIN) : 교집합, 두 테이블 모두에 있는 값만 합치기

SELECT [컬럼 이름] FROM [테이블A 이름] INNER JOIN [테이블B 이름] ON [테이블A 이름].[컬럼 A 이름]=[테이블B 이름].[컬럼B 이름] WHERE 조건식;

 

03. 한쪾을 기준으로 테이블 합치기 (LEFT, RIGHT JOIN)

LEFT JOIN : 왼쪽 테이블에 있는 값만 합치기

SELECT [컬럼 이름] FROM [테이블A 이름]

LEFT JOIN [테이블B 이름]

ON [테이블A 이름].[컬럼 A 이름]=[테이블B 이름].[컬럼B 이름] WHERE 조건식;

RIGHT JOIN : 오른쪽 테이블에 있는 값만 합치기

SELECT [컬럼 이름] FROM [테이블A 이름]

RIGHT JOIN [테이블B 이름]

ON [테이블A 이름].[컬럼 A 이름]=[테이블B 이름].[컬럼B 이름] WHERE 조건식;

 

04. 다양한 방식으로 테이블 합치기 (OUTER, CROSS, SELF JOIN)

OUTER JOIN  : 합집합, 두 테이블에 있는 모든 값 합치기 *MySQL에 없음! 

∴  LEFT JOIN과 RIGHT JOIN을 UNION(중복 제외 합치기)을 통해 구현

SELECT [컬럼 이름] FROM [테이블A 이름]

LEFT JOIN [테이블B 이름]

ON [테이블A 이름].[컬럼 A 이름]=[테이블B 이름].[컬럼B 이름]

UNION

SELECT [컬럼 이름] FROM [테이블A 이름]

RIGHT JOIN [테이블B 이름]

ON [테이블A 이름].[컬럼 A 이름]=[테이블B 이름].[컬럼B 이름];

CROSS JOIN : 두 테이블에 있는 모든 값을 각각 합치기, A테이블 row a개. B테이블 row b개 > CROSS JOIN row a*b 개

SELECT [컬럼 이름] FROM [테이블A 이름] CROSS JOIN [테이블B 이름] ON [테이블A 이름].[컬럼 A 이름]=[테이블B 이름].[컬럼B 이름] WHERE 조건식;

SELF JOIN : 별명으로 테이블 이름 구분 필요

SELECT [컬럼 이름] FROM [테이블A 이름] AS t1 INNER JOIN [테이블A 이름] AS t2 ON t1.[컬럼 A 이름]=t2.[컬럼B 이름] WHERE 조건식;

 

05. [실습] 다양한 방식으로 테이블을 합쳐보자 (JOIN)

DROP DATABASE IF EXISTS pokemon
;

CREATE DATABASE pokemon
;

USE pokemon
;

CREATE TABLE mypokemon
(

number INT,
name VARCHAR(20),
type VARCHAR(10)
);
INSERT INTO mypokemon (number, name, type)
VALUES (10, 'caterpie', 'bug'),
(25, 'pikachu', 'electric'),
(26, 'raichu', 'electric'),
(133, 'eevee', 'normal'),
(152, 'chikoirita', 'grass');
CREATE TABLE ability (
number INT,
height FLOAT,
weight FLOAT,
attack INT,
defense INT,
speed int
);
INSERT INTO ability (number, height, weight, attack, defense, speed)
VALUES (10, 0.3, 2.9, 30, 35, 45),
(25, 0.4, 6, 55, 40, 90),
(125, 1.1, 30, 83, 57, 105),
(133, 0.3, 6.5, 55, 50, 55),
(137, 0.8, 36.5, 60, 70, 40),
(152, 0.9, 6.4, 49, 65, 45),
(153, 1.2, 15.8, 62, 80, 60),
(172, 0.3, 2, 40, 15, 60),
(470, 1, 25.5, 110, 130, 95);

USE pokemon;
SELECT name, attack, defense FROM mypokemon LEFT JOIN ability ON mypokemon.number = ability.number;
SELECT ability.number, name FROM mypokemon RIGHT JOIN ability ON mypokemon.number = ability.number;

DAY 9. 여러 테이블 한번에 다루기

01. 여러 테이블 한번에 다루기 (집합 연산)

집합 연산 : 합집합, 교집합, 차집합을 쿼리로 만들어 실행

 

02. 데이터에 데이터 더하기 (UNION, UNION ALL)

UNION, UNION ALL : 합집합 표현

[쿼리 A] UNION [쿼리 B] : 로우 전체가 동일한 값 제외

[쿼리 A] UNION ALL [쿼리 B] : 로우 전체가 동일한 값 포함

* [쿼리 A] [쿼리B] 결과 값 개수 같아야 함.

* ORDER BY는 쿼리 가장 마지막에 작성, [쿼리 A] 컬럼으로만 가능

 

03. 데이터에서 데이터 빼기 (교집합, 차집합)

INTERSECT : 교집합 *MySQL에 없음! 

INNER JOIN으로 표현, ON 모든 컬럼을 JOIN의 기준으로 합쳐줘야함

ex) INNER JOIN [테이블 이름] ON [테이블이름].[컬럼이름] AND ~ ;

MINUS : 차집합 *MySQL에 없음! 

LEFT JOIN으로 표현, ON 모든 컬럼을 JOIN의 기준으로 합쳐줘야함 WHERE B.[컬림이름] IS NULL; (B에 NULL인 데이터)

 

04. [실습] 여러 테이블의 데이터를 한 번에 조회해보자 (UNION)

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

USE pokemon;

SELECT distinct type
FROM mypokemon 
UNION
SELECT distinct type
FROM friendpokemon;

SELECT number, name
FROM mypokemon
WHERE type = 'grass'
UNION ALL
SELECT number, name
FROM friendpokemon
WHERE type = 'grass';

DAY 10. 조건에 조건 더하기

01. 조건에 조건 더하기 (서브 쿼리)

GROUP BY 에서는 서브 쿼리 활용 X

메인 쿼리와 똑같은 문법

; 을 붙이지 않아도 됨

반드시 괄호 안에 있어야 함

 

02. SELECT 절의 서브 쿼리
= 스칼라 서브 쿼리, 결과값이 반드시 하나의 값

SELECT (SELECT [컬럼 이름] FROM [테이블 이름] WHERE 조건식)

 

03. FROM 절의 서브 쿼리

= 인라인 뷰 서브쿼리, 결과값이 반드시 하나의 테이블, 반드시 별명

FROM (SELECT [컬럼 이름] FROM [테이블 이름] WHERE 조건식) AS [테이블 별명]

 

04. WHERE 절의 서브 쿼리

= 중첩 서브쿼리, 결과값이 반드시 하나의 컬럼

WHERE [컬럼 이름] [연산자] (SELECT [컬럼 이름] FROM [테이블 이름] WHERE 조건식)

* 비교 연산자만 사용 시 서브쿼리의 결과값은 반드시 하나의 값

* 주요 연산자 사용 시 서브쿼리의 결과값은 반드시 하나의 컬럼

주요 연산자

IN([서브 쿼리]) : 서브쿼리 결과값 내에 있음

EXISTS([서브 쿼리]) : 서브쿼리의 결과값 존재 * 여러 컬럼 가능

NOT EXISTS([서브 쿼리]) : 서브쿼리의 결과값 존재 X * 여러 컬럼 가능

ALL([서브 쿼리]) : 서브쿼리의 모든 값에 대해 (비교 연산자 함께 사용)

ANY([서브 쿼리]) : 서브쿼리 중 하나라도 (비교 연산자 함께 사용)

 

05. [실습] 서브쿼리로 복잡한 조건을 하나의 쿼리로 만들어보자 (SubQuery)

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

USE pokemon;

SELECT number FROM ability WHERE weight = (SELECT MAX(weight) FROM ability);
SELECT number FROM ability WHERE speed < ANY(SELECT attack FROM ability WHERE type = 'electric');
SELECT name FROM mypokemon WHERE EXISTS (SELECT * FROM ability WHERE attack > defense);

DAY 11. 알아두면 좋은 고급 기능

01. 데이터 삭제, 수정하기 고급 (DELETE, UPDATE)

DELETE FROM [테이블 이름] WHERE 조건식;

UPDATE [테이블 이름] SET [컬럼 이름] = [새 값] WHERE 조건식;

 

02. 제약 조건 (CONSTRAINTS)

: 데이터 입력 시 실행되는 데이터 입력 규칙

CREATE TABLE/ ALTER TABLE 에서 사용

종류

NOT NULL : NULL 값을 저장할 수 없음

UNIQUE : 서로 다른 값을 가져야 함

DEFAULT 값: 입력값이 없는 경우 기본값 설정

PRIMARY KEY : 테이블의 기본 키, NOT NULL과 UNIQUE의 특징을 지님

FOREIGN KEY [컬럼 이름] REFERENCES [다른 테이블 이름] : 테이블의 외래 키, 다른 테이블의 특정 컬럼(PRIMARY KEY)참조

 

03. 권한과 DCL (GRANT, REVOKE)

DDL : 데이터 정의어

DML : 데이터 조작어

DCL (Data Control Language) : 데이터 제어어

TCL : 트랜잭션 제어어

! 사용자에 따라 데이터 접근 권한 (테이블, 기능) 명시 가능

USE mysql;

SELECT user, host FROM user; : 사용자 조회

CREATE USER [사용자 이름]@[ip주소] IDENTIFIED BY '[사용자 비밀번호]'; : 사용자 생성

DROP USER [사용자 이름]; : 사용자 삭제

GRANT [권한] ON [데이터 베이스 이름].[테이블 이름] TO [사용자 이름]@[ip주소]; : 권한 부여

SHOW GRANTS FOR [사용자 이름]@[ip주소]; : 권한 확인

REVOKE [권한] ON [데이터 베이스 이름].[테이블 이름] FROM[사용자 이름]@[ip주소]

FLUSH PRIVILEGES; : 권한 적용

[권한]

ALL PRIVILEGES : 모든 권한

04. 트랜잭션과 TCL (COMMIT, ROLLBACK, SAVEPOINT)

트랜잭션 : 데이터베이스의 데이터 상태를 바꾸는 작업

START TRANSACTION; : 트랜잭션 시작

COMMIT; : 트랜잭션 저장

ROLLBACK; : 트랜잭션 삭제

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

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

 

본 강의는 국비지원교육입니다.