[3주차] Chapter 04

Jisoo Yu·2024년 1월 21일

혼공학습단 SQL

목록 보기

4/5

진도: Chapter 04

유튜브강의 9~11강

1절

-- 숫자형
USE market_db;
CREATE TABLE hongong4 (
	tinyint_col TINYINT, -- : -128 ~ 127 
    smallint_col SMALLINT, -- : -32,768 ~ 32,767
    int_col INT, -- : 약 21억 ~ 21억
    bigint_col BIGINT -- : 약 -900경 ~ 900경
);

INSERT INTO hongong4 VALUES(127,32767,2147483647,9000000000000000000);
INSERT INTO hongong4 VALUES(127,32767,2147483647,90000000000000000000);

-- Out of range : 범위를 벗어났다는 의미

-- 문자형
-- CHAR : 고정형, 해당 자리수만큼 자리차지, 성능(속도면에서) 더 좋음, 최대 255
DROP TABLE big_table;
CREATE TABLE big_table (
	data1 CHAR(255)
);
-- VARCHAR : 가변형, 숫자 갯수만큼만 자리 차지, 최대 16,383
DROP TABLE big_table;
CREATE TABLE big_table (
    data2 VARCHAR(16383)
);

CREATE DATABASE netflix_db;
USE netflix_db;
CREATE TABLE movie
(movie_id	INT,
movie_title	VARCHAR(30),
movie_director	VARCHAR(20),
movie_star	VARCHAR(20),
movie_script	LONGTEXT,
movie_film	LONGBLOB
);

-- 실수형
-- FLOAT : 소수점 아래 7자리까지 표현, 4byte
-- DOUBLE : 소수점 아래 15자리까지 표현, 8byte

-- 날짜형
-- DATE : 날짜만 저장, 'YYYY-MM-DD', 3byte
-- TIME : 시간만 저장, 'HH:MM:SS', 3byte
-- DATETIME : 날짜 및 시간 저장, 'YYYY-MM-DD HH:MM:SS', 8byte

-- 변수 : 현재 상황에서만 쓰는 임시적인 것 (껐다 키면 새로 해줘야함)
USE market_db;
SET @myVar1 = 5;
SET @myVar2 = 4.25;

SELECT @myVar1;
SELECT @myVar1 + @myVar2;

SET @txt = '가수 이름 ==> ';
SET @height = 166;
SELECT @txt, mem_name FROM member WHERE height > @height;
-- 위랑 같음
SELECT '가수 이름 ==> ', mem_name FROM member WHERE height > 166;

SET @count = 3;
-- SELECT mem_name, height FROM member ORDER BY height LIMIT @count; -- 문법적으로 지원을 안해줘서 에러남

-- 위와 같은 경우에 PREPARE 사용
SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?'; -- ? : 변수가 들어올 부분에 입력, 미정
EXECUTE mySQL USING	@count; -- 실제 수행문

SELECT avg(price) '평균가격' FROM buy;

-- CAST, CONVERT : 형변환
-- SIGNED : 부호가 있는 정수형
SELECT cast(avg(price) as signed) '평균가격' FROM buy;
SELECT CONVERT(avg(price) , signed) '평균가격' FROM buy;

-- 다 됨
SELECT CAST('2022$12$12' as DATE);
SELECT CAST('2022/12/12' as DATE);
SELECT CAST('2022%12%12' as DATE);
SELECT CAST('2022@12@12' as DATE);

-- CONCAT : 문자열 이어주는거
SELECT num, CONCAT(CAST(price AS CHAR), ' X ', CAST(amount AS CHAR), ' = ' ) '가격X수량',
price*amount '구매액'
FROM buy;

-- 암시적 형변환 (직접 지정안해줘도 알아서 변환)
SELECT '100' + '200'; -- 문자 + 문자 >> 정수 변환되어 연산
SELECT CONCAT('100','200'); -- 문자와 문자 연결 >> 문자
SELECT CONCAT(100,'200'); -- 정수와 문자 연결 >> 정수가 문자변환되어 처리
SELECT 1 > '2mega'; -- 정수인 2로 변환되어서 비교
SELECT 3 > '2MEGA'; -- 정수인 2로 변환되어서 비교, (cf. 0: False, 1: True)
SELECT 0 = 'mega2'; -- 문자는 0으로 변환됨

