Création de base de données

create database DonneesTest;

Connexion à la base de données

use BaseTest;

Création de tables

create table employe
(
    idEmploye int primary key not null,
    nomEmploye varchar(16) null,
    genre nvarchar(2) CHECK(genre='Masculin' or genre='Féminin'),
    poste varchar(16) null,
    dateEmbauche datetime null,
    salaire int null,
    commission int null,
    idDepartement int null,
    idManager int null
);

• Création d'une table avec auto-incrémentation

create table etudiant
(
    idEtudiant int identity(1001,1) primary key not null,
    nomEtudiant varchar(200) not null,
);

• Définition d'une clé primaire (si non définie lors de la création)

alter table employe add constraint PK_idEmploye primary key (idEmploye);

Insertion de données

insert into employe values (7001,'JUDY',    'Ingénieur',    1981-1-2,2000, 200, 10, 7006);

insert into etudiant values ('Jean Dupont');

insert into etudiant (nomEtudiant,age) values ('Tom',12);

Mise à jour de données

update employe set nomEmploye = 'Jacques' where idEmploye = 8975

Suppression

• Suppression de toutes les lignes d'une table (après suppression, l'auto-incrémentation recommence à zéro)

truncate table etudiant;

• Suppression de toutes les lignes d'une table (après suppression, l'auto-incrémentation continue à partir de la dernière valeur)

delete from etudiant;

• Suppression d'une table

drop table etudiant;

• Suppression d'une vue

drop vue v$_employe_1;

• Suppression d'une procédure stockée

drop proc pr_Noms; 

• Suppression d'une fonction personnalisée

drop fonction f_plage_salaire

Requêtes

• Colonnes calculées

select nomEmploye, salaire "Salaire mensuel", salaire*12 as "Salaire annuel", poste from employe; -- Colonnes calculées

• distinct [sans doublons]

select distinct idDepartement from employe; -- distinct idDepartement supprime les doublons
select distinct commission from employe; -- distinct peut aussi supprimer les doublons NULL
select distinct idDepartement, commission from employe; -- Combine idDepartement et commission pour filtrer les doublons

• Recherche des employés ayant un salaire entre 1500 et 3000 (inclus)

select * from employe where salaire between 1500 and 3000;

• in [appartient à plusieurs valeurs isolées]

select * from employe where salaire in (1600, 3000); -- salaire égal à 1600 ou 3000
select * from employe where salaire not in (1600, 3000); -- salaire différent de 1600 et 3000
select * from employe where salaire!=1600 and salaire!=3000; -- salaire différent de 1600 et 3000
select * from employe where salaire<>1600 and salaire<>3000; -- salaire différent de 1600 et 3000 ("<>" est différent, recommandé)

• top

select top 2 * from employe; -- Sélectionne les 2 premiers
select top 15 percent * from employe; -- Sélectionne les 15% premiers
-- Sélectionne les deux salaires les plus élevés entre 1000 et 3000 (asc = ascendant, valeur par défaut omissible; desc = descendant)
select top 2 * from employe where salaire between 1000 and 3000 order by salaire desc

• null Afficher les employés ayant une commission non nulle

select * from employe where commission <> null; -- Erreur! Résultat vide error
select * from employe where commission != null; -- Erreur! Résultat vide error
select * from employe where commission = null; -- Erreur! Résultat vide error
-- Résumé: null ne peut pas participer aux opérations <> , != , =
-- null peut participer à is , not is
select * from employe where commission is null;
select * from employe where commission is not null;

-- Afficher le nom et le salaire annuel (incluant la commission) de chaque employé, en supposant que commission est une commission annuelle
select idEmploye,nomEmploye,salaire*12+commission from employe; -- Lorsque commission est NULL, le résultat est NULL, et NULL ne peut pas participer aux opérations mathématiques

• order by Tri

select * from employe order by salaire; -- Tri par salaire ascendant (équivalent à: select * from employe order by salaire asc)
select * from employe order by salaire desc; -- Tri par salaire descendant
select * from employe order by idDepartement, salaire; -- Tri d'abord par idDepartement ascendant, puis par salaire ascendant pour les mêmes idDepartement
select * from employe order by idDepartement, salaire desc; -- Tri d'abord par idDepartement ascendant, puis par salaire descendant pour les mêmes idDepartement

