쿼리 기본

1. 쿼리 동작 순서

1. 서버는 전송된 쿼리가 실행 되기 전 아래 3가지를 확인함
   • 전송된 쿼리 구문을 실행할 권한이 있는가
   • 원하는 데이터에 접근할 권한이 있는가
   • 문법이 정확한가
2. 쿼리 옵티마이저(쿼리 실행 시 가장 효율적인 방법을 결정)로 쿼리를 전달
   • FROM절에 명명된 테이블에 조인할 순서 및 사용가능한 인덱스를 확인
   • 서버가 쿼리 실행에 필요한 실행계획을 선택함
   • 서버가 쿼리 실행을 마치면 호출한 툴로 결과셋을 반환

2. 쿼리절

• SELECT : 쿼리 결과에 포함할 열 결정
• FROM : 검색할 테이블과 조인하는 방법을 식별
• WHERE : 불필요한 데이터를 필터링
• GROUP BY : 공통 열 값을 기준으로 행을 그룹화
• HAVING : 불필요한 그룹을 필터링
• ORDER BY : 하나 이상의 열을 기준으로 행을 정렬

1) SELECT절

* : 모든 열의 결과값을 가져옴

• ‘*’을 사용하지 않고 원하는 컬럼만을 지정하여 결과값을 가져올 수 있음

     SELECT * FROM LANGUAGE;

     SELECT LANGUAGE_ID, NAME 
     FROM LANGUAGE;

• 리터럴(숫자 혹은 문자열) 표현식, 내장함수 등 또한 SELECT절에 사용 가능

• Alias(별칭)
• SELECT LANGUAGE_ID , 'COMMON' LANGUAGE_USAGE , LANGUAGE_ID * 3.14 AS LANG_PI_VALUE , UPPER(NAME) LANGUAGE_NAME FROM LANGUAGE;
• DISTINCT : 중복제거
• SELECT ACTOR_ID FROM FILM_ACTOR ORDER BY ACTOR_ID; -- 한 편 이상의 영화에 출현한 배우들이 있기 때문에 동일한 배우의 ID가 중복되서 출력됨 SELECT DISTINCT ACTOR_ID FROM FILM_ACTOR ORDER BY ACTOR_ID;

2) FROM절

FROM절에는 쿼리에 사용될 테이블 뿐만 아니라 테이블을 서로 연결하는 수단도 함께 정의함

• 영구 테이블 : CREATE TABLE문으로 생성
• 파생 테이블 : 하위 쿼리에서 반환하고 메모리에 보관된 행
• 서브쿼리는 다른 쿼리에 포함된 쿼리이지만 FROM절에서 사용되는 경우, FROM절에 명시된 다른 테이블과 상호작용할 수 있는 파생테이블의 역할을 함

    SELECT CONCAT(CUST.LAST_NAME, ' ', CUST.FIRST_NAME) AS FULL_NAME
    FROM (SELECT FIRST_NAME, LAST_NAME, EMAIL
          FROM CUSTOMER 
          WHERE FIRST_NAME = 'JESSIE'
    )CUST;

• 임시 테이블 : 메모리에 저장된 휘발성 데이터
  • 영구테이블처럼 보이지만 트랜잭션이 끝나거나 DB세션이 종료될 때 사라지는 데이터와 테이블

    CREATE TEMPORARY TABLE ACTORS_J(
            ACTOR_ID SAMLLINT(5)
        , FIRST_NAME VARCHAR(45)
        , LAST_NAME VARCHAR(45)
    );

    INSERT INTO ACTORS_J
        SELECT ACTOR_ID, FIRST_NAME, LAST_NAME
        FROM ACTOR
        WHERE LAST_NAME LIKE 'J%';

    SELECT * FROM ACTOR_J;
    -- ACTOR_J의 데이터 값들은 메모리에 일시적으로 저장되고, 세션이 종료되면 사라짐

