6. SQL intégré

Langage MySQL
Chapitre 1
LDD
1
Table des matières
1.
2.
3.
4.
5.
6.
7.
Introduction .................................................................................................................................................................................. 3
Langage de définition de données ................................................................................................................................................. 3
Langage de manipulation de données ........................................................................................................................................... 3
Langage de protections d'accès ..................................................................................................................................................... 3
Langage de contrôle de transaction ............................................................................................................................................... 4
SQL intégré................................................................................................................................................................................... 4
Création d'une table (CREATE) ............................................................................................................................................. 6
7.1.
*create_definition:.............................................................................................................................................................. 6
7.2.
Les identificateurs MySQL de col_name ........................................................................................................................... 6
7.2.1.
Règles générales ...................................................................................................................................................... 6
les mots-clés SQL sont les suivants : ............................................................................................................................................ 7
7.2.2.
Tables et colonnes (champs) .................................................................................................................................... 8
7.3.
Types de données de col_name .......................................................................................................................................... 8
7.3.1.
Strings – chaîne de caractères : ................................................................................................................................ 9
7.3.2.
Attributs à option (dans le tableau suivant).............................................................................................................. 9
7.3.3.
Paramètres à option (dans le tableau suivant) .......................................................................................................... 9
7.3.4.
La valeur NULL ...................................................................................................................................................... 9
7.3.5.
Tableau récapitulatif des types ................................................................................................................................. 9
8.
Contraintes d'intégrités .......................................................................................................................................................... 11
8.2.
Syntaxe ............................................................................................................................................................................. 11
8.3.
NOT NULL ou NULL : ................................................................................................................................................ 12
8.4.
UNIQUE : .................................................................................................................................................................... 12
8.5.
PRIMARY KEY : ........................................................................................................................................................ 12
8.6.
REFERENCES table [(colonne)] [ON DELETE CASCADE] : ................................................................ 13
8.7.
CHECK (condition) : .......................................................................................................................................... 13
8.8.
DEFAULT valeur : ................................................................................................................................................. 13
8.9.
ON DELETE CASCADE : .......................................................................................................................................... 13
8.10.
ON DELETE SET NULL : .................................................................................................................................. 13
8.11.
EXEMPLE .................................................................................................................................................................. 13
9.
DESCRIBE ...................................................................................................................................................................... 14
9.1.
Syntaxe de DESCRIBE .................................................................................................................................................... 15
10.
détruire une table (DROP TABLE) ................................................................................................................................. 16
10.1.
Syntaxe de DROP TABLE ............................................................................................................................. 16
11.
renommer une table (RENAME TABLE) ......................................................................................................................... 16
11.1.
Syntaxe de RENAME TABLE ..................................................................................................................................... 16
Modifier une table ALTER TABLE ..................................................................................................................................................... 17
12.
ANALYZE TABLE ..................................................................................................................................................... 21
12.1.
Syntaxe de ANALYZE TABLE ................................................................................................................................... 21
13.
TRUNCATE ................................................................................................................................................................. 21
13.1.
Description ..................................................................................................................................................... 21
13.2.
Syntaxe ....................................................................................................................................................................... 22
13.3.
Utilisation ................................................................................................................................................................... 22
13.4.
Autorisations ............................................................................................................................................................... 22
2
1. INTRODUCTION
Les instructions SQL sont regroupées en catégories en fonction de leur utilité et des entités
manipulées. Nous pouvons distinguer cinq catégories, qui permettent :
1. la définition des éléments d'une base de données (tables, colonnes, clefs, index,
contraintes, ...),
2. la manipulation des données (insertion, suppression, modification, extraction, ...),
3. la gestion des droits d'accès aux données (acquisition et révocation des droits),
4. la gestion des transactions,
5. et enfin le SQL intégré.
2. LANGAGE DE DEFINITION DE DONNEES
Le langage de définition de données (LDD, ou Data Definition Language, soit DDL en anglais)
est un langage orienté au niveau de la structure de la base de données. Le LDD permet de créer,
modifier, supprimer des objets. Il permet également de définir le domaine des données (nombre,
chaîne de caractères, date, booléen, ...) et d'ajouter des contraintes de valeur sur les données. Il
permet enfin d'autoriser ou d'interdire l'accès aux données et d'activer ou de désactiver l'audit
pour un utilisateur donné.
Les instructions du LDD sont : CREATE, ALTER, DROP, AUDIT, NOAUDIT, ANALYZE,
RENAME, TRUNCATE.
3. LANGAGE DE MANIPULATION DE DONNEES
Le langage de manipulation de données (LMD, ou Data Manipulation Language, soit DML en
anglais) est l'ensemble des commandes concernant la manipulation des données dans une base de
données. Le LMD permet l'ajout, la suppression et la modification de lignes, la visualisation du
contenu des tables et leur verrouillage.
Les instructions du LMD sont : INSERT, UPDATE, DELETE, SELECT, EXPLAIN, PLAN,
LOCK TABLE.
Ces éléments doivent être validés par une transaction pour qu'ils soient pris en compte.
4. LANGAGE DE PROTECTIONS D'ACCES
Le langage de protections d'accès (ou Data Control Language, soit DCL en anglais) s'occupe
de gérer les droits d'accès aux tables.
Les instructions du DCL sont : GRANT, REVOKE.
3
5. LANGAGE DE CONTROLE DE TRANSACTION
Le langage de contrôle de transaction (ou Transaction Control Language, soit TCL en anglais)
gère les modifications faites par le LMD, c'est-à-dire les caractéristiques des transactions et la
validation et l'annulation des modifications.
Les instructions du TCL sont : COMMIT, SAVEPOINT, ROLLBACK, SET TRANSACTION
6. SQL INTEGRE
Le SQL intégré (Embedded SQL) permet d'utiliser SQL dans un langage de troisième génération
(C, Java, Cobol, php, etc.) :




