0%

安装MySQL

在Ubuntu中使用apt安装MySQL Server,如果是CentOS,将apt替换为yum。

1
sudo apt install mysql-server
以前安装过程中需要设置MySQL root用户密码,现在Ubuntu 18.04和CentOS 8安装MySQL都不需要设置root的密码了。安装完MySQL后,运行mysql_secure_installation,设置root密码和修改一些安全配置。
1
sudo mysql_secure_installation

启动、重启、查看、登录MySQL

启动MySQL

1
systemctl start mysqld

重启MySQL

1
systemctl restart mysqld
查看MySQL状态
1
systemctl status mysql.service
登录MySQL

1
mysql -u root -p

MySQL基础

MySQL语句约定使用大写,其实它是大小写不敏感的,语句以分号;结尾。 查看MySQL版本

1
SELECT VERSION();
1
2
3
4
5
6
+-----------+
| VERSION() |
+-----------+
| 8.0.17 |
+-----------+
1 row in set (0.01 sec)

查看数据库

1
SHOW DATABASES;
1
2
3
4
5
6
7
8
9
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
### SQL初步
新建数据库

1
CREATE DATABASE eg_db;

删除数据库

1
DROP DATABASE eg_db;

选定数据库 对表进行操作之前,需要选定数据库

1
USE eg_db;

创建表格 数据以表格形式保存,每一行是一个记录,每一列是一个属性。

1
2
3
4
5
CREATE TABLE person (
id int,
name varchar(50),
age int
);

查看表结构

1
DESCRIBE person;
1
2
3
4
5
6
7
8
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除表

1
DROP TABLE person;

重命名表

1
ALTER TABLE person RENAME people;

向表中添加一列

1
ALTER TABLE person ADD address varchar(100);

删除表中一列

1
ALTER TABLE person DROP COLUMN address;

修改一个列的数据类型

1
ALTER TABLE person MODIFY address varchar(50);

重命名一个列

1
ALTER TABLE person CHANGE COLUMN address addr varchar(50);

向表格中插入一条记录 INSERT INTO 表名称 VALUES (值1,值2,...)

1
INSERT INTO person VALUES (1, 'chenwen', 28, 'HNU');
或:INSERT INTO 表名称 (列1,列2,...) VALUES (值1,值2,...)

从表格中查询记录 SELECT 列名称1,列名称2... FROM 表名称;

1
SELECT name FROM person;
或:SELECT * FROM 表名称;

从表格中按条件查询一条记录 SELECT 列名称 FROM 表名 WHERE 列 运算符 值;

1
SELECT * FROM person WHERE name='chenwen';

从表格中删除一条记录 DELETE FROM 表名称 WHERE 列 运算符 值;

1
DELETE FROM person WHERE name='chenwen';

或:DELETE * FROM 表名称;

从表格中更新一条记录 UPDATE 表名称 SET 列名称=新值 WHERE 列=值;

1
UPDATE person SET age=31 WHERE name='chenwen';

返回结果删除重复项 SELECT DISTINCT 列名称 FROM 表名称;

1
SELECT DISTINCT name FROM person;

对查询结果按指定列进行排序 SELECT * FROM 表名称 ORDER BY 列名称;

1
SELECT * FROM person ORDER BY id;

或:SELECT * FROM 表名称 ORDER BY 列名称 DESC;

1
SELECT * FROM person ORDER BY id DESC;
以上命令很多,掌握它们的要领是按照增、删、改、查去运用它们,这也是我们实际上最常用的操作。操作数据库之前,记得使用USE选定这个数据库。

MySQL用户及权限管理

MySQL数据库默认只有一个root用户,MySQL将用户信息保存在mysql数据库user表中。 创建一个新用户 CREATE USER 用户名 IDENTIFIED BY '密码';

1
CREATE USER chenwen IDENTIFIED BY 'password';
新用户不能创建新的数据库,因为没有设置权限。我们使用root账号创建新用户后,也顺便创建这个用户操作的数据库,并授予新用户权限,而不是用新用户去创建它的数据库。

删除用户 DROP USER 用户名;

1
DROP USER chenwen;

重命名用户 RENAME USER 原用户名 TO 新用户名;

1
RENAME USER chenwen TO cw;

修改当前用户密码 SET PASSWORD=PASSWORD('新密码');

1
SET PASSWORD=PASSWORD('new_password');

修改指定用户密码 SET PASSWORD FOR 用户名 = PASSWORD('新密码');

1
SET PASSWORD FOR chenwen = PASSWORD('new_password');

授予权限 授予一个用户权限:GRANT ALL PRIVILEGES ON 层级 TO 用户名@主机 WITH GRANT OPTION; 例如,授予chenwen用户针对eg_db数据库全部权限

