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édente | ||
informatique:sql [19/05/2012 00:18] – modification externe 127.0.0.1 | informatique:sql [13/04/2024 14:22] (Version actuelle) – [Manipulons ce qui n'est pas] typo 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 46: | Ligne 56: | ||
ou encore: sélection des lignes orphelines. | 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 , | I've got a SELECT query that I would like to transform as a DELETE query , | ||
Ligne 74: | Ligne 84: | ||
Have you got a idea about it ??? | Have you got a idea about it ??? | ||
- | === Answer | + | **Answer** |
DELETE FROM Words WHERE IID NOT IN | DELETE FROM Words WHERE IID NOT IN | ||
( SELECT Words_ID FROM Feedbacks_has_Words ) | ( 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, | ||
+ | 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; | ||
+ | </ | ||
+ | |||
+ | ==== Insertions ==== | ||
**Question** | **Question** | ||
Ligne 108: | Ligne 134: | ||
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 ==== | ||
Ligne 121: | Ligne 181: | ||
In addition to regular query and data browsing functionality, | 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