[DB-MariaDB] 개발자 지망생 스터디 - 15일차

SQL

1. Scala Function

• 하나의 데이터를 받아서 하나의 데이터를 리턴하는 단위
• 컬럼을 데이터로 제공하면 각 컬럼의 데이터 단위로 작업을 수행한 후 결과를 하나의 컬럼으로 만들어서 리턴

1) 수치 함수

• 숫자 연산과 관련된 함수로 올림, 버림, 반올림 등의 함수가 제공
• 데이터는 숫자 데이터이어야 함
• FROM 절을 제외한 곳에서 사용이 가능
• EMP 테이블에서 EMPNO 가 홀수인 데이터를 조회 : MOD(데이터, 나누는 수) 는 나머지를 리턴

#EMP 테이블에서 EMPNO의 항이 홀수인 데이터를 조회
SELECT *
FROM EMP
WHERE MOD(EMPNO, 2) = 1;

 

2) 문자열 함수

• CONCAT : 문자열 결합
• UPPER, LOWER
• LTRIM, RTRIM, TRIM
• SUBSTRING
• LENGTH

#EMP 테이블에서 1982년에 입사한 사원의 ENAME과 SAL을 조회 (입사일:HIREDATE)
SELECT ENAME, SAL, HIREDATE 
FROM EMP
WHERE HIREDATE >= '19820101' AND HIREDATE <= '19821231';

-- BETWEEN 사용
SELECT ENAME, SAL, HIREDATE 
FROM EMP
WHERE HIREDATE BETWEEN '19820101' AND '19821231';

-- SUBSTRING 사용
SELECT ENAME, SAL, HIREDATE 
FROM EMP
WHERE SUBSTRING(HIREDATE, 1, 4) = '1982';
-- HIREDATE 항의 시작위치 1번부터 4번째 자리까지 1982

3) 날짜 관련 함수

• 현재 날짜 및 시간

CURRENT_DATE(), CURDATE() -- 현재 날짜
CURRENT_TIME(), CURTIME() -- 현재 날짜
NOW(), LOCALTIME(), LOCALTIMESTAMP(), CURRENT_TIMESTAMP() -- 현재 날짜 및 시간

 

• 날짜 및 시간 함수

ADDDATE,SUBDATE, ADDTIME, SUBTIME

 

• 특정 날짜 생성

STR_TO_DATE(문자열 서식) : 서식에 맞춰서 문자열을 날짜 형태로 변환

('1986-05-05 11:00:00', '%Y-%m-%d %H:%i:%S')

MySQL 이나 MariaDB는 일반적인 날짜 포맷의 문자열도 날짜로 간주

 

STR_TO_DATE('1986-05-05', '%Y-%m-%d'), '1986-05-05'('날짜에서 많이 사용하는 포맷이므로 날짜로 간주)

 

4) 시스템 정보 함수

ROW_COUNT()

USER()

DATABASE()

 

5) 타입 변환 함수

CAST(데이터 AS 자료형)

자료형 : DATETIME, DATE, TIME, CHAR(VARCHAR,TEXT), INT, DOUBLE, BINARY...

 

6) NULL 관련 함수

• IFNULL(데이터1, 데이터2) : 데이터1이 NULL 이 아니면 데이터1을 리턴하고 데이터1일 NULL이면 데이터 2를 리턴
• NULLIF(데이터1, 데이터2) : 두 개의 데이터가 같으면 NULL을 리턴하고 그렇지 않으면 데이터 1을 리턴
• COALESCE(데이터 나열) : 나열된 데이터 중 NULL이 아닌 첫 번째 데이터를 리턴

SELECT 10 + 20;
SELECT 10 + NULL; #데이터베이스에서는 NULL과 연산을 하면 결과는 NULL
SELECT 10 + IFNULL(NULL, 20);

7) 분기 관련 함수

• IF
• CASE 데이터 WHEN 값 THEN 결과 ELSE 결과

 

2. GROUPING

• 그룹화 관련된 기능

1) 그룹 함수

