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)
You can combine filters (defined using the WHERE clause) with sorting (defined using the 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;
주석
With joins, you will always state the two tables the information is coming from, in this case people and streets. You also need to specify which two keys must match (foreign key & primary key). If you don’t specify that, you will get a list of all possible combinations of people and streets, but no way to know who actually lives on which street!
정확한 산출물은 다음과 같을 것입니다.
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.6. In Conclusion¶
어떻게 데이터베이스에서 유용한 정보를 추출할 수 있게 해주는 방식으로 쿼리를 사용해서 데이터베이스의 데이터를 반환받을 수 있는지 배웠습니다.
15.4.7. What’s Next?¶
다음 강의에서 사용자가 작성한 쿼리에서 뷰를 생성하는 방법을 배워보겠습니다.