Différences

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

--- informatique:sql [25/10/2015 12:15] – [Articles] cyrille
+++ informatique:sql [09/04/2022 22:10] – [Tora] cyrille
@@ Ligne 7: / Ligne 7: @@
 Cours SQL:
   * [[http://cerig.pagora.grenoble-inp.fr/tutoriel/bases-de-donnees/sommaire.htm|Les bases de données relationnelles]] par Jean-Claude SOHM avec notamment [[http://cerig.pagora.grenoble-inp.fr/tutoriel/bases-de-donnees/chap20.htm|Chap 20]], [[http://cerig.pagora.grenoble-inp.fr/tutoriel/bases-de-donnees/chap21.htm|Chap 21]]
+  * [[https://www.dofactory.com/sql/tutorial|Sql Tutorial]] sur dofactory.com
+    * [[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
 ==== Données Hiérarchiques (Nested Categories) ====
@@ Ligne 83: / Ligne 90: @@
 ==== Insertions ====
 **Question**
@@ Ligne 111: / Ligne 117: @@
 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>
 ===== Tools =====
 ==== 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 124: / Ligne 164: @@
 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