[혼공S] 3주차_CH4

Ch4. SQL 고급문법

4.1 MySQL의 데이터 형식

(1) 숫자형 : 더하기/빼기 연산의 의미가 있거나 크다/작다 또는 순서의 의미가 있는 경우.

• 정수형

데이터 형식	바이트 수	숫자 범위
TINYINT	1	-128~127
SMALLINT	2	-32768~ +32767
INT	4	약 -21억 ~ +21억
BIGINT	8	약 -900경 ~ +900경

- 데이터 형식마다 숫자 범위가 정해져있는데 입력값의 범위를 벗어나면 오류가 남.
- UNSIGNED를 이용하면 범위를 0부터 시작되도록 할 수 있다. ex) 열이름 TINYINT UNSIGNED
- 즉, TINYINT의 경우, 0부터 255로 1byte로 256개를 표현할 수 있다.
- 주로 양수를 나타내야하는 키, 인원수 등에 이용한다.

• 실수형

데이터 형식	바이트 수	설명
FLOAT	4	소수점 아래 7자리까지 표현
DOUBLE	8	소수점 아래 15자리까지 표현

(2) 문자형

데이터 형식	바이트 수	설명
CHAR(개수)	1~255	고정길이 문자형. ex) CHAR(10) : 3글자만 저장해도 10자리 모두 확보한 후 뒤의 7자리는 낭비.
VARCHAR(개수)	1~ 16383	가변길이 문자형. ex) VARCHAR(10) : 3글자를 저장할 경우 3자리만 사용.

💡 국번의 경우, 제일 앞의 0은 정수형에서 나타나지 않으므로 CHAR로 지정할 것!

(3) 날짜형

데이터 형식	바이트 수	설명
DATE	3	YYYY-MM-DD 형식으로 사용
TIME	3	HH:MM:SS 형식으로 사용
DATETIME	8	YYYY-MM-DD HH:MM:SS 형식으로 사용

(4) 대량의 데이터 형식

데이터 형식		바이트 수
TEXT 형식	TEXT LONGTEXT	1 ~ 65535 1 ~ 4294967295(42억)
BLOB 형식	BLOB LONGBLOB	1 ~ 65535 1 ~ 4294967295(42억)

- TEXT, LONGTEXT는 영화대본이나 소설같은 내용을 저장할 때 필요한 데이터 형식이며 LONGTEXT는 최대 4GB까지 입력가능.
- BLOB(Binary Long Object)는 이미지나 동영상등을 저장할 때 필요한 데이터 형식이며 LONGBLOB는 최대 4GB까지 가능.

• 변수의 사용

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

ex1) myVAR1, myVAR2 변수를 선언하고 덧셈 결과 출력

USE market_db;
SET @myVAR1 = 5;
SET @myVAR2 = 4;
SELECT @myVAR1 + @myVAR2;

ex2) 166을 변수로 선언하고 166보다 키가 큰 가수 이름 출력

USE market_db;
SET @txt = '가수 이름==> ';
SET @height = 166;
SELECT @txt, mem_name FROM member where height > @height;

🚨 LIMIT 사용시에는 변수를 사용할 수 없어서 PREPARE와 EXECUTE를 사용한다.

SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @count;

PREPARE을 통해 SELECT ~~ LIMIT? 문을 준비해두고 EXECUTE를 통해 USING으로 ?에 @count변수에 값을 대입하여 실행됨!

• 데이터 형 변환

(1) 함수를 이용한 명시적인 변환

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

ex1) 평균가격을 실수보다 정수로

SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy;

여기서, SIGNED : 부호가 있는 정수, UNSIGNED : 부호가 없는 정수를 의미.

ex2) 다양한 구분자를 날짜형으로 변경

SELECT CAST('2022%12%12' AS DATE);

ex3) 결과를 가격X수량 = 으로 나타내고 싶은경우

SELECT num, CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR), '=' ) '가격X수량', price*amount '구매액'
FROM buy;

여기서 CONCAT()은 문자를 이어주는 역할.

(2) 암시적인 변환 : CAST나 CONCAT을 사용하지 않고도 자연스럽게 형이 변환되는 것.

