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

Let’s retrieve a list of people ordered by their house numbers:

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)

You can sort the results by the values of more than one column:

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.

Here is an example of a numerical filter which only returns objects whose house_no is less than 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 clause):

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)

You can also filter based on text data:

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.

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. Uniones

What if you want to see the person’s details and their street’s name instead of the ID? In order to do that, you need to join the two tables together in a single query. Lets look at an example:

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!

Here is what the correct output will look like:

     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.

First, let’s do a little tweaking of our data:

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;

Let’s take a quick look at our data after those changes: we can reuse our query from the previous section:

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)

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;

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

One of the powerful features of a database is its ability to summarise the data in its tables. These summaries are called aggregate queries. Here is a typical example which tells us how many people objects are in our people table:

select count(*) from people;

Resultado:

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

If we want the counts to be summarised by street name we can do this:

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í.

15.4.5.1. Try Yourself moderate

Resumir las personas por nombre de calle y mostrar los nombres de calle reales en lugar del street_ids.

Verifique sus resultados

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.