혼자 공부하는 SQL / Chapter 04 SQL 고급 문법

04-1 MySQL의 데이터 형식

• 데이터 형식
  • 정수형
    • 소수점이 없는 숫자
    • TINYINT(1byte), SMALLINT(2byte), INT(4byte), BIGINT(8byte)
  • UNSIGNED 예약어
    • 값의 범위가 -가 아닌 0부터 시작되는 예약어
    • 예로 TINYINT는-128~127로 표현하면 TINYINT UNSIGNED는 0~255로 표현할 수 있다.
  • 문자형
    • 글자를 저장하기 위해 사용하며 입력할 최대 글자의 개수를 지정해야 한다.
    • CHAR(1~255byte), VARCHAR(1~16383byte)
      • CHAR은 고정길이 문자형으로 자릿수가 고정되어 있다. 예로 CHAR(10)에 3글자만 저장해도 10자리 모두 확보를 해서 7자리를 낭비하게 된다.
      • VARCHAR은 가변길이 문자형으로 3글자를 저장하면 3자리만 사용한다.
      • 공간은 VARCHAR이 효율적이지만 속도는 CHAR이 더 빠르다.
  • 대량의 데이터 형식
    • TEXT(1~65535byte), LONGTEXT(1~4294967295)
      • 소설이나 영화 대본 같은 내용을 저장한다면 필요한 데이터 형식
    • BLOB(1~65535byte), LONGBLOB(1~4294967295byte)
      • Binary Long Object의 약자로 이미지, 동영상 등의 데이터를 저장할 때 사용
  • 실수형
    • 소수점이 있는 숫자
    • FLOAT(4byte), DOUBLE(8byte)
      • FLOAT는 소수점 아래 7자리까지, DOUBLE은 소수점 아래 15자리까지 표현
  • 날짜형
    • 날짜 및 시간을 저장할 때 사용, 입력 시 작은 따옴표로 묶어서 사용
    • DATE(3byte), TIME(3byte), DATETIME(8byte)
      • DATE는 날짜만 저장하고 YYYY-MM-DD 형식
      • TIME은 시간만 저장하고 HH:MM:SS 형식
      • DATETIME은 날짜와 시간을 저장하고 YYYY-MM-DD HH:MM:SS 형식

 

• 변수의 사용
  • SQL도 변수 선언 및 사용 가능, MySQL 워크벤치 종료 시 사라진다. (임시 사용)
  • 기본 형식
    • 변수 선언 및 대입: SET @변수이름 = 변수의값;
    • 변수 값 출력: SELECT @변수이름;
  • LIMIT에는 변수를 사용할 수 없다. PREPARE, EXECUTE 사용으로 가능

 

• 데이터 형 변환
  • 명시적인 변환
    • 예로 실수에서 정수로 변환할 때 사용
    • CAST (값 AS 데이터형식 [(길이)])
    • CONVERT (값, 데이터형식 [(길이)])
  • 암시적인 변환
    • 함수를 사용하지 않고 자연스럽게 형이 변환되는 것
    • 예로 CONCAT(100, ‘200’) 은 100200 으로 결과가 나오고, 100 + ‘200’은 300이 출력된다.
      • CONCAT(): 문자를 이어주는 역할

 

 

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

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

 

• 일대다 관계(one to many)
  • 일대다 관계란 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러 개의 값이 존재할 수 있는 관계를 말한다.
  • 예로 1번 테이블의 아이디를 기본키로 지정했는데, 2번 테이블의 아이디에서는 n개의 해당 키를 찾을 수 있다. 그래서 2번 테이블의 아이디는 외래키로 설정했다.
  • 1명의 회사원이 여러 번의 급여를 받는 것이나, 1명의 학생이 여러 과목의 학점을 받는 것과 같은 관계가 바로 일대다 관계

 

