三、数据查询sql命令-课件材料及其应用

第六讲SQL命令与数据查询生物医学信息学系周萍本讲内容SQL命令格式及其基本应用基于SQL命令的生物信息学数据挖掘扩充知识：KEGG简介外部数据导入复杂SQL查询学以致用：文献检索与科研思维培养 SQL命令查询SQL是StructuredQueryLanguage的缩写，意为“结构话查询语言”，是操作数据库的标准语言。 SQL语言基本语法Select子句From子句Where子句Orderby子句分组统计Groupby子句SELECT命令格式SELECT[ALL|DISTINCT|DISTINCTROW|TOP]{*|talbe.*|[table.]field1[ASalias1][,[table.]field2[ASalias2][,…]]}FROMtableexpression[,…][INexternaldatabase][WHERE…][GROUPBY…][HAVING…][ORDERBY…][WITHOWNERACCESSOPTION]谓词ALL|DISTINCT|DISTINCTROW|TOP的用法all：显示查询结果中全部数据。distinct：忽略在选定字段中包含重复数据的记录。DISTINCTROW：根据整个重复记录而不是某些重复字段来忽略数据。TOP

n：返回位于ORDER

BY

子句所指定范围内靠前或靠后的前n条记录。

。显示字段：{*|talbe.*|[table.]field1[ASalias1][,[table.]field2[ASalias2][,…]]}

*：代表全部字段表名.字段名：显示指定表中的指定字段，如果select是从一个表中查询，“表名.”可以省略。例如：医生.姓名AS：给字段在查找结果中以新的名称显示例如：SELECT姓名asxmfrom医生From子句

From语句指定可以用来查询记录的表和查询，它可以包含表示引用的表别名，From语句如下所示：Fromtable-list[asalias]Asalias表别名，如果用别名可缩短表名或简化有隐义的名称，如从病人表中选择姓名和身份证号字段，表的别名是患者：Select姓名，身份证号from病人as患者Where子句

Where子句限制Select语句查询记录的范围。检索文本字符串必须用引号括起来。数据必须用#标记括起来。使用通配符必须包括关键字like一个Where子句最多可以包括由关键字AND和OR组合的40列Where子句的语法如下：Whereexpression1[{AND|OR}expression2[…]]例：Where姓名=’李有病’and性别=‘男’Where子句中常使用的运算符

比较运算符例如：返回85年7月出生的病人：SELECT姓名，出生日期FROMOrdersWHERE出生日期>#7/1/85#AND出生日期<#7/31/85#比较运算符含义比较运算符含义=等于>

大于<

小于>=大于等于<=小于等于<>

不等于!>不大于!<不小于（2）范围（BETWEEN和NOTBETWEEN）BETWEEN…AND…运算符指定了要搜索的一个闭区间。例如：返回96年1月到96年2月的病人：WHERE出生日期Between#1/1/96#And#2/1/96#（3）列表（IN，NOTIN）IN运算符用来匹配列表中的任何一个值。IN子句可以代替用OR子句连接的一连串的条件。例如：要找所有男病人：SELECT姓名，性别FROM病人WHERE性别In(‘男’)通配符含义？任何一个单一的字符*任意长度的字符#0~9之间的单一数字[字符列表]在字符列表里的任一值[！字符列表]不在字符列表里的任一值-指定字符范围，两边的值分别为其上下限（4）模式匹配(LIKE)LIKE运算符检验一个包含字符串数据的字段值是否匹配某一指定模式。例如：返回联系方式在到之间的客户：SELECT姓名，联系方式FROM病人WHEREPhoneLike‘13#########’SELECT*fromgpl570wheregene_symbollike'DDR1'Orderby子句

Orderby决定返回的记录的排列顺序，它是一个可选子句，语法如下：Orderbycolmn1[{ASC|DESC}][,…]]例如：Orderby身份证号，性别分组统计Groupby子句

