A database is a structured collection of records or data that is stored in a computer system. A database relies upon software to organize the storage of data.<\/p>\n
A query is a line of text that a user sends to a database telling it what you want it to do. It is usually written in SQL (Structured query language).<\/p>\n
Example of SQL query:<\/p>\n
select * from books where price < 20.00 order by title;<\/pre>\nThe term “relational database” comes from “Relation”, a mathematical term loosely meaning “table”, and thus “relational” roughly means “based on tables”.<\/p>\n
MySQL is a popular database management system within which you can set up and query databases. Many web applications use databases. Youtube, flickr, and wikipedia are all built on MySQL databases. A lot of biological databases (e.g. GO, ensembl) are already available in mysql database tables, or are easily input into a database for easy querying.<\/p>\n
For example, lets say I have a database table with yeast genes and some information about them (created by parsing a text file from SGD):<\/p>\n
<\/p>\n
Here's the first 5 rows of the table, as an example.\n\nmysql> select id,chrom,start,end,strand,sys_id,gene_name from yeast_genes_07 limit 5;\n+----+-------+-------+------+--------+-----------+-----------+\n| id | chrom | start | end | strand | sys_id | gene_name |\n+----+-------+-------+------+--------+-----------+-----------+\n| 1 | 1 | 335 | 649 | W | YAL069W | NULL |\n| 2 | 1 | 538 | 792 | W | YAL068W-A | NULL |\n| 3 | 1 | 1807 | 2169 | C | YAL068C | NULL |\n| 4 | 1 | 2480 | 2707 | W | YAL067W-A | NULL |\n| 5 | 1 | 7236 | 9017 | C | YAL067C | SEO1 |\n+----+-------+-------+------+--------+-----------+-----------+\n5 rows in set (0.00 sec)<\/pre>\nUsing a database, I can slice and dice the information in this table using almost-english queries like this:<\/p>\n
<\/p>\n
select * from yeast_genes_07 where strand = 'W' and start < 5000 and chrom = 5;<\/pre>\nwhich means – show me all the information about genes whose strand is W, start is less than 5000, and are on chromosome 5.<\/p>\n
We have a bunch of databases here at stowers. Additionally, MySQL is installed by default on pretty much any linux system I’ve ever run into.<\/p>\n
<\/p>\n
RMySQL<\/h3>\n
RMySQL is a little tricky to install, depending on your version of R. Here’s some places to look for more info if you have trouble:http:\/\/www.phaget4.org\/R\/R_notes.html<\/a>
\nhttp:\/\/tolstoy.newcastle.edu.au\/R\/e2\/help\/07\/10\/28442.html<\/a><\/p>\nI currently have it installed in R 2.4.0, but couldn’t get it to work in 2.5.1.<\/p>\n
Download from:
\nCRAN (non-windows only)<\/a>
\nBrian Ripley, windows versions<\/a><\/p>\nRMySQL allows you to access MySQL databases from within R.<\/p>\n
Why would you want to do this?<\/p>\n
Because writing SQL queries is intuitive and powerful. And a database is a good place for information that you want to access in different, flexible ways.<\/p>\n
<\/p>\n
RMySQL Examples<\/h2>\n
This is actually how I most often use RMySQL, just to slurp in a whole database table at once. I do this because I like storing information in databases so it is easy to access from R, perl, php, or within mysql itself – Once a data set is in the database, I don’t have to worry about parsing a file anytime I want to look at it, I just grab it from the database without worry about delimiters or column order.<\/p>\n
<\/p>\n
library(DBI)\n\nlibrary(RMySQL)\n\nm<-dbDriver(\"MySQL\");\ncon<-dbConnect(m,user='mcm',password='welcome323',host='omega',dbname='yeast');\nres<-dbSendQuery(con, \"select * from yeast_genes_07\")\ngenes<- fetch(res, n = -1)<\/pre>\nYou can do whatever mysql query you want, though. You can grab only the parts of the table you’re interested in based on different column values.<\/p>\n
res<-dbSendQuery(con, \"select * from yeast_genes_07 where description like \\\"%phosphatase%\\\"\")\nphosphatase_genes<- fetch(res, n = -1)\n\nres<-dbSendQuery(con, \"select chrom,start,end,sys_id,strand from yeast_genes_07 where strand = \\\"C\\\" and chrom = 1\")\n\nc1_genes<- fetch(res, n = -1)<\/pre>\n<\/p>\n
RODBC<\/h3>\n
You can use the RODBC package to connect to an excel file or access database. Download from:\u00a0http:\/\/cran.r-project.org\/web\/packages\/RODBC\/index.html<\/a><\/p>\n
<\/p>\n
RODBC-Excel example<\/h2>\n
Warning – the excel file has to have a pretty regular format or lots of glitches will pop up.<\/p>\n
# efg, 6 August 2007\n# modified by mcm, 4\/14\/2008\n\nlibrary(RODBC)\n\nsetwd(\"U:\/mcm\/presentations\/RDisc\/db\/\");\nfilename <- \"yeast_genes_07.xls\"\nchannel <- odbcConnectExcel(filename)\nsqlTables(channel)\nd <- sqlFetch(channel,\"yeast_genes_07\", as.is=TRUE) #this is the sheet name\nodbcClose(channel)\nnames(d)\n\n# Any SQL query can be used. (Note \"$\" on sheet name is needed here)\nchannel <- odbcConnectExcel(filename)\n\n#pick some columns from the table\n\ns <- sqlQuery(channel, \"select chrom,start,end,sys_id from [yeast_genes_07$]\" )\ns\n\n#pick only chromosome 3\ns <- sqlQuery(channel, \"select chrom,start,end,sys_id from [yeast_genes_07$] where chrom = 3\" )\ns\n\n#chromosome 3 and strand W\ns <- sqlQuery(channel, \"select chrom,start,end,sys_id from [yeast_genes_07$] where chrom = 3 and strand = \\'W\\' \" )\ns\n\n#usually, if you want to limit the number of rows you retrieve, you use limit. This should work, but doesn't.\ns <- sqlQuery(channel, \"select chrom,start,end,sys_id from [yeast_genes_07$] where chrom = 3 and strand = \\'W\\' limit 5\")\n\ns\n\n#if you need to limit the number of rows you retrieve with RODBC, use the max argument.\ns <- sqlQuery(channel, \"select chrom,start,end,sys_id from [yeast_genes_07$] where chrom = 3 and strand = \\'W\\'\" ,max=5)\ns\n\nodbcClose(channel)<\/pre>\n<\/p>\n
RODBC-Access example<\/h2>\n
<\/p>\n
library(RODBC)\nfilename <- \"S:\/Bioinformatics\/Software\/Affy\/Data\/Mouse430_2\/Mouse430_2.mdb\"\nconnection <- odbcConnectAccess(filename)\n\nsqlTables(connection)\n\n# fetch the whole table (fairly slow)\ncodes <- sqlFetch(connection,\"Annotation\")\n\n# fetch only ProbeIDs, GeneID and Gene title (much faster) \nquery <- \"select [Probe Set ID], [Gene Title], [Gene Symbol] from Annotation\" \ncodes <- sqlQuery(connection, query)\nodbcClose(connection)\n\nfilename <- \"S:\/Bioinformatics\/Software\/Affy\/Data\/MG_U74Av2\/MG_U74Av2.mdb\"\nconnection <- odbcConnectAccess(filename)\n\nsqlTables(connection)\n\nquery <- \"SELECT [Probe Set ID], [Gene Title], [Gene Symbol] FROM Annotation\" \nd2 <- sqlQuery(connection, query)\ndim(d2)\n\nquery <- \"SELECT [Probe Set ID], [Gene Title], [Gene Symbol] FROM Annotation WHERE [Gene Symbol] = 'Tfrc'\" \nd3 <- sqlQuery(connection, query, na.strings=\"NA\", as.is=TRUE)\nd3\n\n# Use \"%\" as a wildcard, not the \"*\" allowed in Access\nquery <- \"SELECT [Probe Set ID], [Gene Symbol], [RefSeq Transcript ID]\n\n FROM Annotation\n WHERE ([Gene Symbol] LIKE 'Tf%') AND ([RefSeq Transcript ID] LIKE 'NM_%')\n ORDER BY [Gene Symbol]\"\nd4 <- sqlQuery(connection, query, na.strings=\"NA\", as.is=TRUE)\nd4\n\nodbcClose(connection)<\/pre>\n<\/p>\n
Earl’s RODBC notes<\/h2>\n
<\/p>\n
DO NOT USE RODBC with Excel files unless the data are quite regular - no holes. \n\nAny character fields mixed with numeric fileds are likely to be changed to NAs. \n\nFirst row of Excel spreadsheet MUST have column headings.\n\n[http:\/\/www.omegahat.org\/RDCOMClient\/ RDCOMClient] is a package you can alternatively use to connect to an excel file, but it's not SQL-based. You can also save an excel file as text, tab delimited and then read it into R with the read.table command.<\/pre>\n<\/p>\n
R-database resources<\/h2>\n
R data import and export manual<\/a>\u00a0R database mailing list<\/a>\u00a0(or google with term r-sig-db).<\/p>\n
<\/p>\n
Other MySQL resources<\/h2>\n
Pronunciation: “MySQL” is officially pronounced My Ess Q ell, not “My sequel”. Just FYI.
\nmysql.com<\/a>
\nmanuals<\/a>
\nMySQL query browser (easy mysql interface)<\/a>
\nmysql cheat sheet<\/a>
\nanother cheat sheet<\/a>
\nand some more mysql queries<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"Definitions – Databases, Queries, Relational Databases, SQL, MySQL A database is a structured collection of records or data that is stored in a computer system.… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-341","post","type-post","status-publish","format-standard","hentry","category-r"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/341","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=341"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/341\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}