먼저 시작하기에 앞서 오늘 예제로 쓰일 테이블

CREATE TABLE computerValue(

id int,
cpu_value int,
memory_value int,
graphics_card_value int,
time_value datetime

)

실험 대상 : MariaDB 10.1.41가 설치 돼있는 DB 서버

목적 : 데모데이터를 위한 1000 rows 데이터가 들어있는 csv 파일 import 하기

LOAD DATA INFILE

개념

텍스트 파일을 읽어서 테이블로 데이터를 입력하는(import 하는) 명령어이다.

사용 이유

일일이 INSERT 구문을 쓰는 노가다(반복작업) 방지.

기본 INSERT 구문을 쓰는 것보다 15~20배 정도 속도가 빠름.

DB to DB 데이터 마이그레이션 작업 할 때 사용

조건

파일 이름은 알파벳 문자열로 주어져야 한다.

import 하려는 파일 즉,데이터로 쓰려고 하는 파일의 권한을 가지고 있어야됨

 - 데이터베이스 디렉토리에 존재

 - 누구나 읽을수 있는 파일 권한

문법

표준 양식

LOAD DATA INFILE '{file_name}'

    INTO TABLE {table_name}
    CHARACTER SET utf8
    FIELDS
       TERMINATED BY '{field_terminator}'  # 각 필드 구분 문자 (예: CSV라면 컴마)
        OPTIONALLY ENCLOSED BY '"'  # 필요할 경우, 따옴표(")로 구분
    LINE TERMINATED BY '\n'
    IGNORE 1 LINES  # 제목이 포함된 첫 번째 줄은 생략
    (col1, col2, ... )  # 컬럼명

상황1. Linux(리눅스)

/usr/local/garaDate.csv 라는 파일 import 하기

LOAD DATA INFILE '/usr/local/garaData.csv'
REPLACE INTO TABLE `testDB`.`computerValue` COLUMNS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id, @cpu_value, @memory_value,@graphics_card_value,@time_value)
SET `id` = @id, `cpu_value` = @cpu_value, `memory_value` = @memory_value,
`graphics_card_value` = @graphics_card_value,
`time_value` = STR_TO_DATE(@time_value,'%Y-%m-%d %H:%i');

주의 : LOAD DATA LOCAL INFILE이 아니라 LOAD DATA INFILE 입니다.

LOAD DATA LOCAL INFILE

개념

Local 옵션을 넣을 수 있다.

말 그대로 Linux(리눅스) DB 서버에 있는 파일을 넣는게 아닌

Local에 있는 파일, 즉 사용자의 노트북,컴퓨터 디스크에 존재하는 파일을 넣을 때 사용한다.

오해의 소지가 있을 수 있는데 DB 서버에 로컬 파일을 넣을 수 있습니다.

조건

local 명령어를 추가해서 사용할 경우에는 서버와 클라이언트

모두 local-infile 옵션이 on으로 되어 있어야만 사용가능 

local-infile 옵션 설정 확인 커맨드

show variables like 'local%';

 local-infile 옵션 ON으로 설정 하는법

mysql -u 아이디 -p -h 서버명 DB명 --local-infile=1

문법

표준 양식

LOAD DATA LOCAL INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

상황2. 로컬 윈도우

바탕화면에 있는 "garaData.csv" 라는 파일 원격 DB 서버에 import 하기

LOAD DATA LOCAL INFILE 'C:/Users/juju(사용자이름)/Desktop/garaData.csv'
REPLACE INTO TABLE `localtest`.`computervalue`(테이블이름)
COLUMNS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id, @cpu_value, @memory_value,@graphics_card_value,@time_value)
SET `id` = @id, `cpu_value` = @cpu_value, `memory_value` = @memory_value,
`graphics_card_value` = @graphics_card_value,
`time_value` = STR_TO_DATE(@time_value,'%Y-%m-%d %H:%i');

주의 : 윈도우 파일경로는 \(역슬래쉬)가 아니라 /(슬래쉬)이다.

        LOAD DATA INFILE이 아니라 LOAD DATA LOCAL INFILE 입니다.

C:\Users\user\Desktop (X)

C:/Users/user/Desktop (O)

만약 \(역슬래쉬)를 사용하고 싶다면 \\
C:\\Users\\user\\Desktop (O)

LOAD DATA INFILE 최근 에러 확인 명령어

가장 최근 1건만 확인 가능하다.

show warnings;

대표 예외(오류)

권한 거부 오류

