IMPORTANT: Per accedir als fitxer de subversion: http://acacha.org/svn (sense password). Poc a poc s'aniran migrant els enllaços. Encara però funciona el subversion de la farga però no se sap fins quan... (usuari: prova i la paraula de pas 123456)

Linux Professional Institute Certificate. LPIC-1

Lpic1 large.gif
Certificat LPIC-1
Examen: 102 (torneu a la resta de temes)
Fitxers: LPI105.3GestioDeDadesSQL.pdf (LPI105.3GestioDeDadesSQL.odp)
Objectius: http://www.lpi.org/eng/certification/the_lpic_program/lpic_1/exam_102_detailed_objectives
Dipòsit SVN: https://svn.projectes.lafarga.cat/svn/lpi/Materials/Examen_102/105.3
Usuari: anonymous
Paraula de pas: qualsevol paraula de pas

Objectius del tema

105.3. Gestió de dades SQL
Objective.jpg
060317 Icon - Theory of Knowledge v01 pdc.jpg

Àrees Clau de Coneixement:

Icon list.jpg

La següent és una llista parcial de fitxers, termes i utilitats utilitzades:

Text-editor.svg

Apunts: LPI 105.3. Gestió de dades SQL

Plantilla per prendre apunts

SQL (Standard Query Language)

SQL és el llenguatge de consultes estructurades (sigles en anglès: Structured Query Language). Es tracta d'un llenguatge declaratiu d'accés a bases de dades relacionals que permet realitzar diversos tipus d'operacions en una base de dades.

SQL és un llenguatge de 4a generació.

NOTA: Els llenguatges declaratius a diferència dels imperatius no especifiquen quina són les instruccions que ha de dur a terme l'ordinador sinó que indiquen o declaren el que volen obtenir i és l'interpret del llenguatge declaratiu qui genera les ordres necessàries per executar el "programa declaratiu". En el cas de SQL, és el gestor de base de dades qui interpreta el llenguatge SQL i realitza les accions necessàries sobre la base de dades.

SQL és pot utilitzar de dos formes:

  • SQL interactiu: llenguatge de consultes basat en l'àlgebra relacional que també té suport per a inserir, modificar i eliminar dades
  • SQL immers : En angles anomenat SQL embedded (la traducció podria ser SQL encastat o "empotrat").

NOTA: Aquesta classificació no ens ha de fer pensar que hi ha dos llenguatges SQL, més aviat són dos maneres d'utilitzar SQL (de forma interactiva, normalment utilitzant la consola o una aplicació d'un SGBDR, i de forma programàtica amb llenguatges de programació)

En aquesta article treballarem tota l'estona amb SQL interactiu.

Les operacions que es poden realitzar amb un llenguatge com SQL es poden classificar en els següents Subconjunts de SQL:

Els objectius de LPI només demanen conèixer les operacions més habituals dels DML que són:

  • Consulta: Obtenir informació de la base de dades (en SQL la clàusula o paraula reservada SELECT). També inclou les (sub)clàusules opcionals de SELECT com FROM, WHERE, GROUP BY, JOIN o ORDER BY.
  • Inserció: Afegir informació de la base de dades (en SQL la clàusula o paraula reservada INSERT)
  • Modificació: Modificar informació de la base de dades (en SQL la clàusula o paraula reservada UPDATE)
  • Eliminació: Eliminar informació de la base de dades (en SQL la clàusula o paraula reservada DELETE)

Recursos:

Història

SQL va ser desenvolupat a IBM per Donald D. Chamberlin i Raymond F. Boyce a principis de la decada dels 70 (1970). La versió inicial es va anomenar SEQUEL i estava dissenyada per treballar amb System R un sistema de base de dades de IBM.

SEQUEL es va canviar a SQL per que era una marca registrada per una altra companyia.

Estandarització

Consulteu http://en.wikipedia.org/wiki/SQL#Standardization.

Elements del llenguatge

El llenguatge SQL està dividit en els següents elements de llenguatge:

  • Clàusules: Les clàusules (algunes són opcionals) són els components principals de les declaracions o les consultes
  • Expressions: poden produir valors escalars o taules consistents en columnes i files.
  • Predicats: especifiquen condicions ( SQL three-valued logic (3VL) Boolean truth values) que permeten limitar els efectes d'una declaració o consulta o permeten controlar el flux del programa
  • Consultes (Querys): obtenen informació de la base de dades segons uns criteris específics.
  • Declaracions (Statements): poden tenir efectes persistents sobre l'estructura o les dades de la base de dades o poden controlar transaccions, el flux del programar, les connexions, diagnòstics, etc.
  • Les declaracions SQL poden acabar en punt i coma (semicolon ; ) que indica el final d'una declaració. No és obligatori a totes les plataformes però està definit a la gramàtica estàndard de SQL.
  • Els espais en blanc extres són normalment ignorats a SQL.
AnatomiaSQL.png

Null and Three-Valued Logic (3VL)

El Nul (Null o unknown) a SQL indica la falta d'informació. És diu que SQL utilitza un lògica de 3 valors (3VL) per que els valors lògics poden ser certs (true), falsos (false) o nuls (null). Cal tenir-ho en compte al fer comparacions i operacions lògiques com ORs o ANDs. Les següents taules lògiques us indiquen quins són els resultats amb lògica de 3 valors:

p AND q p
True False Unknown
q True True False Unknown
False False False False
Unknown Unknown False Unknown
p OR q p
True False Unknown
q True True True True
False True False Unknown
Unknown True Unknown Unknown
p NOT p
True False
False True
Unknown Unknown
p = q p
True False Unknown
q True True False Unknown
False False True Unknown
Unknown Unknown Unknown Unknown

Cal tenir en compte que SQL només retorna valors quan la clàusula WHERE retorna un valor de true. Això exclou resultats que siguin falsos o null.

