13/02/2020

1

Transact SQL Débutant et perfectionnement

1

Langage SQL Participants et pré requis

� Participants

� Chargé de reporting ou d'analyse, assistant(e), toutepersonne ayant des besoins d'interrogation ou demises à jour simples d'une base de données avec lelangage SQL.

� Prérequis

� Aucune connaissance particulière.

� Formation commune à toutes les bases relationnelles(Oracle, SQL Server, DB2, PostGreSQL, MySQL,Access, SQL Lite...).

2

1

2

13/02/2020

2

Langage SQL La Formatrice

Laure BERENGUER, consultante et formatrice indépendante

berenguer.laure@laposte.net

http://www.linkedin.com/in/laure-berenguer38/

3

Langage SQL Objectif de la formation

� Comprendre le principe et le contenu d'une base dedonnées relationnelle

� Créer des requêtes pour extraire des donnéessuivant différents critères

� Utiliser des calculs simples et des agrégations dedonnées

� Réaliser des requêtes avec des jointures, pourrestituer les informations de plusieurs tables

� Combiner les résultats de plusieurs requêtes

4

3

4

13/02/2020

3

Langage SQL Le Sommaire

� Introduction aux bases de données Page 6

� Extraire les données d'une table Page 14

� Calculs et fonctions intégrées Page 39

� Interroger plusieurs tables : les Jointures Page 52

� Utiliser des sous-requêtes Page 68

� Requêtes d’actions Page 79

� Les transactions Page 92

� Annexes Page 118

5

INTRODUCTION AUX BASES DE DONNÉES

6

Langage SQL

5

6

13/02/2020

4

Introduction bases de donnéesA la fin de cette session, vous serez capable de :

� Comprendre l’architecture d’une base de données relationnelle

� Reconnaître les tables

� Reconnaître les champs

7

Qu'est-ce qu'une base et un serveur de base de données ?

8

Une base de données est un ensemble structuré etorganisé (en tables) permettant le stockage de grandesquantités d'informations afin d'en faciliter l'exploitation(ajout, mise à jour, recherche et analyse des données).

7

8

13/02/2020

5

Eléments constitutifs

9

Requêtes mono-tables

� Les tables

� Les champs (colonnes) et les types de données

� Les enregistrements (lignes)

Requêtes multi-tables :

◦ Le modèle Physique de données (simplifié) indispensable

� Les clés

� Les relations

Base de données : StructureLes tables

10

9

10

13/02/2020

6

Base de données : StructureLes champs

11

Champs normalisés

Champs non normalisés

Base de données : Structure Les types de données courants

Texte Char(n), VarChar(n), nChar(n), nVarchar(n) 0 à 255 Char(10) : 10 octets sur le disque /et en mémoire

même si la valeur du champ est « Paris » Varchar(10) : 5 octets sur disque/mémoire si le champ contient « Paris »

• Numérique (entier ou décimal)• Int, Smallint, bigint selon la valeur maximale acceptée• Decimal (x,y) : Decimal (5,2) indique 5 chiffres avant la virgule

et une précision à 2 chiffres après la virgule• Numeric (x,y) : Identique à Decimal• Float : Réservé aux calculs scientifiques (très gourmand en mémoire)

◦ DateoDateTime, SmallDateTime (« juste » la date, ou date jusqu’au

centièmes de seconde)

12

11

12

13/02/2020

7

Base de données : Conclusion

Une base de données est :

- Un ensemble de données organisées et reliées

- Dans (et entre) différentes tables composées

- De champs (dont certains clés primaires)

- De types numériques, textes ou dates

Qui nous permet d’enregistrer, modifier et d’extraire de l’information !

13

EXTRAIRE LES DONNEES D’UNE TABLE

14

Langage SQL

13

14

13/02/2020

8

Extraire les données d'une table

Pour extraire les données d’une (ou plusieurs) table en SQL il faut :

Bien connaître le modèle physique (MPD) du sous système de base de données, le contenu, les règles métiers..

Et bien savoir ce que l’on veut !!

15

Les instructions SQL (DDL – DCL – DML)

DDLData Definition LanguageDéclarations (DDL) sont utilisés pour définir la structure de base de données ou un schéma.

DCLData Control LanguageContrôle des droits d’accés

DML (C’est LE langage (99%) des utilisateurs SQL)Data Manipulation LanguageInsert, Update, Delete, Select

