[MySQL] 07. SQL 고급

MySQL의 데이터 형식

MySQL에서 지원하는 데이터 형식 종류

• 숫자 데이터 형식
  • SMALLINT
  • INT
  • BIGINT
  • FLOAT
  • DECIMAL
    • FLOAT, DOUBLE에 비해 정확한 수치를 저장한다.
    • 소수점이 들어간 실수는 되도록 DECIMAL을 사용하는 것이 바람직하다.
• 문자 데이터 형식
  • CHAR
    • 고정길이 문자형
    • 공간은 낭비하지만 INSERT/UPDATE시에 성능은 VARCHAR보다 좋다.
  • VARCHAR
    • 가변길이 문자형
  • TEXT
    • 대용량의 글자를 저장하기 위한 형식
    • 크기에 따라 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT 사용 가능
    • LONG 사용 시 4GB 크기 저장 가능
  • BLOB
    • 사진 파일, 동영상 파일, 문서 파일 등의 대용량의 이진 데이터를 저장하는데 사용될 수 있다.
      • TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB 등의 형식으로 사용할 수 있다
• 날짜와 시간 데이터 형식
  • DATE
    • 날짜만
  • TIME
    • 시간만
  • DATETIME
    • 날짜, 시간 한번에
• 기타 데이터 형식
  • GEMOETRY
    • 공간 데이터 형식 (ch.14에 설명)
  • JSON
    • JSON(JavaScript Object Notation)문서 저장
    • key와 value로 쌍을 이루며 구성되어있다
    • MySQL에선 다양한 JSON 관련 내장함수들이 존재한다.

변수의 사용

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

SELECT @변수이름 ;

이런 식으로 변수 사용 가능

 

데이터 형식과 형 변환

• 데이터 형식 변환 함수(명시적 변환)
  • CAST()
  • CONVERT()
• 암시적인 형 변환
  • 함수를 사용하지 않고도 형이 변환된다.
  • ex) SELECT '100' + '100'. --> 문자와 문자를 더했으나 정수로 형변환되어 연산된다.

MySQL 내장 함수

• 제어 흐름 함수
• 문자열 함수
• 수학함수
• 날짜 및 시간 함수
• 시스템 정보 함수
• 그 외의 함수

https://jaehoney.tistory.com/53 에서 자세한 내용 확인 가능

조인

두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것.

INNER JOIN(내부 조인)

• 가장 많이 사용하는 조인
• 형식

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

• inner join 과정
  • where 검색조건에 맞는 행을 검색
  • 조인 될 조건에 맞는 행을 찾아 두 행을 결합
• 여러 테이블을 조인할 때 별칭을 쓰는 것이 권장된다.
• 양쪽 테이블에 모두 내용이 있을 때만 조인이 된다

 

OUTER JOIN(외부 조인)

• 양쪽 테이블 모두에 존재하지 않더라도 조인을 진행한다.
• 형식

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

• LEFT OUTER JOIN이라면 왼쪽 테이블의 것은 모두 출력한다는 의미이다 (오른쪽 테이블에 일치하는 값이 없다면 NULL)
• RIGHT OUTER JOIN은 오른쪽이 모두 출력, FULL이면 양쪽이 모두 출력

 

CROSS JOIN(상호 조인)

• 한쪽 테이블의 모든 행들과 다른쪽 테이블의 모든 행을 조인시키는 기능
• 결과 개수는 두 테이블 개수를 곱한 수
• cross join 과정
  • A 테이블의 첫 번째 행과 B 테이블의 모든 행이 조인
  • A 테이블의 두 번쨰 행과 B테이블의 모든 행이 조인.. 이를 A테이블이 끝날 때 까지 반복.
• ON 구문을 사용할 수 없다.
• 테스트로 사용할 많은 용량의 데이터를 생성할 때 주로 사용

 

SELF JOIN(자체 조인)

• 하나의 테이블에 같은 데이터가 존재하되 의미는 다르게 존재하는 경우 사용
• ex) 직원 이름 / 상관 이름 / 구내 번호
  A의 상관이 B라 할 때 B는 직원이름에도, 상관 이름에도 들어있다.
  A의 상관의 구내 번호를 알고싶은 경우 self join을 사용한다.

 

UNION / UNION ALL / NOT IN / IN

• UNION
  • 두 쿼리의 결과를 합친다.
  • 형식

SELECT 문장1
    UNION [ALL]
SELECT 문장2

• • 중복된 열은 제거되고 데이터가 정렬되어 나온다
  • SELECT 문장1 , SELECT 문장2 결과 열의 개수가 같아야 하고 데이터 형식도 같아야 한다 (아니라면 호환 되어야 한다)
• UNION ALL
  • UNION과 같고, 다른점은 중복된 열까지 모두 출력된다.
• NOT IN
  • 첫 번째 쿼리 결과 중 두 번째 쿼리에 해당하는 것을 제외하기 위한 구문
• IN
  • 첫 번째 쿼리 결과 중 두 번째 쿼리에 해당하는 것만 조회하기 위해

SQL 프로그래밍

IF ... ELSE

• BEGIN...END와 함께 묶어줘야만 한다
• 형식

DELIMITER $$
CREATE PROCEDURE tmp()
BEGIN
    DECLARE var INT; --변수선언
    SET var = 1; -- 대입
    
    IF <부울 표현식> THEN
        SQL 문장들
    ELSE
        SQL 문장들
    END IF;
END $$
DELIMITER;

CASE

DELIMITER $$
CREATE PROCEDURE tmp()
BEGIN
    DECLARE var INT; --변수선언
    DECLARE credit CHAR(1);
    SET var = 1; -- 대입
    
    CASE
        WHEN var >= 1 THEN
            SET credit = 'A';
        WHEN var >= 0 THEN
            SET credit = 'B';
        ELSE
            SET credit = 'C';
    END CASE;
END $$
DELIMITER;

WHILE과 ITERATE/LEAVE

WHILE <부울 식> DO
    SQL 명령문들
END WHILE;

• ITERATE
  • 지정한 곳으로 이동
• LEAVE
  • WHILE문을 빠져나온다

오류 처리

• 형식

DECLARE 액션 HANDLER FOR 오류조건 처리할문장;
-- 액션 : CONTINUE 혹은 EXIT. 오류 발생 시 행동을 정의한다.
-- 오류조건 : 어떤 오류를 처리할지 지정
--          오류코드 숫자나 상태코드, SQLEXCEPTION, SQLWARNING 등이 올 수 있다
-- 처리할문장 : 문장이 여러개면 BEGIN..END로 묶을 수도 있다

동적 SQL

미리 쿼리문을 준비한 후에 나중에 실행할 수 있다