bases de données

Faculté I&C, Claude Petitpierre, André Maurer

Bases de données SQL (Structured Query Language)


Une base de données contient des tables

idv region cepage annee

1 Lavaux chasselas 2005

3 Chianti sangiovese 2002

2 Bourgogne pinot noir 2000

Le nom et le type de chaque colonne est défini par l’utilisateur.


Chaque colonne a un nomChaque ligne correspond à un objetL’id est une colonne définie par l’utilisateur






On crée, remplit et affiche les lignes de la tableau moyen du langage SQL

• create table vins (idv int auto_increment primary key,

region varchar (20), cepage varchar (20), annee int)

• insert into vins values (0, 'Chianti', 'sangiovese', null)

• insert into vins set idv=0, region='Bourgogne'

• select * from vins


Primary key

• idv int auto_increment primary key

int - le champ contient un entier

auto_increment - chaque fois qu’on crée une ligne contenant un id 0, une nouvelle valeur unique est insérée

primary key - peut être utilisée pour identifier un objet de façon sûre (unique et structurée pour un accès rapide)


Création d’une table

varchar(20) - String de 20 caractères au maximum

create table vins (idv int auto_increment primary key, region varchar (20), cepage varchar (20), annee int)


Insertion

insert into vins values (0, 'Lavaux', null, 2005)

• Introduit une nouvelle ligne dans la table vins.

• Entre les parenthèses de values doivent apparaître une valeur par colonne et dans l’ordre.

• Si l’on ne veut pas introduire une colonne particulière à l’insertion, on peut introduire null.


Insertion, deuxième forme

insert into vins set idv=0, region='Bourgogne'

• le set est formé d’une séquence de couples nom_de_colonne – valeur

• Il peut manquer des colonnes


Lecture des données

select * from vins

select vins.idv, cepage from vins

• Après le select, on peut introduire une * (toutes les colonnes) ou une liste de champs de la table.

• S’il n’y a qu’une table impliquée dans la commande, il n’y a pas besoin de faire précéder le nom du champ par le nom de la table (comme cepage).


Lecture de données déterminées

select * from vins where annee>2003

select vins.idv, cepage from vins where cepage=‘chasselas’ and annee<2000

Après where, on peut introduire une expression contenant des constantes et des champs des tables


Mise à jour de lignes

update vins set annee=1999, cepage=viogner

where idv = 2

update vins set annee=annee+1 where idv = 2


Libraries pour appels depuis Javascript

<script src='/LemanOS/dwr/engine.js'></script><script src='/LemanOS/database.js'></script>


Appels depuis Javascript

var result = database.query("select * from vins")

// au retour de la query, result contient un tableau d’objets

[ {‘idv’:1,’cepage’:’chasselas’,’region’:’Lavaux’,’annee’:2005}, {‘id’:2,’cepage’:’sangiovese’,’annee’:2002}, {‘id’:3, ‘region’:’Bourgogne’} ]

// Les champs nulls n’apparaissent pas dans l’objet.


Accès aux champs

[ {‘idv’:1,’cepage’:’chasselas...},{‘id’:2,c’cepage’:...},{‘id’:3} ]

result[0].idv result[0].cepage result[0].region



result.length lignes


Accès aux champs

[ {‘idv’:1,’cepage’:’chasselas...},{‘id’:2,c’cepage’:...},{‘id’:3} ]

Si un champ , par exemple result[0].cepage, n’est pas défini, on a

typeof result[0].cepage == ‘undefined’

S’il n’y a qu’une ligne, elle est quand même placée dans [ ]


Lecture d’un objet

var result = [ {'a':2, 'b':3} ]

for ( key in result [0] ) {

document.write( result [0] [key] )

}

document.close()


Traitement des erreurs

var result try { result = database.query("select * from vins") } catch(e) { alert(“Erreur: “+e) return}// continue ici, en cas de succèsif (result.length==0) { // pas de ligne retournée}


Passage de paramètres

var an=2003, cep=‘chasselas’

var result = database.query( "select * from vins where annee=? and cepage=?", an, cep )

// Le premier paramètre de query est la string contenant la // commande SQL

// Un nombre arbitraire de paramètres suivent. Chaque // paramètre (nombre ou string) remplace un “?”


Tableau de valeurs

var data = [0, ‘Barolo’, ‘nebbiolo’, null]

