SQLServer Transac SQL Philippe Bancquart EPIDCPIISAIP Philippe Bancquart

SQLServer : Transac. SQL Philippe Bancquart EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 1

Intégrité des données et tables Philippe Bancquart EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 2

Objets n Un objet d’une base porte un nom et occupe un espace n Les données sont organisées en tables. n Les tables contiennent des colonnes qui comportent des données qui ont des types, des règles et valeurs de défaut. n La BD peut contenir des vues, procédures et des triggers. EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 3

Types de données n Un type précise la nature, taille et format d’enregistrement des colonnes n Types systèmes. n Types utilisateurs. l Constitués l Assure l Liens d’un nom, type et propriété la cohérence entre les différentes colonnes. entre les règles EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 4

Types systèmes 1 octet intervalle exemples Tintyint 1 0 à 255 88 Smallint 2 +/- 32768 -23000 Int 4 +/2147483547 Numéric 2à 17 +/-10 E 38 P: nb chiffre Numeric(p, s) S: nb chiffre après virgule decimal Float 4 Double 8 real 4 Smalldatetime 4 1/1/1900 à datetime 8 6/6/2079 EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 5

Types de données 2 Nb octets intervalle Char(n) N 255 maxi Varchar(n) Longueur entrée Text(n) Multiple de 2 K money 8 Binary(n) n Image Multiple de 2 K Bit 1 255 maxi 0 ou 1 Varchar moins rapide que char EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 6

