SQL (Structured Query Language)

Índice de contenido

Què és SQL?

Llenguatge de consultes estructurat
Creat per IBM
S’ha convertit en un estàndar, soportat per la majoria de bases de dades
Bàsicament ens permet fer consultes.
Però té més funcions:
- Definició
- Control
- Gestió
Sentències SQL: Que volem obtenir i d’on

Sentències simples

SELECT principal sentència SQL. Permet recuperar dades.

FROM de quina taula volem recuperar.

Selecció de les columnes :

* l’asteric torna totes les columnes.

Columna1, columna2, columna n : columnes separades per comes.

WHERE quines condicions

Estructura:

SELECT nom_camps FROM nom_taula WHERE condició

SQL a ACCESS

Crear una consulta en modo Disseny
Seleccionar a “Consultes” que la query és SQL.
S’obre una finestra on escriure sentències SQL.

Primeres consultes

SELECT * FROM productes

Torna totes les columnes de la taula Productes

SELECT Preu, Quantitat FROM productes

Torna les columnes Preu i Quantitat de la taula productes

SELECT Nom FROM productes WHERE Preu > 100

Torna el nom del producte, de la taula productes que el seu Preu sigui superior a 100

Condicions

WHERE

AND i OR

Expresions : > , < , <> , etc...

Estructura:

SELECT nom_camps FROM nom_taula WHERE condició AND condició

SELECT nom_camps FROM nom_taula WHERE condició OR condició

SELECT Nom FROM productes WHERE Preu > 100 Or Tipus=“Memòria”

Torna el nom del producte, de la taula productes que el seu Preu sigui superior a 100 o que sigui de tipus Memòria.

Consultes simples

SELECT Nom “Nom del Producte” FROM productes WHERE Preu > 100 Or Tipus=“Memòria”

Torna el nom del producte en una nova columna que es dius “Nom del Producte”, de la taula productes que el seu Preu sigui superior a 100 o que sigui de tipus Memòria.

Consultes Simples: Like

Like / NOT Like

L’operador [NOT] LIKE serveix per comparar una cadena (part esquerra de l’operador) amb una cadena patró (part dreta de l’operador) i pot contenir els següents caràcters especials:

% per indicar qualsevol cadena de zero o més caràcters.

_ per indicar qualsevol caràcter

LIKE 'Torres' Compara amb la cadena ‘Torres’.

LIKE 'Torr%' Compara amb qualsevol cadena iniciada per ‘Torr’.

LIKE '%S%' Compara amb tota cadena que contingui una ‘S’.

LIKE '_o%' Compara amb qualsevol cadena que tingui una ‘o’per segon caràcter.

LIKE '__' Compara amb qualsevol cadena de dos caràcters.

Consultes simples. Between. In. Not Null

Conjunt d’operadors de comparació:

[NOT] BETWEEN <valor_1> AND <valor_2> Permet efectuar la comparació entre dos valors.

[NOT] IN (llista_valors) Permet comparar amb una llista de valors.

IS [NOT] NULL que permet reconèixer si estem davant d’un valor NULL.

Consultes Simples. Exemples

Mostrar els productes que tenen un preu entre 15 € i 25 €:

SELECT producte, preu FROM productes WHERE preu >= 15 and preu <= 25

SELECT producte, preu FROM productes WHERE preu between 15 and 25;

Mostrar els amics de les poblacions 2 i 15:

SELECT codi_amic, nom_cognoms FROM amics WHERE codi_poblacio=2 or codi_poblacio=15;

SELECT codi_amic, nom_cognoms FROM amics WHERE codi_poblacio in (2,15);

SELECT codi_amic, nom_cognoms FROM amics WHERE codi_poblacio = ANY (2,15);

Consultes simples. ANY. ALL

ANY: Permet efectuar una comparació genèrica (=, <>, !=, >,<, >=, <=) amb qualsevol dels valors de la dreta.

<comparador genèric> ANY (llista_valors_de_SELECT)

ALL: Permet efectuar una comparació genèrica (=, <>, !=, >,<, >=, <=) amb tots els valors de la dreta.

<comparador genèric> ALL (llista_valors_de_SELECT)

Observem que:

=ANY és equivalent a IN

!=ALL és equivalent a NOT IN

DISTINCT. COUNT

COUNT(*) Compta registres.

* Ho compta tot

COUNT (id_producte) (sobre la taula productes) compta quants productes hi ha en la taula.

DISTINCT Elimina repetits. Més lent. Internament executa primer l’ordre ORDER BY.

SELECT nom FROM productes; Torna tots els noms de productes.

SELECT distinct nom FROM productes; Torna tots els noms de productes sense repetits.

ORDER BY

Permet ordenar els resultats

ASC Ascendent (per defecte)

DESC Descent

Estructura:

SELECT columna1, columna2

FROM nom taula WHERE condició ORDER BY columna1 ASC/DESC, columna2 ASC/DESC

