혼공스 3주 차 (*￣3￣)╭

레쭈고 혼공스

혼공스 3주 차 (*￣3￣)╭

정땅미 2024. 7. 19. 03:39

오늘부터는 부지런히 월요일부터 차근차근. . . 하나 씩 공부를 해 보려고 해요!

사실 제가 저자님의 강의도 보고 싶었는데 (. ❛ ᴗ ❛.) 지금까지 시간에 쫓겨 블로그를 제출하느라

오늘부터는 밀린 강의 영상이랑 앞으로의 강의 영상을 쭉 - 보고 정말 저를 위한 공부를 하려고 합니다. ( •̀ ω •́ )✧

레츠 고 ~

MySQL의 데이터 형식

테이블을 만들 때는 데이터 형식을 설정해야 한다.

데이터 형식에는 크게 숫자형, 문자형, 날짜형이 있다.

이렇게 다양한 데이터 형식이 존재하는 이유는 실제로 저장될 데이터의 형태가 다양하기 때문이다.

데이터 형식

정수형

정수형은 소수점이 없는 숫자, 즉 인원 수, 가격, 수량 등에 많이 사용한다.

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

만약 값이 넘어갈 경우 Out of range는 입력값의 범위를 벗어났다는 의미이다.

값의 범위가 0부터 시작되는 UNSIGNED 예약어를 사용할 수 있다. \^o^/

그냥 TINYINT는 -128 ~ 127을 표현하지만, TINYINT UNSIGNED를 사용하면 0 ~ 255를 사용할 수 있다.

문자형

문자형은 글자를 저장하기 위해 사용하며, 입력할 최대 글자의 개수를 지정해야 한다.

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

CHAR는 문자를 의미하는 Character의 약자이며 자릿수가 고정되어 있다.

CHAR은 3글자만 저장해도 10자리를 모두 확보하며 7자리를 낭비하게 된다.

하지만 VARCHAR는 가변길이 문자형으로, 3글자만 저장할 경우 3자리만 사용한다.

VARCHAR가 CHAR보다 공간을 효율적으로 운영할 수 있지만,

MySQL 내부적으로 성능면에서는 CHAR로 설정하는 게 조금 더 좋다.

숫자로서 의미를 가지려면 더하기/빼기 등의 연산에 의미가 있어야 하며, 크다/작다 또는 순서에 의미가 있어야 한다.

대량의 데이터 형식

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

TEXT로 지정하면 최대 65535자까지, LONGTEXT로 지정하면 최대 약 42억자까지 저장된다.

또 BLOB는 Binary Long Object의 약자로 글자가 아닌 이미지, 동영상 등의 데이터라고 생각하면 된다.

이런 것을 이진 데이터라고 부른다.

LONGTEXT 및 LONGBLOB로 설정하면 각 데이터는 최대 4GB까지 입력할 수 있다.

실수형

실수형은 소수점이 있는 숫자를 저장할 때 사용한다.

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

FLOAT와 DOUBLE은 거의 비슷하다.

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

날짜형

날짜형은 날짜 및 시간을 저장할 때 사용한다.

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

DATE는 날짜만, TIME은 시간만 저장한다.

날짜와 시간을 둘 다 저장하고 싶으면 DATETIME을 사용하면 된다.

날짜 또는 시간을 입력할 때는 문자와 마찬가지로 작은따옴표로 묶어줘야 한다. ^_^

변수의 사용

변수 사용의 기본 형식이다.

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

변수는 MySQL 워크벤치를 재시작할 때까지는 유지되지만, 종료하면 없어진다.

SELECT 문에서 행의 개수를 제한하는 LIMIT에는 변수를 사용할 수 없기 때문에 문법상 오류가 발생한다.

이를 해결하는 것이 PREPARE와 EXECUTE이다.

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

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

1. @count 변수에 3을 대입한다.

2. PREPARE는 'SELECT ~~ LIMIT ?' 문을 실행하지 않고 mySQL이라는 이름으로 준비만 해 둔다.

LIMIT 다음에 오는 물음표는 '현재는 모르지만 나중에 채워짐'으로 이해하면 된다.

3. EXECUTE로 mySQL에 저장된 SELECT 문을 실행할 때, USING으로 물음표에 @count 변수의 값을 대입하는 것이다.

데이터 형 변환

문자형을 정수형으로 바꾸거나, 정수형을 문자형으로 바꾸는 것을 데이터의 형 변환이라고 한다.

형 변환에는 직접 함수를 사용해서 변환하는 명시적인 변환과

별도의 지시 없이 자연스럽게 변화하는 암시적인 변환이 있다.

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

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

이 두 개는 형식만 다를 뿐 동일한 기능을 한다.

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

