MySQL Accueil
Chapitres :
MySQL Chapitre 4 : Syntaxes
4.1.1.Règles générales de syntaxes
- MySQL n’est pas sensible à la casse
- MySQL attend un point-virgule à la fin de chaque commande
- MySQL permet la saisie sur plusieurs lignes d’une commande (« Entrée » continue la saisie de la même commande)
- Un commentaire en SQL est précédé de -- :
-- Voici un commentaire
- On prend l’habitude d’écrire les COMMANDES de MySQL en MAJUSCULES pour les différencier des paramètres.
- On prend l’habitude d’écrire les noms de Tables en CamelCase avec Majuscule commençante
- On prend l’habitude d’écrire les noms des attributs et de bases de données en CamelCase, minuscules commençantes
- Cependant, SQL n’est pas sensible à la casse.
- Pour les index, on débute leur nom par « ind_ »
o Pour les index UNIQUE, on peut écrire « ind_uni_»
MySQL est un langage qui fonctionne par défaut en ligne de commandes.
Lorsqu’on veut exécuter une série de commandes, et les mémoriser sous forme de « batch file », on peut utiliser un éditeur de texte quelconque et sauvegarder la série de commandes dans un fichier avec l’extension .sql
Ensuite, pour exécuter la batch, on utilise la commande suivante en console MySQL :
SOURCE monfichier.sql;
Attention cependant à préciser l’arborescence relative ou absolue de votre fichier, si celui-ci n’est pas dans le même dossier que le mysql.exe
4.2.1.Description d’un utilisateur MySql :
Un utilisateur MySQL est défini par 2 éléments :
- Son Login, qui lui permet de s’identifier (habituellement entouré de guillemets, mais pas obligatoires)
- L’hôte, à partir duquel il a le droit de se connecter à la base de données. (idem)
o L’hôte peut être 'localhost' pour une connexion locale
o L’hôte peut être une adresse IP
- Le mot de passe n’est pas obligatoire pour un compte MySQL. MAIS IL EST FORTEMENT RECOMMANDÉ.
Ex :
- 'ngeraudie'@'localhost'
- 'root'@'194.28.4.5'
- 'max'@'mon.exemple.net'
NOTER LA PRÉSENCE DES APOSTROPHES
4.2.2.Création d’un nouvel utilisateur
Un tout nouvel utilisateur créé n’a aucun privilège et ne peut rien faire à part se logger.
CREATE USER 'login'@'hote' IDENTIFIED BY 'motdepasse';
Le mot de passe est ici facultatif.
4.2.3.Suppression d’un utilisateur :
DROP USER 'login'@'hote';
4.2.4.Création d’un utilisateur avec plusieurs hôtes :
Il faut utiliser les jokers. Ex :
CREATE USER 'moimeme'@'192.168.0.%' IDENTIFIED BY 'toto';
L’utilisateur « moimeme » peut se connecter depuis n’importe quelle adresse du sous-réseau 192.168.0.x , x valeur quelconque entre 0 et 254.
CREATE USER 'nadine'@'%.voila.com' IDENTIFIED BY 'titi';
L’utilisateur “Nadine” peut se connecter depuis n’importe quelle sous-page du site www.voila.com
CREATE USER 'roger'@'%' IDENTIFIED BY 'tata';
L’utilisateur « roger » peut se connecter de n’importe quel hôte.
(ATTENTION : par soucis de sécurité, ces utilisateurs peuvent dans WAMPSERVER se faire priver de certains droits)
4.2.5.Modification du mot de passe :
SET PASSWORD FOR 'login'@'hote' = PASSWORD('supersecret');
4.2.6.Don de privilèges aux utilisateurs :
On peut donner tous les droits à un utilisateur.
Ex :
GRANT ALL PRIVILEGES
ON maBaseDeDonnees.*
TO 'nomCompte'@'hote'
IDENTIFIED BY 'mot_de_passe';
- L’hote peut être localhost pour connexion locale, ou autres paramètres adéquats si connexion client-serveur non locale (adresse IPV4, ou URL).
- Le nom de la base de données maBaseDeDonnee est à spécifier, cependant il n’est pas obligé qu’elle existe encore, elle peut très bien être créée ultérieurement par « Root ».
- nomCompte sera le nom du nouveau compte utilisateur qui héritera de tous les droits sur la base spécifiée.
- Son mot de passe sera celui spécifié par mot_de_passe
Pour les privilèges configurés plus finement :
GRANT privilege liste_colonnes
ON niveau_privilege
TO utilisateur;
Valeurs possibles pour privilege
|
SELECT |
Les 4 privilèges du CRUD |
|
INSERT |
|
|
UPDATE |
|
|
DELETE |
|
|
CREATE TABLE |
Création de table |
|
CREATE TEMPORARY TABLE |
Création de tables temporaries |
|
CREATE VIEW |
Création de vues |
|
ALTER |
Modification de table |
|
DROP |
Suppression de table, vue et base de données |
|
CREATE ROUTINE |
Créer une procédure stockée |
|
ALTER ROUTINE |
Modifier une procédure stockée |
|
EXECUTE |
Lancer une procédure stockée |
|
CREATE USER |
Créer des utilisateurs |
Valeurs possibles pour niveau_privilege :
|
*.* |
Privilège global : s’applique à tout |
|
* |
Si aucune bdd n’a été sélectionnée, équivalent de *.* Sinon, s’applique à tous les objets de la bdd sélectionnée. |
|
Nom_bdd.* |
S’applique à tous les objets de la bdd sélectionnée |
|
Nom_bdd.nom_table |
Privilège de table |
|
Nom_table |
Idem, si bdd qui la contient présélectionnée avec USE. |
|
Nom_bdd.nom_routine |
Privilège de fonction ou procédure stockée. |
Pour l’utilisateur :
- Si l’utilisateur n’existe pas précédemment ET que la mention IDENTIFIED BY est précisée, la commande le crée en même temps. Exemple :
GRANT privilege liste_colonnes
ON niveau_privilege
TO utilisateur IDENTIFIED BY mot_de_passe;
- Sinon, elle ne fait que lui octroyer les privilèges, ET/OU lui modifier son mot de passe.
4.2.7.Révocation de privilèges
REVOKE privilege
ON niveau_privilege
FROM utilisateur;
4.2.8.Donner le privilège de donner des privilèges
Lorsqu’on octroie un GRANT ALL à un utilisateur, on ne lui attribue pas le droit de pouvoir effectuer les commandes GRANT (donc de pouvoir à son tour distribuer des privilèges)
Ceci est le PRIVILÈGE appelé GRANT OPTION. (Suprême privilège)
GRANT ALL attribue tous les privilèges sauf le GRANT OPTION. ROOT possède le GRANT OPTION.
Pour le donner, il faut donc utiliser une des deux syntaxes suivantes :
GRANT SELECT, UPDATE, INSERT, DELETE, GRANT OPTION
ON paysg20.*
TO 'gerard'@'localhost' IDENTIFIED BY 'motdepassegerard';
- Ici, on vient de créer le compte 'gerard' via l’hôte local, il est gratifié des privilèges :
o Du CRUD
o Et de pouvoir à son tour délivrer des GRANT
o sur L’ENSEMBLE DES DONNÉES DE LA BASE paysg20
Autre syntaxe :
GRANT SELECT, UPDATE, INSERT, DELETE
ON paysg20.*
TO 'gerard'@'localhost' IDENTIFIED BY 'motdepassegerard'
WITH GRANT OPTION;
4.2.9.Droits d’accès et procédures stockées
Le cas des procédures est délicat, car le créateur de la procédure peut très bien être différent de celui qui l’exécute.
Ainsi, il peut y avoir confusion pour savoir quels sont les droits à vérifier pour l’accès aux données lors de l’utilisation d’une procédure : ceux du créateur de la procédure ou ceux de l’invocateur ?
Bien sûr, dans tous les cas, celui qui l’exécute (l’invocateur) se doit d’avoir les droits d’exécution sur la procédure. Mais a-t-il également nécessité d’avoir les droits d’accès sur les données traitées par la procédure ?
En réalité, le créateur de la procédure doit avoir les droits d’accès aux données traitées par la procédure, mais il n’est pas nécessaire que l’invocateur possède ces droits.
L’invocateur n’a seulement besoin que de posséder les droits d’exécution de la procédure.
Ainsi, un utilisateur qui a accès aux procédures peut très bien avoir accès aux données sur lesquelles il n’a aucun droit.
Pour modifier ceci, 2 options possibles :
- Redéfinir l’utilisateur-créateur pour la procédure à la place de celui par défaut
o Si j’écris ma procédure avec 'root', par défaut c’est 'root', mais je pourrais en désigner un autre.
- Configurer la clause SQL SECURITY qui a deux choix possibles :
o SQL SECURITY DEFINER (par défaut) : les droits d’accès aux données sont vérifiés sur le compte du créateur
o SQL SECURITY INVOKER : les droits d’accès aux données sont vérifiés sur le compte de l’invocateur.
Exemple : Je crée la procédure suivante avec 'root' :
USE paysG20;
DELIMITER |
CREATE PROCEDURE tester_acces()
BEGIN
SELECT * FROM caracpays;
END |
DELIMITER;
J’exécute le code suivant avec 'max', qui n’a pas les droits sur les tables de paysG20, mais a les droits d’exécution sur les procédures.
USE paysG20;
SELECT * FROM caracpays;
CALL tester_acces();
Le SELECT fait une erreur, mais la Procédure fonctionne.
Comment 'root' peut interdire à 'max' l’accès ?
2 façons :
- Le définir en DEFINER de la procédure si c’est 'max' qui servira de nouvelle référence de droits pour cette procédure
USE paysG20;
DELIMITER |
CREATE DEFINER = 'max'@'localhost'
PROCEDURE tester_acces()
BEGIN
SELECT * FROM caracpays;
END |
DELIMITER;
- Changer de SQL SECURITY si l’on préfère que les vérifications de droits se fassent selon qui l’invoque
USE paysG20;
DELIMITER |
CREATE PROCEDURE tester_acces()
SQL SECURITY INVOKER
BEGIN
SELECT * FROM caracpays;
END |
DELIMITER;
4.3. Gestion de base de données
4.3.1.Lister les bases de données présentes
SHOW DATABASES;
Vous verrez apparaître en retour un tableau listant les bases de données existant auxquelles vous avez accès.
Il y aura toujours 1 sortie supplémentaire à laquelle il ne faut pas porter attention, c’est « information_schema » qui est une construction système. Elle existe par défaut.
4.3.2.Utiliser une base de données
Si vous êtes entrés sur MySQL sans spécifier de base de données à utiliser ou si vous voulez changer de base de données à utiliser, voici la commande :
USE maBaseDeDonnees;
4.3.3.Créer une base de données
CREATE DATABASE mabaseDeDonnees CHARACTER SET 'utf8';
Le paramètre CHARACTER SET 'utf8' nous permet de préciser quel jeu de caractère utilisera notre base de données.
3 jeux de caractères sont les plus usuels :
- ASCII : le plus ancestral, le plus rudimentaire, ne permet pas de bien couvrir tous les accents des langues non-anglophones, mais est très compact : se code sur 7 bits.
- Latin : extension du ASCII, se code sur 8 bits, couvre la plupart des langages européens à racine latine. C’est le jeu de caractères par défaut de MySQL.
- UTF-8 : de plus en plus prépondérant, car il couvre bien les caractères spéciaux, l’ensemble des accents, mais surtout les langues non-latines comme Russe, Grec, Hébreu et Arabe par exemple.
4.3.4.Détruire une base de données
DROP DATABASE maBaseDeDonnées IF EXISTS;
L’option « If exists » générera des « warnings » indicateurs.
Parfois, dans les commentaires de retour d’une commande, MySQL peut générer des « warnings ». Pour pouvoir avoir accès à ces warnings, il suffit d’utiliser la commande :
SHOW WARNINGS;
4.3.5.Sauvegarder une base de données sous forme de fichier
Première méthode : Dans une console DOS (invite de commandes):
mysqldump -u nomUtilisateur –p --opt maBaseDeDonnees > monfichier.sql
Seconde méthode : En utilisant PHPMyAdmin :
1/ Cliquer sur la base à sauvegarder
2/ Sélectionner l’onglet « Exporter »
4.3.6.Pour récupérer une base de données sauvegardée sous forme de fichier
Dans une console DOS :
mysql maBaseDeDonnees < monfichier.sql
Peut aussi se faire dans MySQL
USE maBaseDeDonnees;
SOURCE monfichier.sql;
Ou dans PHPMyAdmin (onglet « importer »)
4.4.1.Lister les tables présentes, détailler une table
Cette commande nous liste toutes les Tables contenues dans une base de données :
SHOW TABLES;
Cette commande nous détaille toutes les colonnes contenues dans une table, et toutes leurs caractéristiques
DESCRIBE MaTable;
Commande pour créer une Table :
CREATE TABLE IF NOT EXISTS MaTable (
NomColonne1 typeColonne1,
NomColonne2 typeColonne2,
Nomcolonne3 typeColonne3,
...,
PRIMARY KEY (NomColonneCléPrimaire)
)
ENGINE=InnoDB;
- NomColonne : choisir un nom représentatif pour les données qui seront rangées dans cette colonne.
- le type de chaque colonne sera choisi parmi les types de données possibles (voir chapitre 3 "Types de données")
- PRIMARY KEY : spécifier la colonne qui servira de clé primaire.
- Choix du moteur : InnoDB
o Ouvert sur les autres bases,
o Moins performant que d’autres,
o Mais davantage sécuritaire.
- Autre moteur possible : MyISAM : « propriétaire » MySQL, « fermé », mais plus rapide.
- Pour chaque colonne on peut également spécifier une ou des options parmi celles-ci :
- NOT NULL : attribut à spécifier pour obliger la donnée à être non-vide dans cette colonne. Sinon, lors du remplissage des données, cette colonne permettra une non-attribution de valeur, ce qui la spécifiera sur un état 'NULL' (vide)
- AUTO_INCREMENT : attribut pour les clés primaires, qui oblige le champ à s’incrémenter automatiquement.
- DEFAULT : spécifie une valeur par défaut qui s’inscrira dans la donnée en cas de non-attribution dans cette colonne.
Voici un exemple :CREATE TABLE IF NOT EXISTS MesContacts (
Id INT AUTO_INCREMENT, -- (notre clé primaire auto incrémentée)
Nom VARCHAR(63) NOT NULL, -- Une donnée qui ne pourra être omise
Prenom VARCHAR(63), -- Une donnée qui pourra être omise ('NULL')
Age UNSIGNED SMALLINT , -- Une donnée qui pourra être omise ('NULL')
Pays VARCHAR(63) DEFAULT 'Canada' ,
-- Si omise, cette donnée vaudra 'Canada' par défaut.
PRIMARY KEY (Id)
)
ENGINE=InnoDB;
ALTER TABLE MesContacts ADD Telephone VARCHAR(15);
-- permet d'ajouter une colonne à la table existante
ALTER TABLE MesContacts DROP Age;
-- permet de retirer une colonne
ALTER TABLE MesContacts CHANGE ...;
ALTER TABLE MesContacts MODIFY ...;-- permettent de modifier une colonne
- Modifier une colonne avec ALTER … CHANGE :
o On peut changer le type, renommer, ajouter des attributs
ALTER TABLE NomTable CHANGE ancienNomColonne nouveauNomColonne DEFAULT 'ValeurParDefaut';
- Modifier avec ALTER … MODIFY
o On peut changer le type, ajouter des attributs
ALTER TABLE NomTable MODIFY NomColonne NOT NULL;
DROP TABLE MaTable;
4.5.1.Remplir une table : INSERT
- Première syntaxe :
INSERT INTO MaTable
VALUES (maValeur1, MaValeur2, MaValeur3, …);
INSERT INTO MaTable
VALUES (1, 'Tremblay', 'Michel', 48);
Chaque colonne de la table doit être successivement décrite, dans l’ordre de déclaration dans la table, à défaut de mélanger les données.
- Seconde syntaxe, qui spécifie l’ordre des colonnes :
INSERT INTO Animal (sexe, date_naissance, espece)
VALUES ('F', '2006-08-25 05:21:00', 'tortue');
- Possibilité de rentrer plusieurs données en même temps sous cette syntaxe :
INSERT INTO Animal (espece, sexe, date_naissance) VALUES
('tortue', 'F', '2009-08-03 05:12:00'),
('chat', 'M', '2010-07-04 12:23:00'),
('chien', 'F', '2007-11-22 21:33:00');
Dernière syntaxe possible :
INSERT INTO Animal
SET nom='Bobo', espece='chien', sexe='M', date_naissance='2010-07-21 15:41:00';
4.5.2.Remplir une table depuis des données dans un fichier .csv
Cette syntaxe permet d’automatiser le remplissage d’une table à partir de données structurées dans un fichier de type csv (compatible MS Excel).
Dans ce cas, sauvegarder depuis MS Excel en choisissant le type csv, et le séparateur de champs le caractère « ; ».
LOAD DATA LOCAL INFILE 'monfichier.csv'
INTO TABLE MaTable
FIELDS
TERMINATED BY ';'
ENCLOSED BY '\''
ESCAPED BY '\\'
LINES
TERMINATED BY '\n' -- ou '\r\n' selon l'ordinateur et le programme utilisés pour créer le fichier
IGNORE n LINES
(champ1, champ2, champ3, …);
Attention, s’assurer que le fichier CSV est bien dans le même répertoire que l’endroit d’où vous avez lancé MySQL dans la console DOS. Sinon, spécifier le chemin complet.
- LOCAL : pour accès au fichier par côté client
- FIELDS TERMINATED BY ';' précise le symbole séparant les différents champs dans mon fichier texte d’importation.
- ESCAPED BY '\\' précise quel est le caractère d’échappement : celui qui nous permettra d’utiliser les caractères spéciaux dans le texte, en le mettant en avant, en annulant leur effet. Noter ici que le caractère d’échappement doit s’auto-échapper.
- ENCLOSED BY '\'' précise que le caractère apostrophe encadre les champs de texte. Ainsi, si l’on veut qu’un champ soit reconnu comme NULL, il faudra qu’il soit du type :
'CHAMP1';'CHAMP2';NULL;'CHAMP4';-7;0;'CHAMP7'
- TERMINATED BY signale le(s) caractère(s) de fin de ligne, donc de fin de donnée. '\r\n' est l’option qui convient pour les fichiers issus des OS Microsoft. Sinon, '\n' convient.
- IGNORE n LINES : permet de sauter un certain nombre de lignes en en-tête de fichier (usuellement une, qui contient les types de colonnes)
- Tout lire :
SELECT * FROM NomTable;
- Visualiser seulement certaines colonnes :
SELECT (colonne_a, Colonne_b) FROM NomTable;
- Lecture filtrée : utiliser WHERE
SELECT *
FROM Animal
WHERE espece='chien';
- Opérateurs de comparaison :
=
<
<=
>
>=
<> ou !<
<=> (égal ou NULL)
- Combinaisons logiques
|
fonction |
Symbole |
|
ET |
&& |
|
OU |
|| |
|
XOR |
|
|
NOT |
! |
- Tester la « NULL »-ité d’une valeur :
= NULL et <> NULL ne fonctionnent pas. Utiliser à la place IS NULL ou IS NOT NULL.
- Tri des données sortantes : Option ORDER BY sur SELECT :
SELECT *
FROM Animal
WHERE espece='chien'
ORDER BY date_naissance DESC;
DESC : option de tri descendant (TRI INVERSÉ)
- Tri sur plusieurs colonnes :
SELECT *
FROM Animal
ORDER BY espece, date_naissance;
- Recherche dans un intervalle : WHERE … BETWEEN … AND
SELECT *
FROM Animal
WHERE date_naissance BETWEEN '2008-01-05' AND '2009-03-23';
Valable pour nombres, dates et chaînes de caractères. Plus performant qu’une combinaison d’opérateurs de comparaison.
- Recherche approximative : opérateur LIKE
SELECT *
FROM Animal
WHERE nom LIKE 'a%';
Sortira tous les enregistrements dont le nom commence par a
Jokers possibles :
- % : remplace un caractère ou une chaîne de caractères, ou même rien
- _ : remplace un seul caractère, ou rien.
LIKE existe aussi en version NOT LIKE.
LIKE n’est pas sensible à la casse.
UPDATE Animal SET
sexe = 'M',
espece = 'Perroquet'
WHERE id = 3;-- si where non précisé, toutes les données sont affectées
REPLACE INTO Animal (id, sexe, date_naissance, espece)
VALUES (7, 'F', '2006-08-25 05:21:00', 'tortue')
WHERE id = 7; -- si where non précisé, toutes les données sont affectées
La même chose que INSERT, mais en cas de violation de donnée dupliquée, la donnée précédente sera mise à jour (id précisé).
DELETE FROM Animal
WHERE Id = 5;
ATTENTION : Si WHERE n’est pas précisé, TOUTES les lignes subissent la modification. WHERE sert de filtre à la modification
Un Index permet de désigner une colonne qui sera souvent utilisée pour des tris. Il optimise les performances des tris.
Il s’appuie sur la clé primaire, et effectue une correspondance selon cette colonne désignée par rapport à la clé primaire.
Un Index est à rajouter sur les colonnes sujettes à des recherches fréquentes, par requêtes.
Éviter de trop en mettre car ceci n’optimise pas les requêtes d’insertion, de modification, de suppression.
Il est parfois plus pertinent de préférer un index sur plusieurs colonnes, plutôt que plusieurs index sur chacune de ces colonnes (et sur un index multicolonne, l’ordre de gauche à droite détermine une hiérarchie de priorité)
Ajout d’un index dans une table au moment de sa création :
CREATE TABLE nomTable (
colonne1 INT INDEX, -- Crée un index simple sur colonne1
...
);
IMPORTANT : On ne peut pas définir d’index multi-colonne (composite) de cette façon. Autre syntaxe possible :
CREATE TABLE mesContacts (
Id INT,
nom VARCHAR(63),
prenom VARCHAR(63),
...,
PRIMARY KEY (Id), -- Id devient la clé primaire
INDEX ind_nom_prenom (nom, prenom) -- on créée un index multi-colonne
)
ENGINE=InnoDB;
Garantit l’exclusivité des valeurs (ou combinaisons de valeurs si multiple) qui y seront insérées. Également dans la syntaxe ci-dessus, possibilité d’ajouter :
UNIQUE ind_uni_nom_prenom (nom, prenom) -- Crée un index UNIQUE multi-colonne
4.6.3.Ajout d’index après création de table
ALTER TABLE mesContacts
ADD INDEX ind_nom_prenom (nom, prenom); --Ajout d'un index multi-colonne
Pour les index UNIQUE :
ALTER TABLE mesContacts
ADD UNIQUE ind_uni_nom_prenom (nom, prenom); --Ajout d'un index UNIQUE multi-colonne
Autre syntaxe possible :
CREATE INDEX ind_nom_prenom
ON mesContacts (nom, prenom); -- Crée un index multi-colonne
Pour un index UNIQUE :
CREATE UNIQUE INDEX ind_uni_nom_prenom
ON mesContacs (nom, prenom); -- Crée un index UNIQUE multi-colonne
4.6.4.Lister les index d’une table
SHOW INDEX FROM mesContacts;
ALTER TABLE mesContacts
DROP INDEX ind_nom_prenom ON mesContacts;
Les Clés étrangères conservent l’intégrité de la base en cas de relation de table. En fait, elles représentent une extension de la notion de clé primaire, mais pour les relations de tables.
Donc une clé étrangère s’installe entre une colonne d’une table A (référence) et une autre colonne d’une table B (cible)
Exemple : J’ai une table Clients, et j’ai une table Commande.
Clients
|
Id |
Prenom |
Nom |
Tel |
|
1 |
Pierre |
Untel |
514-123-4567 |
|
2 |
Paul |
Autre |
514-234-5678 |
|
3 |
Jacques |
Muche |
514-345-6789 |
Commande
|
Id |
IdClient |
produit |
Quantite |
|
1 |
3 |
Tube de colle |
45 |
|
2 |
2 |
Efface |
7 |
|
3 |
3 |
Ordinateur |
2 |
On comprend dans l'exemple ci-dessus que le client numéro 2 a fait une commande de 7 effaces, le client numéro 3 a fait une commande de 45 tubes de colle ET une commande de 2 Ordinateurs, et le client numéro 1 n'a pas encore fait de commande.
J’installe ma clé étrangère entre le champ Id de la table Clients (référence) et le champ IdClient de la table Commande (Cible)
Syntaxe lors de la création de table cible :
CREATE TABLE Commande (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
client INT UNSIGNED NOT NULL,
produit VARCHAR(40),
quantite SMALLINT DEFAULT 1,
CONSTRAINT fk_client_numero -- nom donné à notre clé étrangère
FOREIGN KEY (IdClient) -- Colonne sur laquelle on crée la clé (cible)
REFERENCES Clients(id) -- Colonne de référence (dans l'autre table)
)
ENGINE=InnoDB;
Syntaxe après création de table :
ALTER TABLE Commande
ADD CONSTRAINT fk_client_numero FOREIGN KEY (IdClient) REFERENCES Clients(id);
Suppression de clé étrangère :
ALTER TABLE nom_table
DROP FOREIGN KEY symbole_contrainte;
Une Jointure permet de jongler avec plusieurs tables avec une seule requête.
Lorsqu’on requiert des données présentes dans différentes tables liées par clé étrangère, on installe une jointure temporaire entre les tables pour permettre aux requêtes de disposer d’informations plus vastes.
Syntaxe de requête comprenant une jointure interne :
SELECT caracpays.pays,regimepays.typeregime
FROM regimepays
INNER JOIN caracpays
ON regimepays.id = caracpays.idtyperegime
WHERE caracpays.nbHab > 100000000;
Ici les deux tables Caracpays et Regimepays se joignent pour permettre la restitution d’informations optimales.
La jointure s’effectue de la même façon que l’on a installé la clé étrangère : Entre le id de la table de référence (regimepays) et le id TypeRegime de la table cible (caracpays).
La jointure permet à la requête d’accéder aux deux tables, les informations sont jointes par le lien établi.
- INNER (facultatif dans la syntaxe) signifie que la jointure ne sera effectuée QUE pour les lignes qui possèdent des données de chacun des côtés de la jointure. Ainsi dans un INNER JOINT, seules les lignes ayant des correspondances parfaites dans le contenu répondent à la requête. Les NULL sont exclues.
C’est à cela que l’on fait appel si l’on veut inclure les cas où l’un des deux côtés de ma jointure peut être nul.
Il faut choisir quel sera le côté qui imposera son tri sur l’autre : le gauche ou le droit.
- Jointure par la gauche
SELECT TableProduit.produit, TableClient.nom
FROM TableProduit– Table de gauche
LEFT JOIN TableClient – Table de droite
ON TableProduit.client = TableClient.id
WHERE TableClient.nom LIKE 'C%'
ORDER BY TableClient.nom;
- Jointure par la droite
SELECT TableProduit.produit, TableClient.nom
FROM TableProduit– Table de gauche
RIGHT JOIN TableClient – Table de droite
ON TableProduit.client = TableClient.id
WHERE TableProduit.Qte < 10
ORDER BY TableProduit.Qte;
Une Union de requêtes s’utilise par exemple, lorsqu’on désire réunir le résultat de deux ou plusieurs requêtes :
Exemple : Ma requête 1 sort :
|
Id |
nom |
Tel |
|
7 |
Jean |
514-123-4567 |
|
86 |
pierre |
514-234-5678 |
Ma requête 2 sort :
|
Id |
nom |
Tel |
|
14 |
Jacques |
514-333-1212 |
|
66 |
georges |
514-222-4545 |
On veut unir les deux requêtes pour obtenir directement en un seul résultat :
|
id |
nom |
tel |
|
7 |
Jean |
514-123-4567 |
|
86 |
Pierre |
514-234-5678 |
|
14 |
Jacques |
514-333-1212 |
|
66 |
georges |
514-222-4545 |
La syntaxe (simple) est
Requête1
UNION
Requête2
Ce qui ressemble souvent à, par exemple :
SELECT
macolonne1
FROM matable1
INNER JOIN matable2
ON matable1.macolonne1 = matable2.macolonne2
WHERE <condition ici …>
UNION
SELECT autrecolonne (etc …)
Les lignes résultantes de chacune des requêtes sont concaténées pour le résultat final.
ATTENTION : il est ABSOLUMENT IMPORTANT de respecter le même nombre de colonnes pour chacune des requêtes de l’UNION.
ATTENTION2 : mySQL est très permissif sur les genres dans un UNION, alors il permet de mélanger les types de données. Ce qui peut donner des résultats exotiques …
DONC : être prudent sur les types, nombres et ordre des colonnes pour chaque requête faisant partie d’une UNION.
ATTENTION3 : UNION efface les doublons : c’est un petit peu comme s’il possédait la propriété DISTINCT inhérente.
Pour désactiver ceci, juste préciser UNION ALL à la place.