[혼공학습단 12기] 혼자 공부하는 SQL - 3주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 1주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 2주차
- 데이터 형식
데이터 형식은 수십개 정도이지만, 자주 사용하는 것만 본다.
- 정수형
정수형은 소수점이 없는 숫자, 인원 수, 가격, 수량 등에 많이 사용되는데, 정수형의 크기와 범위는 다음과 같다. 4개의 정수형으로 표현할 수 있는 숫자는 tinyint, smallint, int, bigint 등이 있다.
표현할 수 있는 값의 범위를 넘어서는 경우, Out of range 라는 오류가 발생한다.
각각의 범위에 맞게 표현하려면, 다음과 같이 저장한다.
인원수(member_num) 열은 int로 지정하고, 최대 127명까지 지정할 수 있는 tinyint로 지정해도 충분하다.
평균키(height) 열은 smallint로 지정하는데 -32768~32767까지 지정할 수 있지만, tinyint가 조금 더 적합하다. 다만 키가 200cm가 넘는 사람도 있으므로 범위가 부족하다.
이를 해결하기 위해 0부터 시작되는 unsigned 예약어를 사용하는데, tinyint와 tinyint unsigned 모두 1바이트의 크기이다.
1바이트는 256개를 표현하므로 -128~+127이나 0~255나 모두 256개를 표현한다. 따라서 평균키 열은 tinyint unsigned로 지정한다.
- 문자형
char(개수) : 바이트 수는 1~255, varchar(개수) : 바이트 수는 1~16383
char은 문자를 의미하는 character의 약자로 고정길이 문자형이다. 자릿수가 고정되어 있다.
char(10)에 '가나다' 3글자만 저장해도 10자리를 모두 확보한 후에 앞의 3자리를 사용하고 뒤의 7자리는 낭비하게 된다.
이와 달리 varchar는 가변길이 문자형으로, varchar(10)에 '가나다' 3글자를 저장한 경우 3자리만 사용한다.
예를 들어, 국번 지정시 제일 앞에 0이 붙어야 하는데 정수형으로 지정하면 0이 사라지므로, 문자형으로 저장한다.
이외에 실수형과 날짜형이 있다.
- 변수의 사용
변수의 선언과 대입은 다음과 같이 이루어진다.
set @변수이름 = 변수의 값;
select @변수이름;
변수의 선언 및 값 대입은 set @변수이름으로, 값을 출력하는 것은 select @변수이름;으로 한다.
변수는 mysql 워크벤치를 재시작할 때까지는 유지되나, 종료하면 없어진다.
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 문에서 행의 개수를 제한하는 limit에 변수를 사용하면 문법상 오류가 발생한다. 이를 해결하기 위해 prepare와 execute를 사용한다.
- 데이터 형변환
문자형을 정수형으로 바꾸거나 정수형을 문자형으로 바꾸는 것을 말한다.
- 명시적인 변환
cast ( 값 as 데이터_형식 [(길이)])
convert()도 동일한 기능을 한다.
select cast('2022$12$12' as date);
다양한 구분자를 날짜형으로 변환할 수 있다.
select num, concat(cast(price as char), 'X', cast(amount as char), '=')
'가격x수량', price*amount '구매액'
from buy;
가격과 수량은 정수지만, cast() 함수를 통해 문자로 바꿨는데, concat()함수는 문자를 이어주는 역할을 한다.
- 임시적인 변환
위의 2가지 함수를 사용하지 않고도 자연스럽게 형이 변환되는 경우를 말한다.
select concat('100','200');
결과는 100200이 출력된다.
- 확인문제
1. 크기가 작은 것부터 차례대로 나열하면, tinyint- smallint - int - bigint
2. Out of range
3. 정수형 데이터에 unsigned를 붙이면 데이터가 양수만 저장된다.
5. 숫자를 char형으로 지정하기에 적합한 것은 전화번호 국번, 전화번호 뒷자리이다. 0으로 시작할 수 있으므로
물품의 가격, 물품의 구매개수
6. 영화테이블의 자막을 저장하기 적합한 데이터형식은 longtext이고, 동영상 파일을 저장하기 적합한 데이터 형식은 longblob이다.
7. 데이터 형식의 변환에 사용되는 함수 2개는 convert(), cast()이다.
- 조인
두개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것
- 내부조인
테이블이 일대다 관계로 연결되어야 한다.
분리된 테이블은 서로 관계를 맺고 있는데, 마켓 db의 회원테이블과 구매 테이블을 보면, 회원테이블의 아이디는 각각의 회원마다 1개씩 부여되므로, 기본키로 지정했지만 구매 테이블의 아이디에서는 아이디 1개당 여러개의 물품을 구매할 수 있으므로 외래키로 지정한다.
select <열 목록>
from <첫번째 테이블>
inner join <두번째 테이블>
on <조인될 조건>
[where 검색 조건]
위의 기본 형식을 따른다. 구매 테이블에는 구매한 회원의 아이디와 물건 등의 정보만 있지만, 물건을 배송하려면 구매한 회원의 주소 및 연락처를 알아야 한다. 회원의 주소와 연락처를 알기 위해 정보가 있는 회원 테이블과 결합하는 것이 내부 조인이다. 예를 들면 특정 아이디를 가진 사람의 정보를 찾으려면 다음과 같이 입력한다.
use market_db;
select *
from buy
inner join member
on buy.mem_id = member.mem_id
where buy.mem_id = '조회할 아이디';
* 두개의 테이블을 조인할 때, 동일한 열이름이 존재한다면, 테이블_이름.열_이름 의 형식을 지켜야한다. ON 구문과 WHERE 구문에서 구매 테이블의 아이디(buy.mem_id)와 회원 테이블의 아이디(member.mem_id)를 사용한다.
위의 형식을 지키다보면 코드가 복잡해지므로, from 절에서 테이블의 이름 뒤에 별칭을 주어 해결한다.
select b.mem_id, m.mem_name, b.prod_name, m.addr,
concat(m.phone1, m.phone2) '연락처'
from buy b
inner join member m
on b.mem_id = m.mem_id;
- 중복된 결과 1개만 출력하기
SELECT DISTINCT M.mem_id, M.mem_name, M.addr
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id
ORDER BY M.mem_id;
- 외부조인
두 테이블 조인 시 필요한 내용이 한쪽 테이블에만 있어도 결과를 추출할 수 있다.
select <열 목록>
from <첫번째 테이블(LEFT 테이블)>
<LEFT : RIGHT : FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
ON <조인될 조건>
[where 검색 조건];
LEFT OUTER JOIN 문의 의미는 '왼쪽 테이블의 내용은 모두 출력되어야 한다'라고 할 수 있다.
외부 조인을 활용하여, 회원으로 가입만 하고, 한번도 구매한 이력이 없는 회원의 목록을 출력한다.
select distinct m.mem_id, b.prod_name, m.mem_name, m.addr
from member m
left outer join buy b
on m.mem_id = b.mem_id
where b.prod_name is null
order by m.mem_id;
이외에 한쪽 테이블의 모든 행과 다른쪽 테이블의 모든행을 조인시키는 상호 조인, 자신이 자신과 조인하는 자체조인 등이 있다.
- 확인문제
1. 두테이블이 일대다의 관계로 연결하기 위한 관계의 전제조건으로 적절한 것은 기본키 - 외래키 관계이다.
3. 외부 조인의 종류가 아닌 것은 center outer join이다.
4. 회원으로 가입만 하고, 한 번도 구매한 적이 없는 회원의 목록
SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
FROM member M
LEFT OUTER JOIN buy B
ON M.mem_id = B.mem_id
WHERE B.prod_name IS NULL
ORDER BY M.mem_id;
- 스토어드 프로시저
mysql에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체이다.
sql 프로그래밍은 스토어드 프로시저 안에 만든다.
delimiter $$
create procedure 이름()
begin
sql 프로그래밍 부분
end $$
delimiter;
call 이름()
* 구분문자는 $$ 외에도 /, &, @ 등을 사용할 수 있다. 다른 기호와 중복될 수 있으므로 기호 2개를 연속하여 사용한다.
이를 활용하여 아이디가 apn인 회원의 데뷔일자가 5년이 넘었는지 확인하고 5년이상이면 축하 메시지를 출력해보았다.
drop procedure if exists ifProc;
delimiter $$
create procedure ifProc()
begin
declare debutdate date;
declare curdate date;
declare days int;
select debut_date into debutdate
from market_db.member
where mem_id = 'apn';
set curdate = current_date();
set days = datediff(curdate, debutdate);
if (days/365) >= 5 then
select concat('데뷔한 지 ', days, '일이나 지났습니다.');
else
select '데뷔한 지' + days + '일밖에 안되었네요';
end if;
end $$
delimiter ;
call ifProc();
'데뷔한 지 4908일이나 지났습니다'가 출력된다.
drop procedure if exists ifProc;
기존에 ifProc()을 만든 적이 있다면 삭제한다는 뜻이다.
변수는 총 3개로, 데뷔일자, 오늘날짜, 데뷔일자부터 오늘까지의 날짜이다.
데뷔일자를 추출하는 select문에서 into 변수가 붙었는데, 이는 결과를 변수에 저장한다는 뜻으로,
데뷔일자가 debutdate 변수에 저장된다.
current_date()함수를 통해 현재 날짜를 변수에 저장한다.
* 오늘 날짜와 시간 함수: current_timestamp()
datediff()함수로 데뷔일자부터 오늘까지의 일수를 변수에 저장한다.
* 이를 변형하여 다음과 같이 입력한다.
drop procedure if exists ifProc3;
delimiter $$
create procedure ifProc3()
begin
declare debutdate date;
declare curdate date;
declare days int;
declare quotient int;
declare remainder int;
select debut_date into debutdate
from market_db.member
where mem_id = 'apn';
set curdate = current_date();
set days = datediff(curdate, debutdate);
set quotient = floor(days / 365);
set remainder = mod(days, 365);
if quotient >= 5 then
select concat('데뷔한 지 ', quotient, '년하고', remainder, '일이나 지났습니다. 축하합니다!');
else
select '데뷔한 지' + days + '일밖에 안되었네요';
end if;
end $$
delimiter ;
call ifProc3();