Par exemple, vous avez une donnée en base, et pour vos tests, vous en voudriez 10 000.

Il est possible d’utiliser cette boucle SQL pour faire 10 000 insertions :

-- Copie d’une ligne n fois
--
-- Paramètres modifiables :
--   - nb de fois (ici 10000)
--   - table concernée (ici my_table)
--   - liste des colonnes à copier (ici column_1, column_2, column_x)
--   - id de la ligne à copier (ici 1)

DO
$$
    DECLARE
        i integer;
    BEGIN
        FOR i IN 1..10000
            LOOP
                INSERT INTO public.my_table (column_1, column_2, column_x)
                SELECT column_1, column_2, column_x
                FROM public.my_table
                WHERE id = 1;
            END LOOP;
    END
$$ LANGUAGE plpgsql;

Pour générer cette requête avec toutes les colonnes de votre table, vous pouvez faire générer l’INSERT directement à PostgreSQL, avec la requête suivante :

-- Génération de la requête d’insertion avec toutes les colonnes sauf la colonne id (car auto-incrémentale)
--
-- Paramètres modifiables :
--   - table concernée (ici my_table)
--   - id de la ligne à copier (ici 1)
WITH cols AS (SELECT column_name, ordinal_position
              FROM information_schema.columns
              WHERE table_schema = 'public'
                AND table_name = 'my_table'
                AND column_name NOT IN ('id')
              ORDER BY ordinal_position)
SELECT format(
               'INSERT INTO %I.%I (%s) SELECT %s FROM %I.%I WHERE id = 1;',
               'public',
               'my_table', string_agg(quote_ident(column_name), ', '), string_agg(quote_ident(column_name), ', '),
               'public', 'my_table') AS sql

Un dernier problème se pose si certaines colonnes doivent contenir des données uniques.
Dans les INSERT précédents, on ne gère que le cas de la colonne id, supposée auto-incrémentale. Pour générer des données pour d’autres colonnes, remplacez la requête précédente par celle-ci :

-- Génération de la requête de copie avec toutes les colonnes sauf celle contenant des ID uniques
--
-- Paramètres modifiables :
--   - table concernée (ici my_table)
--   - liste des colonnes pour lesquelles générer des uuid uniques (ici 'uuid1', 'uuid2')
--   - id de la ligne à copier (ici 1)
WITH cols AS (SELECT column_name, ordinal_position
              FROM information_schema.columns
              WHERE table_schema = 'public'
                AND table_name = 'my_table'
                AND column_name NOT IN ('id', 'uuid1', 'uuid2')
              ORDER BY ordinal_position)
SELECT format(
               'INSERT INTO %I.%I (uuid1, uuid2, %s) SELECT gen_random_uuid(), gen_random_uuid(), %s FROM %I.%I WHERE id = 1;',
               'public',
               'my_table', string_agg(quote_ident(column_name), ', '), string_agg(quote_ident(column_name), ', '),
               'public', 'my_table') AS sql

Note : si les valeurs uniques ne sont pas des UUID, il faut rechercher si PostgreSQL propose d’autres fonctions de génération aléatoires que gen_random_uuid().

On a parfois besoin de supprimer une entrée en base de donnée, mais qui est potentiellement référencées dans plusieurs autres tables. Plutôt que d’exécuter la requête de suppression et de corriger/supprimer une par une les données qui référencent et interdisent la suppression, on peut rechercher toutes les références possibles dans les autres tables.

-- Liste de toutes les clés étrangères vers la colonne `id` de `table1`
SELECT *
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_NAME = 'table1'
  AND REFERENCED_COLUMN_NAME = 'id';

Si jamais vous avez perdu le mot de passe root de MySQL, il est possible de le réinitialiser, ou d'exécuter n'importe quelle commande SQL nécessitant normalement des droits administrateur.

Pour cela :

• Rendez-vous dans le répertoire MySQL (par exemple D:\Dev\wamp\bin\mysql\mysql5.5.24)

• Créez un fichier SQL contenant les requêtes à effectuer. Par exemple pour réinitialiser le mot de passe root :

  UPDATE mysql.user SET Password = PASSWORD ('nouveau_mot_de_passe') WHERE User = 'root';
  FLUSH PRIVILEGES;

• Arrêtez le service MySQL s'il est lancé

