Formation DEV0
Dalibo SCOP
24.09
29 août 2024
Formation | Formation DEV0 |
Titre | Introduction à SQL |
Révision | 24.09 |
ISBN | N/A |
https://dali.bo/dev0_pdf | |
EPUB | https://dali.bo/dev0_epub |
HTML | https://dali.bo/dev0_html |
Slides | https://dali.bo/dev0_slides |
Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :
PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.
Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.
Gestion transactionnelle : la force des bases de données relationnelles :
SELECT
+
, -
,
/
, *
||
now()
age(timestamp)
extract( 'year' FROM timestamp)
date_part('Y',timestamp)
char_length(chaine)
count(*)
Nom_Objet
devient nom_objet
"
conserve la casse
"Nom_Objet"
predicat OR predicat
predicat AND predicat
ASC
ou DESC
NULL
: NULLS FIRST
ou NULLS LAST
COLLATE collation
OFFSET n
FETCH {FIRST | NEXT} n ROWS ONLY
LIMIT n
OFFSET
doit apparaitre avant FETCH
FROM
,
char
, varchar
integer
, smallint
,
bigint
real
, double precision
numeric
, decimal
boolean
date
, time
timestamp
interval
bit
, bit varying
char(n)
varchar(n)
'
'chaîne de caractères'
E
ou e
E'chaîne de caractères'
U&
U&'chaîne de caractères'
smallint
, integer
,
bigint
real
, double precision
numeric(precision, echelle)
,
decimal(precision, echelle)
.
e
[+-]chiffres]e
[+-]chiffres]e
[+-]chiffresTYPE 'chaine'
boolean
TRUE
FALSE
NULL
(ie valeur absente)date
time
timestamp
interval
TYPE 'chaine'
'YYYY-MM-DD HH24:MI:SS.ssssss'
'YYYY-MM-DD HH24:MI:SS.ssssss+fuseau'
'YYYY-MM-DD HH24:MI:SS.ssssss' AT TIME ZONE 'fuseau'
INTERVAL 'durée interval'
timezone
SET TIME ZONE
'Europe/Paris'
'CEST'
'+02'
bit(n)
, bit varying(n)
B
B'01010101'
X
X'55'
xml
json
: texte, avec validation du format JSONjsonb
(binaire)serial
smallserial
bigserial
IDENTITY
text
bytea
array
enum
cidr
, inet
, macaddr
uuid
json
, jsonb
, hstore
range
SELECT
N’hésitez pas, c’est le moment !
Data Definition Language
)Data Manipulation Language
)Transaction Control Language
)Data Definition Language
public
CREATE SCHEMA nom_schéma
ALTER SCHEMA nom_schéma
DROP SCHEMA [ IF EXISTS ] nom_schéma [ CASCADE ]
nom_schema
.nom_objet
search_path
SET search_path = schema1,schema2,public;
$user, public
MINVALUE
MAXVALUE
START
INCREMENT
CACHE
CYCLE
ALTER SEQUENCE nom [ INCREMENT increment ]
[ MINVALUE valeurmin | NO MINVALUE ]
[ MAXVALUE valeurmax | NO MAXVALUE ]
[ START [ WITH ] début ]
[ RESTART [ [ WITH ] nouveau_début ] ]
[ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { nom_table.nom_colonne | NONE } ]
nextval('nom_sequence')
currval('nom_sequence')
nextval()
doit être appelé avant dans la même
sessionserial
/bigserial
/smallserial
nextval(...)
IDENTITY
CREATE TABLE
DEFAULT
CREATE TABLE ... (LIKE table clause_inclusion)
INCLUDING DEFAULTS
INCLUDING CONSTRAINTS
INCLUDING INDEXES
ALTER TABLE
UNIQUE
NULL
UNIQUE
UNIQUE
!= index UNIQUE
UNIQUE
et NOT NULL
FOREIGN KEY
MATCH
MATCH FULL
(complète)MATCH SIMPLE
(partielle)GENERATED … AS IDENTITY
ALWAYS
BY DEFAULT
serial
ON UPDATE
ON DELETE
NOT NULL
CHECK
DEFERRABLE
, NOT DEFERRABLE
INITIALLY DEFERED
,
INITIALLY IMMEDIATE
CREATE TABLE paquet (
code text PRIMARY KEY,
reception timestamptz DEFAULT now(),
livraison timestamptz DEFAULT now() + interval '3d',
largeur int, longueur int, profondeur int,
volume int
GENERATED ALWAYS AS ( largeur * longueur * profondeur )
STORED CHECK (volume > 0.0)
) ;
DEFAULT
: expressions très simples, modifiablesGENERATED
SELECT
peut lire les données d’une table ou plusieurs
tables
INSERT
UPDATE
DELETE
UPDATE
BEGIN TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT nom_savepoint
RELEASE SAVEPOINT nom_savepoint
ROLLBACK TO SAVEPOINT nom_savepoint
CREATE
, ALTER
, DROP
INSERT
, UPDATE
, DELETE
N’hésitez pas, c’est le moment !
NULL
GROUP BY
, HAVING
CASE
UNION
, EXCEPT
,
INTERSECT
NULL
NULL
:
NULL
telle qu’elle est implémentée dans
SQL peut poser plus de problèmes qu’elle n’en résout. Son comportement
est parfois étrange et est source de nombreuses erreurs et de
confusions.NULL
le plus possible
NULL
correctement lorsqu’il le fautNULL
pour INSERT
et
UPDATE
NULL
est indiqué explicitement dans les
assignationsINSERT
NULL
NOT NULL
NULL
NULL
est inapplicable
NULL
WHERE
:
IS NULL
ou IS NOT NULL
AND
:
false
si NULL AND false
NULL
si NULL AND true
ou
NULL AND NULL
OR
:
true
si NULL OR true
NULL
si NULL OR false
ou
NULL OR NULL
NULL
count(*)
NULL
par une autre valeur
COALESCE(attribut, ...);
sql GROUP BY expression [, ...]
NULL
si l’ensemble est vide
count()
sql count(expression)
count(*)
count(colonne)
sql min(expression)
sql max(expression)
sql avg(expression)
sql sum(expression)
sql stddev(expression)
sql variance(expression)
regr_slope(Y,X)
regr_intercept(Y,X)
corr (Y,X)
HAVING
WHERE
s’applique sur les lignes luesHAVING
s’applique sur les lignes groupées(
et )
SELECT
WHERE
HAVING
IN
ANY
ALL
IN
vaut true
IN
vaut false
NULL
IN
vaut NULL
NULL
NULL
NOT IN
vaut true
NULL
NOT IN
vaut false
NOT IN
vaut NULL
NULL
NULL
NULL
NULL
FROM
JOIN
ou dans
WHERE
?JOIN
ou dans WHERE
?JOIN
WHERE
CROSS JOIN
INNER JOIN
sql table1 [INNER] JOIN table2 ON prédicat [...]
sql table1 [INNER] JOIN table2 USING (colonne [, ...])
sql table1 NATURAL [INNER] JOIN table2
NULL
NULL
NULL
NULL
sql table1 LEFT [OUTER] JOIN table2 ON prédicat [...]
sql table1 LEFT [OUTER] JOIN table2 USING (colonne [, ...])
sql table1 NATURAL LEFT [OUTER] JOIN table2
sql table1 RIGHT [OUTER] JOIN table2 ON prédicat [...]
sql table1 RIGHT [OUTER] JOIN table2 USING (colonne [, ...])
sql table1 NATURAL RIGHT [OUTER] JOIN table2
sql table1 FULL OUTER JOIN table2 ON prédicat [...]
sql table1 FULL OUTER JOIN table2 USING (colonne [, ...])
sql table1 NATURAL FULL OUTER JOIN table2
switch
en C ou JavaCASE
à l’intérieur d’autres expressions
CASE
ELSE
UNION
INTERSECT
EXCEPT
CAST (expression AS type)
expression::type
chaîne1 || chaîne2
char_length(chaîne)
lower(chaîne)
upper(chaîne)
substring(chaîne [from int] [for int])
position(sous-chaîne in chaîne)
+
-
*
/
%
Arrondi : round(numeric)
Troncature : trunc(numeric [, precision])
Entier le plus petit : floor(numeric)
Entier le plus grand : ceil(numeric)
Générer une suite d’entiers :
generate_series(borne_debut, borne_fin, intervalle)
Générer un nombre aléatoire : random()
current_date
current_time
current_timestamp
/ now()
age(timestamp)
age(timestamp, timestamp)
date_trunc(text, timestamp)
date_trunc('month' from date_naissance)
extract(text, timestamp)
extract('year' from date_naissance)
timestamp
,
time
ou date
date/time - date/time = interval
date/time + time = date/time
date/time + interval = date/time
interval
interval * numeric = interval
interval / numeric = interval
interval + interval = interval
to_char(timestamp, text)
to_char(current_timestamp, 'DD/MM/YYYY HH24:MI:SS')
to_date(text, text)
to_date('05/12/2000', 'DD/MM/YYYY')
to_timestamp(text, text)
to_timestamp('05/12/2000 12:00:00', 'DD/MM/YYYY HH24:MI:SS')
datestyle
generate_series (timestamp_debut, timestamp_fin, intervalle)
SELECT
CREATE VIEW vue (colonne ...) AS SELECT ...
SELECT * FROM vue;
GRANT
et REVOKE
security_barrier
WITH CHECK OPTION
INSTEAD OF
PREPARE
, préparation du plan d’exécution d’une
requêteEXECUTE
, passage des paramètres de la requête et
exécution réelleN’hésitez pas, c’est le moment !
LIMIT
/OFFSET
LATERAL
UPSERT
: INSERT ou UPDATELIMIT
FETCH FIRST xx ROWS
LIMIT
OFFSET
est problématique
RETURNING
INSERT
UPDATE
DELETE
INSERT
ou UPDATE
?
INSERT ... ON CONFLICT DO { NOTHING | UPDATE }
INSERT
INSERT
LATERAL
foreach
LATERAL
foreach
SELECT titre,
top_5_messages.date_publication,
top_5_messages.extrait
FROM sujets,
LATERAL(SELECT date_publication,
substr(message, 0, 100) AS extrait
FROM messages
WHERE sujets.sujet_id = messages.sujet_id
ORDER BY date_publication DESC
LIMIT 5) top_5_messages
ORDER BY sujets.date_modification DESC,
top_5_messages.date_publication DESC
LIMIT 25;
LATERAL
optionnelleJSON
sous la forme tabulaireWITH
et WITH RECURSIVE
MATERIALIZED
pour obtenir cette barrièreINSERT
/UPDATE
/DELETE
RETURNING
WITH RECURSIVE parcours_menu AS (
SELECT menu_id, libelle, parent_id,
libelle AS arborescence
FROM entrees_menu
WHERE libelle = 'Terminal'
AND parent_id IS NULL
UNION ALL
SELECT menu.menu_id, menu.libelle, menu.parent_id,
arborescence || '/' || menu.libelle
FROM entrees_menu menu
JOIN parcours_menu parent
ON (menu.parent_id = parent.menu_id)
)
SELECT * FROM parcours_menu;
UPDATE
perduSELECT FOR UPDATE
SERIALIZABLE
SELECT FOR UPDATE
SELECT FOR UPDATE SKIP LOCKED
SSI : Serializable Snapshot Isolation (9.1+)
serializable
default_transaction_isolation=serializable
dans la
configurationsmallint
: 2 octetsinteger
: 4 octetsbigint
: 8 octetsreal
/float4
double precision
/float8
numeric(.., ..)
>
, >=
, =
,
<=
,<
+
, -
, /
, *
,
modulo (%
), puissance (^
)AND
, OR
, XOR
(&
, |
, #
)>>
,
<<
integer
ou biginteger
:
numeric
:
float
, real
:
SUM()
, AVG()
,
etc.date
YYYY-MM-DD
time
HH24:MI:SS
timestamp
(without time zone
!)
timestamp with time zone
= timestamptz
2019-11-13 15:33:00.824096+01
timestamp without time zone
interval
range
pour tout couple « début /
fin »interval
/generate_series
varchar(_n_)
, text
bytea
SELECT * FROM mots ORDER BY t COLLATE "C" ;
CREATE TABLE messages (
id int,
fr TEXT COLLATE "fr_FR.utf8",
de TEXT COLLATE "de_DE.utf8" );
Source des collations :
CREATE COLLATION danois (provider = icu, locale = 'da-x-icu') ;
JSON
…)hstore
(clé/valeur historique)json
xml
json
json
jsonb
(PG > 9.4)
xml
xml
range
int4range
, int8range
,
numrange
daterange
, tsrange
,
tstzrange
*
, &&
,
<@
ou @>
FILTER
WINDOW
GROUPING SETS
, ROLLUP
,
CUBE
WITHIN GROUPS
GROUP BY
GROUP BY
ORDER BY
dans la fonction d’agrégatarray_agg
, string_agg
et xmlagg
FILTER
CASE
complexesCASE
WINDOW
OVER (PARTITION BY …)
SELECT matricule, salaire, service,
SUM(salaire) OVER (PARTITION BY service)
AS total_salaire_service
FROM employes;
matricule | salaire | service | total_salaire_service
-----------+----------+-------------+-----------------------
00000004 | 4500.00 | Courrier | 7500.00
00000020 | 3000.00 | Courrier | 7500.00
00000001 | 10000.00 | Direction | 10000.00
00000006 | 4000.00 | Publication | 7000.00
00000040 | 3000.00 | Publication | 7000.00
OVER (ORDER BY …)
row_number()
rank()
,
dense_rank()
lead()
,
lag()
OVER (PARTITION BY .. ORDER BY ..)
SELECT continent, pays, population,
rank() OVER (PARTITION BY continent
ORDER BY population DESC)
AS rang
FROM population;
continent | pays | population | rang
------------------+--------------------+------------+------
Afrique | Nigéria | 173.6 | 1
Afrique | Éthiopie | 94.1 | 2
Afrique | Égypte | 82.1 | 3
Afrique | Rép. dém. du Congo | 67.5 | 4
(…)
Amérique du Nord | États-Unis | 320.1 | 1
Amérique du Nord | Canada | 35.2 | 2
(…)
lead()
et lag()
lead(colonne, n)
lag(colonne, n)
lead()
et lag()
:
exempleSELECT pays, continent, population,
lag(population) OVER (PARTITION BY continent
ORDER BY population DESC)
FROM population;
pays | continent | population | lag
-----------------------+-----------+------------+--------
Chine | Asie | 1385.6 |
Iraq | Asie | 33.8 | 1385.6
Ouzbékistan | Asie | 28.9 | 33.8
Arabie Saoudite | Asie | 28.8 | 28.9
France métropolitaine | Europe | 64.3 |
Finlande | Europe | 5.4 | 64.3
Lettonie | Europe | 2.1 | 5.4
lead()
et
lag()
: principefirst_value(colonne)
last_value(colonne)
nth_value(colonne, n)
first
/last
/nth_value
:
exempleSELECT pays, continent, population,
first_value(population)
OVER (PARTITION BY continent
ORDER BY population DESC)
FROM population;
pays | continent | population | first_value
-----------------+-----------+------------+-------------
Chine | Asie | 1385.6 | 1385.6
Iraq | Asie | 33.8 | 1385.6
Ouzbékistan | Asie | 28.9 | 1385.6
Arabie Saoudite | Asie | 28.8 | 1385.6
France | Europe | 64.3 | 64.3
Finlande | Europe | 5.4 | 64.3
Lettonie | Europe | 2.1 | 64.3
WINDOW
WINDOW
: syntaxeUNBOUNDED PRECEDING
: depuis le début de la
partitionCURRENT ROW
: depuis la ligne couranteUNBOUNDED FOLLOWING
: jusqu’à la fin de la
partitionCURRENT ROW
: jusqu’à la ligne courantexxx PRECEDING
: depuis les xxx valeurs devant la ligne
couranteCURRENT ROW
: depuis la ligne courantexxx FOLLOWING
: depuis les xxx valeurs derrière la
ligne couranteCURRENT ROW
: jusqu’à la ligne couranteGROUPS
xxx PRECEDING
: depuis les xxx groupes de valeurs
identiques devant la ligne couranteCURRENT ROW
: depuis la ligne courante ou le premier
élément identique dans le tri réalisé par ORDER BY
xxx FOLLOWING
: depuis les xxx groupes de valeurs
identiques derrière la ligne couranteCURRENT ROW
: jusqu’à la ligne courante ou le dernier
élément identique dans le tri réalisé par ORDER BY
EXCLUDE
EXCLUDE CURRENT ROW
: exclut la ligne couranteEXCLUDE GROUP
: exclut la ligne courante et le groupe
de valeurs identiques dans l’ordreEXCLUDE TIES
exclut et le groupe de valeurs identiques
à la ligne courante dans l’ordre mais pas la ligne couranteEXCLUDE NO OTHERS
: pas d’exclusion (valeur par
défaut)SELECT pays, continent, population,
last_value(population)
OVER (PARTITION BY continent ORDER BY population
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)
FROM population;
pays | continent | population | last_value
-----------------------+-----------+------------+------------
Arabie Saoudite | Asie | 28.8 | 1385.6
Ouzbékistan | Asie | 28.9 | 1385.6
Iraq | Asie | 33.8 | 1385.6
Chine (4) | Asie | 1385.6 | 1385.6
Lettonie | Europe | 2.1 | 64.3
Finlande | Europe | 5.4 | 64.3
France métropolitaine | Europe | 64.3 | 64.3
WITHIN GROUP
WITHIN GROUP
: exempleGROUPING SETS/ROLLUP/CUBE
GROUP BY
ROLLUP
CUBE
La solution actuelle semble techniquement meilleure et
la solution actuelle a donc été choisie. Le wiki du projet PostgreSQL
montre que l’ordre MERGE
a été étudié et qu’un certain
nombre d’aspects cruciaux n’ont pas été spécifiés, amenant le projet
PostgreSQL à utiliser sa propre version. Voir la documentation : https://wiki.postgresql.org/wiki/UPSERT#MERGE_disadvantages.↩︎