4장. SQL 고급 문법

04-1 MySQL의 데이터 형식

• 테이블의 각각의 열에는 데이터 형식을 지정해야함

정수형

• INT
  - 가장 기본적
  • 4바이트 차지
  • -21억 ~ 21억
• SMALLINT
  - 2바이트 차지
  • -32768~32767
• TINYINT
  - 1바이트 차지
  • -128~127
  • UNSIGNED : 양의 정수만 쓴다. 0~255

문자형

• CHAR
  - 고정길이 문자형
  • 조금 더 속도가 빠르다
  • 전화번호 국번같은 경우, CHAR가 더 빠르다. INT로 하지 않는 이유: 02와 같이 0이 있기 때문
  • 최대 255까지
• VARCHAR
  - 가변길이 문자형
  - 공간을 효율적으로 사용할 수 있다.
  - 최대 16383까지
  (전화번호를 문자형으로 쓰는 이유: 숫자의 가치가 없다. 더하기, 빼기, 크기 비교 등을 하지 않음)

대량의 데이터 형식

• LONGTEXT
  - 긴 문자열
  - ex.자막
• LONGBLOB
  - 대용량 파일
  - ex.동영상

실수형

• 소수점이 있는 숫자
• FLOAT
  - 소수점 아래 7자리
  • 4바이트
• DOUBLE
  - 소수점 아래 15자리
  • 8바이트

날짜형

• DATE
  - 날짜만
• TIME
  - 시간만
• DATETIME
  - 날짜와 시간 모두

변수의 사용

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

• 변수는 계속 기억되지 않는다. 임시적.
  

• 변수를 넣는거랑 진짜 값을 넣는 거랑 똑같다
  

• 변수가 안쓰여진다. 문법적으로 지원이 안됨.
  

• 그럴때 PREPARE를 쓴다.
  - PREPARE로 미리 실행하고 싶은 쿼리문 작성
  - EXCUTE를 통해 실행.
  - 물음표자리에 변수가 들어간다.
  

데이터 형 변환

명시적인 변환

• CAST
  CAST(바꾸고 싶은 값 AS SIGNED)
  (SIGEND 는 양의 정수를 뜻함)
• CONVERT
  CONVERT(바꾸고 싶은 값, SIGNED)

암시적인 변환

• 형변환을 직접 안했는데도 형변환이 됨
  

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

• 실무에서 많이 사용한다.

조인

• 두개의 테이블을 묶는 것
• 배송을 하기 위해서
  - 회원테이블: 이름과 주소
  • 구매테이블: 물건

일대다 관계의 의미

• 기본키
• 외래키
• 회원은 1명(1)이지만 회원은 구매를 여러번(다, many) 한다.

내부조인 (INNER JOIN)

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

서로 다른 테이블에 같은 이름의 컬럼이 있기 때문에 SELECT 절에 테이블명.컬럼명으로 써준다

외부조인 (OUTER JOIN)

• 내부조인: 조인이 되는 것만 출력 - 둘다 값이 있어야 출력
• 외부조인: 한 쪽에만 있는 것도 출력 - 한 쪽에만 있어도 출력

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;

• 결과: 구매한 적이 없더라도 member테이블에 있는 mem_id는 모두 출력된다.

기타 조인 (CROSS JOIN)

• 내용상 의미는 없지만 대용량 데이터를 만들 때 사용된다.

SELECT * 
   FROM buy 
     CROSS JOIN member ;

• ON 구문이 없다.
• 테스트를 위해 대용량 데이터를 생성할 때만 사용한다.

자체조인 (SELF JOIN)

• 자기 자신과 자기 자신이 JOIN하는 것
• 같은 테이블이 두개 있다고 생각하면 된다.

SELECT A.emp "직원" , B.emp "직속상관", B.phone "직속상관연락처"
   FROM emp_table A
      INNER JOIN emp_table B
         ON A.manager = B.emp
   WHERE A.emp = '경리부장';

• 같은 테이블이기 때문에 구분을 위해 ALIAS 사용해야한다. (A, B)

04-3 SQL 프로그래밍

• 한마디로 코딩하는 것.
• 일반적인 언어보다는 약한데 반복문, 조건문 사용할 수 있다.
• SQL 프로그래밍은 스토어드 프로시저에서 사용하여야 한다.

스토어드 프로시저

• 나중에 자세히 배울 예정. 형태만 기억하자.
• BEGIN과 END 사이에 SQL문을 써준다. (이미지에서 네모친 부분)
• CALL을 통해 호출

IF문

• 조건식이 참이면 실행문을 실행하고, 거짓이면 실행하지 않는다.

IF~ELSE 문

• 조건식이 참이면 참일 때의 실행문을 실행, 거짓이면 거짓일 때의 실행문를 실행한다.

IF문의 응용

SELECT debut_date INTO debutdDate

• INTO: debut_date를 조회한 값을 debutDate 라는 변수에 넣어라

CASE문

• IF문과 달리 다중분기가 가능하다.
• CASE WHEN WHEN ELSE END

CASE문의 활용

        CASE  
           WHEN (SUM(price*amount)  >= 1500) THEN '최우수고객'
           WHEN (SUM(price*amount)  >= 1000) THEN '우수고객'
           WHEN (SUM(price*amount) >= 1 ) THEN '일반고객'
           ELSE '유령고객'
        END "회원등급"

• CASE문은 SELECT 문 안에 넣는다.

WHILE 반복문

• 변수 지정할 때 SET을 계속 써준다.

WHILE 반목문의 응용

WHILE IF LEAVE

동적 SQL

• PREPARE와 EXECUTE
• PREPARE SQL을 실행하지는 않고, 준비만 해둔다.
• EXECUTE 준비한 SQL문을 실행한다.
• DEALLOCATE PREPARE 실행 후 문장을 해제

예제

• 출입문에 카드를 태그하는 순간, 날짜와 시간을 저장한다.
• PREPARE에 미리 날짜와 시간을 저장하는 쿼리 저장
• 카드를 댈 때마다 EXECUTE

기본미션

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

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 - x → 조인은 FROM 안에 들어가야함
2. LIMIT - x → LIMIT는 ORDER BY 뒤에 들어가야함
3. HAVING - x → GROUP BY 뒤에 나와야 함
4. WHERE B.prod_name IS NULL → prod_name이 NULL인 것 중에~ 출력해라~

선택미션

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