Sql – Valor NULL i funcions estàndards

1

SQL – Unitat didàctica 3

Miriam Clavé Ibarz

Departament: InformàticaCicle: Desenvolupament d’Aplicacions Informàtiques

(Grau Superior)Crèdit C3: Anàlisi i disseny de base de dades

Curs: 2008/20095 de desembre 2008

2

SQL – UD 3 Valor NULL

Concepte Expressions IS NULL, IS NOT NULL

Funcions estàndard SQL Matemàtiques: ABS, SIN, COS, POWER, EXP,

SQRT, LOG De cadena de caràcters: SPACE, LENGHT,

LEFT, RIGHT, LOWER, UPPER, SUBSTRING, LTRIM, RTRIM, REVERSE

De data i hora: DATEPART

3

SQL – UD 3. Valor NULL Quan definim un camp com a valor NULL

vol dir: Que és un valor desconegut o que no existeix.

Utilitzem NULL quan alguna tupla ha de tenir algun o alguns atributs no coneguts.

El valor NULL NO és: Equivalent a cero. Ni és una cadena de caràcters buida.

4

SQL – UD 3. Valor NULL El resultat de qualsevol expressió

aritmètica amb null és null. Exemple: 5 + null retorna null

Qualsevol funció ignora els null excepte COUNT, REPLACE, CONCAT i GROUPING. Per exemple, si tenim un sumatori on

hi ha valors nulls o no hi ha valors, amb la funció SUM el resultat es NULL.

5

SQL – UD 3. Valor NULL Qualsevol comparació amb null retorna

unknown. Exemples: 5 < null o null <> null o null = null

Comportament dels operadors lògics amb unknown:

OR: (unknown or true) = true, (unknown or false) = unknown (unknown or unknown) = unknown

AND: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown

NOT: (not unknown) = unknown

6

SQL – UD 3 NULL – NOT NULL

Les columnes poden acceptar o no valors NULL. Si la definició d’una columna només té la paraula clau NULL,

accepta valors NULL. Si la definició d’una columna conté la clàusula NOT NULL, no

es poden inserir files que tinguin el valor NULL en aquella columna.

Les columnes definides com a primary key mai poden acceptar valors NULL.

Si s’insereix una fila, però no s’inclou cap valor per a una columna que permet valors NULL, normalment, el motor de la BD proporcionarà el valor NULL.

Una columna definida amb la paraula clau NULL també accepta una entrada explícita de NULL per part de l’usuari, independentment del tipus de dades definit.

El valor NULL no s’ha de posar entre cometes ja que no serà interpretat com a un valor NULL, sinó como la cadena de caràcters "NULL".

El valor NULL és una paraula reservada.

7

SQL – UD 3 Definició en una BD Per definir si una columna acceptarà o

no valors null se fa amb les restriccions NULL o NOT NULL. Exemple:

CREATE TABLE Alumnes (idalumne serial primary key,nom varchar (20),edat int,coeficient int NOT NULL

); Per defecte estan definides com a NULL.

8

SQL – UD 3 IS NULL – IS NOT NULL El predicat IS

NULL pot ser utilitzat per comprovar si hi ha valors null.

IS NULL

TRUE FALSE

NULL TRUE

FALSE FALSE

IS NOT NULL

TRUE TRUE

NULL FALSE

FALSE TRUE

9

SQL – UD 3. Exemples IS NULL – IS NOT NULL Podem llistar els clients que no tenen

telèfon.SELECT nom, poblacioFROM clientsWHERE telefon IS NULL;

Llistar els clients que tenen assignat un nombre de fills.

SELECT nom, estatcivil, poblacioFROM clientsWHERE nfills IS NOT NULL;

10

SQL – UD 3. Funció ABS ABS(n) - Retorna el valor absolut de n.

Exemple: SELECT ABS(2);

+--------+ | ABS(2) |+--------+ | 2 |+--------+

SELECT ABS(-32); +-----------+ | ABS(-32) |+-----------+ | 32 |+-----------+

11

SQL – UD3Funcions SIN I COS SIN(n) – Retorna el sinus de n. On n ve donat amb radians.

Exemple:SELECT SIN(PI()); +-----------+ | SIN(PI()) | +-----------+ | 0.000000 |+-----------+

COS(n) – Retorna el cosinus de n. On n ve donat amb radians. Exemple:

SELECT COS(PI()); +-------------+ | COS(PI()) | +-------------+ | -1.000000 | +-------------+

12

SQL – UD 3. Funció POWER POWER(x,y) – Retorna el valor de x

elevat a la potencia y. Exemple:

SELECT POWER(2,2) o SELECT POW(2,2) +---------------+ | POWER(2,2) |+---------------+| 4.000000 |+---------------+

13

SQL – UD 3. Funció EXP EXP(n) – Retorna el valor del

nombre e elevat a la potència n. Exemple:

SELECT EXP(2); +------------+ | EXP(2) | +------------+ | 7.389056 | +------------+

e ≈ 2,7182

SELECT EXP(-2); +------------+ | EXP(-2) | +------------+ | 0.135335 | +------------+

14

SQL – UD 3. Funció SQRT SQRT(n) – Retorna l’arrel quadrada

no negativa de n. Exemple:

SELECT SQRT(20); +------------+ | SQRT(20) | +------------+ | 4.472136 | +------------+

15

SQL – UD 3. Funció LOG LOG(n) – Retorna el logaritme

natural de n. Exemple:

SELECT LOG(2);+------------+ | LOG(2) | +------------+ | 0.693147 | +------------+

SELECT LOG(-2); +----------+ | LOG(-2) |+----------+| NULL |+----------+

16

SQL – UD 3Funcions SPACE i LENGTH SPACE(n) – Retorna una cadena que consisteix en n caràcters

espai. Exemple:

SELECT SPACE(6); +------------+| SPACE(6) | +------------+ | | +------------+

LEN(str) – Retorna la longitud de la cadena str. Exemple:

SELECT LEN('text'); +-------------------+ | LEN('text') | +-------------------+ | 4 |+-------------------+ SELECT LEN(poblacio) FROM clients;

17

SQL – UD3Funcions LEFT i RIGHT LEFT(cadena,longitud) – Retorna els 'longitud' caràcters de

l’esquerra de la 'cadena'. Exemple:

SELECT LEFT('SQL amb Classe', 3); +----------------------------------+ | LEFT('SQL amb Classe', 3) | +----------------------------------+ | SQL | +----------------------------------+

RIGHT(cadena,longitud) – Retorna els 'longitud' caràcters de la dreta de la 'cadena'.

Exemple:SELECT RIGHT('SQL amb Classe', 10); +------------------------------------+ | RIGHT('SQL amb Classe', 10) | +------------------------------------+ | amb Classe |+------------------------------------+

18

SQL – UD 3Funcions LOWER i UPPER LOWER(str) – Retorna la cadena str amb tots els caràcters

canviats a minúscules. Exemple:

SELECT LOWER(‘HOLA’); +---------------------+ | LOWER(‘HOLA‘) | +---------------------+ | hola | +---------------------+

UPPER(str) - Retorna la cadena str amb tots els caràcters canviats a majúscules.

Exemple:SELECT UPPER(‘hola’); SELECT UPPER(provincia) FROM

clients; +---------------------+ | UPPER(‘hola‘) | +---------------------+ | HOLA | +---------------------+

19

SQL – UD 3Funció SUBSTRING SUBSTRING(cadena,posicio,longitud) -

Retorna una subcadena de ‘longitud’ caràcters de la 'cadena' començant per la 'posicio’. Exemple:

SELECT SUBSTRING('SQL amb Classe',5,3); +--------------------------------------------+ | SUBSTRING('SQL amb Classe',5,3) |+--------------------------------------------+ | amb | +-------------------------------------------+

20

SQL – UD 3Funcions LTRIM i RTRIM LTRIM(str) – Retorna la cadena str amb els caràcters d’espai

inicials eliminats. Exemple:

SELECT LTRIM(‘ barbar'); +----------------------+ | LTRIM(' barbar') | +----------------------+ | barbar | +----------------------+

RTRIM(str) - Retorna la cadena str amb els caràcters d’espai finals eliminats.

Exemple:SELECT LTRIM('barbar '); +----------------------+ | LTRIM('barbar ') | +----------------------+ | barbar | +----------------------+

21

SQL – UD 3Funció REVERSE REVERSE(str) – Retorna la cadena str

amb l’ordre dels caràcters invertits. Exemple:

SELECT REVERSE('abc'); +--------------------+ | REVERSE(‘abc') | +-------------------+ | cba | +-------------------+ SELECT REVERSE(provincia) FROM clients;

22

SQL – UD 3Funció DATEPART DATEPART(datepa

rt, date). On: Datepart – és la

part de date (una data o hora)

date – la data de la qual volem la informació.

datepart Abreviatura

year yy, yyy

quarter qq, q

month mm, m

dayofyear dy, y

day dd, d

week wk, ww

weekday dw

hour hh

minute mi, n

second ss, s

milisecond ms

microsecond mcs

nanosecond ns

23

SQL – UD 3Funció DATEPART

ExemplesSELECT DatePart(yyyy,’18-08-2007’);

Resultat: 2007SELECT DatePart(yy,’18-08-2007’);

Resultat: 07SELECT DatePart(q,’02-12-1998’);

Resultat: 4

24

SQL – UD 3“Libros en pantalla”

L’entorn SQL Server Management Studio, proporciona uns documents anomenats libros en pantalla on es pot consultar totes les funcions entre altres coses.

Els trobem a: Ayuda->Contenido

25

Bibliografia

SQL.org Curs SQL