/* SQL 오류 (29): File 'C:\Users\juju\Desktop\garaData.csv' not found (Errcode: 13 "Permission denied") */

local-infile 옵션 설정 확인 커맨드

show variables like 'local%';

 local-infile 옵션 ON으로 설정 하는법

mysql -u 아이디 -p -h 서버명 DB명 --local-infile=1

데이터에 포맷 오류

String 타입을 date에 넣거나

int 타입의 허용 범위를 넘거나 할 때 나는 오류이다.

주로 날짜 date , datetime 쓸 때 진짜 많이 나는 에러이다.

/* SQL 오류 (1292): Incorrect datetime value: '2018-11-23 10:47

' for column `localtest`.`computervalue`.`time_value` at row 1 */

잘못된 구분-String인데 Datetime에 넣을려고 시도

`time_value`= @time_value (X)

올바른 구분-String to Datetime으로 변경 후 데이터 삽입

`time_value` = STR_TO_DATE(@time_value,'%Y-%m-%d %H:%i'); (O)

LOAD DATA INFILE 사용법

MariaDB 공식 홈페이지

https://mariadb.com/kb/en/library/load-data-infile/

MySQL 공식 홈페이지

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

자주 쓰는 날짜 API

CURDATE() : 현재 날짜를 반환

SELECT CURDATE();

결과 : YYYY-MM-DD || YYYYMMDD(시간 반환X)

CURTIME() : 현재 시간을 반환

SELECT CURTIME();

결과 : HH:MM:SS || HHMMSS 

SYSDATE() || NOW() : 현재날짜 시간을 반환

SELECT SYSDATE();
SELECT NOW();

결과 : YYYY-MM-DD HH:MM:SS || YYYYMMDDHHMMSS

SYSDATE()와 NOW()는 엄연히 다르다. 절때 다르다. 당연히 다르다

SYSDATE() : 그 함수(쿼리포함) 실행되는 시점의 시각을 바로 리턴

NOW() : 쿼리가 끝난 시점의 시각 리턴

예를 들어 SELECT * FROM computer_value 를 조회하는데 데이터가 많아서 10분이상 혹은 1시간 10시간 걸렸다면

처음 조회한 첫 데이터의 SYSDATE()는 20시 30분이였다면

중간 조회된 데이터 SYSDATE()는 21시 20분

끝은 22시 30분 이런식..

거기에 FULL-TABLE-SCAN을 하기 때문에 성능상에도 굉장히 좋지 않다.

한마디로 NOW()를 쓰자.

UNIX_TIMESTAMP() : '1970-01-01 00:00:00' 부터의 UTC 시간을 초를 반환

SELECT UNIX_TIMESTAMP()

#값을 지정 할 수도 있다.
#그럼 이 시간 기준으로 반환된다.
SELECT UNIX_TIMESTAMP('2019-11-12 17:00');

결과 : 초를 반환하기 때문에 1573545907 이런식

FROM_UNIXTIME(unix_timestamp) : UTC 시간(timestamp)을 날짜 형식으로 변환한다.

SELECT FROM_UNIXTIME(1573545600);

결과 : 2019-11-12 17:00:00

DATE_FORMAT(date,format) : 날짜를 해당 형식의 문자열로 변환하여 반환.

대표적인 예시

SELECT DATE_FORMAT('2019-11-12 20:23:00', '%H:%i:%s');

결과 : 20:23:00

SELECT DATE_FORMAT('2019-11-12 20:23:00', '%y-%m-%d');

결과 : 19-11-12

SELECT DATE_FORMAT('2019-11-12 20:23:00', '%y-%m-%d %H:%i:%s');

결과 : 19-11-12 20:23:00

FORMAT 양식은 너무 길어서 생략 합니다.

STR_TO_DATE(str, format) : 문자열 str와 형식 문자열 format을 입력받음

받은 str 문자열을 Date 형식으로 바꿔줍니다.

SELECT STR_TO_DATE('2019-11-12','%y-%m-%d')

결과 : 2019-11-12

SELECT STR_TO_DATE('August 12 2019', '%M %d %Y')

결과 : 2019-08-12

SELECT STR_TO_DATE('Nov 12 2019', '%M %d %Y')

결과 : 2019-11-12

주의 : str이랑 format 폼을 꼭 동일하게 맞춰야 합니다. 맞지 않을 경우 null 반환

DATE_SUB(date, INTERVAL) 빼기

현재 시간 - 1시간(DATETIME 값만 가능)

SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);

현재 시간 - 1일

SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);

현재 시간 - 1달

SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

DATE_ADD(date, INTERVAL) 더하기

현재 시간 + 1시간(DATETIME 값만 가능)

SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR);

현재 시간 + 1일

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

현재 시간 + 1달

SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);

현재 시간 - 1시간(DATETIME 값만 가능)

SELECT DATE_ADD(NOW(), INTERVAL -1 HOUR);

현재 시간 - 1일

SELECT DATE_ADD(NOW(), INTERVAL -1 DAY);

DATEDIFF(date1,date2) 두 날짜 차이 구하기

두 날짜의 일 차이를 가져오는 것입니다.

앞에 파라미터(date1)를 기준으로 합니다.

select datediff('2019-11-15','2019-11-10')

결과 : 5  (양수)

select datediff('2019-11-10','2019-11-15')

결과 : -5 (음수)

TIMESTAMPDIFF(unit(단위),date1,date2)

두 날짜의 시간 차이를 가져오는 것입니다.

이것 역시 앞에 파라미터(date1)를 기준으로 합니다.

select TIMESTAMPDIFF(SECOND,'2019-11-10 18:22:30','2019-11-10 18:22:50')

결과 : 20(초)

select TIMESTAMPDIFF(SECOND,'2019-11-10 18:22:50','2019-11-10 18:22:30')

결과 : -20(초)

select TIMESTAMPDIFF(MINUTE,'2019-11-10 18:28:55','2019-11-10 18:40:20')

결과 : 11(분) 

주의 : 왜 12가 아니고 11일까? 바로 초가 모자라서 그렇습니다. 28분 55초-40분 20초 그래서 11분으로 인식됩니다. 조심

TIMESTAMPDIFF, DATE_SUB, DATE_ADD에 쓸 수 있는 포맷

SECOND

MINUTE

HOUR

DAY

MONTH

YEAR

문법

문법에 들어가기 앞서, 공통 테이블 예시

CREATE TABLE computerValue(

id int,
cpu_value int,
memory_value int,
graphics_card_value int,
time_value datetime

)

날짜 데이터는 많이 들어가 있어야 쿼리 할 때 이해가 더 쉬우니 데모데이터를 넣어봅시다.

DEMO CSV 만들기

https://www.mockaroo.com/

설정

설정을 끝낸 뒤 Download Data 누르시면 됩니다.

만들어진 CSV 파일 MariaDB에 import 하기

LOAD DATA LOCAL INFILE 'C:/Users/사용자/Desktop/MOCK_DATA.csv'(csv 파일 경로)
REPLACE
INTO TABLE `localtest(DB 이름)`.`computervalue(TABLE 이름)`
COLUMNS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
#CSV 컬럼들
(@id, @name, @cpu_value, @memory_value,@graphics_card_value,@time_value) 
#테이블컬럼 = CSV 컬럼 
SET `id` = @id, `name` = @name, `cpu_value` = @cpu_value, `memory_value` = @memory_value,`graphics_card_value` = @graphics_card_value,`time_value` = @time_value; 

주의 - file path 부분

Windows : C:/Users/   "/"(슬래쉬)를 사용

Linux : \temp\home\  "\"(역슬래쉬) 사용

실전 예제

DATE 값 기준 최근 동향 구하기

최근 3일 컴퓨터의 CPU or MEMORY 70% 사용량을 구하기

SELECT DISTINCT id,cpu_value,memory_value,graphics_card_value,time_value
FROM computervalue
WHERE time_value >= DATE_ADD(CURRENT_DATE(), INTERVAL -3 DAY) 
AND (cpu_value >= 70 OR memory_value >= 70);

최근 n일은 DATE_ADD(CURRENT_DATE(), INTERVAL -3 DAY) INTERVAL 뒤에 정수값만 바꿔주시면 됩니다.

DATETIME 값 기준 최근 동향 구하기

최근 3일 컴퓨터의 CPU or MEMORY 70% 사용량을 구하기

SELECT DISTINCT id,cpu_value,memory_value,graphics_card_value,time_value
FROM computervalue
WHERE time_value >= DATE_ADD(NOW(), INTERVAL -3 DAY) 
AND (cpu_value >= 70 OR memory_value >= 70);

이럴 경우 DATETIME인 경우는 그 최근 n일 사이에 70%가 넘은 데이터가 여러 개라면 이름과 아이디가 똑같은 데이터가 여러 개 나오겠죠? 테이블에 표출하고 싶은데 말이죠

