Table des matières

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

Oracle : 

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

Doc : Oracle

MS SQL Server : 

SELECT
	-- Séparer heures et minutes
	SUBSTRING (a.actvissalheurearr, 1, 2) + ':' + SUBSTRING (a.actvissalheurearr, 3, 2) AS "Heure"
FROM ACTEVISITESALARIE a

Doc : https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver15

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

Oracle

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)

Ou en précisant le format : 

SELECT * FROM latable
WHERE DATE = CONVERT(DATE, '20210825', 112)

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

MySQL

SELECT STR_TO_DATE("2022/03/25", "%Y/%m/%d")

Doc : STR_TO_DATE(str,format) et formats

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

Plus grande valeur entre plusieurs lignes

Par exemple, ne garder d'une table que la ligne la plus récente pour chacun des utilisateurs… 

WITH nom_alias AS (
	SELECT *
		, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY datecreation DESC) AS POSITION
	FROM table_a_filtrer
)
SELECT
	 ...
FROM
	nom_alias f
INNER JOIN
	 ...
WHERE
	f.position = 1 -- uniquement dernier document
	AND ...
ORDER BY ...

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

Testé avec M$ SQL Server : 

SELECT *
INTO destination
FROM SOURCE;

Doc : https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql

ALTER TABLE

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

MSSQL : lier un utilisateur à une connexion

USE NomDelaBase
GO
 
ALTER USER Utilisateur WITH LOGIN = Connexion
GO

MSSQL : copier des tables d'un schéma à un autre

Use the Generate Scripts Wizard in SSMS for generating the script for constraints and indexes.

Follow these steps:

1. Open SSMS
2. Expand databases and select the database→tasks→generate scripts to launch the GSW
3. click next and set “Script Check Constraints” to true, “Script Indexes ” to true, “ScriptDependencies” to true and set whatever is needed.
4. Select the tables for which the script is needed
5. Click next →next→finish

This generates the script for table with indexes, constraints etc.

More information: http://msdn.microsoft.com/en-us/library/ms178078.aspx

Source : https://social.msdn.microsoft.com/Forums/en-US/17a16dc4-dcf1-4de8-baba-6949c55a4c5d/copy-constraints-and-indexes-from-one-database-to-another?forum=transactsql

MSSQL : trouver les verrous sur une table

SELECT 
	object_name(object_id) AS objet_bloque, 
    LOCK.resource_type,
    db.name AS db_name,
    LOCK.resource_associated_entity_id,
    LOCK.request_mode,
    LOCK.request_session_id,
    task.blocking_session_id,
    task.*
FROM sys.dm_tran_locks LOCK
INNER JOIN sys.partitions p ON p.hobt_id= LOCK.resource_associated_entity_id
INNER JOIN sys.databases db ON db.database_id = LOCK.resource_database_id
LEFT JOIN sys.dm_os_waiting_tasks AS task
    ON LOCK.lock_owner_address = task.resource_address
LEFT JOIN sys.dm_os_tasks AS STasks
    ON STasks.task_address = task.waiting_task_address
WHERE resource_database_id = 20 AND object_name(object_id) = 'NOM_DE_LA_TABLE'

Documentations