[이것이 MySQL이다] 7장 정리

쿼리

set @myVar1 = 5;	-- 글로벌 변수 선언
set @myVar2 = 3;
set @myVar3 = 4.25;
set @myVar4 = '가수 이름==>';

SELECT @myVar1;	-- 변수 출력
SELECT @myVar2 + @myVar3;
select @myVar4, name from usertbl where height > 180;

select avg(amount) as '평균 구매 개수' from buytbl;
select cast(avg(amount) as signed integer) as '평균 구매 개수' from buytbl;	-- 형변환
select convert(avg(amount), signed integer) as '평균 구매 개수' from buytbl;

SELECT ELT(2, 'a', 'b'); -- 해당 위치 문자열 반환
SELECT FIELD('a', 'a', 'b');	-- 해당 문자열 위치 반환

SELECT FORMAT(12341234123, 1);	-- 숫자를 소수점 아래 자릿수까지 표현, 1000 단위마다 콤마 표현
SELECT 
    INSERT('abcdefghi', 3, 4, '@@@@'),
    INSERT('abcdefghi', 3, 2, '@@@@');	-- 기준 문자열의 위치부터 길이만큼 지우고 삽입할 문자열 끼워넣음

SELECT LEFT('abcdefghi', 3); -- 왼쪽에서 문자열의 길이만큼 반환
SELECT REPLACE('이것이 mysql이다', '이것이', 'this is');	-- 문자열 변환
SELECT REVERSE('abcde');	-- 문자열 거꾸로

SELECT SUBSTRING('대한민국만세', 3, 2);	-- 문자열 자르기
SELECT SUBSTRING('대한민국만세'from 3 for 2);	

SELECT FLOOR(RAND() * 6 + 1) AS '주사위 눈'; -- 임의의 0 이상 1 미만의 실수 구함

select year(now());	-- 현재 날짜 출력

핵심 포인트

• set @변수명을 통해 MySQL에서 글로벌 변수를 선언할 수 있다.

• cast()와 convert()를 통해 형변환을 할 수 있다.

• elt()와 field()를 통해 문자열과 위치를 가져올 수 있다.

• format()을 통해 숫자의 형식을 변경할 수 있다.

• insert(), substring()을 통해 문자열을 수정할 수 있다.

• left(), replace(), reverse()를 통해 문자열을 반환할 수 있다.

• floor() 를 통해 소수점을 제거할 수 있다.

• rand() 를 통해 임의의 0이상 1미만의 실수를 구할 수 있다.

• now() 와 기타 함수를 통해 날짜를 원하는 형식으로 출력할 수 있다.

쿼리

select *	-- 이너 조인 (구매를 한 사람만 나온다)
from buytbl b
join usertbl u on b.userID = u.userID;

select u.userid, u.name, b.prodname, u.addr, concat(u.mobile1, u.mobile2) as '연락처'	-- 아우터 조인 (구매를 하지 않은 유저도 나온다)
from usertbl u
left outer join buytbl b
on u.userID = b.userid
order by u.userid;

핵심 포인트

• 일반적으로 조인은 이너조인을 뜻한다. 데이터베이스의 테이블은 중복과 공간 낭비를 피하고 데이터의 무결성을 위해서 여러 개의 테이블로 분리하여 저장하는데, 조인은 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만드는 것이다.

• 아우터 조인은 조인의 조건에 만족되지 않는 행까지도 포함시키는 것이다. 형식에 따라 left, right, full이 있다.

쿼리

drop procedure if exists ifProc;
-- 구분자 설정
delimiter $$	
create procedure ifProc()	-- 프로시져 정의
begin
	declare var1 int; -- 로컬 변수 선언 
    set var1 = 100;
    if var1=100 then	-- if문 시작 
		select '100입니다';
	else
		select '100이 아닙니다';
	end if;	-- if문 종료 
end $$
delimiter ;

show procedure status;	-- 프로시져 조회
call ifProc();	-- 프로시져 호출

drop procedure if exists ifProc3;
delimiter $$
create procedure ifProc3()
begin
	declare point int;
    declare credit char(1);
    set point = 77;
    
    if point >= 90 then
		set credit = 'A';
	elseif point >= 80 then
        set credit = 'B';
	elseif point >= 70 then
        set credit = 'C';
	elseif point >= 60 then
        set credit = 'D';
	else
		set credit = 'F';
	end if;
    select concat('취득 점수 ==> ', point), concat('학점 ==> ',credit);
end $$
delimiter ;
call ifProc3();

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 hap;
end $$
delimiter ;
call whileProc();

delimiter $$
create procedure whileProc2()
begin
	declare i int;
    declare hap int;
    
    set i = 1;
    set hap = 0;
    
    myWhile: while(i<=100) do
		if(i%7=0) then
			set i = i+1;
			iterate myWhile;	-- continue문 처럼 돌아감
		end if;
		
		set hap = hap+i;
		if(hap>1000) then
			leave myWhile;	-- break문 처럼 벗어남
		end if;
	end while;
    select hap;
end $$
delimiter ;
call whileProc2();

select u.userid, u.name, sum(price * amount) as '총 구매액',
-- 	case
-- 		when(sum(price * amount) >= 1500) then '최우수 고객'
--         when(sum(price * amount) >= 1000) then '우수 고객'
--         when(sum(price * amount) >= 1) then '일반 고객'
-- 		else '유령 고객'
-- 	end as '고객 등급'
	 if(sum(price * amount) >= 1500,'최우수 고객',
		if(sum(price * amount) >=1000, '우수 고객',
        if(sum(price * amount) >=1, '일반 고객', '유령 고객'))) as '고객 등급' 
from buytbl b
right outer join usertbl u on b.userid = u.userid
group by u.userid, u.name
order by sum(price * amount) desc;

핵심 포인트

• MySQL에서도 다른 프로그래밍 언어처럼 분기, 흐름 제어, 반복을 할 수 있다.
• if…else문은 다음과 같은 형식으로 작성한다.

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

• CASE문은 다음과 같이 작성한다.

case
	when(sum(price * amount) >= 1500) then '최우수 고객'
	when(sum(price * amount) >= 1000) then '우수 고객'
	when(sum(price * amount) >= 1) then '일반 고객'
else '유령 고객'

• WHILE문은 다음과 같이 작성한다. ITERATE와 LEAVE를 통해 continue와 break문과 같은 동작을 수행할 수도 있다.

WHILE <부울 식> DO
	SQL명령문들...
END WHILE;

결론!

• 해당 코드들을 작성하면서 MySQL 내부에서 어떻게 프로그래밍을 할 수 있는지 이해할 수 있었다.