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.