쌩로그

혼공 SQL - ch04(SQL 고호오급 문법) 본문

DB/RDB

혼공 SQL - ch04(SQL 고호오급 문법)

.쌩수. 2023. 8. 8. 01:11
반응형

목록

  1. 포스팅 개요
  2. 본론
        2-1. MySQL의 데이터 형식
        2-2. 두 테이블을 묶는 조인
        2-3. SQL프로그래밍
  3. 요약

1. 포스팅 개요

혼공 SQL 챕터 4를 학습하는 과정에서 정리하는 포스팅.

빨리 이거 흩고 REAL MYSQL 보고싶다..

2. 본론

2-1. MySQL의 데이터 형식

정수형

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

UNSIGNED

  • 해당 키워드를 사용하면 값의 범위를 0부터 시작하게 할 수 있다.
  • 만약 TINYINT UNSIGNED로 데이터형식을 지정했다면, -127 ~ 128로 설정되는 범위가 0 ~ 255가 된다.
  • 선언 방식은 데이터_형식 UNSIGNED로 선언하면 된다.

문자형

  • 글자를 저장하기 위해 사용하는 데이터 형식
데이터 형식 바이트 수
CHAR(개수) 1~255
VARCHAR(개수) 1~16383

CHAR

  • 문자를 의미하는 Character의 약자
  • 길이를 고정으로 가진다.

VARCHAR

  • 길이를 가변으로 가진다.

VARCHAR VS CHAR
VARCHAR가 CHAR보다 공간을 효율적으로 가질 수 있다. 하지만,
CHAR가 내부적으로 성능면에서는 VARCHAR보다 뛰어나다.

※ 참고로 데이터가 연산이나 크기에 의미가 없다면, 전화번호 같이 숫자형태를 가지더라도 문자형으로 지정하는 것이 좋다.

대량의 데이터 형식

큰 데이터를 저장할 때 사용가능

TEXT는 소설이나 영화 대본과 같은 대량의 글자를 저장할 때 필요한 데이터 형식이다.
반면에,

BLOB이 나오는데, BLOB은 Binary Long Object의 약자로 글자가 아닌 이미지, 동영상 등의 데이터를 저장할 때 사용하는 데이터 형식이다.
Binary라는 단어가 보이는 것처럼 이진수 데이터를 저장한다.

예를 들어 넷플릭스 데이터베이스가 있다고 가정할 때,
TEXT는 자막, BLOB은 동영상 데이터를 저장할 때 사용할 수 있다.

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 형식으로 사용

변수의 사용

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

선언 방식

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

참고로 이렇게 선언한 변수는 MySQL의 워크벤치를 종료하면 없어진다.

데이터 형 변환

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

직접 함수를 사용해서 변환하는 '명시적인 변환'이 있고,
자연스럽게 변환되는 '암시적인 변환'이 있다.

함수를 이용한 명시적인 변환
CAST()와 CONVERT()를 이용해서 데이터 형식을 변환할 수 있다.
CAST()와 CONVERT()는 형식만 다를 뿐, 기능은 똑같다.

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

예시

select avg(price) as '평균가격' from buy; -- 소수점으로 결과가 나오는데, 가격에 소수점이 없기때문에, 형변환을 아래와 같이 할 수 있다.

SELECT CAST(avg(price) as signed) '평균 가격' from buy;
SELECT CONVERT(avg(price), signed) '평균 가격' from buy;

나는 여기서 문법보단, 형변환이 가능하다는 내용만 일단 얻어가야겠다.

SELECT num, concat(CAST(price as char), 'X', CAST(amount as char), '=' ) '가격 X 수량', price*amount '구매 액' from buy;

위에서 price와 amount는 정수이다. 하지만, CAST()를 통해서 문자로 바꿨다.

다음과 같은 결과가 나온다.

암시적인 변환

  • CAST()와 CONVERT()를 사용하지 안고 자연스럽게 형을 변환하는 것.
SELECT '100' + '200'; -- 300
SELECT CONCAT('100','200'); -- 100200
SELECT 100 + '200'; -- 300

CONCAT()을 사용하면, 숫자가 문자로 변하고, 더하기만 사용하면 문자가 숫자로 변환하여 연산된다.

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

조인(Join)

