Easy-Micro

ESPACEMEMBRE SQL
Instructions DDL
Data Definition Language : modification d'une base ou d'une table

Formation SQL par Easy-Micro Liste des commandes DDL les plus utilisées

  • CREATE DATABASE - création d'une nouvelle base de données
  • RENAME TABLE - renomme une table
  • ALTER TABLE - modifier une table
  • ALTER TABLE - modifier une table
  • DROP TABLE - supprimer une table
  • TRUNCATE TABLE - vide une table
  • CREATE INDEX - créer un index
  • DROP INDEX - supprimer un index

Formation SQL par Easy-Micro Bases de données

Création d'une bases de données 
CREATE DATABASE nomdelabase;

Suppression d'une bases de données 
DROP DATABASE nomdelabase;

Connexion à une bases de données 
CONNECT TO nomduserveur USER nomutilisateur;

Note: A la suite de cette commande, le mot de passe associé au nom d'utilisateur sera requis. Pour ce déconnecter, utilisez la commande DISCONNECT.

Formation SQL par Easy-Micro Création d'une table

CREATE TABLE nomdelatable(
colonne1 type(taille) [NOT NULL] [AUTO_INCREMENT] [PRIMARY KEY],
colonne2 type(taille) [NOT NULL] [AUTO_INCREMENT] [PRIMARY KEY],
colonne3...);
Les paramètres entre crochets sont optionnels
  • "colonnex" correspond au nom des différents champs de la table
  • "type" correspond aux différent types possible (numériques ou de chaînes)
  • "taille" indique la longueur ou le format de la colonne (le champ): [(M[,D])] [UNSIGNED] [ZEROFILL]
    - La valeur M optionnelle spécifie la taille max (255 par défaut).
    - La valeur D optionnelle spécifie le nombre de chiffre après la virgule.
    - L'attribut UNSIGNED pour accepter les nombres positifs uniquement.
    - L'attribut ZEROFILL spécifie le nombre de 0 à gauche d'un chiffre (ex:000050).
  • L'attribut NOT NULL signifie que toutes les lignes de la table doivent contenir une valeur pour cette colonne. Le champ ne peut pas être vide (sinon NULL).
  • L'attribut AUTO_INCREMENT - utilisé sur des colonnes de type entier - permet d'auto incrémenter une valeur unique si aucune saisie n'est réalisée. Les colonnes qui utilise cette option doivent être indexées. Cette option ne peut être utilisée qu'une seule fois par table. Elle permet de différencier de manière unique chacune des lignes.
  • L'attribut PRIMARY KEY spécifie que la colonne sert de clé primaire pour la table, c'est à dire que cette colonne sera prioritairement utilisée pour toutes les opérations sur les lignes. Les valeurs d'une telle colonne doivent être uniques. MySQL index automatiquement ce type de colonne.

Formation SQL par Easy-Micro Contraintes

◊ Contrainte d'unicité : On peut rajouter l'instruction en fin de création de table : 

CONSTRAINT unique_nom_prenom UNIQUE (nom, prenom)
ce qui signifie "ne peut pas avoir plusieurs fois une personne avec le même couple nom et prénom"

◊ Contrainte de validation
A chaque fois que l'on va ajouter un élément dans une colonne, une condition va se vérifier et l'enregistrement ne se fera qu'en cas de passage de la validation. Par exemple, on peut rajouter cette instruction à la suite d'une déclaration de colonne, comme "notes(INT)" 

CHECK(VALUE BETWEEN 1 AND 6)
ce qui signifie "qu'une note est obligatoirement contenue entre 1 et 6"

Formation SQL par Easy-Micro Renommer une table

RENAME TABLE matable1 TO newmatable1;
Attention: opération atomique!

Formation SQL par Easy-Micro Modification d'une table

ALTER [IGNORE] TABLE matable specification [,specification ...];
Les paramètres entre crochets sont optionnels