• 가상 테이블(뷰) : CREATE VIEW문으로 생성
  • 데이터 딕셔너리에 저장된 쿼리로, 테이블처럼 동작하지만 뷰에 저장된 데이터는 존재하지 않음
  • → 뷰에 대해서 쿼리를 실행하면 쿼리가 뷰 정의와 합쳐져서 실행할 최종 쿼리를 만드는 것

    CREATE VIEW CUST_VW AS
    SELECT CUSTOMER_ID, FIRST_NAME, LAST_NAME, ACTIVE
    FROM CUSTOMER;

    -- 뷰를 작성하더라도 데이터가 추가로 생성되거나 저장되지 않음
    -- 뷰를 생성한 후 다음에 사용할 때는
    SELECT FIRST_NAME, LAST_NAME
    FROM CUST_VW
    WHERE ACTIVE = 0;

• 테이블 연결

    SELECT CUST.FIRST_NAME, CUST.LAST_NAME, TIME(R.RENTAL_DATE) AS RENTAL_TIME
    FROM CUSTOMER CUST
    INNER JOIN RENTAL R
                ON CUST.CUSTOMER_ID = R.CUSTOMER_ID
    WHERE DATE(R.RENTAL_DATE) = '2005-06-14';

• FROM절에 테이블이 2개 이상 있는 경우, 그 테이블들을 연결하는데 필요한 조건도 포함해야 함

3) WHERE절

결과 셋에 출력하고 싶지 않은 데이터들을 필터링 하는 매커니즘

SELECT TITLE 
FROM FILM
WHERE RATING = 'G' AND RENTAL_DURATION >= 7;

SELECT TITLE 
FROM FILM
WHERE RATING = 'G' OR RENTAL_DURATION >= 7;

4) GROUP BY와 HAVING절

SELECT C.FIRST_NAME, C.LAST_NAME, COUNT(*)
FROM CUSTOMER C
INNER JOIN RENTAL R
            ON C.CUSTOMER_ID = R.CUSTOMER_ID
GROUP BY C.FIRST_NAME, C.LAST_NAME
HAVING  COUNT(*) >= 40;

5) ORDER BY절

열 데이터를 기반으로 표현식을 사용하여 결과셋을 정렬하는 매커니즘

SELECT C.FIRST_NAME, C.LAST_NAME , TIME(R.RENTAL_DATE) RENTAL_DATE
FROM CUSTOMER C
INNER JOIN RENTAL R
            ON C.CUSTOMER_ID = R.CUSTOMER_ID
WHERE DATE(R.RENTAL_DATE) = '2005-06-14'
ORDER BY C.LAST_NAME, C.FIRST_NAME;

-- ASC, DESC
-- 1, 2, 3과 같이 열의 나열 순서로 ORDER BY 할 수도 있음

연습문제

-- 3-1
SELECT ACTOR_ID, FIRST_NAME, LAST_NAME
FROM ACTOR 
ORDER BY LAST_NAME, FIRST_NAME

-- 3-2
SELECT *
FROM ACTOR
WHERE LAST_NAME = 'WILLIAMS' 
OR LAST_NAME = 'DAVIS';

-- 3-3
SELECT DISTINCT CUSTOMER_ID
FROM RENTAL
WHERE DATE(RENTAL_DATE) = '2005-07-05'
ORDER BY CUSTOMER_ID;

-- 3-4
SELECT C.EMAIL, R.RETURN_DATE
FROM CUSTOMER C
INNER JOIN RENTAL R
            ON C.CUSTOMER_ID = R.CUSTOMER_ID
WHERE DATE(R.RENTAL_DATE) = '2005-06-14'
ORDER BY R.RETURN_DATE DESC;

SELECT RENTAL_ID, CUSTOMER_ID, RETURN_DATE
FROM RENTAL
WHERE RETURN_DATE IS NOT NULL AND CUSTOMER_ID = 1
ORDER BY CUSTOMER_ID;