Exemples

Comptar quants productes hi ha en la base de dades.

SELECT count(*) "Quants productes" FROM productes;

Mostrar quants fabricants hi ha que tinguin productes en la base de dades.

SELECT count(distinct id_fabricant) "Quants fabricants amb productes“ FROM productes;

Mostrar els codis, títols i preus dels productes ordenats de forma ascendent pel seu preu i ordenatspel títol quan tinguin el mateix preu.

SELECT codi_producte, titol, preu

FROM productes ORDER BY preu, titol;

Mostrar el codi i el títol dels productes ordenats de forma descendent per la longitud del seu títol.

SELECT codi_producte, titol FROM productes ORDER BY length(titol) DESC;

Funcions incorporades

AVG() Mitjana

SELECT AVG(Preu) “Preu mig” FROM productes

AVG (preu) (sobre la taula productes) retorna el preu mitjà de tots els productes seleccionats que tenen preu (els nuls s’ignoren). Retorna NUL si no hi ha cap registre seleccionat.

MAX() Valor màxim, retorna el preu més alt.

SELECT MAX(preu) "Preu màxim“ FROM productes

MIN() Valor mínim

SUM() Suma valors

LENGHT (cad) longitut d’una paraula

LENGTH("Diumenge") retorna 8

SELECT nom FROM productes WHERE Length(nom) > 10

TRIM() elimina els espais blancs

TRIM(' Hola ') retorna 'Hola‘

SELECT TRIM(Nom) FROM productes

UCASE() converteix a majúscules.

UCASE ('Avui') retorna 'AVUI‘

SELECT UCASE(Nom) FROM productes

Combinacions entre taules

Com combinar taules?
Com posar àlies a les taules?

Exemple:

SELECT codi_amic "Codi", nom_cognoms "Amic", poblacio "Població“ FROM amics , poblacions WHERE amics.codi_poblacio = poblacions.codi_pob ORDER BY nom_cognoms

SELECT codi_amic "Codi amic", nom_cognoms "Amic", codi_poblacio "Codi població", poblacio "Població" FROM amics a, poblacions p

WHERE a.codi_poblacio = p.codi_pob ORDER BY nom_cognoms;

Subconsultes

Una subconsulta és una sentència SELECT que s’inclou en la clàusula WHERE d’una altra sentència SELECT. La subconsulta es tanca entre parèntesis i no inclou el punt i coma finals.

Mostrar els productes que tenen preu igual o superior al preu mitjà dels productes actuals:

SELECT codi_producte "Codi", titol "Títol“

FROM productes WHERE preu > (SELECT avg(preu) FROM productes);

GROUP BY … HAVING

La clàusula GROUP BY permet agrupar les files resultants de les clàusules SELECT, FROM i WHERE segons una o diverses de les columnes seleccionades.
La clàusula HAVING permet especificar condicions de filtratge sobre els grups assolits per la clàusula GROUP BY.

Sintaxi:

SELECT [distinct] <expressió/columna>, <expressió/columna>,...

FROM <taula>, <taula>,...

WHERE <condició_de_recerca>

GROUP BY <àlias/columna>, <àlias/columna>,...

HAVING <condició_sobre_grups>

ORDER BY <expressió/columna> [ASC/DESC], <expressió/columna> [ASC/DESC],...;

Exemple:

Mostrar quants productes hi ha de cada tipus de suport:

SELECT tipus “Tipus", count(*) "Quants productes" FROM productes GROUP BY tipus;

Mostrar els suports per als quals hi ha productes en la base de dades, amb el preu de producte més alt, i ordenada pel màxim dels preus:

SELECT tipus “Tipus", max(preu) "Preu màxim"

FROM productes GROUP BY tipus ORDER BY "Preu màxim";

Mostrar el nombre de productes que hi ha de cada tipus de suport per als tipus que tenen més de deu productes:

SELECT tipus “Tipus", count(*) "Quants productes"

FROM productes GROUP BY tipus having count(*) > 10;

Connexió php / bases de dades relacionals

introducció

Una de les funcionalitats principals de PHP és la capacitat de comunicar-se amb bases de dades relacionals.
De fet, hi ha funcions per relacionar-se amb la majoria de bd del mercat:
- MySql
- Oracle
- Informix
- PostgreSQL
- Microsoft SQL Server

Funcions mysql

Consultar php.net: http://es2.php.net/manual/es/ref.mysql.php

Funcions principals

(A partir de la version 5 se le añade a los comnados la letra i (i=mejorado) ej. mysqli_connect)

mysql_connect connectem amb el gestor MySQL

mysql_select_db seleccionem la base de dades amb la que volem treballar

mysql_query executa la query que volguem sobre la taula

mysql_close tanca la connexió amb el gestor MySQL

mysql_error() torna l’error que ha donat qualsevol de les funcions

mysql_fetch_array retorna un vector associatiu amb lees dades de la taula

mysql_num_rows() quantes files torna la query select.

