MySQL 1: SQL

MySQL 에서 사용하는 데이터 타입의 종류는 약 30 개 가까이 있다. 

 

 

숫자 데이터

 

데이터 타입	바이트 수	숫자 범위	설명
BIT(N)	N/8		1~64bit 표현. b'0000' 형식
TINYINT	1	-128 ~ 127	정수
SMALLINT	2	-32768 ~ 32767	정수
MEDIUMINT	3	- 8,388,608 ~ ...	정수
INT, INTEGER	4	- 2147483648 ~ ...	정수
BIGINT	8	- 900 경 ~ ...	정수
FLOAT	4	-3.40E+38 ~ ...	소수점 아래 7자리까지
DOUBLE, REAL	8	-1.22E-308 ~ ...	소수점 아래 15자리까지
DECIMAL(m,[d]), NUMERIC(m,[d])	5 ~ 17	-10^38 + 1 ~ ...	전체 자리수(m)와 소수점 이하 자리수(d)를 가진 숫자형

 

 

문자 데이터

 

데이터 타입		바이트 수	설명
CHAR(n)		1 ~ 255	고정길이 문자형. n 은 1~255
VARCHAR(n)		1 ~ 65535	가변길이 문자형
BINARY(n)		1 ~ 255	고정길이 이진 데이터
VARBINARY(n)		1 ~ 255	가변길이 이진 데이터
TEXT 형식	TINYTEXT	1 ~ 255	255 크기의 TEXT 데이터 값
	TEXT	1 ~ 65535	N 크기의 TEXT 데이터 값
	MEDIUMTEXT	1 ~ 16777215	16777215 크기의 TEXT 데이터
	LONGTEXT	1 ~ 4294967295	최대 4GB 크기의 TEXT 데이터
BLOB 형식	TINYBLOB	1 ~ 255	255 크기의 BLOB 데이터 값
	BLOB	1 ~ 65535	N 크기의 BLOB 데이터 값
	MEDIUMBLOB	1 ~ 16777215	16777215 크기의 BLOB 데이터
	LONGBLOB	1 ~ 4294967295	최대 4GB 크기의 BLOB 데이터
ENUM(값들...)		1 또는 2	최대 65535 개의 열거형 데이터
SET(값들...)		1, 2, 3, 4, 8	최대 64 개의 서로 다른 데이터

 

 

 

위 데이터타입 외에도 날짜와 시간 데이터 형식으로 사용되는 DATE, DATETIME, TIMESTAMP 등이 있고 JSON, GEOMETRY 도 사용할 수 있다.

 

 

 

SQL 에서도 다른 언어들처럼 변수를 선언하고 사용할 수 있다. 변수의 선언과 값의 대입은 아래와 같은 형식으로 이루어진다.

 

 

SET @변수이름 = 변수의값;
SElECT @변수이름; -- 변수값 출력

 

 

스토어드 프로시저나 함수 안에서 변수를 사용할 때는 DECLARE 문으로 선언한 뒤에 사용하게된다. 또, 스토어드 프로시저와 함수 내에서는 @변수명 형식이 아니라 그냥 변수명만 사용된다. @변수명은 전역변수처럼 사용하고, DECLARE 로 선언한 것은 지역변수처럼 사용된다.

 

 

 

MySQL 에서 제공하는 내장함수들에 대해서 알아보았다.

 

 

IFNULL(수식1, 수식2) : 수식1이 NULL 이면 수식2가 반환되고, 아니면 수식1이 반환됨.

 

CASE ~ WHEN ~ELSE ~END: CASE 는 내장함수는 아니고 연산자로 분류된다. Java 에서 사용하는 SWITCH/CASE 와 동일하다고 생각하면된다.

 

CONCAT(문자열1, 문자열2, ...), CONCAT_WS(구분자, 문자열1, 문자열2, ...) : 문자열을 이어준다. CONCAT_WS 는 구분자와 함께 문자열을 이어준다.

 

ELT(위치, 문자열1, 문자열2, ...) : 위치 번째에 해당하는 문자열을 반환한다. (1부터 시작)

 

FIELD(찾을 문자열, 문자열1, 문자열2, ...) : 찾을 문자열의 위치를 찾아서 반환한다. (1부터 시작)

 

LPAD(문자열, 길이, 채울문자열), RPAD(문자열, 길이, 채울문자열) : 문자열을 길이만큼 늘린 후에, 빈 곳을 채울 문자열로 채운다.

 

LTRIM(문자열), RTRIM(문자열) : 문자열의 왼쪽/오른쪽 공백을 제거한다.

 

TRIM(문자열) : 문자열의 앞뒤 공백을 제거한다.

 

REPLACE(문자열, 원래문자열, 바꿀문자열) : 문자열에서 원래 문자열을 찾아 바꿀문자열로 바꾼다.

 

SUBSTRING(문자열, 시작위치, 길이) : 시작위치부터 길이만큼 문자열을 잘라 반환한다.

 

