중요
번역은 여러분이 참여할 수 있는 커뮤니티 활동입니다. 이 페이지는 현재 85.11% 번역되었습니다.
15.4. 수업: 쿼리
When you write a SELECT ... command it is commonly known as a query -
you are interrogating the database for information.
이 수업의 목표: 유용한 정보를 반환하는 쿼리를 생성하는 방법을 배우기.
참고
If you did not do so in the previous lesson, add the following people
objects to your people table. If you receive any errors related to
foreign key constraints, you will need to add the ‘Main Road’ object to your
streets table first
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)
Here we used the LIKE clause to find all names with an s in them.
You’ll notice that this query is case-sensitive, so the Sally Norman entry
has not been returned.
If you want to search for a string of letters regardless of case, you can do a
case in-sensitive search using the ILIKE clause:
select name, house_no from people where name ilike '%r%';
name | house_no
--------------+----------
Roger Jones | 33
Sally Norman | 83
(2 rows)
That query returned every people object with an r or R in their
name.
15.4.3. 결합
여러분이 ID 대신 사람들의 상세 정보와 그들이 사는 도로 이름을 보고 싶은 경우엔 어떻게 할까요? 그러려면 단일 쿼리에서 두 테이블을 결합(join)해야 합니다. 다음 예시를 보십시오:
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. 서브셀렉트
서브셀렉트(sub-select)를 사용하면 어떤 테이블에서 외래 키 관계를 통해 연결된 또다른 테이블의 데이터를 기반으로 객체를 선택할 수 있습니다. 이 예시에서는 특정 거리에 사는 사람을 찾고자 합니다.
먼저 데이터를 조금 조정해볼까요:
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)
Now let’s show you a sub-selection on this data. We want to show only people who
live in street_id number 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. 집계 쿼리
데이터베이스의 강력한 기능 가운데 하나는 데이터베이스 테이블에 있는 데이터를 요약할 수 있는 능력입니다. 이런 요약을 집계(aggregate) 쿼리라고 합니다. 다음은 ‘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)
참고
Because we have not used an ORDER BY clause, the order of your
results may not match what is shown here.
혼자서 해보세요: ★★☆
사람들을 도로 이름으로 요약하고 ‘street_id’ 대신 실제 도로 이름을 나타내도록 하십시오.
해답
여러분이 사용해야 할 올바른 SQL 선언문은 다음과 같습니다:
select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;
결과:
count | name
------+-------------
1 | Low Street
2 | High street
1 | Main Road
(3 rows)
필드 이름 앞에 테이블 이름을 접두어로 붙였다는 (예: ‘people.name’과 ‘streets.name’) 사실을 알아차렸을 겁니다. 필드 이름이 모호한 경우 (예를 들면 데이터베이스에 있는 모든 테이블에서 유일한 이름이 아닌 경우) 항상 이렇게 해야 합니다.
15.4.6. 결론
어떻게 쿼리를 데이터베이스에서 유용한 정보를 추출할 수 있게 해주는 방식으로 사용해서 데이터베이스의 데이터를 반환받을 수 있는지 배웠습니다.
15.4.7. 다음은 무엇을 배우게 될까요?
다음 수업에서는 여러분이 작성한 쿼리로부터 뷰를 생성하는 방법을 배워보겠습니다.