Création d’une base de données

Pour créer une base de données en SQL, on utilise la commande CREATE DATABASE suivi du nom de la base de données. La syntaxe donne :

CREATE DATABASE nom_de_base_de_données ;

Création d’une base de données de nom customer.

CREATE DATABASE customer ;

Les utilisateurs de MySQL on deux options :

Soit utiliser la page d’administration web phpmyadmin et là on peut passer par l’onglet Bases de données pour créer la base de données avec des tables dans Structure ou écrire des scripts SQL dans l’onglet SQL ;

Soit utiliser la console MySQL.

Les méthodes pratiques de conception d’une base de données

Pour éviter les problèmes dans l’administration et la gestion des données, il est recommandé:

D’éviter l’usage des majuscules dans la nomination de la base de données, des tables et des attributs (colonnes) ;

D’éviter les accents pour les noms des tables et des attributs

D’éviter les espaces entre les noms composés, à priori on utilise l’endoscope (la barre de 8) ; par exemple pour un attribut de nom numéro de téléphone, on écrit numero_de_telephone.

D’éviter les noms abrégés. Il est préférable d’écrire les noms en entier pour faciliter une bonne lecture de tous ;

D’éviter d’utiliser les noms réservés comme SQL, bases, tables, colonnes, type, text, date, … ;

Si on utilise le singulier pour un attribut, il faudrait utiliser le singulier pour tous les autres attributs et si c’est le pluriel qui est utilisé pour un attribut, les autres attributs aussi peuvent être au pluriel ;

 

Création d’une table

Avant de créer une table, on crée d’abord la base de données. C’est une base de données qui peut contenir une table. Une table est en quelque sorte un objet d’une base de données.

Pour créer une table, on utilise la commande CREATE TABLE suivi du nom de la table. La syntaxe donne :

CREATE TABLE nom_table ;

Une table comporte une ou plusieurs colonnes. Pour définir une colonne dans une table, on donne son nom et on ajoute ses contraintes : le type de données SQL qui doit être nul (NULL) ou non nul (NOT NULL), préciser PRIMARY KEY si l’attribut est une clé primaire). On peut ajouter d’autres contraintes selon la structure de la table.

Il existe 3 types de contraintes qu’on applique à la table avec ses champs à savoir les contraintes d’intégrité de domaine, les contraintes d’intégrité d’entité ou de table et les contraintes d’intégrité de référence.

Les contraintes d’intégrité sont appliquées aux colonnes (attributs) et peuvent se définir comme étant des règles de contrôle de données dans une base de données. Une contrainte est comme une règle qu’on ne peut pas violer. Pour créer la table student, on va utiliser le script SQL ci-dessous pour voir comment s’appliquent les contraintes d’intégrité de domaine aux attributs de cette table.

Dans le script SQL, on sépare chaque élément par une virgule. La syntaxe donne :