1
GRANT ALL PRIVILEGES ON eg_db.* TO chenwen@'%' WITH GRANT OPTION;

如果你用的是老版本的MySQL,比如5.7,授权的命令不一样

1
GRANT ALL PRIVILEGES ON eg_db.* TO chenwen@'%' IDENTIFIED BY 'password';
可以使用以下方式指定权限范围
1
2
3
4
1. 所有主机:'%'
2. 精确的主机名或IP地址:www.biochen.com或者192.168.1.1
3. 使用"*"通配符:*.biochen.com
4. 指定一个网段:192.168.1.0/255.255.255.0

MySQL备份与恢复

备份数据库 mysqldump -u root -p 数据库名称 > 备份文件.sql

1
mysqldump -u root -p eg_db > eg_db.sql

mysqldump备份出来的是纯文本的SQL文件,可以修改后作为其他数据库数据使用。

从备份文件中恢复数据库 mysql -u root -p 数据库名称 < 备份文件.sql

1
mysqldump -u root -p eg_db > eg_db.sql

MySQL的字符编码

MySQL 8.0数据库的默认编码是utf8,老版本默认是latin,再也不用担心字符编码设置不对的乱码了。 以下命令可以查看MySQL当前使用的编码

1
SHOW VARIABLES LIKE 'character_set%';
1
2
3
4
5
6
7
8
9
10
11
12
13
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
1
SHOW VARIABLES LIKE 'collation%';
1
2
3
4
5
6
7
8
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

创建数据库时指定编码

1
2
3
CREATE DATABASE eg_db
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE stf8_general_ci;

修改一个数据库的编码

