Aide mémoire SQL sur les instructions de base.

Annotations

L’opérateur de concaténation est CONCAT. Le double pipe (deux barres verticales) est souvent utilisé pour remplacer CONCAT. Sur AS400, vous trouverez plus souvent le double point d’exclamation pour remplacer le CONCAT.

L’utilisation du symbole Pipe (barre verticale) est nuisible à  la portablilité du code entre les différents produits base de données IBM. Il est préférable d’utiliser l’opérateur CONCAT plutôt que le double Pipe. Consulter la note d’information IBM sur les caractères.

Rappel des instructions sql de base

Sélectionner les données

 SELECT FROM
 SELECT * FROM t1 (toutes les colonnes)

 SELECT c1,c2 FROM t1 (sélection des colonnes c1 et c2)

 SELECT DISTINCT c1 FROM t1 (élimine les doublons : ne ramène qu’une valeur pour la colonne c1)

 SELECT c1 AS « colonne1 » FROM t1 (renommer une colonne)
[[Sur certain système le mot clé AS est facultatif]]

Restreindre la sélection

 SELECT * FROM t1 WHERE

 SELECT * FROM t1 WHERE c1 IN (’01’,’02’,’04’)

 SELECT * FROM t1 WHERE c2 NOT BETWEEN 10 AND 15

 SELECT * FROM t1 WHERE c3 IS NULL

 SELECT * FROM t1 WHERE c3 IS NOT NULL

 >, >=, <, <=, =, <>, (comparateur arithmétiques)

 AND, OR, NOT, (comparateur logique)

 % (n’importe quelle séquence de car.)

 _ (soulignement) (n’importe quel caractère)

Trier et présenter les résultats

 SELECT * FROM t1 ORDER BY c1 (tri ascendant par défaut)

 SELECT * FROM t1 ORDER BY c2,c4 (tri par c2 puis tri par c4)

 SELECT * FROM t1 ORDER BY c1 ASC, c3 DESC (tri ascendant ou descendant)

Exprimer les jointures

 SELECT * FROM t1,t2 (jointure sans qualification =
produit cartésien)

 SELECT * FROM t1,t2 WHERE t1.c1 = t2.c2 (jointure avec égalité)

 SELECT * FROM t1 a,t2 b,t3 c WHERE a.c1=b.c2 AND b.c2=c.c3
(jointures en cascades)

Manipuler les données

 SELECT c1,c2*3.25 AS « PRIX » FROM t1

 YEAR, MONTH, DATE (date)

 SUBSTRING, UPPER, LOWER, CHARACTER_LENGTH (manipulation de chaînes de car.)

Les fonctions statistiques

 AVG (moyenne)

 COUNT (nombre d’éléments)

 MAX (maximum)

 MIN (minimum)

 SUM (somme)

 SELECT COUNT(*)FROM t1

 SELECT SUM(c1) FROM t2

Regroupements

 SELECT * FROM t1 GROUP BY c1

