MySQL/Syntaxe

MySQL

1. Introduction
2. PhpMyAdmin
3. Parcourir les bases de données
4. Spécifier les noms
5. LDD, LMD et LCD
6. Variables utilisateurs
7. Alias
8. Types de données
9. Manipulation de base
   • mysqldump
10. Manipulation de table
11. Manipulation de données
12. Requêtes
13. NULL
14. Opérateurs
15. Fonctions
16. Procédures stockées
17. Regex
18. Bases de données spatiales
19. Importer et exporter
20. Réplication
21. Optimisation
22. API
23. Sécurité
24. Mots réservés
25. Débogage

Principe

On peut diviser le vocabulaire SQL en trois groupes :

• Le LDD est composé de CREATE, ALTER et DROP. Il permet d'ajouter, modifier et supprimer les structures logiques qui contiennent les données, ou autorisent les utilisateurs à y accéder ou à les maintenir (bases, MaTables, vues, clés...). le LDD concerne les métadonnées.
• Le LMD est constitué de INSERT, UPDATE et DELETE. Pour ajouter, modifier et supprimer les données stockées dans les bases.
• Le LCD représente GRANT et REVOKE. Il s'agit de la sécurité de la base, des permissions des utilisateurs.

On peut aussi distinguer deux autres catégories :

• Le DQL (Data Query Language : langage de requête de données), comme SELECT, SHOW et HELP. Ils sont rattachés au LMD dans le modèle traditionnel.
• Le LCT (en anglais DTL ou Data Transaction Language : langage de transaction de données) avec START TRANSACTION, SAVEPOINT, COMMIT et ROLLBACK [TO SAVEPOINT]. Affiliable au LCD dans le modèle à trois catégories.

Visualisation

• L'étoile (alias wildcard : "*") désigne toutes les colonnes d'une table, pour éviter de les sélectionner une par une.
• Quand un nom de colonne est un mot réservé par MySQL, pour ne pas qu'il soit interprété, il faut l'entourer d'accents graves (ex : SELECT `key`).

 SELECT * FROM MaTable
 SELECT * FROM MaTable1, MaTable2, ...
 SELECT champ1, champ2, ... FROM MaTable1, MaTable2, ...
 SELECT ... FROM ... WHERE condition
 SELECT ... FROM ... WHERE condition GROUPBY champ
 SELECT ... FROM ... WHERE condition GROUPBY champ HAVING condition2
 SELECT ... FROM ... WHERE condition ORDER BY champ1, champ2
 SELECT ... FROM ... WHERE condition ORDER BY champ1, champ2 DESC
 SELECT ... FROM ... WHERE condition LIMIT 10
 SELECT DISTINCT champ1 FROM ...
 SELECT DISTINCT champ1, champ2 FROM ...

Jointures

 SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
 SELECT ... FROM t1, t2 WHERE t1.id1 = t2.id2 AND condition
 SELECT ... FROM t1 INNER JOIN t2 ON (t1.id1 = t2.id2) WHERE condition
 SELECT ... FROM t1 NATURAL JOIN t2 WHERE condition
 SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
 SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...

Conditions

 champ1 = valeur1
 champ1 <> valeur1
 champ1 LIKE 'valeur _ %'
 champ1 IS NULL
 champ1 IS NOT NULL
 champ1 IS IN (valeur1, valeur2)
 champ1 IS NOT IN (valeur1, valeur2)
 champ1 BETWEEN valeur1 AND valeur2
 condition1 AND condition2
 condition1 OR condition2

Modification du contenu

 INSERT INTO MaTable1 (champ1, champ2, ...) VALUES (valeur1, valeur2, ...)

 DELETE FROM MaTable1 / TRUNCATE MaTable1
 DELETE FROM MaTable1 WHERE condition
 -- jointure :
 DELETE FROM MaTable1, MaTable2 WHERE MaTable1.id1 = MaTable2.id2 AND condition

 UPDATE MaTable1 SET champ1=nouvelle_valeur1 WHERE condition
 -- jointure :
 UPDATE MaTable1, MaTable2 SET champ1=nouvelle_valeur1, champ2=nouvelle_valeur2, ... WHERE MaTable1.id1 = MaTable2.id2 AND condition

