Requêtes de manipulation de données

Utiliser le caractère d'échappement dans une requête

Lorqu'on utilise le caractère d'échappement (\) dans une requête, il faut prendre quelques précautions. En effet, il faut le doubler pour pouvoir l'utiliser car le parseur en enlève un.

-- Ne fonctionne pas
SELECT 1 FROM <table> WHERE <column> = '1\1'
INSERT INTO <table> (<column>) VALUES ('1\1')
-- Fonctionne
SELECT 1 FROM <table> WHERE <column> = '1\\1'
INSERT INTO <table> (<column>) VALUES ('1\\1')

Lorsqu'on utilise la méthode de recherche LIKE, il faut prendre des précautions supplémentaires. En effet, il faut le quadrupler car le parseur en enlève un et le motif également. On peut utiliser un caractère d'échappement différent et dans ce cas, il ne faut que le doubler.

-- Ne fonctionne pas
SELECT 1 FROM <table> WHERE <column> LIKE '%\%'
SELECT 1 FROM <table> WHERE <column> LIKE '%\\%'
SELECT 1 FROM <table> WHERE <column> LIKE '%\\\%'
-- Fonctionne
SELECT 1 FROM <table> WHERE <column> LIKE '%\\\\%'
SELECT 1 FROM <table> WHERE <column> LIKE '%\\%' ESCAPE '|'

Extrait de la documentation :

Because MySQL uses C escape syntax in strings (for example, \n to represent a newline character), you must double any \ that you use in LIKE strings. For example, to search for \n, specify it as \\n. To search for \, specify it as \\\\; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

Calculer une moyenne géométrique

Il n'existe pas de fonction d'agrégation pour réaliser cela facilement. Cependant, en utilisant les mathématiques décrites dans cet article, on peut la calculer nous même.

SELECT EXP(AVG(LN(<column>)))
FROM <table>

Afficher le numéro de ligne

Il n'existe pas de pseudo-colonne pour numéroter les lignes comme il en existe sur la base de données Oracle. On peut cependant recréer cette pseudo-colonne. Ça surcharge la requête, mais ça peut être utile si on veut utiliser le résultat tel quel.

SELECT @rownum := @rownum + 1 AS rownum
, <column>
FROM <table>
, (SELECT @rownum := 0) AS <alias>

Pour plus d'information, voir ici

Il est également possible d'introduire des sauts dans la numérotation pour, par exemple, numéroter les lignes par groupes :

SELECT a
, b
, @num := IF(@a = a, @num + 1, 1) AS rownum
, @a := a
FROM <table>
, (SELECT @a := '', @num := 1) AS <alias>

Pour plus d'information, voir ici

Lors de l'écriture de ce genre de requêtes dans Talend, il faut faire attention à choisir des noms de variables en minuscule.

Mise à jour d'une table avec des données variables

Quand on veut mettre à jour une table avec des données provenant d'une requête, voici la structure de la requête à utiliser. On retrouve facilement cette syntaxe en cherchant sur le web ou en testant rapidement, mais c'est encore mieux de savoir où trouver l'information rapidement quand ce n'est pas quelque chose que l'on fait régulièrement.

UPDATE <table-1>
SET <table-1>.<column-1> = (
    SELECT <table-2>.<column-1>
    FROM <table-2>
    WHERE <table-1>.id = <table-2>.id
)
WHERE EXISTS (
    SELECT 1
    FROM <table-2>
    WHERE <table-1>.id = <table-2>.id
)

Après avoir utilisé cette syntaxe depuis pas mal de temps, je viens de me rendre compte qu'il existe une syntaxe beaucoup plus facile et surtout qui autorise la mise à jour de plus d'un champ à la fois. La cerise sur le gâteau est qu'elle autorise l'utilisation d'alias de tables.

UPDATE <table-1>, <table-2>
SET <table-1>.<column-1> = <table-2>.<column-1>
, <table-1>.<column-2> = <table-2>.<column-2>
WHERE <table-1>.id = <table-2>.id

Créer une table à partir d'une autre table

Méthode 1 :

-- Copie de la structure et des données
CREATE TABLE <new_table>
AS
SELECT *
FROM <old_table>
 
-- Copie de la structure uniquement
CREATE TABLE <new_table>
AS
SELECT *
FROM <old_table>
LIMIT 0

Méthode 2 :

-- Copie de la structure uniquement
CREATE TABLE <new_table>
LIKE <old_table>

Lors de l'utilisation de la méthode 1, on perd l'ensemble des index de la table ainsi que certaines propriétés (auto_increment, comment). La méthode 2 n'a pas ce problème.

Changer la base d'un nombre

/* De binaire en octal */
SELECT CONV( '101101' , 2, 8 ) -- Retourne 55
 
/* De binaire en décimal */
SELECT CONV( '101101' , 2, 10 ) -- Retourne 45
 
/* De binaire en héxadécimal */
SELECT CONV( '101101' , 2, 16 ) -- Retourne 2D

La fonction CONV supporte les bases comprises entre 2 et 36.

Générer les requêtes pour déplacer les tables d'une base à une autre

Pour déplacer des tables d'une base à une autre, on peut passer par un export des données de la base source puis par un import des données dans la base cible. Mais ces actions sont très longues. Si les bases source et cible peuvent communiquer entre-elles (par exemple, elles sont sur le même serveur), il est possible de renommer les tables en ne changeant que le nom de la base de données.

RENAME TABLE `<source>`.`<table>`
TO `<destination>`.`<table>`

Si la table de la base d'origine existe déjà dans la base cible, celle-ci n'est pas déplacée.

Si le nombre de tables à déplacer est élevé, il est intéressant de générer la liste les requêtes dynamiquement. Par exemple, pour déplacer toutes les tables d'une base vers une autre :

-- Initialisation des variables
SET @orig:='<source>';
SET @dest:='<destination>';
 
-- Génération des requêtes
SELECT CONCAT('RENAME TABLE ', REPLACE(NAME, CONCAT(@orig, '/') ,CONCAT('`', @orig, '`.`')), '` TO ', REPLACE(NAME, CONCAT(@orig, '/'), CONCAT('`', @dest, '`.`')),'`;')
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME LIKE CONCAT(@orig, '/%');

Cette méthode de déplacement de table ne fonctionne qu'en innoDB.

Filtre multiple

SELECT * FROM <table1>
WHERE (<table1>.<column1>, <table1>.<column2>) IN (
    SELECT <table2>.<column1>, <table2>.<column2> FROM <table2>
)

Filtrer des données accentuées

-- Retourne l'ensemble des valeurs contenant "e", "é", "è", "E", "É", "È", …
SELECT *
FROM <table>
WHERE <column> LIKE '%e%'
 
-- Retourne uniquement les valeurs contenant "e" et "E"
SELECT *
FROM <table>
WHERE LOWER(<field>) LIKE '%e%' COLLATE utf8_bin
 
-- Retourne uniquement les valeurs contenant "e"
SELECT *
FROM <table>
WHERE <field> LIKE '%e%' COLLATE utf8_bin