• Requête floue ("%" : correspond à 0 ou plusieurs caractères, "_" : correspond à un caractère)

select * from employe where nomEmploye like '%A%'; -- Recherche les noms contenant "A"
select * from employe where nomEmploye like 'A%'; -- Recherche les noms commençant par "A"
select * from employe where nomEmploye like '%A'; -- Recherche les noms se terminant par "A"
select * from employe where nomEmploye like '_A%'; -- Recherche les noms contenant "A" avec exactement un caractère avant
select * from employe where nomEmploye like '_[A-F]%'; -- Affiche les enregistrements où le deuxième caractère est entre A et F
select * from employe where nomEmploye like '_[A,F]%'; -- Affiche les enregistrements où le deuxième caractère est A ou F
select * from employe where nomEmploye like '_[^A-C]%'; -- Affiche les enregistrements où le deuxième caractère n'est pas A, B ou C
select * from employe where nomEmploye like '%\%%' escape '\'; -- Affiche les enregistrements où le nom contient "%" (escape définit le caractère d'échappement)
select * from employe where nomEmploye like '%a%%' escape 'a'; -- Affiche les enregistrements où le nom contient "%" (escape définit le caractère d'échappement)
select * from employe where nomEmploye like '%\_%' escape '\'; -- Affiche les enregistrements où le nom contient "_" (escape définit le caractère d'échappement)

• Fonctions d'agrégation

select lower(nomEmploye) from employe; -- Convertit tous les noms en minuscules, une ligne par résultat (fonction mono-ligne)
select max(salaire) from employe; -- Valeur maximale du salaire, une ligne pour plusieurs résultats (fonction multi-lignes)
select count(*) from employe; -- Retourne le nombre total d'enregistrements de la table employe
select count(idDepartement) from employe; -- Retourne le nombre d'enregistrements non nuls pour idDepartement
select count(distinct idDepartement) from employe; -- Retourne le nombre d'enregistrements non nuls et uniques pour idDepartement
select max(salaire) "Salaire maximum", min(salaire) "Salaire minimum", count(*) "Nombre total" from employe;

• group by

select idDepartement,avg(salaire) as "Salaire moyen du département" from employe group by idDepartement; -- Affiche l'identifiant de chaque département et son salaire moyen
-- Groupe d'abord par idDepartement, puis par poste pour les mêmes idDepartement
select idDepartement, poste as "Poste", avg(salaire) as "Salaire moyen du groupe", count(*) as "Nombre de personnes" 
from employe 
group by idDepartement, poste 
order by idDepartement; 

• having

-- Affiche l'identifiant et le salaire moyen des départements ayant un salaire moyen supérieur à 1500
select idDepartement, avg(salaire) as "Salaire moyen" 
from employe 
group by idDepartement 
having avg(salaire) > 1500;

Jointures internes

• Utilisation de select ... from A, B

select * from employe, departement;
-- Résultat: crée une nouvelle table, le nombre de lignes est le produit des lignes de A et B, le nombre de colonnes est la somme des colonnes de A et B (produit cartésien)

• Utilisation de select ... from A, B where ...

select * from employe, departement where idEmploye = 7369;
-- Résultat: produit cartésien filtré par la condition where
select * from employe, departement where employe.idDepartement = departement.idDepartement;

• Utilisation de select ... from A join B on ...

select "E".nomEmploye 'Nom de l''employé', "D".nomDepartement 'Nom du département', "D".localisation 'Adresse'
    from employe "E"
    inner join departement "D" -- join est la jointure, inner peut être omis
    on "E".idDepartement="D".idDepartement; -- on est la condition de jointure ( on 1=1 équivaut à omettre la condition de jointure)

select *
    from employe "E"
    join departement "D" -- join est la jointure
    on "E".idDepartement="D".idDepartement
    where "E".salaire>1500;

• Jointures internes multi-tables

