SharePoint au Quotidien

SQL : Structured Query Language

Caractéristiques

1) Historique :

1. SQL est devenu en quelques années "Le" standard des bases de données relationnelles.
2. Conçu en 1970 ( dans un des laboratoires de recherche d' IBM ).
3. Le "père" du SQL : E.F.Codd. qui a défini les principes d'une approche relationnelle des B de D.
4. Dans un premier temps, SQL a été connu par son implantation sur les mainframes.
5. depuis sa normalisation en 1987 par l'ANSI (American National Standard Institute), le SQL a été implanté sur les mini et micro-ord.

2) SQL n'est pas un langage de programmation.

1. SQL n'est pas un langage de programmation complet (tel que le Basic, Pascal, C ...) : il n'a que deux douzaines d'instructions environ (et la plupart du temps, on n'en utilisera qu'une, avec de nombreuses variantes).
2. SQL trouve sa place à l'intérieur d'un langage de programmation : les mêmes données pourront être atteintes par différents langages de programmation (elles sont indépendantes des programmes qui les utilisent).
3. SQL n'est pas un langage procédural : la manière dont les données sont recherchées dans la base est totalement transparente pour l'utilisateur.
4. On peut effectuer des requêtes SQL en deux modes :

- interactif : on tape directement une instruction SQL, instantanément traitée ;
- programme : on utilise des API (Application Programming Interface) pour appeler SQL à l'intérieur d'un programme en C, COBOL, Pascal, Basic, Assembleur...

3) Modèles hiérarchiques, en réseau, relationnels.

Il existe 3 modèles logiques de représentation des enregistrements d'une base de donnée :

- Modèle hiérarchique : les données sont représentées par des enregistrements et des liens entre ceux-ci. L'organisation générale est dite "en arbre".

- Modèle réseau : chaque enregistrement de la base de donnée est représenté par des noeuds, et les relations entre les fichiers de la base par des liens entre les noeuds. L'ensemble de la base de donnée forme ainsi un graphe.

- Modèle relationnel : c'est un modèle purement logique basé uniquement sur des concepts mathématiques de la théorie des ensembles. Les données et leurs relations sont représentées au moyen de tables. Le but des modèles relationnels est de dégager l'utilisateur de toute référence à l'implémentation (représentation de l'information, type d'accès, etc.). En effet, l'utilisateur est uniquement intéressé par le contenu informatif et non par les détails de la représentation.

Remarques :

1. Au départ, les 1ers S.G.B.D. avaient été définis selon les modèles hiérarchiques ou réseau.
   (on définit les relations entre entités lors de la création des bases de données (relations statiques).
   Pour rechercher des informations avec ce système, il faut "naviguer" dans les relations, étape par étape. La modification des relations entre entités nécessite en général des opérations "lourdes").
2. Avec une B.D.R., les relations sont dynamiques, (définies à chaque interrogation et non pas au moment de la création de la Base de Données).
3. E.F.Codd a donné un ensemble de 12 règles qui déterminent si un S.G.B.D est relationnel ou non.

4) DDL, DCL et DML.

Le langage SQL peut être divisé en 3 parties :

1. le DDL (Data Definition Language) : Langage de Définition de Données : permet de créer, modifier, ou effacer la définition d'une Base de Données ou d'une Table.(CREATE, DROP)
2. le DML (Data Manipulation Language) : Langage de Manipulation de Données : permet de manipuler l'info contenue dans les tables (ajouter ou effacer des lignes, faire des sélections) (DELETE, UPDATE, INSERT, SELECT)
3. le DCL (Data Control Language) : Langage de Contrôle de Données : commandes permettant d'effectuer un contrôle sur les données d' une table.(GRANT, REVOKE)

5) Serveurs de bases de données (modèle client / serveur).

Dans les S.I. modernes, une B de D est en général partagée entre plusieurs utilisateurs.
L'architecture "client-serveur" consiste à spécialiser le serveur pour la gestion des B de D. Grâce à des possibilités de traitement multitâche, le serveur est capable de traiter les requêtes simultanées de plusieurs postes de travail, et cela de manière intelligente.

Par exemple, lorsqu'un poste de travail "client" envoie une requête telle que "calcule-moi la moyenne des salaires" , le serveur va exécuter lui-même cette requête et ne retourner que la valeur 8256, la réponse à la requête.

Terminologie

1) Bases de données et tables.

Un S.G.B.D. est un outil pour organiser, stocker, modifier, calculer et extraire des infos.
Une B.D.R. peut être considérée comme un ensemble de tables à 2 dimensions.

