데이터베이스 (2025 11 12)

use cookdb;

select U.userID, U.userName, 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 '고객등급'
from buytbl as B right outer join usertbl U
	on B.userID = U.userID
group by U.userID, U.userName
order by sum(price * amount) desc;

select 안쪽 속성에 case문으로 설정한 조건 반환을 넣을 수 있다

use cookdb;

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

while 문 또한 사용할 수 있다

use cookdb;

drop procedure if exists whileProc;
delimiter $$
create procedure whileProc()
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;
		end if;
        
        set hap = hap + i;
			if (hap > 1000) then
				leave myWhile;
			end if;
			set i = i + 1;
		end while;
		select hap;
end $$
delimiter ;
call whileProc();

특이하게 while밖에서 커스텀while을 넣어 2단 while을 구성할 수 있다

 

use cookdb;

drop procedure if exists checkOddEven;
delimiter $$
create procedure checkOddEven(in num int)
begin
	if (num % 2 = 0) then
		select '짝수';
	else
		select '홀수';
	end if;
end $$
delimiter ;

이렇게 짝수 홀수를 구분하는 함수를 만들고

import pymysql;

conn = pymysql.connect(host = "127.0.0.1",
                       user = "root", 
                       password = "1234", 
                       db = "cookdb", 
                       charset = "utf8")
num = input("홀짝찾기 숫자 : ")
cur = conn.cursor()
sql = """
    call checkOddEven(%s);
"""
cur.execute(sql, (num, ))
rows = cur.fetchall()
for row in rows :
    print(row[0])
conn.close()

 

py에서 호출도 가능하다

 

use schooldb;
drop procedure if exists checkOddEven;
delimiter $$
create procedure checkOddEven(in stuID int, courID int)
begin
	declare course_open int;
    declare already_in int;
    declare current_stu int;
    declare max_stu int;
    declare already_full bool;
    
	select is_open into course_open from course where course_id = courID;
    select enroll_id into already_in from enroll where course_id = courID and student_id = stuID;
    select count(*) into current_stu from enroll where course_id = courID;
    select capacity into max_stu from course where course_id = courID;
    
    set already_full = max_stu <= current_stu;
    
    if course_open != 1 then
		select '미개설' as result;
	elseif already_in is not null then
		select '기수강' as result;
	elseif already_full then
		select '정원초과' as result;
	else
		select '수강신청가능';
	end if;
end $$
delimiter ;
call checkOddEven(2, 1);drop procedure if exists addEnroll

조건을 섞어 쓰면 이런 기괴한 SQL을 만드는 것도 가능하다

 

		signal sqlstate '450000' set message_text = '실패 정원초과';

이런 오류 핸들링도 된다