• COUNT : 데이터 개수
• SUM : 합계
• AVG : 평균
• MAX : 최대값
• MIN : 최소값
• STDDEV : 표준편차
• VARIANCE : 분산

2) 특징

• NULL은 제외하고 연산
• COUNT를 제외한 모든 함수는 컬럼 이름이나 연산식을 대입해야 하지만 COUNT는 *이 가능
  • COUNT(*) : 모든 컬럼이 NULL인 경우를 제외하고 데이터의 개수를 계산
• SUM과 AVG, STDDEV, VARIANCE 는 문자열에는 사용 못함
• GROUP BY 이후 부터 사용가능
  • HAVING, SELECT, ORDER(실제로는 사용할 필요가 거의 없음)에서 사용 가능
  • 대부분 HAVING, SELECT에서 사용
• SELECT 절에 사용할 때는 대부분 별명과 함께 사용

3) COUNT

#tStaff 의 데이터 개수
SELECT COUNT(*) # 모든 컬럼의 값이 NULL이 아닌 경우의 데이터 개수
FROM tStaff;

SELECT COUNT(score) #score 컬럼에서 NULL을 제외한 개수
FROM tStaff;

• 특별한 경우가 아니라면 COUNT에는 컬럼이름을 잘 사용하지 않음.

4) GROUP 함수에서 NULL을 제외하고 연산

• tStaff 테이블에서 score의 평균 구하기

#18개의 데이터 평균
SELECT ROUND(AVG(score),0)
FROM tStaff;

#COUNT에 *을 사용했기 때문에 20개의 데이터의 평균
#데이터가 NULL인 경우는 0으로 간주
SELECT ROUND(SUM(score)/COUNT(*),0)
FROM tStaff;

5) GROUPING

• SELECT 구문에서 데이터를 그룹화 하고자 할 때 사용하는 절
• WHERE 절 다음에 수행
• 이 절이 수행되어야 그룹 함수를 사용하는 것이 가능 - WHERE 절에서는 그룹 함수를 사용할 수 없음

#EMP 테이블에서 부서별(DEPTNO) 평균 급여(SAL) 조회 :
#그룹화한 항목을 제외하고 출력하면 데이터를 알아보기 어렵기 때문에 그룹화 한 항목과 같이 조회
SELECT DEPTNO, ROUND(AVG(SAL),0)
FROM EMP
GROUP BY DEPTNO;

• 그룹화는 여러 개 가능

SELECT DEPTNO, JOB, ROUND(AVG(SAL),0)
FROM EMP
GROUP BY DEPTNO, JOB ;

• 그룹화 한 후 SELECT 절에서 그룹화 한 항목이나 집계 함수가 아닌 데이터의 조회 (ORACLE은 에러이고 MySQL 이나 MariaDB는 그룹화 한 항목 중 첫번째 데이터 조회

6) HAVING

• GROUP BY 이후의 조건을 설정해서 행 단위로 추출
• 그룹 함수는 WHERE 절에서 사용 불가하기 때문에 그룹 함수를 이용한 조건을 설정할 때는 HAVING에 작성해야 함

#tStaff 테이블에서 depart 별로 그룹화해서 평균 salary가 340이 넘는 부서의 depart와 평균 salary를 조회
SELECT depart, AVG(salary)
FROM tStaff
WHERE AVG(salary) > 340 -- WHERE 자리에는 AVG를 쓸수 없기 때문에 에러가 발생함
GROUP BY depart

#순서를 생각해 볼 필요가 있음
-- 정정 코드
SELECT depart, AVG(salary)
FROM tStaff
GROUP BY depart
WHERE AVG(salary) > 340

#tStaff 테이블에서 depart가 인사과 와 영업부인 데이터를 depart 별로 그룹화해서 depart와 평균 salary를 조회
-- 결과는 나오지만 잘못된 코드
SELECT depart, AVG(salary)
FROM tStaff
GROUP BY depart
HAVING depart IN('인사과', '영업부');
-- 인사과 영업부 총무부를 모두 그룹화 한 후 인사과와 영업부를 추출 (HAVING 이 뒤에 있기 때문임- 불필요한 작업이 동반됨)

