SQL consultes I

Primers passos amb consultes en SQL (utilitzant MySQL).

Connectant-nos a MySQL

Per connectar-nos a la base de dades MySQL utiltizarem el programa de client des de línia de comandes de Linux. Se li ha d'especificar l'usuari amb el paràmetre -u i indiquem que volem introduïr password amb -p

$> mysql -u <usuari> -p

Un cop dintre de MySQL podem veure les bases de dades disponibles amb show databases.

mysql> show databases;

i podem seleccionar la base de dades amb la comanda use.

mysql> use <database>;

Nosaltres utilitzarem la base de dades de països "mundo":

mysql> use mundo;

Per mostrar les taules de la base de dades seleccionada fem show tables.

mysql> show tables;

I per saber el nom de les columnes utilitzem la comanda desc (de descripció).

mysql> desc Paises;

Fixeu-vos que el nom de les taules sol estar amb la primera lletra en majúscules. Així serà fàcil veure en una comanda SQL on hi ha una taula i on hi ha paraules reservades del llenguatge. També és convenient no utiltizar accents ni caràcters especials en els noms de taules i columnes per facilitar l'elaboració de sentències SQL i evitar errors.

Consultes bàsiques

Ens caldrà, de tant en tant, consultar la sintaxi de la sentència SELECT. Ho podem fer a la web de MySQL: Consultar a mysql.com

Per començar, visualitzem tot (*) de la taula Paises amb un SELECT:

mysql> SELECT * FROM Paises;

I com podem veure, tenim massa resultats i no ens hi caben a la pantalla. Podem limitar el nombre de resultats afegint "LIMIT" a la consulta (és una mica cutre fer-ho però pot ser pràctic):

mysql> SELECT * FROM Paises LIMIT 10;

I la nostra segona consulta, podem llistar tots els països (ara jo no posem "*") del continent anomenat "Asia". Com que volem triar, haurem d'utilitzar la condició WHERE:

mysql> SELECT Pais FROM Paises WHERE Continente="Asia";

Podem encadenar diferents condicions amb els operadors lògics AND i OR, per exemple, per llistar els països d'Asia i Europa:

mysql> SELECT Pais FROM Paises WHERE Continente="Asia" OR Continente="Europa";

Ara voldrem fer una consulta per comptar el nombre de països. Per això ens caldrà una funció, en aquest cas, count

mysql> SELECT count(*) FROM Paises WHERE Continente="Asia";

I per fer un llistat del nom dels països que comencen per "A" haurem d'utilitzar el comodí "%", que equival a "qualsevol cadena de caràcters". I per utilitzar els comodins no podem utiltizar el signe "=" sinó que ens caldrà l' operador LIKE:

mysql> SELECT Pais FROM Paises WHERE Pais LIKE "A%";

Per obtenir una llista dels continents podriem fer:

mysql> SELECT Continente FROM Paises;

però veurem que es van repetint els noms dels continents per cada registre de país de la taula. Si volem una llista sense repeticions ho podem fer utilitzant "DISTINCT":

mysql> SELECT DISTINCT Continente FROM Paises;

Finalment, per ordenar una llista podem utiltizar ORDER BY <columna> i afegir ASC (per defecte) o DESC a darrere.

mysql> SELECT DISTINCT Continente FROM Paises ORDER BY Continente;
mysql> SELECT DISTINCT Continente FROM Paises ORDER BY Continente DESC;

Comodins

Ara podem fer una llista de països del continent Oceanía. El problema és que per la línia de comandes els accents no funcionen del tot bé. Per evitar aquest problema podem utilitzar els comodins AMB LA PARTÍCULA LIKE:

• El comodí "%" substitueix qualsevol cadena de caràcters (amb nombre indeterminat de caràcters).
• El comodí "_" substitueix qualsevol caràcter, però només un.

mysql> SELECT Pais FROM Paises WHERE Continente LIKE "Ocean%";
(...o bé...)
mysql> SELECT Pais FROM Paises WHERE Continente LIKE "Ocean_a";

Agrupacions

Si volem fer un llistat dels contintents amb el nombre de països dels que consta cadscun d'ells, necessitarem utiltizar de nou la funció count i també el GROUP BY per poder agrupar el compte.