mysql_connect

mysql_connect (servidor, usuari, clau)

Servidor: on es troba el gestor

Usuari: usuari amb el que ens connectem

Clau: password de l’usuari amb el que ens connectem

Retorna el link a MySQL o False si la connexió ha fallat.

Crear Base de dades, Exemple

CREATE TABLE alumnes (

codi int(11) NOT NULL auto_increment,

nom varchar(40) default NULL,

cognom varchar(40) default NULL,

mail varchar(50) default NULL,

codicurs int(11) default NULL,

PRIMARY KEY (`codi`)

)

Formulari per l’exemple

<html>

<head>

<title>Exercici d’exemple: exemple1mysql.html</title>

</head>

<body>

<h1>Alta d' Alumnes</h1>

Nom: <input type="text" name="nom"><br/>

Cognom: <input type="text" name=“cognom"><br/>

Mail: <input type="text" name="mail"><br/>

Seleccioni el curs:

</select> <br/>

</form>

</body>

</html>

mysql_close()

mysql_connect (punter)

Punter: punter a la connexió MySQL

Retorna TRUE si s’ha tancat correctament o FALSE si la connexió ha fallat.

Connexió Exemple 1

<html>

<head>

<title>Exercici d’exemple: exemple1mysql.php</title>

</head>

<body>

<?php

$conexion=mysql_connect("localhost","root","") or

die("Problemes en la connexió");

</body>

</html>

Missatges d’error

mysql_error : torna l’error de la última funció mysql executada.

Exemple:

<?php

$conexion=mysql_connect("localhost","roo t","") or

die("Problemes en la connexió“.mysql_error());

Connexió Exemple 2

<html>

<head><title>Exercici d’exemple: exercici1mysql.php</title></head>

<body>

<?php

$conexion=mysql_connect("localhost","root","");

if ($conexion==FALSE){

echo "Ha fallat la connexió <br/>";

} else {

echo "S ha connectat perfectament <br/>";

mysql_close($conexion);

}

</body>

</html>

Connexió Exemple 3

<html>

<head><title>Exercici d’exemple: exercici1mysql.php</title></head>

<body>

<?php

$conexion=mysql_connect("localhost","root","") or

die("Problemes en la connexió");

echo "S ha connectat perfectament <br/>";

mysql_close($conexion);

</body>

</html>

Connexió Exemple 4

<?php

$conexion=mysql_connect("localhost","root","");

if ($conexion==FALSE){

echo “Ha fallat la connexió <br/>”;

} else {

echo “S’ha connectat perfectament <br/>”;

$resultat=mysql_close($conexion);

if ($resultat==TRUE){

echo “La connexió s’ha tancat bé <br/>”;

} else {

echo “La connexió no s’ha tancat bé <br/>”;

}

Seleccionar la bd

mysql_select_db (base de dades, identificador)

Base de dades: on es troba les nostres taules

Identificador: identifica la connexió

Torna TRUE si ha trobat la bd o FALSE en cas contrari.

Exemple

mysql_select_db (“Academia",$conexion) or die ("Problemes en la selecció de la base de dades");

Insertar / Buscar / Modificar dades

mysql_query (consulta, identificador)

Aquesta funció envia una consulta a MySQL

Consulta: la query que volen enviar

Identificador: paràmetre opcional

Retorna valors diferents segons la sentència:

INSERT, UPDATE, DELETE, DROP: true o false

SELECT : retorna les files o false si ha anat malament.

Insertar dades

mysql_query ("INSER INTO alumnes(nom, cognom, mail, codicurs) values (

'$_REQUEST[nom]',

'$_REQUEST[cognom]',

'$_REQUEST[mail]',

'$_REQUEST[codigcurs]'

)”,$conexion

) or die ("Problemes en el SELECT".mysql_error());

Exemple

<?php

foreach ($_REQUEST as $clau =>$Valor){

echo "camp: $clau valor: $Valor <br/>";

}

...

mysql_query("INSERT INTO alumnes(nom,cognom,mail,codicurs)

values(

'$_REQUEST[nom]',

'$_REQUEST[cognom]',

'$_REQUEST[mail]',

'$_REQUEST[codicurs]'

)",$conexion

) or die("Problemes en el SELECT".mysql_error());

Exemple complert

<html> <head><title>Exercici d’exemple: exercici1mysql.php </title></head>

<body>

<?php

$conexion=mysql_connect("localhost","root","");

mysql_select_db(“Academia",$conexion) or

die("Problemas en la seleccion de la base de datos");

mysql_query("insert into alumnes(nom, cognom, mail, codicurs)

values(

'$_REQUEST[nom]',

'$_REQUEST[cognom]',

'$_REQUEST[mail]',

'$_REQUEST[codicurs]'

)", $conexion

) or die("Problemes en el SELECT".mysql_error());

mysql_close($conexion);

</body>

</html>

jueves, 14 de febrero de 2013