2(컴퓨터ID) 사용량 사용량 2019-11-12 19:05

2(컴퓨터ID) 사용량 사용량 2019-11-12 21:35 처럼요.

DATETIME 값 기준 최근 동향 구하기

최근 3일 1컴퓨터당 1개의 데이터만 사용량이 제일 높았던 것 구하기

SELECT id,cpu_value,memory_value,graphics_card_value,time_value
FROM computervalue
WHERE time_value >= DATE_ADD(NOW(), INTERVAL -1 DAY) and (cpu_value > 70 or memory_value > 70)
GROUP BY name,id

실시간 사용량 TOP5 구하기

SELECT *
FROM computervalue
where `time_value` IN (SELECT MAX(time_value) FROM computervalue) 
order by cpu_value desc, memory_value desc,graphics_card_value desc limit 5;

CPU -> MEMORY -> GRAPHIC 기준입니다.

우선순위를 바꾸시려면 order by 컬럼위치를 바꾸시면 됩니다.

DATE : 2019-11-10 처럼 날짜만 나오는 데이터.

DATETIME : 2019-11-10 15:35처럼 날짜+시간이 같이 나오는 데이터

추가 실전 예제는 제가 프로젝트를 진행하면서 남들도 어렵다고 생각하겠다 OR 제가 삽질했던거 OR 자주 쓰는데 계속 까먹는 것 위주로 올립니다

이 글은 계속 업데이트 될 예정입니다.

예제 파일 제공

서브쿼리(Subquery)

개념

하나의 SQL 문 안에 포함되어 있는 또 다른 SQL문을 말한다. 

서브쿼리는 메인쿼리가 서브쿼리를 포함하는 종속적인 관계이다.

이제부터 앞에 있는 쿼리 select * from ex를 메인 쿼리

(select * from sub_ex) 괄호()안에 있는 쿼리를 서브 쿼리라 칭합니다.

서브쿼리(자식쿼리) - 메인쿼리 컬럼 사용 가능

메인쿼리(부모쿼리) - 서브쿼리 컬럼 사용 불가

Java의 상속과 똑같은 개념입니다.

종류

중첩 서브쿼리( Nested Subquery ) - WHERE 문에 나타나는 서브쿼리

1.단일 행

2.복수(다중) 행

3.다중 컬럼

인라인 뷰(Inline View) - FROM 문에 나타나는 서브쿼리

스칼라 서브쿼리( Scalar Subquery ) - SELECT 문에 나타나는 서브쿼리

이 세개의 종류는 그냥 외워주세요.

WHERE 중첩 서브쿼리

FROM 인라인 뷰

SELECT 스칼라 서브쿼리 

실행 순서

서브쿼리 실행 -> 메인(부모) 쿼리 실행

사용 이유

예를 들어 회사원 테이블에 김동식 대리보다 급여를 많이 받는 사람을 구하시오.

위와 같은 상황이 주어지면 서브쿼리가 없을 경우 우리는 일단

김대리의 급여가 얼만지 모르니까

select salary from employee where name = '김대리'로 김대리의 정보를 구한다음

그 정보를 저장했다가

select name,salary from employee where salary > 김대리 급여 저장한 정보

벌써 피곤합니다.

하지만 서브 쿼리를 쓴다면

select name,salary from employee where salary > (select salary from employee where name = '김대리')

한줄로 *깰끔하게 정리가 됩니다.

이것은 예시일 뿐입니다. 값을 구할 땐 무조건 PK_id로….

*깰끔 : 깔끔의 인터넷 용어

한마디로

여러번의 쿼리를 수행해야만 얻을 수 있는 결과를 

하나의 중첩된 SQL문장으로 간편하게 결과를 얻을 수 있게 해줍니다.

조건

서브쿼리는 SELECT문으로만 작성 할 수 있다.

(SELECT문 쿼리밖에 사용 할 수 없는것 이다. SELECT문에만 사용 하는 것이 아니라)

반드시 괄호()안에 존재하여야 한다.

괄호가 끝나고 끝에 ;(세미콜론)을 쓰지 않는다.

ORDER BY를 사용 할 수 없다.

사용 가능 한 곳

SELECT

FROM

WHERE

HAVING

ORDER BY

INSERT문의 VALUES

UPDATE문의 SET

서브쿼리도 별칭(alias) 사용이 가능하다.

문법

문법에 들어가기 앞서, 공통 테이블 예시

CREATE TABLE employee (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64),
salary INT,
office_worker VARCHAR(64),
)