A SQL disposeu dels operadors:

IS NULL 
IS NOT NULL

Gestors de bases de dades SQL

SQL és un estàndard de facto i es pot afirmar sense riscs a ser temeraris, que la gran majoria de gestors de bases de dades el suporten.

NOTA: Cal però indicar que no tots els gestors de base de dades suporten el mateix SQL. Hi ha diferents estàndards SQL i diferències en les implementacions de diferents fabricants. No us alarmeu però que tot el que veurem en aquest article és aplicable a qualsevol gestor de base de dades modern

Vegeu també:

MYSQL

Per a realitzar els exemples i els exercicis d'aquest article utilitzarem el que possiblement és el gestor de bases de dades lliures més utilitzat en l'actualitat (--acacha 17:09, 2 abr 2010 (UTC)): MySQL.

NOTA: Sabieu que l'origen del nom MySQL ve del nom de la filla del desenvolupador original (la filla de Michael Widenius és deia My)?

Per instal·lar un gestor de base de dades SQL en sistemes de la família Debian com per exemple Ubuntu podeu executar:

$ sudo apt-get install mysql-server

Durant la instal·lació us demanarà que assigneu una paraula de pas a l'usuari root.

Normalment el paquet mysql-server és un paquet ximple que instal·la la última versió del servidor MySQL disponible als repositoris de la vostra distribució. A una Ubuntu 9.10 (Karmic Koala) us instal·larà la versió:

mysql-server-5.1 

Al instal·lar el servidor de base de dades SQL també us ha instal·lat el client i les llibreries necessàries:

