那一刻之后 汶川大地震八周年祭

八年了,

这世界有了多少改变?

多少生命定格在那一刻?

 

那一刻之后,

我们关注,

我们哭泣,

我们倾自己的能力去挽救。

 

那一刻之后,

大地揭开了多少善与恶?

 

大地的伤痕,

把最纯粹的展示给你。

 

那之后不久,

大地再次愈合,

而人心却难以愈合。

 

人们希望,

这世界上多一些善良与纯粹。

《R编程与进化分析》幻灯片(2016年5月9日 北京)

受中国科学院植物研究所赖江山博士邀请, 本人于2016年5月9日在北京昌平讲解《R编程与进化分析》。

幻灯片如下

PCM_00.pdf
PCM_01.pdf
PCM_02.pdf
PCM_03.pdf

主要内容

  1. R开发平台的搭建
  2. R脚本
  3. R的编程习惯
  4. 编写R函数
  5. 程序包的结构
  6. S3与S4 Methods
  7. 程序包的安装和检查
  8. 结合Rcpp编写程序包
  9. 从github安装程序包
  10. 使用git记录程序更改, 并更新github

正则表达式与基本文本处理

  1. gsub
  2. grepl
  3. %in%
  4. regexpr
  5. gregexpr

极大似然以及贝叶斯的基本原理

  1. 用极大似然法估计线性模型的参数
  2. 用MCMCMC估计现行模型的参数
  3. Bootstrap的原理以及应用
  4. Permutation的基本原理

进化树基础

  1. fasta文件
  2. Phylip文件
  3. Newick文件
  4. Nexus文件

DNA Barcoding 序列构建进化树

  1. fasta文件
  2. 用MUSCLE进行Alignment
  3. 用phylotools生成supermatrix (relaxed phylip文件)
  4. 用RAxML构建极大似然进化树
  5. 用r8s进行分子钟校订

基于植物名录建立进化树

  1. 植物名录
  2. 用plantlist程序包生成科属种列表 (基于APGIII)
  3. 用科属种列表提交到Phylomatic, 基于Zanne2014进化树生成种水平的进化树

进化树在R中的操作

CRAN Task Views: Phylogenetics

  1. Figtree显示进化树

  2. ggtree: 进化树绘图

  3. ape程序包简介
     (1)phylo类型简介以及提取
     (2)绘制进化树
     (3)祖先状态重建
     (4)ltt plot

  4. Picante 群落系统发育, 计算 Faith’ PD, 计算系统发育信号, 计算community phylogenetic structure, PIC, Pybus Gamma

  5. laser: 基于极大似然法对物种分化速率和灭绝速率进行估计 speciation and extinction rate.

  6. geiger: 计算MEDUSA

需要安装的软件, 以及用到的软件在 百度盘 (448M), 可以点击以下链接下载

http://pan.baidu.com/s/1geCc9iz

该压缩文件内容如下

  1. 7z1514-x64.exe*
  2. basic-miktex-2.9.5872-x64.exe*
  3. Git-2.7.2-32-bit_setup.1457942412.exe*
  4. npp_6.9.1_Installer.1459233531.exe*
  5. out.txt
  6. package example/
  7. phylogenetic/
  8. R-3.2.5-win.exe*
  9. Rtools33.exe*
  10. XnViewMP-win-x64.exe*
  11. 安装指南.txt

个人简历的Latex模板

用Latex制作的简历风格清爽 。

这里给出本人中文简历的源代码。 请将 代码拷贝到纯文本文件中,并用UTF8 编码。

在Linux Mint下, 完整安装TexLive, 以及TexWorks, 通过TexWorks界面的pdflatex编译为pdf。

也可以在Windows 下的 Miktex所提供的TexWorks下编译为pdf。

编译时需要在文件夹中包含 resume.cls 文件。 请点击下载。

编译成的pdf效果如图。

Jinlong_Zhang_Chinese20160418.pdf

编译时需要的cls文件

resume.cls

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
**%%%** Latex代码开始
%____________
% LaTeX2e  for Jinlong Zhang
\documentclass[a4paper, 12pt, margin, line]{resume}
%pagestyle{plain}
\usepackage{CJKutf8}
\usepackage[usenames]{color}
\usepackage{hyperref}
\hypersetup{colorlinks,
       linkcolor=black,
       filecolor=black,
       urlcolor=blue,
       citecolor=black} 
