MySQL MariaDB : Memo des commandes SQL

Introduction




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

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.

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 :

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 superbase;


Si on veut spécifier le charset et collation :

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


Pour la supprimer :

Code SQL :
DROP DATABASE superbase;


Utiliser les bases




Pour se connecter à la base :

Code SQL :
USE superbase;


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 :

Quelque soit l'hôte :

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


Que pour localhost :

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


Attribuer des droits aux utilisateurs



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

Code SQL :
GRANT ALL PRIVILEGES ON *.* TO 'utilisateur'@'localhost' IDENTIFIED BY 'motdepasse' 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 :

Code SQL :
GRANT SELECT ON *.* TO 'utilisateur'@'localhost' IDENTIFIED BY 'motdepasse' ;


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

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'@'localhost';


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

Code SQL :
GRANT ALL PRIVILEGES ON labase.* TO 'utilisateurdelabase'@'localhost' IDENTIFIED BY 'motdepasse';




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"@"localhost";




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 (macol VARCHAR(32) NOT NULL);


Modifier le type d'une colonne :

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


Supprimer une colonne :

Code SQL :
ALTER TABLE matable DROP COLUMN macol;


Ou renommer une colonne :

Code SQL :
ALTER TABLE matable CHANGE macol manewcol INTEGER(10);




RENAME




RENAME permet de renommer une table :

Code SQL :
RENAME TABLE table1 TO table2;