SQL에서 사용되는 여러가지 데이터 타입, JOIN, SQL 프로그래밍에 대해 정리하는 글입니다.
목차
- 데이터베이스와 SQL
- SQL 미리 맛보기
- SQL 기본 문법
- SQL 고급 문법 ⬅ 데이터 타입, JOIN, SQL 프로그래밍
- 테이블과 뷰
- 인덱스
- 스토어드 프로시져
- SQL과 파이썬 연결
데이터 타입Data Type
데이터베이스에서 테이블을 만들 때 데이터 타입을 설정 할 수 있습니다. 수십 가지가 넘어서 복잡할 수 있지만, 여기에는 여러 이점이 있습니다.
- 데이터를 정확하게 파악하는데 도움을 줄 수 있습니다.
- 저정공간 확보할 수 있습니다.⭐
- 특정 데이터 타입에만 사용할 수 있는 연산, 제약조건, 함수 등을 사용할 수 있습니다.
정수형Integer
데이터 타입 | 바이트 수 | 비트 | 숫자 범위 | UNSIGNED |
---|---|---|---|---|
TINYINT | 1 | 8bit | -128~127 | 256 (28) |
SMALLINT | 2 | 16bit | -32,768 ~ 32,767 | 65536 (216) |
INT | 4 | 32bit | 약 -21억 ~ +21억 | 232 |
BIGINT | 8 | 64bit | 약 -900경 ~ 900경 | 264 |
- 정수형 자료는 타입에 따라 작성할 수 있는 숫자범위가 다릅니다.
UNSIGNED
로 데이터 타입을 지정한다면 0 ~ 최대범위로 지정됩니다. (TINYINT
의 경우 0 ~ 256)- 예를 들어, 사람의 키와 같은 수치는 단위가 cm로 일정하다면
TINYINT UNSIGNED
을 사용하면 0 ~ 256으로 데이터 입력이 가능합니다.
문자형Character
데이터 타입 | 바이트 수 | 데이터 사이즈 |
---|---|---|
CHAR(개수) | 1 ~ 255 | 설정된 길이로 고정됨 |
VARCHAR(개수) | 1 ~ 16383 | 문자 길이에 따라 다름 |
- 문자형은 글자를 저장할 수 있고, 입력할 최대 글자 개수를 정해서 테이블에 지정합니다.
CHAR
는 고정길이 문자형으로 자릿수가 고정되어 있습니다.
예를 들어,CHAR(10)
으로 지정한 후 "abc"를 추가하면 10글자를 모두 확보합니다.VARCHAR
는 최대 글자수는 정해져 있지만, 입력된 문자 길이만을 확보합니다.VARCHAR
가 공간적으로 더 효율적이지만, 속도측면에서는CHAR
가 조금 더 빠릅니다.
대량의 데이터 타입
데이터 타입 | 최대 바이트 수 |
---|---|
TEXT | 216 |
LONGTEXT | 232 |
BLOB | 216 |
LONGBLOB | 232 |
CHAR
,VARCHAR
보다 더 큰 데이터를 저장하기 위해 TEXT, BLOBBinary Long Object이 존재합니다.- BLOB은 이미지, 동영상 같은 Binary 데이터를 저장합니다.
LONGTEXT
와LONGBLOB
은 4GB까지 저장이 가능합니다.
예를 들어, 아마도 넷플릭스의 동영상은LONGBLOB
, 자막은LONGTEXT
에 저장할 것입니다.
실수형Float or Real Number
데이터 형식 | 바이트 수 | 설명 |
---|---|---|
FLOAT | 4 | 소수점 아래 7자리까지 표현 |
DOUBLE | 8 | 소수점 아래 15자리까지 표현 |
- 소수점이 있는 숫자를 저장하는 타입입니다.
날짜형Date/Time
데이터 형식 | 바이트 수 | 설명 |
---|---|---|
DATE | 3 | 날짜만 저장. YYYY-MM-DD 형식 |
TIME | 3 | 시간만 저장. HH:MM:SS 형식 |
DATETIME | 8 | 날짜 및 시간을 저장. YYYY-MM-DD HH:MM:SS 형식. |
- 날짜 및 시간을 저장할 때 사용합니다.
변수 사용
SQL에서 변수를 지정하는 방법은 다음과 같습니다.
1 | SET @변수이름 = 변수의 값; --변수 선언 |
SET
으로@
와 함께 지정한후 사용할 수 있습니다.- 하지만, 예외가 존재하는데
LIMIT
함수같은 경우에는 사용할 수 없습니다.
1 | SET @count = 3; |
이럴 때, PREPARE와 EXECUTE를 사용할수 있습니다.
1 | SET @count = 3; |
1️⃣ PREPARE
를 이용해 SQL문을 실행하지 않고 mySQL에 저장만 해놓습니다. 대입에 사용할 변수는 ?
으로 지정합니다.
2️⃣ EXECUTE
로 저장된 구문을 USING
을 이용해 @count
를 대입시켜 실행시킵니다.
데이터 형 변환Type Conversion
데이터 타입을 변환할 수 있습니다. 변환에는 두 가지가 있습니다.
명시적인 변환explicit conversion
- 함수를 직접 사용하는 변환입니다.
CAST
, 및CONVERT
를 이용해 변환이 가능합니다.
1 | CAST (값 AS 데이터 타입 [(길이)]) |
예를 들어 다음처럼 사용할 수 있습니다.
1 | SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy; |
암시적인 변환inplicit conversion
- 자연스럽게 타입이 변형되는 것입니다.
- 예를들어 다음과 같습니다.
SELECT '100' + '200';
은 문자에 대한 연산이지만, 300을 결과로 내줍니다.SELECT 100 + '200';
은 숫자와 문자가 혼재하지만, 300을 결과로 내줍니다.SELECT CONCAT(100, '200');
또한 숫자가 포함되어있지만 문자로 인식하여 100200을 결과로 내줍니다.
JOIN
- 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내줍니다.
- INNER, OUTER, CROSS, SELF 로 나누어집니다.
⭐ 혼공학습단 홈페이지에도 좋은 예시가 있습니다.
INNER JOIN
- 두 테이블을 연결 할 때 가장 많이 사용되는 JOIN입니다. INNER JOIN을 JOIN으로 사용해도 됩니다.
- 기본 구조는 다음과 같습니다.
1 | SELECT <열 목록> |
INNER JOIN 예시
Microsoft Learn
- INNER JOIN은 두 테이블에서 조인될 조건에 따라 일치하는 행만 가져옵니다.
- 데이터가 한쪽 테이블에 존재하지 않는다면 가져오지 않습니다.
OUTER JOIN
- OUTER JOIN은 INNER JOIN과 달리 데이터가 한쪽테이블에만 존재해도 결과가 나옵니다.
- 기본 구조는 다음과 같습니다.
1 | SELECT <열 목록> |
- OUTER JOIN은 LEFT, RIGHT, FULL 로 나누어 집니다.
- 각각 우선되야할 테이블을 어떤 것으로 할지 나타낸다고 보시면 됩니다.
LEFT OUTER JOIN
LEFT OUTER JOIN
Microsoft Learn
RIGHT OUTER JOIN
RIGHT OUTER JOIN
Microsoft Learn
FULL OUTER JOIN
FULL OUTER JOIN
Microsoft Learn
CROSS JOIN
- CROSS JOIN은 한쪽 테이블의 모든 row를 다른 쪽 테이블의 모든 row와 JOIN하는 기능입니다.
- 카티시안 곱cartesian product라고도 부릅니다.
CROSS JOIN
SQLShack
CROSS JOIN의 특징은 다음과 같습니다.
ON
을 사용할 수 없음.- 모든 테이블을 JOIN하기 때문에 결과의 내용은 의미가 없음.
- 주로 테스트를 하기 위한 대용량 데이터를 생성하기 위해 사용.
SELF JOIN
- 테이블 1개에 대해 자체 JOIN을 합니다. 1개의 테이블을 사용합니다.
- 기본 구조는 다음과 같습니다.
1 | SELECT <열 목록> |
여기서 중간점검을 해보겠습니다.
회원 (member)으로 가입만 하고 한번도 구매 (buy) 한 적이 없는 회원의 목록을 얻으려 합니다.
다음 SQL문에서 빈칸에 무엇이 들어가야 할까요?
1 | SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr |
<보기>
1️⃣ JOIN B.prod_name IS NULL
2️⃣ LIMIT B.prod_name IS NULL
3️⃣ HAVING B.prod_name IS NULL
4️⃣ WHERE B.prod_name IS NULL
존재하는 SQL문만 읽어보면 다음과 같습니다.
DISTINCT
로 mem_id, prod_name, mem_name, addr의 중복된 row를 제거합니다.- LEFT TABLE은 member 입니다.
LEFT OUTER JOIN
을 이용할 것이고 RIGHT TABLE은 buy입니다.- member TABLE의 mem_id로 정렬합니다.
OUTER JOIN
의 구조상으로도 그렇지만 한번도 구매한 적 없는 이라는 조건이 없습니다. 따라서, 조건을 걸어주기 위해 WHERE
를 사용하고 buy TABLE에서 prod_name이 존재하지 않는 row만 고른다면 (NULL),4️⃣ WHERE B.prod_name IS NULL
이 맞습니다.
SQL Programming
- Stored Procedure으로 프로그래밍을 할 수 있습니다.
- 기초 문법으로는
IF
,CASE
,WHILE
,동적 SQL
가 있습니다.
Stored Procedure의 기본 구조는 다음과 같습니다.
1 | DELIMITER $$ |
여기서 부터는 stored procedure를 설명할 때,
-- SQL 프로그래밍 기입
부분에 대해서만 기입하겠습니다.
IF
기본 문법은 다음과 같습니다.
1 | IF <조건식> THEN |
예를 들면 아래처럼 작성이 가능합니다.
1 | DECLARE myNum INT; |
CASE
- 여러가지 조건 중에서 선택하는 경우,
CASE
문을 사용할 수 있습니다. - 기본 구조는 다음과 같습니다.
1 | CASE |
실제로 쓰일 수 있는 예시는 다음과 같습니다. CASE문으로 price * amount
에 따라 회원 등급을 분류하는 예시입니다.
1 | SELECT M.mem_id, M.mem_name, SUM(price*amount) "총 구매액", |
WHILE
- 프로그래밍에 필수적인 반복문을 WHILE로 사용할 수 있습니다.
- 기본 구조는 다음과 같습니다.
1 | WHILE <조건식> DO |
예를 들면 다음과 같습니다.
1 | DECLARE i INT; |
- i, hap을
INT
로 선언합니다. 그리고 1, 0으로 지정합니다. - i를 4로 나눈 나머지가 0이면 (4의 배수라면) SET에 1을 추가하고,
myWhile
로 돌아갑니다. hap
이 1,000을 초가화면myWhile
을 빠져나갑니다.
동적 SQL
SQL은 대부분 고정된 값을 가집니다. 앞서 변수선언을 할때 PREPARE과 EXECUTE를 보여드렸습니다. SQL도 상황에 따라 값이 변동될 수 있는데, PREPARE와 EXECUTE를 사용할수 있는데, 이처럼 미리 SQL을 준비하고 나중에 실행하는 방식을 동적 SQL이라 부릅니다. 또한 문장을 실행한 후에 DEALLOCATE PREPARE
으로 해제를 해주어야 합니다. (해제해 주는 것이 바람직합니다.)
앞서 변수에 대한 값을 대입하는것은 보였드렸으므로, 해제하는 것을 보여드리면 다음과 같습니다.
1 | PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"'; |
오늘은 데이터 타입, JOIN, SQL Programming에 대해 정리하였습니다.
다음은 TABLE과 VIEW에 대해 알아보겠습니다.
읽어주셔서 감사합니다.✨