\pagenumbering{roman}
\name{Large 个人简历}
%______________________________________________________________________________________________________________________
\begin{document}
\begin{CJK}{UTF8}{gbsn}
\begin{resume}
\renewcommand{today}{numberyear 年 numbermonth 月 numberday 日}
%__________________________________________________________________________________________________________________
% Contact Information
\section{mysidestyle {\bf 联系信息}}
   \textbf{Large 张金龙} 博士\
   \textbf{性别:} 男                                                                                                                                                           \
   \textbf{籍贯:} 天津宝坻                                                                                    \
   \textbf{地址:} 香港新界大埔林锦公路 嘉道理农场暨植物园 植物保育部                   \
   \textbf{办公室电话}: 00852-00000000 \
   \textbf{手机}: 00852-00000000 \
   \textbf{电子信箱}: href{mailto:jinlongzhang01@gmail.com}{jinlongzhang01@gmail.com}                                                                                                               \textbf{个人主页}: {tt href{http://phylodiversity.net/jinlongzhang/}{http://phylodiversity.net/jinlongzhang/}}
   \textbf{github主页}: {tt href{https://github.com/helixcn/}{https://github.com/helixcn/}}
   \textbf{科学网博客}: {tt href{http://blog.sciencenet.cn/u/zjlcas}{http://blog.sciencenet.cn/u/zjlcas}}
% {color{blue}{http://phylodiversity.net/jinlongzhang/}}
%___________
% Research Interests
\section{mysidestyle {\bf 研究领域}}
1. 群落生态学               \
2. 系统发育比较分析与适应性进化 \
3. 物种地理分布格局与宏生态学\
4. R,Python, C++++ 统计编程.
%______
% Employment
\section{mysidestyle {\bf 工作单位}} 
\textbf{高级生态学主任、标本馆馆长}\
嘉道理农场暨植物园植物保育部 hfill 2015年4月 - 现在 
\textbf{植物鉴定和记录主任}\
嘉道理农场暨植物园植物保育部 hfill 2013年1月 -2015年3月
%________
% Education
\section{\mysidestyle {\bf 教育背景}} 
   \textbf{理学博士:生态学} hfill 中国科学院植物研究所 2012年                                  \%
   论文题目:\hspace{ 3mm }
   \textsl{植物系统发育多样性格局及其环境解释: 从群落到区域}                                       \
   导师: 马克平 研究员                                                                          %
   \textbf{理学硕士:植物学} hfill 东北农业大学 2008年                                         \%
   论文题目:\hspace{ 3mm } 
   \textsl{东北早春类短命植物区系及分布格局研究}                                                  \
  导师: 李晶 教授 及 马克平 研究员
   textbf{理学学士:生物学} hfill 东北农业大学 2005年                               
%__________________________________________________________________________________________________________________
\section{\mysidestyle {\bf 专业技能}}
{\bf 植物鉴定: }
中国东部常见种子植物除个别科以外可直接鉴定到属, 华南以及东北常见植物可以鉴定到种。
 2005到2016年之间考察的区域包括: 东北、华南、 西南、华中、华东、香港特别行政区, 瑞典西博腾等 。大部分植物照片已经鉴定并且上传到中国自然标本馆
({tt href{http://www.cfh.ac.cn/Album/UserAlbums.aspx?Username=helixcn}{http://www.cfh.ac.cn/Album/UserAlbums.aspx?Username=helixcn} }). 
{\bf 分子生物学实验技能:}
DNA提取、 PCR、琼脂糖凝胶电泳、毛细管电泳 (CE)、SSR 以及亲本分析 
{\bf 计算机语言:}
R(熟练)、 C、C++++、FORTRAN、 Python、SQL、LaTeX
{\bf 本人编写的R程序包:}
phylotools、spaa、HK80、plantlist、skycalc、herblabel
{\bf 掌握的软件:}
PAUP*, PHYLIP, MrBayes, PHYML, RAxML, r8s, BEAST, MEGA, Phylocom, Phylomatic, Mesquite, ape, geiger, ArcGIS, DIVA-GIS, Maxent
section{mysidestyle {\bf 植物考察}}
\textsl{\bf{2016年4月}}       \hfill 广东封开县黑石顶考察50公顷森林样地            
\textsl{\bf{2013年到2016年}}  \hfill 香港植物调查与摄影                    
\textsl{\bf{2007年12月}}       \hfill 浙江省开化县古田山森林样地植物回访研究         
\textsl{\bf{2004, 2005 and 2006年暑假}} hfill  textbf{辽宁省鞍山市千山} 
东北农业大学植物学野外实习老师
%__________________________________________________________________________________________________________________
%  Academic Presentations
\section{mysidestyle {\bf 学术审稿:}}
\textsl{生物多样性},
\textsl{Journal of Ecology}, 
\textsl{PLoS One}, 
\textsl{Journal of Systematics and Evolution},
\textsl{Journal of Plant Ecology}, 
\textsl{Plant Ecology and Diversity}, 
\textsl{Animal Systematics Evolution and Diversity}
%__________________________________________________________________________________________________________________
% Publications
section{mysidestyle {\bf 近五年发表论文 }}
{\bf 2015 年}
Qian, Hong and Field, Richard and {\bf Zhang, Jin-Long} and Zhang, Jian and Chen, Shengbin. (2015) Phylogenetic structure and ecological and evolutionary determinants of species richness for angiosperm trees in forest communities in China.  textsl {Journal of Biogeography}, 43(3):603–615.
{\bf 2014 年}
Chen, S., Mao, L., {\bf Zhang, J.}, Zhou, K. and Gao, J. (2014). Environmental determinants of geographic butterfly richness pattern in eastern China. textsl {Biodiversity and conservation}, 23(6), 1453-1467.
{\bf 张金龙},马克平.2014.种间联接和生态位重叠的计算:spaa程序包. 见马克平主编.中国生物多样性保护与研究进展X.北京:气象出版社,165-174
section{mysidestyle {\bf 参编书籍}}
李晶,苍晶,{\bf 张金龙} (2014) 黑龙江常见野生植物图鉴. 北京. 高等教育出版社
%__________________________________________________________________________________________________________________
% Referees
\\section{mysidestyle {\bf 证明人}}
\begin{tabular}{@{}p{7.5cm}p{7.5cm}} 
\textbf{某某 博士}                                            & \textbf{某某 研究员}                            \
香港嘉道理农场暨植物园                                                     & 中国科学院植物研究所                                \
植物保育部部门主管                                                        & 植被与气候变化国家重点实验室                          \
Email: \href{mailto: xxx@kfbg.org}{\textsl{xxx@kfbg.org} }   & Email:\href{mailto:aaa@ibcas.ac.cn}{\textsl{aaa@ibcas.ac.cn}} \
\end{tabular}
{\hfill 更新日期: \today}
%______________________________________________________________________________________________________________________
\end{resume}
\end{CJK}         % 结束中文环境
\end{document}

%%% Latex代码 结束

在R中绘制进化树:ggtree学习笔记

ggtree (https://guangchuangyu.github.io/software/ggtree/)是香港大学余光创博士编写的R程序包。ggtree是ggplot2程序包的扩展 (http://www.ggplot2-exts.org/),能够像ggplot2一样,用图层化的语法绘制进化树。这与APE用参数控制绘图明显不同。与ggplot2一样,在ggtree中,通过不同的图层组合即可绘制出更为复杂的图形。

该程序包在正式发布之前就受到CRAN Task Views:Phylogenetics 的作者Brian O’Meara 的关注。ggtree发布在Bioconductor之后,更是受到国际同行的广泛赞誉。作者介绍ggtree的论文在英国生态学会的Methods in Ecology and Evolution发表之后, 在不到一年的时间里, 引用次数已经超过100,可见受欢迎程度。

在数据分析中,读取进化树一般是用ape程序包的read.tree(), 但是构建进化树时,不同软件生成的文件格式多种多样,虽然大部分都是以newick或者nexus格式为基础,但是有时要将进化树建立过程中的信息标注在进化树上,这时read.tree读取的信息往往不足,编写函数导入相关信息也比较麻烦。鉴于此,作者为ggtree编写了读取进化树的函数,以弥补ape程序包read.tree的不足。ggtree能够读取并转换BEAST, r8s,RAxML等软件所生成的数据。用户也可自定义数据,轻松实现R实现基于进化树的高级绘图。

余博士关于ggtree的介绍:

http://cos.name/2015/11/to-achieve-the-visualization-and-annotation-of-evolutionary-tree-using-ggtree/

本笔记主要基于:
http://www.bioconductor.org/packages/release/bioc/vignettes/ggtree/inst/doc/ggtree.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
#### ggtree可以直接读取的数据格式

Newick (via ape)
Nexus (via ape)
New Hampshire eXtended format (NHX) (http://home.cc.umanitoba.ca/~psgendb/doc/atv/NHX.pdf)
Jplace (http://journals.plos.org/plosone/article?id=10.1371/journal.pone.0031009)
Phylip

以及以下软件输出的数据:

BEAST2
EPA3
HYPHY5
PAML1
PHYLDOG6
pplacer4
r8s7
RAxML8
RevBayes9

####################################################
用以下函数读取
read.beast()       ## for parsing output of BEASE
read.codeml()      ## for parsing output of CODEML (rst and mlc files)
read.codeml_mlc()  ## for parsing mlc file (output of CODEML)
read.hyphy()       ## for parsing output of HYPHY
read.jplace()      ## for parsing jplace file including output from EPA and pplacer
read.nhx()         ## for parsing NHX file including output from PHYLODOG and RevBayes
read.paml_rst()    ## for parsing rst file (output of BASEML and CODEML)
read.r8s()         ## for parsing output of r8s
read.raxml()       ## for parsing output of RAxML

#####################################################
###### 定义的 S4 Classes #####################################
apeBootstrap ##for bootstrap analysis of ape::boot.phylo()10, output of apeBoot() defined in ggtree
beast        ##for storing output of read.beast()
codeml       ## for storing output of read.codeml()
codeml_mlc   ##for storing output of read.codeml_mlc()
hyphy        ## for storing output of read.hyphy()
jplace       ## for storing output of read.jplace()
nhx          ## for storing output of read.nhx()
paml_rst     ## for rst file obtained by PAML, including BASEML and CODEML.
phangorn     ## for storing ancestral sequences inferred by R package phangorn11, output of phyPML defined in ggtree
r8s          ## for storing output of read.r8s()
raxml        ## for storing output of read.raxml()

### 其他支持的S4 类
###

phylo, 
multiPhylo (defined by ape10), 
phylo4 (defined by phylobase) 
obkData (defined in OutbreakTools) and 
phyloseq (defined in phyloseq).

### 获取任何能够在进化树上显示的参数
get.fields

### 显示进化树
ggtree(tree_object)

### 将任何格式的进化树转换为 phylo格式
get.tree

### 界定分类单元
groupOTU()
groupClade()

### 将进化树转换为 data.frame
fortify

############################################
#########3 举例: BEAST

library(ggtree)
file <- system.file("extdata/BEAST""beast_mcc.tree", package="ggtree")
beast <- read.beast(file)
beast

get.fields(beast)
ggtree(beast, ndigits = 2, branch.length = 'none') + geom_text(aes(x = branch, label =length_0.95_HPD), vjust = -.5, color = 'firebrick')

######### 导入的数据通过 fortify函数能够转换为 data.frame
beast_data <- fortify(beast)
head(beast_data)


################ PAML
brstfile <- system.file("extdata/PAML_Baseml""rst", package="ggtree")
brst <- read.paml_rst(brstfile)
brst

p <- ggtree(brst) +
   geom_text(aes(x = branch, label = marginal_AA_subs), vjust=-.5, color='steelblue') +
   theme_tree2()
print(p)

################# CODEML
crstfile <- system.file("extdata/PAML_Codeml""rst", package="ggtree")
crst <- read.paml_rst(crstfile)
crst

##### %<% 类似于格式刷, 将某一进化树绘制时的参数, 转移到要绘制的进化树中
p %<% crst

################ 
#### CODEML mlc文件
mlcfile <- system.file("extdata/PAML_Codeml""mlc", package="ggtree")
mlc <- read.codeml_mlc(mlcfile)
mlc

ggtree(mlc) + geom_text(aes(x=branch, label=dN_vs_dS), color='blue', vjust=-.2)

#### 获取有用的信息
get.fields(mlc)

#### 指定需要调整的枝长

ggtree(mlc, branch.length = "dN_vs_dS", aes(color=dN_vs_dS)) +
   scale_color_continuous(name='dN/dS', limits=c(01.5),
                          oob=scales::squish, low="darkgreen", high="red")+
   theme_tree2(legend.position=c(.9.5))

#### CODEML 的 rst以及 mlc文件

ml <- read.codeml(crstfile, mlcfile)
ml
#### 
head(fortify(ml))

#### 读取 HYPHY文件并绘制进化树

nwk <- system.file("extdata/HYPHY""labelledtree.tree", package="ggtree")
ancseq <- system.file("extdata/HYPHY""ancseq.nex", package="ggtree")
tipfas <- system.file("extdata""pa.fas", package="ggtree")
hy <- read.hyphy(nwk, ancseq, tipfas)
hy

ggtree(hy) + geom_text(aes(x=branch, label=AA_subs), vjust=-.5)


##### 读取r8s生成的文件并绘制进化树
r8s <- read.r8s(system.file("extdata/r8s""H3_r8s_output.log", package="ggtree"))
ggtree(r8s, branch.length="TREE", mrsd="2014-01-01") + theme_tree2()

ggtree(get.tree(r8s), aes(color=.id)) + facet_wrap(~.id, scales="free_x")

##### 读取RAxML生成的bootstrap文件并绘制进化树

raxml_file <- system.file("extdata/RAxML""RAxML_bipartitionsBranchLabels.H3",package="ggtree")
raxml <- read.raxml(raxml_file)
ggtree(raxml) + geom_label(aes(label=bootstrap, fill=bootstrap)) + geom_tiplab() +
   scale_fill_continuous(low='darkgreen', high='red') + theme_tree2(legend.position='right')

##### 读取NHX New Hampshire eXtended文件并绘制进化树
nhxfile <- system.file("extdata""ADH.nhx", package="ggtree")
nhx <- read.nhx(nhxfile)
ggtree(nhx) + geom_tiplab() + geom_point(aes(color=S), size=5, alpha=.5) + 
   theme(legend.position="right") +
   geom_text(aes(label=branch.length, x=branch), vjust=-.5) + 
   xlim(NA0.3)

##### 读取PHYLIP文件并绘制进化树以及序列比对图
phyfile <- system.file("extdata""sample.phy", package="ggtree")
phylip <- read.phylip(phyfile)
phylip

ggtree(phylip) + geom_tiplab()

msaplot(phylip, offset=1)

##### jplace格式数据同时保存自定义数据

jpf <- system.file("extdata/sample.jplace",  package="ggtree")
jp <- read.jplace(jpf)
print(jp)
## get only best hit
get.placements(jp, by="best")

## get all placement
get.placements(jp, by="all")

########################################################################
############ 进化树合并 ### 分类单元匹配 ##################################
############ 作者讲的不太清楚 
beast_file <- system.file("examples/MCC_FluA_H3.tree", package="ggtree")
beast_tree <- read.beast(beast_file)

rst_file <- system.file("examples/rst", package="ggtree")
mlc_file <- system.file("examples/mlc", package="ggtree")
codeml_tree <- read.codeml(rst_file, mlc_file)

merged_tree <- merge_tree(beast_tree, codeml_tree)

merged_tree

#################################################################
#### 读取 jplace 文件, 并将保存在csv文件种的性状信息

tree <- system.file("extdata""pa.nwk", package="ggtree")
data <- read.csv(system.file("extdata""pa_subs.csv", package="ggtree"),stringsAsFactor=FALSE)
print(tree)

outfile <- tempfile()
write.jplace(tree, data, outfile)
jp <- read.jplace(outfile)

ggtree(jp) + 
   geom_text(aes(x=branch, label=subs), color="purple", vjust=-1, size=3) + 
   geom_text(aes(label=gc), color="steelblue", hjust=-.6, size=3) +
   geom_tiplab()

#############################################################################
########## 绘制进化树
library("ggtree")
nwk <- system.file("extdata""sample.nwk", package="ggtree")
tree <- read.tree(nwk)

ggplot(tree, aes(x, y)) + geom_tree() + theme_tree()
ggtree(tree, color="firebrick", size=1, linetype="dotted")

### ggtree会自动将进化树转换为 ladderized, 如果要取消 ladderized, 则应该改为 ladderize
ggtree(tree, ladderize=FALSE)

### 不要枝长, 只查
ggtree(tree, branch.length="none")

########## 进化树显示的方式 ############

矩形 rectangular (by default)
偏斜 slanted
扇形/圆形 fan or circular

ggtree(tree) + ggtitle("(Phylogram) rectangular layout")
ggtree(tree, layout="slanted") + ggtitle("(Phylogram) slanted layout")
ggtree(tree, layout="circular") + ggtitle("(Phylogram) circular layout")

###### 只显示拓扑结构
### 矩形
ggtree(tree, branch.length='none') + ggtitle("(Cladogram) rectangular layout")

### 偏斜
ggtree(tree, layout="slanted", branch.length='none') + ggtitle("(Cladogram) slanted layout")

### 圆形
ggtree(tree, layout="circular", branch.length="none") + ggtitle("(Cladogram) circular layout")

##### 无根树
ggtree(tree, layout="unrooted") + ggtitle("unrooted layout")

#######################################################################################
########### Ultrametric Tree 绘图会自动添加标尺 ###################
ggtree(tree) + geom_treescale()

#### 更改标尺的长短颜色, 粗细 
ggtree(tree)+geom_treescale(x=0, y=12, width=6, color='red')

#################### 
ggtree(tree)+geom_treescale(fontsize=8, linesize=2, offset=-1)

###### 添加时间轴 依靠 theme_tree2()
ggtree(tree) + theme_tree2()

#########################################################################################
###### 节点以及分类单元的标识, 根据是否为 tiplabel以及node分别显示
ggtree(tree)+geom_point(aes(shape=isTip, color=isTip), size=3)

#### Node Point参数 以及 tippoint分别显示
p <- ggtree(tree) + geom_nodepoint(color="#b5e521", alpha=1/4, size=10)
p + geom_tippoint(color="#FDAC4F", shape=8, size=3)

#### Tiplabel的显示
p + geom_tiplab(size=3, color="purple")

#### 圆形进化树的标签
ggtree(tree, layout="circular") + geom_tiplab(aes(angle=angle), color='blue')

#### 进化树tip label显示到branch上
p + geom_tiplab(aes(x=branch), size=3, color="purple", vjust=-0.3)

#### %<% 若之前已经有ggtree object完成了进化树的绘制, 那么可以用 %>% 将前一个进化树的属性转移到当前进化树中。 %<% 的作用相当于word中的格式刷。 
p %<% rtree(50)

#################################################################
######### ggtree中有两个主题 theme, theme_tree2() 和 theme_tree()
### theme_tree2 与 后者的区别在于 增加了时间坐标轴。 
### 无坐标轴
ggtree(rtree(30), color="red") + theme_tree()

### 有坐标轴
ggtree(rtree(30), color="red") + theme_tree2()

### 改变背景颜色
ggtree(rtree(30), color="red") + theme_tree("steelblue")

### 
ggtree(rtree(20), color="white") + theme_tree("#b5e521")

#################################################################
############# 显示多个进化树 ######################################
trees <- lapply(c(102040), rtree)
class(trees) <- "multiPhylo"
ggtree(trees) + facet_wrap(~.id, scale="free") + geom_tiplab()

#################################################################
btrees <- read.tree(system.file("extdata/RAxML""RAxML_bootstrap.H3", package="ggtree"))
ggtree(btrees) + facet_wrap(~.id, ncol=10)


#################################################################
########### 显示bootstrap进化树作为背景 #################################
p <- ggtree(btrees, layout="rectangular",   color="lightblue", alpha=.3)

best_tree <- read.tree(system.file("extdata/RAxML""RAxML_bipartitionsBranchLabels.H3",package="ggtree"))
df <- fortify(best_tree, branch.length = 'none')
p + geom_tree(data=df, color='firebrick')

################################################################################
############# 进化树的操作 ##########################################

#### 对进化树操作之前, 必须要定位进化树内部的节点, 每一个节点都有一个顺序号
#### ggtree提供了显示内部节点号的函数

nwk <- system.file("extdata""sample.nwk", package="ggtree")
tree <- read.tree(nwk)
ggtree(tree) + geom_text2(aes(subset=!isTip, label=node), hjust=-.3) + geom_tiplab()

#### 用MRCA显示节点编号
MRCA(tree, tip=c('A''E'))

########### 显示某一个节点下的整个类群 clade
p <- ggtree(tree)
viewClade(p + geom_tiplab(), node=21)

########### 基于内部节点号, 划定clade, 按照clade划分颜色
tree <- groupClade(tree, node=21)
ggtree(tree, aes(color=group, linetype=group))

##########  不同group分不同颜色显示, 显示tiplabel时, 不同的组别用不同的颜色表示
tree <- groupClade(tree, node=c(2117))
ggtree(tree, aes(color=group, linetype=group)) + geom_tiplab(aes(subset=(group==2)))

########### 手工指定类群的分组,并且表示为不同的颜色 
cls <- list(c1=c("A""B""C""D""E"),
           c2=c("F""G""H"),
           c3=c("L""K""I""J"),
           c4="M")

#### s输入对象未 ggtree时
tree <- groupOTU(tree, cls)
library("colorspace")
ggtree(tree, aes(color=group, linetype=group)) + geom_tiplab() +
    scale_color_manual(values=c("black", rainbow_hcl(4))) + theme(legend.position="right")

#### 输入对象为
p <- ggtree(tree)
groupOTU(p, LETTERS[1:5]) + aes(color=group) + geom_tiplab() +scale_color_manual(values=c("black""firebrick"))


##### groupOTU划分组
library("ape")
data(chiroptera)
groupInfo <- split(chiroptera$tip.label, gsub("_\w+""", chiroptera$tip.label))
chiroptera <- groupOTU(chiroptera, groupInfo)
ggtree(chiroptera, aes(color=group), layout='circular') + geom_tiplab(size=1,aes(angle=angle))


##### 类群折叠与展开
cp <- ggtree(tree) %>% collapse(node=21)

### 折叠节点号为21的类群
cp + geom_point2(aes(subset=(node == 21)), size=5, shape=23, fill="steelblue")

### 展开
cp %>% expand(node=21)

### 折叠以及展开多个节点
p1 <- ggtree(tree)
p2 <- collapse(p1, 21) + geom_point2(aes(subset=(node==21)), size=5, shape=23, fill="blue")
p3 <- collapse(p2, 17) + geom_point2(aes(subset=(node==17)), size=5, shape=23, fill="red")
p4 <- expand(p3, 17)
p5 <- expand(p4, 21)

multiplot(p1, p2, p3, p4, p5, ncol=5)


#### 类群的缩放
multiplot(ggtree(tree) + geom_hilight(21"steelblue"),
         ggtree(tree) %>% scaleClade(21, scale=0.3) + geom_hilight(21"steelblue"),
         ncol=2)

#### 指定节点编号, 高亮某一个类群
ggtree(tree) + geom_hilight(21"steelblue")

#### 类群clade旋转180度 (此例子不能运行)
tree <- groupClade(tree, c(2117))
### p <- ggtree(tree, aes(color=group)) + scale_color_manual(values=c("black", "firebrick", "steelblue"))
#### p2 <- rotate(p, 21) %>% rotate(p, 17)
multiplot(p, p2, ncol=2)

########################################################################
##### 两个分类群位置互换
multiplot(p, flip(p, 1721), ncol=2)

############################################################################
############################################################################
##### 进化树
beast_file <- system.file("examples/MCC_FluA_H3.tree", package="ggtree")
beast_tree <- read.beast(beast_file)
beast_tree


##### 绘制进化树
p1 <- ggtree(beast_tree, mrsd='2013-01-01') + theme_tree2() +
   ggtitle("Divergence time")
p2 <- ggtree(beast_tree, branch.length = 'rate') + theme_tree2() +
   ggtitle("Substitution rate")
multiplot(p1, p2, ncol=2)

###### 局部放大某一个分类群 clade
library("ape")
data(chiroptera)
library("ggtree")
gzoom(chiroptera, grep("Plecotus", chiroptera$tip.label))

###### 局部放大某分类群
groupInfo <- split(chiroptera$tip.label, gsub("_\w+""", chiroptera$tip.label))
chiroptera <- groupOTU(chiroptera, groupInfo)
p <- ggtree(chiroptera, aes(color=group)) + geom_tiplab() + xlim(NA23)
gzoom(p, grep("Plecotus", chiroptera$tip.label), xmax_adjust=2)

###### 各分支按照条件设色
ggtree(beast_tree, aes(color = rate)) +
   scale_color_continuous(low='darkgreen', high='red') +
   theme(legend.position="right")

##### 标注某一个clade, 在右侧画竖线
set.seed(2015-12-21)
tree = rtree(30)
p <- ggtree(tree) + xlim(NA6)

p + geom_cladelabel(node=45, label="test label") + 
   geom_cladelabel(node=34, label="another clade"

##### 右侧标注用的竖线可以对齐
p + geom_cladelabel(node=45, label="test label", align=TRUE, offset=.5) +
   geom_cladelabel(node=34, label="another clade", align=TRUE, offset=.5)

##### 改变标注竖线的颜色
p+geom_cladelabel(node=45, label="test label", align=T, color='red') +
   geom_cladelabel(node=34, label="another clade", align=T, color='blue')

##### 旋转标注类群的文字
p+geom_cladelabel(node=45, label="test label", align=T, angle=270, hjust='center',offset.text=.5) + geom_cladelabel(node=34, label="another clade", align=T, angle=45)     

##### 改变标注竖线的粗细, 以及文字的大小
p+geom_cladelabel(node=45, label="test label", align=T, angle=270, hjust='center',offset.text=.5, barsize=1.5) +
   geom_cladelabel(node=34, label="another clade", align=T, angle=45, fontsize=8

##### 为文字添加边框, 并设置文本框的背景颜色
p+ geom_cladelabel(node=34, label="another clade", align=T, geom='label', fill='lightblue'

########################################################################
################# 对类群进行高亮显示 ######################################
nwk <- system.file("extdata""sample.nwk", package="ggtree")
tree <- read.tree(nwk)
ggtree(tree) + geom_hilight(node=21, fill="steelblue", alpha=.6) +
   geom_hilight(node=17, fill="darkgreen", alpha=.6)

#### 圆形树的类群高亮
ggtree(tree, layout="circular") + geom_hilight(node=21, fill="steelblue", alpha=.6) +
   geom_hilight(node=23, fill="darkgreen", alpha=.6)

#########################################################################
#### 标注 bootstrap数值 
#### 用ape生成一个NJ树, 并且进行bootstrap, 将bootstrap的结果在NJ树的节点上显示。 

library(ape)
data(woodmouse)
d <- dist.dna(woodmouse)
tr <- nj(d)
bp <- boot.phylo(tr, woodmouse, function(xx) nj(dist.dna(xx)))

##### 
tree <- apeBoot(tr, bp)  ### merge phylo and output of boot.phylo to 'apeBootstrap' object
ggtree(tree) + geom_label(aes(label=bootstrap)) + geom_tiplab()


##### 对phangorn生成进化树的标注
library(phangorn)
treefile <- system.file("extdata""pa.nwk", package="ggtree")
tre <- read.tree(treefile)
tipseqfile <- system.file("extdata""pa.fas", package="ggtree")
tipseq <- read.phyDat(tipseqfile,format="fasta")
fit <- pml(tre, tipseq, k=4)
#### fit是设定条件生成的最优进化树
fit <- optim.pml(fit, optNni=FALSE, optBf=T, optQ=T,
                optInv=T, optGamma=T, optEdge=TRUE,
                optRooted=FALSE, model = "GTR")

#### 将pml格式的数据转换为 ggtree能够识别的类型
phangorn <- phyPML(fit, type="ml")
ggtree(phangorn) + geom_text(aes(x=branch, label=AA_subs, vjust=-.5))

#### ggtree可以读取如下软件生成的进化树, 并且同时保存这些软件输出结果关于进化速率等相应的信息
BEAST
EPA
HYPHY
PAML
PHYLDOG
pplacer
r8s
RAxML
RevBayes

########################################################################
######## 将性状信息与进化树匹配绘图 ###############

nwk <- system.file("extdata""sample.nwk", package="ggtree")
tree <- read.tree(nwk)
p <- ggtree(tree)

##### 生成随机数据
dd <- data.frame(taxa  = LETTERS[1:13], 
                place = c(rep("GZ"5), rep("HK"3), rep("CZ"4), NA),
                value = round(abs(rnorm(13, mean=70, sd=10)), digits=1))
## you don't need to order the data
## data was reshuffled just for demonstration
dd <- dd[sample(1:1313), ]
row.names(dd) <- NULL

##### 数据框匹配字符
##### %<+% 运算符的要求
##### 左侧为进化树
##### 右侧为 data.frame, 要求是该进化树的第一列种的物种名必须能与进化树的tip.label匹配上。 

p <- p %<+% dd + geom_tiplab(aes(color=place)) + 
      geom_tippoint(aes(size = value, shape = place, color = place), alpha = 0.25)
      
p + theme(legend.position="right")

##### 将采集地点以及性状值写在枝长上
p + geom_text(aes(color=place, label=place), hjust=1, vjust=-0.4, size=3) +
 geom_text(aes(color=place, label=value), hjust=1, vjust=1.4, size=3)

########################################################################
########################################################################
########################################################################
####################### 在进化树右侧绘制热图 ###############################

beast_file <- system.file("examples/MCC_FluA_H3.tree", package="ggtree")
beast_tree <- read.beast(beast_file)

genotype_file <- system.file("examples/Genotype.txt", package="ggtree")
genotype <- read.table(genotype_file, sep="t", stringsAsFactor=F)
head(genotype)

p <- ggtree(beast_tree, mrsd="2013-01-01") + geom_treescale(x=2008, y=1)
p <- p + geom_tiplab(size=3)
gheatmap(p, genotype, offset = 2, width=0.5)

########################################################################
#### scale_x_ggtree() 保证右侧矩阵的文字说明能正常显示。 
p <- ggtree(beast_tree, mrsd="2013-01-01") + geom_tiplab(size=3, align=TRUE) + theme_tree2()
pp <- (p + scale_y_continuous(expand=c(00.3))) %>%
   gheatmap(genotype, offset=4, width=0.5, colnames=FALSE) %>%
       scale_x_ggtree()
pp + theme(legend.position="right")

######################## msaplot ###### 
##### 进化树 以及 fasta比对矩阵 
fasta <- system.file("examples/FluA_H3_AA.fas", package="ggtree")
msaplot(ggtree(beast_tree), fasta) 

######################## 扇形的 msaplot #####
msaplot(ggtree(beast_tree), fasta, window=c(150200)) + coord_polar(theta='y')

######################## 在进化树的总图上高亮显示某一个clade: geom_hilight
######################## 并且在右侧的大图中放大该clade viewClade subview
set.seed(2016-01-04)
tr <- rtree(30)
tr <- groupClade(tr, node=45)
p <- ggtree(tr, aes(color=group)) + geom_tippoint()
p1 <- p + geom_hilight(node=45)
p2 <- viewClade(p, node=45) + geom_tiplab()
subview(p2, p1+theme_transparent(), x=2.3, y=28.5)


#############################################################################
########## 用inset为内部节点添加直方图, 以显示每个节点在性状上所占比例  ##############
#############################################################################

set.seed(2015-12-31)
tr <- rtree(15)
p <- ggtree(tr)

a <- runif(1400.33)
b <- runif(1400.33)
c <- runif(1400.33)
d <- 1 - a - b - c
dat <- data.frame(a=a, b=b, c=c, d=d)
## input data should have a column of `node` that store the node number 
### 数据必须有一列名为node, 并且值为节点数
dat$node <- 15+1:14

## cols parameter indicate which columns store stats (a, b, c and d in this example)
## cols表示dat的1:4列储存了用于绘图的数据
bars <- nodebar(dat, cols=1:4)

inset(p, bars)

## 柱状图的大小和颜色
inset(p, bars, width=.03, height=.06)

### 柱状图的排列方式, 改为并排排列
bars2 <- nodebar(dat, cols=1:4, position='dodge',
                color=c(a='blue', b='red'c='green', d='cyan'))
p2 <- inset(p, bars2, x='branch', width=.03, vjust=-.3)
print(p2)


##############################################################
### 节点上的饼图 

pies <- nodepie(dat, cols=1:4, alpha=.6)
inset(p, pies) 

inset(p, pies, hjust=-.06### 饼图向右平移

##############################################################
#### 同时用 pie和bar标注内部节点

pies_and_bars <- bars2
pies_and_bars[9:14] <- pies[9:14]
inset(p, pies_and_bars)

##############################################################
##### 用 inset函数用tip添加对应的箱线图 boxplot, 以展示数据分布

d <- lapply(1:15, rnorm, n=100)
ylim <- range(unlist(d))
bx <- lapply(d, function(y) {
   dd <- data.frame(y=y)
   ggplot(dd, aes(x=1, y=y)) + geom_boxplot() + ylim(ylim) + theme_inset()
})
names(bx) <- 1:15
inset(p, bx, width=.03, height=.1, hjust=-.05)


#########################################################################
### 混合标注, 未能成功
### p2 <- inset(p, bars2, x='branch', width=.03, vjust=-.4)
### p2 <- inset(p2, pies, x='branch', vjust=.4)
### bx2 <- lapply(bx, function(g) g+coord_flip())
### inset(p2, bx2, width=.2, height=.03, vjust=.04, hjust=p2datadatax[1:15]-4) + xlim(NA, 4.5)


########################################################################
###### ggplot统计与进化树绘制 ################ 
tr <- rtree(30)
df <- fortify(tr)
df$tipstats <- NA
d1 <- df
d2 <- df
d2$tipstats[d2$isTip] <- abs(rnorm(30))
d1$panel <- 'Tree'
d2$panel <- 'Stats'
d1$panel <- factor(d1$panel, levels=c("Tree""Stats"))
d2$panel <- factor(d2$panel, levels=c("Tree""Stats"))

p <- ggplot(mapping=aes(x=x, y=y)) + facet_grid(.~panel, scale="free_x") + theme_tree2()
p+geom_tree(data=d1) + geom_point(data=d2, aes(x=tipstats)) 

########################################################################
##### 从 phylopic数据库上下载剪影图像, 并标在进化树上

pp <- ggtree(tree) %>% phylopic("79ad5f09-cf21-4c89-8e7d-0c82a00ce728", color="steelblue",alpha = .3)
print(pp)

参考:

Yu, G., Smith, D. K., Zhu, H., Guan, Y., & Lam, T. T. Y. (2017). ggtree: an R package for visualization and annotation of phylogenetic trees with their covariates and other associated data. Methods in Ecology and Evolution, 8(1), 28-36

克里格空间插值Kriging及其在R的实现

克里格是一种空间插值的方法. 克里格插值, 是法国地统计学家 Georges Matheron (1930-2000)基于南非工程师Danie G. Krige (1919-2013)的硕士论文发展起来的方法. D. Krige硕士论文研究的问题是如何根据金矿探测点的含金量推断含金量在空间上的变化.

所谓空间插值, 就是通过一些列具有空间关系的点数据, 来推广到面上数据的方法.

除了克里格方法之外, 从点数据推断面的数据变化, 还有线性插值, 距离反平方插值等多种方法. 而克里格其实也是一类方法的通称, 它包括普通克里格, 通用克里格, 通用模块克里格等等. 但是克里格方法可以求得模型的无偏估计,并且方便得获得预测的误差, 这对于其他方法来讲常常难易实现.

这里只简单介绍用gstat程序包实现普通克里格, 以及通过一套模拟数据获得高程图.除了gstat程序包外, geoR程序包以及fields程序包也提供了克里格插值方法.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#### 首先生成一套模拟数据simdat, 通过Volcano数据随机提取1000个点, 用raster的extract函数提取

library(raster)
rrr <- raster(t(volcano), xmn=0, xmx= 870 , ymn=0, ymx=610)
x11()
plot(rrr, col = terrain.colors(100))

set.seed(12345)
loc <- data.frame(x = round(runif(1000min = 0max = 870), 2), y = round(runif(1000,min = 0max = 610), 2))
value <- extract(rrr, loc)
simdat <- data.frame(loc, alt = value) ### The simulated data


#### Krig准备工作开始, 导入程序包
library(sp)
library(gstat)

#### simdat需要转换为sp格式, 需要指定坐标
coordinates(simdat) <- ~x+y
### bubble(simdat, "alt")
spplot(simdat, "alt"### 查看每个点的值 

#### 创建克里格所用的网格, 20m方格的中心位置
xgrid <- seq(ceiling(min(simdat$x)), floor(max(simdat$x)), by = 20
ygrid <- seq(ceiling(min(simdat$y)), floor(max(simdat$y)), by = 20
basexy <- expand.grid(xgrid, ygrid)
#### plot(y ~ x, basexy)
colnames(basexy) <- c("x""y")
coordinates(basexy) <- ~x+y
gridded(basexy) <- TRUE

### 因Kriging需要基于 Variogram以及相应的模型.Variogram横轴是距离, 纵轴是方差. 做模型拟合时, 应先查看 Variogram. Variogram根据点与点之间的距离, 分成若干段, 并计算每一段的方差, 再通过拟合Variogram模型, 获得Krig所需要的参数. 拟合Variogram模型时, 需要提供模型的初始值,再进行迭代优化, 否则很难获得模型的准确参数. 

######### 若所进行的是普通克里格插值, 则需要插值的变量只与自身取样点的距离相关,用公式表示为 alt~1, 这里的~1是variogram的规定 (参见 ?variogram). 
vgm1 <- variogram(alt~1, simdat)
plot(vgm1, plot.numbers = TRUE

### variogram的模型常包括
### Circular
### Spherical
### Exponential
### Gaussian
### Linear
### gstat程序包提供的模型可以通过 vgm() 查看

m <- fit.variogram(vgm1,vgm(.01,"Sph",100,300))
plot(vgm1, model=m)

在拟合gm模型时, 需要提供以下几个参数的初始值

  • psill: partial sill
  • model: 模型的类型
  • range: 范围
  • nugget: 块金值

图1 Range, Sill, Partial Sill, and Nugget of the Variogram

各参数的几何意义如图一所示. 初始值要通过在variogram的图中读取, 尽量接近真实值, 这样后续的迭代才会成功.

例如以下例子中:

  • .01 为 psill
  • “Sph” 为球状模型
  • 300 为范围
  • nugget为 块金值.
1
2
3
4
5
6
7
8
### 设定vgm模型的初始值
m <- vgm(.01,"Sph",300, 0.2)

### 进行克里格
krige_res <- krige(alt~1, simdat, basexy, model = m)

### 查看克里格插值的结果
spplot(krige_res, zcol = "var1.pred", main = "Predictions of altitude based on the randomly sampled data", col.regions = terrain.colors(100))

参考:

http://planet.botany.uwc.ac.za/nisl/GIS/spatial/images/pic024.jpg
http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=How%20Krige%20and%20Variogram%20work

http://geostat-course.org/**system**/files/lewis_tutortPM.pdf
苏姝,林爱文,刘庆华 (2004) 普通 Kriging 法在空间内插中的运用 江南大学学报 (自然科学版) 3(1):18-21

R编程的一些规则与建议

使用R软件进行统计分析, 必须要接触R脚本. R脚本是保存了R代码的文本文件, 一般以.R为扩展名.

要高效得进行代码的编写和调试, 需要一个好的编辑器或者好的编程环境. 当然, 每个人都可以使用最简单的文本编辑, 如Windows Notepad 或者Rgui所提供的文本编辑工具, 但是要避免使用MS word等文档编辑器编辑R脚本.

编辑器最好能符合以下要求:

  1. 等宽字体, 如Consolas, Courier New 等, Consolas配合微软雅黑字体是不错的选择.

  2. 代码高亮显示, 意思是对字符串, 数字, 以及语言的关键词, 以及注释部分 分别用不同颜色显示出来.

  3. 括号配对, 小括号, 中括号以及花括号成对匹配时同时高亮显示.

  4. 提供良好的 正则表达式匹配, 以及特殊字符匹配, 如针对 n, t, r等特殊字符的匹配, 以及替换.

  5. 列模式, 即同时对多个列进行输入, 或者编辑.

  6. 编码转换工具, 如UTF-8编码转ASCII, GBK18030, BIG5等等.

  7. 大小写转换工具

  8. 快速运行功能, 如一键运行代码.

  9. 显示行号

  10. 具有导出为html功能或者Latex, 或RTF等功能

  11. 字体大小能够灵活控制, 一般通过按住Contrl并转动鼠标滚轮, 即可调整显示字体的大小.

在众多的编辑器中, 符合这种要求的恐怕不是很多. 目前比较理想的, Windows下是Notepad++, Linux下则是Geany, MacOS下的编辑器则不太了解. 试用过gedit, Kate, 它们都提供了R的高亮, 但是似乎还是不够灵活. 甚至Rstudio这样专门为R设计的IDE, 还是有很多功能没有实现, 所以并不能让人十分满意.

说到编程习惯, 难免老生常谈. 例如, 什么要缩进, 要提供详细的注释等等. 这和其他编程语言并无区别. 然而R毕竟还是有自己的特点, 所以这里还是列出来一些经验:

在开始分析前, 一定要先简要查看数据的分布, 以确定所选择的统计方法. 在开始编写函数前, 应该对要数据的数据格式, 以及输出的数据有清楚的理解.

  1. 所有的R脚本, 以应R作为扩展名, 这样编辑器打开时能够默认是R语言的高亮, 从而节省选择语言高亮的时间.

  2. R脚本建议以 #### 开头, 然后写清楚 该脚本的标题, 作者, 日期, 联系方式等, 这样, 日后查看脚本这部分注释代码, 有很好的参考作用.

  3. 第一句写 setwd(), 指定工作目录. 任何一个任务, 都应该单独创建一个文件夹, 其中放数据, R脚本, 以及运行的结果等

  4. 随后是加载该脚本需要用到的程序包 如 library(vegan), 每一行只加载一个程序包, 直到所有用到的程序包全部加载.

  5. 脚本中定义的函数要在加载程序包之后立即定义.

  6. 如果数据是保存在xls文件中的, 每个xls文件只保存一个data_sheet. xls文件尽量保存为csv文件, 再读取, 不要保存为txt文件, 因为read.csv()比read.table()要稳定很多. 如果数据是保存在xlsx文件中的, 尽量用 openxlsx程序包所提供的函数read.xlsx()读取数据.

  7. 变量的名称要具体,并且只用ASCII码字母, 要避免A,B,C,D,X等类似的变量名. 变量命名时, 尽量不要大小写混搭, 不同部分用下划线_分隔. 理想状态是, 即使不看注释, 只看变量的名称, 也可以知道变量的意义.

  8. 算法的关键部分, 要注释, 特别容易出错的地方也要注释. 但是注释不能滥用. 数据处理的名称, 还应该通过变量名体现出来.

  9. 缩写的函数要尽量简单明确, 以方便维护.

  10. 写函数时要善用 warning, stop等处理异常的函数, 以便规范数据输入.

  11. 要尽量学会 git, 以记录所有关键的更改.

  12. 一个R脚本, 拷贝到R console运行时, 应该能够全部自动运行, 中间不能再要求手动输入参数.

  13. 保存R生成的图像, 应该尽量用 tiff函数和dev.off()配合. 不要再用手动保存的方法.

  14. 生成R程序包骨架时, 用 package.skeletons(), 然后分别编写Rd的函数帮助文件. 很多投机取巧的办法编写R程序包只能让问题更加复杂.

  15. 为了简化绘图编程, 应该尽快掌握ggplot2程序包.

  16. 为了克服计算瓶颈, 应该学会C或者FORTRAN语言, 也可以运用Rcpp结合C++, 并用R调用

  17. 及时注意R的更新, 参考如 https://github.com/qinwf/awesome-R 以及CRAN Task Views的动态.

张金龙

2016年2月1日

《标本馆与生物多样性研究》在浸会大学国际学院的讲座

2015年11月23日, 很荣幸应浸会大学Sisi老师的邀请,为她的学生们介绍植物标本馆在生物多样性中的作用。

幻灯片 The role of a herbarium in Biodiversity Studies.ppt

这里主要介绍了标本馆的工作流程, 标本的制作, 排列, 以及标本馆数据的应用。

img

浸会大学国际学院

http://www.cie.hkbu.edu.hk/main/index.php

(https://upload.wikimedia.org/wikipedia/commons/d/d0/HK_Baptist_University_Shek_Mun_Campus.jpg)

sqlite3 简明指南

sqlite3 是运行SQL语言的小型数据库软件,有着广泛的应用。本文是学习笔记,原文请参考 http://zetcode.com/db/sqlite/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585

--下载: https://www.sqlite.org/download.html

--开启数据库, 在terminal输入:

sqlite3 test.db

--开启帮助
.help

--读取 sql脚本
.read friends.sql

--查询有什么表格
.TABLE s

--显示一个表格的全部内容
SELECT * FROM Friends;

--设定各列之间的间隔
.separator

--不同的显示模式
.mode column
.headers on

--选出部分列
SELECT Name, Title FROM Authors NATURAL JOIN Books;

--显示不同列时的宽度
.width 1518

--显示打印的属性
.show

--显示表格的属性
.schema Cars

--更改 调用命令的前缀
.prompt "> "". "

--从 Terminal 运行 sqlite
sqlite3 test.db "SELECT * FROM Cars;"

--导出数据
--1. 指名文件
.output cars2.sql
--2. 导出sql脚本
.dump Cars

--.sqlite_history 文件
位置 /home/jinlong
tail -5~/.sqlite_history

--.sqliterc 文件, 内容为纯文本,可以保存sqlite的配置命令, 位置
/home/jinlong

每次sqlite3启动时,会自动加载其中的sqlite命令

--显示帮助
sqlite3 --help

--进入sqlite3时,设定相应的参数
sqlite3 -echo -line -noheader test.db

--显示版本
sqlite3 -version

--以html的格式显示
sqlite3 -html test.db

----#####################
---数据库的创建
CREATE
--任何一个field,有如下几种类型

--NULL — The value is a NULL value
--INTEGER — a signed integer
--REAL — a floating point value
--TEXT — a text string
--BLOB — a blob of data

---创建一个名为 Testing的表, 包含Id一列, 为整数类型
CREATE TABLE Testing(Id INTEGER);

-- 查看创建Testing的代码
.schema Testing

-- 若Tesing已经存在, 则再次创建会出错
CREATE TABLE Testing(Id INTEGER);

-- 为了减少这种错误, 可以用 IF NOT EXISTS 检查.
CREATE TABLE IF NOT EXISTS Testing(Id INTEGER);

-- 从其他数据生成表格
CREATE TABLE Cars2 AS SELECT * FROM Cars;

--查看现有数据库名
.databases

--有多个数据库时,需要在命令中指定要更改的数据库

ATTACH DATABASE 'test2.db' AS 'test2'; -- ### 可先在sqlite中更改数据库的名称
CREATE TABLE test2.Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
INSERT INTO test2.Cars VALUES(1,'Porsche',107699);
SELECT * FROM main.Cars WHERE Id=1;

-- 临时数据库, 其中的数据表不会显示. 临时数据库的名称为 temp

CREATE TEMPORARY TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
INSERT INTO temp.Cars VALUES(1,'Kia',24300);
.databases

--删除表格
DROP

--查看表格
.TABLE s
DROP TABLE Testing;
--如果 Testing表格不存在, 而用DROP则会出错.

--为了排除这种错误, 一般改为
DROP TABLE IF EXISTS Testing;

--删除某一个数据库中的数据表
DROP TABLE IF EXISTS test2.Cars;

--###########################################
-- ALTER
-- 对表格的更改

CREATE TABLE Names(Id INTEGER, NameTEXT);

--表格改名
ALTER TABLE Names RENAME TO NamesOfFriends;
.schema NamesOfFriends

--为表格中增加一列
ALTER TABLE NamesOfFriends ADD COLUMN Email TEXT;
.schema NamesOfFriends

---########################################
--- sqlite语法规则

SELECT 3,'Wolf',34.5;
.null value NULL

SELECT NULL;

---二进制大型物件 Binary Large Object (BLOB)
SELECT x'345eda2348587aeb';

---###### sqlite中的 运算符
--- Arithmetic operators
--- Boolean operators
--- Relational operators
--- Bitwise operators
--- Other operators

--- ||
--- * / %
--- + -
--- << >> & |
--- < <= > >=
--- = == != <> IS IN LIKE GLOB BETWEEN
--- AND
--- OR

--取反字符
SELECT -(3-44);
--- unary prefixoperators:
--- - + ~ NOT

--- 数值运算
SELECT 3 * 3/9;
SELECT 3 + 4 - 1 + 5;
SELECT 11 % 3;

---逻辑运算符
SELECT 1 AND 1, 0 AND 1, 1 AND 0, 0 AND 0 ;
SELECT 3=3 AND 4=4;
SELECT 0 OR 1,1 OR 0, 1 OR 1,0 OR 1;
SELECT NOT 1, NOT 0;
SELECT NOT (3=3);


--- 判断
--- Symbol Meaning
--- < strictly less than
--- <= less than or equal to
--- > greater than
--- >= greater than or equal to
--- = or == equal to
--- != or <> not equal to

SELECT 3 * 3==9, 9=9;
SELECT 3 <4, 3<>5, 4>=4, 5!=5;

---位运算, 针对二进制的运算

--- bitwise and operator &,

SELECT 6 & 3;
SELECT 3 & 6;

--- bitwise or operator|

--- bitwise sh IF t << >>

--- 取反
--- bitwise negation operator
SELECT ~7;

--- 其他运算符
--- ||字符串连接符
--- IN判断某个字符是否在一个向量中

--例如
SELECT 'Tom' IN ('Tom','Frank','Jane');
---例如
SELECT * FROM Cars WHERE Name IN('Audi','Hummer');

---LIKE, 配合 WHERE , 用正则表达式筛选
SELECT * FROM Cars WHERE Name LIKE'Vol%';

--- 特定字符数的字符筛选
SELECT * FROM Cars WHERE Name LIKE'____';

--- GLOB, 类似于 Like, 但是区分大小写, 同时识别UNIX字符
SELECT * FROM Cars WHERE Name GLOB ' * en';
SELECT * FROM Cars WHERE Name GLOB '????';---四个字符
AND
BETWEEN
SELECT * FROM Cars WHERE Price BETWEEN 20000 AND 55000;

--- 运算符的优先级
--- unary + - ~ NOT
--- ||
--- * / %
--- + -
--- << <> & |
--- < <= > >=
--- = == != <> IS IN LIKE GLOB BETWEEN
--- AND
--- OR

---同一级别运算符的顺序-
---从左至右
SELECT 9/3 * 3;
---- 从左至右的运算符包括
---- Arithmetic, boolean, relational, and bitwise operators are all left to right associated.


--- 数据的插入, 更新和删除
DROP TABLE IF EXISTS Cars;
CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER DEFAULT 'Not available');
INSERT INTO Cars(Id, Name, Price) VALUES (1,'Audi',52642);
INSERT INTO Cars(Name, Price) VALUES ('Mercedes',57127);
SELECT * FROM Cars;
--- ### 设定为 INTEGER PRIMARY KEY 之后, 插入数据时, 可以忽略, 数据库会为新纪录自动增加一个Id

---若未能在INSERT 时给出各列名称, 则需要按照长度提供所有值
INSERT INTO Cars VALUES (3, 'Skoda', 9000);

--- 显示 NULL as NULL
.nullvalue NULL

SELECT * FROM Cars WHERE Id=4;

--- 对于已经有primary key条目的插入
INSERT INTO Cars VALUES(4,'Volvo',29000);
INSERT OR REPLACE INTO Cars VALUES(4,'Volvo',29000);
SELECT * FROM Cars WHERE Id=4;

--- 如果插入数据不成功, 会提示fail
INSERT OR FAIL INTO Cars VALUES(4,'Bentley',350000);

--- 不会提示任何信息
INSERT OR IGNORE INTO Cars VALUES(4,'Bentley',350000);

--- sqlite 3.7.11 之后, 可以同时插入多行
CREATE TABLE Ints(Id INTEGER PRIMARY KEY, Val INTEGER);

INSERT INTO Ints(Val) VALUES(1),(3),(5),(6),(7),(8),(6),(4),(9);
CREATE TABLE Cars2(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
INSERT INTO Cars2 SELECT * FROM Cars;

---############################################
--- ####### DELETE 删除部分数据

DELETE FROM Cars2 WHERE Id=1;
SELECT * FROM Cars2; -- 删除Cars2中的全部内容
DELETE FROM Cars2;

SELECT Count(Id)AS'# of cars'FROM Cars2;
.read cars.sql
SELECT * FROM Cars;
DELETE FROM Cars LIMIT5;
SELECT * FROM Cars;

----############################################
---- ####### Update,按照条件对数据进行修改
.read cars.sql
UPDATE Cars SET Name='Skoda Octavia' WHERE Id=3;
SELECT * FROM Cars WHERE Id=3;

----###########################################
---- #### SELECT 的使用
---- ### 显示全部表格
SELECT * FROM Cars;

----### 选择特定的列
SELECT Name, Price FROM Cars;

---- ### 查询结果, 但是改查询结果的名
SELECT Name, Price AS 'Price of car'FROM Cars;

---- ### 显示部分数据
SELECT * FROM Cars LIMIT 4;

SELECT * FROM Cars LIMIT 2, 4; --- 选择显示四行,但是不要查看前两行

SELECT * FROM Cars LIMIT 4 OFFSET 2; --- Offset 忽略前两行,但是显示四行

----##############################################
----### 输出数据的排序

SELECT * FROM Cars ORDER BY Price;
SELECT Name, Price FROM Cars ORDER BY Price DESC;

----##############################################
---和 WHERE 搭配使用

SELECT * FROM Orders WHERE Id=6;
SELECT * FROM Orders WHERE Customer = "Smith";
SELECT * FROM Orders WHERE Customer LIKE 'B%';

#################################################
--- 检查重复记录
SELECT Customer FROM Orders WHERE Customer LIKE 'B%';

--- 去除重复
SELECT DISTINCT Customer FROM Orders WHERE Customer LIKE 'B%';

----#####################################################
----数据分组

SELECT sum(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer;

SELECT sum(OrderPrice) AS Total, Customer FROM Orders
GROUP BY Customer HAVING sum(OrderPrice)>1000;

----#######################################################
----### 限制条件

-- NOT NULL
-- UNIQUE
-- PRIMARY KEY
-- FOREIGN KEY
-- CHECK
-- DEFAULT

----### 创建表的时候, 声明不能有空值
CREATE TABLE People(Id INTEGER,LastName TEXT NOT NULL, FirstName TEXT NOT NULL, City TEXT);

INSERT INTO People VALUES(1,'Hanks', 'Robert', 'New York');
INSERT INTO People VALUES(2, NULL, 'Marianne', 'Chicago');

----##########################################################
CREATE TABLE Brands(Id INTEGER, BrandName TEXT UNIQUE);
----#### 不符合条件时, 要报错
INSERT INTO Brands VALUES(1,'Coca Cola');
INSERT INTO Brands VALUES(2,'Pepsi');
INSERT INTO Brands VALUES(3,'Pepsi');

---PRIMARY KEY 限制条件, PRIMARY KEY只能指定一个, 该列所有值为唯一的, 并且顺序增加. primary key是其他表格引用该表格的基础.
DROP TABLE Brands;
CREATE TABLE Brands(Id INTEGER PRIMARY KEY,BrandName TEXT);
INSERT INTO Brands(BrandName)VALUES('CocaCola');
INSERT INTO Brands(BrandName)VALUES('Pepsi');
INSERT INTO Brands(BrandName)VALUES('Sun');
INSERT INTO Brands(BrandName)VALUES('Oracle');
SELECT * FROM Brands;

----引用其他表中的id
FOREIGN KEY(AuthorId) REFERENCES

BEGIN TRANSACTION;
DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Authors;

CREATE TABLE Authors(AuthorId INTEGER PRIMARY KEY, Name TEXT);
INSERT INTO Authors VALUES(1,'Jane Austen');
INSERT INTO Authors VALUES(2,'Leo Tolstoy');
INSERT INTO Authors VALUES(3,'Joseph Heller');
INSERT INTO Authors VALUES(4,'Charles Dickens');

CREATE TABLE Books(BookId INTEGER PRIMARY KEY, TitleTEXT, AuthorId INTEGER, FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId));
INSERT INTO Books VALUES(1,'Emma',1);
INSERT INTO Books VALUES(2,'War and Peace',2);
INSERT INTO Books VALUES(3,'Catch XII',3);
INSERT INTO Books VALUES(4,'David Copperfield',4);
INSERT INTO Books VALUES(5,'Good as Gold',3);
INSERT INTO Books VALUES(6,'Anna Karenia',2);
COMMIT;

---将两个表格的id绑定(其中一个引用另外一个),则被引用表格中的数据不能直接删除
PRAGMA foreign_keys=1;
DELETE FROM Authors WHERE AuthorId=1;

CREATE TABLE Books(BookId INTEGER PRIMARY KEY, TitleTEXT, AuthorId INTEGER, FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId)ON DELETE CASCADE);

SELECT Name, Title FROM Authors NATURAL JOIN Books;

DELETE FROM Authors WHERE AuthorId=2;
SELECT Name, Title FROM Authors NATURAL JOIN Books;

.schema Orders --- 查看表格头以及各列的名称和数据类型

INSERT INTO Orders(OrderPrice, Customer) VALUES (-10,'Johnson');

---默认值关键字 DEFAULT
CREATE TABLE Hotels(Id INTEGER PRIMARY KEY, NameTEXT, City TEXT DEFAULT 'not available');

INSERT INTO Hotels(Name) VALUES ('Slovan');

----###############################################
----两个表格的合并

----三种类型的合INNER 并
-- (1) INNER JOIN,
-- (2) NATURAL INNER JOIN
-- (3) CROSS INNER JOIN

---(1)INNER JOIN,
SELECT Name, Day FROM Customers AS C JOIN Reservations AS R ON C.CustomerId = R.CustomerId;
--- 注意AS XX 是为数据表临时改名, 以方便引用

---以上句子可以用SELECT 实现相同的效果
SELECT Name, Day FROM Customers, Reservations WHERE Customers.CustomerId = Reservations.CustomerId;

---(2)NATURAL INNERJOIN
--- NATURALJOIN自动以两个表中相同的列名进行匹配
SELECT Name, Day FROM Customers NATURAL JOIN Reservations;

---(3)CROSS INNER JOIN 是将表1和表2所有项进行匹配,并无实际应用价值.

--- OUTER JOINS,也分为三种 (left outer joins,right outer joins,and full outer joins),但是SQLite只支持第一种 left outer joins
--- OUTER JOINS 查询左侧表格的所有内容,即便在右侧表格没有值,也将以NULL的形式给出

SELECT Name, Day FROM Customers LEFT JOIN Reservations ON Customers.CustomerId = Reservations.CustomerId;
---另一种方法选择
SELECT Name, Day FROM Customers LEFT JOIN Reservations USING(CustomerId);

----自动匹配另一个表格
NATURALLEFTOUTERJOIN
SELECT Name, Day FROM Customers NATURAL LEFT OUTER JOIN Reservations;

----#############################################################
----SQLite中的函数

----第一类 核心函数
SELECT sqlite_version()
SELECT random() AS Random;
SELECT abs(11),abs(-5),abs(0),abs(NULL);
SELECT max(Price),min(Price) FROM Cars;
SELECT upper (Name) AS 'Names in capitals' FROM Friends;
SELECT lower (Name) AS 'Names in lowercase' FROM Friends WHERE Id IN(1,2,3);
SELECT length('ZetCode');
SELECT total_changes() AS 'Total changes'; --- 数据库连接以来INSERT ,UPDATE,orDELETE的次数
SELECT sqlite_compileoption_used('SQLITE_DEFAULT_FOREIGN_KEYS') AS 'FK'; ---查看某选项的状态
SELECT typeof(12), typeof('ZetCode'), typeof(33.2), typeof(NULL), typeof(x'345edb');

----第二类 聚合函数
SELECT * FROM Cars;
SELECT count( * ) AS '# of cars' FROM Cars;--- 返回行数
SELECT count(Customer) AS '# of orders' FROM Orders;
SELECT count(DISTINCT Customer) AS '# of customers' FROM Orders;

----让sqlite显示NULL
.nullvalue NULL
CREATE TABLE TESTING(Id INTEGER);
INSERT INTO Testing VALUES(1),(2),(3),(NULL),(NULL);
SELECT last_insert_rowid();

--- count( * )和 count(Id)的区别,count( * )考虑NULL,而后者全部去掉NULL
SELECT count(*) AS '# of rows' FROM Testing;
SELECT count(Id) AS '# of non NULL values' FROM Testing;

---平均值
SELECT avg(Price) AS 'Average price' FROM Cars;

---求和
SELECT sum(OrderPrice) AS Sum FROM Orders;

----第三类 日期和时间函数
SELECT date('now');
SELECT datetime('now');
SELECT time('now');
SELECT time();
SELECT date();
SELECT date('now','2 months');
SELECT date('now','-55 days');
SELECT date('now','start of year');
SELECT datetime('now','start of day');
SELECT date('now','weekday 6'); --- 其中 Sunday is0, Monday 1,..., Saturday 6
SELECT date('now','start of year','10 months','weekday 4');
SELECT strftime('%d-%m-%Y');
SELECT 'Current day: '|| strftime('%d');
SELECT 'Days to XMas: '||(strftime('%j','2015-12-24')- strftime('%j','now'));

---- ########### 视图 VIEWS #################

SELECT * FROM Cars;
CREATE VIEW CheapCars AS SELECT Name FROM Cars WHERE Price <30000;
SELECT * FROM CheapCars;
.TABLE s
DROP VIEW CheapCars;
.TABLE s

--VIEW是一种虚拟表格
--记录数据操作
-- Triggers 记录

CREATE TABLE Log(Id INTEGER PRIMARY KEY, OldName TEXT, NewName TEXT,Date TEXT);

CREATE TRIGGER mytrigger UPDATE OF Name ON Friends

BEGIN
INSERT INTO Log(OldName, NewName,Date) VALUES(old.Name,new.Name, datetime('now'));
END;

----查看 trigger
SELECT name, tbl_name FROM sqlite_master WHERE type='trigger';

SELECT * FROM Friends;
UPDATE FriendsSET Name='Frank' WHERE Id=3;
SELECT * FROMLog;

SELECT name, tbl_name FROM sqlite_master WHERE type='trigger';

----SQLite命令组

----两种, 分别以commit和ROLLBACK结尾

BEGIN TRANSACTION;
CREATE TABLE Test(Id INTEGER NOT NULL);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
INSERT INTO Test VALUES(3);
INSERT INTO Test VALUES(NULL);
COMMIT;

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS Test(Id INTEGER NOT NULL);
INSERT INTO Test VALUES(11);
INSERT INTO Test VALUES(12);
INSERT INTO Test VALUES(13);
INSERT INTO Test VALUES(NULL);
ROLLBACK;

--- ############### 导入和导出
.mode --查询模式

--- 导入CSV文件 .IMPORT
.import --- 文件名\表

---### 需要设定 字段的分隔符。
.mode CSV
.import C:/work/mydat.csv TABLE 1

--- 导出 .output
.head on
.mode csv
.output out_Cars.csv
SELECT * FROM Cars;
.output stdout

--- 导出为html格式
.header on
.separator ","
.mode html
.output out_Cars.html
SELECT * FROM Cars;
.output stdout

mySQL简明入门

详情请参考
http://downloads.mysql.com/docs/mysql-tutorial-excerpt-5.1-en.pdf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
mysql -u root ---- 回车
--- 密码 12345

--- Ctrl + D 退出

--- 查看版本
SELECT VERSION(), CURRENT_DATE;

--- 查看数据库
SHOW DATABASES;

--- 创建数据库
 CREATE DATABASE menagerie;

--- 使用数据库
 USE menagerie

--- 创建表格
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1),birth DATE, death DATE);

--- 显示数据库中的表格的名称
SHOW TABLES;

--- 显示表格各列的属性
DESCRIBE pet;

--- 从本地读取文件
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
注意, 如果是Windows, 要使用 LINES TERMINATED BY 'rn';

--- 在表格中插入一行
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
INSERT INTO pet VALUES ('Fluffy',' Harold ','cat ','f ','1993-02-04',NULL);
INSERT INTO pet VALUES ('Claws ','Gwen ','cat ','m ','1994-03-17',NULL);
INSERT INTO pet VALUES ('Buffy ','Harold ','dog ','f ','1989-05-13',NULL);
INSERT INTO pet VALUES ('Fang ','Benny',' dog ','m ','1990-08-27',NULL);
INSERT INTO pet VALUES ('Bowser ','Diane ','dog ','m ','1979-08-31','1995-07-29');
INSERT INTO pet VALUES ('Chirpy',' Gwen',' bird',' f',' 1998-09-11',NULL)
INSERT INTO pet VALUES ('Whistler',' Gwen',' bird',' NULL''1997-12-09',NULL)
INSERT INTO pet VALUES ('Slim',' Benny',' snake',' m ','1996-04-29',NULL)

--- 从数据表中获取数据 
---SELECT 的基本语句格式: 
---SELECT what_to_select
---FROM which_table
---WHERE conditions_to_satisfy;

--- 查询全部数据
SELECT * FROM pet;

--- 修改部分数据 
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

--- 查询部分数据 
SELECT * FROM pet WHERE name = 'Bowser';

--- 比较运算 
SELECT * FROM pet WHERE birth >= '1998-1-1';

--- 逻辑运算 AND
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';

--- 逻辑运算 OR
SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';

--- 运算的优先级
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm'OR (species = 'dog' AND sex = 'f');

--- 选择特定的列
SELECT name, birth FROM pet;

SELECT owner FROM pet;

--- 去重复 
SELECT DISTINCT owner FROM pet;

--- SELECT 的基本组合
SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';

--- 排序, 默认是 由小到大排序
SELECT name, birth FROM pet ORDER BY birth;

--- 由大到小排序
SELECT name, birth FROM pet ORDER BY birth DESC;

--- 多个列排序
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

--- 当前日期 
CURDATE()

--- 两个日期相隔时间
TIMESTAMPDIFF()

--- 形成新的一列 
AS age (见后面的例子)

--- 日期计算
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;

--- 加入新一列的表格再排序, 按照name
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BYname;

--- 加入新一列的表格再排序, 按照 age
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BYage;

--- 判断 is not null
SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;

--- 时间计算
YEAR(), MONTH(), DAYOFMONTH()

--- 哪个月份出生 
SELECT name, birth, MONTH(birth) FROM pet;

--- 按照运算出的结果查找
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

--- 十二月份等的处理
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

--- 对NULL的判断
SELECT 1 IS NULL1 IS NOT NULL;

---匹配与正则表达式, 寻找name中以b开头的
SELECT * FROM pet WHERE name LIKE 'b%';

---寻找name中以fy结尾的
SELECT * FROM pet WHERE name LIKE '%fy';

---寻找名字中含有w的
SELECT * FROM pet WHERE name LIKE '%w%';

---寻找名字只包含五个字母的, 用五个_
SELECT * FROM pet WHERE name LIKE '_____';

---正则表达式的关键字
REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE)

---正则表达式
---. 任意单个字符
---[a-z] a到z的任意字符
---[0-9] 0到9的任意字符
---“*” 之前, 放任何字符, 表示的是任意重复次数的该字符
---.* 表示任意重复次数的任意字符
---^ 开始
---$ 结束

--- 正则表达式举例
--- 以b开始的人名(不区分大小写)
SELECT * FROM pet WHERE name REGEXP '^b';

--- 匹配大小写 BINARY, 名字以小写的b开头的
SELECT * FROM pet WHERE name REGEXP BINARY '^b';

--- 名字以fy结尾的
SELECT * FROM pet WHERE name REGEXP 'fy$';

--- 名字中含有w的
SELECT * FROM pet WHERE name REGEXP 'w';

--- 名字仅由五个字母组成
SELECT * FROM pet WHERE name REGEXP '^.....$';
--或者 
SELECT * FROM pet WHERE name REGEXP '^.{5}$';

--- 统计表格的行数 
SELECT COUNT(*FROM pet;

--- GROUP BY, 针对每一个水平进行计算
SELECT owner, COUNT(*FROM pet GROUP BY owner;
SELECT species, COUNT(*FROM pet GROUP BY species;
SELECT sex, COUNT(*FROM pet GROUP BY sex;

--- 分组可以同时考虑几个因素
SELECT species, sex, COUNT(*FROM pet GROUP BY species, sex;

--- 进一步筛选, 只要 dog 或者 cat的部分记录
SELECT species, sex, COUNT(*FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BYspecies, sex;

--- 只保留已知性别的, 按照 species 和 sex 统计
SELECT species, sex, COUNT(*FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;

注意: select后面的列, 如果使用了count()函数, 则后面的group by后面, 必须跟上相同的列. 否则mysql会报错. 

------使用多个表格
---创建一个新的表
CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));

---载入数据
LOAD DATA INFILE '/home/jinlong/programming/mysql/event.txt' INTO TABLE event;

---同时查询两个表格, 注意应该先将两个表格 
---用 INNER JOIN 合并.
---ON 表格合并的凭借 对于pet表格来说, 是name, 所以是 pet.name
----对于event表格来说, 也是name, 所以是event.name
---而筛选的条件,是 event表格中的type为 'litter'

SELECT pet.name, 
 (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,remark
 FROM pet INNER JOIN event
ON pet.name = event.name 
 WHERE event.type = 'litter';

---为了计算的方便, 有时候可以将同一个表格进行 INNER JOIN
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
 FROM pet AS p1 INNER JOIN pet AS p2
ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

---查询数据库的名称
SELECT DATABASE();

---显示表名
SHOW TABLES;

---获取表格的结构
DESCRIBE pet;

---# MYSQL的批处理 从terminal运行
---UNIX平台
mysql < batch-file 
---WINDOWS 平台
mysql -e "source batch-file"

---# 
---从terminal运行 mySQL的批处理文件
mysql -h host -user -< batch-file

---从terminal运行 结果的分屏显示
mysql < batch-file | more

---从terminal运行 输出结果导出到文件
mysql < batch-file > mysql.out

---从terminal运行 在批处理模式下显示输出
mysql -t

---从terminal运行 在批处理模式下, 保存输入的命令
mysql -vvv

---在mySQL中运行脚本文件
source filename;
或 . filename;


------------------------------------------------------------------##
--- 创建一个商品价目表, 并基于该表做各种查询

show databases;
create database test;
use test;

CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20)
DEFAULT ''
NOT NULL,
price
DOUBLE(16,2)
DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));

INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

SELECT * FROM shop;

---查找最贵商品
SELECT MAX(article) AS article FROM shop;

---查找最高价对应的货品, 供应商
SELECT article, dealer, price
FROM
shop
WHERE price=(SELECT MAX(price) FROM shop);

---每一组的最贵商品
SELECT article, MAX(price) AS price
FROM
shop
GROUP BY article;

---每组最贵商品所在的行
SELECT article, dealer, price
FROM
shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);

---临时变量
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

------------------------------------##
---# KEYS

CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt''polo''dress'NOT NULL,
color ENUM('red''blue''orange''white''black'NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL'polo''blue'@last),
(NULL'dress''white'@last),
(NULL't-shirt''blue'@last);

INSERT INTO person VALUES (NULL'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL'dress''orange'@last),
(NULL'polo''red'@last),
(NULL'dress''blue'@last),
(NULL't-shirt''white'@last);

SELECT * FROM person;

##---查询keys
SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR field2_index = '1'

--- 查询每个月的访问量

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
(2000,2,23),(2000,2,23);


SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;


---为行增加 unique identity for new rows
AUTO_INCREMENT

CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;

诗一首 北方人在南方

当家乡随着云彩远去,

老屋也消失在天边,

游子的心,

便开始漂泊。

 

当父母的叮咛随着汽笛的轰鸣远去,

小小的渔船消失在水天一线,

游子的心,

就注定开始漂泊。

 

从北方,

吹来甘冽的风,

雾霾却遮住了你的双眼。

家乡,

已经干瘪成几个汉字。

父母的微笑,

都只映在屏幕上。

 

在南方,

你在冬日的暖阳下,

徜徉在海滨的清风中。

家乡,

却化作一块云彩环绕着你的心,

时雨时晴,

幻化出儿时的风景。

 

2015年10月23日

香港大埔