Outils pour utilisateurs

Outils du site


windows:mssql

SQL Server 2008 : Gestionnaire de base de données

Présentation

Microsoft SQL Server est un système de gestion de base de données (abrégé en SGBD ou SGBDR pour « Système de gestion de base de données relationnelles ») développé et commercialisé par la société Microsoft.
SQL Server 2008
Pour les requêtes, SQL Server utilise T-SQL (Transact-SQL), il s'agit d'une implémentation de SQL qui prend en charge les procédures stockées et les déclencheurs (trigger).
SQL Server est un SGBD relationnel. Il est possible de définir des relations entre les tables de façon à garantir fortement l'intégrité des données qui y sont stockées. Ces relations peuvent être utilisées pour modifier ou supprimer en chaîne des enregistrements liés.
SQL Server est un SGBD transactionnel. Il est capable de préparer des modifications sur les données d'une base et de les valider ou de les annuler d'un bloc. Cela garantit l'intégrité des informations stockées dans la base.

Fichiers

Les bases de données sont contenues physiquement dans des fichiers. Les fichiers portent généralement les extensions :

  • MDF (Main Database File) pour le premier fichier de données
  • NDF (Next Database File) pour les autres fichiers de données
  • LDF (Log Database File) pour les fichiers du journal de transaction

Les fichiers de données et journaux sont stockés dans :

C:\ProgramFiles\Microsoft\SQLServer\MSSQL10.MSSQL\MSSQL\DATA

Microsoft Transact SQL

SQL

  • SQL : Structured Query Language
  • SGBDR : Systèmes de Gestion de Base de Données Relationnelles

Le Transact SQL est un language de requêtes amélioré par rapport au SQL dont il reprend les bases. Le SQL est le language standard, créé par un ingénieur d'IBM dans les années 70 pour la gestion des SGBDR.

Il existe 3 catégories d'instructions :

LDD : Language de Définition des Données

Il permet la création, modification et suppression des objets SQL (Base, Tables, Index, Vues, Procédures..)

Créer CREATE
Modifier ALTER
Supprimer DROP

LMD : Language de Modification des Données

Il fournit les instructions de création, mise à jour, suppression et extraction des données stockées. Le LMD vise à modifier une ligne caractérisé par des attributs. (Ensemble de n-upplets)

Créer INSERT
Modifier UPDATE
Supprimer DELETE
Interroger SELECT

LCA : Language de Contrôle d'accès

Il permet la gestion des accès aux aux données, des transactions et de la configuration des sessions et des bases.

SQL Server Management Studio

SQL Server Management Studio est l'interface d'administration, accessible avec l'authentification Windows (compte administrateur) ou avec l'authentification interne (compte sa):

Console d'administration

Points forts de l'interface :

  • Le shell avec coloration syntaxique et complétion pour intéragir sur la serveur
  • La possibilité de créer un schéma relationnel
  • L'aide local et en ligne à la programmation

Les types de données

MSSQL

Lors de la définition d'une colonne, on précisera le format d'utilisation de la donnée ainsi que le mode de stockage par le type de colonne.

Caractères :

char (n) Chaine de caractères de longueur fixe
varchar (n) Chaine de caractères à langueyr variable de n caractères maximum

Numériques :

decimal (p,d) numérique exact de précision p(nombre de chiffres total) et d =0 par défaut(nombre de chiffres après la virgule)
numeric (p,d) identique à décimal
bigint entier codé sur 8 octets. Compris entre -2exp63 et 2exp63-1
int entier compris entre -2exp31 et 2exp31
smallint nombre entier compris entre -2exp15 et 2exp15-1
tinyint nombre entier positif entre 0 et 255
float (n) numérique approché de n chiffres, n allant de 1 à 53
real identique à float(24)
money numérique au format monétaire sur 8 octets
smallmoney numérique au format monétaire sur 4 octets

Date :

datetime permet une date et heure sur 8 octets
smalldatetime permet une date et heure sur 4 octets
datetime2 plus précis que datetime avec une précision de 100 nanosecondes
date permet de stocker une date
time permet de stocker une donnée positive < 24h avec une précision de 100 nanosecondes

Spéciaux :

bit valeur entière pouvant prendre les valeurs 0, 1 ou null
timestamp donnée dont la valeur est mis à jour automatiquement lorsque la ligne est inséré/modifié
uniqueidentifier permet de créer un identificateur unique en s'appuyant sur la fonction NEWID()

Intégrité des données

Data integrity

  • La mise en œuvre de l'intégrité des données peut se faire de manière procédurale par les valeurs par défaut (DEFAULT) et les déclencheurs (TRIGGER) ou de manière interactive par les contraintes (CONSTRAINT) et la propriété IDENTITY.



