[혼공S_3주]chapter04 SQL고급 문법

3주차 진도🐥

✨기본미션✨

P.195 4번

문제
SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
FROM memeber 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

✨추가미션✨

p.184 손코딩 실행하고 결과 캡처

공부📚

04-1 MySQL의 데이터형식

• 데이터형식

  • 정수형 : 소수점이 없는 숫자 / 연산에 의미가 있고 순서에 의미가 있을때 사용

    	TINYINT	SMALLINT	INT	BIGINT
    바이트수	1	2	4	8
    표현할수 있는 숫자	127	32767	21억	900경

    
    UNSIGNED : 1바이트 크기를 표시함 (0~255)
    

  • 문자형

    	CHAR(숫자)	VARCHAR(숫자)
    바이트수	1~255	1~16383
    	고정 길이 문자형	가변 길이 문자형
    	내부적으로 성능이 좋음	공간을 효율적으로 사용 가능
    예시	CHAR(6) 가나다OOO	VARCHAR(6)가나다

    

  • 실수형
    소수점이 있는 숫자 저장할때 사용

    	FLOAT	DOUBLE
    바이트수	4(소수점아래 7자리)	8(소수점아래 15자리)

  • 날짜형
    날짜와 시간을 입력할때는 작은따음표를 묶어서 써줘야함

    	DATE	TIME	DATETIME
    바이트수	3	3	3
    	날짜만 저장	시간만 저장	날짜, 시간저장
    형식	YYYY-MM-DD	HH:MM:SS	YYYY-MM-DD HH:MM:SS

  • 대량데이터 형식

    TEXT형식	TEXT	LONGTEXT
    바이트수	1~65535	1~4294967295	소설, 영화 대본 저장

    BLOB형식	BLOB	LONGBLOB
    바이트수	1~65535	1~4294967295	이미지, 동영상 저장

    

• 변수의 사용
  변수는 워크벤치를 종료하면 없어는 임시
  변수선언 방법

  SET @변수이름 = 변수값 -- 변수선언 및 값 넣기
  SELECT @변수이름 -- 변수 값 출력

  
  LIMIT에 변수사용하지 못함 -> PREPARE, EXECUTE를 사용함
  
  

• 데이터 형 변환
  형변환은 문자형→정수형 또는 정수형→문자형 바꾸는것

  명시적 변환	암시적변환
  함수를 이용해서 변환	자연스럽게 변환

  • 명시적 변환
    CAST 또는 CONVERT 사용
    함수에 쓸수있는 데이터형식: CHAR, SIGNED(부호있는 정수), UNSIGNEDSIGNED(부호없는 정수), DATE, TIME, DATETIME

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

    
    날짜형으로 병경하기
    
    결과를 원하는 형태로 변경하기
    

  • 암시적 변환
    문자+문자 = 숫자
    
    CONCAT(문자,문자) = 문자문자
    
    CONCAT(숫자, 문자) = 문자문자
    
    숫자+문자 = 숫자
    

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

내부조인

• 일대다 관계(one to many/PK-FK관계)
  한쪽 테이블에는 하나의 값이 있고 연결된 테이블에는 여러개의 값이 있는 관계
  기본키, 외래키 관계를 가지고 있음

  SELECT 열
  FROM 첫번째 테이블
  INNER JOIN 두번째 테이블
  ON 조인할 조건 -- 두개 테이블에 같은 이름의 열이 있으면 <테이블이름.열이름>으로 표기해야함
  [WHERE 검색조건]

  
  조인하는 두 테이블의 열이름이 같은것이 있으면 테이블이름까지 적어야 오류가 안생김
  
  테이블이름의 별칭을 사용하기
  
  내부조인은 양쪽테이블에 모두 내용이 있는것만 조인된다
  (외부조인은 한쪽 테이블에 내용이 있어도 조인함)
  
  결과의 중복을 제거하기 싶을때는 DISTINCT 사용하기
  

외부조인

• 외부조인: 한쪽 테이블에만 데이터가 있어도 결과가 나오는 조인

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

  LEFT OUTER JOIN : 왼쪽테이블의 내용을 모두 출력해라
  

  

  FULL OUTER JOIN : 왼쪽 외부조인, 오른쪽 외부조인을 합친것. 자주사용하지 않음

상호조인(CROSS JOIN)

• 상호조인: 한쪽 테이블과 모든 행과 다른 테이블의 모든 행을 조인 하는것
  • ON구문을 사용하지 않음
  • 결과가 의미가 없음
  • 상호조인은 테스트를 위한 대용량 데이터를 만들때 사용함
• SELECT TABLE ~ SELECT : 대용량 데이터를 만들 때 사용함
  
• 자체조인: 자신과 조인하는 것. 1개 테이블로 조인함. 실무에서 많이 사용하지 않음

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

  하나의 테이블을 각각 별칭을 지정해서 조인함
  

04-3 SQL프로그래밍

• MySQL에서 프로그래밍기능이 필요할때는 스토어드 프로시저안에서 만든다
• 스토어드 프로시저 구조
  스토어드 프로시저의 끝은 '$$'으로 구분한다

  DELIMITER $$
  CREATE PROCEDURE 스토어드 프로시저 이름()
  BEGIN SQL프로그래밍 코딩
  END $$ -- 스토어드 프로시저 종료
  DELIMITER;
  CALL 스토어드 프로시저 이름() -- 스토어드 프로시저 실행

• IF문
  

• IF ~ ELSE문
  조건이 참일때와 참이 아닐때 실행하는 문장이 다름
  
  SELECT ~ INTO : 결과를 변수에 저장할때 사용
  
  

• CASE문
  다중 분기에 사용함

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

  
  CASE-END와 INNER JOIN같이 써서 등급을 구함
  

• WHILE문
  조건문이 참인동안 <SQL문>을 반복한다

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

  
  ITERATE[레이블] : 지정한 레이블의 계속진행함 /CONTINUE와 비슷
  LEAVE[레이블] : 지정한 레이블을 종료함 /BREAK와 비슷
  

• 동적SQL
  내용변경이 필요할때 동적SQL문을 사용해서 변경함
  PREPARE : SQL문을 미리 준비합(실행X)
  EXECUTE : SQL문을 실해함
  DEALLOCATE PREPARE: SQL문을 해제 함

  기본형태
  
  현재시간 이용해서 출입기록 SQL문 만들기