Voici quelques notes sur le jargon de la base de données:

• DB: DataBase.
• SGBD-R: Systèmes de gestion de bases de données relationnels.
• AOO: Approche orientée objet.
• UML: Unified Modeling Language. Il s'agit d'un langage de modélisation graphique normalisé permettant de représenter les multiples aspects de la conception d'un système. Il propose plusieurs types de diagrammes, chacun permettant de décrire les différentes facettes du système (fonctionnalité, architecture logique ou physique…).
• La notion de classe: Une classe est le modèle abstrait d'un objet. Elle définit les attributs et les opérations de cet objet. C'est à partir de ce modèle que seront créés les objets « concrets » possédant des valeurs particulières pour leurs attributs. Ainsi la classe définit les caractéristiques de tous les objets de cette classe (nom, liste des attributs).
• La notion d'instance: L'instanciation est l'action de création d'un objet à partir d'une classe. Le résultat de cette création est une instance de la classe. Les instances d'une classe sont les occurrences d'objets correspondant à cette classe.
• Tables: Ces tables correspondent globalement aux classes en AOO. Chaque colonne d'une table représente un attribut. Chaque ligne (appelée tuple ou n-uplet) représente une instance donnant ainsi la valeur de chaque attribut de l'objet. Une base de données relationnelle est donc composée d'un ensemble de tables, pouvant être liées entre elles. Cette manière d'organiser les éléments s'appuie sur ce que l'on appelle un modèle relationnel.
• MPD: Modèle Physique de Données.
• Multiplicités: servent à apposer des contraintes numériques sur l'association, combien d'instances d'une classe peuvent être liées à une instance de la classe de l'autre côté de l'association. Elles bornent les cardinalités des instances liées entre elles.

• dans la partie haute : le nom de la classe
• dans la partie en dessous : la liste des attributs.

On y retrouve la table restaurant avec les colonnes :

• nom
• cuisine
• adresse
• note

On note la colonne id

• Pour le nom des classes :
  • au singulier
  • avec des lettres non accentuées
  • commençant par une majuscule
  • utilisant Pascal Case (chaque mot commence avec une majuscule)
• Pour les attributs :
  • au singulier
  • avec des lettres non accentuées
  • commençant par une minuscule
  • utilisant Camel Case (chaque mot commence avec une majuscule sauf le premier) Ex : couleurYeux

• Pour le nom des tables et des attributs :
  • au singulier
  • avec des lettres non accentuées
  • en minuscules
  • utilisant Underscore Case (_) Ex : pierre_precieuse, couleur_yeux

Les noms sont l'appellation des éléments constitutifs des systèmes complexes. Si les noms ne sont pas appropriés, le système peut rapidement être inintelligible.

Si les noms sont clairs, bien choisis et partagés par tous les membres de l'équipe projet, alors la communication sera bien plus efficace !

Posez des limites avec les multiplicités
En UML, la relation entre deux classes s'appelle une association.
Pour modéliser une association entre deux classes, on fait un trait entre elles.

• On précise la multiplicité entre les 2 classes:
• On peut donner un nom à cette association:

1. Un à un (one-to-one):
   
   • Un conducteur conduit une et une seule voiture à la fois
   • Une voiture n'est pas conduite (en stationnement) ou conduite par un seul conducteur à la fois.
