DDL : CREATE, ALTER, DROP
NB : Dans cette lecon, nous n’utiliserons pas le client en ligne de commande PSQL mais la plateforme graphique de PostgreSQL : pgAdmin.
Afin de comprendre ce qu’est concrètement une base de données prenons l’exemple d’Excel.
Un classeur Excel est une collection d’informations pouvant contenir une ou plusieurs feuilles Excel.
Voici une feuille Excel :
Le tableau ci-dessus est une table (feuille) contenue dans une database (classeur).
Simplement, une database (base de données) est un contenant dont les contenus sont des tables.
Une table est un croisement de ligne et de colonne. Chaque colonne est une variable avec ses caractéristiques : ses nombres de caractères, ses contraintes, son type. Chaque ligne ou observation est un tuple (référence à l’introduction sur PostgreSQL), un objet avec ses caractéristiques propres.
Créer une database
Pour créer une database, il faut utiliser l’instruction DDL CREATE.
Il y a plusieurs façons de créer sa propre base de données. Dans un premier temps, voyons ensemble une méthode graphique : cliquez droit sur Databases et cliquez sur ‘Create > Database…’
Vous pouvez ensuite donner le nom que vous souhaitez à la database et cliquer sur ‘Save’.
Sinon, vous pouvez aussi rédiger une requête SQL à l’aide de ‘Query Tool’ : créer votre database avec l’instruction DDL CREATE :
CREATE DATABASE mydb;
C’est aussi simple que ça.
NB : pour certains utilisateurs, créer des databases n’est pas forcément nécessaire si elles existent déjà. Ces utilisateurs ne vont utiliser que des instructions DML.
Créer une table
Maintenant que nous avons notre database, nous pouvons créer nos tables. Encore une fois, nous pouvons créer une table en cliquant droit sur Tables et ‘Create > Table…’ ou bien utiliser une requête SQL avec ‘Query Tool’.
-Avec l’instruction DDL CREATE:
CREATE TABLE table1 ();
Modifier une table
Maintenant que vous avons une table, vide, nous devons ajouter des colonnes et leurs caractéristiques à l’aide de l’instruction DDL ALTER.
En cliquant, il suffit de cliquer droit sur notre table et sélectionner ‘Properties…’ puis d’ajouter les colonnes dans l’onglet correspondant.
-Avec une requête SQL :
ALTER TABLE public.table1
ADD id SERIAL NOT NULL PRIMARY KEY,
ADD nom VARCHAR(50) NOT NULL,
ADD prenom VARCHAR(50) NOT NULL,
ADD email VARCHAR(50) NOT NULL,
ADD age INT NOT NULL,
ADD adresse VARCHAR(100) NOT NULL,
ADD ville VARCHAR(50) NOT NULL;
NB : Pour supprimer une colonne d’une table, il suffit de remplacer l’instruction ADD (ou ADD COLUMN) par DROP COLUMN <nom de la colonne> dans l’instruction. Il existe de nombreuses modifications de tables avec l’instruction DDL ALTER.
- Ajouter une contrainte : même syntaxe que lors de la création de table.
ALTER TABLE table1
ADD CONTRAINT, CHECK, FOREIGN KEY, PRIMARY KEY, etc.
- Supprimer une contrainte :
ALTER TABLE table1
DROP CONSTRAINT <contrainte>;
- Déclarer ou supprimer une contrainte non nulle :
ALTER TABLE table1
ALTER COLUMN <colonnes> SET NOT NULL/DROP NOT NULL.
- Modifier le type d’une colonne :
ALTER TABLE table1
ALTER COLUMN <colonne> TYPE <type> USING <colonne> :: <type> ;
- Renommer une colonne :
ALTER TABLE table1
RENAME COLUMN <colonnes1> TO <colonnes2>;
(renommer une table : RENAME TO <table2>;)
Créer une table en définissant ses colonnes
Lors de la création d’une table, si vous avez déjà la structure de la table à disposition, vous pouvez gagner une étape en définissant directement les colonnes et leurs caractéristiques dans l’instruction DDL CREATE TABLE.
-Avec la méthode graphique :
-Avec une requête SQL :
CREATE TABLE public.table1
(
id SERIAL NOT NULL PRIMARY KEY,
nom VARCHAR(50) NOT NULL,
prenom VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
age INT NOT NULL,
adresse VARCHAR(100) NOT NULL,
ville VARCHAR(50) NOT NULL
);
Supprimer une table
Pour supprimer entièrement une table, il faut utiliser l’instruction DDL DROP TABLE.
-Avec la méthode graphique :
-Avec une requête SQL :
DROP TABLE table1, table2;
NB : l’utilisation en requête SQL de l’instruction DROP TABLE permet aussi de supprimer plusieurs tables de la database en séparant les noms des tables par des virgules ‘,’.
Exemple
Maintenant, reprenons la structure de notre dataset « air_quality_Nov2017.csv » que nous avons exploré et nettoyé dans les cours précédents.
Créer la structure d’une table
CREATE TABLE air_quality
(id SERIAL PRIMARY KEY,
Station VARCHAR(50),
"Air Quality" CHAR(1),
Longitude VARCHAR(20),
Latitude VARCHAR(20),
"O3 Hour" VARCHAR(10),
"O3 Quality" VARCHAR(10),
"O3 Value" NUMERIC,
"NO2 Hour" VARCHAR(10),
"NO2 Quality" VARCHAR(10),
"NO2 Value" NUMERIC,
"PM10 Hour" VARCHAR(10),
"PM10 Quality" VARCHAR(10),
"PM10 Value" NUMERIC,
"Generated" DATE,
"Date Time" DATE
);
Vérifier si la table a bien été créée avec la structure
Il suffit de cliquer droit sur notre table, ‘View/Edit Data > All Rows’.
-Avec une requête SQL pour les pros :
SELECT *
FROM air_quality;
Génial ! Voici notre table et sa structure (4 premières colonnes) :
Vous remarquerez qu’en créant des colonnes, nous avons aussi défini la structure de ces colonnes avec des types et des contraintes comme VARCHAR, SERIAL, PRIMARY KEY ou NOT NULL. Nous détaillerons ces concepts dans les cours suivants.
Voilà, vous savez à peu près tout sur la création et la suppression de tables.