TCLTransaction contrôle des déclarations (TCL) sont utilisés pour gérer les modifications apportées par les instructions DMLCommit, RollBack..

16

15

16

13/02/2020

9

SELECT et la syntaxe SQL

17

3 mots clés :SELECT

Champ1,

Champ2

SELECT est la commande de base du SQL destinée à extraire des données

provenant (FROM) du tableau

appelé « nom_du_tableau ».

FROM

Table

Cette requête SQL

va sélectionner et afficher (SELECT) le champ

« nom_du_champ »

WHERE

Champ3 =‘test’Contenant (condition) quand le «nom_du_champ» est

18

SELECT et la syntaxe SQL (2)

3 types de select:SELECT *

FROM

Retourne et affiche l’ensemble des

champs (colonnes) et des lignes.

(A éviter)

SELECT TOP 100 *

FROM

Retourne l’ensemble des champs

(colonnes) et seulement les 100

premières lignes : permet de visualiser

les données et ce que contient la table

sans problématique de performance.

SELECT DISTINCT

ClientPays Retourne la liste des pays des clients

sans doublons (on affiche distinctement)

SELECT DISTINCT

ClientPays Retourne la liste des pays des clients

sans doublons (on affiche distinctement)

17

18

13/02/2020

10

Filtrage des données : La clause DISTINCT

19

Le DISTINCT se fait sur l’ensemble des champs du Select

La commande SELECT : Exemples SQL server

20

19

20

13/02/2020

11

La commande SELECT : Règles d’écritures : (erreurs surlignées en rouge)Les erreurs de code sont souvent les mêmes

21

Les champs sélectionnés doivent

