4. SQL 고급 문법-1

0. 학습 목표

• 다양한 데이터 형식에 대해 이해
• 두 테이블을 연결하는 조인을 이해하고 활용
• SQL에서 일반 프로그래밍 기능을 구현

0. 요약

• 정수형은 소수점이 없는 숫자이며 TINYINT, SMALLINT, INT, BIGINT가 있음
• 문자형은 고정형 문자형인 CHAR과 가변형 문자형인 VARCHAR이 있음
• 실수형은 소수점 아래 7자리까지 표현되는 FLOAT와 15자리까지 표현되는 DOUBLE가 있음
• 날짜형은 날짜DATE, 시간TIME, 날짜시간DATETIME이 있음
• MySQL에서 제공되는 변수 앞에는 @를 붙임
• 데이터 형식을 변경하는 형 변환 함수에는 CAST(), CONVERT()가 있음

정수형

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

문자형

데이터 형식	바이트 수
CHAR(개수)	1 ~ 255
VARCHAR(개수)	1 ~ 16383

대량의 데이터 형식

데이터 형식		바이트 수
TEXT 형식	TEXT	1 ~ 65535
	LONGTEXT	1 ~ 4294967295
BLOB 형식	BLOB	1 ~ 65535
	LONGBLOB	1 ~ 4294967295

실수형

데이터 형식	바이트 수	설명
FLOAT	4	소수점 아래 7자리
DOUBLE	8	소수점 아래 15자리

날짜형

데이터 형식	바이트 수	설명
DATE	3	날짜만 저장 YYYY-MM-DD
TIME	3	시간만 저장 HH:MM:SS
DATETIME	8	날짜와 시간을 저장 YYYY-MM-DD HH:MM:SS

---

1. MySQL의 데이터 형식

※핵심 키워드:  정수형, 문자형, 실수형, 날짜형, 변수, 형 변환

---

1-1. 데이터 형식

정수형

정수형은 소수점이 없는 숫자에서 사용되며 정수형의 크기와 범위는 아래와 같다.

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

간략하게 테이블을 만들어서 확인 해 보자.

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);
SELECT * FROM hongong4;

# tinyint_col, smallint_col, int_col, bigint_col
'127', '32767', '2147483647', '9000000000000000000'

추가적으로 최대값을 넘는 값을 입력해 보자 에러가 난다.

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

17:25:35	INSERT INTO hongong4 VALUES (200, 32768, 2147483648, 90000000000000000000)	Error Code: 1264. Out of range value for column 'tinyint_col' at row 1	0.000 sec

지난번에 만들었던 member 회원 테이블의 예제를 살펴보자.

USE market_db --market_db를 선택
CREATE TABLE member -- 회원 테이블
( mem_id  		CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    	VARCHAR(10) NOT NULL, -- 이름
  mem_number    INT NOT NULL,  -- 인원수
  addr	  		CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2		CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height    	SMALLINT,  -- 평균 키
  debut_date	DATE  -- 데뷔 일자
);

• 인원 수(mem_number)는 TINYINT(최대 127)로 지정해도 충분하다
• 평균 키(height)는 SMALLINT(최대 32767)로 지정해도 충분하다
  -> SMALLINT보다는 UNSIGNED와 같이 값이 0으로 시작하는 TINYINT UNSIGNED를 사용하는 것이 더 효율적이다.
      TINYINT(-128 ~ 128) TINYINT UNSIGNED(1 ~ 255)

USE market_db --market_db를 선택
CREATE TABLE member -- 회원 테이블
( mem_id  		CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    	VARCHAR(10) NOT NULL, -- 이름
  mem_number    TINYINT NOT NULL,  -- 인원수
  addr	  		CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2		CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height    	TINYINT UNSIGNED,  -- 평균 키
  debut_date	DATE  -- 데뷔 일자
);

---

문자형

문자형은 글자를 저장하기 위해 사용되며 입력할 최대 글자의 개수를 지정해야 한다. 대표적인 문자형은 아래와 같다.

