Page suivante Page précédente Table des matières 39. Didacticiel SQL On peut trouver ce didacticiel SQL à http://w3.one.net/~jhoffman/sqltut.htm Pour les commentaires ou suggestions, envoyer un courrier électronique à jhoffman@one.net Vous pouvez également souhaiter jeter un oeil à http://w3.one.net/~jhoffman/index.html John Hoffman suggère de visiter les sites suivants : http://www.contrib.andrew.cmu.edu/~shadow/sql.htmlRéférence SQL http://www.inquiry.com/techtips/thesqlpro/Demandez le Pro. de SQL http://www.inquiry.com/techtips/thesqlpro/usefulsites.htmlSites utiles au Pro. du SGBD Relationnel SQL http://infoweb.magi.com/~steve/develop.htmlLes Sites de sources du programmeur SGBD http://info.itu.ch/special/wwwfiles Allez-y et regardez le fichier comp_db.html http://www.compapp.dcu.ie/databases/f017.htmlIngrédients pour SGBD http://www.stars.com/Tutorial/CGI/Création Web http://wfn-shop.princeton.edu/cgi-bin/foldocDictionnaire d'Informatique http://www-ccs.cs.umass.edu/db.htmlDBMS Lab/Liens SQL FAQ http://epoch.CS.Berkeley.EDU:8000/sequoia/dba/montage/FAQ Allez-y et regardez le fichier SQL_TOC.html http://chaos.mur.csu.edu.au/itc125/cgi/sqldb.htmlSGBD SQL http://www.it.rit.edu/~wjs/IT/199602/icsa720/icsa720postings.htmlPage de Conception de Bases de Données RIT http://www.pcslink.com/~ej/dbweb.htmlSite de liens vers des Bases de Données http://www.eng.uc.edu/~jtilley/tutorial.htmlDidacticiels de programmation sur le Web http://www.ndev.com/ndc2/support/resources.htpRessources pour le Développement http://ashok.pair.com/sql.htmListe de Requêtes http://jazz.external.hp.com/training/sqltables/main.htmlIMAGE SQL Diverses http://www.eit.com/web/netservices.htmlListe de Ressources Internet Voici, ci-dessous, un extrait de la page d'accueil du didacticiel SQL. Introduction au Langage de Requête Structuré Version 3.31 Cette page contient un didacticiel du Langage de Requête Structuré( Structured Query Language, également connu sous le nom de SQL). Ce didacticiel constitue une nouveauté sur le World Wide Web, car c'est le premier didacticiel SQL complet disponible sur l'Internet. SQL permet l'accès aux données dans les systèmes de gestion de bases de données relationnels tels que Oracle, Sybase, Informix, Microsoft SQL Server, Access, et autres en permettant aux utilisateurs de décrire les données qu'ils désirent obtenir. SQL permet aussi aux utilisateurs de définir l'organisation des données dans la base et de les manipuler. Le but de cette page est de décrire l'utilisation de SQL, et de donner des exemples. C'est le langage ANSI SQL, ou standard SQL, qui sera utilisé dans ce document. Il ne sera pas question ici des fonctionnalités spécifiques à un SGBD particulier, qui seront traitées dans la section "SQL non-standard". Nous vous recommandons d'imprimer cette page afin de pouvoir vous référer facilement aux différents exemples. ---------------------------------------------------------------------------- Table des matières Principes fondamentaux de l'instruction SELECT Sélection Conditionnelle Opérateurs Relationnels Conditions Composées IN & BETWEEN Utilisation de LIKE Jointures Clés Réalisation d'une Jointure Elimination des Doubles Alias & In/Sous-requêtes Fonctions d'Agrégation Vues Création de Nouvelles Tables Modification des Tables Ajout de Données Suppression de Données Mise à Jour des Données Index GROUP BY & HAVING Sous-requêtes Supplémentaires EXISTS & ALL UNION & Jointures Externes SQL Intégré Questions Courantes sur SQL SQL Non-standard Résumé de la Syntaxe Liens Importants ---------------------------------------------------------------------------- Principes fondamentaux de l'instruction SELECT Dans une base de données relationnelle, les données sont stockées dans des tables. Par exemple, une table pourrait mettre en relation le Numéro de Sécurité Sociale, le Nom et l'Adresse: TableAdresseEmploye NSS Prenom Nom Addresse Ville Etat 512687458Joe Smith 83 First Street Howard Ohio 758420012Mary Scott 842 Vine Ave. LosantivilleOhio 102254896Sam Jones 33 Elm St. Paris New York 876512563Sarah Ackerman 440 U.S. 110 Upton Michigan Maintenant, supposons que nous voulions obtenir l'adresse de chaque employé. On utilisera SELECT, comme ceci : SELECT Prenom, Nom, Adresse, Ville, Etat FROM TableAdresseEmploye; Voici le résultat de l'interrogation de notre base de données : Prenom Nom Adresse Ville Etat Joe Smith 83 First Street Howard Ohio Mary Scott 842 Vine Ave. Losantiville Ohio Sam Jones 33 Elm St. Paris New York Sarah Ackerman 440 U.S. 110 Upton Michigan Explication de ce que l'on vient de faire : on vient de rechercher dans toutes les données de la table TableAdresseEmploye les colonnes nommées Prenom, Nom, Adresse, Ville et Etat. Noter que les noms de colonnes et les noms de tables sont sans espaces... ils doivent être saisis en un seul mot; et que l'instruction se termine par un point-virgule (;). La forme générale d'une instruction SELECT, qui permet de retrouver toutes les lignes d'une table est : SELECT NomColonne, NomColonne, ... FROM NomTable; Pour obtenir toutes les colonnes d'une table sans avoir à taper tous les noms de colonne, utiliser : SELECT * FROM NomTable; Chaque Système de Gestion de Base de Données (SGBD) et chaque logiciel de base de données utilisent différentes méthodes pour se connecter à la base de donnée et pour entrer les instructions SQL; consultez le "guru" de votre ordinateur pour qu'il vous aide à vous connecter de façon à pouvoir utiliser SQL. ---------------------------------------------------------------------------- Sélection Conditionnelle Pour étudier plus avant l'instruction SELECT , jetons un oeil à un nouvel exemple de table (exemple uniquement hypothétique) : EmployeeStatisticsTable EmployeeIDNo Salary Benefits Position 010 75000 15000 Manager 105 65000 15000 Manager 152 60000 15000 Manager 215 60000 12500 Manager 244 50000 12000 Staff 300 45000 10000 Staff 335 40000 10000 Staff 400 32000 7500 Entry-Level 441 28000 7500 Entry-Level ---------------------------------------------------------------------------- Opérateurs Relationnels Il y a six Opérateurs Relationnels en SQL, et, après les avoir présentés, nous verrons comment les utiliser : = Egal <> or != (voir le manuel) Différent < Plus Petit Que > Plus Grand Que <= Plus Petit Que ou Egal à >= Plus Grand Que ou Egal à La clause WHERE est utilisée pour spécifier que l'on affichera seulement certaines ligne de la table, selon un critère définit par cette clause WHERE. Ce sera plus clair en prenant une paire d'exemples. Si l'on désire voir les numéros d'identification des employés (EMPLOYEEIDNO) dont le salaire est égal ou supérieur à 50 000, on utilisera la requête suivante : SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE SALARY >= 50000; Noter que le symbole >= (plus grand que ou égal à) est utilisé, puisque l'on désire voir tout ceux qui gagnent plus de 50 000, ou 50 000, sur la même liste. On aura l'affichage : EMPLOYEEIDNO ------------ 010 105 152 215 244 La description de WHERE, SALARY >= 50000, est appelée une condition. On pourrait effectuer le même traitement sur des colonnes de texte : SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE POSITION = 'Manager'; Ceci entraînera l'affichage des Numéros d'Identification de tous les Managers. En général, avec les colonnes contenant du texte, n'utiliser que égal à ou différent de, et assurez vous que tout texte apparaissant dans l'instruction est entouré d'apostrophes ('). ---------------------------------------------------------------------------- Conditions plus complexes: Conditions Composées L'opérateur AND (ET) combine deux ou plusieurs conditions et n'affiche une ligne que si cette ligne satisfait TOUTES les conditions requises (i.e. où toutes les conditions sont réalisées). Par exemple, pour afficher tout le personnel gagnant plus 40 000, écrire : SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE SALARY > 40000 AND POSITION = 'Staff'; L'opérateur OR (OU) combine deux ou plusieurs conditions mais retourne cette ligne si N'IMPORTE LAQUELLE des conditions requises est remplie. Pour visualiser tous ceux qui gagnent moins de 40 000 ou qui reçoivent moins de 10 000 en participation aux bénéfices, utilisez la requête suivante : SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE SALARY < 40000 OR BENEFITS < 10000; Les opérateurs AND et OR peuvent être combinés, par exemple : SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE POSITION = 'Manager' AND SALARY > 60000 OR BENEFITS > 12000; En premier lieu, SQL recherche les lignes pour lesquelles la valeur de la colonne salaire est supérieure à 60 000 et celle de position est égale à Manager, puis, à partir de cette liste de lignes, SQL recherche alors celles qui satisfont à la condition AND (ET) précédente ou à la condition spécifiant que la colonne Indemnités est supérieure à 12 000. En conséquence, SQL n'affiche seulement que cette seconde liste de lignes, en gardant à l'esprit que tous ceux dont les Indemnités sont supérieures à 12 000 en feront partie puisque l'opérateur OR (OU) inclue la ligne si l'une des conditions est vérifiée. Notez en passant que l'opération AND (ET) est effectuée en premier. Pour généraliser ce processus, SQL effectue l(es) opération(s) AND pour déterminer les lignes ou l(es) opération(s) AND sont vérifiées (souvenez-vous bien : toutes les conditions sont vérifiées), puis ces résultats sont utilisés pour tester les conditions OR, et, ne seront affichées que les lignes où les conditions requises par l'opérateur OR seront vérifiées. Pour que les OR's soient effectués avant les AND's, par exemple si vous vouliez voir une liste des employés dont le salaire est élevé (>50 000) OU bénéficiant d'indemnités importantes (>10 000), ET qui soient cadres, utilisez des parenthèses : SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE POSITION = 'Manager' AND (SALARY > 50000 OR BENEFIT > 10000); ---------------------------------------------------------------------------- IN et BETWEEN Une méthode plus facile pour utiliser les conditions composées consiste à utiliser IN ou BETWEEN. Par exemple, si vous désirez une liste des cadres et du personnel : SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE POSITION IN ('Manager', 'Staff'); ou une liste de ceux dont le salaire est supérieur ou égal à 30 000, mais inférieur ou égal à 50 000, utilisez: SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE SALARY BETWEEN 30000 AND 50000; Pour obtenir la liste de ceux qui n'entrent pas dans ces critères, essayez : SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE SALARY NOT BETWEEN 30000 AND 50000; De la même façon, NOT IN donne la liste de toutes les lignes exclues de la liste obtenue par l'opérateur IN. ---------------------------------------------------------------------------- Utilisation de LIKE Regardons la table EmployeeStatisticsTable, et disons que l'on veut voir tous les gens dont le nom commence par "L"; essayons : SELECT EMPLOYEEIDNO FROM EMPLOYEEADDRESSTABLE WHERE LASTNAME LIKE 'L%'; Le signe pourcentage (%) est utilisé pour représenter n'importe quel caractère possible (nombre, lettre, ou signe de ponctuation) ou ensemble de caractères qui peut apparaître après le "L". Pour trouver les gens dont le Nom se termine avec "L", utiliser '%L', ou si vous désirez le "L" au milieu du mot, essayez '%L%'. Le symbole '%' peut être utilisé pour n'importe quel caractère, et dont la position est relative par rapport à des caractères donnés. NOT LIKE affiche les lignes qui ne correspondent pas à la description donnée. Il y a d'autres manières d'utiliser LIKE, de même que n'importe lesquelles des conditions composées dont nous venons de parler, bien que cela dépende du SGBD que vous utilisez; comme d'habitude, consultez un manuel ou le gestionnaire ou administrateur de votre système pour en connaître les fonctionnalités, ou simplement, assurez vous que ce que vous essayer de faire est possible et autorisé. Cet avertissement est aussi valable pour les fonctionnalités de SQL exposées ci-dessous. Cette section est donnée à titre d'exemple des requêtes qui peuvent être écrites en SQL. ---------------------------------------------------------------------------- Jointures Dans cette section, nous allons parler uniquement des jointures internes et des equi-jointures, dans la mesure où ce sont les plus utiles. Pour avoir plus d'informations, voyez les liens sur des sites SQL au bas de cette page. On suggère qu'une bonne manière de concevoir une base de données implique que chaque table ne contienne des données qui ne concernent qu'une seule entité, et que des informations détaillées peuvent être obtenues, dans une base de données relationnelle, en utilisant des tables supplémentaires et en effectuant une jointure. Premièrement, jetons un oeil à ces exemples de tables : AntiqueOwners OwnerIDOwnerLastName OwnerFirstName 01 Jones Bill 02 Smith Bob 15 Lawson Patricia 21 Akins Jane 50 Fowler Sam --------------------------------------------------------- Orders OwnerIDItemDesired 02 Table 02 Desk 21 Chair 15 Mirror -------------------------------------- Antiques SellerIDBuyerID Item 01 50 Bed 02 15 Table 15 02 Chair 21 50 Mirror 50 01 Desk 01 21 Cabinet 02 21 Coffee Table 15 50 Chair 01 15 Jewelry Box 02 21 Pottery 21 02 Bookcase 50 01 Plant Stand ---------------------------------------------------------------------------- Clés En premier lieu, nous allons parler du concept de clés. Une clé primaire est une colonne ou en ensemble de colonnes qui identifie de manière unique les autres données d'une ligne donnée. Par exemple, dans la table AntiqueOwners, la colonne OwnerID identifie de manière unique cette ligne. Ceci signifie deux choses: que deux lignes ne peuvent avoir le même OwnerID, et que, même si deux propriétaires les mêmes noms et prénoms la colonne OwnerID garantit que ces deux propriétaires ne seront pas confondus l'un avec l'autre, puisque la colonne OwnerID unique sera utilisée à travers la base de données pour se référer à un propriétaire, plutôt que son nom. Une clé externe est une colonne d'une table qui est clé primaire d'une autre table, ce qui signifie que toutes les données d'une clé externe doivent avoir des données correspondantes dans l'autre table, où cette colonne est la clé primaire. Pour parler SGBD, cette correspondance est connue sous le nom d'intégré référentielle. Par exemple, dans la table Antiques, BuyerID et SellerID sont tous les deux des clés externes à la clé primaire de la table AntiqueOwners (OwnerID; pour les besoins de notre argumentation, on doit d'abord être référencé dans la table AntiqueOwners avant de pouvoir acheter ou vendre quoi que ce soit), puisque, dans les deux tables, les colonnes ID sont utilisées pour identifier les propriétaires, les acheteurs ou les vendeurs, et que OwnerID est la clé primaire de la table AntiqueOwners. En d'autres termes, toutes ces données "ID" sont utilisées pour se référer aux propriétaires, acheteurs et vendeurs sans avoir à utiliser les noms effectifs. ---------------------------------------------------------------------------- Réalisation d'une jointure Le but de ces clés est ainsi de pouvoir mettre en relation les données à travers les tables sans avoir à répéter les données dans chaque tables, --c'est toute la puissance des bases de données relationnelles. Par exemple, on peut trouver les noms de ceux qui ont acheté une chaise sans avoir à lister la totalité du nom de l'acheteur dans la table Antiques... vous pouvez trouver ce nom en mettant en relation ceux qui ont acheté une chaise avec les noms dans la table AntiqueOwners en utilisant le OwnerID, qui met en relation les données dans les deux tables. Pour trouver les noms de ceux qui ont acheté une chaise, utilisez la requête suivante : SELECT OWNERLASTNAME, OWNERFIRSTNAME FROM ANTIQUEOWNERS, ANTIQUES WHERE BUYERID = OWNERID AND ITEM = 'Chair'; Notez ce qui suit au sujet de cette requête... notez que les deux tables mise en jeux dans cette relation sont listées dans la clause FROM de l'instruction. Dans la clause WHERE, notez, en premier lieu, que la partie ITEM = 'Chair' de la clause limite la liste à ceux qui ont acheté (et, dans cet exemple, de ce fait possèdent) une chaise. En second lieu, notez comment les colonnes ID sont mises en relation d'une table à la suivante par l'utilisation de la clause BUYERID = OWNERID. Ne seront listés que les noms de la table AntiqueOwners dont les ID correspondent à travers les tables et dont l'article acheté est une chaise (à cause du AND). Parce que la condition de jointure utilisée est un signe égal, cette jointure est appelée une équi-jointures. le résultat de cette requête donnera deux noms: Smith, Bob et Fowler, Sam. La notation avec un point (.) fait référence à l'utilisation du nom de colonne en suffixe du nom de table pour éviter toute ambiguïté, comme par exemple: SELECT ANTIQUEOWNERS.OWNERLASTNAME, ANTIQUEOWNERS.OWNERFIRSTNAME FROM ANTIQUEOWNERS, ANTIQUES WHERE ANTIQUES.BUYERID = ANTIQUEOWNERS.OWNERID AND ANTIQUES.ITEM = 'Chair'; Cependant, puisque les noms de colonnes sont différents dans chaque table, cela n'était pas nécessaire. ---------------------------------------------------------------------------- DISTINCT et l'Elimination des Doubles Disons que vous ne vouliez seulement que la liste des Identificateurs (ID) et des noms des gens qui ont vendu une antiquité. Evidemment, vous ne désirez une liste où chaque vendeur n'apparaît qu'une fois--vous ne voulez pas savoir combien d'antiquités ont été vendues par une personne, mais uniquement le fait que cette personne en a vendu une (pour les comptages, voir la fonction d'Agrégation ci-dessous). Cela signifie qu'il vous faudra dire à SQL d'éliminer les doubles des lignes des ventes, et de ne seulement lister chaque personne qu'une fois. Pour réaliser cela, utilisez le mot-clé DISTINCT. Premièrement, vous aurez besoin de faire un équi-jointures sur la table AntiqueOwners pour obtenir les données concernant le Nom et le Prénom de la personne. Cependant, gardez à l'esprit que, puisque la colonne SellerID dans la table Antiques est une clé externe de la table AntiqueOwners, un vendeur ne sera listé que s'il y a une ligne dans la table AntiqueOwners contenant les ID et les noms. Nous voulons également éliminer les multiples occurrences du SellerID dans notre liste, donc, nous utiliserons le mot-clé DISTINCT pour les colonnes où les répétitions peuvent se produire. Pour ajouter une difficulté, nous voulons aussi que cette liste soit classée par ordre alphabétique des Noms, puis des Prénoms (à l'intérieur des noms), puis des OwnerID (à l'intérieur des noms et des prénoms). Pour cela, nous utiliserons la clause ORDER BY : SELECT DISTINCT SELLERID, OWNERLASTNAME, OWNERFIRSTNAME FROM ANTIQUES, ANTIQUEOWNERS WHERE SELLERID = OWNERID ORDER BY OWNERLASTNAME, OWNERFIRSTNAME, OWNERID; Dans cet exemple, puisque tout le monde a vendu un article, nous aurons une liste de tous les propriétaires, classés par ordre alphabétique sur les noms. Pour référence ultérieure (au cas où quelqu'un le demande), ce type de jointure est considéré comme appartenant à la catégorie des jointures internes. ---------------------------------------------------------------------------- Alias et In/Sous-requêtes Dans cette section, nous parlerons des Alias, In et de l'utilisation des sous-requêtes, et de la manière de les utiliser dans un exemple de 3-table. En premier lieu, regardez cette requête qui imprime le nom des propriétaires qui ont passé une commande et la nature de cette commande, en ne listant seulement que les commandes qui peuvent être satisfaites (c'est à dire qu'il y a un vendeur qui possède l'article commandé) : SELECT OWN.OWNERLASTNAME Last Name, ORD.ITEMDESIRED Item Ordered FROM ORDERS ORD, ANTIQUEOWNERS OWN WHERE ORD.OWNERID = OWN.OWNERID AND ORD.ITEMDESIRED IN (SELECT ITEM FROM ANTIQUES); Ce qui donne : Last name Item Ordered --------- ------------ Smith Table Smith Desk Akins Chair Lawson Mirror Il y a plusieurs choses à noter à propos de cette requête : 1. Tout d'abord, les mots "Last Name" et "Item Ordered" dans les lignes SELECT donnent les en-têtes du rapport. 2. Les mots OWN et ORD sont des alias; ce sont de nouveaux noms pour les deux tables données dans la clause FROM qui sont utilisés comme préfixes pour toutes les notations point (.) de noms de colonnes dans les requêtes (voir ci-dessus). Ceci élimine les risques ambiguïté, spécialement dans l'équi-jointure de la clause WHERE où les deux tables ont une colonne nommée OwnerID, et cette notation point (.) précise à SQL que nous désignons deux OwnerID différents de deux tables différentes. 3. Notez que la table des commandes (ORDERS) est indiquée la première dans la clause FROM; ceci apporte la certitude que la liste sera réalisée à partir de cette table, et que la table AntiqueOwners est utilisée uniquement pour obtenir les informations détaillées (Last Name / Nom). 4. Plus important, la clause AND dans la clause WHERE (OU) force l'utilisation de la Sous-requête In ("= ANY" ou "= SOME" sont deux utilisations équivalentes de IN). Cela entraîne que la sous-requête est effectuée, retournant une liste de tous les articles (Items) appartenant à la table Antiques, comme s'il n'y avait pas de clause WHERE (OU). Donc, pour lister une ligne de la table ORDERS, le ItemDesired (article_désiré) doit être dans la liste retournée des articles appartenant à la table Antiques, et donc un article ne sera listé que si la commande ne peut être honorée que par un autre propriétaire. On peut se le représenter comme ceci: la sous-requête retourne un ensemble d'articles (Items) auquel chaque ItemDesired (Article_Désiré) dans la table des commandes (ORDERS) est comparé; la condition IN (DANS) n'est vraie que si le ItemDesired appartient à l'ensemble provenant de la table ANTIQUES. 5. Notez également, comme c'est le cas ici, qu'il y a un objet ancien pour chaque demande, ce qui, évidemment, n'est pas toujours le cas... De plus, notez aussi que, lorsque IN, "= ANY", ou "= SOME" est utilisé, ces mots-clés font référence à toutes les lignes qui conviennent, pas aux colonnes... c'est à dire que vous ne pouvez pas mettre de multiples colonnes dans un clause SELECT de sous-requête, pour tenter de faire correspondre la colonne de la clause WHERE externe avec l'une des multiples valeurs de colonnes possibles de la sous-requête; une seule colonne peut être indiquée dans la sous-requête, et la correspondance possible provient de multiples valeurs de lignes, dans cette colonne unique, et non pas l'inverse. Ouf! Ce sera tout sur ce sujet des requêtes SELECT complexes pour l'instant. Maintenait, voyons d'autres instructions SQL. ---------------------------------------------------------------------------- Instructions SQL Diverses Fonctions d'Agrégation Je parlerai de cinq fonctions d'agrégation importantes: SUM, AVG, MAX, MIN, et COUNT. On les appelle fonctions d'agrégation parce qu'elles résument les résultats d'une requête, plutôt que de donner une liste de toutes les lignes. * SUM () donne la somme, pour une colonne donnée, de toutes les lignes qui satisfont aux conditions requises, et où la colonne donnée est numérique. * AVG () donne la moyenne de la colonne donnée. * MAX () donne la plus grande valeur dans la colonne donnée. * MIN () donne la plus petite valeur dans la colonne donnée. * COUNT(*) donne le nombre de lignes qui satisfont aux conditions. En utilisant les tables du début de ce document, regardons trois exemples : SELECT SUM(SALARY), AVG(SALARY) FROM EMPLOYEESTATISTICSTABLE; Cette requête donne la somme des de salaires tous les salariés présents dans la table et le salaire moyen. SELECT MIN(BENEFITS) FROM EMPLOYEESTATISTICSTABLE WHERE POSITION = 'Manager'; Cette requête donne le chiffre de la colonne indemnités le plus faible des employés qui sont Managers, cette valeur est 12 500. SELECT COUNT(*) FROM EMPLOYEESTATISTICSTABLE WHERE POSITION = 'Staff'; Cette requête vous donne le nombre d'employés ayant le statut de cadre (Staff, i.e. 3). ---------------------------------------------------------------------------- Les Vues En SQL, vous pouvez (vérifiez auprès de votre Administrateur de Base de Données, DBA) avoir accès à la création de vues par vous-même. Une vue vous permet d'affecter les résultats d'une requête à une nouvelle table personnelle, que vous pourrez utiliser dans d'autres requêtes, pour laquelle vous donnez le nom de la vue dans votre clause FROM. Quand vous accédez à une vue, la requête qui est définie dans l'instruction de création de la vue est effectuée (généralement), et les résultats de cette requête ont la même allure qu'une autre table dans la requête que vous avez écrit en invoquant la vue. Par exemple, pour créer une vue : CREATE VIEW ANTVIEW AS SELECT ITEMDESIRED FROM ORDERS; Maintenant, écrivons une requête utilisant cette vue comme une table, où la table est seulement une liste de tous les articles désirés (ITEMDESIRED) de la table ORDERS : SELECT SELLERID FROM ANTIQUES, ANTVIEW WHERE ITEMDESIRED = ITEM; Cette table montre tous les Identifiants de Vendeurs (SellerID) de la table ANTIQUES où l'article (Item) dans cette table apparaît dans la vue ANTVIEW, qui consiste justement en tous les Articles Désirés (Items Desired) dans la table ORDERS. La liste est crée en parcourant les articles AntiquesItems un par un jusqu'à ce qu'il y ait correspondance avec la vue ANTVIEW. Les vues peuvent être utilisées pour restreindre les accès à la base de données, ainsi que, dans ce cas, pour simplifier une requête complexe. ---------------------------------------------------------------------------- Création de Nouvelles Tables Toutes les tables, dans une base de données doivent être créées à un certain moment... voyons comment mous pourrions créer la table des commandes (ORDERS) : CREATE TABLE ORDERS (OWNERID INTEGER NOT NULL, ITEMDESIRED CHAR(40) NOT NULL); Cette instruction donne un nom à la table et renseigne le SGBD sur la nature de chaque colonne de la table. Veuillez noter que cette instruction utilise des types de données génériques, et que les types de données peuvent être différents, selon le SGBD que vous utilisez. Comme d'habitude, vérifiez vos conditions locales. Voici quelques types de données génériques courants: * Char(x) - Une colonne de caractères, où x est un nombre indiquant le nombre maximum de caractères permis (taille maximale) de la colonne. * Integer - Une colonne de nombres entiers, positifs ou négatifs. * Decimal(x, y) - Une colonne de nombre décimaux, où x est la taille maximum, en digits, des nombres décimaux dans cette colonne, et y le nombre maximal de digits autorisés après la virgule. Le nombre maximal (4,2) sera 99.99. * Date - Une colonne de date dans un format spécifique au SGBD. * Logical - Une colonne qui ne peut contenir que deux valeurs: VRAI ou FAUX. Autre remarque, l'indication NOT NULL (non nul) signifie que la colonne doit avoir une valeur pour chacune des lignes. Si l'on avait utilisé NULL (nul), cette colonne peut être laissée vide dans certaines lignes. ---------------------------------------------------------------------------- Modification des tables Ajoutons une colonne à la table ANTIQUES pour permettre la saisie du prix d'un article donné : ALTER TABLE ANTIQUES ADD (PRICE DECIMAL(8,2) NULL); On verra plus tard comment les données pour cette nouvelle colonne peuvent être mises à jour ou ajoutées. ---------------------------------------------------------------------------- Ajout de Données Pour insérer des lignes dans une table, faites ce qui suit : INSERT INTO ANTIQUES VALUES (21, 01, 'Ottoman', 200.00); Ceci insère les données dans la table, en tant que nouvelle ligne, colonne par colonne, dans un ordre prédéfinit. Au lieu de cela, changeons cet ordre et laissons le Prix vide: INSERT INTO ANTIQUES (BUYERID, SELLERID, ITEM) VALUES (01, 21, 'Ottoman'); ---------------------------------------------------------------------------- Suppression de données Supprimons cette nouvelle ligne de la base de données : DELETE FROM ANTIQUES WHERE ITEM = 'Ottoman'; Mais s'il y a une autre ligne qui contient 'Ottoman', cette ligne sera également supprimée. Supprimons toutes les lignes (une, dans ce cas) qui contient les données spécifiques que nous avons ajouté plus tôt : DELETE FROM ANTIQUES WHERE ITEM = 'Ottoman' AND BUYERID = 01 AND SELLERID = 21; ---------------------------------------------------------------------------- Mise à Jour des Données Mettons un Prix à jour dans une ligne qui n'en contient pas encore : UPDATE ANTIQUES SET PRICE = 500.00 WHERE ITEM = 'Chair'; Ceci met le Prix de toutes les Chaises (Chair) à 500.00. Comme indiqué ci-dessus, conditions WHERE supplémentaires, utilisation de AND, il faut utiliser ces conditions pour limiter la mise à jour à des lignes spécifiques. De plus, des colonnes supplémentaires peuvent être renseignées en séparant les instructions "égal" par des virgules. ---------------------------------------------------------------------------- Considérations Diverses Index Les Index permettent à un SGBD d'accéder au données plus rapidement (veuillez noter que cette fonctionnalité est non-standard/indisponible sur certains systèmes). Le système crée une structure de donnée interne (l'index) qui entraîne une sélection de lignes beaucoup plus rapide, quand la sélection est basée sur des colonnes indexées. Cet index indique au SGBD où se trouve une certaine ligne dans une table étant donné une valeur de colonne indexée, exactement comme l'index d'un livre vous indique à quelle page un mot donné se trouve. Créons un index pour le OwnerID dans la colonne AntiqueOwners : CREATE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID); Maintenant sur les noms: CREATE INDEX NAME_IDX ON ANTIQUEOWNERS (OWNERLASTNAME, OWNERFIRSTNAME); Pour être débarrassé d'un index, supprimez le : DROP INDEX OID_IDX; A propos, vous pouvez aussi bien "supprimer" une table (attention!--cela signifie que votre table est détruite). Dans le second exemple, l'index est construit à partir des deux colonnes, agrégées ensembles--un comportement bizarre peut résulter de cette situation... vérifiez dans votre manuel avant d'effectuer une telle opération. Quelques SGBD n'imposent pas l'utilisation de clés primaires; en d'autres termes, l'unicité d'une colonne n'est pas imposée automatiquement. Cela signifie que, par exemple, j'aurais pu essayer d'insérer une autre ligne dans la table AntiqueOwners avec un OwnerID de 02, quelques systèmes me permettent de faire cela, bien qu'il ne le faille pas, puisque cette colonne est supposée être unique dans cette table (chaque valeur de ligne est supposée être différente). Une manière de contourner cela est de créer un index unique sur la colonne que nous souhaitons voir être la clé primaire pour forcer le système à interdire les doubles : CREATE UNIQUE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID); ---------------------------------------------------------------------------- GROUP BY et HAVING Une utilisation spéciale de la clause GROUP BY est l'association d'une fonction agrégée (spécialement COUNT; qui compte le nombre de lignes dans chaque groupe) avec des groupes de lignes. Premièrement, supposons que la table ANTIQUES possède la colonne Prix (Price)t, et que chaque ligne contienne une valeur dans cette colonne. Nous voulons voir le prix de l'article le plus cher acheté par chaque propriétaire. Il nous faut donc dire à SQL de grouper les achats de chacun des propriétaires, et de nous dire le prix d'achat maximum : SELECT BUYERID, MAX(PRICE) FROM ANTIQUES GROUP BY BUYERID; Maintenant, disons que nous ne voulons voir que le prix maximum si l'achat dépasse 1000, nous devrons utiliser la clause HAVING : SELECT BUYERID, MAX(PRICE) FROM ANTIQUES GROUP BY BUYERID HAVING PRICE > 1000; ---------------------------------------------------------------------------- Sous-requêtes Supplémentaires Un autre usage commun des sous-requêtes amène à l'utilisation des opérateurs pour permettre à une condition WHERE d'inclure la sortie SELECT d'une sous-requête. En premier, demandons la liste des acheteurs ayant acheté un article cher (le prix de cet article est supérieur de 100 au prix moyen de tous les articles achetés) : SELECT OWNERID FROM ANTIQUES WHERE PRICE > (SELECT AVG(PRICE) + 100 FROM ANTIQUES); La sous-requête calcule le Prix moyen, ajoute 100, et, et en utilisant ce chiffre on imprime un OwnerID pour chaque article coûtant plus que ce chiffre. On peut utiliser DISTINCT OWNERID, pour éliminer les doubles. Listons les Noms (Last Names) de ceux qui sont dans la table AntiqueOwners, SEULEMENT s'ils ont acheté un article : SELECT OWNERLASTNAME FROM ANTIQUEOWNERS WHERE OWNERID = (SELECT DISTINCT BUYERID FROM ANTIQUES); Cette sous-requête retourne une liste des acheteurs, et le Nom du propriétaire d'un objet ancien est imprimé seulement si l'identificateur du Propriétaire (Owner's ID) dans la liste obtenue par la sous-requête (appelée quelquefois liste des candidats). Voici un exemple de mise à jour: nous savons que la personne qui a acheté la bibliothèque a un Prénom erroné dans la base de données... Ce devrait être John : UPDATE ANTIQUEOWNERS SET OWNERFIRSTNAME = 'John' WHERE OWNERID = (SELECT BUYERID FROM ANTIQUES WHERE ITEM = 'Bookcase'); Tout d'abord, la sous-requête trouve le BuyerID pour la (les) personne(s) qui a (ont) acheté(s) la bibliothèque, puis la requête externe met à jour son Prénom. Souvenez vous de cette règle à propos des sous-requêtes: quand vous avez une sous-requête faisant partie d'une condition WHERE, la clause SELECT dans la sous-requête doit avoir des colonnes qui correspondent en nombre et en type à celle de la clause WHERE de la requête externe. En d'autres termes, si vous avez "WHERE ColumnName = (SELECT...);", le SELECT ne peut faire référence qu'à une seule colonne, pour pouvoir correspondre à la clause WHERE externe, et elles doivent être du même type (les deux étant soit entiers, soit chaînes de caractères, etc.). ---------------------------------------------------------------------------- EXISTS et ALL EXISTS utilise une sous-requête comme condition, où cette condition est Vraie si la sous-requête retourne au moins une ligne et Fausse si la sous-requête n'en retourne aucune; c'est une fonctionnalité qui n'est pas intuitive et n'est utilisée que dans peu de cas. Cependant, si un client éventuel voulait consulter la liste des propriétaires pour voir s'il y a des chaises (Chairs), essayez : SELECT OWNERFIRSTNAME, OWNERLASTNAME FROM ANTIQUEOWNERS WHERE EXISTS (SELECT * FROM ANTIQUES WHERE ITEM = 'Chair'); S'il y a des Chaises (Chair) dans une colonne de la table ANTIQUES, la sous-requête renverra une ou plusieurs lignes, rendant la clause EXISTS vraie, ce qui amènera SQL à donner une liste des propriétaires dans ANTIQUEOWNERS. S'il n'y avait eu aucune Chaise, la requête externe n'aurait pas renvoyé de ligne. ALL est une autre fonctionnalité peu commune, et en général, on peut réaliser une requête avec ALL de manières différentes et éventuellement plus simples; regardons cet exemple de requête : SELECT BUYERID, ITEM FROM ANTIQUES WHERE PRICE >= ALL (SELECT PRICE FROM ANTIQUES); Ceci va nous retourner l'article de prix le plus élevé (ou plus d'un article s'il y a des ex-aequo), et son acheteur. La sous-requête renvoie la liste de tous les Prix (PRICE) dans la table ANTIQUES, puis la requête externe examine chaque ligne de la table ANTIQUES et si son Prix est supérieur ou égal à chacun (ou ALL, TOUS) des Prix de cette liste, il est affiché, donnant ainsi l'article de prix le plus élevé. La raison pour laquelle ">=" doit être utilisé est que l'article de prix le plus élevé sera égal au prix le plus élevé, puisque cet Article est dans la liste de Prix. ---------------------------------------------------------------------------- UNION et Jointure Externe Il y a des occasions où vous pouvez désirer voir ensembles les résultats de requêtes multiples, leurs sorties étant combinées; pour cela utilisez UNION. Pour fusionner la sortie des deux requêtes suivantes, en affichant l'identificateur de tous les Acheteurs plus tous ceux qui ont passé une Commande : SELECT BUYERID FROM ANTIQUEOWNERS UNION SELECT OWNERID FROM ORDERS; Il faut noter que SQL nécessite que les types de données des listes des clauses SELECT correspondent colonne par colonne. Dans cet exemple, les identificateurs BuyerID et OwnerID sont du même type (entier). Notez également que SQL effectue automatiquement une élimination des doubles quand on utilise la clause UNION (comme si c'étaient deux "ensembles"); dans une requête simple, il faut utiliser la clause DISTINCT. La jointure externe est utilisée quand une requête de jointure est "unifiée", les lignes n'étant pas incluses dans la jointure. Ceci est particulièrement utile si des "balises" de type constante texte sont inclus. D'abord, regardez la requête : SELECT OWNERID, 'is in both Orders & Antiques' FROM ORDERS, ANTIQUES WHERE OWNERID = BUYERID UNION SELECT BUYERID, 'is in Antiques only' FROM ANTIQUES WHERE BUYERID NOT IN (SELECT OWNERID FROM ORDERS); La première requête effectue une jointure pour lister tous les propriétaires qui sont dans les deux tables, et met une balise après l'identificateur (ID) en insérant le texte correspondant à la balise. La clause UNION fusionne cette liste avec la liste suivante. La seconde liste est générée premièrement en listant les identificateurs (ID) qui ne sont pas dans la table ORDERS, c'est à dire en générant une liste des ID exclus de la requête de jointure.. Puis, chaque ligne de la table ANTIQUES est analysée, et, si l'identifiant de l'acheteur (BuyerID) n'est pas dans cette liste d'exclusion, il est listé avec le texte correspondant à sa balise. Il y aurait peut-être une meilleure manière de créer cette liste, mais c'est difficile de générer des balises informationnelles. Ce concept est utile dans des situations où une clé primaire est en relation avec une clé externe, et où la valeur de la clé externe est NULLE (NULL) pour quelques clés primaires. Par exemple, dans une table, la clé primaire est vendeur, et dans une autre table client, avec le vendeur enregistré dans la même ligne. Cependant, si un vendeur n'a pas de clients, le nom de cette personne n'apparaîtra pas dans la table des clients. Une jointure externe sera utilisée pour imprimer une liste de tous les vendeurs, avec leurs clients, que le vendeur ait un client ou pas--c'est à dire qu'il n'y aura pas de client imprimé (valeur logique NULL) si le vendeur n'a pas de client, mais existe dans la table des vendeurs. Autrement, le vendeur sera listé avec chaque client. ASSEZ DE REQUETES!!! Qu'est-ce que vous dites?...Eh bien, maintenant voyons quelque chose de complètement différent... ---------------------------------------------------------------------------- SQL incorporé--un vilain exemple (n'écrivez pas un programme comme cela... il est là UNIQUEMENT à titre d'exemple) /* -Voici un exemple de programme qui utilise le SQL incorporé (Embedded SQL). Le SQL incorporé permet aux programmeurs de se connecter à une base de données et d'inclure du code SQL en plein programme, et ainsi, leurs programmes peuvent utiliser, manipuler, et traiter les données d'une base de données. -Cet exemple de Programme C (qui utilise du SQL incorporé) doit imprimer un rapport. -Les instructions SQL devront être précompilées avant d'effectuer la compilation normale. -Si vous utilisez un langage différent les parties EXEC SQL seront les mêmes (standard), mais le code C qui les entourent devront être modifiées, y compris les déclarations de variables hôtes. -Le SQL incorporé diffère de système à système, aussi, encore une fois, vérifiez la documentation locale, spécialement les déclarations de variables et les procédures de connexion pour lesquelles le réseau, le SGBD, et le système d'exploitation sont cruciaux. */ /***************************************************/ /* CE PROGRAMME N'EST PAS COMPILABLE OU EXECUTABLE */ /* IL EST UNIQUEMENT DONNE A TITRE D'EXEMPLE */ /***************************************************/ #include <stdio.h> /* Section de déclaration des variables hôtes; ce seront les variables utilisées par votre programme, mais également celles utilisées par SQL pour y mettre ou y lire des valeurs,. */ EXEC SQL BEGIN DECLARE SECTION; int BuyerID; char Prenom[100], Nom[100], Item[100]; EXEC SQL END DECLARE SECTION; /* Cette section,insère les variables SQLCA, de façon à pouvoir tester les erreurs. */ EXEC SQL INCLUDE SQLCA; main() ( /* Ceci est une possibilité pour se connecter à la base de données */ EXEC SQL CONNECT UserID/Password; /* Cette partie de code soit vous indique que vous êtes connecté soit teste si un code erreur a été généré, signifiant que la connexion était incorrecte ou impossible. */ if(sqlca.sqlcode) ( printf(Printer, "Erreur de connexion au serveur de base de données.\n"); exit(); ) printf("Connecté au serveur de base de données.\n"); /* Ici, on déclare un "Curseur". C'est utilisé lorsqu'une requête retourne plus d'une ligne, et qu'on doit effectuer un traitement sur chaque ligne obtenue de la requête. Je vais utiliser pour le rapport, chaque ligne obtenue par cette requête. Ensuite, on utilisera "FETCH" (va chercher) pour récupérer les lignes, une par une, mais pour que la requête soit effectivement exécutée, il faut utiliser l'instruction "OPEN". La "Déclaration" (Declare) sert uniquement à construire la requête. */ EXEC SQL DECLARE ItemCursor CURSOR FOR SELECT ITEM, BUYERID FROM ANTIQUES ORDER BY ITEM; EXEC SQL OPEN ItemCursor; /* +-- Insérer ici un test d'erreur similaire au précédent si vous le désirez --+ */ /* L'instruction FETCH insère les valeurs de la ligne suivante respectivement dans chacune des variables hôtes. Cependant un "priming fetch" (technique de programmation) doit être exécuté en premier. Lorsque le curseur n'a plus de données un code (sqlcode) est généré nous permettant de sortir de la boucle. Notez que, pour des raisons de simplicité, on abandonne la boucle pour n'importe quel sqlcode, même s'il correspond à un code erreur. Autrement, il faut effectuer un test d'erreur spécifique. */ EXEC SQL FETCH ItemCursor INTO :Item, :BuyerID; while(!sqlca.sqlcode) ( /* Nous effectuerons également deux traitements pour chaque ligne. Premièrement, augmenter le prix de 5 (rétribution du marchand) et ensuite, lire le nom de l'acheteur pour le mettre dans le rapport. Pour faire cela, j'utiliserai les instructions Update et Select, avant d'imprimer la ligne sur l'écran. La mise à jour suppose, cependant, qu'un acheteur donné n'a acheté qu'un seul article, ou, sinon, le prix sera augmenté de trop nombreuses fois. Sinon, il aurait fallu utiliser une logique "RowID" (consulter la documentation). De plus, notez la présence du signe : (deux points) qui doit être placé devant les noms de variables hôtes quand elles sont utilisées dans des instructions SQL. */ EXEC SQL UPDATE ANTIQUES SET PRICE = PRICE + 5 WHERE ITEM = :Item AND BUYERID = :BuyerID; EXEC SQL SELECT OWNERFIRSTNAME, OWNERLASTNAME INTO :Prenom, :Nom FROM ANTIQUEOWNERS WHERE BUYERID = :BuyerID; printf("%25s %25s %25s", Prenom, Nom, Item); /* Rapport grossier--uniquement à but d'exemple! Aller chercher la ligne suivante */ EXEC SQL FETCH ItemCursor INTO :Item, :BuyerID; ) /* Fermer le curseur, enregistrer les modifications (voir ci-dessous), et quitter le programme. */ EXEC SQL CLOSE DataCursor; EXEC SQL COMMIT RELEASE; exit(); ) ---------------------------------------------------------------------------- Questions courantes sur SQL--Sujets avancés (Consulter les liens FAQ pour en avoir plusieurs autres) 1. Pourquoi ne puis-je pas demander uniquement les trois premières lignes d'une table? --Parce que, dans une base de données relationnelle, les lignes sont insérées sans aucun ordre particulier, c'est à dire que le système les insèrent dans un ordre arbitraire; de ce fait, vous ne pouvez demander des lignes qu'en utilisant des fonctionnalités SQL valides, telles que ORDER BY, etc. 2. Que sont ces DDL et DML dont j'entends parler ? --DDL (Data Definition Language - Langage de Définition de Données) fait référence à (en SQL) l'instruction de Création de Table (Create Table)...DML (Data Manipulation Language - Langage de Manipulation de Données) fait référence aux instructions Select, Update, Insert, et Delete. 3. Les tables des base de données ne sont elles pas simplement des fichiers? --Eh bien, les SGBD stockent les données dans des fichiers déclarés par le gestionnaire du système avant que de nouvelles tables ne soient créées (sur les grands systèmes), mais le système stocke les données dans un format spécial, et peut répartir les données d'une table dans plusieurs fichiers. Dans le monde des bases de données, un ensemble de fichiers créés pour une base de données est appelé un "espace de tables". En général, sur les petits systèmes, tout ce qui concerne une base de données (définitions et toutes les tables de données) est stocké dans un seul fichier. 4. (Question en relation avec la précédente) Les bases de données ne sont elles pas simplement que des tableurs? --Non, et ceci pour deux raisons. Premièrement, les tableurs peuvent avoir des données dans une cellule, mais une cellule est plus qu'une intersection ligne-colonne. Selon votre tableur, une cellule peut aussi contenir des formules et un formatage, ce que les bases de données (actuelles) ne permettent pas. Deuxièmement, les cellules dépendent souvent des données présentes dans d'autres cellules. Dans les bases de données, les "cellules" sont indépendantes, sauf que les colonnes sont en relation logique (heureusement: ensembles, une ligne et une colonne décrivent une entité), et, en dehors des contraintes de clé primaire et de clé externe, chaque ligne d'une table est indépendante des autres. 5. Comment puis-je importer un fichier texte de données dans une base de données? --Eh bien, vous ne pouvez pas le faire directement...il vous faut utiliser un programme utilitaire, tel que le SQL*Loader pour Oracle, ou écrire un programme pour charger les données dans la base de données. Un programme pour réaliser cela lit simplement chaque enregistrement du fichier texte, le sépare en colonnes, et effectue une insertion (INSERT) dans la base de données. 6. Qu'est-ce qu'un schéma? --Un schéma est un ensemble logique de tables, tel que la base de données ANTIQUES ci-dessus...habituellement, on s'y réfère simplement en tant que "base de données", mais une base de données peut contenir plus d'un schéma. Par exemple, un schéma en étoile est un ensemble de tables où une table centrale, de grande taille, contient toutes les informations importantes, et est liée, via des clés externes, à des tables de dimension qui contiennent l'information détaillée, et qui peuvent être utilisées dans une jointure pour créer des rapports détaillés. 7. Quels conseils généraux pourriez vous donner pour rendre mes requêtes SQL et mes bases de données meilleures et plus rapides (optimisées)? o Vous devriez essayer, si vous le pouvez, d'éviter, dans les clauses SELECT, des expressions telles que SELECT ColumnA + ColumnB, etc. L'optimiseur de requêtes de la base de données, partie du SGBD qui détermine la meilleure manière d'extraire les données requises de la base de données elle-même, traite les expressions d'une façon telle que cela demande en général plus de temps pour extraire les données que si les colonnes avaient été sélectionnées normalement, et que l'expression elle-même calculée par programme. o Minimisez le nombre de colonnes incluses dans une clause Group By. o Si vous effectuez une jointure, tâchez d'avoir les colonnes, de cette jointure, indexées (dans les deux tables). o Si vous avez un doute, créez un index. o A moins que vous ne fassiez de multiple comptages ou une requête complexe, utilisez COUNT(*) (le nombre de lignes sera généré par la requête) plutôt que COUNT(Column_Name). 8. Qu'est-ce que la Normalisation? --La Normalisation est une technique de conception de base de données qui suggère qu'un certain critère doit être pris en compte quand on définit l'organisation des tables (prise de décision sur le nombre de colonnes de chaque table, et création de la structure des clés), où l'idée est d'éliminer la redondance à travers les tables des données qui ne sont pas des clés. On parle de la normalisation en s'y référant habituellement en termes de "formes", et j'introduirais ici uniquement les trois premières, bien qu'il soit tout à fait courant d'en utiliser d'autres formes, plus avancées (quatrième, cinquième, Boyce-Codd; consultez la documentation). La Première Forme Normale consiste à placer les données dans des tables séparées où les données dans chaque tables sont de type similaire, et à donner à chaque table une clé primaire. Mettre les données en Seconde Forme Normale consiste à mettre les données dans les tables où elle ne dépendent uniquement que d'une partie de la clé. Par exemple, si j'avais laissé les noms des propriétaires d'objets anciens dans la table des articles, ce n'aurait pas été une seconde forme normale puisque les données auraient été redondantes; le nom aurait dû être répété pour chaque article possédé, donc, les noms ont été placés dans leur propre table. Les noms en eux-mêmes n'ont rien à voir avec les articles, seules les identités des acheteurs et des vendeurs sont concernées. La Troisième Forme Normale consiste à se débarrasser, dans une table, à tout ce qui ne dépend pas uniquement de la clé primaire. On met uniquement l'information qui dépend de la clé, et l'on déplace, dans d'autres tables, tout ce qui est indépendant de la clé primaire, et l'on crée des clés primaires pour les nouvelles tables. Il y a une certaine forme de redondance dans chaque forme, et si les données sont en 3NF (abrégé pour 3ième forme normale), elles sont déjà en 1NF et 2NF. En terme d'organisation des données, organisez vos données de façon que les colonnes qui ne sont pas des clés primaires dépendent seulement de la clé primaire entière. Si vous jetez un oeil sur la base de données en exemple, vous verrez que, lorsque vous naviguez à travers la base de données, c'est au travers de jointures qui utilisent les colonnes de clé commune. Deux autre points importants dans la conception de bases de données sont l'utilisation de noms significatifs, bien choisis, cohérents et logiques pour les tables et les colonnes et l'utilisation de noms significatifs pour la base de données elle-même. Sur le dernier point, ma base de données pèche, puisque j'utilise des codes numériques comme identificateurs. C'est en général bien meilleur d'utiliser, si possible, des clés qui ont, en elles-mêmes, un sens; par exemple, une meilleure clé pourrait consister des quatre premières lettres du nom et de la première initiale du prénom, comme JONEB pour Bill Jones (ou pour éviter les doubles, ajoutez-y des nombres à la fin pour différencier deux ou plusieurs personnes ayant le même nom, ainsi, on pourrait essayer JONEB1, JONEB2, etc.). 9. Quelle est la différence entre une requête simple ligne et une requête multi-lignes et pourquoi est-ce important de connaître cette différence? --Premièrement, pour parler de ce qui est évident, une requête simple ligne est une requête qui retourne une ligne unique comme résultat, et le résultat d'une requête multi-lignes est constitué de plusieurs lignes. Qu'une requête retourne une ligne ou plusieurs dépend complètement de la conception (ou schéma) des tables qui constituent la base de données. Assurez-vous d'inclure suffisamment de conditions, et structurez vos instructions SQL correctement, de façon à obtenir le résultat désiré (soit une ligne, soit plusieurs). Par exemple, si vous vouliez être sûr qu'une requête sur la table AntiqueOwners ne retourne qu'une ligne, employez une condition d'égalité sur la clé primaire, OwnerID. Trois raisons, concernant l'importance de ce sujet, viennent immédiatement à l'esprit. Premièrement, l'obtention de plusieurs lignes alors que vous n'en attendez qu'une, ou vice-versa, peut signifier que la requête est erronée, que la base de données est incomplète, ou simplement que vous découvrez quelque chose de nouveau concernant vos données. Deuxièmement, si vous utilisez une instruction de mise à jour (UPDATE) ou de suppression (DELETE), il vaudrait mieux vous assurer que l'instruction que vous écrivez effectue bien l'opération sur la ligne désirée (ou les lignes)...ou sinon, vous pourriez supprimer ou mettre à jour plus de lignes que vous ne le désirez. Troisièmement, il faut soigneusement penser au nombre de lignes qui seront retournées pour toutes les requêtes rédigées en SQL incorporé. Si vous écrivez une requête simple ligne, une seule instruction SQL peut suffire pour satisfaire à la logique du programme. D'un autre côté, si votre requête retourne de multiples lignes, il vous faudra utiliser l'instruction FETCH, et très certainement quelque chose comme une structure de boucle sera nécessaire dans votre programme pour traiter chaque ligne retournée par la requête. 10. Que sont les relations? --C'est une autre question de conception...le terme "relation" fait habituellement référence aux relations entre clés primaires et externes entre les tables. Ce concept est important parce que, quand les tables d'une base de données relationnelle sont conçues, ces relations doivent être définies parce que cela détermine quelles colonnes sont ou ne sont pas des clés primaires ou externes. Vous avez peut-être entendu parler des diagrammes Entités-Relations, qui sont une représentation graphique des tables dans les schéma de la base de données. Voyez l'exemple de diagramme à la fin de cette section ou consultez quelques sites indiqués ci-dessous concernant ce sujet, car il y a de nombreuses manières de dessiner les diagrammes E-R. Mais d'abord, jetons un oeil à chaque type de relation... Une relation 1-à-1 (ou 1:1, ou 1-1) signifie que vous avez une colonne clé primaire et que chaque clé primaire est en relation avec une clé externe. Par exemple, dans le premier exemple, dans la table des adresses des employés TableAdresseEmploye nous avons une colonne numéro d'identification de l'employé (EmployeeIDNo). Donc, la table TableAdresseEmploye est en relation avec la table EmployeeStatisticsTable (deuxième exemple de table) par l'intermédiaire du numéro EmployeeIDNo. Plus précisément, chaque employé, de la table TableAdresseEmploye possède des statistiques (une ligne de données) dans la table EmployeeStatisticsTable. Même si c'est un exemple inventé, c'est une relation "1-1". Inscrivez en caractères gras le "has" ("a" ou "possède")...quand on décrit une relation, il est important de décrire une relation en utilisant un verbe. Les deux autres types de relations peuvent ou pas utiliser une clé primaire logique et des contraintes par rapport aux clés externes...cela dépend strictement des souhaits du concepteur. La première de ces relations est la relation un-à-plusieurs ("1-M"). Cela signifie que pour chaque valeur d'une colonne dans une table, il y a une ou plusieurs valeurs correspondantes dans une autre table. Des contraintes de clé peuvent être ajoutées au modèle, ou éventuellement une colonne d'identification peut être utilisée pour établir une relation. Un exemple serait que pour chaque OwnerID dans la table AntiqueOwners, il y ait un ou plusieurs (la valeur zéro est également autorisée) articles (Items) achetés dans la table ANTIQUES (verbe: acheter). Finalement, la relation plusieurs-à plusieurs ("M-M") n'utilise généralement pas de clés, et habituellement utilise des identificateurs de colonnes. L'apparition inhabituelle d'une relation "M-M" signifie qu'une colonne, dans une table est en relation avec une autre colonne dans une autre table, et que pour chaque valeur de l'une de ces deux colonnes, il y a une ou plusieurs valeurs correspondantes dans la colonne correspondante de l'autre table (et vice-versa), ou possibilité plus courante, les deux tables ont une relation 1-M avec l'autre (deux relations, une 1-M dans chaque direction). Un [mauvais] exemple de la situation la plus courante consisterait, si vous avez une bases de données de fonctions, à avoir une table possédant une ligne pour chaque employé et sa fonction, et d'avoir une autre table contenant une ligne pour chaque fonction avec un des employés l'occupant. Dans ce cas, vous auriez plusieurs lignes pour chaque employé dans la première table, une pour chaque fonction, et plusieurs lignes pour chaque fonction dans le seconde table, une pour chaque employé ayant cette fonction. Ces tables sont en relation M-M: chaque employé, dans la première table a plusieurs fonctions dans la seconde table, et chaque fonction, dans la seconde table, a plusieurs attributaires dans la première table. Ceci est la partie émergée de l'iceberg concernant ce sujet...consultez les liens ci-dessous pour avoir de plus amples informations et regardez le diagramme ci-dessous donnant un exemple simplifié de diagramme E-R. [Exemple Simplifié de Diagramme Entités-Relations] 11. Quelles sont quelques unes des fonctionnalités importantes, non standard, de SQL (Question extrêmement courante)? --Eh bien, nous allons voir çà dans la section suivante... ---------------------------------------------------------------------------- SQL Non-standard..."A vérifier pour votre site" * INTERSECT et MINUS sont comme des instructions UNION , sauf que INTERSECT produits des lignes qui apparaissent dans les deux requêtes, et que MINUS produit des lignes provenant de la première requête mais pas de la seconde. * Fonctionnalités de la Génération de Rapport: la clause COMPUTE est placée à la fin d'une requête pour placer le résultat d'une fonction agrégée à la fin d'une liste, comme COMPUTE SUM (PRICE); Une autre solution est d'utiliser une logique d'interruption: définir une interruption pour diviser les résultats de la requête en groupes basés sur une colonne, comme BREAK ON BUYERID. Alors, pour sortir un résultat après la liste d'un groupe, utilisez COMPUTE SUM OF PRICE ON BUYERID. Si, par exemple, vous avez utilisé ces trois clauses ("BREAK" en premier, "COMPUTE on break" en second, "COMPUTE overall sum" en troisième), vous obtiendrez un rapport qui regroupera les articles par acheteurs, listera la somme de Prix pour chaque groupe d'articles d'un acheteur, puis, après que tous les groupes aient été listés, listera la somme de tous les Prix, le tout, avec des en-têtes et des lignes générés par SQL. * En plus des fonctions agrégées indiquées ci-dessus, quelques SGBD ont des fonctions supplémentaires qui peuvent être utilisées dans des listes de sélection (SELECT), sauf que ces fonctions (quelques fonctions caractères autorisent des résultats de plusieurs lignes) doivent être utilisées avec une valeur individuelle (pas de groupes), pour des requêtes simple ligne. De plus, les fonctions ne doivent être utilisées qu' avec les types de données appropriés. Voici quelques Fonctions Mathématiques: ABS(X) Valeur A-convertit les nombres négatifs en nombres positifs et laisse les positifs inchangés CEIL(X) X est une valeur décimale qui sera arrondie à la valeur supérieure. FLOOR(X) X est une valeur décimale qui sera arrondie à la valeur inférieure. GREATEST(X,Y)Retourne la plus grande des deux valeurs. LEAST(X,Y) Retourne la plus petite des deux valeur. MOD(X,Y) Retourne le reste de X / Y. POWER(X,Y) Retourne X à la puissance Y. ROUND(X,Y) Arrondit X à Y positions décimales. i Y n'est pas donné, X est arrondi à la valeur de l'entier le plus proche. SIGN(X) Retourne le signe - si X < 0, sinon retourne un signe plus. SQRT(X) Retourne la racine carrée de X. Fonctions Caractères LEFT(<string>,X) Retourne les X caractères les plus à gauche de la chaîne de caractères. RIGHT(<string>,X) Retourne les X caractères les plus à droite de la chaîne de caractères. UPPER(<string>) Convertit tous les caractères de la chaîne en majuscules. LOWER(<string>) Convertit tous les caractères de la chaîne en minuscules. INITCAP(<string>) Convertit les caractères initiaux de la chaîne en Capitales. LENGTH(<string>) Retourne le nombre de caractères de la chaîne. <string>||<string> Combine les deux chaînes en une seule chaîne, concaténée, où la première chaîne est immédiatement suivie par la seconde. LPAD(<string>,X,'*') Insère des caractères * (ou n'importe quel autre, mis entre guillemets) à gauche de la chaîne de caractères pour lui donner une longueur de X caractères. RPAD(<string>,X,'*') Insère des caractères * (ou n'importe quel autre, mis entre guillemets) à droite de la chaîne de caractères pour lui donner une longueur de X caractères. SUBSTR(<string>,X,Y) Extrait Y lettres de la chaîne à partir de la position X. NVL(<column>,<value>) La fonction NVL va substituer la valeur <value> pour chaque valeur nulle dans la colonne <column>. Si la valeur courante dans la colonne <column> n'est pas nulle (NULL), NVL est sans effet. ---------------------------------------------------------------------------- Résumé de la Syntaxe--Pour Utilisateurs Avancés Seulement Voici la forme générale des instructions dont il a été question dans ce didacticiel, avec, en plus, quelques autres, (des explications sont données). SOUVENEZ-VOUS que toutes ces instructions peuvent ne pas être disponibles sur votre système, aussi vérifiez leur disponibilité dans la documentation: ALTER TABLE <TABLE NAME> ADD|DROP|MODIFY (COLUMN SPECIFICATION[S]...voir Create Table); --vous permet d'ajouter ou de supprimer une ou plusieurs colonnes d'une table, ou de changer les spécifications(données, types, etc.) d'une colonne existante; cette instruction est également utilisée pour changer les spécifications physiques d'une table (comment la table est stockée, etc.), mais ces définitions sont spécifiques au SGBD, aussi, lire la documentation. De même, ces spécifications physiques sont utilisées avec les instructions de création de table, lorsqu'une table est créée pour la première fois. De plus, seulement une seule option peut être exécutée par instruction de modification: (Alter Table)--soit add (ajout), drop (suppression), OU modify (modification) dans une simple instruction. COMMIT; --rend les modifications effectuées sur une bases de données permanentes (depuis le dernier COMMIT; connu sous le nom de transaction) CREATE [UNIQUE] INDEX <INDEX NAME> ON <TABLE NAME> (<COLUMN LIST>); --UNIQUE est optionnel; entre parenthèses. CREATE TABLE <TABLE NAME> (<COLUMN NAME> <DATA TYPE> [(<SIZE>)] <COLUMN CONSTRAINT>, ...autres colonnes); (valide également avec ALTER TABLE) --où SIZE est uniquement utilisé avec certains types de données (voir ci-dessus), et les contraintes incluent les possibilités suivantes (imposé automatiquement par le SGBD; (un non respect entraîne la génération d'une erreur) : 1. NULL ou NOT NULL (voir ci-dessus) 2. UNIQUE impose que deux lignes ne peuvent avoir la même valeur pour cette colonne 3. PRIMARY KEY indique à la base de données que cette colonne est la colonne clé primaire (utilisé uniquement si la clé est une colonne clé, autrement une instruction PRIMARY KEY (column, column, ...) apparaît après la dernière définition de colonne. 4. CHECK permet de tester une condition quand on insère ou on met à jour une donnée dans cette colonne; par exemple, CHECK (PRICE > 0) amène le système à tester si la colonne Prix est supérieure ou égale à zéro avant d'accepter la valeur...quelquefois implanté comme instruction CONSTRAINT. 5. DEFAULT insère une valeur par défaut dans la base de données si l'on veut insérer une ligne qui ne contienne pas de valeur pour cette colonne; par exemple, BENEFITS INTEGER DEFAULT = 10000 6. FOREIGN KEY fonctionne comme la Clé Primaire, mais est suivi par: REFERENCES <TABLE NAME> (<COLUMN NAME>), qui fait référence à la clé primaire de référence. CREATE VIEW <TABLE NAME> AS <QUERY>; DELETE FROM <TABLE NAME> WHERE <CONDITION>; INSERT INTO <TABLE NAME> [(<COLUMN LIST>)] VALUES (<VALUE LIST>); ROLLBACK; --Annule toutes les modifications effectuées dans la base de données, celles qui ont été faites depuis la dernière commande COMMIT...Attention! Quelques logiciels travaillant en transactions, donc, la commande ROLLBACK peut ne pas fonctionner. SELECT [DISTINCT|ALL] <LIST OF COLUMNS, FUNCTIONS, CONSTANTS, ETC.> FROM <LIST OF TABLES OR VIEWS> [WHERE <CONDITION(S)>] [GROUP BY <GROUPING COLUMN(S)>] [HAVING <CONDITION>] [ORDER BY <ORDERING COLUMN(S)> [ASC|DESC]]; --où ASC|DESC permet le classement en ordre ascendant (ASCending) ou descendant (DESCending) UPDATE <TABLE NAME> SET <COLUMN NAME> = <VALUE> [WHERE <CONDITION>]; --si la clause WHERE n'est pas donnée, toutes les lignes seront mises à jour selon l'instruction SET ---------------------------------------------------------------------------- Liens Importants Liens Informatique & SQL/DB : Netscape -- Oracle -- Sybase -- Informix --Microsoft Page de Référence -- Ask the SQL Pro -- SQL Pro's Relational DB Sites Utiles Programmer's Source -- DBMS Sites -- inquiry.com -- DB Ingredients Web Authoring -- Computing Dictionary -- DBMS Lab/Links -- SQL FAQ -- SQL Databases RIT Database Design Page -- Database Jump Site -- Didacticiels de programmation sur le Web Ressources pour le Développement -- Query List -- IMAGE SQL Divers: CNN -- USA Today -- Pathfinder -- ZDNet -- Metroscope -- CNet Liste de ressources sur Internet -- Netcast Weather -- TechWeb -- LookSmart Moteurs de Recherche: Yahoo -- Alta Vista -- Excite -- WebCrawler -- Lycos -- Infoseek -- search.com L'auteur n'est pas reponsable de ces sites. ---------------------------------------------------------------------------- Avertissement J'espère que vous aurez appris quelque chose de ce premier regard sur un langage très important qui est en train de devenir plus répandu dans le mode de l'informatique client-serveur. J'ai rédigé cette page web pour apporter quelque chose d'utile au web et à la communauté des utilisateurs du web. En réalité, j'ai appris que ce document est utilisé dans plusieurs collèges pour des cours sur les bases de données et par des chercheurs. En outre, lisez cette page dans le nouveau livre, édité par Waite Publishing, sur Borland C++ Builder, qui sera publié cet été et dans une version à venir chez Sams Publishing. De plus, je voudrais remercier tous les gens, sur les cinq continents, qui m'ont contactés à propos de cette page. J'espère aussi continuer à ajouter plus d'éléments à ce didacticiel, tels que des articles sur la conception d'une base de données et les extensions SQL non standard, même si je souhaite me tenir à l'écart de particularités propres à un Système de Gestion de Base de Donnée. Bonne chance pour vos développements en SQL et autres aventures informatiques. Jim Hoffman ---------------------------------------------------------------------------- Commentaires ou Suggestions? Envoyez-moi un courrier électronique à jhoffman@one.net. Ou vous pouvez désirer jeter un oeil aux pages Web de Jim Hoffman's Web Pages pour plus d'informations me concernant. Copyright 1996-1997, James Hoffman. Ce document peut être utilisé gratuitement par n'importe quel utilisateur d'Internet, mais ne peut pas être inclus dans un autre document, publié sous une autre forme, ou produit en masse de quelque façon que ce soit. Netscape Navigator donne le meilleur affichage de cette page; celui-ci n'est pas très bon si l'on utilise Microsoft Internet Explorer. Dernière mise à jour : 8-25-1997; ajout de quelques éléments. Page suivante Page précédente Table des matières