Différences

Ci-dessous, les différences entre deux révisions de la page.

--- informatique:databases:mysql:requetes_donnees [2024/11/21 18:37] – supprimée - modification externe (Date inconnue) 127.0.0.1
+++ informatique:databases:mysql:requetes_donnees [2024/11/21 18:54] (Version actuelle) – alexis
@@ Ligne 1: / Ligne 1: @@
+====== 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.
+<WRAP prewrap>
+<code sql>
+-- 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')
+</code>
+</WRAP>
+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.
+<WRAP prewrap>
+<code sql>
+-- 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 '|'
+</code>
+</WRAP>
+<WRAP info>
+Extrait de la [[https://dev.mysql.com/doc/refman/5.6/en/string-comparison-functions.html|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.
+</WRAP>
+===== 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 [[http://www.datagenetics.com/blog/april42014/index.html|cet article]], on peut la calculer nous même.
+<WRAP prewrap>
+<code sql>
+SELECT EXP(AVG(LN(<column>)))
+FROM <table>
+</code>
+</WRAP>
+===== 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.
+<WRAP prewrap>
+<code sql>
+SELECT @rownum := @rownum + 1 AS rownum
+, <column>
+FROM <table>
+, (SELECT @rownum := 0) AS <alias>
+</code>
+</WRAP>
+Pour plus d'information, voir [[http://jimmod.com/blog/2008/09/displaying-row-number-rownum-in-mysql/|ici]]
+Il est également possible d'introduire des sauts dans la numérotation pour, par exemple, numéroter les lignes par groupes :
+<WRAP prewrap>
+<code sql>
+SELECT a
+, b
+, @num := IF(@a = a, @num + 1, 1) AS rownum
+, @a := a
+FROM <table>
+, (SELECT @a := '', @num := 1) AS <alias>
+</code>
+</WRAP>
+Pour plus d'information, voir [[http://www.xaprb.com/blog/2006/12/02/how-to-number-rows-in-mysql/|ici]]
+<WRAP alert>
+Lors de l'écriture de ce genre de requêtes dans Talend, il faut faire attention à choisir des noms de variables en minuscule.
+</WRAP>
+===== 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.
+<WRAP prewrap>
+<code sql>
+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
+)
+</code>
+</WRAP>
+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.
+<WRAP prewrap>
+<code sql>
+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
+</code>
+</WRAP>
+===== Créer une table à partir d'une autre table =====
+  * Méthode 1 :
+<WRAP prewrap>
+<code sql>
+-- 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
+</code>
+</WRAP>
+  * Méthode 2 :
+<WRAP prewrap>
+<code sql>
+-- Copie de la structure uniquement
+CREATE TABLE <new_table>
+LIKE <old_table>
+</code>
+</WRAP>
+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 =====
+<WRAP prewrap>
+<code sql>
+/* 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
+</code>
+</WRAP>
+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.
+<WRAP prewrap>
+<code sql>
+RENAME TABLE `<source>`.`<table>`
+TO `<destination>`.`<table>`
+</code>
+</WRAP>
+<WRAP info>
+Si la table de la base d'origine existe déjà dans la base cible, celle-ci n'est pas déplacée.
+</WRAP>
+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 :
+<WRAP prewrap>
+<code sql>
+-- 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, '/%');
+</code>
+</WRAP>
+<WRAP important>
+Cette méthode de déplacement de table ne fonctionne qu'en innoDB.
+</WRAP>
+===== Filtre multiple =====
+<WRAP prewrap>
+<code sql>
+SELECT * FROM <table1>
+WHERE (<table1>.<column1>, <table1>.<column2>) IN (
+    SELECT <table2>.<column1>, <table2>.<column2> FROM <table2>
+)
+</code>
+</WRAP>
+===== Filtrer des données accentuées =====
+<WRAP prewrap>
+<code sql>
+-- 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
+</code>
+</WRAP>