81.《Bioinformatics Data Skills》之SQLite SELECT 操作

SQL数据库的SELECT操作将是你学过的最为有用的数据操作技术之一。除了关系数据库,你能够在广泛的工具中找到它的影子。不同数据库管理系统的语言相当一致,你可以将这项技术应用于公共的关系数据库UCSC基因组浏览器与Ensembl数据库。

SELECT 基本操作

继续采用上节例子数据gwascat.db,以下命令可以查找所有信息。

sqlite> SELECT * FROM gwascat;
1|2014-08-02|24388013|Ferreira MA|2013-12-30|...
2|2014-08-02|24388013|Ferreira MA|2013-12-30|...
...

基本格式为SELECT <colname> FROM <table>* 代表所有列。

SQLite可以在终端而不进入交互式界面直接操作,方便我们直接查询结果,如

$ sqlite3 gwascat.db "SELECT * FROM gwascat" > result.txt
$ head result.txt -n 1
1|2014-08-02|24388013|Ferreira MA|2013-12-30|...

LIMIT 限制行数

以上语句返回了所有结果,可通过LIMIT限制结果行数:

sqlite> SELECT * FROM gwascat LIMIT 2;
1|2014-08-02|24388013|Ferreira MA|2013-12-30|...
2|2014-08-02|24388013|Ferreira MA|2013-12-30|...

选择特定列

前面我们选择了所有列,可以返回指定列的结果:

sqlite> SELECT trait, chrom , position , strongest_risk_snp , pvalue FROM gwascat LIMIT 5;
Asthma and hay fever|6|32658824|rs9273373|4.0e-14
Asthma and hay fever|4|38798089|rs4833095|5.0e-12
Asthma and hay fever|5|111131801|rs1438673|3.0e-11
Asthma and hay fever|2|102350089|rs10197862|4.0e-11
Asthma and hay fever|17|39966427|rs7212938|4.0e-10

清楚起见,进行以下展示设置(使用column模式,展示表格的头部):

sqlite> .mode column
sqlite> .header on

再次查询结果显示为:

sqlite> SELECT trait, chrom , position , strongest_risk_snp , pvalue FROM gwascat LIMIT 5;
trait                 chrom  position   strongest_risk_snp  pvalue
--------------------  -----  ---------  ------------------  -------
Asthma and hay fever  6      32658824   rs9273373           4.0e-14
Asthma and hay fever  4      38798089   rs4833095           5.0e-12
Asthma and hay fever  5      111131801  rs1438673           3.0e-11
Asthma and hay fever  2      102350089  rs10197862          4.0e-11
Asthma and hay fever  17     39966427   rs7212938           4.0e-10

如果列很多的话可以恢复更紧凑的list布局(.mode list)。

注意:即使是相同的操作,结果顺序也并非固定,如果需要固定顺序,采用ORDER BY命令(见下方)。

ORDER BY 排序结果

ORDER BY接需要排序的列,例如根据P值进行排序:

sqlite> SELECT trait, chrom , position , strongest_risk_snp , pvalue FROM gwascat ORDER BY pvalue LIMIT 5;
trait          chrom  position  strongest_risk_snp  pvalue
-------------  -----  --------  ------------------  ------
Brain imaging                   rs10932886
Brain imaging                   rs429358
Brain imaging                   rs7610017
Brain imaging                   rs6463843
Brain imaging                   rs2075650

注意,某列若包含多种数据类型,将按照 NULL(缺失) < INTEGER(整数) < REAL(实数) < TEXL(文本) < BLOB(二进制文件) 的顺序展示,这里首先展示的是缺失值。

默认升序,增加DESC表示降序,如:

sqlite> SELECT trait , chrom, position , strongest_risk_snp , pvalue FROM gwascat ORDER BY pvalue DESC LIMIT 5;
trait                                       chrom  position   strongest_risk_snp  pvalue
------------------------------------------  -----  ---------  ------------------  ----------
Serum protein levels (sST2)                 18     10382886   rs206548            90000000.0
Periodontitis (Mean PAL)                    14     68893914   rs12050161          4000000.0
Coronary artery disease or ischemic stroke  11     116778201  rs964184            2.0e-05
Lipid traits                                1      62447248   rs10158897          9.0e-06
Rheumatoid arthritis                        15     38541832   rs8032939           9.0e-06

