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 <condition(s)>
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)</condition(s)>
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.