SQL | Data Type, JOIN, Programming

SQL에서 사용되는 여러가지 데이터 타입, JOIN, SQL 프로그래밍에 대해 정리하는 글입니다.

목차

  1. 데이터베이스와 SQL
  2. SQL 미리 맛보기
  3. SQL 기본 문법
  4. SQL 고급 문법 ⬅ 데이터 타입, JOIN, SQL 프로그래밍
  5. 테이블과 뷰
  6. 인덱스
  7. 스토어드 프로시져
  8. 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 데이터를 저장합니다.
  • LONGTEXTLONGBLOB은 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
2
SET @변수이름 = 변수의 값; --변수 선언
SELECT @변수이름; -- 변수 값 출력



  • SET으로 @와 함께 지정한후 사용할 수 있습니다.
  • 하지만, 예외가 존재하는데 LIMIT 함수같은 경우에는 사용할 수 없습니다.
1
2
3
SET @count = 3;
SELECT mem_name, height FROM member ORDER BY height LIMIT @count;
-- 에러 발생



이럴 때, PREPARE와 EXECUTE를 사용할수 있습니다.

1
2
3
4
5
6
SET @count = 3;
-- 1️⃣
PREPARE mySQL SELECT mem_name, height FROM member ORDER BY height LIMIT ?;

-- 2️⃣
EXECUTE mySQL USING @count;

1️⃣ PREPARE를 이용해 SQL문을 실행하지 않고 mySQL에 저장만 해놓습니다. 대입에 사용할 변수는 ? 으로 지정합니다.
2️⃣ EXECUTE로 저장된 구문을 USING을 이용해 @count를 대입시켜 실행시킵니다.



데이터 형 변환Type Conversion

데이터 타입을 변환할 수 있습니다. 변환에는 두 가지가 있습니다.



명시적인 변환explicit conversion

  • 함수를 직접 사용하는 변환입니다.
  • CAST, 및 CONVERT를 이용해 변환이 가능합니다.
1
2
CAST (값 AS 데이터 타입 [(길이)])
CONVERT (값, 데이터 타입 [(길이)])

예를 들어 다음처럼 사용할 수 있습니다.

1
2
3
4
5
6
7
8
SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy;

--다양한 구분자를 가진 날짜형도 변경할 수 있습니다.
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);
-- 출력: 2022-12-12



암시적인 변환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
2
3
4
5
SELECT <열 목록>
FROM <첫 번째 테이블>
INNER JOIN <두 번째 테이블>
ON <조인될 조건>
[WHERE 검색 조건];



INNER JOIN 예시
Microsoft Learn



  • INNER JOIN은 두 테이블에서 조인될 조건에 따라 일치하는 행만 가져옵니다.
  • 데이터가 한쪽 테이블에 존재하지 않는다면 가져오지 않습니다.

OUTER JOIN

  • OUTER JOIN은 INNER JOIN과 달리 데이터가 한쪽테이블에만 존재해도 결과가 나옵니다.
  • 기본 구조는 다음과 같습니다.
1
2
3
4
5
SELECT <열 목록>
FROM <첫 번째 테이블(LEFT)>
<LIGHT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT)>
ON <조인될 조건>
[WHERE 검색 조건];
  • 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
2
3
4
5
SELECT <열 목록>
FROM <테이블> 별칭A
INNER JOIN <테이블> 별칭B
ON <조인될 조건>
[WHERE 검색 조건];



여기서 중간점검을 해보겠습니다.

회원 (member)으로 가입만 하고 한번도 구매 (buy) 한 적이 없는 회원의 목록을 얻으려 합니다.

다음 SQL문에서 빈칸에 무엇이 들어가야 할까요?

1
2
3
4
5
6
SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
FROM member M
LEFT OUTER JOIN buy B
ON M.mem_id = B.mem_id
[ ]
ORDER BY M.mem_id;

<보기>

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문만 읽어보면 다음과 같습니다.

  1. DISTINCT로 mem_id, prod_name, mem_name, addr의 중복된 row를 제거합니다.
  2. LEFT TABLE은 member 입니다.
  3. LEFT OUTER JOIN을 이용할 것이고 RIGHT TABLE은 buy입니다.
  4. 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
2
3
4
5
6
7
8
DELIMITER $$
CREATE PROCEDURE Stored_Procedure_Name()
BEGIN
-- SQL 프로그래밍 기입
END $$
DELIMITER ;

CALL Stored_Procedure_Name(); -- 실행

여기서 부터는 stored procedure를 설명할 때, -- SQL 프로그래밍 기입 부분에 대해서만 기입하겠습니다.



IF

기본 문법은 다음과 같습니다.

1
2
3
4
5
IF <조건식> THEN
-- SQL 작성
[ELSE]
-- SQL 작성
END IF



예를 들면 아래처럼 작성이 가능합니다.

1
2
3
4
5
6
7
DECLARE myNum INT;
SET myNum = 200;
IF myNum = 100 THEN
SELECT '100입니다.';
ELSE
SELECT '100이 아닙니다.';
END IF;



CASE

  • 여러가지 조건 중에서 선택하는 경우, CASE문을 사용할 수 있습니다.
  • 기본 구조는 다음과 같습니다.
1
2
3
4
5
6
7
8
9
10
CASE
WHEN 조건1 THEN
--SQL문 1
WHEN 조건2 THEN
--SQL문 2
WHEN 조건3 THEN
--SQL문 3
ELSE:
--SQL문 4
END CASE;



실제로 쓰일 수 있는 예시는 다음과 같습니다. CASE문으로 price * amount 에 따라 회원 등급을 분류하는 예시입니다.

1
2
3
4
5
6
7
8
9
10
11
12
SELECT M.mem_id, M.mem_name, SUM(price*amount) "총 구매액",
CASE
WHEN (SUM(price*amount) >= 1500) THEN '최우수고객'
WHEN (SUM(price*amount) >= 1000) THEN '우수고객'
WHEN (SUM(price*amount) >= 1) THEN '일반고객'
ELSE '유령고객'
ELSE "회원등급"
FROM but B
RIGHT OUTER JOIN member M
ON B.mem_id = M.mem_id
GROUP BY M.mem_id
GROUP BY SUM(price*amount) DESC;



WHILE

  • 프로그래밍에 필수적인 반복문을 WHILE로 사용할 수 있습니다.
  • 기본 구조는 다음과 같습니다.
1
2
3
WHILE <조건식> DO
-- SQL 구문
END WHILE;



예를 들면 다음과 같습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE i INT;
DECLARE hap INT;
SET i = 1;
SET hap = 0;

myWhile:
WHILE (i<= 100) DO
IF (i%4 = 0) THEN
SET i = i +1;
ITERATE myWhilel; -- 지정한 label 문으로 가서 계속 진행.
END IF;
SET hap = hap + i;
IF (hap > 1000) THEN
LEAVE myWhile;
END IF;
SET i = i + 1;
END WHILE;

SELECT '1부터 100까지의 합(4의 배수 제외), 1000넘으면 종료 ==>', hap;
  • 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
2
3
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXCUTE myQuery;
DEALLOCATE PREPARE myQuery;



오늘은 데이터 타입, JOIN, SQL Programming에 대해 정리하였습니다.

다음은 TABLE과 VIEW에 대해 알아보겠습니다.

읽어주셔서 감사합니다.✨