패스트캠퍼스 ㅣ 데이터분석부트캠프 18기 8주차 ㅣ MYSQL 기본 용어

 

MYSQL

 

DAY 3

1. FUNCTIONS (함수)

: 미리 만들어져있는 기능을 사용하는 방식

 

1) 단일행 함수 : 특정 컬럼의 데이터 1개에 특별한 기능을 정용해 결과를 출력함 , ROW 데이터의 갯수와 동일하게 출력

- CEIL, ROUND, TUNCATE, CONCAT, DATE_FORMAT

CEIL	소수점 자리를 올림해 정수로 출력  but, 소수점을 지정할수 없음	SELECT CEIL(12.345);
ROUND	소수점 3번째 자리에서 반올림해, 소수점 2번째 자리까지 출력	SELECT ROUND(12.345, 2);
TRUNCATE	소수점 3번째 자리에서 버림해, 소수점 2번째 자리까지 출력	SELECT TRUNCATE(12.345, 2);
CONCAT	데이터베이스의 테이블에서 '국가이름(국가코드)' 와 같은 포멧의 문자열로 출력되는 컬럼을 생성하여 출력	SELECT code, name, CONCAT(code, '(', name, ')') AS code_name 3 FROM country;
DATE_FORMAT	데이터베이스의 테이블에서 요금지불일 (payment_date)의 날짜 포멧을 '년-월'로 변경하고, monthly 컬럼을 추가해 데이터를 출력	SELECT payment_date, DATE_FORMAT(monthly, '%Y-%M') AS monthly FROM payment;

 

 

2) 다중행 함수 : 특정 컬럼에 있는 모든 데이터에 특별한 기능을 적용해 결과를 출력함

- COUNT, SUM, AVG, MIN, MAX 등등

- 다중행 함수는 결과가 1개의 row로 출력됨

- 여러개의 컬럼에 적용해 사용할 수 있음

COUNT()	데이터베이스의 테이블에서 인구수 100만 이상인 도시의 갯수 출력	SELECT COUNT(*) AS upper_100 FROM city WHERE population >= 100 * 10000;
SUM()	데이터베이스의 테이블에서 인구수 Asia 대륙의 총인구수 출력	SELECT SUM(population) FROM country WHERE continent = 'Asia';
AVG()	데이터베이스의  테이블에서 전세계의 인구와 평균GNP 출력	SELECT SUM(population) AS total_population, AVG(gnp) AS avg_gnp FROM country;

 

 

 

2. CONDITION (조건문)

: 특정 조건에 따라서 출력되는 결과를 다르게 하는 문법, 함수와 명령어를 사용해 조건문을 구현함

 

1) IF : 1가지 조건의 논리값(True, False)에 따라서 결과를 출력함

- IF(Condition, True Data, False Data)

 

예시)  데이터베이스의 테이블에서 도시의 인구가 100만 이상이면 'big', 아니면 'small'을 출력하는 scale 컬럼 추가

SELECT countrycode, name, population
           , IF(scale >= 100 * 10000, 'big', 'small') AS scale
FROM city;

 

2) IFNULL 

- IFNULL(True Data, False Data)

 

예시) 데이터베이스의 테이블에서 독립년도(indepyear)가 NULL 데이터는 0으로 출력 

SELECT code, name, ISNULL(indepyear, 0) AS indepyear
FROM country;

 

3) CASE WHEN THEN ELSE : 조건이 2가지 이상인 경우, 출력되는 결과를 다르게 할 때 사용되며 명령어로 구현함

- 기본구조

CASE

             WHEN condition1 THEN data1

             WHEN condition2 THEN data2

             ELSE data3

END AS column_name

 

 

 

3. GROUP BY / HAVING

- GROUP BY : 특정 컬럼의 중복데이터를 경합해 결과를 출력함

- 중복데이터를 결합할 컬럼을 설정하고, 다른 컬럼은 어떤 방법으로 데이터를 결합할지에 대해

다중행함수를 사용하여 SQL을 작성

- GROUP BY 명령어 뒤에 중복데이터를 결합할 컬럼을 작성함

- GROUP BY 명령어 뒤에 ,를 이용하여 여러개의 컬럼을 그룹핑하여 데이터를 출력할수 있습니다.

ex) GROUP BY continent, region;

 

 

- HAVING : 쿼리 실행 후 결과데이터에서 데이터를 필터링해 결과를 출력함

예시) 데이터베이스의  테이블에서 대륙(continent)별 총인구(total_population)에서 5억 이상인 대륙의 데이터 출력

 

SELECT continent, SUM(population) AS total_population 7
FROM country
GROUP BY continent
HAVING total_population >= 50000 * 10000;

 

- WITH ROLLUP : 여러개의 컬럼을 그룹핑하고, 그룹별 총합을 출력하는 ROW를 추가하는 명령어

 

 

 

4. UNION

: sql 쿼리를 실행한 결과를 결합해 출력할 수 있음

 