SELECT depart, AVG(salary)
FROM tStaff
WHERE depart IN('인사과', '영업부')
GROUP BY depart;
-- 인사과 와 영어부 데이터를 추출한 후 그룹화를 수행
-- 데이터를 필터링 할때는 할 수있으면 빨리하는 것이 좋음
-- 그룹 함수를 이용한 조건이 아니라면 HAVING에 작성하지 말고 WHERE에 작성을 해야 함

7) SELECT 구문

• [5] - SELECT
  [1] - FROM
  [2] - WHERE
  [3] - GROUP BY
  [4] - HAVING
  [6] - ORDER BY
  [7] - LIMIT

 

3. Window 함수

• 행과 행 사이의 관계를 표현하기 위한 함수
• 순위나 누적합 등의 연산을 위한 함수

1) 순위

• 순위 함수로는 동등한 값일 때 어떤 식으로 처리하느 냐에 따라 여러 함수를 제공하고 N 등분한 그룹도 제공해주는 함수
  • RANK, DENSE_RANK, ROW_NUMBER, NTILE 함수 제공
• 함수 뒤에 OVER를 이용해서 순위를 구할 방법을 ORDER BY로 제공해야 함

#EMP 테이블에서 SAL 의 오름차순
SELECT RANK() OVER(ORDER BY SAL ASC), ENAME, SAL
FROM EMP;

#EMP 테이블에서 SAL 의 오름차순 정렬해서 4개의 그룹으로 분할
SELECT NTILE(4) OVER(ORDER BY SAL ASC), ENAME, SAL
FROM EMP;

2) JSON 출력

• 데이터를 조회할 때 JSON_OBJECT로 감싸면 JSON 문자열을 리턴

SELECT JSON_OBJECT('name',ENAME, 'salary',SAL)
FROM EMP;

 

4. SET OPERATOR

1) 개요

• 동일한 테이블 구조를 가진 2개의 테이블을 가지고 수행하는 연산
• 컬럼의 개수가 같아야 하고 컬럼의 자료형이 일치해야함
• 컬럼의 이름이나 테이블의 이름은 아무런 상관이 없음
• 종류로는 UNION, UNION ALL, INTERSECT, EXCEPT(MINUS 인 데이터베이스도 있음)
• 컬럼의 이름은 첫 번째 테이블의 컬럼 이름을 사용
• ORDER BY는 마지막에 한 번만 작성
• 데이터의 자료형이 BLOB, CLOB, BFILE, LONG(데이터의 사이즈가 너무 커서 일치 여부를 판단하는데 시간이 많이 걸리 때문 - 이 자료형들은 INDEX를 생성하지 않음)은 안됨
• 데이터가 분산되어 있는 경우 연산의 결과를 합칠때 사용
• UNION은 중복을 제거하고 ...

데이터가 분산되어 있을 때 처리 방식

1. 데이터를 모은 후 처리
   • 과거의 처리 방식으로 프로세서가 비쌌기 때문에 한곳에 데이터를 모아 처리했음.
2. 데이터를 처리한 후 결과를 모으는 것 : 일반적으로 속도가 빠른 경우가 많음, Map Reduce

2) 형식

• SELECT
  FROM
  ...
  SET 연산자
  SELECT
  FROM
  ...

3) 실습

• DEPT 테이블의 DEPTNO 와 EMP 테이블의 DEPTNO의 합집합

SELECT DEPTNO
FROM DEPT
UNION ALL
SELECT DEPTNO 
FROM EMP;

• DEPT 테이블의 DEPTNO 와 EMP 테이블의 DEPTNO의 교집합

SELECT DEPTNO
FROM DEPT
INTERSECT
SELECT DEPTNO 
FROM EMP;

• DEPT 테이블의 DEPTNO 와 EMP 테이블의 DEPTNO의 차집합

SELECT DEPTNO
FROM DEPT
EXCEPT
SELECT DEPTNO 
FROM EMP;

 

5. SubQuery

1) 개요

