BD II: SQL LDD

Définition de données

Équipe BD

LPSM Université Paris-Cité

2025-10-03

Introduction

Définition de données

SQL est un monde à part entière

On peut :

Interroger des données par des requêtes (déjà vu en partie)
Administrer les bases de données :
- Créer celles-ci : définition des tables (schémas, etc), contraintes..
- Créer de nouvelles données, en insérer, supprimer, mettre à jour, etc
- Créer et gérer les utilisateurs, leurs droits sur les objets de la base,
- …

Dans ce cours : création des tables (version simplifiée) et des données

Création de tables

Instruction : CREATE TABLE ...

Permet de définir :

Les noms et les domaines (type) des attributs ainsi que des contraintes d’intégrité comme :
- Contraintes de clés primaires (PRIMARY KEY)
- Contraintes référentielles (clés étrangères FOREIGN KEY, par exemple)
Des contraintes assez générales (de contrôle des données)
Des index (pour la recherche et l’exécution efficaces)

Création de tables : noms et domaines d’attributs

Chaque relation est définie par un nom de relation et une liste d’attributs

CREATE TABLE <nom_relation> (
  <element de relation>
  <,element de relation>*
  <,contrainte de relation>* );

Chaque attribut est défini par un nom d’attribut et un type de données

<element de relation> ::= <nom_attrib><type_donnees>[<contrainte_attrib>*]

Exemples de types

<type_donnees> ::=  VARCHAR <longueur>| INT | REAL | DATE

Exemple

Schéma:

fournisseur(nomf, villef),
piece(nomp, prix, couleur),
livraison(numliv, nomf, nomp, dateliv, quantite)

CREATE TABLE fournisseur(
    nomf VARCHAR(20),
    villef VARCHAR(20)
);

CREATE TABLE piece(
    nomp VARCHAR(20),
    prix REAL,
    couleur VARCHAR(15)
);

Exemple avec valeurs par défaut

CREATE TABLE livraison(
    numLiv INT,
    nomf VARCHAR(20),
    nomp VARCHAR(20),
    dateliv DATE DEFAULT NOW(),
    quantite INT DEFAULT 1
);

Valeurs par défaut

Pour dateliv, c’est la date courante lors de l’insertion de la ligne dans la table,
Pour quantite, c’est une constante.

Exemple : schéma `world`

CREATE TABLE world.city (
    id int4 NOT NULL,
    name text NOT NULL,
    countrycode bpchar(3) NOT NULL,
    district text NOT NULL,
    population int4 NOT NULL,
    CONSTRAINT city_pkey
        PRIMARY KEY (id)
);

CREATE TABLE world.code_continent (
    continent name NOT NULL,
    codecontinent int4 NOT NULL,
    CONSTRAINT code_continent_pk
        PRIMARY KEY (codecontinent)
);

Exemple : schéma `world` avec clé primaire composée

CREATE TABLE world.countrylanguage (
    countrycode bpchar(3) NOT NULL,
    "language" text NOT NULL,
    isofficial bool NOT NULL,
    percentage float4 NOT NULL,
    CONSTRAINT countrylanguage_pkey
        PRIMARY KEY (countrycode, language)
);

Attention

Une clé peut être composée de plusieurs attributs

Exemple: schéma `world` (suite)

CREATE TABLE world.country (
    countrycode bpchar(3) NOT NULL,
    name_country text NOT NULL,
    continent text NOT NULL,
    region text NOT NULL,
    surfacearea float4 NOT NULL,
    indepyear int2 NULL,
    population_country int4 NOT NULL,
    lifeexpectancy float4 NULL,
    gnp numeric(10, 2) NULL,
    gnpold numeric(10, 2) NULL,
    localname text NOT NULL,
    governmentform text NOT NULL,
    headofstate text NULL,
    capital int4 NULL,
    code2 bpchar(2) NOT NULL,
    CONSTRAINT country_continent_check CHECK (
        ((continent = 'Asia'::text) OR (
            continent = 'Europe'::text) OR
            (continent = 'North America'::text) OR
            (continent = 'Africa'::text) OR
            (continent = 'Oceania'::text) OR
            (continent = 'Antarctica'::text) OR
            (continent = 'South America'::text))),
    CONSTRAINT country_pkey PRIMARY KEY (countrycode)
);

Suppression d’une table

DROP TABLE fournisseur;
DROP TABLE piece;
DROP TABLE livraison;

Suppression n’est pas vidange

Supprimer une table, c’est supprimer son contenu (vidange) mais aussi son schéma

Insertion, mise à jour, suppression

Contenu des relations

On peut gérer le contenu des tables directement dans le langage SQL :