데이터 형식	바이트 수
CHAR(개수)	1 ~ 255
VARCHAR(개수)	1 ~ 16383

CHAR는 고정길이 문자열이라고 부른다. 즉 자리수가 고정되어 있다. VARCHAR은 가변길이 문자열이라고 부른다. 자리수가 가변적이다.

• CHAR(10)이라고 선언하면 10자리를 먼저 예약하고 3개의 글자만 저장되어도 10자리 모두를 사용한다.
• VARCHAR(10)이라고 선언하고 3개의 글자가 저장되면 3자리만 사용한다.

VARCHAR가 CHAR보다 공간은 효율적으로 사용할 수 있지만, 성능면에서는 CHAR로 설정하는 것이 좋다.

회원 테이블의 문자형을 살펴보자.

USE market_db --market_db를 선택
CREATE TABLE member -- 회원 테이블
( mem_id  		CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    	VARCHAR(10) NOT NULL, -- 이름
  mem_number    INT NOT NULL,  -- 인원수
  addr	  		CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2		CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height    	SMALLINT,  -- 평균 키
  debut_date	DATE  -- 데뷔 일자
);

• mem_id: 최대 8글자 입력 가능, VARCHAR(8)로 변경해도 무관
• mem_name: 최대 10글자 입력 가능, 가변 길이로 하는 것이 효율적
• addr: 지역 번호로 2글자까지만 입력 가능, 정수형으로 사용하면 앞에 0이오면 사라지기 때문에 CHAR로 선언
• phone1, 2: 정수형으로 지정할 수 있으나, 더하기/빼기, 크다/작다, 순서 등의 연산이 없어 CHAR로 선언

---

대량의 데이터 형식

CHAR은 255, VARCHAR은 16383까지 지정 가능하다. 이것을 넘어서는 더 큰 데이터를 저장하려면 다음과 같은 형식을 사용한다.

데이터 형식		바이트 수
TEXT 형식	TEXT	1 ~ 65535
	LONGTEXT	1 ~ 4294967295
BLOB 형식	BLOB	1 ~ 65535
	LONGBLOB	1 ~ 4294967295

• TEXT, LONGTEXT: 소설이나 영화 대본과 같은 것을 저장한다면 필요한 데이터 형식
• BLOB(Binary Long Object), LONGBLOB: 사진이나 동영상 같은 것을 저장한다면 필요한 데이터 형식

예를 들어 넷플릭스와 같은 동영상 사이트라면 아래와 같이 만들 수 있다.

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

---

실수형

실수형은 소수점이 있는 숫자를 저장할 때 사용한다.

데이터 형식	바이트 수	설명
FLOAT	4	소수점 아래 7자리
DOUBLE	8	소수점 아래 15자리

과학 기술용 데이터가 아니라면 보통 FLOAT를 사용한다.

---

날짜형

날짜와 시간을 저장할때 사용한다.

데이터 형식	바이트 수	설명
DATE	3	날짜만 저장 YYYY-MM-DD
TIME	3	시간만 저장 HH:MM:SS
DATETIME	8	날짜와 시간을 저장 YYYY-MM-DD HH:MM:SS

주의할 점은 날짜와 시간을 입력할 때는 작은따옴표로 묶어줘야 한다.

---

1-2. 변수의 사용

변수의 선언과 값의 대입은 다음 형식을 따른다.

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

변수는 MySQL 워크벤치를 재시작할 때까지는 유지되지만 종료하면 없어진다. 즉 임시로 사용한다고 생각하면 된다.

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

SELECT @myVar1;
SELECT @myVar1 + @myVar2;

SET @txt = '가수 이름==> ';
SET @height = 156;

SELECT @txt, mem_name FROM member WHERE height > @height;

# @myVar1
'5'

# @myVar1 + @myVar2
'9.250000000000000000000000000000'

