mysql数据库与生信

发表于 | 分类于

  主要介绍win下和linux下连接重用生信数据库，操作数据库中表，以及根据自己的需要下载公共数据，创建mysql数据库。

mysql数据库与生信

mysql学习资料

（1）21分钟 MySQL 入门教程

（2）MySQL 5.7 Reference Manual: 具体技巧例如 Counting Rows；Pattern Matching

mysql连接生信主要公共数据库

linux下格式：

mysql -h[host] -u[user] -p[password] -P[port] [database-name]

  • -h : 该命令用于指定客户端所要登录的MySQL主机名, 登录当前机器该参数可以省略;
  • 退出：输入 exit; 或 quit; 退出登录

例如：

Server User Password Port
mysql-db.1000genomes.org anonymous - 4272

（1）1000 Genomes: since June 16, 2011

mysql -h mysql-db.1000genomes.org -u anonymous -P 4272  -A

（2）UCSC Downloading Data using MySQL

mysql -h genome-mysql.soe.ucsc.edu -u genome -P 3306 -A

或者

mysql -h genome-euro-mysql.soe.ucsc.edu -u genome -P 3306 -A

注意：必须加上端口号-P 3306，按照官网的方法会报错：

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘genome-mysql.soe.ucsc.edu’ (111)

（3）The Ensembl public MySQL Servers

mysql -h ensembldb.ensembl.org -u anonymous -P  5306  -A

（4）GO

mysql -h mysql-amigo.ebi.ac.uk -u go_select -P 4085 -pamigo -A

The -A flag is optional but is recommended for speed.

其他数据库：参见网址

mysql直接读ucsc数据库数据

mysql --user=genome --host=genome-mysql.cse.ucsc.edu -P 3306 -A -D hg19 -e 'select * from refGene where name="NM_000038"' | less

显示：

bin     name    chrom   strand  txStart txEnd   cdsStart        cdsEnd  exonCount       exonStarts      exonEnds        score   name2
1440    NM_000038       chr5    +       112073555       112181936       112090587       112179823       16      112073555,112090569,112

windows下

使用Navicat，其好处在于更加方便直观

如图：

查询某一张表，查看具体内容，导出，筛选等等，很方便

mac下可以使用Sequel Pro

python连接mysql数据库

pip2.7 install MySQL-python

解决：libmysqlclient.so.18库在mysql安装目录的lib下，设置LD_LIBRARY_PATH环境变量即可

export LD_LIBRARY_PATH="$DYLD_LIBRARY_PATH:/home/wangdong/local/mysql/lib"

ps： 网上一般的做法是在mysql/lib目录下找到libmysqlclient.so.18，设置软链接到usr/lib或者usr/lib64，但这两个目录都需要管理员权限的。

应用例子

利用mysql客户端查询UCSC数据库，获取geneSymbol名的bed信息

脚本geneInfo.py

根据需要创建UCSC genome MySQL database

数据来源：UCSC hg19 hg38

需要两种文件：

  • （1）*sql是一种数据库脚本语言，是一种对关系数据库中的数据进行定义和操作的句法，为大多数关系数据库管理系统所支持的工业标准。可以用文本编辑器打开并查看
  • （2)数据文件。txt文件

例子：.sql文件

使用shell脚本自动完成

1
2
3
4
5
6
7
8
9
10
11
# create database
mysql -u root -prootab -e 'create database hg19'
# obtain table schema
cd /home/wangdong/local/mysql/data
wget http://hgdownload.cse.ucsc.edu/goldenPath/hg19/database/ensGene.sql
# create table
mysql -u root -prootab hg19 < ensGene.sql
# obtain and import table data
wget http://hgdownload.cse.ucsc.edu/goldenPath/hg19/database/ensGene.txt.gz
gunzip ensGene.txt.gz
mysqlimport -u root -prootab --local hg19 ensGene.txt

注意：数据需要下载到/home/wangdong/local/mysql/data，位置可以在配置文件(my.cnf)中修改，具体操作查看链接：MySQL 转移 datadir

  • 为了便于在命令提示符下显示中文, 在创建时通过 character set gbk 将数据库字符编码指定为 gbk

    create database samp_db character set gbk;

  • 创建数据库表时，对于一些较长的语句在命令提示符下可能容易输错, 因此我们可以通过任何文本编辑器将语句输入好后保存为 createtable.sql 的文件中, 通过命令提示符下的文件重定向执行执行该脚本。

    mysql -D samp_db -u root -p < createtable.sql

  • desc TABLE_NAME; 显示数据库表的结构（desc是describe的缩写）

  • select * from TABLE_NAME limit 0,2;显示表的前两行内容，0表示起始行偏移量

