• Tables utilisées dans les exemples

La commande SELECT constitue, à elle seule, le langage permettant d'interroger une base de données. Elle permet :

• Sélection de colonnes ou projection

La commande SELECT la plus simple a la syntaxe suivante :

SELECT *
FROM  nom_table ;

• nom_table : est le nom de la table sur laquelle porte la sélection.
• * : signifie que toutes les colonnes de la table sont sélectionnées.

SELECT nom_col1, nom_col2, ...
FROM  nom_table ;

La clause DISTINCT ajoutée derrière la commande SELECT permet d'éliminer les duplications.

• Sélection de lignes ou restriction

La clause WHERE permet de spécifier quelles sont les lignes à sélectionner. Elle est suivie d'un prédicat qui sera évalué pour chaque ligne de la table. Les lignes pour lesquelles le prédicat est vrai seront sélectionnées.

SELECT *
FROM  nom_table
WHERE predicat ;

On peut, en SQL, exprimer des expressions plus complexes en utilisant des opérateurs et des fonctions étudiés dans le chapitre Expressions et fonctions.

• Prédicat simple

• Prédicats composés

Les opérateurs logiques AND (et) et OR (ou inclusif) peuvent être utilisés pour combiner entre eux plusieurs prédicats. L'opérateur NOT placé devant un prédicat en inverse le sens.

L'opérateur AND est prioritaire par rapport à l'opérateur OR. Des parenthèses peuvent être utilisées pour imposer une priorité dans l'évaluation du prédicat, ou simplement pour rendre plus claire l'expression logique.

La requête précédente donnerait le même résultat sans les parenthèses, résultat différent de celui du SELECT suivant.

• Valeurs NULL

Pour SQL, une valeur NULL est une valeur non définie. Il est possible d'ajouter une ligne à une table sans spécifier de valeur pour les colonnes non obligatoires : ces colonnes absentes auront la valeur NULL.

Par exemple les employés dont la rémunération ne prend pas en compte de commission auront une valeur NULL, c'est-à-dire indéfinie, comme commission.