ex1) 문자는 더할 수 없으니까 자동으로 숫자로 변환해서 덧셈 수행.

SELECT '100' + '200';

ex2) CONCAT을 이용했으니까 문자그대로 이어짐.

SELECT CONCAT('100','200');

ex3) 숫자와 문자를 연산할 때 CONCAT을 이용하면 숫자가 문자로 변하고, 더하기만 사용하면 문자가 숫자로 변한 후에 연산됨.

SELECT CONCAT(100, '200');
SELECT 100 + '200';

4.2 두 테이블을 묶는 조인

조인 : 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어내는 것.
(1) 내부조인
- 일대다관계인 두 테이블을 조인
💡일대다관계(PK-FK관계)인 두 테이블?? : 한쪽 테이블에는 하나의 값만 존재해야하지만, 연결된 다른 테이블에는 여러개의 값이 존재할 수 있는 관계
ex1) 회원테이블에서, 아이디가 한 개의 값임. -> 기본키(PK)
구매테이블에서, 아이디는 여러번 출력됨. -> 외래키(FK)
ex2)1명의 회사원. 급여는 여러번 -> 회사원 테이블에서 이름이 기본키, 급여 테이블에서 이름이 외래키.
ex3) 1명의 학생. 학점은 여러개 -> 학생 테이블에서 이름이 기본키, 학점 테이블에서 이름이 외래키.
- 두 테이블에 모두 있는 내용만 조인됨.

• 내부조인의 형식

  SELECT 조인할 열들
  FROM 첫 번째 테이블
  INNER JOIN 두번째 테이블
  ON 조인될 조건
  WHERE 검색될 조건

🚨 필요한 열만 추출시에는 여러 테이블에 동일한 열이름이 존재할 수 있으므로 테이블이름.열이름 으로 표기할 것. 이때, 테이블 이름도 별칭(alias)을 통해 더 간결히 표현 가능.

🚨 WHERE 가 없으면 모든 행에 대해서 조인이 수행됨.

ex) 테이블 이름에 별칭을 사용해서 더 간결하게 표현.

SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) '연락처'
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;

구매 테이블을 기준으로, 구매테이블의 모든 행이 회원테이블과 결합.

ex) 한번이라도 구매기록이 있는 회원들 출력

SELECT DISTINCT M.mem_id, M.mem_name, M.addr
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_Id
ORDER BY M.mem_id;

💡 중복된 결과 1개만 출력하려면 DISTINCT문 활용.

(2) 외부조인 (이해가 잘안감...⭐⭐⭐)
필요한 내용이 한쪽 테이블에만 있어도 결과추출가능

• 외부조인의 형식

  SELECT 조인할 열이름
  FROM <첫번째 테이블(left 테이블)>
  <left|right|full> OUTER JOIN <두번째 테이블(right 테이블)>
  ON <조인될 조건>
  WHERE 검색될 조건;

여기서, LEFT(RIGHT) OUTER JOIN : 왼쪽(오른쪽) 테이블의 내용은 모두 출력. FULL OUTER JOIN : 왼쪽이든 오른쪽이든 한쪽에 있는 내용이면 출력.

ex) 전체 회원의 구매기록을 출력
1) 왼쪽에 있는 member테이블을 기준으로 외부조인

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;

2) 오른쪽에 있는 member테이블을 기준으로 외부조인

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
FROM buy B
RIGHT OUTER JOIN member M
ON M.mem_id = B.mem_id
ORDER BY M.mem_id;

🤔 오른쪽 왼쪽은 상관없고 기준이 어떤 테이블이냐가 중요한건가..? 근데 왜 둘이 다르지? 밑에는 buy가 기준이 되는게 아닌가....?

ex) 회원으로 가입만 하고, 한 번도 구매한 적이 없는 회원의 목록 = mem_id는 있지만 prod_name이 NULL인 경우

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
WHERE B.prod_name IS NULL
ORDER BY M.mem_id;

(3) 기타조인