L'intégrité des données traduit les règles du modèle relationnel, règle de cohérence (intégrité de domaine), existence de valeurs nulles, règle d'unicité (intégrité d'identité) et clés étrangères (intégrité référentielle).

IDENTITY

Permet de faire générer par le système un ID partant d'une valeur initiale (spécifiée ou 1 par défaut) en augmentant/diminuant ligne après ligne. IDENTITY se définie sur une colonne dans une commande de création (CREATE TABLE) ou de mise à jour (ALTER TABLE)

  • Exemple :

CREATE TABLE TEST (
         identifiant int IDENTITY (1000,1),
         label varchar (30)
         )

  • La variable @@IDENTITY stocke la dernière valeur affectée par une identié au cours de la session courante.
  • Pour pouvoir insérer des données sans utiliser la numérotation automatique, il faut faire appel à l'instruction :

SET IDENTITY_INSERT nom_2_table ON

Contraintes d'intégrité

NOT NULL

Spécifie que la colonne doit être valorisé à la création/modification.

  • Exemple :

CREATE TABLE TEST (
       nom        varchar (30) not null,
       prenom     varchar (30) 
);

  • RAPPEL : la valeur par défaut d'une colonne est NULL (accepte les champs non valorisés)

PRIMARY KEY

Cette contrainte permet de définir un identifiant clé primaire, une ou plusieurs colonnes n'acceptant que des valeurs uniques dans la table.

  • Exemple :

CREATE TABLE TEST (
       numclient    numeric (6) 
                    CONSTRAINT primary key,
       nom        varchar (30) not null,
       prenom     varchar (30) 
);

  • Une PK peut-être définie sur plusieurs colonnes à condition de poser la contrainte sur la table !

CREATE TABLE GRILLETARIFS (
	codeCate	char (5),
	codeFami	char (5),
	codeTarif	char (5),
	-- contraintes de table --
	constraint FK_codeTarif foreign key (codeTarif) references TARIFS(codeTarif),
	constraint PK_grilletarifs primary key (codeCate,codeFami)
);

UNIQUE

Cette contrainte permet de traduire la règle d'unicité pour les autres clés uniques d'une table (clé secondaire). Contrairement à une PK, il est possible d'avoir plusieurs UNIQUE par table et les colonnes utilisées peuvent être à NULL (non recommandé).

  • Exemple :

CREATE TABLE FAMILLES (
	codeFami	char (5)
				constraint PK_codeFami primary key,
	libelle		varchar (80)
				constraint UN_familles_libelle UNIQUE
);

FOREIGN KEY

Cette contrainte traduit l'intégrité référentielle entre une clé étrangère d'une table et une clé primaire (ou secondaire) d'une autre table.

  • Exemple :

create table GRILLETARIFS (
	codeCate	char (5),
	codeFami	char (5),
	codeTarif	char (5),
	-- contraintes de table --
	constraint FK_codeDate foreign key (codeCate) references CATEGORIES(codeCate),
	constraint FK_codeFami foreign key (codeFami) references FAMILLES(codeFami),
	constraint FK_codeTarif foreign key (codeTarif) references TARIFS(codeTarif),
	constraint PK_grilletarifs primary key (codeCate,codeFami)
);

L'option de cascade permet de préciser le comportement que doit adopter SQL Server lorsque l'utilisateur met à jour ou tente de supprimer une colonne référencée.
Les clauses ON DELETE et ON UPDATE sont suivies d'une de ces options :

  • NO ACTION
  • ON DELETE CASCADE
  • ON UPDATE CASCADE
  • SET NULL
  • SET DEFAULT

DEFAULT

Cette contrainte permet de préciser la valeur qui va être positionnée dans la colonne si aucune information n'est précisée lors de l'insertion de la ligne.

  • Exemple :

CREATE TABLE LIGNESFIC (
	nofic		numeric (6) not null,
	nolig		numeric (3) not null,
	refart		char (8),
	depart		datetime not null default getdate(),
	retour		datetime null,
	-- contraintes de table --
	constraint PK_LIGNESFIC primary key (nofic,nolig),
	constraint FK_nofic foreign key (nofic) references FICHES(nofic),
	constraint FK_refart foreign key (refart) references ARTICLES(refart),
	constraint CK_depart check (retour is NULL or retour >= depart)

CHECK

La contrainte de validation permet de définir des règles mettant en rapport des valeurs issues de différentes colonnes de la même ligne. Ce type de contrainte permet aussi de s'assurer que les données respectent un format d'entrée précis lors de l'insertion et mise à jour.

  • Exemple:

CREATE TABLE FICHES (
	nofic		numeric (6) constraint PK_nofic primary key,
	nocli		numeric (6) not null,
	datcreation	datetime not null default getdate(),
	datretour	datetime null, 	
	etat		char (2) not null
				constraint CHOIX_etat check (etat in ('EC', 'RE', 'SO'))
				constraint DF_etat default 'EC',
	-- contraintes de table --
	constraint FK_nocli foreign key (nocli) references CLIENTS(nocli),
	constraint CK_date check (datretour is NULL or datretour >= datcreation),
	constraint CK_fiches_date_etat CHECK(datretour is NULL and etat = 'EC'
									OR datretour is not NULL and etat <> 'EC')
);

Exemple

  • Schéma relationnel

Exemple de schéma

  • Script de création de base et tables

/****************************************/
/*         SQL Server 2008              */
/*       Création bdd et tables         */
/****************************************/
-- définition de la database --
create database TP_sport ;

GO

-- séléction de la table --
use TP_sport;

GO

-- définition des tables --

-- création table CLIENTS --
create table CLIENTS (
	nocli		numeric (6) constraint PK_nocli primary key,
	nom			char (30) not null,
	prenom		varchar (30) not null,
	adresse		varchar (120) not null,
	cpo			char (5) not null,
	ville       char (80) not null constraint DF_ville default 'NANTES',
	-- contraintes de table --
	constraint CK_cpo check (cpo between 1000 and 95999)
);

GO

-- création table FICHES --
create table FICHES (
	nofic		numeric (6) constraint PK_nofic primary key,
	nocli		numeric (6) not null,
	datcreation	datetime not null default getdate(),
	datretour	datetime null, 	
	etat		char (2) not null
				constraint CHOIX_etat check (etat in ('EC', 'RE', 'SO'))
				constraint DF_etat default 'EC',
	-- contraintes de table --
	constraint FK_nocli foreign key (nocli) references CLIENTS(nocli),
	constraint CK_date check (datretour is NULL or datretour >= datcreation),
	constraint CK_fiches_date_etat CHECK(datretour is NULL and etat = 'EC'
									OR datretour is not NULL and etat <> 'EC')
);

GO

-- création table CATEGORIES --
create table CATEGORIES (
	codeCate	char (5)
				constraint PK_codeDate primary key,
	libelle		varchar (80)
				constraint UN_categories_libelle UNIQUE
);

GO

-- création table FAMILLE --
create table FAMILLES (
	codeFami	char (5)
				constraint PK_codeFami primary key,
	libelle		varchar (80)
				constraint UN_familles_libelle UNIQUE
);

GO

-- création table TARIFS --
create table TARIFS (
	codeTarif	char (5) not null
				constraint PK_codeTarif primary key,
	libelle		varchar (80) not null,
	codeDevise	char (2) not null,
	prixJour	numeric (10,2) not null
				constraint CK_prixJour check (prixJour >= 0)
);

GO

-- création table GRILLETARIFS --
create table GRILLETARIFS (
	codeCate	char (5),
	codeFami	char (5),
	codeTarif	char (5),
	-- contraintes de table --
	constraint FK_codeDate foreign key (codeCate) references CATEGORIES(codeCate),
	constraint FK_codeFami foreign key (codeFami) references FAMILLES(codeFami),
	constraint FK_codeTarif foreign key (codeTarif) references TARIFS(codeTarif),
	constraint PK_grilletarifs primary key (codeCate,codeFami)
);

GO

-- création table ARTICLES --
create table ARTICLES (
	refart		char (8)
				constraint PK_refart primary key,
	designation	varchar (80),
	codeCate	char (5),
	codeFami	char (5),
	-- contraintes de table --
	constraint FK_grilletarifs foreign key (codeCate,codeFami) references GRILLETARIFS(codeCate,codeFami)
);

Go

-- création table LIGNESFIC --
create table LIGNESFIC (
	nofic		numeric (6) not null,
	nolig		numeric (3) not null,
	refart		char (8),
	depart		datetime not null default getdate(),
	retour		datetime null,
	-- contraintes de table --
	constraint PK_LIGNESFIC primary key (nofic,nolig),
	constraint FK_nofic foreign key (nofic) references FICHES(nofic),
	constraint FK_refart foreign key (refart) references ARTICLES(refart),
	constraint CK_depart check (retour is NULL or retour >= depart)
);

windows/mssql.txt · Dernière modification: 15/04/2016 à 21:15 (modification externe)

Outils de la page