select "E".nomEmploye "Employé", "E".salaire "Salaire", "S".niveau "Niveau de salaire"
    from employe "E"
    join departement "D"
    on "E".idDepartement = "D".idDepartement
    join niveau_salaire "S"
    on "E".salaire >= "S".salaire_min and "E".salaire <= "S".salaire_max;

Exercicse

-- Exercice 1: Trouver le nom, l'identifiant du département, le salaire et le niveau de salaire de chaque employé
select "E".nomEmploye "Nom", "E".idDepartement "Département", "E".salaire "Salaire", "S".niveau "Niveau de salaire"
    from employe "E"
    join niveau_salaire "S"
    on "E".salaire between "S".salaire_min and  "S".salaire_max;

-- Exercice 2: Trouver l'identifiant de chaque département, le salaire moyen de tous les employés de ce département et le niveau de salaire moyen
select "T".idDepartement, "T".salaire_moyen "Salaire moyen du département", "S".niveau "Niveau de salaire moyen","D".nomDepartement "Nom du département"
    from (
        select "E".idDepartement, avg("E".salaire) as "salaire_moyen"
        from employe "E"
        group by "E".idDepartement
    ) "T"
    join niveau_salaire "S"
    on "T".salaire_moyen between "S".salaire_min and "S".salaire_max
    join departement "D"
    on "T".idDepartement = "D".idDepartement;

-- Exercice 3: Trouver le nom du département ayant le salaire moyen le plus élevé et ce salaire moyen
select "D".nomDepartement "Département", "T".salaire_moyen "Salaire moyen"
    from (
        select top 1 avg(salaire) as "salaire_moyen", "E".idDepartement
        from employe "E"
        group by idDepartement
        order by avg(salaire) desc
    ) "T"
    join departement "D"
    on "T".idDepartement = "D".idDepartement

-- Exercice 4: Afficher les informations des trois employés ayant les salaires les plus bas parmi ceux ayant un salaire supérieur au minimum
select "T".nomEmploye, "T".salaire,"T".idDepartement,"D".nomDepartement,"S".niveau
    from(
        select top 3 * 
        from employe
        where salaire > (select min(salaire) from employe)
        order by salaire asc
    ) "T"
    join departement "D"
    on "T".idDepartement = "D".idDepartement
    join niveau_salaire "S"
    on "T".salaire between "S".salaire_min and "S".salaire_max;

-- Ou
select top 3 "T".nomEmploye, "T".salaire,"T".idDepartement,"D".nomDepartement,"S".niveau
    from(
        select  * 
        from employe
        where salaire > (select min(salaire) from employe)
    ) "T"
    join departement "D"
    on "T".idDepartement = "D".idDepartement
    join niveau_salaire "S"
    on "T".salaire between "S".salaire_min and "S".salaire_max
    order by "T".salaire asc;

Jointures externes

• Jointure gauche, jointure externe gauche (retourne toutes les lignes de la table gauche, les colonnes correspondantes de la table droite affichent NULL s'il n'y a pas de correspondance)

select *
    from departement "D"
    left outer join employe "E" -- outer peut être omis
    on "E".idDepartement="D".idDepartement;

• Jointure droite, jointure externe droite (retourne toutes les lignes de la table droite, les colonnes correspondantes de la table gauche affichent NULL s'il n'y a pas de correspondance)

select *
    from departement "D"
    right outer join employe "E" -- outer peut être omis
    on "E".idDepartement="D".idDepartement;

Jointures complètes

L'ensemble de résultats contient trois parties :

1.Toutes les lignes correspondantes dans les deux tables 2.Les lignes de la table gauche qui n'ont pas de correspondance dans la table droite, ces lignes ont NULL pour toutes les colonnes de droite 3.Les lignes de la table droite qui n'ont pas de correspondance dans la table gauche, ces lignes ont NULL pour toutes les colonnes de gauche

select * from employe "E"<strong>
    full</strong> outer join departement "D" -- outer peut être omis
    on "E".idDepartement = "D".idDepartement;

Jointures croisées

Sans clause where, retourne le produit cartésien des deux tables Avec clause where, équivalent à une jointure interne retournant les données correspondantes

select * from employe E
    cross join departement D
    where E.idDepartement = D.idDepartement;

Auto-jointures