$ dpkg -l | grep mysql
...
ii  mysql-client                               5.1.37-1ubuntu5.1                          MySQL database client (metapackage depending
ii  mysql-client-5.1                           5.1.37-1ubuntu5.1                          MySQL database client binaries
ii  mysql-common                               5.1.37-1ubuntu5.1                          MySQL database common files (e.g. /etc/mysql
ii  mysql-server                               5.1.37-1ubuntu5.1                          MySQL database server (metapackage depending
...
ii  mysql-server-5.1                           5.1.37-1ubuntu5.1                          MySQL database server binaries
ii  mysql-server-core-5.1                      5.1.37-1ubuntu5.1                          MySQL database core server files
...

NOTA: Sovint volem instal·lar tot un sistema Linux-Apache-MySQL-PHP (LAMP). Aleshores es recomanable que utilitzeu tasksel:

$ sudo tasksel install lamp-server

Amb tasksel mateix podríeu instal·lar un servidor PostgreSQL.

Ara ja podeu utilitzar el client per tal de connectar-vos a la base de dades amb:

$ sudo mysql -p
[sudo] password for sergi: 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 110
Server version: 5.1.37-1ubuntu5.1 (Ubuntu) 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

mysql> 

NOTA: Observeu que la primera paraula de pas que us pregunten és la de sudo i després ve la de la base de dades!

Que és equivalent a:

$ mysql -u root -p

On:

  • -u: Indica amb quin usuari us voleu connectar a la base de dades.
  • -p: indica que voleu que us preguntin la paraula de pas (amb -P podeu indicar la paraula de pas directament però no és gaire segur oi?)

Ara ja podeu començar a utilitzar SQL des de la línia d'ordres que us proporciona el client MYSQL.

Clients gràfics i web MYSQL. PhpMyAdmin i phpPgAdmin

Existeixen múltiples clients SQL gràfics tant d'entorn d'escriptori com eines web. Possiblement l'eina més utilitzada és PHPMyAdmin però també s'utilitzen molts les eines que proveïx la empresa MySQL (primer la companyia sueca MySQL AB, després comprat per Sun i finalment propietat d'Oracle al ser comprat Sun per Oracle al 2009).

Podeu instal·lar algunes d'aquestes aplicacions amb:

$ sudo apt-get install mysql- (tabuleu!)
mysql-admin                  mysql-client-5.1             mysql-doc-5.0                mysql-navigator              mysql-server-4.1             mysql-server-core-5.1
mysql-admin-common           mysql-common                 mysql-enterprise-client-5.0  mysql-proxy                  mysql-server-5.0             mysql-server-data-5.1
mysql-client                 mysql-common-4.1             mysql-enterprise-server-5.0  mysql-query-browser          mysql-server-5.1             
mysql-client-4.1             mysql-community-client-5.0   mysql-gpl-client             mysql-query-browser-common   mysql-server-core            
mysql-client-5.0             mysql-community-server-5.0   mysql-gui-tools-common       mysql-server                 mysql-server-core-5.0   

Consulteu:

Base de dades d'exemple

Per a seguir els exemple d'aquest article utilitzarem la base de dades world.sql.

Podeu obtenir la base de dades de la web de MYSQL:

http://dev.mysql.com/doc/index-other.html

Podeu obtenir una còpia amb wget i descomprimir amb gunzip:

$ cd; mkdir world && cd world
$ wget http://downloads.mysql.com/docs/world.sql.gz
$ gunzip world.sql.gz

Ara anem a importar les dades a una base de dades MySQL:

$ mysql -u root -p
...

Creeu-la base de dades i la utilitzem:

mysql> CREATE DATABASE world;
mysql> USE world;

Ara carreguem els continguts del fitxer:

mysql> SOURCE world.sql;

Comproveu les taules amb:

> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| City            | 
| Country         | 
| CountryLanguage | 
+-----------------+
3 rows in set (0,00 sec)

I consultar altres bases de dades d'exemple a:

Per exemple per descarregar la base de dades employers:

$ cd; mkdir employers && cd employers
$ wget http://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
$ tar jxvf employees_db-full-1.0.6.tar.bz2 
employees_db/._load_departments.dump
employees_db/load_departments.dump
employees_db/load_dept_emp.dump
employees_db/._load_dept_manager.dump
employees_db/load_dept_manager.dump
employees_db/._load_employees.dump
employees_db/load_employees.dump
employees_db/._load_salaries.dump
employees_db/load_salaries.dump
employees_db/._load_titles.dump
employees_db/load_titles.dump
employees_db/._employees.sql
employees_db/employees.sql
employees_db/employees_partitioned.sql
employees_db/._employees_partitioned2.sql
employees_db/employees_partitioned2.sql
employees_db/employees_partitioned3.sql
employees_db/objects.sql
employees_db/test_employees_md5.sql
employees_db/test_employees_sha.sql
employees_db/Changelog
employees_db/._README
employees_db/README

Ara anem a importar les dades a una base de dades MySQL:

$ cd employees_db
$ sudo mysql -p < employees.sql

Ordres SQL bàsiques

Ordres de consulta

Les operacions més comunes amb bases de dades són són les consultes (querys) les quals es realitzen amb l'element de llenguatge SELECT. SELECT obté informació d'una o més taules d'una base de dades

Normalment les consultes SQL no provoquen canvis persistents a la base de dades.

NOTA: Algunes operacions no estàndard com SELECT INTO existents en alguns gestors de bases de dades poden tenir efectes sobre les dades.

Cal tenir en compte que les consultes només indiquen les dades que volem obtenir i no pas com s'han d'obtenir. És el gestor de base de dades (DBMS) el responsable de planificar, optimitzar i realitzar les operacions físiques per tal d'obtenir els resultats demanats.

SELECT

Les declaracions SELECT (SELECT statements) s'utilitzen per seleccionar dades d'una base de dades.

La sintaxi (simplificada) és:

SELECT
   [ALL | DISTINCT | DISTINCTROW ]
   select_expr [, select_expr ...]
   [FROM table_references
   [WHERE where_condition]
   [GROUP BY {col_name | expr | position}
     [ASC | DESC], ... 
   [HAVING where_condition]
   [ORDER BY {col_name | expr | position}
     [ASC | DESC], ...]
   [LIMIT {[offset,] row_count | row_count OFFSET offset}]

El resultat de la consulta (query) és guarda en una taula de resultat (result table) anomenada result-set (conjunt resultat).

Després de la paraula clau SELECT, sempre s'indica un llista de camps (select_expr) que volem mostrar com a resultat de la consulta. Es pot utilitzar l'asterisc (*) per tal d'indicar que volem mostrar totes les columnes implicades a les taules de la consulta

 mysql> USE world;
 mysql> SHOW TABLES;
 +-----------------+
 | Tables_in_world |
 +-----------------+
 | City            | 
 | Country         | 
 | CountryLanguage | 
 +-----------------+
 3 rows in set (0,00 sec)
 mysql> SELECT * FROM Country LIMIT 10;

NOTA: LIMIT permet limitar el resultat de la consulta a n files, en l'exemple les 10 primeres files

Es pot utilitzar també nom_taula.*

 
 mysql> SHOW COLUMNS FROM City;
 +-------------+----------+------+-----+---------+----------------+
 | Field       | Type     | Null | Key | Default | Extra          |
 +-------------+----------+------+-----+---------+----------------+
 | ID          | int(11)  | NO   | PRI | NULL    | auto_increment | 
 | Name        | char(35) | NO   |     |         |                | 
 | CountryCode | char(3)  | NO   |     |         |                | 
 | District    | char(20) | NO   |     |         |                | 
 | Population  | int(11)  | NO   |     | 0       |                | 
 +-------------+----------+------+-----+---------+----------------+
 5 rows in set (0,00 sec) 

 mysql> SHOW COLUMNS FROM Country;
 +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
 | Field          | Type                                                                                  | Null | Key | Default | Extra |
 +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
 | Code           | char(3)                                                                               | NO   | PRI |         |       | 
 | Name           | char(52)                                                                              | NO   |     |         |       | 
 | Continent      | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO   |     | Asia    |       | 
 | Region         | char(26)                                                                              | NO   |     |         |       | 
 | SurfaceArea    | float(10,2)                                                                           | NO   |     | 0.00    |       | 
 | IndepYear      | smallint(6)                                                                           | YES  |     | NULL    |       | 
 | Population     | int(11)                                                                               | NO   |     | 0       |       | 
 | LifeExpectancy | float(3,1)                                                                            | YES  |     | NULL    |       | 
 | GNP            | float(10,2)                                                                           | YES  |     | NULL    |       | 
 | GNPOld         | float(10,2)                                                                           | YES  |     | NULL    |       | 
 | LocalName      | char(45)                                                                              | NO   |     |         |       | 
 | GovernmentForm | char(45)                                                                              | NO   |     |         |       | 
 | HeadOfState    | char(60)                                                                              | YES  |     | NULL    |       | 
 | Capital        | int(11)                                                                               | YES  |     | NULL    |       | 
 | Code2          | char(2)                                                                               | NO   |     |         |       | 
 +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
 15 rows in set (0,00 sec)
 
 mysql> SELECT t1.*, t2.* FROM Country as t1 INNER JOIN City as t2 LIMIT 10;

NOTA: Fixeu-vos en l'ús d'alias (t1 i t2) per per als noms de taules

Els alias també es poden utilitzar a select_expr:

 mysql> SELECT CONCAT(Name, ' is in ', Continent) as whereis FROM Country LIMIT 3;
 +------------------------------------------+
 | whereis                                  |
 +------------------------------------------+
 | Afghanistan is in Asia                   | 
 | Netherlands is in Europe                 | 
 | Netherlands Antilles is in North America | 
 +------------------------------------------+
 3 rows in set (0,00 sec)

No es pot utilitzar:

 
mysql> SELECT t1.*, * FROM Country as t1 INNER JOIN City as t2 LIMIT 10;

NOTA: As és completament opcional, però cal tenir en compte que si ens oblidem el separador de columnes, aleshores el teòric segon camp es considerat com un alias:

 mysql> SELECT Name Continent FROM Country LIMIT 3;
 +----------------------+
 | Continent            |
 +----------------------+
 | Afghanistan          | 
 | Netherlands          | 
 | Netherlands Antilles | 
 +----------------------+
 3 rows in set (0,00 sec)

NOTA: No es poden utilitzar alias a la clàusula WHERE.

Les clàusules opcionals i paraules clau són:

  • FROM: Indica de quina taula o taules seran obtingudes les dades. La clàusula FROM pot incloure subclàusules opcionals JOIN per especificar les normes per a unir taules entre si.
  • WHERE: aquesta clàusula inclou un predicat de comparació, que restringeix el conjunt de files retornades per la consulta. La clàusula WHERE elimina totes les files del conjunt resultat per les quals les condicions del predicat no són avaluades com a certes (true).
  • GROUP BY: s'utilitza per agrupar les files del conjunt resultat amb valors comuns en conjunts de files més petites. GROUP BY s'utilitza sovint amb funcions d'agregació o s'utilitza per eliminar files duplicades. La clàusula WHERE sempre s'aplica abans que la GROUP BY.
  • HAVING: aquesta clàusula inclou un predicat que s'utilitza per filtrar files resultat d'una agrupació amb GROUP BY. També s'utilitzen sovint funcions d'agregació.
  • ORDER BY: ordena les files del conjunt resultat i també permet especificar en quina direcció han de ser ordenades (ASC, ordre ascendent o DESC, ordre descendent). Sinó s'utilitza ORDER BY, aleshores l'ordre del conjunt resultat no està definit a priori.

Totes les clàusules són opcionals fins i tot FROM ja que podem fer consultes com la següent:

 mysql> SELECT 1+1
     -> ;
 +-----+
 | 1+1 |
 +-----+
 |   2 | 
 +-----+
 1 row in set (0,00 sec)

Hi ha una taula especial anomenada DUAL que permet:

mysql> SELECT 1+1 FROM DUAL;
 +-----+
 | 1+1 |
 +-----+
 |   2 | 
 +-----+
 1 row in set (0,00 sec)

La taula no existeix però serveix per fer consultes amb FROM quan la consulta no agafa cap dada de cap taula.

Recursos

From

En una clàusula SELECT permet indicar quina taula o taules utilitzem com a fonts de dades. Si s'especifica més d'una taula aleshores s'està fent un JOIN. La clàusula FROM també s'utilitza en altres declaracions que no són consultes.

Order by

Permet ordenar les files d'un conjunt resultat. Per exemple per obtenir els 5 primers països en ordre alfabètic a la base de dades exemple world:

 mysql> SELECT Name FROM Country ORDER BY Name ASC LIMIT 5;
 +----------------+
 | Name           |
 +----------------+
 | Afghanistan    | 
 | Albania        | 
 | Algeria        | 
 | American Samoa | 
 | Andorra        | 
 +----------------+
 5 rows in set (0,02 sec)

O els últims 5:

 > SELECT Name FROM Country ORDER BY Name DESC LIMIT 5;
 +----------------+
 | Name           |
 +----------------+
 | Zimbabwe       | 
 | Zambia         | 
 | Yugoslavia     | 
 | Yemen          | 
 | Western Sahara | 
 +----------------+
 5 rows in set (0,00 sec)

O mostrar els 10 països més poblats:

mysql> SELECT Name, Population From Country ORDER BY Population DESC LIMIT 10;
+--------------------+------------+
| Name               | Population |
+--------------------+------------+
| China              | 1277558000 | 
| India              | 1013662000 | 
| United States      |  278357000 | 
| Indonesia          |  212107000 | 
| Brazil             |  170115000 | 
| Pakistan           |  156483000 | 
| Russian Federation |  146934000 | 
| Bangladesh         |  129155000 | 
| Japan              |  126714000 | 
| Nigeria            |  111506000 | 
+--------------------+------------+
10 rows in set (0,00 sec)


NOTA: L'ordre aplicat depèn del tipus de dades i els tipus de dades suportat depenen del tipus de gestor de base de dades, però normalment es pot ordenar de forma lògica per camps de text, numèrics, dates, etc.

Recursos:

Where

Serveix per mostrar només les files que compleixen amb un criteris específics.

 SELECT column_name(s)
 FROM table_name
 WHERE column_name operator value

Per exemple, per obtenir els països del continent Antarctica:

 > SELECT Name FROM Country WHERE Continent='Antarctica';
 +----------------------------------------------+
 | Name                                         |
 +----------------------------------------------+
 | Antarctica                                   | 
 | Bouvet Island                                | 
 | South Georgia and the South Sandwich Islands | 
 | Heard Island and McDonald Islands            | 
 | French Southern territories                  | 
 +----------------------------------------------+
 5 rows in set (0,00 sec)

O si volem mostrar tota la informació d'un pais:

 mysql> SELECT * FROM Country WHERE Name="Andorra";

Fixeu-vos que podeu utilitzar dos tipus de cometes, simples o dobles. Les cometes s'utilitzen per a indicar valors tipus text i de fet són obligatòries per a aquests tipus de columnes. No cal utilitzar-los amb camps numèrics (de fet si l'utilitzeu tindreu un error de sintaxi). Per exemple per obtenir la llista de països amb més de 30 milions d'habitants:

 mysql> SELECT Name, Population From Country WHERE Population > 30000000;

La següent és una taula dels operadors que es poden utilitzar a una clàusula WHERE:

Operador Descripció
= Igual
<> o != No igual o diferent
> Major
>= Major o igual
< Menor
<= Menor que
BETWEEN Indica un rang que inclou els extrems
LIKE Busca un patró
IN Indica una llista de valors possibles

Recursos:

Group by

S'utilitza conjuntament amb funcions d'agregació per agrupar el conjunt resultat en una o més columnes:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

Per exemple, per tal d'obtenir la llista de continents de la base de dades exemple world:

 mysql> SELECT Continent FROM Country GROUP BY Continent;
 +---------------+
 | Continent     |
 +---------------+
 | Asia          | 
 | Europe        | 
 | North America | 
 | Africa        | 
 | Oceania       | 
 | Antarctica    | 
 | South America | 
 +---------------+
 7 rows in set (0,00 sec)

Normalment s'utilitzen funcions d'agregació, per mostrar la població de cada continent:

> SELECT Continent, SUM(Population) as TotalPopulation FROM Country GROUP BY Continent ORDER BY TotalPopulation DESC;
+---------------+-----------------+
| Continent     | TotalPopulation |
+---------------+-----------------+
| Asia          |      3705025700 | 
| Africa        |       784475000 | 
| Europe        |       730074600 | 
| North America |       482993000 | 
| South America |       345780000 | 
| Oceania       |        30401150 | 
| Antarctica    |               0 | 
+---------------+-----------------+
7 rows in set (0,00 sec)

Les funcions d'agregació es poden utilitzar directament sense GROUP BY. Per exemple per calcular la població total del mon a partir de la base de dades exemple world:

 > SELECT SUM(Population) as TotalPopulation FROM Country;
 +-----------------+
 | TotalPopulation |
 +-----------------+
 |      6078749450 | 
 +-----------------+
 1 row in set (0,00 sec)

Podem agrupar per més d'un camp. Per exemple, per veure les regions i a quin continent pertanyen:

 SELECT Continent,Region FROM Country GROUP BY Continent,Region;
 +---------------+---------------------------+
 | Continent     | Region                    |
 +---------------+---------------------------+
 | Asia          | Eastern Asia              | 
 | Asia          | Middle East               | 
 | Asia          | Southeast Asia            | 
 | Asia          | Southern and Central Asia | 
 | Europe        | Baltic Countries          | 
 | Europe        | British Islands           | 
 | Europe        | Eastern Europe            | 
 | Europe        | Nordic Countries          | 
 | Europe        | Southern Europe           | 
 | Europe        | Western Europe            | 
 | North America | Caribbean                 | 
 | North America | Central America           | 
 | North America | North America             | 
 | Africa        | Central Africa            | 
 | Africa        | Eastern Africa            | 
 | Africa        | Northern Africa           | 
 | Africa        | Southern Africa           | 
 | Africa        | Western Africa            | 
 | Oceania       | Australia and New Zealand | 
 | Oceania       | Melanesia                 | 
 | Oceania       | Micronesia                | 
 | Oceania       | Micronesia/Caribbean      | 
 | Oceania       | Polynesia                 | 
 | Antarctica    | Antarctica                | 
 | South America | South America             | 
 +---------------+---------------------------+
 25 rows in set (0,00 sec)

Recursos:

Funcions d'agregació

S'utilitzen amb GROUP BY:

Funció Descripció
AVG() Calcula la mitja.
COUNT() Conta el número de files
FIRST() Mostra el primer valor
LAST() Mostra l'últim valor
MAX() Retorna el valor més gran
MIN() Retorna el valor més petit
SUM() Retorna la suma dels camps

Recursos:

Having

Permet aplicar restriccions a l'estil de WHERE però sobre els resultats de les funcions d'agregació. Per exemple si obtenim una llista del nombre de països per continent:

mysql> SELECT Continent,count(Code) FROM Country GROUP BY Continent;
+---------------+-------------+
| Continent     | count(Code) |
+---------------+-------------+
| Asia          |          51 | 
| Europe        |          46 | 
| North America |          37 | 
| Africa        |          58 | 
| Oceania       |          28 | 
| Antarctica    |           5 | 
| South America |          14 | 
+---------------+-------------+
7 rows in set (0,00 sec)

I només volem mostrar els continents amb més de 30 països:

> SELECT Continent,count(Code) as NumberOfCountries FROM Country GROUP BY Continent HAVING NumberOfCountries>30;
+---------------+-------------------+
| Continent     | NumberOfCountries |
+---------------+-------------------+
| Asia          |                51 | 
| Europe        |                46 | 
| North America |                37 | 
| Africa        |                58 | 
+---------------+-------------------+
4 rows in set (0,02 sec)

També es pot executar sense alias:

mysql> SELECT Continent,count(Code)  FROM Country GROUP BY Continent HAVING count(Code)>30;
+---------------+-------------+
| Continent     | count(Code) |
+---------------+-------------+
| Asia          |          51 | 
| Europe        |          46 | 
| North America |          37 | 
| Africa        |          58 | 
+---------------+-------------+
4 rows in set (0,00 sec)

Recursos:

Join

La paraula clau JOIN s'utilitza per tal d'obtenir dades de dos o més taules segons les relacions entre certes columnes d'aquestes taules. Les taules sovint estan relacionades entre si mitjançant claus:

  • Clau primària: és una columna o combinació de columnes amb un únic valor per cada fila. Les claus primàries identifiquen de forma unívoca un registre o fila d'una taula.
  • Claus foranies: són camps d'una taula que contenen una clau primària d'un altra taula.

Hi ha diferents tipus de JOINs:

  • JOIN o INNER JOIN: Mostra les files que tenen al menys una coincidència a les 2 taules. Si no s'indica JOIN i s'indiquen múltiples taules separades per coma (","), aleshores s'està fent un INNER JOIN.
  • LEFT JOIN: Mostra totes les files de la taula de l'esquerre inclòs si no hi ha cap coincidència a la taula de la dreta.
  • RIGHT JOIN: Mostra totes les files de la taula de la dreta inclòs si no hi ha cap coincidència a la taula de l'esquerre.
  • FULL JOIN: Mostra les files que tenen una coincidència al menys a una de les taules.

NOTA: Gestors de bases de dades com SQL proporcionen més tipus de JOINS.

La sintaxi d'un INNER JOIN és:

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Per exemple, per obtenir la llista d'empleats i el salaris cobrats a la base de dades exemple employees:

 mysql> USE employees;
 mysql> SHOW TABLES;
 +---------------------+
 | Tables_in_employees |
 +---------------------+
 | departments         | 
 | dept_emp            | 
 | dept_manager        | 
 | employees           | 
 | salaries            | 
 | titles              | 
 +---------------------+
 6 rows in set (0,00 sec)
 mysql> SHOW COLUMNS FROM employees;
 +------------+---------------+------+-----+---------+-------+
 | Field      | Type          | Null | Key | Default | Extra |
 +------------+---------------+------+-----+---------+-------+
 | emp_no     | int(11)       | NO   | PRI | NULL    |       | 
 | birth_date | date          | NO   |     | NULL    |       | 
 | first_name | varchar(14)   | NO   |     | NULL    |       | 
 | last_name  | varchar(16)   | NO   |     | NULL    |       | 
 | gender     | enum('M','F') | NO   |     | NULL    |       | 
 | hire_date  | date          | NO   |     | NULL    |       | 
 +------------+---------------+------+-----+---------+-------+
 6 rows in set (0,00 sec)
 mysql> SHOW COLUMNS FROM salaries;
 +-----------+---------+------+-----+---------+-------+
 | Field     | Type    | Null | Key | Default | Extra |
 +-----------+---------+------+-----+---------+-------+
 | emp_no    | int(11) | NO   | PRI | NULL    |       | 
 | salary    | int(11) | NO   |     | NULL    |       | 
 | from_date | date    | NO   | PRI | NULL    |       | 
 | to_date   | date    | NO   |     | NULL    |       | 
 +-----------+---------+------+-----+---------+-------+
 4 rows in set (0,00 sec)
 mysql> SELECT employees.emp_no, first_name, last_name, from_date, to_date  ,salary FROM employees INNER JOIN salaries ON salaries.emp_no=employees.emp_no LIMIT 10;
 +--------+------------+-----------+------------+------------+--------+
 | emp_no | first_name | last_name | from_date  | to_date    | salary |
 +--------+------------+-----------+------------+------------+--------+
 |  10001 | Georgi     | Facello   | 1986-06-26 | 1987-06-26 |  60117 | 
 |  10001 | Georgi     | Facello   | 1987-06-26 | 1988-06-25 |  62102 | 
 |  10001 | Georgi     | Facello   | 1988-06-25 | 1989-06-25 |  66074 | 
 |  10001 | Georgi     | Facello   | 1989-06-25 | 1990-06-25 |  66596 | 
 |  10001 | Georgi     | Facello   | 1990-06-25 | 1991-06-25 |  66961 | 
 |  10001 | Georgi     | Facello   | 1991-06-25 | 1992-06-24 |  71046 | 
 |  10001 | Georgi     | Facello   | 1992-06-24 | 1993-06-24 |  74333 | 
 |  10001 | Georgi     | Facello   | 1993-06-24 | 1994-06-24 |  75286 | 
 |  10001 | Georgi     | Facello   | 1994-06-24 | 1995-06-24 |  75994 | 
 |  10001 | Georgi     | Facello   | 1995-06-24 | 1996-06-23 |  76884 | 
 +--------+------------+-----------+------------+------------+--------+
 10 rows in set (0,00 sec)

Fixeu-vos que cal definir de quina taula venen els camps ambigus, sinó tindreu el següent error de sintaxi:

mysql> SELECT emp_no, first_name, last_name, from_date, to_date  ,salary FROM employees INNER JOIN salaries ON salaries.emp_no=employees.emp_no LIMIT 10;
ERROR 1052 (23000): Column 'emp_no' in field list is ambiguous

Es pot utilitzar més d'una taula en un JOIN, per exemple, per tal d'obtenir la llista d'empleats i el departament al que pertanyen:

 mysql> SHOW COLUMNS FROM departments;
 +-----------+-------------+------+-----+---------+-------+
 | Field     | Type        | Null | Key | Default | Extra |
 +-----------+-------------+------+-----+---------+-------+
 | dept_no   | char(4)     | NO   | PRI | NULL    |       | 
 | dept_name | varchar(40) | NO   | UNI | NULL    |       | 
 +-----------+-------------+------+-----+---------+-------+
 2 rows in set (0,00 sec)  
 
 mysql> SHOW COLUMNS FROM dept_emp;
 +-----------+---------+------+-----+---------+-------+
 | Field     | Type    | Null | Key | Default | Extra |
 +-----------+---------+------+-----+---------+-------+
 | emp_no    | int(11) | NO   | PRI | NULL    |       | 
 | dept_no   | char(4) | NO   | PRI | NULL    |       | 
 | from_date | date    | NO   |     | NULL    |       | 
 | to_date   | date    | NO   |     | NULL    |       | 
 +-----------+---------+------+-----+---------+-------+
 4 rows in set (0,00 sec)
 mysql>  SELECT employees.emp_no, first_name, last_name, dept_emp.dept_no, departments.dept_name FROM employees INNER JOIN dept_emp ON employees.emp_no=dept_emp.emp_no INNER JOIN departments ON departments.dept_no = dept_emp.dept_no  LIMIT 10;
+--------+-------------+-------------+---------+------------------+
| emp_no | first_name  | last_name   | dept_no | dept_name        |
+--------+-------------+-------------+---------+------------------+
|  10011 | Mary        | Sluis       | d009    | Customer Service | 
|  10038 | Huan        | Lortz       | d009    | Customer Service | 
|  10049 | Basil       | Tramer      | d009    | Customer Service | 
|  10060 | Breannda    | Billingsley | d009    | Customer Service | 
|  10088 | Jungsoon    | Syrzycki    | d009    | Customer Service | 
|  10098 | Sreekrishna | Servieres   | d009    | Customer Service | 
|  10112 | Yuichiro    | Swick       | d009    | Customer Service | 
|  10115 | Chikara     | Rissland    | d009    | Customer Service | 
|  10126 | Kayoko      | Valtorta    | d009    | Customer Service | 
|  10128 | Babette     | Lamba       | d009    | Customer Service | 
+--------+-------------+-------------+---------+------------------+
10 rows in set (0,02 sec)

La sintaxi d'un LEFT JOIN és:

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

NOTA: Alguns gestors de base de dades utilitzen LEFT OUTER JOIN

Per exemple, per obtenir les persones gestores (managers) dels diferents departaments a la base de dades exemple employees:

mysql> SELECT employees.emp_no, first_name, last_name, dept_manager.dept_no   FROM employees INNER JOIN dept_manager ON dept_manager.emp_no = employees.emp_no;
...
| 111534 | Hilary      | Kambil       | d008    | 
| 111692 | Tonny       | Butterworth  | d009    | 
| 111784 | Marjo       | Giarratana   | d009    | 
| 111877 | Xiaobin     | Spinelli     | d009    | 
| 111939 | Yuchang     | Weedman      | d009    | 
+--------+-------------+--------------+---------+
24 rows in set (0,00 sec)

Només hi ha 24 registres. Si fem la mateixa consulta amb LEFT JOIN obtindrem 300024 registres:

 SELECT employees.emp_no, first_name, last_name, dept_manager.dept_no   FROM employees LEFT JOIN dept_manager ON dept_manager.emp_no = employees.emp_no;
Query aborted by Ctrl+C
300024 rows in set (1,72 sec)

Que són el nombre de registres de la taula de l'esquerre del JOIN (taula employees):

mysql> SELECT count(employees.emp_no) FROM employees;
+-------------------------+
| count(employees.emp_no) |
+-------------------------+
|                  300024 | 
+-------------------------+
1 row in set (0,23 sec)

NOTA: Fixeu-vos com es mostra NULL per aquells camps que no tenen registre equivalent a la taula de la dreta.

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).

La sintaxi de RIGHT JOIN és:

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

El funcionament és molt similar al del LEFT JOIN però el que s'aplicava a la taula de la dreta ara s'aplica a la de l'esquerre i al revés.

La sintaxi de FULL JOIN és:

IMPORTANT: Alguns gestors de base de dades no suporten FULL JOIN, per exemple MySQL

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

La paraula clau FULL JOIN retorna totes les files de la taula de l'esquerre, i totes les files de la taula de la dreta. Els camps que no tinguin coincidència es mostraran amb el valor null.

Es pot simular un FULL JOIN amb la paraula clau UNION, fent una unió de les consultes LEFT JOIN i RIGHT JOIN:

SELECT employees.emp_no, first_name, last_name, dept_manager.dept_no FROM employees LEFT JOIN dept_manager ON dept_manager.emp_no = employees.emp_no UNION SELECT employees.emp_no, first_name, last_name, dept_manager.dept_no FROM employees RIGHT JOIN dept_manager ON dept_manager.emp_no = employees.emp_no;

Recursos:

Subconsultes

Les subconsultes són consultes que estan incloses en altres consultes. Vegem alguns exemples de la base de dades exemple world. Anem a fer consultes relacionades amb l'idioma català. Si executeu:

mysql> USE world;
mysql> SELECT * FROM CountryLanguage WHERE CountryLanguage.Language LIKE "catalan";
+-------------+----------+------------+------------+
| CountryCode | Language | IsOfficial | Percentage |
+-------------+----------+------------+------------+
| AND         | Catalan  | T          |       32.3 | 
| ESP         | Catalan  | F          |       16.9 | 
+-------------+----------+------------+------------+
2 rows in set (0,00 sec)

NOTA: MySQl fins a la versió 4.1 no suportava subconsultes

Veureu que el català es parla a Espanya i a Andorra. Ara imagineu-vos que voleu obtenir tota la informació sobre aquests dos països, podeu fer la subconsulta:

mysql> SELECT * FROM Country WHERE Country.Code IN (SELECT CountryCode FROM CountryLanguage WHERE CountryLanguage.Language LIKE "catalan"); 

Ha calgut utilitzar IN per què la subconsulta retorna més d'una fila:

 mysql> SELECT * FROM Country WHERE Country.Code = (SELECT CountryCode FROM CountryLanguage WHERE CountryLanguage.Language LIKE "catalan");
 ERROR 1242 (21000): Subquery returns more than 1 row

En canvi el Xhosa que es parla només a un pais:

 mysql > SELECT * FROM Country WHERE Country.Code = (SELECT CountryCode FROM CountryLanguage WHERE CountryLanguage.Language ="Xhosa");

UNION

Ordres de manipulació de dades

Les clàusules que es permeten manipular dades són ( Data Manipulation Language o DML):

  • INSERT: Permet afegir dades a les taules de la base de dades.
  • UPDATE: Permet modificar dades ja existents a les taules de la base de dades.
  • DELETE: Permet eliminar dades de les taules de la base de dades.

Insert

Les declaracions INSERT INTO serveixen per afegir registres a una taula de la base de dades.

Hi ha dos sintaxi a l'hora d'utilitzar INSERT:

INSERT INTO table_name
VALUES (value1, value2, value3,...)

Aquesta forma no especifica els noms de columna on les dades seran inserides, només s'especifiquen els valors. Només podeu utilitzar aquesta forma si indiqueu tots els camps de la taula i els poseu en l'ordre adient (i poseu bé els tipus de dades, per exemple, els text s'ha de posar entre cometes).

La segona forma permet especificar el nom de les columnes:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Catalunya és una nació? i un país? Depèn a qui li preguntis... Suposem que algun dia cal inserir un nou país a la taula Country. Consultem quins són els camps:

 mysql> SHOW COLUMNS FROM Country;
 +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
 | Field          | Type                                                                                  | Null | Key | Default | Extra |
 +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
 | Code           | char(3)                                                                               | NO   | PRI |         |       | 
 | Name           | char(52)                                                                              | NO   |     |         |       | 
 | Continent      | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO   |     | Asia    |       | 
 | Region         | char(26)                                                                              | NO   |     |         |       | 
 | SurfaceArea    | float(10,2)                                                                           | NO   |     | 0.00    |       | 
 | IndepYear      | smallint(6)                                                                           | YES  |     | NULL    |       | 
 | Population     | int(11)                                                                               | NO   |     | 0       |       | 
 | LifeExpectancy | float(3,1)                                                                            | YES  |     | NULL    |       | 
 | GNP            | float(10,2)                                                                           | YES  |     | NULL    |       | 
 | GNPOld         | float(10,2)                                                                           | YES  |     | NULL    |       | 
 | LocalName      | char(45)                                                                              | NO   |     |         |       | 
 | GovernmentForm | char(45)                                                                              | NO   |     |         |       | 
 | HeadOfState    | char(60)                                                                              | YES  |     | NULL    |       | 
 | Capital        | int(11)                                                                               | YES  |     | NULL    |       | 
 | Code2          | char(2)                                                                               | NO   |     |         |       | 
 +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
 15 rows in set (0,00 sec)

La sintaxi podria ser:
mysql> INSERT INTO Country VALUES ("CAT","Catalonia","Europe","Western Europe",32114,800,7475420,81.42,1424285,1392448,"Catalunya","Republic","Pepe Montilla?","Barcelona","CAT");
Query OK, 1 row affected, 2 warnings (0,03 sec)

Recursos:

Update

S'utilitza per modificar dades de les taules de la base de dades. La sintaxi és:

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

IMPORTANT: Noteu que s'utilitza la clàusula WHERE per restringir les files a les quals s'apliquen els canvis. Si no poseu la clàusula WHERE aleshores s'actualitzen totes les files de la base de dades

Seguint amb l'exemple de Catalunya, és força fàcil pensar que si algun dia Catalunya arriba a ser un país, Montilla no serà el president ;-). Per tant si volem actualitzar el registre que ara és:

mysql> SELECT * FROM Country WHERE Country.Code="CAT"; +------+-----------+-----------+----------------+-------------+-----------+------------+----------------+------------+------------+-----------+----------------+----------------+---------+-------+
| Code | Name      | Continent | Region         | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP        | GNPOld     | LocalName | GovernmentForm | HeadOfState    | Capital | Code2 |
+------+-----------+-----------+----------------+-------------+-----------+------------+----------------+------------+------------+-----------+----------------+----------------+---------+-------+
| CAT  | Catalonia | Europe    | Western Europe |    32114.00 |       800 |    7475420 |           81.4 | 1424285.00 | 1392448.00 | Catalunya | Republic       | Pepe Montilla? |       0 | CA    | 
+------+-----------+-----------+----------------+-------------+-----------+------------+----------------+------------+------------+-----------+----------------+----------------+---------+-------+
1 row in set (0,00 sec)

L'ordre UPDATE seria:

mysql> UPDATE Country SET HeadOfState="Victor Saule" WHERE Country.Code="CAT";
Query OK, 1 row affected (0,08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Recursos:

Delete

La declaració (statement) DELETE s'utilitza per a esborrar registres d'una taula de la base de dades.

La sintaxi és:

DELETE FROM table_name
WHERE some_column=some_value

IMPORTANT: Observeu l'ús de la clàusula WHERE que permet indicar quins són els registres que volem eliminar. Si no s'especifica WHERE aleshores esborrem tots els registres de la taula!

Seguint amb l'exemple de Catalunya, desgraciadament també es possible que per la forta oposició Catalunya deixi de ser un país (o és tracti d'un error a la base de dades introduït per un hacker "nacionalista" ;-)). Per esborrar el registre de Catalunya:

mysql> DELETE FROM Country WHERE Country.Code="CAT";
Query OK, 1 row affected (0,04 sec)

Es poden esborrar totes les files amb:

DELETE FROM table_name

o

DELETE * FROM table_name

NOTA: Alguns gestors de base de dades com MySQL tenen un ordre equivalent anomenada TRUNCATE

Recursos:

Funcions

Consulteu:

Mediawiki, namespaces i interwiki links

El programari amb el que està implementada aquesta wiki (mediawiki) utilitza espais de noms (nam spaces) que permeten fer enllaços ràpids amb altres pàgines o wikis. Teniu un per a SQL, només posant:

[[SQL:QUELCOM_SQL]]

Tindreu un enllaç directe a la documentació SQL () del que demaneu. Per exemple:

[[SQL:SELECT]]

Es converteix en:

SQL:SELECT

Un enllaç a:

http://www.w3schools.com/sql/sql_SELECT.asp

També si us fixeu està instal·lat la extensió de mediawiki:

Mediawiki#Sintax_Highlight

Que suporta codi SQL (observeu els codis d'exemple SQL que us trobareu arreu d'aquest article).

Vegeu també

Enllaços externs