Comment créer une table en SQL – Exemple de requête Postgres et MySQL

Savoir comment créer des tables en SQL est un concept important et fondamental.

Dans ce tutoriel, je vous guiderai à travers la syntaxe SQL de l’instruction CREATE TABLE en utilisant des exemples de code pour PostgreSQL et MySQL.

Syntaxe de base de l’instruction CREATE TABLE

Voici la syntaxe de base de l’instruction CREATE TABLE:

CREATE TABLE nom_table(
	column1 data_type column_constraint,
    column2 data_type column_constraint,
    column3 data_type column_constraint,
    column4 data_type column_constraint,
    ... etc
) ;

Pour la première partie, vous devez commencer par l’instruction CREATE TABLE suivie du nom de la table que vous voulez créer.

Si je voulais créer une table d’informations sur les enseignants, j’écrirais quelque chose comme ceci :

CREATE TABLE teachers() ;

À l’intérieur de la parenthèse, vous ajouterez les informations pour créer les colonnes de la table. Si vous oubliez les parenthèses, vous obtiendrez un message d’erreur.

CREATE TABLE teachers ;
Screen-Shot-2021-09-19-at-5.54.40-PM

Le point-virgule à la fin de la parenthèse indique à l’ordinateur qu’il s’agit de la fin de l’instruction SQL. Vous entendrez parfois parler d’un terminateur d’instruction.

Que sont les moteurs de stockage MySQL

?
Selon la documentation de MySQL:

Les moteurs de stockage sont des composants MySQL qui gèrent les opérations SQL pour différents types de tables.

MySQL utilise ces moteurs de stockage pour effectuer des opérations CRUD (création, lecture, mise à jour et suppression) sur la base de données.

Dans MySQL, vous avez la possibilité de spécifier le type de moteur de stockage que vous souhaitez utiliser pour votre table. Si vous omettez la clause ENGINE, le moteur par défaut sera InnoDB.

CREATE TABLE nom_table(
	column1 data_type column_constraint,
    column2 data_type column_constraint,
    column3 data_type column_constraint,
    column4 data_type column_constraint,
    ... etc
)ENGINE=storage_engine ;

Qu’est-ce que la clause IF NOT EXISTS

?
Il existe une clause facultative appelée IF NOT EXISTS qui vérifie si la table que vous voulez créer existe déjà dans la base de données. Vous pouvez placer cette clause juste avant le nom de la table.

CREATE TABLE IF NOT EXISTS teachers() ;

Si la table existe déjà, l’ordinateur ne créera pas de nouvelle table.

Voir aussi :  Insérer dans le SQL - Comment insérer dans une requête de table [Exemple de déclaration]

Si vous omettez la clause IF NOT EXISTS et que vous essayez de créer une table qui existe déjà dans la base de données, vous obtiendrez un message d’erreur.

Dans cet exemple, j’ai d’abord créé une table appelée teachers. Mais si j’essaie de créer cette même table dans la commande suivante, je rencontrerai une erreur.

CREATE TABLE IF NOT EXISTS teachers() ;
CREATE TABLE teachers() ;
Screen-Shot-2021-09-19-at-5.27.02-PM

Comment créer des colonnes dans la table

À l’intérieur de la parenthèse de l’instruction CREATE TABLE, vous allez énumérer les noms des colonnes que vous souhaitez créer, ainsi que leurs types de données et leurs contraintes.

Voici un exemple de la façon dont nous pouvons ajouter quatre colonnes – school_id, name, email et age – à notre table teachers. Chaque nom de colonne doit être séparé par une virgule.

CREATE TABLE teachers(
	school_id data_type column_constraint, 
	nom_données_type_colonne_constraint,
    email data_type column_constraint, 
	âge - type de données - contrainte de colonne
) ;

Selon la documentation de MySQL:

MySQL a une limite stricte de 4096 colonnes par table, mais le maximum effectif peut être inférieur pour une table donnée. La limite exacte de colonnes dépend de plusieurs facteurs.

Si vous travaillez sur de petits projets personnels MySQL, vous n’aurez probablement pas à vous soucier du dépassement du nombre de colonnes pour vos tables.

Selon la documentation de PostgreSQL, il existe une limite de 1600 colonnes par table. Comme pour MySQL, la limite exacte peut varier en fonction de l’espace disque ou des restrictions de performance.

Types de données en SQL

Lorsque vous créez des colonnes dans une table, vous devez leur attribuer un type de données. Les types de données décrivent le type de valeur contenu dans les colonnes.

Voir aussi :  ROW_NUMBER en SQL - Exemple de sélection supérieure en SQL et SQL Server

Voici six catégories populaires de types de données en SQL:

  1. Numérique (int, float, serial, decimal, etc)
  2. Données et temps (timestamp, data, time, etc)
  3. Caractère et chaîne (char, varchar, text, etc.)
  4. Unicode (ntext, nvarchar, etc.)
  5. Binaire (binary, etc.)
  6. Divers (xml, table, etc.)

Cet article ne va pas passer en revue tous les types de données, mais couvrir quelques-uns des plus populaires.

Voici la liste complète des types de donnéesPostgreSQL et des types de donnéesMySQL.

Qu’est-ce que SERIAL et AUTO_INCREMENT