가격은 실수보다 정수로 표현하는 것이 좋기 때문에 CAST()나 CONVERT() 함수를 사용해서 정수로 나타낼 수 있다.

이 두 개의 함수 안에 올 수 있는 데이터 형식은 CHAR, SIGHED, UNSIGNED, DATE, TIME, DATETIME 등이다.

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

SELECT num, CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR), '=')
	'가격X수량', price*amount '구매액'
FROM buy ;

CAST() 함수를 통해 문자로 바꿨으며, CONCAT() 함수는 문자를 이어주는 역할을 한다.

암시적인 변환

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

숫자와 문자를 연산할 때, CONCAT()을 사용하면 숫자가 문자로 변하고, 더하기만 사용하면

문자가 숫자로 변한 후에 연산된다.

두 테이블을 묶는 조인

조인이란 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말한다.

내부 조인

일대다 관계의 이해

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

데이터베이스의 테이블은 하나로 구성되는 것보다는 여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적이다.

이 분리된 테이블은 서로 관계를 맺고 있다.

두 테이블의 조인을 위해서는 기본 키-외래 키 관계로 맺어져야 한다.

내부 조인의 기본

일반적으로 조인이라고 부르는 것은 내부 조인이다.

내부 조인의 기본 형식!은 이것이다. o(*￣▽￣*)ブ

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

SELECT *
    FROM buy
        INNER JOIN member
        ON buy.mem_id = member.mem_id
WHERE buy.mem_id = 'GRL';

만약 위의 코드에서 WHERE buy.mem_id = 'GRL'; 이 코드가 없다면!

모든 행이 회원 테이블과 결합해서 나오게 된다~ 조심하자아 -

내부 조인의 간결한 표현

SELECT buy.mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) '연락처'
	FROM buy
    INNER JOIN member
    ON buy.mem_id = member.mem_id;

테이블_이름. 열_이름과 별칭을 사용하면 너무너무 좋다! ==> 프로그램에서도 적극 권장

SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(phone1, phone2) '연락처'
	FROM buy B
    INNER JOIN member M
    ON B.mem_id = M.mem_id;

테이블(buy, member)에 별칭(B, M)을 붙여서 사용한다. ~_~

내부 조인의 활용

내부 조인은 두 테이블에 모두 있는 내용만 출력된다.

만약 양쪽 중에 한 곳이라도 내용이 있을 때 조인하려면 외부 조인을 사용해야 한다. ( •̀ ω •́ )✧

외부 조인

외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다.

외부 조인의 기본

외부 조인은 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과를 추출할 수 있다.

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

외부 조인의 기본 형식이다~

내부 조인보다는 조금 복잡해 보이지만, 사용 방법은 거의 비슷하다!

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

LEFT OUTER JOIN 문의 의미는 '왼쪽 테이블의 내용은 모두 출력되어야 한다'라는 의미로 해석하면 된다.

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

RIGHT OUTER JOIN으로 동일한 결과를 출력하려면 왼쪽과 오른쪽 테이블의 위치를 바꿔주면 된다.

외부 조인의 활용

SELECT DISTINCT M.mem_id, M.mem_name, B.prod_name, M.addr
	FROM member M
    LEFT OUTER JOIN buy B
    ON M.mem_id = B.mem_id
WHERE B.prod_name IS NULL

이렇게 코드를 작성해 주면 한 번도 구매한 적이 없는 회원의 목록을 추출할 수 있다~

FULL OUTER JOIN은 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것이라고 생각하면 된다.

왼쪽이든 오른쪽이든 한쪽에 들어 있는 내용이면 출력한다.

기타 조인

상호 조인

상호 조인은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능을 말한다.

그래서 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수가 된다.

SELECT *
	FROM buy
	CROSS JOIN member;

상호 조인은 ON 구문을 사용할 수 없으며, 결과의 내용은 의미가 없다. 왜냐하면 랜덤으로 조인하기 때문이다.

상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때이다. (*^_^*)

진짜로 대용량의 테이블을 만들고 싶으면 CREATE TABLE ~ SELECT문을 사용한다.

자체 조인

지금까지 배운 세 개의 조인은 모두 2개의 테이블을 조인했다.

자체 조인은 자신이 자신과 조인한다는 의미이다.

그런데 자체 조인은 한 개의 테이블을 사용한다.

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

실무에서 자체 조인을 많이 사용하지는 않지만, 대표적인 사례로 회사의 조직 관계를 예로 들 수 있다.

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

SQL 프로그래밍

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

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

스토어드 프로시저는 DELIMITER $$ ~ END $$ 안에 작성하고 CALL로 호출한다.

IF 문

IF 문의 기본 형식

IF 문은 조건식이 참이라면 'SQL 문장들'을 실행하고 그렇지 않으면 그냥 넘어간다.

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

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

