| 
SSSSSSSSS QQQQQQQQQ LL 44 000000 000000 SS QQ QQ LL 44 44 00 00 00 00 SSSSSSSSS QQ QQ LL === 44444444 00 00 00 00 SS QQ QQ LL 44 00 00 00 00 SSSSSSSSS O QQQQQQQQQ O LLLLLLL 44 000000 000000 Q -------------------------------------------------------------------- STRUCTURED QUERY LANGUAGE = Langage structuré d'interrogation |
|
A/ TERMINOLOGIE . ----------------- SELECTION PROJECTION JONCTION, INTERSECTION, DIFFERENCE SELECTION (stagiaires de l'agence 01) .................................................. : NUMSTG : NOM : PRENOM : AGENCE : :........:..................:...........:........: : 101 : DUBOIS : Eric : 01 : : 107 : ERNST : Patrick : 01 : : 121 : GARDEL : Sophie : 01 : :........:..................:...........:........: |
|
PROJECTION (nom,agence) ............................. : NOM : AGENCE : :...........................: : DUBOIS : 01 : : ERNST : 01 : : DUPONT : 02 : : MERCIER : 03 : : BOZUFFI : 03 : : GARDEL : 01 : : FLAVARD : 02 : : GOUDE : 02 : : FORTRAN : 03 : : DUBROVNIK : 04 : :..................:........: |
|
JONCTION (sur agence) ......................................................... : NUMSTG : NOM : PRENOM : AGENCE : LIBAGE : :........:............:..........:........:.............: : 101 : DUBOIS : Eric : 01 : LILLE : : 107 : ERNST : Patrick : 01 : LILLE : : 110 : DUPONT : Alain : 02 : NANTES : : 102 : MERCIER : Anne : 03 : DAX : : 104 : BOZUFFI : Ricardo : 03 : DAX : : 121 : GARDEL : Sophie : 01 : LILLE : : 130 : FLAVARD : Cecile : 02 : NANTES : : 132 : GOUDE : Jean : 02 : NANTES : : 103 : FORTRAN : Yves : 03 : DAX : :........:............:..........:........:.............: |
|
INTERSECTION (si l'agence existe) ........................................................... : NUMSTG : NOM : PRENOM : AGENCE : NOTE: ENTREE : :........:.............:..........:........:.....:........: : 101 : DUBOIS : Eric : 01 : 07 : 890405 : : 107 : ERNST : Patrick : 01 : 12 : 891215 : : 110 : DUPONT : Alain : 02 : 14 : 890405 : : 102 : MERCIER : Anne : 03 : 18 : 900302 : : 104 : BOZUFFI : Ricardo : 03 : 12 : 900302 : : 121 : GARDEL : Sophie : 01 : 17 : 891215 : : 130 : FLAVARD : Cecile : 02 : 09 : 890405 : : 132 : GOUDE : Jean : 02 : 13 : 890405 : : 103 : FORTRAN : Yves : 03 : 17 : 900302 : :........:.............:..........:........:.....:........: |
|
DIFFERENCE (si l'agence n'existe pas) ........................................................... : NUMSTG : NOM : PRENOM : AGENCE : NOTE: ENTREE : :........:.............:..........:........:.....:........: : 105 : DUBROVNIK : Marie : 04 : 16 : 891215 : :........:.............:..........:........:.....:........: |
|
B/ commande STRSQL ------------------ STRSQL COMMIT(*NONE) (contrôle de validation) *NONE pas de contrôle de validation ^ *CHG contrôle de validation actif | seuls les enreg modifiés, ajoutés journalisation | supprimés sont verrouillés obligatoire | *CS idem *CHG plus verrouillage du dernier enreg | sélectionné (pour les tables en consultation) | *ALL contrôle de validation actif | tous les enreg modifies, ajoutes | supprimes ET SELECTIONNES V sont verrouillés NAMING(*SYS) (convention d'appellation) *SYS convention d'appellation système (LIB/OBJET) un objet non qualifié est recherché dans *LIBL |
|
*SQL convention d'appellation SQL (LIB.OBJET) un objet non qualifie est recherché dans une bibliothèque ayant le nom du USER (sur 8 c) PROCESS(*RUN) (type d'exécution) *RUN les instructions sont exécutees *VLD seules la syntaxe et la validité des instructions sont verifiées *SYN seule la syntaxe est verifiée LIBOPT(*LIBL) (liste de bibliothèque) Liste de bibliothèque utilisee par F16-F17-F18 *LIBL liste de bibliothèques du travail *USRLIBL partie utilisateur de *LIBL *ALLUSR toutes les bibliothèques user *ALL toutes les bibliothèques *CURLIB la bibliothèque en cours -NOMBIB- une bibliothèque donnée |
|
LISTTYPE(*ALL) (type de liste) type d'objets affiches par F16-F17-F18 *ALL tous les objets *SQL seuls les objets SQL (collection,table,vue,index) REFRESH(*ALWAYS) (mise à jour de l'écran) Quand réactualiser les données provenant d'un SELECT *ALLWAYS à chaque fois que l'utilisateur demande un défilement. *FORWARD uniquement à la première visualisation d'un écran (défilement avant) DATFMT( ) DATSEP( ) définissent la présentation des colonnes TIMFMT( ) de type DATE/HEURE. TIMSEP( ) |
|
Touches de commandes valides sur l'écran SQL F3 = EXIT F4 = INVITE F6 = INSERER UNE LIGNE une ligne blanche est insérée en-dessous de la ligne ou se trouve le curseur F9 = RAPPEL d'une instruction SQL (ou sous-selection sur INVITE d'un select) F10 = COPIER UNE LIGNE la ligne où se trouve le curseur est copiée en-dessous F13 = SERVICE affiche l'écran de service permettant de modifier les paramètres de la commande STRSQL et de choisir le type de sortie. (ECRAN,FICHIER D'IMPRESSION,FICHIER BD) |
|
F14 = SUPPRIMER UNE LIGNE supprime la ligne où se trouve le curseur F15 = SCINDER UNE LIGNE place tout ce qui se trouve à droite du curseur sur une ligne en-dessous F16 = LISTE DE BIBLIOTHEQUES F17 = LISTE DE FICHIERS D'UNE BIBLIOTHEQUE F18 = LISTE DES ZONES D'UN FICHIER F24 = AUTRES TOUCHES F6, F10, F14 et F15 sont valides aussi sur l'écran d'invite. |
|
SQL/400 langage relationnel de manipulation de données. ---------------------------------------------------------- ORDRES SQL EXECUTABLES SUR L'ECRAN SQL-INTERACTIF l'ORDRE SELECT (définition d'une extraction) SELECT ..........ce que je veux FROM ...........à partir de quel(s) fichier(s) WHERE ..........sélection GROUP BY .......traitement récapitulatif HAVING .........sélection sur trt récap. ORDER BY .......ordre de classement |
|
SELECT ------- * toutes les variables du(des) table(s) [ex : select * from articles] ou <expression1> [AS nom], [ <expression2> [AS nom], .... ] TOUTE VIRGULE DOIT ETRE SUIVIE D'UN ESPACE (EN FRANCE) |
|
toute expression peut se voir attribuer dynamiquement un nouveau nom qui lui servira aussi d'en-tête. - SELECT NOM, SALAIRE + COM AS TOTPAIE FROM PERSON attribue le "nom" TOTPAIE à l'expression SALAIRE + COM expressions valides: + un nom de variable + une constante + un calcul + , * , - , / ex : (QTE * PU) , (SALBRU * 13) etc ... |
|
+ une fonction sous la forme Fonction(argument) toutes les fonctions acceptent comme argument une expression (au sens qui vient d'être vu). les fonctions peuvent donc être imbriquées. TRANSLATE(raisoc) transforme la raison sociale en majuscule SUBSTR(RAISOC , 2 , 5) extrait les caractères 2,3,4,5 et 6 TRANSLATE( SUBSTR(RAISOC , 2 , 5) ) transforme en majuscules les caractères extraits etc... |
|
Exemples :
+ SELECT CODART, LIBART, QTESTO, PUART,
(QTESTO * PUART) as montant FROM SQLDB/STOCKP1
+ SELECT MATRIC, NOM, SUBSTR(PRENOM, 1, 8),
(SALBRU * 13) AS SALAIRE
FROM SQLDB/PERSONP1
+ SELECT NOM, (CURRENT DATE - ARRIVEE)
FROM SQLDB/PERSONP1
+ SELECT NOM, PRENOM, ADRESSE , VILLE
FROM SQLDB/PERSONP1
+ SELECT TRANSLATE(ADRESSE) ...
Une fonction peut être testé par VALUES
+ VALUES TRANSLATE('Volubis')
|
|
remarque : La qualification BIBLI/TABLE n'est valide que si la convention d'appellation système a été choisie sur la commande STRSQL. Si la table n'est pas qualifiée la recherche est faite dans *LIBL Il faudra qualifier bibli.table si on choisit la convention d'appellation SQL. Si la table n'est pas qualifiée la recherche est faite dans la bibliothèque ayant le même nom que l'utilisateur (sur 8 c.) |
|
FROM ------- TABLE1, TABLE2,.. (32 tables maxi) IL EST POSSIBLE DE QUALIFIER LA TABLE (suivant la convention choisie) IL EST POSSIBLE D'ASSOCIER A UNE TABLE UNE CORRELATION (un nom interne) QUI POURRA ETRE UTILISEE EN DEFINITION DE COLONNE exemple: FROM SQLDB/STGTBL A, SQLDB/AGETBL B La table STGTBL est connue sous le "nom" A AGETBL sous le "nom" B |
|
WHERE ------- SELECTIONS POSSIBLES + = egal <> non egal (expression1> > <expression2> sup < inf >= sup ou egal <= inf ou egal WHERE codart = 0 .... WHERE qtecde <> 0 ... |
|
+ <expression1> BETWEEN <exp2> AND <exp3> compris entre <exp2> et <exp3> bornes incluses WHERE DEPT BETWEEN 22 and 44 !-----USER----------! + <expression1> LIKE !--:variable !-------------- + !--chaine de car. ! ! ! !--CURRENT SERVER---! !-ESCAPE-! Un "%" dans la chaine de caractères indique que n'importe quel nombre de n'importe quel caractère peut occuper cette position Un "_" (soulignement) dans la chaine de caractère indique que n'importe quel caractère peut occuper cette position. |
|
ex: LIKE '%ABC%' est vrai si la chaine "ABC" est trouvée à n'importe quel endroit de la variable ex: LIKE 'ABC%' est vrai si la variable commence par "ABC" ex: LIKE 'A_BC%' est vrai si la variable commence par "A" suivi d'un caractère quelconque puis par les deux lettres "BC" USER le contenu de la variable est comparé avec le profil utilisateur en cours CURRENT SERVER le contenu de la variable est comparé avec le nom du serveur en cours (cf CONNECT) ESCAPE indique un caractère d'échappement par ex, LIKE '%A+%BC%' ESCAPE '+', cherche "A%BC" V5R10 : les expressions sont admises dans la clause LIKE. where nom like '%' concat trim(COMMUNE) concat '%' |
|
+ <expression1> IN (<exp2>, <exp3>) Liste de valeurs à comparer ces valeurs pouvant être: I-USER----------I ------I-:variable-----I (cf LIKE) I-constante-----I WHERE DEPT IN (22, 29, 35, 44) Remarque : Il est toujours préferable de comparer une colonne avec une valeur de même définition, même longueur. TOUTES CES CONDITIONS PEUVENT être RELIEES PAR DES "OR" ET DES "AND" et/ou inversées (niées) par "NOT". |
|
EXEMPLES DE SELECTIONS VALIDES + SELECT * FROM SQLDB/STGTBL WHERE AGENCE = 01 + SELECT NUMSTG, NOM, PRENOM FROM SQLDB/STGTBL WHERE NUMSTG BETWEEN 0001 AND 0010 AND AGENCE <> 01 + SELECT NOM, AGENCE FROM SQLDB/STGTBL WHERE NOM LIKE 'D%' on peut bien sur utiliser des fonctions + SELECT NOM, AGENCE FROM SQLDB/STGTBL WHERE TRANSLATE(NOM) LIKE 'D%' |
|
fonctions valides: fonctions de changement de genre DIGITS(exp) représentation en alphanumérique d'une expression numérique DIGITS(ZONDEC) DECIMAL(exp, l, d) représentation en packé d'une expression numérique DECIMAL(AVG(SALBRU), 7, 2) forcer une précision : DECIMAL((QTE * PU), 11, 3) binaire vers packé : DECIMAL(ZONBIN, 6, 0) FLOAT(exp) représent. en virgule flottante d'une expression numérique |
|
INTEGER(exp) représent. en numérique binaire d'une expression numérique ZONED(exp) représent. en numérique étendu d'une expression numérique CHAR(date) renvoie la représentation caractère d'une date. (séparateurs inclus, suivant le format en cours.) VARCHAR(expression, Lg----------------) ! ! !--CCSID---! Transforme une expression à lg fixe en colonne à lg variable avec choix du code page (CCSID) VARGRAPHIC( ) idem en DBCS (idéogrammes orientaux ou DBCS) |
|
CHAR(exp) transforme en colonne à taille fixe un expression variable. BINARY() et VARBINARY() liées aux nouveaux types de données v5r30, proches du BLOB, les données étant sans notion de CCSID. la fonction CAST vient remplacer toutes les fonctions précédentes en proposant d'indiquer clairement le nouveau type et la longueur. CAST(expression AS type[lg]) V4R20 ATTENTION : CAST qui convertit du numérique en caractère remplace les zéros non significatifs (ceux de gauche) par des blancs, ce que ne fait pas la fonction DIGITS, qui conserve donc une particularité. |
|
types valides INT(INTEGER)--| SMALLINT----------------------------------------------- DEC(DECIMAL)---| NUMERIC---------(longueur, nb-de-décimales)-------------- FLOAT---| REAL ------------------------------------------------- DOUBLE--| CHAR(CHARACTERE)-| |--FOR BIT DATA--| VARCHAR------------(lg)---FOR-- --FOR SBCS -------------- |---n°-ccsid *---| DATE----------| TIME----------------------------------------------------- TIMESTAMP-----| * : un CSSID est un équivalent code-page associé à une donnée (france = 297) |
|
Autres fonctions: CONCAT( ) ancienne syntaxe : nom CONCAT prenom [ou !!] maintenant en plus : CONCAT(nom, prenom). SUBSTR(exp, dep, lg) extrait une chaîne de caracères depuis "dep" sur 'lg' caractères à partir d'une expression alpha. écriture V3R60 : SUBSTRING(zone FROM début ------------------------) !--FOR longueur--! si longueur n'est pas indiqué on va jusqu'à fin de zone LEFT(zone, lg) Extrait les "lg" caractères de gauche. RIGHT(zone, lg) Extrait les "lg" caractères de droite (V5R30) LENGTH(expr) donne la longueur physique d'une expression. (le résultat est donné sous forme binaire) |
|
ex :SUBSTR(nom, LENGTH(nom) -1 , 2) CHARACTER_LENGTH = nbr de caractères (zone à lg variable) ex :CHARACTER_LENGTH(trim(nom)) OCTET_LENGTH indique le nombre d'octets occupés par une colonne (V5R3) BIT_LENGTH() indique le nombre de bits occupés par une colonne (V5R3) TRANSLATE(exp) conversion minuscule/MAJUSCULE d'une chaîne ou UCASE( ) ou UPPER( ) mais aussi : |
|
TRANSLATE(chaîne-----------------------------------------------------> ! ! !---, remplacement ---------------------------! ! ! !-- , origine -----! >-------------------------------) ! ! !--PADDED WITH------! Exemple: TRANSLATE(var1 ,' ,F' , '0.$') remplace 0 par ' ' "." par "," et "$" par "F" . LOWER( ) ou LCASE( ) conversion MAJUSCULE/minuscule d'une chaîne LTRIM( ) , RTRIM( ) et TRIM( ) Supprimme les espaces à gauche, à droite ou aux deux extrémités. |
|
STRIP(exp, [BOTH] , [C]) LEADING TRAILING Alternative à TRIM (et aussi RTRIM et LTRIM) supprime le(s) caractère(s) "C" (dft = "espace") à gauche (LEADING), à droite (TRAILING) ou les deux (BOTH), de la chaîne indiquée. le résultat est de longueur variable. LOCATE(recherche, origine, ---------------) V4R20 |-départ--| indique si "recherche" est présent dans "origine" (à partir de "départ"). l'information retournée est numérique et indique la position de début. POSITION(recherche IN origine) [alias à LOCATE] |
|
REPEAT(c , nb) repète le caractère 'c', nb fois (V5R3) REPLACE(zone, org, new) remplace org par new dans zone (V5R3) INSERT(Z , deb, nb, chaine) (V5R3) insert 'chaine' à partir de 'deb' en remplacant nb caractères (0 = insertion pure). Exemple : sur une zone char(1) contenant '*' Insert(zone1 , 1 , 0 , 'XX') --> 'XX*' --on insert devant Insert(zone1 , 1 , 1 , 'XX') --> 'XX' --on remplace Insert(zone1 , 2 , 0 , 'XX') --> '*XX' --on insert derrière |
|
ENCRYPT_RC2(data, pwd , hint) (V5R3) Encrypte les données founies en premier argument en utilisant le deuxième comme clé, selon l'algorithme RC2. le mot de passe peut être fixé aussi par SET ENCRYPTION PASSWORD, avant. l'asctuce (facultative) est un "pense-bète" mémorisé avec la donnée. la zone réceptrice doit être CHAR FOR BIT DATA , BINARY ou BLOB V5R4 ENCRYPT_TDES(data , pwd, hint) comme ENCRYPT_RC2 mais en utilisant le cryptage "Triple DES" V6R1 ENCRYPT_AES(data , pwd, hint) comme ENCRYPT_RC2 mais en utilisant le cryptage "AES" GETHINT() retourne l'astuce (le pense-bète) permettant de se souvenir de la clé (qui est obligatoire avec les fonctions ci-dessous) |
|
DECRYPT_BIT() décrypte une donnée cryptée et retourne du VARCHAR /BIT DATA DECRYPT_BINARY() décrypte une donnée cryptée et retourne du BINARY DECRYPT_CHAR() décrypte une donnée cryptée et retourne du VARCHAR simple DECRYPT_DB() décrypte une donnée cryptée et retourne du Double Byte (UTF-8) ABSVAL(exp) renvoie la valeur absolue d'une expression numérique SQRT(exp) renvoie la racine carrée (voir aussi sinus, cosinus,...) |
|
MOD(exp1, exp2) renvoie le reste de la division des deux arguments POWER(nombre, exposant) élévation à la puissance SIGN(colonne) retourne le signe d'une colonne -1 si négatif, 1 si positif strictement, 0 si null RAND() retourne un nombre aléatoire (< à 1) CEIL(colonne) Transforme un réel (avec décimales) en son entier immédiatement supérieur. CEIL(2,42) = 3 CEIL(2,56) = 3 FLOOR(colonne) Transforme un réel (avec décimales) en son entier immédiatement inférieur. FLOOR(2,42) = 2 FLOOR(2,56) = 2 |
|
ROUND(colonne,p) arrondi comptable d'une expression numérique avec choix de la précision. ROUND(2,42 , 1) = 2,40 ROUND(2,56 , 1) = 2,60 TRUNCATE(colonne,p) arrondi inférieur d'une expression numérique avec choix de la precision. TRUNCATE(2,42 , 1) = 2,4 TRUNCATE(2,56 , 1) = 2,5 MULTIPLY_ALT() alternative à l'opérateur *, travaille avec une plus grande précision, à utiliser avec des résultats intermédiaires de plus de 63 chiffres ! (V5R3) RRN(nom-table) renvoie le numéro de rang. DATABASE() retourne le nom du serveur comme CURRENT SERVER (V5R3) |
|
Gestion de la valeur nulle la valeur nulle (la différence entre un prix à 0 et un prix non renseigné) est bien intégrée à SQL. elle devient vite indispensable avec la gestion des dates (date de départ dans le fichier du personnel, par ex) elle est par défaut lors du CREATE TABLE et peut être précisée en SDD par le mot-clé ALWNULL. les tables contenant ces zones doivent être manipulées en RPG4 ou SQL les tests se font sous la forme : DAT_DEPART IS NULL PRIX IS NOT NULL |
|
VALUE(exp1, exp2 [,exp...] ) COALESCE et IFNULL alias de VALUE (IFNULL est limité à 2 arguments) renvoient la première valeur non nulle de la liste des expressions Intéressant sous la forme IFNULL(NOM, 'non précisé') pour attribuer une valeur par défaut si NOM est NULL. particulièrement lors des jointures non abouties, les colonnes de la table de droite sont initilaisées à NULL NULLIF(argument1, argument2) retourne NULL, si les deux arguments sont égaux. pour remplacer une valeur significative par la val. nulle. |
|
+ CASE : condition à la réalisation d'une expression Exemple : ...................................................... : SELECT MATRICULE, NOM, : : : : CASE SUBSTR(SERVICE, 1, 3) : : : : WHEN 'INF' THEN 'INFORMATIQUE' : : WHEN 'PER' THEN 'PERSONNEL' : : WHEN 'PRO' THEN 'PRODUCTION' : : WHEN 'COM' THEN 'COMMERCIAL' : : ELSE 'Divers ....' : : END : : : : FROM personnel WHERE ... : : : :....................................................: |
|
on peut indiquer la colonne testée sur la clause WHEN ........................................................ : SELECT CODART, LIBART, PRIX, : : CASE : : WHEN PRIX < 0 THEN 'prix négatif' : : WHEN CODART = 1557 THEN 'pas de prix' : : ELSE 'prix normal' END : : : : FROM ... WHERE ... : :......................................................: ......................................................................... : SELECT * FROM COMMANDES WHERE : : (CASE WHEN PRIXMOYEN = 0 THEN PRIXTARIF * QTE : : ELSE PRIXMOYEN * QTE END) > 10000 : :.......................................................................: le test peut être IS NULL / IS NOT NULL la valeur retournée (THEN|ELSE) peut être le mot réservé NULL |
|
GROUP BY ---------- Cette clause ne donne qu'une ligne par groupe d'enregistrements il n'est pas possible de demander des variables qui ne sont pas precisées dans le GROUP BY Fonctions associées : ces fonctions utilisées sans GROUP BY donnent un résultat général AVG(exp) moyenne ex. AVG(SALBRU) moyenne des salaires COUNT(*) nb d'enregistrements sélectionnés |
|
COUNT(DISTINCT col1) nb de valeurs rencontrées pour col1. ex: SELECT COUNT(DISTINCT nocli) FROM ENTETECDE WHERE DATLIV > CURRENT DATE ici, il faut compter le nombre de clients et non le nombre de lignes qui donnerait le nombre de commandes. MAX(exp) valeur la plus grande rencontrée (y compris dates) ex: MAX(SALBRU) plus grand salaire mais aussi : MAX(PRXACHAT , PRXMOYEN) la plus grande des deux colonnes MIN(exp) valeur la plus petite rencontrée (y compris dates) ex: MIN(SALBRU) plus petit salaire mais aussi : MIN(DAT1 , DAT2) idem fonction MAX. |
|
SUM(exp) somme ex: SUM(SALBRU) somme des salaires SUM(QTESTO * PUART) somme des montants VAR(exp) variance la formule est VAR(x) = SUM(x**2)/COUNT(x) - ( (SUM(x)/COUNT(x))**2) pour [10,12,7] la variance est 4,22 STDDEV(exp) écart-type la formule est STDDEV(x) = SQRT(VAR(X)) pour [10,12,7] l'écart-type est 2,04 |
|
EXEMPLES DE SELECTIONS VALIDES + SELECT COUNT(*), AGENCE FROM SQLDB/STGTBL GROUP BY AGENCE donne le nombre de stagiaires par agence + SELECT AVG(SALBRU), SERVICE, COEFF FROM SQLDB/PERSONP1 GROUP BY SERVICE, COEFF donne la moyenne des salaires par service et coef du fichier personnel EXEMPLES DE SELECTION INVALIDE + SELECT COUNT(*), NOM, AGENCE FROM SQLDB/STGTBL GROUP BY AGENCE la variable nom n'identifiant pas un groupe |
|
HAVING -------- Permet de donner des conditions sur le résultat d'un traitement récapitulatif EXEMPLE + SELECT COUNT(*), AGENCE FROM SQLDB/STGTBL GROUP BY AGENCE HAVING COUNT(*) > 20 donne le nombre de stagiaires par agence, uniquement pour les agences ayant plus de 20 stagiaires |
|
ORDER BY ---------- Permet de donner un ordre de classement ORDER BY ----nom-colonne------------------------. I--n° de déclaration-I I-ASC--I I-DESC-I Sur le résultat d'un GROUP BY les enregistrements sont classés sur le critère de traitement récapitulatif EXEMPLES DE SELECTIONS VALIDES + SELECT * FROM SQLDB/STGTBL ORDER BY NOM + SELECT MATRIC, NOM, SUBSTR(PRENOM, 1, 8) FROM SQLDB/PERSONP1 ORDER BY 3 (classement sur début du prénom) |
|
V4R40 les expressions sont maintenant admises sur les clauses GROUP BY ORDER BY à condition qu'elles soient indiquées aussi (et à l'identique) sur la clause SELECT. soit bdist dans le fichier clients contenant dep+bureau (44000 par ex.) SELECT substr(digits(bdist), 1, 2), COUNT(distinct NOCLI) from clients group by substr(digits(bdist), 1, 2) donne le nombre de clients par département. |
|
SELECT bdist, COUNT(distinct NOCLI) from clients group by substr(digits(bdist), 1, 2) est invalide, l'expression n'étant pas présente sur le SELECT. ORDER BY offre rigoureusement les mêmes possibilités. tout en conservant la possibilité d'indiquer un N° de colonne (N° dans le select bien sur) Ou | SELECT codcli, left(raisoc, 20) | SELECT codcli, left(raisoc, 20) from clients | from clients ORDER BY 2 | ORDER BY left(raisoc, 20) | | |
|
CAS PARTICULIER DES DATES DATE(expression) convertit "expression" au format DATE formats admis: TIMESTAMP (extrait la partie DATE) 7 ALPHA (format julien AAAAJJJ) ALPHA représentant une date éditée (AAAA-MM-JJ, par exemple) numérique représentant (nbr de jours - 1) depuis le 1er janvier de l'AN 1. DAY, MONTH, YEAR renvoient la partie concernée d'une expression date (voir ci dessus + variable de type DATE) DAYS(expression) renvoie le nombre de jours -1 séparant cette date du 1er janvier de l'an 1. |
|
MANIPULATION D'HORAIRES TIME(expression) convertit "expression" au format TIME formats admis: TIMESTAMP (extrait la partie TIME) ALPHA représentant une heure éditée (HH:MM:SS) HOUR, MINUTE, SECOND renvoient la partie concernée d'une expression TIME (voir ci dessus + variable de type TIME) TIMESTAMP (date +heure +6 décimales) TIMESTAMP(expression) convertit "expression" (horodatage édité) TIMESTAMP(date heure) produit l'horodatage correspondant (microsec. à zéro) |
|
NOTION DE DUREE: + Durées explicites basées sur un chiffre et un mot clé lui donnant du sens. - YEARS, MONTHS, DAYS - HOURS, MINUTES, SECONDS, MICROSECONDS servent aux calculs (ARRIVEE + 6 MONTHS + 15 DAYS) ainsi que - CURRENT DATE - CURRENT TIME - CURRENT TIMEZONE (fuseau horaire) (CURRENT TIMEZONE représente le décalage avec GMT ==> CURRENT TIME - CURRENT TIMEZONE = temps universel) |
|
+ Durées numériques (ou implicites) pour une date = valeur numérique 8,0 => AAAA MM JJ ainsi 615 (0000.06.15) = 6 mois et 15 jours pour une heure = valeur numérique 6,0 => HH MM SS pour un horodatage = 20,6 (décimales = microsecondes) INCREMENTATION, DECREMENTATION, DIFFERENCE AJOUT (+) TOUJOURS Date + durée = date heure + durée = heure si le mois est modifié en dépassant 12, il est ajusté et l'année est incrémentée (idem jours, minutes, secondes) |
|
RETRAIT (-) DATE - durée = DATE : il s'agit d'une décrémentation DATE - DATE = durée : il s'agit d'une différence la durée est exprimée en durée numérique à l'affichage ainsi 010514 ==> 1 an , 5 mois et 14 jours. une durée explicite peut être utilisée lors d'un calcul WHERE (DATliv - 2 MONTHS) > DATcde un calcul produit FORCEMENT un résultat de type durée numérique et doit donc être comparé avec une valeur numérique (aaaammjj ou hhmmss). WHERE (DATliv - DATcde) < 200 (soit 2 mois) |
|
Autres Fonctions DATE : CURDATE() = idem au registre CURRENT DATE CURTIME() = " " CURRENT TIME NOW() = " " CURRENT TIMESTAMP DAYOFMONTH(date) = jour dans le mois (idem DAY().) DAYOFWEEK(date) = jour dans la semaine (1=dimanche) DAYOFWEEK_ISO(date) = jour dans la semaine (1=Lundi) [V5R10] DAYOFYEAR(date) = jour (julien) dans l'année. QUARTER(date) = N° du trimestre |
|
WEEK(date) = N° de la semaine ATTENTION : 1er Janvier = semaine 1 WEEK_ISO(date) = N° de la semaine, 1er janvier = 1 ou 53. [V5R10] DAYNAME(Date) retourne le nom du jour (en Français) de Date (V5R3) MONTHNAME(Date) retourne le nom du mois (en Français) de Date (V5R3) EXTRACT(DAY from zonedate) extrait la partie JOUR de zone date, on peut demander : DAY, MONTH, YEAR d'une date HOUR, MINUTE SECOND d'une heure TIMESTAMP_ISO() convertit en TIMESTAMP : une date (l'heure est à 00:00:00) ou une heure(la date est à aujourd'hui) |
|
V5R40
LAST_DAY(date) retourne la date correspondant au dernier jour du mois
par exemple LAST_DAY('2006-04-21') = 2006-04-30
ADD_MONTHS(date, nbr) ajoute un nombre de mois à la date
Attention, si la date est au dernier jour du mois, la date
calculée est aussi au dernier jour du mois
par exemple DATE('2006-04-30') + 1 months = 2006-05-30
ADD_MONTHS('2006-04-30' , 1) = 2006-05-31
GENERATE_UNIQUE() genère une valeur unique de type CHAR(13)
(basée sur le timestamp en cours)
la fonction TIMESTAMP() peut-être utilisée pour
convertir en clair la valeur générée.
|
|
NEXT_DAY(date , 'JOUR')
retourne la prochaine date ayant le jour demandé
(sur 3 c ou 10c.) à partir de la date d'origine.
valeurs admises :
'MON' ou 'LUN' ou 'LUNDI' pour le prochain Lundi
'TUE' ou 'MAR' ou 'MARDI', prochain Mardi
'WED' ou 'MER' ou 'MERCREDI', prochain Mercredi
'THU' ou 'JEU' ou 'JEUDI', prochain Jeudi
'FRI' ou 'VEN' ou 'VENDREDI', prochain Vendredi
'SAT' ou 'SAM' ou 'SAMEDI', prochain Samedi
'SUN' ou 'DIM' ou 'DIMANCHE', prochain Dimanche
Exemple :
NEXT_DAY('2006-12-31' , 'DIM') => '2007-01-07'
|
|
JOINTURE : SI la clause WHERE n'est pas utilisée SQL joint à CHAQUE enregistrement de TABLE1 TOUS les enregistrements de TABLE2. norme ISO 89: + SELECT NOM, PRENOM, STGTBL.AGENCE, LIBAGE FROM SQLDB/STGTBL, SQLDB/AGETBL WHERE STGTBL.AGENCE = AGETBL.AGENCE (la variable AGENCE étant dupliquée il devient obligatoire de qualifier la variable par le nom de la table "STGTBL.AGENCE") OU + SELECT NOM, PRENOM, A.AGENCE, LIBAGE FROM SQLDB/STGTBL A, SQLDB/AGETBL B WHERE A.AGENCE = B.AGENCE (Utilisation des corrélations) |
|
la jonction norme 92 peut être définie avec la clause JOIN de la manière suivante (disponible depuis la V3R10) + produit cartésien: SELECT ... FROM table1 CROSS JOIN table2 (équivalent à l'absence de clause WHERE) + jointure conditionnelle SELECT ... FROM table1 JOIN table2 ON zone1 = zone2 (toute expression logique est acceptée après ON) les jointures sont par défaut internes (elles n'affichent que les enregistrements en correspondance) On parle de INNER JOIN (qui est indentique à JOIN seul) |
|
+ jointure externe (OUTER JOIN) on parle de LEFT OUTER JOIN quand on désire tous les enregistrements du fichier1 (celui à gauche du mot JOIN) qu'ils soient ou non en correspondance avec le fichier2 [comme JDFTVAL/SDD ou OPNQRYF JDFTVAL(*YES)] pour SQL/400 LEFT JOIN est identique à LEFT OUTER JOIN les colonnes de table2 sont initialisées à NULL. ce qui rend très intéressantes deux fonctions : IFNULL() et VALUE() qui assignent une valeur de remplacement à une colonne contenant la valeur nulle. et la clause IS NULL à utiliser dans un test (WHERE CODE IS NULL) |
|
exemples : liste des clients, ayant passé des commandes : SELECT codcli, nomcli, numcde, datcde, datliv FROM clients c JOIN command d ON c.numcli = d.numcli liste des clients, avec, pour ceux d'entre eux ayant passé des commandes, la liste des commandes : SELECT codcli, nomcli, numcde, datcde, datliv FROM clients c LEFT OUTER JOIN command d ON c.numcli = d.numcli WHERE ... liste des stagiaires (si l'agence est inconnue on lui attribut la valeur 'invalide') SELECT NOM, PRENOM, STGTBL.AGENCE, IFNULL(LIBAGE, 'invalide') FROM STGTBL S LEFT OUTER JOIN AGETBL A ON S.NOSTAG = A.NOSTAG |
|
+ traitement des enregistrements sans correspondance SELECT ... FROM table1 EXCEPTION JOIN table2 ON zone1 = zone2 ne fournit que les enregistrements de table1 n'ayant pas d'équivalence dans table2 [comme OPNQRYF JDFTVAL(*ONLYDFT)] exemple : liste des stagiaires enregistrés sous une agence inconnue. SELECT NOM, PRENOM FROM STGTBL S EXCEPTION JOIN AGETBL A ON S.NOSTAG = A.NOSTAG |
|
V5R10 : - RIGHT OUTER JOIN jointure externe à partir du fichier de droite du mot-clé JOIN select ... from clients RIGHT OUTER JOIN commandes (toutes les commandes ,même si le client est inconnu) - RIGHT EXCEPTION JOIN select ... from clients RIGHT EXCEPTION JOIN commandes (toutes les commandes dont le client est inconnu) V5R30 : SELECT * from clients JOIN commandes USING( numcli ) -> si la zone porte le même nom dans les deux fichiers. |
|
SELECT EN CREATION DE VUE (mémorisation d'un ordre SELECT) --------------------------- CREATE VIEW nomvue AS SELECT NOM, PRENOM FROM SQLDB/STGTBL (projection) CREATE VIEW nomvue (NM, PM) AS SELECT NOM, PRENOM FROM SQLDB/STGTBL (projection , variables renommées) CREATE VIEW nomvue (NOM, PRENOM, STGAGE, LIBAGE) AS SELECT NOM, PRENOM, X.AGENCE, LIBAGE FROM SQLDB/STGTBL X JOIN SQLDB/AGETBL Y ON X.AGENCE = Y.AGENCE (jonction de deux fichiers) Vous pouvez aussi mémoriser des fonctions récapitulatives, utilisables par pgm en lecture séquentielle : |
|
CREATE VIEW nomvue (AGENCE, NBSTAG) AS SELECT AGENCE, COUNT(*) FROM SQLDB/STGTBL GROUP BY AGENCE (traitement récapitulatif) AUTRES SELECT --------------- + SELECT DISTINCT AGENCE FROM SQLDB/STGTBL ********** * AGENCE * 1 ligne par valeur ********** * 01 * * 02 * * 03 * ********** |
|
AUTRES ORDRES -------------- l'ORDRE UPDATE UPDATE ..........table à modifier SET ............les modifs à faire WHERE ..........sélection (sans WHERE maj de toute la table) EXEMPLES DE MISES A JOUR VALIDES UPDATE AGETBL SET LIBAGE = 'Nantes' WHERE AGENCE = 02 UPDATE PERSONP1 SET SALBRU = (SALBRU * 1,02) WHERE COEF = 215 |
|
l'ORDRE DELETE DELETE FROM .....table à modifier WHERE ..........sélection (sans WHERE mise a blanc de la table) EXEMPLES DE SUPRESSIONS VALIDES DELETE FROM STGTBL WHERE AGENCE = 04 DELETE FROM PERSONP1 WHERE MATRIC = 4112 DELETE FROM TABLE (sans where) = suppression de tous les enregistrements |
|
l'ORDRE INSERT INSERT INTO .....table à modifier (<nom-variable>, <nom-variable>, etc...) si non precisé = toutes les variables VALUES .........valeurs assignées aux variables EXEMPLES D'INSERTIONS VALIDES INSERT INTO STGTBL VALUES(14, 'BREGAY', 'Frederique', 01) OU INSERT INTO STGTBL (NUMSTG, NOM, AGENCE) VALUES(14, 'BREGAY', 01) INSERT INTO STGTEMP SELECT * FROM STGTBL WHERE AGENCE = 03 (le résulat de l'ordre SELECT est place dans la table STGTEMP) |
