Easy-Micro


ESPACEMEMBRE SQL
Instructions DDL

Data Definition Language : modification d'une base ou d'une table

Liste des commandes DDL les plus utilisées

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

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.

Les Tables

Création d'une table (les paramètres entre crochets sont optionnels)
CREATE TABLE nomdelatable(
colonne1 type(taille) [NOT NULL] [AUTO_INCREMENT] [PRIMARY KEY],
colonne2 type(taille) [NOT NULL] [AUTO_INCREMENT] [PRIMARY KEY],
colonne3...);

Explication:
• "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.

• Contrainte d'unicité
On peut rajouter l'instruction : CONSTRAINT unique_nom_prenom UNIQUE (nom, prenom) en fin de création de table, 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 l'instruction : CHECK(VALUE BETWEEN 1 AND 6) à la suite d'une déclaration de colonne, comme "notes(INT)", ce qui signifie "qu'une note est obligatoirement contenue entre 1 et 6"

Renommer une table (attention: opération atomique!)
RENAME TABLE matable1 TO newmatable1;

Modification d'une table (les paramètres entre crochets sont optionnels)
ALTER [IGNORE] TABLE matable specification [,specification ...];

• 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
);

Suppression d'une table
DROP TABLE matable;

Suppression des données d'une table (vide la table)
TRUCATE TABLE matable;

SQL Les verrous
Lorsque nous voulons lire, mettre à jour ou supprimer des enregistrements d'une table, il est parfois bon de poser un verou 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;

SQL 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 faudras 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 longeur totale de la colonne (pour les types CHAR et VARCHARuniquement).

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

Créer une vue (une table virtuelle)
CREATE VIEW nom_vue(nom_colonne1, nom_colonne2...) AS SELECT nom_colonne1, nom_colonne2 FROM matable;


Suivez les aventures des skippers aquitains sur EasyVoile.com
Flux RSS Easy-Micro - Abonnez-vous Easy-Micro [ François Bisch ]
Jeudi 20 juin 2019 - Semaine 25 - Saint Silvère (Demain: Saint Rodolphe)
Aujourd'hui, c'est l'anniversaire de Nicole Kidman (1967-52 ans), actrice australienne et américaine.
Haut de page Easy-Micro