SYSTÈME DE GESTION DE BASES DE DONNÉES

Animateur : M. HAIJOUBI

2

SOMMAIRE

1. Traduire les opérations de l’algèbre Relationnelle en requêtes SQL.

2. Exploiter l'environnement du SGBDR pour interroger une base de données.

3. Exploiter les commandes de description de données.

4. Exploiter les commandes de manipulation des données.

5. Gérer des transactions.6. Utiliser les différents types d’objets sur une base de

données.7. Sécuriser les données.

3

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 stagiairesSelect nom,prénom from stagiaire

nom prénom ln dn

Amrani Med Sefrou 30/12/1987

Saadi Maha Fes 12/10/1988

Ayachi Rachida Fes 11/08/1986

SELECT nom, prénomFROM stagiaire

nom prénom

Amrani Med

Saadi Maha

Ayachi Rachida

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’

nom prénom ln dn

Amrani Med Sefrou 30/12/1987

Saadi Maha Fes 12/10/1988

Ayachi Rachida Fes 11/08/1986

SELECT nom, prénomFROM stagiaire Where ln=‘Fes’

nom prénom ln dn

Saadi Maha Fes 12/10/1988

Ayachi Rachida Fes 11/08/1986

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

nom prénom ln dn codeg

Amrani Med Sefrou 30/12/1987 TDI1B

Saadi Maha Fes 12/10/1988 TDI1A

Ayachi Rachida Fes 11/08/1986 TDI1C

codeg nomg

TDI1A Groupe A

TDI1B Groupe B

TDI1C Groupe C

SELECT * FROM stagiaire,groupe WHERE stagiaire.codeg = groupe.codeg

nom prénom ln dn codeg codeg nomg

Amrani Med Sefrou 30/12/1987 TDI1B TDI1B Groupe B

Saadi Maha Fes 12/10/1988 TDI1A TDI1A Groupe A

Ayachi Rachida Fes 11/08/1986 TDI1C TDI1C Groupe C

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

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

Opérateur=>

>=<

<=<>

SignificationEgal àSupérieur à Supérieur ou égal à Inférieur à Inférieur ou égal àDifférent de

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’ ANDAND ln=‘Fes’

28

L’opérateur OR = Union

sexe = ‘M’ OROR 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ériquesFonctions DateFonctions 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

