MySQL: Difference between revisions

From 太極
Jump to navigation Jump to search
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

http://localhost/phpmyadmin/

Use through R

Examples from r-bloggers

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


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