Toutes les commandes SQL server que vous aves besoin dans l'exam de Fin de Formation

/************************/
/* Adil el hajiz */
/************************/

/*Creation de la base de donnée Simple*/

CREATE DATABASE TEST on default=1
GO
USE TEST1
/*Pour supprimer une base de donnée :*/
use master
DROP DATABASE TEST

/*CRÉER UNE BASE DE DONNÉE Avancée( paramétrer les différents fichiers):*/
CREATE DATABASE TEST
ON PRIMARY
( NAME = TEST_dat,
FILENAME = 'C:\program files\Microsoft SQL Server\mssql\data\TEST_dat.mdf',
SIZE = 10,
MAXSIZE = 50, FILEGROWTH = 5 %) LOG ON
( NAME = TEST_log,
FILENAME = 'C:\program files\Microsoft SQL Server\mssql\data\TEST_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )

/*Pour utiliser une base de donnée :*/

USE TEST

/*MODIFIER LA STRUCTURE D’UNE BD :*/

--Pour ajouter un fichier supplémentaire :

ALTER DATABASE TEST
ADD FILE
(
NAME =TEST_dat2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TEST_dat2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)

--Supprimer un fichier de BD :

ALTER DATABASE TEST
REMOVE FILE TEST_dat2

--Pour modifier l’un des fichiers d’une BD (Modification du Journal par exemple) :

ALTER DATABASE TEST
MODIFY file (Name=TEST_log, MAXSIZE = 80MB)
-- Pour renommer une BD:
Sp_renamedb 'TEST','BASE_2'

/*Creation des tables*/

/*CREATION DE LA TABLE PERE*/
CREATE TABLE PERE
(codep int,
nomp varchar,
age int,
constraint pk_PERE primary key (codep));

/*CREATION DE LA TABLE FILS*/
CREATE TABLE FILS
(codef int,
nomf varchar,
sexe varchar,
agef int,
codep int,
constraint pk_FILS primary key (codef),
constraint fk_FILS foreign key (codep)references PERE (codep));

/*Pour ajouter un type utilisateur :*/
Sp_addtype telephone, 'varchar(24)', 'NOT NULL'
--Pour supprimer une table :
DROP TABLE FILS;

--/*CLÉ PRIMAIRE / SECONDAIRE :*/---------------------
--Pour Créer une table avec un clé primaire (un seul champ) :

CREATE TABLE Personnes (n° int identity(1,1), nom char(12) default 'BTH',
CONSTRAINT CP primary key (n°)) ;

--Pour Créer une table un clé primaire (2 champs ou plus) :

CREATE TABLE Profession (n° integer, code char(8), prof varchar,
CONSTRAINT CP2 primary key (n°, code)) ;
-- Pour créer une table avec un clé étrangère :

CREATE TABLE Bus (n° integer, code char(8), prof varchar,
CONSTRAINT CE1 foreign key (n°) REFERENCES Etudiant (n°);

--Pour supprimer une contrainte (clé primaire, clé étrangère, condition, not null, unique) :

ALTER TABLE Bus
DROP CONSTRAINT CE1 ;

--Pour Ajouter un clé étrangère (Modification) :

ALTER TABLE Bus
ADD CONSTRAINT CE1 foreign key (n°) REFERENCES Etudiant (n°);
--Pour Créer une table avec une condition sur un champ :
CREATE TABLE Salles
(n° integer, désignation char(15),
CONSTRAINT C1 check (designation like 'Salle%')
)

--MODIFIER LA STRUCTURE DE TABLE :
--« Ajout column »
ALTER TABLE Etudiant
ADD adresse char(30), age integer ;
--« Suppression column»
ALTER TABLE Etudiant
DROP nom, note ;
--Modification column
ALTER TABLE Etudiant
MODIFY nom char(15) ;

--ACTIVER / DÉSACTIVER UNE CONTRAINTE :
--Pour désactiver une contrainte :

ALTER TABLE BUS NOCHECK CONSTRAINT C1

--Pour réactiver une contrainte :

ALTER TABLE BUS CHECK CONSTRAINT C1
CRÉATION DES VUES :

--Pour créer une Vue à partir d’une table :

CREATE VIEW Etudiant_Admis ( n°, nom, date_naiss, note) AS
SELECT(n°, nom, date_naiss, note)
FROM Etudiant
WHERE note>10 ;

--Pour supprimer une vue :

DROP VIEW Etudiant_Admis ;

--CRÉATION DES INDEX :
--Pour créer un index sur 2 champs d’une table :
CREATE INDEX Index1
ON Etudiant (n°, nom);

--Pour créer un index en forçant l’unicité sur un champ :

CREATE UNIQUE CLUSTERED INDEX Index2
ON Bus (société);

--Pour supprimer un index :

DROP INDEX Index2
--RENOMMER DES OBJETS :

--Pour renommer un objet (table, vue, procédure stocké, trigger, contrainte, règles …)

Sp_rename ‘Etudiant’, 'Stagiaires', ‘Object’ --Pour renommer une colonne d’une table :
Sp_rename 'Etudiant.nom', 'Nom_E', 'COLUMN' --Pour renommer un index :
Sp_rename 'Index1', 'Index01', 'INDEX'

--Pour renommer un type utilisateur :

Sp_rename 'telephone', 'phone', 'USERDATATYPE'

-------------/*Définition des enregistrements dans les Tables :*/-----------------------------
--AJOUTER DES ENREGISTREMENTS :
--1ère méthode :

INSERT INTO Etudiant (n°, nom, note) VALUES (5,'Mounir', 12) ;

--2ème méthode :

INSERT INTO Etudiant VALUES (3, 'Amine', '15/08/1985', 13.25) ;

INSERT INTO Etudiant VALUES (7, 'Meriem', null, 14) ;

INSERT INTO Bus VALUES (5, 63,'medina') ;

INSERT Bus VALUES (5, 20,'raha') ;

--Pour insérer des enregistrements à partir d’une autre table :

INSERT INTO Etudiant (n°, nom)
SELECT n°, nom
FROM Personnes
WHERE nom like 'n%' ;

--MISE À JOUR DES ENREGISTREMENTS :

UPDATE Etudiant
SET n°=6, nom='adil' WHERE n°=5 ;

UPDATE Etudiant
SET note=13.26 WHERE n°=6 ;

UPDATE Bus
SET société='medina' WHERE no_bus=70 ;

UPDATE Articles
SET prix = prix * 0.25

--SUPPRIMER DES ENREGISTREMENTS :

DELETE FROM Etudiant WHERE n°=6--« Suppression des enregistrements précis »

DELETE FROM Bus --« Suppression de toutes les enregistrements »

--------/*Requêtes (Select) :*/----------------------
1) SELECT n°, nom
FROM Etudiant
WHERE note>10
GROUP BY nom
HAVING nom like ' _J%'
ORDER BY note

2)SELECT nom, AVG (note) FROM Etudiant ;

