TUTORIAL COMPLET : SQL SERVER

Implémentation des Bases de Données

Programme d’études BTS - INFEP/INT0703

Introduction à SQL Server et à ses Outils

Qu’est-ce que SQL Server?
SQL Server est un système de gestion de bases de données relationnelles (SGBDR)
développé par Microsoft. Il permet de stocker, gérer et récupérer efficacement les données
dans les environnements informatiques modernes.
SQL Server est un SGBDR qui offre une plateforme complète pour la gestion des données,
incluant des outils de développement, de maintenance et de sécurité.

Les Fonctions Principales de SQL Server

SQL Server offre les fonctionnalités suivantes:
• Stockage sécurisé des données

• Récupération rapide des informations

• Gestion de la sécurité et des accès

• Sauvegarde et récupération des données

• Réplication des données

• Analyse et rapport

Vue d’ensemble de l’Architecture SQL Server

L’architecture de SQL Server comprend plusieurs composants:
• Database Engine: Le cœur du système qui traite les requêtes

• SQL Server Management Studio (SSMS): L’interface graphique

• SQL Server Configuration Manager: Gestion des services

• SQL Server Agent: Automatisation des tâches

Besoins en Ressources du Serveur

Avant d’installer SQL Server, assurez-vous d’avoir:
 • Processeur: Minimum 1.4 GHz (recommandé 2.0 GHz ou plus)

• RAM: Minimum 2 Go (recommandé 8 Go ou plus)

• Disque dur: Minimum 6 Go d’espace libre

• Système d’exploitation: Windows Server ou Windows professionnel

Exemple: Vérification de la Configuration Système

Pour vérifier si votre système répond aux exigences:
-- Vérifier la version de SQL Server
SELECT @@VERSION;

-- Vérifier les propriétés du serveur

SELECT
SERVERPROPERTY('ServerName') AS NomServeur,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS Version;

Il est recommandé de vérifier les mises à jour du système d’exploitation avant d’installer
SQL Server.

Installation et Configuration de SQL Server

Étapes d’Installation
L’installation de SQL Server se déroule en plusieurs étapes:
1. Téléchargement du fichier d’installation

2. Exécution du programme d’installation

3. Configuration des instances

4. Configuration de l’authentification

5. Vérification de l’installation

Téléchargement et Versions
SQL Server est disponible en plusieurs versions:
• Enterprise: Édition complète avec toutes les fonctionnalités

• Standard: Édition pour usage professionnel standard

• Express: Version gratuite et allégée

• Developer: Version pour développeurs (gratuite)

Assurez-vous de télécharger la version appropriée pour vos besoins et votre licence.

Configuration de l’Authentification
SQL Server supporte deux modes d’authentification:
• Mode Windows: Utilise l’authentification du système d’exploitation

• Mode Mixte: Combine l’authentification Windows et SQL Server

Vérification de l’Installation
Après l’installation, vérifiez que:
1. Le service SQL Server est en cours d’exécution

2. SQL Server Management Studio est installé

3. Vous pouvez vous connecter au serveur

Exemple: Vérification des Services Installés

Utilisez la commande SQL suivante dans SSMS:
-- Vérifier l'état du serveur
SELECT
GETDATE() AS 'Date/Heure Actuelle',
@@SERVERNAME AS 'Serveur',
USER AS 'Utilisateur Connecté';

SQL Server Management Studio (SSMS) et Outils

Interface de SSMS
SQL Server Management Studio (SSMS) est l’outil principal pour gérer SQL Server. Son
interface comprend:
• Explorateur d’Objets: Navigation dans les bases de données

• Éditeur de Requêtes: Saisie des commandes SQL

• Explorateur de Solutions: Gestion des projets

• Fenêtre Propriétés: Affichage des propriétés des objets

SQL Server Configuration Manager

Cet outil permet de:
• Gérer les services SQL Server
 • Configurer les protocoles de connexion

• Définir les paramètres de performance

• Gérer les instances de SQL Server

Les Composants Principaux de SQL Server

1. Database Engine: Traitement des requêtes

2. SQL Server Agent: Planification des tâches

3. SQL Server Analysis Services: Analyses OLAP

4. SQL Server Reporting Services: Création de rapports

5. SQL Server Integration Services: Intégration de données

Exemple: Connexion à SQL Server via SSMS

-- Dans SSMS, allez à: Fichier > Nouvelle Requête
-- Assurez-vous d'être connecté au bon serveur
-- Tapez votre première requête:
SELECT 'Bienvenue dans SQL Server!' AS Message;

Conservez la fenêtre des résultats visible pour voir immédiatement le résultat de vos
requêtes.

Concepts Généraux du Serveur de Bases de Données

Présentation du Serveur SQL Server (Instance)
Une instance est une installation de SQL Server indépendante sur un serveur. Vous pouvez
avoir plusieurs instances sur un même serveur.

Explorateur d’Objets
L’Explorateur d’Objets montre l’hiérarchie:
• Serveurs

• Instances

• Bases de données

• Tables

• Vues

• Procédures stockées

• Fonctions
 • Déclencheurs

Propriétés du Serveur
Les propriétés importantes incluent:
• Nom du Serveur et Instance

• Mémoire Allouée

• Nombre de Connexions

• Paramètres de Performance

Exemple: Affichage des Propriétés du Serveur

-- Afficher les propriétés du serveur
SELECT
SERVERPROPERTY('ServerName') AS 'Nom Serveur',
SERVERPROPERTY('InstanceName') AS 'Instance',
SERVERPROPERTY('MachineName') AS 'Machine',
SERVERPROPERTY('ProductVersion') AS 'Version';

-- Afficher les informations de connexion

SELECT
DB_NAME() AS 'Base Actuelle',
USER AS 'Utilisateur',
GETDATE() AS 'Heure Serveur';

Bases de Données dans SQL Server