INSERT INTO employee VALUES(1,'허사장',20000000,'사장');

INSERT INTO employee (name,salary,office_worker) VALUES('유부장',10000000,'부장');
INSERT INTO employee (name,salary,office_worker) VALUES('박차장',5000000,'차장');
INSERT INTO employee (name,salary,office_worker) VALUES('정과장',4000000,'과장');
INSERT INTO employee (name,salary,office_worker) VALUES('정대리',3895000,'대리');
INSERT INTO employee (name,salary,office_worker) VALUES('노사원',2500000,'사원');
INSERT INTO employee (name,salary,office_worker) VALUES('하사원',2000000,'사원');
INSERT INTO employee (name,salary,office_worker) VALUES('길인턴',1000000,'인턴');

SELECT * FROM employee

회사원 정보 테이블

서브쿼리는 무조건! 괄호()로 감싼 뒤 사용해야 한다.

(SELECT ~ 서브쿼리 내용)

중첩 서브쿼리 - 단일 행

#Nested Subquery - 단일 행 
#정대리라는 사람의 직급을 구하시오.
SELECT office_worker
FROM employee
WHERE office_worker = (SELECT office_worker FROM employee WHERE name = '정대리')

중첩 서브쿼리 - 복수(다중) 행

-IN, ANY, ALL, EXISTS 등의 연산자로 얻은 서브쿼리 결과 여러개의 행을 반환.

#Nested Subquery - 복수(다중) 행 
#정대리보다 급여가 높은 사람들을 구하시오.
SELECT *
FROM employee
WHERE salary > (
SELECT salary
FROM employee
WHERE NAME = '정대리')

#직급이 사원인 사람들을 구하시오.
SELECT *
FROM employee
WHERE office_worker IN (
SELECT office_worker
FROM employee
WHERE office_worker = '사원')

인라인 뷰(Inline View)

#인라인 뷰(Inline View)
#잘못된 구문 - 꼭 파생 테이블엔 별칭을 정해줘야 합니다.
SELECT * FROM (SELECT * FROM employee WHERE office_worker='사원') (X)
/* SQL 오류 (1248): Every derived table must have its own alias */

#인라인 뷰(Inline View)
#직급이 사원인 사람들의 이름과 급여를 구하시오.
SELECT EX1.name,EX1.salary
FROM (
SELECT *
FROM employee AS Ii
WHERE Ii.office_worker='사원') EX1;

스칼라 서브쿼리(Scalar Subquery)

#스칼라 서브쿼리(Scalar Subquery)
#정대리 급여와 테이블 전체 평균 급여를 구하시오.
SELECT name,salary,(
SELECT ROUND(AVG(salary),-1)
FROM employee) AS '평균급여'
FROM employee
WHERE name = '정대리';

INSERT 문 서브쿼리(INSERT Subquery)

#테이블2의 정보를 뽑아서 그 데이터를 테이블1에 넣어준다.
INSERT INTO table1 (SELECT * FROM table2);

DELETE 문 서브쿼리(DELETE Subquery)

#인턴의 정보를 구해와서 삭제한다.
DELETE FROM employee WHERE id = (SELECT id FROM employee where office_worker = '인턴' );

UPDATE 문 서브쿼리(UPDATE Subquery)

#인턴에 정보를 구해와서 급여를 10만원 인상한다.
UPDATE employee SET salary=(salary+100000)
WHERE id = (SELECT id FROM employee where office_worker = '인턴' );

실컷 설명하고 찬물 끼얹는 말이지만,

MySQL 기준

in 함수와 subquery는 성능이 좋지 않으므로 잘 쓰지 않는다.

IN ()은 INNER JOIN 혹은 EXISTS로 변환(대체) 가능

NOT IN()은 LEFT OUTER JOIN 혹은 NOT EXISTS로 변환가능하다

예제 파일 제공

AUTO_INCREMENT

개념

특정 데이터는 구분자가 되기위해 PK(Primary key) 설정을 한다.

PK의 특징은 고유성(유니크)을 가져야하기 때문에 똑같은 데이터를 넣을 수 없다.

그래서 보통 PK를 int 타입으로 겹치지않게 인덱스(정수값)를 계속 올린다.

그때 그 인덱스(정수값)를 체크해 자동으로 올려주는 옵션이 바로 AUTO_INCREMENT이다.

자동으로 올려주는 옵션

자동으로 올려주는 옵션

한번 설정 해두면 Insert into 할 때 마다 자동으로 값을 올려준다.

조건