두 개의 테이블을 서로 묶어서 하나의 결과를 만들어내는 것
두 테이블의 조인을 위해서는 기본 키-외래 키 관계로 맺어져야 하고, 이를 일대다 관계라고한다.

내부 조인

  • 보통 그냥 '조인'이라고 부르면 내부조인을 의미

기본 문법

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

※ INNER JOIN을 그냥 JOIN이라고만 써도 INNER JOIN으로 인식한다.
※ 테이블 뒤에 별칭을 줌으로써 코드를 보다 조금은 더욱 간결하게 할 수 있다.

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

외부 조인

  • 두 테이블에 모두 데이터가 있어야만 결과가 나오는 내부조인과는 다르게, 한쪽에만 데이터가 있어도 결과를 추출할 수 있는 JOIN
SELECT 열_목록
FROM 첫_번째_테이블(LEFT 테이블)
    (LEFT || RIGHT || FULL) OUTER JOIN 두_번째_테이블(RIGHT_테이블) -- LEFT OUTER JOIN을 LEFT JOIN이라고만 써도 됨.
    ON 조인될_조건
WHERE 검색_조건 ;

※ 주석 부분 잠깐만 정리하자면, INNER JOIN을 그냥 JOIN으로 사용할 수 있고, LEFT OUTER JOIN(외부 조인)은 OUTER JOIN으로 사용할 수 있다.

※TIP 책에서 알려주는 내용이지만..
LEFT OUTER JOIN의 의미는 왼쪽 테이블의 내용은 모두 출력되어야 한다로 생각하면 쉽다.
RIGHT OUTER JOIN은 그 반대!

LEFT OUTER JOIN과 RIGHT OUTER JOIN의 결과를 똑같이 하려면,
똑같은 외부 조인에 테이블의 순서만 바꿔주면 된다.

FULL OUTER JOIN

  • 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것
  • 왼쪽이든 오른쪽이든 한쪽에 들어있는 내용이면 그냥 출력
  • 자주 사용되진 않음.

기타 조인 - 상호 조인

  • CROSS JOIN이라고 한다.
  • 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능
    • 때문에 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 개수가 된다.
    • 하나의 테이블에 행이 3개, 또 다른 테이블의 행이 10개라면, 이 두 테이블을 상호조인했을 때 행 데이터 개수는 30개가 된다.

※상호조인 문법

SELECT *
    FROM 테이블1
    CROSS JOIN 테이블2 ;

책에서 잘 안 사용한다고 되이있는데, 예제 결과보니깐 진짜 잘 안 사용할 듯;;

※상호조인의 특징

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

기타 조인 - 자체 조인

다른 조인과는 다르게 하나의 테이블이 자기 자신과 조인하는 것을 의미한다.
그래서 자체 조인은 1개의 테이블만을 사용한다.

자체 조인의 기본 문법

SELECT 열_목록
FROM 테이블 별칭A
    INNER JOIN 테이블 별칭B
    ON 조인될_조건
(WHERE 검색_조건) ;

테이블은 하나지만, 서로 다른 별칭을 사용하여 실행하면 된다.

일대다 관계

주로 기본 키(PK)와 외래 키(FK) 관계로 맺어져있다.
그래서 일대다 관계를 'PK-FK'관계라고도 부른다.

※ CREATE TABLE ~ SELECT

SELECT의 결과가 테이블로 생성된다.

CREATE TABLE 테이블_이름
    SELECT ~ ;

2-3. SQL프로그래밍

스토어드 프로시저

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

기본 구조

DELIMETER $$ -- 스토어드 프로시저 시작 -- DELIMITER는 구분 문자이다.  
-- SQL문의 끝인지, 스토어드 프로시저의 끝인지를 구별하기 위해 이와 같이 한다.
-- 달러($$)말고 다른 몬자도 가능한다.
CREATE PROCEDURE 스토어드_프로시저_이름() -- 괄호 ㅇㅇ
BEGIN
    ...
    -- 이 부분에 SQL 프로그래밍 코딩
    ...
END $$  -- 스토어드 프로시저 종료
DELIMITER ; -- DELIMITER 종료 문자를 세미콜론(;)으로 변경.
CALL 스토어드_프로시저_이름() ;  -- 스토어드_프로시저 호출

IF문

조건문 프로그래밍에 사용

기본 문법

