SQL : LMD/LDD

1 Les requêtes action ; delete, update, insert

Les requêtes action sont des requêtes qui modifient le contenu de la base de données.

Elles concernent :

la suppression d'enregistrements,
la modification d'enregistrements,
l'insertion de nouveaux enregistrements dans une table.

Les clauses respectives sont :

delete from table where … ;
-> Attention, pas de champs derrière delete !
update table set attribut1=nouvelleValeur, attribut2=expression, … where … ;
-> Attention, pas de from derrière update !
insert into table (Attribut1, Attribut2, Attribut3, …) values( valeurAttribut1, valeurAttribut2, valeurAttribut3, …)

dans la requête insert, il est possible d'indiquer la liste des attributs à remplir si on ne dispose pas de toutes les valeurs. Attention à renseigner complètement la clé primaire.

Exemples :
Rédiger les requêtes suivantes (Article(codeA, descA, prixHT, …))

Supprimer les articles obsolètes qui contiennent le mot "Solex" dans la désignation.
L'article "roue 13X153 H" a été valorisé à zéro par erreur. Son prix doit être de 30€
Ajouter le nouveau "voyant avant halogène" VAH001Arg au prix de 14,34€ dans Article.

Attention, la virgule dans les nombres est notée '.'

Retour index

2 Les vues ; create/drop view

Une vue est une pseudo-table, que l'on peut manipuler comme une table.
Elles sont aussi appelées vues dynamique car leur contenu est mis à jour à chaque appel, si les tables ont changé.
Elles sont construites à partir d'une requête, c'est la façon de sauvegarder une requête si elle est souvent utilisée.

Restrictions d'usage : sur une vue, on ne peut pas faire d'ajout, de modification ou de suppression d'enregisrement !
donc pas de requête action sur une vue, on se limite au SELECT.

Exemples :
Liste des désignations et noms des fournisseurs des articles.

Create view VArticleFournisseur as select artDesc, fourNom from article, Fournisseur ;

Elles peuvent remplacer avantageusement des requêtes sélection dans une requête imbriquée complexe et éviter de refaire régulièrement la même requête à de multiples endroits, avec tous les risques d'erreurs d'écriture que cela comporte.

Pour supprimer une vue :

Drop view VartFou ;

Note 2 : Dans ms-Access, les vues sont des requêtes que l'on a enegistré (...)

Retour index

3 Les requêtes de manipulation de tables ; create, alter, drop

De la même façon dont nous pouvons manipuler des enregistrements, nous pouvons manipuler des tables.

Les clauses suivantes : create table, drop table et alter table servent à créer, détruire et modifier une table.

Note : create table se raproche de create view, drop table se rapproche de drop view.

La syntaxe est la suivante :

Create table NomDeLaTable ( liste_des_champs_des_clés_et_des_contraintes );
Drop table NomDeLaTable;
Alter table NomDeLaTable (liste_des_modif );

La liste des champs contient pour chaque champ :

Nom_du_champ type longueur contraintes

avec :
type : {integer, varchar, float, char, date ... } consulter les aides du SQL
longueur : un nombre entier ou décimal
contraintes : NOT NULL, REFERENCE ..., PRIMARY KEY, ...

Les lignes sont terminées par une virgule (sauf la dernière)

Les contraintes expriment si un champ (unique) est clé primaire ou/et clé étrangère, si la valeur NULL est autorisée ou non.
La liste des clés est utilisée si la clé primaire est composite.
Des contraintes peuvent être appliquées à la table.

La liste des modifications rappelle le champ et correspond aux nouvelles valeurs de type, longueur et contraintes à appliquer.

Note : ms-Access ne connait pas la contrainte de référence. Il faut faire la référence lors de la modification de la table, avec l'assistant de création (hum! encore un coup de mon ami Bill). Voir image ici

Exemple :
Créer les tables qui correspond au modèle relationnel suivant :

Article(artCode, artDesc, artStock, typArtCode)