• Utilisation d'une fonction d'agrégation pour trouver les informations de l'employé ayant le salaire le plus élevé

select * from employe where salaire = (select max(salaire) from employe);

• Sans fonction d'agrégation pour trouver les informations de l'employé ayant le salaire le plus élevé

select * from employe
    where idEmploye not in (
        select distinct "E1".idEmploye
        from employe "E1"
        join employe "E2"
        on "E1".salaire < "E2".salaire
    );

Unions (connexion verticale entre tables)

Afficher le nom de chaque employé, son salaire et le nom de son supérieur

select "E1".nomEmploye, "E1".salaire, "E2".nomEmploye
    from employe E1
    join employe E2
    on "E1".idManager = "E2".idEmploye
union
select nomEmploye, salaire, 'Déjà plus haut dirigeant' from employe where idManager is null;

Vues

Afficher l'identifiant du département ayant le salaire moyen le plus bas et ce salaire moyen

• Sans vue

select *
    from (
    select idDepartement, avg(salaire) "salaire_moyen"
        from employe
        group by idDepartement
    ) "T"
    where "T"."salaire_moyen" = (
        select min("E"."salaire_moyen") from(
            select idDepartement, avg(salaire) "salaire_moyen"
                from employe
                group by idDepartement
        ) "E"
    ) 

• Avec vue

create view v$_employe_1
as
    select idDepartement, avg(salaire) "salaire_moyen"
        from employe
        group by idDepartement

select * from v$_employe_1
    where salaire_moyen = (select min(salaire_moyen) from v$_employe_1)

Transactions

Transactions: principalement utilisées pour garantir la cohérence des données et la capacité de traitement concurrent

1.Éviter que les données ne se trouvent dans un état intermédiaire non cohérent (comme un virement) 2.Les transactions résolvent de nombreux problèmes par le verrouillage

create table banque
(
    nomClient nvarchar(200),
    soldeActuel money
)
insert into banque values('Tom', 1000)
insert into banque values('Jack', 1)

alter table banque add constraint check_solde check(soldeActuel>1)
update banque set soldeActuel = soldeActuel-1000 where nomClient = 'Tom'
update banque set soldeActuel = soldeActuel+1000 where nomClient = 'Jack'

begin transaction
declare @erreurSomme int
set @erreurSomme = 0
update banque set soldeActuel = soldeActuel - 1000
    where nomClient='Tom'
set @erreurSomme = @erreurSomme + @@error
update banque set soldeActuel = soldeActuel + 1000
    where nomClient='Jack'
set @erreurSomme = @erreurSomme + @@error
if(@erreurSomme<>0)
    begin
        print 'Échec du virement'
        rollback transaction
    end

Conversion de types

select 1+2; -- 3
select 'a'+'b'; -- 'ab'
select 'abc' + 2; -- error
select 'abc' + convert(varchar,2); -- 'abc2'
select convert(int,'abc') + 2; -- error
select convert(varchar(10), getdate(), 126); -- 2022-06-25 (format de date, 126 représente un format)
select 'abc' + cast(2 as varchar); -- 'abc2'
select cast('2021-1-23' as datetime); -- 2021-01-23 00:00:00.000

Opérations sur les chaînes de caractères

select charindex('bc','abcdbc'); -- 2 (retourne la position de la première occurrence de la sous-chaîne)
select patindex('%bc%','abcdbc'); -- 2 (retourne la position de la première occurrence de la sous-chaîne, la sous-chaîne doit être entourée de %)
select upper('abcdefg'); -- ABCDEFG (minuscules vers majuscules)
select lower('ABCDEFG'); -- abcdefg (majuscules vers minuscules)
select len('abc'); -- 3 (longueur)
select ltrim('   a bc   '); -- Supprime les espaces à gauche
select rtrim('   a bc   '); -- Supprime les espaces à droite
select left('abcdefg',4); -- abcd (retourne les 4 premiers caractères à partir de la gauche)
select right('abcdefg',4); -- defg (retourne les 4 derniers caractères)
select substring('abcdefg', 3,2); -- cd (retourne une chaîne de 2 caractères à partir de la position 3)
select replicate('abc',4); -- abcabcabcabc (répète la chaîne 4 fois pour créer une nouvelle chaîne)
select reverse('abcdefg'); -- gfedcba (inversion de la chaîne)
select replace('abcdefg','cd','xx'); -- abxxefg (remplacement partiel de la chaîne)
select stuff('acccefg', 2,3,'x'); -- axefg (remplace une sous-chaîne de 3 caractères à partir de la position 2 par 'x')