SELECT nom, AVG (note AS Note_Moyenne) FROM Etudiant ;

SELECT nom FROM Etudiant WHERE note IN ( SELECT MAX (note) FROM Etudiant) ;
3)SELECT n°, count (DISTINCT note)
FROM Etudiant
GROUP BY n° DESC;
4)
SELECT nom, no_bus
FROM Etudiant
WHERE n° IN ( SELECT n° FROM Bus WHERE société='casa') ;
5)
/*Ancienne :*/

SELECT Etudiant.nom, Bus.no_bus
FROM Etudiant, Bus WHERE Etudiant.n° = Bus.n° AND Bus.société = ‘casa’ ;

/*Actuelle :*/

SELECT Etudiant.nom, Bus.no_bus
FROM Etudiant INNER JOIN Bus ON Etudiant.n° = Bus.n°
WHERE Bus.société = 'casa' ;
--AUTO JOINTURE :
SELECT Etudiant.nom, Etudiant2.note
FROM Etudiant INNER JOIN Etudiant AS Etudiant2 ON Etudiant.note = Etudiant2.note
WHERE Etudiant.n°<> Etudiant2.n° ;

--REQUÊTES PLUS PRÉCISES :

LIKE ‘%[M-R]’ Finissent par M, N, O, P, Q ou R
LIKE ‘%[FMR]%’ ne contenant ni F ni M ni R
TOP 10 Affiche les 10 premières lignes du résultat
-----------/*Fonctions SQL Server :*/---------------------------

