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 [18/02/2010 14:48] – cyrille | informatique:sql [09/04/2022 22:10] – [Tora] 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 105: | Ligne 116: | ||
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 ===== | ||
+ | |||
+ | ==== MySQL Workbench ==== | ||
+ | |||
+ | [[/ | ||
+ | ==== HeidiSQL ==== | ||
+ | |||
+ | https:// | ||
+ | |||
+ | HeidiSQL runs fine on Windows 8 and 10 (and on Windows 7 + 11 with some minor issues). | ||
+ | ==== Tora ==== | ||
+ | |||
+ | http:// | ||
+ | |||
+ | TOra is an open-source multi-platform database management GUI that supports accessing most of the common database platforms in use, including Oracle, MySQL, and Postgres, as well as limited support for any target that can be accessed through Qt's ODBC support. TOra has been built for various Linux distributions, | ||
+ | |||
+ | In addition to regular query and data browsing functionality, | ||
+ | |||
+ | ==== DB Browser for SQLite ==== | ||
+ | |||
+ | https:// | ||
+ | |||
+ | |||
+ | |||
+ | |||
informatique/sql.txt · Dernière modification : 13/04/2024 14:22 de cyrille