SQL | Selection, JOIN, View 실습 (vetdb 예제) - 2

 

이전 글에서는 vetdb 데이터베이스를 설계하고 Owner, Pet 테이블을 생성하는 과정을 다뤘습니다. 이번 글에서는 그 다음 단계로, 데이터를 조회하고 결합하고 가공하는 데 유용한 SQL 문법을 실습 중심으로 익혀보겠습니다.

데이터 선택 (Selection)

mysql> SELECT * FROM owner;
+----------+-------------+---------------+-------------------------+
| owner_id | name        | phone         | email                   |
+----------+-------------+---------------+-------------------------+
|        1 | Kim Soojin  | 010-1234-5678 | soojin.kim@example.com  |
|        2 | Lee Hyunwoo | 010-9876-5432 | hyunwoo.lee@example.com |
|        3 | Choi Yuna   | 010-4567-8901 | yuna.choi@example.com   |
+----------+-------------+---------------+-------------------------+
3 rows in set (0.02 sec)

mysql> SELECT * FROM Pet;
+--------+------+---------+------------------+------------+----------+
| pet_id | name | species | breed            | birthdate  | owner_id |
+--------+------+---------+------------------+------------+----------+
|      1 | Bori | Dog     | Shih Tzu         | 2020-04-15 |        1 |
|      2 | Nabi | Cat     | Korean Shorthair | 2019-07-23 |        1 |
|      3 | Coco | Dog     | Poodle           | 2021-11-30 |        2 |
|      4 | Toto | Parrot  | Cockatiel        | 2018-02-12 |        3 |
+--------+------+---------+------------------+------------+----------+
4 rows in set (0.00 sec)

mysql> SELECT name, species FROM Pet;
+------+---------+
| name | species |
+------+---------+
| Bori | Dog     |
| Nabi | Cat     |
| Coco | Dog     |
| Toto | Parrot  |
+------+---------+
4 rows in set (0.00 sec)

mysql> SELECT name, species FROM Pet WHERE owner_id = 1;
+------+---------+
| name | species |
+------+---------+
| Bori | Dog     |
| Nabi | Cat     |
+------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM Pet WHERE species = 'Cat';
+--------+------+---------+------------------+------------+----------+
| pet_id | name | species | breed            | birthdate  | owner_id |
+--------+------+---------+------------------+------------+----------+
|      2 | Nabi | Cat     | Korean Shorthair | 2019-07-23 |        1 |
+--------+------+---------+------------------+------------+----------+
1 row in set (0.00 sec)

 

• SELECT *: 모든 컬럼을 조회합니다.
• WHERE: 조건절로 특정 행만 필터링합니다.
• 문자열은 ' 작은 따옴표로 감싸야 합니다.

테이블 조인 (JOIN)

mysql> SELECT Pet.name AS pet_name, Pet.species, Owner.name AS owner_name FROM Pet JOIN Owner ON Pet.owner_id = Owner.owner_id;
+----------+---------+-------------+
| pet_name | species | owner_name  |
+----------+---------+-------------+
| Bori     | Dog     | Kim Soojin  |
| Nabi     | Cat     | Kim Soojin  |
| Coco     | Dog     | Lee Hyunwoo |
| Toto     | Parrot  | Choi Yuna   |
+----------+---------+-------------+
4 rows in set (0.01 sec)

 

 

• JOIN: 기본적으로 INNER JOIN이며, 두 테이블에서 공통된 조건을 만족하는 행만 결합합니다.
• ON: 조인 조건을 명시합니다.
• AS: 컬럼에 별칭을 부여해 출력 결과를 더 읽기 쉽게 만듭니다.

mysql> SELECT * FROM Pet JOIN Owner ON Pet.owner_id = Owner.owner_id; 
+--------+------+---------+------------------+------------+----------+----------+-------------+---------------+-------------------------+
| pet_id | name | species | breed            | birthdate  | owner_id | owner_id | name        | phone         | email                   |
+--------+------+---------+------------------+------------+----------+----------+-------------+---------------+-------------------------+
|      1 | Bori | Dog     | Shih Tzu         | 2020-04-15 |        1 |        1 | Kim Soojin  | 010-1234-5678 | soojin.kim@example.com  |
|      2 | Nabi | Cat     | Korean Shorthair | 2019-07-23 |        1 |        1 | Kim Soojin  | 010-1234-5678 | soojin.kim@example.com  |
|      3 | Coco | Dog     | Poodle           | 2021-11-30 |        2 |        2 | Lee Hyunwoo | 010-9876-5432 | hyunwoo.lee@example.com |
|      4 | Toto | Parrot  | Cockatiel        | 2018-02-12 |        3 |        3 | Choi Yuna   | 010-4567-8901 | yuna.choi@example.com   |
+--------+------+---------+------------------+------------+----------+----------+-------------+---------------+-------------------------+
4 rows in set (0.00 sec)

 

다중 테이블에서 조건 조회

mysql> SELECT Pet.name, Pet.species, Pet.breed FROM PET JOIN Owner ON Pet.owner_id = Owner.owner_id WHERE Owner.name = 'Kim Soojin';
+------+---------+------------------+
| name | species | breed            |
+------+---------+------------------+
| Bori | Dog     | Shih Tzu         |
| Nabi | Cat     | Korean Shorthair |
+------+---------+------------------+
2 rows in set (0.01 sec)

 

• 이처럼 WHERE 조건절은 조인된 결과에도 사용할 수 있습니다.
• 테이블을 결합한 뒤 원하는 조건으로 필터링하는 것이 핵심입니다.

View 생성

View는 자주 사용하는 SELECT 쿼리를 가상의 테이블처럼 저장해두는 기능입니다.

mysql> CREATE VIEW PetWithOwner AS SELECT Pet.name AS pet_name, Pet.breed, Owner.name AS owner_name FROM PET JOIN Owner ON Pet.owner_id = Owner.owner_id;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM PetWithOwner;
+----------+------------------+-------------+
| pet_name | breed            | owner_name  |
+----------+------------------+-------------+
| Bori     | Shih Tzu         | Kim Soojin  |
| Nabi     | Korean Shorthair | Kim Soojin  |
| Coco     | Poodle           | Lee Hyunwoo |
| Toto     | Cockatiel        | Choi Yuna   |
+----------+------------------+-------------+
4 rows in set (0.01 sec)

 

 

• CREATE VIEW 뷰이름 AS SELECT ...: 지정한 SELECT 쿼리 결과를 뷰로 저장합니다.
• 뷰는 읽기 전용으로도 사용할 수 있으며, 복잡한 쿼리를 단순화할 때 유용합니다.