Ajouter des tuples à une table : INSERT INTO ...
Mettre à jour des tuples d’une relation : UPDATE ...
Supprimer des tuples : DELETE FROM ...

Insertion

Insertion de tuples : syntaxe

INSERT INTO <nom_relation>
    [(nom_attrib [, nom_attrib]*)]
    VALUES (valeur [, valeur]*);

INSERT INTO piece VALUES('x21', 1.51, 'rouge');

INSERT INTO piece (nomp,couleur) VALUES('x22', 'vert');

+--------+--------+-----------+
| nomp   | prix   | couleur   |
|--------+--------+-----------|
| x21    | 1.51   | rouge     |
| x22    | <null> | vert      |
+--------+--------+-----------+

Attention

Les valeurs doivent être fournies dans l’ordre de déclaration des attributs,
Les valeurs non spécifiées sont affectées à NULL

Insertion, exemple (suite)

INSERT INTO world.Country
  (countrycode, name_country, continent,
   region, surfacearea, indepyear, population_country,
   lifeexpectancy, gnp, gnpold,
   localname, governmentform, headofstate, capital, code2)
VALUES(
    'SYL', 'Syldavia', 'Europe',
    'Eastern Europe', 200000, 1918, 21345717, 81.2, 25213, 24878,
    'Syldavie', 'Constitutional Monarchy', 'Ottokar II', 0, 'SY'
);

Remarques

La dernière partie du constructeur multiligne, qui commence avec VALUES est souvent appelée la liste de valeurs.

Une liste de valeurs peut être définie sans référence à une table existante et définir à la volée une nouvelle table virtuelle/éphémère.

SELECT *
FROM (
  VALUES
    ('robe', 'logged in', '2011-01-10 10:15 AM EST'::timestamptz),
    ('lhsu', 'logged out', '2011-01-11 10:20 AM EST'::timestamptz)
) AS l (user_name, description, log_ts);

Note

Quand on utilise VALUES pour définir une table virtuelle, il faut spécifier le nom des colonnes.

Par défaut, les types des colonnes sont inférés. Si l’inférence de type n’est pas possible, il faut effectuer explicitement des conversions de type comme ici avec ::timestamptz.

Exemple : Insertion de tuples (suite)

INSERT INTO livraison
    VALUES
    (1, 'fournisseur1', 'x21', '2019-07-13', 2);

-- Avec des colonnes non renseignées

INSERT INTO livraison
   (numliv, dateliv, quantite)
    VALUES
   (2, '2018-07-29', 10);

+----------+--------------+--------+------------+------------+
| numliv   | nomf         | nomp   | dateliv    | quantite   |
|----------+--------------+--------+------------+------------|
| 1        | fournisseur1 | x21    | 2019-07-13 | 2          |
| 2        | <null>       | <null> | 2018-07-29 | 10         |
+----------+--------------+--------+------------+------------+

Insertion de tuples par requête

On peut faire une insertion par le biais d’une requête,
La clause VALUES est remplacée par une requête,
Les résultats de la requête sont insérés comme valeurs dans la table.
Pour respecter les contraintes d’unicité, le mot clé DISTINCT peut être nécessaire, en particulier lorsqu’il y a une jointure.

Exemple 1

INSERT INTO FournisseursParisiens (
  SELECT *
  FROM Fournisseur
  WHERE VilleF='Paris'
);

Exemple 2

CREATE TABLE monschema.city () 
  INHERITS (world.city) ;

CREATE TABLE monschema.country () 
  INHERITS (world.country) ;

INSERT INTO monschema.country (
    SELECT * 
    FROM world.country
    WHERE continent = 'Europe' 
);

INSERT INTO monschema.city (
    SELECT ci.*  
    FROM world.city ci NATURAL JOIN 
      world.country co
    WHERE co.continent = 'Europe'
) ;

Mise à jour

Mise à jour de colonnes, de cellules

On peut mettre à jour des tuples d’une table par le biais d’une requête UPDATE.

UPDATE nom_table1
SET nom_attribut = <expression de valeur> 
    [ , nom_attribut = <expression de valeur> ]
[FROM nom_table2 [, [JOIN] nom_table3]]
[WHERE <condition de recherche> ];

Note

<expression de valeur> peut être :

NULL,
une constante explicite,
une expression arithmétique sur les attributs des tables des clauses SET ou FROM,
le résultat d’une requête scalaire.

Avertissement

On ne peut pas utiliser la notation nom_table.nom_attribut dans la clause SET.

Exemple de mise à jour de tuples

Augmentation du prix d’une pièce

UPDATE Piece
SET prix = prix * 1.05
WHERE nomp = 'x21';