기본키(Primary Key)에만 옵션 부여가 가능하다.

문법

테이블 생성시 옵션 부여

CREATE TABLE auto_test(
id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64));

이미 생성한 테이블에 옵션 추가 - Command

ALTER TABLE auto_test MODIFY id INT NOT NULL AUTO_INCREMENT;

이미 생성한 테이블에 옵션 추가 - GUI (HeidiSQL 10.2.0.5599 version)

라디오 버튼 체크하고 밑에 저장을 눌러주면 된다.

AUTO_INCREMENT 초기화

개념

AUTO_INCREMENT을 사용하다가 1~5번까지의 데이터를 넣어뒀다가

2~5번 데이터를 지우고 데이터를 다시 생성(insert)해보면 다시 2~5번 키의 데이터가 생성되는게 아니라

5~8번의 키를 가진 데이터가 생성된다.

이 값을 다시 2~5번 데이터로 넣고 싶을 땐 초기화를 해줘야한다.

문법

ALTER TABLE [TABLE] AUTO_INCREMENT = [시작 값];

주의 : 테이블에 새로 시작할 값보다 높은 값이 있으면 안됩니다. 

AUTO_INCREMENT 값을 초기화 후, 테이블 안의 모든 데이터의 ID값을 재조정 하기

무슨 말이냐 하면, 데이터 1000개가 존재하는데 많이 삭제됬다가 다시 생성되고 해서 첫번 째 데이터가 2000 or 10000 이런식으로 높게 존재할 경우

다시 1000개의 데이터를 1~1000번의 키를 가질 수 있게 하는 방법이다.

문법

ALTER TABLE tableName AUTO_INCREMENT=1;
SET @COUNT = 0;
UPDATE tableName SET auto_id(auto 설정한 컬럼) = @COUNT:=@COUNT+1;

오늘은 Mysql || MariaDB JOIN 개 (개념) 이 (문법 및 예시) 시작하겠습니다.

JOIN

개념

JOIN 연산은 두 테이블을 결합하는 연산입니다.

즉, 테이블은 각자에 맞는 데이터를 저장하고 있는데, 서로 다른 테이블에서 데이터를 가져오려면 JOIN 연산을 해야 합니다.

종류

Inner JOIN

Cross JOIN || 카티전 조인 || 상호 조인

OUTER JOIN || External JOIN

1.LEFT OUTER

2.RIGHT OUTER

3.FULL OUTER JOIN

문법에 들어가기 앞서, 공통 테이블 예시

CREATE TABLE people (

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64),
age INT

)

CREATE TABLE card_company(

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64),
amount_of_payment INT,
payment_location VARCHAR(64),
paymentdate DATETIME,
people_id INT 
)

INSERT INTO people VALUES(1,'김사원',24);

INSERT INTO people (NAME,age) VALUES('구대리',28);

INSERT INTO people (NAME,age) VALUES('허차장',42);

INSERT INTO people (NAME,age) VALUES('차부장',45);

INSERT INTO people (NAME,age) VALUES('홍임원',54);


insert into card_company VALUES(1,'NH',30000,'배달서비스','2019-11-09 23:02',1);

insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('shinhan',7700,'편의점','2019-11-09 10:10',1);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('KB',4500,'편의점','2019-11-09 15:21',1);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('KB',8550,'당구장','2019-11-09 19:35',1);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('shinhan',330000,'명품신발','2019-11-09 05:00',2);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('shinhan',4500000,'명품옷','2019-11-09 07:00',2);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('NH',400000,'돈 인출','2019-11-09 11:00',3);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('NH',1300000,'사성냉장고','2019-11-09 15:00',3);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('NH',10000000,'골프','2019-11-09 17:00',4);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('NH',15000000,'유흥','2019-11-09 03:00',4);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('NH',500,'문방구','2019-11-09 04:00',6);

people table (사람 정보 테이블)

card_company (카드 지출내역 테이블)

INNER JOIN

개념

두 테이블에 *교집합 이라고 생각을 하면 된다. 두 테이블에 공통된 값이 있으면 출력

INNER JOIN은 조인하는 테이블의 ON 절의 조건이 일치하는 결과만 출력됩니다.

*교집합 : 집합론에서, 두 집합 A와 B의 교집합 A ∩ B는 그 두 집합이 공통으로 포함하는 원소로 이루어진 집합이다.

문법

#inner join
SELECT * FROM people JOIN card_company ON people.id = card_company.people_id

간단 설명

ON people.id = card_company.people_id

