BD II: SQL LDD

Définition de données

Équipe BD

LPSM Université Paris-Cité

2024-10-04

Plan

• Introduction
• Création de tables
• Insertion, mise à jour, suppression
• Insertion
• Mise à jour
• Suppression de tuples
• Résumé
• Fin

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

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_city text NOT NULL,
    countrycode bpchar(3) NOT NULL,
    district text NOT NULL,
    population_city 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.

On peut donc :

• 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érene à 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);

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 pas 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         |
+----------+--------------+--------+------------+------------+

Rappel

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

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

Alimentation d’une nouvelle table à l’aide des données d’une autre

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

Exemple

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

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

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

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

Mise à jour

Mise à jour de colonnes, ed cellules

• On peut mettre à jour des tuples existants par le biais d’une requête

  • Soit tous les tuples de la table,
  • Soit l’ensemble des tuples vérifiants une condition donnée

UPDATE nom_relation
SET nom_attribut = <expression de valeur> 
    [ , nom_attribut = <expression de valeur> ]*
[WHERE <condition de recherche> ];

Mise à jour conditionnelle de tuples

Le WHERE permet de sélectionner les tuples à mettre à jour

UPDATE Fournisseur
SET VilleF='St.Petersburg'
WHERE VilleF='Leningrad';

On peut aussi effectuer des opérations sur les tuples à mettre à jour.

La clause <expression de valeur> peut être :

• NULL

• Une constante (explicite ou via une requête)

• Une expression arithmétique sur les attributs de la table à modifier

Mise à jour de tuples (exemple)

Augmentation du prix d’une pièce

UPDATE Piece
SET Prix = Prix * 1.05
WHERE NomP = 'x21';

Mise à jour par requête

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

Suppression de tuples

Suppression de tuples : syntaxe

DELETE FROM <nom_relation>
[WHERE <condition de recherche>];

Exemple

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'
);

DELETE FROM <table> USING <t> WHERE ...

Note

Souvent, lorsque vous supprimez des données d’une table, vous souhaitez supprimer ces données en fonction de leur présence dans un autre ensemble de données.

Spécifiez cet ensemble supplémentaire avec le prédicat USING

DELETE FROM <table> USING <t> WHERE ...

Astuce

Souvent, lorsque vous supprimez des données d’une table, vous souhaitez supprimer ces données en fonction de leur présence dans un autre ensemble de données.

Spécifiez cet ensemble supplémentaire avec le prédicat USING

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

Note

La requête suivante pose-t’elle problème ?

DELETE FROM Piece
WHERE NomP IN (
  SELECT NomP
  FROM Livraison
  WHERE Quantite>10
);

Peut-être !

Cela dépend des contraintes d’intégrité qui pèsent sur la base

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, …