MySQL의 데이터 형식

데이터 형식

1. 정수형 : 소수점이 없는 숫자

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

• 정수형에 UNSIGNED를 붙이면 범위가 0부터 지정

      CREATE TABLE member -- 회원 테이블
      (
          member_id TINYINT UNSIGNED,
          -- member_id TINYINT NOU NULL
      )

2. 문자형 : 글자를 저장하기 위해 사용, 입력할 최대 글자의 개수를 지정

   데이터 형식	바이트 수	숫자 범위
   CHAR(개수)	1	-128 ~ 127
   VARCHAR(개수)	2	-32,768 ~ 32,767

• CHAR(Character) : 고정 길이 문자형
  • CHAR(10)에 3글자만 저장하더라도 7자리는 낭비
• VARCHAR(Variable Character) : 가변 길이 문자형
• VARCHAR가 CHAR보다 공간을 효율적으로 운형하지만, 내부적으로 속도를 고려하면 CHAR로 설정하는것이 더 좋음
• 데이터가 숫자 형태라도 연산이나 크기에 의미가 없다면 문자형으로 지정하는 것이 좋음.
  • ex. 전화번호

3. 대량의 데이터 형식

   데이터 형식		숫자 범위
   TEXT 형식	TEXT	1~65535
   	LONGTEXT	1~4294967295
   BLOB 형식	BLOB	1~65535
   	LONGBLOB	1~4294967295

• TEXT : 소설이나 영화대본같은 내용을 저장시
• BLOB(Binary Long Object) : 글자가 아닌 이미지, 동양상 등의 데이터

4. 실수형

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

• 소수점 아래를 어디까지 정밀하게 표현하는지의 차이

5. 날짜형

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

---

변수의 사용

• SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용가능

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

• PREPARE & EXECUTE

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

  -- 이를 실행하기위해 PREPARE & EXECUTE 활용
  SET @count = 3;
  PREPARE mtSQL FROM 'SELECT mem_name, hegith FROM member ORDER BY height LIMIT ?';
  EXECUTE mySQL USING @count;

데이터 형 변환

• 형 변환 : 문자형 -> 정수형, 정수형 -> 문자형으로 바꾸는 것
  • 명시적인 변환 : 직접 함수를 사용해서 변환
  • 암시적인 변환 : 별도의 지시없이 자연스럽게 변환

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

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

• 정수형
  • SIGNED : 부호가 있는 정수 / UNSIGNED : 부호가 없는 정수

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

• 날짜

	SELECT CAST('2023$12$12' AS DATE);
	SELECT CAST('2023/12/12' AS DATE);

2. 암시적인 변환

• CAST()나 CONVERT() 함수를 사용하지 않고 자연스럽게 형이 변환

	SELECT '100' + '200'; -- 300

---

두 테이블을 묶는 조인

내부 조인

1. 일대다 관계의 이해

   • 두 테이블의 조인을 위해서는 테이블이 일대다(one to many) 관계로 연결되어야 함.
   • 구매 데이터를 활용할 경우
     • 회원 테이블의 아이디를 기본 키(Primary Key, PK)로 지정
     • 구매 테이블의 아이디는 외래 키(Foreign Key, FK)로 지정

2. 내부 조인의 기본

   • 일반적으로 조인이라고 부르는 것은 내부 조인(inner join)을 말함

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

외부 조인

1. 외부 조인의 기본
   • 외부 조인(outer join) : 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과를 추출 가능

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

기타 조인

1. 상호 조인(cross join) = 카티션 곱(cartesian product)
   • 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든행을 조인시키는 기능
   • 행의 개수 = A테이블의 행수 * B 테이블의 행수
   • 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때

	SELECT *
	FROM buy
		CROSS JOIN member;

2. 자체 조인(self join)
   • 자신이 자신과 조인한다는 의미

---

SQL 프로그래밍

• 스토어드 프로시저는 MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터 베이스 개체
• SQL 프로그래밍은 기본적으로 스토어드 프로시저 안에 만들어야 함

  DELIMITER $$ -- 스토어드 프로시저의 코딩 부분 
  CREATE PROCEDURE 스토어드_프로시저_이름()
  BEGIN 
      이 부분에 SQL 프로그래밍 코딩
  END $$ -- 스토어드 프로시저 종료
  DELIMITER ; -- 종류 문자를 다시 세미콜온으로 변경;
  CALL 스토어드_프로시저_이름(); -- 스토어드 프로지서 실행

IF 문

1. IF 문의 기본 형식

• 'SQL문장들'이 한문장이라면 그 문장만 써도 되지만, 두 문장 이상이 처리되어야 할 때는 BEGIN~END로 묶어줘야함

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

2. IF ~ ELSE 문

	DELIMITER $$
		CREATE PROCEDURE ifProc2()
		BEGIN
			DECLARE myNum INT;
			set myNum = 200;
			IF myNum = 100 THEN
				SELECT '100입니다.';
			ELSE 
				SELECT '100이 아닙니다.';
			END IF;
	END $$
	DELIMITER;
	CALL ifProc2(); -- 100이 아닙니다.

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

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

4. WHILE 문
   • 필요한 만큼 계속 같은 내용을 반복하는 경우

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

• 1부터 100까지의 값을 모두 더하는 쿼리

		DROP PROCEDURE IF EXISTS whileProc;
		DELIMITER $$
		CREATE PROCEDURE whileProc()
		BEGIN
			DECLARE i INT; -- 1부터 100까지 증가할 변수
			DECLARE hap INT; -- 더한 값을 누적할 변수
			SET i = 1;
			SET hap = 0;

			WHILE (i <= 100) DO
				SET hap = hap + u;
				SET i = i + 1;
			END WHILE;
			SELECT '1부터 100까지의 합 ==>', hap;
		END $$
		DELIMITER;
		CALL whileProc();

• 응용
  • ITERATE [레이블] : 지정한 레이블로 가서 계속 진행 == continue
  • LEAVE [레이블] : 지정한 레이블을 빠져나감. WHILE 문 종료 == break

		DROP PROCEDURE IF EXISTS whileProc2;
		DELIMITER $$
		CREATE PROCEDURE whileProc2()
		BEGIN
			DECLARE i INT; -- 1부터 100까지 증가할 변수
			DECLARE hap INT; -- 더한 값을 누적할 변수
			SET i = 1;
			SET hap = 0;

			myWhile:
			WHILE (i <= 100) DO
				IF (i%4 = 0) THEN
					SET i = i + 1;
					ITERATE myWhile; -- 지정한 label 문으로 계속 가서 진행
				END IF;
				SET hap = hap + i;
				IF (hap > 1000) THEN
					LEAVE mywhile; -- 지정한 label 문을 떠남. 즉 While 종료
			END WHILE;
			
			SELECT '1부터 100까지의 합(4의 배수 제외), 1000넘으면 종료
			 ==>', hap;
		END $$
		DELIMITER;
		CALL whileProc2();

5. 동적 SQL
   1. PREPARE : SQL문을 실행하지 않고 미리 준비
   2. EXECUTE : 준비한 SQL문을 실행
   3. DEALLOCATE PREPARE로 문장해제

	use market_db;
	PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
	EXECUTE myQuery;
	DELLOCATE PREPARE myQuery;

---

• 참고 서적 : 혼자 공부하는 SQL