PDF de programación - Basesdatos prac 2 consultas

Sistemas de Información II – Práctica II: Consultas en SQL

Carlos Castillo / Victor Pascual – [email protected]

1. Uso de tu propia base de datos

Recuerda que nos conectaremos primero a otro servidor. Abre un terminal en Linux y ejecuta:

ssh 193.145.45.40 -l XXXXXXX
[email protected]’s password: YYYYYYY

XXXXXXX e YYYYYYY serán entregados durante la clase de prácticas. A continuación nos conectare-

mos a la base de datos, usando:

mysql si2_NN -u si2_NN -p
Enter password: (password creada en clase anterior)

El objetivo de esta práctica es aprender a realizar consultas en SQL. Para ello usaremos un fichero con

información de municipios creado por Toni Navarrete.

Para insertar los datos en la base de datos, usaremos lo siguiente:

[user@si2-aules ~]$
wget http://www.tejedoresdelweb.com/slides/bases_datos/municipis.txt -O /tmp/NN.txt
less /tmp/NN.txt
cat /tmp/NN.txt | mysql si2_NN -f -u si2_NN -p

NN es el número de tu base de datos. El primer comando descarga un fichero con comandos sql, el segundo
lo examina y el tercero lo ejecuta. La opción -f es para que ignore los errores que produzcan los DROP TABLE
que incluye el fichero.

Si el comando wget no funciona, podeis copiar y pegar el contenido de municipis.txt en el intérprete

mysql directamente.

2. Verificar si los datos fueron cargados

Primero verificamos si las tablas fueron creadas:

mysql si2_NN -u si2_NN -p

mysql> SHOW COLUMNS FROM comunitat;
+-------+-------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|
| ca_id | int(11)
| nom
|
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

| YES
| varchar(50) | YES

| NULL
| NULL

|
|

|
|

1

mysql> SHOW COLUMNS FROM municipi;
+--------------+-------------+------+-----+---------+-------+
| Field
| Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| m_id
|
|
| nom
|
| poblacio2003 | int(11)
|
| poblacio2001 | int(11)
|
| poblacio1996 | int(11)
| poblacio1991 | int(11)
|
|
| superficie
| ca_id
|
+--------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

|
| int(11)
| varchar(70) | YES |
|
|
|
|
|
|

| NULL
| NULL
| NULL
| NULL
| NULL
| NULL
| NULL
| NULL

| YES

| YES
| YES
| YES
| YES
| YES
| YES

| Type

| float
| int(11)

|
|
|
|
|
|
|
|

Luego si tienen datos:

mysql> SELECT * FROM comunitat;
mysql> SELECT * FROM municipi;

Nótese que siempre ponemos todas las palabras clave de MySQL en mayúsculas, esto es para que que sea

más comprensible al leer.

3. Búsqueda de strings

En SQL el operador más usado de búsqueda de strings es LIKE. Con LIKE se puede realizar el siguiente

tipo de consultas:

Consulta exacta LIKE ’Catalunya’

Consulta omitiendo una parte LIKE ’Catal %’

Consulta omitiendo un carácter LIKE ’Ca lu
Éstas se pueden combinar usando OR, AND y NOT:

’

mysql> SELECT nom FROM comunitat WHERE

-> nom LIKE ’%Castilla%’
-> AND
-> nom NOT LIKE ’%Mancha%’;

+-----------------+
| nom
|
+-----------------+
| Castilla y León |
+-----------------+

Además de LIKE existen funciones que se pueden aplicar sobre los strings, como:

LENGTH(’texto’) → 5
CONCAT(’tex’,’to’) → ’texto’
LOWER(’TeXtO’) → ’texto’
UPPER(’TeXtO’) → ’TEXTO’

2

RTRIM(’TeXtO ’) → ’TeXtO’. Esto es útil cuando hay columnas de ancho fijo, para quitar los espa-
cios.

