MySQL: Difference between revisions
No edit summary |
No edit summary |
||
Line 1: | Line 1: | ||
== Under Shell == | |||
=== Create a new user account to grant access to a database === | |||
http://www.cyberciti.biz/faq/mysql-user-creation/ | |||
'''NOTE''': please be mindful when creating a user name. There is no protection against creating an old user's name. If you accidentally create a same user name as say mediawiki DB user, then mediawiki will not be working. | |||
<pre> | |||
$ mysql -u root -p | |||
OR | |||
$ mysql -u root -h myserver-sever.com -p | |||
</pre> | |||
Create a new mysql database called demo; | |||
<pre> | |||
mysql> CREATE DATABASE testdb; | |||
</pre> | |||
Create a new user called user1 for database demo | |||
<pre> | |||
mysql> GRANT ALL ON testdb.* TO user1@localhost IDENTIFIED BY 'mypassword'; | |||
</pre> | |||
User user1 can connect to mysql server demo database using following command: | |||
<pre> | |||
$ mysql -u user1 -p testdb | |||
OR | |||
$ mysql -u user1 -h mysql.server.com -p testdb | |||
</pre> | |||
=== MYSQL commands === | |||
http://www.pantz.org/software/mysql/mysqlcommands.html | |||
Find out about your MySQL version and all installed [http://www.howtoforge.com/how-to-install-mysql-5.6-on-ubuntu-12.10-including-memcached-plugin-p2 plugins]: | |||
<pre> | |||
mysql> select version(); | |||
mysql> show plugins; | |||
</pre> | |||
=== Default database location === | |||
On debian, it is '''/var/lib/mysql'''. | |||
=== phpMyAdmin === | |||
http://localhost/phpmyadmin/ | |||
== Use through R == | |||
Examples from r-bloggers | |||
* http://www.r-bloggers.com/mysql-and-r/ | |||
* http://www.r-bloggers.com/accessing-mysql-through-r/ | |||
* http://gettinggeneticsdone.blogspot.com/2011/05/accessing-databases-from-r-rstats-sql.html | |||
* [http://files.meetup.com/1781511/useR%20Vignette%20-%20Accessing%20Databases%20from%20R%20-%2020110504.pdf useR Vignette from Jeffrey Breen] | |||
* [http://www.stat.berkeley.edu/~spector/sql.pdf Phil Spector] | |||
=== Installation === | |||
* http://cran.r-project.org/web/packages/RMySQL/INSTALL | |||
First, log in using root. | |||
<pre> | |||
install.packages("DBI") | |||
</pre> | |||
Go to shell and | |||
<pre> | |||
apt-get install libdbd-mysql libmysqlclient16-dev (not sure if this is necessary) | |||
http://cran.r-project.org/src/contrib/RMySQL_0.9-3.tar.gz | |||
R CMD INSTALL RMySQL*.tar.gz | |||
</pre> | |||
=== Open and close connection: dbConnect/dbDisconnect === | |||
<pre> | |||
con <- dbConnect(MySQL(), | |||
user="me", password="nuts2u", | |||
dbname="my_db", host="localhost") | |||
on.exit(dbDisconnect(con)) | |||
</pre> | |||
=== List tables and fields: dbListTables and dbListFields === | |||
<pre> | |||
dbListTables(mydb) | |||
dbListFields(mydb, 'some_table') | |||
</pre> | |||
=== Read and write entire tables: dbReadTable and dbWriteTable === | |||
We can create tables in the database using R dataframes. | |||
<pre> | |||
df = dbReadTable(con, 'motortrend') | |||
dbWriteTable(mydb, name='table_name', value=data.frame.name) | |||
</pre> | |||
=== Query: dbGetQuery and dbSendQuery === | |||
You can process query results row by row, in blocks or all at once. The highly useful function '''dbGetQuery(con, sql)''' returns all query results as a data frame. With '''dbSendQuery''', you can get all or partial results with fetch. | |||
<pre> | |||
con <- dbConnect(MySQL(), user="network_portal", password="monkey2us", dbname=db.name, host="localhost") | |||
rs <- dbSendQuery(con, "select name from genes limit 10;") # results in in mysql | |||
data <- fetch(rs, n=10) # return result to R as a data frame; use n=-1 to retrieve all pending records. | |||
huh <- dbHasCompleted(rs) | |||
dbClearResult(rs) | |||
dbDisconnect(con) | |||
</pre> | |||
=== Aggregate and Sort === | |||
<pre> | |||
df = dbGetQuery(con, "SELECT mfg, avg(hp) AS meanHP FROM motortrend GROUP BY mfg ORDER BY meanHP DESC") | |||
df = dbGetQuery(con, "SELECT cyl as cylinders, avg(hp) as meanHP FROM motortrend GROUP by cyl ORDER BY cyl") | |||
</pre> | |||
=== dbApply() === | |||
<pre> | |||
sql = "SELECT cyl, hp FROM motortrend ORDER BY cyl" | |||
rs = dbSendQuery(con, sql) | |||
dbApply(rs, INDEX='cyl', FUN=function(x, grp) quantile(x$hp)) | |||
</pre> | |||
=== Retrieving AUTO_INCREMENT IDs === | |||
* http://www.w3schools.com/sql/sql_autoincrement.asp | |||
<pre> | |||
create.network <- function(species.id, network.name, data.source, description) { | |||
con <- dbConnect(MySQL(), | |||
user="super_schmuck", password="nuts2u", | |||
dbname="my_db", host="localhost") | |||
on.exit(dbDisconnect(con)) | |||
sql <- sprintf("insert into networks | |||
(species_id, name, data_source, description, created_at) | |||
values (%d, '%s', '%s', '%s', NOW());", | |||
species.id, network.name, data.source, description) | |||
rs <- dbSendQuery(con, sql) | |||
dbClearResult(rs) | |||
id <- dbGetQuery(con, "select last_insert_id();")[1,1] | |||
return(id) | |||
} | |||
</pre> | |||
=== Full example from Jeffrey Breen === | |||
http://www.r-bloggers.com/slides-%E2%80%9Caccessing-databases-from-r%E2%80%9D-rstats/ | |||
First, create new database & user in MySQL: | |||
<pre> | |||
mysql> create database testrdb; | |||
mysql> grant all privileges on testrdb.* to 'testuser'@'localhost' identified by 'testpass'; | |||
mysql> flush privileges; | |||
</pre> | |||
In R, load the “mtcars” data.frame, clean it up, and write it to a new “motortrend” table: | |||
<pre> | |||
library(stringr) | |||
library(RMySQL) | |||
data(mtcars) | |||
# car name is data.frame's rownames. Let's split into manufacturer and model columns: | |||
mtcars$mfg = str_split_fixed(rownames(mtcars), ' ', 2)[,1] | |||
mtcars$mfg[mtcars$mfg=='Merc'] = 'Mercedes' | |||
mtcars$model = str_split_fixed(rownames(mtcars), ' ', 2)[,2] | |||
# connect to local MySQL database (host='localhost' by default) | |||
con = dbConnect("MySQL", "testrdb", username="testuser", password="testpass") | |||
dbWriteTable(con, 'motortrend', mtcars) | |||
dbDisconnect(con) | |||
</pre> | |||
And check the database from mysql | |||
<pre> | |||
mli@PhenomIIx6:~$ mysql -u testuser -p | |||
Enter password: | |||
Welcome to the MySQL monitor. Commands end with ; or \g. | |||
Your MySQL connection id is 153 | |||
Server version: 5.1.63-0ubuntu0.11.10.1 (Ubuntu) | |||
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. | |||
Oracle is a registered trademark of Oracle Corporation and/or its | |||
affiliates. Other names may be trademarks of their respective | |||
owners. | |||
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. | |||
mysql> show databases; | |||
+--------------------+ | |||
| Database | | |||
+--------------------+ | |||
| information_schema | | |||
| testrdb | | |||
+--------------------+ | |||
2 rows in set (0.00 sec) | |||
mysql> use testrdb; | |||
Reading table information for completion of table and column names | |||
You can turn off this feature to get a quicker startup with -A | |||
Database changed | |||
mysql> show tables; | |||
+-------------------+ | |||
| Tables_in_testrdb | | |||
+-------------------+ | |||
| motortrend | | |||
+-------------------+ | |||
1 row in set (0.00 sec) | |||
mysql> describe motortrend; | |||
+-----------+--------+------+-----+---------+-------+ | |||
| Field | Type | Null | Key | Default | Extra | | |||
+-----------+--------+------+-----+---------+-------+ | |||
| row_names | text | YES | | NULL | | | |||
| mpg | double | YES | | NULL | | | |||
| cyl | double | YES | | NULL | | | |||
| disp | double | YES | | NULL | | | |||
| hp | double | YES | | NULL | | | |||
| drat | double | YES | | NULL | | | |||
| wt | double | YES | | NULL | | | |||
| qsec | double | YES | | NULL | | | |||
| vs | double | YES | | NULL | | | |||
| am | double | YES | | NULL | | | |||
| gear | double | YES | | NULL | | | |||
| carb | double | YES | | NULL | | | |||
| mfg | text | YES | | NULL | | | |||
| model | text | YES | | NULL | | | |||
+-----------+--------+------+-----+---------+-------+ | |||
14 rows in set (0.01 sec) | |||
mysql> SELECT * FROM motortrend WHERE row_names like "Mazda%" order by mpg; | |||
+---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+ | |||
| row_names | mpg | cyl | disp | hp | drat | wt | qsec | vs | am | gear | carb | mfg | model | | |||
+---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+ | |||
| Mazda RX4 | 21 | 6 | 160 | 110 | 3.9 | 2.62 | 16.46 | 0 | 1 | 4 | 4 | Mazda | RX4 | | |||
| Mazda RX4 Wag | 21 | 6 | 160 | 110 | 3.9 | 2.875 | 17.02 | 0 | 1 | 4 | 4 | Mazda | RX4 Wag | | |||
+---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+ | |||
2 rows in set (0.00 sec) | |||
mysql> SELECT row_names, wt, model FROM motortrend WHERE row_names like "Mazda%" order by wt; | |||
+---------------+-------+---------+ | |||
| row_names | wt | model | | |||
+---------------+-------+---------+ | |||
| Mazda RX4 | 2.62 | RX4 | | |||
| Mazda RX4 Wag | 2.875 | RX4 Wag | | |||
+---------------+-------+---------+ | |||
2 rows in set (0.00 sec) | |||
mysql> | |||
</pre> | |||
== RODBC == | |||
== SQLite == | |||
== Find database online == | |||
* http://www.inside-r.org/howto/finding-data-internet | |||
* http://www.r-bloggers.com/list-of-public-databases-from-the-washington-post/ | |||
== Create a user and grant permission in mysql == | == Create a user and grant permission in mysql == | ||
http://www.cyberciti.biz/faq/mysql-user-creation/ | http://www.cyberciti.biz/faq/mysql-user-creation/ |
Revision as of 09:58, 27 November 2012
Under Shell
Create a new user account to grant access to a database
http://www.cyberciti.biz/faq/mysql-user-creation/
NOTE: please be mindful when creating a user name. There is no protection against creating an old user's name. If you accidentally create a same user name as say mediawiki DB user, then mediawiki will not be working.
$ mysql -u root -p OR $ mysql -u root -h myserver-sever.com -p
Create a new mysql database called demo;
mysql> CREATE DATABASE testdb;
Create a new user called user1 for database demo
mysql> GRANT ALL ON testdb.* TO user1@localhost IDENTIFIED BY 'mypassword';
User user1 can connect to mysql server demo database using following command:
$ mysql -u user1 -p testdb OR $ mysql -u user1 -h mysql.server.com -p testdb
MYSQL commands
http://www.pantz.org/software/mysql/mysqlcommands.html
Find out about your MySQL version and all installed plugins:
mysql> select version(); mysql> show plugins;
Default database location
On debian, it is /var/lib/mysql.
phpMyAdmin
Use through R
Examples from r-bloggers
- http://www.r-bloggers.com/mysql-and-r/
- http://www.r-bloggers.com/accessing-mysql-through-r/
- http://gettinggeneticsdone.blogspot.com/2011/05/accessing-databases-from-r-rstats-sql.html
- useR Vignette from Jeffrey Breen
- Phil Spector
Installation
First, log in using root.
install.packages("DBI")
Go to shell and
apt-get install libdbd-mysql libmysqlclient16-dev (not sure if this is necessary) http://cran.r-project.org/src/contrib/RMySQL_0.9-3.tar.gz R CMD INSTALL RMySQL*.tar.gz
Open and close connection: dbConnect/dbDisconnect
con <- dbConnect(MySQL(), user="me", password="nuts2u", dbname="my_db", host="localhost") on.exit(dbDisconnect(con))
List tables and fields: dbListTables and dbListFields
dbListTables(mydb) dbListFields(mydb, 'some_table')
Read and write entire tables: dbReadTable and dbWriteTable
We can create tables in the database using R dataframes.
df = dbReadTable(con, 'motortrend') dbWriteTable(mydb, name='table_name', value=data.frame.name)
Query: dbGetQuery and dbSendQuery
You can process query results row by row, in blocks or all at once. The highly useful function dbGetQuery(con, sql) returns all query results as a data frame. With dbSendQuery, you can get all or partial results with fetch.
con <- dbConnect(MySQL(), user="network_portal", password="monkey2us", dbname=db.name, host="localhost") rs <- dbSendQuery(con, "select name from genes limit 10;") # results in in mysql data <- fetch(rs, n=10) # return result to R as a data frame; use n=-1 to retrieve all pending records. huh <- dbHasCompleted(rs) dbClearResult(rs) dbDisconnect(con)
Aggregate and Sort
df = dbGetQuery(con, "SELECT mfg, avg(hp) AS meanHP FROM motortrend GROUP BY mfg ORDER BY meanHP DESC") df = dbGetQuery(con, "SELECT cyl as cylinders, avg(hp) as meanHP FROM motortrend GROUP by cyl ORDER BY cyl")
dbApply()
sql = "SELECT cyl, hp FROM motortrend ORDER BY cyl" rs = dbSendQuery(con, sql) dbApply(rs, INDEX='cyl', FUN=function(x, grp) quantile(x$hp))
Retrieving AUTO_INCREMENT IDs
create.network <- function(species.id, network.name, data.source, description) { con <- dbConnect(MySQL(), user="super_schmuck", password="nuts2u", dbname="my_db", host="localhost") on.exit(dbDisconnect(con)) sql <- sprintf("insert into networks (species_id, name, data_source, description, created_at) values (%d, '%s', '%s', '%s', NOW());", species.id, network.name, data.source, description) rs <- dbSendQuery(con, sql) dbClearResult(rs) id <- dbGetQuery(con, "select last_insert_id();")[1,1] return(id) }
Full example from Jeffrey Breen
http://www.r-bloggers.com/slides-%E2%80%9Caccessing-databases-from-r%E2%80%9D-rstats/
First, create new database & user in MySQL:
mysql> create database testrdb; mysql> grant all privileges on testrdb.* to 'testuser'@'localhost' identified by 'testpass'; mysql> flush privileges;
In R, load the “mtcars” data.frame, clean it up, and write it to a new “motortrend” table:
library(stringr) library(RMySQL) data(mtcars) # car name is data.frame's rownames. Let's split into manufacturer and model columns: mtcars$mfg = str_split_fixed(rownames(mtcars), ' ', 2)[,1] mtcars$mfg[mtcars$mfg=='Merc'] = 'Mercedes' mtcars$model = str_split_fixed(rownames(mtcars), ' ', 2)[,2] # connect to local MySQL database (host='localhost' by default) con = dbConnect("MySQL", "testrdb", username="testuser", password="testpass") dbWriteTable(con, 'motortrend', mtcars) dbDisconnect(con)
And check the database from mysql
mli@PhenomIIx6:~$ mysql -u testuser -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 153 Server version: 5.1.63-0ubuntu0.11.10.1 (Ubuntu) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | testrdb | +--------------------+ 2 rows in set (0.00 sec) mysql> use testrdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-------------------+ | Tables_in_testrdb | +-------------------+ | motortrend | +-------------------+ 1 row in set (0.00 sec) mysql> describe motortrend; +-----------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------+------+-----+---------+-------+ | row_names | text | YES | | NULL | | | mpg | double | YES | | NULL | | | cyl | double | YES | | NULL | | | disp | double | YES | | NULL | | | hp | double | YES | | NULL | | | drat | double | YES | | NULL | | | wt | double | YES | | NULL | | | qsec | double | YES | | NULL | | | vs | double | YES | | NULL | | | am | double | YES | | NULL | | | gear | double | YES | | NULL | | | carb | double | YES | | NULL | | | mfg | text | YES | | NULL | | | model | text | YES | | NULL | | +-----------+--------+------+-----+---------+-------+ 14 rows in set (0.01 sec) mysql> SELECT * FROM motortrend WHERE row_names like "Mazda%" order by mpg; +---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+ | row_names | mpg | cyl | disp | hp | drat | wt | qsec | vs | am | gear | carb | mfg | model | +---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+ | Mazda RX4 | 21 | 6 | 160 | 110 | 3.9 | 2.62 | 16.46 | 0 | 1 | 4 | 4 | Mazda | RX4 | | Mazda RX4 Wag | 21 | 6 | 160 | 110 | 3.9 | 2.875 | 17.02 | 0 | 1 | 4 | 4 | Mazda | RX4 Wag | +---------------+------+------+------+------+------+-------+-------+------+------+------+------+-------+---------+ 2 rows in set (0.00 sec) mysql> SELECT row_names, wt, model FROM motortrend WHERE row_names like "Mazda%" order by wt; +---------------+-------+---------+ | row_names | wt | model | +---------------+-------+---------+ | Mazda RX4 | 2.62 | RX4 | | Mazda RX4 Wag | 2.875 | RX4 Wag | +---------------+-------+---------+ 2 rows in set (0.00 sec) mysql>
RODBC
SQLite
Find database online
- http://www.inside-r.org/howto/finding-data-internet
- http://www.r-bloggers.com/list-of-public-databases-from-the-washington-post/
Create a user and grant permission in mysql
http://www.cyberciti.biz/faq/mysql-user-creation/
mysql -u root -p CREATE DATABASE demo; GRANT ALL ON demo.* TO guest@localhost IDENTIFIED BY 'guest123';
Create a database 'demo' and a table 'employee'
http://www.thegeekstuff.com/2011/10/mysql-tutorial-basics/
mysql -u guest -p demo show databases; use demo; show tables; create table employee .............; desc employee; insert into employee .............; select * from employee;
Access mysql using perl
http://www.cyberciti.biz/faq/how-to-access-mysql-database-using-perl