Naviguer dans MySQL

 SHOW DATABASES
 SHOW TABLES
 SHOW INDEX FROM MaTable
 SHOW FIELDS FROM MaTable / DESCRIBE MaTable
 SHOW CREATE TABLE MaTable
 SHOW PROCESSLIST
 KILL numero
 USE ma_bdd

Créer / supprimer une base

 CREATE DATABASE MaBase
 CREATE DATABASE MaBase CHARACTER SET utf8
 DROP DATABASE `MaBase`

 ALTER DATABASE MaBase CHARACTER SET utf8

Créer/supprimer/modifier une table

 CREATE TABLE MaTable (champ1 type1, champ2 type2, ...)
 CREATE TABLE MaTable (champ1 type1, champ2 type2, ..., INDEX (champ))
 CREATE TABLE MaTable (champ1 type1, champ2 type2, ..., PRIMARY KEY (champ1))
 CREATE TABLE MaTable (champ1 type1, champ2 type2, ..., PRIMARY KEY (champ1, champ2))
 CREATE TABLE MaTable1 (fk_champ1 type1, champ2 type2, ...,
   FOREIGN KEY (fk_champ1) REFERENCES MaTable2 (t2_champA))
     [ON UPDATE|ON DELETE] [CASCADE|SET NULL]
 CREATE TABLE MaTable1 (fk_champ1 type1, fk_champ2 type2, ...,
   FOREIGN KEY (fk_champ1, fk_champ2) REFERENCES MaTable2 (t2_champA, t2_champB))
 CREATE TABLE IF NOT EXISTS MaTable (...)
 CREATE TABLE MaTable (champ1 type1, champ2 type2, ...) SELECT ...

 CREATE TEMPORARY TABLE MaTable (...)

 DROP TABLE MaTable
 DROP TABLE IF EXISTS MaTable
 DROP TABLE MaTable1, MaTable2, ...

 ALTER TABLE MaTable ADD (champ1 type1, champ2 type2, ...)
 ALTER TABLE MaTable MODIFY champ1 type1 
 ALTER TABLE MaTable MODIFY champ1 type1 NOT NULL ... 
 ALTER TABLE MaTable CHANGE ancien_nom_champ1 nouveau_nom_champ1 type1
 ALTER TABLE MaTable CHANGE ancien_nom_champ1 nouveau_nom_champ1 type1 NOT NULL ...
 ALTER TABLE MaTable ALTER champ1 SET DEFAULT ...
 ALTER TABLE MaTable ALTER champ1 DROP DEFAULT
 ALTER TABLE MaTable ADD INDEX (champ);
 DROP INDEX champ ON MaTable;

 ALTER TABLE ancien_nom RENAME nouveau_nom;

Clés primaires et étrangères

Lecture

Les clés (primaires et étrangères) d'une table sont incluses dans sa description :

SHOW CREATE TABLE MaTable

Mais on peut aussi ne sélectionner qu'elles :

SELECT *
FROM  `information_schema`.`TABLE_CONSTRAINTS`
WHERE `TABLE_NAME` = 'MaTable'

Sinon :

SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'MaBase' AND REFERENCED_TABLE_NAME = 'MaTable';

Création

 CREATE TABLE MaTable (..., PRIMARY KEY (champ1, champ2))
 CREATE TABLE MaTable (..., FOREIGN KEY (champ1, champ2) REFERENCES MaTable2 (t2_champ1, t2_champ2))

Pour ajouter une clé étrangère à une table existante :

ALTER TABLE MaTable ADD FOREIGN KEY (maTable2_id) REFERENCES maTable2(id);

 dans PhpMyAdmin, ceci peut être fait à la souris en parcourant des menus déroulant : menu "Structure" de la table, puis "Vue relationnelle" (Relation view).

CONSTRAINT

Facultativement, la clé étrangère peut aussi être nommée : on la baptise après le mot CONSTRAINT.

ALTER TABLE MaTable ADD CONSTRAINT fk_maTable2_id FOREIGN KEY (maTable2_id) REFERENCES maTable2(id);

