혼자 공부하는 SQL 4장 - SQL 고급문법

04-1 SQL의 데이터 형식

 

<데이터 형식>

- MySQL에서 제공하는 데이터 형식의 종류는 수십개이다 -> 이를 다 외울 필요는 없고, 자주 사용하는 혀앹를 우선기억하자.

1) 정수형 : 소수점이 없는 숫자. 인원수, 가격, 수량 등에 많이 사용.

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

- BIT, MEDIUMINT도 있으나 잘 사용하진 않는다.

- 범위를 벗어날 경우 아래와 같이 Out of range 에러가 발생한다. 이는 입력값의 범위를 벗어남을 의미한다.

Error Code: 1264. Out of range value for column 'tinyint_col' at row 1

- UNSIGNED 를 사용하면 값의 범위가 0부터 시작하게 되어 양수로 표현할수 있는 값의 범위가 2배로 증가하게 된다.

 

2) 문자형

- 글자를 저장하기 위해 사용하며 입력할 최대 글자의 개수를 지정해야 한다.

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

- BINARY, VARBINARY도 있지만 잘 사용하지 않는다.

- CHAR는 고정길이 문자형이다. 즉 실제 들어가는 글자수와 상관없이 CHAR에 선언된 숫자만큼의 공간이 차지된다.

- 이와 달리 VARCHAR은 가변길이 문자형으로, 뒤에 숫자는 최대 길이이며 그보다 적은 수의 글자가 들어갈 경우 그만큼의 길이만 저장이 된다.

- VARCHAR이 CHAR보다 공간을 효율적으로 운영하지만, MySQL 내부적으로 성능 면에서는 CHAR로 설정할때 조금 더 좋다.

 

※ 숫자 데이터를 정수로 넣을지 문자로 넣을지 정하는 기준

- 더하기 빼기 등의 연산에 의미가 있다.

- 크다 작다 또는 순서에 의미가 있다.

=> 이 둘에 해당이 되지 않는다면 문자로 취급하는게 좋다.

 

3) 대량의 데이터 형식

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

- CHAR보다 더 큰 데이터를 저장하기 위해 만들어진 형식

- BLOB는 이미지나 동영상 데이터를 저장하는 형식이다.

 

4) 실수형

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

- 보통 FLOAT로도 충분히 표현이 가능하다.

 

5) 날짜형

데이터 형식	바이트 수	설명
DATE	3	날짜만 저장, YYYY-MM-DD 형식 사용
TIME	3	시간만 저장, HH:MM:SS 형식 사용
DATETIME	6	날짜 및 시간 저장 YYYY-MM-DD HH:MM:SS 형식 사용

 

<변수의 사용>

- SQL도 다른 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다.

SET @변수 이름 = 변수의 값;      -- 변수 선언 및 값 대입
SELECT @변수이름;               -- 변수 값 출력

- SELECT 같은 곳은 변수를 사용가능하지만, LIMIT 같은 곳은 바로 변수를 쓸 수 없다. 이 경우 변수를 쓸 수 있도록 PREPARE, EXECUTE 등으로 조정이 필요하다.

SET @count = 3; ---------------------------------------------------- (1)
PREPARE mySQL FROM 'SELECT * FROM db1 ORDER BY id LIMIT ?'; -------- (2)
EXECUTE mySQL USING @count; ---------------------------------------- (3)

(1) count에 3을 대입

(2) PREPARE를 통해 SELECT 구문을 mySQL이란 이름으로 준비를 해놓는다. '?'에는 나중에 값을 넣는다는 의미이다.

(3) EXECUTE를 통해 mySQL을 실행하고 이때 USING을 통해 '?'에 들어갈 값을 채워 넣는다.

 

<데이터 형 변환>

- 형 변환 : 문자를 정수형으로 바꾸는 등 데이터의 형식을 바꾸는 것

- 직접 함수를 사용해서 변환하는 명시적인 변환, 별도의 지시없이 자연스레 바꾸는 임시적인 변환 등이 있다.

 

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

- CAST, CONVERT 등의 함수를 사용. 두 함수는 사용법이 다르지만 동일한 기능을 한다.

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

- CAST나 CONVERT에 올수 있는 데이터 형식은 CHAR,SIGNED, UNSIGNED, DATE, TIME DATETIME 등이 있다. 

 

2) 일시적인 변환

- 숫자와 문자를 연산할때 CONCAT함수를 사용하면 숫자가 문자로 변하고, 일반적인 더하기만 사용하면 문자가 숫자로 바뀌는 등 계산이나 함수 실행 결과로 인해 자연스레 바뀌는 것을 의미.

 

 

04-2 두 테이블을 묶는 조인

 

조인 : 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것

일대다 관계 : 한쪽 테이블에는 하나의 값만, 다른쪽 테이블에는 여러개의 값이 존재할 수 있는 관계

 

<내부조인>

- 일반적인 조인을 일컫는 말. 가장 많이 사용된다.

SELECT <열 목록>
FROM <첫 번째 테이블>
	INNER JOIN <두 번째 테이블>
    ON <조인될 조건>
[WHERE 검색 조건]

<외부조인>

- 한쪽에만 데이터만 있어도 조인이 가능한 방식.

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
	<LEFT|RIGHT|FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
    ON <조인될 조건>
[WHERE 검색 조건]

 

<기타조인>

1) 상호조인 : 한쪽 테이블의 모든 행과 다른쪽 테이블의 모든 행을 조인 시키는 기능, 전체 행 개수는 두 테이블의 행 개수를 곱한것과 동일

SELECT <열 목록>
FROM <첫 번째 테이블>
	CROSS JOIN <두 번째 테이블>;

2) 자체조인: 자신이 자신과 조인한다. 1개의 테이블만을 사용.

SELECT <열 목록>
FROM <테이블> 별칭 A
	INNER JOIN <테이블> 별칭 B
    ON <조인될 조건>
[WHERE 검색 조건]

 

04-3 SQL 프로그래밍

 

스토어드 프로시저 : MySQL에서 프로그래밍 기능이 필요할때 사용하는 데이터베이스 개체.

DELIMETER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
	이 부분에 SQL 프로그래밍 코딩
END $$
DELIMETER;

CALL 스토어드_프로시저_이름()

IF문 : 조건문으로 가장 많이 사용되는 프로그래밍 언어

IF <조건식> THEN
	SQL 문장들
END IF

CASE문 : 여러 가지 조건중에서 선택해야 하는 경우

CASE
    WHEN 조건1 THEN
    	SQL문장들1
    WHEN 조건2 THEN
    	SQL문장들2
    WHEN 조건3 THEN
    	SQL문장들3
    ELSE
    	SQL문장들4
END CASE;

WHILE문 : 조건식이 참인 동안은 반복하도록 하기

WHILE <조건식> DO
	SQL 문장들
END WHILE;

- ITERATE,LEAVE 등으로 반복문을 빠져나갈지, 조건식으로 갈지도 결정할 수 있다.