혼공학습단 11기 - 혼공S (3주차)

 

혼자 공부하는 SQL

#	진도	기본 미션	선택 미션
1주차 (1/2 ~ 1/7)	Chapter 01 ~ 02	p. 80의 shop_db의 회원 테이블(member)에서 아이유 회원에 대한 정보만 추출한 후 결과 화면 인증하기	데이터베이스 개체 3가지 설명하기
2주차 (1/8 ~ 1/14)	Chapter 03	p. 138의 확인 문제 2번 풀고 인증하기	데이터 입력, 삭제하는 기본 형식 작성하기
3주차 (1/15 ~ 1/21)	Chapter 04	p. 195의 확인 문제 4번 풀고 인증하기	p. 183 [좀 더 알아보기] 손코딩 실행하고 결과화면 인증하기
4주차 (1/22 ~ 1/28)	Chapter 05	p. 226의 market_db의 회원 테이블(member) 생성하고, p. 229 데이터 입력한 후 인증하기	p. 271 확인 문제 4번 풀고 인증하기
5주차 (1/29 ~ 2/4)	Chapter 06	p. 310 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과 화면 캡처하기	인덱스 생성, 제거하는 기본 형식 작성하기
6주차 (2/5 ~ 2/12)	Chapter 07 ~ 08	p. 363 market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거 작성하고 인증하기	p. 402 GUI 응용 프로그램 만들고 인증하기

 

 

이번 주 진도

#	진도	기본 미션	선택 미션
3주차 (1/15 ~ 1/21)	Chapter 04	p. 195의 확인 문제 4번 풀고 인증하기	p. 183 [좀 더 알아보기] 손코딩 실행하고 결과화면 인증하기

 

 

기본 미션

p. 195의 확인 문제 4번 풀고 인증하기

4. 다음 SQL은 회원으로 가입만 하고, 한 번도 구매한 적이 없는 회원의 목록입니다. 빈칸에 들어갈 가장 적합한 것을 고르세요.

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

A. 4

해설

한 번도 구매한 적이 없는 회원들만 목록으로 만들 것이기 때문에 조건식을 넣을 때 사용하는 WHERE 절을 골라야 한다. (HAVING 절도 조건식이나 GROUP BY의 경우에만 사용함)

 

선택 미션

p. 183 [좀 더 알아보기] 손코딩 실행하고 결과화면 인증하기

 

3장에서 배운 DISTINCT 문을 JOIN에서도 활용할 수 있다.

 

 

정리

 

04 - 1 MySQL의 데이터 형식

1. 데이터 형식: 정수형

  - 소수점이 없는 숫자

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

 

  - UNSIGNED

    음수가 필요하지 않을 경우 (ex 키, 나이 등) 데이터 형식 뒤에 UNSIGNED를 붙이면 범위가 0부터 지정된다.

    예를 들어 TINYINT UNSIGNED를 사용한다면, 범위는 0 ~ 255가 되는 것이다.

 

 

2. 데이터 형식: 문자형

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

  - CHAR는 고정길이 문자형, VARCHAR는 가변길이 문자형이다.

  - CHAR(10)는 3글자만 저장해도 10자리를 확보하여 7자리를 낭비하게 된다. 그에 비해 VARCHAR(10)는 3글자만 사용할 경우, 3자리만 사용한다. 대신 VARCHAR이 조금 더 속도가 느리다.

  - 글자의 개수가 고정된 문자일 경우 CHAR, 변동될 수 있는 문자일 경우 VARCHAR을 사용하자.

 

  - 대량의 데이터 형식

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

    VATCHAR의 16384로도 부족할 때 사용할 수 있는 더 큰 데이터 형식도 있다.

    BLOB라는 용어가 등장했는데, Binary Long Object의 약자로 Binary 데이터(보통 이미지, 동영상 등)에 해당한다.

 

 

3. 데이터 형식: 실수형

  - 소수점이 있는 숫자

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

 

 

4. 데이터 형식: 날짜형

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

 

 

