Mon wiki

Outils pour utilisateurs

Outils du site

Vous êtes ici : start » informatique » mysql » requetes_donnees
informatique:mysql:requetes_donnees

Table des matières

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

Requêtes amusantes

Multiplier le nombre de lignes d'une table

Cette requête permet de multiplier le nombre d'enregistrements d'une table en fonction d'un multiplicateur de cette même table. Dans l'exemple suivant, on ne pourra pas générer plus de 9 lignes. Il suffit d'augmenter le nombre de UNION pour pouvoir générer plus d'enregistrements. 

SELECT id
, multiplier
FROM <table>
, (
    SELECT 0 AS `rank`
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
) AS multiplier
WHERE multiplier.`rank` < <table>.multiplier

Il est possible de se passer des UNION en remplaçant la sous-requête par une table contenant les nombres de 0 à n. 

SELECT id
, multiplier
FROM <table>
, multiplier
WHERE multiplier.`rank` < <table>.multiplier

Trier des numéros de version

Cette requête permet de trier des numéros de versions (5, 4.1, 3.2.5, …) entre eux. 

SELECT version
, @version := IF(LOCATE('.',version) = 0, CONCAT(version, '.0.0'), version) -- Ajoute les versions mineure et hotfix quand on a qu'une version majeure
, @version := IF(@version REGEXP '^.+\..+\..+$' = 1, @version , CONCAT(@version, '.0')) -- Ajoute la version hotfix quand on a qu'une version mineure
, CONCAT(
 LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(@version, '.', 1), '.', -1), 10, '0'), -- Extrait la version majeure et la préfixe avec des 0
 LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(@version, '.', 2), '.', -1), 10, '0'), -- Extrait la version mineure et la préfixe avec des 0
 LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(@version, '.', 3), '.', -1), 10, '0')  -- Extrait la version hotfix et la préfixe avec des 0
) AS concat
FROM docs
ORDER BY concat DESC

Limitations :

  • Cette requête ne permet de gérer que des versions dont le format correspond au versionnage sémantique.
  • Seules les versions ne contenant que des chiffres sont supportées.
  • Les sections du numéro de version ne peuvent pas excéder 10 caractères.

Récupération des "trous" dans une séquence

Récupération sous la forme de plages d'identifiants

Cette requête ne permet pas de récupérer le premier “trou” de séquence si celui-ci commence au premier identifiant. 

SELECT range_start
, range_end
FROM (
	SELECT  @rownum1 := @rownum1 + 1 AS rownum_end
	, table1.id - 1 AS range_end
	FROM <table> AS table1
	JOIN (SELECT @rownum1 := 0) AS dummy
	LEFT JOIN <table> AS table2 ON table2.id = table1.id - 1
	WHERE table2.id IS NULL
	AND table1.id <> (SELECT MIN(id) FROM <table>)
) AS e
, (
	SELECT @rownum2 := @rownum2 + 1 AS rownum_start
	, table1.id + 1 AS range_start
	FROM <table> AS table1
	JOIN (SELECT @rownum2 := 0) AS dummy
	LEFT JOIN <table> AS table2 ON table2.id = table1.id + 1
	WHERE table2.id IS NULL
	AND table1.id <> (SELECT MAX(id) FROM <table>)
) AS s
WHERE rownum_end = rownum_start

Récupération de la liste complète des identifiants 

SELECT dummy.`row`
FROM (
	SELECT @ROW := @ROW + 1 AS `row`
	FROM 
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy1,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy2,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy3,
		(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy4,
		(SELECT @ROW := 0) numbers
	WHERE @ROW <= (SELECT MAX(id) FROM <table>)
) dummy
LEFT JOIN <table> AS table1 ON table1.id = dummy.`row`
WHERE table1.id IS NULL

Générer une liste de 1000 dates

Cette méthode peut être étendue pour générer plus de dates ou au contraire restreinte pour en générer moins. Dans l'exemple suivant, la date de départ sera la plus petite date, mais il est possible qu'elle devienne la plus grande en changeant le signe de l'opération. Pour que les dates soient ordonnées, la requête units doit impérativement être avant la requête tens qui elle même doit impérativement être avant la requête hundreds. 

SELECT '2013-04-01' + INTERVAL (hundreds.val + tens.val + units.val) DAY AS DAY
FROM (
    SELECT 0 AS val
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
) AS units
CROSS JOIN (
    SELECT 0 AS val
    UNION ALL SELECT 10
    UNION ALL SELECT 20
    UNION ALL SELECT 30
    UNION ALL SELECT 40
    UNION ALL SELECT 50
    UNION ALL SELECT 60
    UNION ALL SELECT 70
    UNION ALL SELECT 80
    UNION ALL SELECT 90
) AS tens
CROSS JOIN (
    SELECT 0 AS val 
    UNION ALL SELECT 100
    UNION ALL SELECT 200
    UNION ALL SELECT 300
    UNION ALL SELECT 400
    UNION ALL SELECT 500
    UNION ALL SELECT 600
    UNION ALL SELECT 700
    UNION ALL SELECT 800
    UNION ALL SELECT 900
) AS hundreds

Générer une liste de nombre

Cette requête permet de générer une liste de 10000 valeurs consécutives. Pour avoir plus de valeur, il faut ajouter une ligne identique aux lignes dummy existantes. 

SELECT @ROW := @ROW + 1 AS `row`
FROM 
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy1,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy2,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy3,
	(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS dummy4,
	(SELECT @ROW := 0) numbers

Quine

Une quine est un programme sans entrée qui génère son propre code source. 

SELECT REPLACE(REPLACE('SELECT REPLACE(REPLACE("$",CHAR(34),CHAR(39)),CHAR(36),"$") AS Quine',CHAR(34),CHAR(39)),CHAR(36),'SELECT REPLACE(REPLACE("$",CHAR(34),CHAR(39)),CHAR(36),"$") AS Quine') AS Quine
Code ASCII Caractère
34 “
36 $
39 '
informatique/mysql/requetes_donnees.txt · Dernière modification : 2022/09/23 13:56 de alexis