2절

-- 일대다(one to many) : 기본키(PK)-외래키(FK) 관계

-- 내부조인(INNER) : 서로 있는것만 나옴
USE market_db;
SELECT *
	FROM buy
		INNER JOIN member
		ON	buy.mem_id = member.mem_id
	WHERE buy.mem_id = 'GRL';
    
SELECT *
	FROM buy
		INNER JOIN member
		ON	buy.mem_id = member.mem_id;
        
-- 어느 테이블의 mem_id인지 모호해서 오류남       
SELECT mem_id, mem_name, prod_name, addr, concat(phone1, phone2) as '연락처'
	FROM buy
		INNER JOIN member
		ON	buy.mem_id = member.mem_id;
        
-- 테이블 명은 다 붙여주는게 좋음
SELECT buy.mem_id, mem_name, prod_name, addr, concat(phone1, phone2) as '연락처'
	FROM buy
		INNER JOIN member
		ON	buy.mem_id = member.mem_id;
        
-- 테이블 명을 그대로쓰면 길어지기 때문에 아래처럼 별명을 줘서 사용
SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, 
        CONCAT(M.phone1, M.phone2) AS '연락처' 
   FROM buy B
     INNER JOIN member M
     ON B.mem_id = M.mem_id;

-- 외부조인(OUTER) : 한쪽 테이블에 값이 없어도 다 나옴
-- < LEFT | RIGHT | FULL > 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;
  
  
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;

-- 상호조인(CROSS JOIN) : N x N
-- 테스트하기위해 대용량 데이터 생성할 때 사용, ON 구문 X

SELECT *
FROM buy
	CROSS JOIN member;
    
SELECT count(*) "데이터 개수"
	FROM sakila.inventory
		CROSS JOIN world.city;

DROP TABLE cross_table;
CREATE TABLE cross_table
	SELECT *
		FROM sakila.actor
			CROSS JOIN world.country;

SELECT * FROM cross_table LIMIT 5;

-- 자체조인(SELF) : 자기자신과 매핑할 떄 사용
-- 같은 테이블끼리 조인하는거기때문에 별칭을 다르게 줌
USE market_db;
CREATE TABLE emp_table (emp CHAR(4), manager CHAR(4), phone VARCHAR(8));

INSERT INTO emp_table VALUES('대표', NULL, '0000');
INSERT INTO emp_table VALUES('영업이사', '대표', '1111');
INSERT INTO emp_table VALUES('관리이사', '대표', '2222');
INSERT INTO emp_table VALUES('정보이사', '대표', '3333');
INSERT INTO emp_table VALUES('영업과장', '영업이사', '1111-1');
INSERT INTO emp_table VALUES('경리부장', '관리이사', '2222-1');
INSERT INTO emp_table VALUES('인사부장', '관리이사', '2222-2');
INSERT INTO emp_table VALUES('개발팀장', '정보이사', '3333-1');
INSERT INTO emp_table VALUES('개발주임', '정보이사', '3333-1-1');

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

3절

USE market_db;

-- IF문 : 참 or 거짓
DROP PROCEDURE IF EXISTS ifProc1; -- 기존에 만든적이 있다면 삭제
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
   IF 100 = 100 THEN  
      SELECT '100은 100과 같습니다.';
   END IF;
END $$
DELIMITER ;
CALL ifProc1();


DROP PROCEDURE IF EXISTS ifProc2; 
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
   DECLARE myNum INT;  -- DECLEAR : 프로시저 안에서 변수선언 (밖에선 @로 변수선언)
   SET myNum = 200;  -- 변수에 값 대입
   IF myNum = 100 THEN  
      SELECT '100입니다.';
   ELSE
      SELECT '100이 아닙니다.';
   END IF;
END $$
DELIMITER ;
CALL ifProc2();

