[DATABASE] MySQL SELECT문

1. JOIN

가. INNER JOIN

• INNER 조인은 우리가 생각하는 일반적인 용도에 사용한다.
• A,B 테이블 중에 A의 KEY값과 B KEY값이 같은 결과만을 JOIN 한다.
• 즉, A KEY와 B KEY의 값이 일치 하지 않는다면 JOIN을 하지 않겠다는 뜻이다.

SELECT mv.movie_nm, si.people_nm
FROM movie AS mv JOIN stars_in AS si
ON mv.MOVIE_UD = si.movie_ud;

• ON 대신 WHERE를 사용 할 수 있다.
• INNER 조인은 MySQL에서는 간략히 JOIN으로 나타낸다. 일반적으로 사용하는 JOIN이다.
• 핵심은 JOIN뒤에 ON인데, 두 테이블이 결합하는 조건을 나타낸다.
• 두 테이블 모두 movie_ud를 가지고 있으며, 서로 모두 포함하는 레코드를 합쳐서 표현한다.

INNER JOIN의 집합 표현

나. LEFT JOIN와 RIGHT JOIN

SELECT mv.movie_nm, si.people_nm
FROM movie AS mv LEFT JOIN stars_in AS si
ON mv.MOVIE_UD = si.movie_ud;
 
SELECT mv.movie_nm, si.people_nm
FROM movie AS mv RIGHT JOIN stars_in AS si
ON mv.MOVIE_UD = si.movie_ud;

LEFT JOIN과 RIGHT JOIN의 집합 표현

다. OUTER JOIN

• MySQL에서는 OUTER JOIN을 지원하지 않는다. 하지만 유사한 처리는 가능하다.

OUTET JOIN의 집합 표현

2. GROUP BY ... HAVING ...

• GROUP BY
  • GROUP BY는 특정 컬럼 이름을 지정해주면 그 컬럼의 UNIQUE한 값에 따라서 데이터를 그룹 짓고, 중복된 열을 제거한다.
  • 보통 집합(집계) 함수와 같이 쓰인다.
  • GROUP BY로 그룹화하지 않은 컬럼은 SELECT 해도 정확한 데이터가 나오지 않기 때문에 그룹화해서 사용한다.
  • 집합(집계) 함수는 다음과 같다.
    • count(필드명) : null 값이 아닌 레코드의 개수
    • sum(필드명) : 필드명의 값들의 합계
    • avg(필드명) : 필드명의 값들의 평균
    • max(필드명) : 필드명의 값들 중 최대값
    • min(필드명) : 필드명의 값들 중 최소값
    • count(*) : 테이블에 속하는 레코드의 개수

SELECT open_dt, count(MOVIE_NM)
FROM movie
where open_dt > '2019-01-01'
group by open_dtd;

• HAVING
  • GROUP BY 절에서는 조건을 주려면 WHERE가 아닌, HAVING 절을 사용해야 한다.
  • SELECT 실행 순서를 보면, WHERE 절이 GROUP BY 보다 먼저 실행되기 때문에, GROUP BY에 대응된다.

3. 연산자의 종류

가. IN 연산자

• 리턴되는 값 중에서 조건에 해당하는 값이 있으면 참이다.
• 서브쿼리가 리턴하는 행중에서 어느 하나라도 만족하는 경우에 결과를 리턴한다.

-- Q) 부서번호가 20인 부서의 사원들과 같은 업무를 가진 사원들의 이름
-- SQL> 
 
select  p.ename, p.job, e.ename
from emp p, emp e
where p.mgr=e.empno
AND p.job IN ( select job 
                from emp 
                  where deptno=20);

나. ANY 연산자

• 서브쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 하나 이상을 만족하면 참이다.
• ANY는 OR의 개념과 유사하다.
  • ANY 연산자는 IN 연산자와 달리 어떤 특정한 값이 아닌 범위로 비교연산을 처리한다.
  • ANY 연산자는 서브쿼리에서 리턴되는 어떠한 값이라도 만족을 하면 조건이 성립한다.

--Q) 업무가 manager인 어떤 사원보다 급여를 많이 받는 다른 업무의 사원을 출력
--    (단, 업무가 manager인 사원은 제외)
 
