Définition
des attributs (champs)
Un attribut
est défini par un nom et un type de donnée.
Le nom est composé de caractères et éventuellement un underscore (tiret
bas). Le nom n'est pas sensible à la casse mais par contre les majuscules et
minuscules peuvent être utilisés pour améliorer la lisibilité.
Le type détermine de manière stricte la nature des données que le champs
peut contenir. De plus il définit également l'ordre de grandeur maximale des
données. Il s'exprime par un nombre entre parenthèses.
Syntaxe :
<nom> <type_donnée>(<ordre_grandeur>)
exemple :
nomTable varchar(20)
L'exemple ci_dessus définit un champs appelé « nomTable » de type
varchar pouvant contenir jusqu'à 20 caractères.
Contraintes
Les contraintes définissent la clé primaire et la/les clé(s)
étrangère(s).
Une clé primaire sert à identifier de manière unique un
enregistrement. En général on utilise un champ du type number mais une
clé primaire peut être basé sur un varchar. Une table peut avoir soit une
clé primaire soit plusieurs dans lequel cas nous parlons de clé primaire
multi-attributs.
Syntaxe: constraint
<nom> primary key(<nomChamp>)
exemple :
constraint pk_test primary key(id_test)
L'exemple ci-dessus crée une clé primaire au nom de « pk_test »
et lie celle-ci au champ « id_test »
Une clé étrangère référence une clé primaire d'une autre table afin
d'étendre les données.
Syntaxe: constraint
<nom> foreign key(<nomChamp>) references
<nomTableEtrangere>(<nomChamps>)
exemple :
contraint fk_test foreign key(externe) references testExterne(id_test)
L'exemple ci-dessus crée un clé étrangère nommée « fk_test » du
champ « externe » et lie celui-ci au champ « id_test » de
la table « textExterne »
Création de la
table
Syntaxe: CREATE
TABLE <nomTable> (<domaine>)
L'instruction de création de table est composé de 3 parties : l'instruction
« CREATE TABLE », le nom de la table et le domaine. Le nom de
la table suit les mêmes règles que nom des champs. Le domaine est composé
de la définition des attributs et des contraintes liés à la table que l'on veut
créer.
Exemple: CREATE
TABLE test (id_test number(2), nom varchar(20), constraint pk_test primary
key(id_test))
L'exemple ci-dessus crée une table de deux attributs (id_test et
nom). L'attribut « id_test » est de type nombre et est limité à
des nombre de 2 chiffres. Le champ « nom » est de type varchar
et peut contenir au maximum 20 caractères.
Séquences
La gestion des clé primaires peut être délégué en créant une séquence..
Syntaxe: CREATE
SEQUENCE <nomSequence>
Exemple: CREATE
SEQUENCE seqTest
L'exemple ci-dessus crée un séquence appelée « seqTest ».
Ce nom sert de référence à utiliser en lieu et place de la donnée lors de
l'insertion.
Exemple: INSERT
INTO test VALUES(seqTest.nextVal, ' hello ')
L'exemple ci-dessus insère automatiquement un nombre pour clé primaire.
Les opérateurs ensemblistes
Les opérateurs ensemblistes sont les outils nécessaire à l'extraction des
données voulues.
La sélection ou
restriction
La sélection s'applique à une seule table. La sélection renvoi les
enregistrements qui répondent à un critère défini.
Notation logique: clientele
WHERE ville= 'BRUXELLES'
Notation SQL :
SELECT * FROM clientele WHERE ville='BRUXELLES'
La projection
La projection s'applique à une seule table. La projection limite les
champs renvoyés.
Notation logique:
pret_hypothecaire[client]
Notation SQL: SELECT
client FROM pret_hypothecaire
De plus l'instruction « DISTINCT » permets d'éliminer les
doublons.
Exemple: SELECT
DISTINCT client FROM pret_hypothecaire
L'union
Cette opération ne s'applique qu'à des tables qui sont compatibles.
Pour cela les tables doivent avoir le même nombre de colonnes ET doivent être
définies sur des domaines compatibles (même nom et type de données).
L'union fusionneras les données de la première table avec celles de la deuxième
table.
Remarque : Le
résultat ne contiendras que des données uniques et distinctes. Pour
obtenir toutes les valeurs il faut utiliser UNION ALL.
Notation logique:
pret_hypothecaire UNION pret_voiture
Notation SQL: SELECT *
FROM pret_hypothecaire UNION SELECT * pret_voiture
La différence
Cet opérateur ne s'applique qu'à des tables compatibles. La
différence des tables T1 et T2 donnera l'ensemble des lignes de T1 non
présentes dans T2.
Notation logique:
pret_hypothecaire[client] MINUS pret_voiture[client]
Notation SQL: SELECT
client FROM pret_hypothecaire MINUS SELECT client FROM pret_voiture
L'intersection
Cet opérateur ne s'applique qu'à des tables compatibles.
L'intersection des tables T1 et T2 donnera l'ensemble des lignes communes aux
deux tables.
Notation logique:
pret_hypothecaire[client] INTERSECT pret_voiture[client]
Notation SQL: SELECT
client FROM pret_hypothecaire INTERSECT SELECT client FROM pret_voiture
Le produit
cartésien
Le produit cartésien de deux tables consiste en une table composée de
toutes les concaténations possibles des lignes de la première table avec les
lignes de la deuxième table. Si T1 a 200 lignes et T2 à 300 T1 X T2
donneras une table de 60000 lignes.
Utilisé seul le produit cartésien ne présente pas beaucoup d'intérêt mais
couplé à des opérateurs de projection il permet de rassembler les informations
dispersé dans des tables différentes.
Notation logique: clientele
TIMES pret_hypothecaire
Notation SQL: SELECT *
FROM clientele, pret_hypothecaire
La jointure
La jointure consiste à regrouper deux tables dans une sur base d'une
information commune. Souvent il s'agit d'une clé primaire situé dans
l'autre table.
Le principe revient à appliquer un produit cartésien et d'ensuite ne garder
que les lignes ayant l'information commune.
Notation logique:
(pret_hypothecaire JOIN clientele) [id_pret_hypothecaire,nom]
Notation SQL (sans l'opérateur JOIN): SELECT * FROM pret_hypothecaire, clientele WHERE client=id_client
Notation SQL (avec l'opérateur JOIN): SELECT * FROM pret_hypothecaire JOIN clientele ON client=id_client
Jointure externe
gauche
Il s'agit d'une jointure de T1 et T2 pour laquelle on conserve toutes les
lignes de T1. Les lignes de T1 pour lesquelles on n'a pas pu associer de
ligne de T2 sont complétées par l'indicateur « null » dans les champs
provenant de T2.
Notation logique: (clientele
LEFT JOIN pret_voiture ON clientele.id_client=pret_voiture.client)[nom]
Notation SQL: SELECT nom
FROM clientele LEFT JOIN pret_voiture ON id_client=client
IS
NULL et IS NOT NULL
Le test si un champ est « null » ou non se fait par l'opérateur
« IS » ou « IS NOT »
Auto-jointure
Il s'agit d'une jointure d'une table avec elle-même. Comme les deux
tables ont le même nom, on doit les renommer pour les distinguer. Ceci
est souvent utilisé pour trouver des paires.
Notation logique: (clientele T1 JOIN clientele T2 ON T1.client>T2.client
WHERE T1.ville=T2.ville)[nom]
Notation SQL: SELECT T1.nom, T2.nom FROM clientele T1 JOIN clientele T2 ON
T1.client>T2.client WHERE T1.ville=T2.ville
Nommer des
colonnes
Par défaut les colonnes portent le nom que l'on a donné lors de leurs
création. Il est possible d'afficher un autre nom en utilisant
l'opérateur AS.
Exemple: SELECT
sname AS nom FROM s
L'exemple ci-dessus affichera « nom » au lieu de l'identifiant
« sname »
Renommer une
table
Pour renommer une table il faut la faire suivre le nom de la table par le
nom qu'on désire lui attribuer.
Exemple: voir
auto-jointure
Les conditions
La clause WHERE exprime une condition de sélection (restriction) à l'aide
de différents opérateurs tels que =, >, <, AND, OR, NOT, BETWEEN,
IN, ANY et EXISTS. Les trois derniers seront vu plus tard.
Extraction d'une
sous-chaine
Substr(c,n,t) : extraction de la sous-chaine c commençant à la position n
sur t caractères.
Mise en
majuscules
Upper(c) : transforme en majuscules la chaine c
Recherche de
sous-chaine
Instr(c1,c2) : renvoie le premier indice d'une sous-chaine c2 dans une
chaine c1.
Exemple: SELECT *
FROM fournisseurs WHERE (instr(noms, 'la')) <> 0
Renvoi les enregistrements de la table fournisseurs dont le nom contiendras
'la' dans leur nom.
Recherche
d'un texte avec l'opérateur IN et substr
Si l'on veut trouver tout les noms des fournisseurs dont la deuxième lettre
est 'o' :
SELECT * FROM fournisseurs WHERE substr(nom,2,1) IN 'o'
Ou bien tout les noms dont la deuxième lettre est 'o' ou 'c'
SELECT * FROM fournisseurs WHERE substr(nom,2,1) IN ('o','c')
Les calculs
Il est possible de faire une opération de calcul qui affecte l'ensemble du
contenu d'une colonne avec les opérateurs +,-,*,/
Exemple: SELECT
id_s,id_p,id_j,qty*500 FROM spj
Les fonctions
d'agrégation
Il est possible de compter le nombres de lignes d'une table, la moyenne
d'une colonne, la somme d'une colonne, de fournir la valeur maximale ou
minimale parmi les contenus d'une colonne avec les fonctions suivantes :
AVG(nom colonne) :
retourne la moyenne de la colonne
AVG(DISTINCT nom colonne) : retourne la moyenne des lignes qui ont des contenus differents pour
une colonne.
COUNT(* ou nom colonne) : retourne le nombre de lignes
COUNT(DISTINCT nom colonne) : retourne le nombre de lignes qui ont des contenus différents pour
une colonne.
MIN(nom colonne) :
retourne la valeur minimale d'une colonne.
MAX(nom colonne) :
retourne la valeur maximale d'une colonne.
SUM(nom colonne) :
retourne la somme d'une colonne.
SUM(DISTINCT nom colonne) : retourne la somme des lignes de contenus différents pour une
colonne.
Ces fonctions s'appellent des fonctions d'agrégation car elles réduisent un
ensemble de valeurs à une valeur unique.
Les fonctions
d'agrégation sur des regroupements de lignes
Les fonctions d'agrégation peuvent s'appliquer sur des groupes de lignes de
table. Il faut alors spécifier les conditions de regroupement dans une
clause GROUP BY.
Exemple: SELECT
id_s, SUM(qty) FROM spj GROUP BY id_s
Le ou les facteurs de regroupement doivent former des groupes dans lesquels
il n'y a qu'une seule valeur possible pour les champs du SELECT
Exemple: SELECT
count(id_s), qty FROM spj GROUP BY qty
Est correcte car chaque regroupement ne donnera qu'une seule ligne de
résultat.
Exemple: SELECT id_s, qty FROM spj GROUP BY qty
Provoque une erreur car il y a plusieurs id_s pour un regroupement.
La clause HAVING
Il est possible d'éliminer des groupes de la solution obtenue par une
requête avec regroupement. La clause HAVING détermine une condition qui
s'applique au groupe. La condition est évaluée pour chaque groupe et les
groupe qui ne satisfont pas à la condition sont éliminés.
La norme SQL
indique que les conditions de la clause HAVING doivent porter soit sur des
champs de la clause SELECT, soit sur une fonction d'agrégation présente ou
absente.
Exemple: SELECT
id_s, count(*) FROM spj GROUP BY id_s HAVING count(*) >2
Est correcte car la clause HAVING porte sur une fonction d'agrégation
présente dans le SELECT.
Exemple: SELECT
id_s, id_p, count(*) FROM spj GROUP BY id_s, id_p HAVING id_p>'P3'
Est correcte car la clause HAVING porte sur un champ présent dans le
SELECT.
Exemple: SELECT
id_s, count(*) FROM spj GROUP BY id_s HAVING SUM(qty) > 800
Est correcte car la clause HAVING porte sur une fonction d'agrégation
absente du SELECT.
Exemple: SELECT
id_s, count(*) FROM spj GROUP BY id_s HAVING qty > 200
N'est pas correcte car la clause HAVING ne porte pas sur une fonction
d'agrégation ou un champ de la clause SELECT. (Plusieurs valeurs de qty
existent dans un groupe)
Remarque: lorsque la
condition de sélection ne porte pas sur une fonction d'agrégation mais porte
sur un des champs de la clause SELECT, on peut alors indifféremment
l'exprimer soit dans la clause WHERE ou dans la clause HAVING.