Sum(字段表达式)求某字段表达式的和，忽略字段为NULL的数据。Avg(字段表达式)求某字段表达式的平均值，忽略字段为NULL的数据。Count(字段表达式)统计查询返回的记录数，忽略字段为NULL的数据。如果表达式使用通配符*，则返回所有记录数。Max(字段表达式)、Min(字段表达式)返回表达式的最大值或最小值。First(字段表达式)、Last(字段表达式)返回在查询所返回的结果集中的第一个或者最后一个记录的字段值StDev(字段表达式)返回已包含在查询的指定字段内的一组值作为总体样本或总体样本抽样的标准偏差的估计值Var(字段表达式)、VarP(字段表达式)返回已包含在查询的指定字段内的一组值作为总体样本或总体样本抽样的方差的估计值。Groupby子句用于对查询结果按指定的列进行分组，并可以利用函数进行统计，如求平均值、最大值、最小值和计数等。即对查询结果进行分组统计，统计选项必须是数值型数据（1）分组字段列表，列表中字段名称的顺序决定了分组的先后顺序，最多可以指定10个用于分组记录的字段。可以和Groupby一起使用的统计函数有以下几个。（2）Having分组过滤条件，与Groupby子句配合使用表示条件，将统计结果作为过滤条件。如果在分组时要求满足某个条件，可以用having子句。Having子句总是跟在Groupby子句之后，用来限定分组必须满足的条件，将满足Having子句指定条件的组放到结果集中。Having子句不可单独使用。在Select语句中，如果Where子句和Having子句同时存在，则Where子句先限定记录，然后进行分组，最后再用Having子句限定分组。例如：从“挂号”表中统计大夫最后一次的挂号时间，并且近保留最后一次的挂号时间在2010年5月19日之后的记录。SelectDID,max(挂号时间)as'maxdate'from挂号groupbyDIDhavingmax(挂号时间)>#5/19/2010#基于SQL命令的生物信息学数据挖掘扩充知识：KEGG简介外部数据导入复杂SQL查询KEGG简介

KEGGPATHWAYmappingistheprocesstomapmoleculardatasets,especiallylarge-scaledatasetsingenomics,transcriptomics,proteomics,andmetabolomics,totheKEGGpathwaymapsforbiologicalinterpretaionofhigher-levelsystemicfunctions.建立kegg_mRNA数据库任务要求：在D盘建立kegg_mRNA数据库在上面数据库中建立表pathway将diagnosis.xlsx文件生成表diagnosis将GSE29044-PL570_series_matrix.xlsx文件生成表mRNA将GPL570.txt生成表GPL570建立表GPL570和表mRNA之间的关联建立kegg_mRNA数据库建立表pathway从KEGG中进入特定pathway从KEGG中进入pathway显示基因选取基因并复制将复制内容粘贴到genes字段在创建选项卡中的列表工具组中点击列表按钮，显示备注字段内容依次添加下列内容将diagnosis.xlsx文件生成表diagnosis将GSE29044-PL570_series_matrix.xlsx

文件生成表mRNA删除多余记录示例将GPL570.txt生成表GPL570

复杂SQL查询背景：乳腺癌的发生发展与年龄和ER、PR和her2密切相关，同时也与细胞周期、凋亡和p53信号转导通路的基因表达相关。目的：研究临床指标和上述通路基因表达的关系。方法:SQL查询在diagnosis表中查找age20-35岁的type等于T的记录

Selefromdiagnosiswhereage=‘20-35’andtype=‘T’

查询20-35平均表达值大于等于12的基因

注意：本组基因表达数据事先都经过1og2变换

selectgpl570.*fromgpl570innerjoinmrnaongpl570.id=mrna.id_refwhere(g104+g105+g106+g107+g108+g109)/6>=12查询20-35平均表达值大于等于10的Apoptosis途径相关基因SELECTgpl570.gene_symbol,mrna.g104,mrna.g105,mrna.g106,mrna.g107,mrna.g108,mrna.g109fromgpl570,mrna,pathwaywhere(mrna.g104+mrna.g105+mrna.g106+mrna.g107+mrna.g108+mrna.g109)/6>=10andgpl570.id=mrna.id_refandinstr(pathway.genes,gpl570.gene_symbol)>0andpathway.path='Apoptosis'Pathway分析Apoptosismap结果学以致用：文献检索与科研思维培养

科研文章结构：题目：引人入胜题目：A、B、C主词摘要：言简意赅生物医学摘要组成：背景，目的，方法，结果，结论关键词：依据在文章中的重要成都排列前言首先针对题目第一主词第一句：教科书式语言（A是xxx具有什么作用，引用一篇权威文献，注意不能抄袭原文！）第二句：进一步的作用或应用，引用1-2篇综述文献。第三句：存在的争议或问题，引用几篇近2年研究型文献。接下来将读者往本文研究的内容方面引领。然后针对题目第二和第三主词进行说明最后是本文工作材料和方法材料：涉及到的试剂、仪器等要有型号和生产商甚至批号，因此在实验中一定在实验记录中记录下来方法：实验要用专业术语描述，但不能抄袭别人文章。生物信息学软件通常是自由软件，但要在文章的参考文献中引用结果图表要规范结论与讨论在生物医学研究中，尤其是分子生物学和基因方面的研究，结论很容易出现片面性，因此通常在讨论部分中将自己的工作结果与别人已发表的论文工作进行对比，对于与之不同的结果进行合理的解释课题练习不同年龄组癌组织在凋亡途径中高表达基因的差异不同年龄组癌旁正常组织在凋亡途径中高表达基因的差异不同