• 다른 SQL 구문 안에 포함된 쿼리
• Sub Query는 SELECT 구문
• Sub Query는 반드시 괄호 안에 작성
• Sub Query는 포함하는 Query가 실행되기 전에 한 번만 실행됨

2) 분류

• 위치에 따른 분류
  • FROM 절이 아닌 경우 : Sub Query
  • FROM 절이 사용된 경우 : Inline View
• 리턴되는 데이터에 따른 분류
  • 단일 행 Sub Query : 리턴되는 결과가 하나의 행
  • 다중 행 Sub Query : 2개 이상의 행이 리턴되는 경우

3) 사용하는 경우

• EMP 테이블에서 ENAME이 MILLER 인 사원과 동일한 DEPTNO를 가진 사원의 ENAME을 조회하고자 하는 경우
• EMP 테이블에서 ENAME이 MILLER인 사원의 DEPTNO를 구해야하고 그 다음 그 DEPTNO와 동일한 DEPTNO를 가진 사원의 ENAME을 조회

-- 1 Step : 결과 10
SELECT DEPTNO
FROM EMP
WHERE ENAME = "MILLER";

-- 2 Step
SELECT ENAME
FROM EMP
WHERE DEPTNO = 10;

-- Sub Query 이용
SELECT ENAME
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO 
FROM EMP WHERE ENAME = 'MILLER');

 

4) 실습

• tCity 테이블에서 popu 가 최대인 도시의 name을 조회

# 무심코 에러나기 쉬움
-- popu가 최대인 데이터가 2개 이상 존재하는 경우 1개만 조회
SELECT NAME, MAX(popu)
FROM tCity;

-- 정정 코드
SELECT NAME
FROM tCity
WHERE popu = (SELECT MAX(popu) FROM tCity);

 

• EMP 테이블에서 평균 급여(SAL) 보다 많은 급여를 받는 사원의 이름(ENAME)과 급여(SAL)를 조회

SELECT ENAME, SAL
FROM EMP 
WHERE SAL > (SELECT AVG(SAL) FROM EMP);

 

5) 다중 열 Sub Query

• Sub Query의 결과가 1개의 컬럼이 아니고 여러 개의 컬럼인 경우
• tStaff 테이블에서 name이 안중근 인 데이터와 depart와 gender가 일치하는 데이터를 조회

SELECT *
FROM tStaff
WHERE (depart,gender) = 
(SELECT depart,gender FROM tStaff WHERE name='안중근');

 

6) 다중 행 Sub Query

• Sub Query에서 리턴되는 결과가 2개 이상의 행인경우
  • 이 경우 =, <> 는 사용이 안되고 >, >=, <, <= 도 단독으로 사용안됨
    • =, <> 는 하나의 데이터와 비교 가능한 단일행 연산자 이기 때문임
  • 이 경우 IN이나 NOT IN 그리고 ANY 와 ALL 같은 다중 행 연산자를 사용 해야함
    • ANY와 ALL은 MAXdhk MIN 함수로 대체가 가능함
• EMP 테이블에서 DEPTNO 별로 그룹화 해서 각 그룹의 SAL이 최대인 데이터와 일치하는 SAL을 가진 데이터의 ENAME과 SAL을 조회

#에러 = 서브쿼리의 결과는 3개인데 = 로 비교해서 에러
SELECT ENAME, SAL
FROM EMP
WHERE SAL = (SELECT MAX(SAL)FROM EMP GROUP BY DEPTNO);
-- ERROR : SQL Error [1242] [21000]: (conn=5) Subquery returns more than 1 row

-- 정정 코드 (IN)
SELECT ENAME, SAL
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)FROM EMP GROUP BY DEPTNO);

#EMP 테이블에서 DEPTO가 30인 데이터의 모든 SAL 보다 많은 SAL을 받는 직원의 ENAME과 SAL을 조회
-- ERROR
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL FROM EMP WHERE DEPTNO = 30);

-- 정정 코드(1) (ALL)
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO = 30);

-- 정정 코드(2) (MAX)
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);

