MySQL

Logiciels de visualisation de base de données

Il existe plusieurs logiciels libres permettant de gèrer des bases de données MySQL.

• DBEAVER Community Edition (Linux)
• Squirrel (Linux)

Sous Linux, le connecteur MySQL nécessite les paquets libmysql-java et mysql-client

Type de moteur de base de données :
Sous MySQL ou MariaDB, les données sont stockées avec un moteur de base de données.
Si l'application nécessitera plus de lecture que d'écriture, il faut privilégier le moteur MyISAM, sinon il faut utiliser InnoDB.

Créer une sauvegarde d'une base de données :

mysqldump --user=nom --password=password --databases nom_base > fichier.sql

Restaurer une base de données

mysql --user=user --password=password < fichier.sql

Bases de données

Créer une base de données :

CREATE DATABASE nombase;

Lister les bases de données :

SHOW DATABASES;

Utiliser une base de données :

USE nombase;

Supprimer une base de données :

DROP DATABASE nombase;

TABLES

Lister les tables :

SHOW TABLES;

Structure d'une table :

DESCRIBE nomtable;

Renommer une table :

ALTER TABLE nomtable RENAME AS nouveaunom;

UTILISATEURS

Création d'un utilisateur

CREATE USER "nom"@"localhost";

Définir un mot de passe :

SET PASSWORD FOR "nom"@"localhost" = password('mot de passe');

Créer un utilisateur en lui définissant un mot de passe

CREATE USER "nom"@"localhost" IDENTIFIED BY PASSWORD = password('mot de passe');

Supprimer un utilisateur :

DROP USER "nom"@"localhost";

Lister les utilisateurs :

SELECT USER FROM mysql.user;

PRIVILEGES

Donner tous les droits â un utilisateur sur une base de données :

GRANT ALL PRIVILEGES ON nombase.* TO "nom"@"localhost";

Supprimer les droits â un utilisateur :

REVOKE ALL PRIVILEGES ON nombase.* FROM "nom"@"localhost";

Lister les droits d'un utilisateur :

SHOW GRANTS FOR "user"@"localhost";

Vues et procédures stockées

Afficher les vues d'une base de données

SHOW FULL TABLES WHERE table_type = 'VIEW';

Afficher les procédures stockées

SHOW PROCEDURE STATUS;
Pour une base spécifique :
SHOW PROCEDURE STATUS WHERE Db = 'nombase';

Installation

Définir le mot de passe lors de l'installation
Sous Debian, MariaDB est installé avec l'utilisateur Root utilisant le plugin unix_socket.
Pour cet utilisateur, le mot de passe est à définir dans le fichier /etc/mysql/debian.cnf à la ligne PASSWORD =

Erreur 1698 : Acces denied for user 'root'@'localhost'

Cette erreur provient sur une installation neuve de MYSQL. Elle est liée au fait que l'utilisateur Root est défini avec le plugin auth_socket. Il faut donc le modifier pour configurer le plugin mysql_native_password.
Se connecter à MYSQL en sudo

sudo mysql -u root -p
mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE user='root';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Installation hors ligne sur CentOS 7

Récupérer l'archive de MariaDB server community sur le site officiel
Récupérer l'ISO de CentOS7
Monter l'image de CentOS 7 :

mkdir -p /media/centos
mount -o loop /root/CentOS-7-...iso /media/centos

Configurer YUM
yum-config-manager --disable base,updates,extras pour supprimer l'utilisation des dépôts en ligne.
yum-config-manager --enable c7-media pour activer le CDROM d'installation.
Lancer l'installation
yum install socat
yum local install *.rpm

Fin de l'installation.

Jointures

Le site https://stackoverflow.com/questions/448023/what-is-the-difference-between-left-right-outer-and-inner-joins?sort=newest explique le principe des jointures.
Il n’est point aisé de comprendre quelles sont les subtilités qui différencient ces différentes formes de jointures, nous allons donc voir ensemble leur signification et leur effet.
Nous prendrons pour cela le cas exemple d’une table main_table, sélectionnée par un FROM à laquelle est joint la table joined_table. Nous parlerons de correspondances dans le cas où une entrée de main_table possède une entrée associée dans joined_table.
Pré-requis :
Pour comprendre le fonctionnement algorithmique d’une jointure, vous devez avoir en tête que main_table et joined_table sont jointes par la valeur partagée d’un de leurs champs respectifs, c’est à dire un champs de main_table à la même valeur qu’un champs de joined_table, permettant d’établir un lien, une correspondance entre les deux.
Mais il se peut qu’il existe des entrées de main_table qui ne soient liées à aucune entrée de joined_table et qu’il existe des entrées de joined_table qui ne soient pas liées à aucune entrée de main_table, ainsi certaines entrées peuvent théoriquement n’avoir aucune correspondance. Si on tente de faire une jointure sur l’autre table alors qu’elle ne possède aucune entrée correspondante, toutes les valeurs vaudront NULL. Et si elle en possède plusieurs, il y a aura autant de résultat que de données correspondantes, même si les données de la première table doivent être dupliquées.

LEFT JOIN

LEFT JOIN est une jointure par la gauche, la table de gauche étant la table principale, cette jointure se base donc sur la table main_table pour établir sa jointure. On sélectionne donc toutes les entrées de main_table et on les joint la table joined_table qu’il y ait une correspondance ou non. C’est implicitement un LEFT OUTER JOIN.

SELECT * FROM main_table LEFT JOIN joined_table ON main_table.id = joined_table.main_id

RIGHT JOIN

RIGHT JOIN est une jointure par la droite, la table de droite étant la table jointe. On sélectionne donc toutes les entrées de main_table et on les joint la table joined_table qu’il y ait une correspondance ou non. C’est implicitement un RIGHT OUTER JOIN.

SELECT * FROM main_table RIGHT JOIN joined_table ON main_table.id = joined_table.main_id

INNER JOIN

Le mot INNER correspond à l’intersection entre les deux tables, cette jointure rend donc la correspondance nécessaire pour obtenir des résultats. Cette jointure permet de récupérer tous les résultats depuis la table main table uniquement si elle possède une correspondance dans joined_table.

SELECT * FROM main_table INNER JOIN joined_table ON main_table.id = joined_table.main_id

FULL OUTER JOIN

OUTER signifie lui que nous souhaitons récupérer l’union des 2 table, en opposition avec INNER JOIN. LEFT JOIN et RIGHT JOIN sont déjà implicitement des LEFT OUTER JOIN et RIGHT OUTER JOIN. Mais à cela vient s’ajouter le FULL OUTER JOIN, qui nous permet de sélectionner toutes les entrées de main_table jointes à la table joined_table qu’il y ait une correspondance ou non. On récupère donc toutes les entrées des deux tables, rien n’est exclu.
Cas d’usage
En pratique, on utilise généralement les LEFT Join et les INNER Join mais il est fréquent de les voir mal utilisés, parfois inversés, il est important de savoir au préalable si on souhaite récupérer toutes les entrées de main_table ou seulement celles ayant une correspondance avec joined_table.