SyntaxeLEN ( 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ésSELECT 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.

Quelques fonctions

DATEADD

DATEDIFF

DATEPART

DAY

MONTH

YEAR

41

La fonction DATEADD

Renvoie une nouvelle valeur datetime calculée en ajoutant un intervalle à la date spécifiée.

SyntaxeDATEADD ( intervalle ,

nombre, date )

Intervalle Abréviations

year yy, yyyy

quart qq, q

month mm, m

dayofyear dy, y

day dd, d

week wk, ww

weekday dw

hour hh

minute min

second ss, s

millisecond ms

42

La fonction DATEDIFF

DATEDIFF : renvoie le nombre de limites de date et d'heure traversées entre deux dates données.

SyntaxeDATEDIFF ( intervalle , dateDebut , dateFin )

Exemple : calcul de l’age des stagiairesSELECT 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ésienL’équi-jointureL’auto-jointureLes jointures externesOpérations ensemblistes

48

Le produit cartésien Le produit de deux tables permet d’obtenir toutes les combinaisons de lignes de ces deux

tablesSELECT * FROM stagiaire, groupe

cin nom codeg

B2501 Ali tdi1a

C2103 Laila tdi1a

D1254 Said tdi1a

codeg nomg

tdi1a Groupe A

tdi1b Groupe B

cin nom codeg codeg nomg

B2501 Ali tdi1a tdi1a Groupe A

C2103 Laila tdi1a tdi1a Groupe A

D1254 Said tdi1a tdi1a Groupe A

B2501 Ali tdi1a tdi1b Groupe B

C2103 Laila tdi1a tdi1b Groupe B

D1254 Said tdi1a tdi1b Groupe B

XX

==

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

cin nom codeg codeg nomg

B2501 Ali tdi1a tdi1a Groupe A

C2103 Laila tdi1a tdi1a Groupe A

D1254 Said tdi1a tdi1a Groupe A

B2501 Ali tdi1a tdi1b Groupe B

C2103 Laila tdi1a tdi1b Groupe B

D1254 Said tdi1a tdi1b Groupe B

===≠≠≠

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

51

L’auto-jointure

C'est une jointure d'une table avec elle-même Utile lorsqu'on souhaite relier des attributs qui

se trouvent à l'intérieur d'une même table

Exemple : afficher les couples de stagiaires (noms) nés à la même villeSELECT s1.nom, s2.nomFROM stagiaire s1, stagiaire s2WHERE s1.ln = s2.lnAND s1.cin<>s2.cin

52

Les jointures externes

Une equi-jointure ne renvoie des lignes que si la colonne de jointure d'une table est égale à la colonne de jointure de l'autre table

Il peut être utile d'afficher toutes les lignes d'une table qu'il y ait ou non correspondance avec l'autre table de jointure

Les colonnes pour lesquelles il n'y a pas de correspondance sont remplies avec la valeur NULL

Cette opération s'appelle une jointure externe Il existe deux type de jointures externes : jointure externe

gauche et jointure externe droite Notation

Jointure externe gauche *= ou LEFT JOIN

Jointure externe droite =* ou RIGHT JOIN

53

TP07 : les jointures (30mn)

Commencer par ajouter les tables suivantes à la BD : Module( codem, nomm) Resultat( cin, codem, controle,efm)

Mettre à jour le diagramme de la BD Remplir la table des modules et ajouter quelques

enregistrements dans la table des résultats Télécharger et attacher la BD efp2 puis développer

les requêtes suivantes : Produit cartésien des tables stagiaire et module Liste des noms et notes des stagiaires Modifier la requête précédente pour afficher même les

stagiaires qui n’ont pas de note

54

Opérations ensemblistes

L’opérateur UNIONL’opérateur INTERSECTL’opérateur MINUS

55

L’opérateur UNION

Exemple : on souhaite créer une liste comprenant les 10 meilleurs stagiaires par rapport à la note du bac avec une répartition équitable par sexe (5 garçons et 5 filles)(SELECT top 5 *

FROM stagiaire WHERE sexe=‘F’

ORDER BY nb DESC)

UNION

(SELECT top 5 *

FROM stagiaire WHERE sexe=‘M’

ORDER BY nb DESC)

56

L’ opérateur INTERSECT

Exemple : on souhaite obtenir les noms des stagiaires de sexe masculin ayant une note du bac supérieure à 12

Solution ensembliste(SELECT nom FROM stagiaire WHERE sexe=‘M’)

INTERSECT(SELECT nom FROM stagiaire WHERE nb>12)

Solution prédicativeSELECT nom FROM stagiaire WHERE sexe=‘M’ AND nb>12

57

L’opérateur MINUS

On voudrait obtenir les noms des stagiaires féminins qui ne sont pas nés à FES

Solution ensemblisteSELECT nom FROM stagiaireWERE sexe=’F’

MINUSSELECT nom FROM stagiaireWHERE ln=‘FES’

Solution prédicativeSELECT nom FROM stagiaireWHERE sexe=‘F’ and ln<>’FES’

58

Donner les versions ensemblistes et prédicatives des requêtes suivantes : Noms des stagiaires masculins ayant moins

de 20 ans Noms des stagiaires du groupe TSDI2A qui ne

sont pas né à TAZA,SEFROU ou TAOUNATE Une liste contenant 3 stagiaires né à FES et 3

né à SEFROU ayant obtenu la meilleurs note au bac

TP08 : opérateurs ensemblistes (20mn)

59

Regrouper les lignes d’une table

Il peut être intéressant de regrouper des lignes afin de faire des opérations par groupe (opérations statistiques par exemple)

Cette opération se réalise à l'aide de la clause GROUP BY, suivie du nom de chaque colonne sur laquelle on veut effectuer des regroupements.

60

Fonctions d’agrégation

FONCTION D’AGREGATION DESCRIPTION

AVG([DISTINCT|ALL]n) Valeur moyenne de n, en ignorant les valeurs NULL (AVERAGE)

COUNT({*|[DISTINCT|ALL]expr}) Nombre de lignes, où expr est différent de NULL. Le caractère * comptabilise toutes les lignes sélectionnées y compris les doublons et les lignes NULL

MAX([DISTINCT|ALL]expr) Valeur maximale de expr, en ignorant les valeurs NULL

MIN([DISTINCT|ALL]expr) Valeur minimale de expr, en ignorant les valeurs NULL

SUM([DISTINCT|ALL]n) Somme des valeurs de n, en ignorant les valeurs NULL

61

Clause GROUP BY

Cette clause permet de créer des sous-ensembles de lignes pour lesquels la valeur d'une (ou plusieurs) colonne est identique

Exemple : calculer la moyenne des notes du bac pour chaque groupe

SELECT codeg, avg(nb)

FROM stagiaire

GROUP BY codeg

62

La clause HAVING

Cette clause, contrairement à la clause WHERE qui précise les conditions à appliquer sur les lignes d'une table, permet de préciser des conditions au niveau des sous-ensembles créés par GROUP BY

Exemple : reprendre la requête précédente mais afficher uniquement les ligne dont la moyenne est supérieur à 12.5

SELECT codeg, avg(nb) FROM stagiaireGROUP BY codegHAVING avg(nb) > 12.5

63

Règle de regroupement

Toutes les colonnes qui apparaissent après la clause SELECT, sujette à un regroupement, doivent :

Soit faire partie d’une fonction d’agrégation

Soit apparaître dans la clause GROUP BY

64

Développer les requêtes suivantes : Moyenne des notes de contrôle par module d’abord en

affichant le codem et la moyenne, ensuite en affichant le nomm et la moyenne

Nombre total de stagiaires Nombre de stagiaires de chaque groupe Note maximale et note minimale pour chaque module Moyenne de tous les contrôles par stagiaire. Afficher

seulement ceux dont la moyenne est supérieur à 11.75 Moyenne des efm des stagiaires de sexe féminin Combien y’a-t-il de lieux de naissance distincts Combien y’a-t-il de prénoms différents par groupe

TP09 : regrouper des données (30mn)

65

Sous requêtes

Une sous requête est une requête SELECT qui renvoie une valeur unique et est imbriquée dans une autre instruction SELECT

Les instructions contenant une sous-requête se présentent généralement sous une des formes suivantes : WHERE expression [NOT] IN (sous requête) WHERE expression opérateur_comparaison [ANY |

ALL] (sous requête) WHERE [NOT] EXISTS (sous requête)

66

Sous requêtes et l’opérateur IN

Exemple : liste des stagiaires n’ayant pas de note au module 3 :

SELECT nom, prenom

FROM stagiaire

WHERE cin NOT IN (SELECT cin FROM resultat

WHERE codem=3)

67

Sous requêtes et l’opérateur ALL/ANY Exemple : le stagiaire ayant la plus hausse

note du bac :

SELECT nom, prenom,nb

FROM stagiaire

WHERE nb > ALL

(SELECT nb FROM stagiaire)

68

Sous requêtes et l’opérateur EXISTS

Exemple : liste des modules pour lesquels il existe au moins une note attribuée à un stagiaire :

SELECT * FROM module

WHERE EXISTS

(SELECT * FROM resultat

WHERE codem=module.codem)

69

TP10 sous requêtes (20mn)

Développer les requêtes suivantes : Liste des stagiaires n’ayant pas de note pour

le module 2 Liste des stagiaires ayant eu la meilleur note

au module 3 Liste des stagiaires ayant eu la dernière note

au module 3

70

71

2. Exploiter l'environnement du SGBDR pour interroger une BD L'Analyseur de requêtes SQL permet : créer des requêtes et autres scripts SQL procéder au débogage en cas de problème de

performances des requêtes (Afficher le plan d'exécution, Afficher la trace du serveur, Afficher les statistiques du client, Assistant Paramétrage d'index)

localiser des objets dans les bases de données insérer, mettre à jour ou supprimer rapidement des

lignes dans une table (fenêtre Ouvrir une table) Vous pouvez exécuter l'Analyseur de requêtes SQL

directement à partir du menu Démarrer ou de SQL Server Enterprise Manager. Une autre possibilité consiste à lancer l'application à partir de l'invite de commandes, en exécutant l'utilitaire isqlw.

72

73

3. Exploiter les commandes de description de données Principaux objets d'une base de données Créer une base de données Créer une table Types de données des colonnes Modifier la définition d’une table Supprimer une table

74

Principaux objets d'une base de donnéesObjet Description

Table Unité de stockage élémentaire, composée de lignes et de colonnes

Vue Représente de manière logique des sous-groupes de données

Index Améliore les performances de certaines requêtes

75

Créer une base de données

CREATE DATABASE efp Crée une bd nommée efp

ON

( NAME = ‘efp_data’, Nom logique du fichier de données

FILENAME = 'd:\demo\efp_dat.mdf', Nom physique sur le disque

SIZE = 10, MAXSIZE = 50, Taille initiale et taille maximale

FILEGROWTH = 5 ) Croissance du fichier de données

LOG ON

( NAME = ‘efp_log', Nom logique du jurnal

FILENAME = 'd:\demo\efp_log.ldf', Nom physique sur le disque

SIZE = 5MB, MAXSIZE = 25MB, Taille initiale et taille maximale

FILEGROWTH = 5MB ) Croissance du fichier journal

76

Créer une table

CREATE TABLE nomtable

(colonne1 definitionType, colonne2 definitionType, ….)

Cet ordre fait partie d'une série d'ordres appartenant au langage de définition des données (LDD) qui sera étudié dans les prochains chapitres.

Exemple :CREATE TABLE resultat

(cin varchar(12), codem int, controle float, efm float,

Moyenne AS (controle + efm)/3 )

77

L’option DEFAULT

On peut déclarer une valeur par défaut pour une colonne en utilisant l'option DEFAULT

Cette option empêche l'insertion de valeurs

NULL dans une colonne lors de l'ajout d'une ligne

78

Types de données des colonnes

Type de données Syntaxe Description

Alphanumérique CHAR(n) Chaîne de caractères de longueur fixe n (n<16383)

VARCHAR(n) Chaîne de caractères de n caractères maximum (n<16383)

Numérique NUMBER(n [,d]) Nombre de n chiffres [optionnellement d après la virgule]

SMALLINT Entier signé de 16 bits (-32768 à 32767)

INT Entier signé de 32 bits (-2E31 à 2E31-1)

FLOAT Nombre à virgule flottante

Horaire DATE Date sous la forme 16/07/99

TIME Heure sous la forme 12:54:24.85

TIMESTAMP Date et Heure

79

Modifier la définition d’une table

Il est possible modifier la structure d’une table pour ajouter, par exemple, une colonne oubliée ou changer une définition de colonne existante

Cela est possible grâce à la commande ALTER TABLE

Vous pouvez ajouter des colonnes à une table en utilisant l'ordre ALTER TABLE avec la clause ADD

Exemple :ALTER TABLE resultat

ADD appreciation VARCHAR(60)

80

Supprimer une table

Pour supprimer une table, tapez la commande :

DROP TABLE <nom de la table>

Exemple : suppression de la table des résultats:

DROP TABLE resultat

81

TP11 Description des données (1h)

En utilisant Enterprise manager, Créer la base de données test puis, avec l’analyseur de requêtes :

Créer la table « groupe » Créer la table « stagiaire » Créer la table « resultat » Créer la table « module »

82

Contraintes

NOT NULL PRIMARY KEY FOREIGN KEY UNIQUE CHECK

83

NOT NULL

La contrainte NOT NULL interdit la présence de valeurs NULL dans la colonne à laquelle elle s'applique

Par défaut, les colonnes peuvent contenir des valeurs NULL

Exemple :CREATE TABLE groupe

(codeg int, nomg varchar(30) NOT NULL)

84

PRIMARY KEY

Une contrainte PRIMARY KEY crée une clé primaire pour la table

La contrainte PRIMARY KEY est une colonne ou un ensemble de colonnes qui identifie de manière unique chaque ligne d'une table

garantit qu'aucune colonne faisant partie de la clé primaire ne contient de valeur NULL

Exemple :CREATE TABLE groupe

(codeg varchar(6), nomg varchar(30) NOT NULL, CONSTRAINT pk_groupe PRIMARY KEY(codeg) )

85

FOREIGN KEY La contrainte FOREIGN KEY, ou contrainte d'intégrité

référentielle, désigne une colonne ou une combinaison de colonnes comme étant une clé étrangère

établit une relation avec une clé primaire

Exemple :CREATE TABLE stagiaire

(cin varchar(12), nom varchar(30), … ,codeg varchar(6),CONSTRAINT pk_stagiaire PRIMARY KEY (cin),CONSTRAINT fk_groupe FOREIGN KEY (codeg)

REFERENCES groupe(codeg)ON DELETE NO ACTION ON UPDATE CASCADE

)

86

UNIQUE

Une contrainte d'intégrité de type clé UNIQUE exige que chaque valeur dans une colonne ou dans un ensemble de colonnes (la clé) soit unique

Exemple :CREATE TABLE stagiaire

(cin varchar(12), ni int, nom varchar(30), … ,codeg varchar(6),CONSTRAINT pk_stagiaire PRIMARY KEY (cin),CONSTRAINT fk_groupe FOREIGN KEY (codeg) REFERENCES groupe(codeg),CONSTRAINT uk_ni UNIQUE (ni) )

87

CHECK

La contrainte CHECK définit une condition que chaque ligne doit obligatoirement satisfaire

Exemple :CREATE TABLE stagiaire

(cin varchar(12), nom varchar(30),

sexe CHAR(1),… ,

CHECK (sexe=‘F’ or sexe=‘M’) )

88

Suppression d’un contrainte

Utilisez l'ordre ALTER TABLE avec la clause DROP

L'option CASCADE provoque également la suppression de toutes les contraintes associées

Exemple :ALTER TABLE stagiaire

DROP CONSTRAINT fk_groupe

89

TP12 contraintes (1h)

Ajouter aux tables de la base de données test les éléments suivants :

Clés primaires Clés étrangères avec mise à jour en cascade Les notes doivent être comprises entre 0 et

20 Le sexe doit être ‘F’ ou ‘M’ avec ‘F’ comme

valeur par défaut Les notes ne doivent pas accepter NULL

90

91

4. Exploiter les commandes de manipulation des données Ajout d’une ligne Ajout de plusieurs lignes avec SELECT Insertion de valeurs NULL Modification de ligne Suppression d’une ligne

92

Ajout d’une ligne

Pour ajouter de nouvelles lignes dans une table, utilisez l'ordre INSERT

Syntaxe : INSERT INTO table [(colonne1, colonne2 … )]

Values (valeur1, valeur2, … )

Exemple :

INSERT INTO groupe (codeg, nomg)

VALUES (‘TDI1A’, ‘Techniques Dév Info 1 groupe A’)

93

Ajout de plusieurs lignes avec SELECT Il est possible d’insérer le résultat d’une

requête SELECT dans une table

Exemple : archivage de la liste des stagiairesINSERT INTO archive_stagiaire

SELECT * FROM stagiaire

94

Insertion de valeurs NULL

Si une colonne n'est pas explicitement spécifiée dans la liste, elle reçoit automatiquement une valeur NULL

Méthode implicite : ajout d’un groupe sans fournir de valeur pour le nom du groupe :

INSERT INTO groupe (codeg)

VALUES (‘TDI1A’)

Méthode explicite : spécifiez le mot-clé NULL INSERT INTO groupe (codeg, nomg)

VALUES (‘TDI1A’, NULL)

95

Modification de ligne

Vous pouvez modifier des lignes existantes au moyen de l'ordre UPDATE

Syntaxe :UPDATE tableSET colonne1=valeur1, colonne2=valeur2 …[WHERE condition]

Exemple :UPDATE stagiaire SET adresse =’19, hay saada 2 Fès’WHERE cin = ‘c150’

UPDATE resultat SET note = note + 1WHERE codem=1

96

Suppression d’une ligne

Vous pouvez supprimer des lignes existantes au moyen de l'ordre DELETE

Syntaxe :DELETE [FROM] tableWHERE condition

Exemple :DELETE resultat WHERE note is null

97

TP13 manipulation des données (30mn)

Ajouter un groupe à la table groupe Code : ‘TDI2A’ nom : ‘Techniques dév. Info 2 A’

Ajouter un stagiaire du groupe TDI2A à la table stagiaire

Supprimer le groupe TDI2A. Que constatez vous et pourquoi ?

Supprimer les stagiaires qui ont une note du bac inférieure à 11. Que constatez vous et pourquoi ?

Tous les stagiaires de première année sont admis en deuxième année, écrire la requête qui permet de modifier leur groupe

98

99

5. Gérer des transactions

Qu’est ce qu’une transaction Démarrage des transactions Fin des transactions BEGIN TRANSACTION COMMIT TRANSACTION ROLLBACK TRANSACTION

100

Qu’est ce qu’une transaction

Les transactions offrent un mécanisme de regroupement d'une série de modifications de base de données dans une opération logique

Une fois les modifications apportées à la base de données, elles peuvent être validées ou annulées comme une seule unité

101

Démarrage des transactions

Vous pouvez lancer des transactions explicites, implicites ou en mode de validation automatique

Transactions explicitesTransactions explicites : Pour démarrer une transaction explicite, utilisez l'instruction BEGIN TRANSACTION.

Transactions en mode de validation automatiqueTransactions en mode de validation automatique : C'est le mode par défaut de SQL Server. Chaque instruction est validée lorsqu'elle se termine. Il est inutile de spécifier des instructions de contrôle des transactions.

Transactions implicitesTransactions implicites : Vous pouvez activer le mode de transaction implicite en utilisant l'instruction SET IMPLICIT_TRANSACTIONS ON. L'instruction suivante démarre automatiquement une nouvelle transaction. La transaction est active jusqu'à l'exécution d'une instruction COMMIT ou

ROLLBACK

102

Fin des transactions

Vous pouvez terminer une transaction par une instruction COMMIT ou ROLLBACK.

COMMITCOMMIT : garantit que toutes les modifications effectuées sur la base de données au cours de la transaction sont permanentes

ROLLBACKROLLBACK : annule toutes les modifications effectuées par la transaction en rétablissant les données dans l'état où elles étaient avant le début de celle-ci

103

BEGIN TRANSACTION

Indique le début d'une transaction locale explicite

Syntaxe :

BEGIN TRANSACTION

104

COMMIT TRANSACTION

Marque la fin d'une transaction réussie implicite ou définie par l'utilisateur

Syntaxe :COMMIT TRANSACTION

105

ROLLBACK TRANSACTION

Annule une transaction implicite ou explicite jusqu'au début de la transaction ou jusqu'au dernier point d'enregistrement à l'intérieur de la transaction

Syntaxe :ROLLBACK TRANSACTION

106

TP14 : les transactions

La table compte comprend les champs suivants : numéro, nom et solde

Simuler une panne du serveur après l’exécution d’une partie d’une transaction. Cette transaction comprend deux commandes update qui permettent d’effectuer un virement de 1000DH du compte n°100 vers le compte n°200Begin transactionUpdate compte set solde = solde -1000 where numero = 100(panne du serveur = arrêter puis redémarrer le serveur)Update compte set solde = solde -1000 where numero = 200Commit transaction

Que constatez vous?

107

108

6. Utiliser les différents types d’objets sur une base de données Les vues Les index

109

Les vues

Une vue est une table virtuelle dont le contenu est défini par une requête

Une vue ressemble à une table réelle, avec un ensemble de colonnes nommées et de lignes de données

Une vue n'existe pas en tant qu'ensemble de valeurs de données stocké dans une base de données

Les lignes et les colonnes de données proviennent de tables référencées dans la requête qui définit la vue et sont produites dynamiquement lorsque la vue est utilisée

110

Avantages des vues

Limiter l'accès à la base de données en affichant une sélection de celle-ci

Simplifier la consultation des données. Une vue peut être utilisée pour extraire les données de plusieurs tables.

Permet à des groupes d'utilisateurs d'accéder aux données en fonction de leurs critères particuliers

111

Création d’une vue

Vous pouvez créer une vue en utilisant l'ordre CREATE VIEW suivi du nom de la vue, du mot AS et d’une requête SQL

Exemple :CREATE VIEW filles ASSELECT * FROM stagiaire WHERE sexe=‘F’

CREATE VIEW garcons ASSELECT * FROM stagiaire WHERE sexe=‘M’

112

Suppression d’une vue

DROP VIEW supprime une vue dans la base de données courante

Exemple : DROP VIEW garcons

113

Les index

Les index des bases de données sont similaires aux index que l'on trouve dans les livres

L'index d'un livre vous permet de rechercher rapidement des informations sans lire la totalité de l'ouvrage

Celui d'une base de données permet de rechercher des données dans une table sans analyser la table tout entière

L'index d'un livre est une liste de mots accompagnés du numéro de page où ils se trouvent

L'index d'une base de données est une liste des valeurs d'une colonne accompagnées des emplacements de stockage des lignes où les valeurs se trouvent

Un index peut être créé dans une table, soit sur une seule colonne, soit sur un ensemble de colonnes

114

Index ordonné et index unique

Lorsque vous créez un index ordonné en clusters, la table est copiée, les données de la table sont triées et la table initiale est supprimée

La création d'un index unique garantit l'échec de toute tentative d'entrer des valeurs de clé en double

115

Création d’un index

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX nom_index ON { table | vue } ( colonne [ ASC | DESC ] [ ,...n ] )

Exemple :

CREATE UNIQUE INDEX idx_inscription

ON stagiaire (nins)

CREATE INDEX idx_nom

ON stagiaire (nom)

116

TP15 vues et index (1h)

Créer des vues à partir des requêtes du TP09

Ajouter un index unique comprenant les deux colonnes nom et prénom des stagiaires

117

118

7. Sécuriser les données

SYSTEME D’EXPLOITATION

SYSTEME DE GESTION DE BASE DE DONNEES

BASE DE DONNEES

OBJETS DE LA BASE DE DONNEES

119

Modes d'authentification

SQL Server peut fonctionner selon deux modes de sécurité :

mode d'authentification Windows : Ce mode permet à l'utilisateur de se connecter par le biais d'un compte utilisateur Windows

mode mixte (authentification Windows et authentification SQL Server) : ce mode permet aux utilisateurs de se connecter à SQL Server à l'aide d’un compte Windows ou d’un compte propre à SQL Server

120

Rôles

Les rôles sont de puissants utilitaires permettant de réunir les utilisateurs en une seule unité à laquelle il est possible d'appliquer des autorisations

Les autorisations octroyées à un rôle, refusées ou révoquées s'appliquent aussi aux membres du rôle.

Vous pouvez créer un rôle représentant un poste rempli par une catégorie de collaborateurs, lui octroyer les autorisations appropriées.

Chaque fois qu'un collaborateur passe à un autre travail, il suffit de l'ajouter comme membre du rôle.

Il n'est pas nécessaire d'accorder, de refuser ou de révoquer les autorisations de chaque utilisateur à chaque fois qu'il accepte ou qu'il quitte un travail.

Elles sont automatiquement appliquées aux utilisateurs dès qu'ils deviennent membres du rôle

121

Rôle de serveur fixe

Rôle Description

sysadmin Exécute n'importe quelle opération dans SQL Server

serveradmin Configure les paramètres au niveau du serveur.

setupadmin Ajoute et supprime les serveurs liés et exécute certaines procédures système stockées telles que sp_serveroption.

securityadmin Gère les connexions au serveur.

processadmin Gère les traitements s'exécutant dans une instance de SQL Server.

dbcreator Crée et modifie les bases de données.

diskadmin Gère les fichiers sur disque.

bulkadmin Exécute l'instruction BULK INSERT.

122

Rôles de base de données fixes rôle utilisation

public par défaut

db_owner Propriétaire de la BD, possède toutes les autorisations sur la BD.

db_accessadmin gère les accès : ajoute ou supprime des utilisateur.

db_securityadmin gère les droits : accès, propriétés d'objet, rôles et membres des rôles

db_ddladmin Exécute les commandes de description de données (DDL)

db_backupoperator

opérateur de sauvegarde (mais pas de restauration !). Lance les instructions DBCC, CHECKPOINT et BACKUP.

db_datareaderdroit de consultation des données de la base (lecture uniquement). Sélectionne toutes les données de toutes les tables utilisateur dans la base de données.

db_datawriterdroit en lecture, écriture des données de la base. Modifie les données de toutes les tables utilisateur dans la base de données.

db_denydatareader révocation des droits en lecture sur tous les objets de la base

db_denydatawriter révocation des droits en écriture sur tous les objets de la base

123

Administrateur système (sa = system administrator) Compte sql server par défaut Il est affecté au rôle de serveur sysadmin et

ne peut pas être modifié Par défaut le compte sa possède un mot de

passe vide Assignez un mot de passe au compte sa pour

vous prémunir contre tout accès non autorisé

124

Propriétaire de la base de données (dbo, DataBase Owner) dbo est un utilisateur qui possède les autorisations

implicites nécessaires pour effectuer toutes les activités dans la base de données

L'utilisateur dbo ne peut pas être supprimé et est toujours présent dans toutes les bases de données

Si l'utilisateur rachid, membre du rôle de serveur sysadmin, crée une table T1, celle-ci appartiendra à dbo et sera désignée par dbo.T1, et non rachid.T1.

Si rachid est seulement membre du rôle de base de données db_owner, et qu'il crée une table T1, celle-ci appartiendra à rachid et sera désignée par rachid.T1

125

Autoriser un compte Windows à accéder à SQL server Cet exemple montre comment accorder à un

compte Windows l'accès à une base de données et comment associer la connexion à un utilisateur de la base de données : (sp = stored procedure procédure stockée)

USE master sp_grantlogin ‘domaine\said’sp_defaultdb @loginame = ‘said', defdb = ‘efp‘USE efpsp_grantdbaccess ‘said’, ‘said’

126

Ajout d'utilisateurs et de connexionsL'ajout d'un utilisateur affectés à des rôles prédéterminés se fait en deux temps :

1. création de la connexion 2. ajout d'un utilisateur et de son rôle

Les procédures stockées à utiliser sont les suivantes :

sp_addlogin 'connexion', 'mot_de_passe', 'base_cible‘sp_adduser 'connexion', 'utilisateur', 'rôle‘

il est nécessaire d'utiliser la base de données cible.

Exemple : création de l’utilisateur hamid dont le mot de passé kw7E.6J et possédant le rôle de lecteur uniquement :

exec sp_addlogin ‘hamid', 'kw7E.6J', ‘efp' sp_adduser ‘hamid', 'u_hamid', 'db_datareader'

127

Octroi de permission

Accordez des autorisations d'instruction et d'objet qui permettent à un compte utilisateur:

d'effectuer des opérations ou de travailler avec des données dans la base de données en cours

de les restreindre quant aux opérations et informations qui ne font pas partie de leur fonction

128

Ajout d'un membre à un rôle

sp_addrolemember ajoute un compte comme membre du rôle de base de données

Syntaxe :sp_addrolemember 'role' , ‘compte'

Exemple :

sp_addrolemember ‘comptable', ‘said'

129

GRANT Permet à un utilisateur de travailler avec les données de la base

ou d'exécuter des instructions particulières. Autorisations sur une instruction :

GRANT { ALL | instruction [ ,...n ] } TO compte [ ,...n ]

ALL : Accorde toutes les autorisations Instruction : Nom de l'instruction pour laquelle l'autorisation

est accordée. La liste des instructions peut inclure : CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, BACKUP DATABASE, BACKUP LOG

Exemples : GRANT ALL to said GRANT CREATE VIEW to rachid

130

GRANT

Autorisations sur un objet :GRANT

    { ALL [ PRIVILEGES ] | permission [ ,...n ] }     {         [ ( colnne [ ,...n ] ) ] ON { table | vue         | ON { table | vue } [ ( colonne [ ,...n ] ) ]     } TO compte [ ,...n ]

Exemples : GRANT SELECT ON module TO public GRANT INSERT, UPDATE, DELETE ON stagiaire TO

fatima, said, ali

131

Refus d'autorisation

supprime une autorisation précédemment accordée à l'utilisateur ou au rôle

désactive une autorisation héritée d'un ou de plusieurs autres rôles

vous assure qu'un utilisateur ou un rôle n'héritera pas à l'avenir d'une autorisation

132

DENY Autorisations sur une instruction :

DENY { ALL | instruction [ ,...n ] } TO compte [ ,...n ]

Autorisations sur un objet :DENY     { ALL [ PRIVILEGES ] | permission [ ,...n ] }     {         [ ( colonne [ ,...n ] ) ] ON { table | vue         | ON { table | vue } [ ( colonne [ ,...n ] ) ]     } TO compte [ ,...n ] [ CASCADE ]

Exemple :GRANT SELECT ON stagiaire TO publicDENY SELECT, INSERT, UPDATE, DELETE ON stagiaire TO laila, driss

133

Retrait d'autorisation

Vous pouvez retirer une autorisation précédemment accordée ou refusée

Le retrait d'une autorisation est similaire à un refus dans la mesure où les deux actions suppriment une autorisation accordée au même niveau

Cependant, cela n'empêche pas l'utilisateur, le groupe ou le rôle d'hériter d'une autorisation accordée à un niveau supérieur.

Par conséquent, si vous retirez l'autorisation d'affichage d'une table accordée à un utilisateur, il est possible que celui-ci puisse encore l'afficher parce que l'autorisation avait été accordée à un rôle auquel il appartient

134

REVOKE Autorisations sur une instruction :

REVOKE { ALL | instruction [ ,...n ] } FROM compte [ ,...n ]

Autorisations sur un objet :REVOKE     { ALL [ PRIVILEGES ] | permission [ ,...n ] }     {         [ ( colonne [ ,...n ] ) ] ON { table | vue         | ON { table | vue } [ ( colonne [ ,...n ] ) ]     } FROM compte [ ,...n ] [ CASCADE ]

Exemple :REVOKE CREATE TABLE FROM aliREVOKE SELECT ON stagiaire FROM meryem

135

TP16 : sécurité

Changer la sécurité du serveur et passer à la sécurité mixte Créer les comptes et les rôles suivants :

Aziz et Driss : directeurs Rachida, said et mourad : surveillance générale Mounia, fatima, khalid : formateurs

Accorder au rôle directeur tous les droits sur la base de données efp

Accorder au rôle surveillant le droit de lecture des tables groupe et stagiaire

Accorder au rôle formateur le droit de lecture sur toutes les tables et le droit d’écriture sur la table resultat

Retirer à l’utilisateur mourad le droit de lecture de la colonne adresse de la stagiaire

Autoriser said à modifier l’adresse