Ceci est une ancienne révision du document !

Requêtes SQL

SELECT

SELECT champ1, champ2
    FROM TABLE
    WHERE condition
    ORDER BY champ1 ASC, champ2 DESC;

Doc : Oracle

Gérer valeur NULL

Utiliser la fonction standard COALESCE : 

-- Téléphone fixe, ou mobile sinon
SELECT COALESCE (cli.telephonefixe, cli.telephonemobile) AS telephone
FROM clients

Doc : Wikibooks, MS SQL

Chez Oracle il existe aussi une fonction non standard Doc : NVL

Switch Case

SELECT
    CASE 
        WHEN prix<10 THEN 'Moins de 10 €'
        WHEN prix<20 THEN 'Entre 10 € et 20 €'
        WHEN prix<40 THEN 'Entre 20 € et 40 €'
        ELSE 'Plus de 40 €'
    END
FROM produits;

Doc : Oracle

Portion de chaîne : SUBSTR

SELECT
	-- Ne garder que les 3e et 4e caractères du code
	SUBSTR(codecoloris, 3, 2) AS ancien_coloris,
FROM produits_coloris

Doc : Oracle

Pour le faire selon une expression régulière : REGEXP_SUBSTR chez Oracle, SUBSTRING w/ POSIX Regular Expressions chez PostgreSQL

Portion avant et après un caractères séparateur : 

SELECT
	SUBSTR(pam.codeelementligne, 0                                       , INSTR(pam.codeelementligne, '|', 1, 1)-1), 
	SUBSTR(pam.codeelementligne, INSTR(pam.codeelementligne, '|', 1, 1)+1, LENGTH(pam.codeelementligne))
FROM xanaka.lignes_perimetre_action_mkt pam

Transtypages

Nombre -> texte

Fonctions CAST ou TO_CHAR 

SELECT
	CAST(nombre AS VARCHAR2(255)) 	AS idtexte,
FROM latable
SELECT
	TO_CHAR(nombre, 'FM990.09') 	AS idtexte,
FROM latable
-- 139,95 -> 139.95
--   7    ->   7.0

Doc : Oracle CAST ou TO_CHAR

Date -> texte

Oracle

SELECT
	TO_CHAR(jour, 'dd/mm/yyyy HH24:MI')
FROM latable
-- Donne par exemple : '04/07/2012 13:37'

Doc : Oracle et formats

MySQL

SELECT DATE_FORMAT(d.date_der_exe,'%Y%m%d%H%i%S')
FROM latable
-- Donne par exemple : '20160719010041

Doc : MySQL

MS SQL Server

SELECT CONVERT(VARCHAR, LOGDate, 103)  
FROM latable
-- Donne par exemple : '28/03/2020'

Doc : CAST and CONVERT (Transact-SQL)

Date -> numéro de semaine

SELECT jour, TO_CHAR(jour, 'iw')
FROM latable
-- Donne par exemple : 2015-04-28 => 18

DateTime -> Date

Microsoft SQL Server

SELECT CAST([DateTime] AS DATE) AS jour
FROM TABLE

Source : https://stackoverflow.com/a/923322

Texte -> date

Fonction TO_DATE 

SELECT * FROM latable
WHERE datedebut = TO_DATE('2012-02-15', 'yyyy-mm-dd')

Doc : Oracle

Microsoft SQL Server

CAST('17/07/2019 00:00:00' AS date) 

SELECT * FROM latable
WHERE TOTAL_DATE = CAST('17/07/2019 00:00:00' AS DATE)

Doc : https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#g-using-cast-and-convert-with-datetime-data

IN

SELECT *
FROM clients
WHERE email IN ('test1@example.com', 'test2@example.org', 'test3@example.net')

Doc : Oracle

Avec dédoublonnage

S'il y a plusieurs comptes pour un email, n'extraire que le compte le plus récent : 

