SQL 고급 문법 (1)

prefer·2024년 12월 26일

Database sql

SQL 기초

목록 보기

5/15

데이터 형식

MySQL에서 열에 지정할 수 있는 데이터 형식은 크게 정수형, 문자형, 대량의 데이터 형식(TEXT와 같은), 실수형, 날짜형 등이 있다. 각각에 대해 자세하게 알아보자.

정수형

정수형은 소수점이 없는 숫자, 즉 정수에 사용되는 자료형이다.

데이터 형식	바이트 수	숫자 범위
TINYINT	1	-128 ~ 127
SMALLINT	2	-32,768 ~ 32,767
INT	4	약 -21억 ~ 21억
BIGINT	8	약 -900경 ~ 900경

크게 4개로 분류할 수 있으며 각각의 숫자 범위에 맞는 값만 가질 수 있다.

USE market_db;
CREATE TABLE hongong4 (
	tinyint_col TINYINT,
	smallint_col SMALLINT,
	int_col INT,
	bigint_col BIGINT
);

INSERT INTO hongong4 VALUES (127, 32767, 2147483647, 9000000000000000000);

각 열에 최대값을 입력할 경우 문제 없이 입력할 수 있고, BIGINT의 경우 0을 18개까지 쓸 수 있다.

INSERT INTO hongong4 VALUES (128, 32768, 2147483648, 90000000000000000000);

하지만 최댓값을 뛰어넘는 값을 입력할 경우 Out of range 오류가 발생하므로, 범위를 잘 맞춰서 자료형과 값을 선택하자.

CREATE TABLE member(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
	mem_name VARCHAR(10) NOT NULL,
	mem_number TINYINT NOT NULL,
	addr CHAR(2) NOT NULL,
	phone1 CHAR(3),
	phone2 CHAR(8),
	height TINYINT UNSIGNED,
	debut_date DATE
);

만약 UNSIGNED 키워드를 붙일 경우 기존 표현 범위에서 음수 표현 범위를 모두 없애고 양수로 변환, 즉 양의 범위가 2배로 증가한다. 따라서 UNSIGNED는 0부터 시작한다.

예시로 SMALLINT UNSIGNED는 기존 -32,768 ~ 32,767 범위에서 음수 범위가 모두 양수로 이동하면서 0부터 65535까지 값을 가질 수 있는 범위로 바뀐다.

문자형

문자형이란 말 그대로 문자나 문자열을 저장할 수 있는 자료형을 말한다.

데이터 형식	바이트 수
CHAR(문자열 길이)	1~255
VARCHAR(문자열 길이)	1~16383
BINARY(문자열 길이) 또는 BYTE(문자열 길이)	1~255
VARBINARY(문자열 길이)	1~65535

크게 4개로 분류할 수 있는데 바이트 수에 맞게 문자를 저장할 수 있다. 영어, 한국어가 한 글자당 몇 바이트인지는 DB에 따라 다를 수 있으므로 반드시 확인하고 사용하도록 하자.

CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
	mem_name VARCHAR(10) NOT NULL,
	mem_number TINYINT NOT NULL,
	addr CHAR(2) NOT NULL,
	phone1 CHAR(3),
	phone2 CHAR(8),
	height TINYINT UNSIGNED,
	debut_date DATE
);

우선 CHAR(character)는 고정 길이 문자형으로 자릿수가 고정되어있다. 예를 들어 CHAR(10)에 ‘가나다’ 3글자만 저장한다고 해도 처음에 지정한 숫자인 (10), 즉 10자리를 모두 확보한 상태에서 3자리를 사용한다. 따라서 10자리를 모두 사용하지 않은 상태이므로 7자리를 낭비하게 된다.

VARCHAR(Varaiable Character)은 가변 길이 문자형으로 예를 들어 VARCHAR(10)에 ‘가나다’ 3글자를 저장할 경우 3자리만 사용한다. 즉 (10)으로 지정하여도 들어간 데이터의 크기에 맞게 저장 공간을 할당한다.

이렇게만 보면 VARCHAR이 CHAR보다 공간을 효율적으로 이용하는 것으로 보여서 더 좋아보이지만, MySQL 내부적으로 성능(속도)면에서는 CHAR이 우세하다. 따라서 값의 가변 길이 범위가 적고, 자주 변경되는 경우(인덱스 열의 경우) CHAR을 사용하는 것이 더 좋다.