?
Dans PostgreSQL, un type de données SERIAL est un nombre entier qui sera automatiquement incrémenté de un pour chaque nouvelle ligne créée.

Nous pouvons ajouter SERIAL juste après la colonne school_id dans notre table teachers.

school_id SERIAL

Dans MySQL, vous utiliseriez AUTO_INCREMENT au lieu de SERIAL. Dans cet exemple, on utilise le type de données INT qui représente un nombre entier.

school_id INT AUTO_INCREMENT

Si nous ajoutons cinq lignes à notre table teachers, le résultat affichera les nombres 1, 2, 3, 4, 5 pour la colonne school_id car le nombre entier s’incrémente automatiquement de un pour chaque nouvelle ligne.

Screen-Shot-2021-10-23-at-6.09.13-PM

Qu’est-ce que le type de données VARCHAR

?
Un type de données VARCHAR est une chaîne de caractères variable dans laquelle vous pouvez définir une longueur maximale de caractères.

Voici un exemple d’utilisation du type de données VARCHAR pour les colonnes de nom et d’adresse électronique dans notre table de professeurs. Le nombre 30 correspond à la longueur maximale des caractères.

name VARCHAR(30) column_constraint,
email VARCHAR(30) column_constraint,

Contraintes de colonne en SQL

Il s’agit de règles qui sont appliquées aux données contenues dans les colonnes de la table.

Voici une liste des contraintes de colonne les plus courantes :

  • PRIMARY KEY – Cette clé sert d’identifiant unique pour la table
  • FOREIGN KEY – cette clé permet de s’assurer que les valeurs d’une colonne sont également présentes dans une autre table. Elle sert de lien entre les tables.
  • UNIQUE – toutes les valeurs de la colonne doivent être uniques
  • NOT NULL – les valeurs ne peuvent pas être NULL. NULL est l’absence d’une valeur
  • CHECK – teste une valeur par rapport à une expression booléenne
Voir aussi :  Comptage SQL - Comment sélectionner, additionner et calculer la moyenne des lignes en SQL

Exemples de clés PRIMAIRE et FOREIGN

Dans notre table des enseignants, nous pouvons ajouter une clé PRIMAIRE à la colonne school_id.

Voici à quoi ressemblerait le code dans PostgreSQL :

 school_id SERIAL PRIMARY KEY

Voici à quoi ressemblerait le code en MySQL :

school_id INT AUTO_INCREMENT PRIMARY KEY

Si vous voulez avoir plus d’une colonne pour la PRIMARY KEY, vous devez l’ajouter juste après la création de votre colonne.

CREATE TABLE nom_table(
	column1 data_type column_constraint,
    column2 data_type column_constraint,
    column3 data_type column_constraint,
    column4 data_type column_constraint,
    ... etc
    PRIMARY KEY (colonne1, colonne2)
) ;

Si vous voulez lier une table à une autre, vous pouvez utiliser une FOREIGN KEY.

Disons que nous avons une table appelée district_employees avec une clé primaire de district_id. Voici à quoi ressemblerait le code dans PostgreSQL :

CREATE TABLE district_employees(
   district_id SERIAL PRIMARY KEY,
   nom_employé VARCHAR(30) NON NULL,
   PRIMARY KEY(district_id)
) ;

Dans notre table teachers, nous pouvons utiliser une clé étrangère et référencer la table district_employees.

district_id INT REFERENCES district_employees(district_id),
CREATE TABLE teachers(
    school_id SERIAL PRIMARY KEY,
    district_id INT REFERENCES district_employees(district_id),
    column1 data_type column_constraint,
    column2 data_type column_constraint,
    column3 data_type column_constraint,
    column4 data_type column_constraint,
    ... etc 
) ;

Exemples de NOT NULL, CHECK et UNIQUE

Si nous voulons nous assurer que nous n’avons pas de valeurs qui sont nulles, nous pouvons utiliser la contrainte NOT NULL.

nom VARCHAR(30) NOT NULL

Nous pouvons utiliser la contrainte CHECK pour nous assurer que tous nos enseignants ont 18 ans et plus. La contrainte CHECK teste une valeur par rapport à une expression booléenne.

age INT CHECK(age >= 18)

Si l’une de nos valeurs ne remplit pas cette condition, nous obtenons un message d’erreur.

Screen-Shot-2021-09-19-at-10.47.07-PM

Nous pouvons utiliser la contrainte UNIQUE pour nous assurer que tous les e-mails sont uniques.

email VARCHAR(30) UNIQUE

Voici le résultat final pour la table des enseignants :

Screen-Shot-2021-10-23-at-8.09.36-PM

Voici à quoi ressemblerait le code dans PostgreSQL :

CREATE TABLE teachers(
	school_id SERIAL PRIMARY KEY,
	nom VARCHAR(30) NOT NULL,
    email VARCHAR(30) UNIQUE,
	age INT CHECK(age >= 18)   
) ;

Voici à quoi ressemblerait le code en MySQL :

CREATE TABLE teachers(
	school_id INT AUTO_INCREMENT PRIMARY KEY,
	nom VARCHAR(30) NOT NULL,
    email VARCHAR(30) UNIQUE,
	age INT CHECK(age >= 18)   
) ;

J’espère que cet article vous a plu et je vous souhaite bonne chance dans votre voyage SQL.