informatique:sql
Différences
Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentesRévision précédenteProchaine révision | Révision précédenteProchaine révisionLes deux révisions suivantes | ||
informatique:sql [21/12/2010 14:13] – cyrille | informatique:sql [09/04/2022 22:01] – [HeidiSQL] cyrille | ||
---|---|---|---|
Ligne 5: | Ligne 5: | ||
===== Articles ===== | ===== Articles ===== | ||
- | Optimisation MySQL Par Laurent DECORPS \\ | + | Cours SQL: |
- | http://phpinfo.net/articles/article_optimisation-mysql.html# | + | * [[http://cerig.pagora.grenoble-inp.fr/tutoriel/bases-de-donnees/ |
+ | * [[https:// | ||
+ | * [[https:// | ||
- | Optimisation MySQL | ||
- | http:// | ||
- | Autres conseils d' | ||
- | http:// | ||
===== Tips ===== | ===== 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:// | ||
+ | * [[https:// | ||
==== Données Hiérarchiques (Nested Categories) ==== | ==== Données Hiérarchiques (Nested Categories) ==== | ||
Ligne 64: | Ligne 74: | ||
To find such words, this SELECT query works fine : | To find such words, this SELECT query works fine : | ||
- | select IID,Word, Feedbacks_IID | + | <code sql> |
- | | + | select IID,Word, Feedbacks_IID |
- | | + | from Words |
- | | + | LEFT JOIN Feedbacks_has_Words |
+ | on IID = Words_IID | ||
+ | where Feedbacks_IID is null | ||
+ | </ | ||
I could not figure how to right the DELETE Query ... | I could not figure how to right the DELETE Query ... | ||
Have you got a idea about it ??? | Have you got a idea about it ??? | ||
Ligne 78: | Ligne 90: | ||
==== Insertions ==== | ==== Insertions ==== | ||
- | |||
**Question** | **Question** | ||
Ligne 106: | Ligne 117: | ||
REPLACE --> http:// | REPLACE --> http:// | ||
+ | |||
+ | **pour les accès concurrents avec SELECT FOR UPDATE en SQL** | ||
+ | * https:// | ||
+ | * https:// | ||
+ | |||
+ | ==== Supprimer doublons ==== | ||
+ | |||
+ | https:// | ||
+ | |||
+ | Compter les doublons: | ||
+ | <code sql> | ||
+ | SELECT count(*) as DOUBLON, employee_id | ||
+ | FROM globalstatements | ||
+ | group by employee_id | ||
+ | having count(*) > 1 | ||
+ | limit 100000 | ||
+ | </ | ||
+ | |||
+ | 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 | ||
+ | ; | ||
+ | </ | ||
===== Tools ===== | ===== Tools ===== | ||
==== MySQL Workbench ==== | ==== MySQL Workbench ==== | ||
+ | [[/ | ||
==== HeidiSQL ==== | ==== HeidiSQL ==== | ||
+ | https:// | ||
+ | |||
+ | HeidiSQL runs fine on Windows 8 and 10 (and on Windows 7 + 11 with some minor issues). | ||
==== Tora ==== | ==== Tora ==== | ||
informatique/sql.txt · Dernière modification : 13/04/2024 14:22 de cyrille