Serveurs Base de données

MySQL MariaDB : Memo des commandes SQL

Cet article a été mis à jour, vous consultez ici une archive de cet article!
Table des matières

Introduction



A force d'utiliser des commandes SQL, j'ai pensé les mettre dans un seul endroit : ICI

Ainsi, je cherche à faire quelque chose, je viens chercher ici.

Si je ne sais pas, je cherche sur Google, et je renseigne ici, je retrouverai plus facilement.

Si besoin, pour vous entrainer, je vous propose un jeu de test avec une liste de villes et de départements : /upload/villes-et-departements_sql.gz

Se connecter à la base



Pour se connecter sous Linux en ligne de commande, on utilisera :

Code BASH :
mysql -u user -p


Et pour un hôte distant, on précise l'IP ou le nom d'hôte :

Code BASH :
mysql -u user -p -h 192.168.21.252


Après on peut aussi utiliser des logiciels graphiques comme mysql-workbench ou HeidiSQL sous Windows.

Commandes de base



Bases de données



Créer et supprimer des bases




Pour crée une base de données, saisir simplement :

Code SQL :
CREATE DATABASE mabase;


Si on veut spécifier le charset et collation :

Code SQL :
CREATE DATABASE mabaseutf8 CHARACTER SET utf8 COLLATE utf8_general_ci;


Pour la supprimer :

Code SQL :
DROP DATABASE mabase;


Utiliser les bases



Pour se connecter à la base :

Code SQL :
USE mabase;


Lister le charset et collation d'une base :

Code SQL :
SELECT @@character_set_database, @@collation_database;


Code TEXT :
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| latin1                   | latin1_swedish_ci    |
+--------------------------+----------------------+
1 row in set (0.00 sec)


Gestion des utilisateurs



Créer des utilisateurs



Pour créer un utilisateur, voici la commande :

Code SQL :
CREATE USER 'utilisateur'@'localhost' IDENTIFIED BY 'motdepasse';


Ici seules les connexions en local sont permises.
Il existe le caractère jocker permettant une connexion quelque soit l'hôte :

Code SQL :
CREATE USER 'utilisateur'@'%' IDENTIFIED BY 'motdepasse';


On peut afiner à un sous réseau de cette manière :

Code SQL :
CREATE USER 'utilisateur'@'192.168.21.%' IDENTIFIED BY 'motdepasse';


Attribuer des droits aux utilisateurs



Pour attribuer les droits à un utilisateur, il doit être créé avant.
(Dans les versions récentes de MariaDB, si l'utilisateur n'existe pas, il est créé à la volée)

On va utiliser la commande GRANT.
Pour me rappeler de la syntaxe, je pense toujours à ceci : GRANT quoi ON où TO qui. (prononcé rapidement, ça fait un peu japonais)

Pour attribuer tous les droits à un utilisateur (en faire en quelque sortes un deuxième root) :

Code SQL :
GRANT ALL PRIVILEGES ON *.* TO 'utilisateur'@'hote';


On peut spécifier des options supplémentaires pour enlever toutes les limites :

Code SQL :
GRANT ALL PRIVILEGES ON *.* TO 'utilisateur'@'hote'  WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;


Ou même en lecture seule uniquement :

Code SQL :
GRANT SELECT ON *.* TO 'utilisateur'@'hote';


Voici une commande pour créer un utilisateur juste pour la sauvegarde sur la machine locale :

Code SQL :
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'motdepasse';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';
GRANT LOCK TABLES ON *.* TO 'backup'@'localhost';


De la même façon, on peut supprimer ds droits avec REVOKE :

Code SQL :
REVOKE ALL ON *.* FROM 'utilisateur'@'hote';



On peut donner les pleins droits à un utilisateur sur une seule base uniquement :

Code SQL :
GRANT ALL PRIVILEGES ON labase.* TO 'utilisateurdelabase'@'hote';



Changer un mot de passe d'utilisateur de MySQL



Cette commande fonctionne uniquement pour MySQL :

Code SQL :
UPDATE mysql.USER SET password=PASSWORD("nouveau") WHERE USER="utilisateur";


OU avec la commande SET PASSWORD :

Code SQL :
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('nouveau');


Voir les utilisateurs



Pour voir les utilisateurs créés :

Code SQL :
SELECT USER,host,password FROM mysql.USER;


Voir les droits des utilisateurs



Pour un utilisateur donné, on peut voir ses droits de la façon suivante :

Code SQL :
SHOW GRANTS FOR 'utilisateur'@'hote';


Tables



Créer et supprimer des tables



Pour créer une table simple, voici un exemple :

Code SQL :
 
CREATE TABLE matable (
id INT(20) NOT NULL AUTO_INCREMENT COMMENT 'id, autoincrémenté',
datecreation datetime NOT NULL,
titre VARCHAR(32),
contenu text,
PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;



SELECT




INSERT




UPDATE




DELETE




ALTER



ALTER permet de modifier une table.

On peut ajouter une colonne à la table :

Code SQL :
ALTER TABLE matable ADD COLUMN (nouvelle_colonne VARCHAR(32) NOT NULL);


Modifier le type d'une colonne :

Code SQL :
ALTER TABLE matable MODIFY ma_colonne INT(32) NOT NULL;


Supprimer une colonne :

Code SQL :
ALTER TABLE matable DROP COLUMN ma_colonne;


Ou renommer une colonne :

Code SQL :
ALTER TABLE matable RENAME ma_colonne TO ma_colonne_renommee 



RENAME



RENAME permet de renommer une table :

Code SQL :
RENAME TABLE table1 TO table2;


Commandes spécifiques côté serveur



Afficher les process



Il est possible de voir les requêtes en cours d'exécution à un instant T.

Code SQL :
SHOW PROCESSLIST;


On va avoir un tableau avec :
Id : ID de la connexion
User : Utilisateur MySQL qui a ouvert la connexion
Host : Serveur + port source qui fait la connexion
db : Base de données sélectionnée (NULL si aucune)
Command : Type de commande
Time : Temps en secondes de la durée de la commande
State : Etat dans lequel la comande est
Info : Commande en cours d'exécution

A noter qu'une ligne représente une connexion et donc la somme des lignes du tableau indique le nombre de connexions en cours.

Modifier à la volée le nombre MAX de connexions



Pour afficher le nombre maximum de connexions possibles sur le serveur, on pourra utiliser la commande :

Code SQL :
SHOW GLOBAL VARIABLES LIKE '%max_connections%';


Si on souhaite élever par exemple le nombre de connexions à 512, on saisira :

Code SQL :
SET GLOBAL max_connections = 512;


Lorsque le service sera redémarré, la valeur par défaut sera réappliquée.
Pour rendre permanent le paramétrage, configurer dans le fichier my.cnf la valeur max_connections.