Cyrille Giquello

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== SQL ======

Voir aussi [[/informatique/SGBD|/informatique/SGBD]]

===== Articles =====

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]]


===== 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) ====

[[http://sqlpro.developpez.com/cours/arborescence/|Gestion d'arbres par représentation intervallaire]]

There are categories and sub-categories (with apparently unlimited sub-categories).

If by this you many there are arbitrarily many levels of category nesting, you can't grab them all with a single query using the ‘parent reference’ schema model. You would have to repeatedly query the children of each category you found, recursively, or just keep doing longer and longer self-joins until you've picked up the most-deeply-nested items.

For single-query access to hierarchical data you will have to look into alternative ways of structuring the schema, principally:

    * Nested Sets: stores a numerical ordering of a recursive walk over the tree
    * Adjacency Relation: adds extra ancestor/descendent pairs to your child/parent lookup

Either or both of these approaches, in one of their flavours, may work for you. (I usually end up with Nested Sets so that I get an explicit first-to-last order as well.)

[[http://www.sitepoint.com/print/hierarchical-data-database/|Storing Hierarchical Data in a Database]] By Gijs Van Tulder April 30th 2003

[[http://dev.mysql.com/tech-resources/articles/hierarchical-data.html|Managing Hierarchical Data in MySQL]] By Mike Hillyer (dev.mysql.com)

[[http://www.phpclasses.org/browse/package/4947.html|PHP Cat Nested Model Category Class]]\\
This class can be used to manage an hierarchy of categories stored in a MySQL database.\\
It can add, update and delete records of categories to a MySQL database table.\\
It can also retrieve categories under a given category, get top categories, get the largest category and get all categories.

[[http://vadimtropashko.wordpress.com/nested-sets-and-relational-division/|Databases Nested Sets and Relational Division]]


==== Manipulons ce qui n'est pas ====

ou encore: sélection des lignes orphelines.

**Question**

I've got a SELECT query that I would like to transform as a DELETE query ,
but I could not write that DELETE query ;o{

Here is the case :

In a search engine, I would like to erase all words that are no more attached to a feedback entry.

table Words ( IID, Word )
table Feedbacks_has_Words ( Feedbacks_IID, Words_ID )
table Feedbacks( IID, .... )

When a word has no more feedback attached (relation is table Feedbacks_has_Words) ,
it should be deleted.

To find such words, this SELECT query works fine :

<code sql>
 select IID,Word, Feedbacks_IID
 from Words
 LEFT JOIN Feedbacks_has_Words
 on IID = Words_IID
 where Feedbacks_IID is null
</code>
I could not figure how to right the DELETE Query ...
Have you got a idea about it ???

**Answer**

  DELETE FROM Words WHERE IID NOT IN
  ( SELECT Words_ID FROM Feedbacks_has_Words )

**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>

==== Insertions ====

**Question**

I've to add some words in a dictionnary table.
Words are managed with an Unique Index.

I think about 2 ways :

1/ Make a first query to look if the word already exists, and insert it if not exists.

or

2/ Directly insert the word and catch the exception if the word already exists.

I think the second way is faster/lighter ...
Is it true ? What do you think about that ? 

**Answer**

there are other alternatives

INSERT IGNORE .... --> (Scroll down for it :) )http://dev.mysql.com/doc/refman/5.0/en/insert.html

INSERT ... ON DUPLICATE KEY UPDATE --> http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

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 =====

==== 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 ====

http://www.torasql.com

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, Mac OS X, MS Windows, and UNIX platforms.

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