补充LD_LIBRARY_PATH环境变量

LIBRARY_PATH和LD_LIBRARY_PATH是Linux下的两个环境变量，二者的含义和作用分别如下：

LIBRARY_PATH环境变量用于在程序编译期间查找动态链接库时指定查找共享库的路径， 例如，指定gcc编译需要用到的动态链接库的目录。设置方法如下（其中，LIBDIR1和LIBDIR2为两个库目录）：

export LIBRARY_PATH=LIBDIR1:LIBDIR2:$LIBRARY_PATH

LD_LIBRARY_PATH环境变量用于在程序加载运行期间查找动态链接库时指定除了系统默认路径之外的其他路径， 注意，LD_LIBRARY_PATH中指定的路径会在系统默认路径之前进行查找。设置方法如下（其中，LIBDIR1和LIBDIR2为两个库目录）：

export LD_LIBRARY_PATH=LIBDIR1:LIBDIR2:$LD_LIBRARY_PATH

区别与使用：

开发时，设置LIBRARY_PATH，以便gcc能够找到编译时需要的动态链接库。

发布时，设置LD_LIBRARY_PATH，以便程序加载运行时能够自动找到需要的动态链接库。

可视化管理工具 MySQL Workbench

  尽管我们可以在命令提示符下通过一行行的输入或者通过重定向文件来执行mysql语句, 但该方式效率较低, 由于没有执行前的语法自动检查, 输入失误造成的一些错误的可能性会大大增加, 这时不妨试试一些可视化的MySQL数据库管理工具, MySQL Workbench 就是 MySQL 官方 为 MySQL 提供的一款可视化管理工具, 你可以在里面通过可视化的方式直接管理数据库中的内容, 并且 MySQL Workbench 的 SQL 脚本编辑器支持语法高亮以及输入时的语法检查, 当然, 它的功能强大, 绝不仅限于这两点。

MySQL Workbench官方介绍: http://www.mysql.com/products/workbench/

MySQL Workbench 下载页: http://dev.mysql.com/downloads/tools/workbench/

MySQL Show命令总结

MySQL中有很多的基本命令，show命令也是其中之一，在很多使用者中对show命令的使用还容易产生混淆，本文汇集了show命令的众多用法。

  • show tables或show tables from database_name; — 显示当前数据库中所有表的名称。
  • show tables like ‘my%’; — 显示当前数据库中以my开头的表。
  • show databases; — 显示mysql中所有数据库的名称。
  • show [full] columns from table_name from database_name; 或show [full] columns from database_name.table_name; — 显示表中列名称。
  • show grants for user_name; — 显示一个用户的权限，显示结果类似于grant 命令。
  • show index from table_name; — 显示表的索引。
  • show status;(show master status;show slave status) — 显示一些系统特定资源的信息，例如，正在运行的线程数量。
  • show variables; — 显示系统变量的名称和值。
  • show [full] processlist; — 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。
  • show table status; — 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。
  • show privileges; — 显示服务器所支持的不同权限。
  • show create database database_name; — 显示create database 语句是否能够创建指定的数据库。
  • show create table table_name; — 显示create database 语句是否能够创建指定的数据库。
  • show engies; — 显示安装以后可用的存储引擎和默认引擎。
  • show innodb status; — 显示innoDB存储引擎的状态。
  • show logs; — 显示BDB存储引擎的日志。
  • show warnings; — 显示最后一个执行的语句所产生的错误、警告和通知。
  • show errors; — 只显示最后一个执行语句所产生的错误。
  • show [storage] engines; –显示安装后的可用存储引擎和默认引擎。

参考

（1）生物信息学学者学习mysql之路

（2）Linux环境变量LD_LIBRARY_PATH

（3）使用MySQL制作SNP146数据库

（4）How to: create a partial UCSC genome MySQL database

（5）5分钟入门MySQL Workbench

-------------本文结束感谢您的阅读-------------