RADIANT GARDEN

최근 게시글

02_03 SELECT·DML 기초 쿼리 실습

11 min read

하위 태스크 1

Chapter4 예제 실행

04-1.sql ~ 04-7.sql 의 예제 쿼리를 실행하고 구조를 분석

04-1.sql 분석

04-1.sql은 SELECT 절과 FROM 절을 사용한 조회 예제를 포함한다. 해당 스크립트의 예제가 나타내는 바는 다음과 같다.

  • SELECT 절: 열 이름의 목록을 명시하여 조회 대상 열을 설정한다.
  • FROM 절: 테이블 이름을 명시하여 조회 대상 테이블을 설정한다.

04-2.sql 분석

04-2.sql은 WHERE 절을 사용한 조건 조회 예제를 포함한다. 해당 스크립트의 예제가 나타내는 바는 다음과 같다.

  • WHERE 절: 선택할 행에 관한 조건을 설정한다.
  • BETWEEN 연산자: 두 조건 사이에 존재하는 행을 선택하기 위해 사용한다.
  • AND 연산자: 두 조건을 모두 만족하는 행을 선택하기 위해 사용한다.
  • OR 연산자: 두 조건 중 어느 하나라도 만족하는 행을 선택하기 위해 사용한다.
  • IN 연산자: 열거된 값과 일치하는 행을 선택하기 위해 사용한다.
  • NOT 연산자: 다른 논리 연산자를 부정하기 위해 사용한다. 조회 시 인덱스를 타지 않을 확률이 높다.
  • IS 연산자: NULL과의 일치 여부를 판단하기 위해 사용한다.

04-3.sql 분석

04-3.sql은 ORDER BY 절을 사용한 행 정렬과 LIMIT 절을 사용한 조회 개수 제한 예제를 포함한다. 해당 스크립트의 예제가 나타내는 바는 다음과 같다.

  • ORDER BY 절: 조회 결과를 ORDER BY 절의 후행 조건에 따라 정렬한다. 기본 정렬 기준은 나열된 열에 대해 순서대로 오름차순 정렬하는 것이다.
  • ASC 정렬 지시자: 조회 결과를 특정 열의 내용을 기준으로 오름차순 정렬한다.
  • DESC 정렬 지시자: 조회 결과를 특정 열의 내용을 기준으로 내림차순 정렬한다.
  • LIMIT 절: 조회할 행의 개수를 제한한다.
  • OFFSET 절: LIMIT 절과 함께 사용되며 OFFSET N의 경우 N + 1 번째 행부터의 조회 결과를 반환한다.

04-4.sql 분석

04-4.sql은 LIKE 절과 REGEXP 절을 사용한 패턴 매칭 예제를 포함한다. 해당 스크립트의 예제가 나타내는 바는 다음과 같다.

  • LIKE 절: 간단한 문자열 패턴 매칭을 통한 조건을 설정한다. % 기호(0개 이상의 문자)와 _ 기호(1개의 문자)를 사용하여 매칭할 패턴을 설정할 수 있다. %A%나 %A와 같이 특정 문자를 포함하거나 특정 문자로 끝나는 패턴을 지정할 경우 조회할 때 인덱스를 타지 않을 확률이 높다.
  • WITH 절: 임시 테이블을 만들기 위해 사용한다.
  • REGEXP 절: LIKE 절보다 복잡한 패턴 매칭이 필요할 경우에 정규 표현식을 사용해서 매칭할 패턴을 설정할 수 있다.

04-5.sql 분석

04-5.sql은 GROUP BY 절을 사용한 그룹화 예제를 포함한다. 해당 스크립트의 예제가 나타내는 바는 다음과 같다.

  • GROUP BY 절: 조회 시 특정 열에 관해 그룹화한다. WHERE 절과 ORDER BY 절 사이에 위치한다.
  • HAVING 절: GROUP BY 절로 그룹화된 결과에 조건을 건다.
  • DISTINCT 키워드: 중복된 행을 제거한다.

04-6.sql 분석

04-6.sql은 AUTO_INCREMENT 키워드를 사용한 자동 값 할당과 ADD FOREIGN KEY 문을 사용한 외래키 설정 예제를 포함한다. 해당 스크립트의 예제가 나타내는 바는 다음과 같다.

  • AUTO_INCREMENT 키워드: 명시적으로 값을 전달하지 않을 경우 새로운 행이 삽입될 때 이전 행의 값보다 1 높은 값을 할당한다.
  • LAST_INSERT_ID() 함수: AUTO_INCREMENT가 적용된 열의 마지막 데이터를 조회한다.
  • SET @@AUTO_INCREMENT_INCREMENT: AUTO_INCREMENT가 적용된 열에 할당할 값의 증가량을 설정한다.
  • ADD FOREIGN KEY 문: 테이블의 특정 열을 다른 테이블을 참조하는 외래 키로 설정한다.

04-7.sql 분석

