MySQL
Installation issues
Fail to start
On Ubuntu 12.04, we can use
sudo dpkg-reconfigure mysql-server-5.5
InnoDB vs MyISAM
The default is InnoDB.
Under Shell
Manual/Documentation
https://dev.mysql.com/doc/refman
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
Find out about your MySQL version and all installed plugins:
mysql> select version(); mysql> show plugins;
Executing SQL Statements from a Text File
https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html
$ mysql db_name $ mysql -h host -u user -p db_name # db_name is not a password ! $ mysql -h host -u user -pPASSWORD db_name # no space after "-p" $ mysql db_name < text_file # text_file that contains the statements you wish to execute/Batch mode $ mysql db_name < text_file > output.txt mysql> source file_name
Create a table
https://dev.mysql.com/doc/refman/5.7/en/creating-tables.html
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
OR put it in a text file
mysql> CREATE TABLE pet { name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE }
Verify your table
mysql> DESCRIBE pet;
Loading data into a table
https://dev.mysql.com/doc/refman/5.7/en/loading-tables.html
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
OR using the mysqlimport utility from the shell
$ mysqlimport --local pet /path/pet.txt
You could add a new record using an INSERT statement like this:
mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Retrieve data
https://dev.mysql.com/doc/refman/5.7/en/retrieving-data.html General form
SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;
Some examples
mysql> SELECT * FROM pet; mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser'; mysql> SELECT * FROM pet WHERE name = 'Bowser'; mysql> SELECT * FROM pet WHERE birth >= '1998-1-1'; mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f'; mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') -> OR (species = 'dog' AND sex = 'f'); mysql> SELECT name, birth FROM pet; mysql> SELECT DISTINCT owner FROM pet; mysql> SELECT name, birth FROM pet ORDER BY birth; mysql> SELECT name, birth FROM pet ORDER BY birth DESC; # To sort in reverse (descending) order mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
mysql> SELECT * FROM pet WHERE name LIKE 'b%'; # find names beginning with b, case-insensitive mysql> SELECT * FROM pet WHERE name LIKE '%fy'; # find names ending with fy: mysql> SELECT * FROM pet WHERE name LIKE '%w%'; # find names containing a w:
Count rows
mysql> SELECT COUNT(*) FROM pet; mysql> SELECT COUNT(DISTINCT owner) FROM pet; mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
Retrieve information from multiple tables https://dev.mysql.com/doc/refman/5.7/en/multiple-tables.html
# suppose we have two tables event & score; each has a column called event_id # score table has columns: name, event_id, score # event table has columns: event_id, date, type mysql> SELECT student_id, date, score, type -> FROM event, score -> WHERE date = "1999-09-20" -> AND event.event_id = score.event_id;
Mysql list tables and sizes - order by size
https://stackoverflow.com/questions/14569940/mysql-list-tables-and-sizes-order-by-size
SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "schema_name" ORDER BY (data_length + index_length) DESC;
Note: replace "schema_name" with your database name.
Find first and last record from mysql table
https://stackoverflow.com/questions/2735395/how-to-find-first-and-last-record-from-mysql-table
# To get the first record: select col1 from tab1 order by col1 asc limit 1; # To get the last record: select col1 from tab1 order by col1 desc limit 1;
Write/save a table/query to a text file
- http://zetcode.com/databases/mysqltutorial/exportimport/
- https://stackoverflow.com/questions/21253704/how-to-save-mysql-query-output-to-excel-or-txt-file
- https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold
# Method 1. mysql. Get a permission denied error $ mysql -u root -p > SELECT * FROM tbl_name INTO OUTFILE '/tmp/orders.txt'; # Method 2. shell. Simple solution; does not need to change FILE privileges. # https://stackoverflow.com/questions/9462416/shell-one-line-query mysql -u root -pmy_password -h ip_add --port=13306 -D DATABASENAME -e "SELECT * FROM tbl_name;" > output.txt
Default database location
On debian, it is /var/lib/mysql.
How to Backup / Export a Single Table from a MySQL Database
https://www.howtogeekpro.com/157/how-to-backup-export-a-single-table-from-a-mysql-database/
phpMyAdmin
http://localhost/phpmyadmin with default username: root
See http://tuxtweaks.com/2011/10/install-lamp-and-phpmyadmin-on-ubuntu-11-10/2/
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
How to See Which MySQL Tables are Taking the Most Space
https://www.howtogeekpro.com/166/how-to-see-which-mysql-tables-are-taking-the-most-space/
How to Exclude Tables from MySQL Exports Using mysqldump
https://www.howtogeekpro.com/160/how-to-exclude-tables-from-mysql-exports-using-mysqldump/
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
- http://cran.r-project.org/web/packages/RMySQL/
- https://db.rstudio.com/my-sql/ (more instructions)
Ubuntu/Debian
First, log in using root.
install.packages("DBI")
Go to shell and
# sudo apt-get install libdbd-mysql (this line seems not necessary) sudo apt-get install libmysqlclient-dev # R # install.packages("RMySQL")
Windows
Check my note with complete screenshots at File:Install MySQL on Windows.pdf.
Open and close connection: dbConnect/dbDisconnect
con <- dbConnect(MySQL(), user="me", password="nuts2u", dbname="my_db", host="localhost") on.exit(dbDisconnect(con)) dbListTables(con) head(dbReadTable(con, "recentchanges"))
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>
dbplyr
RODBC
SQLite
PostgreSQL
8 PostgreSQL Examples to Install, Create DB & Table, Insert & Select Records