15.4. Consultas ILSI
Cuando escribe un comando SELECT ... se conoce comúnmente como una consulta - se están interrogando a la base de datos para obtener información.
La meta de esta lección: Para aprender cómo crear consultas que regresen información útil.
Nota
Si no se hizo en la lección anterior. añada los siguientes objetos de personas a su tabla people. si recibe errores relacionados con restricciones de clave foránea, necesitará añadir el objeto “Carretera principal” a su tabla de calles primero.
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. Ordenar resultados
Recuperemos una lista de personas ordenadas por sus números de casa:
select name, house_no from people order by house_no;
Resultado:
name | house_no
--------------+----------
Joe Bloggs | 3
Roger Jones | 33
Jane Smith | 55
Sally Norman | 83
(4 rows)
Puede ordenar los resultados por los valores de más de una columna:
select name, house_no from people order by name, house_no;
Resultado:
name | house_no
--------------+----------
Jane Smith | 55
Joe Bloggs | 3
Roger Jones | 33
Sally Norman | 83
(4 rows)
15.4.2. Filtrar
A menudo no se desea ver cada registro individual en la base de datos - especialmente si hay miles de registros y sólo se está interesado en ver una o dos.
Aquí hay un ejemplo de un filtro numérico que solo devuelve objetos cuyo house_no es menor que 50:
select name, house_no from people where house_no < 50;
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Puede combinar filtros (definidos usando la cláusula WHERE) con clasificación (definida usando la cláusula 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)
También puede filtrar según los datos de texto:
select name, house_no from people where name like '%s%';
name | house_no
-------------+----------
Joe Bloggs | 3
Roger Jones | 33
(2 rows)
Aquí utilizamos la clausula LIKE para encontrar todos los nombres con una s en ellos. Se dará cuenta que esta consulta distingue entre mayúsculas y minúsculas, por lo que la entrada Sally Norman no ha sido devuelta.
Si desea buscar una cadena de letras independientemente del caso, puede realizar una búsqueda que no distinga entre mayúsculas y minúsculas utilizando la cláusula ILIKE:
select name, house_no from people where name ilike '%r%';
name | house_no
--------------+----------
Roger Jones | 33
Sally Norman | 83
(2 rows)
Esa consulta devolvió todos los objetos personas con un r o R en su nombre.
15.4.3. Uniones
¿Qué sucede si desea ver los detalles de la persona y el nombre de su calle en lugar de la identificación? Para hacer eso, debe unir las dos tablas en una sola consulta. Veamos un ejemplo:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Nota
Con las uniones, siempre indicará las dos tablas de información que esta viendo, en este caso personas y calles. También es necesario especificar que las claves deben coincidir (clave foránea y clave primaria). Si no se especifica eso, se obtendrá una lista de todas las combinaciones posibles de personas y calles, pero ¡no hay forma de saber quién vive en que calle!
Así es como se verá la salida correcta:
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)
Volveremos a examinar las uniones que creamos con consultas más complejas después. Sólo recuerde que proporcionan una manera sencilla de combinar la información de dos o más tablas.
15.4.4. Sub-selección
Las sub-selecciones le permiten seleccionar objetos de una tabla basada en los datos de otra que esta enlazada mediante una relación de clave foránea. En nuestro caso, queremos encontrar personas que vivan en una calle especifica.
Primero, hagamos algunos ajustes en nuestros datos:
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;
Echemos un vistazo rápido a nuestros datos después de esos cambios: podemos reutilizar nuestra consulta de la sección anterior:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Resultado:
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)
Ahora le mostraremos una sub-selección de estos datos. Queremos mostrar solo a las personas que viven en street_id número 1:
select people.name
from people, (
select *
from streets
where id=1
) as streets_subset
where people.street_id = streets_subset.id;
Resultado:
name
--------------
Roger Jones
Sally Norman
(2 rows)
Aunque este es un ejemplo muy sencillo e innecesario con nuestros conjuntos de datos, que ilustra cómo las sub-selecciones útiles e importantes pueden ser al consultar conjunto de datos grandes y complejos.
15.4.5. Las consultas de agregado
Una de las características poderosas de una base de datos es su capacidad para resumir los datos en sus tablas. Estos resúmenes se denominan consultas agregadas. Aquí hay un ejemplo típico que nos dice cuántas personas hay objetos en nuestra tabla de personas:
select count(*) from people;
Resultado:
count
-------
4
(1 row)
Si queremos que los recuentos se resuman por nombre de calle, podemos hacer esto:
select count(name), street_id
from people
group by street_id;
Resultado:
count | street_id
-------+-----------
2 | 1
1 | 3
1 | 2
(3 rows)
Nota
Debido a que no hemos utilizado una cláusula ORDER BY, el orden de sus resultados podrían no coincidir con el que se muestra aquí.
Try Yourself 
Resumir las personas por nombre de calle y mostrar los nombres de calle reales en lugar del street_ids.
Respuesta
Aquí está la instrucción SQL correcta que debe usar:
select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;
Resultado:
count | name
------+-------------
1 | Low Street
2 | High street
1 | Main Road
(3 rows)
Notarás que tenemos nombres de campos con prefijos con nombres de tablas (por ejemplo, personas.nombre y calles.nombre). Esto debe hacerse siempre que el nombre del campo sea ambiguo (es decir, no único en todas las tablas de la base de datos).
15.4.6. In Conclusion
Se ha visto cómo utilizar consultas para regresar los datos en su base de datos en una manera que le permita extraer información útil de esto.
15.4.7. What’s Next?
A continuación, vamos a ver cómo crear vistas de las consultas que ha escrito.