04-7.sql은 열에 대한 자료형 설정과 자료의 길이를 반환하는 함수 사용 예제를 포함한다. 해당 스크립트의 예제가 나타내는 바는 다음과 같다.

  • FLOAT: 실수형 데이터를 표현한다.
  • CHAR: 고정 길이 문자열 데이터를 표현한다.
  • VARCHAR: 가변 길이 문자열 데이터를 표현한다.
  • CHAR_LENGTH(col): 문자 개수를 반환하는 함수다.
  • LENGTH(col): 바이트 크기를 반환하는 함수다.
  • COLLATE 키워드: 문자열 비교 및 정렬 규칙을 지정한다.

하위 태스크 2

테이블 구조 파악

SHOW COLUMNS , DESCRIBE 로 주요 테이블의 컬럼과 타입 확인

SHOW COLUMNS FROM customer 문장의 실행 결과는 다음과 같다. customer 테이블은 9개의 열로 구성되어 있고 customer_id 열이 기본 키로 지정되어 있음을 확인할 수 있다.

DESCRIBE film; 문장의 실행 결과는 다음과 같다. SHOW COLUMNS FROM ... 문과 동일한 결과를 낸다. film 테이블은 13개의 열로 구성되어 있고 film_id 열이 기본 키로 지정되어 있음을 알 수 있다.

하위 태스크 3

조건 쿼리 연습

WHERE, AND/OR, IN, BETWEEN, LIKE 를 활용한 필터링 쿼리 작성

다음 문장은 WHERE 절을 사용해 rental 테이블에서 customer_id가 274인 행을 조회한다.

SELECT * FROM rental WHERE customer_id = 274;

다음 문장은 WHERE 절과 BETWEEN 키워드 및 AND 키워드를 사용해 rental 테이블에서 return_date가 2005-05-29 00:00:00부터 2005-05-30 01:00:00 사이인 행을 조회한다.

SELECT * FROM rental WHERE return_date BETWEEN '2005-05-29 00:00:00' AND '2005-05-30 01:00:00';

다음 문장은 WHERE 절과 OR 키워드를 사용해 rental 테이블에서 inventory_id가 611 또는 3552인 행을 조회한다.

SELECT * FROM rental WHERE inventory_id = 611 OR inventory_id = 3552;

다음 문장은 WHERE 절과 IN 키워드를 사용해 rental 테이블에서 inventory_id가 47, 48인 행을 조회한다.

SELECT * FROM rental WHERE inventory_id IN (47, 48);

다음 문장은 WHERE 절과 LIKE 절을 사용해 rental 테이블에서 return_date가 2005-05로 시작하는 행을 조회한다.

SELECT * FROM rental WHERE return_date LIKE '2005-05%';

하위 태스크 4

정렬/페이징 연습

ORDER BY, LIMIT 를 사용해 상위 N개/하위 N개 데이터 조회

다음 문장은 rental 테이블을 customer_id 열에 대해 오름차순으로 정렬하고 상위 8개 항목을 조회한다.

SELECT * FROM rental ORDER BY customer_id LIMIT 8;

다음 문장은 rental 테이블을 inventory_id 열에 대해 내림차순으로 정렬하고 하위 8개 항목을 조회한다.

SELECT * FROM rental ORDER BY inventory_id DESC LIMIT 8;

하위 태스크 5

리포트 쿼리 작성

“최근 가입 고객”, “자주 대여된 영화” 등 리포트용 쿼리 설계

다음 문장은 customer_id 테이블을 create_date 열에 대해 내림차순으로 정렬하고 상위 10개 항목을 조회한다. 이는 최근 가입한 고객 10명의 목록을 의미한다.

SELECT * FROM customer ORDER BY create_date DESC LIMIT 10;

다음 문장은 film 테이블을 rental_rate 열에 대해 내림차순으로 정렬하고 상위 10개 항목을 조회한다. 이는 대여료가 높은 영화 상위 10개 목록을 의미한다.

SELECT * FROM film ORDER BY rental_rate DESC LIMIT 10;

하위 태스크 6

에러 메시지 해석 연습

의도적으로 문법/이름 오류를 발생시키고 에러를 분석

-- GROUP BY 절에 명시된 inventory이 SELECT 절에 명시되어 있지 않아 오류 발생
-- SQL Error: Unknown column 'inventory' in 'group statement'
SELECT inventory_id FROM rental GROUP BY inventory;
 
-- SELECT 절에 나열된 inventory_id가 GROUP BY 절에 포함되지 않아 오류 발생
-- SQL Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sakila.inventory.inventory_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SELECT inventory_id, film_id FROM inventory GROUP BY film_id;

하위 태스크 7

EXPLAIN 활용

주요 쿼리에 대해 EXPLAIN 으로 실행 계획을 간단히 확인

다음 문장을 실행하면 range 타입으로 조회됨을 확인할 수 있다.

EXPLAIN SELECT * FROM rental WHERE inventory_id IN (47, 48);

다음 문장을 실행하면 ALL 타입으로 조회됨을 확인할 수 있다.

EXPLAIN SELECT * FROM customer ORDER BY create_date DESC LIMIT 10;

그래프 뷰