这里前两行的P值非常可疑,有可能是科学记数法负号丢失造成的错误,再次说明不要盲目相信数据。

WHERE筛选结果

可以通过WHERE命令设定条件筛选结果,比如说筛选名字为“rs206548”的snp所在行:

sqlite> SELECT trait, chrom, position , strongest_risk_snp , pvalue FROM gwascat WHERE strongest_risk_snp == "rs206548";
trait                        chrom  position  strongest_risk_snp  pvalue
---------------------------  -----  --------  ------------------  ----------
Serum protein levels (sST2)  18     10382886  rs206548            90000000.0

这里使用 =或者 == 代表相等,其它常见的过滤操作包括:

image-20211217192854491
image-20211217192915619

因为NULL与任意值不等,匹配缺失值的不能使用 = ,而要采用IS操作,如:

sqlite> SELECT trait, chrom, position , strongest_risk_snp , pvalue FROM gwascat WHERE pvalue IS NOT NULL ORDER BY pvalu
e LIMIT 5;
trait                                      chrom  position   strongest_risk_snp  pvalue
-----------------------------------------  -----  ---------  ------------------  ------
HDL cholesterol                            16     56959412   rs3764261           0.0
Age-related macular degeneration           10     122454932  rs10490924          0.0
Age-related macular degeneration           1      196710325  rs10737680          0.0
Urate levels                               4      9942428    rs12498742          0.0
Vascular endothelial growth factor levels  6      43957789   rs4513773           0.0

可以通过LIKE进行文本的模糊匹配(使用"%代表通配符"):

sqlite> SELECT author , pubmedid , journal FROM gwascat WHERE author LIKE "%Ferreira%" LIMIT 5;
author       pubmedid  journal
-----------  --------  ----------------------
Ferreira MA  24388013  J Allergy Clin Immunol
Ferreira MA  24388013  J Allergy Clin Immunol
Ferreira MA  24388013  J Allergy Clin Immunol
Ferreira MA  24388013  J Allergy Clin Immunol
Ferreira MA  24388013  J Allergy Clin Immunol

WHERE条件支持与,或,非逻辑操作,而多个逻辑之间也可以通过括号组合,如选择在染色体1,2,3上且P值小于10^-11结果:

sqlite> SELECT trait, chrom, position , strongest_risk_snp , pvalue FROM gwascat WHERE (chrom == '1' OR chrom =='2' OR chrom == '3') AND pvalue < 1e-11 ORDER BY pvalue LIMIT 5;
trait                             chrom  position   strongest_risk_snp  pvalue
--------------------------------  -----  ---------  ------------------  ---------------------
Age-related macular degeneration  1      196710325  rs10737680          0.0
Bilirubin levels                  2      233763993  rs6742078           4.94065645841247e-324
Butyrylcholinesterase levels      3      165773492  rs1803274           6.0e-262
Age-related macular degeneration  1      196690107  rs1061170           1.0e-261
Metabolic traits                  2      73591809   rs13391552          5.0e-252

这里的多个OR操作可以改写为更简洁的IN

sqlite> SELECT trait, chrom, position , strongest_risk_snp , pvalue FROM gwascat WHERE (chrom IN ('1', '2', '3')) AND pv
alue < 1e-11 ORDER BY pvalue LIMIT 5;
trait                             chrom  position   strongest_risk_snp  pvalue
--------------------------------  -----  ---------  ------------------  ---------------------
Age-related macular degeneration  1      196710325  rs10737680          0.0
Bilirubin levels                  2      233763993  rs6742078           4.94065645841247e-324
Butyrylcholinesterase levels      3      165773492  rs1803274           6.0e-262
Age-related macular degeneration  1      196690107  rs1061170           1.0e-261
Metabolic traits                  2      73591809   rs13391552          5.0e-252
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 199,393评论 5 467
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 83,790评论 2 376
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 146,391评论 0 330
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 53,703评论 1 270
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 62,613评论 5 359
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,003评论 1 275
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,507评论 3 390
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,158评论 0 254
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,300评论 1 294
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,256评论 2 317
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,274评论 1 328
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 32,984评论 3 316
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,569评论 3 303
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,662评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,899评论 1 255
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 42,268评论 2 345
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 41,840评论 2 339

推荐阅读更多精彩内容