Todas estas funciones pueden ser combinadas, por ejemplo:

mysql> SELECT nom FROM municipi

-> WHERE LOWER(nom) LIKE ’%a%’
-> AND LENGTH(nom) <= 3;

mysql> SELECT nom FROM municipi

-> WHERE (LOWER(nom) LIKE ’%o%’
-> OR LOWER(nom) LIKE ’%u%’)
-> AND LENGTH(nom) <= 3;

Actividad: escribir una consulta para cada uno de estos requerimientos:

Municipios que tengan diez As en su nombre (sólo hay uno).

Municipios que tengan sólo un tipo de vocal en su nombre. Esto incluye: todos los que se escriben sólo
con A, todos los que llevan sólo E, . . . en una sóla consulta. Ejemplo: “Tolox” y “Jun” están bien, pero
“Portbou” no. Usa paréntesis para agrupar condiciones, y escribe todo en una sola consulta.

4. Búsqueda con condiciones numéricas

Los operadores numéricos que se usan son: <, >, ! =, <= y >=. Ejemplos:

SELECT nom FROM municipi

WHERE poblacio2003 > 1000000;

SELECT nom,poblacio1991, poblacio2003

FROM municipi
WHERE poblacio2003 = 0

AND poblacio1991 > 0;

Adicionalmente, existe la posibilidad de usar operadores de agregación, que usan los valores de una

columna completa. Los más típicos son:

AVG: promedio.

MIN y MAX: mínimo y máximo.

SUM: suma.

COUNT: contar.

Ejemplos de consultas con operadores de agregación:

SELECT MAX(poblacio2003) FROM municipi;
SELECT poblacio2001-poblacio1991 FROM municipi;
SELECT AVG(superficie) FROM municipi;
SELECT nom FROM municipi

WHERE poblacio2003 =

(SELECT MAX(poblacio2003) FROM municipi);

Las columnas numéricas se pueden usar para ordenar usando ORDER BY (o ORDER BY col DESC para
ordenar en orden descendente. Adicionalmente se puede usar LIMIT para restringir el máximo de elementos
a retornar.

3

SELECT nom FROM municipi ORDER BY poblacio2003 DESC LIMIT 10;

Actividad: escribir una consulta para cada uno de estos requerimientos:

Nombre de los 3 municipios menos densamente poblados en 1991. Densidad = Población / Superficie.

Nombre de los municipios que tienen una superficie mayor a 1/3 del promedio.

Nombre y población en el 1996 y en el 2001 de los municipios que tienen una población 10 veces mayor
en el 2001 que en 1991, pero que tenían habitantes el 1991 (hay sólo uno).

5. Búsqueda en más de una tabla

Para buscar en más de una tabla, lo que se necesita es unir ambas tablas por una columna que tenga el
mismo significado. En este caso, la tabla comunitat tiene una columna ca id, y la tabla municipi también.

SELECT municipi.nom,comunitat.nom

FROM municipi,comunitat
WHERE municipi.ca_id=comunitat.ca_id

AND comunitat.nom = ’Catalunya’

ORDER BY municipi.nom;

SELECT comunitat.nom, SUM(municipi.poblacio2003)

FROM municipi,comunitat
WHERE municipi.ca_id=comunitat.ca_id
GROUP BY comunitat.nom
ORDER BY comunitat.nom;

Actividad: escribir una consulta para cada uno de estos requerimientos (una sola consulta por requer-

imiento):

Población total de cada comunidad.

Variación porcentual de la población entre 2001 y 2003 en cada comunidad.

Población en el 2003 de Murcia.

Superficie combinada del País Vasco y Andalucía.

Importante: es una sola consulta por requerimiento, no vale hacer dos SELECT para responder una

pregunta.

6. RESULTADO

Lo que se debe entregar es todos los comandos SQL que ejecuten todas las actividades de esta sesión.
El resultado de esta práctica debe entregarse hoy vía Campus Global.

4