Différences

Ci-dessous, les différences entre deux révisions de la page.

--- memo:sql [28/05/2015 14:44] – [Plusieurs enregistrements en une requête] Arnaud Jacquemin
+++ memo:sql [03/11/2025 17:35] (Version actuelle) – [MSSQL : trouver les verrous sur une table] Arnaud Jacquemin
@@ Ligne 1: / Ligne 1: @@
-====== Mémo Requêtes SQL ======
+====== Requêtes SQL ======
-===== SELECT =====
+====== SELECT ======
 <code sql>
@@ Ligne 13: / Ligne 13: @@
-==== NVL ====
+===== Gérer valeur NULL =====
+Utiliser la fonction standard COALESCE :
 <code sql>
 -- Téléphone fixe, ou mobile sinon
-SELECT NVL(cli.telephonefixe, cli.telephonemobile) AS telephone
+SELECT COALESCE (cli.telephonefixe, cli.telephonemobile) AS telephone
 FROM clients
 </code>
-Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm|Oracle]]
+Doc : [[https://en.wikibooks.org/wiki/Structured_Query_Language/SELECT:_IS_NULL_Predicate#Coalesce()_and_Similar_Functions|Wikibooks]], [[https://docs.microsoft.com/fr-fr/sql/t-sql/language-elements/coalesce-transact-sql|MS SQL]]
-==== Switch Case ====
+Chez Oracle il existe aussi une fonction non standard Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm|NVL]]
+===== Switch Case =====
 <code sql>
@@ Ligne 38: / Ligne 42: @@
 Doc : [[http://docs.oracle.com/cd/B13789_01/server.101/b10759/expressions004.htm|Oracle]]
-==== Portion de chaîne : SUBSTR ====
+===== Portion de chaîne =====
+**Oracle :**
 <code sql>
 SELECT
@@ Ligne 46: / Ligne 52: @@
 FROM produits_coloris
 </code>
 Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm|Oracle]]