ADDDATE(날자, 차이), SUBDATE(날짜, 차이) : 날짜를 기준으로 차이를 더하거나 뺀 날짜를 구한다.

 

ADDTIME(날짜/시간, 차이), SUBTIME(날짜/시간, 차이) : 날짜/시간을 기준으로 시간을 더하거나 뺀 결과를 구한다.

 

CURDATE(), CURTIME(), NOW(), SYSDATE() : CURDATE() 는 현재 연-월-일, CURTIME() 은 현재 시:분:초, NOW() 와 SYSDATE() 는 현재 '연-월-일 시:분:초' 를 구한다.

 

 

 

이렇게 내장함수들을 여러개 만들어본 후, 직접 데이터베이스 테이블을 만들어 실습해보는 시간을 가졌다.

 

 

USE MYSQL_STUDY;

create table movietbl
(movie_id INT,
movie_title varchar(30),
movie_script LONGTEXT,
movie_film LONGBLOB
) DEFAULT CHARSET=utf8mb4;

 

 

영화대본은 LONGTEXT, 영화동영상은 LONGBLOB 형식으로 하여 파일 내용을 넣어볼 수 있게끔 설계한다. 한글 처리를 문제 없이 하기 위해서 기본 문자세트를 utf8mb4 로 지정한다. 데이터를 넣기 전에 아직 해줘야할 작업이 있다.

 

 

show variables like 'max_allowed_packet';

show variables like 'secure_file_priv';

 

 

위 두 구문을 실행시켜보면, 최대 패킷 크기(max_allowed_packet) 값은 매우 작게 설정되어 있으며 파일을 업로드/다운로드할 폴더 경로(secure_file_priv) 는 null 로 들어가있는 것을 확인할 수 있다. 이 두 값을 수정해주기 위해서는 터미널에서 mysql 설정파일인 my.cnf 파일 경로로 들어가서 해당 파일의 내용을 수정해줘야 한다. Mac 에서 homebrew 를 통해 다운로드 받았을 경우, /opt/homebrew/etc 경로에서 해당 파일을 찾을 수 있다.

 

 

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
# Secure File Priv.
secure-file-priv="/Users/wonil/study/mysql_upload"
# Max-allowed packet
max_allowed_packet=1024M

 

 

위와 같이 작성해준 뒤, mysql.server restart 를 해주었다. 그 다음, 아래 구문으로 데이터를 INSERT 해본다.

 

 

INSERT INTO movietbl values(2, "TEST_MOVIE", LOAD_FILE('/Users/wonil/study/mysql_upload/file.txt'), LOAD_FILE('/Users/wonil/study/mysql_upload/내게오는길.mp4'));

 

 

데이터가 잘 입려되는 것을 확인할 수 있다. 입력된 데이터를 다시 파일로 내려받기 위해서는 아래와 같은 구문을 실행한다.

 

 

SELECT movie_script from movietbl m where movie_id = 2 into outfile '/Users/wonil/study/mysql_upload/outfile.txt' lines terminated by '\\n';

SELECT movie_film from movietbl m where movie_id = 2 into dumpfile '/Users/wonil/study/mysql_upload/outdumpfile.mp4';

 

 

result

 

 

다운로드 성공!

 

 

MySQL 에서 JSON 형식의 데이터도 사용할 수 있다고 하였다.

 

 

select JSON_OBJECT('movie_id', movie_id, 'movie_title', movie_title, 'movie_script', movie_script) AS 'JSON DATA' FROM movietbl m;

 

 

result

 

위와 같이 JSON_OBJECT 함수를 사용할 수도, JSON_ARRAY 가 필요할 때는 JSON_ARRAY() 함수를 사용할 수도 있다.

 

 

그 다음으로는 JOIN 에 대해서 다시 한 번 알아본다. JOIN 이란 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어내는 것을 말한다. 데이터베이스의 테이블은 중복과 공간 낭비를 피하고 데이터의 무결성을 위해서 여러 개의 테이블로 분리하여 저장한다. 그리고 이 분리된 테이블들은 서로 관계를 맺고 있다. 이러한 관계를 활용하여 JOIN 을 진행할 수 있다.

 

 

JOIN 중에서 가장 많이 사용되는 것은 INNER JOIN 이다. (일반적으로 JOIN 을 말하면 이 INNER JOIN 을 뜻하는 것) 

 

 

SELECT *
FROM tbl1
	INNER JOIN tbl2
    ON condition
WHERE condition2

 

 

위와 같은 형식으로 작성된다. 위에서 INNER JOIN 대신 JOIN 만 써도 동일하게 동작한다. 

 

 

OUTER JOIN 은 INNER JOIN 과는 반대로, 조인의 조건에 만족되지 않는 행까지도 포함시키는 것이다. 

 

 

SELECT *
FROM tbl1(Left table)
 < LEFT | RIGHT | FULL > OUTER JOIN tbl2(Right table)
 ON condition
WHERE condition;

 

 

위와 같이 작성한다. 

 

 

 

 

 

 

 

Reference:

이것이 MySQL 이다