Administrez vos bases de données avec MySQL Par Chantal Gribaumont (Taguan)
Sommaire
2Sommaire ...........................................................................................................................................
7Partager ..............................................................................................................................................
9 Administrez vos bases de données avec MySQL ..............................................................................
9Quelques exemples d'applications ....................................................................................................................................
9Points abordés dans ce tutoriel .........................................................................................................................................
11Partie 1 : MySQL et les bases du langage SQL ................................................................................
11Introduction ......................................................................................................................................................................
11Concepts de base ......................................................................................................................................................................................................
11Base de données .......................................................................................................................................................................................................
11SGBD ........................................................................................................................................................................................................................
11SGBDR ......................................................................................................................................................................................................................
12Le langage SQL ........................................................................................................................................................................................................
13Présentation succincte de MySQL... .........................................................................................................................................................................
13Un peu d'histoire .......................................................................................................................................................................................................
13Mise en garde ............................................................................................................................................................................................................
13... et de ses concurrents ............................................................................................................................................................................................
13Oracle database ........................................................................................................................................................................................................
13PostgreSQL ...............................................................................................................................................................................................................
14MS Access .................................................................................................................................................................................................................
14SQLite .......................................................................................................................................................................................................................
14Organisation d'une base de données ........................................................................................................................................................................
15Installation de MySQL .....................................................................................................................................................
16Avant-propos .............................................................................................................................................................................................................
16Ligne de commande ..................................................................................................................................................................................................
16Interface graphique ...................................................................................................................................................................................................
17Pourquoi utiliser la ligne de commande ? .................................................................................................................................................................
17Installation du logiciel ................................................................................................................................................................................................
17Windows ....................................................................................................................................................................................................................
18Mac OS .....................................................................................................................................................................................................................
19Linux ..........................................................................................................................................................................................................................
20Connexion à MySQL .................................................................................................................................................................................................
20Connexion au client ...................................................................................................................................................................................................
22Déconnexion .............................................................................................................................................................................................................
22Syntaxe SQL et premières commandes ....................................................................................................................................................................
22"Hello World !" ...........................................................................................................................................................................................................
22Syntaxe .....................................................................................................................................................................................................................
24Un peu de math .........................................................................................................................................................................................................
24Utilisateur ..................................................................................................................................................................................................................
26Les types de données .....................................................................................................................................................
26Types numériques .....................................................................................................................................................................................................
26Nombres entiers ........................................................................................................................................................................................................
27Nombres décimaux ...................................................................................................................................................................................................
27Types alphanumériques ............................................................................................................................................................................................
27Chaînes de type texte ...............................................................................................................................................................................................
28Chaînes de type binaire ............................................................................................................................................................................................
29SET et ENUM ............................................................................................................................................................................................................
31Types temporels ........................................................................................................................................................................................................
31DATE, TIME et DATETIME ........................................................................................................................................................................................
33YEAR .........................................................................................................................................................................................................................
33TIMESTAMP ..............................................................................................................................................................................................................
33La date par défaut .....................................................................................................................................................................................................
33Création d'une base de données ....................................................................................................................................
34Avant-propos : conseils et conventions .....................................................................................................................................................................
34Conseils ....................................................................................................................................................................................................................
34Conventions ..............................................................................................................................................................................................................
35Mise en situation .......................................................................................................................................................................................................
35Création et suppression d'une base de données ......................................................................................................................................................
35Création .....................................................................................................................................................................................................................
35Suppression ..............................................................................................................................................................................................................
36Utilisation d'une base de données ............................................................................................................................................................................
37Création de tables ...........................................................................................................................................................
38Définition des colonnes .............................................................................................................................................................................................
38Type de colonne ........................................................................................................................................................................................................
38NULL or NOT NULL ? ................................................................................................................................................................................................
38Récapitulatif ..............................................................................................................................................................................................................
39Introduction aux clés primaires .................................................................................................................................................................................
39Identité .......................................................................................................................................................................................................................
39Clé primaire ...............................................................................................................................................................................................................
39Auto-incrémentation ..................................................................................................................................................................................................
40Les moteurs de tables ...............................................................................................................................................................................................
40 Préciser un moteur lors de la création de la table ....................................................................................................................................................
40Syntaxe de CREATE TABLE .....................................................................................................................................................................................
40Syntaxe .....................................................................................................................................................................................................................
42Application : création de Animal ................................................................................................................................................................................
2/414
www.siteduzero.com
42Vérifications ...............................................................................................................................................................................................................
42Suppression d'une table ............................................................................................................................................................................................
42Modification d'une table ...................................................................................................................................................
43Syntaxe de la requête ...............................................................................................................................................................................................
43Ajout et suppression d'une colonne ..........................................................................................................................................................................
43Ajout ..........................................................................................................................................................................................................................
44Suppression ..............................................................................................................................................................................................................
44Modification de colonne .............................................................................................................................................................................................
44Changement du nom de la colonne ..........................................................................................................................................................................
44Changement du type de données .............................................................................................................................................................................
45Insertion de données .......................................................................................................................................................
46Syntaxe de INSERT ..................................................................................................................................................................................................
46Insertion sans préciser les colonnes .........................................................................................................................................................................
47Insertion en précisant les colonnes ...........................................................................................................................................................................
47Insertion multiple .......................................................................................................................................................................................................
47Syntaxe alternative de MySQL ..................................................................................................................................................................................
48Utilisation de fichiers externes ..................................................................................................................................................................................
48Exécuter des commandes SQL à partir d'un fichier ..................................................................................................................................................
49Insérer des données à partir d'un fichier formaté ......................................................................................................................................................
50Remplissage de la base ............................................................................................................................................................................................
50Exécution de commandes SQL .................................................................................................................................................................................
51LOAD DATA INFILE ...................................................................................................................................................................................................
52Sélection de données ......................................................................................................................................................
53Syntaxe de SELECT .................................................................................................................................................................................................
53Sélectionner toutes les colonnes ..............................................................................................................................................................................
54La clause WHERE ....................................................................................................................................................................................................
54Les opérateurs de comparaison ................................................................................................................................................................................
54Combinaisons de critères ..........................................................................................................................................................................................
56Sélection complexe ...................................................................................................................................................................................................
57Le cas de NULL .........................................................................................................................................................................................................
58Tri des données .........................................................................................................................................................................................................
58Tri ascendant ou descendant ....................................................................................................................................................................................
59Trier sur plusieurs colonnes ......................................................................................................................................................................................
59Éliminer les doublons ................................................................................................................................................................................................
59Restreindre les résultats ...........................................................................................................................................................................................
59Syntaxe .....................................................................................................................................................................................................................
61Syntaxe alternative ....................................................................................................................................................................................................
61Élargir les possibilités de la clause WHERE ...................................................................................................................
62Recherche approximative .........................................................................................................................................................................................
63Sensibilité à la casse .................................................................................................................................................................................................
63Recherche dans les numériques ...............................................................................................................................................................................
64Recherche dans un intervalle ....................................................................................................................................................................................
64Set de critères ...........................................................................................................................................................................................................
65Suppression et modification de données ........................................................................................................................
66Sauvegarde d'une base de données .........................................................................................................................................................................
67Suppression ..............................................................................................................................................................................................................
67Modification ...............................................................................................................................................................................................................
68Partie 2 : Index, jointures et sous-requêtes .......................................................................................
69Index ................................................................................................................................................................................
69Etat actuelle de la base de données .........................................................................................................................................................................
70Qu'est-ce qu'un index ? .............................................................................................................................................................................................
72Intérêt des index ........................................................................................................................................................................................................
72Désavantages ...........................................................................................................................................................................................................
72Index sur plusieurs colonnes .....................................................................................................................................................................................
74Index sur des colonnes de type alphanumérique ......................................................................................................................................................
75Les différents types d'index .......................................................................................................................................................................................
75Index UNIQUE ...........................................................................................................................................................................................................
76Index FULLTEXT .......................................................................................................................................................................................................
76Création et suppression des index ............................................................................................................................................................................
76Ajout des index lors de la création de la table ...........................................................................................................................................................
78Ajout des index après création de la table ................................................................................................................................................................
79Complément pour la création d'un index UNIQUE - le cas des contraintes ..............................................................................................................
79Suppression d'un index .............................................................................................................................................................................................
80Recherches avec FULLTEXT ....................................................................................................................................................................................
80Comment fonctionne la recherche FULLTEXT ? .......................................................................................................................................................
81Les types de recherche .............................................................................................................................................................................................
89Clés primaires et étrangères ...........................................................................................................................................
89Clés primaires, le retour ............................................................................................................................................................................................
89Choix de la clé primaire .............................................................................................................................................................................................
90Création d'une clé primaire .......................................................................................................................................................................................
91Suppression de la clé primaire ..................................................................................................................................................................................
91Clés étrangères .........................................................................................................................................................................................................
92Création .....................................................................................................................................................................................................................
93Suppression d'une clé étrangère ...............................................................................................................................................................................
93Modification de notre base ........................................................................................................................................................................................
94La table Espece ........................................................................................................................................................................................................
95La table Animal ..........................................................................................................................................................................................................
97Jointures ..........................................................................................................................................................................
98Principe des jointures et notion d'alias ......................................................................................................................................................................
98Principe des jointures ................................................................................................................................................................................................
99Notion d'alias .............................................................................................................................................................................................................
Sommaire 3/414
www.siteduzero.com
100Jointure interne ........................................................................................................................................................................................................
101Syntaxe ...................................................................................................................................................................................................................
103Pourquoi "interne" ? ................................................................................................................................................................................................
104Jointure externe .......................................................................................................................................................................................................
104Jointures par la gauche ...........................................................................................................................................................................................
105Jointures par la droite ..............................................................................................................................................................................................
106Syntaxes alternatives ..............................................................................................................................................................................................
106Jointures avec USING .............................................................................................................................................................................................
106Jointures naturelles .................................................................................................................................................................................................
107Jointures sans JOIN ................................................................................................................................................................................................
108Exemples d'application et exercices .......................................................................................................................................................................
108A/ Commençons par des choses faciles .................................................................................................................................................................
109B/ Compliquons un peu les choses .........................................................................................................................................................................
110C/ Et maintenant, le test ultime ! ..............................................................................................................................................................................
112Sous-requêtes ...............................................................................................................................................................
113Sous-requêtes dans le FROM .................................................................................................................................................................................
114Les règles à respecter .............................................................................................................................................................................................
115Sous-requêtes dans les conditions ..........................................................................................................................................................................
115Comparaisons .........................................................................................................................................................................................................
118Conditions avec IN et NOT IN .................................................................................................................................................................................
119Conditions avec ANY, SOME et ALL ........................................................................................................................................................................
120Sous-requêtes corrélées .........................................................................................................................................................................................
122Jointures et sous-requêtes : modification de données .................................................................................................
123Insertion ...................................................................................................................................................................................................................
123Sous-requête pour l'insertion ..................................................................................................................................................................................
125Modification .............................................................................................................................................................................................................
125Utilisation des sous-requêtes ..................................................................................................................................................................................
127Modification avec jointure ........................................................................................................................................................................................
127Suppression ............................................................................................................................................................................................................
127Utilisation des sous-requêtes ..................................................................................................................................................................................
128Suppression avec jointure .......................................................................................................................................................................................
128Union de plusieurs requêtes .........................................................................................................................................
129Syntaxe ...................................................................................................................................................................................................................
129Les règles ................................................................................................................................................................................................................
131UNION ALL ..............................................................................................................................................................................................................
132LIMIT et ORDER BY ................................................................................................................................................................................................
132LIMIT .......................................................................................................................................................................................................................
134ORDER BY ..............................................................................................................................................................................................................
135Options des clés étrangères .........................................................................................................................................
136Option sur suppression des clés étrangères ...........................................................................................................................................................
136Petits rappels ..........................................................................................................................................................................................................
136Suppression d'une référence ..................................................................................................................................................................................
138Option sur modification des clés étrangères ...........................................................................................................................................................
139Utilisation de ces options dans notre base ..............................................................................................................................................................
139Modifications ...........................................................................................................................................................................................................
140Suppressions ..........................................................................................................................................................................................................
140Les requêtes ............................................................................................................................................................................................................
141Violation de contrainte d'unicité .....................................................................................................................................
142Ignorer les erreurs ...................................................................................................................................................................................................
142Insertion ...................................................................................................................................................................................................................
142Modification .............................................................................................................................................................................................................
143LOAD DATA INFILE .................................................................................................................................................................................................
143Remplacer l'ancienne ligne .....................................................................................................................................................................................
145Remplacement de plusieurs lignes .........................................................................................................................................................................
145LOAD DATA INFILE .................................................................................................................................................................................................
146Modifier l'ancienne ligne ..........................................................................................................................................................................................
146Syntaxe ...................................................................................................................................................................................................................
147Attention : plusieurs contraintes d'unicité sur la même table ..................................................................................................................................
147Partie 3 : Fonctions : nombres, chaînes et agrégats .......................................................................
148Rappels et introduction .................................................................................................................................................
148Etat actuel de la base de données ..........................................................................................................................................................................
150Rappels et manipulation simple de nombres ..........................................................................................................................................................
150Rappels ...................................................................................................................................................................................................................
151Combiner les données avec des opérations mathématiques .................................................................................................................................
153Définition d'une fonction ..........................................................................................................................................................................................
155Fonctions scalaires vs fonctions d'agrégation .........................................................................................................................................................
155Quelques fonctions générales .................................................................................................................................................................................
156Informations sur l'environnement actuel ..................................................................................................................................................................
156Informations sur la dernière requête .......................................................................................................................................................................
158Convertir le type de données ..................................................................................................................................................................................
160Fonctions scalaires .......................................................................................................................................................
160Manipulation de nombres ........................................................................................................................................................................................
160Arrondis ...................................................................................................................................................................................................................
161Exposants et racines ...............................................................................................................................................................................................
162Hasard .....................................................................................................................................................................................................................
163Divers ......................................................................................................................................................................................................................
163Manipulation de chaînes de caractères ...................................................................................................................................................................
163Longueur et comparaison .......................................................................................................................................................................................
164Retrait et ajout de caractères ..................................................................................................................................................................................
166Recherche et remplacement ...................................................................................................................................................................................
168Concaténation .........................................................................................................................................................................................................
Sommaire 4/414
www.siteduzero.com
168FIELD(), une fonction bien utile pour le tri ...............................................................................................................................................................
169Code ASCII ..............................................................................................................................................................................................................
169Exemples d'application et exercices .......................................................................................................................................................................
169On commence par du facile ....................................................................................................................................................................................
170Puis on corse un peu ..............................................................................................................................................................................................
172Fonctions d'agrégation ..................................................................................................................................................
172Fonctions statistiques ..............................................................................................................................................................................................
172Nombre de lignes ....................................................................................................................................................................................................
173Minimum et maximum .............................................................................................................................................................................................
173Somme et moyenne ................................................................................................................................................................................................
174Concaténation .........................................................................................................................................................................................................
174Principe ...................................................................................................................................................................................................................
174Syntaxe ...................................................................................................................................................................................................................
175Exemples ................................................................................................................................................................................................................
175Regroupement ...............................................................................................................................................................
176Regroupement sur un critère ...................................................................................................................................................................................
177Voir d'autres colonnes .............................................................................................................................................................................................
177Colonnes sélectionnées ..........................................................................................................................................................................................
179Tri des données .......................................................................................................................................................................................................
180Et les autres espèces ? ...........................................................................................................................................................................................
181Regroupement sur plusieurs critères ......................................................................................................................................................................
182Super-agrégats ........................................................................................................................................................................................................
185Conditions sur les fonctions d'agrégation ................................................................................................................................................................
186Optimisation ............................................................................................................................................................................................................
188Exercices sur les agrégats ............................................................................................................................................
188Du simple… .............................................................................................................................................................................................................
1881. Combien de races avons-nous dans la table Race ? ..........................................................................................................................................
1882. De combien de chiens connaissons-nous le père ? ............................................................................................................................................
1883. Quelle est la date de naissance de notre plus jeune femelle ? ...........................................................................................................................
1884. En moyenne, quel est le prix d'un chien ou d'un chat de race, par espèce, et en général ? ..............................................................................
1895. Combien avons-nous de perroquets mâles et femelles, et quels sont leurs noms (en une seule requête bien sûr) ? .......................................
189…Vers le complexe .................................................................................................................................................................................................
1891. Quelles sont les races dont nous ne possédons aucun individu ? ......................................................................................................................
1892. Quelles sont les espèces (triées par ordre alphabétique du nom latin) dont nous possédons moins de cinq mâles ? ......................................
190
3. Combien de mâles et de femelles de chaque race avons-nous, avec un compte total intermédiaire pour les races (mâles et femelles confondues) et pour les espèces ? Afficher le nom de la race, et le nom courant de l'espèce.
1904. Quel serait le coût, par espèce et au total, de l'adoption de Parlotte, Spoutnik, Caribou, Cartouche, Cali, Canaille, Yoda, Zambo et Lulla ? ...
191Partie 4 : Fonctions : manipuler les dates .......................................................................................
192Obtenir la date/l'heure actuelle .....................................................................................................................................
192Etat actuel de la base de données ..........................................................................................................................................................................
194Rappels ...................................................................................................................................................................................................................
195Date .........................................................................................................................................................................................................................
195Heure .......................................................................................................................................................................................................................
195Date et heure ...........................................................................................................................................................................................................
195Timestamp ...............................................................................................................................................................................................................
195Année ......................................................................................................................................................................................................................
195Date actuelle ...........................................................................................................................................................................................................
196Heure actuelle .........................................................................................................................................................................................................
196Date et heure actuelles ...........................................................................................................................................................................................
196Les fonctions ...........................................................................................................................................................................................................
196Qui peut le plus, peut le moins ................................................................................................................................................................................
197Timestamp Unix ......................................................................................................................................................................................................
198Formater une donnée temporelle ..................................................................................................................................
198Extraire une information précise .............................................................................................................................................................................
198Informations sur la date ...........................................................................................................................................................................................
200Informations sur l'heure ...........................................................................................................................................................................................
201Formater une date facilement .................................................................................................................................................................................
201Format .....................................................................................................................................................................................................................
202Exemples ................................................................................................................................................................................................................
203Fonction supplémentaire pour l'heure .....................................................................................................................................................................
204Formats standards ..................................................................................................................................................................................................
204Créer une date à partir d'une chaîne de caractères ................................................................................................................................................
205Calculs sur les données temporelles ............................................................................................................................
206Différence entre deux dates/heures ........................................................................................................................................................................
207Ajout et retrait d'un intervalle de temps ...................................................................................................................................................................
208Ajout d'un intervalle de temps .................................................................................................................................................................................
210Soustraction d'un intervalle de temps .....................................................................................................................................................................
211Divers ......................................................................................................................................................................................................................
211Créer une date/heure à partir d'autres informations ................................................................................................................................................
212Convertir un TIME en secondes, et vice versa ........................................................................................................................................................
212Dernier jour du mois ................................................................................................................................................................................................
212Exercices .......................................................................................................................................................................
213Commençons par le format .....................................................................................................................................................................................
214Passons aux calculs ................................................................................................................................................................................................
215Et pour finir, mélangeons le tout ..............................................................................................................................................................................
217Partie 5 : Sécuriser et automatiser ses actions ...............................................................................
218Transactions ..................................................................................................................................................................
218Etat actuel de la base de données ..........................................................................................................................................................................
221Principe ...................................................................................................................................................................................................................
222Support des transactions ........................................................................................................................................................................................
222Syntaxe et utilisation ...............................................................................................................................................................................................
222Valider/annuler les changements ............................................................................................................................................................................
Sommaire 5/414
www.siteduzero.com
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
1
/
413
100%