1
ALTER DATABASE eg_db CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL 数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。 数值类型 MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1 字节 (-128,127) (0,255) 小整数值
SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38,1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8 字节 (1.797 693 134 862 315 7 E+308,2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度 浮点数值
DECIMAL DECIMAL(M,D) ,如果M>D,为M+2,否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间类型 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性。

类型 大小 (字节) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 8 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值,时间戳

字符串类型 字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型,有相同的最大长度和存储需求。

类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

结束

我大部分时候,只是创建一个MySQL数据库给其他程序调用,比如WordPress。以上的内容,基本适合这种轻量级应用了。感谢你看到结尾,最后我们以一个命令结束。 退出MySQL

1
EXIT;

对于实验生物学研究者来说,经常遇到的一个场景是,对照组测量3个值,实验组测量3个值,然后用T检验得出均值是否有显著性差异,然后做出结论。如下图所示:

那么T检验靠谱吗?本文采用模拟抽样的方法来探讨这件事情,由于本人统计学知识有限,还请各位读者批评指正。

我们通过构建两个随机总体,总体1的均值固定为1(模拟control),总体2的均值我们从依次取0.5到2.0(模拟treatment相对与control的变化),标准差我们取0.01、0.05、0.10(模拟测量误差1%,5%,10%)。总体数量为1000,每次随机抽取3个样本,抽样1000次,考察通过T检验得出正确结论的频率。代码如下:

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
import numpy as np
import pandas as pd
import seaborn as sns
from scipy import stats
import matplotlib.pyplot as plt
plt.xkcd()
sns.set(style="white", context="talk")
def norm_t(loc1, loc2, scale1, scale2, n):
normSpace1 = stats.norm.rvs(loc = loc1, scale = scale1, size = 1000)
normSpace2 = stats.norm.rvs(loc = loc2, scale = scale2, size = 1000)
count = 0
for i in range(1000):
sample1 = np.random.choice(normSpace1, n)
sample2 = np.random.choice(normSpace2, n)
t, p = stats.ttest_ind(sample1, sample2)
if (p &lt; 0.05 and (sample1.mean() &gt; sample2.mean()) == (loc1 &gt; loc2)):
count = count + 1
return count / 1000
def plot(stds):
df = pd.DataFrame()
for std in stds:
x = np.arange(0.5, 2.1, 0.1)
y = [norm_t(loc1 = 1, loc2 = i, scale1 = std, scale2 = std, n = 3) for i in x]
z = [std for i in x]
df_std = pd.DataFrame([x, y, z]).T
df_std.columns = ["difference", "sensitivity", "std"]
if df.shape == (0, 0):
df = df_std
else:
df = df.append(df_std)
sns.pointplot(x = "difference", y = "sensitivity", hue = "std", data = df)
plot([0.01, 0.05, 0.10])

通过上图,我们发现,当标准差我们取0.01(模拟测量误差1%)时,增大到1.1倍或者减小到0.9倍,正确率可以达到100%。而当标准差取0.05和0.10(模拟测量误差5%和10%),效果就不理想了,本来是有差异的,还是有很大几率通过T检验得出错误的结论。显而易见的是,随着标准差的增加,T检验的效果变差了。

那么当标准差取0.10时,随着抽样样本数的增加,T检验判断正确的频率会是怎么样的呢?请看如下代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
def plot2(diffs):
df = pd.DataFrame()
for diff in diffs:
x = list(range(3, 60, 3))
y = [norm_t(loc1 = 1, loc2 =1.1, scale1 = 0.10, scale2 = 0.10, n = i) for i in x]
z = [diff for i in x]
df_diff = pd.DataFrame([x, y, z]).T
df_diff.columns = ["samle number", "sensitivity", "difference"]
if df.shape == (0, 0):
df = df_diff
else:
df = df.append(df_diff)
sns.pointplot(x = "samle number", y = "sensitivity", hue = "difference", data = df)
plot2([0.5, 0.8, 1.2, 2.0])

通过上图,我们发现,即使标准差达到0.10,当样本数量达到30以上时,T检验的效果就非常好了。

那么如果两个总体没有差异,T检验得出有差异的错误结论的情况是什么样子的呢?我们构建两个均值为1容量为1000的总体,每次随机抽取3个样本,在不同标准差及样本数目的情况下,考察T检验得出正确结论的频率。请看代码:

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
def norm_t2(loc, scale1, scale2, n):
normSpace1 = stats.norm.rvs(loc = loc, scale = scale1, size = 1000)
normSpace2 = stats.norm.rvs(loc = loc, scale = scale2, size = 1000)
count = 0
for i in range(1000):
sample1 = np.random.choice(normSpace1, n)
sample2 = np.random.choice(normSpace2, n)
t, p = stats.ttest_ind(sample1, sample2)
if p &gt; 0.05:
count = count + 1
return count / 1000
def plot3(stds):
stds = [0.1, 0.5, 1]
df = pd.DataFrame()
for std in stds:
x = list(range(3, 60, 3))
y = [norm_t2(loc = 1, scale1 = std , scale2 = std, n = i) for i in x]
z = [std for i in x]
df_std = pd.DataFrame([x, y, z]).T
df_std.columns = ["samle number", "specificity", "std"]
if df.shape == (0, 0):
df = df_std
else:
df = df.append(df_std)
sns.pointplot(x = "samle number", y = "specificity", hue = "std", data = df)
plt.ylim(0, 1.2)
plot3([0.1, 0.5, 1])

通过上图,我们惊讶地发现,当两个总体没有差异时,不管标准差是多少和取样数目是多少,T检验都有非常好的表现,即得到差异不显著的的结论。

这里构建的总体都是正太总体,其实对于其他分布的总体,结论是一样的,这里就不展示了。

通过上面的探索,我们可以得到如下结论: 1. 对于两个未知总体,通过T检验考察均值是否有显著差异,如果得出结论是差异不显著,那么进一步分析这些数据的标准差是否太大了,考虑是否增加抽样样本数做进一步分析。 2. 对于两个未知总体,通过T检验考察均值是否有显著差异,如果得出结论是差异显著,那么请相信它吧!

如果差异不显著,我们通过增大样本数量,使得差异显著;如果差异显著,那就是一个好结果嘛! 哈哈,T检验是实验生物学家的利器啊!

FASTA

FASTA文件是DNA/RNA序列文件。第一行是由大于符号>打头的任意文字说明,主要为标记序列用。从第二行开始是序列本身,标准核苷酸符号或氨基酸单字母符号。通常核苷酸符号大小写均可,而氨基酸一般用大写字母。文件中和每一行都不要超过80个字符(通常60个字符)。

1
2
>sequence info
GATGGGATTGGGGTTTTCCCCTCCCATGTGCTCAAGACTGGCGCTAAAAGTTTTGAGCTTCTCAAAAGTC

FASTQ

FASTQ格式的序列一般都包含有四行,第一行由’@’开始,后面跟着序列的描述信息,这点跟FASTA格式是一样的。第二行是序列。第三行由’+’开始,后面也可以跟着序列的描述信息。第四行是第二行序列的质量评价,字符数跟第二行的序列是相等的。

1
2
3
4
@SEQ_ID
GATTTGGGGTTCAAAGCAGTATCGATCAAATAGTAAATCCATTTGTTCAACTCACAGTTT
+
!”*((((***+))%%%++)(%%%%).1***-+*”))**55CCF>>>>>>CCCCCCC65

BED

