Les tables
Une table ou une entité
Une table encore appelée une entité est un tableau dans lequel les données sont stockées. Une table contient une ou plusieurs colonnes. Les colonnes représentent l’en-tête de la table et les lignes représentent le corps de la table. Les colonnes sont également appelées les attributs ou les champs.
La structure d’une table contient les champs et les lignes (appelés souvent les enregistrements) comme présente par exemple la table ci-dessous :
Table student
id_student | last_name | first_name | average | id_activity | address |
1 | Luca | Andro | 12.50 | 4 | 89 Oak Ave |
2 | Moricio | Jim | 11.47 | 1 | 148 Elm St |
3 | Daly | Spenxief | 08.95 | 3 | Mercer St |
4 | Patricia | Rossiny | 14.75 | 3 | NY 10019 Bld |
5 | Miller | Lineroz | 17.80 | 1 | 60w 57th St |
6 | Hines | Mark | 10.25 | 2 | 432 Park Ave |
7 | Salvador | Bertrand | 17.02 | 1 | 31 Broad Bld |
Les propriétés d’une table
Les colonnes (attributs) d’une table constituent les propriétés de cette table.
Ces propriétés sont soumises aux contraintes d’intégrité de données des systèmes de gestion des bases de données SGBD pendant la création d’une table.
Les champs
Un champ ou une colonne ou un attribut
Un champ ou une colonne est un élément vertical dans une table représentant un ensemble de valeurs. Dans une table, la colonne correspond généralement à un attribut. Les attributs sont encore appelés les champs ou les propriétés. Il n’est pas possible d’avoir deux attributs de même nom dans une table. Si on prend par exemple la table student, elle possède six colonnes ou attributs (id_stdent, last_name, first_name, average, id_activity, address).
id_student | last_name | first_name | average | id_activity | address |
Une colonne a pour valeur une ligne, c’est-à-dire la donnée ou l’information enregistrée sur la ligne.
Les lignes
Une ligne (appelée parfois enregistrement)
Une ligne est un élément horizontal dans une table représentant un ensemble de valeurs des différents attributs de la table. Une ligne est souvent appelé ‘enregistrement’ sauf que le mot « enregistrement » n’est pas approprié dans le monde de bases de données car il fait référence à un support de stockage physique.
Chaque ligne d’une colonne représente une valeur pour la colonne. Une ligne sans valeur ou donnée doit contenir un NULL qui est une contrainte SQL.
Exemple
La table student contient sept lignes et chaque ligne possède six valeurs (données).
Table student
id_student | last_name | first_name | average | id_activity | address |
1 | Luca | Andro | 12.50 | 4 | 89 Oak Ave |
2 | Moricio | Jim | 11.47 | 1 | 148 Elm St |
3 | Daly | Spenxief | 08.95 | 3 | Mercer St |
4 | Patricia | Rossiny | 14.75 | 3 | NY 10019 Bld |
5 | Miller | Lineroz | 17.80 | 1 | 60w 57th St |
6 | Hines | Mark | 10.25 | 2 | 432 Park Ave |
7 | Salvador | Bertrand | 17.02 | 1 | 31 Broad Bld |
Si on prend par exemple la ligne 2 de cette table ci-dessus:
L’attribut id_student a pour valeur 2 ;
L’attribut last_name a pour valeur Moricio ;
L’attribut first_name a pour valeur Jim ;
L’attribut average a pour valeur 11.47 ;
L’attribut id_activity a pour valeur 1 ;
L’attribut address a pour valeur 148 Elm St.
Donc l’ensemble de chaque valeur de chaque colonne de la table constitue une ligne.
Chaque valeur d’une colonne donnée doit être d’un type donné par exemple le texte, numérique, alphanumérique, etc. Pendant la création d’une table, on définit un type de données pour chaque colonne. Si une colonne est définie pour stocker les valeurs de type texte, elle ne peut plus stocker autres valeurs que les valeurs de type texte. On appelle ces différents types de données les types de données SQL.
Les types de données SQL
Lorsque l’on crée une table dans une base de données en utilisant un script SQL ou tout autre méthode, chaque attribut doit être déclaré d’un type de données (type texte, nombre, date,…).
Les types de données SQL sont généralement classés en 4 types à savoir les types numériques, alphanumériques, temporels et spatiaux.
Les types Numériques
Ils regroupent les nombres entiers et les nombres décimaux,
Types | Stockage sur | Intervalle |
Nombres entiers | ||
TINYINT | 1 Octet | Stocke des entiers compris entre -128 et 127 ou de 0 à 255 avec UNSIGNED |
SMALLINT | 2 Octets | Stocke des entiers compris entre compris entre -32768 et 32767 ou de 0 à 65 535 avec UNSIGNED |
MEDIUMINT | 3 Octets | Stocke des entiers compris entre compris entre -8 388 608 et 8 388 607 ou de 0 à 16 777 215 avec UNSIGNED |
INT ou INTEGER | 4 Octets | Stocke des entiers compris entre -2 147 4783 648 et 2 147 483 647 ou de 0 à 4 294 967 295 avec UNSIGNED |
BIGINT | 8 Octets | Stocke des entiers compris entre -9 223 372 036 854 775 808 et 9 223 372 036 854 775 807 ou de 0 à 18 446 744 073 709 551 616 avec UNSIGNED |
Nombres décimaux | ||
Types | Stockage sur | Description |
FLOAT | 4 Octets | Stocke des réels (nombres à virgule positif ou négatif) dont la valeur est approximative avec des arrondis un peu « violent ». L’ordre des arrondis peut être aléatoire |
REAL | 4 Octets | Stocke des réels comportant 6 chiffres après la virgule. se comporte comme un FLOAT |
DOUBLE | 8 Octets | Stocke des réels comportant 15 chiffres après la virgule. La valeur est approximative mais plus précise que FLOAT |
DECIMAL | 8 Octets | Stocke des nombres à virgule dont la valeur est exacte |
Remarque : Les décimaux de types FLOAT, REAL, DOUBLE stockent des nombres avec des valeurs approximatives tandis que type DECIMAL (il y a aussi NUMERIC) stocke des nombres avec valeurs exactes. Par exemple pour un nombre décimal comme 43.45126879213584 ; FLOAT peut couper le nombre en stockant 43.45 ; REAL peut couper le nombre en stockant 43.451269 ; et ainsi de suite. Par contre on peut stocker exactement ce nombre en utilisant le type DECIMAL en le paramétrant comme DECIMAL (5, 14).
FLOAT, REAL, DOUBLE n’utilisent pas des paramètres contrairement à DECIMAL qui utilise des paramètres (DECIMAL (E, D)). Le paramètre E désigne le nombre de chiffres pour la partie entière de nombre et le paramètre D désigne le nombre de chiffres pour la partie décimale du nombre à stocker. Lorsqu’on utilise le type DECIMAL sans paramètre, la partie décimale du nombre n’est pas stockée, c’est seulement la partie entière du nombre qui est stocké. Par exemple on veut stocker ce nombre 421.35221462
Type d’attribut | Entrée | Sortie (Donnée stockée) |
DECIMAL | 421.35221462 | 421 |
DECIMAL (10, 5) | 421.35221462 | 421.35221 |
8.2544896641 | 8.25449 (Il arrondit) |
Intérêt des types décimaux
Il est préférable d’utiliser le type DECIMAL ou NUMERIC que les types FLOAT, REAL, DOUBLE pour les attributs à nature monétique comme un compte bancaire par exemple.
Remarque :
L’attribut UNSIGNED
Il n’accepte pas les nombres négatifs. Lorsqu’on ajoute UNSIGNED à un attribut, on l’interdit de stocker les valeurs (nombres) négatives. Par contre la partie positive de son intervalle de stockage est doublée. Par exemple pour un champ de type TINYINT, l’intervalle de stockage est de [-128 à 127]. Si on applique UNSIGNED à ce champ de type TINYINT, son intervalle de stockage sera alors de [0 à 255]. C’est comme si on calculait la distance de cet intervalle en faisant 127 – (-128) = 255.
L’attribut ZEROFILL
ZEROFILL ajoute des zéros à gauche d’un nombre. Dans certains SGBD c’est ZEROFILL et dans d’autres c’est UNSIGNED ZEROFILL. Par exemple soit un attribut num_phone de type INT avec ZEROFILL va donner :
Attribut num_phone | Entrée | Sortie (Donnée Stocké) |
num_phone TINYINT(3) UNSIGNED ZEROFIL | 24 | 024 |
124 | 124 | |
231 | Error | |
num_phone INT(5) UNSIGNED ZEROFIL | 24 | 00024 |
13224 | 13224 | |
num_phone INT UNSIGNED ZEROFIL | 24 | 0000000024 |
Les types temporels
Ils regroupent les dates et heures
Types | Stockage au format | Description |
DATE | YYYY-MM-DD | Une date |
DATETIME | YYYY-MM-DD HH:MM:SS | Une date et une heure |
TIMESTAMP | YYYY-MM-DD HH:MM:SS | TIMESTAMP d’une date représente le nombre de secondes écoulées depuis le 1er janvier 1970 et la date concernée |
TIME | HH:MM:SS | Une heure |
YEAR | YYYY | Une année |
Exemple de TIMESTAMP de 20 Février 2020 à 18h 30 min 54 s. Le timestamp de cette date est nombre total de seconde depuis le 1er Janvier 1970 jusqu’à 20 Février 2020 à 18h 30 min 54 s
Pour DATETIME on a 2020–02–20 18 :30 :54
Pour TIMESTAMP on a 20200220183054, donc 20200220183054 secondes. Malheureusement dans MySQL, TIMESTAMP s’affiche simplement comme DATETIME.
Les types alphanumériques
Ils regroupent les chaines de caractères, textes et binaires.
Les Textes et chaines de caractères
Types | Nombre de caractères |
CHAR | Stocke une chaine de 0 à 255 caractères |
VARCHAR | Stocke une chaine de 0 à 65 535 caractères |
TINYTEXT | Stocke un texte de 0 à 255 caractères soit 2^8 caractères. Par exemple 3 lignes de texte |
TEXT | Stocke un texte de 0 à 65 535 caractères soit 2^16 caractères. Par exemple un article |
MEDIUMTEXT | Stocke un texte de 0 à 16 777 215 caractères soit 2^24 caractères. Par exemple un petit livre |
LONGTEXT | stocke un texte de 0 à 4 294 967 295 caractères soit 2^32 caractères. Par exemple un ouvrage |
La chaine de caractère ou le texte peut contenir tout : lettres, chiffres, toute sorte de caractères qui existe.
Remarque : Le types CHAR et VARCHAR
CHAR et VARCHAR sont très stricts sur le nombre de caractères que l’on précise. Si on précise par exemple 8 comme le taille (nombre de caractères) c’est-à-dire VARCHAR(8) et que l’on saisit une chaine de 9 caractères, cette chaine sera rejetée. Il est donc préférable de saisir la taille maximale 255.
Avec CHAR quand la taille d’une valeur enregistrée n’atteint pas la valeur fixé, CHAR complète les espaces à droite jusqu’à atteindre la taille fixée contrairement à VARCHAR qui ne complète pas des espaces. Pendant la lecture, ces espaces complétés à droite sont retirés.
Les chaines de caractères binaires
Ils stockent les données binaires non interprétées. Ceci permet de stocker par exemple des différents types fichiers. Les types BINARY et VARBINARY sont similaires à CHAR et VARCHAR, sauf le fait qu’ils contiennent des chaînes binaires, plutôt que des chaînes de texte. Comme CHAR, BINARY stocke des valeurs avec des espaces à droite contrairement à VARBINAIRE et VARCHAR qui ne stockent pas des espaces.
Types | Nombre de caractères |
BINARY | Stocke une chaine de 0 à 255 octets |
VARBINARY | Stocke une chaine de 0 à 65 535 octets |
TINYBLOB | Stocke une chaine binaire de 0 à 255 octets |
MEDIUMBLOB | Stocke une chaine binaire de 0 à 65 535 octets |
BLOB | Stocke une chaine binaire de 0 à 16 777 215 octets |
LONGBLOB | Stocke une chaine binaire de 0 à 4 294 967 295 octets |
Les types constants
Il s’agit de NUM et SEP. Ce sont des types propres à MySQL. Les types constants permettent de stocker une liste de valeurs pour un attribut.
Types | Nombre de caractères |
ENUM | On peut avoir une liste de 0 à 65 535 valeurs possibles et c’est une seule valeur qui sera stockée. ENUM (‘valeur1’, ‘valeur2’, ‘valeur3’, …). Il est recommandé d’énumérer les valeurs numériques. |
SET | On peut avoir une liste de 0 à 64 valeurs possibles et c’est possible de stocker une ou plusieurs valeurs parmi la liste de 64. SET (‘valeur1’, ‘valeur2’, ‘valeur3’, …) |
Les enregistrements
Les enregistrements des données dans des tables en SQL se font en utilisant la commande INSERT INTO. Il existe plusieurs façons d’insérer des données dans des tables ou dans une base de données. Avec cette commande INSERT INTO, on peut :
Insérer une ligne dans une table ;
Insérer plusieurs lignes à la fois dans une table ;
Insérer une ligne en choisissant certaines colonnes de la table.
En dehors de la commande INSERT INTO, on peut faire l’insertion de données par l’exécution des fichiers SQL.
- Insertion dans toutes les colonnes d’une table
Insertion d’une ligne dans une table
Elle permet de stocker une valeur pour chaque colonne de la table. La syntaxe donne :
INSERT INTO nom_table (colonne1, colonne2, . . ., colonne (n)) VALUES (‘valeur1’, ‘valeur2’, . . ., ‘valeur (n)’) ;
Sans mentionner des colonnes dans la requête
INSERT INTO nom_table VALUES (‘valeur1’, ‘valeur2’, . . ., ‘valeur (n)’) ;
Les valeurs de type chaine de caractères ou texte sont entre griffes (‘ ‘) par contre les valeurs de type numérique ne se mettent pas entre griffes.
Dans une base de données relationnelle où les tables sont liées, il est très important de souligner que pour insérer des données dans des tables, on commence par les tables qui ne contiennent pas de clés étrangères et en suite on termine celles qui contiennent des clés étrangères. Dans la structure de la requête, tous les attributs de la table où l’AUTO-INCREMENT s’applique ne doivent pas avoir de valeurs dans la requête d’insertion.
Demo
Dans notre cas ici l’AUTO_INCREMENT s’applique à la clé primaire id_student, donc la colonne id_student dans la requête prend sans doute la valeur NULL.
INSERT INTO student (id_student, last_name, first_name, average, id_activity, address)
VALUES (NULL, ‘Luca’, ‘Andro’, 12.50, 1, ‘148 Elm St’) ;
Sans mentionner des colonnes dans la requête
INSERT INTO student VALUES (NULL, ‘Luca’, ‘Andro’, 12.50, 1, ‘148 Elm St’) ;
Insertion de plusieurs lignes dans une table
Dans ce cas on veut insérer toutes les données dans une table par une seule requête. La syntaxe donne :
INSERT INTO nom_table (colonne1, colonne2, . . ., colonne (n))
VALUES (‘valeur11’, ‘valeur12’, . . ., ‘valeur (1n)’),
(‘valeur21’, ‘valeur22’, . . ., ‘valeur (2n)’),
(‘valeur31’, ‘valeur32’, . . ., ‘valeur (3n)’) ;
Sans mentionner des colonnes dans la requête
INSERT INTO nom_table VALUES (‘valeur11’, ‘valeur12’, . . ., ‘valeur (1n)’),
(‘valeur21’, ‘valeur22’, . . ., ‘valeur (2n)’),
(‘valeur31’, ‘valeur32’, . . ., ‘valeur (3n)’) ;
Demo
INSERT INTO student (id_student, last_name, first_name, average, id_activity, address)
VALUES (NULL,’Luca’, ‘Andro’, 12.50, 4, ’89 Oak Ave’),
(NULL, ‘Moricio’, ‘Jim’, 11.47, 1, ‘148 Elm St’),
(NULL, ‘Daly’, ‘Spenxief’, 08.95, 3, ‘Mercer St’),
(NULL, ‘Patricia’, ‘Rossiny’, 14.75, 3, ‘NY 10019 Bld’),
(NULL, ‘Miller’, ‘Lineroz’, 17.80, 1, ’60w 57th St’),
(NULL, ‘Hines’, ‘Mark’, 10.25, 2, ‘432 Park Ave’),
(NULL, ‘Salvador’, ‘Bertrand’, 17.02, 1, ’31 Broad Bld’);
Sans mentionner des colonnes dans la requête
INSERT INTO student VALUES (NULL,’Luca’, ‘Andro’, 12.50, 4, ’89 Oak Ave’),
(NULL, ‘Moricio’, ‘Jim’, 11.47, 1, ‘148 Elm St’),
(NULL, ‘Daly’, ‘Spenxief’, 08.95, 3, ‘Mercer St’),
(NULL, ‘Patricia’, ‘Rossiny’, 14.75, 3, ‘NY 10019 Bld’),
(NULL, ‘Miller’, ‘Lineroz’, 17.80, 1, ’60w 57th St’),
(NULL, ‘Hines’, ‘Mark’, 10.25, 2, ‘432 Park Ave’),
(NULL, ‘Salvador’, ‘Bertrand’, 17.02, 1, ’31 Broad Bld’);
- Insertion dans quelques colonnes de la table
Il s’agit de ne pas suivre l’ordre des colonnes, c’est-à-dire on peut sauter certaines colonnes dans la requête. Ce type d’insertion est possible si et seulement on n’a pas appliqué NOT NULL aux attributs pendant la création de la table. Si la contrainte NOT NULL a été appliquée aux colonnes alors il faut d’abord modifier cette valeur par défaut NOT NULL en NULL. Pour MySQL par exemple avec la page d’administration web phpmyadmin, il suffit d’aller dans la Structure de la table pour les changer.
L’avantage de ce type d’insertion est que l’on peut enregistrer des données non complètes. Toutes les colonnes peuvent ne pas être dans la requête.
Insertion d’une ligne
INSERT INTO nom_table (colonne(a), colonne(d), . . ., colonne (n))
VALUES (‘valeur(a)’, ‘valeur(d)’, . . ., ‘valeur (n)’) ;
Insertion de plusieurs lignes
INSERT INTO nom_table colonne(a), colonne(d), . . ., colonne (n))
VALUES (‘valeur (a1)’, ‘valeur (d1)’, ‘valeur (n1)’),
(‘valeur (a2)’, ‘valeur (d2)’, ‘valeur (n2)’),
(‘valeur (a3)’, ‘valeur (d3)’, ‘valeur (n3)’) ;
Demo
Nous allons inserer seulement pour 3 colonnes de la table student et laisser les 2 autres colonnes. Après l’exécution de cette requête, on verra que toutes les colonnes qui n’ont pas été mentionnées dans la requête vont prendre des valeurs nulles (NULL).
INSERT INTO student (id_student, last_name, address)
VALUES (NULL, ‘Luca’, ’89 Oak Ave’);
Pour plusieurs lignes
INSERT INTO student (id_student, last_name, address)
VALUES (NULL, ‘Luca’, ’89 Oak Ave’),
(NULL, ‘Moricio’, ‘148 Elm St’)
(NULL, ‘Daly’, ‘Mercer St’)
(NULL, ‘Patricia’, ‘NY 10019 Bld’);
- Insertion par l’exécution de fichiers
Pour ce type d’insertion de données, on écrit généralement le script SQL hors du système de gestion de base de données. On peut utiliser n’importe quel éditeur de fichier pour écrire mais à condition d’enregistrer ce fichier en SQL. Ce type de fichier contient le nom de la base de données, les noms de toutes les tables et les codes d’insertion des lignes dans des tables. Il existe une commande SQL pour exécuter ces fichiers depuis la console MySQL :
La commande SQL SOURCE nom_fichier.sql
Lorsqu’on ouvre la console MySQL, on saisit la commande SOURCE et ajoute le repertoire du fichier.sql comme par exemple SOURCE F:\Documents\monfichier.sql pour les systèmes Windows.
Remarque : L’insertion des données dans des tables peut également se faire en passant par la partie Insérer de la page d’administration web phpmyadmin.
Suppression d’une table
Comment enregistrer des tables dans une base de données ?
Lorsqu’on crée une base de données, il faut insérer les tables en son sein. Pour faire cette insertion on sélectionne le nom en utilisant la commande USE suivi du nom de la base de données.
USE nom_BDD ;
Demo
Supposons qu’on veut créer une base de données de nom customer avec deux tables student et subject.
CREATE DATABASE customer ;
USE customer ;
CREATE TABLE student (
id_student INT(10) NOT NULL AUTO_INCREMENT,
last_name VARCHAR(40),
first_name VARCHAR(40),
average FLOAT,
id_activity INT(10),
address VARCHAR(30),
FOREIGN KEY(id_activity) REFERENCES subjects(id_activity),
PRIMARY KEY(id_student)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE subject (
id_activity INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
cost VARCHAR(40),
INDEX beta (id_activity),
PRIMARY KEY (id_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Il est important de préciser dans des tables l’encodage utf8 et le moteur de stockage InnoDB. Il existe plusieurs moteurs de stockage des données pour MySQL. InnoDB est transactionnel et relationnel, donc favorable à l’usage de clés étrangères.