Upload
truongtuyen
View
216
Download
0
Embed Size (px)
Citation preview
24/04/201223 avril 2012
Utiliser une base de données relationnelle sous RInitiation au langage SQL
Raymond BaudoinConservatoire botanique national du Bassin parisienDépartement [email protected]
Ecole doctorale 227Sciences de la Nature et de l’Homme
ModuleR : APPLICATIONS
24/04/2012
Utiliser SQL pour manipuler des données sous
Quand on veut filtrer les données à traiter car : Elles sont trop nombreuses Qu’une partie seulement nous intéresse
Elles sont éclatées dans plusieurs entités (fichiers)
C’est-à-dire chaque fois qu’un read.table() n’est pas totalement satisfaisant *
Pour un utilisateur de R
Pour un utilisateur SQL :Quand on souhaite y appliquer des fonctions de R
(*) permet cependant la restriction du nombre de lignes lues
Librairies utilisées : pour la connexion aux bases de données : RODBC, RSQLite, RMySQL, RPostgreSQL, ROracle, DBI pour accéder aux données des :
fichiers txt, .csv : sqldf fichiers Excel : RODBC, xlsReadWrite, xlsx bases de données : RODBC, RSQLite, RMySQL, RPostgreSQL, ROracle, DBI
pour manipuler des data frames : sqldf
24/04/2012
Fonctions de base pour lire un fichier texte Si le fichier est sous la forme d'un tableau statistique :
read.table (file = fich , # Nom complet du fichier ou "clipboard" pour le presse papier (copier/coller) sep = ";" , # Séparateur des valeurs, \t pour tabulation, \n : pas de séparateurdec = "," , # Symbole décimalheader = FALSE ,stringsAsFactors = TRUE , # Conversion des chaines en facteurrow.names = 1, # colonne utilisée comme identifiant des lignesskip = 1, nrow = 3 ) # Restriction de lecture, ici saut de la première ligne et 3 lignes lues
Si le fichier est sous une autre forme :scan (file = fich , sep=";", what = "character", skip = 1, nlines = 3)
> scan (file = "Habitants.csv", sep=";", what = "character",skip=1, nlines=2)Read 18 item[1] "1" "Lecesve" "André" "9" "rue Gay Lussac" "92320" "CHATILLON" [8] "146542856" "19/10/1920" "2" "Larrouy" "Catherine" "10" "rue Gay Lussac"[15] "92320" "CHATILLON" "140920841" "01/01/1999"
readLines (con = fich, n = 3) > readLines (con = "Habitants.csv", n=2)[1] "pk;Nom;Prénom;numéro;voie;Code;Ville;Téléphone;Depuis" [2] "1;Lecesve;André;9;rue Gay Lussac;92320;CHATILLON;146542856;19/10/1920
> read.table (file = "Habitants.csv", sep=";", header=FALSE, nrow=2)V1 V2 V3 V4 V5 V6 V7 V8 V9
1 pk Nom Prénom numéro voie Code Ville Téléphone Depuis2 1 Lecesve André 9 rue Gay Lussac 92320 CHATILLON 146542856 19/10/1920> read.csv2 (file = "Habitants.csv", row.names=1, nrow=2)
Nom Prénom numéro voie Code Ville Téléphone Depuis1 Lecesve André 9 rue Gay Lussac 92320 CHATILLON 146542856 19/10/19202 Larrouy Catherine 10 rue Gay Lussac 92320 CHATILLON 140920841 01/01/1999> read.delim2 ("clipboard", row.names=1, nrow=2)
Nom Prénom numéro voie Code Ville Téléphone Depuis1 Lecesve André 9 rue Gay Lussac 92320 CHATILLON 146542856 19/10/19202 Larrouy Catherine 10 rue Gay Lussac 92320 CHATILLON 140920841 01/01/1999
Copier/Coller d'une feuille Excel
24/04/2012
read.xlsx (file, # nom du fichiersheetIndex=, # numéro de la feuillesheetName=NULL, # ou son nom
as.data.frame=TRUE,header=TRUE,colClasses=NA, # numeric, integer, logical, character, Date
rowIndex=NULL,colIndex=NULL,keepFormulas=FALSE,encoding="unknown",...)
Fonctions pour lire une feuille MS-Excelau format .xls (Excel 97/2000/XP/2003) ou .xlsx (Excel 2007)
R ≥ 2.12.2
read.xlsx2 (file, # nom du fichiersheetIndex=, # numéro de la feuillesheetName=NULL, # ou son nom
as.data.frame=TRUE,header=TRUE,colClasses="character", # ou "numeric"
startRow=1,startColumn=1,noRows=NULL, # nombre de lignes à lire
noColumns=NULL)
library (xlsx)
read.xlsx ("OCCSOL_1000.xlsx", sheetIndex=1, rowIndex=c(1:4,rep(0,135-4)) )NA. BB BH BI BM BT O TR VB VG VH VM
1 0 0.0787 25.2158 30.0836 15.3149 1.1547 0.0465 6.5254 5.4042 3.2627 5.0906 7.82292 1 0.2431 22.2879 27.8258 22.7205 1.1578 0.0000 5.9316 5.1204 2.8737 3.8698 7.96933 2 0.1411 16.7779 23.4831 8.4136 0.7781 18.2162 7.0589 6.9271 2.5255 6.9970 8.6815
read.xlsx2 ("OCCSOL_1000.xlsx", sheetIndex=1, colClasses="numeric", startRow=1, noRows=3)c.0..1. BB BH BI BM BT O TR VB VG VH VM
1 0 0.0787 25.2158 30.0836 15.3149 1.1547 0.0465 6.5254 5.4042 3.2627 5.0906 7.82292 1 0.2431 22.2879 27.8258 22.7205 1.1578 0.0000 5.9316 5.1204 2.8737 3.8698 7.9693
read.xlsx ("Habitants.xls", sheetIndex=1, rowIndex=c(1:3,rep(0,14-3)), encoding="UTF-8", stringsAsFactors=FALSE)Nom Prénom numéro Adresse Code Ville Téléphone Depuis
1 Lecesve André 9 rue Gay Lussac 92320 CHATILLON 146542856 1920-10-192 Larrouy Catherine 10 rue Gay Lussac 92320 CHATILLON 140920841 1999-01-01
24/04/2012
read.xls( file, colNames = TRUE, sheet = 1, colClasses = NA, # numeric, integer, logical, character, isodate, isotime, isodatetime…stringsAsFactors = TRUE… )
Pour enregistrer un data.frame au format MS-Excel :write.xls(datal, file="localisation.xls", rowNames = TRUE)
Fonctions pour lire une feuille MS-Excel au format .xls
library (xlsReadWrite)
fich <- file.choose() # chemin complet# [1] "C:\\Mes documents sauvegardés\\Enseignement\\Ecole doctorale mnhn\\Module R compl\\Habitants.xls"Encoding (fich) # "UTF-8"fich <- iconv (fich , from = Encoding (fich), to = "latin1") # changement de code page
read.xls (file = fich, sheet = 1, stringsAsFactors = FALSE)Nom Prénom numéro Adresse Code Ville Téléphone Depuis
1 Lecesve André 9 rue Gay Lussac 92320 CHATILLON 146542856 75982 Larrouy Catherine 10 rue Gay Lussac 92320 CHATILLON 140920841 361613 Larrouy Eric 10 rue Gay Lussac 92320 CHATILLON 140920841 36161
read.xls (file=fich, sheet=1, stringsAsFactors=FALSE, dateTime="isodatetime")Nom Prénom numéro Adresse Code Ville Téléphone Depuis
1 Lecesve André 9 rue Gay Lussac 92320 CHATILLON 146542856 1920-10-192 Larrouy Catherine 10 rue Gay Lussac 92320 CHATILLON 140920841 1999-01-013 Larrouy Eric 10 rue Gay Lussac 92320 CHATILLON 140920841 1999-01-01
24/04/2012
SGBD : Système de gestion de base de donnéesOu DBMS (Database management system)
Un ensemble de services pour :
permettre l'accès aux données de façon simple
autoriser un accès aux informations à de multiples utilisateurs
manipuler les données présentes dans la base (insertion, suppression, modification)
Se décompose en trois sous-systèmes :
un système de gestion de fichiers pour le stockage des informations sur le support physique
un système pour gérer l'ordonnancement des informations
une interface avec l'utilisateur
24/04/2012
Elément de base : la table• Les données sont réparties en tables ou entités• Une table est composée de lignes• Une ligne est un ensemble fixe de champs (attributs)
Exemple : la table PersonnesChaque champ a :
un nom
05/09/2005146428564321BernardAuquier10
05/09/2005636699001321AnneAuquier9
05/09/2005157750048321AnneAuquier8
05/06/1960315MichelMeyer6
01/01/1999140920841210EricLarrouy3
01/01/1999140920841210CatherineLarrouy2
19/10/192014654285629AndréLecesve1
DepuisTéléphoneid_localisationnuméroPrénomNomid
Propriétés d’une table :• pas de nom de colonne dupliqué• ordre des lignes sans signification• ordre des colonnes (champs) sans signification• chaque champ permet la sélection de lignes
INTEGER CHARACTER INTEGER DECIMAL DATE• un type
Avec ces propriétés, une table :
un fichier une feuille xls
24/04/2012
alphanumériques(ou CHARACTER)caractèresTexte255CHAR(ou CHARACTER VARYING)caractères32767VARCHAR(ou NATIONAL CHARACTER)NCHAR
temporels
combiné date tempsDate/Heure8TIMESTAMPdu calendrier grégoriendateDATE
longueur fixechaîne de bitBIT
sur 24 heurestempsTIME
RemarqueNatureMS AccessValeurs limitesoctetType SQL
représentation binaire, 7 décimalesréel à virgule flottanteRéel simple 3.402823E384REAL
représentation exacte (précision, échelle), 28 décimales
nombre décimalDécimal (10^28) -112NUMERIC (ou DECIMAL ou DEC)
entier courtEntier 32 7682SMALLINT
représentation binaireréel à virgule flottante8DOUBLE PRECISION
représentation binaire, précision obligatoire, 15 décimales
réel à virgule flottanteRéel double1,7976931E308
8FLOAT
entier longEntier Long 2 147 483 6474INTEGER (ou INT)
numériques
Les types de champs 1/2 Les types normalisés SQL 2
24/04/2012
autres typesou LOGICALOui/NonBOOLEANprécision de deux chiffres.NUMERICMonétaireMONEYou BINARYoctet1BYTESentier à incrément automatiqueNuméroAuto4AUTOINC
RemarqueNatureMS AccessValeurs limitesoctetType SQL
Windowsobjet OLEOLE
longueur indéterminécaractèresMémo () 65 536TEXTstockage dans un format déterminéimageIMAGE
BLOBS
Les types de champs 2/2
Les types non normalisés SQL 2
24/04/2012
SGBDR : Le modèle relationnel
Permet la création de liaisons fixes entre les tables en mettant en relation un ou plusieurs champs.
Objectif du modèle :Pas d’information redondante
La clef primaire d'une table est un attribut ou un groupe d'attributs de cette table dont la valeur permet d'identifier de manière unique une ligne de la table.
Entités
Attributs
Relation
Cardinalité : dénombre le nombre d'éléments de l'entité départ en relation avec un élément de l'entité arrivée.
Relations possibles : 1/1, 1/n, n/1, n/m
Une relation n/m nécessite une table de jointure
24/04/2012
SGBDR : Le modèle relationnel
table de jointure
• 1 nom à n téléphones• 1 téléphone est partagépar m nom
relation n / m
L'attribut id de l'entité xxx= clé primaire de la table xxx
permet la relation avec l'entité yyy via l'attribut id_xxx = clé étrangère de la table yyy
24/04/2012
SQLite : SGBDR portable utilisable sous• moteur de base de données relationnelles accessible par le langage SQL.• implémente en grande partie le standard SQL-92• la base de données (déclarations, tables, index et données) stockée dans un fichier• utilisation sans restriction dans des projets aussi bien open source que propriétaires• multi-plateforme
SQLite est le moteur de base de données le plus distribué au monde, grâce à son utilisation dans de nombreux logiciels grand public comme Firefox, Skype, Google Gears, dans certains produits d'Apple, d'Adobe et de McAfee et dans les librairies standards de nombreux langages comme PHP ou Python. De par son extrême légèreté (moins de 300 ko), elle est également très populaire sur les systèmes embarqués, notamment sur la plupart des smartphones modernes.
http://www.sqlite.org/
http://sqlitebrowser.sourceforge.net/
Une « interface » utilisateur : SQLite Database Browser
La librairie sqldf permet à d'utiliser SQLite
24/04/2012
Serveur de donnéesSGBD (DBMS)
Excelfichier texte
Pourquoi se connecter au SGBD ?Sans connexion
fichier.txt.csv
sélectionexport
copiercoller
A refaire si les données sont modifiées ou si mauvaise sélectionIntroduction d’un fichier intermédiaire l’intégrité des données n’est plus respectée
Avec connexion
Intégrité des données respectéeSélection des données dans R
read.table()read.csv2()
odbcDriverConnect()sqlFetch()sqlQuery()
Serveur de donnéesSGBD (DBMS)
Excelfichier texte
Interface de connexion
24/04/2012
Accès à l'application gérant les données
L’interface doit :
ApplicationServeur de données
SGBD (DBMS)Excel
fichier texteDBI
database interface
ODBCOpen DataBase Connectivity
ODBC : interface spécialiséeL'application doit avoir un pilote ODBC.
Exemple : pilote ODBC SQLite http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe
Identifier l'application DBI Driver
DBI Connexion Etablir la connexion
Renvoyer les données DBI Résultats
Dans ce cas la connexion se fait directement sur le pilote ODBC de l'application
rôle des objets de
Interface de connexion
24/04/2012
Des packages R spécifiques* R ≥ 2.9
ApplicationServeur de données
* Contient à la fois le driver et l'interface (DBI)
RMySQL
RPostgreSQL
ROracle
MySQL
PostgreSQL
Oracle
Package source: RMySQL_0.7-4.tar.gz MacOS X binary: RMySQL_0.7-4.tgz Windows binary: RMySQL_0.7-4.zip
Package source: RPostgreSQL_0.1-6.tar.gz MacOS X binary: Non disponibleWindows binary: RPostgreSQL_0.1-6.zip
Package source: ROracle_0.5-9.tar.gz MacOS X binary: Non disponibleWindows binary: Non disponible
RSQLite SQLitePackage source: RSQLite_0.8-2.tar.gz MacOS X binary: RSQLite_0.8-2.tgz Windows binary: RSQLite_0.8-2.zip
RODBCBases de données ayant
un pilote ODBC : Access, Excel, SQLite...
Package source: RODBC_1.3-1.tar.gz MacOS X binary: RODBC_1.3-1.tgz Windows binary: RODBC_1.3-1.zip
Interface de connexion
24/04/2012
La source de données est identifiée par son DSN = Data Source Name
Applicationswindows SGBD
Client Microsoft
Driver ODBCcorrespondant à
l'application source
DS
NODBC
Microsoft, UNIX, Linux
Couche logiciel qui permet la connexion d’une application windows à une source de données
(format défini par Microsoft)ODBC = Open DataBase Connectivity
Client Oracle Oracle
Machine utilisateur Serveur de données distant
24/04/2012
channel <- odbcDriverConnect ()Connexion à une base de données via ODBC
Configuration du DSNL’accès à la base est décrit dans le dsn associé dansC:\Program Files\Fichiers communs\ODBC\Data Sources
Contrôle de l’accès si activé
La base est sur un serveur distant
La base est dans un répertoire accessible
Configurer un lien ODBC sous Windows
• Démarrer• Panneau de configuration• Outils d’administration• Sources de données (ODBC)
Permet d’identifier la source
Nom de la base à connecter
24/04/2012
1. charger la librairielibrary (RSQLite) library (RODBC)
2. charger le pilotedrv <- dbDriver ("SQLite")
3. ouvrir une connexion sur les donnéescon <- dbConnect (drv, dbname=*...) channel <- odbcDriverConnect()* nom de la base uniquement ou channel <- odbcConnectExcel(…)
channel <- odbcConnectAccess(…)4. voir la structure des données (non obligatoire)
dbListTables (con) sqlTables (channel,…)dbListFields (con, "sqtable") sqlColumns (channel, sqtable,…)dbGetRowCount (res); dbColumnInfo (res)
5. charger les données avec tous les champsdbReadTable (con, sqtable) sqlFetch (channel, sqtable,…)
6. charger les données avec un SELECT (sql)res <- dbSendQuery (con, sql) sqlQuery (channel, sql,…)
fetch (res, n = -1)dbGetQuery (channel, sql,…)
7. fermer la connexion : OBLIGATOIRE pour libérer l’accèsdbDisconnect (con) close(channel) ou odbcClose(channel)
odbcCloseAll()8. libérer le pilote : dbUnloadDriver (drv)
via DBIexemple : SGBD sqlite
via ODBCConnexion
Connexion : la démarche
24/04/2012
Exemples de connexionà une base sqlite : "SeminR.db"
library (RSQLite)# Se mettre dans le répertoire de la basesetwd(choose.dir()) db <- "SeminR.db"drv <- dbDriver("SQLite")con <- dbConnect(drv, dbname=db)
drv<SQLiteDriver:(2192)>con <SQLiteConnection:(2192,0)>
dbGetInfo(drv)$num_con[1] 1 # nombre de connexiondbGetInfo(con)$dbname[1] "SeminR.db"$serverVersion[1] "3.6.21"$rsIdinteger(0)$loadableExtensions[1] "off"$flags[1] 6$vfs[1] ""dbDisconnect (con)[1] TRUEcon<Expired SQLiteConnection: DBI CON (2192, 0)>
library (RODBC)
c_sqlite <- odbcDriverConnect()
Via DBI Via ODBC
c_sqliteRODBC Connection 10Details:
case=nochangeDSN=SemirR3Database=C:\Mes documents
sauvegardés\Enseignement\Séminaire R\Nouveau 2010\SeminR.db
StepAPI=0SyncPragma=NORMALNoTXN=0Timeout=ShortNames=0LongNames=1NoCreat=0NoWCHAR=0JournalMode=LoadExt=
library (RODBC)(conn <- file.choose())[1] "C:\\...\\Séminaire R\\RODBC.mdb"
channel <- odbcConnectAccess (conn)
channelRODBC Connection 11Details:
case=nochangeDBQ=C:\Mes documents
sauvegardés\Enseignement\Séminaire R\RODBC.mdb
Driver={Microsoft Access Driver (*.mdb)}DriverId=25FIL=MS AccessMaxBufferSize=2048PageTimeout=5UID=admin
à une base Access
24/04/2012
dbListTables(con)[1] "Habitants" "localisation" "personnes"
sqlTables (c_sqlite)TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS1 <NA> <NA> Habitants TABLE <NA>2 <NA> <NA> localisation TABLE <NA>3 <NA> <NA> personnes TABLE <NA>
sqlTables (channel)TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1 C:\\...\\Séminaire R\\RODBC <NA> MSysAccessObjects SYSTEM TABLE <NA>2 C:\\...\\Séminaire R\\RODBC <NA> MSysAccessXML SYSTEM TABLE <NA>3 C:\\...\\Séminaire R\\RODBC <NA> MSysACEs SYSTEM TABLE <NA>4 C:\\...\\Séminaire R\\RODBC <NA> MSysObjects SYSTEM TABLE <NA>5 C:\\...\\Séminaire R\\RODBC <NA> MSysQueries SYSTEM TABLE <NA>6 C:\\...\\Séminaire R\\RODBC <NA> MSysRelationships SYSTEM TABLE <NA>7 C:\\...\\Séminaire R\\RODBC <NA> Habitants TABLE <NA>8 C:\\...\\Séminaire R\\RODBC <NA> Localisation TABLE <NA>9 C:\\...\\Séminaire R\\RODBC <NA> Personnes TABLE <NA>
sqtable = "localisation"dbExistsTable (con, sqtable)[1] TRUE
dbListFields( con, sqtable)[1] "id" "Adresse" "Code" "Ville"
sqlColumns (c_sqlite, sqtable)[,3:6]TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
1 localisation id 4 INTEGER2 localisation Adresse -9 VARCHAR3 localisation Code 8 NUMERIC4 localisation Ville -10 TEXT
sqlColumns (channel, sqtable)[,3:6]TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME
1 localisation id 4 COUNTER2 localisation Adresse 12 VARCHAR3 localisation Code 4 INTEGER4 localisation Ville 12 VARCHAR
Accès aux structures
Tables
Champs
DBI
ODBC
DBI
ODBC
24/04/2012
En enregistrant un data.frame : sqlSave ()sqlSave(channel, datal[1:2,], rownames ="pk", addPK =TRUE)
Création d’une clef primaire nommée pkPar défaut :tablename =
nom du data.framesqlQuery(channel,"SELECT * FROM datal")
pk id Adresse Code Ville1 1 1 avenue Verdun 92170 VANVES2 2 2 rue Gay Lussac 92320 CHATILLON
En copiant la structure d'une table existante : sqlCopyTable ()
sqlCopy(channel, query, desttable, destchannel = channel)
En copiant le résultat d'une requête : sqlCopy ()
sqlCopyTable(channel, srctable, desttable, destchannel = channel)
Nom de la nouvelle table et sa localisation
Nom de la nouvelle table et sa localisation
Nom de la table à copier (source)
"SELECT ... FROM ... ;"
Créer une tableTravailler avec les tables via ODBC
24/04/2012
à une table : sqlSave ()sqlQuery(channel,"SELECT * FROM datal")
pk id Adresse Code Ville1 1 1 avenue Verdun 92170 VANVES2 2 2 rue Gay Lussac 92320 CHATILLON3 3 3 rue Roissis 92140 CLAMART
# ajouter la 3ème ligne du data.frame datal# à la table de même nom
sqlSave (channel, datal[3,],rownames = "pk", addPK = TRUE, append = TRUE)
d’une table : sqlUpdate ()# modification de la 3ème ligne de dataldatal[3,3:4] = datal[1,3:4]datal <- cbind(rownames(datal),datal)colnames(datal)[1] <- "pk"
sqlUpdate (channel, datal[3,], tablename = "datal", index = "pk")
sqlQuery(channel,"SELECT * FROM datal")pk id Adresse Code Ville
1 1 1 avenue Verdun 92170 VANVES2 2 2 rue Gay Lussac 92320 CHATILLON3 3 3 rue Roissis 92170 VANVES
Travailler avec les tables via ODBC
Ajouter des lignes
Modifier des lignes
24/04/2012
sqlClear (channel, "datal")
d’une table : sqlClear ()
sqlQuery(channel,"SELECT count(*) FROM datal")Expr1000
1 0Mais la structure de la table existe toujourssqlSave(channel,datal,rownames = FALSE, append = TRUE)sqlQuery(channel,"SELECT count(*) FROM datal")Expr1000
1 3
sqlDrop ()sqlDrop (channel,"datal")
Travailler avec les tables via ODBC
Supprimer toutes les lignes
Supprimer une table
24/04/2012
Accès aux données Pas de sélection de colonnes
(dDBI <- dbReadTable(con, sqtable))id Adresse Code Ville
1 1 avenue Verdun 92170 VANVES2 2 rue Gay Lussac 92320 CHATILLON3 3 rue Roissis 92140 CLAMART
str(dDBI)'data.frame': 3 obs. of 4 variables:$ id : int 1 2 3$ Adresse: chr "avenue Verdun" "rue Gay Lussac" "rue Roissis"$ Code : int 92170 92320 92140$ Ville : chr "VANVES" "CHATILLON" "CLAMART"
(dODBC <- sqlFetch (c_sqlite, sqtable))localisation.id localisation.Adresse localisation.Code localisation.Ville
1 1 avenue Verdun 92170 VANVES2 2 rue Gay Lussac 92320 CHATILLON3 3 rue Roissis 92140 CLAMART
str(dODBC)'data.frame': 3 obs. of 4 variables:$ localisation.id : int 1 2 3$ localisation.Adresse: Factor w/ 3 levels "avenue Verdun",..: 1 2 3$ localisation.Code : num 92170 92320 92140$ localisation.Ville : Factor w/ 3 levels "CHATILLON","CLAMART",..: 3 1 2
Pour avoir des caractères, mettre le paramètre stringsAsFactors = FALSE
DBI
ODBC
24/04/2012
Charger les données ligne(s) à ligne(s)
Utilisation de sqlFetch() sqlFetchMore()
sqlFetch (channel,"personnes", max=3)Nom Prénom numéro Adresse Code Ville Téléphone Depuis
1 Lecesve André 9 rue Gay Lussac 92320 CHATILLON 146542856 1920-10-192 Larrouy Catherine 10 rue Gay Lussac 92320 CHATILLON 140920841 1999-01-013 Larrouy Eric 10 rue Gay Lussac 92320 CHATILLON 140920841 1999-01-01
sqlFetchMore (channel, max=1)Nom Prénom numéro Adresse Code Ville Téléphone Depuis
1 Malher Goerges 13 rue Gay Lussac 92320 CHATILLON 146576986 1960-06-05
sqlFetchMore (channel)Nom Prénom numéro Adresse Code Ville Téléphone Depuis
1 Lipinski Ludovic 15 rue Gay Lussac 92320 CHATILLON 147352329 1952-09-232 Meyer Michel 15 rue Roissis 92140 CLAMART NA 1960-06-053 Foucher Georges 17 rue Roissis 92140 CLAMART 146449501 2000-02-034 Auquier Anne 21 rue Roissis 92140 CLAMART 157750048 2005-09-055 Auquier Anne 21 rue Roissis 92140 CLAMART 636699001 2005-09-056 Auquier Bernard 21 rue Roissis 92140 CLAMART 146428564 2005-09-057 Mahier Ludovic 3 avenue Verdun 92170 VANVES 147361266 1983-05-078 Berrue Christiane 4 avenue Verdun 92170 VANVES 146381434 1985-10-219 Berrue Christiane 4 avenue Verdun 92170 VANVES 954912355 1985-10-21
sqlFetchMore (channel, max=1)[1] -1
Gestion d’un
curseurlignes
4curseur + max
1 à max
5 à 13
code de fin
Commande RODBC
24/04/2012
channel <- odbcConnectExcel() ( conn <- file.choose() )[1] "C:\\Mes documents sauvegardés\\Enseignement \\CoursStat\\Séminaire R\\Habitants.xls"
channel <- odbcConnectExcel(conn)
Connexion ODBC à un classeur Excel
Par sélection dans un menu Par son nom
channelRODB Connection 3Details:case=nochangeDBQ=C:\Mes documents sauvegardés\Enseignement\Séminaire R\Habitants.xlsDefaultDir=C:\Mes documents sauvegardés\Enseignement\Séminaire RDriver={Microsoft Excel Driver (*.xls)}DriverId=790MaxBufferSize=2048PageTimeout=5
Ouvrir la connexion avec l’option d’écriture (par défaut : readOnly = TRUE)channel <- odbcConnectExcel (conn, readOnly = FALSE)
24/04/2012
data <- sqlFetch (channel, "Feuil1")
nom de la « table » obligatoirenom de la connexion
Voir la structure des données : description du classeursqlTables(channel)
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS1 C:\\Mes documents sauvegardés\\Enseignement\\Séminaire R\\Habitants <NA> Feuil1$ SYSTEM TABLE <NA>2 C:\\Mes documents sauvegardés\\Enseignement\\Séminaire R\\Habitants <NA> Feuil2$ SYSTEM TABLE <NA>3 C:\\Mes documents sauvegardés\\Enseignement\\Séminaire R\\Habitants <NA> Feuil3$ SYSTEM TABLE <NA>
Charger les données : sqlFetch()
Accès aux données ExcelCommandes identiques à l'accès à un SGBDR sauf sqlQuery()
sqlSave(channel, data1, tablename = "New", rownames = FALSE) Ecriture des données dans une nouvelle feuille
data.frame des données nom de la feuille TRUE par défaut
Pas d’écrire possible dans une feuille vide existante.
Nouveau nom de feuille obligatoire
Ajouter des données dans une feuillesqlSave(channel,data1,tablename = "New", rownames = FALSE, append = TRUE)
Possible que si la feuille à des données
Obligatoire : fermer la connexion pour accéder au fichier par Excelclose(channel)
Commandes RODBC
24/04/2012
Utilisation du language SQL pour sélectionner :
des colonnes des lignes
d'une ou plusieurs tables
par l'utilisation d'une requête SQL
SQL (Structured Query Language)
24/04/2012
SQL (Structured Query Language)
Caractéristiques Inventé en 1970
Déclaratif c'est-à-dire indépendant du contexte d'exécution
Régi par une norme ANSI/ISO Première normalisation ISO en 1987 SQL 2 correspond à la norme SQL/92 SQL 3 en 1999, actuellement SQL 2003
Portable sur différentes plates-formes aussi bien matérielles que logicielles.
Une commande SQL écrite dans un environnement Windows sous MS-ACCESS est utilisable directement dans un environnement ORACLE sous Unix.
Langage normalisé pour décrire, manipuler, contrôler l'accès et interrogerles bases de données relationnelles
24/04/2012
SQL (Structured Query Language)
Langage de requêtes structuréregroupant :
un langage de contrôle des accès (DCL, Data Control Language)
un langage de définition de données (DDL Data Definition Language)
Pour créer, modifier ou supprimer des tables dans la base
un langage de manipulation de données (DML, Data Manipulation Language)
Pour sélectionner, insérer, modifier ou supprimer des données dans une table
24/04/2012
Les commandes SQL
DCL : Contrôle des donnéesGRANT Attribution de droits d'accès REVOKE Suppression de droits d'accès COMMIT Prise en compte des mises à jour ROLLBACK Suppression des mises à jour
DDL : Description des donnéesCREATE Création de tables ALTER Modification de tables DROP Suppression de tables
DML : Manipulation des donnéesSELECT Interrogation des données
INSERT Insertion de lignes dans une table UPDATE Mise à jour de lignes dans une table DELETE Suppression de lignes dans une table
24/04/2012
SELECT [ALL | DISTINCT] { * | col | expr [AS alias], ... }FROM table [alias], ...[ WHERE { conditions de recherche | sous conditions} ][ GROUP BY col, ...] [HAVING conditions de recherche ][ ORDER BY { col | num } {ASC | DESC}, ...] ;
Syntaxe de la commande SELECT
Légende :{ } : Une des valeurs
séparées par '|' obligatoire.
[ ] : Valeur optionnelle.... : Répétition.__ : Valeur par défaut.
SELECT Précise les colonnes qui vont apparaître dans la réponse FROM Précise la (ou les) table intervenant dans l'interrogation WHERE Précise les conditions à appliquer sur les lignes avec :
- Des opérateurs de comparaison : =, >, <, >=, <=,<>- Des opérateurs logiques : AND, OR, NOT- Des prédicats : IN, LIKE, NULL, ALL, ANY...
GROUP BY Précise la (ou les) colonne de regroupement HAVING Précise la (ou les) conditions associées à un regroupement ORDER BY Précise l'ordre dans lequel vont apparaître les lignes de la réponse :
- ASC : ordre ascendant (par défaut)- DESC : ordre descendant
24/04/2012
sql <- "SELECT * FROM localisation ;"
dbGetQuery(con, sql)id Adresse Code Ville
1 1 avenue Verdun 92170 VANVES2 2 rue Gay Lussac 92320 CHATILLON3 3 rue Roissis 92140 CLAMART
res <- dbSendQuery(con, sql) # Prépare la requêtefetch (res, n = 2) # Affiche n lignes (curseur), si n = -1 → jusqu’à la finid Adresse Code Ville
1 1 avenue Verdun 92170 VANVES2 2 rue Gay Lussac 92320 CHATILLON
dbGetRowCount(res) # nb lignes lues[1] 2
dbHasCompleted(res) # test la fin de l’affichage[1] FALSE
fetch(res, n = -1) id Adresse Code Ville
3 3 rue Roissis 92140 CLAMART
dbHasCompleted(res)[1] TRUE
Autres fonctions :dbClearResult (res) # libère le lien à la table. A faire pour un nouveau dbSendQuery() si dbHasCompleted(res)→FALSEdbColumnInfo (res) # à sqlColumns() dbGetStatement (res) # affiche le SELECT
sqlQuery(c_sqlite, sql)localisation.id localisation.Adresse localisation.Code localisation.Ville
1 1 avenue Verdun 92170 VANVES2 2 rue Gay Lussac 92320 CHATILLON3 3 rue Roissis 92140 CLAMART
Première utilisation
Deuxièmeutilisation
DBI
ODBC
Accès aux données via SQLLa requête :
24/04/2012
sqldf () permet l'usage des requêtes SQL sur : les tables d'une base SQLite des data frames des fichiers textes
Utilise les librairies : • DBI (data base interface)• RSQLite : pilote (driver) R pour l’accès à une base SQLite
library (sqldf) pour l'utilisation d'une base de données sqlite
24/04/2012
Connexion à une base sqlite via sqldf () Ouverture d'une connexion à la base sqlite "nom_de_la_base" *
* exclusivement le nom de la base et non le chemin completsqldf (dbname = "nom_de_la_base")
Si "nom_de_la_base" non trouvé : création d'une base vide à ce nom
Si dbname non précisé : ouverture d'une base virtuelle( sqldf () )<SQLiteConnection:(1036,0)>
Utilisation des commandes DBI :dbGetInfo(SQLite())$num_con # nombre de connexions ouvertescon <- sqldf (dbname = "nom_de_la_base") # récupération de l’identifiant de la connexiondbListTables(con) # liste les tables de la base "SQLite" connectée
Fermeture de la connexion au 2ème appelsqldf () NULL
Exemple d’un script setwd(dirname(dbase <- file.choose())) # change le répertoirede sélection de la dbase = iconv(dbase,Encoding(dbase),"latin1") # conversion code caractèrebase à connecter dbase <- substring(dbase,max(unlist(gregexpr("\\",dbase,fixed=TRUE)))-1)
sqldf(dbname = dbase) # connexion
(*)
24/04/2012
sqldf ("Requête SQL", dbname = ..., connection = ... )
nom* d'une base SQLitepar défaut :ouvre une base virtuelle en mémoire
nom d'une connexion sur une base SQLiteouverte par :- dbConnect ()- sqldf (dbname = )
par défaut
SELECT
• sur des tables d'une base SQLite (ou MySQL)• sur des fichiers textes• sur des data.frames
* uniquement le nom de la base
Remarque sur l'ouverture / fermeture de la connexion( sqldf() ) # ouverture d’une connexion sur une base virtuelle<SQLiteConnection:(1036,0)> ( sqldf() ) # fermeture de la connexion au 2ème appelNULL
OU
Utiliser SQL pour manipuler des données dans library (sqldf)
24/04/2012
Exemple : iris
sqldf ("SELECT * FROM iris ;", method = "raw", row.names = FALSE)
Requête SQL "raw" Données brutes "auto" Types ajustés
TRUE : ajoute une colonne row_names à la tablesi method= "raw" et SELECT * : ajoute une
colonne row_names au data.frame
2 - Utilisation avec un data.frame
1 - Utilisation avec une base sqlite
sqldf ("SELECT * FROM ma_table ;", method = "raw" )
Requête SQL "raw" Données brutes "auto" Types ajustés au "mieux"
SELECT SQL via sqldf
Attention – Dans les cas 2, 3 et 4 si la connexion est sur une base nommée, il y aura copie de la (des) table(s) du FROM. Risque
de conflit ! si la connexion ne pointe aucune base nommée, c’est la base virtuelle qui est utilisée.
S’assurer qu’une connexion est bien ouverte
24/04/2012
• Si pas de nom de colonne : nomme des colonnes de V1 à Vncol• Si header = TRUE :
si length(colnames) = ncol : nomme les colonnes avec les nomssi length(colnames) = ncol -1 ajoute une colonne row_names à la table
si method= "raw" et SELECT * ajoute une colonne row_names au data.frame
sqldf ("SELECT * FROM fich;", method = "auto")
Requête SQL
3 – Avec la syntaxe sqldf
fich <- file(file.choose()) # ouvre une connexion sur un fichier texte au choixattr(fich , "file.format") <- list(sep = "\t" , header = TRUE) # affectation des attributs
Séparateur des champs Si nom des colonnes en 1ère ligne
4 – Avec un read.csv filtré par SELECT SQLread.csv2.sql (file.choose(), sql = "select * from file” )
«file» mot clé obligatoire
SELECT SQL via sqldf sur un fichier texte
Remarque :dbRemoveTable(con, "ma_table") # supprime la table "ma_table" de la base connectée
24/04/2012
sqldf () pour manipuler des données dans
1 - Utilisation avec une base SQLite
2 - Utilisation avec des data.frames
3 - Utilisation avec des fichiers textes
pers <- sqldf ("SELECT Nom, prenom, id_localisation FROM Personnes ;",dbname = "SeminR.db") # nom de la base
condb <- dbConnect (drv = "SQLite", dbname = "SeminR.db")
loca <- sqldf ("SELECT * FROM localisation ;", connection = condb) # connexion
sql <- "SELECT Nom, Ville FROM loca , pers # data.frameWHERE loca.id = pers.id_localisation # jointure
AND Nom LIKE 'L%' ;" # troncature à droiteNV <- sqldf (sql) # requête exécuté dans la base virtuelle
NVNom Ville
1 Lecesve CHATILLON2 Larrouy CHATILLON3 Larrouy CHATILLON4 Lipinski CHATILLON
Loc <- file ("Localisation.txt") # connexion au fichier texteattr (Loc ,"file.format") = list (sep = "\t", header=TRUE)
sql1 ="SELECT DISTINCT Nom, code, NV.Ville FROM NV, LocWHERE Loc.Ville = NV.Ville ;" # jointure
sqldf (sql1)
Nom Code Ville1 Larrouy 92320 CHATILLON2 Lecesve 92320 CHATILLON3 Lipinski 92320 CHATILLON
data.framefichier texte
requête SQL
24/04/2012
sqlQuery vs sqldf
SGBDAccess
channel <- odbcDriverConnect (maBase)
fichier.txt.csv
channel_file <- file (monFichier)
mon.data.frame
sqlQuery (channel, SQL)
sqldf (SQL)
Connexions RequêtesSQLDonnées
channel <- odbcConnectAccess (maBase)
channel <- odbcConnectExcel (monClasseur)
sqlFetch (channel, "maFeuil")
Excel
Habitants <- file( paste(getwd(),"Habitants.txt",sep="/") )attr(Habitants ,"file.format") = list(sep = "\t",header=TRUE)
Personnes <- file( paste(getwd(),"Personnes.txt",sep="/") )attr(Personnes ,"file.format") = list(sep = "\t",header=TRUE)
Localisation <- file( paste(getwd(),"Localisation.txt",sep="/") )attr(Localisation ,"file.format") = list(sep = "\t",header=TRUE)
Comm2 <- file( paste(getwd(),"Comm2.txt",sep="/") )attr(Comm2 ,"file.format") = list(sep = "\t",header=TRUE)
channel <- odbcConnectAccess (paste(getwd(),"RODBC.MDB",sep="/"))
Commandes sqldf
Commandes RODBC
24/04/2012
SELECT [ALL | DISTINCT] { * | col | expr [AS alias], ... }FROM table [alias], ...[ WHERE { conditions de recherche | sous conditions} ][ GROUP BY col, ...] [HAVING conditions de recherche ][ ORDER BY { col | num } {ASC | DESC}, ...] ;
Syntaxe de la commande SELECT
Légende :{ } : Une des valeurs
séparées par '|' obligatoire.
[ ] : Valeur optionnelle.... : Répétition.__ : Valeur par défaut.
SELECT Précise les colonnes qui vont apparaître dans la réponse FROM Précise la (ou les) table intervenant dans l'interrogation WHERE Précise les conditions à appliquer sur les lignes avec :
- Des opérateurs de comparaison : =, >, <, >=, <=,<>- Des opérateurs logiques : AND, OR, NOT- Des prédicats : IN, LIKE, NULL, ALL, ANY...
GROUP BY Précise la (ou les) colonne de regroupement HAVING Précise la (ou les) conditions associées à un regroupement ORDER BY Précise l'ordre dans lequel vont apparaître les lignes de la réponse :
- ASC : ordre ascendant (par défaut)- DESC : ordre descendant
24/04/2012
sql <- "SELECT Adresse AS Voie, Code AS [Code postal], Ville AS Commune FROM Localisation;"
Voie Code postal Commune1 avenue Verdun 92170 VANVES2 rue Gay Lussac 92320 CHATILLON3 rue Roissis 92140 CLAMART
sql <- "SELECT Ville, ' - ' AS Sep, Code AS CP FROM Localisation;"
Ville Sep CP1 VANVES - 921702 CHATILLON - 923203 CLAMART - 92140
Alias renommer une colonne ou une table(cf auto jointure)
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
croissant ou alphabétique : ASC (par défaut)sql <- "SELECT * FROM Localisation ORDER BY Code;"
id Adresse Code Ville1 3 rue Roissis 92140 CLAMART2 1 avenue Verdun 92170 VANVES3 2 rue Gay Lussac 92320 CHATILLON
sql <- "SELECT * FROM Localisation ORDER BY Ville;"id Adresse Code Ville
1 2 rue Gay Lussac 92320 CHATILLON2 3 rue Roissis 92140 CLAMART3 1 avenue Verdun 92170 VANVES
décroissant DESCsql <- "SELECT * FROM Localisation ORDER BY Code desc;"id Adresse Code Ville
1 2 rue Gay Lussac 92320 CHATILLON2 1 avenue Verdun 92170 VANVES3 3 rue Roissis 92140 CLAMART
ORDER BY Trier la sélection
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
sql <- "SELECT Nom, Prénom FROM Personnes ORDER BY Nom, Prénom;"
Nom Prénom1 Auquier Anne2 Auquier Anne3 Auquier Bernard4 Berrue Christiane5 Berrue Christiane6 Foucher Georges7 Larrouy Catherine8 Larrouy Eric9 Lecesve André10 Lipinski Ludovic11 Mahier Ludovic12 Malher Goerges13 Meyer Michel
DISTINCT Supprimer les lignes identiques
sql <- "SELECT DISTINCT Nom FROM Personnes;"Nom
1 Auquier2 Berrue3 Foucher4 Larrouy5 Lecesve6 Lipinski7 Mahier8 Malher9 Meyer
Remarque : DISTINCT trie aussi
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
# nombre d'enregistements de la tablesql <- "SELECT count(*) FROM Personnes;"count(*)
1 13# nombre d'enregistements pour un champsql <- "SELECT count(Nom) FROM Personnes;"count(*)
1 13sql <- "SELECT count(Distinct Nom) FROM Personnes;"count(Distinct Nom)
1 9sql <- "SELECT count(Téléphone) FROM Personnes;"count(Téléphone)
1 12
COUNT () Compter des enregistrements
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
sql <- "SELECT * FROM Localisation WHERE ville = 'VANVES' ;"id Adresse Code Ville
1 1 avenue Verdun 92170 VANVES
sql <- "SELECT * FROM Localisation WHERE Code < 92200 ;"id Adresse Code Ville
1 1 avenue Verdun 92170 VANVES2 3 rue Roissis 92140 CLAMART
WHERE Introduire des conditions de recherche
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
% symbole de troncature _ remplace 1 caractère
sql <- "SELECT * FROM Localisation WHERE Ville LIKE 'C%' ;"id Adresse Code Ville
1 2 rue Gay Lussac 92320 CHATILLON2 3 rue Roissis 92140 CLAMART
sql <- "SELECT * FROM Localisation WHERE Ville LIKE '%N%' ;"id Adresse Code Ville
1 1 avenue Verdun 92170 VANVES2 2 rue Gay Lussac 92320 CHATILLON
sql <- "SELECT * FROM Localisation WHERE Ville LIKE '_A%' ;"id Adresse Code Ville
1 1 avenue Verdun 92170 VANVES
LIKE rechercher à l'intérieur d'une chaîne de caractèresPrédicats : LIKE, BETWEEN, IN, ISNULL
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
sql <- "SELECT * FROM Localisation WHERE Code IN (92140, 92320) ;"
IN rechercher dans une liste de valeursPrédicats : LIKE, BETWEEN, IN, ISNULL
sql <- "SELECT * FROM Localisation WHERE Ville IN ('CHATILLON', 'CLAMART') ;"
id Adresse Code Ville1 2 rue Gay Lussac 92320 CHATILLON2 3 rue Roissis 92140 CLAMART
Troncature impossible
Exemples
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
sql <- "SELECT * FROM Localisation WHERE Code BETWEEN 92140 AND 92200 ;"
id Adresse Code Ville1 1 avenue Verdun 92170 VANVES2 3 rue Roissis 92140 CLAMART
BETWEEN rechercher entre deux bornes
Prédicats : LIKE, BETWEEN, IN, ISNULL
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
sql <- "SELECT * FROM Localisation WHERE Ville NOT LIKE '%N%' ;"
id Adresse Code Ville1 3 rue Roissis 92140 CLAMART
NOT …Prédicats : LIKE, BETWEEN, IN, ISNULL
Les commandes LIKE, IN et BETWEEN peuvent être utilisées avec le préfixe NOT
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
sql <- "SELECT Nom, Prénom FROM Personnes WHERE ISNULL (Téléphone);" # NOT ISNULL ()
sqlQuery(channel,sql)Nom Prénom
1 Meyer Michelsqldf(sql) # erreur
sql <- "SELECT Nom, Prénom FROM Personnes WHERE Téléphone ISNULL;"sqldf(sql)
Nom Prénom1 Meyer MichelsqlQuery(channel,sql) # erreur
sql <- "SELECT Nom, Prénom FROM Personnes WHERE Téléphone IS NULL;"# IS NOT NULL
sqldf(sql)sqlQuery(channel,sql)
Nom Prénom1 Meyer Michel
sql <- "SELECT Nom, Prénom FROM Personnes WHERE NOT Téléphone IS NULL;"
IS NULL rechercher des valeurs nulles
Prédicats : LIKE, BETWEEN, IN, ISNULL
24/04/2012
sql <- "SELECT * FROM Localisation WHERE Adresse Like '%s' OR Adresse Like '%n' ;"id Adresse Code Ville
1 1 avenue Verdun 92170 VANVES2 3 rue Roissis 92140 CLAMART
sql <- "SELECT * FROM Localisation WHERE Ville LIKE 'c%' AND Code > 92200 ;"id Adresse Code Ville
1 2 rue Gay Lussac 92320 CHATILLON
sql <- "SELECT * FROM Localisation WHERE NOT ville = 'VANVES' ;"# avec sqldf(sql) utilisation possible de !=
id Adresse Code Ville1 2 rue Gay Lussac 92320 CHATILLON2 3 rue Roissis 92140 CLAMART
sql <- "SELECT * FROM Localisation WHERE NOT Code = 92320 ;"# idem avec sql <- "SELECT * FROM Localisation WHERE Code <> 92320 ;"id Adresse Code Ville1 1 avenue Verdun 92170 VANVES2 3 rue Roissis 92140 CLAMART
OR, AND, NOTLier les conditions de recherche
opérateurs logiques OR, AND, NOT
AND
NOT
OR
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
GROUP BY Aggrégation des lignes
sql <- "SELECT Ville, count(*) AS Nb_personnes FROM HabitantsGROUP BY Ville ;"Ville Nb_personnes
1 CHATILLON 52 CLAMART 53 VANVES 3
Critères d'aggrégation : min, max, avg, sum ou une expression
sql <- "SELECT Ville, avg(numéro) AS Moy FROM HabitantsGROUP BY Ville ;"Ville Moy
1 CHATILLON 11.4000002 CLAMART 19.0000003 VANVES 3.666667
Permet faire un dénombrement (count) ou le calcul d'une fonction d'aggrégationpour les champs des lignes regroupées.
Nombre d'enregistrements par communeExemples :
Moyenne des numéros par commune
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
GROUP BY Réaliser un tableau de contingence
sql <- "SELECT Nom, sum(Ville = 'CHATILLON') AS CHATILLON,sum(Ville = 'CLAMART') AS CLAMART, sum(Ville = 'VANVES') AS VANVESFROM Habitants GROUP BY Nom, Ville ;"
Nom CHATILLON CLAMART VANVES1 Auquier 0 3 02 Berrue 0 0 23 Foucher 0 1 04 Larrouy 2 0 05 Lecesve 1 0 06 Lipinski 1 0 07 Mahier 0 0 18 Malher 1 0 09 Meyer 0 1 0
Nombre de téléphones par famille et par communes
tabsql <- sqldf(sql)str(tabsql)'data.frame': 9 obs. of 4 variables:$ Nom : Factor w/ 9 levels "Auquier","Berrue",..: $ CHATILLON: int 0 0 0 2 1 1 0 1 0$ CLAMART : int 3 0 1 0 0 0 0 0 1$ VANVES : int 0 2 0 0 0 0 1 0 0
rownames(tabsql) <- tabsql[,1](tabsql <- tabsql[,-1])
sel <- sqldf("SELECT Nom, Ville FROM Habitants ;")( tab <- table(sel) ) # class 'table' str(tab)as.data.frame(tab) # Résultat non attendu ! Pourquoi ?
class(tab) <- "matrix"as.data.frame(tab) # C'est mieux
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
HAVING Clause " Where" d'un GROUP BY
Exemple : Qui a plus d'un téléphone dans les villes ayant un CP > 92150 ?
sql <- "SELECT Nom, Prénom, Count(Téléphone) AS NbTéléphoneFROM Habitants WHERE Code > 92150GROUP BY Nom, PrénomHAVING Count (Téléphone) >= 2 ;"
Permet d'introduire une condition dans un GROUP BY
Nom Prénom NbTéléphone1 Berrue Christiane 2
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
Dans la clause WHERE
sql <- "SELECT Nom, Prénom, Ville FROM Localisation, Personnes WHERE Localisation.id = id_localisation ;"
Jointure entre tables 1 - Jointure interne
1.1 - Equi-jointure : relier avec une relation d'égalité des tables par un champ commun
Dans le FROM avec INNER JOIN ... ON
sql <- "SELECT Nom, Prénom, Ville FROM Localisation INNER JOIN Personnes ON Localisation.id = id_localisation ;"
Nom Prénom Ville1 Mahier Ludovic VANVES2 Berrue Christiane VANVES3 Berrue Christiane VANVES4 Lecesve André CHATILLON5 Larrouy Catherine CHATILLON6 Larrouy Eric CHATILLON7 Malher Goerges CHATILLON8 Lipinski Ludovic CHATILLON9 Meyer Michel CLAMART10 Foucher Georges CLAMART11 Auquier Anne CLAMART12 Auquier Anne CLAMART13 Auquier Bernard CLAMART
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
Jointure entre tables 1 - Jointure interne
1.2 - Auto-jointure : jointure d'une table avec elle-même
Exemple : Qui partage le même téléphone ?
sql <- "SELECT a.Nom, a.Prénom, a.Téléphone FROM Habitants a, Habitants bWHERE a.id <> b.id AND a.Téléphone = b.Téléphone;"
Nom Prénom Téléphone1 Larrouy Catherine 1409208412 Larrouy Eric 140920841
Rechercher des champs qui se trouvent à l'intérieur d'une même table.
Utilisation obligatoire d'un alias
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
Jointure entre tables 1 - Jointure interne
1.3 - Théta jointure : jointure avec un opérateur de comparaison autre que =
Exemple : Dans un ordre alphabétique, combien de villes sont après chaque ville ?
sql <- "SELECT a.Ville, COUNT(*) AS Reste FROM Localisation a, Localisation bWHERE a.Ville < b.Ville GROUP BY a.Ville;"
Ville Reste1 CHATILLON 22 CLAMART 1
Utilisation possible de : supérieur (>), inférieur (<), supérieur ou égal (>=), inférieur ou égal (<=), différent (<>).
La liste est-elle complète ?
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
Jointure entre tables
sql <- "SELECT count(*) FROM Localisation, Personnes ;"count(*)1 39
Vérification :sqldf ("SELECT count(*) FROM Personnes;") * sqldf("SELECT count(*) FROM Localisation;")count(*)
1 39
Remarques 1/2
Une jointure sans clause WHERE réalise produit cartésien des tables impliquées
Une jointure sur des champs non uniques
sql <- "SELECT P.Nom, P.Prénom, P.Téléphone FROMPersonnes P, Habitants H WHERE P.Nom= H.Nom AND P.Prénom= H.Prénom;"
Nom Prénom Téléphone1 Lecesve André 1465428562 Larrouy Catherine 1409208413 Larrouy Eric 1409208414 Malher Goerges 1465769865 Lipinski Ludovic 1473523296 Meyer Michel NA7 Foucher Georges 1464495018 Auquier Anne 1577500489 Auquier Anne 15775004810 Auquier Anne 63669900111 Auquier Anne 63669900112 Auquier Bernard 14642856413 Mahier Ludovic 14736126614 Berrue Christiane 14638143415 Berrue Christiane 14638143416 Berrue Christiane 95491235517 Berrue Christiane 954912355
réalise le produit entre ces champs
Comment éliminer ces doublons ?
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
Jointure entre tables
sql <- "SELECT P.Nom, P.Prénom, P.Téléphone FROM Personnes P, Habitants H WHERE P.Nom= H.Nom AND P.Prénom= H.Prénom AND P.Téléphone = H.Téléphone;"
ou
sql <- "SELECT P.Nom, P.Prénom, P.Téléphone FROM Personnes P inner join Habitants HON P.Nom= H.Nom AND P.Prénom= H.Prénom AND P.Téléphone = H.Téléphone;"
Jointure sur des champs contenant des valeurs nulles (NULL)
Nom Prénom Téléphone1 Lecesve André 1465428562 Larrouy Catherine 1409208413 Larrouy Eric 1409208414 Malher Goerges 1465769865 Lipinski Ludovic 1473523296 Foucher Georges 1464495017 Auquier Anne 1577500488 Auquier Anne 6366990019 Auquier Bernard 14642856410 Mahier Ludovic 14736126611 Berrue Christiane 14638143412 Berrue Christiane 954912355
Dans ce cas, il manque un nom car : Meyer Michel n'a pas de téléphone
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
Remarques 2/2
24/04/2012
Jointure entre tables 2 – Jointure externe
{ LEFT | (RIGHT } OUTER JOIN : pour inclure tous les enregistrements d'une table et seulement ceux de l'autre table pour lesquels les champs joints sont égaux.
FULL OUTER JOIN : pour inclure les enregistrements pour lesquels les champs joints sont égaux ou ont des valeurs nulles.
Exemple :
Utile quand les champs joints ont des valeurs nulles
Habitants LEFT OUTER JOIN Comm2ON Habitants.Code = Comm2.Code
sql <- "SELECT Habitants.Nom, Habitants.Prénom, Comm2.VilleFROM Habitants LEFT OUTER JOIN Comm2 ON Habitants.Code = Comm2.Code;"
24/04/2012
Jointure entre tables 2 – Jointure externe
LEFT OUTER JOINsql <- "SELECT Personnes.Nom, Personnes.Prénom, Personnes.Téléphone FROM
Personnes LEFT OUTER JOIN Habitants ON Personnes.Nom = Habitants.Nom ANDPersonnes.Prénom = Habitants.Prénom AND Personnes.Téléphone = Habitants.Téléphone ;"
Nom Prénom Téléphone1 Lecesve André 1465428562 Larrouy Catherine 1409208413 Larrouy Eric 1409208414 Malher Goerges 1465769865 Lipinski Ludovic 1473523296 Meyer Michel NA7 Foucher Georges 1464495018 Auquier Anne 1577500489 Auquier Anne 63669900110 Auquier Bernard 14642856411 Mahier Ludovic 14736126612 Berrue Christiane 14638143413 Berrue Christiane 954912355
Résultat identique avec :sql <- "SELECT P.Nom, P.Prénom, P.Téléphone
FROM Personnes P, Habitants H WHERE(P.Nom= H.Nom AND P.Prénom= H.Prénom)AND(P.Téléphone = H.Téléphone OR P.Téléphone is null;"
Remarque : sqldf : RIGHT AND FULL OUTER JOINs are not currently supportedMS Access : FULL OUTER JOIN n'existe pas
RIGHT OUTER JOINsql <- "SELECT Personnes.Nom, Personnes.Prénom, Personnes.Téléphone FROM
Habitants RIGHT OUTER JOIN Personnes ON Personnes.Nom = Habitants.Nom ANDPersonnes.Prénom = Habitants.Prénom AND Personnes.Téléphone = Habitants.Téléphone ;"
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
SELECT imbriqués Permet d'utiliser dans la clause WHERE d'un SELECT le résultat d'un autre SELECT
La sous requête peut retourner un sous ensemble de 0 à n valeurs.
En fonction de la condition de la clause WHERE du SELECT principal, on utilisera pour la liaison entre le champ du SELECT principal et les valeurs renvoyées par la sous requête :
un des opérateurs de comparaison : =, >, <, >=, <=,<> un des prédicats : IN, ANY, ALL ou EXISTS
Suivant la condition souhaitée, le prédicat doit être :IN Réalise l'égalité avec un OU entre toutes valeurs ANY Vérifie si au moins une des valeurs satisfait la conditionALL Vérifier si toutes les valeurs satisfont la conditionEXISTS La condition est VRAIE si n valeurs retournées > 0, FAUSSE si n = 0
SELECT ... WHERE champ { opérateurs | prédicats } (SELECT ...
S'utilise souvent à la place d'une la jointure
24/04/2012
sql <- "SELECT a.Nom, a.prénom, a.numéro, a.AdresseFROM Habitants aWHERE a.numéro <= (SELECT avg(b.numéro) FROM Habitants b
GROUP BY Adresse HAVING b.Adresse = a.Adresse) ;"
Corrélation
Nom Prénom numéro Adresse1 Lecesve André 9 rue Gay Lussac2 Larrouy Catherine 10 rue Gay Lussac3 Larrouy Eric 10 rue Gay Lussac4 Meyer Michel 15 rue Roissis5 Foucher Georges 17 rue Roissis6 Mahier Ludovic 3 avenue Verdun
Exemple : Nom des habitants dont l'adresse est dans la première moitié de la rue
SELECT imbriqués
Cas particulier :Quand une condition fait intervenir le même champ de la même table
Utilisation obligatoire d'alias
corrélation
Exécution de la requête SQL :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
IN, EXISTS SELECT imbriqués
sql <- "SELECT Adresse, Ville FROM LocalisationWHERE id IN (SELECT id_localisation FROM Personnes WHERE Téléphone IS NULL ) ;"
Exemples d'utilisation
sql <- "SELECT DISTINCT Nom, Prénom , Depuis FROM PersonnesWHERE Depuis IN ( SELECT Depuis FROM Personnes
WHERE nom = 'Larrouy' AND Prénom ='Eric' )AND NOT (nom = 'Larrouy' AND Prénom ='Eric') ;"
Dans quelle(s) rue(s) des habitants n'ont pas de téléphone ?
# Avec une corrélation et EXISTS
Quel(s) habitant(s) ont la même ancienneté qu'Eric Larrouy ?
IN pour tester l'égalité avec toutes valeurs prises une par une (OU)
sql <- "SELECT DISTINCT Nom, Prénom , Depuis FROM Personnes aWHERE EXISTS ( SELECT * FROM Personnes b
WHERE b.nom = 'Larrouy' AND b.Prénom ='Eric'AND b.Depuis = a.Depuis )
AND NOT (a.nom = 'Larrouy' AND a.Prénom ='Eric') ;"
Pourquoi DISTINCT ?
Exécution requête :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
ANY, ALL SELECT imbriqués
sql <- "SELECT DISTINCT Nom, Prénom , Depuis FROM PersonnesWHERE Depuis >= ANY ( SELECT Depuis FROM Personnes
WHERE nom = 'Larrouy' AND Prénom ='Eric' )AND NOT (nom = 'Larrouy' AND Prénom ='Eric') ;"
Exemples d'utilisation
sql <- "SELECT DISTINCT Nom, Prénom ,Ville, Depuis FROM Habitants WHERE Depuis < ALL ( SELECT Depuis FROM Habitants
WHERE Adresse = 'avenue Verdun' AND Code = 92170 ) ;"
# comparer le résultat en utilisant ANY
Quels habitants ont une ancienneté identique ou plus faible de celle d'Eric Larrouy ?
Quels habitants ont une ancienneté plus élevée que ceux de l'avenue Verdun àVANVES (92170)
ANY pour tester si au moins une des valeurs satisfait la condition
ALL pour tester si toutes les valeurs satisfont la condition
Habitants VanvesDepuis
1 1983-05-072 1985-10-213 1985-10-21
Exécution requête :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
UNION
sql1 <- "SELECT DISTINCT Ville, Code FROM Localisation WHERE Ville LIKE 'c%' "sqldf(sql1)
Ville Code1 CHATILLON 923202 CLAMART 92140
Les requêtes doivent impérativement retourner des lignes de composition identique :même champs à la même position
Pour fusionner les lignes issues de deux requêtes
sql2 <- "SELECT DISTINCT Ville, Code FROM Habitants WHERE NOT Ville LIKE 'c%' ;"sqldf(sql2)
Ville Code1 VANVES 92170
Union des requêtes sql1 et sql2sql <- paste (sql1, "UNION", sql2)sqldf(sql)
Ville Code1 CHATILLON 923202 CLAMART 921403 VANVES 92170
Et avecsql2 <- "SELECT DISTINCT Ville, Code, numéro FROM Habitants WHERE NOT Ville LIKE 'c%'"sqlQuery(channel,sql) # [1] "[RODBC] ERROR: Could not SQLExecDirect" sqldf(sql) # Erreur dans sqliteExecStatement
Faire attention au ;qui doit être absent de la première requête(non obligatoire avec ACCESS)
24/04/2012
INTERSECTSélection des lignes communes aux deux requêtes
Utiliser des requêtes imbriquées Utiliser des jointures ...
S'utilise comme UNION
Si non implémenté dans le SQBDR
sql1 <- "SELECT Ville, Code FROM Localisation"sqldf(sql1)
sql2 <- "SELECT Ville, Code FROM Comm2"sqldf(sql2)
Intersection des requêtes sql1 et sql2sql <- paste(sql1,"INTERSECT",sql2)sqldf (sql)
Ville Code1 VANVES 921702 CHATILLON 923203 CLAMART 92140
Ville Code1 CHATILLON 923202 CLAMART 921403 MALAKOFF 92240
Ville Code1 CHATILLON 923202 CLAMART 92140
sqlQuery(channel,sql) # [1] "[RODBC] ERROR
24/04/2012
INTERSECT
sql <- "SELECT L.Ville, L.Code FROM Localisation L, Comm2 C WHERE L.Ville = C.Ville AND L.Code = C.Code ;"
Si non implémenté dans le SQBDRComment faire ?
En utilisant des jointures
sql <- "SELECT * FROM Comm2 C WHERE EXISTS (SELECT * FROM Localisation L
WHERE L.Ville = C.Ville AND L.Code = C.Code) ;"
De même :
sql <- "SELECT L.Ville, L.Code FROM Localisation L WHERE EXISTS (SELECT * FROM Comm2 C
WHERE L.Ville = C.Ville AND L.Code = C.Code) ;"
Utiliser des requêtes imbriquéesExécution requête :sqldf (sql)sqlQuery (channel, sql)
24/04/2012
EXCEPTSélection des lignes différentes d'une requête par rapport à l'autreS'utilise comme UNION
Utiliser des requêtes imbriquées Utiliser des jointures ...Si non implémenté dans le SQBDR
sqlQuery(channel,sql) # [1] "[RODBC] ERROR
sql1 <- "SELECT Ville, Code FROM Localisation"sqldf(sql1)
sql2 <- "SELECT Ville, Code FROM Comm2"sqldf(sql2)
Lignes de sql1 non dans sql2sql <- paste(sql1,"EXCEPT",sql2)sqldf (sql)
Ville Code1 VANVES 921702 CHATILLON 923203 CLAMART 92140
Ville Code1 CHATILLON 923202 CLAMART 921403 MALAKOFF 92240
Ville Code1 MALAKOFF 92240
Lignes de sql2 non dans sql1sql <- paste(sql2,"EXCEPT",sql1)sqldf (sql)
Ville Code1 VANVES 92170
24/04/2012
EXCEPT
# Communes de Localisation non présentent dans Comm2sql1 <- "SELECT L.Ville, L.Code FROM Localisation L WHERE NOT EXISTS (SELECT * FROM Comm2 C
WHERE L.Ville = C.Ville AND L.Code = C.Code);"Ville Code
1 VANVES 92170
# Communes de Comm2 non présentent dans Localisation sql2 <- "SELECT * FROM Comm2 C WHERE NOT EXISTS (SELECT * FROM Localisation L
WHERE L.Ville = C.Ville AND L.Code = C.Code);"id Ville Code
1 3 MALAKOFF 92240
En utilisant des jointures
En utilisant des requêtes imbriquées
# Communes de Localisation non présentent dans Comm2sql1 <- "SELECT L.Ville, L.Code FROM Localisation L LEFT JOIN Comm2 C
ON L.Ville = C.Ville AND L.Code = C.Code WHERE C.Code IS NULL ;"
Ville Code1 VANVES 92170
# Communes de Comm2 non présentent dans Localisation sql2 <- "SELECT C.Ville, C.Code FROM Comm2 C LEFT JOIN Localisation L
ON L.Ville = C.Ville AND L.Code = C.Code WHERE L.Code IS NULL ;"
Ville Code1 MALAKOFF 92240
Avec LEFT JOIN
sqldf(paste(sql1, "UNION", sql2))
Si non implémenté dans le SQBDRComment faire ?
Exécution requête :sqldf (sql1)sqlQuery (channel, sql1)
24/04/2012
DELETE * FROM table [alias] [ WHERE { conditions de recherche | sous conditions} ] ;
Commande DELETE
Légende :{ } : Une des valeurs
séparées par '|' obligatoire.
[ ] : Valeur optionnelle.# Création de la table de travail Personne2Personnes <- sqlQuery(channel,"SELECT * FROM Personnes;")sqlSave(channel, Personnes[,-7],"Personne2",rownames=FALSE) sqlQuery(channel,"SELECT * FROM Personne2;")
pour supprimer des lignes d'une tableSyntaxe
sql <- "DELETE * FROM Personne2 WHERE Téléphone IS NULL"
Exemples
Suppression dans la table Personne2 des lignes où le champ Téléphone est nul
sql <- "DELETE * FROM Personne2 Suppression de toutes les lignes de la table Personne2
Remarque : la définition de la table est conservée# VérificationsqlQuery (channel, "SELECT * FROM Personne2;")
Exécution requête :sqlQuery (channel, sql)
24/04/2012
INSERT INTO table [ (col [, col ] ...)]{ VALUES ( val [, val ] ...) | SELECT }
Commande INSERTpour insérer des lignes dans une table
Syntaxe
Exemple
Insérer dans la table Personne2 le contenu des champs Nom, Prénom, numéro, id_localisation, Téléphone à partir de la table Personnes pour les habitants de Clamart.
Légende :{ } : Une des valeurs
séparées par '|' obligatoire.
[ ] : Valeur optionnelle... : Répétition
sql <- "INSERT INTO Personne2 ( Nom, Prénom, numéro, id_localisation, Téléphone )SELECT DISTINCT P.Nom, P.Prénom, P.numéro, P.id_localisation, P.TéléphoneFROM Localisation L, Personnes P WHERE L.id = P.id_localisation AND L.Ville='CLAMART' ;"
# VérificationsqlQuery (channel, "SELECT * FROM Personne2;") # Que remarque-t-on et pourquoi ?
(afficher Habitants)
Exécution requête :sqlQuery (channel, sql)
24/04/2012
UPDATE table [alias] [ INNER JOIN . ON ]SET { col = expr [, col = expr ] ... | ( col [, col ] ... ) = ( SELECT ) }[ WHERE conditions de recherche ]
Commande UPDATEpour modifier le contenu des champs d'une tableSyntaxe
Exemples
Mettre à jour le champ id de la table Personne2 à partir de la table Personnes.
Légende :{ } : Une des valeurs
séparées par '|' obligatoire.
[ ] : Valeur optionnelle... : Répétition
sql <- "UPDATE Personne2 INNER JOIN Personnes ON (Personne2.Téléphone = Personnes.Téléphone)
AND (Personne2.Prénom = Personnes.Prénom)AND (Personne2.Nom = Personnes.Nom)
SET Personne2.id = Personnes.id ;"
(afficher Habitants)
sql <- "UPDATE Personne2 SET id = '999' WHERE Nom ='Foucher' " Mettre 999 dans le champ id de la table Personne2 pour Foucher
# VérificationsqlQuery (channel, "SELECT * FROM Personne2;")
Exécution requête :sqlQuery (channel, sql)
24/04/2012
CREATE TABLE table ( col datatype [DEFAULT expr] [constraint] [, col datatype [DEFAULT expr] [constraint] ... ] )
Commande CREATE TABLEpour créer une tableSyntaxe
ExempleLégende :[ ] : Valeur optionnelle... : Répétition
sql <- "CREATE TABLE New (col1 varchar(20), col2 integer) ;"
sqlQuery (channel,sql)
# VérificationsqlColumns (channel, "New")[, 3:7]
Créer la table New avec deux colonnes
TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE1 New col1 12 VARCHAR 202 New col2 4 INTEGER 10
DDL
Exécution requête :sqlQuery (channel, sql)
24/04/2012
DROP TABLE table
Commande DROP TABLEpour supprimer une table
Syntaxe
Exemple
sql <- "DROP TABLE New ;"
sql <- "DROP TABLE Personne2 ;"
# Vérification
sqlTables(channel)$TABLE_NAME[-c(1:6)]
Supprimer les tables New et Personne2
# Lister le nom des tables de la basesqlTables (channel)$TABLE_NAME [-c(1:6)]
DDL
Exécution requête :sqlQuery (channel, sql)
24/04/2012
Exercice
Créer une table "communes" à partir de la table "localisation" avec uniquement les communes CHATILLON et CLAMART et en y ajoutant MALAKOFF (92240).La table commune n'a que les champs "Ville" et "Code".
24/04/2012
Exercice
Créer une table "communes" à partir de la table "localisation" avec uniquement les communes CHATILLON et CLAMART et en y ajoutant MALAKOFF (92240).La table commune n'a que les champs "Ville" et "Code".
# 1 - Création de la table comex à partir d'un SELECTsql <- "SELECT DISTINCT Ville, Code FROM Localisation WHERE Ville LIKE 'c%';"sqlCopy(channel, sql, "comex",rownames="id", addPK=TRUE) # par défaut, dans la basesqlQuery(channel,"SELECT * FROM comex ;") # Vérification
# 2 - Création du data.frame avec la nouvelle commune à ajouternewcom <- as.data.frame(cbind("MALAKOFF","92240"))str(newcom ) # Problèmes : factornewcom [,1] <- as.character(newcom[,1])newcom [,2] <- as.integer(as.character(newcom[,2]))# Obligatoire :colnames(newcom) <- c("Ville","Code") # noms champs de la tablerownames(newcom) <- "3" # bonne valeur de l'index ("id")
# 3 - Ajout du data.frame à la tablesqlSave(channel, newcom ,tablename ="comex",
rownames = "id", addPK = TRUE, append = TRUE)# Vérification
sqlQuery(channel,"SELECT * FROM comex ;")