Bases de données en SQL : tables, champs, enregistrements ou lignes et types de données SQL

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

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).

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

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,

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

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 :

Les types temporels 

Ils regroupent les dates et heures

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 1^er 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

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.

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.

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.