예시) 데이터베이스의 테이블에서 첫번째 ROW에는 아시아(asia)의 총인구수(total_population)와
총GNP(total_gnp)출력하고, 두번째 ROW에는 아프리카(africa)의 총인구수와 총GNP를 출력

 

SELECT 'Asia' AS continent, SUM(poplulation) AS total_population, SUM(gnp) AS total_gnp
FROM countrycode;
WHERE continent = 'Asia'
UNION
SELECT 'Africa' AS continent, SUM(poplulation) AS total_population, SUM(gnp) AS total_gnp
FROM countrycode
WHERE continent = 'Africa';

 

 

 

DAY 4

 

 

 

1. JOIN

: 특정 컬럼을 기준으로 두 개의 테이블을 컬럼으로 결합

- INNER, LEFT, RIGHT, OUTER, CROSS, SELF

- JOIN을 잘못하면 시스템에 많은 과부하를 줄 수 있음

- 과부하 문제를 해결하려면, 서브쿼리를 사용해서 데이터의 양을 줄이거나 / INDEX를 사용해서 쿼리의 탐색속도를 빠르게 하거나/ 서버의 성능을 높이는 방법이 있음

 

1) INNER JOIN

SELECT user.ui, user.name, addr.an
FROM user
(INNER) JOIN addr
ON user.ui = addr.ui;

 

★INNER 생략 가능

 

2) LEFT JOIN

SELECT user.ui, user.name, addr.an
FROM user
LEFT JOIN addr
ON user.ui = addr.ui;

 

3) RIGHT JOIN

SELECT addr.ui, user.name, addr.an
FROM user
RIGHT JOIN addr
ON user.ui = addr.ui;

 

4) OUTER JOIN

SELECT user.ui, user.name, addr.an6
FROM user
LEFT JOIN addr
ON user.ui = addr.ui
UNION
SELECT addr.ui, user.name, addr.an
FROM user
RIGHT JOIN addr
ON user.ui = addr.ui;

 

★별도의 명령어가 없어 UNION을 이용해 구현해야함.

 

 

 

2. SUB QUERY

: 쿼리 안에 쿼리를 작성 가능

- SELECT, FROM(JOIN), WHERE(HAVING) 사용 가능

- FROM : 쿼리의 실행 순서를 변경할 때, 쿼리 실행결과를 쿼리로 실행하고 싶을 때

- 단점 : 코드의 복잡도가 증가함 -> 이를 해결하기 위해 사용하는 방법 : VIEW, WITH

 

 

 

3. VIEW

: 가상의 테이블

- 실제로 데이터를 저장하지않음 -> 데이터를 추가, 수정, 삭제하지않음

- 복잡한 쿼리를 단순화함. READ만 사용가능해 데이터 보안을 향상시킴.

 

- VIEW 생성 포맷

CREATE VIEW <view name> AS <view query>

 

 

 

4. WITH

: 본 쿼리를 실행하기 전에, 미리 실행한 임시쿼리를 실행한 결과를 본 쿼리에서 사용할 수 있도록 함

- CTE (공통테이블 표현식)을 표현하기 위한 구문

- MYSQL 8.0버전부터 사용가능

 

- 기본구조
WITH cte_name AS (
             SQL Query
)
SELECT columns
FROM cte_name;

 

 

 

5. INDEX

: 테이블의 컬럼을 기준으로 설정 가능함

- 장점 : READ의 속도를 빠르게 해줌

- 단점: CREATE, UPDATE, DELETE 속도가 느려짐 / 저장공간 10%정도 더 사용함

- 효율적인 사용방법: WHERE 구문에서 자주 사용되는 컬럼을 INDEX로 설정

 

- INDEX 종류

1) 클러스터형 인덱스 : PRIMARY KEY 설정된 컬럼이 적용 -> 속도가 빨라지는 것보다 데이터 정렬을 위해 사용함

2) 보조 인덱스 : 일부 데이터만 INDEX로 사용해 속도가 빨라지게 하는 인덱스

 

 - 기본구조

INDEX  목록 확인 -> INDEX 사용 X 실행시간 확인 -> INDEX 생성 -> INDEX 목록 확인

-> INDEX 사용 O 실행시간 확인 -> 실행계획 확인 : EXPLAIN 활용(쿼리 실행 전 쿼리가 INDEX 사용하는지 확인)

-> INDEX 삭제

 

 

 

6. TRIGGER

:  특정 테이블을 감시하고 있다가 설정한 조건에 대한 쿼리가 실행되면, 
미리 설정해놓은 쿼리가 자동으로 실행되도록하는 방법

- 간단한 백업이나 쿼리 실행 시 다른 테이블 데이터를 추가, 수정할 때 많이 사용함

 

- 기본 구조
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} 
ON table_name FOR EACH ROW
BEGINE 
            trigger_body; 
END; 

 

예시)

DELIMITER $$
           CREATE TRIGGER chat_bak_tr
           BEFORE DELETE ON chat
           FOR EACH ROW BEGIN
                      INSERT INTO chat_bak(chat_id, msg)
                      VALUES(old.chat_id, old.msg);
           END $$