• 내부 조인
  • 두 테이블을 연결할 때 가장 많이 사용되는 것, 그냥 조인이라고 부르면 내부 조인을 의미
  • 두 테이블에 모두 데이터가 있어야만 결과가 나온다.
  • 조인은 3개 이상의 테이블로도 할 수 있지만 대부분은 2개로 조인한다.
  • 기본 형식
    • INNER JOIN을 그냥 JOIN으로 써도 INNER JOIN으로 인식한다.
    • ex) 두 개의 테이블에 동일한 열 이름이 존재한다면 테이블이름.열이름 형식으로 표기

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

    • 테이블 이름에 AS로 별칭을 주면 간결하게 표기할 수 있다.
  • SELECT 열목록 FROM 첫번째테이블 INNER JOIN 두번째테이블 ON 조인될조건 [WHERE 검색조건]

 

• 중복된 결과 1개만 출력
  • DISTINCT
    • 열 이름 앞에 DISTINCT 표기 시 중복된 결과 1개만 출력 가능

 

• 외부 조인(outer join)
  • 외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다. (내부 조인은 양쪽 모두 있어야 결과 출력)
  • 내부 조인보다 자주 사용되지는 않지만 가끔 사용
  • 기본 형식
  • SELECT 열목록 FROM 첫번째테이블(LEFT 테이블) <LEFT or RIGHT or FULL> OUTER JOIN 두번째테이블(RIGHT 테이블) ON 조인될조건 [WHERE 검색 조건]
  • LEFT OUTER JOIN
    • 첫번째 테이블(LEFT 테이블)의 내용은 모두 출력
  • RIGHT OUTER JOIN
    • 두번째 테이블(RIGHT 테이블)의 내용은 모두 출력
  • FULL OUTER JOIN
    • 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것
    • 왼쪽이든 오른쪽이든 한쪽에 들어 있는 내용이면 출력, 자주 사용되지는 않음

 

• 기타 조인
  • 상호 조인(cross join)
    • 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능
    • 결과의 행 개수는 두 테이블의 각 행의 개수를 곱한 개수가 된다.
    • 주 용도는 테스트를 위한 대용량의 데이터 생성할 때
    • 기본 형식
    • SELECT * FROM table1 CROSS JOIN table2;
    • ON 구문 사용 불가, 랜덤으로 조인하기 때문에 결과의 내용은 무의미
  • 자체 조인(self join)
    • 자신이 자신과 조인한다는 의미 (1개의 테이블 사용)
    • 기본 형식
    • SELECT 열목록 FROM 테이블 별칭A INNER JOIN 테이블(위랑 같은테이블!) 별칭B ON 조인될조건 [WHERE 검색조건]

 

 

04-3 SQL 프로그래밍

• 스토어드 프로시저
  • MySQL에서 프로그래밍 기능이 필요할 때 사용하는 DB 개체
  • SQL 프로그래밍은 스토어드 프로시저 안에 만들어야 한다.
  • 기본 구조
  • DELIMITER $$ CREATE PROCEDURE 스토어드프로시저이름() BEGIN <SQL 프로그래밍 코드> END $$ DELIMITER; CALL 스토어드프로시저이름() // 실행

 

• IF 문, IF-ELSE 문
  • 조건문
  • 기본 형식

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

  • ex) 두 문장 이상 처리시 BEGIN~END로 묶어줘야 한다.

  DELIMITER $$
  CREATE PROCEDURE ifProc()
  BEGIN
  	DECLARE myNum INT;  // myNum 변수 선언
  	SET myNum = 200;  // 변수 값 대입
  	IF myNum = 100 THEN
  		SELECT '100입니다.';
  	ELSE
  		SELECT '100이 아닙니다.';
  	END IF;
  END $$
  DELIMITER;
  
  CALL ifProc();  // 함수 실행 
  

 

• CASE 문
  • 여러 가지 조건 중에서 선택하는 경우
  • CASE와 END CASE 사이에 WHEN을 사용하여 여러 조건을 넣을 수 있다.
  • 기본 형식
  • 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 [레이블]
      • 지정한 레이블로 가서 계속 진행
    • LEAVE [레이블]
      • 지정한 레이블을 빠져나감. WHILE 문 종료

---

미션

• 기본 미션
  • p.195의 확인 문제 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;
    

    • 정답: 4번 WHERE B.prod_name IS NULL
    • 이유: 해당 위치는 WHERE 검색 조건이 들어갈 위치이다!