être séparés d’une virgule (comme

lorsque l’on énumère plusieurs

exemples (sauf pour le dernier qui

clôture)

Lorsque l’on renomme un champ,

calcul ou lorsque l’on met une

condition : ne pas oublier de mettre

des cotes

Lorsque l’on fait un calcul

(utilisation d’une fonction

d’agrégation) = il faut bien remettre

les autres champs du select dans le

Group By

La commande SELECT : Exemples Oracle

22

21

22

13/02/2020

12

Les commentaires en SQL

23

� Introduits par « -- » (une seule ligne

� Entourés par /*,,,,,,,,,,*/ (plusieurs lignes)

La commande SELECT : Règles d’écritures : (erreurs surlignées en rouge)Où sont les erreurs ?

24

Ouvrir la requête : Ou_sont_erreurs_code.sql

23

24

13/02/2020

13

La commande SELECT : SQL Server et la clause TOP (voir diapo suivante pour Oracle et MySQL)

25

La commande SELECT : Oracle et la clause RowNumMySQL et la clause LIMIT

26

25

26

13/02/2020

14

La commande SELECT : Sensibilité à la casse (majuscule / minuscule)

27

Ces 2 requêtes produisent un résultat différent si le serveur (ou la base) est sensible a la casse

ou

Renommer les colonnes

28

27

28

13/02/2020

15

La commande SELECT : La clause ORDER BY

29

Ordre décroissantordre croissant

La commande SELECT : La clause WHERE : Les critères simples

30

Texte :=‘France’<>’Spain’

Dates :Between ‘12/01/1950’ and ‘25/06/1970’

Numérique :=‘25’>’50’< ‘50’<>’25’

29

30

13/02/2020

16

La commande SELECT : La clause WHERE : (not) IN

31

select * from t_client

where ClientPays

Not in ('France','italy')

Select *

from Orders

where

Year in (‘2008’,2009,2012)

and

Month in ('april’, ‘october’)

La commande SELECT : La clause WHERE : (not)LIKE

32

Like = qui intègre les caractèresA% qui commence par a%a qui termine par a%a% qui contient a

31

32

13/02/2020

17

La commande SELECT : La clause WHERE : BetweenVs > & <

33

Cette commande avec AND à la place de betweenprendra trois fois plus de temps pour arriver au même résultat

La commande SELECT : La clause WHERE : Synthèse

34

= <> exactement égal à une seule valeurwhere ClientPays <> ‘France’

(not) in exactement égal à un ensemble de valeurs (remplace un or)where ClientPays not in (‘France’ ,’ Espagne’)where (ClientPays <> ‘France’ or

ClientPays <> ‘Espagne)

(not) like contient (et commence) par telle(s) valeur(s)where ClientPays like ‘%rance%’

where (ClientPays not like ‘Franc%’orClientPays not like ‘%pagne’)

Between .. and… entre telle et telle valeurwhere nombers between 5 and 7

33

34

13/02/2020

18

La commande SELECT : La clause CASE

35

La commande SELECT : La clause CASE (exemples dates)

36

35

36

13/02/2020

19

La commande SELECT : La clause CASE (Oracle)

37

La commande SELECT : CASE imbriqués

38

37

38

13/02/2020

20

LES CALCULS ET FONCTIONS INTÉGRÉES

Langage SQL

39

Les calculs / Fonctions intégrées

� Calculs simples

� Fonctions d’agrégations

� Fonctions DATE

� Fonctions TEXTE

� Fonctions CONVERT

40

39

40

13/02/2020

21

Les calculs / fonctions intégréesChamps calculé simple

41

Les calculs / fonctions intégréesLes fonctions d’agrégations

42

Les instructions d'agrégation permettent des opérations comme le comptage ou les sommes.

Les principales fonctions d'agrégation sont :

� AVG(<champs>)

� COUNT(*)

� SUM (<champs>)

41

42

13/02/2020

22

Les calculs / fonctions intégréesLes fonctions d’agrégationsGROUP BY : Regroupement et agrégation de données

43

• Utilisation des fonctions d'agrégation • Utilisation de la clause Group By • Filtrage de groupes avec la clause Having

Les calculs / fonctions intégréesLes fonctions d’agrégationsGROUP BY : Where et Having

44

Le critère where est avant le regroupement car sur un CHAMP

Le critère having est après le regroupement car sur une fonction d’agrégation

43

44

13/02/2020

23

Les calculs / fonctions intégréesLes fonctions d’agrégationsGROUP BY ou La clause OVER (partition order by)

45

Utilisation du group by : résultat

Mais nous pouvons aussi utiliser un partitionnement : Clause OVER

Les calculs / fonctions intégréesLes fonctions d’agrégationsGROUP BY ou La clause OVER (partition order by)Les clauses Row_number et rank

46

45

46

13/02/2020

24

47

Les calculs / fonctions intégréesLes fonctions : Date (SQL Server)

getdate() = sélectionnerdate du jourAttention si serveur encloud avec une locationet donc une datepotentiellementdifférente

48

Les calculs / fonctions intégréesLes fonctions : Date (SQL Server) : Exemples

47

48

13/02/2020

25

49

Les calculs / fonctions intégréesLa clause WHERE : Les critères avec fonctions

datediff = différence entre 2 dates

50

Les calculs / fonctions intégréesChaînes de caractères (SQL Server)

49

50

13/02/2020

26

51

Les calculs / fonctions intégréesConvertir : La fonction CAST

LES JOINTURES

52

Langage SQL

51

52

13/02/2020

27

53

Les jointuresStructure d’une table : Les clés

Clé primaire (primaryKey /PK)

Clé étrangèreForeign Key / FK)

Relation 0,1….n

54

Les jointuresRôle des clés

53

54

13/02/2020

28

55

Les jointuresLire un modèle relationnel (ou physique / MPD)

56

Les jointuresLe MPD (simplifié) de la base ContosoRetailDW

55

56

13/02/2020

29

57

Les jointuresLes jointures Vue d’ensemble

58

Les jointuresINNER JOIN (schéma) Inclusion

C’est LA Jointure par défaut qui compare deux tables et retourne tous les enregistrements comportant une concordance (en règle quasi générale) PK vers FK

57

58

13/02/2020

30

59

Les jointuresINNER JOIN (schéma) Inclusion

60

Les jointuresRenommer les tables (alias)

Exercice : - Dessiner le schéma avec les

tables utilisées,- Cocher clés primaires et

étrangères,- Lister champs utilisés par tables- Dites-moi s’il y a une erreur

dans le codeClient C

IdClient (PK)

ProduitID (FK)

Nom

59

60

13/02/2020

31

61

Les jointuresRenommer les tables (alias « métier »)

62

Les jointuresLEFTJOIN : l’exclusion

La commande LEFT JOIN (aussi appelée LEFT OUTER JOIN) est un type de jointure entre 2 tables. Cela permet de lister tous les résultats de la table de gauche (left = gauche) s’il n’y a pas de correspondance dans la deuxième tables.

61

62

13/02/2020

32

63

Les jointuresLEFT (outer) JOIN (exemple)

64

Les jointuresLeft JOIN : Exemple

63

64

13/02/2020

33

65

Les jointures« Old School » à proscrire dans la plupart des cas

(même si les versions récentes des SGBD créent le même plan d’exécution et offrent les mêmes performances)

66

Les jointuresSynthèse et Méthodologie

1. Sur quelles tables sont les informations nécessaires pour mon extraction

(A afficher et pour mes conditions) ?

FROM : Je fais mes jointures et je crée mes alias pour relier mes tables

(je peux dessiner un schéma lors de cette étape si besoin)

2. Qu’est-ce que je souhaite afficher ? Quelles colonnes / champs ?

SELECT : je liste mes champs en n’oubliant pas de remettre l’alias de la table avant

3. Est-ce que j’ai des conditions particulières de sélection des données ?

WHERE : Sur quels critères je souhaite avoir des informations ? Je n’oublie pas de mettre l’alias dela table avant le champ.

4. Comment je peux vérifier mon résultat ?

SELECT : mettre mes champs du where afin de vérifier que les critères fonctionnement

5. Est-ce que je veux exclure ou inclure des données d’une table par rapportà une autre ?

INNER JOIN ou LEFT JOIN ou RIGHT JOIN

Ne pas oublier ensuite la condition NULL (dans la table où l’on ne souhaite pas retrouver les données.

65

66

13/02/2020

34

La commande SELECT : SELECT UNION / ALL / INTERSECT / EXCEPT

67

LES SOUS REQUÊTESLangage SQL

68

67

68

13/02/2020

35

Utilisation de sous-requêtes

� Écriture de sous-requêtes simples

� Écriture de sous-requêtes corrélées

� Utilisation du prédicat Exists avec les sous-requêtes

� Utilisation du IN,ALL,ANY, SOME

69

70

Sous requêtes : Dans la clause WHERE : In ou NOT IN (requête simples)L’inclusion et l’exclusion

69

70

13/02/2020

36

71

Sous requêtes : Dans la clause WHERE : ALL / ANY / SOME

ALL : signifie supérieur à toutes les valeursExemple : > ALL(1,2,3) signifie supérieur à 3

>ANY(1,2,3) = signifie supérieur à la valeur minimale donc supérieur à 1

72

Sous requêtes : Dans la clause WHERE : EXISTS (requêtes corrélées)

3 différences avec les requêtes simples :

-Dans la condition where et le prédicat Exists : aucun champ car on va lier les requêtes par les tables entre la 1ère requête et la seconde : requêtes corrélées,

- Dans la sous-requête, on va utiliser l’ancienne syntaxe des jointures : tables énumérées dans le From et clés reliées dans le where,

- Dans le where de la sous-requête : on lie la clé de la table de la sous-requête à la clé de la table de la première requête (corrélation des requêtes)

71

72

13/02/2020

37

73

Sous requêtes Vs jointures

� Ces 2 requêtes retournent le même résultat. Les performances sont en faveur du join

(si grosse volumétrie)

74

Sous requêtes : Dans la clause FROM

73

74

13/02/2020

38

75

Sous requêtes : Dans le SELECT

76

Les sous-requêtesSynthèse et Méthodologie (1/2)1. Sur quelles tables sont les informations nécessaires pour mon extraction

(A afficher et pour mes conditions) ?

FROM : Je fais mes jointures et je crée mes alias pour relier mes tables

(je peux dessiner un schéma lors de cette étape si besoin)

2. Qu’est-ce que je souhaite afficher ? Quelles colonnes / champs ?

SELECT : je liste mes champs en n’oubliant pas de remettre l’alias de la table avant

3. Est-ce que j’ai des conditions particulières de sélection des données ?

WHERE : Sur quels critères je souhaite avoir des informations ? Je n’oublie pas de mettre l’alias de latable avant le champ.

4. Comment je peux vérifier mon résultat ?

SELECT : mettre mes champs du where afin de vérifier que les critères fonctionnement

5. Est-ce que je veux exclure ou inclure des données d’une table par rapportà une autre ?

INNER JOIN ou LEFT JOIN ou RIGHT JOIN

Ne pas oublier ensuite la condition NULL (dans la table où l’on ne souhaite pas retrouver les données.

Ou SOUS-REQUETE

75

76

13/02/2020

39

77

6. Est-ce que j’ai besoin de faire un calcul (fonction d’agrégation :sum, count, max, min, average) sur une autre fonction d’agrégationou sur un autre résultat ?

Je vais devoir créer une requête imbriquée dans le FROM afin de créer une table temporaire /table dérivée

7. Est-ce que je souhaite avoir d’autres calculs (fonctiond’agrégation) dans mon select qui soit un résultat général afin decomparer avec le résultat par enregistrement (client, commande,etc.) ?

Est-ce que je souhaite ramener un résultat d’une autre table oud’un autre résultat dans ma requête afin d’avoir l’information surmon extraction?

Je vais devoir créer une requête imbriquée dans le SELECT afin de créer un nouveau champ quiaura pour chaque ligne la même valeur.

Les sous-requêtesSynthèse et Méthodologie (2/2)

78

Sous requêtes : Règles à ne pas oublier !!!

� Toujours préciser les colonnes retourner par la recherche (le « select * » est

très gourmand)

� TOUJOURS utiliser le mot clé « JOIN (inner pour les select d’inclusion Multi-

tables

� Choisir sous requêtes ou LEFT JOIN pour requêtes d’exclusion

� Ne joindre que les tables nécessaires

� Pour augmenter la lisibilité des requêtes, renommer vos tables (alias)

� Documenter la requête avant de l’écrire :

• But

• Auteur

• Date

ET ne pas oublier le TOP !!

77

78

13/02/2020

40

LES REQUÊTES ACTIONS

Langage SQL

79

Requêtes « ACTION » :

� SELECT….. INTO

� INSERT INTO

� UPDATE

� DELETE

� TRUNCATE TABLE

80

79

80

13/02/2020

41

Requêtes « ACTION » :INSERT : Ne renseigner que certains champs

use DistrisysDW

insert into DimSite

(SiteCode,Site)

VALUES

('D008','Marseille')

81

Requêtes « ACTION » : INSERT : Renseigner tous les champs

INSERT T_Categorie VALUES (1,'Beverages','Soft drinks, coffees, teas, beers, and ales')

INSERT T_ClientVALUES('ALFKI','Alfreds Futterkiste','Maria Anders','SalesRepresentative','Obere Str. 57','Berlin',NULL,'12209','Germany','030-0074321','030-0076545')

INSERT INTO T_Commande

VALUES (10248,N'VINET',5,'7/4/2004','8/1/2004','7/7/2004',3,32.38,

N'Vins et alcools Chevalier',N'59 rue de l''Abbaye',N'Reims', NULL,N'51100',N'France')

ET PLUSIEURS LIGNES

82

81

82

13/02/2020

42

Requêtes « ACTION » : Requête INSERT INTO (sous requête)

CREATE TABLE T_into(

ClientNom varchar(255) NOT NULL,ClientPays nvarchar(50)

)

INSERT INTO T_intoSELECT ClientNom,ClientPaysFROM T_ClientWHERE ClientPays = 'france';

83

Requêtes « ACTION » : Requête UPDATE : Modification de valeurs

84

83

84

13/02/2020

43

Requêtes « ACTION » : Requête UPDATE : Modifier une ligne

85

Requêtes « ACTION » : Requête UPDATE : Modifier toutes les lignes

86

85

86

13/02/2020

44

Requêtes « ACTION » : Requête UPDATE

UPDATE T_Produit

SET Produit_PU = Produit_PU * 1.1

Where

PRODUIT_PU < (select avg (Produit_PU) fromT_Produit)

87

Requêtes « ACTION » : Requête CREATE TABLE

88

CREATE TABLE utilisateur ( id INT PRIMARY KEY NOT NULL, nom VARCHAR(100), prenomVARCHAR(100), email VARCHAR(255), date_naissance DATE, pays VARCHAR(255), ville VARCHAR(255), code_postalVARCHAR(5),nombre_achat INT

)Je souhaite créer un table qui s’intitule UTILISATEUR et qui contient :

Un id de type intéger/entier dont on n’autorise pas les null,Un Nom de type Varchar (charactère variable) de taille maximum = 100

Un date de naissance de type DATE car on ne souhaite pas connaître l’heure, les minutes, les secondes, etc.

87

88

13/02/2020

45

Requêtes « ACTION » : Requête CREATE TABLE

89

CREATETABLE artists( idartiste INTEGER PRIMARY KEY NOT NULL,

name TEXT)

CREATETABLE tracks( traid INTEGER PRIMARY KEY NOT NULL,

title TEXT,ida INTEGER,

FOREIGN KEY(ida) REFERENCES artists(idartiste) )

Je crée une première table « Artists » avec deux colonnes, un ID PK et un nom.

Je crée ensuite une seconde table « Tracks » avec un ID PK, un title, un Ida de type entier qui fait référence à une clé étrangère relative au champs PK

Idartiste de la table artiste.

Requêtes « ACTION » : Requête DELETE

Delete from T_Client WHERE condition

� La plus définitive

� On ne l’utilise qu’en développement,

� On préfère « flagger » un client (non actif, parexemple, associé à une date de modification) plutôtque le supprimer de la base de données

90

89

90

13/02/2020

46

Requêtes « ACTION » : Requête TRUNCATE TABLE

TRUNCATE TABLE T_CLIENT

Vide la table sans « journaliser » Utilisé essentiellement par les développeurs

TRUNCATE TABLE `table`

DROP TABLE : supprime la table et non plus les lignes comme DELETE ou TRUNCATE.

91

LES TRANSACTIONS

Langage SQL

92

91

92

13/02/2020

47

Implémentation SQLTransactions : Définition

� Une transaction est un traitement cohérent et fiable. Dans le cas des basesde données, c'est une action qui transforme la base de données d'un état stablecohérent en un autre état stable cohérent (après Insertion, suppression oumise à jour de données)

� En cas d'interruption pour une raison quelconque, la transaction garantit delaisser la base de données dans l'état dans lequel elle l'avait trouvée(Rollback). On dira ici que l’unité logique de traitement (transaction)est complétement abandonnée.

� En cas de réussite, la base contient les données modifiées (ou nouvelles)(Commit). On dira ici que l’unité logique de traitement (transaction)est complétement exécutée.

� Une transaction a quatre types d'opérations : un début de transaction, lalecture, l'écriture, et enfin la terminaison.

Implémentation SQLTransactions : Définition

� COMMIT� La commande COMMIT termine une transaction avec succès ;

toutes les mises à jour de la transaction sont validées� On dit que la transaction est validée� Tous ses effets sont alors connus des autres transactions

s'exécutant concurremment.

� ROLLBACK� La commande ROLLBACK termine une transaction avec échec ;

toutes les mises à jour de la transaction sont annulées (tout sepasse comme si la transaction n’avait jamais existé).

� On dit que la transaction est annulée.� Aucune des opérations effectuées par cette transaction n'est

connue des autres transactions

93

94

13/02/2020

48

Implémentation SQL

Transactions : Les propriétés ACID

� Atomicité : Cette propriété garantit qu'une série d'opérations (une"transaction") est exécutée soit en totalité soit pas du tout.

� Cohérence : Cette propriété garantit que toute modification faitévoluer la base d'un état cohérent à un autre état cohérent, enrespectant l'intégralité des contraintes d'intégrité, y compris lescontraintes référentielles.

� Isolation : Cette propriété garantie qu'aucune transaction en coursn'est affectée par une quelconque transaction non encore validée,même si certaines des opérations qui la composent sont déjàvalidées séparément.

� Durabilité (rémanence) : Cette propriété, qui ne peut jamais êtreabsolue du fait des risques de destruction matérielle des supportsd'information, garantit que les résultats d'une transaction une foisvalidée sont permanents, y compris en cas de destruction de partiesdu matériel non affectées au stockage des données de la base.

Implémentation SQLTransactions : Verrouillage

L'outil le plus répandu pour sérialiser les transactions est basé sur l'utilisation de verrous (lock).

On impose que l'accès aux données se fassent de manière mutuellement exclusive.

Un verrou est posé sur chaque donnée accédée par une transaction afin d'empêcher les autres transactions d'y accéder.

95

96

13/02/2020

49

Implémentation SQLTransactions : Niveau d’isolation

Read uncommitted :les donnéesphysiquementcorrompues ne sontpas lues mais lecturenon validée.

Il est donc nécessaired’avoir ReadCommited : niveaupar défaut du moteurde base de données :Mise en place deverrous sur leslectures empêchantque la modification deslignes ne soientcommises pendant lalecture.

Implémentation SQLVues

� Une vue est une table virtuelle, c'est-à-diredont les données ne sont pas stockées dansune table de la base de données, et danslaquelle il est possible de rassembler desinformations provenant de plusieurs tables.

� On parle de "vue" car il s'agit simplementd'une représentation des données dans le butd'une exploitation visuelle.

� Les données présentes dans une vue sontdéfinies grâce à une clause SELECT

97

98

13/02/2020

50

Implémentation SQLCTE

� Une expression de table commune (CTE, CommonTable Expression) peut être considérée comme unjeu de résultats temporaire défini dans l’étendued’exécution d’une seule instruction SELECT, INSERT,UPDATE, DELETE ou CREATE VIEW.

� Elles permettent de remplacer les sous requêtesdans le From (table dérivée) qui rendent un codeillisible

(et souvent sans commentaire) dans une requêtestandard.

Implémentation SQLCTE : exemple (1/2)-- version en sous-requête : table dérivée

select count(*) as nb

from(select

CUS.lastname,

GEO.[RegionCountryName],

sum(FAC.salesamount)as total

from

DimCustomer CUS

inner join DimGeography GEO

on CUS.GeographyKey=GEO.GeographyKey

inner join FactOnlineSales FAC

on FAC.CustomerKey=CUS.CustomerKey

group by

CUS.lastname,

GEO.[RegionCountryName]) as fun

99

100

13/02/2020

51

Implémentation SQLCTE : exemple (2/2)

with fun as

(select

CUS.lastname,

GEO.[RegionCountryName],

sum(FAC.salesamount)as total

from

DimCustomer CUS

inner join DimGeography GEO

on CUS.GeographyKey=GEO.GeographyKey

inner join FactOnlineSales FAC

on FAC.CustomerKey=CUS.CustomerKey

group by

CUS.lastname,

GEO.[RegionCountryName])

select count(*) as nb

from fun

Implémentation SQLProcédures stockées

� Une procédure stockée est un grouped'instructions Transact-SQL qui est compiléune fois pour toutes et qui peut êtreexécuté plusieurs fois.

� Lorsqu'elle est exécutée, les performancessont améliorées car les instructionsTransact-SQL ne doivent pas êtrerecompilées.

101

102

13/02/2020

52

103

Je crée ma procédure stockée : - Je vais la nommer et créer la variable,- Je met ma requête qui utilise ma variable dans la procédure stockée

(SP: stocked procedure)- J’éxecute le tout enregistrement de la SP.

create proc sp_nbclientpays @strPays varchar(50)as

selectclientpays,

count(clientpays) as 'Nombre de clients'from T_Client

Where clientpays = @strPaysGroup by ClientPays

Puis donner la valeur pour variable : exec sp_nbclientpays 'SPAIN'

Implémentation SQLProcédures stockées (1/2)

104

Je crée ma procédure stockée avec deux variables :

create proc sp_nbclientpays2 @strPays varchar(50), @strclient varchar(5)as

selectclientpays,clientnom,

count(clientpays)from T_Client

Where clientpays= @strPaysand left(ClientNom,1) = @strclientGroup by ClientPays, ClientNom

Puis les valeurs : exec sp_nbclientpays2 'FRANCE', 'B'

Implémentation SQLProcédures stockées (2/2)

103

104

13/02/2020

53

105

Implémentation SQLDéclaration BEGIN… END

Résultat :

v_a= 50

v_b= 100

v_Result= 150

106

Je veux attraper mon erreur :

Implémentation SQLGestion des erreurs : 1er exemple

set nocount on

begin try -- fonction

select 1/0 -- je fais un essai : requête avec une

possibilité d'erreur

end try

begin catch -- j'attrape -- interception erreur car il

n'aime pas si on divise par 0 :

print 'division par 0';

throw; -- je jette car je n'aime pas

end catch

105

106

13/02/2020

54

107

Implémentation SQLGestion des erreurs : 2 exemple

-- un ajout ou une suppression ou une mise à jour commence toujours par un begin transaction

BEGIN TRANSACTION;

BEGIN TRY -- code dans de l'applicatif

-- Generate a constraint violation error.

DELETE FROM Production.Product

WHERE ProductID = 980; -- si on lance la requête il y a

une contrainte d'intégrité référentielle

-- il interdit de supprimer un produit si déjà commandé :

déjà clé étrangère dans une table.

END TRY

108

Implémentation SQLGestion des erreurs : suite

BEGIN CATCH -- si pas de problème, il ne viendra pas ici

SELECT -- si problème, il vient exécuter toutes les

lignes qu'il y a jusqu'au End Catch

ERROR_NUMBER() AS ErrorNumber

-- il fait un select pour nous afficher le numéro de

l’erreur

,ERROR_SEVERITY() AS ErrorSeverity

-- sévérité systeme (message d'erreurs systèmes)

,ERROR_MESSAGE() AS ErrorMessage;

-- le message d'erreur

IF @@TRANCOUNT > 0

-- variable système : j’éxecute ou pas

ROLLBACK TRANSACTION; -- à ce moment il annule

la transaction (delete) il revient dans l'état initial de

la table)

END CATCH;

107

108

13/02/2020

55

109

Implémentation SQLDéclaration IF ELSEIF ELSE

- si la condition1 est respectée alors JOB 1 - sinon, si condition 2 est validée alors JOB 2 (action à faire), - pour tous les autres Job N+1- Fin du IF

110

Implémentation SQLDéclaration IF ELSEIF ELSE : exemple

Que permet de faire cette requête

?

Résultat :

In block else if @v_Option = 2@v_Action= Backup

109

110

13/02/2020

56

111

Implémentation SQLWHILE : La boucle

Je crée uneboucle :Quand x estplus petit que y.

Je monte d’uneétape à chaquefois.

Mes données xpassent à lasupérieure.Mes données ypassent àl’inferieur.

112

Implémentation SQLWHILE : La boucle et le BREAK

Je stoppe maboucle si lesdonnées suiventune autrecondition.

111

112

13/02/2020

57

113

Implémentation SQLIF dans une procédure stockée

Implémentation SQLdéclencheurs (triggers)

� Un déclencheur est un type spécifique de procédurestockée qui n'est pas appelé directement par unutilisateur. Lorsque le déclencheur est créé, il est définide façon à se déclencher lorsqu'un certain type demodification de données est effectué dans une tableou une colonne spécifique (After INSERT, Instead OfDELETE)

� On peut dire qu’il s’agit d’une procédure stockéeévénementielle.

� Il existe des déclencheurs de tables et de bases dedonnées (DDL)

113

114

13/02/2020

58

Implémentation SQLdéclencheurs (trigger DDL)

� Dans les bases de données, lors de la mise à jour ou dela suppression d'une donnée, si un déclencheur existe, ilpeut lancer automatiquement une procédure stockée,qui agit en parallèle sur la même donnée dans une tableafférente.

� Il peut également remplir automatiquement une tablehistorique, ou contrôler la validité de la données

� Cela permet d'automatiser certains traitementsassurant la cohérence et l'intégrité de la base dedonnées.

� Aujourd’hui c’est souvent l’applicatif qui gèreces problématiques.

Implémentation SQLSécurité (droits d’accès et rôles)

� Les risques propres à une source de données sont les suivants :

�vol de données (perte de confidentialité)�altération de données (perte d'intégrité)�destruction de données (remise en cause de la

continuité d'activité)�augmentation du niveau de privilèges d'un

utilisateur d'une application (sécurité, espionnage)�ressources systèmes abusives (déni de service)

115

116

13/02/2020

59

Implémentation SQLSécurité (droits d’accès et rôles)

� Contrôle des privilèges� La principale question qui se pose lors du

développement d'une application, c'est quellestratégie adopter vis à vis des utilisateurs : contrôlede leurs droits d'accès par l'application ou par leSGBD ?

� Le créateur d’une table est propriétaire de cettetable et obtient les droits d’accès de cette table.

� Le propriétaire de la table peut passer sesprivilèges sélectivement à d’autres utilisateurs ou àtout le monde.

ANNEXES

Sommaire / Plan détaillé du support de formation

Schémas des bases de données

Liens web utiles

118

117

118

13/02/2020

60

Sommaire / Plan détaillé (1/3)

119

Sommaire / Plan détaillé (2/3)

120

119

120

13/02/2020

61

Sommaire / Plan détaillé (3/3)

121

Schémas des bases de données - Comptoir

122

121

122

13/02/2020

62

Schémas des bases de données_SQL_SESSION_SIF

123

Schémas des bases de donnéesConsotoRetailDW (simplifié)

124

123

124

13/02/2020

63

Liens web utiles

125

Le Web regorge de sites traitant (parfois de façon excellente) du SQL.

Comme il n’est pas possible de les citer tous, en voici une liste tout à fait arbitraire mais représentative de ce qu’on trouve sur le web :

� http://sql.sh/� https://openclassrooms.com/courses/apprenez-a-programmer-en-vb-

net/introduction-au-langage-sql� http://sql.dev� https://stackoverflow.com/� https://technet.microsoft.com/fr-

fr/library/ms190750(v=sql.105).aspxeloppez.com/#apprendre-sql

MERCI POUR VOTRE ATTENTION

J’espère que vous repartez confiant et serein autour du langage SQL

Bonne continuation

126

125

126