# @txt, mem_name
'가수 이름==> ', '에이핑크'
'가수 이름==> ', '블랙핑크'
'가수 이름==> ', '소녀시대'
'가수 이름==> ', '잇지'
'가수 이름==> ', '마마무'
'가수 이름==> ', '오마이걸'
'가수 이름==> ', '레드벨벳'
'가수 이름==> ', '우주소녀'
'가수 이름==> ', '트와이스'
'가수 이름==> ', '여자친구'

위와 같이 3가지 결과 값을 얻을 수 있다.

하지만 LIMIT는 변수를 사용할 수 없다.

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

19:53:14	SELECT mem_name, height FROM member ORDER BY height LIMIT @count	Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@count' at line 1	0.000 sec

이를 해결하는 것이 PREPARE와 EXECUTE이다. PREPARE는 실행하지 않고 SQL문만 준비해 놓고 EXECUTE에서 실행하는 방식이다.

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

# mem_name, height
'오마이걸', '160'
'레드벨벳', '161'
'우주소녀', '162'

---

1-3. 데이터 형 변환

문자를 정수로 바꾸거나 반대로 바꾸는 것을 데이터의 형 변환이라고 한다. 형 변환에는 직접 함수를 사용해서 변환하는 명시적인 변환과 별도의 지시없이 변환하는 암시적인 변환이 있다.

함수를 이용한 명시적인 변환

데이터 형식을 변환하는 함수는 CAST(), CONVERT()이다. 형식만 다를 뿐 동일한 기능을 한다.

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

market_db의 구매 테이블(buy)에서 평균 가격을 구하는 SQL을 확인하면 결과가 실수로 나온다.

SELECT AVG(price) AS '평균 가격' FROM buy;

# 평균 가격
'142.9167'

이를 CAST(), CONVERT()를 사용해서 정수로 변경할 수 있다. 이 두 함수에 올 수 잇는 데이터 형식은 CHAR, SIGNED(부호가 있는 정수), UNSIGNED(부호가 없는 정수), DATE, TIME, DATETIME등이다.

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

# 평균 가격
'143'

이번에는 날짜를 확인해 보자. 다양한 구분자를 날짜형으로 변경할 수 있다.

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

# CAST('2022$12$12' AS DATE)
'2022-12-12'

# CAST('2022/12/12' AS DATE)
'2022-12-12'

# CAST('2022%12%12' AS DATE)
'2022-12-12'

# CAST('2022@12@12' AS DATE)
'2022-12-12'

SQL의 결과를 원하는 형태로 표현하는 경우도 사용할 수 있다. 가격(price)과 수량(amount)을 곱한 실제 구매액을 표시하는 SQL을 아래와 같이 작성할 수도 있다.

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

# num, 가격x수량, 구매액
'1', '30X2=', '60'
'2', '1000X1=', '1000'
'3', '200X1=', '200'
'4', '200X5=', '1000'
'5', '50X3=', '150'
'6', '80X10=', '800'
'7', '15X5=', '75'
'8', '15X2=', '30'
'9', '50X1=', '50'
'10', '30X1=', '30'
'11', '15X1=', '15'
'12', '30X4=', '120'

---

암시적인 변환

암시적인 변환은 위 함수를 사용하지 않고 자연스럽게 형이 변환되는 것을 의미한다.

예를 들어 문자 '100' + '200'을 실행하면 자동으로 숫자로 형을 변환하여 덧셈을 수행한다.

SELECT '100' + '200';


# '100' + '200'
'300'

문자로 연결하려면 CONCAT()을 사용한다.

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

# CONCAT('100', '200')
'100200'

위의 의미는 즉 상황에 따라 달라진다는 이야기 이다. CONCAT()을 사용하면 작은 따옴표가 없더라도 문자로 변환하여 연산하고, 일반적인 사항에서 작은따옴표로 문자 + 숫자를 하더라도 자동으로 숫자로 변환하여 연산한다.

SELECT CONCAT(100, '200');

# CONCAT(100, '200')
'100200'


SELECT 100 + '200';

# 100 + '200'
'300'