详解MySQL及RMySQL

Liang / 2018-11-24

01. Mac上安装MySQL #

访问MySQL的官网http://www.mysql.com/downloads/ 然后在页面中会看到“MySQL Community Server”下方有一个“download”点击。进入MySQL的下载界面（http://www.mysql.com/downloads/mysql/），如果你是用的Mac OS来访问的话那么就会默认为你选好了Mac OS X 平台，而下面罗列的都是在Mac OS上能用的MySQL的版本，如果是用的其他平台，在“Select Platform”选项的下拉列表中选一下就好了。按照安装步骤一路向下走，记得保存最后弹出框中的密码（它是你mysql root账号的密码）。打开设置并且点击MySQL并开启MySQL服务。

此时我们在终端输入此时我们在命令行输入mysql -uroot -p命令会提示没有commod not found，我们还需要将mysql加入系统环境变量。

(1).进入/usr/local/mysql/bin,查看此目录下是否有mysql。

(2).执行vim ~/.bash_profile

PATH=$PATH:/usr/local/mysql/bin

添加完成后，按esc，然后输入wq保存。最后在命令行输入source ~/.bash_profile。现在你就可以通过mysql -uroot -p登录mysql了，会让你输入密码，就是之前弹窗中记录的密码

至此MySQL成功安装到mac电脑下，可以进行下面的开发了。

02. 安装RMySQL #

安装RMySQL非常简单，安装之后可以使用help命令查询包的介绍信息

install.packages("RMySQL")
library(RMySQL)

help("RMySQL")

03. RMySQL基本操作 #

创造一个新的数据库并使用，SQL查询可以通过dbSendQuery或dbGetQuery传给数据库管理系统。dbGetQuery传送查询语句，把结果以数据框形式返回。dbSendQuery传送查询，返回的结果是继承"DBIResult"的一个子类的对象。函数fetch用于获得查询结果的部分或全部行，并以列表返回。函数dbHasCompleted确定是否所有行已经获得了，而dbGetRowCount返回结果中行的数目。如果只是简单的读整个表，也可以用dbReadTable函数。

library(RMySQL)

mydb = dbConnect(MySQL(), #数据库平台类型
                 user='root', #登录账号（MySQL初始安装时设置的账号）
                 password='password', #登录密码（MySQL初始安装时设置的密码）
                 host='localhost')
# creating a database using RMySQL in R
dbSendQuery(mydb, "CREATE DATABASE bookstore;")
dbSendQuery(mydb, "USE bookstore;")
# reconnecting to database we just created using following command in R :
mydb = dbConnect(MySQL(), user='root', password='password', host='localhost', dbname="bookstore") # 重连数据库
dbSendQuery(mydb, "drop table if exists books, authors")

在数据库中创造表：

# creating tables in bookstore:
dbSendQuery(mydb, "
CREATE TABLE books (
book_id INT,
title VARCHAR(50),
author VARCHAR(50));")

显示数据库中的表：

# Show table using R:
dbListTables(mydb)

在包中插入新的列：

dbSendQuery(mydb, "ALTER TABLE books
CHANGE COLUMN book_id book_id INT AUTO_INCREMENT PRIMARY KEY,
CHANGE COLUMN author author_id INT,
ADD COLUMN description TEXT,
ADD COLUMN genre ENUM('novel','poetry','drama', 'tutorials', 'text', 'other'),
ADD COLUMN publisher_id INT,
ADD COLUMN pub_year VARCHAR(4),
ADD COLUMN isbn VARCHAR(20);")

创造新的表并创造一些列名：

# Now, Before moving on to adding data to our books table, let's quickly set up the authors table.

dbSendQuery(mydb, "CREATE TABLE authors
(author_id INT AUTO_INCREMENT PRIMARY KEY,
author_last VARCHAR(50),
author_first VARCHAR(50),
country VARCHAR(50));")

往新的表中插入数据：

# Adding data into tables
dbSendQuery(mydb, "INSERT INTO authors
(author_last, author_first, country)
VALUES('Kumar','Manoj','India');")

从数据库中获取选取结果：

# fetching last data insert id number:
last_id = fetch(dbSendQuery(mydb, "SELECT LAST_INSERT_ID();"))
last_id

插入新的值到book表，并查看插入的值：

# Inserting data into books table and using last insert ID number:

dbSendQuery(mydb, "INSERT INTO books
(title, author_id, isbn, genre, pub_year)
VALUES('R and MySQL', 1,'6900690075','tutorials','2014');")

try1 = fetch(dbSendQuery(mydb, "SELECT book_id, title, description FROM books WHERE genre = 'tutorials';"))

显示所有的表并插入数值到新的表：

dbListTables(mydb) # 显示所有的表
dbWriteTable(mydb, name = "mtcars", value = mtcars[,]) # 插入R自带的数据mtcars形成新的表
dbListTables(mydb)

插入表格的时候如果有错误的话如：

Error in .local(conn, statement, ...) : could not run statement: The used command is not allowed with this MySQL version

则是因为MySQL的一些安全问题，可以在MySQL的命令界面通过命令查询：

SHOW VARIABLES LIKE 'local_infile';

全局更改则可以依据以下命令：

SET GLOBAL local_infile = 1;

读取数据库里面的表格：

mtcars <- dbReadTable(mydb, "mtcars")
head(mtcars)

删除表格：

dbGetQuery(mydb,"DROP TABLE mtcars")
dbListTables(mydb)

删除数据库：

dbGetQuery(mydb,"DROP DATABASE bookstore;")

断开连接：

dbDisconnect(mydb)

查询配置文件 #

可以使用下面的命令进行查询配置文件

mysql --verbose --help | grep my.cnf

/etc/my.cnf - Global
/etc/mysql/my.cnf - Global
SYSCONFDIR/my.cnf - Global
$MYSQL_HOME/my.cnf - Server-specific (server only)
~/.my.cnf - User-specific
~/.mylogin.cnf - User-specific (clients only)

04. 相关 #

查询mysql数据存放的位置

mysql> show global variables like "%datadir%";

Mac系统下默认是没有MySQL的配置文件my.cnf的，如果需求的话需要自己创建并修改。

最后一次修改于 2018-11-24