SET @myvar1 = 5;
SET @myvar2 = 4.25;
SELECT @myvar1 + @myvar2
출력 결과
SET @txt = '가수 이름==>';
SET @height = 166;
SELECT @txt, mem_name FROM member WHERE height > @height;
출력 결과
member 테이블의 height가 @height보다 mem_name을 @txt와 같이 출력.
SET @count = 3;
SELECT mem_name, height
FROM member
ORDER BY height
LIMIT @count; # 오류 발생
--------------------------
SET @count = 3;
PREPARE pre_name
FROM 'SELECT mem_name, height
FROM member
ORDER BY height
LIMIT ?';
EXECUTE pre_name USING @count
출력 결과
LIMIT은 compile time에 값이 정적으로 확정되어야 쿼리를 최적화할 수 있다.
그러나, @count는 runtime 변수이기에, 정적으로 판단을 못함.
이를 해결하기 위해, 'PREPARE + EXECUTE'를 사용한 동적 쿼리 방식으로 처리.
정수 변환
SELECT AVG(price) '평균 가격' FROM buy
출력 결과
SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy;
#위와 아래 둘 다 동일
SELECT CONVERT(AVG(price), SIGNED) '평균 가격' FROM buy
출력 결과
문자와 숫자를 섞어 사용
SELECT num, CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR), '=') '가격x수량', price*amount
FROM buy
출력 결과
강의에서는 price, 'X', amount, '='를 합치기 위해서 price와 amount를 문자로 변환해야 한다고 나온다.
아래와 같이 문자로 변환하지 않아도 결과는 똑같은 것을 확인했으나, 만일을 대비해서 변환하는 것을 습관들이는 것이 좋을 것 같다.
위와 같이 member 테이블과 buy 테이블이 있다고 했을 때, 두 테이블의 연결고리는 member_id이다.
INNER JOIN을 하게 되면, member_id를 기준으로 교집합의 행만을 join한다.
SELECT *
FROM buy
INNER JOIN member
ON buy.member_id = member.member_id
출력 결과
결과를 보면, 교집합이 아닌 'iyou', 'yg'는 가져오지 않는다.
buy와 member 모두에 member_id 컬럼이 있기에, 중복되어 출력된다.
따로 지정하여 SELECT하면 됨.
SELECT member_id, product_name, price, member_name, member_addr, height
FROM buy
INNER JOIN member
ON buy.member_id = member.member_id #오류 발생
----------------------------------------------------------------------------
SELECT buy.member_id, product_name, price, member_name, member_addr, height
FROM buy
INNER JOIN member
ON buy.member_id = member.member_id
출력 결과
위에서 말했듯 member_id는 두 테이블에 모두에 있기에, 어떤 테이블의 컬럼인지 지정해줘야 함.
SELECT B.member_id, B.product_name, B.price, M.member_name, M.member_addr, M.height
FROM buy B
INNER JOIN member M
ON B.member_id = M.member_id
buy는 B, member는 M으로 별명을 줘서 사용도 가능하다.
OUTER JOIN
두 테이블에 모두 데이터가 있어야만 결과가 나오는 내부 조인과 달리, 외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다.
# LEFT OUTER JOIN
SELECT B.member_id, B.product_name, B.price, M.member_name, M.member_addr, M.height
FROM buy B
LEFT OUTER JOIN member M
ON B.member_id = M.member_id
-----------------------------------------------------------------------------------
# RIGHT OUTER JOIN
SELECT B.member_id, B.product_name, B.price, M.member_name, M.member_addr, M.height
FROM buy B
RIGHT OUTER JOIN member M
ON B.member_id = M.member_id
출력 결과
↑ LEFT JOIN ↑
↑ RIGHT JOIN ↑
LEFT와 RIGHT는 위치만 다르지 결과는 같은 것을 볼 수 있다.
CROSS JOIN
내부 조인이나 외부 조인처럼 자주 사용되지는 않지만, 가끔 유용하게 사용되는 조인이다.
내용적 의미는 없으나, 대용량의 데이터를 생성할 때 사용한다.
SELECT *
FROM buy
CROSS JOIN member
출력 결과
11강.
STORED PROCEDURE
어떠한 동작을 일괄 처리하기 위한 용도이다.
쿼리문 처리를 바로 처리하지 않고 값을 받아와 프로시저 내부에서 일괄적으로 처리하 후 결과를 보내주기 위한 루틴이다.
IF문
프로그래밍에서 사용하는 IF문과 동일하다.
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
IF 100 = 100 THEN
SELECT '100은 100과 같습니다.' ;
END IF ;
END $$
DELIMITER ;
CALL ifProc1() ;
출력 결과
100=100을 만족하므로 '100dms 100과 같습니다.'를 출력.
'100=100이 아닌 숫자'인 경우에는 아무것도 출력하지 않는다.
IF ~ ELSE문
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
DECLARE myNum INT ;
SET myNum = 200 ;
IF myNum = 100 THEN
SELECT '100입니다.' ;
ELSE
SELECT '100이 아닙니다.' ;
END IF ;
END $$
DELIMITER ;
CALL ifProc2() ;
출력 결과
CASE문
IF문은 참과 거짓 2가지(2중 분기)이지만, CASE문은 2가지 이상의 경우(다중 분기)일 때 처리가 가능하다.
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() ;
출력 결과
WHILE문
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() ;
출력 결과
1~100까지의 합
DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
DECLARE i INT ; #1에서 100까지 증가할 변수
DECLARE hap INT ; #더한 값을 누적할 변수
SET i = 1 ;
SET hap = 0 ;
myWhile:
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() ;
출력 결과
1~100까지의 합(4의 배수 제외), 1000 넘으면 종료
동적 SQL
PREPARE & EXECUTE
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 ;
DEALLOCATE PREPARE myQuery ;
SELECT * FROM gate_table ;