SQL> 
select empno,ename,job,sal 
from emp 
where sal > ANY ( select sal 
                    from emp 
                  where job='MANAGER') AND job <> 'MANAGER';
 
 
 
--Q) 부서번호가 30인 어떤 사원보다도 먼저 입사한 사원을 출력
--    (단, 부서번호 30인 사원은 제외)
 
SQL> 
select ename,hiredate,deptno,sal from emp
where hiredate < ANY ( select hiredate 
                       from emp 
                          where deptno=30) AND deptno <>30;

다. ALL 연산자

• 값을 서브쿼리에 의해 리턴되는 모든 값과 조건값을 비교하여 모든 값을 만족해야만 참이다.
• ALL은 AND의 개념과 유사하다.
  • ALL 연산자는 IN 연산자와는 달리 어떤 특정한 값이 아닌 범위로 비교연산을 처리한다.
  • ALL 연산자는 서브쿼리에서 리턴되는 모든 값을 만족하면 조건이 성립된다.

--Q) job이 analyst인 모든 사원보다 급여를 많이 받는 타 업무의 사원을 출력
--   (단, 업부가 clerk인 사원은 제외)
 
SQL> 
select empno,ename,job,sal 
from emp
where sal > ALL ( select sal 
                  from emp 
                  where job='ANALYST') AND job <> 'CLERK';
 
 
 
--Q) 부서번호가 30인 사원보다 먼저 입사한 사원을 출력
--    (단, 부서번호 30인 사원은 제외)
 
SQL> 
select ename,hiredate,deptno 
from emp
where hiredate < ALL ( select hiredate 
                       from emp 
                          where deptno=30) AND deptno <> 30;

라. EXISTS 연산자

• 메인 쿼리의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참이다.
• 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이다.
• NOT EXISTS는 서브쿼리에서 검색된 결과가 하나도 존재하지 않으면 메인쿼리 조건절은 참이다.

SQL> 
select empno,ename,sal,comm 
from emp 
where EXISTS ( select empno 
                from emp 
                where comm IS NOT NULL);

 

4. 이전 날짜 또는 이후 날짜를 사용하는 쿼리문 작성 방법

• 오늘, 즉 기준이 되는 날짜가 항상 달라질 수 있을 것이다. 기준이 오늘 날짜를 먼저 알아야 한다.
• 프로그램 상에서 매일 날짜를 바꿔 줄 수 없기 때문에 함수를 이용한다.

Q) 오늘 날짜 구하는 법(php언어에서 오늘 날짜를 구하는 방법이다.)
  -> $today = date("Y-m-d H:i:s", time());

Q) NOW() 함수를 SQL문에 사용한 방법
  -> SELECT * FROM date = NOW();

Q) 한달 이전부터 오늘까지의 데이터 불러오는 예제
  -> SELECT * FROM tableName WHERE date > (NOW() - INTERVAL 1 MONTH);

Q) 일주일 이전부터 오늘까지의 데이터만 불러오는 예제
  -> SELECT * FROM tableName WHERE date > (NOW() - INTERVAL 7 DAY);

Q) 3년 이후의 데이터만 쿼리로 불러올 경우의 예제
  -> SELECT * FROM tableName WHERE date > (NOW() - INTERVAL 3 YEAR);

 

• 추가적으로 만약 시간, 분, 초 단위로 원하는 데이터만 불러올 경우에는?
  • INTERVAL 뒤에 추가하였던 YEAR을 MONTH. DAY, MINUTE, SECOND로 변경해주면 된다.

5. DISTINCT

• 중복된 결과를 제거하고 하나만 출력하고자 할 때 사용하는 문법이다.

6. INTO

• 동일한 테이블의 형태를 복사하여 만드는데 사용하는 문법이다.
• SELECT * INTO A_BACKUP FROP A;
  • 위 구문을 실행하게 되면 A_BACKUP이라는 테이블은 존재하지 않지만 실행을 하게 되면 A라는 테이블의 칼럼과 데이터를 가지고 A_BACKUP이라는 테이블이 생성된다.

7. AS

• SELECT문에서 AS를 이용하면 속성값을 별칭으로 지정해서 사용 할 수 있다.

FROM stars_in as si inner join movie as mv

 

8. LIKE

