Kaillou, Forum AS400

Forum AS400 et de discussions


    Ordres SQL

    Partagez
    avatar
    Shewolf
    Admin

    Nombre de messages : 190
    Localisation : 91 idf
    Emploi : Analyste Réalisateur / Chef de Projets

    Ordres SQL

    Message par Shewolf le Mar 13 Nov - 11:01

    Ordres SQL

    extrait de :

    http://www.volubis.fr/Pausecaf/PAUSECAF27.htm
    http://cibretagne.org/bonus/SQL_memo.htm




    Mémo SQL



    * indique une nouveauté 5.40





    Ordres de base

    SQL 89


    SELECT colonne1 [as entete1],
    colonne2 [as entete2]
    ( select imbriqué retournant une valeur) as entete3
    FROM fichier1 [f1], fichier2 [f2]
    WHERE [critères de jointure et sélection]
    GROUP BY colonne
    HAVING [sélection]

    ORDER BY colonne [ASC|DESC]
    ou N°-de-colonne

    FETCH FIRST n ROWS ONLY

    SQL
    92
    (V3R10) SELECT colonne1 [as entete1],
    colonne2 [as entete2]
    ( select imbriqué retournant une valeur) as entete3
    FROM fichier1 f1 join fichier2 f2 ON f1.clea = f2.clea
    and f1.cleb = f2.cleb
    [join fichier3 f3 on f2.clec = f3.clec]
    WHERE [sélection]
    GROUP BY colonne (ou expression en V4R40)
    HAVING [sélection]

    ORDER BY colonne [ASC|DESC]
    ou N°-de-colonne (ou expression en V4R40)

    FETCH FIRST n ROWS ONLY

    join = uniquement les enregistrements en correspondance
    Left outer join = tous les enregistrements de fichier1
    exception join = uniquement les enregistrements sans correspondance
    right outer join = tous les enregistrements de fichier2
    right exception join = uniquement les enregistrements sans correspondance

    (tables dérivées) Soit
    SELECT colonne1 [as entete1],colonne2 [as entete2]
    FROM (SELECT ... FROM ...) as nom-temporaire WHERE [sélection]

    Soit
    WITH nom-temporaire as (SELECT ... FROM ...)
    SELECT colonne1 [as entete1],colonne2 [as entete2]
    FROM nom-temporaire WHERE [sélection]

    (Requête recursive) * WITH temp (composant, compose, quantite) as (select L.composant, L.compose, L.quantite from liens L where composant = 'voiture' UNION ALL select fils.composant , fils.compose, fils.quantite from temp AS Pere join liens AS Fils on pere.compose=Fils.composant ) SELECT * FROM TEMP

    UPDATE
    (-> V4R20) UPDATE fichier SET colonne1 = valeur1
    WHERE [sélection]
    UPDATE
    (V4R30 et +) UPDATE fichier f SET colonne1 =
    (select valeur1 from autrefichier where cle = f.cle)
    WHERE [sélection]
    DELETE DELETE FROM fichier WHERE [sélection]
    INSERT INSERT INTO fichier VALUES(valeur1, touteslescolonnes...)
    ou
    INSERT INTO fichier (colonne2, colonne3) VALUES(v2, v3)
    ou
    INSERT INTO fichier (SELECT ... FROM ...WHERE ...)


    Sélections

    Operateur logique Exemple(s)
    colonne op. colonne
    ou
    colonne op. valeur op.

    =
    <
    >

    =

    QTECDE 0

    LIBART = 'Pamplemousse'

    PRIX >= 45
    IN (val1, val2, val3, ...) DEPT IN (44, 49, 22, 56, 29)
    BETWEEN val1 AND val2 DEPT BETWEEN 44 AND 85
    LIKE nom LIKE 'DU%' (commence par)
    nom LIKE '%PON%' (contient)
    nom LIKE '%RAND' (se termine par)

    depuis la V5R1 : nom LIKE '%'concat ville concat '%'

    IS (ISNOT) NULL test la valeur nulle (pratique avec les jointures externes)

    et aussi OR, AND, NOT, (, ). CODART = 1245 or LIBART = 'Pamplemousse'
    V5R40 * : les valeurs peuvent être comparées en ligne ... where (cepage1, cepage2) = ('Syrah' , 'Grenache')


    Dernière édition par le Mar 13 Nov - 11:05, édité 2 fois
    avatar
    Shewolf
    Admin

    Nombre de messages : 190
    Localisation : 91 idf
    Emploi : Analyste Réalisateur / Chef de Projets

    Re: Ordres SQL

    Message par Shewolf le Mar 13 Nov - 11:01

    Fonctions valides (fonctions de groupe)

    Fonction(x) Retourne ? Exemple
    AVG(x) la moyenne de X pour un groupe de ligne AVG(quantite)
    COUNT(*) le nombre de lignes sélectionnées
    COUNT(DISTINCT X) le nombre de valeurs différentes pour X COUNT(distinct nocli)
    MAX(X) retourne la plus grande valeur sélectionnée MAX(DATLIV)
    MIN(X) retourne la plus petite valeur sélectionnée MIN(prix)
    SUM(x) retourne la somme de X SUM(qte * prix)
    VAR(X) retourne la variance
    STDDEV(X) retourne l'écart type

    Fonctions valides (ligne à ligne)

    Fonction(x) Retourne ? Exemple
    MAX(X,Y) retourne la plus grande valeur de X ou de Y MAX(prixHA, pritarif) * qte
    MIN(X,Y) retourne la plus petite valeur de X ou de Y MIN(datce, datliv)
    ABSVAL(x) la valeur absolue de x ABSVAL(prix) * qte
    CEIL(x) Retourne l'entier immédiatement supérieur à X CEIL(2,42) = 3 CEIL(2,56) = 3

    RAND() Retourne un nombre aléatoire
    ROUND(x , y) Retourne l'arrondi comptable à la précision y ROUND(2,42 , 1) = 2,40
    ROUND(2,56 , 1) = 2,60

    SIGN(x) Retourne -1 si x est négatif, 1 s'il est positif, 0 s'il est null Where SIGN(x) = -1
    TRUNCATE(x , y) Retourne le chiffre immédiatement inférieur à X
    (à la précision y) TRUNCATE(2,42 , 1) = 2,40 TRUNCATE(2,56 , 1) = 2,50

    DEC(x , l, d) x au format numérique packé avec la lg et la précision demandée. DEC(zonebinaire)
    DEC(avg(prix), 9, 2)
    DIGITS(x) x en tant que chaîne de caractères DIGITS(datnum)
    CHAR(x) x en tant que chaîne de car. (x étant une date) CHAR(current date)
    FLOAT(x) x au format "virgule flottante" FLOAT(qte)
    INT(x) x au format binaire INT(codart)
    ZONED(x) x au format numérique étendu ZONED(prix)
    CAST(x as typeSQL[lg]) x au format indiqué par typeSQL :

    types valides
    INT | INTEGER
    SMALLINT
    DEC(lg, nb-décimales)
    NUMERIC(lg, nb-décimales)
    FLOAT | REAL | DOUBLE
    CHAR | VARCHAR
    - --FOR BIT DATA-
    -- -FOR SBCS ---
    ----FOR nø-ccsid *--
    DATE
    TIME
    TIMESTAMP

    * : un CSSID est un ‚quivalent code-page associ‚ … une donn‚e (france = 297)
    CAST(qte AS CHAR(9))
    Attention les zéros de gauche sont éliminés

    CAST(prixchar as NUMERIC(7, 2))

    cast('123456,89' as numeric(8, 2)) fonctionne

    cast('123456,89' as numeric(7, 2))
    donne une erreur
    (trop peu d'entiers)
    STRIP(x) ou
    TRIM(x)
    RTRIM(x) LTRIM(x)
    supprime les blancs au deux extrémités de x.

    • les blancs de droite
    • les blancs de gauche TRIM(raisoc)

    LENGTH(x) ou
    OCTET_LENGTH(x)
    la longueur de x LENGTH(nom)

    LENGTH(TRIM(nom))
    CONCAT(x , y) concatene X et Y (aussi x CONCAT y ou X !! Y) CONCAT(nom, prenom)
    SUBSTR(x, d, l) extrait une partie de x depuis D sur L octets SUBSTR(nom, 1, 10)
    SUBSTR(nom, length(nom), 1)
    LEFT(x, l) extrait une partie de x depuis 1 sur L octets LEFT(nom, 10)
    RIGHT(x, l) extrait les L derniers octets de x RIGHT(nom, 5)
    SPACE(n) retourne n blancs nom concat space(5) concat prenom
    REPEAT(x , n) retourne n fois x repeat('*', 15)
    MOD(x, y) le reste de la division de x par y MOD(annee, 4)
    RRN(fichier) N° de rang RRN(clientp1)
    TRANSLATE(x)
    UPPER(x)
    UCASE(x)
    X en majuscule WHERE
    UCASE(RAISOC) LIKE 'VO%'
    LOWER(x)
    LCASE(x) x en minuscule WHERE
    LCASE(ville) LIKE 'nan%'
    TRANSLATE( x, remplace, origine) Remplace tous les caractères de X présent dans origine par le caractère de même position dans remplace. TRANSLATE(prixc, ' F', '0$')
    remplace 0 par espace et $ par F
    REPLACE( x, origine, remplacement) Remplace la chaîne de caractère origine par la chaîne remplacement dans x REPLACE(x, 'Francs' , 'Euros')
    remplace Francs par Euros
    SOUNDEX( x) Retourne le SOUNDEX (représentation phonétique) de X [basé sur un algorhytme anglo-saxon] Where SOUNDEX(prenom) = SOUNDEX('Henri')
    DIFFERENCE( x) Retourne l'écart entre deux SOUNDEX
    [0 = très différents, 4 = trés proches] Where DIFFRENCE(prenom, 'HENRI)> 2
    VALUE(x, y)
    IFNULL(x, y) retourne X s'il est non null, sinon Y IFNULL(DEPT, 0)
    NULLIF(x, y) retourne NULL si X = Y NULLIF(Prix, 0)
    LOCATE(x, y ,d) retourne la position à laquelle x est présent dans y ou 0
    (la recherche commence en D, qui est facultatif) LOCATE(' ', raisoc)
    POSITION(x IN y) idem LOCATE POSITION(' ' IN raisoc)
    INSERT(x, d, nb, ch) insert ch dans x à la position d, en remplacant nb octets (0 admis)
    DATABASE() retourne le nom de la base (enregistré par WRKRDBDIRE)
    ENCRYPT_RC2(x, p, a) Encrypte x en utilisant p comme mot de passe
    (a est l'astuce mémorisée pour se souvenir du mot de passe)
    ENCRYPT_RC2(data, 'AS400', 'avant I5')
    ENCRYPT_TDES(x, p, a) * Encrypte (algorithme TDES) x en utilisant p comme mot de passe
    (a est l'astuce mémorisée pour se souvenir du mot de passe)
    ENCRYPT_TDES(data, 'AS400', 'avant I5')
    GET-HINT(x) retrouve l'astuce associée à x
    GET-HINT(data) --> 'avant I5'
    DECRYPT_BIT(x) retourne (en varchar for bit data) les données d'origine de x.
    (on doit lancer avant SET ENCRYPTION PASSWORD = p)

    DECRYPT_BINARY(x) retourne (en binary) les données d'origine de x.
    (on doit lancer avant SET ENCRYPTION PASSWORD = p)

    DECRYPT_CHAR(x) retourne (en VARCHAR) les données d'origine de x.
    (on doit lancer avant SET ENCRYPTION PASSWORD = p)

    CASE
    when ... then ..
    when ... then ..
    [else]
    END retourne la valeur du premier THEN ayant la clause WHEN de vérifiée. CASE dept
    WHEN 44 then 'NANTES'
    WHEN 49 then 'ANGERS'
    ELSE 'Hors région'
    END AS METROPOLE

    ou bien

    CASE
    WHEN prix < 0 then 'négatif'
    WHEN codart = 0 then 'inconnu'
    ELSE 'positif ou nul'
    END


    Fonctions OLAP

    ROW_NUMBER() numérote les lignes sur un critère de tri
    select ROW_NUMBER() over (order by prix), codart, libart from articles [order by autre-chose]
    RANK() attribue un rang (en gérant les ex-aequo, par exemple 1-1-3-4-4-4-7)
    select RANK() over (order by prix), codart, libart
    from articles
    DENSE_RANK() attribue un rang consécutif (par exemple 1-1-2-3-3-3-4)
    select DENSE_RANK() over (order by prix), codart, libart from articles



    Cas particulier des dates

    On ne peut utiliser l'arithmétique temporelle qu'avec des dates, des heures, des horodatages

    les calculs peuvent se faire sous la forme
    date + durée = date
    date - durée = date
    date - date = durée
    heure + durée = heure
    etc ..
    les durées peuvent être exprimées de manière explicite avec
    YEARS MONTHS DAYS
    HOURS MINUTES SECONDS

    les durées résultat (datcde - datliv) seront toujours exprimées sous la forme AAAAMMJJ, où :
    AAAA represente le nombre d'années
    MM le nombre de mois
    JJ le nombre de jours


    Ainsi, si SQL affiche 812, il faut comprendre 8 mois, 12 jours
    40301 signifie 4 ans , 03 mois, 01 jour (attention SQL risque d'afficher 40.301)

    Fonctions liées aux dates

    Fonction(x) Retourne ? Exemple
    DATE(x)
    X doit être une chaîne au format SQL
    (celui du JOB par défaut)
    une date (sur laquelle les fonctions suivantes s'appliquent) DATE(
    substr(digits(dat8), 7, 2)
    concat '/' concat
    substr(digits(dat8), 5, 2)
    concat '/' concat
    substr(digits(dat8), 3, 2) )
    DAY(D)
    DAYOFMONTH(D) retourne la partie jour de D
    (doit être une date ou un écart AAAAMMJJ). DAY(DATCDE)
    MONTH(D) retourne la partie mois de D (idem) MONTH(current date)

    YEAR(D) Retourne la partie année de D (idem) YEAR(current date - DATCDE)
    DAYOFYEAR(D) retourne le n° de jour dans l'année (julien) DAYOFYEAR(datdep)
    DAYOFWEEK(D) retourne le N° de jour dans la semaine
    (1 = Dimanche, 2=Lundi, ...) DAYOFWEEK(ENTRELE)
    DAYOFWEEK_ISO(D) retourne le N° de jour dans la semaine
    (1 = Lundi, ...) DAYOFWEEK_ISO(ENTRELE)
    DAYNAME(d) retourne le nom du jour de d (Lundi, Mardi, ...) DAYNAME(datcde)

    MONTHNAME(d) retourne le nom du mois de d (Janvier, Février, ...) MONTHNAME(datcde)

    EXTRACT(day from d) Extrait la partie jour de D (aussi MONTH et YEAR) EXTRACT(MONTH from datcde)

    DAYS(D) retourne le nbr de jours depuis 01/01/0001 DAYS(datcde)- DAYS(datliv)
    QUARTER(D) retourne le n° du trimestre QUARTER(DATEFIN)
    WEEK(D) retourne le n° de semaine
    (Attention 01/01/xx donne toujours semaine 1) WHERE
    WEEK(DATLIV)= WEEK(DATCDE)
    WEEK_ISO(D) retourne le n° de semaine
    (la semaine 1 est celle qui possède un JEUDI dans l'année.) WHERE
    WEEK_ISO(DATLIV)= WEEK_ISO(DATCDE)
    CURDATE() retourne la date en cours, comme CURRENT DATE
    CURTIME() retourne l'heure en cours, comme CURRENT TIME
    NOW() retourne le timestamp en cours
    JULIAN_DAYS(d) retourne le nbr de jours qui sépare une date du 1er Janv. 4712 av JC. JULIAN_DAYS(datcde)

    LAST_DAYS(d) * retourne la date correspondant au dernier jour du mois. LAST_DAYS('2006-04-21') = 2006-04-30

    ADD_MONTHS(d, nbr ) * ajoute un nbr de mois à une date ,
    si la date est au dernier jour du mois, la date calculée est aussi au dernier jour du mois ADD_MONTHS('2006-04-30' , 1) = 2006-05-31

    NEXT_DAYS(d, 'day' ) * retourne le timestamp de la prochaine date ayant le jour demandé NEXT_DAYS('2006-12-31' , 'DIM') = ' 2007-01-07-00.00.00.000000'


    Fonctions liées aux heures

    Fonction(x) Retourne ? Exemple
    TIME(T) une heure TIME(
    substr(digits(h6), 1, 2)
    concat ':' concat
    substr(digits(h6), 3, 2)
    concat ':' concat
    substr(digits(h6), 5, 2) )
    HOUR(T)
    retourne la partie heure de T HOUR(Pointage)
    MINUTE(D) retourne la partie minute de T

    SECOND(T) Retourne la partie secondes de T
    EXTRACT(hour from t) la partie heure de T (aussi MINUTE et SECOND) EXTRACT(SECOND from pointage)


    Fonctions liées aux Timestamp

    Fonction(x) Retourne ? Exemple
    TIMESTAMP(T) un timestamp (date - heure - microsecondes) TIMESTAMP('
    1999-10-06.15.45.00.000001 ')
    TIMESTAMP
    (D T)
    un timestamp (microsecondes à 0) TIMESTAMP(datcde heure)
    TIMESTAMP_ISO(x)

    un timestamp à partier de x
    Si x est une date, l'heure est à 00:00:00
    Si x est une heure, la date est à aujourd'hui. TIMESTAMP_ISO(heure_pointage)
    TIMESTAMPDIFF
    (c 'DIFFERENCE')
    C indique l'unité de mesure de l'écart que vous souhaitez obtenir
    1 = fractions de s. 16 = jours
    2 = secondes 32 = semaines
    4 = minutes 64 = mois
    8 = heures 128 = trimestres
    256 = Année

    'DIFFERENCE' est la représentation caractères [ CHAR(22) ] d'un écart entre deux timestamp.
    TIMLESTAMPDIFF(32 ,
    CAST(CURRENT_TIMESTAMP
    - CAST(DATLIV AS TIMESTAMP)
    AS CHAR(22)) ) indique l'écart en semaines entre DATLIV et aujourd'hui

    MIDNIGHT_SECONDS retourne le nbr de secondes qui sépare un timestamp de minuit MIDNIGHT_SECONDS(pointage)

    VARCHAR_FORMAT(d, 'YYYY-MM-DD HH24:MI:SS' ) * Transforme un timestamp en chaine (le format est imposé) VARCHAR_FORMAT( now() )

    GENERATE_UNIQUE() * genère une valeur unique de type CHAR(13) basée sur le timestamp en cours. insert GENERATE_UNIQUE() ....

    plus toutes les fonctions liées aux dates et aux heures




    Sous sélections, Ordre SQL intégré dans la clause WHERE (ou dans la liste des colonnes) d'un ordre SQL :


    SELECT * FROM tarif WHERE prix < (SELECT AVG(prix) from tarif)

    donne la liste des articles ayant un prix inférieur à la moyenne

    --------------------------------------------------------------------------------
    SELECT * FROM tarif WHERE prix BETWEEN
    (SELECT AVG(prix)*0,9 from tarif) AND (SELECT AVG(prix)*1,1 from tarif)

    donne la liste des articles ayant un prix variant d'au maximum +/- 10 % par rapport à la moyenne

    --------------------------------------------------------------------------------
    SELECT * FROM tarif T WHERE prix <
    (SELECT AVG(prix) from tarif Where famille = t.famille)

    donne la liste des articles ayant un prix inférieur à la moyenne de leur famille

    --------------------------------------------------------------------------------
    Select codart , (qte * prix) as montant, (select sum(qte * prix) from commandes where
    famcod = c1.famcod) as global_famille
    from commandes c1


    donne la liste des commandes (article, montant commandé), en rappelant sur chaque ligne le montant global commandé dans la famille.

    --------------------------------------------------------------------------------

    vous pouvez aussi utiliser la clause EXISTS dans un SELECT imbriqué.

    • Elle indique VRAI si le select imbriqué retourne une ligne (ou plus)
    • Elle indique FAUX si le select imbriqué ne retourne aucune ligne.


    soit un fichier article ayant une colonne "unité_stockage" et un fichier stock,
    il s'agit de supprimer les articles dans le fichiers stock si la zone "unité_stockage" est à blanc dans le fichier article.
    DELETE from stock S where exists
    (SELECT * from articles where codart = S.codart
    and "unité_stockage" = ' ')
    avatar
    Shewolf
    Admin

    Nombre de messages : 190
    Localisation : 91 idf
    Emploi : Analyste Réalisateur / Chef de Projets

    Re: Ordres SQL

    Message par Shewolf le Mar 13 Nov - 11:02


    Contenu sponsorisé

    Re: Ordres SQL

    Message par Contenu sponsorisé


      La date/heure actuelle est Lun 20 Nov - 1:51