Les bases de données se divisent en:
• Bases Système: Gestion du serveur (master, msdb, tempdb)

• Bases Utilisateurs: Données métier

Objets de la Base de Données

• Tables: Structures de stockage des données

• Contraintes: Règles de validation

• Index: Optimisation des recherches

• Vues: Requêtes préenregistrées

• Procédures Stockées: Code réutilisable

• Déclencheurs: Automatisation des actions

Langage Transact-SQL (T-SQL) - Fondamentaux
Introduction à Transact-SQL
Transact-SQL (T-SQL) est le dialecte SQL utilisé par SQL Server. Il étend le standard SQL
avec des fonctionnalités avancées.

Les Quatre Catégories Principales

1. DDL (Data Definition Language): CREATE, DROP, ALTER

2. DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE

3. DCL (Data Control Language): GRANT, DENY, REVOKE

4. TCL (Transaction Control Language): BEGIN TRAN, COMMIT, ROLLBACK

Éléments de Syntaxe T-SQL

Directives de Lot
-- GO: Directive qui envoie les commandes au serveur
GO

-- EXEC: Exécuter une procédure stockée

EXEC sp_help;
GO

Commentaires
-- Commentaire sur une ligne

/* Commentaire
sur plusieurs lignes */

Identificateurs
-- Identificateur simple
SELECT NomColonne FROM MaTable;

-- Identificateur entre crochets

SELECT [Nom Colonne] FROM [Ma Table];

-- Identificateur entre guillemets (en mode compatible)

SELECT "NomColonne" FROM MaTable;

Types de Données
• Numériques: INT, BIGINT, DECIMAL, FLOAT

• Chaînes: VARCHAR, NVARCHAR, CHAR

• Dates: DATE, DATETIME, DATETIME2

• Booléens: BIT
 • Binaires: VARBINARY, IMAGE

Variables
-- Déclaration de variables
DECLARE @NomVariable INT;
DECLARE @NomClient VARCHAR(50);
DECLARE @DateActuelle DATETIME = GETDATE();

-- Affectation de valeurs
SET @NomVariable = 100;
SELECT @NomVariable AS Resultat;

Contrôle de Flux
IF...ELSE
DECLARE @Age INT = 25;

IF @Age >= 18
SELECT 'Vous êtes majeur';
ELSE
SELECT 'Vous êtes mineur';

WHILE
DECLARE @Compteur INT = 1;

WHILE @Compteur <= 5

BEGIN
PRINT 'Itération ' + CAST(@Compteur AS VARCHAR);
SET @Compteur = @Compteur + 1;
END

Exemple: Instructions Transact-SQL Simples

-- Afficher le message simple
SELECT 'Bienvenue dans SQL Server' AS Message;

-- Utiliser des variables

DECLARE @Prenom VARCHAR(50) = 'Jean';
DECLARE @Age INT = 30;

SELECT
@Prenom AS Prenom,
@Age AS Age,
GETDATE() AS DateActuelle;

Exercice: Utilisation de Variables et Contrôle de Flux

Écrivez un script T-SQL qui:
1. Déclare deux variables: @Nombre1 (valeur 50) et @Nombre2 (valeur 30)
 2. Affiche le plus grand nombre

3. Affiche la somme des deux nombres

Solution: Utilisation de Variables et Contrôle de Flux

-- Déclaration des variables
DECLARE @Nombre1 INT = 50;
DECLARE @Nombre2 INT = 30;

-- Afficher le plus grand nombre

SELECT
CASE
WHEN @Nombre1 > @Nombre2 THEN @Nombre1
ELSE @Nombre2
END AS PlusGrandNombre;

-- Afficher la somme
SELECT @Nombre1 + @Nombre2 AS Somme;

-- Alternative avec messages

DECLARE @PlusGrand INT;
SET @PlusGrand = CASE
WHEN @Nombre1 > @Nombre2 THEN @Nombre1
ELSE @Nombre2
END;

SELECT
@PlusGrand AS PlusGrandNombre,
(@Nombre1 + @Nombre2) AS Somme;

Création des Bases de Données et Objets

Notions Générales sur les Bases de Données
Une base de données est un conteneur qui stocke:
• Tables

• Index

• Vues

• Procédures stockées

• Fonctions

• Déclencheurs

• Fichiers de données et journaux

Liens entre Base de Données et Organisation Physique
• Fichiers de Données: Stockent les tables et index

• Fichiers Journaux: Enregistrent toutes les modifications

Création d’une Base de Données

Mode Graphique
1. Ouvrir SSMS

2. Cliquer sur "Bases de Données"

3. Cliquer droit > "Nouvelle Base de Données"

4. Remplir le nom et les paramètres

Mode Programmation
-- Créer une base de données simple
CREATE DATABASE MaBaseTest;
GO

-- Créer une base de données avec paramètres