Index (par défaut non groupés)

• Index groupé

create clustered index PK_Banque 
on banque(soldeActuel)
with
(
    drop_existing=on -- Si cet index existe déjà, il est supprimé avant d'être recréé
    --drop_existing=off -- Ne supprime pas l'existing, affiche une erreur, l'index existe déjà
)

• Index non groupé

create unique nonclustered index uq_employe -- Index unique non groupé
on employe(nomEmploye)
with
(
    pad_index=on,
    fillfactor=100,  -- Facteur de remplissage (spécifie le pourcentage de l'espace de la page d'index occupé par les données lors de la création de l'index)
    ignore_dup_key=on
)

• Index composite: index créé sur plusieurs colonnes

create nonclustered index index_employe -- Index non groupé
on employe(nomEmploye,poste)
with
(
    drop_existing=on
)

Procédures stockées

• Création d'une procédure stockée sans paramètres

create proc recherche_salaire
as
begin
    select idEmploye,nomEmploye,poste,salaire from employe
    select * from banque
end;

• Suppression d'une procédure stockée

drop proc recherche_salaire

• Création d'une procédure stockée avec paramètres

create proc ajouter_employe
@idEmploye int,
@nomEmploye varchar(16),
@poste varchar(16),
@salaire int,
@idDepartement int,
@idManager int
as 
begin
    declare @temps datetime
    set @temps = getdate()
    insert into employe
    (idEmploye,nomEmploye,poste,salaire,idDepartement,idManager)
    values(@idEmploye,@nomEmploye,@poste,@salaire,@idDepartement,@idManager)
end;

--Appel
exec ajouter_employe 7651,'Li Fei','Vendeur', 3000, 20, 7777

Déclencheurs (un type spécial de procédure stockée, déclenché par événement, exécuté automatiquement)

Classification: Déclencheurs DDL (create alter drop); Déclencheurs DML (insert update delete) Les tables logiques sont des tables virtuelles, créées en mémoire par le système, non stockées dans la base de données, en lecture seule, on peut lire mais pas modifier les données. La structure est identique à celle de la table manipulée. Le déclencheur existe pendant son exécution et peut être accédé, une fois le travail terminé, la table est automatiquement supprimée

• after / insert Déclencheur (alert trigger trigger_employe_insert)

create trigger trigger_employe_insert 
on employe
for insert -- for after insert
as
    declare @id int, @nom varchar(16) -- Définition de variables locales
    select @id=idEmploye,@nom=nomEmploye from inserted
    print convert(varchar,@id)+''+@nom+'ajouté avec succès';

-- Déclenchement de trigger_employe_insert
insert into employe (idEmploye,nomEmploye,poste,salaire,commission,idDepartement,idManager) 
    values (7011,'Zhang San','Administrateur',2800,100,10,7001);

• after / update Déclenhceur

create trigger trigger_employe_update 
on employe
for update
as
    declare @id int, @nom varchar(16), @montant int -- Définition de variables locales
    select @id=idEmploye,@nom=nomEmploye,@montant=salaire from deleted
    print 'Avant modification'+convert(varchar,@id)+''+@nom+','+convert(varchar,@montant);
    select @id=idEmploye,@nom=nomEmploye,@montant=salaire from inserted
    print 'Après modification'+convert(varchar,@id)+''+@nom+','+convert(varchar,@montant);

-- Déclenchement de trigger_employe_update
update employe set nomEmploye='Wang Wu', salaire=2999 where idEmploye=7011;

Conversion ligne-colonne

• Ligne vers colonne

-- 1. Création d'une table de base pour la conversion ligne-colonne
create table test
(
    id int identity(1,1) not null,
    nomEtudiant varchar(255) null,
    matiere varchar(255) null,
    note int null
);

-- 2. Insertion de données
insert into test (nomEtudiant, matiere, note) values ('Zhang San', 'Français', 66);
insert into test (nomEtudiant, matiere, note) values ('Zhang San', 'Mathématiques', 54);
insert into test (nomEtudiant, matiere, note) values ('Zhang San', 'Anglais', 63);
insert into test (nomEtudiant, matiere, note) values ('Li Si', 'Français', 75);
insert into test (nomEtudiant, matiere, note) values ('Li Si', 'Mathématiques', 89);
insert into test (nomEtudiant, matiere, note) values ('Li Si', 'Anglais', 88);
insert into test (nomEtudiant, matiere, note) values ('Wang Wu', 'Français', 69);
insert into test (nomEtudiant, matiere, note) values ('Wang Wu', 'Mathématiques', 86);
insert into test (nomEtudiant, matiere, note) values ('Wang Wu', 'Anglais', 98);
insert into test (nomEtudiant, matiere, note) values ('Wang Wu', 'Physique', 72);
insert into test (nomEtudiant, matiere, note) values ('Wang Wu', 'Chimie', 65);

-- 3. Méthode 1 pour ligne vers colonne: utilisation de case when then else, on peut aussi utiliser sum
select nomEtudiant,
    max(case matiere when 'Français' then note else 0 end) as francais,
    max(case matiere when 'Mathématiques' then note else 0 end) as mathematiques,
    max(case matiere when 'Anglais' then note else 0 end) as anglais,
    max(case matiere when 'Physique' then note else 0 end) as physique,
    max(case matiere when 'Chimie' then note else 0 end) as chimie
    from test group by nomEtudiant;

-- 4. Méthode 2 pour ligne vers colonne: utilisation de la fonction pivot
select 
    nomEtudiant, 
    max(t.Français) as francais,
    max(t.Mathématiques) as mathematiques,
    max(t.Anglais) as anglais,
    max(t.Physique) as physique,
    max(t.Chimie) as chimie
    from test pivot (max(note) for matiere in(Français,Mathématiques,Anglais,Physique,Chimie)) t
    group by nomEtudiant;

• Colonne vers ligne

-- 1. Création d'une table de base pour la conversion colonne-ligne
create table test1 
(
    id int identity(1,1) not null,
    nomEtudiant varchar(255) null,
    francais int null,
    mathematiques int null,
    anglais int null,
    physique int null,
    chimie int null
)

-- 2. Insertion de données
insert into test1 values ('Ma Liu',110,120,85,null,null)
insert into test1 values ('Chen Qi',130,88,89,null,null)
insert into test1 values ('Sun Ba',93,124,87,98,67)

union: supprime les doublons dans l'ensemble de résultats****union all: ne supprime pas les doublons

-- 3. Méthode 1 pour colonne vers ligne:
select row_number() over (order by id desc) as id,nomEtudiant,t.matiere,t.note from
(
    select id,nomEtudiant,matiere='Français',note=francais from test1 union all
    select id,nomEtudiant,matiere='Mathématiques',note=mathematiques    from test1 union all
    select id,nomEtudiant,matiere='Anglais',note=anglais    from test1 union all
    select id,nomEtudiant,matiere='Physique',note=physique    from test1 union all
    select id,nomEtudiant,matiere='Chimie',note=chimie    from test1
) t where note is not null order by id asc;

-- 4. Méthode 2 pour colonne vers ligne:
select row_number() over (order by id desc)
as
    id,nomEtudiant,t.matiere,t.note
    from test1
    unpivot (note for matiere in (francais,mathematiques,anglais,physique,chimie)) a;

Fonctions personnalisées

• Fonction scalaire, retourne une valeur scalaire

create function f_salaire_moyen( @salaireMin int )
returns int
as
begin
    declare @moyenne int
    set @moyenne = (select avg(salaire) from employe where salaire < @salaireMin)
    return @moyenne
end;

select dbo.f_salaire_moyen(2000);  -- Appel de la fonction

1. Fonction de table en ligne, retourne une table

create function f_plage_salaire (@salaire_min int, @salaire_max int)
returns table
as
return (select * from employe where salaire between @salaire_min and @salaire_max);

select * from f_plage_salaire(2000, 4000);  -- Appel de la fonction (comme une requête sur une table normale)

• Fonction de table à instructions multiples (peut être considérée comme une combinaison de fonction scalaire et fonction de table en ligne)

create function fonction
(
    @poste varchar(50), 
    @salaire int
)
returns @copie table
(
    idEmploye int,
    nomEmploye varchar(50),
    poste varchar(50),
    salaire int,
    idDepartement int
)
as
begin
    declare @idDepart int
    select @idDepart=idDepartement from employe where poste=@poste or salaire=@salaire
    if @idDepart is null
        insert into @copie select idEmploye,nomEmploye,poste,salaire,idDepartement from employe
    else
        insert into @copie select idEmploye,nomEmploye,poste,salaire,idDepartement from employe where idDepartement=@idDepart
    return
end

select * from fonction('IT',2800);   -- Appel de la fonction

Clés primaires et étrangères

• Définition d'une clé primaire après la création de la table

alter table nomTable
add constraint PK_nomChamp--"PK" est l'abréviation de clé primaire, nomChamp est le champ sur lequel créer la clé primaire, 'PK_nomChamp' est le nom de la contrainte
primary key (nomChamp) --nomChamp est identique au précédent

• Contrainte d'unicité

alter table nomTable
add constraint UQ_nomChamp
unique (nomChamp)

• Contrainte de clé étrangère

alter table nomTable
add constraint FK_nomChamp--"FK" est l'abréviation de clé étrangère
foreign key (nomChamp) references tableAssociee(champAssocie) --notez 'tableAssociee' et 'champAssocie'

• Mise à jour en cascade, suppression en cascade, ainsi lors de la suppression de la table principale Student, toutes les notes de cet étudiant dans la table de notes seront supprimées.

alter table tableA add constraint FK_B foreign key (ticket_no) references tableB(ticket_no)
alter table tableA add constraint FK_C foreign key (person_no) references tableC(person_no)
alter table tableNotes add constraint FK_NumEtudiant foreign key (NumEtudiant) references Etudiant(NumEtudiant)
ON UPDATE CASCADE ON DELETE CASCADE

• Contrainte de vérification

alter table nomTable
add constraint CK_nomChamp
check (condition) --la condition dans l'expression de condition est connectée par des opérateurs relationnels

• Contrainte de valeur par défaut

alter table nomTable
add constraint DF_nomChamp
default 'valeur_par_defaut' for nomChamp--'valeur_par_defaut' est la valeur que vous voulez par défaut, notez 'for'

• Suppression d'une contrainte créée

alter table nomTable
drop constraint nomContrainte--nomContrainte est le nom que vous avez créé précédemment comme : PK_nomChamp
--note: si la contrainte a été créée lors de la création de la table, elle ne peut pas être supprimée par commande
--elle ne peut être supprimée que dans 'Enterprise Manager'

• Obtenir la structure de table dans SqlServer

SELECT syscolumns.name,systypes.name,syscolumns.isnullable,
syscolumns.length
FROM syscolumns,systypes
WHERE syscolumns.xusertype = systypes.xusertype
AND syscolumns.id = OBJECT_ID('Etudiant')

• Interroger séparément le champ d'incrémentation d'une table

SELECT [name] FROM syscolumns WHERE
id = OBJECT_ID(N'Etudiant') AND COLUMNPROPERTY(id,name,'IsIdentity')=1

• Obtenir les contraintes de clé primaire et étrangère d'une table

EXEC sp_helpconstraint 'ResultatsStu'

• Interroger les informations de clé primaire et étrangère d'une table

SELECT sysobjects.id objectId,OBJECT_NAME(sysobjects.parent_obj) tableName,
sysobjects.name constraintName, sysobjects.xtype AS constraintType,
syscolumns.name AS columnName
FROM sysobjects INNER JOIN sysconstraints
ON sysobjects.xtype in('C', 'F', 'PK', 'UQ', 'D')
AND sysobjects.id = sysconstraints.constid
LEFT OUTER JOIN syscolumns ON sysconstraints.id = syscolumns.id
WHERE OBJECT_NAME(sysobjects.parent_obj)='ResultatsStu'