mysql> SELECT Continente,count(*) FROM Paises GROUP BY Continente;

Tingueu en compte que això no funciona sense el GROUP BY. El següent és incorrecte:

mysql> SELECT Continente,count(*) FROM Paises;

(proveu-ho igualment i esbrineu perquè dóna això)

IMPORTANT: quan feu agrupacions cal posar el camp del GROUP BY també en la llista de columnes a visualitzar. Si us fixeu en l'exemple anterior, Continente és el camp del GROUP BY. Per tant, també l'hem de posar al llistat de columnes del SELECT, al costat del count(*).

Views

Una view és una consulta emmagatzemada i que es pot utiltizar com si fos una taula. Per crear-les podem utiltizar la comanda CREATE VIEW. És a dir, CREATE VIEW <nom_view> AS <sentencia_select>;

Per exemple:

mysql> CREATE VIEW testview AS SELECT Pais,Capital FROM Paises;

Si volem comprovar els resultats de la VIEW la podem veure com una taula qualsevol:

mysql> desc testview;
mysql> SELECT * FROM testview;

Exercicis

Creeu les següents views. NOTES:

• NO feu servir SELECT *, la informació no es veu clara.
• Procureu també que es vegin tots els camps pertinents per comprovar que la consulta és correcta. Per exemple, si busqueu els països amb moneda anomenada "EURO" seleccioneu la columna "Pais" i també la de "Moneda".
• Al crear les views anomeneu-les amb el el vostre nom + "_" + el de la "tongada" + el número de consulta. Per exemple: enric_1.2, enric_2.3 (tongada 2, consulta 3).

Tongada 1:

1. Llistat de països de religió Musulmana.
2. Llistat de països de religió Animista.
3. Llistat de països de religió Catòlica.
4. Llistat de països d'Asia.
5. Llistat de països d'Amèrica.
6. Llistat de països d'Oceania.
7. Llistat de països que utilitzen el dòlar (o qualsevol moneda que tingui per títol dòlar, com per exemple, el "dòlar australià").
8. Llistat de països que utilitzen el peso.
9. Llistat de països que parlen Suahili.
10. Recompte de països de religió Musulmana.
11. Recompte de països de religió Animista.
12. Recompte de països de religió Catòlica.
13. Recompte de països de llengua Suahili.
14. Recompte de països de llengua Anglesa.

Tongada 2:

1. Llistat de països amb superfície inferior a 1.000 km2
2. Llistat de països amb superfície major de 5 milions de km2 (ordenats descendentment).
3. Llistat de països amb esperança de vida superior a 78 anys
4. Llistat de països amb esperança de vida inferior a 40 anys (I QUE NO VAL 0!!! en aquests casos és que no tenim dades)
5. Llistat de països amb població superior a 10 milions i inferior a 15 milions de persones.
6. Recompte de països per religió.
7. Recompte de països per llengua.
8. Recompte de països que tenen una superfície inferior a 1.000 km2
9. Mitjana d'esperança de vida dels països europeus (elimineu els països amb esperança=0).
10. Mitjana d'esperança de vida dels països africans (elimineu els països amb esperança=0).
11. Mitjana d'esperança de vida per continent.
12. Mitjana d'esperança de vida per religió.
13. Suma de superfície dels països asiàtics.
14. Suma de superfície dels països americans.
15. Suma de superfície per continent.

Tongada 3:

1. ...ja arribarà...

Exercici 2

Descarregueu la base de dades Fitxer:Ufs.sql, després importeu-la en la vostra màquina local i realitzeu les següents views:

1. Llistat de UFs de l'assignatura "Xarxes locals".
2. Llistat d'assignatures de la titulació de SMX.
3. Llistat d'assignatures de la ESO.
4. Recompte d'assignatures per titulació.
5. Llistat d'alumnes de Sant Vicenç
6. Recompte d'alumnes per població.
7. Recompte de matrícules fetes al curs 2009-2010.
8. Suma de les hores de les assignatures de SMX.
9. Suma de les hores de les UFs.
10. Recompte (suma) de les hores de les UFs per assignatura.
11. Recompte d'assignatures per professor.
12. Recompte de matrícules per UF.

TODO

(caràcter de ESCAPE: \)