var result = database.

query( 'insert into vins values (?)’ , data )

// data étant un array, le “?” est remplacé par les // valeurs de data séparées par des virgules

// comme cette query est une action, result reçoit le // nombre de lignes modifiées: 1


Objet en paramètre

var vin = {'region':'Bordeaux', 'cepage':'merlot'}

var result = database.

query( 'insert into vins set ?’ , vin )

// vin étant un objet, le “?” est remplacé par les couples// nom-valeur de vin séparés par des virgules

// result contient le nombre de lignes introduites: 1

// la clé primaire est générée automatiquement; on peut obtenir// sa valeur en appelant (après la query):

idNb = database.last_insert_id()


Points d’interrogation

• Il peut y avoir plusieurs “?”

• A chaque “?” doit correspondre un argument (il peut y en avoir plusieurs après la query)

• Un “?” peut être remplacé par– un entier ou un réel– une string– un tableau– un objet

• Evidemment, l’ordre des paramètres doit correspondre aux “?”, le système ne teste pas s’il est pertinent


Trois façons de construire un objet Javascript

unVin = {‘idv’:0, ‘Valais’, ‘cepage’:‘chasselas‘, ’annee’:2004}

function Vin(a, b, c) { this.idv = 0 this.region = a this.cepage = b this.annee = c}v = new Vin(‘Meursault’, ‘chard’)v.annee = 1999

var vin = { }vin.idv = 0vin.region = ‘Valais’vin.cepage = ‘chasselas‘vin.annee = 2004


Relations: vins reliés à un marchand

idv region cepage annee marchand

1 Lavaux chasselas 2005 3

2 Chianti sangiovese 2002 3

3 Bourgogne pinot noir 2000 1

idm nom

1 Jean

3 Luc

Vins

Marchands


Liste des vins offerts par Luc

select vins.* from vins, marchands

where vins.marchand=marchands.idm

and marchands.nom='Luc'

idv region cepage annee marchand idm nom

1 Lavaux chasselas 2005 3 1 Jean

2 Chianti sangiovese 2002 3 1 Jean

3 Bourgogne pinot noir 2000 1 1 Jean

1 Lavaux chasselas 2005 3 3 Luc

2 Chianti sangiovese 2002 3 3 Luc

3 Bourgogne pinot noir 2000 1 3 Luc

vins, marchands (join)


Si un marchand peut fournir plusieurs vins et chaque vin peut être fourni par plusieurs marchands

idm nom

1 Jean

2 Marc

3 Luc





idm idv

1 3

1 2

3 1

3 2vins

vinsXmarchands

marchands


Le numéro du fournisseur du Lavauxpuis son nom

select vinsXmarchands.idm from vins, vinsXmarchands

where vins.region=‘Lavaux’

and vins.idv=vinsXmarchands.idv

noMarchand

select marchands.nom from marchands

where marchands.idm=noMarchand

Jean


Les vins fournis par Luc

select idm from marchands where marchands.nom=‘Luc’ noLuc

select vins.* from vins, vinsXmarchands

where vins.idv=vinsXmarchands.idv

and vinsXmarchands.idm=noLuc


Les vins fournis par Luc(un seul select)

select vins.* from vins, marchands, vinsXmarchands

where vins.idv=vinsXmarchands.idv

and marchands.idm=vinsXmarchands.idm == sur la même ligne

and marchands.nom=‘Luc’

// en Javascript

function findMarchandByNom(nom) { return database.query(“select vins* from vins, marchands,” +“vinsXmarchands where vins.idv=vinsXmarchands.idv” +”and marchands.idm=vinsXmarchands.idm” +”and marchands.nom=?”, nom)}


Schéma E/R (entité-relation)

Marchands Vins

fournit0..n 0..n

Stock

1

1

0..n

1


Exercices

1. Charger la page http://lti.epfl.ch/LemanOS/database/SQL.htmlpuis cliquez chaque ligne (éventuellement en cliquant le select * entre chaque ligne)Essayez de modifier quelques lignes

2. Créez les tableaux de vins et de marchands des derniers transparents précédents et affichez les vins d’un marchand et les marchands qui fournissent un vin particulier.

3. Mettez vos requêtes dans une fonction dans le fichier http://lti.epfl.ch/Documents/squelette.html, préparé à cet effet.