IF 조건식 THEN
     SQL_문장 -- 문장이 하나라면 괜찮지만, 두 문장 이상이라면, BEGIN ~ END로 묶어줘야 한다.
END IF;

IF ~ ELSE문

조건에 따라 다른 부분을 수행한다.

IF 조건식 THEN
     SQL_문장; 
ELSE 
     SQL_문장;
END IF;
DECLARE
  • 스토어드 프로시저에서 사용할 변수 선언에 사용한다.
  • SET @변수 구문과는 별도이다.
DECLARE 변수_이름 데이터_형식;
SELECT ~ INTO 변수

SELECT 의 결과 값을 변수에 대입한다.

DECLARE debutDate DATE; -- 스토어드 프로시저 내에서 debutDate라는 변수 선언

SELECT debut_date INTO debutDate  
    FROM market_db.member
    where mem_id = 'APN';

-- INTO debutDate를 제외한 SELECT문의 결과가 debutDate에 들어감.
MySQL에서 날짜와 관련된 함수
CURRENT_DATE() : 오늘 날짜
CURRENT_TIMESTAMP() : 오늘 날짜 및 시간을 함께 알려줌.
DATEDIFF(날짜1, 날짜2) : 날짜2부터 날짜1까지 일수로 몇일인지 알려준다.

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 문장들
END WHILE;

ITERATE (레이블)
지정한 레이블로 가서 계속 진행한다.
자바의 제어문의 continue와 비슷

LEAVE (레이블)
지정한 레이블을 빠져나간다. WHILE문이 종료된다.
자바의 제어문의 break와 판박이

예시 : 1~100까지 4의 배수를 제외하며 값을 더해가다가 1000이 넘으면 멈추는 예제

DROP PROCEDURE IF EXISTS whileProc2;

DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
    DECLARE i INT;
    DECLARE sum INT;
    SET i = 1;
    SET sum = 0;

    myWhile: -- 레이블 지정
    WHILE(i <= 100) DO
        IF(i%4 = 0) THEN        -- 4의 배수는 skip
            SET i = i + 1;
            ITERATE myWhile;
        END IF;
        SET sum = sum + i;
        IF(sum > 1000) THEN
            LEAVE myWhile;
        END IF;
        SET i = i + 1;
    END WHILE;
        SELECT '1부터 100까지 4의 배수를 제외한 합, 1000 넘으면 종료 ==> ', sum;
END $$
DELIMITER ;

call whileProc2();

동적 SQL

PREPARE

  • PREPARE는 SQL을 실행하지 않고, SQL문을 준비만 해놓는다.

EXECUTE

  • PREPARE에서 준비한 SQL을 실행한다.
  • EXECUTE로 실행 후에는 DEALLOCATE PREPARE로 문장을 해제해주는 것이 바람직하다.
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXECUTE myQuery;

DEALLOCATE PREPARE myQuery; -- 실행 후, 해당 문장을 실행 후에 EXECUTE문을 실행하면, 실행 오류가 발생한다.
  • USING 이후 변수가 오는데, PREPARE에서 물음표(?)로 선언해놓은 SQL문에 변수를 대입한다. 예시는 아래에..
set @count = 3;
-- select mem_name, height FROM member ORDER BY height LIMIT @count; -- 이 문장에서 오류가 발생한다. 
-- limit에서 변수를 사용했기 때문인지 오류코드는 신택스 에러(문법 오류)가 발생한다.

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

이렇게 SQL을 실행할 수 있다.

3. 요약

MySQL의 데이터 형식,
JOIN문에 대해서 알아보았고,
SQL 프로그래밍 문법도 간단히 알아보았다.

SQL 프로그래밍 부분은 살짝 지루했다.

후딱 보고 REAL MYSQL로 넘어가자..!!!

728x90
저작자표시

'DB > RDB' 카테고리의 다른 글

혼공 SQL - ch06(인덱스)  (0) 2023.08.09
혼공 SQL - ch05(테이블과 뷰)  (0) 2023.08.08
혼공 SQL - ch03(SQL 기본 문법)  (0) 2023.08.06
혼공 SQL - ch02(실전 SQL 미리 맛보기)  (0) 2023.08.04
혼공 SQL - ch01(데이터베이스와 SQL)  (0) 2023.07.28
'DB/RDB' Related Articles more
Comments