BED文件是一种基因组注释文件,或者叫做基因组坐标文件。每行表示一段序列,只不过不是ATCG碱基,而是它们在基因组上的坐标。每一行有由tab分割的多列,BED行有三个必须的列和九个额外可选的列。 每行的数据格式要求一致。 3个必须列:

  1. chrom,染色体或scafflold 的名字
  2. chromStart,序列在染色体或scaffold的起始位置
  3. chromEnd,序列在染色体或scaffold的结束位置

9个可选列: 4. name 指定BED行的名字,这个名字标签会展示在基因组浏览器中的bed行的左侧。 5. score 0到1000的分值, 可以设置为'0'。 6. strand 定义链的方向,”+” 或者”-” 7. thickStart 起始位置(The starting position at which the feature is drawn thickly)(例如,基因起始编码位置) 8. thickEnd 终止位置(The ending position at which the feature is drawn thickly)(例如:基因终止编码位置) 9. itemRGB 是一个RGB值的形式, R, G, B (eg. 255, 0, 0), 如果itemRgb设置为’On”, 这个RBG值将决定数据的显示的颜色。可以设置为'0,0,0' 10. blockCount BED行中的block数目,也就是外显子数目。 11. blockSize 用逗号分割的外显子的大小, 这个item的数目对应于BlockCount的数目。 12. blockStarts 用逗号分割的列表, 所有外显子的起始位置,数目也与blockCount数目对应。

GFF/GTF

GFF/GTF文件和BED文件一样,也是一种基因组注释文件。每行表示一段序列,有9个tab分割的列共同描述这段序列的信息。 9个列定义如下: 1. seq_id:序列的编号,一般为chr或者scanfold编号; 2. source: 注释的来源,一般为数据库或者注释的机构,如果未知,则用点“.”代替; 3. type: 注释信息的类型,比如Gene、cDNA、mRNA、CDS等 4. start:该基因或转录本在参考序列上的起始位置; 5. end: 该基因或转录本在参考序列上的终止位置; 6. score: 得分,数字,是注释信息可能性的说明,可以是序列相似性比对时的E-values值或者基因预测是的P-values值,“.”表示为空; 7. strand: 该基因或转录本位于参考序列的正链(+)或负链(-)上; 8. phase: 仅对注释类型为“CDS”有效,表示起始编码的位置,有效值为0、1、2(对于编码蛋白质的CDS来说,本列指定下一个密码子开始的位置。每3个核苷酸翻译一个氨基酸,从0开始,CDS的起始位置,除以3,余数就是这个值,,表示到达下一个密码子需要跳过的碱基个数。该编码区第一个密码子的位置,取值0,1,2。0表示该编码框的第一个密码子第一个碱基位于其5'末端;1表示该编码框的第一个密码子的第一个碱基位于该编码区外;2表示该编码框的第一个密码子的第一、二个碱基位于该编码区外;如果Feature为CDS时,必须指明具体值。); 9. attributes:一个包含众多属性的列表,格式为“标签=值”(tag=value),标签与值之间以空格分开,且每个特征之后都要有分号;(包括最后一个特征),其内容必须包括gene_id和transcript_id。以多个键值对组成的注释信息描述,键与值之间用"=",不同的键值用";"。

有空继续添加...

读本科那会儿,写博客、写日志、弄QQ空间的人很多。我的一位大学同学,坚持写了4年博客,我也看了4年。这期间,微博流行起来了,再后来又是微信,进入了移动互联网的时代。大家喜欢阅读短小的文字,喜欢在手机上阅读,博客似乎被人遗忘了。大学毕业后,这位同学的博客停止更新了。有时候,做一件事情是因为身边有人在做同样的事情,我写博客的源头或许是这位同学做了这样的事情。

2013年6月21日,注册了域名BioChen.com,用WordPress搭建了博客。我不是一个善于写作的人,博客以分享自己学习和工作的经验和感悟为主。跟预想的一样,最开始的一两年没什么访问量。随着文章越来越多,访问量慢慢地有所提升,到现在日访问量已经过千。一些网友的也会给我发邮件,寻求进一步的帮助;我也收到过几次网友的打赏。这些让我觉得写博客是有意义的,能帮助到一些人。

2020年3月17日,我决定放弃WordPress,改用Hexo;并把博客迁移到云主机,启用新的网址www.biochen.com/blog。原博客网址blog.biochen.com还会保留一段时间。WordPress越来越臃肿,它的编辑器我也不喜欢;Hexo支持markdown,可以生成静态网站;这或许是我从WordPress转向Hexo的理由吧。博客中有些文章已经过时了,我也想借迁移博客的机会,重新整理博客,也是重新整理自己。

写作即表达。感谢这个互联网时代,让我这样的小人物,也可以自由表达自己。