BINARY 데이터 타입은 관련된 문자 세트가 없는 문자의 전체 바이트 저장에 사용한다. 예를 들어 GIF 이미지를 저장하는데 사용할 수 있다. BINARY와 VARBINARY는 앞선 CHAR와 VARCHAR의 특징 및 차이점과 동일하다.

만약 CHAR, VARCHAR을 선언할 때 따로 저장 공간의 크기를 지정하지 않는다면 CHAR(1), VARCHAR(1)로 저장 공간의 크기가 선언된다.

대량의 데이터 형식

대량의 데이터 형식은 앞서서 문자보다 더 큰 양의 문자들을 저장해야할 때 주로 사용되는 데이터 형식이다.

데이터 형식	바이트 수
TEXT	1~65535
LONGTEXT	1~4294967295
BLOB	1~65535
LONGBLOB	1~4294967295
TINYTEXT	1~255
MEDIUMTEXT	1~16777215
TINYBLOB	1~255
MEDIUMBLOB	1~16777215

TEXT로 지정 시 최대 65535, LOGTEXT로 지정 시 약 42억자까지 저장 가능하며, BLOB(Binary Long Object)는 글자가 아닌 이미지, 동영상 등의 이진 데이터를 저장하기 위해 사용된다.

CREATE DATABASE netflix_db;
USE netflix_db;
CREATE TABLE movie
(
	movie_id INT,
	movie_title VARCHAR(30),
	movie_director VARCHAR(20),
	movie_star VARCHAR(20),
	movie_script LONGTEXT,
	movie_film LONGBLOB
);

자막에 해당하는 movie_script와 영상에 해당하는 movie_film의 경우 LONGTEXT, LONGBLOB으로 설정하여 최대 4GB까지 데이터를 입력할 수 있다.

실수형

실수형은 소수점을 포함한 숫자를 저장하는데 사용하는 데이터 형식이다.

데이터 형식	바이트 수
FLOAT	4
DOUBLE	8

FLOAT는 소수점 아래 7자리까지 표현 가능하고, DOUBLE은 소수점 아래 15자리까지 표현 가능하다.

날짜형

날짜형은 연월일, 시간을 저장할 수 있는 데이터 형식이다.

데이터 형식	바이트 수
DATE	3
TIME	3
DATETIME	8

DATE는 날짜만 저장하며 YYYY-MM-DD 형식으로 사용한다.

TIME은 시간만 저장하며 HH:MM:SS 형식으로 사용한다.

DATETIME은 날짜 및 시간을 모두 저장하며 YYYY-MM-DD HH:MM:SS 형식으로 사용한다.

날짜 또는 시간을 입력할 때는 문자와 마찬가지로 작은 따옴표 사용하여 입력한다.

변수의 사용

SQL도 다른 프로그래밍 언어와 마찬가지로 변수를 선언하고 사용할 수 있다. 참고로 변수를 사용하는 개념은 코딩테스트에서 꽤 출제되기 때문에, 알아두면 도움이 된다.

참고로 MySQL 워크벤치를 종료하면 변수는 소멸된다. 즉 임시로 사용 가능한데 프로그래밍에서 지역 변수 개념으로 생각하면 이해하기 쉽다.

SET @변수이름 = 변수의 값;
SELECT @변수이름;

기본적으로 변수의 사용법은 위와 같이 @(변수명)으로 변수를 선언하고 SET을 이용하여 선언과 동시에 값을 초기화한다.

간단한 예시로 이해해보도록 하자.
<실행>

USE market_db;
SET @myVar1 = 5;
SET @myVar2 = 4.25;

SELECT @myVar1;
SELECT @myVar1 + @myVar2;

SET @txt = '가수 이름==> ';
SET @height = 166;
SELECT @txt, mem_name FROM member WHERE height > @height;

<결과>

변수를 사용하는 문법에 맞게 myVar1 변수, myVar2 변수를 사용해 이를 합했고 출력했다. 또한 문자열을 저장하는 txt와 기준이 되는 키를 저장하는 height로 멤버들을 비교하여 결과를 출력했다.

SET @count = 3;
SELECT mem_name height FROM member ORDER BY height LIMIT @count;

참고로 LIMIT에는 변수를 사용할 수 없으므로, 위 쿼리문은 잘못된 문법이다. 만약 LIMIT에도 변수를 사용하고 싶다면 어떻게 해야할까?

<실행>

SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @count;

<결과>

