STUDY AND LESSON

JOIN을 할 때 조건으로 어떤 컬럼과 일치시켜 줄 것인가에 대해 ON이나 USING을 사용하는데 두 키워드의 차이점을 설명드리겠습니다.

EMPLOYEE 테이블과 SALARY 테이블이 있고 이 두 테이블에 모두 DEPT_ID라는 컬럼이 있어서 이 컬럼을 기준으로 조인을 해주게 되면 아래와 같이 작성하면 됩니다.

SELECT *
FROM EMPLOYEE e
JOIN SARALY s
ON e.DEPT_NO = s.DEPT_NO

그런데 이처럼 조인할 두 컬럼의 이름이 같은 경우에는 ON 대신에 USING을 사용할 수 있고 USING 사용 시 다음과 같이 작성하시면 됩니다.

SELECT *
FROM EMPLOYEE e
JOIN SALARY s
USING(DEPT_NO)

* 주의할 점은 두 컬럼의 이름이 같을 때만 동작한다는 것입니다.

GROUP BY

GROUP BY는 특정 컬럼을 기준으로 그룹화 하는 명령어로 그룹화를 통해 통계 데이터를 추출할 수 있습니다.

SELECT 컬럼
FROM 테이블명
WHERE 조건
GROUP BY 그룹화할 컬럼
HAVING 그룹화할 조건

부서에서 연봉이 5000 이상인 정보를 추출

SELECT
DEPT.NAME,
COUNT(EMP.ID),
SUM(EMP.SALARY),
AVG(EMP.SALARY),
MIN(EMP.SALARY),
MAX(EMP.SALARY)
FROM EMPLOYEE EMP
JOIN DEPARTMENT DEPT ON (DEPT.ID = EMP.DEPT_ID)
GROUP BY DEPT.ID
HAVING AVG(EMP.SALARY) >= 5000

중복된 데이터를 제거하고 싶을 때는 DISTINCT를 사용합니다.

아래는 학생 테이블에서 학과를 하나씩만 출력하는 쿼리입니다.

SELECT DISTINCT(DEPARTMENT) FROM STUDENT;

날짜를 이용하여 주문번호를 생성하는 SQL문을 만들어보겠습니다.

SQL문

SQL문은 다음과 같이 작성합니다.

select concat(cast(d.receiptDate as char), right(concat("00000" , rowCount + 1),  6)) as receiptNo,
       count(e.cntNo) + 1 as cntNo, rowCount + 1 as rowCount
from tn_exp_hall as e, 
     (select concat(substr(date_format(now(), '%Y-%m-%d'), 1, 8)) as receiptDate,
     (select cntNo as cnt from tn_exp_hall order by cntNo desc limit 1) as rowCount FROM dual) d;

주문번호 생성 SQL문 분석

우선 SQL문에서 사용된 함수들은 다음과 같습니다.

concat : 문자열 결합

cast : 형변환

right : 문자열 자르기 * 오른쪽에서부터 입력한 숫자만큼 자름 -> right("안녕하세요", 3) => 결과 : 하세요.

count() : 괄호 안에 들어간 컬럼의 갯수 반환

substr : 문자열 자르기 * 문자열을 인덱스 단위로 자름

date_format() : 날짜 타입의 포맷 변환

위 SQL문에서 결과로 세 가지를 가져왔습니다.

첫번째, 생성한 주문번호

두번째, 총 데이터 갯수

세번째, Primary Key(기본키)의 마지막 값

주문번호 생성 과정

예제 날짜) 2021-10-05

1) 현재 날짜를 가져와서 문자열로 변환

결과 : 2021-10-05

2) 문자열로 변환한 현재 날짜 '년월일' 중에서 '일' 부분만 제거

결과 : 2021-10-

3) 지금 테이블에 있는 기본키 마지막 값을 가져옴 * 주의 : count()로 가져올 시 중간에 삭제된 데이터가 있을 경우 기본키 중복이 발생할 수 있습니다.

결과 : 현재 테이블에 있는 기본키의 마지막 값 * 여기에서는 37이라고 하겠습니다.

4) 가져온 기본키 마지막 값에 1을 더하고 앞에 문자열 '00000'을 결합

결과 : 0000037

5)  바로 위 '4)' 결과에서 오른쪽 6칸을 자름

결과 : 000037

6) '2)' 결과에 '5)' 결과를 결합

결과 : 2021-10-000037

실행결과

다음에 보이는 컬럼은 주문번호, 데이터 수, 마지막 기본키가 갖고 있는 값입니다.