MySQL: Difference between revisions

Revision as of 10:22, 29 August 2017

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;

Pattern match

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
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

# 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;

Use through R

Examples from r-bloggers

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 (not sure if this is 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

http://www.w3schools.com/sql/sql_autoincrement.asp

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>