Ex :

Les commandes associées à la définition de table sont :

·        CREATE TABLE ( Création )

·        ALTER TABLE ( Modification de la structure )

·        DROP TABLE ( Effacement )

Pour modifier le contenu des tables, on utilise :

·        INSERT ( Ajout )

·        DELETE ( Effacement )

·        UPDATE ( Modification )

2) Lignes, colonnes champs et valeurs.

Les tables sont composées de lignes et de colonnes.
A leur intersection se trouve une valeur.
Une colonne est constituée d'un ensemble de données de même nature.
Une ligne est une séquence de valeurs telles que la nième valeur est une donnée de la nième colonne.

3) Types de données.

Voici quelques types :

SMALLINT : nbres entiers de -99999 à 99999.

INTEGER : nbres entiers de -9999999999 à 99999999999

FLOAT : nombres à virgule flottante de 0.1 e-307 à 0.9e+308

NUMERIC ( x , y ) : gèrent tous les deux des nombres décimaux

DECIMAL ( x , y ) (x) : nombre total de chiffres et (y) : nombres de chiffres décimaux.
19 chiffres maxi et une position pour son signe (-).
(pour NUMERIC, (x) doit prévoir une position pour la virgule décimale).

CHAR ( n ) : chaîne de n caractères (254 maxi)

LOGICAL : valeurs VRAI (.T. ou .O.) ou FAUX (.F. ou .N.)

DATE : date (format par défaut : JJ/MM/AA)

4) Syntaxe des instructions.

Toutes les instructions SQL doivent se terminer par un point-virgule (;)

Créer une base de donnée

Avant d'enregistrer des informations dans des tables, il faut créer une base de données SQL dans laquelle on va ranger les tables.

Syntaxe :

CREATE DATABASE [chemin d'accès]nom_base ;

Ex :

CREATE DATABASE a:\exercice ;

Rem : Le fait de créer une base de données l'ouvre automatiquement.

Avant de travailler avec une base de données, il faut l'ouvrir :

Syntaxe :

START DATABASE nom_base ;

Rem :

Après s'être servi d'une base de données, il faut la fermer :

Syntaxe :

STOP DATABASE ;

·        La commande SHOW DATABASE ; répertorie les bases de données SQL.

1) Créer / effacer une table.

a) Création :

CREATE TABLE nom_table
( nom_col type ,
............................. ) ;

Ex :

CREATE TABLE employe
( nomchar(20),
prenom char(15),
age integer ,
statut char(15) ,
salaire integer ) ;

b) Effacement :

DROP TABLE nom_table ;

Ex :

DROP TABLE employe ;

2) Créer / effacer une vue.

Une vue est un moyen de synthétiser l'info. qui figurerait dans une ou plusieurs tables, en une seule table (logique). On a donc l'impression de travailler avec une seule table alors que l'on utilise les infos de plusieurs tables.

Rem : la majeure partie des commandes SQL s'appliquant à une table s'appliquent à une vue.

a) Création :

CREATE VIEW nom_vue
( nom_col_1 , ... , nom_col_n )
as sélection ;

Ex :

CREATE VIEW vue1
( col1, col2, col3 )
as SELECT table1.col1, table2.col3, table2.col4
FROM table1 , table2
WHERE table1.cond1 = table2.cond2 ;

Ex :

CREATE VIEW cadre
as SELECT * FROM employe
WHERE statut = ' cadre ' ;

b) Effacement :

DROP VIEW nom_vue ;

Ex :

DROP VIEW cadre ;

3) Créer / effacer une index.

a) Création :

CREATE [UNIQUE] INDEX nom_index
on nom_table
( nom_champ [ASC / DESC ],
............................................. );

Ex :

CREATE INDEX nom_emp
on employe (nom) ;

Crée un index sur la colonne Nom de la table Employe.

CREATE INDEX nom_emp
on employe (nom ASC , prenom ASC ) ;

Crée un index sur les colonnes Nom et Prenom.

Rem :

·        UNIQUE empêche d'avoir 2 ou plusieurs lignes identiques pour la même clé.

·        les index ne peuvent être créés que sur des tables (pas des vues).

·        une fois créé, un index est automatiquement mis à jour par SQL.

b) Effacement :

DROP INDEX nom_index ;

Ex :

DROP INDEX nom_emp ;

Modifier / enrichir une base de donnée

1) Insertion de colonnes dans une table.

