15.4. Lesson: 쿼리

SELECT ... 명령을 작성할 때 이를 흔히 쿼리라고 합니다. 사용자가 데이터베이스에서 정보를 얻는 행위입니다.

이 강의의 목표: 유용한 정보를 반환하는 쿼리를 생성하는 방법을 배우기.

주석

이전 강의에서 벌써 하지 않았다면, 사용자의 people 테이블에 다음 인물 오브젝트들을 추가하십시오. 외래 키 제약 조건에 관한 오류를 반환받았을 경우, 먼저 streets 테이블에 ‘Main Road’ 오브젝트를 추가해야 합니다.

insert into people (name,house_no, street_id, phone_no)
          values ('Joe Bloggs',3,2,'072 887 23 45');
insert into people (name,house_no, street_id, phone_no)
          values ('Jane Smith',55,3,'072 837 33 35');
insert into people (name,house_no, street_id, phone_no)
          values ('Roger Jones',33,1,'072 832 31 38');
insert into people (name,house_no, street_id, phone_no)
          values ('Sally Norman',83,1,'072 932 31 32');

15.4.1. 결과물 정렬

인물 목록을 번지 순으로 정렬해서 받아봅시다.

select name, house_no from people order by house_no;

결과물 :

     name     | house_no
--------------+----------
 Joe Bloggs   |        3
 Roger Jones  |       33
 Jane Smith   |       55
 Sally Norman |       83
(4 rows)

하나 이상의 열의 값들을 기준으로 결과를 정렬할 수 있습니다.

select name, house_no from people order by name, house_no;

결과물 :

     name     | house_no
--------------+----------
 Jane Smith   |       55
 Joe Bloggs   |        3
 Roger Jones  |       33
 Sally Norman |       83
(4 rows)

15.4.2. 필터링

대부분의 경우 데이터베이스에 있는 모든 레코드를 하나하나 보고 싶지는 않을 겁니다. 특히 레코드가 수 천 개 있는데 그 중 하나나 두 개에만 관심이 있을 때는 말이죠.

다음은 house_no 값이 50 미만인 오브젝트만 반환하도록 하는 숫자 필터의 예입니다.

select name, house_no from people where house_no < 50;

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

사용자가 ( WHERE 구문을 사용해 정의하는) 필터와 ( ORDER BY 구문을 사용해 정의하는) 정렬을 결합할 수 있습니다.

select name, house_no from people where house_no < 50 order by house_no;

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

텍스트 데이터를 기준으로 필터링할 수도 있습니다.

select name, house_no from people where name like '%s%';

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

이 예시에서 LIKE 구문을 이용해 s 가 들어간 모든 성명을 찾았습니다. 이 쿼리가 대소문자를 구분하기 때문에 Sally Norman 항목을 반환하지 않았다는 사실을 알아차리셨을 겁니다.

대소문자를 구분하지 않고 어떤 문자열을 검색하고자 한다면, ILIKE 구문을 써서 대소문자를 구분하지 않는 검색을 실행할 수 있습니다.

select name, house_no from people where name ilike '%r%';

       name     | house_no
  --------------+----------
   Roger Jones  |       33
   Sally Norman |       83
  (2 rows)

이 쿼리는 성명에 r 이나 R 이 들어간 모든 ‘people’ 오브젝트를 반환했습니다.

15.4.3. 결합

여러분이 인물의 ID 대신 상세 정보와 주소를 알고 싶을 땐 어떻게 할까요? 이렇게 하려면 단일 쿼리에서 두 테이블을 함께 결합해야 합니다. 다음 예시를 살펴보십시오.

select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;

주석

결합을 이용할 때 항상 정보를 가져오는 두 테이블을 선언해야 합니다. 이 경우엔 ‘people’과 ‘streets’입니다. 또한 어떤 두 키(기본 키와 외래 키)가 일치해야만 하는지도 지정해야 합니다. 키를 지정하지 않을 경우 ‘people’과 ‘streets’의 가능한 모든 조합의 목록을 받게 되지만, 누가 실제로 어디에 사는지 알 수 있는 방법이 없습니다!

정확한 산출물은 다음과 같을 것입니다.

     name     | house_no |    name
--------------+----------+-------------
 Joe Bloggs   |        3 | Low Street
 Roger Jones  |       33 | High street
 Sally Norman |       83 | High street
 Jane Smith   |       55 | Main Road
(4 rows)

이후 좀 더 복잡한 쿼리를 생성할 때 다시 이 결합을 살펴볼 것입니다. 지금은 두 개 이상의 테이블에서 정보를 조합할 수 있는 간단한 방법이라는 것만 기억해두십시오.

15.4.4. 내부 선택

내부 선택을 사용하면 외래 키 관계를 통해 연결된 다른 테이블의 데이터를 기반으로 테이블에서 오브젝트를 선택할 수 있습니다. 이 예시에서는 특정 거리에 사는 인물을 찾고자 합니다.

먼저 데이터를 약간 수정해봅시다.

insert into streets (name) values('QGIS Road');
insert into streets (name) values('OGR Corner');
insert into streets (name) values('Goodle Square');
update people set street_id = 2 where id=2;
update people set street_id = 3 where id=3;

이렇게 수정한 다음 데이터를 살펴보도록 합시다. 이전 단계에서 사용했던 쿼리를 다시 쓸 수 있습니다.

select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;

결과물 :

     name     | house_no |    name
--------------+----------+-------------
 Roger Jones  |       33 | High street
 Sally Norman |       83 | High street
 Jane Smith   |       55 | Main Road
 Joe Bloggs   |        3 | Low Street
(4 rows)

이제 이 데이터에 대해 어떻게 결과 내 선택을 하는지 볼까요? street_id 숫자가 1 인 곳에 사는 인물들만 보려 합니다.

select people.name
from people, (
    select *
    from streets
    where id=1
  ) as streets_subset
where people.street_id = streets_subset.id;

결과물 :

     name
--------------
 Roger Jones
 Sally Norman
(2 rows)

이 예시가 매우 단순하며 여러분의 작은 데이터셋에서는 불필요하긴 하지만, 복잡한 대용량 데이터셋을 쿼리할 때 내부 선택이 얼마나 유용하고 중요할 수 있는지를 보여줍니다.

15.4.5. 집계 쿼리

데이터베이스의 강력한 기능 가운데 하나가 테이블에 있는 데이터를 요약할 수 있다는 것입니다. 이런 요약을 집계 쿼리라고 부릅니다. 다음은 ‘people’ 테이블에 있는 인물 오브젝트가 몇 개인지 보여주는 예시입니다.

select count(*) from people;

결과물 :

 count
-------
     4
(1 row)

도로명으로 요약한 집계를 바란다면 다음과 같이 할 수 있습니다.

select count(name), street_id
from people
group by street_id;

결과물 :

 count | street_id
-------+-----------
     2 |         1
     1 |         3
     1 |         2
(3 rows)

주석

ORDER BY 구문을 쓰지 않았기 때문에, 사용자의 결과물 순서가 예시와 일치하지 않을 수도 있습니다.

15.4.5.1. Try Yourself moderate 중급

도로명으로 인물을 요약하고 ‘street_id’ 대신 실제 도로명을 나타내도록 하십시오.

결과 확인

15.4.6. In Conclusion

어떻게 데이터베이스에서 유용한 정보를 추출할 수 있게 해주는 방식으로 쿼리를 사용해서 데이터베이스의 데이터를 반환받을 수 있는지 배웠습니다.

15.4.7. What’s Next?

다음 강의에서 사용자가 작성한 쿼리에서 뷰를 생성하는 방법을 배워보겠습니다.