一些关于mysql在terminal上的指令:
1 .
/mysql -u root -p //进入mysql 数据库
2.
//修改root密码
$ mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
3. 在MySQL中建库建表
~ mysql -uroot -p
mysql> create database rmysql;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on rmysql.* to rmysql@'%' identified by 'rmysql';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on rmysql.* to rmysql@localhost identified by 'rmysql';
Query OK, 0 rows affected (0.00 sec)
mysql> use rmysql
Database changed
mysql> CREATE TABLE t_user(
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> user varchar(12) NOT NULL UNIQUE
-> )ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO t_user(user) values('A1'),('AB'),('fens.me');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t_user;
4. 通过R程序,读MySQL数据库数据
//Database Operation
library(RMySQL)
// 建立本地连接
conn <- dbConnect(MySQL(), dbname = "rmysql", username="rmysql", password="rmysql")
// 建立远程连接
conn <- dbConnect(MySQL(), dbname = "rmysql", username="rmysql", password="rmysql",host="192.168.1.201",port=3306)
// 查看数据库的表
dbListTables(conn)
// 查看表的字段
dbListFields(conn, "t_user")
// 查询MySQL信息
summary(MySQL(), verbose = TRUE)
// MySQL连接实例信息
summary(conn, verbose = TRUE)
// MySQL连接信息
dbListConnections(MySQL())
//##########################################
// RMySQL数据库操作 #
//##########################################
// 数据框
t_demo <- dataFrame
// 覆盖原表数据
dbWriteTable(conn, "test", t_demo, overwrite=TRUE)
// 建立新表格
dbWriteTable(conn, "test", t_demo)
// 插入新数据
dbWriteTable(conn, "world_box_office", t_demo, append=TRUE)
# 查看表格
dbReadTable(conn,"test")
// 覆盖原表数据
> dbWriteTable(conn, "t_demo", t_demo, overwrite=TRUE)
// 数据库询问
d0 = dbGetQuery(conn, "SELECT * FROM test")
//执行SQL脚本查询,并分页
rs <- dbSendQuery(conn, "SELECT * FROM test")
d1 <- fetch(rs)
rs <- dbSendQuery(conn, "SELECT * FROM test where Domestic>500")
d1 <- fetch(rs)
rs <- dbSendQuery(conn, "SELECT * FROM test ORDER BY World DESC")
d1 <- fetch(rs)
qu <- dbSendQuery(conn, "show tables")
d2 <- fetch(qu)
// 删除表
if(dbExistsTable(conn,'world_box_office')){
dbRemoveTable(conn, "world_box_office")
}
// 不插入row.names字段
dbWriteTable(conn, "test", d1,row.names=FALSE,overwrite=TRUE)
// 取消连接,结束数据库
dbDisconnect(conn)
5. RMySQL案例实践
系统需求描述:Linux MySQL,Win7的R环境,远程连接
1. 通过SQL新建表t_blog,主键索引,唯一键索引
2. 用RMySQL插入数据,包括中文字段
3. 再用RMySQL取出数据
1). 通过SQL新建表t_blog,主键索引,唯一键索引
建表语句
CREATE TABLE t_blog(
id INT PRIMARY KEY AUTO_INCREMENT,
title varchar(12) NOT NULL UNIQUE,
author varchar(12) NOT NULL,
length int NOT NULL,
create_date timestamp NOT NULL DEFAULT now()
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
mysql> desc t_blog;
+-------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(12) | NO | UNI | NULL | |
| author | varchar(12) | NO | | NULL | |
| length | int(11) | NO | | NULL | |
| create_date | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------------+-------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)
mysql> show indexes from t_blog;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_blog | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| t_blog | 0 | title | 1 | title | A | 3 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
INSERT INTO t_blog(title,author,length) values('你好,第一篇','Conan',20),('RMySQL数据库编程','Conan',99),('R的极客理想系列文章','Conan',15);
mysql> select * from t_blog;
+----+------------------------------+--------+--------+---------------------+
| id | title | author | length | create_date |
+----+------------------------------+--------+--------+---------------------+
| 1 | 你好,第一篇 | Conan | 20 | 2013-08-15 00:13:13 |
| 2 | RMySQL数据库编程 | Conan | 99 | 2013-08-15 00:13:13 |
| 3 | R的极客理想系列文章 | Conan | 15 | 2013-08-15 00:13:13 |
+----+------------------------------+--------+--------+---------------------+
3 rows in set (0.00 sec)
2). 用RMySQL插入数据,包括中文字段,再取出数据
> library(RMySQL)
> conn <- dbConnect(MySQL(), dbname = "rmysql", username="rmysql", password="rmysql",host="192.168.1.201",port=3306)
>
> dbSendQuery(conn,'SET NAMES gbk')
> dbSendQuery(conn,"INSERT INTO t_blog(title,author,length) values('R插入的新文章','Conan',50)");
>
> query<-dbSendQuery(conn, "SELECT * FROM t_blog")
Warning message:
In mysqlExecStatement(conn, statement, ...) :
RS-DBI driver warning: (unrecognized MySQL field type 7 in column 4 imported as character)
> data <- fetch(query, n = -1)
> mysqlCloseResult(query)
[1] TRUE
> print(data)
id title author length create_date
1 1 你好,第一篇 Conan 20 2013-08-15 00:13:13
2 2 RMySQL数据库编程 Conan 99 2013-08-15 00:13:13
3 3 R的极客理想系列文章 Conan 15 2013-08-15 00:13:13
4 4 R插入的新文章 Conan 50 2013-08-15 00:29:45
>
> dbDisconnect(conn)
[1] TRUE
特别提示,不能用dbWriteTable函数!!
我们已经完成,掌握了RMySQL的各种使用技巧,希望大家理解原理后,能少犯错误,提高工作效率!
参考: R语言如何连接MySQL的攻略,摘录自粉丝日志
http://blog.fens.me/r-mysql-rmysql/
分享到:
相关推荐
Mac OS X 10.15 下源码安装 mysql-8.0.20-macos10.15-x86_64 https://my.oschina.net/kinglyphp/blog/4281112 博客地址 下载mysql-8.0.20-macos10.15-x86_64.tar.gz 可以从mysql官网下载,如果比较慢, 从...
SetOverwrite on functionend ;注册服务 Function "installService" detailprint "------------------------------------install tomcat Service..." Sleep 2000 SetOutPath "$INSTDIR\tomcat\bin" nsExec::...
matlab转换java代码utl_passing_sas_macro_variables_to_R_and_retrieving_macro_variables_from_R 将宏变量传递给R并从R中检索宏变量。关键字:sas sql连接合并大数据分析宏oracle teradata mysql sas社区...
Dash 是Mac下非常方便的文档查看器,一体化集成了许多常用的文档,但是不内购它的完整版就要在搜索完后等待8秒钟,非常影响开发效率。这个分享是Dash的破解,可以自行在App Store下载最新版本的Dash,然后将Dash.app...
Mobile systems based on Linux or Mac OS/X (e.g. Android, iPhone, Zaurus, iPaq) HOW TO USE ---------- Type "./configure", followed by "make" to compile hydra and then "./hydra -h" to see the command...
arp 查看和处理ARP缓存,ARP是名字解析的意思,负责把一个IP解析成一个物理性的MAC地址。arp -a将显示出全部信息 start 程序名或命令 /max 或/min 新开一个新窗口并最大化(最小化)运行某程序或命令 mem 查看cpu...
chmod a-r CHANGELOG.txt Note that the example only affects CHANGELOG.txt. To completely hide all documentation files from public view, repeat this command for each of the Drupal documentation ...
iOS_Mac\OReilly Mac OS X for Unix Geeks Leopard 4th.mobi iOS_Mac\Pragmatic iOS Recipes.mobi iOS_Mac\Pragmatic Mac Kung Fu.mobi iOS_Mac\Wrox Beginning iOS Game Development.mobi iOS_Mac\Wrox Building ...
Mac 12 Windows 12 Text editor 12 Using an IDE 13 FTP client 14 MySQL client 14 Coding best practices 15 Basic organization 15 Isolate tasks into functions 16 Use classes 16 Use descriptive ...
Investing Online For Dummies 5th Ed.pdf Ipaq For Dummies.pdf Iphone For Dummies.pdf Ipod & Itunes For Dummies 3rd Ed.pdf Ipod & Itunes 2nd Ed For Dummies.pdf ISBN-13 Special Ed For Dummies.pdf IBM ...