déclaration d'objets ou d'instructions ;
exécution d'instructions ;
gestion des variables et des curseurs ;
traitement des erreurs.
Les instructions du SQL intégré sont : DECLARE, TYPE, DESCRIBE, VAR, CONNECT,
PREPARE, EXECUTE, OPEN, FETCH, CLOSE, WHENEVER.
4
Définition de données (LDD)
Les instructions du LDD sont : CREATE, ALTER, DROP, ANALYZE, RENAME,
TRUNCATE.
On va s'appuyer sur l'exemple du cinéma :
selon les règles du passage du MCD au MLD on a les tables suivantes :
selon la règle I :
SALLE_CINEMA( nom_salle , adresse )
FILM ( visa , titre, realisateur , année_sortie )
SPECTATEUR ( numero, nom , prenom , adresse , date_naissance , cat_prof )
la règle II
APPRECIATION ( visa , numero , impression )
SALLE_CINEMA( nom_salle , adresse , visa)
en définitif on aura 4 tables pour le cinéma :
5
FILM ( visa , titre, realisateur , année_sortie )
SPECTATEUR ( numero, nom , prenom , adresse , date_naissance , cat_prof )
APPRECIATION ( visa , numero , impression )
SALLE_CINEMA( nom_salle , adresse , visa)
7. CREATION D'UNE TABLE (CREATE)
La création d'une table implique:




lui donner un nom (dans une base de données)
définir des colonnes (attributs ) : nom , type, taille, valeurs par défaut, ...
ajouter des contraintes pour les colonnes si nécessaire,
donner éventuellement des permissions (grants)
dans ce qui suit : les indications entre crochet sont facultatifs.
les commandes Mysql peuvent être écrites en majuscules ou minuscules.
la syntaxe (voir plus loin pour les explications):
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition*,...)
[table_options] [select_statement]
7.1.
*create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] KEY(index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or CHECK (expr)
7.2.
Les identificateurs MySQL de col_name
7.2.1. Règles générales

utilisés pour: noms de bases de données , tables, colonnes, etc.
6





Taille: mots de 30 caractères max;
Caractères autorisés: lettres, chiffres, #, $, _ (le premier char doit être une lettre) ;
pas de distinction entre minuscules et majuscules;
(sauf pour les bases de données et tables dans MySQL sous Unix !!)
PAS d'accents !
PAS de mots clefs (SELECT, WHERE, .....) voir la liste ci-dessous :
les mots-clés SQL sont les suivants :
Mot réservé
add
any
begin
bit
call
cast
check
commit
continue
cube
cursor
dec
delete
do
else
endif
exec
externlogin
for
from
group
if
inner
inserting
integer
iq
Mot réservé
all
as
between
bottom
capability
char
checkpoint
connect
convert
current
date
decimal
deleting
double
elseif
escape
execute
fetch
force
full
having
in
inout
install
integrated
is
Mot réservé
alter
asc
bigint
break
cascade
char_convert
close
constraint
create
current_timestamp
dbspace
declare
desc
drop
encrypted
except
existing
first
foreign
goto
holdlock
index
insensitive
instead
intersect
isolation
Mot réservé
and
backup
binary
by
case
character
comment
contains
cross
current_user
deallocate
default
distinct
dynamic
end
exception
exists
float
forward
grant
identified
index_lparen
insert
int
into
join
7
key
lock
membership
natural
not
of
option
others
passthrough
print
publication
reference
remove
restore
right
savepoint
session
smallint
start
synchronize
then
to
truncate
unique
updating
values
varying
when
with
within
lateral
login
message
new
notify
off
options
out
precision
privileges
raiserror
references
rename
restrict
rollback
scroll
set
some
stop
syntax_error
time
top
tsequal
unknown
user
varbinary
view
where
with_cube
work
left
long
mode
no
null
on
or
outer
prepare
proc
readtext
release
reorganize
return
rollup
select
setuser
sqlcode
subtrans
table
timestamp
tran
unbounded
unsigned
using
varchar
wait
while
with_lparen
writetext
like
match
modify
noholdlock
numeric
open
order
over
primary
procedure
real
remote
resource
revoke
save
sensitive
share
sqlstate
subtransaction
temporary
tinyint
trigger
union
update
validate
variable
waitfor
window
with_rollup
7.2.2. Tables et colonnes (champs)



On peut utiliser le même nom de colonne dans plusieurs tables;
Les données dans une colonne doivent être toutes du même type;
Nom complet d'une colonne: base_de_données.table.colonne
7.3.
Types de données de col_name
MySQL implémente la plupart des données SQL (lire le manuel)
8
7.3.1. Strings – chaîne de caractères :


délimitateurs: '....' ou " ....."
Il faut quoter les caractères spéciaux avec le \:
\n (newline), \r (CR), \t = (tab), \', \", \\, \%, \_
7.3.2. Attributs à option (dans le tableau suivant)

UNSIGNED (pour les entiers): nombres positives seulement

ZEROFILL (tous les nombres): retourne des 0, par ex. 0004)
7.3.3. Paramètres à option (dans le tableau suivant)


M : taille de display maximal
D (nombres flottants): chiffres après la virgule
7.3.4. La valeur NULL

