SQL funcions

L'article principal de MySQL el trobareu aquí.

Dintre d'un SELECT podem incloure operacions aritmètiques estàndard amb els operadors (+,-,*,/), per exemple:

mysql> SELECT 1+3*4;

Funcions algebraiques

Les més conegudes són:

• AVG(): de l'anglès average, és la mitjana
• SUM(): efectua la suma dels registres

Funcions de data i hora

En particular ens interessen les funcions de data i hora per manipular-les. L'article principal el trobareu a funcions de data i hora de MySQL.

Utiltizarem de nou l'exemple Fitxer:Videoclub.sql.

Destaquem les següents funcions:

• NOW(): retorna data i hora.
• CURRENT_DATE(): retorna la data actual.
• CURRENT_TIME(): retorna el temps actual.
• DATEDIFF( <data1>,<data2> ): retorna la diferència de temps (dies) entre 2 dates).
• DATE_FORMAT( , <format>): formata la data segons es necessiti.

NOW es molt comuna per introduïr registres de qualsevol tipus. Per exemple, en una web, quan s'introdueix una dada nova (com un missatge en un llibre de visites), podem posar la data i hora en què es va crear el registre amb aquesta funció.

Per exemple:

mysql> INSERT INTO <taula> ( <col1>, <col2>, ... , data)
       VALUES ( <val1>, <val2>, ... , NOW() );

DATEDIFF ens permetrà conèixer la diferència de dies entre 2 dates. Per exemple, en el cas del videoclub podem saber els dies que un client ha tingut una peli amb la consulta:

mysql> SELECT data_entrada, data_sortida, DATEDIFF(data_entrada,data_sortida) as dies
       FROM lloguers;

El què ens hauria de donar:

+---------------------+---------------------+------+
| data_entrada        | data_sortida        | dies |
+---------------------+---------------------+------+
| 2009-10-13 18:00:00 | 2009-10-12 00:00:00 |    1 | 
| 2009-10-13 00:00:00 | 2009-10-12 00:00:00 |    1 | 
| 2009-10-14 00:00:00 | 2009-10-13 00:00:00 |    1 | 
| 2009-10-14 00:00:00 | 2009-10-13 00:00:00 |    1 | 
| 2009-10-16 00:00:00 | 2009-10-14 00:00:00 |    2 | 
| 2009-10-17 00:00:00 | 2009-10-16 00:00:00 |    1 | 
| 2009-10-18 00:00:00 | 2009-10-17 00:00:00 |    1 | 
| 2009-10-19 00:00:00 | 2009-10-18 00:00:00 |    1 | 
| 2009-10-18 00:00:00 | 2009-10-18 00:00:00 |    0 | 
| 2009-10-20 00:00:00 | 2009-10-19 00:00:00 |    1 | 
| 2009-10-22 00:00:00 | 2009-10-20 00:00:00 |    2 | 
| 2009-10-25 00:00:00 | 2009-10-24 00:00:00 |    1 | 
| 2009-10-26 00:00:00 | 2009-10-25 00:00:00 |    1 | 
| 2009-10-26 00:00:00 | 2009-10-25 00:00:00 |    1 | 
| 2009-10-27 00:00:00 | 2009-10-25 00:00:00 |    2 | 
+---------------------+---------------------+------+

Si, a més, volem canviar el format de la data d'entrada, podem utilitzar DATE_FORMAT. Per una llista de les possibilitats d'aquesta funció podeu clicar aquí.

Proveu aquesta:

mysql> SELECT DATE_FORMAT(data_sortida,'%W %M %Y') as sortida , DATEDIFF(data_entrada,data_sortida) as dies
       FROM lloguers;

Com podeu veure, %W ens calcula el día de la setmana (i ens ho mostra en anglès). %M ens posa el mes (també en anglès) i %Y ens mostra l'any amb números.

Exercicis

Realitza les següents views amb el nom view_data_<num>:

1. Mostra els lloguers amb la persona que l'ha llogat, el nom del film i el preu a cobrar-li si cada dia que passa el cobrem a 3€.
2. Busca a la web el format adequat perquè aparegui la data de sortida amb:
   • el nom del dia de la setmana (Monday, Tuesday, ...)
   • el número del dia de l'any (000-365) ENTRE PARÈNTESIS
   • el número del dia del mes (00-31)
   • el mes amb el nom i el número entre parèntesis
   • l'any amb 2 dígits i prou

   o sigui que us ha de quedar així:

+--------------------------------+
| exercici de formats            |
+--------------------------------+
| Monday (285) October(10) 09    | 
| Monday (285) October(10) 09    | 
| Tuesday (286) October(10) 09   |