--Pour ajouter un nombre à une partie de la date :

DATEADD (Partie_Date, nombre, colonne)
DATEADD (month, 5, datenaiss)

--Pour soustrait la date de fin de celle de départ :

DATEDIFF (Partie_Date, Date_départ, Date_fin)
DATEDIFF (year, datenaiss, Gatedate())

--Pour retourner une chaîne de caractères d’une partie de la date :

DATENAME (Partie_Date, colonne)
DATENAME (month, datenaiss) Pour retourner une partie d’une date :
DATEPART (Partie_Date, colonne)
DATEPART (day, colonne)

--Ou utiliser :

Day (colonne) Month (colonne) Year (colonne)

--Formats (Partie_Date) :

dddd, mmmm dd, yyyy hh:mm:ss.ffff tt
Friday, December 28, 2001 11:16:41.0021 AM
FONCTIONS CHAÎNES :

--Pour concaténer deux chaînes ou plus :

CONCAT (chaine1, chaine2)
CONCAT (Nom, Prénom)

--Pour extraire une partie du chaîne :

SUBSTR (colonne, Position_départ, longueur)
SUBSTR (Nom, 1, 1)

--Pour remplacer une valeur dans une chaîne par une autre valeur :

REPLACE (colonne, valeur1, valeur2)
REPLACE (Nom, ‘Mb’, ‘Mn’)

--Pour convertir une chaîne en majuscule / minuscule :

UPPER (chaîne) ou LOWER (chaîne)
UPPER (Nom) ou LOWER (Prénom)

--Pour retourner la longueur de la chaîne de caractères :

LENGTH (chaîne)
LENGTH (Nom)

--Pour retourner la valeur ASCII d'un jeu de caractères indiqué :

ASCII (jeu_caractères)
ASCII (Nom)

--FONCTIONS MATHÉMATIQUES :

--Pour retourner la valeur absolue d’une expression :

ABS (Expression)
ABS (Remise)

--Pour retourner la puissance de l'expression (ExpressionExposant) :

POWER (Expression, Exposant)
POWER (Prix, 2)

--Pour retourner la racine carrée de l'expression :

SQRT (Expression)
SQRT (Remise)

FONCTIONS CHAÎNES :

--Pour concaténer deux chaînes ou plus :

CONCAT (chaine1, chaine2)
CONCAT (Nom, Prénom)

--/*nformations supplémentaires :*/------------
--COMMENTAIRES :

/*Pour ajouter un commentaire sur une seule ligne :*/

-- c’est un commentaire

Pour ajouter un commentaire de plusieurs lignes :

/* c’est
Un
Commentaire */

--------------Administration de Sql server 2000----------------------------------
-----/*creation de connexion*/------------------------------
--1)Connexion Windows
sp_grantlogin 'HACKEUR\Adil'
--Pr changer la base donnée par default--
sp_defaultdb 'HACKEUR\Adil','TEST1'
--Pr Refuser L acces-----
sp_denylogin 'HACKEUR\Adil'

--2)Connexion Sql
sp_addlogin 'test','123456','TEST1','french'
--Pr supprimer
sp_droplogin 'test'

