SQL (2) - 데이터 형식과 조인

 

SQL 데이터 형식

 

1. 데이터 형식

 

- 숫자 데이터 형식

• SMALLINT, INT, BIGINT
• FLOAT : 소수점 7자리까지
• DECIMAL : 전체자릿수(m) + 소수점 이하 자릿수(d) = decimal(m,d)

- 문자 데이터 형식

• CHAR(n) : 고정길이 문자형. 1부터 255까지 지정
  CHAR(100)에 ABC 3글자만 저장하면 100자리를 모두 확보하고 97자리는 낭비.
• VARCHAR(n) : 가변길이 문자형. 1 ~ 65535까지 지정
  ABC 3글자만 저장하면 3자리만 사용.
• LONGTEXT : 최대 4GB크기의 TEXT 데이터값
  대용량의 글자를 저장하기 위한 형식.
• LONGBLOB : 최대 4GB크기의 BLOB 데이터값
  BLOB(Binary Large OBject) 사진, 동영상, 문서 파일 등의 대용량의 이진 데이터를 저장하기 위한 형식.

- 날짜와 시간 데이터 형식

• DATE : 1001-01-01 ~ 9999-12-31까지 저장
• DATETIME : 1001-01-01 00:00:00 ~ 9999-12-31 23:59:59까지 저장

날짜 데이터 생성

SELECT CAST ('2022-05-03 10:34:40.123' AS DATE) AS DATE #output 2022-05-03

SELECT CAST ('2022-05-03 10:34:40.123' AS TIME) AS TIME #output 10:34:40

SELECT CAST ('2022-05-03 10:34:40.123' AS DATETIME) AS DATETIME #output 2022-05-03 10:34:40

 

- 기타 데이터 형식

• GEOMETRY : 공간 데이터 형식으로 선, 점 및 다각형 같은 공간 데이터 개체를 저장하고 조작
• JSON : JSON 문서 저장

 

2. 변수의 사용

 

- 변수 선언

SET @변수이름 = 변수의 값  # 변수의 선언 및 값 대입

  SELECT @변수이름 # 변수의 값 출력

 

- 변수 활용

SET @myVar1 = 3;    #변수 선언

PREPARE myQuery     #쿼리문을 준비만 하고 실행하지 않음

    FROM 'SELECT Name, height FROM usertbl ORDER BY height LIMIT ?' ;

EXECUTE myQuery USING @myVar1 ; ]    #USING @변수를 이용해서 쿼리문에서 ?으로 처리한 부분에 대입

 

3. 데이터 형식과 형 변환

 

- 데이터 형식 변환 함수

 

CAST(), CONVERT()함수를 사용

CAST (expression AS 데이터형식 [(길이)])

CONVERT (expression , 데이터형식 [(길이)])

예시

SELECT CAST(AVG(amount) AS SIGNED INTEGER) AS '평균 구매 개수' FROM 테이블이름

또는

SELECT CONVERT(AVG(amount) , SIGNED INTEGER) AS '평균 구매 개수' FROM 테이블이름

 

- 암시적인 형변환

 

• CONCAT은 문자열을 연결해주는 함수로 문자열로 처리됨
• '3mega' 와 같이 숫자가 먼저 오는 경우는 앞의 정수로 대치(3)
• 'mega3'과 같이 문자열이 먼저 오는 경우는 0으로 대치

 

4. MySQL 내장 함수

 

(1) 제어 흐름 함수

 

- IF(수식, 참, 거짓)

SELECT IF (100>200, '참이다', '거짓이다');

- IFNULL(수식1, 수식2)

SELECT IFNULL (NULL, '널입니다'), IFNULL(100, '널입니다');
# 왼쪽 수식이 NULL아니면 수식1이 맞으면 수식2가 출력

- NULLIF(수식1, 수식2)

SELECT NULLIF(100,100), NULLIF(200,100)
# 수식1,2가 같으면 NULL을 반환하고, 다르면 수식1을 반환

- CASE ~ WHEN ~ ELSE ~ END(연산자)

다중 분기에 사용될 수 있음.

SELECT CASE 10
	WHEN 1 THEN '일'
        WHEN 4 THEN '사'
        WHEN 10 THEN '십'
        ELSE '모름'
    END AS 'CASE연습';  #출력될 열의 별칭

 

