데이터베이스4. SQL 타입, 고급문법

tinyint

-128~127 을 나타내는 형식

 

smallint

-32,768 ~ 32,768을 나타내는 형식

 

midiumint 

약 -800만 ~ 800만

 

int, integer 

약 -21억 ~ 21억

 

bigint

-900경 ~ 900경

 

모든 정수형에 unsigned를 부팅면 0부터 시작한다.

 

float

소수점 아래 7자리 까지

근사치를 저장한다.

 

double, real

소수점 아래 15자리 까지

근사치를 저장한다

 

decimal(m,[d])

numeric(m,[d])

전체자리수(m), 소수점자리수(d)를 가진 유형 

decimal(5,2)는 전체5자리, 소수점 2자리로 하곘다는 뜻

근사치가 아닌 정확한 값을 입력한다.

 

char(n) 

n개의 문자를 입력함.

255개 까지 가능

그냥 char은 char(1)과 동일

 

varchar()

가변길이 문자형 65635자까지 저장 가능

 

binary(n)

고정길이 이진데이터 저장

 

varbinary()

가변길이 고정데이터값 저장

 

tinytext 

1~255자까지 텍스트 저장

 

text

65535자까지 텍스트 저장

 

mediumtext

약 1600만개의 텍스트 저장

 

longtext

약 4GB의 큰 텍스트 저장

 

tinyblob

255크기의 blob(비디오 등)데이터

 

blob

65355크기의 blob데이터

 

mediumblob

약 1600만의 blob데이타

 

longblob

최대 4GB까지의 blob데이터

 

 

char과 varchar은 가변길이냐, 고정길이냐 차이가 있음.

char은 절해진 길이보다 짧을 경우 남은 공간을 빈공간으로 둠.

varchar은 입려된 길이만큼만 사용

공간효율은varchar, 속도는 char이 유리함.

 

date 3바이트

1001-101-01 ~ 9999-12-31까지 지정가능

yyyy-mm-dd 형식으로 사용

 

time 3바이트

hh:mm:ss 형식으로 사용

 

datetime 8바이트

yyyy-mm-dd hh:mm:ss 형식으로 사용

 

timestamp 4바이트

yyyy-mm-dd hh:mm:ss 형식으로 사용

time_zone변수와 관련있음

 

json 데이터

웹, 모바일 응용프로그램 등 데이터를 교환하기 위한 개방형 표준 포멧

JavaScript에서 파생

속성 과 값이 쌍을 이룸 key : value

{
	"아이디" : "BLK",
    "이름"  : "블랙핑크",
    "가입일" : "2016-04-15"
}

 

임시변수 @

워크벤치, 커맨드라인이 종료되기 전까지 유지되는 임시변수는 @로 선언할 수 있다.

use market_db;
set @myvar1 = 5;
set @myvar2 = 4.25;

select @myvar1;
select @myvar1 + @myvar2;
-- 변수를 출력함

set @txt = '가수 이름==>';
set @hight = 166;

select @txt, mem_name from member where height > @height;
-- 출력문 가수 이름==> 트와이스

 

임시변수가 사용 불가능한 경우

set @count = 3;
select mem_name, height from member order by height limit @count ;
-- limit에서 사용 시 오류가 발생 함.

 

 

SQL 준비문 실행문 prepare execute

set @num = 8, @addr='부산';
prepare mysql from 'select * from member where mem_number >= ? and addr = ?'
execute musql using @num, @addr;
 -- mysql이라는 이름으로 sql문을 미리 저장

prepare 문장을 준비한다. ?에는 변수가 들어간다

execute로 실행한다. using 다음 변수 자리에 들어갈 변수를 순서대론 넣는다.

 

데이터 형변환

명시적 변환

select cast(avg(price) as signed) '평균 가격' from buy;
select convert(avg(price), signed) '평균 가격' from buy;

-- int타입으로 변환하는 예시

cast, convert로 바꿀 수 있는 형식은 char, signed, unsigned, date, time, datetime 등

 

암묵적 형변환

별도의 지시 없이 변환

select 100 + 200, '100' + 200, '100' + '200';
-- 위의 경우 문자는 연산할 수 없으므로 자동으로 숫자로 변환
-- 300 출력

select concat(100,200), concat('100',200), concat('100','200');
-- 전화번호 처럼 연산 없이 이을 경우 사용
-- 100200출력

 

내부조인 inner join

일반적으로 조인이라고 하면 내부조인을 의미함.

select *
from buy
	inner join member
    on buy.mem_id = member.mem_id
    where buy.mem_id = 'grl'
    
 -- buy, member을 inner join하고 buy.mem_id가 grl인 값을 찾아라
 -- 결과는 member, buy에 있는 값을 겹치지 않게 합쳐서 가져온다.
 
 
 select B.mem_id, M.mem_name, M.addr, concat(M.phone1, "-", M.phone2) 연락처,
 	B.prod_name 제품, B.amount 수량
    from buy B
    inner join member M
    on B.mem_id = M.mem_id;
    
 --원하는 컬럼만 별명을 주는 경우
 
 select B.mem_id, M.mem_idm M.addrm Bmamount 수량
 from buy B, member M
 where B.mem_id = M.mem_id
 	and B.amount > 2
    order by B.amount desc;
    
 -- where절로 적는 방법

where절로 적는 방법은 호환성 문제가 있음

2가지 방식 모두 알고 있을것

 

외부조인 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;
    --왼쪽에 있는 테이블을 기준으로 외부조인
    
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;
   -- 오른쪽에 있는 테이블을 기준으로 외부조인

 

한쪽 테이블을 사용할 떄 다른 테이블을 외부조인하여 값을 가져오는 형태

 

~하지 않은 값을 가져오는 방법 not in, is null

selelct mem_id, mem_name, addr
	from member
    where mem_id not in(select distinct mem_id from buy)
    order by mem_id;
    -- 조건에 not in을 써서 해당하지 않는 값을 가져온다.
    
    select M.mem_id, M.mem_name, M.addr
    	from member M
        left outer join buy B
        on M.memPid = B.mem_id
        where B.prod_name is NULL
        order by M.name_id;
        -- is NULL과 outer join을 사용한 예

 

상호조인 cross join

cartesian product라고도 함

select * form buy cross join member;

-- 두 테이블의 각 행,열을 곱한 개수가 출력

모든 경우의 수를 출력한다고 생각하면 된다.

 

자체조인 self join

이너조인을 활용하여 자신과 조인하는 것

조건에 자기 자신의 테이블에 있는 값을 활용해야 하는 경우

SELECT A.emp AS "직원", B.emp AS "직속상관", B.phone AS "직속상관연락처"
FROM emp_table A
INNER JOIN emp_table B ON A.manager = B.emp
WHERE A.emp = '경리부장';
    -- 같은 테이블에 별칭을 2개 사용하여 내부조인처럼 사용한다.
    -- 경리부장의 직속상관의 연락처를 출력