SQL(2)-데이터 형식 및 조인 종류

오늘은 SQL 2번째 파트입니다. 테이블을 만들 떄는 데이터 형식을 설정해야합니다. SQL내에서는 다양한 데이터 형식이 존재하는데, 실제로 저장될 데이터의 형태가 다양하기 때문입니다. 각 데이터에 맞는 데이터 형식을 지정함으로써 효율적으로 데이터베이스 내에 저장할 수 있습니다.

 

데이터 형식

1.정수형

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

 

또, 정수형은 unsigned을 활요하면 범위가 0부터 시작하게 사용할 수 있습니다. 예시로, TINYINT UNSIGNED 을 선언하면 0~255까지로 범위를 바꿀 수 있습니다.

 

2.문자형

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

 

CHAR는 문자를 의미하는 약자로, 고정길이 문자형입니다. VARCHAR는 가변길이 문자형으로, VARCHAR(10)에 3글자 저장할 경우, 3자리만 사용합니다. VARCHAR가 CHAR보다 공간을 효율적으로 운영할 수 있지만, MySQL 내부적으로 성능면에서는 CHAR로 설정하는 것이 조금 더 좋습니다.

 

3. 대량의 형식

데이터 형식		바이트 수
TEXT 형식	TEXT	1~65535
	LONGTEXT	1~약 42억

text형식 말고 blob이라는 형식도 존재하는데, 이미지, 동영상 등의 데이터라고 생각하면 됩니다. 이런 것을 이진 데이터라고 부릅니다.

 

4.실수형

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

 

5.날짜형

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

 

6.변수 선언

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

변수는 워크벤치를 재시작 할때까지는 유지되지만, 종료하면 없어집니다.  SELECT 문에서 행을 제한하는 LIMIT에도 변수를 사용하려면 PREPARE 와 EXECUTE입니다. 아래가 기본형입니다!

SET @COUNT=3;
PREPARE mySQL FROM ~;
EXECUTE mySQL USING @count;

 

7.데이터 형 변환

형변환은 문자형을 정수형으로 바꾸는 등을 의미합니다. 형 변환 에는 직접 함수를 사용해서 변환하는 명시적인 변환과 암시적인 변환이 있습니다.

  1) 명시적인 변환

  데이터 형식을 변환하는 함수는 CAST(),CONVERT()입니다.

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

  

  2)암시적인 변환

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

SELECT '100'+ '200'; -- 300
SELECT CONCAT('100'.'200'); -- 100200

---

조인

조인이란 두개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말합니다.지금까지는 하나의 테이블을 다루는 작업을 위주로 공부했지만, 두개의 테이블이 서로 관계되어 있는 상태를 고려하는 상황입니다. 데이터 베이스의 테이블은 하나로 구성되는 것보다는 여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적입니다.

1.내부 조인

내부 조인이라고 불리기도 하고, 그냥 조인이라고 불리긴도 합니다. 두 테이블의 조인을 위해서는 테이블이 일대다(onetomany) 관계로 연결되어야합니다. 일대다 관계란 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러개의 값이 존재할 수 있는 관계를 말합니다. 두 테이블의 조인을 하기 위해서는 기본키(PK)-외래키(FK)관계를 맺고 있어야합니다.

기본키는 후보키들중 메인으로 선정되는 키로 후보키의 성질을 갖습니다. 유일성과 최소성을 가지며, 해당 레코드를 식별할때 기준이 되는 반드시 필요한 키입니다. 추가로 기본키는 다음과 같은 조건을 따릅니다.

• 값의 변동이 잦은 후보키는 기본키로 부적절하다
• NULL값을 가질 수 없다.
• 하나의 테이블에는 반드시 하나의 기본키만 존재한다

외래키는 테이블간의 관계를 나타낼 때 사용하며, 다른 테이블의 기본키를 참조해 외래키로 사용합니다.즉, 한 테이블의 외래키는 연결되어있는 다른 테이블의 기본키중 하나입니다. 사실 외래키 설정을 다로 하지 않아도 데이터 조회가 가능하지만, 굳이 하는 이유는 데이터 무결성 때문입니다. 데이터가 항상 정확한 값을 유지하는 성질을 지니고 있습니다.

 