--Creation de comptes d'utilisteur----
--Pr cree un utilisateur qui porte le méme nom que la connexion--
sp_grantdbaccess 'HACKEUR\Adil'
sp_grantdbaccess 'test'
--Pr supprimer
sp_revokedbaccess 'HACKEUR\Adil'
sp_revokedbaccess 'test'

---creation des utilisateur simple
sp_adduser 'HACKEUR\Adil','Adil'
sp_adduser 'test','user'
--Pr supprimer
sp_dropuser 'Adil'
sp_dropuser 'user'

--Pour autoriser les connexions sans compte d'utilisateur associé à accéder à BD :
Sp_grantdbaccess 'guest'

--Creation de Role
sp_addrole 'group1'
sp_droprole 'group1'
---Ajouter des user a un role Standart
sp_addrolemember 'group1','Adil'
sp_addrolemember 'group1','user'
--pr supprimer
sp_droprolemember 'group1','Adil'
---Ajouter des user a un role sys
sp_addsrvrolemember 'HACKEUR\Adil','sysadmin'
sp_dropsrvrolemember 'HACKEUR\Adil','sysadmin'
--Creation des role Application (Sécurité) :
sp_addapprole 'roleapplication','12345'
--pr Activer le role d'application
sp_setapprole 'roleapplication','12345'
--Pr supprimer
sp_dropapprole 'roleapplication'

--------------/*Autorisations SQL :*/------------------------
--AUTORISATIONS SUR DES OBJETS :

--Pour autoriser la sélection sur une table (Etudiant) :

Grant Select ON Etudiant TO adil

--Pour empêcher les autres instructions à 2 utilisateurs:

Deny Insert, Update, Delete ON Etudiant TO adil, [FST.com\adil]

--Pour autoriser la modification mais seulement sur un champ (nom de l’étudiant) :

Grant Update (nom) ON Etudiant TO adil

--Pour autoriser un rôle d’insérer sur une table (Etudiant) :

Grant Insert ON Etudiant TO Réseau

--Les objets sont :

Select, Insert, Update, Delete, References sur une table ou vue. Select, Update, References sur une colonne.
Exec sur une procédure stocké.


--AUTORISATIONS SUR DES INSTRUCTIONS :

--Pour autoriser la création des tables et des vues :

Grant Create table, Create view TO elhajizadil

--Pour empêcher la création des bases de données :

Deny Create database TO adil

--Pour supprimer (Lever les autorisations et les empêchements) :

REVOKE Create database, Create view TO adil

GRANT ALL TO adil

--Les instructions sont :

CREATE DATABASE, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE,
CREATE RULE, CREATE DEFAULT, CREATE FUNCTION, BACKUP DATABASE, BACKUP LOG.

--AUTORITÉ POUR DONNER DES AUTORISATIONS :

--Pour autoriser Namiro à faire la sélection sur la table Etudiant, ainsi de la possibilité de la donner à d’autre utilisateurs :

GRANT SELECT ON Etudiant TO Réseau
WITH GRANT OPTION

--Le rôle Réseau à l’autorité de faire Select sur la table Etudiant, adil est un membre du rôle Réseau, Pour que Namiro puisse donner des autorisations Select sur Etudiant :

GRANT SELECT ON Etudiant TO Util1 AS Réseau

-----------/*Sauvegarde & Restauration :*/-----------------

--CRÉATION D’UNITÉS DE SAUVEGARDE :

--Pour créer une unité de sauvegarde nommé US1 sur un disque :

Sp_addumpdevice 'DISK', 'US1', '\\servername\sharename\path\filename.bak'
--Pour créer une unité sur un emplacement physique d’une bande magnétique:
Sp_addumpdevice 'TAPE', 'US2', '\\.\tape0'


--SAUVEGARDER UNE BD :

--Pour sauvegarder une BD sans unité de sauvegarde permanente :