DROP PROCEDURE IF EXISTS ifProc1;
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
	IF 100 = 100 THEN
		SELECT '100은 100과 같습니다.';
	END IF;
END $$
DELIMITER ;
CALL ifProc1();

세미콜론으로는 SQL의 끝인지, 스토어드 프로시저의 끝인지 구별할 수 없어서 $$를 사용한다.

IF ~ ELSE 문

IF ~ ELSE 문은 조건에 따라 다른 부분을 수행한다.

DROP PROCEDURE IF EXISTS ifProc2;
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();

DECLARE 예약어를 사용해서 myNum 변수를 선언했다.

SET 예약어로 myNum 변수에 200을 대입했다.

IF 문의 활용

DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
	DECLARE debutDate DATE;
    DECLARE curDate DATE;
    DECLARE days INT;
    SELECT debut_date INTO debutDate
		FROM market_db.member
        WHERE mem_id = 'APN';
        
    SET curDate = CURRENT_DATE();
    SET days = DATEDIFF(curDate, debutDate);
    
	IF (days/365) >= 5 THEN
		SELECT CONCAT('데뷔한 지 ', days, '일이나 지났습니다. 핑순이들 축하합니다!');
	ELSE 
		SELECT '데뷔한 지 ' +  days + '일밖에 안 되었네요. 핑순이들 화이팅~';
	END IF;
END $$
DELIMITER ;
CALL ifProc3();

SELECT 문에 INTO 변수가 붙었는데 이럴 경우 결과를 변수에 저장한다.

CURRENT_DATE() 함수로 현재 날짜를 curDate에 저장했다.

DATEDIFF() 함수로 데뷔 일자부터 현재 날짜까지 일수를 days에 저장했다.

CASE 문

CASE 문의 기본 형식

CASE 문은 2가지 이상의 여러 가지 경우일 때 처리가 가능하므로 '다중 분기'라고 부른다.

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

CASE와 END CASE 사이에는 여러 조건을 넣을 수 있다.

WHEN 다음에 조건이 나오는데 조건이 여러 개라면 WHEN을 여러 번 반복한다.

CASE 문의 활용

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 '유령고객'
	END "회원등급"
	FROM buy B
		RIGHT OUTER JOIN member M
        ON B.mem_id = M.mem_id
    GROUP BY M.mem_id
    ORDER BY SUM(price*amount) DESC;

WHILE 문

WHILE 문의 기본 형식

WHILE 문은 조건식이 참인 동안에 'SQL문장들'을 계속 반복한다.

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

WHILE 문의 응용

ITERATE [레이블] : 지정한 레이블로 가서 계속 진행한다. (=CONTINUE)

LEAVE [레이블] : 지정한 레이블을 빠져나간다. WHILE 문 종료라는 의미이다. (=BREAK)

동적 SQL

PREPARE와 EXECUTE

PREPARE는 SQL 문을 실행하지 않고 미리 준비만 해 놓고, EXECUTE는 준비한 SQL 문을 실행한다.

그리고 실행 후에는 DEALLOCATE PREPARE로 문장을 해제해 주는 것이 바람직하다.

USE market_db;
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXECUTE myQuery;
deallocate prepare myQuery;

이렇게 미리 SQL을 준비한 후에 나중에 실행하는 것을 동적 SQL이라고 한다.

동적 SQL의 활용

PREPARE 문에서는 ?로 향후에 입력될 값을 비워 놓고, EXECUTE에서 USING으로 ?에 값을 전달할 수 있다.

그러면 실시간으로 필요한 값들을 전달해서 동적으로 실행된다.

오늘 너무 열심히 달려서 힘들어유...

그리고 내용이 너무 어려웠다ㅠㅠ (´。＿。｀)

기본 형식이 너무 헷갈리구. . 종류가 너무 많아서 주의 깊게 보고 상황에 따라 잘 사용해야겠당.

이제 시간이 많으니~ SQL 강의 보러 뿅 (￣o￣) . z Z

'레쭈고 혼공스' 카테고리의 다른 글

혼공스 6주 차 (˘･_･˘) (0)	2024.08.17
혼공스 5주 차 (oﾟvﾟ)ノ (7)	2024.08.03
혼공스 4주 차 UwU (7)	2024.07.24
혼공스 2주 차 (*￣3￣)╭ (0)	2024.07.14
혼공스 1주 차 ^^ (0)	2024.07.06

현재글혼공스 3주 차 (*￣3￣)╭

얼렁뚱땅미

JavaScript, godot, 프로그래머스, Kotlin, JS, MySQL, 혼공단, 코딩, SQL, 회고록, gdscript, 안드로이드스튜디오, 코테, 코틀린, 개발, 코딩테스트, 게임개발, 혼공스, 고도엔진, 1일1코테,

Today :
Yesterday :

얼렁뚱땅미