>> SubQuery 연습문제는 PPT

 

6. JOIN

1) 개요

• 2개 이상의 테이블을 합쳐서 하나의 테이블을 만드는 것
  • 2개의 테이블이 동일한 테이블 일 수도 있음
• 조회하고자 하는 데이터가 2개 이상의 테이블에 나누어져 있거나 하나의 테이블에서 2번 이상 찾아야 하는 경우 사용

2) 종류

• Cartesian Product : Cross Join 이라고도 하는데 단순하게 2개 테이블의 모든 조합을 만들어내는 것
• Equi Join : 양쪽 테이블에 동일한 의미를 갖는 컬럼이 존재할 때 2개의 컬럼의 값이 일치하는 경우에만 결합하는 것으로 Inner Join 이라고도 함
• Non Equi Join : Equi Join과 유사하지만 2개의 컬럼의 값이 일치하지 않는 조건으로 결합하는 것
• Outer Join : Equi Join과 유사하지만 한 쪽 테이블에만 존재하는 데이터도 Join에 참여하는 것
• Self Join : 동일한 테이블끼리 Join 하는 것으로 하나의 테이블에 동일한 의미를 갖는 컬럼이 2개이상 존재할 때 사용
• Semi Join : Sub Query를 이용해서 Join

3) Cross Join - Cartesian Product

• 양쪽 테이블의 모든 데이터 조합을 만들어 내는 것
• 컬럼의 수는 양쪽 테이블의 컬럼의 수의 합이 되고 행의 수는 양쪽 행의 수의 곱
• FROM 절에 테이블 이름만 나열하고 Join 조건을 기재하지 않는 경우 
  • EMP 테이블은 14개의 행으로 구성되어 있고 8개의 컬럼으로 만들어져 있음
  • DEPT 테이블은 4개의 행으로 구성되어 있고 3개의 컬럼으로 만들어져 있음
  • Cross Join 의 결과는 11개의 컬럼과 56개의 행으로 만들어 짐.

SELECT *
FROM EMP, DEPT;

 

4) Equi Join

• 양쪽 테이블의 동일한 의미를 갖는 컬럼의 값이 일치하는 경우에만 Join을 수행
  • 동일한 의미를 갖는 컬럼이 Foreign Key 이면 Join의 성능은 좋아짐
• WHERE 절에 Join 조건을 기재하면 됨
• 양쪽 테이블에 동일한 이름의 컬림이 존재하는 경우는 컬럼 이름이 중복되기 때문에 앞에 테이블 이름 
  • EMP 테이블의 DEPTNO는 부서 번호이고 DEPT 테이블의 DEPTNO도 부서번호

-- ERROR : 동일한 컬럼의 이름이 존재하여 에러
SELECT *
FROM EMP, DEPT
WHERE DEPTNO = DEPTNO;

-- 정정 코드
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

• JOIN을 한 후 조건을 가지고 데이터를 조회하는 경우 JOIN 조건을 먼저 기재
  • ENAME이 MILLER 인 사원의 DNAME 과 ENAME을 조회
  • 이 경우, DNAME은 DEPT 테이블에 존재하고
  • ENAME은 EMP 테이블에 존재함
  • 따라서, 이 경우는 JOIN으로 해결해야 함
  • 만약, 조건이 복잡하지만 조회해야 하는 컬럼이 하나의 테이블에 존재한다면 Sub Query로 해결 가능함
  • 또한, 동일한 문제를 Sub Query로 해결할 수 있다면 Sub Query를 사용해야 함
  • 관계형 데이터 베이스의 단점? 빈번한 JOIN으로 메모리를 많이 사용

#JOIN 조건을 먼저
SELECT DNAME, ENAME
FROM DEPT, EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO AND ENAME='MILLER';

-- AND는 자리가 바껴도 결과가 같음. 특별한 경우가 아닌 경우는 위의 방식으로 사용

SELECT DNAME, ENAME
FROM DEPT, EMP
WHERE ENAME = 'MILLER' AND DEPT.DEPTNO = EMP.DEPTNO;

