see https://www.tutorialspoint.com/sqlite/
.open rls.db
.databases
.tables
.separator ::
.headers on
.mode column
select distinct experiment from result_experiment limit 20;
.indices
.indices set
.width 5
select * from result limit 1;
/* The following select can take rls and its reference rls */
select experiments,set_name,set_strain,set_background,set_genotype,
set_lifespan_mean,ref_genotype,ref_lifespan_mean
from result limit 2;
CREATE TEMPORARY TABLE my_RLS_out AS
SELECT experiments,set_name,set_strain,set_background,set_genotype,
set_lifespan_mean,ref_genotype,ref_lifespan_mean
FROM result;
DROP TABLE my_RLS_out;
CREATE TEMPORARY TABLE my_RLS_out AS
SELECT experiments,set_name,set_strain,set_background,set_genotype,
set_lifespan_mean,ref_genotype,ref_lifespan_mean, ranksum_p
FROM result
WHERE ranksum_p < 0.05
;
select * from my_RLS_out limit 5;
select distinct set_genotype from my_RLS_out;
sqlite> .indices result
result_percent_change
result_pooled_by
result_ranksum_p
result_ref_background
result_ref_genotype
result_ref_locus_tag
result_ref_mating_type
result_ref_media
result_ref_name
result_ref_strain
result_ref_temperature
result_set_background
result_set_genotype
result_set_lifespan_mean
result_set_locus_tag
result_set_mating_type
result_set_media
result_set_name
result_set_strain
result_set_temperature
#output result table into csv
sqlite> .headers on
sqlite> .mode csv
sqlite> .output qintest.csv
sqlite> select * from result;
sqlite> .quit
emc313b02:data hqin$ ll
total 146712
-rw-r--r-- 1 hqin staff 8.5M Apr 30 23:31 qintest.csv
lrwxr-xr-x 1 hqin staff 15B Apr 30 20:07 rls.db -> rls_20160802.db
-rw------- 1 hqin staff 8.3M Aug 9 2016 rls_2016-08-02.csv
-rw------- 1 hqin staff 55M Aug 9 2016 rls_20160802.db
emc313b02:data hqin$ less qintest.csv
emc313b02:data hqin$ open qintest.csv
emc313b02:data hqin$
Note
Based on
http://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-to-mysql
Sqlite database is not easily convertable to mysql database.
Based on
http://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-to-mysql
Sqlite database is not easily convertable to mysql database.