PREPARE는 작성된 쿼리 문자열을 실행하지 않고 준비만 해둔다. 위에서는 mySQL이란 이름으로 쿼리문을 준비했다. ‘?’는 현재는 모르지만 나중에 채워지는 값이 된다.

EXECUTE는 준비된 SQL문을 실행하는 것으로, USING으로 ‘?’에 @count 변수 값 대입하여 사용할 수 있다.

데이터 형 변환

형 변환이란 문자형을 정수형을 바꾸는 것과 같이 데이터 형식을 변환하는 것으로 크게 명시적 변환과 암시적 변환으로 나뉜다.

명시적인 변환이란 직접 함수를 사용하여 데이터 형식 변환하는 것이고, 암시적인 변환이란 별도의 지시나 함수 없이 자연적으로 변환이 처리되는 것을 말한다.

명시적인 변환

명시적인 변환이란 사용자가 직접 함수를 사용하여 데이터 형식을 변환하는 것이다.

CAST (값 AS 데이터 형식 [(길이)])
CONVERT (값, 데이터 형식 [(길이)])

명시적인 변환은 함수를 사용하여 변환하는 것인데, CAST와 CONVERT는 데이터 형식을 변환하는 함수로 동일한 기능을 보유한다.

<실행>

SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy;
SELECT CONVERT(AVG(price), SIGNED) '평균 가격' FROM buy;

<결과>

CAST나 CONVERT 함수 내에 올 수 있는 데이터 형식은 CHAR, SIGNED, UNSIGNED, DATE, TIME, DATETIME 등등이며, SIGNED는 부호가 있는 정수, UNSIGNED는 부호가 없는 정수로 각각 SIGEND INTEGER, UNSIGNED INTEGER와 동일하다. 참고로 INT와 INTEGER는 동일하다.

<실행>

SELECT CAST('2022$12$12' AS DATE);
SELECT CAST('2022/12/12' AS DATE);
SELECT CAST('2022%12%12' AS DATE);
SELECT CAST('2022@12@12' AS DATE);

<결과>

문자열을 날짜로 변환하는 예시이며, YYYY(구분자)MM(구분자)DD 형식으로 이루어진 문자열을 날짜로 변환한다. 다양한 구분자를 날짜형으로 변경할 수 있다.

<실행>

SELECT num, CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR), '=' ) '가격X수량',
	price*amount '구매액'
FROM buy;

<결과>

이번에는 반대로 숫자를 문자열로 변경한 후, 이를 이어붙여 출력하는 예시이다. CONCAT(문자열1, 문자열2, ...) 함수는 매개변수로 넘어온 문자 데이터 형식을 순서대로 이어붙여주는 역할을 한다.

암시적인 변환

암시적인 변환이란 사용자가 별도의 지시나 함수의 작성 없이 자연적으로 변환이 처리되는 것을 말한다.

<실행>

SELECT '100' + '200';

<결과>

위 예시는 암시적인 변환의 예시인데 문자형끼리는 덧셈이 불가하므로 숫자로 자동 변환하여 연산을 수행했다. 즉 ‘100’을 실제 숫자 100, ‘200’을 실제 숫자 200으로 변환하여 100 + 200을 실시하였고 그 결과로 300을 산출하였다.

<실행>

SELECT CONCAT(100, '200'), 100 + '200';

<결과>

이번에는 숫자에서 문자, 문자에서 숫자로 암시적인 변환을 모두 담은 예시이다. CONCAT()은 문자열을 이어붙이는 함수이므로 숫자와 문자를 CONCAT()으로 연결 시 숫자인 100이 문자 ‘100’으로 변환되어 100200이란 결과가 나왔다.

반대로 아까 봤다시피 문자열끼리 덧셈 연산은 불가하므로 숫자와 문자를 덧셈 연산 시 문자 ‘200’이 숫자 200으로 변환되어 300이란 결과가 나왔다.

출처

혼자 공부하는 SQL(우재남 저, 한빛미디어)
요즘IT(https://yozm.wishket.com/magazine/detail/2717/)

prefer

기술적 의사결정에 객관성을 가지는 Back-End 개발자 이선호입니다.

SQL 고급 문법 (1)

SQL 기초

데이터 형식

정수형

문자형

대량의 데이터 형식

실수형

날짜형

변수의 사용

데이터 형 변환

명시적인 변환

암시적인 변환

출처

SQL 기본 문법 (2)

SQL 고급 문법 (2)

0개의 댓글