SYSTÈME DE GESTION DE BASES DE DONNÉES

1. SYSTÈME DE GESTION DE BASES DE DONNÉES Animateur : M. HAIJOUBI

2. SOMMAIRE • Traduire les opérations de l’algèbre Relationnelle en requêtes SQL. • Exploiter l'environnement du SGBDR pour interroger une base de données. • Exploiter les commandes de description de données. • Exploiter les commandes de manipulation des données. • Gérer des transactions. • Utiliser les différents types d’objets sur une base de données. • Sécuriser les données.

3. 1

4. 1. Traduire les opérations de l’algèbre Relationnelle en requêtes SQL • L’algèbre relationnelle • Le langage SQL • Utiliser L’ordre SELECT Base de données exemple : Groupe(codeg,nomg) stagiaire(cin,nom,prénom,adresse,dn,ln,sexe, nb,codeg)

5. L’algèbre relationnelle • L'algèbre relationnelle est à l'origine du langage SQL (Structured Query Language) d'IBM, langage d'interrogation et de manipulation de tous les SGBDR actuels (Oracle, PostgreSQL, MySQL, MS SQLServer, MS Access et tous les autres). • Une bonne maîtrise de l'algèbre relationnelle permet de concevoir n'importe quelle requête aussi complexe soit elle avant de la mettre en œuvre à l'aide du langage SQL • Les opérations de base • Projection • Sélection • Jointure • Les opérations ensemblistes • Union • Intersection • Différence • Produit cartésien

6. La projection • Opération qui consiste à extraire toutes lignes d’une table pour un sous ensemble de colonnes • Exemple : Liste des nom,prénom des stagiaires Select nom,prénom from stagiaire SELECT nom, prénom FROM stagiaire

7. La sélection • Permet de retenir les lignes répondant à une condition de sélection • La condition est exprimée à l'aide des opérateurs =, >, <, >=, <=, <>, opérateurs logiques de base ET, OU, NON et éventuellement des parenthèses • Exemple : SELECT * FROM stagiaire WHERE ln=‘FES’ SELECT nom, prénom FROM stagiaire Where ln=‘Fes’

8. La jointure • Cet opérateur porte sur 2 tables qui doivent avoir au moins un attribut défini dans le même domaine • La condition de jointure peut porter sur l'égalité d'un ou de plusieurs attributs définis dans le même domaine SELECT * FROM stagiaire,groupe WHERE stagiaire.codeg = groupe.codeg

9. Union (OU) • Cet opérateur porte sur deux tables qui doivent avoir le même nombre d'attributs définis dans le même domaine. On parle de tables ayant le même schéma. • La table résultat possède les attributs des tables d'origine et les lignes de chacune, avec ou sans élimination des doublons éventuels.

10. Intersection (ET) • Cet opérateur porte sur deux tables de même schéma • La table résultat possède les attributs des tables d'origine et les lignes communes à chacune.

11. Différence • Cet opérateur porte sur deux tables de même schéma • La table résultat contient les lignes de la première table qui n'appartiennent pas à la deuxième

12. Produit cartésien • Cet opérateur porte sur deux tables • La table résultat est formé par la concaténation de chaque ligne de la première table avec l'ensemble des lignes de la deuxième

13. TP01 : Création de la BD (30mn) • Dans cet exercice vous allez utiliser SQL Server pour créer la base de données EFP comprenant les deux tables : groupe et stagiaire. • Démarrer SQL Enterprise manager • Enregistrer votre serveur • Créer la base de données • Créer les tables • Créer le schéma de la base de données • Utiliser l’analyseur de requêtes pour interroger la BD