Format :

ALTER TABLE nom_table
ADD ( nom_col type,
.............) ;

Rem :

·        la valeur initiale des champs rajoutés est nulle.

·        la (les) colonne rajoutée est la dernière de la table.

Ex :

ALTER TABLE employe
ADD ( anc numeric (5,2) ) ;

2) Insertion de lignes dans une table (vue).

Format :

INSERT INTO nom_table
( nom_col_1 , ..... , nom_col_n )
VALUES ( valeur_1 , ..... , valeur_n ) ;

Ex :

INSERT INTO employe
( nom , prenom , statut )
VALUES ( 'Dupont', 'Jean' , 'cadre' ) ;

3) Effacement de lignes dans une table.

DELETE FROM nom_table
WHERE condition de sélection ;

Ex :

DELETE FROM employe
WHERE statut = 'R' ;

Efface dans la table des employés, toutes les personnes qui sont parties en retraite.

4) Modification de lignes dans une table.

UPDATE nom_table
SET nom_col = expression ,
......................
[ WHERE condition de recherche ] ;

Ex :

UPDATE employe
SET salaire = 13000
WHERE nom = ' Dupont ' ;

L'employé Dupont aura son salaire fixé à 13000 F.

UPDATE employe
SET salaire = salaire * 1.05 ;

Tous les salaires sont augmentés de 5 %.

UPDATE employe
SET salaire = salaire * 1.05
WHERE statut = ' Cadre ' ;

Seuls les cadres sont augmentés.

Interroger une table

1) Format général d'une commande de sélection.

Une commande de sélection contient les clauses suivantes :

SELECT ..... FROM ..... WHERE ..... GROUP BY ..... HAVING ..... ORDER BY .....

2) Sélection de colonnes.

a) Toutes les colonnes :

SELECT * FROM nom_table ;

Ex :

SELECT * FROM employe ;

b) Une (des) colonne(s) :

SELECT nom_col , ..........
FROM nom_table ;

Ex :

SELECT nom , prenom , age
FROM employe ;

3) Sélection de lignes. ( WHERE )

SELECT nom_col , ......
FROM nom_table
WHERE critère ;

Ex :

SELECT nom , prenom , age
FROM employe
WHERE salaire > 8000 AND salaire < 20000 ;

4) Critères généraux de sélection.

a) BETWEEN : valider si une expression est comprise entre 2 valeurs.

Syntaxe :

Expression1 [NOT] BETWEEN Expression2 AND Expression3

Exemple :

SELECT nom, prenom FROM Employe
WHERE Salaire BETWEEN 8000 and 20000 ;

b) LIKE : rechercher des valeurs possédant certaines caractéristiques. On peut utiliser les caractères jokers :

·        _ (souligné ) qui remplace n'importe quel caractère ;

·        %(pourcentage) qui remplace n'importe quelle chaîne de caractères.

Syntaxe :

Nom_Col [NOT] LIKE Expression

Exemple :

SELECT Nom, Prenom FROM Employe
WHERE Nom LIKE 'Du%' ;

Sélectionne tous les employés dont le nom commence par Du.

Exemple :

SELECT Nom, Prenom FROM Employe
WHERE Nom LIKE '__R%' ;

Sélectionne tous les employés dont le nom contient un R en 3ème position.

Exemple :

SELECT Nom, Prenom FROM Employe
WHERE Nom LIKE '%RA%' ;

Sélectionne tous les employés dont le nom contient "RA".

c) EXISTS : teste si le résultat d'une sélection contient au moins une ligne.

Syntaxe :

EXISTS ( sélection) ;

Exemple :

SELECT nom FROM employe
WHERE EXISTS ( SELECT * FROM Employe WHERE anc = 0 ) ;

d) IN : sélectionne les lignes contenant une valeur coïncidant avec l'une des valeurs dans une liste.

Syntaxe :

expression [NOT] IN liste
ou expression [NOT] IN Sous-sélection

Exemple :

SELECT * FROM employe
WHERE age IN ( 64 , 65 ) ;

Sélectionne tous les employés ayant 64 ou 65 ans.

Rem : cette commande est équivalente à :

SELECT * FROM employe
WHERE age = 64 OR age= 65 ;

e) Opérateurs :

On peut aussi utiliser les opérateurs :

·        booléens : AND, OR et NOT,

·        de comparaison : = , > , et < ( et leur combinaisons )

Exemples :

SELECT * FROM Employe
WHERE NOT statut = 'Directeur' ;

ou

