Bergougnoux Consulting : www.enduser4gl.com
bergou@club-internet.fr
- Attribut : chaque " colonne " de la table est identifiée par un nom (attribut).
- Base de données relationnelles : rangée sous forme de " table " dont les recherches s’effectue à partir des données d’une colonne spécifique (clé) d’une ou des " lignes "(enregistrement) de la " table " (fichier). En exemple, lors d’une recherche dans la base de données relationnelles, on peut associer l’information d’une " colonne " à celle d'une " colonne " correspondante d’une autre " table " afin de produire un troisième rassemblement de certaines " colonnes " des deux tables.
- Clé : Attribut identifiant une ligne ou des lignes de la table;
- Colonnes (columns ) : ensemble de valeurs ou attributs liés à une " ligne " de la table;
- Données : chaque donnée élémentaire appartient à une colonne;
- Ensembliste : C’est la manipulation d’ensemble utilisant le schéma relationnel;
- Ligne (Rows) : Associée à des " colonnes " d’une table.
- Prédicat : expression d’une condition entre deux ou plusieurs constantes ou variables ou autres.
- S.Q.L : Structure Query Language
- S.G.B.D : Systéme de Gestion de Base de Données est un logiciel qui permet de créer,consulter,modifier et supprimer les données de la base.
- Table (table): ensemble de données représentant une structure identifiée par une clé associée à l’ensemble des valeurs ou " colonnes " d’une " ligne " (rows).
1.2 Définition et attendus principaux de S.Q.L :
Définition de S.Q.L : " est le nom générique d’une famille de langages de définition, de manipulation et de contrôle d’une base de données relationnelles " Extrait, Comprendre et évaluer S.Q.L, S.Miranda
1.2.1 Les principaux attendus sont :
1 - Langage de définition du schéma relationnel ou définitions des données ;
2 - Langage de manipulation de la base de données:
il comprend deux sous-ensembles :
- Le langage d’interrogation des données - SELECT -
- des sous-ensembles de commandes : INSERT- UPDATE- DELETE.
3 - Langage de contrôle de la base de données .
il comprend deux sous-ensembles :
- Le langage de contrôle des données - GRANT et REVOKE-
- des sous-ensembles de commandes : CREATE -ALTER-TRUNCATE-DROP- RENAME
1.2.2 Les dimensions de S.Q.L :
- Définition d’une base de données;
- Définition et modification du " schéma " d’une base de données relationnelles;
- Interrogation d’une base de données relationnelles;
- Contrôle de sécurité et d’intégrité de la base de données relationnelles.
1.3 Les différents types d’utilisateurs de S.Q.L :
Ce sont principalement :
- Un utilisateur final exprimant des besoins;
- Un responsable d’application ou un administrateur de base de données en charge de la création des tables et de leurs relations;
- Un développeur d’applications utilisant le langage SQL et un langage de quatrième génération.
C’est l’ expression d’une condition entre deux ou plusieurs constantes ou variables ou autres.
C’est la manipulation d’ensemble utilisant le schéma relationnel.
1.6 Les règles de syntaxe SQL :
Les règles ci-après appartiennent au langage SQL
- Introduction;
- Opérations de projection;
- Opérations de sélection;
- Tri du résultat d’un SELECT;
- Expressions et fonctions;
- La jointure;
- Manipulation d’ensemble;
Nota :
| --> les barres verticales indiquent une alternative entre les options;
[ ] --> des crochets entourent des options facultatives;
.... --> des points de suspension représentent un certains nombres d’options;
SELECT | ALL | | DISTINCT | UNIQUE | | select-list |
FROM table-expr
[ WHERE search-condition ]
[ GROUP BY column-name, ... ]
[ HAVING search-condition ]
[ ORDER BY | expression | [ | ASC | ],]
| integer | | DESC|
select-list:
| expression [ AS alias-name ] |, ...
| table-name.* |
table-expr:
|table-spec |
|table-expr join-type table-spec
[ ON join-condition ] |
|( table-expr, ... ) |
table-spec:
[userid . ] table-name [ [AS] correlation-name]
search-condition :
expr rel-op expr
expr [NOT] BETWEEN expr AND
expr [NOT] in (items)
column_name [NOT] LIKE " string " [ESCAPE escape caractere]
column_name [NOT] MATCH " string " [ESCAPE escape caractere]
expr rel-op (ALL | [ANY | SOME] ( SELECT- statement)
expr [NOT] IN ( SELECT- statement)
[NOT] EXISTS ( SELECT- statement)
collumn-name IS [NOT] NULL
1.6.2 Opérations de projection:
1.6.2.1 Projection de toutes les colonnes :
SELECT * from mp30.POI .
Sélectionne toutes les colonnes de la table mp30.POI .
ou SELECT a.* from mp30.POI a .
1.6.2.2 Projection de colonnes d’une table :
SELECT org, nbr-order, vendor, .... from mp30.POI
ou SELECT a.org, a.nbr-order, a.vendor from mp30.POI a .
ou SELECT org[1,3], a.nbr-order, a.vendor from mp30.POI a .
Nota : [x,y] permet de selectionner une colonne à partir du caractère x sur y positions
ou [y] permet de selectionner une colonne à partir du caractère 1 sur y positions
Effectue une opération arithmétique
SELECT a.org, a,amount * 120.6 " MONTANT TTC ", a.vendor from mp30.POI a .
Affiche un intitulé de la colonne
SELECT a.org ORGANISATION, a.vendor FOURNISSEURS from mp30.POI a .
Affiche une constante pour chaque ligne trouvée.
SELECT a.org, a,amount * 120.6 " MONTANT TTC ", a.vendor from mp30.POI a .
1.6.3 Opérations de sélection:
1.6.3.1 Sélection de lignes d’une table
C’est l’utilisation d’un ou de plusieurs prédicat:
Nom de la colonne Opérateur Nom de la colonne
constante constante
expression expression
SELECT (projection)
FROM (nom de table)
WHERE prédicat
Exemple : Afficher toutes les lignes mp30.poi si l’organisation de mp30.poi est connu dans la table mp30.pom
SELECT a.org, a,amount * 120.6 " MONTANT TTC ", a.vendor, b.org
FROM mp30.POI a , mp30.pom
WHERE a.org = b.org
1.6.3.2 Les opérateurs de sélection :
= égal
!= différent
> supérieur
>= supérieur ou égal
< inférieur
<= inférieur ou égal
[NOT] BEETWEEN.... AND ..... [pas] entre .....et ...
[NOT] IN [pas] dans
[NOT] LIKE [pas] comme
IS NULL est indéfini
IS NOT NULL n’est pas indéfini
= | != | > | < | <=| >= ANY au moins 1
SOME
= | != | > | < | <=| >= ALL tout
Exemple : Afficher toutes les lignes mp30.poi si l’organisation de mp30.poi dont toutes les commandes sont comptrise entre 500000 et 1000000
SELECT a.org, a.amount * 120.6 " MONTANT TTC ", a.vendor
FROM mp30.POI a , mp30.pom b
WHERE a.mount BEETWEEN 500000 AND 1000000
Exemple 1 : afficher toutes les commandes dont le nom de l’acheteur commence par’B’
SELECT buyer, id-nbr-ordr, amount
FROM mp30.pom
WHERE buyer LIKE ‘B%’
nota : % est un caractère " JOKER " de l’opérateur LIKE remplaçant de 0 à n caractères quelconques
Exemple 2 : afficher toutes les commandes dont le nom de l’acheteur comporte les lettres ’B’ et ‘X’
SELECT buyer, id-nbr-ordr, amount
FROM mp30.pom
WHERE buyer LIKE ‘%*BX’ ESCAPE ‘*’
nota : le caractère précisé derrière la clause ESCAPE permet la recherche des 2 caractères " B " et " X " dans la chaine de caractères.
Nota : l’extension de l’opérateur est MATCHES -extension spécifique de " INFORMIX "-
Permet la recherche d’égalité entre la variable et le liste des valeurs
Exemple : Afficher toute les commandes en cours de confirmation
SELECT * FROM mp30.pom
WHERE stts-code in (" 01 " ," 08 " )
1.6.3.6 Opérateurs ANY, SOME et ALL :
Ils se combinent avec l’un des opérateurs arithmétiques
Exemple :
SELECT * FROM mp30.pom
WHERE stts-code = ANY (" 01 " ," 08 " )
l’opérateur ANY est équivalent à l’opérateur SOME
la condition ci-dessus =ANY est équivalente à la condition IN
1.6.3.7 Opérateur IS NULL et IS NOT NULL:
Rappel : NULL = non défini
Exemple : Affiche pour les demandes d’achats dont le fournisseur n’est pas renseigné
SELECT * from mp30.POM
WHERE vendor is NULL
1.6.4 Les prédicats multiples :
Intersection et union dans la selection par les opérateurs logiques AND et OR.
Exemple : Selection des toues les lignes commandes et entete de commande de la même organisation
SELECT a.org, a.amount * 120.6 " MONTANT TTC ", a.vendor
FROM mp30.POI a , mp30.pom b
WHERE a.mount BEETWEEN 500000 AND 1000000
AND a. org = b.org
Nota: Le " AND " est prioritaire sur le " OR "
1.6.5 Les tris d’un SELECT - ORDER BY- :
Les tris s’effectuent d’après les colonnes des tables de façon croissante(ASC) ou décroissante(DESC).
Exemple : tri des commandes par fournisseur et par numéro decommande DECROISSANT.
SELECT a.org,a.id-nbr-ordr, a.amount * 120.6 " MONTANT TTC ", a.vendor
FROM mp30.POI a , mp30.pom b
WHERE a.mount BEETWEEN 500000 AND 1000000
AND a. org = b.org
ORDER BY a.vendor, a. id-nbr-ordr DESC
ou ORDER BY 4,2 DESC
Tri implicite sur les colonnes et élimine les valeurs dupliquées .
Exemple : Afficher une ligne unique de la commande en ignorant les lignes détails de la commande
mp30.poi est la table des lignes détails d’une commande
SELECT DISTINCT id-nbr-ordr
FROM mp30.poi
Sélectionner sur une même ligne RESULTAT des informations issues de plusieurs tables
SELECT a.org, a,amount * 120.6 " MONTANT TTC ", a.vendor, b.org, b.
FROM mp30.POI a , mp30.pom b
WHERE a.org = b.org
deux tables : mp30.POI a , mp30.pom b
1.6.7 La Manipulations d’ensembles:
C’est la manipulation d’ensembles liés par plusieurs opérateurs et employant la clause SELECT.
Les régles sont identiques à celles déjà définies dans les opérations de projection .
SELECT * from mp30.poi
UNION
SELECT * from mp30.pom
1.6.7.1 Les sous interrogations:
evaluela sous interrogation de niveau le plus bas et interroge les niveaux supérieurs avec le résultat trouvé pour chaque niveau.
Exemple : affiche toutes les lignes détails des commandes dont l’entete de commande n’est pas en statut " non libérée.
SELECT a.nbr_id, a.code-sttss
FROM mp30.poi a
WHERE a.ssts not in ( select b.code-stts from mp30.poi b
WHERE b.sttts = (select ..........from
where .........)
1.6.7.2 Les sous interrogations multiples :
SELECT a.nbr_id, a.code-sttss, a.org
FROM mp30.poi a
WHERE a.ssts not in ( select b.code-stts from mp30.poi b
WHERE b.sttts = " 01 ")
AND a.org = (select ..........from
where .........)
1.6.7.3 Les interrogations complexes :
1.6.7.3.1 Opérateurs EXISTS ou NOT EXISTS :
Execute la condition principale selon qu’il existe au moins une ligne répondant
Exemple : affiche le nombre de couples Articles/Fournisseurs inconnus entre 2 tables .
SELECT count(*) INCONNU,
from mp30-vpm a
WHERE NOT EXISTS ( select b.part-nbr, b.vend-id
from mp30-pvv b
WHERE a.part_nbr = b.part_nbr
and a.vend-id = b.vend-id)
1.6.8 Les fonctions de groupe:
Ensemble de lignes regroupées selon un critére de regroupement
1.6.8.1 Liste des fonctions de groupe:
AVG (expr) moyenne
COUNT (expr) nombre
MAX (expr) valeur maxi
MIN (expr) valeur mini
SUM (expr) somme
1.6.8.1.1 La fonction AVG -moyenne -:
Exemple : Calcul la moyenne du chiffre d’affaire d’un fournisseur
SELECT a.vend_id ,
AVG(a.tot_hcur_amt) MOYENNE,
from mp30_pom a
1.6.8.1.2 La fonction SUM -somme -:
SELECT a.vend_id ,
SUM(a.tot_hcur_amt) TOTAL_CA,
from mp30_pom a
1.6.8.1.3 La fonction COUNT -nombre -:
Exemple : affiche le nombre de couples Articles/Fournisseurs inconnus entre 2 tables .
SELECT count(*) INCONNU,
from mp30-vpm a
WHERE NOT EXISTS ( select b.part-nbr, b.vend-id
from mp30-pvv b
WHERE a.part_nbr = b.part_nbr
and a.vend-id = b.vend-id)
1.6.8.2 Création de groupes -GROUP BY-:
Crée à partir d’un ensemble, des sous-ensembles définis.
Exemple : Affiche le nombre de commandes par fournisseur
SELECT a.vend_id, count(*) a.id_ordr_nbr
from mp30_pom a
GROUP BY a.vend_id
1.6.8.3 Selection de groupes - HAVING-:
Permet de sélectionner des groupes répondant à certains critéres.
Exemple : Affiche au moins 5 commandes par fournisseur
SELECT a.vend_id, count(*) a.id_ordr_nbr
from mp30_pom a
GROUP BY a.vend_id
HAVING count (*) > 4
Le verbe UPDATE permet de modifir une ou des colonnes d’une ou de plusieurs lignes
UPDATE table-name SET {column- name = expression [,...]
| {(col_list | * = (expr-list)}
[WHERE condition ]
Exemple : Pratiquer aux transitions de statut des commandes en " attente " en commande en " cours "
UPDATE mp30_pom
set code_stts = " 08 " WHERE code_stts in (" 00 ", "01 ")