Calcul de la nouvelle valeur par une sous-requête

UPDATE Piece
SET Prix = (
    SELECT prix
    FROM Piece
    WHERE nomp = 'x21'
)
WHERE Prix <= 1000;

Utilisation de la clause `FROM`

On peut avoir besoin de plusieurs tables pour sélectionner les tuples à mettre à jour.
La syntaxe à utiliser varie un peu selon le SGBD.
Avec PostgreSQL, on écrira les tables suplémentaires dans une clause FROM (JOIN possible si au moins deux tables supplémentaires).

UPDATE Piece p
SET p.prix = p.prix * 1.1
FROM Livraison l
WHERE
    p.nomp = l.nomp
    AND l.quantite > 2* (SELECT MIN(quantite) FROM Livraison);

Mise en garde

Si, pour une même ligne de la table Piece, plusieurs lignes de la table Livraison vérifient la condition, la mise à jour n’est effectuée qu’une seule fois.

`UPDATE` avec CTE

Il est parfois pratique d’écrire une CTE pour définir les couples (identifiant, nouvelle valeur).

WITH r AS (
  SELECT p.nomp AS id, p.prix * 1.1 AS newvalue
  FROM Piece p
  JOIN Livraison l ON p.nomp = l.nomp
  WHERE l.quantite > 2* (SELECT MIN(quantite) FROM Livraison)
)
UPDATE Piece p
SET prix = r.newvalue
FROM r
WHERE p.nomp = r.id

Suppression de tuples

DELETE FROM <nom_relation>
[USING nom_table2 [, [JOIN] nom_table3]]
[WHERE <condition de recherche>];

Important

L’oubli de la clause where supprime toutes les données de la table.

Exemple de suppression de tuples

DELETE FROM Fournisseur
WHERE NomF = 'FastDelivery';

On supprime dans Fournisseur les tuples correspondant au fournisseur nommé 'FastDelivery'

Suppression de tuples avec requête imbriquée

DELETE FROM Livraison
WHERE NomP IN (
    SELECT NomP
    FROM Piece
    WHERE Couleur='Rouge'
);

Utilisation de la clause `USING`

On peut avoir besoin de plusieurs tables pour sélectionner les tuples à supprimer.
La syntaxe à utiliser varie un peu selon le SGBD. PostgreSQL ne supporte pas l’utilisation de JOIN dans la clause FROM d’une instruction DELETE.
On écrira les tables suplémentaires dans une clause USING (JOIN possible si au moins deux tables supplémentaires).

DELETE FROM monschema.city AS ci
USING monschema.country AS co
WHERE 
  ci.countrycode = co.countrycode AND 
  co.region= 'Eastern Europe' AND 
  co.indepyear = 1991 ;

Suppression de données et contraintes d’intégrité référentielle

Avertissement

La requête précédente pose-t’elle problème ?

Les identifiants de l’attribut country.capital pour les pays vérifiant country.region = 'Eastern Europe' AND country.indepyear = 1991 ne seront plus référencés dans la table city.

Résumé

On a vu comment créer des tables (de façon simplifiée) et gérer le contenu de celles-ci,
On peut spécifier bien plus de contraintes dans la partie création de tables,
L’ajout ou la suppression de données n’est pas libre : les contraintes (de clés notamment) doivent être satisfaites après l’exécution des mises à jours.

Fin

SQL : Définition de données, Insertion, …

BD II: SQL LDD

Introduction

Définition de données

Création de tables

Création de tables

Création de tables : noms et domaines d’attributs

Exemples de types

Exemple

Exemple avec valeurs par défaut

Exemple : schéma world

Exemple : schéma world avec clé primaire composée

Exemple: schéma world (suite)

Suppression d’une table

Insertion, mise à jour, suppression

Contenu des relations

Insertion

Insertion de tuples : syntaxe

Insertion, exemple (suite)

Remarques

Exemple : Insertion de tuples (suite)

Insertion de tuples par requête

Exemple 1

Exemple 2

Mise à jour

Mise à jour de colonnes, de cellules

Exemple de mise à jour de tuples

Calcul de la nouvelle valeur par une sous-requête

Utilisation de la clause FROM

UPDATE avec CTE

Suppression de tuples

Suppression de tuples

Exemple de suppression de tuples

Suppression de tuples avec requête imbriquée

Utilisation de la clause USING

Suppression de données et contraintes d’intégrité référentielle

Résumé

Fin

Exemple : schéma `world`

Exemple : schéma `world` avec clé primaire composée

Exemple: schéma `world` (suite)

Utilisation de la clause `FROM`

`UPDATE` avec CTE

Utilisation de la clause `USING`