Clé primaire : artCode
Clé étrangère : typArtCode référence typArtCode dans TypeArticle

TypeArticle(typArtCode, typArtDesc)

Clé primaire : typArtCode

fournir(artCode, fourNo, delaiAppro)
// un article est fourni par un fournisseur avec un certain délai d'approvisionnement

Clé primaire : artCode, fourNo
Clé étrangère : artCode référence artCode dans Article
Clé étrangère : fourNo référence fourNo dans Fournisseur

le DD est :

n°	code	description	type/long	observations
1	artCode	n	AN 15	identifiant de Article
2	artDesc		AN 30
3	artStock		N 9,2
4	typArtCode		N 6	identifiant de Type Article
5	typArtDesc		AN 30
6	fourNo		AN 6	identifiant de Fournisseur
7	delaiAppro		N 9,2

Retour index

4 Les droits sur les bases de données (en cours de conception)

Pour limiter l'accès aux données, il est possible de mettre en place des droits.
Ces droits sont attribués à différents types d'utilisateurs, en fonction de leur rôle.

4.1 Les acteurs

L'administrateur possède les droits sur la base et les utilisateurs mais doit se faire accorder les droits sur les tables par leur(s) concepteur(s) créateur(s).
Les concepteurs peuvent avoir et transmettre des droits sur les tables aux autres acteurs.
Les utilisateurs ont des droits d'accès aux données plus ou moins importants (création, interrogation, modification ou suppression – CIMS), et plus ou moins étendus selon leurs attributions.

Ces droits à accorder (grant) ou refuser (revoke) sont définis lors de la rédaction du modèle logique ou organisationnel de données.

4.2 La structure

Les droits peuvent être structuré en groupes.
On peut accorder des droits à un acteur particulier ou à un groupe auquel est rattaché un ou plusieurs acteurs.

4.3 Accorder ou supprimer des droits ; grant, revoke

La clause pour accorder des droit de modification de contenu sur une table à un acteur "toto" est la suivante :

Grant toto on maTable select, insert, update, delete ;

Toto aura tous les droits sur le contenu de la table maTable.

Pour supprimer des droits, on utilise la clause revoke avec la même syntaxe.

Revoke toto on maTable select, insert, update, delete ;

Retour index

Annexe 1 solutions des exercices ou exemples

Chapitre 1

Article(artCode, artDesc, prixHT, …)
Supprimer les articles obsolètes qui contiennent le mot "Solex" dans la désignation.

delete from Article where artDesc like "%Solex%" ;

L'article "roue 13X153 H" a été valorisé à zéro par erreur. Son prix doit être de 30€

update Article set prixHT=30 where descA="roue 13X153 H" ;

Ajouter le nouveau "voyant avant halogène" VAH001Arg au prix de 14,34€ dans Article.

insert into Article (artCode, artDesc, prixHT)
values ("VAH001Arg", "voyant avant halogène", 14.34) ;

Chapitre 3

Créer les tables qui correspond au modèle relationnel suivant :
Article(artCode, artDesc, artStock, typArtCode)

Clé primaire : artNo
Clé étrangère : typArtCode référence typArtCode dans TypeArticle

create table Article(

artCode varchar(15) primary key,
artDesc varchar(30),
artStock float(9.2),
typArtCode interger(6) reference TypeArticle.typeArtCode

) ;

TypeArticle(typArtCode, typArtDesc)

Clé primaire : typArtCode

create table TypeArticle(

typArtCode interger(6) primary key,
typArtDesc varchar(30)

) ;

fournir(artCode, fourNo, delaiAppro)
// un article est fourni par un fournisseur avec un certain délai d'approvisionnement

Clé primaire : artCode, fourNo
Clé étrangère : artCode référence artCode dans Article
Clé étrangère : fourNo référence fourNo dans Fournisseur

create table fournir(

artCode varchar(15) reference Article.artCode,
fourNo integer(6) reference Fournisseur.fourNo,
primary key artCode, fourNo

) ;

Retour index