CREATE TABLE nom_table (

Colonne1 contrainte_colonne1,

Colonne1 contrainte_colonne1,

Colonne1 contrainte_colonne1,

.                 .                 .

Colonne(n) contrainte_colonne(n),

PRIMARY KEY (colonne_clé_primaire)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

On a choisit le moteur de stockage InnoDB

Demo

Création de la table student.

CREATE TABLE student (

id_student INT(10) NOT NULL AUTO_INCREMENT,

last_name VARCHAR(40),

first_name VARCHAR(40),

average FLOAT NOT NULL,

id_activity INT(10) NOT NULL,

address VARCHAR(30),

FOREIGN KEY(id_activity) REFERENCES subjects(id_activity),

PRIMARY KEY(id_student)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Remarque : On peut ajouter NOT NULL à tous les attributs de la table par exemple comme :

CREATE TABLE student (

id_student INT(10) NOT NULL AUTO_INCREMENT,

last_name VARCHAR(40) NOT NULL,

first_name VARCHAR(40) NOT NULL,

average FLOAT NOT NULL,

id_activity INT(10) NOT NULL,

address VARCHAR(30) NOT NULL,

FOREIGN KEY(id_activity) REFERENCES subjects(id_activity),

PRIMARY KEY(id_student)

) ;

Les contraintes d’intégrité de domaine : ce sont les types de données SQL qu’on a déjà parlé. Pour rappel il s’agit des INT, FLOAT, DOUBLE, CHAR, VARCHAR, etc.

La contrainte NOT NUL : sur un attribut  empêche l’enregistrement des lignes vides dans la colonne. Si on l’applique à tous les attributs d’une table, il n’y aura pas des valeurs nulles dans la table.

La contrainte  NUL : sur un attribut  permet l’enregistrement des lignes vides dans la colonne. Son action sur tous les attributs permet de faire des enregistrements même complets ou non complets.

La contrainte de clé primaire: PRIMARY KEY

Appelée contraintes d’intégrité d’entité, elle définit un attribut comme la clé primaire d’une table. Dans une table, il ne peut exister qu’une et une seule clé primaire. On peut aussi ajouter à une clé primaire AUTO_INCREMENT qui veut dire ajout passage automatique du numéro suivant à chaque nouvel enregistrement d’une ligne. La clé primaire est toujours NOT NULL.

Par exemple pour notre table student, l’attribut (colonne) id_student est définit comme clé primaire et s’auto-incrémente. Une clé primaire est toujours non nulle (NOT NULL), donc on aura

CREATE TABLE student (

.     .       .,

id_student VARCHAR (40) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (id_student)

) ;

La contrainte UNIQUE : définit une interdiction pour deux ou plusieurs linges (tuples) d’avoir les mêmes valeurs pour l’ensemble des attributs de la table. UNIQUE doit forcément définir avec NOT NULL.

Par exemple pour la table student, on veut que chaque adresse soit unique, donc pour l’attribut adresse, on aura

CREATE TABLE student (

.   .   .,

address VARCHAR (30) UNIQUE,

.       .      . );

La contrainte DEFAULT : Il définit une valeur par défaut qui existe déjà pour un attribut en cas d’absence d’une valeur pendant les insertions des données. Elle est très utilisée avec des fonctions SQL comme CURRENT_DATE (DEFAULT CURRENT_DATE), CURRENT_TIME (DEFAULT CURRENT_TIME), CURRENT_USER (DEFAULT CURRENT_USER), SESSION_USER (DEFAULT SESSION_USER), … .

Si on veut par exemple qu’en cas d’absence de valeur (d’adresse) pour l’attribut address, que le système stocke une adresse par défaut comme 432 Park Ave.

CREATE TABLE student (

.     .       .,

address CHAR (25)  DEFAULT ‘432 Park Ave’,

.     .       .,

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

La contrainte CHEC : limite les valeurs qui seront acceptées pour un attribut donné dans une table.

On peut par exemple restreindre les valeurs de la colonne (attribut) average en ce sens que l’on puisse stocker que des moyennes comprises entre 10 et 19. On aura

CREATE TABLE student (

.     .       .,

average FLOAT  CHEC (average > 10 AND average < 19),

.     .       .,

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

La contrainte de clé étrangère : FOREIGN KEY

Appelée contraintes d’intégrité de référence : elle garantit que les valeurs de chaque ligne de la table référençant existent dans la table référencée : ainsi une ligne de la table référençant ne peut pas contenir un ensemble de valeurs qui n’existe pas dans la table référencée.

On a id_studend qui est une clé primaire de la table student et id_activity qui est une clé primaire de la table subject. La table student contient la clé primaire id_activity de la table subject. Dans la table student, id_activity est donc une clé étrangère. On aura dans la table student :

CREATE TABLE student (

.     .       .,

Id_activity INT FOREIGN KEY REFERENCES subject(id_activity),

.     .       .,

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Remarque : Une table dans un système de gestion de base de données est soumise aux contraintes d’intégrité d’entité, aux contraintes d’intégrité de domaine et aux contraintes d’intégrité de référence. La colonne est soumise aux mêmes contraintes que la table.

Remarque : Ces contraintes d’intégrité s’appliquent aux attributs pendant la création de la table. Elles se placent juste après les attributs (champs) dans la déclaration de la table.

 

Les Index

Un index

Représenté par INDEX ou KEY, l’index n’est pas une contrainte d’intégrité. Un index est une structure qui ordonne la liste des valeurs d’une colonne ou d’une table. Il a pour rôle d’optimiser les requêtes pour la recherche des données dans une base de données. Il apporte une importante performance dans les opérations SQL surtout la recherche des données dans un SGBD.

L’usage des index dans des tables est presque la même qu’un gros document contenant une partie index.  Pour faire une chercher rapide d’une partie précise du contenu du document, il suffit d’aller à la page d’index pour savoir où se trouve exactement la partie recherchée.

Pour une base de données qui contient peu de données, les index ne sont pas nécessaires par contre pour des bases de données de à quantité volumineuse de de données, les index jouent un très grand rôle et il est recommandé de les utiliser pour une lecture rapide. Pour les opérations de recherche des données, les SGBD commencent dans des index des bases de données au cas où ils ont été créés dans une base de données.

Création et suppression d’index

Création d’index

Créer un index sur une colonne ou une table ne modifie pas les données de la colonne ou de la table mais copie la colonne ou la table sur le disque et réorganise les données de cette colonne ou table dans un ordre donné pour faciliter les opérations de recherche. Les index créent des copies de tables ordonnées mais de structures différentes. Il faut reconnaitre qu’en posant un index sur une colonne, on l’ajoute une contrainte bien que l’index n’a pas été définit comme une contrainte d’intégrité.

On peut créer un index sur une table et aussi sur un ou sur plusieurs attributs (colonnes).

Il existe deux façons de création d’index

Ajout d’index pendant la création des tables

Un index porte toujours un nom. On soit écrire INDEX ou KEY suivit du nom d’inex. Supposons qu’on pose un index sur la colonne 1 de la table, on aura comme syntaxe :

CREATE TABLE nom_table (colonne1 contraint_colonne1, . . . INDEX nom_index (colonne1), colonne(n) contraint_colonne(n)) ;

Demo

Dans notre cas on pose un index sur la clé primaire de la table subject.

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;

Ajout d’index après la création des tables

Après avoir créé des tables dans une base de données, on peut ajouter des index avec l’option CREATE ou l’option ALTER

Option CREATE INDEX

Lorsqu’on finit de créer des tables, on peut ajouter des index sur ces tables. La syntaxe de la création donne :

CREATE INDEX nom_index ON nom_table (nom_colonne) ;

Dans le cas de plusieurs colonnes, il suffit d’ajouter les noms de colonnes.

Demo

Création d’index sur la colonne name de la table subject.

CREATE INDEX beta ON subject (name) ;

 

Option ALTER INDEX

Que l’on utilise CREATE ou ALTER, on obtient toujours les mêmes résultats. La syntaxe donne :

ALTER TABLE nom_table ADD INDEX nom_index (nom_colonne) ;

Ou

ALTER TABLE nom_table ADD INDEX nom_index (nom_colonne1, nom_colonne2) ; dans le cas de deux colonnes.

Demo

Création d’index sur la colonne id_activity de la table subject.

ALTER TABLE subject ADD INDEX beta (id_activity) ;

Les types d’index (UNIQUE, FULLTEXT et SPATIAL)

Index UNIQUE 

Un index unique (UNIQUE INDEX) sur une table impose que toutes les données (valeurs) à saisir pour les différents attributs de la table doivent êtres toutes différentes.

Un index unique sur un attribut impose que toutes les lignes de cet attributs doivent êtres des valeurs toutes différentes.

Exemple : Si on veut créer un index unique de nom beta sur les colonnes id_activity et name de la table subjects.

CREATE UNIQUE INDEX beta ON subject (id_activity, name) ;

Index FULLTEX

Un index FULLTEXT (INDEX FULLTEXT) est utilisé sur les attributs de type texte et chaines de caractères à savoir les types TEXT, CHAR et VARCHAR. Il permet une recherche rapide de texte ou de chaine de caractères.

Pour utiliser UNIQUE, FULLTEXT, SPATIAL, il suffit d’ajouter INDEX à la fin de chacun.

UNIQUE => UNIQUE INDEX ; FULLTEXT => FULLTEX UNIQUE ; SPATIAL => SPATIAL INDEX.

Les avantages des index

Ils permettent une accélération de recherche de données dans une base de données. Les index empêchent les duplications des données ou les doublons qui peuvent provenir des erreurs de saisie ou de mauvaise manipulation des informations contenues dans des bases de données. Les recherches se font en se basant sur les colonnes indexées. Les index améliorent les performances des bases de données.

Les inconvénients des index

Les index créent de redondances sur le support de SGBD par exemple sur le disque de stockage. En augmentant le volume des données, cela entraine le ralentissent du système.

Ils ralentissent les opérations d’ajout, de modification et de suppression de données à cause de la mise à jour qui se fait à chaque d’exécution d’une opération.

Comment poser des index sur une table ?

On ne pose pas d’index sur n’importe quel attribut de la table. On pose des index ou on crée des index sur soit :

La colonne contenant la clé primaire

La colonne contenant la  clé étrangère

Les colonnes les plus utilisées pour la recherche

La colonne contenant la contrainte d’unicité

Suppression d’un index

Pour supprimer un index, on utilise la  commande DROP suivi du nom de l’index comme les tables et les bases de données. Au passage, les index sont non modifiables. Un index une fois créé ne peut plus être modifié. La solution est de le supprimer puis de recréer à nouveau.

DROP INDEX nom_index ;

Exemple DROP INDEX beta ;