Microsoft SQL Server

Un peu de connaissance

SQL : Structure Query Language

3 familles le constitu :

  • DDL -> Data Definition Language (CREATE, DROP, ALTER)
  • DML -> Data Manipulation Language (SELECT, INSERTE INTO, UPDATE, DELETE)
  • DCL -> Data Control Language (GRANT, REVOKE, DENY)

NoSQL -> Not only SQL

Définition : OLTP Vs OLAP

  • OLTP = beaucoup d’écriture
  • OLAP = beaucoup de lecture

ACID (Atomicity Consistancy Isolation Durability) (p.174 Transact SQL)

  • Atomicity -> transaction (tout ou rien)
  • Cohérence -> Cohérence des données avant et apres la transaction
  • Isolation -> les etats intermediaires sont invisibles, visible uniquement le resultat
  • Durability -> Une fois une transaction validée, le resultat de celle-ci persiste

Installation d’un moteur SQL Server :

Installation des fonctionnalités :

  • Services Moteur de base de données
  • Connectivité des outils clients
  • Outils de gestion

Configuration base de données « French CI_AS »

  • insensible à la casse (CI)
  • sensible aux accents (AS)

Commandes utiles

lien : https://sql.sh/

Création d’une table avec plusieurs champs:

CREATE TABLE nom_table (
champ_1 varchar(6) CONSTRAINT pk_table PRIMARY KEY,
champ_2 varchar(50) NOT NULL CONSTRAINT un_table_champ UNIQUE,
champ_3 tinyint NULL DEFAULT 2 CONSTRAINT ck_table_champ CHECK (valeur=3 OR valeur=2 OR valeur=1)
);

Liaison entre deux tables: FOREIGN KEY

ALTER TABLE nom_table_1 ADD CONSTRAINT fk_table_1_table_2 FOREIGN KEY(clef_primaire) REFERENCES table_2 (clef_primaire);

Contraint de plusieurs clef primaire: PRIMARY KEY

Attention la Clef primaire -> Unique et NON-Null

CONSTRAINT pk_nom_table PRIMARY KEY (clef_1, clef_2);

Contraint de checker une valeur: CHECK

CONSTRAINT ck_nom_table_1_date CHECK (datedebut < datefin));

Suppression d’une table: DROP

DROP TABLE nom_table;

Vider une table: DELETE FROM

DELETE FROM nom_table;

Afficher les valeurs d’une table: SELECT FROM

SELECT champ_1 FROM nom_table WHERE champ_2=valeur;

Insérer des valeurs dans une table: INSER INTO

INSERT INTO nom_table (champ_1, champ_2, champ_3) VALUES ('valeur_1', 'valeur_2', 'valeur_3');

Afficher le moi en lettre: DATENAME()

DATENAME(month, nom_table.date)

Faire une moyenne: AVG()

SELECT AVG(nom_table.champ) AS 'nom_champ' FROM nom_table ;

Les Jointures SQL:

INNER JOIN, LETF JOIN, RIGHT JOIN:

SELECT nom_table_1.champ_1, nom_table_1.champ_2, nom_table_2.champ_1
FROM nom_table_1
INNER JOIN nom_table_2 ON nom_table_1.champ=nom_table_2.champ
WHERE nom_table.champ=valeur AND nom_table.champ > '2019-10-02';

ORDER BY:

La commande ORDER BY permet de trier les lignes dans un résultat d’une requête SQL. Il est possible de trier les données sur une ou plusieurs colonnes, par ordre ascendant ou descendant.

ORDER BY nom_table.champ DESC ou ASC;

Sous-requete:

Dans le langage SQL une sous-requête (aussi appelé “requête imbriquée” ou “requête en cascade”) consiste à exécuter une requête à l’intérieur d’une autre requête. Une requête imbriquée est souvent utilisée au sein d’une clause WHERE ou de HAVING pou remplacer une ou plusieurs constante.

SELECT *
FROM `table`
WHERE `nom_colonne` = (
SELECT `valeur`
FROM `table2`
LIMIT 1
)

HAVING:

La condition HAVING en SQL est presque similaire à WHERE à la seule différence que HAVING permet de filtrer en utilisant des fonctions telles que SUM(), COUNT(), AVG(), MIN() ou MAX().

