중요
번역은 여러분이 참여할 수 있는 커뮤니티 활동입니다. 이 페이지는 현재 100.00% 번역되었습니다.
15.4. 수업: 쿼리
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 대신 사람들의 상세 정보와 그들이 사는 도로 이름을 보고 싶은 경우엔 어떻게 할까요? 그러려면 단일 쿼리에서 두 테이블을 결합(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)
이제 이 데이터에 서브셀렉트를 사용해볼까요? 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. 집계 쿼리
데이터베이스의 강력한 기능 가운데 하나는 데이터베이스 테이블에 있는 데이터를 요약할 수 있는 능력입니다. 이런 요약을 집계(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)
참고
ORDER BY 구문을 쓰지 않았기 때문에, 여러분의 결과물 순서가 예시와 일치하지 않을 수도 있습니다.
혼자서 해보세요: ★★☆
사람들을 도로 이름으로 요약하고 ‘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. 다음은 무엇을 배우게 될까요?
다음 수업에서는 여러분이 작성한 쿼리로부터 뷰를 생성하는 방법을 배워보겠습니다.