[2] SQL 기본문법 (2)

1. 데이터 형식

(1) 데이터 형식

1) 정수형

type	byte	range
tinyint	1	-128~127
tinyint unsigned	1	0~255
smallint	2	-32,768~32,767
smallint unsigned	2	0~65,535
int	4	약-21억~21억
int unsigned	4	0~42억
bigint	8	약-900경~900경
bigint unsigned	8	0~1,800경

2) 문자형

type	byte	characteristic
char	1~255	고정길이
varchar	1~16,383	가변길이

3) 대량의 데이터 형식

type	byte
text	1~65,535
longtext	1~약42억
blob	1~65,535
longblob	1~약42억

4) 실수형

type	byte	range	characteristic
float	4	약-3E+38~3E+38	소수점 아래 7자리
double	8	float 보다 더 큼	소수점 아래 15자리

5) 날짜형

type	byte	range	characteristic
날짜형	date	3	YYYY-MM-DD
날짜형	time	3	HH:MM:SS
날짜형	datetime	8	YYYY-MM-DD HH:MM:SS

(2) 변수 선언

SET문을 통해 변수를 선언할 수 있다. LIMIT 뒤에는 변수를 쓸 수 없지만 PREPARE문과 EXECUTE문을 통해 해결할 수 있다.

--예시)
set @변수이름 = 변수의 값;

--예시) limit 뒤에는 원칙적으로 변수를 사용할 수 없음.
set @count = 3;
prepare exercise from 'select mem_name, height from member limit ?';
execute exercise using @count;

(3) 데이터 형식 변환

데이터 형식을 바꾸는 것을 형 변환이라고 하는데, 직접 함수를 사용해 변환하는 명시적인 변환과 별도 지시 없이 변환하는 암시적인 변환으로 나뉜다. 명시적인 변환시에 CAST문 혹은 CONVERT 문을 사용하는데 둘은 동일한 기능을 한다.

--예시1) 명시적인 변환 - 평균가를 정수로 표현
select cast(avg(price) as signed) '평균가' from buy;

--예시2) 명시적인 변환 - 날짜형 변경
select cast('2022$12$12 as date);
select cast('2022/12/12 as date);

--예시3) 암시적인 변환(자동으로 변환)
select concat(100, '200');
select 100+'200';

2. FROM ~ JOIN ~ ON (KEY)

Join은 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어내는 것을 말한다.

(1) INNER JOIN

두 테이블의 공통된 데이터를 추출하고자 할 때 사용한다.

--예시1) mem_id를 key로 사용해서 구매한 제품 뒤에 멤버 이름, 주소, 폰 번호를 같이 출력
select buy.mem_id, member.mem_name, buy.prod_name, member.addr, concat(member.phone1,'-',member.phone2) 'phone' 
	from buy
	inner join member
    on buy.mem_id = member.mem_id;

--예시2) 예시 1번에 별칭을 사용해서 더 간결하게 표현
select B.mem_id, M.mem_name, B.prod_name, M.addr, concat(M.phone1,'-',M.phone2) 'phone' 
	from buy B
	inner join member M
    on B.mem_id = M.mem_id;

(2) OUTER JOIN

Inner join과 달리 한쪽의 데이터만 있어도 결과가 나온다. 기본적으로 from 뒤에 오는 테이블을 left, outer join 뒤에 오는 테이블을 right라고 한다. 또한 left outer join은 left의 모든 데이터가 출력되며, right outer join은 right의 모든 데이터가 출력된다. 즉 기준이 되는 테이블을 정하고 join 후 출력된다고 볼 수 있다.

--예시1) left 인 member 테이블을 기준으로 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;

--예시2) 예시 1번과 같은 결과를 출력하지만, right outer join
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;

--예시3) 구매 기록이 없는 회원들 목록 추출
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
    where B.prod_name is null;

(3) CROSS JOIN

한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 join한다. 자주 사용되진 않는다. 테스트를 위한 대용량 데이터의 생성이 주 목적이다. 모든 행을 join하기 때문에 on을 사용하지 않는다.

--예시)
select *
	from buy
    	cross join member;

(4) SELF JOIN

말 그대로 자신과 자신을 조인하는 것을 의미한다. 예로 다음과 같은 조직도의 직속 상관의 연락처를 출력하고자 할 때 사용할 수 있다.