BACKUP DATABASE Bse_1 TO DISK = 'C:\Temp\Mycustomers.bak'
--Pour sauvegarder une BD sur 2 unités de sauvegarde en même temps :
BACKUP DATABASE Bse_1 TO US1, US2 WITH MEDIANAME = US

--Pour effectuer une sauvegarde complète d’une BD sur une unité avec description :

BACKUP DATABASE Base_1 TO US1 WITH DESCRIPTION = ‘Première sauvegarde’
--Pour effectuer une sauvegarde complète d’une BD sur une unité en écrasant le précédent :
BACKUP DATABASE Base_1 TO US1 WITH INIT

--Pour effectuer une sauvegarde différentielle d’une BD sur une unité :

BACKUP DATABASE Base_1 TO US1 WITH DIFFERENTIAL

--Pour sauvegarder le journal des transactions :

BACKUP LOG Base_1 TO US2

--Pour supprimer la partie inactive d'un journal sans faire de copie de sauvegarde :

BACKUP LOG Base_1 WITH TRUNCATE_ONLY

--Pour supprimer la partie inactive d'un journal plein sans en faire de copie de sauvegarde.

BACKUP LOG Base_1 WITH NO_LOG

--Pour sauvegarder un fichier ou un groupe de fichier :

BACKUP DATABASE Base_1
FILE = Base_1dat TO US1
BACKUP LOG Base_1 to US2


--VÉRIFIER UNE SAUVEGARDE AVANT RESTAURATION :

--Pour obtenir les informations d'en-tête d'un fichier de sauvegarde particulier :

RESTORE HEADERONLY

--Pour obtenir des informations sur les fichiers de base de données ou de journal :

RESTORE FILELISTONLY

--Pour obtenir des informations sur le support de sauvegarde contenant un fichier :

RESTORE LABELONLY

--Pour assurer que les différents fichiers constituant le jeu de sauvegardes sont complets :

--RESTORE VERIFYONLY


--/*RESTAURER UNE BD :*/----

--Pour restaurer une BD à partir d’une unité de sauvegarde :

USE master
RESTORE DATABASE Base_1
FROM US1

--Pour restaurer une BD à partir de plusieurs unités de sauvegarde (2 par exemple) :

USE master
RESTORE DATABASE Base_1
FROM US1
WITH NORECOVERY pas de validation

RESTORE DATABASE Base_1
FROM US2 --validation (WITH RECOVERY par default)
--Pour restaurer une sauvegarde spécifique (l’un des fichiers existant sur une unité) :
USE master
RESTORE DATABASE Base_1
FROM US1
WITH FILE = 2, RECOVERY Restaurer la deuxième sauvegarde.

--Pour restaure tous les enregistrements du journal des transactions écrits dans la base de données avant un certain moment défini :

USE master
RESTORE DATABASE Base_1
FROM US1
WITH NORECOVERY

RESTORE LOG Base_1
FROM US2
WITH FILE = 1, NORECOVERY

RESTORE LOG Base_1
FROM US2
WITH FILE = 2, RECOVERY, STOPAT = '3 janvier 2000, 01:00'

----------/**ATTACHER / DÉTACHER UNE BD :**/'''''''''

--Pour détacher une BD (pas en cours d’exécution) :

Sp_detach_db 'BASE_1'

--Pour attacher une BD (mdf + ldf) :

Sp_attach_db @dbname = 'BASE_1',
@filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\fst_dat.mdf',
@filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\fst_1log.ldf'
--Pour attacher une base de données ne contenant qu'un seul fichier de données :
Sp_attach_single_file_db @dbname = fst_1,
@physname = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\base_1dat.mdf'


/**PROPRIÉTAIRE DE BD :*/

--Pour modifier le propriétaire de la base de données courante :

Sp_changedbowner ‘adil’

p
---------------------/**********et Merci el hajiz adil Group 1 licence PRO************/--------------------------------------