쿼리 역학
쿼리가 MySQL 서버로 전송될 때마다 서버는 다음 사항을 확인한 후 구문을 실행합니다.
- 이 구문을 실행할 권한이 있는가?
- 원하는 데이터에 액세스 할 수 있는 권한이 있는가?
- 구문의 문법이 정확한가?
이를 통과한 쿼리는 가장 효율적인 방법을 결정하는 쿼리 옵티마이저로 전달되고, 옵티마이저는 from 절에 명명된 테이블에 조인할 순서 및 인덱스를 확인한 후, 쿼리 실행에 필요한 실행 계획을 선택합니다. 이후 쿼리 실행이 완료되면 서버는 쿼리를 호출한 응용 프로그램으로 행과 열을 포함하는 테이블로 나타나는 결과셋을 반환합니다. 아무런 결과도 얻지 못할 경우는 Empty set 을 반환합니다.
쿼리 절
select 문은 여러 개의 구성요소 및 절로 구성됩니다. 아래는 그 종류입니다.
| 절 이름 | 목적 |
|---|---|
| select | 쿼리 결과에 포함할 열을 결정 |
| from | 데이터를 검색할 테이블과 테이블을 조인하는 방법을 식별 |
| where | 불필요한 데이터를 걸러냄 |
| group by | 공통 열 값을 기준으로 행을 그룹화 |
| having | 불필요한 그룹을 걸러냄 |
| order by | 하나 이상의 열을 기준으로 최종 결과의 행을 정렬 |
Select 절
최종 결과셋에 포함할 항목을 결정하려면 이에 해당할 수 있는 모든 열을 먼저 알아야 할 필요가 있습니다. 따라서 select 절은 select 문의 가장 앞에 있는 절이지만 데이터베이스 서버가 판단하는 마지막 절 중 하나가 됩니다. 아래의 쿼리처럼 from 절을 통해 선택된 select 문의 범위인 language 테이블에 대해 select 절은 포함될 열을 지정하여 결과를 얻습니다. (* 문자를 사용할 경우에는 전체 열을 얻습니다.)
MariaDB [sakila]> SELECT * FROM language; /* 전체 열 */
+-------------+----------+---------------------+
| language_id | name | last_update |
+-------------+----------+---------------------+
| 1 | English | 2006-02-15 05:02:19 |
| 2 | Italian | 2006-02-15 05:02:19 |
| 3 | Japanese | 2006-02-15 05:02:19 |
| 4 | Mandarin | 2006-02-15 05:02:19 |
| 5 | French | 2006-02-15 05:02:19 |
| 6 | German | 2006-02-15 05:02:19 |
+-------------+----------+---------------------+
6 rows in set (0.001 sec)
MariaDB [sakila]> SELECT name FROM language; /* name 열만 */
+----------+
| name |
+----------+
| English |
| Italian |
| Japanese |
| Mandarin |
| French |
| German |
+----------+
6 rows in set (0.001 sec)select 절에는 단순하게 테이블의 열만 포함하는 것이 아니라 다음과 같은 항목을 추가할 수 있습니다.
- 숫자 또는 문자열과 같은 리터럴
- 표현식
- 내장 함수 호출
- 사용자 정의 함수 호출
아래는 이에 대한 예시입니다.
MariaDB [sakila]> SELECT language_id,
-> 'COMMON' language_usage,
-> language_id * 3.1415972 lang_pi_value,
-> upper(name) language_name
-> FROM language;
+-------------+----------------+---------------+---------------+
| language_id | language_usage | lang_pi_value | language_name |
+-------------+----------------+---------------+---------------+
| 1 | COMMON | 3.1415972 | ENGLISH |
| 2 | COMMON | 6.2831944 | ITALIAN |
| 3 | COMMON | 9.4247916 | JAPANESE |
| 4 | COMMON | 12.5663888 | MANDARIN |
| 5 | COMMON | 15.7079860 | FRENCH |
| 6 | COMMON | 18.8495832 | GERMAN |
+-------------+----------------+---------------+---------------+
6 rows in set (0.001 sec)이 때, 'language_usage', 'lang_pi_value', 'language_name'처럼 열 별칭을 지정할 수 있습니다.
중복 제거
DISTINCT 키워드를 select 바로 뒤에 붙여서 중복된 결과를 제거할 수 있습니다
From 절
from 절은 쿼리에 사용되는 테이블을 명시할 뿐만 아니라, 테이블을 서로 연결하는 수단도 함께 정의하는 절입니다.
테이블 유형
테이블을 단순히 데이터베이스에 저장된 일련의 관련 행 집합으로만 정의하는 것이 아닌, 관련 행들의 집합으로 생각해보면 네 가지 유형으로 정의할 수 있습니다.
- 영구 테이블: create table 문으로 생성
- 파생 테이블: 하위 쿼리에서 반환하고 메모리에 보관된 행
- 임시 테이블: 메모리에 저장된 휘발성 데이터
- 가상 테이블: create view 문으로 생성
파생 테이블
괄호로 묶여 있는 다른 쿼리에 포함된 쿼리인 서브쿼리는 from 절 내에서 from 절에 명시된 다른 테이블과 상호작용할 수 있는 파생 테이블을 생성하는 역할을 합니다. 아래 예제의 from 절 내에서 생성되는 cust 데이터는 쿼리 기간 동안 메모리에 보관된 후 삭제됩니다.
MariaDB [sakila]> SELECT concat(cust.last_name, ', ', cust.first_name) full_name
-> FROM
-> (SELECT first_name, last_name, email
-> FROM client
-> WHERE first_name = 'JESSIE'
-> ) cust;
+---------------+
| full_name |
+---------------+
| BANKS, JESSIE |
| MILAM, JESSIE |
+---------------+
2 rows in set (0.003 sec)임시 테이블
모든 관계형 데이터베이스는 휘발성의 임시 테이블을 저장할 수 있습니다. mysql에서는 아래처럼 TEMPORARY 명령어를 활용하여 임시 테이블을 생성할 수 있으며, 여기에 삽입된 데이터는 세션이 종료될 때 사라집니다.
MariaDB [sakila]> CREATE TEMPORARY TABLE actors_j (actor_id smallint(5), first_name varchar(45), last_name varchar(45));
Query OK, 0 rows affected (0.003 sec)
MariaDB [sakila]> INSERT INTO actors_j
-> SELECT actor_id, first_name, last_name FROM actor
-> WHERE last_name LIKE 'J%';
Query OK, 7 rows affected (0.001 sec)
Records: 7 Duplicates: 0 Warnings: 0
MariaDB [sakila]> SELECT * FROM actors_j;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
| 119 | WARREN | JACKMAN |
| 131 | JANE | JACKMAN |
| 8 | MATTHEW | JOHANSSON |
| 64 | RAY | JOHANSSON |
| 146 | ALBERT | JOHANSSON |
| 82 | WOODY | JOLIE |
| 43 | KIRK | JOVOVICH |
+----------+------------+-----------+
7 rows in set (0.000 sec)가상 테이블(뷰)
뷰는 데이터 딕셔너리에 저장된 쿼리입니다. 뷰는 마치 테이블처럼 동작하지만 뷰에 저장된 데이터가 존재하지 않기에 가상 테이블이라고 부릅니다. 아래 뷰를 생성하는 쿼리의 결과에서 보이는 것처럼 실제 데이터에 영향을 미치지 않습니다. 뷰에 대해 쿼리를 실행하면 쿼리가 뷰 정의와 합쳐져 실행할 최종 쿼리를 만드는 방식으로 뷰는 사용됩니다.
MariaDB [sakila]> CREATE VIEW cust_vw AS
-> SELECT client_id, first_name, last_name, active FROM client; /* 뷰 생성 */
Query OK, 0 rows affected (0.023 sec) /* 실제 데이터에 영향이 없음 */
MariaDB [sakila]> SELECT first_name, last_name FROM cust_vw WHERE active = 0; /* 뷰에 대한 쿼리 */
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| SANDRA | MARTIN |
| JUDITH | COX |
| SHEILA | WELLS |
| ERICA | MATTHEWS |
| HEIDI | LARSON |
| PENNY | NEAL |
| KENNETH | GOODEN |
| HARRY | ARCE |
| NATHAN | RUNYON |
| THEODORE | CULP |
| MAURICE | CRAWLEY |
| BEN | EASTER |
| CHRISTIAN | JUNG |
| JIMMIE | EGGLESTON |
| TERRANCE | ROUSH |
+------------+-----------+
15 rows in set (0.002 sec)테이블 연결 및 별칭
from 절에 둘 이상의 테이블이 있으면 그 테이블들을 연결하는 데 필요한 조건도 포함해야 합니다. 이는 데이터베이스 서버에서의 요구사항은 아니지만, 여러 테이블을 조인하는 ANSI 승인 방법입니다. 아래는 join을 이용한 테이블 연결 방식의 예시입니다. client 테이블과 rental 테이블 모두에 저장되어 있는 client_id를 활용하여 연결하는 것을 볼 수 있습니다. 이 때 테이블의 별칭을 할당하여 간결하게 쿼리를 할 수 있습니다. (별칭 지정 시 AS 키워드를 함께 사용해도 됩니다.
MariaDB [sakila]> SELECT c.first_name, c.last_name, time(r.rental_date) rental_time
-> FROM client c
-> INNER JOIN rental r
-> ON c.client_id = r.client_id
-> WHERE date(r.rental_date) = '2005-06-14';
+------------+-----------+-------------+
| first_name | last_name | rental_time |
+------------+-----------+-------------+
| CATHERINE | CAMPBELL | 23:17:03 |
| JOYCE | EDWARDS | 23:16:26 |
| AMBER | DIXON | 23:42:56 |
...
| ELMER | NOE | 22:55:13 |
| TERRANCE | ROUSH | 23:12:46 |
| TERRENCE | GUNDERSON | 23:47:35 |
+------------+-----------+-------------+
16 rows in set (0.031 sec)Where 절
where 절은 결과셋에 출력되기를 원하지 않는 행을 필터링하는 메커니즘입니다. and, or 또는 not과 같은 연산자를 함께 사용하여 다양한 필터 조건을 포함할 수 있습니다. 아래는 최소 일주일 동안 대여할 수 있으며, G 등급인 영화를 검색하는 예시입니다. 해당 필터 조건을 통해 총 1000개의 행 중 971개가 필터링 되었습니다.
MariaDB [sakila]> SELECT title FROM film
-> WHERE rating = 'G' AND rental_duration >= 7;
+-------------------------+
| title |
+-------------------------+
| BLANKET BEVERLY |
| BORROWERS BEDAZZLED |
| BRIDE INTRIGUE |
...
| TRUMAN CRAZY |
| WAKE JAWS |
| WAR NOTTING |
+-------------------------+
29 rows in set (0.002 sec)Group by 절과 having 절
데이터베이스에서 결과를 검색하기 전에 데이터베이스 서버가 데이터를 정제할 수 있도록 할 수 있습니다. group by는 이러한 메커니즘 중 하나로 데이터를 열 값 별로 그룹화 합니다. 아래는 40편 이상의 영화를 대여한 모든 고객을 찾기 위해 고객별로 모든 대여 내역을 group by로 그룹화하고 having 절을 이용하여 40편 이상의 영화를 대여한 고객만 필터링하는 예시입니다.
MariaDB [sakila]> SELECT c.first_name, c.last_name, count(*)
-> FROM client c
-> INNER JOIN rental r
-> ON c.client_id = r.client_id
-> GROUP BY c.first_name, c.last_name
-> HAVING count(*) >= 40;
+------------+-----------+----------+
| first_name | last_name | count(*) |
+------------+-----------+----------+
| CLARA | SHAW | 42 |
| ELEANOR | HUNT | 46 |
| KARL | SEAL | 45 |
| MARCIA | DEAN | 42 |
| SUE | PETERS | 40 |
| TAMMY | SANDERS | 41 |
| WESLEY | BULL | 40 |
+------------+-----------+----------+
7 rows in set (0.027 sec)Order by 절
order by 절은 열 데이터를 기반으로 표현식을 사용하여 결과셋을 정렬하는 메커니즘입니다. 이전 테이블 연결에서 예시로 사용한 쿼리를 last_name기준으로 정렬되도록 하면 아래와 같이 됩니다. 이 때, 정렬 기준을 추가하는 방식으로 다중 정렬을 수행할 수 있습니다.
MariaDB [sakila]> SELECT c.first_name, c.last_name, time(r.rental_date) rental_time
-> FROM client c
-> INNER JOIN rental r
-> ON c.client_id = r.client_id
-> WHERE date(r.rental_date) = '2005-06-14'
-> ORDER BY c.last_name;
+------------+-----------+-------------+
| first_name | last_name | rental_time |
+------------+-----------+-------------+
| DANIEL | CABRAL | 23:09:38 |
| CATHERINE | CAMPBELL | 23:17:03 |
| HERMAN | DEVORE | 23:35:09 |
...
| JEFFERY | PINSON | 22:53:33 |
| MINNIE | ROMERO | 23:00:34 |
| TERRANCE | ROUSH | 23:12:46 |
+------------+-----------+-------------+
16 rows in set (0.033 sec)내림차순 및 오름차순 정렬
기본 값이 오름차순이므로 desc 키워드를 활용하여 내림차순으로 필터할 수 있습니다.
순서를 통한 정렬
열의 이름이 아닌 순서를 기준으로도 정렬할 수 있습니다. 위의 정렬은 c.last_name 대신 2를 사용해도 동등한 결과가 됩니다.
참고
이 글 및 이와 관련된 앞으로의 글의 모든 내용은 한빛미디어의 러닝 SQL의 내용을 공부하며 요약한 것입니다.