• Ouvrez une invite de commande et déplacez-vous dans le répertoire MySQL

• Lancez la commande suivante :

  bin\mysqld.exe --defaults-file="D:\Dev\wamp\bin\mysql\mysql5.5.24\my.ini" --init-file="D:Dev\wamp\bin\mysql\mysql5.5.24\my_sql_script.sql" --console

Explications :

• Modifiez la requête SQL pour définir le mot de passe que vous souhaitez utiliser
• La commande mysqld doit recevoir 2 paramètres valués :
  • Le fichier de configuration de MySQL à utiliser (prendre celui déjà existant)
  • Le fichier SQL d'initialisation à lancer au démarrage (c'est lui qui contient votre fameuse requête)

Se connecter

Pour pouvoir exporter/importer une base de données PostgreSQL vous devrez sans doute vous connecter avec l'utilisateur système postgres.

Ex:

sudo su - postgres

Sauvegarder

Pour créer un dump de votre base de données utilisez la commande suivante :

pg_dump -U username -h localhost dbname > path/to/dump.sql

Par défaut :

• l'hôte utilisé est localhost
• le port utilisé est 5432

D'autres options sont possibles.

Remarque :

Même si c'est la valeur par défaut, il est parfois nécessaire de préciser l'hôte dans la commande, pour indiquer au client postgres que vous accédez à la base via une connexion TCP et non PEER.

Importer

La commande d'import est similaire à celle de dump :

psql -U username -h localhost dbname < path/to/dump.sql

Par défaut :

• l'hôte utilisé est localhost
• le port utilisé est 5432

Si vous utilisez phpMyAdmin pour sauvegarder une base de données, vous pouvez générer automatiquement le nom du fichier sql.

Vous pouvez définir par exemple un nom de la forme <nom_bdd>_<date>. Pour cela, utilisez le modèle de nom suivant :

@DATABASE@_%Y-%m-%d

Pour la base devnotebook, vous obtiendrez par exemple devnotebook_2013-02-14.sql.

Pour autoriser une machine distante à se connecter à votre base de données MySQL, exécutez la requête suivante :

GRANT ALL privileges ON my_db.* TO my_user@my_server IDENTIFIED BY 'my_password';

Explications :

• Dans cet exemple, l'autorisation d'accès concerne toutes les tables de la base my_db.
• Le mot nom d'utilisateur et le mot de passe sont ceux de l'utilisateur de la base de données à laquelle on pourra se connecter.
• Le serveur est la machine souhaitant se connecter à la base. Cela peut être son IP ou son nom.

Remarque : Si my_user doit être super-utilisateur, vous devez ajouter WITH GRANT OPTION à la requête :

GRANT ALL privileges ON my_db.* TO my_user@my_server IDENTIFIED BY 'my_password' WITH GRANT OPTION;

Il est arrivé que vous ayez une table avec deux champs indépendants, ne devant pas être remplis simultanément.

Par exemple pour un utilisateur : s'il est étudiant le champ formation est rempli et s'il travaille c'est le champ métier. Pour simplifier l'utilisation de ces données, vous voulez pouvoir récupérer un champ activité qui contient soit la formation, soit le métier.

En SQL, vous pouvez donc utiliser la fonction COALESCE() :

SELECT COALESCE( formation, metier ) AS activite FROM user;

Parmi les noms de champ en paramètres, la fonction COALESCE() retourne le premier non nul.

Sauvegarder

Pour créer un dump de votre base de données, utilisez la commande suivante :

mysqldump --host=localhost --port=3306 --databases database1_name database2_name --user=my_user --password=my_password > path/to/dump.sql

Par défaut :

• l'hôte utilisé est localhost
• le port utilisé est 3306

Vous devez spécifier la ou les bases de données à sauvegarder, ainsi que les login et mot de passe d'un utilisateur ayant le droit de consultation de la base.

Importer

La commande d'import est similaire à celle de dump :

mysql --host=localhost --port=3606 --user=my_user --password=my_password --default_character_set utf8 database_name < path/to/dump.sql

Le site OpenClassrooms (anciennement Site du Zéro) propose un cours très détaillé sur le Système de Gestion de Base de Donnée MySQL : https://openclassrooms.com/fr/courses/1959476-administrez-vos-bases-de-donnees-avec-mysql