L'opérateur IS NULL permet de tester la valeur NULL : le prédicat expr IS NULL est vrai si l'expression a la valeur NULL (c'est-à-dire si elle est indéfinie).

Exemple : Quels sont les employés dont la commission a la valeur NULL?

L'opérateur IS NOT NULL permet de construire un prédicat vrai si la valeur n'est pas NULL (et donc le prédicat expr IS NOT NULL est vrai si expr est définie)

• La valeur NULL est différente de la valeur zéro qui, elle, qui est une valeur bien définie.
• Le prédicat expr = NULL est toujours faux, et ne permet donc pas de tester si l'expression a la valeur NULL.
• Une expression de la forme NULL + val donne NULL comme résultat quelle que puisse être la valeur de val.

• Nom de colonne

Les colonnes constituant le résultat d'un SELECT peuvent être renommées dans le SELECT, ceci est utile en particulier lorsque la colonne résultat est une expression. Pour cela, il suffit de faire suivre l'expression définissant la colonne d'un nom, selon les règles suivantes :

• le nom (30 caractères maximum) est inséré derrière l'expression définissant la colonne, séparé de cette dernière par un espace.
• si le nom contient des séparateurs (espace, caractère spécial), ou s'il est identique à un mot clé de SQL (ex : DATE), il doit être mis entre guillemets "".

Ce nom est celui sous lequel la colonne sera connue des interfaces externes. Sous SQLPLUS, par exemple, il constituera le titre par défaut de la colonne, et servira de référence pour définir un format pour la colonne.

Remarque : Attention, ce nom n'est pas connu à l'intérieur du SELECT.

• Classer le résultat d'une interrogation

Les lignes constituant le résultat d'un SELECT sont obtenues dans un ordre indéterminé. On peut, dans un SELECT, demander que le résultat soit classé dans un ordre ascendant ou descendant, en fonction du contenu d'une ou plusieurs colonnes (jusqu'à 16 critères de classement possibles). Les critères de classement sont spécifiés dans une clause ORDER BY dont la syntaxe est la suivante :

ORDER BY {nom_col1 | num_col1 [DESC] [, nom_col2 | num_col2 [DESC],...]}

Le classement se fait d'abord selon la première colonne spécifiée dans l'ORDER BY puis les lignes ayant la même valeur dans la première colonne sont classées selon la deuxième colonne de l'ORDER BY, etc... Pour chaque colonne, le classement peut être ascendant (par défaut) ou descendant (DESC).
L'ORDER BY peut faire référence à une colonne par son nom ou par sa position dans la liste des colonnes présentes derrière le SELECT (la première colonne sélectionnée a le numéro 1, la deuxième a le numéro 2, ...).

Remarque : Dans un classement les valeurs NULL sont toujours en tête quel que soit l'ordre du classement (ascendant ou descendant).

• Syntaxe du Select

SELECT [DISTINCT | ALL]
   { *
    | { [schema.]{table | view | snapshot}.* | expr [c_alias]
         [, { [schema.]{table | view | snapshot}.* | expr [c_alias] } ] ... }
FROM [schema.]{table | view | snapshot} [t_alias]
     [, [schema.]{table | view | snapshot} [t_alias] ] ...
[WHERE condition ]
[ [START WITH condition] CONNECT BY condition]
[GROUP BY expr [, expr] ... [HAVING condition] ]
[{UNION | UNION ALL | INTERSECT | MINUS} SELECT command ]
[ORDER BY {expr|position} [ASC | DESC] [, {expr | position} [ASC | DESC]] ...]
[FOR UPDATE [OF [[schema.]{table | view}.]column
                 [, [[schema.]{table | view}.]column] ...] [NOWAIT] ]

• DISTINCT

• ALL

• *

renvoie toutes les colonnes de toutes les tables, les vues et les clichés précisés dans le FROM.

• table.*, view.*, snapshot.*

• expr

sélectionne une expression habituellement calculée sur les valeurs des colonnes appartenant à l'une des tables, vues, ou clichés de la clause FROM.

• c_alias

la chaîne de caractères qui sert d'en-tête à la colonne (par défaut expr)

• schema

• table, view, snapshot

est le nom de la table, de la vue ou du cliché contenant les données sélectionnées.

 

• t_alias

• WHERE

restreint les lignes sélectionnées à celles pour lesquelles la condition est vraie. Si cette clause est omise, toutes les lignes des tables, vues ou clichés précisés derrière le FROM sont renvoyées.

• START WITH, CONNECT BY

• GROUP BY

groupe les lignes sélectionnées en se basant sur la valeur de expr pour chaque ligne et renvoie une seule ligne par groupe.

• HAVING

• UNION, UNION ALL, INTERSECT, MINUS

Combine les lignes retournées par deux SELECT en utilisant une opération ensembliste.

• ORDER BY

ordonne les lignes sélectionnées :

expr

en utilisant la valeur de expr. Cette expression est basée sur des colonnes précisées derrière le SELECT ou sur des colonnes appartenant à des tables, vues ou clichés présents derrière le FROM.

position

donne le numéro de la colonne dans l'ordre du SELECT.

ASC, DESC

mode ascendant ou descendant. La valeur par défaut ASC.

• FOR UPDATE

• NO WAIT

retourne le controle à l'utilisateur si la commande SELECT essaye de bloquer une table utilisée par un autre utilisateur.

• Prérequis

Pour pouvoir sélectionner des lignes d'un objet(table, vue, cliché) il faut soit être propriétaire de cet objet, soit avoir le privilège SELECT sur cet objet.

Le privilège SELECT ANY TABLE permet de sélectionner des lignes de n'importe quel objet appartenant à n'importe quel utilisateur.

La jointure est une opération permettant de combiner des informations venant de plusieurs tables. Les exemples suivants se limiteront à deux tables, mais on peut joindre jusqu'à 256 tables. Une jointure se formule simplement en spécifiant plusieurs tables derrière le FROM de la façon suivante :

SELECT ...
FROM  nom_table1, nom_table2...
WHERE predicat;

Si on ne précise pas de condition de sélection, le résultat obtenu sera le produit cartésien des tables présentes derrière le FROM (résultat non souhaité en général).

• Equi-jointure

Le rapprochement de chaque ligne de la table emp avec la ligne de la table dept ayant même numéro de département permet d'obtenir la liste des employés avec la localité dans laquelle ils travaillent. Ce rapprochement entre deux colonnes appartenant à deux tables différentes mais ayant le même sens (ici le numéro de département) et venant vraisemblablement d'une relation 1-n lors de la conception (ici 1 entité département pour n entités employés) est assez naturel. C'est pourquoi ce type de jointure porte le nom de jointure naturelle ou d'équi-jointure.

Le fait que la colonne contenant le numéro de département ait le même nom dans les deux tables a rendu nécessaire le préfixage par le nom de table dans le critère de jointure (clause WHERE). Le nom de colonne nom a lui aussi besoin d'être préfixé car il appartient aux deux tables (nom de la personne dans l'une et nom du département dans l'autre). Par contre le nom de colonne lieu n'a pas besoin d'être préfixé car il n'y a pas d'ambiguïté sur la table à laquelle cette colonne appartient.

• Jointure d'une table à elle-même

Remarque : Dans ce cas, il faut impérativement renommer au moins l'une des deux occurences de la table (ici emp) en lui donnant un synonyme, afin de pouvoir préfixer sans ambiguité chaque nom de colonne.

• Autres jointures

Exemple : Quels sont les employés gagnant plus que SIMON?

• Jointure externe

Exemple : Le département 40 ne figurait pas dans le résultat du SELECT précédent. Par contre, il figurera dans le résultat du SELECT suivant.

Le (+) peut s'interpréter comme l'ajout d'une ligne fictive dont toutes les colonnes ont la valeur NULL, et qui réalise la correspondance avec les lignes de l'autre table qui n'ont pas de correspondant réel. Dans l'exemple ci-dessus, la valeur de nom associée au département 40 est la valeur NULL.

• l'union : UNION
• l'intersection : INTERSECT
• la différence relationnelle : MINUS

SELECT ...   {UNION | INTERSECT | MINUS } SELECT ...

• Tous les SELECT doivent avoir le même nombre de colonnes sélectionnées, et leur types doivent être un à un identiques. Les conversions éventuelles doivent être faites à l'intérieur du SELECT à l'aide des fonctions de conversion.
• Les doubles sont éliminés (DISTINCT implicite).
• Les noms de colonnes (titres) sont ceux du premier SELECT.
• La largeur des colonnes est la plus grande parmi tous les SELECT.
• Dans une requête on ne peut trouver qu'un seul ORDER BY. S'il est présent, il doit être mis dans le dernier SELECT et il ne peut faire référence qu'aux numéros des colonnes et non pas à leurs noms (car les noms peuvent être différents dans chacune des interrogations).

On peut combiner le résultat de plus de deux SELECT au moyen des opérateurs UNION, INTERSECT, MINUS.

SELECT ... UNION SELECT ... MINUS SELECT ...

SELECT ...
UNION  (SELECT ...
        MINUS
        SELECT ...)

Une caractéristique puissante de SQL est la possibilité qu'un critère de recherche employé dans une clause WHERE (expression à droite d'un opérateur de comparaison) soit lui-même le résultat d'un SELECT ; c'est ce qu'on appelle une sous-interrogation.

• Sous-interrogation ramenant une seule valeur

• Sous-interrogation ramenant plusieurs lignes

• l'opérateur IN
• les opérateurs obtenus en ajoutant ANY ou ALL à la suite d'un opérateur de comparaison classique (=, !=, >, >=, <, <=)
• ANY: la comparaison est vraie si elle est vraie pour au moins un des éléments de l'ensemble.
• ALL: la comparaison sera vraie si elle est vraie pour tous les éléments de l'ensemble.

• Sous-interrogation ramenant plusieurs colonnes

Il est possible de comparer le résultat d'un SELECT ramenant plusieurs colonnes à une liste de colonnes. La liste de colonnes figurera entre parenthèses à gauche de l'opérateur de comparaison.

Exemple : Quels sont les employés ayant même fonction et même supérieur que CODD?

• Sous-interrogation synchronisée avec l'interrogation principale

Il a fallu ici renommer la table emp de l'interrogation principale pour pouvoir la référencer dans la sous-interrogation.
n_sup IS NOT NULL est nécessaire car dans le cas de JOUBERT la colonne n_sup est NULL et la sous-requête ne ramène alors aucune valeur.

• Sous-interrogation ramenant au moins une ligne

L'opérateur EXISTS permet de construire un prédicat vrai si la sous-interrogation qui suit ramène au moins une ligne.

Remarque : On peut inverser le sens de l'opérateur EXISTS en le faisant précéder de NOT.

• Sous-interrogations multiples

Un SELECT peut comporter plusieurs sous-interrogations, soit imbriquées, soit au même niveau dans différents prédicats combinés par des AND ou des OR.

Exemple : Liste des employés du département 10 ayant même fonction que quelqu'un du département de DUPONT.

• en tant que colonne résultat d'un SELECT,
• dans une clause WHERE,
• dans une clause ORDER BY.

• Expressions et fonctions arithmétiques

• Fonctions arithmétiques

Dans ce paragraphe, ont été regroupées les fonctions ayant un ou plusieurs nombres comme arguments, et renvoyant une valeur numérique. [ROUND(n ,m )]

• ABS(nb)

Renvoie la valeur absolue de nb.

• CEIL(nb)

Renvoie le plus petit entier supérieur ou égal à nb.

• COS(n)

Renvoie le cosinus de n, n étant un angle exprimé en radians.

• COSH(n)

Renvoie le cosinus hyperbolique de n.

• EXP(n)

Renvoie e puissance n.

• FLOOR(nb)

Renvoie le plus grand entier inférieur ou égal à nb.

• LN(n)

Renvoie le logarithme népérien de n qui doit être un entier strictement positif.

• LOG(m,n)

Renvoie le logarithme en base m de n. m doit être un entier strictement supérieur à 1, et n un entier strictement positif.

• MOD(m,n)

Renvoie le reste de la division entière de m par n, si n vaut 0 alors renvoie m. Attention, utilisée avec au moins un de ses arguments négatifs, cette fonction donne des résultats qui peuvent être différents d'un modulo classique. Cette fonction ne donne pas toujours un résultat dont le signe du diviseur.

• POWER(m,n)

Renvoie m puissance n, m et n peuvent être des nombres quelconques entiers ou réels mais si m est négatif n doit être un entier.

• ROUND(n[,m])

Si m est positif, renvoie n arrondi (et non pas tronqué) à m chiffres après la virgule. Si m est négatif, renvoie n arrondi à m chiffres avant la virgule. m doit être un entier et il vaut 0 par défaut.

• SIGN(nb)

Renvoie -1 si nb est négatif, 0 si nb est nul, 1 si nb est positif.

• SIN(n)

Renvoie le sinus de n, n étant un angle exprimé en radians.

• SINH(n)

Renvoie le sinus hyperbolique de n.

• SQRT(nb)

Renvoie la racine carrée de nb qui doit être un entier positif ou nul.

• TAN(n)

Renvoie la tangente de n, n étant un angle exprimé en radians.

• TANH(n)

• TRUNC(n[,m])

Si m est positif, renvoie n arrondi tronqué à m chiffres après la virgule. Si m est négatif, renvoie n tronqué à m chiffres avant la virgule. m doit être un entier et il vaut 0 par défaut.

• Expressions et fonctions sur les chaînes de caractères

Il existe un seul opérateur sur les chaînes de caractères : la concaténation. Cet opérateur se note au moyen de deux caractères |(barre verticale) accolés. Le résultat d'une concaténation est une chaîne de caractères obtenue en écrivant d'abord la chaîne à gauche de || puis celle à droite de ||.

• CONCAT(chaîne1,chaîne2)

Renvoie la chaîne obtenue en concaténant chaîne1 à chaîne2. Cette fonction est équivalente à l'opérateur de concaténation | |.

• INITCAP(chaîne)

Renvoie chaîne en ayant mis la première lettre de chaque mot en majuscule et toutes les autres en minuscule. Les séparateurs de mots sont les espaces et les caractères non alphanumériques.

• LOWER(chaîne)

Renvoie chaîne en ayant mis toutes ses lettres en minuscules.

• LPAD(chaîne,long,[char])

Renvoie la chaîne obtenue en complétant, ou en tronquant, chaîne pour qu'elle ait comme longueur long en ajoutant éventuellement à gauche le caractère (ou la chaîne de caractères) char. La valeur par défaut de char est un espace.

• LTRIM(chaîne[,ens])

Renvoie la chaîne obtenue en parcourant à partir de la gauche chaîne et en supprimant tous les caractères qui sont dans ens. On s'arrête quand on trouve un caractère qui n'est pas dans ens. La valeur de defaut de ens est un espace.

• REPLACE(chaine,avant,après

Renvoie chaine dans laquelle toutes les occurrences de la chaîne de caractères avant ont été remplacés par la chaîne de caractèresaprès.

• RPAD(chaîne,n,[char])

Renvoie la chaîne obtenue en complétant, ou en tronquant, chaîne pour qu'elle ait comme longueur long en ajoutant éventuellement à droite le caractère (ou la chaîne de caractères) char. La valeur par défaut de char est un espace.

• RTRIM(chaîne[,ens])

Renvoie la chaîne obtenue en parcourant à partir de la droite chaîne et en supprimant tous les caractères qui sont dans ens. On s'arrête quand on trouve un caractère qui n'est pas dans ens. La valeur de defaut de ens est un espace.

• SOUNDEX(chaîne)

Renvoie la chaîne de caratères constituée de la représentation phonétique des mots de chaîne.

• SUBSTR(chaîne,m[,n])

Renvoie la partie de chaîne commençant au caractère m et ayant une longueur de n.

• TRANSLATE(chaîne, avant, après)

Renvoie une chaîne de caractères en remplaçant chaque caractère de chaîne présent dans avant par le caractère situé à la même position dans après. Les caractères de chaîne non présents dans avant ne sont pas modifiés. avant peut contenir plus de caractères que après, dans ce cas les caractères de avant sans correspondants dans après seront supprimés de chaîne .

• UPPER(chaîne)

Renvoie chaîne en ayant mis toutes ses lettres en majuscules.

• INSTR(chaîne, sous-chaîne, debut, occ)

Renvoie la position du premier caractère de chaîne correspondant à l'occurrence occ de sous-chaîne en commençant la recherche à la position début.

• LENGTH(chaîne)

Renvoie la longueur de chaîne, exprimée en nombre de caractères.

• Expressions et fonctions sur les dates

• date +/- nombre : le resultat est une date obtenue en ajoutant le nombre de jours nombre à la date date.
• date2 - date1 : le resultat est le nombre de jours entre les deux dates.

• Fonctions sur les dates

• ADD_MONTHS(date,n)

Renvoie la date obtenue en ajoutant n mois à date. n peut être un entier quelconque. Si le mois obtenu a moins de jours que le jour de date, le jour obtenu est le dernier du mois.

• LAST_DAY(date)

Renvoie la date du dernier jour du mois de date.

• MONTHS_BETWEEN(date2, date1)

Renvoie le nombre de mois entre date2 et date1, si date2 est après date1 le résultat est positif, sinon le résultat est négatif. Si les jours date2 et date1 sont les mêmes, ou si ce sont les derniers jours du mois, le résultat est un entier. La partie fractionnaire est calculée en considérant chaque jour comme 1/31ème de mois

• NEXT_DAY(date,nom_du_jour)

Renvoie la date du prochain jour de la semaine dont le nom est nom_de_jour.

• ROUND(date[,précision])

Renvoie date arrondie à l'unité spécifiée dans précision. L'unité de précision est indiquée en utilisant un des masques de mise en forme de la date. On peut ainsi arrondir une date à l'année, au mois, à la minute,... Par défaut la précision est le jour.

• SYSDATE

• TRUNC(date[,précision])

Renvoie date tronquée à l'unité spécifiée dans précision. Les paramètres sont analogues à ceux de la fonction ROUND.

• Fonctions de conversion

• ASCII(chaîne)

Renvoie le nombre correspondant au code ascii du premier caractère de chaine.

• CHR(nombre)

Renvoie le caractère dont nombre est le code ascii.

• TO_CHAR(nombre, format)

Renvoie la chaîne de caratères en obtenue en convertissant nombre en fonction de format.
Format est une chaîne de caractères pouvant contenir les caractères suivants :

9

représente un chiffre (non représenté si non significatif)

0

représente un chiffre (représenté même si non significatif)

.

point décimal apparent

V

définit la position du point décimal non apparent

,

une virgule apparaîtra à cet endroit

$

un $ précédera le premier chiffre significatif

B

le nombre sera représenté par des blancs s'il vaut 0

EEEE

le nombre sera représenté avec un exposant (le spécifier avant MI ou PR)

MI

le signe négatif sera à droite

PR

un nombre négatif sera entre <>

• TO_CHAR(date, format)

Renvoie conversion d'une date en chaîne de caractères. Le format indique quelle partie de la date doit apparaître, c'est une combinaison des codes suivants :

scc

siècle avec signe

cc

siècle

sy,yyy

année (avec signe et virgule)

y,yyy

année( avec virgule)

yyyy

année

yyy

3 derniers chiffres de l'année

yy

2 derniers chiffres de l'année

y

dernier chiffre de l'année

q

numéro du trimestre dans l'année

ww

numéro de la semaine dans l'année

w

numéro de la semaine dans le mois

mm

numéro du mois

ddd

numéro du jour dans l'année

dd

numéro du jour dans le mois

d

numéro du jour dans la semaine

hh ou hh12

heure (sur 12 heures)

hh24

heure sur 24 heures

mi

minutes

ss

secondes

sssss

secondes après minuit

j

jour du calendrier julien

syear ou year

année en toutes lettres

month

nom du mois

mon

nom du mois abrégé sur 3 lettres

day

nom du jour

dy

nom du jour abrégé sur 3 lettres

am ou pm

indication am ou pm

bc ou ad

indication avant ou après Jésus Christ

th

ajout du suffixe ordinat st, nd, rd, th

sp

nombre en toutes lettres

• TO_DATE(chaîne, format)

Permet de convertir une chaîne de caractères en donnée de type date. Le format est identique à celui de la fonction TO_CHAR.

• TO_NUMBER(chaîne)

Exemple : Donner la liste de tous les employés dont le nom ressemble à DUPONT.

• Autres fonctions

• GREATEST(expr1, expr2,...)

Renvoie la plus grande des valeurs expr1, expr2,.... Toutes les expressions sont converties au format de expr1 avant comparaison.

• LEAST

Renvoie la plus petite des valeurs expr1, expr2,.... Toutes les expressions sont converties au format de expr1 avant comparaison.

• NVL(expr_1, expr_2)

Prend la valeur expr_1, sauf si expr_1 est NULL auquel cas NVL prend la valeur expr_2.
Une valeur NULL en SQL est une valeur non définie.
Lorsque l'un des termes d'une expression a la valeur NULL, l'expression entière prend la valeur NULL. D'autre part, un prédicat comportant une comparaison avec une expression ayant la valeur NULL prendra toujours la valeur faux. La fonction NVL permet de remplacer une valeur NULL par une valeur significative.

• DECODE(crit, val_1, res_1 [, val_2, res_2 ...], def)

Cette fonction permet de choisir une valeur parmi une liste d'expressions, en fonction de la valeur prise par une expression servant de critère de sélection.
Le résultat récupéré est :

res_1 si l'expression crit a la valeur val_1
res_2 si l'expression crit a la valeur val_2
def (la valeur par défaut) si l'expression crit n'est égale à aucune des expressions val_1, val_2,...,.

Les expressions résultats res_1, res_2, ..., def peuvent être de types différents : caractère et numérique, ou caractère et date (le résultat est du type de la première expression rencontré dans le DECODE).

La fonction DECODE permet également de mélanger dans une colonne résultat des informations venant de plusieurs colonnes d'une même table.

Dans les exemples précédents, chaque ligne résultat d'un SELECT était le résultat de calculs sur les valeurs d'une seule ligne de la table consultée. Il existe un autre type de SELECT qui permet d'effectuer des calculs sur l'ensemble des valeurs d'une colonne. Ces calculs sur l'ensemble des valeurs d'une colonne se font au moyen de l'une des fonctions suivantes :

• AVG([DISTINCT | ALL] expression)

Renvoie la moyenne des valeurs d'expression.

• COUNT(* | [DISTINCT | ALL] expression)

Renvoie le nombre de lignes du résultat de la requête. Si expression est présent, on ne compte que les lignes pour lesquelles cette expression n'est pas NULL.

• MAX([DISTINCT | ALL] expression)

Renvoie la plus petite des valeurs d'expression.

• MIN([DISTINCT | ALL] expression)

Renvoie la plus grande des valeurs d'expression.

• STDDEV([DISTINCT | ALL] expression)

Renvoie l'écart-type des valeurs d'expression.

• SUM([DISTINCT | ALL] expression)

• VARIANCE([DISTINCT | ALL] expression)

Renvoie la variance des valeurs d'expression.

• DISTINCT

• ALL

• Ces SELECT sont différents de ceux vus précédemment. Il est, par exemple, impossible de demander en résultat à la fois une colonne et une fonction de groupe.
• un SELECT comportant une fonction de groupe peut être utilisé dans une sous-interrogation.

• Valeurs NULL

Aucune des fonctions de groupe ne tient compte des valeurs NULL à l'exception de count(*). Ainsi, SUM(col) est la somme des valeurs non NULL de la colonne col. De même AVG est la somme des valeurs non NULL divisée par le nombre de valeurs non NULL.

Il est possible de subdiviser la table en groupes, chaque groupe étant l'ensemble des lignes ayant une valeur commune. C'est la clause GROUP BY qui permet de découper la table en plusieurs groupes :

GROUP BY expr_1, expr_2, ...

Si on a une seule expression, ceci définit les groupes comme les ensembles de lignes pour lesquelles cette expression prend la même valeur. Si plusieurs expressions sont présentes les groupes sont définis de la façon suivante : parmi toutes les lignes pour lesquelles expr_1 prend la même valeur, on regroupe celles ayant expr_2 identique, ... Un SELECT de groupe avec une clause GROUP BY donnera une ligne résultat pour chaque groupe.

Remarque : Dans la liste des colonnes résultat d'un SELECT comportant une fonction de groupe, ne peuvent figurer que des caractéristiques de groupe, c'est-à-dire :

• soit des fonctions de groupe ;
• soit des expressions figurant dans le GROUP BY.

De la même façon qu'il est possible de sélectionner certaines lignes au moyen de la clause WHERE, il est possible dans un SELECT comportant une fonction de groupe de sélectionner par la clause HAVING, qui se place après la clause GROUP BY.

Le prédicat dans la clause HAVING suit les mêmes règles de syntaxe qu'un prédicat figurant dans une clause WHERE.

Cependant, il ne peut porter que sur des caractéristiques du groupe : fonction de groupe ou expression figurant dans la clause GROUP BY, dans ce cas la clause HAVING doit être placée après la clause GROUP BY.

Remarque : Un SELECT de groupe peut contenir à la fois une clause WHERE et une clause HAVING. La clause WHERE sera d'abord appliquée pour sélectionner les lignes, puis les groupes seront constitués à partir des lignes sélectionnées, et les fonctions de groupe seront évaluées.

Une clause HAVING peut comporter une sous-interrogation.

Il est possible d'appliquer au résultat d'un SELECT avec GROUP BY un deuxième niveau de fonction de groupe.

Exemple : la fonction MAX peut être appliquée aux nombres d'employés de chaque département pour obtenir le nombre d'employés du département ayant le plus d'employés.

• Définition

• Ajout de lignes

La commande INSERT permet d'insérer une ligne dans une table en spécifiant les valeurs à insérer. La syntaxe est la suivante :

INSERT INTO nom_table(nom_col1, nom_col2, ...)
VALUES (val1, val2...)

La liste des noms de colonne est optionnelle. Si elle est omise, la liste des colonnes sera par défaut la liste de l'ensemble des colonnes de la table dans l'ordre de la création de la table.
Si une liste de colonnes est spécifiée, les colonnes ne figurant pas dans la liste auront la valeur NULL.

INSERT INTO nom_table(nom_col1, nom_col2, ...)
SELECT ...

Le SELECT peut contenir n'importe quelle clause sauf un ORDER BY qui impliquerait un classement des lignes contraire à l'esprit du relationnel.

 INSERT INTO [schema.]table | view

     [ (column [, column] ...) ]

    VALUES (expr [, expr] ...) | subquery

• schema

• table / view

• column

• VALUES Expr / subquery

Pour pouvoir insérer des lignes dans une table il faut soit être propriétaire de cet objet, soit avoir le privilège INSERT sur cette table.

Le privilège INSERT ANY TABLE permet d'insérer des lignes dans n'importe quelle table appartenant à n'importe quel utilisateur.

• Modification de lignes

La commande UPDATE permet de modifier les valeurs d'une ou plusieurs colonnes, dans une ou plusieurs lignes existantes d'une table. La syntaxe est la suivante :

UPDATE nom_table
SET nom_col1 = {expression1 | ( SELECT ...) },
    nom_col2 = {expression2 | ( SELECT ...) }
WHERE predicat

Les valeurs des colonnes nom_col1, nom_col2, ... sont modifiées dans toutes les lignes satisfaisant au prédicat. En l'absence d'une clause WHERE, toutes les lignes sont mises à jour.
Les expressions expression1, expression2,... peuvent faire référence aux anciennes valeurs de la ligne.

 UPDATE [schema.]table | view [alias]
     SET  (column [, column] ...) = (subquery)
        |  column =  expr | (subquery)
     [,     (column [, column] ...) = (subquery)
        |  column =  expr | (subquery)   ] ...
    [WHERE condition]

• schema

• table, view

• alias

est un alias assigné à la table. les alias sont généralement utilisés dans des UPDATE contenant des requêtes.

• column

• expr

• subquery

est un SELECT qui renvoie les nouvelles valeurs affectées aux colonnes correspondantes.

• WHERE

Pour pouvoir modifier des lignes appartenant à une table, il faut soit être propriétaire de cet objet, soit avoir le privilège UPDATE sur cette table.

le privilège UPDATE ANY TABLE permet de sélectionner des lignes de n'importe quel objet appartenant à n'importe quel utilisateur.

• Suppression de lignes

La commande DELETE permet de supprimer des lignes d'une table.
La syntaxe est la suivante :

DELETE FROM nom_table
WHERE prédicat ;

Toutes les lignes pour lesquelles prédicat est évalué à vrai sont supprimées. En l'absence de clause WHERE, toutes les lignes de la table sont supprimées.

 

 DELETE [FROM] [schema.]table | view [alias]

     [WHERE condition]

• schema

• table, view

• alias

Est un alias assigné à la table. les alias sont généralement utilisés dans des DELETE contenant des requêtes.

• WHERE

Pour détruire des lignes appartenant à une table, il faut soit être propriétaire de la table, soit avoir le privilège DELETE sur cette table.

Le privilège DELETE ANY TABLE permet à un utilisateur de détruire des lignes se trouvant dans n'importe quelle table, ou n'importe quelle vue basée sur une table.

• Suppression d'une table

• schema

• table

CASCADE CONSTRAINTS

Pour pouvoir détruire une table il faut soit être propriétaire de cette table, soit avoir le privilège DROP ANY TABLE.

• Gestion des transactions

• Valider la transaction en cours par la commande COMMIT. Les modifications deviennent définitives et visibles à tous les utilisateurs.
• Annuler la transaction en cours par la commande ROLLBACK. Toutes les modifications depuis le début de la transaction sont alors défaites.

Ce mécanisme est utilisé par les systèmes de gestion de bases de données pour assurer l'intégrité de la base en cas de fin anormale d'une tâche utilisateur : il y a automatiquement ROLLBACK des transactions non terminées.

• Créer une table

La commande de création de table la plus simple ne comportera que le nom et le type de chaque colonne de la table. L'on peut créer une table par la commande CREATE TABLE en spécifiant le nom et le type de chaque colonne. A la création, la table sera vide mais un certain espace lui sera alloué. La syntaxe est la suivante :

CREATE TABLE nom_table
       (nom_col1 TYPE1,
        nom_col2 TYPE2,
        ...)

L'option NOT NULL assure qu'ORACLE interdit lors d'un INSERT ou d'un UPDATE que cette colonne contienne la valeur NULL, par défaut elle est autorisée.

CREATE TABLE nom_table
       [(nom_col1,
         nom_col2,
	 ...)]
AS SELECT...

On peut ainsi, en un seul ordre SQL créer une table et la remplir avec des données provenant du résultat d'un SELECT.

On n'a pas besoin alors de spécifier de type pour les colonnes : les types des données sont ceux provenant du SELECT. Si des conversions de type sont à faire, on peut dans le SELECT utiliser les fonctions TO_CHAR, TO_DATE, TO_NUMBER.

Par défaut les noms des colonnes de la nouvelle table sont les noms des colonnes du SELECT. Si des expressions apparaissent dans le SELECT, les colonnes correspondantes doivent impérativement être renommées.

Le SELECT peut contenir des fonctions de groupes mais pas d'ORDER BY car les lignes d'une table ne peuvent pas être classées.

• NUMBER[(longueur,[précision])

Ce type de données permet de stocker des données numériques à la fois entières et réelles dont la valeur est comprise entre 10^-130 et 10^125 avec une précision de 38 chiffres.

longueur

précise le nombre maximum de chiffres significatifs stockés (par défaut 38),

précision

donne le nombre maximum de chiffres après la virgule (par défaut 38), sa valeur peut être comprise entre -84 et 127. Une valeur négative signifie que le nombre est arrondi à gauche de la virgule.

• CHAR(longueur)

Ce type de données permet de stocker des chaînes de caractères de longueur fixe. Longueur doit être inférieur à 255, sa valeur par défaut est 1.

• VARCHAR(longueur)

Ce type de données permet de stocker des chaînes de caractères de longueur variable. Longueur doit être inférieur à 2000, il n'y a pas de valeur par défaut.

• DATE

• RAW(longueur)

• LONG

Ce type de données permet des stocker des chaînes de caractères de longueur variable et inférieure à 2^31 -1. Les colonnes de ce type sont soumises à certaines restrictions ;

une table ne peut pas contenir plus d'une colonne de ce type ;
les colonnes de ce type ne peuvent pas apparaître dans des contraintes d'intégrité ;
les colonnes de ce type ne peuvent pas être indexées ;
les colonnes de ce type ne peuvent pas apparaître dans des clauses : WHERE, GROUP BY, ORDER BY ou CONNECT BY ainsi que dans un DISTINCT.

CREATE TABLE nom_table (
nom_col_1 type_1,
nom_col_2 type_2,
...
nom_col_n type_n
CONSTRAINT [nom_contrainte_1] contrainte_1,
CONSTRAINT [nom_contrainte_2] contrainte_2,
...
CONSTRAINT [nom_contrainte_m] contrainte_m
);

CREATE TABLE nom_table (
nom_col_1 type_1  CONSTRAINT [nom_contrainte_1_1] contrainte_1_1
                  CONSTRAINT [nom_contrainte_1_2] contrainte_1_2
		  ...
                  CONSTRAINT [nom_contrainte_1_m] contrainte_1_m,

nom_col_2 type_2  CONSTRAINT [nom_contrainte_2_1] contrainte_2_1
                  CONSTRAINT [nom_contrainte_2_2] contrainte_2_2
		  ...
                  CONSTRAINT [nom_contrainte_2_p] contrainte_2_p,

...
nom_col_n type_n  CONSTRAINT [nom_contrainte_n_1] contrainte_n_1
                  CONSTRAINT [nom_contrainte_n_2] contrainte_n_2
		  ...
                  CONSTRAINT [nom_contrainte_n_q] contrainte_n_q
);

• NOT NULL

La colonne ne peut pas contenir de valeurs NULL.

• UNIQUE

Chaque ligne de la table doit avoir une valeur différente ou NULL pour cette (ou ces) colonne.

• PRIMARY KEY

Chaque ligne de la table doit avoir une valeur différente pour cette (ou ces) colonne. les valeurs NULL sont rejetées.

• FOREIGN KEY

• CHECK

• Modifier une table

On peut modifier dynamiquement la définition d'une table grâce a la commande ALTER TABLE.
Deux types de modifications sont possibles : ajout d'une colonne et modification d'une colonne existante.

Il n'est pas possible de supprimer une colonne. Par contre une colonne qui n'est plus utilisée peut être mise à la valeur NULL, auquel cas elle n'occupe plus d'espace disque. Si on désire vraiment supprimer une colonne, il faut :

• Ajouter une colonne

ALTER TABLE nom_table ADD
      (nom_col1 TYPE1,
       nom_col2 TYPE2,
       ...)

Les types possibles sont les mêmes que ceux décrits avec la commande CREATE TABLE.

Si la table contient déjà des lignes, la nouvelle colonne aura des valeurs NULL pour les lignes existantes.

• Modifier une colonne

• dans tous les cas il est possible d'augmenter la taille d'une colonne ;
• il est possible de diminuer la taille, ou même de changer le type d'une colonne vide ;
• on peut spécifier NOT NULL si la colonne ne contient aucune valeur NULL ;
• on peut dans tous les cas spécifier NULL pour autoriser les valeurs NULL.

• Supprimer une table

La commande DROP TABLE permet de supprimer une table, sa syntaxe est la suivante :

DROP TABLE nom_table ;

La table nom_table est alors supprimée. La définition de la table ainsi que son contenu sont détruits, et l'espace occupé par la table est libéré.

• Renommer une table

On a la possibilité de changer le nom d'une table par la commande RENAME, la syntaxe est la suivante :

RENAME ancien_nom TO nouveau_nom ;

On a vu que le résultat d'un SELECT est lui-même une table.
Une telle table, qui n'existe pas dans la base mais est créée dynamiquement lors de l'exécution du SELECT, peut être vue comme une table réelle par les utilisateurs. Pour cela, il suffit de cataloguer le SELECT en tant que vue.

Les utilisateurs pourront consulter la base, ou modifier la base (avec certaines restrictions) à travers la vue, c'est-à-dire manipuler la table résultat du SELECT comme si c'était une table réelle.

• Créer une vue

La commande CREATE VIEW permet de créer une vue en spécifiant le SELECT constituant la définition de la vue :

CREATE VIEW nom_vue [(nom_col1,...)]
AS SELECT ...
WITH CHECK OPTION ;

La spécification des noms de colonnes de la vue est facultative. Par défaut, les noms des colonnes de la vue sont les mêmes que les noms des colonnes résultat du SELECT (si certaines colonnes résultat du SELECT sont des expressions, il faut renommer ces colonnes dans le SELECT, ou spécifier les noms de colonne de la vue).

Exemple : Création d'une vue constituant une restriction de la table emp aux employés du département 10.

Le CHECK OPTION permet de vérifier que la mise à jour ou l'insertion faite à travers la vue ne produisent que des lignes qui font partie de la sélection de la vue.

Ainsi donc, si la vue emp10 a été créée avec CHECK OPTION on ne pourra à travers cette vue ni modifier, ni insérer des employés ne faisant pas partie du département 10.

Il est possible d'effectuer des INSERT et des UPDATE à travers des vues, sous deux conditions :

• le SELECT définissant la vue ne doit pas comporter de jointure,
• les colonnes résultat du SELECT doivent être des colonnes réelles et non pas des expressions.

Exemple : Modification des salaires du département 10 à travers la vue emp10.

Toutes les lignes de la table emp, telles que le contenu de la colonne n_dept est égal à 10 seront modifiées.

• Supprimer une vue

DROP  VIEW nom_vue;

• Renommer une vue

RENAME ancien_nom TO nouveau_nom;

• Introduction - Généralités

Considérons le SELECT suivant :

SELECT *
FROM emp
WHERE nom = 'MARTIN'

Un moyen de retrouver la ou les lignes pour lesquelles ,font color=red>nom est égal à MARTIN est de balayer toute la table.

Exemple : Les requêtes suivantes bénéficieront d'un index sur le champ n_dept.

Exemple : La requête suivante bénéficiera d'un index sur la colonne nom.

Exemple : La requête suivante ne bénéficiera pas d'un index sur le champ nom.

• Valeurs NULL

Elles ne sont pas représentées dans l'index, ceci afin de minimiser le volume nécessaire pour stocker l'index. En contrepartie, l'index ne sera d'aucune utilité pour retrouver les valeurs NULL lorsque le critère de recherche est du type IS NULL.

• Conversions

L'index n'est utilisable que si le critère de sélection est le contenu de la colonne indexée, sans aucune transformation. Par exemple un index sur salaire ne sera pas utilisé pour la requête suivante :

WHERE fonction(col_indexée) = constante

Exemple : Le prédicat suivant ne bénéficiera pas d'un index sur le champ embauche.

WHERE TO_CHAR(embauche) LIKE '????'

Le critère de recherche est une fonction de embauche, et non le champ embauche lui-même, dans ce cas l'index est inutilisable.

• Créer un index

CREATE [UNIQUE]  INDEX nom_index
ON nom_table (nom_col1 , nom_col2, ...)
[PCTFREE nombre]
[COMPRESS | NOCOMPRESS]
[ROWS = nombre_lignes] ;

• L'option UNIQUE indique que l'on interdit que deux lignes aient la même valeur dans la colonne indexée.
• PCTFREE précise le pourcentage de place laissée libre dans les blocs d'index à la création de l'index. Cette place libre évitera une réorganisation de l'index des les premières insertions de nouvelles clés. La valeur par défaut est 20% .
• NOCOMPRESS indique que l'on ne veut pas comprimer les clés.
• nombre_lignes est une estimation du nombre de lignes, permettant d'optimiser l'algorithme de classement..

• Supprimer un index

DROP  INDEX nom_index;

• Introduction - Généralités

Par exemple on pourrait mettre en cluster les tables emp et dept selon n_dept. Ces tables seraient réorganisées de la façon suivante : un bloc de cluster serait créé pour chaque numéro de département, ce bloc contenant à la fois les lignes de la table emp et de la table dept correspondant à ce numéro de département. La jointure entre les tables emp et dept selon n_dept deviendrait alors beaucoup plus rapide, puisqu'elle serait déjà réalisée dans l'organisation physique des tables.

Pour que l'on puisse mettre une table en cluster il faut que l'une au moins des colonnes faisant partie du cluster soit définie comme obligatoire (NOT NULL).

• Créer un cluster

Avant de pouvoir mettre en cluster une ou plusieurs tables il faut créer le cluster au moyen de la commande CREATE CLUSTER dont la syntaxe est la suivante :

CREATE CLUSTER nom_cluster
       (cle1 type1,
        cle2 type2,
        ...)

CREATE CLUSTER nom_cluster
       (cle1 type1,
        cle2 type2,
        ...)
[SIZE taille_du_bloc]
[COMPRESS | NOCOMPRESS]

[SPACE nom_de_space_definition]

• SIZE

• COMPRESS|NOCOMPRESS

• SPACE

spécifie le SPACE DEFINITION qui définira les paramètres d'allocation d'espace pour le cluster.

CREATE CLUSTER DEM
(DEPNO NUMBER)
SIZE 512
;

• Mise en cluster d'une table

L'option cluster de l'ordre CREATE TABLE permet de spécifier que la table doit être mise en cluster. Le cluster doit déjà exister.

CREATE TABLE nom_table

(nom_col1 TYPE1 NOT NULL ,
(nom_col2 TYPE2 NOT NULL ,
				...)
CLUSTER NOM_CLUSTER  (nom_coli, nom_colj...)

nom_coli, nom_colj sont des noms de colonnes de la table, elles seront identifiées une à une aux colonnes clés du cluster spécifiées à la création du cluster.

• Retrait d'une table d'un cluster

• Supprimer un cluster

DROP CLUSTER nom_cluster ;