Sous-requêtes SQL

 SELECT * FROM t1 WHERE c1 > (SELECT MIN(c1 FROM t2)

 SELECT * FROM t1 WHERE c2 NOT IN (SELECT c2 FROM t2)

 SELECT * FROM t1 WHERE c1 > ALL (SELECT c2 FROM t2) (sup. à  ttes les valeurs)

 SELECT * FROM t1 WHERE c1 > ANY (SELECT c2 FROM t2) (sup. à  au moins 1)

Opérateurs ensemblistes

Ils s’intercalent entre deux sélections

 UNION (sans les doublons) ou UNION ALL (y compris les doublons)

 INTERSECT à  partir de la v5r3

 EXCEPT à  partir de la v5r3

Insérer des enregistrements

 INSERT INTO t1 VALUES (‘abc’,5,7) (toutes les valeurs doivent être
renseignées)

 INSERT INTO t1(c1,c2) VALUES (1, ‘ROUGE’) (on ne renseigne que les colonnes
indiquées, les colonnes non précisées sont mises à  NULL ou à  la valeur par défaut si elle est précisée)

 INSERT INTO t1 SELECT * FROM t2

Mises à  jour d’enregistrement

 UPDATE t1 SET c2=’ROUGE’ WHERE c1=1
Supprimer des enregistrements

 DELETE FROM t1 WHERE c1=1

 DELETE FROM t1 WHERE c1 IN (SELECT c2 FROM t2)

 DELETE FROM t1 (supprime tous les enregistrements de la table t1)

iSeries : Nouveautés SQL en V4R5

Limites SQL

 nombre de lignes maximales dans une table = 4 Go

 nombre de tables accédées dans une instruction SQL : 256

 taille maximale d’un récepteur de journaux : 500 fois la capacité d’un journal V4R4

 nombre d’enregistrement vérouillés par une transaction : 500 millions

Apparition de nouvelles fonctions scalaires

 BIGINT(expression) : renvoi d’un « gros entier »; fonctionnement identique à  un INTEGER

 CEILING(expression) : renvoi de l’entier immédiatement supérieur à  l’expression; abréviation CEIL

 SIGN(expression) : renvoi -1, 0 ou 1selon que l’expression est négative, nulle ou positive

 ROUND(expression1, expression2) : renvoi l’expression 1 arrondie (ROUND (873,726, 2) = 873,730)

 TRUNCATE(expression1, expression2) : renvoi l’expression 1 tronquée (TRUNCATE (873,726, 2) = 873,720)

 DIFFERENCE(expression1, expression 2) : renvoi de 0 à  4 selon le dégré de proximité du son de 2 expressions (4 si les sons sont quasi identiques) … pour mélomanes…

 SOUNDEX(expression) : renvoi un code de 4 caractères représentant le son de l’expression

 RAND(entier) : renvoi un nombre aléatoire entre 0 et 1 de type FLOAT

 RADIANS(expression numérique) : renvoi de la valeur en radians de type FLOAT d’une expression fournie en degré …on sait jamais…

 ATAN2(X, Y) : renvoi de la valeur en radians d’un angle de coordonnées x, y

Procédures et fonctions SQL

 GET DIAGNOSTICS variable=ROW_COUNT : permet d’obtenir le nombre de lignes concernées par l’instruction précédente DELETE, INSERT, UPDATE, PREPARE (estimation)

 GOTO étiquette

 SIGNAL : pour positionenr un SQLSTATE et un libellé de message (SIGNAL SQLSTATE ‘II001′ SET MESSAGE_TEXT=’Trop long’)

 RESIGNAL : pour substituer un SQLSTATE à  un autre

 création d’une procédure stockée en JAVA

Nouveaux formats SQL :

 BIGINT : entier signé sur 8 octets (de – à  + 9 milliards de milliards)

 COUNT_BIG : idem COUNT, mais résultat dans un DECIMAL(31, 0)

iSeries : Nouveautés SQL en V5R1

Limitation possible du nombre de lignes retournées par un SELECT, pour ne « ramener » que quelques lignes.


SELECT * FROM Txxx
WHERE ...
FETCH FIRST nn ROWS ONLY

Utilisation du prédicat LIKE dans un ordre de concaténation.


SELECT * FROM Txxx
WHERE NOM LIKE '%' CONCAT PRENOM LIKE '%'...

Définition d’une jointure externe droite : renvoi de toutes les lignes de la table de droite et les lignes de la table de gauche vérifiant la condition.


RIGHT [OUTER] JOIN

Les prédicats IN, BETWEEN, LIKE et IS [NOT] NULL sont désormais acceptés sur une condition de jointure.

Création d’une table dupliquée par SQL.


CREATE TABLE Tzzz
LIKE Txxx

 les caractéristiques de clé ne sont pas dupliquées

 les lignes ne sont pas copiées

Enrichissement des SUBSELECT : il est désormais possible de citer un SELECT dans la liste des colonnes d’un autre SELECT, à  condition que ce premier SELECT ne renvoie qu’une valeur.

 SELECT (zone1, SELECT MAX(zone2) FROM Tyyy) FROM Txx

Il est possible de faire un tri sur une zone qui ne figure pas dans la clause select, sans avoir de code avertissement dans la log.

 SELECT c1, c2 from t1 order by c1, c3

Procédure SQL de création de collection

 CREATE_SQL_SAMPLE(nom de la collection)

Nouvelles fonctions scalaires

 DAYOFWEEK_ISO(date/horodate) : renvoi de 1 (lundi) à  7 (dimanche)

 JULIAN_DAY(date/horodate) : renvoi du nombre de jours depuis le 1er janvier -4712

 MIDNIGHT_SECONDS(time/horodate) : renvoi du nombre de secondes depuis le minuit précédent

 TIMESTAMPDIFF(expression1, expression2) : expression 1 est une valeur entière signfiant 1=fraction de seconde, 2=secondes, 4=minutes, 8=heures, 16=jours, 32=semaines, 64=mois, 128=trimestres, 256=années et expression 2 est est une chaîne de longueur 22 représentant la différence entre 2 horodatages

 WEEK_ISO(date/horodate) : fournit le numéro de la semaine (la semaine 1 contient le 4 janvier)

 GRAPHIC(expression alpha, [longueur], [CCSID]) : transforme une chaîne en DBCS ou UCS-2 (UNicode)

 PI() : fournit la valeur de PI en format FLOAT

 SPACE(expression numérique) : pour aller dans la LUNE… fournit une chaîne VARCHAR du nombre d’espaces indiqué

iSeries : Nouveautés SQL en V5R2

Crèer un fichier à  partir d’un select

 Create table TABTEMP as (select codart, libart from TABART) whith data pour créer un fichier avec les données résultantes

 Create table TABTEMP as (select codart, libart from TABART) whith no data pour créer un fichier sans les données résultantes

 La clause with no data est prise par défaut si vous ne l’indiquez pas.

Aides mémoire SQL publiés sur le site

 161

 208

 174

 202

 269

Les astuces SQL publiées sur le site

 200

 30

 135

 147

 140

 107

 242

 248

 323

 219

 266

 261

 57

 244

 344

 Déterminer le nombre d’enregistrement concernés par une requête sql

 Ajouter un code retour chariot dans une variable, en SQL

 324

 238

 387

 341

 394

 395

 429

 Formater une requête SQL.

 447

 448

Print Friendly, PDF & Email