사람테이블 id와 카드 테이블의 사람_id 맞는 정보만 가져왔다.

그 이유로 홍임원은 지출이 없어서 결과에 나오지 않았음. 

LEFT OUTER JOIN

개념

두 테이블 중에서 오른쪽 테이블에 조인시킬 컬럼의 값이 없는 경우에 사용을 하게 됩니다. 왼쪽 테이블 (Table A)의 값은 모두 다 나오지만 오른쪽 테이블 (Table B)의 값은 매칭이 되는게 없으면 출력이 되지 않음.

말 그대로 왼쪽 테이블 정보는 무조건 다 나옴

왼쪽 테이블 정보는 무조건 다 나옴

왼쪽 테이블 정보는 무조건 다 나옴

문법

#LEFT OUTER join
SELECT * FROM people LEFT OUTER JOIN card_company ON people.id = card_company.people_id

간단 설명

위에서 3번 강조한 것처럼 왼쪽!! 테이블정보가 다 출력되어서 홍임원은 지출내역이 없지만 출력된 모습.

RIGHT OUTER JOIN

개념

두 테이블 중에서 오른쪽 테이블에 조인시킬 컬럼의 값이 없는 경우에 사용을 하게 됩니다. 오른쪽 테이블 (Table B)의 값은 모두 다 나오지만 왼쪽 테이블 (Table A)의 값은 매칭이 되는게 없으면 출력이 되지 않음.

말 그대로 오른쪽 테이블 정보는 무조건 다 나옴

오른쪽 테이블 정보는 무조건 다 나옴

오른쪽 테이블 정보는 무조건 다 나옴

문법

#RIGHT OUTER JOIN
SELECT * FROM people RIGHT OUTER JOIN card_company ON people.id = card_company.people_id

간단 설명

위에서 3번 강조한 것처럼 오른쪽!! 테이블정보가 다 출력되어서 people 테이블에 6번이라는 id를 가진 사람은 없지만 출력이 된 모습.

제가 일부러 안맞는 데이터를 하나씩 넣어두었습니다.

CROSS JOIN(상호 조인,카티전 조인)

개념

한 쪽 테이블의 모든 행들과 다른 테이블의 모든 행을 조인시키는 기능

모든 경우의 수, 즉 A 테이블 row 개수 X B 테이블 row 개수 만큼의 row를 가진 테이블이 출력

*Cartesian Product, 곱집합 이용

문법

#CROSS JOIN
SELECT * FROM people CROSS JOIN card_company

..데이터 중간 생략

간단 설명

people row 5개

card_company 11개

5X11=55개의 ROW가 출력됨.

사용 이유

cross join은 사용이유가 궁금하니 이것만 특별하게 사용 이유를 알아보자.

대개 테스트로 사용할 대용량의 테이블을 생성할 경우에 사용된다.

예를 들어, 각 1 만건과 10 만건의 테이블을 CROSS JOIN 하면 10억건의 데이터를 생성시킬 수 있다.

SELF JOIN

개념

별도의 구문이 있는 것이 아니라, 자기 자신과 자기 자신이 조인한다는 의미

SELECT * FROM people SELF JOIN card_company 같은 문법이 있는게 아니라

말 그대로 자기 자신의 테이블 컬럼끼리 조인을 한다고 보면 된다.

문법

SELF JOIN을 위한 예시 테이블

CREATE table ugaga_tribes (
id INT AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR(64),
classes_id INT
)

insert into ugaga_tribes VALUES(1,'족장_우가콜라',null);

insert into ugaga_tribes (name,classes_id) VALUES('부족장_우가펩시',1);
insert into ugaga_tribes (name,classes_id) VALUES('부하1_우가팔일오',2);
insert into ugaga_tribes (name,classes_id) VALUES('부하2_우가우간다',3);
insert into ugaga_tribes (name,classes_id) VALUES('부하3_우가막내',4);

SELF JOIN-INNER JOIN 사용

#Self join-Inner join
SELECT ut_a.id,ut_a.name AS 부하 ,ut_b.name AS 상관
FROM ugaga_tribes ut_a 
JOIN ugaga_tribes ut_b 
ON ut_a.classes_id = ut_b.id;

간단 설명

ON ut_a.classes_id = ut_b.id; - inner join의 특징은 서로 일치하는 값을 표현하는것이라고 위에서 말했다.

상관의 ID(ut_a.classes_id) 부하의 ID(ut_b.id)가 일치하는 값만 출력하기 때문에 위와 같은 결과가 나왔다.

