Consultes Postgres
De FFAWiki
- Composició d'una consulta:
- SELECT camp1/expressió, camp2/expressió, camp3/expressió, ..,campn/expressió FROM taula WHERE condició_cerca GROUP BY camp1, camp2 , ..., campn HAVING condicio_sobre_grups ORDER BY camp1 [ASC|DESC], camp2 [ASC|DESC], ..., campm [ASC|DESC] LIMIT n
Links de descarga de les bases de dades en les que es fan els exemples
Descargar base de dades sanitat
Descargar base de dades videoclub
Descargar base de dades toctag
Descargar base de dades empresa
SELECT
- Definició:
- Mostra els camps que volem que ens tregui per pantalla, s'utilitza per seleccionar dades d'una base de dades. Les dades retornades s’emmagatzemen en una taula de resultats, anomenada conjunt de resultats o result-set en anlges.
- Exemple: (Base de dades sanitat)
- SELECT * FROM doctor;
- En aquest cas no tenim filtre en el SELECT per lo que mostra tots els camps de la taula doctor
Eines que es poden utilitzar:
COUNT(*)
- Definició:
- Compta les files que s’imprimeixen, en general la funció COUNT() retorna el nombre de files que coincideix amb un criteri especificat.
- Exemple: (Base de dades sanitat)
- SELECT count(especialitat) FROM doctor WHERE especialitat='Pediatria';
- Conta totes aquelles especialitats dels doctors les quals son Pediatria.
DISTINCT
- Definició:
- Agrupa el camp que s’especifiqui traient els que estan repetits (si s’han de mostrar més d’un camp no es molt recomanat, millor GROUP BY)
- Exemple: (Base de dades sanitat)
- SELECT DISTINCT(especialitat) FROM doctor;
- Mostra les especialitats de la taula doctors que son diferents (Per dir d'alguna manera les agrupa).
DATE_PART(,)
- Definició:
- Extreu de tot aquell camp o valor de tipus (datetime, date, time...).
- Maneres de fer-lo servir:
- date_part('field', source)
- field = microseconds | milliseconds | second | minute | hour | day | week | month | quarter (Quart d'un any) | year | decade (Decada, cada 10) | century (secle, cada 100) | millennium (mileni, cada 1000)
- SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40'); -> Retorna: 16 (Quan no li fiques un camp de la base de dades s'ha d'especificar que és el valor que li poses en aquest cas un temps)
- SELECT date_part('hour', INTERVAL '4 hours 3 minutes'); -> Retorna: 4 (Quan no li fiques un camp de la base de dades s'ha d'especificar que és el valor que li poses en aquest cas un interval)
- Exemple: (Base de dades empresa)
- SELECT DATE_PART('YEAR',com_data) FROM comanda; -> Retorna: tots els anys de la columne com_data de la taula comanda (En aquest cas s'utilitzen dades d'una taula)
COALESCE
- Definició:
- Mostra el primer valor de la llista (COALESCE(comissio,0), el que esta en nagreta) es la llista separats per coma que no sigui null
- Exemple: (base de dades empresa)
- En aquest cas volem sumar el salari mes la comisio, pero no tots tenen comisio (son camps nulls) per tan utilitzarem COALESCE que el que fa es agafar el pimer valor que no sigui null de la llista COALESCE(comissio,0), el que esta en nagreta es la llista.
- SELECT cognom,salari+COALESCE(comissio,0) as sou FROM emp;
UPPER()
- Definició:
- Passa tot el text entre parèntesis a majúscules, també poden ser camps d’una taula
- Exemple:
- Exemple amb un text qualsevol:
- SELECT UPPER('hola, estas a ffawiki') as TEXT; -> Resultat: HOLA, ESTAS A FFAWIKI
- Exemple amb un camp d'una taula: (base de dades empresa)
- SELECT UPPER(cognom) as COGNOM FROM emp;
LOWER()
- Definició:
- Passa tot el text entre parèntesis a minúscules, també poden ser camps d’una taula
- Exemple:
- Exemple amb un text qualsevol:
- SELECT UPPER('HOLA, ESTAS A FFAWIKI') as TEXT; -> Resultat: hola, estas a ffawiki
- Exemple amb un camp d'una taula: (base de dades empresa)
- SELECT LOWER(cognom) as COGNOM FROM emp;
CASE (CASE camp1 WHEN valor1 THEN mostra1 WHEN valor2 THEN mostra2 ELSE mostra3 END)
- Definició:
- Si cambia un valor del camp especificat si surt entre els valors especificats (when valor) per el que se li digui (then valor) , per acabar s’ha de posar END i tancar parèntesis. L'ELSE no es obligatori
- Exemple: (Base de dades videoclub)
- SELECT codipeli,titol,(case codigenere when 1 then 'Brus Lee' when 2 then 'Stalone' when 3 then 'Al pacino' END) from pelicula;
- Enves de dirte un numero et diu el nom d'un actor
SUM()
- Definició:
- Suma tots els camps o valors entre parèntesis separats per comes.
- Exemple: (Base de dades Sanitat)
- Sumarem els llits que te cada hospital
- SELECT hospital_cod,sum(qtat_llits) as llits FROM sala GROUP BY hospital_cod;
AVG()
- Definició:
- Fa la mitjana dels camps o valors entre parèntesis separats per comes
- Exemple: (Base de dades empresa)
- Fa la mitjana dle que cobren tots els empleats
- SELECT ofici,AVG(salari) FROM emp GROUP BY ofici;
AS
- Definició:
- A l’hora de mostrar camps que tinguin alguna funció d’agregació si es posa (camp as alies) quan mostri les files el nom de la fila serà l’alies especificat
- Exemple: (Base de dades empresa)
- Posem un alies (mitjana_sou) a la mitjana dels salaris
- SELECT ofici,AVG(salari) AS mitjana_sou FROM emp GROUP BY ofici;
FROM
- Definició:
- Taula a on bucar els camps
- Exemple: (Base de dades empresa)
- Mostra els cognoms de la taula emp.
- SELECT cognom FROM emp;
JOIN
- Definició:
- Ajunta taules de diferentes maneres: Left Join, Left Outer Join, Inner Join, Right Join, Right Outer Join, Full Join, Full Outer Join.
- Exemple: (Base de dades sanitat)
- Mostra per els noms de l'hospital els nomes de totes les seves sales
- SELECT hospital.nom,sala.nom FROM sala INNER JOIN hospital ON sala.hospital_cod=hospital.hospital_cod;
- Imatge de Tots els Joins
Inner Join
- Definició:
- Uneix dos (pots fer de dos en dos) taules pels camps que tenen iguals (tants iguals com vulguis), i nomes s'utilitzen els tu especifiques en el SELECT.
- Exemple: (Base de dades toctag)
- Mostra el nom del cicle (c) i el nom de la família professional (f) on la id de la família coincideixi amb la id de la manilia de cicle
- SELECT c.descripcio,f.descripcio FROM cicle_formatiu c INNER JOIN familia_professional f ON c.familia_id=f.familia_id;
Right Join
- Definició:
- Agafa la taula de la dreta i els camps en comú
- Exemple: (Base de dades toctag)
- Mostra el titol de la pelicula i si esta llogada o no.
- SELECT pelicula.titol,(CASE count(prestec.codipeli) WHEN 0 THEN 'No Llogada' ELSE 'Llogada' END) as prestec FROM prestec RIGHT JOIN pelicula ON prestec.codipeli=pelicula.codipeli GROUP BY pelicula.titol;
Lefet Join
- Definició:
- Agafa la taula de la esquerra i els camps en comú
- Exemple: (Base de dades toctag)
- Mostra el titol de la pelicula i si esta llogada o no.
- SELECT pelicula.titol,(CASE count(prestec.codipeli) WHEN 0 THEN 'No Llogada' ELSE 'Llogada' END) as prestec FROM pelicula LEFT JOIN prestec ON prestec.codipeli=pelicula.codipeli GROUP BY pelicula.titol;
Right Join is NULL
- Definició:
- Agafa la taula de la dreta pero no els camps en comú
- Exemple: (Base de dades toctag)
- Mostra les pelicules no llogades
- SELECT titol FROM prestec t RIGHT JOIN pelicula p ON p.codipeli=t.codipeli WHERE t.codipeli IS NULL;
Lefet Join is NULL
- Definició:
- Agafa la taula de la esquerra pero no els camps en comú
- Exemple: (Base de dades toctag)
- Mostra les pelicules no llogades
- SELECT titol FROM pelicula p LEFT JOIN prestec t ON p.codipeli=t.codipeli WHERE t.codipeli IS NULL;
Full Join
- Definició:
- Agafa la taula de la esquerra pero no els camps en comú
- Exemple: (Base de dades toctag)
- Mostra les pelicules no llogades
- SELECT titol FROM pelicula p LEFT JOIN prestec t ON p.codipeli=t.codipeli WHERE t.codipeli IS NULL;
WHERE
- Definició:
- Condicions que han de complir les files per a que les mostri per pantalla
- Exemple: (Base de dades empresa)
- Mostra els cognoms i el salari de aquella gent que el seu salari es superior a 200.000
- SELECT cognom,salari FROM emp WHERE salari>200000;
Eines que es poden utilitzar:
Subconsultes
- Definició:
- Es pot utilitzar una consulta per tal de fer un filtre o condicio amb dades d'alguna altre taula relacionada o de la propia
- Exemple:
- Subconsulta taula relacionada (Base de dades toctag)
- Mostra tots aquells dni i noms d'alumnes de la taula algumnes que coincideixen amb els dni de la taula d'expulsions.
- SELECT dni,alumne FROM alumnes WHERE dni IN(SELECT DISTINCT(dni) FROM expulsions);
- Subconsulta en la mateixa taula (Base de dades empresa)
- Mostra els treballadors que el seu numero d'empleat de la taula emp coincideix amb la llista de numeros de empleats que son caps.
- SELECT cognom FROM emp WHERE emp_no IN(SELECT distinct(cap) FROM emp);
AND
- Definició:
- El seu us es unir varies conducions o filtres.
- Exemple: (Base de dades empresa)
- Mostra aquells salaris que son mes grans de 200.000 i mes petits a 300.000
- SELECT cognom,salari FROM emp WHERE salari>200000 AND salari<300000;
IN/NOT IN
- Definició:
- Comproba si esta dins de la llista que se li especifiqui entre parèntesi IN(), on esta la nagreta es on va la llista.
- Exemple: (Base de dades toctag)
- IN
- Exemple amb subcunsulta
- Mostra tots aquells dni i noms d'alumnes de la taula algumnes que coincideixen amb els dni de la taula d'expulsions.
- SELECT dni,alumne FROM alumnes WHERE dni IN(SELECT DISTINCT(dni) FROM expulsions);
- Exemple amb la mateixa taula
- Mostra tots aquells treballadors que tenen la categoria empleat i venedor
- SELECT cognom,ofici FROM emp WHERE ofici IN('empleat','venedor');
- NOT IN
- Exemple amb la mateixa taula
- Mostra tots aquells treballadors que no tenen la categoria empleat i venedor
- SELECT cognom,ofici FROM emp WHERE ofici NOT IN('empleat','venedor');
EXISTS/NOT EXISTS
- Definició:
- Fa una subconsulta i l'exists segons si troba algo o no, torna true o false i en el cas que dongui true imprimeix. (S'utilitza en subconsultes)
- Exemple:
- Mostra totes les columnes de la taula expulsions, pero nomes mostra les dades quan el dni de la taula alumnes el qual esta fent quart de l'ESO i si auqest mateix dni coincideix amb el dni de la taula d'expulsions.
- SELECT * FROM expulsions WHERE EXISTS (SELECT dni FROM alumnes WHERE estudis like 'ESO4%' and dni=expulsions.dni);
Operadors
- Definició:
- És una funció que realitza algun tipus d'operació en un nombre, variable o funció
+
- +
- Definició:
- suma
-
- -
- Definició:
- resta
=
- =
- Definició:
- igual
<
- <
- Definició:
- Més petit que
>
- >
- Definició:
- Més gran que
!=
- !=
- Definició:
- No igual
LIKE / NOT LIKE
- LIKE / NOT LIKE
- Definició:
- Compara un camp o un valor amb un patro.
- Exemple:
- Mostra els alumnes que estan cursant l'ESO.
- SELECT dni,alumne FROM alumnes WHERE estudis LIKE 'ESO%';
Eines: _ / %
- _ / %
- _ : S'utilitza per calcular quin caracter exacte del parametre vols que sigui igual
- % : S'ho traga tot, per exemple si vols bucar una parula que conte una x seria '%x%'
ALL
- Definició:
- L'ordre retorna true si tots els valors de la subqueria compleixen la condició. Necessita sempre un comarador devant.
- Exemple: (Base de dades empresa)
- Mostra cognom d'aquella persona que te el salari mes alt de tots.
- SELECT cognom FROM EMP WHERE salari >= ALL(SELECT salari FROM emp);
ANY/SOME
- Definició:
- ANY: qualsevol
- SOME: algun
- Si (qualsevol/algun) coincideix amb la llista s'imprimeix. ANY() SOME(), en la nagreta es on es posa la llista.
- Exemple:
- Mostra els alumnes amb estudis DAM
- SELECT alumne,estudis FROM alumnes WHERE estudis = SOME(SELECT estudis FROM alumnes WHERE estudis like 'DAM%');
- SELECT alumne,estudis FROM alumnes WHERE estudis = ANY(SELECT estudis FROM alumnes WHERE estudis like 'DAM%');
GROUP BY
- Definició:
- Agrupa els valors que coincideixen amb la mateixa columna.
- Exemple: (Base de dades sanitat)
- Agrupa els codis de l'hopital iguals i conta quantes sales hi ha per hospital.
- SELECT hospital_cod,count(sala_cod) FROM sala GROUP BY hospital_cod;
HAVING
- Definició:
- Condició que s’aplica un cop agrupats els camps.
- Exemple: (Base de dades sanitat)
- Agrupa els codis de l'hopital iguals i conta quantes sales hi ha per hospital pero nomes mostra si hi ha mes de 2 sales.
- SELECT hospital_cod,count(*) FROM sala GROUP BY hospital_cod HAVING count(*)>2;
ORDER BY
- Definició:
- Mostra les files ordenades per els camps que s’hi posin.
- Exemple: (Base de dades sanitat)
- Ordena els codis hospital
- SELECT hospital_cod,sala_cod FROM sala ORDER BY hospital_cod;
DESC
- Definició:
- Fa que l’ordre sigui descendent.
- Exemple:
- Ordena de manera descendent els codis hospital
- SELECT hospital_cod,sala_cod FROM sala ORDER BY hospital_cod DESC;
LIMIT
- Definició:
- Mostra les files fins a arribar al número de files que hi especifiquem. Es sol fer servir amb l'ORDER BY
- Exemple: (Base de dades sanitat)
- SELECT hospital_cod,count(*) FROM sala GROUP BY hospital_cod ORDER BY hospital_cod DESC LIMIT 1;
- Captura sense LIMIT
- Captura amb LIMIT