*** sqlite 3 rls.db , result table

sqlite3 on rls.db

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.