3:56

한빛출판사 우재남 저 <혼자 공부하는 SQL>을 참고하여 작성하였습니다.

1. 데이터 형식

테이블의 열을 만들 때는 데이터 형식을 지정해주어야 함. 대표적인 데이터 형식 예로는 정수형, 문자형, 실수형, 날짜형 등이 있음.

1 ) INT (정수형)

TINYINT : -128 ~ 127 (1바이트)

SMALLINT : -32,768 ~ 32,767 (2바이트)

INT : 약 -21억 ~ 21억 (4바이트)

BIGINT : 약 - 900경 ~ 900경 (8바이트)

cf) UNSIGNED

값의 범위가 0부터 시작하여 만약 데이터 타입을 ‘TINYINT UNSIGNED’로 선언하면 0부터 255까지 범위를 표현할 수 있음. (UNSIGNED를 사용하지 않으면 127까지만 가능)

CREATE TABLE member (height TINYINT UNSIGNED);
INSERT INTO member values (200);

2) CHAR (문자형)

CHAR (개수)  : 255자까지 

개수보다 작은 문자가 저장되어도 개수만큼 자리를 확보하기 때문에 남은 자리를 낭비함.

VARCHAR (개수)  : 16383자까지

가변길이 문자형으로, 입력한 개수만큼만 자리를 확보하여 낭비가 없으나 CHAR보다 속도가 느림.

CREATE TABLE member (
mem_id VARCHAR(10), -- 몇 글자가 입력될 지 모름
mem_adddr CHAR(2) -- 서울, 경기, 경남처럼 항상 2글자로 입력
)

3) 실수형

FLOAT (4바이트) 소수점 아래 7자리까지

DOUBLE (8바이트) 소수점 아래 15자리까지

4) 날짜형

DATE : YYYY-MM-DD (3바이트)

TIME : HH:MM:SS (3바이트)

DATETIME : YYYY-MM-DD HH:MM:SS (8바이트)

5)변수의 사용

SET @var1 = 3;
SET @var2 = 5;

SELECT @var1;
SELECT @var1 + @var2;

'SET @변수이름 = 값;' 으로 선언하고 'SELECT @변수이름'으로 사용함.

cf) LIMIT & PREPARE & EXECUTE

  LIMIT에는 변수를 사용할 수 없기 때문에 PREPARE과 EXECUTE로 아래처럼 우회하여 사용한다.

SET @cnt = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @cnt;

6) 데이터 형 변환

① 명시적 변환

CAST() 함수나 CONVERT() 함수를 이용하여 형 변환. 여기에 올 수 있는 데이터 형식으로는 CHAR, SIGNED(부호가 있는 정수), UNSIGNED(부호가 없는 정수), DATE, TIME, DATETIME 등이 있음.

SELECT CAST(float_data AS SIGNED) FROM table1;
SELECT CONVERT(float_data, SIGNED) FROM table2;

② 암시적 변환

SELECT CONCAT(100,200); --정수 100, 200이 문자열 '100','200'으로 형변환
SELECT 100+'200'; -- '200' 문자열이 정수 200으로 형변환

cf) CONCAT() 함수는 문자열을 이어줌.

2. JOIN

두 테이블을 어떤 기준으로 합칠 수 있음.

1) 내부 조인

두 테이블에 모두 있는 내용만 조인 되며, 테이블이 일대다 관계로 연결되어 있어야 함. 따라서 내부 조인 된 테이블에는 NULL이 존재하지 않음.

SELECT * FROM buy
INNER JOIN member -- 구매목록 테이블과 회원 테이블을 INNER JOIN 함.
ON member.mem_id=buy.mem_id -- mem_id 열을 기준으로 JOIN.
WHERE buy.mem_id=""; -- 특정 mem_id를 가진 행만 출력할 수도 있음.

만약 mem_id 구매 내역별로 회원 정보를 붙여 확인하고 싶다면 위와 같이 내부 조인하여 확인이 가능.

2) 외부 조인

한 쪽 테이블에만 내용이 있어도 조인 가능. 'LEFT OUTER JOIN'은 왼쪽 테이블의 내용을 모두 출력하고, 'RIGHT OUTER JOIN'은 오른쪽 테이블의 내용을 모두 출력함. 'FULL OUTER JOIN'은 자주 사용하지는 않으나 왼쪽이든 오른쪽이든 한 쪽에 들어 있는 내용이면 출력함.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
FROM member M
LEFT OUTER JOIN buy B -- member 테이블 기준으로 JOIN함. prod_name 열에 null값이 나올 수 있음.
ON M.mem_id = B.mem_id --mem_id 기준으로 JOIN
ORDER BY M.mem_id; -- mem_id 순으로 정렬

RIGHT OUTER JOIN으로 바꾸고 두 테이블의 순서를 바꾸면 같은 내용이 출력됨.

3) 상호 조인

상호 조인은 한 테이블의 모든 행과 다른 테이블의 모든 행을 조인시키는 기능을 하는데, 랜덤으로 조인하여 결과는 큰 의미가 없음.

SELECT * FROM buy CROSS JOIN member;

4) 자체 조인

한 테이블 안에서 조인이 일어난다.

CREATE TABLE emp_table (emp CHAR(4), manager CHAR(4), phone VARCHAR(4));

-- INSERT...

SELECT A.emp '직원', B.emp '직속 상관', B.phone '직속 상관 연락처'
FROM emp_table A
INNER JOIN emp_table B
ON A.manager = B.emp
-- WHERE A.emp = ''; //특정 직원을 지정하여 상관과 상관 연락처를 확인할수도 있음.

첫 번째 emp_table의 상관과 두 번째 emp_table의 직원이 같으면 직원, 직원의 상관, 상관의 연락처를 동시에 출력함. (기존 테이블에 직속 상관 연락처 열이 없었음에도 불구하고!)