상관이 없는 부족들까지 출력하기.

SELF JOIN-LEFT JOIN 사용

#Self join-Left join
SELECT ut_a.id,ut_a.name,ut_b.name 
FROM ugaga_tribes ut_a 
LEFT JOIN ugaga_tribes ut_b 
ON ut_a.classes_id = ut_b.id;

간단 설명

ugaga_tribes ut_a  LEFT JOIN ugaga_tribes ut_b , 즉 같은 테이블이긴 하지만 위에 LEFT JOIN처럼 왼쪽!! 테이블 정보가 모두 출력되어 저 결과을 출력 할 수 있다.

JOIN 조건 걸기

JOIN을 사용하다 보면 굉장히 복잡한 쿼리문이 탄생하는데요,

실무에 투입되면 분명 JOIN에 조건을 걸어야 할 일이 생깁니다.

바로 알아보겠습니다.

위에 SELF JOIN-LEFT JOIN 사용 예제에 조건을 달아보겠습니다.

ut_b에 id가 2 이상인 데이터만 출력하기

SELECT ut_a.id,ut_a.name,ut_b.name 
FROM ugaga_tribes ut_a 
LEFT JOIN ugaga_tribes ut_b 
ON ut_a.classes_id = ut_b.id AND ut_b.id > 2;

ut_b에 name이 '부족'이 포함된 데이터만 출력하기

SELECT ut_a.id,ut_a.name,ut_b.name 
FROM ugaga_tribes ut_a 
LEFT JOIN ugaga_tribes ut_b 
ON ut_a.classes_id = ut_b.id AND ut_b.name LIKE '%부족%';

JOIN에 조건을 주고 싶을 땐, on절에 and를 이용하여 값을 추가하면 됩니다. 

예제 파일 제공

사용 이유

Select 문으로 다양한 Count 개수를 뽑아 낼 때

문법 및 예시

select count(CASE WHEN column=조건 THEN 1 END) as 별명(생략가능),
count(CASE WHEN column=조건 THEN 1 END) as 별명(생략가능) from 테이블명;

먼저 예시로 사용할 테이블 및 데이터 하나 만들겠습니다.

create table expenditure(

id varchar(64),
timestamp timestamp,
money int,
primary key(id,timestamp)

);

insert into expenditure values('gildong','2019-10-19',100000);
insert into expenditure values('gildong','2019-10-20',70000);
insert into expenditure values('gildong','2019-10-21',3000);
insert into expenditure values('gildong','2019-10-22',7700);
insert into expenditure values('gildong','2019-10-23',50100);
insert into expenditure values('samsam','2019-10-19',50000);
insert into expenditure values('samsam','2019-10-20',60000);
insert into expenditure values('samsam','2019-10-21',90000);
insert into expenditure values('samsam','2019-10-22',1110000);
insert into expenditure values('samsam','2019-10-23',31000);
insert into expenditure values('YUMDDA','2019-10-19',11200000);
insert into expenditure values('YUMDDA','2019-10-20',122300000);
insert into expenditure values('YUMDDA','2019-10-21',10330000);
insert into expenditure values('YUMDDA','2019-10-22',3541100);
insert into expenditure values('YUMDDA','2019-10-23',10000000);

#기본 MySQL 카운트 함수

select count(*) from expenditure;

상황1. id가 gildong인 사람만 카운트 , id가 samsam인 사람만 카운트

SELECT COUNT(CASE WHEN id='gildong' THEN 1 END) AS gildongCnt,
COUNT(CASE WHEN id='samsam' THEN 1 END) AS samsamCnt
FROM expenditure;

#조건 여러개(다중) 카운트

상황2. id가 YUMDDA인 사람이 돈을 1천만이상 쓴 데이터만 카운트 , id가 samsam인 사람만 카운트

SELECT COUNT(CASE WHEN id='YUMDDA' THEN 1 END AND CASE WHEN money > 10000000 THEN 1 END) AS YUMDDACnt, 
COUNT(CASE WHEN id='samsam' THEN 1 END) AS samsamCnt
FROM expenditure;

상황3. 다른 테이블의 특정 컬럼 Count 개수 구하기

SELECT COUNT(CASE WHEN id='gildong' THEN 1 END) AS gildongCnt,
COUNT(CASE WHEN id='samsam' THEN 1 END) AS samsamCnt,
(SELECT COUNT(*)
FROM RelationTable) AS relationCnt
FROM expenditure;

예제 파일 제공