5. 변수의 사용

  - SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다.

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

 

  - 예시

    변수에 5가 잘 저장되어 있다.

    변수에 데이터 타입을 지정해주지 않아도 되는 점이 신기하다!

 

  - 활용 예시

    txt 변수로 테이블을 보기 좋게 만들고, height 변수에 저장된 값과 비교하는 조건을 작성하였다.

 

  - 변수는 일시적인 것으로 다른 쿼리를 생성하거나 워크벤치를 다시 시작하면 사라진다.

  

  - LIMIT에 변수를 사용하고 싶을 때

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

 

  위와 같이 입력하면 변수를 사용할 수 있을 것 같지만, 문법 오류이다.

 

    여기서는 PREPARE와 EXECUTE를 사용해야 한다.

    PREPARE에서는 실행하지 않고 SQL 문만 준비해 놓는다. EXECUTE에서 실행한다.

    ? 가 들어간 위치에 @count가 대입된다.

 

 

6. 데이터 형 변환

  - 데이터를 다른 데이터 타입으로 바꾸는 것을 데이터의 형 변환(type conversion)이라고 부른다.

  - 형 변환에는 직접 함수를 사용해서 변환하는 명시적인 변환(explicit conversion)과 별도의 지시 없이 자연스럽게 변환되는 암시적인 변환(implicit conversion)이 있다.

 

  - 명시적인 변환

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

 

    형 변환 함수는 CAST(), CONVERT()가 있다. 두 함수는 형식만 다를 뿐 동일한 기능을 한다.

 

    실수형을 정수형으로 바꾸었다.

    SIGNED는 부호가 있는 정수, UNSIGNED는 부호가 없는 정수를 의미한다.

 

- 암시적인 변환

 

    함수를 따로 사용하지 않았지만 문자 '100'과 문자 '200'을 정수로 간주하여 더하였다.

   

    문자를 더하는 결과를 얻고 싶다면, CONCAT() 함수를 사용한다.

    CONCAT() 함수는 문자를 이어주는 역할을 한다.

 

 

04 - 2 두 테이블을 묶는 조인

1. 조인(join)

  - 조인이란 두 개의 테이블을 서로 묶어서 하나의 결과를 만드는 것.

  - 회원 테이블과 구매 테이블을 조인하여 배송 정보 테이블을 만든다.

 

2. 내부 조인

  - 가장 많이 사용되는 조인이다. 기본적으로 조인이라고 부르면 내부 조인을 의미한다.

  - 일대다(one to many)

    두 테이블의 조인을 위해서는 테이블이 일대다 관계로 연결되어야 한다.

    일대다 관계란 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러 개의 값이 존재할 수 있는 관계를 말한다.

 

예시

회원 테이블

아이디	이름
TWC	트와이스
BLK	블랙핑크
WMN	여자친구

 

구매 테이블

순번	아이디	물품명
1	BLK	지갑
2	BLK	맥북프로
3	TWC	아이폰
4	WMN	아이폰

 

    위와 같은 두 개의 테이블이 있다고 할 때, 블랙핑크의 아이디는 회원 테이블에서 유일하다. 회원 테이블은 아이디를 기본 키(PK)로 지정하였다. 그러나 구매 테이블에서는 여러 개가 있을 수 있다. 구매 테이블은 아이디를 외래 키(Foreign Key, FK)로 설정했다.

 

  - 형식

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

   

    WHERE 절은 생략 가능하다.

 

    

    buy 테이블의 mem_id와 member 테이블의 mem_id가 같으면 조인하였다. 같은 아이디일 경우 연결한 것으로, 두 테이블이 성공적으로 합쳐진 모습을 볼 수 있다.

 

 

    이번에는 전체 열이 아니라, 필요한 열만 출력하였다. 여기서 놓치기 쉬운 부분은, mem_id의 경우 buy와 member 테이블 양 쪽 모두 있기 때문에 어느 한쪽만 출력할 수 있도록 앞에 테이블 명을 적어야 한다는 것이다.

 

   