Une colonne vide contient NULL (qui signifie "vide", et PAS zéro ou "" !!)
7.3.5. Tableau récapitulatif des types
Type
explication
range
exemple
NOMBRES
TinyInt(2)
TinyInt[(M)][UNSIGNED]
entier minuscule
[ZEROFILL]
-128 à 127 (0 à 255)
SmallInT[(M)]...
petit entier
-32768 à 32767 (0 à 64K)
20001
MediumINT[(M)]...
entier moyen
-8388608 to 8388607
-234567
INT[(M)] ...
entier
-2147483648 to 2147483647
BigINT[(M)]...
gros entier
63bits
FLOAT(precision)
nombre flottant
FLOAT[(M,D)]...
nombre flottant
9
-3.402823466E+38
9
to -1.175494351E-38
DOUBLE[(M,D)]...
grand nombre flottant
DATES
DATE
date
DateTime
YYYY-MM-DD
3000-12-31
YYYY-MM-DD HH:MM:SS
TimeStamp[(M)]
TIME
YEAR
Chaînes de caractères (strings)
M = 1 à 225 chars
Char(M) [binary]
String de longeur fixe
case insensible (sauf binary)
VarChar(M)[binary]
String variable
M = 1 à 225 chars
char(4)
'ab '
login(8)[binar
y]
schneiDe
Texte (blobs)
TINYBLOB
petit texts
255 chars
TINYTEXT
BLOB
65535 chars
TEXT
MEDIUMBLOB
16777215 chars
MEDIUMTEXT
LONGBLOB
grand text
4294967295 chars
10
LONGTEXT
Enumération
Enum('val1', 'val2',...)
un string parmi la liste
65535 distinct values
ou NULL
'toto'
Set('val1', 'val2', ...)
zéro ou plusieurs
strings parmi la liste
('toto', 'blurp')
64 members
Voici un exemple simple sans aucune contraintes :
CREATE TABLE SALLE_CINEMA ( nom_salle varchar(20) , adresse varchar(250) ,
visa int(5) ) Myisam;
CREATE TABLE FILM ( visa INT(5) , titre VARCHAR(20) , realisateur VARCHAR(20) ,
année_sortie YEAR ) ;
CREATE TABLE SPECTATEUR ( numero INT(6) , nom VARCHAR(30), prenom
VARCHAR(30), adresse VARCHAR(250) , date_naissance DATE , cat_prof CHAR(5));
Create TABLE APPRECIATION ( visa INT(5), numero INT(6) , impression ENUM('bon',
'moyen', 'mediocre' ) ;
8. CONTRAINTES D'INTEGRITES
8.1.1.1.
les contraintes programment les règles de la gestion des colonnes.
la contrainte est là pour éviter les erreurs par exemple, au moment de la saisie: exemple une note
doit être comprise entre o et 100.
les contraintes peuvent être déclarées en ligne en même temps que la déclaration de la colonne;
ou après la déclaration de la colonne.
8.2.
Syntaxe
A la création d'une table, les contraintes d'intégrité se déclarent de la façon suivante :
CREATE TABLE name_table (
name_col_1 type_1 [CONSTRAINT name_1_1] contrainte_de_colonne_1_1
11
[CONSTRAINT name_1_2] contrainte_de_colonne_1_2
...
...
[CONSTRAINT name_1_m] contrainte_de_colonne_2_m,
name_col_2 type_2 [CONSTRAINT name_2_1] contrainte_de_colonne_2_1
[CONSTRAINT name_2_2] contrainte_de_colonne_2_2
...
...
[CONSTRAINT name_2_m] contrainte_de_colonne_2_m,
...
name_col_n type_n [CONSTRAINT name_n_1] contrainte_de_colonne_n_1
[CONSTRAINT name_n_2] contrainte_de_colonne_n_2
...
...
[CONSTRAINT name_n_m] contrainte_de_colonne_n_m,
[CONSTRAINT name_1] contrainte_de_table_1,
[CONSTRAINT name_2] contrainte_de_table_2,
...
...
[CONSTRAINT name_p] contrainte_de_table_p
)
il faut prendre l'habitude de nommer les contraintes, on prendra les conventions suivantes :
 les clés primaires auront les lettres pk_nomCléPrimaire_nomTable
 les clés secondaires auront les lettres fk_nomCléEtrangère_tableSource_tableCible
 pour une vérification ck_
 pour une unicité un_
On peut aussi ajouter des indexs
 Les indexes améliorent la performance des opérations SELECT
 Chaque table peut avoir 16 indexes (max.)
 On peut indexer toutes les colonnes (SAUF blob et text), mais les colonnes doivent être
déclarées non NULL !!
 On peut limiter l'indexage des CHAR et VARCHAR à qq caractères
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type]
ON tbl_name (col_name [(length)] [ASC | DESC], ...)
Les différentes contraintes de colonne que l'on peut déclarer sont les suivantes :
8.3.
NOT NULL ou NULL :
Interdit (NOT NULL) ou autorise (NULL) l'insertion de valeur NULL pour cet attribut.
8.4.
UNIQUE :
Désigne l'attribut comme clé secondaire de la table. Deux n-uplets ne peuvent recevoir des
valeurs identiques pour cet attribut, mais l'insertion de valeur NULL est toutefois autorisée.
Cette contrainte peut apparaître plusieurs fois dans l'instruction.
8.5.
PRIMARY KEY :
Désigne l'attribut comme clé primaire de la table. La clé primaire étant unique, cette
contrainte ne peut apparaître qu'une seule fois dans l'instruction. La définition d'une clé
12
primaire composée se fait par l'intermédiaire d'une contrainte de table. En fait, la contrainte
PRIMARY KEY est totalement équivalente à la contraite UNIQUE NOT NULL.
8.6. REFERENCES table [(colonne)] [ON
DELETE CASCADE] :
Contrainte d'intégrité référentielle pour l'attribut de la table en cours de définition. Les
valeurs prises par cet attribut doivent exister dans l'attribut colonne qui possède une
contrainte PRIMARY KEY ou UNIQUE dans la table table. En l'absence de précision
d'attribut colonne, l'attribut retenu est celui correspondant à la clé primaire de la table
table spécifiée.
8.7.
CHECK (condition) :
Vérifie lors de l'insertion de n-uplets que l'attribut réalise la condition condition.
8.8.
DEFAULT valeur :
Permet de spécifier la valeur par défaut de l'attribut.
8.9.
Complément sur les contraintes
8.9.1. ON DELETE CASCADE :
Demande la suppression des n-uplets dépendants, dans la table en cours de définition,
quand le n-uplet contenant la clé primaire référencée est supprimé dans la table maître.
8.9.2. ON DELETE SET NULL :
Demande la mise à NULL des attributs constituant la clé étrangère qui font référence au nuplet supprimé dans la table maître.
La suppression d'un n-uplet dans la table maître pourra être impossible s'il existe des n-uplets
dans d'autres tables référençant cette valeur de clé primaire et ne spécifiant pas l'une de ces deux
options.
Voici un exemple simple avec les contraintes d'intégrités :
8.10. EXEMPLE
CREATE TABLE FILM (
visa INT(5) ,
titre VARCHAR(20) ,
realisateur VARCHAR(20) ,
13
année_sortie YEAR
CONSTRAINT pk_FILM PRIMARY KEY (visa)) ENGINE=InnoDB DEFAULT
CHARSET=latin1;
CREATE TABLE SALLE_CINEMA ( nom_salle varchar(20) ,
adresse varchar(250) NOT NULL
visa int(5) ,
CONSTRAINT pk_SALLE_CINEMA PRIMARY KEY (nom_salle),
CONSTRAINT fk_SALLE_CINEMA_FILM_visa FOREIGN KEY (visa)
REFERENCES FILM(visa) )ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE SPECTATEUR ( numero INT(6) ,
nom VARCHAR(30),
prenom VARCHAR(30),
adresse VARCHAR(250) ,
date_naissance DATE ,
cat_prof ENUM ('1', '2', '3', '4', '5'),
CONSTRAINT pk_FILM PRIMARY KEY (numero)) ENGINE=InnoDB DEFAULT
CHARSET=latin1;
CREATE TABLE APPRECIATION ( visa INT(5),
numero INT(6) ,
impression ENUM('bon', 'moyen', 'mediocre' ),
CONSTRAINT pk_APPRECIATION PRIMARY KEY (visa, numero),
INDEX (visa),
CONSTRAINT fk_APPRECIATION_SPECTATEUR_numero FOREIGN KEY (numero),
CONSTRAINT fk_APPRECIATION_FILM_visa FOREIGN KEY (visa)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
9. DESCRIBE
14
9.1.
Syntaxe de DESCRIBE
DESCRIBE fournit des informations à propos des colonnes de la table. DESCRIBE est un
raccourci de SHOW COLUMNS FROM.
{DESCRIBE | DESC} nom_de_table [nom_de_colonne | wild
nom_de_colonne peut être le nom d'une colonne ou une chaîne contenant les caractères
spéciaux SQL ‘%’ et ‘_’. Il n'est pas nécessaire de placer la chaîne entre guillemets, hormis s'il y
a des espaces ou d'autres caractères spéciaux.
9.2.
Exemple de describe
mysql> DESCRIBE city;
+------------+----------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+------------+----------+------+-----+---------+----------------+
| Id
| int(11) |
| PRI | NULL
| auto_increment |
| Name
| char(35) |
|
|
|
|
| Country
| char(3) |
| UNI |
|
|
| District
| char(20) | YES | MUL |
|
|
| Population | int(11) |
|
| 0
|
|
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
Null indique si la valeur NULL peut être stockée dans la colonne. YES indique que c'est le cas.
Key indique si un champ est indexé. La valeur PRI indique que le champ fait partie de la clé
primaire de la table.
UNI indique que le champ fait partie d'un index UNIQUE.
La valeur MUL indique que plusieurs occurences d'une valeur sont autorisées dans le champ.
Un champ peut être désigné comme MUL même si l'index UNIQUE est utilisé, si les valeurs NULL
sont autorisées, car une colonne UNIQUE peut contenir plusieurs valeurs NULL si la colonne est
déclarée comme NOT NULL. Une autre cause pour MUL sur une colonne UNIQUE est lorsque
deux colonnes forment un couple UNIQUE : même si la combinaison des deux colonnes est
toujours unique, chaque colonne peut contenir des valeurs multiples. Notez que dans un index
composé, seul le champ de gauche aura une entrée dans la colonne Key.
Default indique la valeur par défaut assignée à ce champ.
Extra indique des informations supplémentaires, disponibles sur le champ. Dans notre exemple,
la colonne Extra indique que la colonne Id porte l'attribut AUTO_INCREMENT.
Cette instruction est fournie pour une meilleure compatibilité avec Oracle.
L'instruction SHOW renvoie les mêmes informations.
15
10.
DETRUIRE UNE TABLE (DROP
TABLE)
10.1. Syntaxe de DROP TABLE
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
DROP TABLE supprime une ou plusieurs tables. Toutes les données et la structure de la tables
sont perdues, alors soyez prudents avec cette commande !
RESTRICT et CASCADE sont autorisés pour faciliter le port. Pour le moment, elles ne font rien.
Note : DROP TABLE va automatiquement valider les transactions actives (hormis si vous utilisez
la version 4.1 et le mot clé TEMPORARY).
L'option TEMPORARY est ignorée en 4.0. En 4.1, cette option fonctionne comme suit :

Détruit uniquement les tables temporaires.

Ne termine pas les transactions en cours.

Aucun droits d'accès n'est vérifié.
TEMPORARY est pour le moment ignoré; Dans un futur proche, il servira à s'assurer qu'on efface
vraiment une table temporaire.
10.2. Exemple de DROP
DROP table nom_table ;
11. RENOMMER UNE TABLE (RENAME
TABLE)
11.1. Syntaxe de RENAME TABLE
RENAME TABLE nom_de_table TO nouveau_nom_de_table[, nom_de_table2 TO
nouveau_nom_de_table2,...]
Le changement de nom se fait automtiquement ce qui signifie qu'aucun autre processus ne peut
16
accéder la table tant que l'opération est en cours. Cela rend possible de remplacer une vielle table
avec une table vide :
CREATE TABLE nouvelle_table (...);
RENAME TABLE ancienne_table TO backup_table, nouvelle_table TO ancienne_table;
L'opération s'effectue de gauche à droite ce qui signifie que si vous voulez échanger deux noms
de tables, vous devez :
RENAME TABLE ancienne_table
TO backup_table,
nouvelle_table
TO ancienne_table,
backup_table TO nouvelle_table;
Si les deux bases de données sont sur le même disque, vous pouvez renommer à travers les
bases :
RENAME TABLE bdd_courante.nom_de_table TO autre_bdd.nom_de_table;
Quand vous exécutez RENAME, vous ne pouvez avoir aucune transaction active ou une table
protégée en mode écriture. Vous devez avoir les privilèges ALTER et DROP sur l'ancienne table,
et les privilèges CREATE et INSERT sur la nouvelle.
Si MySQL rencontre des erreurs dans un renommage multiple, il remettra les noms changés à
leurs valeurs d'origine pour revenir à l'état d'origine.
RENAME TABLE a été ajouté à la version 3.23.23 de MySQL.
11.2. Exemple de rename
RENAME table toto LOLO ;
Désormais La table toto sera la table LOLO
à partir de maintenant.
12. MODIFIER UNE TABLE ALTER TABLE
ALTER TABLE vous permet de changer la structure d'une table existante. Par exemple, vous
pouvez ajouter ou supprimer des colonnes, des index, changer le type des colonnes existantes,
renommer ces colonnes, ou la table elle-même. Vous pouvez de même changer le commentaire
sur la table, ou le type de celle-ci
12.1. Syntaxe de ALTER TABLE
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD INDEX [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
17
| ADD [CONSTRAINT [symbol]]
UNIQUE [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| table_options
.

Pour utiliser ALTER TABLE, vous devez avoir les droits ALTER, INSERT, et CREATE
sur la table.

IGNORE est une extension MySQL pour ANSI SQL92. Cette option contrôle la façon
dont ALTER TABLE fonctionne s'il y a des duplications sur une clef unique de la
nouvelle table. Si IGNORE n'est pas spécifiée, la copie est annulée et la table originale est
restaurée. Si IGNORE est spécifiée, les lignes contenant les éléments doublons de la table
seront effacées, hormis la première, qui sera conservée.

Vous pouvez effectuer plusieurs opérations de ADD, ALTER, DROP, et CHANGE dans une
même commande ALTER TABLE.

MODIFY est une extension Oracle à ALTER TABLE.

Le mot optionnel COLUMN peut être ignoré.

Si vous utilisez ALTER TABLE nom_de_table RENAME TO nouveau_nom sans
autre option, MySQL va simplement renommer les fichiers qui correspondent à la table
nom_de_table. Il n'y a pas de création de fichier temporaire.
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
Si vous ne voulez changer que le type de la colonne, avec la clause CHANGE vous devrez
redonner le nom de la colonne. Par exemple :
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
18
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

Si vous utilisez les clauses CHANGE ou MODIFY pour réduire la taille d'une colonne qui
comportait un index sur une partie de la colonne (par exemple, si vous aviez un index sur
10 caractères d'une colonne de type VARCHAR), vous ne pouvez pas rendre la colonne
plus petite que le nombre de caractères indexés.

Si des colonnes sont effacées d'une table, ces colonnes sont aussi supprimés des index
dont elles font partie. Si toutes les colonnes qui forment un index sont effacées, l'index lui
même est supprimé.

Si une table ne comporte qu'une seule colonne, La colonne ne peut être supprimée. Si
vous voulez effacer la table, utilisez la commande DROP TABLE.

DROP PRIMARY KEY supprime la clef primaire. Si cette clef n'existe pas, cette
commande effacera le premier index UNIQUE de la table. (MySQL marque la première
clef UNIQUE en tant que PRIMARY KEY si aucune PRIMARY KEY n'a été spécifiée
explicitement.)
Si vous ajoutez un UNIQUE INDEX ou PRIMARY KEY à une table, c'est enregistré
avant les index non-UNIQUE pour que MySQL puisse détecter les valeurs dupliquées
aussi vite que possible.

ORDER BY vous permet de créer une nouvelle table tout en ordonnant les lignes par
défaut. Notez que cet ordre ne sera pas conservé après les prochaines insertions et
modifications. Dans certains cas, cela aide MySQL si les colonnes sont dans l'ordre dans
lequel vous allez trier les valeurs. Cette option n'est vraiment utile que si vous savez à
l'avance dans quel ordre vous effectuerez les tris : vous y gagnerez alors en performances.

Si vous utilisez ALTER TABLE sur une table MyISAM, tous les index non-uniques sont
créés par des opérations séparées. (comme dans REPAIR). Cela devrait rendre ALTER
TABLE plus rapide quand vous avez beaucoup d'index.
Depuis la version 4.0, la fonctionnalité ci-dessus peut être activée explicitement. ALTER
TABLE ... DISABLE KEYS force MySQL à ne plus mettre à jour les index nonuniques pour les tables au format MyISAM. ALTER TABLE ... ENABLE KEYS doit
alors être utilisé pour recréer les index manquants. Comme MySQL le fait avec un
algorithme spécial qui est plus rapide que le fait d'insérer les clefs une par une, désactiver
les clefs peut vous faire gagner en performances.

Les clauses FOREIGN KEY et REFERENCES sont supportées par le moteur de tables
InnoDB, qui implémente les clauses ADD [CONSTRAINT [symbol]] FOREIGN
KEY (...) REFERENCES ... (...).

Pour les autres moteurs de stockages, ces clauses sont lues mais ignorées. La clause
CHECK est analysée mais ignorée par tous les moteurs de stockage. La raison pour
accepter mais ignorer ces clauses est que cela renforce la compatibilité avec le code des
autres serveurs SQL, et qu'il est possible de créer des tables avec des références.

Depuis MySQL 4.0.13, InnoDB supporte l'utilisation de ALTER TABLE pour effacer
19
des clés étrangères :
ALTER TABLE yourtablename
DROP FOREIGN KEY fk_symbol

Depuis MySQL 4.1.2, si vous voulez changer dans toutes les colonnes de texte (CHAR,
VARCHAR, TEXT) le jeu de caractères, vous pouvez utiliser la commande suivante :
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
12.2. Exemple de alter table
Voilà un exemple qui montre quelques utilisations de ALTER TABLE. On commence par une
table t1 créée comme suit :
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
Pour renommer la table de t1 à t2 :
mysql> ALTER TABLE t1 RENAME t2;
Pour changer une colonne a de INTEGER en TINYINT NOT NULL (en laissant le même nom),
et pour changer une colonne b de CHAR(10) à CHAR(20) et la renommant de b en c :
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
Pour ajouter une nouvelle colonne TIMESTAMP nommée d :
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
Pour ajouter un index sur une colonne d, et rendre la colonne a la clef primaire :
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
Pour effacer la colonne c :
mysql> ALTER TABLE t2 DROP COLUMN c;
Pour ajouter une nouvelle colonne AUTO_INCREMENT nommée c :
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (c);
Notez que nous avons indexé c, car les colonnes AUTO_INCREMENT doivent être indexées, et
que nous définissons aussi c en tant que NOT NULL, car les colonnes indexées ne peuvent être
NULL.
Quand vous ajoutez une colonne AUTO_INCREMENT, les valeurs de la colonne sont remplies
automatiquement pour vous. Vous pouvez choisir la valeur de départ pour l'indexation en
utilisant SET INSERT_ID=# avant ALTER TABLE ou en utilisant l'option
AUTO_INCREMENT = # de la table.
20
Avec les tables de type MyISAM, si vous ne changez pas la colonne AUTO_INCREMENT, l'indice
d'auto-incrémentation ne sera pas affecté. Si vous effacez une colonne AUTO_INCREMENT puis
en ajoutez une autre, l'indexation recommencera à partir de 1.
13.
ANALYZE TABLE
13.1.
Syntaxe de ANALYZE TABLE
ANALYZE TABLE nom_de_table[,nom_de_table...]
Cette commande analyse et stocke la clé de distribution de la table. Durant l'analyse, la table est
verrouillée en lecture. Cette commande fonctionne avec les tables MyISAM et BDB.
C'est l'équivalent de la commande en ligne myisamchk -a.
MySQL utilise les clés de distribution pour décider dans quel ordre les tables doivent être
rassemblées lors des jointures qui ne s'effectuent pas sur une constante.
La commande retourne une table avec les colonnes suivantes :
Colonne
Table
Op
Msg_type
Msg_text
Valeur
Nom de la table
``analyze'' (toujours)
Un des status, error, info ou warning.
Le message.
Si la table n'a pas changé depuis la dernière commande ANALYZE TABLE, elle ne sera pas
analysée à nouveau.
13.2. Exemple de ANALYZE
Analyse BENARIB.toto ;
Analyse la table toto de la base BENARIB.
14.
TRUNCATE
14.1.
Description
Supprimer toutes les lignes d'une table, sans supprimer la définition de la table.
21
14.2. Syntaxe
TRUNCATE TABLE [ propriétaire.]nom_table
14.3. Utilisation
L'instruction TRUNCATE TABLE permet de supprimer toutes les lignes d'une table. C'est
l'équivalent d'une instruction DELETE sans clause WHERE, si ce n'est qu'aucun trigger n'est
déclenché et que les suppressions de lignes individuelles ne sont pas enregistrées dans le journal
de transactions.
Après une instruction TRUNCATE TABLE, la structure de la table et tous ses index continuent
d'exister et ce, jusqu'à l'exécution d'une instruction DROP TABLE. Les définitions et les
contraintes de colonne restent intactes, et les triggers et les autorisations restent en vigueur.
L'instruction TRUNCATE TABLE est enregistrée dans le journal de transactions en tant
qu'instruction unique, au même titre qu'une instruction de définition de données. Les suppressions
de lignes individuelles ne sont pas consignées dans le journal.
Si l'option TRUNCATE_WITH_AUTO_COMMIT est activée (ON, valeur par défaut) et que
tous les critères suivants sont satisfaits, une troncature rapide de la table est exécutée :

Il n'existe pas de clés étrangères dirigées vers la table ou en provenant.

L'instruction TRUNCATE TABLE n'est pas exécutée à l'intérieur d'un trigger.

L'instruction TRUNCATE TABLE n'est pas exécutée à l'intérieur d'une instruction
atomique.
En cas de troncature rapide, un COMMIT est exécuté avant et après l'opération.
14.4. Autorisations
Vous devez être le propriétaire de la table, ou posséder les droits , ou détenir les autorisations
ALTER sur la table.
Pour les tables de la base, l'instruction TRUNCATE TABLE exige un accès exclusif sur la table
car l'opération est atomique (soit toutes les lignes sont supprimées, soit aucune ne l'est). Cela
signifie que les curseurs précédemment ouverts et référençant la table tronquée doivent être
fermés et qu'un COMMIT ou un ROLLBACK doit être exécuté pour libérer la référence à la
table.
Pour les tables temporaires, chaque utilisateur possède sa propre copie de données et l'accès
exclusif n'est donc pas requis.
22