SELECT *
FROM  clients
WHERE codeclient IN (
    SELECT MAX(codeclient)
    FROM clients
    WHERE email IN ('test1@example.com', 'test2@example.org', 'test3@example.net')
    GROUP BY email
);

Agrégation

SELECT department_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY department_id;

Doc : Oracle

Plus petite valeur entre 2 colonnes

SELECT LEAST(prd.datemodification, art.datemodification)
FROM articles art
LEFT JOIN produits prd
	ON art.codeproduit = prd.codeproduit

Doc : Oracle

Plus grande valeur entre 2 colonnes

SELECT GREATEST(stk.stock, 0) AS stock,
FROM stock stk

Doc : Oracle

Pour la plus petite valeur, utiliser la fonction LEAST 

SELECT LEAST(datemodification, datecreation)
FROM matable

Doc : Oracle 10g

INSERT

INSERT INTO employees (employee_id, last_name, email,  hire_date, job_id, salary, commission_pct) 
   VALUES (207, 'Gregory', 'pgregory@oracle.com', sysdate, 'PU_CLERK', 1.2E3, NULL);

Doc : Oracle

Depuis une sous-requête

Si la table existe déjà, sinon utiliser creer_table_a_partir_d_une_requete 

INSERT INTO bonuses
   SELECT employee_id, salary*1.1 
   FROM employees
   WHERE commission_pct > 0.25;

Doc : Oracle

Plusieurs enregistrements en une requête

INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;

UPDATE

UPDATE unetable
	SET champ = valeur
	WHERE champ = valeur

Plusieurs champs, depuis une sous-requête : 

UPDATE tableA a 
    SET (champA1, champA2) = 
        (SELECT champB1, champB2 
          FROM tableB b 
          WHERE a.bid = b.id) 
    WHERE bid = 123;

Doc : Oracle

Remplacer caractères

Remplacer les points-virgules par des points dans une chaîne de caractères : 

UPDATE unetable
SET champ = REPLACE(champ , ';', '.')
WHERE champ LIKE '%;%'

Doc : Oracle

DELETE

DELETE FROM bonus
WHERE sales_amt < quota;

Doc : Oracle

Avec jointures

Avec Oracle, impossible d'utiliser des jointures comme habituellement. Voici la syntaxe à utilise : 

DELETE FROM refint.ecom_cmd_lignes_int l 
WHERE EXISTS (
  SELECT 1 FROM refint.ecom_cmd_entete_int t 
  WHERE t.num_commande = l.num_commande
	  AND t.idlot = l.idlot
	  AND t.etat_integration IS NULL
)
 
-- Pour supprimer les lignes suivantes :
SELECT * FROM refint.ecom_cmd_lignes_int l
INNER JOIN refint.ecom_cmd_entete_int t
ON t.num_commande = l.num_commande
	AND t.idlot = l.idlot
WHERE t.etat_integration IS NULL

CREATE TABLE

Clé primaire composée

CREATE TABLE tmp_photos_articles (
    codeinternearticle NUMBER(8),
    numvue NUMBER(1),
    cheminphoto varchar2(128),
    CONSTRAINT tmp_photos_articles_pk PRIMARY KEY (codeinternearticle, numvue)
);

Doc : Oracle

Créer table à partir d'une requête

Testé avec postgresql 9 et Oracle 10g : 

CREATE TABLE nom_table AS
    SELECT
        champ1 AS nom_colonne1,
        champ2 AS nom_colonne2,
        champ3 AS nom_colonne3
    FROM 
        tables_source
    WHERE
        ...

Doc : PostgreSQL

ALTER TABLE

ALTER TABLE nom_table
   ADD (nouveau1  NUMBER(6,0) DEFAULT 10267 NOT NULL,
        nouveau2  VARCHAR2(3)
   );

Documentations

  • memo/sql.1590145955.txt.gz
  • Dernière modification : il y a 6 ans
  • de Arnaud Jacquemin