[DragonSoft] [DS]

Développement Web / MySQL

 [DS] [JF] [OPi]
MySQL (fr) : MySQL 5.6 Reference Manual, 5.7, 8.0

CREATE TABLE

DELETE

[IGNORE]
FROM table
[WHERE conditions]
[ORDER BY tri]
[LIMIT nb]

INSERT

[IGNORE]
[INTO] table [(colonne, …)]
VALUES (expression, …)

INSERT

[IGNORE]
[INTO] table
SET colonne=expression, …

Jointure croisée (produit cartésien)


  1.  
  2. id_a
  3. 1
  4. 2
  5. 3

  1.  
  2. id_b
  3. 1
  4. 2
  5. 103
  6. 104
  1. SELECT * FROM `a` CROSS JOIN `b`;
    SELECT * FROM `a`, `b`;
  2. id_a  id_b
  3. 1      1
  4. 2      1
  5. 3      1
  6. 1      2
  7. 2      2
  8. 3      2
  9. 1      103
  10. 2      103
  11. 3      103
  12. 1      104
  13. 2      104
  14. 3      104

Jointure interne


  1.  
  2. id_a
  3. 1
  4. 2
  5. 3

  1.  
  2. id_b
  3. 1
  4. 2
  5. 103
  6. 104
  1. SELECT * FROM `a` INNER JOIN `b` ON `a`.`id_a`=`b`.`id_b`;
    SELECT * FROM `a`, `b` WHERE `a`.`id_a`=`b`.`id_b`;   (cette syntaxe n’est pas dans la norme ANSI)
  2. id_a  id_b
  3. 1      1
  4. 2      2

Jointure externe gauche

  1.  
  2. id_a
  3. 1
  4. 2
  5. 3
  1.  
  2. id_b
  3. 1
  4. 2
  5. 103
  6. 104
  1. SELECT * FROM `a` LEFT OUTER JOIN `b` ON `a`.`id_a`=`b`.`id_b`;
  2. id_a  id_b
  3. 1      1
  4. 2      2
  5. 3      NULL

Jointure externe droite

  1.  
  2. id_a
  3. 1
  4. 2
  5. 3
  1.  
  2. id_b
  3. 1
  4. 2
  5. 103
  6. 104
  1. SELECT * FROM `a` RIGHT OUTER JOIN `b` ON `a`.`id_a`=`b`.`id_b`;
  2. id_a  id_b
  3. 1      1
  4. 2      2
  5. NULL   103
  6. NULL   104

Jointure externe bilatérale (émulé par un UNION)



  1.  
  2. id_a
  3. 1
  4. 2
  5. 3


  1.  
  2. id_b
  3. 1
  4. 2
  5. 103
  6. 104
  1. SELECT * FROM `a` LEFT OUTER JOIN `b` ON `a`.`id_a`=`b`.`id_b`
    UNION
    SELECT * FROM `a` RIGHT OUTER JOIN `b` ON `a`.`id_a`=`b`.`id_b`;
  2. id_a  id_b
  3. 1      1
  4. 2      2
  5. 3      NULL
  6. NULL   103
  7. NULL   104

SELECT

[DISTINCT] expression[, …]
FROM table
[WHERE conditions]
[ORDER BY expression [DESC]]
[LIMIT [offset,] nb]

SELECT

[DISTINCT] expression[, …]
FROM table1
[INNER] JOIN table2
ON conditions
[WHERE conditions]
[ORDER BY expression [DESC]]
[LIMIT [offset,] nb]

SELECT

[DISTINCT] expression[, …]
FROM table1
LEFT|RIGHT [OUTER] JOIN table2
ON conditions
[WHERE conditions]
[ORDER BY expression [DESC]]
[LIMIT [offset,] nb]

UPDATE

[IGNORE]
table
SET colonne=expression, …
[WHERE conditions]
[ORDER BY tri]
[LIMIT nb]

[DragonSoft] [DS] dimanche 16 octobre 2022 [DS] [JF] [OPi]