Différences

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

--- informatique:sql [11/04/2020 15:01] – [Articles] cyrille
+++ informatique:sql [24/08/2025 09:12] (Version actuelle) – [Manipulons ce qui n'est pas] cyrille
@@ Ligne 10: / Ligne 10: @@
     * [[https://www.dofactory.com/sql/join|SQL Join]]
-Optimisation MySQL Par Laurent DECORPS \\
-http://phpinfo.net/articles/article_optimisation-mysql.html#
-Optimisation MySQL
-http://dev.mysql.com/doc/refman/5.0/fr/mysql-optimization.html\\
-Autres conseils d'optimisation\\
-http://dev.mysql.com/doc/refman/5.0/fr/tips.html
 ===== Tips =====
+==== Sql Join ====
+  * (INNER) JOIN: Select records that have matching values in both tables.
+  * LEFT (OUTER) JOIN: Select records from the first (left-most) table with matching right table records.
+  * RIGHT (OUTER) JOIN: Select records from the second (right-most) table with matching left table records.
+  * FULL (OUTER) JOIN: Selects all records that match either left or right table records.
+  * All INNER and OUTER keywords are optional
+Docs:
+  * [[https://sql.sh/cours/jointures|Jointure SQL]] pas Sql.sh
+  * [[https://www.dofactory.com/sql/join|{{https://www.dofactory.com/Images/sql-joins.png|SQL Join tutorial}}]] by Dofactory
+Et pour un ''update'' avec un ''join'' :
+<code sql>
+UPDATE `table_a` a
+ LEFT JOIN `table_b` b ON a.id = b.id
+ SET a.password = 'abcdef'
+ WHERE b.id = 195812
+</code>
 ==== Données Hiérarchiques (Nested Categories) ====
@@ Ligne 51: / Ligne 65: @@
 ou encore: sélection des lignes orphelines.
-=== Question ===
+**Question**
 I've got a SELECT query that I would like to transform as a DELETE query ,
@@ Ligne 79: / Ligne 93: @@
 Have you got a idea about it ???
-=== Answer ===
+**Answer**
   DELETE FROM Words WHERE IID NOT IN
   ( SELECT Words_ID FROM Feedbacks_has_Words )
-==== Insertions ====
+**Question**
+Avec une base de données sqlite avec 2 tables. Une table contient les stations métérologiques et une table contient les mesures de températures journalières pour ces stations. Comment trouver en SQL les dates et les stations pour lesquelles il manque des mesures ?
+**Réponse**
+<code sql>
+SELECT s.id, s.name AS nom_station, dates.measured_at
+FROM stations s
+CROSS JOIN (
+    SELECT DISTINCT measured_at FROM measures
+) AS dates
+LEFT JOIN measures m ON s.id = m.station_id AND dates.measured_at = m.measured_at
+WHERE m.station_id IS NULL
+ORDER BY s.id, dates.measured_at;
+</code>
+** Question **
+Dans la bdd de [[https://www.sympa.community/|Sympa]], comment supprimer les abonnés à des listes qui n'existent pas/plus ?
+Et contourner l'erreur "Error 1093: You can't specify target table for update in FROM clause".
+** Réponse **
+<code sql>
+delete T1 FROM subscriber_table as T1 join (
+ select st.list_subscriber from subscriber_table st
+ left join list_table lt on lt.name_list = st.list_subscriber
+ where lt.name_list is null
+ group by st.list_subscriber
+) as T2 on T2.list_subscriber = T1.list_subscriber ;
+</code>
+==== Insertions ====
 **Question**
@@ Ligne 112: / Ligne 159: @@
 REPLACE --> http://dev.mysql.com/doc/refman/5.0/en/replace.html
+**pour les accès concurrents avec SELECT FOR UPDATE en SQL**
+  * https://www.bortzmeyer.org/select-for-update.html
+  * https://mariadb.com/kb/en/for-update/
+==== Supprimer doublons ====
+https://sql.sh/138-methode-supprimer-doublons
+Compter les doublons:
+<code sql>
+SELECT count(*) as DOUBLON, employee_id
+FROM globalstatements
+group by employee_id
+having count(*) > 1
+limit 100000
+</code>
+Les supprimer:
+<code sql>
+delete G1 from globalstatements G1
+LEFT OUTER JOIN (
+        SELECT MIN(id) as id, employee_id
+        FROM globalstatements
+        GROUP BY employee_id
+    ) as G2
+    ON G1.id = G2.id
+WHERE G2.id IS NULL
+;
+</code>
+==== Supprimer dans plusieurs tables ====
+Supprimer les lignes dans les tables wp2r_posts et wp2r_postmeta :
+<code sql>
+DELETE m , p
+FROM wp2r_posts p INNER JOIN wp2r_postmeta m
+WHERE m.post_id= p.ID
+and p.post_type="custom_post_type"
+</code>
 ===== Tools =====
@@ Ligne 117: / Ligne 207: @@
 ==== MySQL Workbench ====
+[[/informatique/sgbd/mysql#mysql_workbench]]
 ==== HeidiSQL ====
+https://www.heidisql.com
+HeidiSQL runs fine on Windows 8 and 10 (and on Windows 7 + 11 with some minor issues).
 ==== Tora ====
@@ Ligne 126: / Ligne 220: @@
 In addition to regular query and data browsing functionality, it includes several additional tools useful for database administrators and developers – which aims to help the DBA or developer of database application. Features PL/SQL debugger, SQL worksheet with syntax highlighting, DB browser and a comprehensive set of DBA tools.
+==== DB Browser for SQLite ====
+https://sqlitebrowser.org