[혼자 공부하는 SQL] Chapter04. SQL 고급 문법

Chapter04-1. MySQL의 데이터 형식

[데이터 형식]

- 정수형(INT)

소수점이 없는 숫자

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

- 문자형(CHAR)

글자 저장을 위해 사용. 최대 입력 글자 수를 지정해야함

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

* CHAR은 고정길이 문자형, VARCHAR는 가변길이 문자형

cf) VARCHAR(10) 선언 후 3글자만 저장할 경우 3자리만 사용하게 됨. 즉, 공간을 효율적으로 운영 가능

 

- 대량의 데이터 형식

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

* BLOB(Binary Long Object) : 글자가 아닌 이미지, 동영상 등의 데이터

 

- 실수형

소수점이 있는 숫자 저장

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

 

- 날짜형

날짜 및 시간 저장

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

 

[변수의 사용]

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

#예시
USE market_db;
SET @myVar1 = 5;
SET @myVar2 = 4.25;

SELECT @myVar1;
SELECT @myVar1 + @myVar2;

###############################################

SET @txt = '가수이름 => ';
SET @height = 166;

SELECT @txt, mem_name
FROM member
WHERE height > @height;

예시 문제를 실행할 경우 출력은 다음과 같다

5
9.25000000000000000000

가수이름 => 소녀시대
가수이름 => 잇지
가수이름 => 트와이스

즉, SET 함수를 통해 변수 선언 후 정수, 실수를 대입

SELECT 함수로 선언된 변수를 연산 후 출력! 조건문에서도 사용 가능

 

BUT! LIMIT와 같은 특정 함수에서는 변수를 사용할 수 없기 때문에 오류 발생 가능함

PREPARE와 EXECUTE를 통해 해결가능

SET @count = 3;

PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @count;

PREPARE는 실행하지 않고 준비만 해놓은 상태로 '?'를 통해 현재는 모르지만 나중에 채워짐 으로 남겨놓음

EXECUTE로 mySQL에 저장된 SELECT 문을 실행할 때 USING을 통해 변수 값을 대입하는 것

 

[데이터 형 변환]

명시적 형 변환 : 직접 함수를 사용해서 변환

# 함수를 활용한 명시적 형변환
CAST( 값 AS 데이터_형식 [(길이)] )
CONVERT( 값, 데이터_형식 [(길이)] )

## 예시1 : 날짜형으로 형변환 하기
SELECT CAST('2022$12$12' AS DATE);
## 예시2 : 가격과 수향을 곱한 실제 구매액 표기하기
SELECT num, CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR), '=' ) AS '가격X수량', price*amount AS '구매액'
FROM buy;

암시적 형 변환 : 자연스럽게 형 변환

SELECT '100' + '200';
# 300

SELECT CONCATE('100', '200');
#100200

 

Chapter04-2. 두 테이블을 묶는 조인

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

[내부 조인]

테이블 간의 관계 이해

두 테이블의 조인을 위해서는 일대다(one to many) 관계로 연결!

 

기본키(Primary Key) : 후보키 중 하나를 선정하여 대표로 삼는 키

• 릴레이션 내 투플을 식별할 수 있는 고유한 값
• NULL 값은 허용하지 않음.
• 키 값의 변동이 일어나지 않음
• 최대한 적은 수의 속성을 가진 것

외래키(Foreingn Key) :  다른 릴레이션의 기본키를 참조하는 속성. 참조되는 양쪽 릴레이션의 도메인이 같아야 함

• 관계 데이터 모델의 릴레이션 간의 관계를 표현
• 다른 릴레이션의 기본키를 참조하는 속성
• 참조하고(외래키) 참조되는(기본키) 양쪽 릴레이션의 도메인은 서로 같아야 함.
• NULL 값과 중복 값 등이 허용!
• 자기 자신의 기본키를 참조하는 외래키도 가능

내부 조인(inner join)

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

SELECT B.mem_id, M.mem_name, B.prod_name, M.addr
FROM buy B
	INNER JOIN member M
    ON B.mem_id = M.mem_id
   ORDER BY M.mem_id;

- DISTINCT 중복 제거 함수

 

[외부 조인]

한쪽 테이블에만 있어도 결과 추출 가능한 외부 조인

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

LEFT OUTER JOIN : 왼쪽 테이블의 내용은 모두 출력

RIGHT OUTER JOIN : 오른쪽 테이블의 내용은 모두 출력

FULL OUTER JOINT :  왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것! 한쪽에 들어있는 내용이면 출력

 

[기타 조인]

- 상호조인(Cross Join) : 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능

                                        → 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 수

• ON 구문을 사용할 수 없음
• 결과의 내용은 의미가 없음! 랜덤으로 조인하기 때문
• 상호 조인의 주 용도는 테스트하기 위해! 대용량 데이터를 생성할 때

- 자체 조인(self Join) : 자신과 조인

SELECT A.emp "직원", B.emp "직속상관", B.phone "직속상관연락처"
FROM emp_table A
	INNER JOIN emp_table B
    ON A.manager = B.emp
   ORDER BY A.emp = '경리부장';