14. Le langage SQL • En 1970, SQL (Standard Query Language, en français langage de requêtes structurées ) est né à la suite des travaux mathématiques de Codd (Directeur de recherche du centre IBM à San-José) , travaux qui ont fondé les bases de données relationnelles • Suite à ces recherches plusieurs langages sont apparus :•IBM Sequel (Structured English Query Language) en 1977 •IBM Sequel/2 •IBM System/R •IBM DB2 • En 1982, SQL fut lancé commercialement par IBM. SQL a subit 3 normalisations :•SQL1 en 1986 (version minimale)•SQL1 en 1989 (+ intégrité)•SQL2 en 1992•SQL3 en 1999 • Aujourd'hui SQL constitue le point d'entrée obligatoire sur une Base de Donnée relationnelle.

15. Utiliser l’ordre SELECT SELECT[DISTINCT] */liste d’attributs FROM nom(s) de table(s) WHERE condition de sélection GROUP BY nom de l’attribut de regroupement HAVING condition de regroupement ORDER BY critère(s) de tri [ UNION | INTERSECT | MINUS SELECT ….. ]

16. DISTINCT • Le résultat d’un SELECT étant un ensemble, il peut y avoir des doublons • Le mot clé DISTINCT permet de préciser que l’on ne veut qu’un seul exemplaire de ces enregistrements • Remarque : le résultat est une liste triée • Exemple : liste des prénom des stagiaires SELECT prénom FROM stagiaire SELECT DISTINCT prénom FROM stagiaire

17. Opérateurs Arithmétiques • Vous pouvez utiliser les opérateurs arithmétiques * / + - pour effectuer des calculs • Priorité des opérateurs : * et / sont prioritaires sur + et – • Vous pouvez utiliser les parenthèses • Exemple : SELECT nom, 2*nb FROM stagiaire

18. Les Valeurs NULL • Une valeur NULL est une valeur non disponible, non affectée, inconnue ou inapplicable. Une valeur NULL est différente du zéro ou de l'espace. Le zéro est un chiffre et l'espace est un caractère • Exemple : SELECT * FROM stagiaire WHERE nb IS NULL

19. Alias de Colonne • Renomme l’en-tête de colonne • Est utile dans les calculs • Suit immédiatement le nom de la colonne avec le mot-clé AS • Doit obligatoirement être inclus entre guillemets s’il contient des espaces, des caractères spéciaux ou si il faut tenir compte des majuscules/minuscules • Exemple : SELECT nom, 2*nb AS note FROM stagiaire

20. Opérateur de Concaténation • L'opérateur de concaténation (+) permet de concaténer des colonnes à d'autres colonnes, à des expressions arithmétiques ou à des valeurs constantes • Exemple : SELECT nom + ’ ‘ + prénom AS NP FROM stagiaire

21. TP02 : Utiliser SELECT (20mn) Écrire les requêtes qui permettent : • Afficher la liste de tous les stagiaires • Afficher la liste des villes de naissance sans doublons • Afficher une liste qui comprend deux colonnes : une pour le nom et prénom concaténés et une autre pour l’adresse • Afficher la liste des lieux et dates de naissance en définissant des aléas pour ces deux colonnes

22. Opérateur = > >= < <= <> Signification Egal à Supérieur à Supérieur ou égal à Inférieur à Inférieur ou égal à Différent de la Clause WHERE • Dans la clause WHERE, les chaînes alphanumériques et les dates doivent être incluses entre simples quottes • Opérateurs de Comparaison

23. L'Opérateur BETWEEN • L'opérateur BETWEEN permet d'afficher des lignes en fonction d'un intervalle de valeurs • Vous spécifiez un intervalle comprenant une limite inférieure et une limite supérieure • Exemple : SELECT * FROM stagiaire WHERE nb BETWEEN 12 AND 13

24. Opérateur IN • Permet de comparer une expression avec une liste de valeurs, utilisez l'opérateur IN • Opérateur d’appartenance à un ensemble de valeurs • Exemple : SELECT * FROM stagiaire WHERE ln IN (‘taza’,’sefrou ‘,’rabat’)

