Transact SQL en vue de programmation

ISAIP-EPID Formation IRIISQL SERVER /Transact-SQL 04/01/03
Bancquart Page 1 sur 1 CPI-BD-Cours-Transac SQL.doc
Transact SQL en vue de programmation
Avec Entreprise Manager, vous êtes capable de créer une base de données et
ses tables, vues, etc et de l’administrer. Mais si devez accéder à cette base via des
programmes externes comment faire ?
Dans tous les langages vous avez des fonctions qui permettent de se connecter à
des BD, ensuite il ne reste plus qu’à exécuter des ordres SQL (pour SQLserver et
Sybase TransacSQL). Ces commandes sont généralement écrites dans des chaînes
de caractères, nous verrons cela dans de futurs cours. Actuellement attachons nous
à savoir écrire les ordres pour les introduire dans des programmes, en fichiers batch
ou procédures stockées).
Beaucoup de commandes système commencent par "sp_" pour avoir de l’aide sur
un objet : sp_help nom_objet
Besoin aide, "Aide sous l’analyseur de requêtes", toute la documentation de
Transac SQL s' y trouve.
Ici nous voyons que la partie émergée de SQLServer et TRANSACSQL
Choix de BD.
Savoir sur quelle base sommes nous ?
Lancer SQLServer et Analyseur de requête (quitter Entreprise Manager).
Taper : select db_name() : réponse base sur laquelle vous êtes connectée
Pour aller sur la base Pubs : use pubs use nom_base
Taper : select db_name()
Rappel : Un Objet d’une base de données porte un nom et occupe de l’espace
dans cette base de données. Plusieurs objets de la BD utilisent des tables.
Affecter à la table
le trigger qui peuvent lancer : trigger et
procédures stockées, vues.
La procédures stockées peuvent lancer
PS et vues.
Les vues peuvent lancer d’autres vues.
Affecter à des colonnes de la
tables.
Valeur par défaut
Type de données
Règles de gestion
Index.
Types
Création de types défini par l’utilisateur. : sp_addtype "montype","type system",
"null | not null". Ensuite vous pouvez l’utiliser pour la création de variables.
C' est pour obtenir plus de standard. Pour avoir des infos sur le type, sp_help
EXERCICES :
sp_addtype montype , "varchar(6)" , "not null"
sp_help montype
sp_droptype montype
Rappel sur les différents types de données.