(2) 문자열 함수

 

- ASCII(아스키 코드), CHAR(숫자)

SELECT ASCII('A') , CHAR(65)
#output 65, 'A' -- 문자의 아스키값, 아스키코드값에 맞는 문자열 출력

 

- BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)

 

할당된 Bit 크기 또는 문자 크기를 반환 ( 영문 1byte, 한글 3byte )

 

- CONCAT(문자열1, 문자열2,...), CONCAT_WS(구분자, 문자열1, 문자열2, ...)

 

문자열을 단순하게 이어주는 함수, CONCAT_WS는 구분자를 문자열 사이사이에 넣어서 이어준다.

 

- ELT(위치, 문자열1, 문자열2,...) : 위치에 해당하는 문자열을 출력

- FIELD(찾을 문자열, 문자열1, 문자열2, ...) : 찾을 문자열의 위치를 출력 , 찾을 문자열이 없는 경우 0 출력

- FIND_IN_SET(찾을 문자열, 문자열 리스트) : 찾은 문자열을 문자열 리스트에서 찾아서 위치 출력

- INSTR(기준 문자열, 부분 문자열) : 기준 문자열에서 부분 문자열을 찾아서 시작 위치 출력

- LOCATE(부분 문자열, 기준 문자열) : INSTR과 같은 기능( 변수 위치가 다름 )

- FORMAT(숫자, 소수점 자릿수) : 숫자를 소수점 아래 자릿수까지 표현

- BIN(숫자), HEX(숫자), OCT(숫자) : 2진수, 16진수, 8진수 값 반환

- INSERT(기준 문자열, 위치, 길이, 삽입한 문자열) : 기준문자열에서 위치에서 길이만큼을 삭제하고 문자열을 대치

- LEFT(문자열,길이), RIGHT(문자열, 길이) : 문자열에서 길이만큼 출력

- UPPER(문자열), LOWER(문자열) : 대문자, 소문자 변경

- LPAD(문자열, 길이, 채울 문자열), RPAD(문자열, 길이, 채울 문자열) : left padding, right padding 빈칸 채움

- LTRIM(문자열), RTRIM(문자열) : 공백 제거

- TRIM(문자열), TRIM(방향 자를_문자열 FROM 문자열) : 공백제거, 방향(BOTH앞뒤, LEADING(앞), TRAILING(뒤))에 해당 문자만 삭제

- REPEAT(문자열,횟수) : 문자열 만큼 횟수 반복

- REPLACE(문자열, 원래 문자열, 바꿀 문자열) : 문자열을 찾아 대치

- REVERSE(문자열) : 문자열 순서를 거꾸로

- SPACE(길이) : 길이만큼 공백 생성

- SUBSTRING(문자열, 시작위치, 길이) 또는 SUBSTRING(문자열 FROM 시작위치 FOR 길이) : 문자열 추출

- SUBSTRING_INDEX(문자열, 구분자, 횟수) : 문자열안에 구분자로 구분해서 추출, 양수면 오른쪽 삭제, 음수면 왼쪽 삭제

 

(3) 수학 함수

 

- ABS(숫자) : 절대값

- CEILING(숫자), FLOOR(숫자), ROUND(숫자) : 올림, 내림, 반올림

- CONV(숫자, 원래 진수, 변환할 진수) : 원래 진수의 숫자를 변환할 진수로 변경해서 출력

- POW(숫자1, 숫자2), SQRT(숫자) : 거듭제곱값 , 제곱근

- TRUNCATE(숫자, 정수) : 숫자를 소수점을 기준으로 정수 위치까지 구하고 나머지 버림

 

(4) 날짜 및 시간 함수

 

- ADDDATE(날짜, 차이), SUBDATE(날짜, 차이) : 날짜를 기준으로 더하거나 뺀 날짜

- CURDATE(), CURTIME(), NOW(), SYSDATE() : 현재 날짜 시간

- DATEDIFF(날짜1, 날짜2), TIMEDIFF(시간1, 시간2) : 두 인수의 차이값 출력

- MAKEDATE(연도, 정수) : 연도에서 정수만큼 날짜가 지난 날을 출력

- PERIOD_ADD(연월, 개월수), PERIOD_DIFF(연월1,연월2) : 연월에서 개월수가 지난 날짜, 연월 개월수 차이 출력

 