Cette syntaxe permet aussi d'ajouter des évènements en cas de suppression ou mise à jour du champ lié par une clé étrangère, à préciser parmi : RESTRICT, CASCADE, SET NULL, NO ACTION, SET DEFAULT. Ex :

ALTER TABLE `MaTable`
ADD CONSTRAINT `fk_maTable2_id` FOREIGN KEY (`MaTable2_id`) REFERENCES `MaTable2`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

CHECK

A ne pas confondre avec CONSTRAINT, il gère les contraintes indépendantes des autres tables. Exemples :

 CREATE TABLE MaTable (
  champ1 INT CHECK (champ1 > 10),
  champ2 INT CONSTRAINT champ2_positif CHECK (champ2 > 0),
  CHECK (champ1 > champ2)
 )

Suppression

Pour désactiver les contraintes le temps d'une session :

 SET FOREIGN_KEY_CHECK = 0;

Pour le faire globalement :

SET GLOBAL FOREIGN_KEY_CHECKS = 0;

Pour faire supprimer définitivement une contrainte :

ALTER TABLE MaTable1
DROP FOREIGN KEY FK_MaTable1_MaTable2

Mais parfois pour éviter les vérifications des contraintes, il suffit de lancer la commande suivante le temps d'un UPDATE avec un WHERE :

SET SQL_SAFE_UPDATES = 0;

Lors du clustering comme avec Galera, il faut avoir une clé primaire par table.

Créer/supprimer une vue

  CREATE VIEW nomvue AS SELECT champ1, champ2 FROM MaTable1               -- ou
  CREATE VIEW nomvue (champ1, champ2...) AS SELECT champ1, champ2 FROM MaTable1

  ALTER VIEW nomvue (champ1, champ2...) AS SELECT champ2 FROM MaTable1;
  DROP VIEW nomvue;

Il est impossible d'ajouter un index à une vue.

Permissions

Pour les lister :

SHOW GRANTS FOR CURRENT_USER();

Pour les définir:

 GRANT ALL PRIVILEGES ON base.* TO 'utilisateur'@'localhost' IDENTIFIED BY 'password';
 GRANT SELECT, INSERT, DELETE ON base.* TO 'utilisateur'@'localhost' IDENTIFIED BY 'password';
 REVOKE ALL PRIVILEGES ON base.* FROM 'utilisateur'@'hôte'; -- une seule permission
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'utilisateur'@'hôte'; -- toutes les permissions

 SET PASSWORD = PASSWORD('nouveau_pass')
 SET PASSWORD FOR 'utilisateur'@'hôte' = PASSWORD('nouveau_pass')
 SET PASSWORD = OLD_PASSWORD('nouveau_pass')

 DROP USER 'utilisateur'@'hôte'

Oubli de mot de passe

$ service mysql stop
$ mysqld_safe --skip-grant-MaTables
> UPDATE mysql.user SET password=PASSWORD('nouveau') WHERE user='root';
## Tuer mysqld_safe, avec Control + \
$ service mysql start

Réparer les tables après un arrêt soudain

mysqlcheck --all-databases
mysqlcheck --all-databases --fast

Relancer la synchronisation de la base du serveur secondaire

$ mysql
mysql> slave start;
mysql> show slave status\G

Manipuler des variables

Les définitions sont effectuées à l'aide des mots clés "select" (suivi de ":=") ou "set" (avec "=") :

 SELECT @test := 2;
 SELECT @test + 1
 
 SET @date1='date une', @date1='date deux'

Pour les afficher ensuite :

 show variables like 'test';
 show variables like 'date1';
 show variables like 'date2';

Certaines variables globales représentent la configuration du système, et peuvent être changées provisoirement le temps d'une session, ou de façon permanente :

mysql> set @@global.max_connections = 1000;
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  |    60 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set @@session.wait_timeout=120;

En cas de conversion de dates au format Text en Datetime, cela les efface toutes à 0000-00-00 00:00:00

Variables globales

Pour avoir la version de MySQL :

SHOW VARIABLES LIKE 'version';