25. Opérateur LIKE • L'opérateur LIKE permet d’effectuer une comparaison partielle • Il est surtout employé avec les colonnes contenant des données de type alphanumériques • Il utilise les jokers % et _ (‘pour cent’ et ‘blanc souligné’). Le joker % remplace n'importe quelle chaîne de caractères, y compris la chaîne vide. Le blanc souligné remplace un et un seul caractère. • Exemple : SELECT * FROM stagiaire WHERE nom LIKE ‘%BA%’

26. Opérateurs Logiques • Un opérateur logique combine le résultat de deux conditions pour produire un résultat unique ou inverse le résultat d'une condition unique. SQL inclut trois opérateurs logiques : AND Intersection OR Union NOT négation

27. L’opérateur AND = intersection sexe=‘M’ AND ln=‘Fes’

28. L’opérateur OR = Union sexe = ‘M’ OR ln=‘Fes’

29. Le tri ORDER BY • ORDER BY colonne1 | 1 [ASC ou DESC ] [, colonne2 | 2 [ASC ou DESC ] ... • La clause ORDER BY sert à trier les lignes • ASC (ascending) classe les lignes en ordre croissant. C'est l'ordre par défaut. • DESC (descending) classe les lignes en ordre décroissant. • Exemple : SELECT * FROM stagiaire ORDER BY sexe, nb DESC

30. La clause TOP • La clause TOP n permet d’afficher les n premières lignes de la requête • Exemple : SELECT TOP 5 * FROM stagiaires ORDER BY nb DESC

31. TP03 : la clause WHERE (30mn) Développer les requêtes suivantes : • Liste des stagiaires ayant une note du bac supérieure à 12 • Liste des stagiaires de sexe féminin puis celle des stagiaires masculins • Liste des stagiaires né à Taza puis ceux qui ne sont pas nés à Fes • Liste des stagiaires qui portent les prénoms : Fatima, Meryem ou Ahmed • Les stagiaires qui habitent le quartier Benslimane • Les stagiaires de sexe masculin ayant plus de 12 dans la note du Bac • Les stagiaires ayant une note entre 11 et 12 • Liste des stagiaires triée dans l’ordre décroissant des notes • Liste des stagiaires triée par sexe puis par nom

32. Fonctions mono ligne • Fonctions de traitement des chaînes de caractères • Fonctions numériques • Fonctions Date • Fonctions de Conversion de type

33. Fonctions de traitement des chaînes • Les fonctions scalaires suivantes effectuent une opération sur une valeur de type chaîne et renvoient une valeur numérique ou de type chaîne. • LEN : longueur d’une chaîne • LEFT RIGHT SUBSTRING : extraction • LOWER UPPER : majuscule/minuscule • LTRIM RTRIM : supprimer espaces • REVERSE : Renvoie l'inverse d'une expression de type caractère • DIFFERENCE : compare deux chaînes et renvoie une valeur de 0 à 4 (0 indique peu ou pas de similarité, 4 indique une forte similarité )

34. La fonction LEN • Renvoie le nombre de caractères d'une expression de chaîne, à l'exception des espaces de droite en fin de chaîne • Syntaxe LEN ( string_expression ) • Exemple : dans l'exemple ci-après, le nombre de caractères et les données figurant dans le nom du stagiaire sont sélectionnés SELECT LEN(nom), nom FROM Stagiaire

35. Les fonctions LEFT,RIGHT et SUBSTRING • Extraction d’une sous chaîne de caractères • Syntaxe : • LEFT ( chaîne ,longueur ) • RIGHT ( chaîne ,longueur ) • SUBSTRING ( chaîne ,début ,longueur )

36. Les fonction UPPER et LOWER • Conversion majuscule (UPPER) et minuscule (LOWER) d’une chaîne de caractères alphabétiques • Syntaxe : • UPPER ( chaîne ) • LOWER ( chaîne )

37. Les fonction LTRIM et RTRIM • Suppression des espaces à gauche (LTRIM) ou à droite (RTRIM) d’une chaîne de caractères • Syntaxe : • LTRIM ( chaîne ) • RTRIM ( chaîne )

38. TP05 : Traitement des chaînes (20mn) Développer les requêtes suivantes • Liste des noms et longueurs des noms des stagiaires. Puis trier la liste dans l’ordre décroissant des longueurs des noms. • Afficher une liste contenant les cinq premiers caractères du nom. Puis une autre liste avec les cinq derniers caractères du nom. • Liste des adresses en majuscule avec les lieux de naissance en minuscule. • Liste des adresses inversées • Afficher tous les prénoms similaires à « FATM »

39. Fonctions numériques • Les fonctions numériques utilisent et ramènent des valeurs numériques. • ROUND : calculer l’arrondi • CEILLING : trouver l’entier immédiatement supérieur ou égal • FLOOR : valeur entière

40. Fonctions Date • Date = Année, mois, jour, heure, minute, seconde, milliseconde • Les fonctions scalaires suivantes effectuent une opération sur une valeur d'entrée de type date et heure et renvoient une valeur numérique, une valeur de type date ou heure, ou une chaîne.

41. La fonction DATEADD • Renvoie une nouvelle valeur datetime calculée en ajoutant un intervalle à la date spécifiée. • Syntaxe DATEADD (intervalle ,nombre,date )

42. La fonction DATEDIFF • DATEDIFF : renvoie le nombre de limites de date et d'heure traversées entre deux dates données. • Syntaxe DATEDIFF ( intervalle ,dateDebut ,dateFin ) • Exemple : calcul de l’age des stagiaires SELECT DATEDIFF(year, dn, getdate()) FROM stagiaire

43. La fonction DATEPART • Renvoie un entier représentant l'élément de date précisé dans la date spécifiée. • Syntaxe : DATEPART ( intervalle ,date ) • Exemple : SELECT DATEPART(month, dn) FROM stagiaire

44. DAY, MONTH et YEAR • Ces fonctions renvoient respectivement le jour, mois et année d’une date donnée • Syntaxes : • DAY ( date ) • MONTH ( date ) • YEAR (date)

45. TP06: gestion des dates (30mn) Développer les requêtes suivantes : • Liste des nom et année de naissance des stagiaires • Liste des noms et ages des stagiaires • Liste des stagiaires nés un Jeudi • Liste des stagiaires nés au mois de Juillet • Les noms et prénoms des stagiaires nés entre 15/3/1985 et le 14/3/1987

46. Fonctions de conversion de type • STR Convertit un nombre en chaîne de caractère • ASCII Renvoie le code ASCII du caractère placé à l'extrême gauche d'une expression de caractères • CHAR : convertit un code ASCII int en un caractère • CAST et CONVERT : Convertit explicitement une expression d'un type de données en une expression d'un type de données différent. CAST et CONVERT offrent la même fonctionnalité.

47. Interroger plusieurs tables • Le produit cartésien • L’équi-jointure • L’auto-jointure • Les jointures externes • Opérations ensemblistes

48. Le produit cartésien • Le produit de deux tables permet d’obtenir toutes les combinaisons de lignes de ces deux tables SELECT * FROM stagiaire, groupe X =

49. L’equi-jointure • On appelle équi-jointure une jointure dont la qualification est une égalité entre deux colonnes. Select * FROM stagiaire, groupe WHERE stagiaire.codeg = groupe.codeg Select * FROM stagiaire INNER JOIN groupe ON stagiaire.codeg = groupe.codeg = = = ≠ ≠ ≠

50. Alias de table • Il est possible d’utiliser des aléas des tables utilisé dans une jointure pour simplifier l’écriture de la requête SQL • Exemple : SELECT nom, nomg FROM stagiaire s, groupe g WHERE s.codeg = g.codeg