2. Un à plusieurs (one-to-many) ou plusieurs à un (many-to-one) :
   
   • Un journal contient aucun (journal en préparation), un ou plusieurs articles
   • Un article est contenu dans aucun (en cours d'écriture) ou un seul journal
3. Plusieurs à plusieurs (many-to-many) :
   
   • Un musicien fait partie d'aucun, un ou plusieurs groupes
   • Un groupe est constitué de un ou plusieurs musiciens

Il faut que la clé primaire soit unique, exemple dans le modèle physique de données (MPD):
Attribut unique:

TABLE personne
numeroSecuriteSocials: VARCHAR [PK] <== car numéro UNIQUE
nom: VARCHAR
prenom: VARCHAR

Clé primaire composite: 2 clés

TABLE commune
code_postal: VARCHAR [PK] <== UNIQUE par ces 2 clés
nom: VARCHAR [PK]         <==
superficie: INTEGER
nbr_habitant: INTEGER

Clé primaire créée:

TABLE produit
id: INTEGER [PK]  <== clé primaire auto-incrémentée
nom: VARCHAR
prix_unitaire: DECIMAL

L'association un à un (one-to-one) est une association qui possède 1 comme cardinalité maximum de part et d'autre de celle-ci.
UML

• Un conducteur conduit une et une seule voiture à la fois
• Une voiture n'est pas conduite (en stationnement) ou conduite par un seul conducteur à la fois.

MDP

L'association un à plusieurs (one-to-many) ou plusieurs à un (many-to-one) est une association qui possède 1 comme cardinalité maximum d'un côté et une cardinalité maximum supérieure à 1 (cardinalité multiple) de l'autre côté de celle-ci.
UML

• Un journal contient aucun (journal en préparation), un ou plusieurs articles
• Un article est contenu dans aucun (en cours d'écriture) ou un seul journal

MDP
On doit créer la clé étrangère dans la table correspondant à la cardinalité maximum supérieure à 1 (cardinalité multiple).

Il est ainsi possible de référencer plusieurs fois le même journal dans des articles différents (cardinalité *), et un article ne peut être lié qu'à un seul journal au maximum (cardinalité 0..1)

L'association plusieurs à plusieurs (many-to-many) est une association qui possède une cardinalité maximum supérieure à 1 (cardinalité multiple) de part et d'autre de celle-ci.
UML

• Un musicien constitue aucun, un ou plusieurs groupes
• Un groupe est constitué de un ou plusieurs musiciens

MDP
Il faut ici créer une Table intermédiaire pour matérialiser les relations:

Tuples

En créant la classe d'association Concert, on peux ajouter, un attribut date qui spécifie la date du concert.
UML

Une classe d'association se traduit dans le MPD comme une association plusieurs à plusieurs (many-to-many) : En créant une table du nom de la classe d'association avec une clé primaire composée et contenant les attributs de la classe d'association.
MDP

Il y a de nombreux cas où les classes d'association sont utiles, par exemple enregistrer le numéro de siège dans une relation Passager/Vol, etc.

Pour être en première forme normale (1FN), il faut que chaque attribut soit atomique. En d'autres termes, aucun attribut ne doit être multivalué (liste de valeurs) ou composé (si on le décompose, on obtient des informations supplémentaires).
Exemple: Une liste de contact avec un champ e-mail.
Cas où la 1ere forme normale ne serai pas respectée:

• 2 e-mails dans le champs e-mail.

1ere solution (pas conseillée):

• Prévoir un champ supplémentaire e-mail_2

2ème solution (recomandée):

• Créer une table pour les e-mails du type 1 à plusieurs
  
  

⇒ UML


⇒ MDP

La deuxième forme normale ne concerne que les tables avec une clé primaire composite.
Pour être en deuxième forme normale:

• Il faut déjà être en 1FN et en plus respecter.
  
• Aucun attribut ne faisant pas partie de la clé primaire ne doit dépendre que d'une partie de la clé primaire.
  
  

Exemple:

On ajoute une information donnant la difficulté de la figure:

La difficulté d'une figure ne dépend que de la figure et non du skater.
L'attribut difficulte ne dépend que d'une partie de la clé primaire (figure) et non de la clé primaire complète (skater, figure) : La 2FN n'est pas respecté.

⇒ La solution consiste donc à isoler les attributs concernés dans des tables dédiées. On créé une table figure et déplacer la colonne difficulte dans cette nouvelle table :

Dans la table resultat, la colonne figure devient clé étrangère :
UML

MDP

La troisième forme normale ressemble à la deuxième (2FN) mais concerne la dépendance entre attributs non clés.
Pour être en troisième forme normale :

• il faut déjà être en 2FN.
• Aucun attribut ne faisant pas partie de la clé primaire ne doit dépendre d'une partie des autres attributs ne faisant pas non plus partie de la clé primaire.
  

Exemple: La Table “personne”:

Cette table ne respecte pas la 3FN car l'attribut sexe peut être déduit de l'attribut non clé primaire civilite.
Afin de résoudre ce problème, comme pour la 2FN, il convient de créer une nouvelle table civilite et d'y déplacer l'attribut sexe :

MPD:

voici le nouveau contenu des tables personne et civilite : Tuples:

Autre exmple qui ne respecte pas la 3FN:
La colonne total qui peut être calculée avec une opération mathématique simple à partir des colonnes quantite et prix_unitaire (total = quantite x prix_unitaire).
Sa valeur ne dépend pas de la clé primaire.

La solution consiste à simplement supprimer la colonne total.

L'agrégation, c'est une classe qui regroupe des objets.
Il est représenté en UML par un losange vide.
Exemple:

Une agrégation sera traduite dans le MPD comme une association classique de type un à plusieurs ou plusieurs à plusieurs. Il n'y a pas de formalisme particulier.

Une composition est une sorte d'agrégation plus « forte ».
Elle s'emploie lorsque :

• une classe (composite) est “composée” de plusieurs autres classes (composant) ;
• une instance de la classe composant ne peut pas être liée à plusieurs instances de la classe composite.
  Association obligatoirement un à plusieurs et non pas plusieurs à plusieurs ;
• si on détruit une instance de la classe composite, ses composants devrait “normalement” être détruits.
  

Exemples de composition :

• Répertoire - Fichier :
  • un répertoire est composé de fichiers.
  • Un fichier ne peut pas être dans plusieurs répertoires.
  • Si on supprime le répertoire, les fichiers sont supprimés.
• Immeuble - Appartement :
  • un immeuble est composé d'un ensemble d'appartements.
  • Un appartement ne se situe que dans un seul immeuble.
  • si on détruit l'immeuble, les appartements sont détruits aussi !

Une composition est matérialisée par un losange plein du côté de la classe jouant le rôle de composite.

De même que pour l'agrégation, la composition sera traduite dans le MPD comme une association classique de type un à plusieurs. Il n'y a pas de formalisme particulier.

En approche orientée objet, il est possible de généraliser des comportements grâce à l'héritage.

Exemple:


Le marteau et la clé à pipe sont des outils. On dit alors que les classes Marteau et CleAPipe héritent de la classe Outil.
Une instance de Marteau ou de CleAPipe sont indirectement des instances d'Outil :

• Le passage de ces sous-classes (Marteau et CleAPipe) à la super-classe (Outil) est appelé généralisation.
• Le passage de la super-classe aux sous-classes est appelé spécialisation.

Grâce à l'héritage, on va pouvoir :

• placer des attributs communs (dans la super-classe), qui seront « automatiquement » repris dans les sous-classes ;
• créer des associations communes au niveau de la super-classe, qui, de même, seront « automatiquement » reprises dans les sous-classes.
  

Ainsi, il m'est possible de modéliser facilement le fait que :

• tous les outils ont un nom
• tous les outils sont rangés dans une boîte à outils
• les marteaux ont comme caractéristique leur poids
• les clés à pipe ont comme caractéristiques leur diamètre et si elles sont débouchées ou non

Il est également possible de préciser que nous ne pouvons pas instancier directement un outil, mais seulement ses sous-classes, en rendant la classe Outil abstraite. Cela est représenté sur le diagramme de classes en mettant le nom de la classe en italique :


La notion d'héritage n'existe pas en tant que telle dans le modèle relationnel, mais il y a plusieurs moyens de traduire l'héritage dans le MPD. Méthode conseillée:

1. Créer les 3 tables outil, marteau, cle_a_pipe avec leurs attributs respectifs comme vous le feriez pour n'importe quelles autres classes.
2. Partager la même valeur de clé primaire entre la table outil et les tables marteau/cle_a_pipe pour les tuples correspondants : la clé primaire dans les tables des sous-classes sera donc aussi une clé étrangère vers la table de la super-classe.
   

Une association est dite réflexive quand les deux extrémités de celle-ci pointent sur la même classe.
On peut modéliser une arborescence de catégories comme celle-ci :

• Maison
  • Décoration
  • Multimédia
    • TV
    • Hi-Fi
  • Extérieur
    • Salon de jardin
    • Outillage

MPD:

Documentations complémentaires:

• http://www.uml-diagrams.org
• http://laurent-audibert.developpez.com/Cours-UML/

• En 1er utiliser le papier et le crayon
• https://www.draw.io
• https://eclipse.org/papyrus/
• http://software.sqlpower.ca/page/architect

Source: https://openclassrooms.com/fr/courses/4055451-modelisez-et-implementez-une-base-de-donnees-relationnelle-avec-uml/4458689-mettez-en-place-votre-environnement-de-developpement

PostgreSQL

• https://www.postgresql.org/
• Paquet dispo dans Debian
• Configuration : https://docs.postgresql.fr/9.6/runtime-config-connection.html

listen_addresses = 'localhost'
port = 5432

• Configuration accès utilisateur: https://docs.postgresql.fr/9.6/client-authentication.html

## Permettre la connexion de tous les utilisateurs à toutes les bases
## via le réseau sur localhost
## en utilisant un mot de passe (le mot de passe est chiffré en MD5)
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             localhost               md5

• La doc des type de données: https://www.postgresql.org/docs/current/datatype.html

SQL Power Architect

• Nécéssite Java: https://java.com/fr/download/
• http://software.sqlpower.ca/page/architect

SQuirreL SQL

• Nécéssite Java: https://java.com/fr/download/
• http://www.squirrelsql.org/#installation

DBeaver

• https://dbeaver.io/
• https://dbeaver.io/download/

Les index sont utilisé pour les recherche dans le base de données.
Un même index peut porter sur une ou plusieurs colonnes à la fois. Il est également possible de créer plusieurs index sur une même table.
En plus de servir de référencement, un index peut avoir des caractéristiques supplémentaires :

• unique : l'index ajoute une contrainte supplémentaire empêchant de créer plusieurs lignes ayant la même combinaison de valeurs dans les colonnes faisant partie de cet index.
• clustered : les lignes de la table sont triées dans l'ordre imposé par cet index. Agissant au niveau du stockage des lignes de la table, vous ne pouvez donc créer qu'un seul index clustered par table.

En général, les SGBD créent automatiquement des index pour les colonnes de clés primaire et certains le font pour les colonnes clés étrangères aussi.

• Pour la clé primaire, l'index impose une unicité de valeur : il n'est pas possible de créer 2 lignes qui auront exactement les mêmes valeurs dans les colonnes constituant la clé primaire.
• Pour la clé étrangère, même sans index, la contrainte suivante est imposée : les valeurs renseignées dans les colonnes de clés étrangères doivent exister dans la clé primaire de la table référencée (c'est-à-dire la table située de l'autre côté de la relation).

Pour ajouter un index sur une table dans SQL Power Architect, il faut procéder ainsi :

1. Sélectionnez la table concernée.
2. Cliquez sur l'icône de création d'un nouvel index
3. Une fenêtre s'ouvre, vous permettant de définir les propriétés de l'index (nom, caractéristiques) et de sélectionner les colonnes sur lesquelles il porte. S'il s'agit d'un index clustered, vous pouvez définir le sens de tri de chaque colonne (croissant/décroissant) ainsi que l'ordre dans lequel les colonnes interviennent dans le tri (grâce aux boutons ^v ).

Dans l'exemple ci-dessus, un index unique et clustered, nommé commune_idx a été ajouté à la table commune :

• Les lignes de la table seront ordonnées (index clustered) par :
  1. le code_postal, par ordre croissant (ASCENDING)
  2. le nom, par ordre croissant (ASCENDING)
• Les couples de valeurs (code_postal;nom) doivent être uniques (index unique) :
  il ne sera pas possible d'avoir deux communes avec le même nom et le même code postal.

Les colonnes concernées par un index unique sont signalées par la notation [ AK ].

Exemple du MPD:

Pour générer le script:
Cliquer sur l'icône SQL






La documentation SQL: https://docs.postgresql.fr/9.6/sql-commands.html

CREATE TABLE public.historique_statut (
    ticket_numero INTEGER NOT NULL,
    statut_id INTEGER NOT NULL,
    DATE TIMESTAMP NOT NULL,
    commentaire_id INTEGER,
    utilisateur_id INTEGER NOT NULL,
    CONSTRAINT historique_statut_pk PRIMARY KEY (ticket_numero, statut_id)
);

Les clés étrangères sont ajoutées une fois que toutes les tables ont été créées.

ALTER TABLE public.commentaire ADD CONSTRAINT utilisateur_commentaire_fk
FOREIGN KEY (utilisateur_id)
REFERENCES public.utilisateur (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

INSERT INTO public.utilisateur (
    nom,
    prenom
) VALUES (
    'Dalton',
    'Joe'
);

SELECT * 
FROM public.projet;

Croisement de table: Clé étrangère

SELECT *
FROM public.projet
JOIN public.utilisateur ON projet.responsable_id = utilisateur.id;

UPDATE public.projet SET
    nom = 'System 6x7'
WHERE
    id = 12;

UPDATE public.projet
WHERE
    id = 12;

Il est possible de regrouper les instructions dans une transaction comme s'il s'agissait d'un seul bloc.

Une transaction est ouverte avec l'instruction BEGIN TRANSACTION; et elle est clôturée de deux manières :

• soit en la validant (instruction COMMIT;)
• soit en l'annulant (instruction ROLLBACK;)

Imaginez par exemple que vous vouliez supprimer un projet de la base. À cause des clés étrangères, vous allez aussi devoir supprimer les lignes des autres tables qui référencent ce projet (ticket, version) et ceci en cascade (tables bug, evolution, bug_version_affectee…).

BEGIN TRANSACTION;   -- Ouverture de la transaction
 
-- Projet à supprimer : projet.id = 18
 
DELETE FROM evolution
WHERE EXISTS (
    SELECT 1 FROM ticket
    WHERE ticket.numero = evolution.ticket_numero
    AND ticket.projet_id = 18
);
 
/*
DELETE FROM bug_version_affectee ...
DELETE FROM bug ...
...
DELETE FROM ticket ...
DELETE FROM version ...
*/
 
DELETE FROM projet WHERE id = 18;
 
COMMIT;   -- Validation de la transaction

owner = models.ForeignKey(
    User, default=1, 
    on_delete=models.CASCADE)

INSERT INTO public.utilisateur   -- INSERT INTO <schéma>.<table>
(  
    id, nom, prenom         -- <colonnes dont vous allez donner les valeurs>
)
VALUES
(
    4, 'Dalton', 'Joe'      -- <valeurs des colonnes listées (dans le même ordre)>
);

Ajout de plusieurs lignes en une requête SQL:

INSERT INTO public.utilisateur
    (id, nom, prenom)
VALUES
    (4, 'Dalton', 'Joe'),      -- ligne 1
    (5, 'Dalton', 'William'),  -- ligne 2
    (6, 'Dalton', 'Jack'),     -- ligne 3
    (7, 'Dalton', 'Averell')   -- ligne 4
;

Exemple de définition de TABLE avec des valeurs par défaut:

CREATE TABLE tache (
    id INTEGER NOT NULL DEFAULT NEXTVAL('tache_id_seq'),
    libelle VARCHAR(100) NOT NULL,
    priorite INTEGER NOT NULL DEFAULT 1,
    realisateur_id INTEGER,
    CONSTRAINT tache_pk PRIMARY KEY (id)
);

SELECT currval('tache_id_seq');
-- Renvoie : 15

Le principe est de copier ou cloner une ligne existante : Ici je record 18.

INSERT INTO projet (
    nom, date_creation, cloture, responsable_id
)
SELECT
    nom, date_creation, cloture, responsable_id
FROM projet
WHERE id = 18;

Il est possible de modifier à la volée des données lors du clonage: Ici le nom.

INSERT INTO projet (
    'Dalton', date_creation, cloture, responsable_id
)
SELECT
    nom, date_creation, cloture, responsable_id
FROM projet
WHERE id = 18;

Requête sur la même principe, avec un peu plus de complexité:

INSERT INTO projet (
    nom,
    date_creation, cloture, responsable_id
)
SELECT
    projet.nom || ' Perso ' || utilisateur.prenom,
    CURRENT_TIMESTAMP, FALSE, projet.responsable_id
FROM
    projet
    JOIN utilisateur ON utilisateur.id = projet.responsable_id
WHERE projet.id IN (18, 42);

Explications : Clonage des lignes 18 et 42

• Ajout de “perso” et le nom du responsable après la nom du projet
• Ajout de la date et l'heure actuelle dans le colonne date_creation
• Passage de la colonne cloture à False

Pour comprendre les liens des tables des exemples suivant, voici le schéma :

• Afficher l'id et le nom de tous les projets.

SELECT
    id, nom         -- <liste des colonnes à afficher>
FROM
    public.projet   -- <schéma>.<table>
;
/* Version allégée: */
SELECT * FROM utilisateur;

• Tous les noms:

SELECT nom FROM utilisateur;

• Tous les noms sans les doublons:

SELECT DISTINCT nom FROM utilisateur;

• Ajoutez des critères de recherche

SELECT *
FROM utilisateur
WHERE nom = 'Dalton';

• Afficher la liste des projets :

        non gérés par Édouard Bracame,
        ET :
            clôturés
            OU créés entre le 10 et le 30 janvier 2017 (inclus)

SELECT * FROM projet
WHERE
    (NOT responsable_id = 3)
AND (cloture = TRUE
        OR (date_creation >= '2017-01-10'
            AND date_creation < '2017-01-31')
    )
;

• Opérateurs de conditions

• Opérateur LIKE

• Opérateur IN et NOT IN

-- Afficher les informations des projets 18 et 42
SELECT * FROM projet
WHERE id IN (18, 42);
 
-- Afficher les informations des projets autres que 18 et 42
SELECT * FROM projet
WHERE id NOT IN (18, 42);

• Faites des opérations et appliquez des fonctions

SELECT
    nom || prenom
FROM utilisateur
WHERE LOWER(nom) LIKE `dal%`;

• Ordonnez les valeurs
  • par nom croissant
  • puis par prénom décroissant

SELECT * FROM utilisateur
WHERE LOWER(nom) LIKE '%a%'
ORDER BY nom ASC, prenom DESC;

• IN ou EXIST: Suivez les clés étrangères dans vos critères de recherche

-- le croisement se fait sur projet.responsable_id = utilisateur.id
 
SELECT * FROM projet
WHERE
    responsable_id IN (                              -- le croisement se fait ici
        SELECT utilisateur.id FROM utilisateur       -- et là
        WHERE utilisateur.nom = 'Dalton'
    )
;

-- le croisement se fait sur projet.responsable_id = utilisateur.id
 
SELECT * FROM projet
WHERE EXISTS (
        SELECT 1 FROM utilisateur
        WHERE utilisateur.id = projet.responsable_id   -- le croisement se fait ici
        AND utilisateur.nom = 'Dalton'
    )
;

Afficher la liste des tickets qui ne sont pas de bugs.

SELECT * FROM ticket
WHERE
    numero NOT IN (                      -- j'utilise ici NOT IN
        SELECT ticket_numero FROM bug
    )
;

SELECT * FROM projet
WHERE NOT EXISTS (                           -- j'utilise ici NOT EXISTS
        SELECT 1 FROM ticket
        WHERE ticket.projet_id = projet.id
    )
;

• Définissez des alias pour les tables

Afficher la liste des projets dont le nom est le même que celui d'un autre projet.

SELECT *
FROM projet AS p1
WHERE EXISTS (
    SELECT 1 FROM projet AS p2
    WHERE p2.nom = p1.nom
    AND p2.id <> p1.id
);

• INNER JOIN: Fusionnez le contenu de plusieurs tables

Afficher la liste des noms de projets gérés par les « Dalton » avec les informations du responsable associé.

SELECT projet.nom AS "Nom du projet", utilisateur.*
FROM projet
INNER JOIN utilisateur ON utilisateur.id = projet.responsable_id
WHERE utilisateur.nom = 'Dalton';

La clause INNER JOIN va réaliser un produit cartésien des tables. C'est-à-dire qu'à chaque ligne de la table projet sera associée toutes les lignes de la table utilisateur.

La clause ON va permettre de ne conserver que les lignes qui remplissent les conditions définies après ON. Ici, on ne conserve que les associations de lignes où la valeur de projet.responsable_id correspond à celle de utilisateur.id.

Sans le ON, on aurait par exemple ceci :
Avec ON utilisateur.id = projet.responsable_id, on obtient :

SELECT * FROM projet
INNER JOIN utilisateur ON utilisateur.id = projet.responsable_id;
 
SELECT * FROM projet
JOIN utilisateur ON utilisateur.id = projet.responsable_id;

• OUTER JOIN
  • table_a LEFT OUTER JOIN table_b ON … :
    va ajouter toutes les lignes de table_a où il n'y a aucune correspondance dans table_b
  • table_a RIGHT OUTER JOIN table_b ON … :
    va ajouter toutes les lignes de table_b où il n'y a aucune correspondance dans table_a
  • table_a FULL OUTER JOIN table_b ON …
    va ajouter :
    • toutes les lignes de table_a où il n'y a aucune correspondance dans table_b
    • toutes les lignes de table_b où il n'y a aucune correspondance dans table_a

• Groupez les valeurs et faites des calculs
  Combien y a-t-il de tickets par projet non clôturé ?

SELECT
    projet.id, projet.nom,
    COUNT(ticket.numero) AS "Nombre ticket"   -- comptage des tickets par groupe
FROM projet
JOIN ticket ON ticket.projet_id = projet.id
WHERE projet.cloture = FALSE
GROUP BY projet.id, projet.nom;               -- grouper par projet.id, projet.nom

ajouter un filtre se basant sur le résultat d'un calcul sur le groupe :

SELECT
    projet.id, projet.nom,
    COUNT(ticket.numero) AS "Nombre ticket"
FROM projet
JOIN ticket ON ticket.projet_id = projet.id
WHERE projet.cloture = FALSE
GROUP BY projet.id, projet.nom
HAVING COUNT(ticket.numero) > 10;       -- Ne garder que les lignes avec count(...) > 10

Cette requête n'affiche dans le résultat que les projets qui ont plus de 10 tickets :

Modifier le titre du ticket numéro 64 en « Ajouter le nom du commercial sur l'affichage d'un contrat ».

UPDATE ticket
SET titre = 'Ajouter le nom du commercial sur l''affichage d''un contrat'
WHERE numero = 64;

On peut aller plus loin en se basant sur les données déjà présentes.

UPDATE evolution
SET priorite = priorite + 1
WHERE priorite >= 2;

Passer en majuscules le nom et en minuscules le prénom des utilisateurs.

UPDATE utilisateur SET
    nom = UPPER(nom),
    prenom = LOWER(prenom)
;

On peut aussi utiliser une clause WHERE plus complexe.

UPDATE evolution
SET priorite = priorite + 2
WHERE EXISTS (
    SELECT 1 FROM ticket
    WHERE ticket.numero = evolution.ticket_numero
    AND ticket.projet_id = 18
)
AND priorite >= 3;

La suppression de lignes se fait à l'aide d'une requête de type DELETE.
Supprimer les lignes de la tablehistorique_statutconcernant le projet d'id 18.

DELETE FROM historique_statut
WHERE EXISTS (
    SELECT 1 FROM ticket
    WHERE ticket.numero = historique_statut.ticket_numero
    AND ticket.projet_id = 18
);