DROP PROCEDURE IF EXISTS ifProc3; 
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
    DECLARE debutDate DATE; -- 데뷰일
    DECLARE curDate DATE; -- 오늘
    DECLARE days INT; -- 활동한 일수

    SELECT debut_date INTO debutDate -- debut_date 결과를 debutDATE에 대입
       FROM market_db.member
       WHERE mem_id = 'APN';

    SET curDATE = CURRENT_DATE(); -- 현재 날짜
    SET days =  DATEDIFF(curDATE, debutDate); -- 날짜의 차이, 일 단위

    IF (days/365) >= 5 THEN -- 5년이 지났다면
          SELECT CONCAT('데뷔한지 ', days, '일이나 지났습니다. 핑순이들 축하합니다!');
    ELSE
          SELECT '데뷔한지 ' + days + '일밖에 안되었네요. 핑순이들 화이팅~' ;
    END IF;
END $$
DELIMITER ;
CALL ifProc3();

SELECT CURRENT_DATE(), DATEDIFF('2021-12-31', '2000-1-1');

-- CASE문 : 조건 여러개, 다중분기
DROP PROCEDURE IF EXISTS caseProc; 
DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
    DECLARE point INT ;
    DECLARE credit CHAR(1);
    SET point = 88 ;
    
    CASE 
        WHEN point >= 90 THEN
            SET credit = 'A';
        WHEN point >= 80 THEN
            SET credit = 'B';
        WHEN point >= 70 THEN
            SET credit = 'C';
        WHEN point >= 60 THEN
            SET credit = 'D';
        ELSE
            SET credit = 'F';
    END CASE;
    SELECT CONCAT('취득점수==>', point), CONCAT('학점==>', credit);
END $$
DELIMITER ;
CALL caseProc();

SELECT mem_id, SUM(price*amount) "총구매액"
   FROM buy
   GROUP BY mem_id;
   
SELECT mem_id, SUM(price*amount) "총구매액"
   FROM buy
   GROUP BY mem_id
   ORDER BY SUM(price*amount) DESC ; -- 내림차순 정렬


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

-- 구매 안한 사람도 다 나오게 하기
SELECT M.mem_id, M.mem_name, SUM(price*amount) "총구매액"
   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 ;
   

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 ;

-- WHILE : 반복문, 참일 경우 반복   
DROP PROCEDURE IF EXISTS whileProc; 
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE i INT; -- 1에서 100까지 증가할 변수
    DECLARE hap INT; -- 더한 값을 누적할 변수
    SET i = 1;
    SET hap = 0;

    WHILE (i <= 100) DO
        SET hap = hap + i;  -- hap의 원래의 값에 i를 더해서 다시 hap에 넣으라는 의미
        SET i = i + 1;      -- i의 원래의 값에 1을 더해서 다시 i에 넣으라는 의미
    END WHILE;

    SELECT '1부터 100까지의 합 ==>', hap;   
END $$
DELIMITER ;
CALL whileProc();

-- WHILE 응용
-- ITERATE & LEAVE : 특정 배수를 건너뛰고 싶을 때 
DROP PROCEDURE IF EXISTS whileProc2; 
DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
    DECLARE i INT; -- 1에서 100까지 증가할 변수
    DECLARE hap INT; -- 더한 값을 누적할 변수
    SET i = 1;
    SET hap = 0;

    myWhile: -- while문 이름
    WHILE (i <= 100) DO  -- While문에 label을 지정
       IF (i%4 = 0) THEN
         SET i = i + 1;     
         ITERATE myWhile; -- 지정한 label문으로 가서 계속 진행
       END IF;
       SET hap = hap + i; 
       IF (hap > 1000) THEN 
         LEAVE myWhile; -- 지정한 label문을 떠남. 즉, While 종료.
       END IF;
       SET i = i + 1;
    END WHILE;

    SELECT '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==>', hap; 
END $$
DELIMITER ;
CALL whileProc2();

-- 동적 SQL
-- PREPARE : 쿼리문을 실시간으로 변형 가능
-- EXECUTE : 위 PREPARE문 실행
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;


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, ?)'; -- ? : 변수 들어갈 자리
EXECUTE myQuery USING @curDate; -- USING : 변수 사용
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;

기본 미션

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

선택 미션

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

Jisoo Yu

꽤 행복한 사람😎

이전 포스트

[2주차] Chapter 03

다음 포스트

[3주차] Chapter 04

혼공학습단 SQL

진도: Chapter 04

기본 미션

선택 미션

[2주차] Chapter 03

[4주차] Chapter 05

0개의 댓글