Création type utilisateur n sp_addtype nulltype] nom. Type, datatype [, ’identity’ | n Sp_droptype n Sp_addtype n Sp_help n Un ‘nom. Type’ Etat. Type, ‘char(2)’, ’not null’ nom. Type type ne peut être supprimé s’il est déjà utilisé. EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 7

Propriétés d’une colonne n La propriété peut être nulle ou non l Si valeur Null alors elle est inconnu l Exp id integer not null n Identifiant ‘identity’ : attribution automatique de valeurs numériques séquentielles à une colonnes. l Id numeric(4, 0) identity l Ne pas utiliser cette colonne dans un insert. EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 8

Création de tables Create table nom_table (nom_colonne datatype [identity|null|not null , … ) n EXERCICES : create table Ventes. Au. Detail (stor_id char(4) not null, title_id varchar(20) not null, quantite smallint not null, remise float null ) select * from Ventes. Au. Detail EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 9

Fonction d’accès à une table sp_help Donne informations sur la table Sp_rename Renomme le nom d’une table Alter table Modification des propriétés d’une table Drop table Suppression, irréversible. EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 10

Tables temporaires Elles sont utilisées pour contenir un ensemble de résultats intermédiaires, elles commencent par le signe #. Possibilité d'utiliser la commande Select into. , qui permet d’insérer directement des données à partir d’un select. n SQLServer supprime automatiquement les tables temporaires à la déconnexion mises dans tempdb n select title_id, total_orders = sum(qty) into #qty_table from sales group by title_id EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 11

Création Select Into from nom_ancienne select_list into nom_nouvelle_table where condition Le select into permet de créer et copier la structure d’une table dans une autre select * into newtitles. X from titles where pubdate < ‘ 1993 -06 -01’’ Mettre l’option à vrai, sinon autorisé, avoir les droits administrateur. sp_dboption Nom. Base , "select into" , TRUE. EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 12

Pratique Création table et type n Choix base : use Nom n Vérification : select db_name() n use pubs , select db_name. n Création table PRODUCTEUR-PB n Création d’un type pid, type char(4) et non null n N_pub_id comme identité n Pub_id de type pid n Pub_name 40 caractères n City 40 caractères n State 2 caractères EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 13

TP suite n Select * into titre. PB from titles n Select * into magasin. PB from stores n Select * into auteurs. PB from authors n Select * into remise. PB from discounts n Select * into vente. Bis. PB from vente n Consulter les scripts (3 eme icône afficher plan exécution) EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 14

Création d’une vues n Est le stockage d’une instruction SELECT fonctionnant comme une table n Ne stocke pas les données n Peut effectuer des sélections à partir de plusieurs tables et être utilisée pour contenir des données partielles d’une ou plusieurs tables. n Si les données d’une table change alors la vue dynamique change. EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 15

Création de vues n Create view nom_vue (col 1, coln, . . ) as commande_sélection [with check option] create view ca_auteur. PB as select au_id , au_lname , au_fname from auteurs where state = ‘CA’ Ensuite visualiser notre vue : select * from ca_ auteur EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 16

Restrictions Une vue peut inclure n une fonction d'agrégat et regroupement n Jointure , Une autre vue , Une clause where Une vue ne peut inclure n Une clause order by , compute, select into n Sp_helptext nom_vue EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 17

Vues : check option n n With check option limite la mise à jour de lignes sur la base de la clause where Permet d’interdire la mise à jour de la BD si l’on ne vérifie pas le restriction. Create view vue. Auteur. CA as select au_id, au_lname, au_fname, phone, address, city, state, zip, contract from auteurs where state =‘CA’ with check option n Les insertions ne sont possibles que dans l’état CA Insert vue. Auteur. CA values (‘ 111 -222 -3333’, ’phil’, ’banc’, ’O 328’ , ’paix’, ’losangeles’ , ’NY’, ’ 789’ , ’ 1’) dans ce cas l’insert échoue Mettre CA vérifier EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 18

exo n Création d’une vue ‘pub-vue_PB’ contenant le pub_id, pub_name, city, state de la table ‘publishers’ ou l’état est ‘CA’. n Vérifier le contenu de votre vue et celui de la table avec même restriction EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 19

Contraintes Elles sont utilisées dans la commande ‘create table’ pour limiter les valeurs contenues dans les tables. S’applique au niveau colonne ou table. n Clause Default : met une valeur si aucune valeur n’est indiquée. State char(2) default ‘CA’ n Vérification : Précise une liste ou fourchette de valeurs, vérifier une condition. Pub_id like ’ 99[0 -9]’ commence par 99 xx Vérifie que la colonne vérifie la condition. Qty smallint not null check (qty <=3000) EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 20

Contraintes Contrainte de table. Exemple à la table discounts vérifier que la quantité maxi soit supérieur quantité mini : constraint low_high_check (lowqty < highqty) n Create table nom_table n (nom_colonne datatype [constraint nom_contrainte] check (search_condition). . n EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 21

Messages Pour être parlant on peut ajouter des messages. n Sp_addmessage 20002 « lowqty indiqué est supérieur à highqty » n Ensuite on affecte ce message à la contrainte. n Sp_bindmsg low_high_check 20002 n n Si les données sont rejetées par la contrainte alors messages au client. Ajouter la contrainte à la table discount alter table discounts add constraint low_high_check (lowqty < highqty) EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 22

Index C'est une structure de stockage indépendante, créée en plus, c'est un objet distinct dans la BD La création d'un index améliore les performances en réduisant les lectures de pages nécessaires pour retrouver des données. Garantit l'unicité. n n Ø Index Clustérisés : Intéressant sur la jointure la plus courante, % élevé de duplication. Ils améliorent fortement les performances de la base, mais attention il ne peut y avoir qu'un seul index clusterisé par table (place en base). Ø Index non clusterisés : ne reclassent pas les données et n'affectent pas les pages de données. Ils fournissent des pointeurs aux lignes de la table, 249 index maxi, ils sont plus longs en traitement. EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 23

Unicité Create table nom_table (non colonne type [ constraint non_contrainte] unique [clustered|nonclustered] Pour la création d’une table, on défini toujours une clé primaire. « Primary key » sur 1 ou n colonnes. Par défaut une primary key construit un index. EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 24

Alter table n Ajouter, modifier des colonnes. n Ajouter, éliminer des contraintes. n Modification, attention dans certain cas impossible sans perte du passé. n Alter table sales add constraint contr_unicite unique (stor_id, ord_num) n Alter table sales drop constraint contr_unicite EPID-CPI-ISAIP Philippe Bancquart - mise à jour 24/02/2005 - page 25