+**MS SQL Server :**
+<code sql>
+SELECT
+	-- Séparer heures et minutes
+	SUBSTRING (a.actvissalheurearr, 1, 2) + ':' + SUBSTRING (a.actvissalheurearr, 3, 2) AS "Heure"
+FROM ACTEVISITESALARIE a
+</code>
+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 : [[http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions116.htm|REGEXP_SUBSTR chez Oracle]], [[http://www.postgresql.org/docs/9.1/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP|SUBSTRING w/ POSIX Regular Expressions chez PostgreSQL]]
-==== Transtypages ====
+Portion avant et après un caractères séparateur :
+<code sql>
+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
+</code>
+===== Transtypages =====
-=== Nombre -> texte ===
+==== Nombre -> texte ====
 Fonctions CAST ou TO_CHAR
@@ Ligne 73: / Ligne 96: @@
 Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions016.htm#i1269136|Oracle CAST]] ou [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions181.htm|TO_CHAR]]
-=== Date -> texte ===
+==== Date -> texte ====
+=== Oracle ===
 <code sql>
 SELECT
-	TO_CHAR(date, 'dd/mm/yyyy HH24:MI')
+	TO_CHAR(jour, 'dd/mm/yyyy HH24:MI')
 FROM latable
 -- Donne par exemple : '04/07/2012 13:37'
@@ Ligne 84: / Ligne 109: @@
 Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm#i1009324|Oracle]] et [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#sthref419|formats]]
-=== Texte -> date ===
+=== MySQL ===
+<code sql>
+select DATE_FORMAT(d.date_der_exe,'%Y%m%d%H%i%S')
+from latable
+-- Donne par exemple : '20160719010041
+</code>
+Doc : [[https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format|MySQL]]
+=== MS SQL Server ===
+<code sql>
+select CONVERT(varchar, LOGDate, 103)
+from latable
+-- Donne par exemple : '28/03/2020'
+</code>
+Doc : [[https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#date-and-time-styles|CAST and CONVERT (Transact-SQL)]]
+==== Date -> numéro de semaine ====
+<code sql>
+SELECT jour, TO_CHAR(jour, 'iw')
+FROM latable
+-- Donne par exemple : 2015-04-28 => 18
+</code>
+==== DateTime -> Date ====
+=== Microsoft SQL Server ===
+<code sql>
+SELECT cast([DateTime] As Date) AS jour
+FROM table
+</code>
+Source : https://stackoverflow.com/a/923322
+==== Texte -> date ====
+=== Oracle ===
 Fonction TO_DATE
@@ Ligne 95: / Ligne 163: @@
 Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm#i1003589|Oracle]]
-==== IN ====
+=== Microsoft SQL Server ===
+CAST('17/07/2019 00:00:00' AS date)
+<code sql>
+SELECT * FROM latable
+WHERE TOTAL_DATE = CAST('17/07/2019 00:00:00' AS date)
+</code>
+Ou en précisant le format :
+<code sql>
+SELECT * FROM latable
+WHERE date = CONVERT(date, '20210825', 112)
+</code>
+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 ===
+<code sql>
+SELECT STR_TO_DATE("2022/03/25", "%Y/%m/%d")
+</code>
+Doc : [[https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date| STR_TO_DATE(str,format)]] et [[https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format|formats]]
+===== IN =====
 <code sql>
@@ Ligne 105: / Ligne 198: @@
 Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm#i1050801|Oracle]]
-=== Avec dédoublonnage ===
+==== Avec dédoublonnage ====
 S'il y a plusieurs comptes pour un email, n'extraire que le compte le plus récent :
@@ Ligne 120: / Ligne 213: @@
-==== Agrégation ====
+===== Agrégation =====
 <code sql>
@@ Ligne 130: / Ligne 223: @@
 Doc : [[http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_10002.htm#i2066419|Oracle]]
-==== Plus petite valeur entre 2 colonnes ====
+===== Plus petite valeur entre 2 colonnes =====
 <code sql>
@@ Ligne 142: / Ligne 235: @@
-==== Plus grande valeur entre 2 colonnes ====
+===== Plus grande valeur entre 2 colonnes =====
 <code sql>
@@ Ligne 151: / Ligne 244: @@
 Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions060.htm|Oracle]]
-===== INSERT =====
+Pour la plus petite valeur, utiliser la fonction ''LEAST''
+<code sql>
+SELECT LEAST(datemodification, datecreation)
+FROM matable
+</code>
+Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions075.htm|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...
+<code sql>
+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 ...
+</code>
+====== INSERT ======
 <code sql>
@@ Ligne 160: / Ligne 285: @@
 Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm|Oracle]]
-==== Depuis une sous-requête ====
+===== Depuis une sous-requête =====
 Si la table existe déjà, sinon utiliser [[#creer_table_a_partir_d_une_requete]]
@@ Ligne 174: / Ligne 299: @@
-==== Plusieurs enregistrements en une requête ====
+===== Plusieurs enregistrements en une requête =====
 <code sql>
@@ Ligne 188: / Ligne 313: @@
-===== UPDATE =====
+====== UPDATE ======
 <code sql>
@@ Ligne 207: / Ligne 332: @@
 Doc : [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10007.htm#i2189756|Oracle]]
-===== DELETE =====
+===== Remplacer caractères =====
+Remplacer les points-virgules par des points dans une chaîne de caractères :
+<code sql>
+UPDATE unetable
+SET champ = REPLACE(champ , ';', '.')
+WHERE champ LIKE '%;%'
+</code>
+Doc : [[http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions153.htm#SQLRF00697|Oracle]]
+====== DELETE ======
 <code sql>
@@ Ligne 216: / Ligne 354: @@
 Doc : [[http://docs.oracle.com/cd/B12037_01/appdev.101/b10807/13_elems014.htm|Oracle]]
-==== Avec jointures ====
+===== Avec jointures =====
 Avec Oracle, impossible d'utiliser des jointures comme habituellement. Voici la syntaxe à utilise :
@@ Ligne 238: / Ligne 376: @@
-===== CREATE TABLE =====
+====== CREATE TABLE ======
-==== Clé primaire composée ====
+===== Clé primaire composée =====
 <code sql>
@@ Ligne 253: / Ligne 391: @@
 Doc : [[http://docs.oracle.com/cd/B14117_01/server.101/b10759/statements_7002.htm|Oracle]]
-==== Créer table à partir d'une requête ====
+===== Créer table à partir d'une requête =====
 Testé avec postgresql 9 et Oracle 10g :
@@ Ligne 270: / Ligne 408: @@
 Doc : [[http://www.postgresql.org/docs/9.0/static/sql-createtableas.html|PostgreSQL]]
+Testé avec M$ SQL Server :
+<code sql>
+SELECT *
+INTO destination
+FROM source;
+</code>
+Doc : https://learn.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql
+====== ALTER TABLE ======
+<code sql>
+ALTER TABLE nom_table
+   ADD (nouveau1  NUMBER(6,0) DEFAULT 10267 NOT NULL,
+        nouveau2  VARCHAR2(3)
+   );
+</code>
+====== MSSQL : lier un utilisateur à une connexion ======
+<code sql>
+USE NomDelaBase
+GO
+ALTER USER Utilisateur WITH LOGIN = Connexion
+GO
+</code>
+====== 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 ======
+<code sql>
+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'
+</code>
-==== Documentations ====
+====== Documentations ======
   * PostgreSQL 9.1 : http://docs.postgresql.fr/9.1/
-  * Oracle 11g : http://docs.oracle.com/cd/E11882_01/server.112/e26088/toc.htm
+  * Oracle
+    * 11g : https://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm
+    * 10g : https://docs.oracle.com/cd/B19306_01/server.102/b14200/toc.htm
+  * Microsoft SQL Server :
+    * 2017 : https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-2017