emp	manager	phone
대표	null	0000
영업이사	대표	1111
관리이사	대표	2222
영업과장	영업이사	1111-1
경리부장	관리이사	2222-1
인사부장	관리이사	2222-2

select A.emp "직원", B.emp "직속상관", B.phone "직속상관 연락처"===3
	from emp_table A
    	inner join emp_table B
    	on A.manager = B.emp
    where A.emp = '경리부장';

다음과 같은 결과가 나온다.

직원	직속상관	직속상관 연락처
경리부장	관리이사	2222

3. 프로그래밍

(1) IF

if(조건문, 참일 때 반환값, 거짓일 때 반환값)

if 조건문 then
	~
end if;

기본 형식은 전자와 같다. 하지만 procedure 안에 있을 때는 후자와 같이 사용될 수 있다.

--예시1) 100이 넘는지 확인하는 procedure
//기존의 proecedure 있으면 삭제
drop procedure if exists ifProc;
//구분
delimiter &&
//생성
create procedure ifProc()
//시작
begin
	declare num int;
    set num = 200;
    if num = 100 then
		select '100입니다.';
	else
		select '100이 아닙니다.';
	end if;
//끝
end 
//구분
&& delimiter ;
//불러오기
call ifProc();

--예시2)
drop procedure if exists ifProc2;
delimiter &&
create procedure ifProc2()
begin
	declare debutDate date;
    declare curDate date;
    declare days int;
    select debut_date into debutDate
		from member
        where mem_id = 'APN';
    set curDate = current_date();
    set days = datediff(curDate, debutDate);
    if (days/365) >=5 then
		select concat('데뷔한 지', days, '일이나 지났습니다. ');
	else
		select concat('데뷔한 지', days, '일 밖에 안됐습니다.');
    end if;
end
&& delimiter ;
call ifProc2();

(2) CASE

앞서 본 if는 이중 분기인 것과 달리 case는 다중 분기이다.

case
	when <조건1> then ~
	when <조건2> then ~
    else ~
end

case의 기본 형식은 위와 같다. 하지만 if문과 마찬가지로 procedure 안에서 사용할 경우 end case;로 마쳐야한다.

--예시)
select M.mem_id "아이디", M.mem_name "이름", sum(B.price*B.amount) "총구매금액",
	case
    when sum(B.price*B.amount) >= 1500 then '최우수고객'
    when sum(B.price*B.amount) >= 1000 then '우수고객'
    when sum(B.price*B.amount) >= 1 then '일반고객'
    else '유령고객'
    end as "회원등급"
	from member M
		left outer join buy B
		on B.mem_id = M.mem_id
	group by M.mem_id
    order by sum(B.price*B.amount) desc;

(3) WHILE

whlie은 조건식이 참인 동안에 반복하는 명령어다. iterate문과 leave문을 활용해서 응용할 수 있다.

--예시1) 1부터 100까지 합
drop procedure if exists whileProc1;
delimiter &&
create procedure whileProc1()
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 whileProc1();

--예시2) 1부터 100까지 합 (4의 배수 제외, 총합 1000 넘으면 종료)
drop procedure if exists whileProc2;
delimiter &&
create procedure whileProc2()
begin
	declare i int;
    declare hap int ;
    set i = 1;
    set hap = 0;
    //레이블 지정
    myWhile:
    while i <= 100 do
		//4의 배수 제외
        if i%4 = 0 then
			set i = i+1;
			iterate myWhile;
		end if;
		set hap = hap + i;
        //총합이 1000을 넘으면 종료
        if hap >= 1000 then
			leave myWhile;
        end if;
        set i = i+1;
	end while;
    select '1부터 100까지의 합(4의 배수 제외, 합이 1000이 넘으면 종료) =>', hap;
end
&& delimiter ;
call whileProc2();

(4) PREPARE & EXECUTE

상황에 따라 내용 변경이 필요할 때 동적 sql을 사용할 수 있다.

prepare 이름 from '~?~';
execute 이름 using 변수;

기본 형식은 위와 같다.

--예시)
create table gate_table (id int auto_increment primary key, entry_time datetime);
set @cur_date = current_timestamp();
// my_quer에 입력, 동적으로 할당할 부분은 ?로 작성
prepare my_query from 'insert into gate_table values (null, ?)';
// ?에 @cur_date를 대입해서 실행
execute my_query using @cur_date;
// prepare문 해제
deallocate prepare my_query;

---

저자 우재남, 혼자 공부하는 SQL, 한빛미디어