(5) 시스템 정보 함수

 

- USER(), DATABASE() : 현재 사용자 및 데이터베이스 확인

- FOUND_ROWS() : 바로 앞의 SELECT문에서 조회된 행 개수 확인

- ROW_COUNT() : 바로 앞의 INSERT, UPDATE, DELETE문 에서 입력, 수정, 삭제된 행의 개수 확인

- SLEEP(초) : 쿼리의 실행을 잠깐 멈춘다.

 

 

조인

 

- 조인이란?

 

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

 

1. INNER JOIN(내부조인)

 

조인 중에서 가장 많이 사용되는 조인

양쪽 테이블에 모두 내용이 있는 것만 조인이 되는 방식

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

#예시 buytbl테이블과 usertbl테이블을 조인하는데 userID조건이 맞게 조인한다. 
SELECT *
FROM buytbl
	INNER JOIN usertbl
    ON buytbl.userID = usertbl.userID
WHERE buytbl.userID = 'JYP'

#3개 테이블 조인
SELECT *
	FROM stdtbl S
    	INNER JOIN stdclubtbl SC
        	ON S.stdName = SC.stdName
        INNER JOIN clubtbl C
        	ON SC.clubName = C.clubName
    ORDER BY S.stdName

 

한번이라도 기록된 내용만 출력(중복 X)

#1
SELECT DISTINCT U.userID, U.name, U.addr
	FROM usertbl U
    	INNER JOIN buytbl B
        	ON U.userID = B.userID
    ORDER BY U.userID ;

#2
SELECT U.userID, U.name, U.addr
	FROM usertbl U
    WHERE EXISTS (
    	SELECT *
        FROM buytbl B
       	WHERE U.userID = B.userID) ;

 

 

2. OUTER JOIN(외부조인)

 

조인의 조건에 만족되지 않는 행까지도 포함시키는 조인

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

LEFT : 왼쪽 테이블에서 확인된 내용은 모두 출력

RIGHT : 오른쪽 테이블에서 확인된 내용은 모두 출력

FULL : 한쪽을 기준으로 조건과 일치하지 않는 것을 출력하는 것이 아니라, 양쪽 모두에 조건이 일치하지 않는 것을 모두 출력.

 

3. CROSS JOIN(상호조인)

 

한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능

USE sqldb;
SELECT *
	FROM buytbl
      CROSS JOIN usertbl;

 

4. SELF JOIN(자체조인)

 

자기 자신과 자기 자신을 조인

 

5. UNION / UNION ALL / NOT IN / IN 

 

두 쿼리의 결과를 행으로 합치는 것

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

 

 

SQL 프로그래밍

 

스토어드 프로시져

DELIMITER $$
CREATE POCEDURE 스토어드 프로시져 이름()
BEGIN

	SQL 프로그래밍 코딩
    
END $$
DELIMITER ;

CALL 스토어드 프로시저 이름()

 

1. IF... ELSE

#형식
IF <부울 표현식> THEN
	SQL 문장1...
ELSE
	SQL 문장2...
END IF;

#예시
DROP PROCEDURE IF EXISTS ifProc;  -- 기존에 생성된 프로시저 삭제
DELIMITER $$
CREATE PROCEDURE ifProc()
BEGIN
	DECLARE var1 INT  -- 변수 선언
    SET var1 = 100;	-- 변수에 값 대입
    
    IF var1 = 100 THEN
    	SELECT '100 입니다';
    ELSE
    	SELECT '100이 아닙니다';
    END IF;
END $$
DELIMITER ;
CALL ifProc();

 

2. CASE

 

다중 IF 와 같은 기능을 합니다.

CASE
	WHEN 조건1 THEN
    	SET 변수 = '값' ;
    WHEN 조건2 THEN
    	SET 변수 = '값' ;
    ELSE
    	SET 변수 = '값';
END CASE;

 

3. WHILE / ITERATE / LEAVE

 

WHILE 참인 경우 동안 계속해서 반복

myWhile : WHILE 조건	--While문에 label을 지정
IF 조건1 THEN
	SET i = i+1
    ITERATE myWhile;	-- 지정한 label문으로 가서 계속 진행
END IF;
	SET 값대입
    IF 조건2 THEN
    	LEAVE myWhile -- 지정한 label문을 떠남 While 종료
	END IF;
END WHILE;