CREATE DATABASE GestionClients
ON
(
NAME = GestionClients_Data,
FILENAME = 'C:\SQLDATA\[Link]',
SIZE = 10MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
LOG ON
(
NAME = GestionClients_Log,
FILENAME = 'C:\SQLLOG\[Link]',
SIZE = 5MB,
MAXSIZE = 50MB,
FILEGROWTH = 1MB
);
GO

Gestion d’une Base de Données

Supprimer une Base de Données
-- Supprimer une base de données
DROP DATABASE MaBaseTest;
GO
Modifier une Base de Données
-- Modifier la taille d'une base de données
ALTER DATABASE GestionClients
MODIFY FILE
(
NAME = GestionClients_Data,
SIZE = 20MB,
MAXSIZE = 200MB
);
GO

Exemple: Création Complète d’une Base de Données

-- Créer la base de données
CREATE DATABASE Entreprise
ON
(
NAME = Entreprise_Data,
FILENAME = 'C:\SQLDATA\[Link]',
SIZE = 50MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB
)
LOG ON
(
NAME = Entreprise_Log,
FILENAME = 'C:\SQLLOG\[Link]',
SIZE = 25MB,
MAXSIZE = 250MB,
FILEGROWTH = 5MB
);
GO

-- Vérifier que la base est créée

USE Entreprise;
GO
SELECT 'Base de données Entreprise créée avec succès' AS Message;
GO

Création et Gestion des Tables

Concepts de Base des Tables
Une table est la structure fondamentale pour stocker les données. Elle est composée de
colonnes et de lignes.
Création de Tables
Syntaxe Générale
CREATE TABLE NomTable
(
ColonneID INT PRIMARY KEY IDENTITY(1,1),
NomColonne1 VARCHAR(50) NOT NULL,
NomColonne2 INT,
NomColonne3 DATETIME DEFAULT GETDATE()
);
GO

Contraintes
PRIMARY KEY (Clé Primaire)
CREATE TABLE Clients
(
ClientID INT PRIMARY KEY IDENTITY(1,1),
NomClient VARCHAR(50) NOT NULL,
Email VARCHAR(100)
);
GO

FOREIGN KEY (Clé Étrangère)

CREATE TABLE Commandes
(
CommandeID INT PRIMARY KEY IDENTITY(1,1),
ClientID INT NOT NULL,
DateCommande DATETIME DEFAULT GETDATE(),
FOREIGN KEY (ClientID) REFERENCES Clients(ClientID)
);
GO

NOT NULL et DEFAULT

CREATE TABLE Employes
(
EmployeID INT PRIMARY KEY IDENTITY(1,1),
NomEmploye VARCHAR(100) NOT NULL,
Email VARCHAR(100) NOT NULL UNIQUE,
Salaire DECIMAL(10,2) DEFAULT 0,
DateEmbauche DATETIME DEFAULT GETDATE()
);
GO

CHECK (Vérification)
CREATE TABLE Produits
(
ProduitID INT PRIMARY KEY IDENTITY(1,1),
NomProduit VARCHAR(100) NOT NULL,
Prix DECIMAL(10,2) CHECK (Prix > 0),
 Stock INT CHECK (Stock >= 0)
);
GO

Valeurs Auto-incrémentées et Séquences

IDENTITY
CREATE TABLE Utilisateurs
(
UtilisateurID INT IDENTITY(1,1) PRIMARY KEY,
NomUtilisateur VARCHAR(50),
Email VARCHAR(100)
);
GO

SEQUENCE
-- Créer une séquence
CREATE SEQUENCE NumeroFacture
START WITH 1
INCREMENT BY 1;
GO

-- Utiliser la séquence
CREATE TABLE Factures
(
NumeroFacture INT PRIMARY KEY DEFAULT (NEXT VALUE FOR
NumeroFacture),
DateFacture DATETIME,
Montant DECIMAL(10,2)
);
GO

Colonnes Calculées
CREATE TABLE Ventes
(
VenteID INT PRIMARY KEY IDENTITY(1,1),
PrixUnitaire DECIMAL(10,2),
Quantite INT,
MontantTotal AS (PrixUnitaire * Quantite)
);
GO

Modification d’une Table

Ajouter une Colonne
ALTER TABLE Clients
ADD Telephone VARCHAR(20);
GO
Modifier une Colonne
ALTER TABLE Clients
ALTER COLUMN NomClient VARCHAR(150);
GO

Supprimer une Colonne

ALTER TABLE Clients
DROP COLUMN Telephone;
GO

Suppression d’une Table

DROP TABLE NomTable;
GO

-- Supprimer si elle existe

DROP TABLE IF EXISTS NomTable;
GO

Exemple: Création d’une Table Complète

-- Créer une table d'étudiants
CREATE TABLE Etudiants
(
EtudiantID INT PRIMARY KEY IDENTITY(1,1),
NomEtudiant VARCHAR(100) NOT NULL,
PrenomEtudiant VARCHAR(100) NOT NULL,
Email VARCHAR(100) NOT NULL UNIQUE,
DateNaissance DATE NOT NULL,
Moyenne DECIMAL(4,2) CHECK (Moyenne >= 0 AND Moyenne <= 20),
DateInscription DATETIME DEFAULT GETDATE(),
Actif BIT DEFAULT 1
);
GO

-- Insérer des données

INSERT INTO Etudiants (NomEtudiant, PrenomEtudiant, Email,
DateNaissance, Moyenne)
VALUES
('Dupont', 'Marie', '[Link]@[Link]', '2000-05-15', 18.5),
('Martin', 'Jean', '[Link]@[Link]', '2001-03-20', 16.0),
('Durand', 'Sophie', '[Link]@[Link]', '2000-11-10',
17.5);
GO

-- Afficher les données

SELECT * FROM Etudiants;
GO

Exercice: Création d’une Table de Gestion de Magasin

Créez une table Articles avec les colonnes suivantes:
 • ArticleID (clé primaire, auto-incrémentée)

• NomArticle (VARCHAR(100), obligatoire)

• Description (VARCHAR(500))

• Prix (DECIMAL(10,2), > 0)

• Stock (INT, >= 0)

• DateAjout (DATETIME, valeur par défaut = date actuelle)

Solution: Création d’une Table de Gestion de Magasin

CREATE TABLE Articles
(
ArticleID INT PRIMARY KEY IDENTITY(1,1),
NomArticle VARCHAR(100) NOT NULL,
Description VARCHAR(500),
Prix DECIMAL(10,2) CHECK (Prix > 0),
Stock INT CHECK (Stock >= 0),
DateAjout DATETIME DEFAULT GETDATE()
);
GO

-- Vérifier que la table est créée

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Articles';
GO

-- Insérer des données de test

INSERT INTO Articles (NomArticle, Description, Prix, Stock)
VALUES
('Chaise', 'Chaise ergonomique', 45.99, 100),
('Bureau', 'Bureau en bois', 199.99, 25),
('Lampe', 'Lampe LED', 29.99, 150);
GO

-- Afficher les données

SELECT * FROM Articles;
GO

Requêtes DML: SELECT, INSERT, UPDATE, DELETE

SELECT: Récupération des Données
Syntaxe Basique
SELECT colonnes FROM Table WHERE conditions;
GO
SELECT Simple
-- Récupérer toutes les colonnes
SELECT * FROM Clients;
GO

-- Récupérer des colonnes spécifiques

SELECT NomClient, Email FROM Clients;
GO

-- Renommer les colonnes (alias)

SELECT
NomClient AS 'Nom du Client',
Email AS 'Adresse Email'
FROM Clients;
GO

WHERE: Filtrer les Données

-- Filtrer avec une condition simple
SELECT * FROM Employes WHERE Salaire > 3000;
GO

-- Filtrer avec plusieurs conditions

SELECT * FROM Employes
WHERE Salaire > 2000 AND Departement = 'IT';
GO

-- Utiliser OR
SELECT * FROM Employes
WHERE Departement = 'IT' OR Departement = 'HR';
GO

-- Utiliser IN
SELECT * FROM Employes
WHERE Departement IN ('IT', 'HR', 'Finance');
GO

-- Utiliser LIKE (recherche de texte)

SELECT * FROM Clients
WHERE NomClient LIKE 'M%'; -- Commence par M
GO

ORDER BY: Trier les Données

-- Trier en ordre ascendant (par défaut)
SELECT * FROM Employes ORDER BY Salaire;
GO

-- Trier en ordre descendant

SELECT * FROM Employes ORDER BY Salaire DESC;
GO
-- Trier par plusieurs colonnes
SELECT * FROM Employes
ORDER BY Departement ASC, Salaire DESC;
GO

INSERT: Insérer des Données

Syntaxe
INSERT INTO Table (Colonne1, Colonne2, Colonne3)
VALUES (Valeur1, Valeur2, Valeur3);
GO

INSERT Simple
INSERT INTO Clients (NomClient, Email, Telephone)
VALUES ('Dupont Jean', 'jean@[Link]', '0123456789');
GO

INSERT Multiple
INSERT INTO Clients (NomClient, Email)
VALUES
('Martin Sophie', 'sophie@[Link]'),
('Durand Pierre', 'pierre@[Link]'),
('Dubois Isabelle', 'isabelle@[Link]');
GO

UPDATE: Modifier les Données

Syntaxe
UPDATE Table
SET Colonne1 = Valeur1, Colonne2 = Valeur2
WHERE Condition;
GO

UPDATE Simple
-- Modifier un enregistrement
UPDATE Employes
SET Salaire = 3500
WHERE EmployeID = 1;
GO

-- Modifier plusieurs enregistrements

UPDATE Employes
SET Salaire = Salaire * 1.10
WHERE Departement = 'IT';
GO

Toujours inclure une clause WHERE pour éviter de modifier tous les enregistrements par
erreur!
DELETE: Supprimer les Données
Syntaxe
DELETE FROM Table WHERE Condition;
GO

DELETE Simple
-- Supprimer un enregistrement
DELETE FROM Clients WHERE ClientID = 5;
GO

-- Supprimer plusieurs enregistrements

DELETE FROM Commandes WHERE DateCommande < '2020-01-01';
GO

-- Supprimer tous les enregistrements (attention!)

DELETE FROM TableTemporaire;
GO

Avant de supprimer, faites toujours une sauvegarde et utilisez SELECT pour vérifier les
enregistrements qui seront supprimés.

Exemple: Opérations DML Complètes

-- Créer une table de test
CREATE TABLE Departements
(
DepartementID INT PRIMARY KEY IDENTITY(1,1),
NomDepartement VARCHAR(100) NOT NULL,
Budget DECIMAL(12,2)
);
GO

-- Insérer des données

INSERT INTO Departements (NomDepartement, Budget)
VALUES
('Informatique', 50000),
('Ressources Humaines', 30000),
('Ventes', 40000),
('Marketing', 25000);
GO

-- Sélectionner et afficher
SELECT * FROM Departements ORDER BY Budget DESC;
GO

-- Modifier un budget
UPDATE Departements
SET Budget = 60000
WHERE NomDepartement = 'Informatique';
GO
-- Supprimer un département
DELETE FROM Departements
WHERE NomDepartement = 'Marketing';
GO

-- Afficher les résultats finaux

SELECT * FROM Departements;
GO

Exercice: Opérations DML sur une Table de Produits

1. Créez une table Produits avec les colonnes: ProduitID, NomProduit, Prix,
Stock

2. Insérez 5 produits

3. Augmentez le prix de 10% pour tous les produits

4. Affichez les produits avec un prix > 100

5. Supprimez les produits dont le stock = 0

Solution: Opérations DML sur une Table de Produits

-- Créer la table
CREATE TABLE Produits
(
ProduitID INT PRIMARY KEY IDENTITY(1,1),
NomProduit VARCHAR(100) NOT NULL,
Prix DECIMAL(10,2),
Stock INT
);
GO

-- Insérer 5 produits
INSERT INTO Produits (NomProduit, Prix, Stock)
VALUES
('Ordinateur', 800, 15),
('Clavier', 50, 50),
('Souris', 25, 0),
('Écran', 300, 10),
('Imprimante', 150, 5);
GO

-- Augmenter le prix de 10%

UPDATE Produits
SET Prix = Prix * 1.10;
GO

-- Afficher les produits avec prix > 100

SELECT * FROM Produits
WHERE Prix > 100
ORDER BY Prix DESC;
GO

-- Supprimer les produits avec stock = 0

DELETE FROM Produits
WHERE Stock = 0;
GO

-- Afficher les résultats

SELECT * FROM Produits;
GO

Index: Optimisation des Performances

Notion d’Index
Un index est une structure qui améliore la vitesse des recherches dans une table. Il
fonctionne comme l’index d’un livre.

Types d’Index
Index Organisé (Clustered)
Il existe un seul index organisé par table. Les données sont triées selon cet index.
-- L'index organisé est généralement la clé primaire
CREATE TABLE Clients
(
ClientID INT PRIMARY KEY, -- Index organisé
NomClient VARCHAR(100),
Email VARCHAR(100)
);
GO

Index Non-organisé (Non-clustered)

Plusieurs index non-organisés peuvent exister sur une table.
-- Créer un index non-organisé
CREATE INDEX IX_Email ON Clients(Email);
GO

-- Index composé (sur plusieurs colonnes)

CREATE INDEX IX_Nom_Email ON Clients(NomClient, Email);
GO
Création d’Index
-- Index simple
CREATE INDEX IX_Employes_Departement
ON Employes(Departement);
GO

-- Index avec colonnes incluses

CREATE INDEX IX_Employes_Salaire
ON Employes(Salaire)
INCLUDE (NomEmploye, Email);
GO

Suppression d’Index
DROP INDEX IX_Employes_Departement ON Employes;
GO

Reconstruction d’Index
-- Reconstruire un index
ALTER INDEX IX_Employes_Departement ON Employes REBUILD;
GO

-- Réorganiser un index (moins coûteux)

ALTER INDEX IX_Employes_Departement ON Employes REORGANIZE;
GO

Statistiques et Performance
-- Afficher les statistiques d'un index
DBCC SHOW_STATISTICS (Employes, IX_Employes_Departement);
GO

-- Mettre à jour les statistiques

UPDATE STATISTICS Employes;
GO

Exemple: Création et Gestion d’Index

-- Créer une table
CREATE TABLE Clients_Test
(
ClientID INT PRIMARY KEY,
NomClient VARCHAR(100) NOT NULL,
Email VARCHAR(100),
Telephone VARCHAR(20),
Ville VARCHAR(50)
);
GO

-- Créer plusieurs index

CREATE INDEX IX_Email ON Clients_Test(Email);
GO
CREATE INDEX IX_Ville ON Clients_Test(Ville);
GO

CREATE INDEX IX_Nom_Ville

ON Clients_Test(NomClient, Ville);
GO

-- Vérifier les index

SELECT
name AS NomIndex,
type_desc AS TypeIndex
FROM [Link]
WHERE object_id = OBJECT_ID('Clients_Test')
AND name IS NOT NULL;
GO

Vues: Requêtes Pré-enregistrées

Définition d’une Vue
Une vue est une requête pré-enregistrée qui peut être utilisée comme une table virtuelle.
Elle ne stocke pas les données, seulement la requête.

Avantages des Vues

• Simplification des requêtes complexes

• Sécurité: masquer les colonnes sensibles

• Réutilisabilité

• Maintenance facilitée

Création de Vues
Vue Simple
CREATE VIEW VueClients AS
SELECT ClientID, NomClient, Email
FROM Clients
WHERE Actif = 1;
GO

Vue Complexe
CREATE VIEW VueCommandes_Details AS
SELECT
[Link],
[Link],
[Link],
[Link],
 SUM([Link] * [Link]) AS MontantTotal
FROM Clients c
INNER JOIN Commandes com ON [Link] = [Link]
INNER JOIN Details_Commandes dcmd ON [Link] = [Link]
GROUP BY [Link], [Link], [Link], [Link];
GO

Utilisation des Vues

-- Utiliser une vue comme une table
SELECT * FROM VueClients;
GO

-- Filtrer sur une vue

SELECT * FROM VueClients WHERE NomClient LIKE 'D%';
GO

Modification et Suppression de Vues

-- Modifier une vue (remplacer le contenu)
ALTER VIEW VueClients AS
SELECT ClientID, NomClient, Email, Telephone
FROM Clients
WHERE Actif = 1;
GO

-- Supprimer une vue

DROP VIEW VueClients;
GO

-- Supprimer si elle existe

DROP VIEW IF EXISTS VueClients;
GO

Exemple: Création Complète d’une Vue

-- Créer les tables
CREATE TABLE Clients_V
(
ClientID INT PRIMARY KEY IDENTITY(1,1),
NomClient VARCHAR(100),
Email VARCHAR(100),
Actif BIT DEFAULT 1
);
GO

-- Insérer des données

INSERT INTO Clients_V (NomClient, Email)
VALUES
('Dupont Jean', 'jean@[Link]'),
('Martin Sophie', 'sophie@[Link]'),
('Durand Pierre', 'pierre@[Link]');
GO
-- Créer une vue
CREATE VIEW Vue_Clients_Actifs AS
SELECT ClientID, NomClient, Email
FROM Clients_V
WHERE Actif = 1;
GO

-- Utiliser la vue
SELECT * FROM Vue_Clients_Actifs;
GO

Procédures Stockées: Code Réutilisable

Introduction aux Procédures Stockées
Une procédure stockée est un bloc de code T-SQL pré-compilé et stocké dans la base de
données. Elle peut être exécutée à plusieurs reprises.

Avantages des Procédures Stockées

• Performance améliorée (pré-compilée)

• Sécurité (contrôle d’accès)

• Réutilisabilité du code

• Réduction du trafic réseau

• Maintenance centralisée

Création de Procédures Stockées

Procédure Simple
CREATE PROCEDURE sp_Afficher_Clients
AS
BEGIN
SELECT * FROM Clients;
END;
GO

-- Exécuter la procédure
EXEC sp_Afficher_Clients;
GO

Procédure avec Paramètres

CREATE PROCEDURE sp_Afficher_Client_Par_ID
@ClientID INT
AS
BEGIN
 SELECT * FROM Clients
WHERE ClientID = @ClientID;
END;
GO

-- Exécuter avec paramètre

EXEC sp_Afficher_Client_Par_ID 5;
GO

Procédure avec Paramètres de Sortie

CREATE PROCEDURE sp_Compter_Clients
@Total INT OUTPUT
AS
BEGIN
SELECT @Total = COUNT(*) FROM Clients;
END;
GO

-- Exécuter et récupérer le résultat

DECLARE @NombreClients INT;
EXEC sp_Compter_Clients @NombreClients OUTPUT;
SELECT @NombreClients AS 'Nombre de Clients';
GO

Contrôle du Contexte d’Exécution

CREATE PROCEDURE sp_Inserer_Client_Securise
@NomClient VARCHAR(100),
@Email VARCHAR(100)
AS
BEGIN
-- Vérifier les paramètres
IF @NomClient IS NULL OR @Email IS NULL
BEGIN
RAISERROR('Les paramètres ne peuvent pas être NULL', 16, 1);
RETURN -1;
END;

-- Insérer le client
INSERT INTO Clients (NomClient, Email)
VALUES (@NomClient, @Email);

-- Retourner le succès
RETURN @@IDENTITY;
END;
GO

Exemple: Procédure Stockée Complète

-- Créer une procédure pour ajouter un employé
CREATE PROCEDURE sp_Ajouter_Employe
@NomEmploye VARCHAR(100),
 @Email VARCHAR(100),
@Salaire DECIMAL(10,2),
@EmployeID INT OUTPUT
AS
BEGIN
BEGIN TRY
-- Vérifier les paramètres
IF LEN(@NomEmploye) = 0
BEGIN
RAISERROR('Le nom de l''employé est obligatoire', 16, 1);
RETURN -1;
END;

-- Insérer l'employé
INSERT INTO Employes (NomEmploye, Email, Salaire)
VALUES (@NomEmploye, @Email, @Salaire);

-- Récupérer l'ID généré

SET @EmployeID = @@IDENTITY;

SELECT 'Employé ajouté avec succès' AS Message;

END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS 'Message d''Erreur';
RETURN -1;
END CATCH;
END;
GO

-- Exécuter la procédure
DECLARE @NouvauID INT;
EXEC sp_Ajouter_Employe
@NomEmploye = 'Leclerc Marc',
@Email = 'marc@[Link]',
@Salaire = 4000,
@EmployeID = @NouvauID OUTPUT;

SELECT @NouvauID AS 'ID du Nouvel Employé';

GO

Déclencheurs (Triggers): Automatisation

Conception et Implémentation des Triggers
Un déclencheur est un bloc de code qui s’exécute automatiquement en réponse à une action
(INSERT, UPDATE, DELETE) sur une table.
Types de Déclencheurs
• AFTER INSERT: S’exécute après l’insertion

• AFTER UPDATE: S’exécute après la modification

• AFTER DELETE: S’exécute après la suppression

• INSTEAD OF: Remplace l’action originale

Création de Déclencheurs
Trigger Simple
-- Créer une table de journalisation
CREATE TABLE Journal_Modifications
(
JournalID INT PRIMARY KEY IDENTITY(1,1),
NomTable VARCHAR(50),
TypeAction VARCHAR(20),
DateModification DATETIME DEFAULT GETDATE()
);
GO

-- Créer un trigger
CREATE TRIGGER tr_Clients_Insert
ON Clients
AFTER INSERT
AS
BEGIN
INSERT INTO Journal_Modifications (NomTable, TypeAction)
VALUES ('Clients', 'INSERT');
END;
GO

Trigger avec Vérification

CREATE TRIGGER tr_Employes_Salaire
ON Employes
AFTER UPDATE
AS
BEGIN
-- Vérifier que le salaire n'a pas diminué de plus de 50%
IF EXISTS (
SELECT 1 FROM inserted i
JOIN deleted d ON [Link] = [Link]
WHERE [Link] < ([Link] * 0.5)
)
BEGIN
RAISERROR('Diminution de salaire trop importante', 16, 1);
ROLLBACK;
END;
END;
GO

Exemple: Trigger Complet de Journalisation

-- Créer une table pour enregistrer les changements
CREATE TABLE Audit_Clients
(
AuditID INT PRIMARY KEY IDENTITY(1,1),
ClientID INT,
Action VARCHAR(20),
AncienneValeur VARCHAR(MAX),
NouvelleValeur VARCHAR(MAX),
DateAction DATETIME DEFAULT GETDATE()
);
GO

-- Créer un trigger pour UPDATE

CREATE TRIGGER tr_Clients_Update
ON Clients
AFTER UPDATE
AS
BEGIN
INSERT INTO Audit_Clients (ClientID, Action, AncienneValeur,
NouvelleValeur)
SELECT
[Link],
'UPDATE',
[Link] + ' - ' + [Link],
[Link] + ' - ' + [Link]
FROM inserted i
JOIN deleted d ON [Link] = [Link];
END;
GO

-- Créer un trigger pour DELETE

CREATE TRIGGER tr_Clients_Delete
ON Clients
AFTER DELETE
AS
BEGIN
INSERT INTO Audit_Clients (ClientID, Action, AncienneValeur)
SELECT
[Link],
'DELETE',
[Link] + ' - ' + [Link]
FROM deleted d;
END;
GO
Transactions: Garantir l’Intégrité
Concepts des Transactions
Une transaction est une unité de travail qui doit être complètement exécutée ou
complètement annulée.

Propriétés ACID
• Atomicité: Tout ou rien

• Cohérence: État valide

• Isolement: Pas d’interférence

• Durabilité: Persistance

Gestion des Transactions

BEGIN TRAN, COMMIT, ROLLBACK
-- Transaction simple
BEGIN TRAN
INSERT INTO Clients (NomClient, Email)
VALUES ('Nouveau Client', 'nouveau@[Link]');

INSERT INTO Commandes (ClientID, DateCommande)

VALUES (SCOPE_IDENTITY(), GETDATE());
COMMIT;
GO

-- Transaction avec gestion d'erreur

BEGIN TRY
BEGIN TRAN
UPDATE Employes SET Salaire = Salaire + 500
WHERE Departement = 'IT';
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK;
SELECT ERROR_MESSAGE() AS 'Erreur';
END CATCH;
GO

SAVE TRANSACTION
BEGIN TRAN
INSERT INTO Clients VALUES ('Client 1', 'c1@[Link]');
SAVE TRAN Etape1;

INSERT INTO Clients VALUES ('Client 2', 'c2@[Link]');

SAVE TRAN Etape2;
 -- Si erreur, revenir à Etape1
-- ROLLBACK TRAN Etape1;

COMMIT;
GO

Exemple: Transaction Complète

-- Transférer un montant entre deux comptes
BEGIN TRY
BEGIN TRAN
-- Débiter le compte source
UPDATE Comptes
SET Solde = Solde - 1000
WHERE CompteID = 1;

-- Vérifier le solde
IF (SELECT Solde FROM Comptes WHERE CompteID = 1) < 0
BEGIN
RAISERROR('Solde insuffisant', 16, 1);
END;

-- Créditer le compte destination

UPDATE Comptes
SET Solde = Solde + 1000
WHERE CompteID = 2;

-- Enregistrer la transaction
INSERT INTO Historique_Transactions (Montant, DateTransaction)
VALUES (1000, GETDATE());

COMMIT;
SELECT 'Transfert réussi' AS Message;
END TRY
BEGIN CATCH
ROLLBACK;
SELECT ERROR_MESSAGE() AS 'Message d''Erreur';
END CATCH;
GO

Sécurité des Données

Authentification
SQL Server supporte deux modes d’authentification:
• Mode Windows: Authentification du système d’exploitation

• Mode SQL Server: Authentification avec login/mot de passe

Comptes de Connexion
-- Créer un login
CREATE LOGIN NomLogin WITH PASSWORD = 'MotDePasse123!';
GO

-- Créer un utilisateur pour la base de données

USE MaBase;
GO
CREATE USER NomUtilisateur FOR LOGIN NomLogin;
GO

-- Supprimer un utilisateur
DROP USER NomUtilisateur;
GO

-- Supprimer un login
DROP LOGIN NomLogin;
GO

Permissions (GRANT, DENY, REVOKE)

GRANT: Accorder des Permissions
-- Accorder SELECT sur une table
GRANT SELECT ON Clients TO NomUtilisateur;
GO

-- Accorder plusieurs permissions

GRANT SELECT, INSERT, UPDATE ON Commandes TO NomUtilisateur;
GO

-- Accorder exécution d'une procédure

GRANT EXECUTE ON sp_Afficher_Clients TO NomUtilisateur;
GO

DENY: Refuser des Permissions

-- Refuser DELETE
DENY DELETE ON Clients TO NomUtilisateur;
GO

REVOKE: Retirer des Permissions

-- Retirer les permissions
REVOKE SELECT ON Clients FROM NomUtilisateur;
GO

Exemple: Gestion des Utilisateurs et Permissions

-- Créer un login
CREATE LOGIN Utilisateur_Ventes WITH PASSWORD = 'Passe123456!';
GO
-- Créer un utilisateur
USE Entreprise;
GO
CREATE USER Utilisateur_Ventes FOR LOGIN Utilisateur_Ventes;
GO

-- Accorder les permissions

GRANT SELECT ON Clients TO Utilisateur_Ventes;
GRANT SELECT ON Commandes TO Utilisateur_Ventes;
GRANT SELECT ON Produits TO Utilisateur_Ventes;
GO

-- Exécuter des procédures

GRANT EXECUTE ON sp_Afficher_Clients TO Utilisateur_Ventes;
GO

-- Refuser la suppression
DENY DELETE ON Clients TO Utilisateur_Ventes;
GO

Exercices Récapitulatifs Complets

Exercice 1: Gestion d’une Petite Bibliothèque
Créez une base de données complète pour gérer une bibliothèque avec:
• Table des Livres (LibreID, Titre, Auteur, ISBN, Disponible)

• Table des Emprunts (EmpruntID, LivreID, DateEmprunt, DateRetour)

• Table des Adhérents (AdherentID, Nom, Prenom, Email, DateAdhésion)

Objectifs:
1. Créer la structure

2. Insérer des données

3. Créer une vue des livres disponibles

4. Créer une procédure pour emprunter un livre

Solution: Exercice 1: Gestion d’une Petite Bibliothèque

-- Créer la base de données
CREATE DATABASE Bibliotheque;
GO

USE Bibliotheque;
GO
-- Créer la table des Adhérents
CREATE TABLE Adherents
(
AdherentID INT PRIMARY KEY IDENTITY(1,1),
NomAdherent VARCHAR(100) NOT NULL,
PrenomAdherent VARCHAR(100) NOT NULL,
Email VARCHAR(100),
DateAdhesion DATETIME DEFAULT GETDATE()
);
GO

-- Créer la table des Livres

CREATE TABLE Livres
(
LivreID INT PRIMARY KEY IDENTITY(1,1),
Titre VARCHAR(200) NOT NULL,
Auteur VARCHAR(100) NOT NULL,
ISBN VARCHAR(20) UNIQUE,
Disponible BIT DEFAULT 1
);
GO

-- Créer la table des Emprunts

CREATE TABLE Emprunts
(
EmpruntID INT PRIMARY KEY IDENTITY(1,1),
LivreID INT NOT NULL,
AdherentID INT NOT NULL,
DateEmprunt DATETIME DEFAULT GETDATE(),
DateRetour DATETIME,
FOREIGN KEY (LivreID) REFERENCES Livres(LivreID),
FOREIGN KEY (AdherentID) REFERENCES Adherents(AdherentID)
);
GO

-- Insérer des adhérents

INSERT INTO Adherents (NomAdherent, PrenomAdherent, Email)
VALUES
('Dupont', 'Marie', '[Link]@[Link]'),
('Martin', 'Jean', '[Link]@[Link]'),
('Durand', 'Sophie', '[Link]@[Link]');
GO

-- Insérer des livres

INSERT INTO Livres (Titre, Auteur, ISBN)
VALUES
('Les Misérables', 'Victor Hugo', '978-2-253-05656-0'),
('Notre-Dame de Paris', 'Victor Hugo', '978-2-253-13186-9'),
('Le Seigneur des Anneaux', 'J.R.R. Tolkien', '978-2-2540-5636-
6'),
 ('Harry Potter', 'J.K. Rowling', '978-2-07-042639-8');
GO

-- Créer une vue des livres disponibles

CREATE VIEW Vue_Livres_Disponibles AS
SELECT LivreID, Titre, Auteur, ISBN
FROM Livres
WHERE Disponible = 1;
GO

-- Créer une procédure pour emprunter

CREATE PROCEDURE sp_Emprunter_Livre
@LivreID INT,
@AdherentID INT
AS
BEGIN
BEGIN TRY
-- Vérifier que le livre est disponible
IF NOT EXISTS (SELECT 1 FROM Livres WHERE LivreID = @LivreID
AND Disponible = 1)
BEGIN
RAISERROR('Le livre n''est pas disponible', 16, 1);
RETURN -1;
END;

BEGIN TRAN
-- Enregistrer l'emprunt
INSERT INTO Emprunts (LivreID, AdherentID)
VALUES (@LivreID, @AdherentID);

-- Marquer le livre comme non disponible

UPDATE Livres
SET Disponible = 0
WHERE LivreID = @LivreID;
COMMIT;

SELECT 'Emprunt réussi' AS Message;

END TRY
BEGIN CATCH
ROLLBACK;
SELECT ERROR_MESSAGE() AS 'Erreur';
END CATCH;
END;
GO

-- Tester la procédure
EXEC sp_Emprunter_Livre 1, 1;
GO

-- Afficher les livres disponibles

SELECT * FROM Vue_Livres_Disponibles;
GO

Exercice 2: Système de Gestion de Stock

Créez un système de gestion de stock avec:
• Table des Produits

• Table des Mouvements de Stock (Entrée/Sortie)

• Procédure pour ajouter/retirer du stock

• Trigger pour mettre à jour le stock automatiquement

Solution: Exercice 2: Système de Gestion de Stock

-- Créer la base de données
CREATE DATABASE GestionStock;
GO

USE GestionStock;
GO

-- Créer la table des Produits

CREATE TABLE Produits
(
ProduitID INT PRIMARY KEY IDENTITY(1,1),
NomProduit VARCHAR(100) NOT NULL,
Prix DECIMAL(10,2) CHECK (Prix > 0),
Stock INT DEFAULT 0,
StockMinimum INT DEFAULT 10
);
GO

-- Créer la table des Mouvements

CREATE TABLE Mouvements_Stock
(
MouvementID INT PRIMARY KEY IDENTITY(1,1),
ProduitID INT NOT NULL,
Quantite INT NOT NULL,
TypeMouvement VARCHAR(20) NOT NULL, -- 'ENTREE' ou 'SORTIE'
DateMouvement DATETIME DEFAULT GETDATE(),
Raison VARCHAR(200),
FOREIGN KEY (ProduitID) REFERENCES Produits(ProduitID)
);
GO

-- Insérer des produits

INSERT INTO Produits (NomProduit, Prix, Stock, StockMinimum)
VALUES
 ('Chaise', 45.99, 100, 20),
('Bureau', 199.99, 50, 10),
('Lampe', 29.99, 75, 15);
GO

-- Créer un trigger pour mettre à jour automatiquement le stock

CREATE TRIGGER tr_Mouvements_Stock_Insert
ON Mouvements_Stock
AFTER INSERT
AS
BEGIN
-- Augmenter ou diminuer le stock
UPDATE Produits
SET Stock = Stock +
CASE
WHEN [Link] = 'ENTREE' THEN [Link]
WHEN [Link] = 'SORTIE' THEN -[Link]
ELSE 0
END
FROM Produits p
JOIN inserted i ON [Link] = [Link];

-- Vérifier le stock négatif

IF EXISTS (SELECT 1 FROM Produits WHERE Stock < 0)
BEGIN
RAISERROR('Le stock ne peut pas être négatif', 16, 1);
ROLLBACK;
END;
END;
GO

-- Créer une procédure pour ajouter du stock

CREATE PROCEDURE sp_Ajouter_Stock
@ProduitID INT,
@Quantite INT
AS
BEGIN
IF @Quantite <= 0
BEGIN
RAISERROR('La quantité doit être positive', 16, 1);
RETURN -1;
END;

INSERT INTO Mouvements_Stock (ProduitID, Quantite, TypeMouvement,

Raison)
VALUES (@ProduitID, @Quantite, 'ENTREE', 'Approvisionnement');
END;
GO

-- Créer une procédure pour retirer du stock

CREATE PROCEDURE sp_Retirer_Stock
@ProduitID INT,
@Quantite INT
AS
BEGIN
IF @Quantite <= 0
BEGIN
RAISERROR('La quantité doit être positive', 16, 1);
RETURN -1;
END;

INSERT INTO Mouvements_Stock (ProduitID, Quantite, TypeMouvement,

Raison)
VALUES (@ProduitID, @Quantite, 'SORTIE', 'Vente');
END;
GO

-- Tester les procédures

EXEC sp_Ajouter_Stock 1, 50;
GO

EXEC sp_Retirer_Stock 1, 20;

GO

-- Afficher le stock actuel

SELECT
NomProduit,
Stock,
CASE
WHEN Stock < StockMinimum THEN 'Stock Faible'
ELSE 'Stock OK'
END AS Statut
FROM Produits;
GO

-- Afficher l'historique des mouvements

SELECT * FROM Mouvements_Stock
ORDER BY DateMouvement DESC;
GO