• 상호조인 : 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능. / 주로 테스트하기 위한 대용량 데이터 생성을 위함.
  결과로 출력되는 행개수 = 두 테이블의 각 행의 개수를 곱한 개수.

SELECT *
FROM buy
CROSS JOIN member;

🚨 조인의 조건인 ON은 사용불가.

ex) 대용량 테이블 만들기.

CREATE TABLE cross_table
SELECT
FROM skila.actor
CROSS JOIN world.country;
SELECT FROM cross_table LIMIT 5;

salika.actor는 200행, world.country는 239행으로 이루어져있어서 대용량 테이블이 만들어지고 5건을 조회함.

• 자체조인 : 자기 자신과 조인. 테이블을 1개 사용하므로 테이블에 서로 다른 별칭을 사용하여 서로 다른 것처럼 사용.

  SELECT 조인할 열이름
  FROM 테이블이름 별칭 A
  INNER JOIN 테이블이름 별칭 B
  ON 조인될 조건
  WHERE 검색 조건

ex) 직원 중 직속상관의 연락처 알아내기

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

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
WHERE B.prod_name is NULL
ORDER BY M.mem_id;

4.3 SQL 프로그래밍

• 스토어드 프로시저 : MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체

  DELIMITER $$ #스토어드 프로시저 코딩 시작
  CREATE PROCEDURE 스토어드 프로시저 이름()
  BEGIN
  #이부분에 SQL 코딩
  END $$ #스토어드 프로시저 종료
  DELIMITER ;
  CALL 스토어드 프로시저 이름(); #해당 스토어드 프로시저 실행

(1) IF문
- 두 문장 이상 처리되어야 할 때에는 BEGIN~END로

DROP PROCEDURE IF EXISTS ifProc1;
DELIMITER $$ #스토어드 프로시저 코딩 시작
CREATE PROCEDURE ifProc1() #ifProc1이 스토어드 프로시저 이름
BEGIN
IF 100 = 100 THEN
SELECT '100은 100과 같습니다.';
END IF;
END $$
DELIMITER;
CALL ifProc1(); #ifProc1 스토어드 프로시저 실행

🚨 값이 같다는 표현은 == 가 아닌 = 로 표현!

- 조건에 따라 다른 부분을 수행해야 할 땐 IF ~ ELSE문으로

DROP PROCECURE IF EXISTS ifProc2; #이전에 ifProc2이름으로 생성된 스토어드 프로시저 삭제
DELIMITER $$ #스토어드 프로시저 코딩 시작
CREATE PROCEDURE ifProc2();
BEGIN
DECLARE myNUM INT; #정수형 myNUM변수 선언
SET myNUM = 200; #값부여
IF myNUM = 100 THEN
SELECT '100입니다';
ELSE
SELECT '100이 아닙니다.';
END IF;
END $$
DELIMITER;
CALL ifProc2();

- 아이디가 APN(에이핑크)인 회운의 데뷔 일자가 5년이 넘었는지 확인하고 5년이 넘었으면 축하 메시지 출력

DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3() #여기서 세미콜론 안닫음.
BEGIN
DECLARE debutDate DATE;
DECLARE curDate DATE; #오늘날짜
DECLARE days INT; #활동일수
SELECT debut_date INTO debutDate
FROM market_db.member
WHERE mem_id = 'APN';
SET curDATE = CURRENT_DATE(); #날짜계산함수 이용
SET days = DATEDIFF(curDATE, debutDate); #날짜계산함수 이용
IF (days/365) >= 5 THEN
SELECT CONCAT('데뷔한 지', days, '일이나 지났습니다. 축하합니다!');
ELSE
SELECT '데뷔한 지' + days + '일밖에 안됩니다. 화이팅!';
END IF;
END $$
DELIMITER;
CALL ifProc3();

참고) 날짜관련된 함수
- CURRENT_DATE(): 오늘 날짜
- CURRENT_TIMESTAMP() : 오늘 날짜 및 시간을 함께 출력
- DATEDIFF(날짜1, 날짜2) : 날짜2부터 날짜1까지 일수로 몇일인지 알려줌.

(2) CASE문 : 여러가지 조건 중에서 선택해야하는 경우

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