• JOIN에서 테이블의 순서
  • 선행 테이블의 조건을 설정해서 데이터를 추출한 후 후행 테이블의 데이터를 결합하는 방식
  • 조건을 확인하여 되도록이면 데이터의 추출 개수가 적은 테이블을 선행 테이블로 사용해야 함
  • 한쪽 테이블에만 적용되는 조건이라면 그 조건이 적용되어야 하는 테이블을 먼저 기재하는 것이 좋음

앞의 문제에서 조건 중에 ENAME을 가지고 조회하는 조건이 있으므로 JOIN을 할 때 ENAME을 포함하고 있는 EMP 테이블을 선행 테이블로 사용하는 것이 효율적

SELECT DNAME, ENAME
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO AND ENAME='MILLER';

5) NON EQUI JOIN

• JOIN 조건이 = 가 아닌 경우
  • EMP 테이블의 SAL은 급여이고 SALGRADE 테이블의 GRADE는 급여 등급이고 LOSAL은 등급의 최저 급여이고 HISAL은 등급의 최고 급여임
  • SAL은 LOSAL이나 HISAL과 일치 하지 않을 수 있음
  • SAL이 LOSAL과 HISAL 사이의 값일 경우의 GRADE를 조회
• 다른 테이블의 데이터와 비교를 할 때 = 연사자가 아닌 것으로 비교를 하면 NON EQUI JOIN이라고 함
• • 각 사원의 이름(ENAME - EMP)과 GRADE(SALGRADE)를 조회

SELECT ENAME, GRADE
FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;

6) SELF JOIN

• 동일한 테이블을 가지고 JOIN
• 하나의 테이블에 동일한 의미를 갖는 컬럼이 2개 이상 존재할 때 사용함
  • EMP 테이블은 사원 번호에 해당하는 EMPNO가 있고 관리자 사원 번호에 해당하는 MGR 이 있음.
  • 이 경우, SELF JOIN이 가능함
• SNS(친구 추천) 나 인력 배치 와 같은 인사관리 시스템에 많이 사용됨.
• 사원 이름과 관리자의 사원 이름을 조회
  • 사원번호 →  관리자의 사원번호 →  관리자의 이름 순 (SELF JOIN 필요)
  • 이 경우 JOIN을 할 때 2개의 동일한 이름의 테이블을 사용해야 하기 때문에 반드시 테이블 이름을 변경해서 사용해야 함.

SELECT e1.ENAME '사원 이름',e2.ENAME '관리자 이름'
FROM EMP e1, EMP e2 #EMP 이름이 없어지고 e1,e2로 생성
WHERE e1.MGR = e2.EMPNO; #e1 이 자신의 정보, e2가 관리자의 정보

• FRIEND 테이블이 있고 이 테이블에는 ID와 FRIENDID(친구 아이디)로 구성된 경우 친구의 친구 찾기

SELECT f2.FRIENDID
FROM FRIEND f1, FRIEND f2
WHERE f1.FRIENDID =
f2.ID AND f1.ID != # 나 자신은 빼기
f2.FRIENDID AND NOT IN(SELECT FRIENDID FROM FRIEND WHERE f1.ID) #겹치는 친구는 빼기

7) ANSI(미국 표준 협회) JOIN

• CROSS JOIN을 할 때 FROM 절에 CROSS JOIN을 기재함

SELECT *
FROM EMP CROSS JOIN DEPT;

• Equi Join 대신에 INNER JOIN이라는 표현을 사용
  • WHERE 절에 JOIN 조건을 적지않고 WHERE 절 앞에 ON을 추가
  • ON 절에 JOIN 조건을 기재

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO

-- 동일한 결과

SELECT *
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO

양쪽 테이블에 컬럼 이름이 동일한 경우에는 ON 절 대신에 USING(컬럼이름)을 사용하는 것도 가능함.

양쪽 테이블의 동일한 컬럼은 한 번만 조회됨. ↓

SELECT *
FROM EMP INNER JOIN DEPT
USING(DEPTNO)