SELECT colonne1, SUM(colonne2)
FROM nom_table
GROUP BY colonne1
HAVING fonction(colonne2) operateur valeur

GROUP BY:

La commande GROUP BY est utilisée en SQL pour grouper plusieurs résultats et utiliser une fonction de totaux sur un groupe de résultat. Sur une table qui contient toutes les ventes d’un magasin, il est par exemple possible de liste regrouper les ventes par clients identiques et d’obtenir le coût total des achats pour chaque client.

SELECT colonne1, fonction(colonne2)
FROM table
GROUP BY colonne1

Administration BDD

BDD :

  • Fichier DATA (fichier de donnée) -> .mdf
  • Fichier LOG (journal de transaction)   -> .ldf

DML/DDL -> chargé en mémoire et inscris dans les logs

checkpoint -> mémoire
checkpoint -> LOG puis Data

Création d’une base de donnée:

CREATE DATABASE [NouvelleBase]

ON PRIMARY

( NAME = N'NouvelleBase', FILENAME = N'C:\MSSQL\DATA\NouvelleBase.mdf', SIZE = 512KB, MAXSIZE = 10485760KB, FILEGROWTH = 1048576KB)

LOG ON

( NAME = N'NouvelleBase_log', FILENAME = N'C:\MSSQL\DATA\NouvelleBase_log.ldf', SIZE = 1024KB, FILEGROWTH = 10%)

Suppression d’une base de donnée:

DROP DATABASE NouvelleBase;

la base ne doit pas être en cour d’utilisation

Avoir qu’un seul utilisateur connecter à la base immediatement:

ALTER DATABASE NouvelleBase SET single_user WITH ROLLBACK IMMEDIATE;

ajout de groupe de fichier:

ALTER DATABASE [TSQL] ADD FILEGROUP [TPTSQL]

ajout de fichier de donnée:

ALTER DATABASE [TSQL] ADD FILE ( NAME = N'TSQL_2', FILENAME = N'C:\MSsql\Data\TSQL_2.ndf' , SIZE = 5MB , FILEGROWTH = 2MB, FILEMAX = 10MB ) TO FILEGROUP [TPTSQL]

Ajout de fichier de journal:

ALTER DATABASE [TSQL] ADD FILE ( NAME = N'TSQL_LOG2', FILENAME = N'C:\MSsql\Data\TSQL_LOG2.ndf' , SIZE = 10MB , FILEGROWTH = 0) TO FILEGROUP [PRIMARY]

Modifier un fichier de données:

ALTER DATABASE [TSQL] MODIFY FILE ( NAME = N'TSQL', SIZE = 51200KB )

Réduire la taille d’un fichier de données:

DBCC SHRINKFILE (N'TSQL' , 2)

Réduire la taille de la base de données:

DBCC SHRINKDATABASE(N'TSQL', 10 )

Créer une table sur un groupe de fichiers:

Créer une fichier et l’associer au groupe de fichier

puis:

CREATE TABLE CLIENTS (
numero INT CONSTRAINT pk_clients PRIMARY KEY,
nom NVARCHAR(50) NOT NULL,
prenom NVARCHAR(50) NOT NULL,
adresse NVARCHAR(100),
codepostal CHAR(5),
ville NVARCHAR(50)
) ON TPSSMS;

Compresser les données:

CREATE TABLE ARTICLES(
refart char(10) constraint pk_articles primary key,
designation nvarchar(100),
prixht money
) WITH (DATA_COMPRESSION=ROW);

Mettre en place des index:

CREATE TABLE produits(
id int identity(1,1) constraint pk_produis primary key,
libelle nvarchar(200),
description xml,
categorie varchar(10),
marque varchar(50)
);

CREATE INDEX iproduits_marque ON produits(marque);

Indexer une colonne de type XML:

CREATE PRIMARY XML INDEX iproduits_xml ON produits(description);
GO
CREATE XML INDEX iproduits_xml_value ON produits(description) USING XML INDEX iproduits_xml FOR VALUE;

Définir un index couvrant:

CREATE INDEX iproduits_libelle ON produits(libelle) INCLUDE (marque);