ISAIP-EPID Formation IRIISQL SERVER /Transact-SQL 04/01/03
Bancquart Page 2 sur 2 CPI-BD-Cours-Transac SQL.doc
Intégrité des données et tables.
Tables
• Table : Constituée de lignes et de colonnes, pour chaque colonne définir le
type et propriété.
• Type : système ou types de données définis par l'utilisateur.
Remarque : Dans les tables, l’on peut avoir des colonnes identity , cad que la
valeur s’incrémente de 1 à chaque enregistrement créé, il ne faut le faire dans un
ordre select. La syntaxe de création de table :
Create table nom_table
(nom_colonne datatype [identity|null|not null ,
(nom_colonne datatype [identity|null|not null ,
… )
EXERCICES :
create table VentesAuDetail
(stor_id char(4) not null,
title_id varchar(20) not null,
quantite smallint not null,
remise float null )
select * from VentesAuDetail
En complément nous avons : sp_help, sp_rename , drop table et alter table
Voir DOC.
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.
SQLServer supprime automatiquement les tables temporaires à la déconnexion.
Type
Nombre d'octets
Limite de valeur
Exemple
Smallint
int
2
4
-32768 à 32768
+/- 2147483647
88
15000
Numeric(p,s)
decimal(p,s)
2 à 17
-1038 à 1038
3,14
Float
double
real
4 ou 8
8
4
Fonction de la
machine
3,1415976..
Smalldatetime
Datetime
4
8
1/1/1900 à 6/6/2079
1/1/1763 à 12/31/9999
17-6-2001
Char(n)
varchar(n)
nchar(n)
Nvarchar(n)
N
longueur de l’entrée
peut comporter des espaces
suppriment les blancs à la fin
255 caractères
Money
8
=/-922337203685477
Fct pays
Binary(n)
varbinary(n)
N
fonction de l’entrée
255 octets
Image
Multiple de 2 Ko
Bit
1
0 ou 1

ISAIP-EPID Formation IRIISQL SERVER /Transact-SQL 04/01/03
Bancquart Page 3 sur 3 CPI-BD-Cours-Transac SQL.doc
Exemple : la table temporaire utilisée dans cet exemple contient un ensemble de
résultats intermédiaires. Ils désignent les livres pour lesquels les commandes
dépassent la moyenne dans la tables sales.
select title_id, total_orders = sum(qty) into #qty_table from sales group by title_id
select t.title_id , title , total_orders from titles t, #qty_table qt
where t.title_id = qt.title_id and total_orders > (select avg(total_orders) from
#qty_table)
select select_list into nom_nouvelle_table from nom_ancienne_table where
condition
Le select into permet de créer et copier la structure d’une table dans une autre avec
une clause where éventuellement.
Exercice :
select * into newtitles from titles where pubdate < "jan 1, 1993"
Vous avez une erreur, c’est normal. En tant qu’administrateur des options à
configurer, voyons cela.
Exercice :
Sur l’aide de transac cherchez sp_dboption , voyez les options à mettre en service
ou non possible, Le Select Into est parfois mis (pratique mais dans ce cas vous
autoriser l’utilisateur X à créer des tables de façon détournée)
Faire : sp_dboption pubs , "select into" , TRUE.
Terminez l' exercice précédent.
VUES
Est le stockage d’une instruction SELECT fonctionnant comme une table
Ne stocke pas les données
Peut effectuer des sélections à partir de plusieurs tables et être utilisée pour
contenir des données partielles d’une ou plusieurs tables.
La vue est dynamique, si une donnée de tables change et quelle soit projetée
dans une vue, alors celle ci change aussi
EXERCICE :
create view ca_authors as select au_id , au_lname , au_fname from authors where
state = "CA"
Ensuite visualiser notre vue : select * from ca_authors
Une vue peut inclure : une fonction d' agrégat et regroupement; jointure, autre vue,
clause where, un distinct. Mais elle ne peut pas inclure : order by, compute , select
into.
Contraintes.
Contraintes intégrité, d'unicité, clé primaire.
¾Default : s'applique à une insertion si pas de valeur est indiquée.
¾Vérification : Précise une liste ou fourchette de valeurs, vérifier une condition.
Create table table nom_table
(nom_colonne datatype
[constraint nom_contrainte] check (search_condition) ..
exemple à la table discounts vérifier que la quantité maxi soit supérieur quantité
mini : constraint low_high_check
check (lowqty < highqty)

ISAIP-EPID Formation IRIISQL SERVER /Transact-SQL 04/01/03
Bancquart Page 4 sur 4 CPI-BD-Cours-Transac SQL.doc
Pour être parlant on peut ajouter des messages.
Sp_addmessage 20002 « lowqty indiqué est supérieur à highqty »
Ensuite on affecte ce message à la contrainte.
Sp_bindmsg low_high_check 20002
Si les données sont rejetées par la contrainte alors messages au client.
Voir Doc
Exercice ajouter la contrainte à la table discount
alter table discounts
add constraint low_high_check check (lowqty < highqty)
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é.
¾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.
UNICITE et CLE PRIMAIRE
Une valeur ne peut se trouver sur plus d'une seule ligne, donc un seul null,
création d'index automatique. Create table nom_table (non colonne type [
constraint non_contrainte] unique [clustered|nonlustered]
Idem avec primary key
Règles
Objet de la BD qui définit une restriction appliquée à une colonne, assure l'intégrité
d'un domaine, s'applique à une colonne ou un type. Les règles et les valeurs par
défaut peuvent être associées à plusieurs colonnes. Elles peuvent être visualisées
avec SP_HELPTEXT.
Elles sont stockées dans la BD et donc modifiable pour l'ensemble de la BD.
Create rule nom_regle as expression
Sp_bindrule nom_regle , nom_objet.colonne
ALTER TABLE : modification de structure de tables.
Ajouter des colonnes, ajouter, modifier ou éliminer des contraintes. Il est interdit
de supprimer des colonnes ou modifier un type. A l'ajout d'une contrainte, les
données présentes dans la table ne sont pas affectées.
Manipulation de données
Insertion et MAJ de données.
¾INSERT : ajoute une ou plusieurs lignes à une table existante.

ISAIP-EPID Formation IRIISQL SERVER /Transact-SQL 04/01/03
Bancquart Page 5 sur 5 CPI-BD-Cours-Transac SQL.doc
Liste de colonne pour la table est facultative, pour ajouter des valeurs null, ne pas
indiquer la colonne dans la liste : Insert [into] nom_table [(liste_colonne)] {
values (expression, [expression] …) | instruction select
Exercice : insert publishers values ("9934" , "germinal" , "Dunkerque", "NO", "FR")
UPDATE : modifie tout ou une partie d'une ligne existante :
Update table set col = " val"
update nom_table Set nom_colonne = « valeur » where condition
EXERCICE : update discounts
set discounttype = " remise maxi de philippe" where stor_id = 1000
update titles set price = price * 0.9 where pubdate < "JAN 1, 1993"
¾DELETE : permet de supprimer les lignes d'une table avec ou non une
restriction (where) delete publishers where state = "NO"
¾TRUNCATE : Supprime toutes les lignes, il est plus rapide mais il supprime
toutes les lignes.
DELETE effectue une écriture dans le journal, on peut donc gérer des
RollBack. Truncate impossible de revenir en arrière.
Exercice : Insertion d’un enregistrement dans la table discount
insert discounts values ("ma remise",1000, 5, 9, 6.4)
Cela ne fonctionne pas, en effet l’identifiant magasin 1000 n’existe pas dans la
table Stores. Nous voyons ici l’interêt des clés étrangères, il n’est pas possible de
faire une erreur, cad affecter des commandes à un magasin qui n’existe pas.
Création du magasin Philippe id=1000
insert stores (stor_id , stor_name) values (1000, "philippe")
On précise que certains champs à insérer.
Rejouer la commande Insert Î erreur
insert discounts values ("ma remise",1000,5,9,6.4) : Par défaut tous les
champs
Suppression : Delete from nom_table where condition
EXERCICE
insert discounts values ("ma remise",6380,5,9,6.4)
insert discounts values ("ma remise",6380,5,9,6.4)
insert discounts values ("ma remise",6380,5,9,6.4)
select * from discounts
delete from discounts where stor_id = 6380
select * from discounts
Convertions
Fonction
Syntaxe
Exemple
Résultat
Substring
Substring(expression, position
départ,longeur)
Substring(“abcde”,2,2)
“bc”
Right
Rignht(chaine,longueur)
Right(“abcde”,3)
“cde”
Upper
Upper(chaine)
Upper(abcde)
ABCDE
Charindex
Charindex(masque,expression
Charindex(“bc”,”abcde”
2
ascci
Ascii(chaine)
Asci(t)
84
Char
Char(nombre)
Char(84)
T
Ltrim
Ltrim(chaineà
Ltrim(« abcde »
« abcde »
Space
Space(nombre)
Space(2)
2 espaces
insérés
Str
Str(nb réel,
longeur,nb_décimal)
Str(456.56 , 5 , 1)
456,6