SELECT * FROM Employe
WHERE statut ! = 'Directeur' ;

f) ALL : utilisé avec un opérateur de comparaison, sert à tester si une expression est vérifiée dans tous les cas de figure. L'expression est juste si la comparaison est vérifiée pour toutes les valeurs renvoyées par la clause "sous-sélection".

Ex :

SELECT * FROM employe
WHERE 65 > ALL ( SELECT age FROM employe ) ;

permet de vérifier si tout le personnel a moins de 65 ans.
si oui , on affiche " * FROM employe ".

g) ANY : Contrairement à ALL, l'expression est juste si la comparaison est vérifiée dans la clause de sous-sélection pour au moins une valeur.

Ex :

SELECT * FROM employe
WHERE 65 < ANY ( SELECT age FROM employe ) ;

si au moins une valeur de la sélection " SELECT age FROM employe" est > à 65, alors on affiche " * FROM employe "

5) TRI des résultats :

Il est possible de trier les résultats d'une sélection avec la clause ORDER BY :

Syntaxe :

ORDER BY nom_col [ ASC / DESC ]

Exemple :

SELECT statut, nom, prenom, salaire FROM employe
ORDER BY statut ASC, salaire ASC ;

On sélectionne les employés et on les classe par statut ; pour chaque statut, ils sont classés par salaire, le salaire le moins important en premier.(le champ critère de tri doit être présent après le SELECT)

6) Pour éviter de sélectionner des lignes en double (redondantes) :

on utilise la clause DISTINCT.

Syntaxe :

SELECT DISTINCT nom_col, .... FROM nom_table
WHERE critère ;

Exemple :

SELECT DISTINCT nom FROM employe

Interroger une table

7) Fonctions de groupe de valeurs :

Les fonctions de groupe de valeurs permettent de traiter des informations récapitulatives. Il y en a 2 :

a) GROUP BY : permet de grouper les lignes par type.

Syntaxe :

GROUP BY nom_col , ...

Exemple :

SELECT statut, SUM(salaire) FROM employe
GROUP BY statut ;

va donner, statut par statut, le total des salaires des employés.

le résultat pourrait être :

Exemple :

SELECT statut, COUNT(*), SUM(salaire) FROM employe
GROUP BY statut ;

le résultat pourrait être :

il y a 12 personnes qui sont cadre et le total de leur salaire se monte à 176000 francs.

b) HAVING : permet de spécifier des critères de sélections sur les groupes de valeur.

(accompagne toujours le GROUP BY)

Syntaxe :

GROUP BY ......
HAVING condition

Exemple :

SELECT statut, COUNT(*), SUM(salaire) FROM employe
GROUP BY statut
HAVING COUNT (*) > 10 ;

le résultat pourrait être :

on ne prend en compte que les statuts qui comprennent plus de 10 personnes.

Rem : la différence entre HAVING et WHERE est que WHERE s'applique à des lignes seules et que HAVING s'applique à des groupes de lignes.

8) Fonctions numériques s'appliquant sur les colonnes :

1) AVG : permet de calculer la moyenne de valeurs.

Syntaxe :

AVG ( [ALL] expression )
ou
AVG ( DISTINCT nom_col )

Exemple :

SELECT AVG ( age ) FROM employe ;

calcule la moyenne des âges des employés.

2) COUNT : comptage du nombre d'élément sélectionnés.

Syntaxe :

COUNT (*)
ou
COUNT ( DISTINCT nom_col )

Exemple :

SELECT COUNT(*) FROM employe
WHERE statut='cadre' ;

compte le nombre de cadres.

SELECT COUNT (DISTINCT statut ) FROM employe ;

compte le nombre de status différents au sein de l'entreprise.

3) MAX : détermine la valeur maximale d'un ensemble de valeurs.

Syntaxe :

MAX ( [ALL] expression )
ou
MAX ( DISTINCT nom_col )

Exemple :

SELECT MAX (age) FROM employe
WHERE statut='cadre' ;

trouve l'âge le plus élevé dans la catégorie cadre.

4) MIN : semblable à MAX, pour le minimum de valeurs.

5) SUM : permet de déterminer la somme de valeurs.

Syntaxe :

SUM ( [ALL] expression )
ou
SUM ( DISTINCT nom_col )

Exemple :

SELECT SUM (salaire) FROM employe
WHERE statut='cadre' ;

trouve le total des salaires des cadres.
 

Interrogation de tables multiples

1) Interrogation sur plusieurs tables :