양쪽 테이블의 컬럼이름이 동일하다면 USING 절도 생략하고 INNER JOIN 대신에 NATURAL JOIN이라고 기재 ↓

SELECT *
FROM EMP NATURAL JOIN DEPT;

 

8) OUTER JOIN

• 한쪽 테이블에만 존재하는 데이터도 JOIN에 참여하는 것
• MariaDB에서는 LEFT OUTER JOIN과 RIGHT OUTER JOIN 만 지원
• FULL OUTER JOIN은 지원하지 않기 때문에 UNION 연산을 이용해서 수행
• 다른 테이블에 존재하지 않는 데이터는 그 테이블에 모든 컬럼의 값을 NULL로 설정함

EMP 테이블에는 DEPTNO가 10, 20, 30 만 존재하고 DEPT 테이블에는 DEPTNO 가 10, 20, 30, 40이 존재함

SELECT *
FROM EMP RIGHT OUTER JOIN DEPT
USING(DEPTNO)

DEPT 테이블에만 존재하는 40번도 JOIN에 참여하게 됨

SELECT *
FROM EMP LEFT OUTER JOIN DEPT
ON EMP.DEPTNO =DEPT.DEPTNO

UNION

SELECT *
FROM EMP RIGHT OUTER JOIN DEPT
ON EMP.DEPTNO =DEPT.DEPTNO;

 

9) 다중 JOIN

• 3개 이상의 테이블도 JOIN 가능
  • 2개 테이블을 JOIN을 하고 난 후 다른 테이블과 JOIN을 하는 형식

10) JOIN 시 주의사항

• JOIN은 메모리를 많이 사용하기 때문에 최대한 자제해야 함
• 관계형 데이터베이스의 최대 단점이 빈번한 JOIN의 문제이기 때문
• JOIN 없이 해결할 수 있는 문제는 JOIN없이 해결 해야함
• 반드시 JOIN이 필요한 경우는 SELECT 절에 기재하는 조회하고자 하는 컬럼이 2개 이상의 테이블에 존재하는 경우뿐임

그 이외의 경우는 대부분 Sub Query로 해결이 가능하고 Sub Query를 사용하면 SQL이 길어질 가능성이 높아짐.

---

DDL (Data Definition Language)

• 데이터 구조를 생성하고 변경하고 삭제하는 명령어

1. 테이블 생성

1) 기본형식

Create [Temporary] Table 테이블이름(컬럼이름 자료형 [컬럼 제약 조건], ... [테이블 제약 조건])조건나열;

2) 자료형

• 숫자 : TINYINT(1Byte - true/false), INT(INTEGER), FLOAT, DOUBLE
• 문자 : CHAR(길이 - 길이가 고정), VARCHAR(길이 - 길이가 가변), TEXT(긴 문자열), BLOB(파일의 내용 저장)
• 날짜 : DATE(날짜), DATETIME(날짜와 시간), TIMESTAMP(날짜와 시간 - 2037년 까지 밖에 저장하지 못함), TIME(시간), YEAR(년도)
• 기타 : JSON, GEOMETRY(공간 정보)

<파일을 데이터베이스에 저장하는 방법>

• 파일의 경로를 저장하는 방법 : 파일을 별도로 저장하고 그 경로를 저장
• 파일의 내용을 저장하는 방법 : 파일을 별도로 저장하지 않고 데이터베이스에 저장 (BLOB)

3) 조건 나열

• ENGINE : MyISAM(Indexed Sequential Access Media - 삽입,삭제, 갱신에 불리하고, 조회에 유리함) 이나 InnoDB(삽입, 삭제, 갱신에 유리)를 설정할 수 있음
• DEFAULT CHARSET : 한글이 깨지는 경우 한글 설정하는 옵션으로 utf8을 설정해주면 되는데 MariaDB는 기본이 utf8
• auto_increament = 시작숫자 : 일련번호를 사용할 때 시작 숫자부터 시작함
• Timezone 설정 : Mac에서 사용할 때 시간 대역이 안맞아서 설정해주어야 하는 경우가 있음

- CHAR VARCHAR 비교-