ex) 회원들의 총 구매액을 계산해서 회원의 등급을 4단계로 나누기
1) 구매 테이블에서 ORDER BY를 통해 회원별로 총 구매액을 구한 후에 내부조인으로 구매한 회원들에 대해서만 출력

SELECT B.mem_id, SUM(price*amount) "총구매액"
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id
GROUP BY mem_id
ORDER BY SUM(price*amount) DESC;

2) 외부조인으로 구매하지 않은 회원도 포함하여 출력

SELECT M.mem_id, M.mem_name, SUM(price*amount) '총구매액',
CASE
WHEN (SUM(price*amount) >= 1500) THEN '최우수고객'
WHEN (SUM(price*amount) >= 1000) THEN '우수고객'
WHEN (SUM(price*amount) >= 1) THEN '일반고객'
ELSE '유령고객'
END "회원등급"
FROM buy B
RIGHT OUTER JOIN member M
ON B.mem_id = M.mem_id
GROUP BY M.mem_id
ORDER BY SUM(price*amount) DESC;

회원등급열이 추가되어 거기에 최우수고객, 우수고객, 일반고객, 유령고객으로 표시된다.

(3) WHILE문 : 조건식이 참인동안 필요한 만큼 같은 내용 반복

WHILE 조건식 DO
SQL문장들
END WHILE;

ex) 1부터 100까지 값을 모두 더하는 WHILE문

DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$ #스토어드프로시저 시작
CREATE PROCEDURE whileProc()
BEGIN
DECLARE i INT;
DECLARE hap INT;
SET i = 1;
SET hap = 0;
WHILE (i<100) DO
SET hap = hap + i;
SET i = i + 1;
END WHILE;
SELECT '1부터 100까지의 합 ==>', hap;
END $$
DELIMITER;
CALL whileProc();

ex) 1부터 100까지의 합계에서 4의 배수를 제외하고, 1000이 넘어가는 순간의 숫자를 출력하고 프로그램을 종료하려면?
💡ITERATE, LEAVE문 이용!
ITERATE 레이블 :지정한 레이블로 가서 계속 진행
LEAVE 레이블 : 지정한 레이블을 빠져나가서 WHILE 종료

DROP PROCEDURE IF EXISTS whileProc2;
DELIMITER $$ #스토어드프로시저 시작
CREATE PROCEDURE whileProc2()
BEGIN
DECLARE i INT; #정수형 i변수 선언
DECLARE hap INT; #정수형 hap변수 선언
SET i = 1;
SET hap = 0;
myWhile; #myWhile이라는 레이블 지정
WHILE (i<100) DO
IF (i%4 = 0) THEN
SET i = i + 1;
ITERATE mywhile; #myWhile문을 계속해서 진행
END IF; #i가 4의배수가 아니면
SET hap = hap + i; #hap에 그 i를 더해줘
IF (hap > 1000) THEN #1000을 초과하면
LEAVE mywhile; #While종료
END IF;
SET i = i + 1;
END WHILE;
SELECT '1부터 10까지의 합(4의 배수 제외), 1000만 넘으면 종료 ==>', hap;
END $$
DELIMITER;
CALL whileProc2();

(4) 동적 SQL : 미리 SQL을 준비하고 나중에 실행하는것.
- PREPARE 문에서 ?로 향후에 입력될 값을 비워놓고, EXECUTE 문에서 USING으로 ?에 값을 전달.
ex) 출입증을 태그하는 순간의 날짜와 시간이 INSERT문으로 만들어져 입력되도록.

DROP TABLE IF EXISTS gate_table;
CREATE TABLE gate_table (id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);
SET @curDATE = CURRENT_TIMESTAMP();
PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL,?)'; #?로entry_time값 비워둠.
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;
SELECT * FROM gate_table;

id INT AUTO_INCREMENT : 아이디가 자동증가하도록 생성
entry_time : 출입하는 시간은 DATETIME형으로
?로 비워둔 곳에 @curDate가 채워지면서 myQuery실행됨! 그러므로 이 SQL을 실행한 시점의 날짜와 시간이 입력됨.