내부조인의 기본형식은 다음과 같습니다.

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

 또 SQL에는 정규화라는 개념이 있습니다. 정규화는 데이터베이스의 데이터들을 최대한 중복 제거하여 이상현상을 방지하기 위한 기술입니다.아래 블로그에서 너무 잘 정리해주셔서 참고하시면 좋을꺼 같습니다:) ! 기회가 된다면 정규화 관련 내용을 한번 정리해보겠습니다.

[ Database ] 제 1정규화, 제 2정규화, 제 3정규화

 

정규화 개념을 왜 나왔냐면 테이블 세팅할때 항상 고려해야되기 때문입니다!

USE market_db;
select mem_id,mem_name,prod_name 
from buy
inner join member
on buy.mem_id = member.id;
-- Error Code: 1052. Column 'mem_id' in field list is ambiguous	0.000 sec

아래와 같이 error code가 나오는 것을 확인할 수 있었는데, 열이름인 mem_id가 불확실하다는 오류 메세지입니다. 즉, 회원 아이디는 회원 테이블, 구매 테이블에 모두 들어있어 어느 테이블의 mem_id 인지 헷갈린다는 의미입니다. 이런걸 방지하기 위해서는 정규화를 제대로 해놓거나, 테이블_이름.열_이름 형식으로 지정하는 방법이 있습니다.

USE market_db;
select buy.mem_id,member.mem_name,buy.prod_name 
from buy
inner join member
on buy.mem_id = member.id;

위와 같이 테이블_이름.열_이름 형식으로 하면 오류는 안나지만 깔끔해 보이지가 않아서 테이블들을 약자를 활용해서 지정해보겠습니다.

USE market_db;
select B.mem_id,M.mem_name,B.prod_name 
from buy B
inner join member M
on B.mem_id = M.mem_id;

 

지금까지 사용한 내부 조인은 두 테이블에 모두 있는 내용만 조인되는 방식이지만, 양쪽 중에서 한곳이라도 내용이 있을때 조인하려면 외부 조인을 사용해야합니다. 

 

2.외부 조인

외부 조인은 한쪽에만 데이터가 있어도 결과가 나오는 조인입니다. 형식은 다음과 같습니다.

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

'LEFT OUTER JOIN' 문의 의미를 '왼쪽 테이블의 내용을 모두 출력해야한다' 정도로 해석하면 기억하기 쉽습니다 :)

 

3.기타 조인

상호 조인과 자체 조인도 있습니다. 상호 조인은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능입니다.아래는 상호 조인의 기본 형식입니다.

SELECT *
FROM ~
CROSS JOIN ~;

상호조인의 특징은 ON 구문을 사용할 수 없고, 결과에 내용의 의미가 없습니다. 상호 조인의 주 용도는 대용량의 데이터를 생성할 때입니다.

 

자체 조인은 자신과 조인한다는 의미입니다. 별도의 문법이 있는 것이 아니고 1개로 조인하면 자체 조인이 되는 것입니다.

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

---

SQL 프로그래밍

 

스토이도 프로시저는 MySQL에서 ㅍ로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체입니다. 

DELIMIER $$
CREATE PROCECURE
BEIGN

(코딩 부분)

END $$
DELIMITER;
CALL 프로시저_이름();

 

SQL 문법 자체는 파이썬과 비슷해서 간략하게 보고 넘어가겠습니다. 

-- IF 문

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

--IF~ELSE 문

IF <조건식> THEN
	SQL문장들;
ELSE
	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문장들;
    ITERATE[레이블]:지정된 LABEL문 가서 실행
    LEAVE[레이블]-WHILE문 종료
    
END WHILE;

-- PREPARE와 EXECUTE 상황에 따라 내용 변경이 필요할때 사용 -> 동적 SQL

PREPARE myQuery FROM'sql문장';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery; --문장 해제
  
END WHILE;

---