• L'attibut specification vaut une des options suivantes:

  • ADD [COLUMN] definition [FIRST | AFTER nomcolonne]
  • ADD INDEX [nomindex] (nomcolindex, ...)
  • ADD PRIMARY KEY (nomcolindex, ...)
  • ADD UNIQUE [nomindex] (nomcolindex, ...)
  • ADD FULLTEXT [nomindex] (nomcolindex, ...)
  • ALTER COLUMN nomcolonne [nomindex] (nomcolindex, ...)
  • CHANGE COLUMN oldnomcolonne newnomcolonn [FIRST | AFTER nomcolonne]
  • MODIFY [COLUMN] definition [FIRST | AFTER nomcolonne]
  • DROP [COLUMN] nomcolonne
  • RENAME [TO] newnomtable
  • ORDER BY col
  • Tableoptions

• L'attibut IGNORE contrôle le fonctionnement de la commande ALTER TABLE si une création d'index est tentée sur une colonne contenant des valeurs non uniques. Si IGNORE n'est pas spécifié, la commande ALTER TABLE est annulée.

Exemple de modification d'une table

- Ajouter une colonne
ALTER TABLE clients ADD datecree DATE AFTER idclient;
- Supprimer une colonne
ALTER TABLE clients DROP datecree;
- Modifier un type de colonne
ALTER TABLE clients MODIFY nom VARCHAR(50) NOT NULL;

Exemple de création de deux tables (clients et articles)

CREATE TABLE clients (
idclient SMALLINT(5) PRIMARY KEY NOT NULL,
nom CHAR(30),
prenom CHAR(30),
adresse CHAR(50),
codepostal INT(6),
ville CHAR(30)
);
CREATE TABLE articles (
codearticle CHAR(5) PRIMARY KEY NOT NULL,
nom CHAR(50),
prix FLOAT(6,2),
stock SMALLINT UNSIGNED,
idcategorie TINYINT UNSIGNED
);

Formation SQL par Easy-Micro Suppression d'une table

DROP TABLE matable;

Formation SQL par Easy-Micro Suppression des données d'une table

TRUNCATE TABLE matable;
Vide la table

Formation SQL par Easy-Micro Les verrous

Lorsque nous voulons lire, mettre à jour ou supprimer des enregistrements d'une table, il est parfois bon de poser un verrou sur cette table pour s'assurer qu'aucun autre utilisateur n'accède à la table.

Verrouiller une ou plusieurs tables (verouillée ici en lecture) 
LOCK TABLES articles READ;

Déverrouiller toutes les tables (toutes ou rien, pas le choix de table) 
UNLOCK TABLES;

Formation SQL par Easy-Micro Les INDEX

Lors d'une recherche, si la colonne faisant l'objet de la condition n'est pas indexée, MySQL parcourt tous les enregistrements de la table... Les index permettent de trouver plus rapidement les enregistrements répondant aux critères de recherche.

CREATE INDEX nom_index ON matable [ASC | DESC];

Mais si l'index permet de gagner en vitesse, il a le défaut de coûter du temps pour les autres types de requêtes (INSERT, UPDATE, DELETE) car il faut mettre les index à jour. Les index sont aussi consommateurs d'espace disque, MySQL créant un fichier par index. Ces fichiers portent l'extension .MYI.

Dans un exemple pécédent, nous avons recherché les articles qui comporte le mot clavier :

SELECT nom FROM articles WHERE nom LIKE '%clavier%';

La colonne nom de la table articles ne dispose pas d'index. Tant que nous disposons de peu d'articles, il y a peu d'infuence sur la vitesse, mais il en sera bien autrement dès que nous gérerons plus d'articles. Il faudra alors créer un index sur la colonne nom de la table articles.

Création d'un INDEX inom (2 commandes identiques) 
ALTER TABLE articles ADD INDEX inom(nom);
CREATE INDEX inom ON articles(nom);

Suppression de l'index inom 
DROP INDEX inom ON articles;

Afin d'optimiser la recherche et le poid du fichier index, nous pouvons créer des index composés d'une partie de la longueur totale de la colonne (pour les types CHAR et VARCHAR uniquement).

Création d'un index partiel inom sur les 5 premières lettres 
ALTER TABLE articles ADD INDEX inom(nom(5));

Formation SQL par Easy-Micro Créer une vue

Une vue est une table virtuelle

CREATE VIEW nom_vue(nom_colonne1, nom_colonne2...) AS SELECT nom_colonne1, nom_colonne2 FROM matable;
< Page précédente SQL Page suivante >