• 특정 문자열이 포함된 데이터를 검색 할 때 사용한다.
• SELECT [칼럼명] FROM [테이블명] WHERE [칼럼명] LIKE '특정문자열%; 

SELECT *
FROM movie
WHERE SYNOPSIS LIKE '%한국%'

 

9. BETWEEN

• 말 그대로 사이에 있는 값에 해당하는 데이터를 검색 할 때 사용한다.
• BETWEEN a AND b 에서 a와 b를 포함한 그 사이의 값에 해당하는지 여부를 조건으로 한다.

SELECT *
FROM movie
WHERE open_dt BETWEEN '2018-01-01' and '2019-09-05'

 

10. ORDER BY

• 해당 필드를 기준으로 정렬을 하고자 할 때 사용한다.
• IN과 같은 연산자에서도 ORDER BY FIELD()를 사용하면 순서대로 값을 얻어올 수 있다.
• ASC(오름차순), DESC(내림차순) 정렬

select MOVIE_NM
from movie
where REP_NATION_NM="한국" and show_Tm < 100
order by MOVIE_NM

 

11. UNION

• 여러개의 SELECT문의 결과를 하나의 테이블이나 결과 집합으로 표현할 때 사용한다. 이 때 각각의 SELECT 문으로 선택된 필드의 개수와 타입은 모두 같아야 하며, 필드의 순서 또한 같아야 한다.
• UNION은 몇개라도 계속해서 연결 할 수 있다.
• 유니온의 규칙
  • 하나의 ORDER BY만 사용할 수 있다.
  • 각 SELECT의 열수, 표현식이 같아야 한다.
  • SELECT 문들끼리 순서는 상관없다.
  • 유니온을 한 결과가 중복되면 하나만 나온다.
  • 열의 타입은 같거나 반환 가능한 형태여야 한다.
  • 중복값을 나타내고 싶다면 UNION ALL 을 사용해야 한다.

SELECT MOVIE_NM FROM movie
UNION
SELECT PEOPLE_NM FROM movie_people

 

12. Case

• MySQL 에서 CASE문은 프로그래밍 언어에서 스위치(switch)문과 비슷하지만, 다수의 조건에 하나의 반환 값은 동작하지 않는다.
• WHEN과 THEN은 한쌍이어야 한다.
• WHEN과 THEN은 다수가 존재할 수 있다.
• ELSE가 존재하면 모든 조건에 해당하지 않는 경우에 반환 값을 설정 할 수 있다.
• ELSE가 존재하지 않고, 조건에 맞지 않아서 반환 값이 없으면 NULL를 반환한다.

select MOVIE_NM, PRDT_DATE,
    CASE
    WHEN VIEWS = '0'
    THEN '빵명'
    WHEN VIEWS = '1'
    THEN '한명'
    WHEN VIEWS = '2'
    THEN '두명'
    ELSE '다수명'
    END AS VIEWS_NUM
from movie

 

13. SYSDATE(), NOW()

• SYSDATE() : 트랜잭션이나 쿼리 단위에 전혀 관계 없이, 그 함수가 실행되는 시점의 시각을 리턴한다.
  • 만약 이 테이블의 레코드가 너무 많아서 처음 레코드부터 끝까지 스캔하는데 1시간이 걸린다면, 처음 레코드를 비교할 때의 값과 마지막 레코드를 비교할 때의 SYSDATA()의 값은 1시간의 차이가 발생하게 될 것이다.
• NOW() : 하나의 쿼리 단위로 동일한 값을 리턴하게 된다.
  • SYSDATA()와는 다르게 처음 레코드부터 끝까지 스캔 하는데 1시간이 걸린다고 하더라고 모든 레코드의 값은 동일 할 것이다.
• 즉, NOW() 함수는 상수이지만, SYSDATE() 함수는 상수가 아닌 것이다. 이로 인해서, 전체적인 쿼리의 실행 계획을 바꿔버리게 된다.
• 이를 방지하기 위해서는 아래와 같은 방법을 사용하면 된다.
  • SYSDATE()의 사용 금지
  • 또는 --sysdate-is-now 옵션을 설정하여 MySQL Server 기동
• --sysdate-is-now 옵션이 활성화되면, SYSDATE()는 NOW() 함수와 동일하게 작동하게 된다.

 

 

 

2019.09.05(목)

MySQL SELECT문