테이블에 별칭을 줄 수도 있는데, 이름을 이렇게 바꾸어 놓고 원래 이름(buy.mem_id)을 사용하면 오류가 난다.

 

 

3. 외부 조인

  - 내부 조인은 두 테이블 모두 데이터가 있어야 결과가 나오지만, 외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다.

 

  - 형식

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

   

    LEFT OUTER JOIN은 왼쪽 테이블의 내용은 모두 출력시킨다.

    RIGHT OUTER JOIN은 오른쪽 테이블의 내용은 모두 출력시킨다.

    FULL OUTER JOIN 은 어느 한쪽에라도 들어있다면 모두 출력시킨다.

 

예시

 

 

4. 기타 조인

  - 상호 조인(cross join)

    한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능을 말한다. 결과적으로 두 테이블을 곱하는 것. 데이터적 가치는 없지만 대용량 데이터를 만들고 싶을 때 사용한다.

 

 

 

이렇게 조인하여 새 테이블을 만들기도 한다.

 

  - 자체 조인(self join)

    자체 조인은 자기 자신과 조인한다는 의미로 1개의 테이블만 사용한다.

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

 

    이너 조인과 형식은 똑같으나, 별칭을 사용하여 다른 테이블을 조인하는 것처럼 사용한다.

 

 

04 - 3 SQL 프로그래밍

1. 스토어드 프로시저 형식

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
SQL_프로그래밍_코딩
END $$
DELIMITER ;

CALL 스토어드_프로시저_이름 -- 호출

 

 

2. IF 문

  - 조건식이 참일 경우 실행한다.

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

 

예시

   

    조건식이 100 = 100으로 참이기 때문에 SELECT '100은 100과 같습니다.'를 실행하였다.

 

- IF ~ ELSE 문

   

    SQL에도 if else 문이 존재한다.

    여기서는 변수를 사용했는데, DECLARE 예약어를 사용해서 INT형인 myNum 변수를 선언하였다. 스토어드 프로시저 안에서는 변수에 @를 붙이지 않고 사용한다.

 

 

4. CASE 문

  - 여러 조건 중 선택한다.

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

 

예시

 

 

5. WHILE 문

  - 조건식이 참인 동안 계속 반복한다.

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

 

예시

 

 

    i가 1일 때부터 시작해 100보다 작거나 같을 때까지 더하는 예제이다. 즉 1부터 100까지의 합이다.

 

  - ITERATE [레이블]: 지정한 레이블로 가서 계속 진행

  - LEAVE [레이블]: 지정한 레이블을 빠져나간가. (WHILE 문 종료)

    C++, C#에서의 continue, break와 같은 역할을 한다.

    여기서 레이블이란 WHILE문의 이름을 말하는 것으로 WHILE문의 시작 전에 이름을 지정해 줄 수 있다.

 

 

    아까 전 예제와 기본적으로 비슷하지만, 4의 배수일 경우 다음 루프로 이동하는 부분과 합이 1000보다 커질 경우 반복문을 종료하는 부분이 추가되었다.

    WHILE 문 앞에 myWhile:이라는 레이블을 지정해 주었다.

 

 

6. 동적 SQL

  - 기본적으로 SQL 문은 내용이 고정되어 있지만 상황에 따라 내용 변경이 필요한 경우 동적 SQL을 사용해 실시간으로 적용시킬 수 있다.

  - PREPARE: SQL 문을 미리 준비해 놓는다.

  - EXECUTE: 준비한 SQL 문을 실행한다.

  - DEALLOCATE PREPARE: 준비했던 문장을 해제한다.

 

  - 앞에서 LIMIT에 변수를 사용할 때 잠깐 사용해 봤다.

 

    현재 시간을 담은 데이터를 INSERT 하기 위해 사용하였다.

 

 

 

 

이전 글 보기

 

1주 차

https://mountain-noroo.tistory.com/94

혼공학습단 11기 - 혼공S (1주차)

 

2주 차

https://mountain-noroo.tistory.com/97

혼공학습단 11기 - 혼공S (2주차)