Il est possible d' utiliser plus d'une table dans une instruction " SELECT .... FROM ...."

Exemple :

SELECT nom, CA , SUM(facture) FROM Ventes, Compta
WHERE Ventes.nom = Compta.nom
AND SUM (facture) > 10000
GROUP BY nom;

sélectionne les ingénieurs commerciaux dont le total de facture en cours de leurs clients dépasse 10000 francs

On accède ici à 2 tables :

·        Vente qui contient notamment le nom du commercial et son chiffre d'affaire annuel ;

·        Compta qui contient, pour chaque facture en cours, le nom du commercial et le montant correspondant.

Le critère qui permet la jointure des deux tables est : Ventes.nom = Compta.nom

Il indique que pour un même ingénieur commercial, on va chercher dans la table Vente le chiffre d'affaire (CA) et dans la table Compta, la somme des factures en cours.

Syntaxe :

SELECT nom_col1, nom_col2, ... FROM table1, table2 ...
WHERE ......

Rem : l'exemple précédent aurait pu s'écrire :

SELECT nom, CA , SUM(facture) FROM Ventes V, Compta C
WHERE V.nom = C.nom
AND SUM(facture) > 10000
GROUP BY nom;

V est défini comme synonyme de Ventes, et C comme synonyme de Compta.

2) Sous-requêtes :

Il est possible de mettre des SELECT à l'intérieur d'autres SELECT : on peut alors faire des requêtes à partir de résultats d'autres requêtes (requêtes imbriquées).

Les sous-requêtes sont utilisées avec les opérateurs : IN, ALL, ANY, EXISTS, SOME.

3) Opérations sur les ensembles :

a) Union : l'union de 2 tables produit une table logique qui contient à la fois les lignes renvoyées par la 1ère sélection et les lignes de la 2nde sélection.

Syntaxe :

sélection1
UNION [ALL]
sélection2
[ORDER BY nom_col [ASC / DESC ] ] ;

Exemple :

SELECT Societe, cp FROM Client
UNION
SELECT Societe, codepost FROM fournis ;

Permet d'obtenir une table composée des clients et des fournisseurs.

Les clients qui sont également des fournisseurs ne figureront qu'une seule fois dans la table résultante.

b) Intersection : sur certaines versions de SGBD SQL, il est possible d'effectuer des intersections de tables.

Syntaxe :

sélection1
INTERSECT
sélection2
[ORDER BY nom_col [ASC / DESC ] ] ;

Exemple :

SELECT societe, cp FROM client
INTERSECT
SELECT societe, codepost FROM fournis ;

Permet de trouver les clients qui sont également fournisseurs.

c) Exclusion : on utilise l'opérateur MINUS (qui n'existe que dans certaines versions de SGBD).

Syntaxe :

sélection1
MINUS
sélection2
[ORDER BY nom_col [ASC / DESC ] ] ;

(c) Par William Vaughn - Mis en ligne le 23/10/02

site : http://www.itpro.fr/index1.asp?Idarticle=1207&rub=3

Voir l'article sur SQL : Exchange 2000 et Sharepoint Portal Server

http://www.itpro.fr/index1.asp?Idarticle=1001&rub=2

Et l'article sur Windows & .NET MAGAZINE Vol.1 - Numéro 2 de Février 2002

sur Exchange 2000 et SharePoint Portal Server :

http://www.itpro.fr/index1.asp?Idarticle=1001&rub=2

http://www.itpro.fr/index1.asp?Idarticle=966&rub=2

http://www.itpro.fr/index1.asp?Idarticle=826&rub=2

et le site Microsoft : http://www.microsoft.com/france/sql/default.asp

 07/06/2003

[ Retour page Accueil ] [ Remonter ]

Envoyez un courrier électronique à EROL GIRAUDY (attention nospam dans l'E-mail) pour toute question ou remarque concernant ce site Web et visitez la rubrique Condition Utilisation et CNIL. Copyright © 2002 EROL (les sigles et logos ci-après sont la propriété de : Microsoft, Supinfo, Adobe, Compaq, HP, Sybari, Veritas, Moreover, K-map, Vyapin, Plumtree, Ixos, TooStore, K-Map, eRoom, DocKIT,NQL, Only4gurus, Nsius, Sharepointexperts, Iora, Erol, KCura, FrontPages, Nsi, Frontlook, IBuySpyPortal, moreover, slipstick, networknowledge, clubsps.org )
Dernière modification : vendredi, 26. décembre 2003 11:27