osX mysql

AppleJack

mysql-5.7.18-macos10.12-x86_64.dmg

ucscDb in R

ucscDb = dbConnect(MySQL(), user="genome", host="genome-mysql.cse.ucsc.edu")
result = dbGetQuery(ucscDb, "show databases;")
dbDisconnect(ucscDb)
result

mysql tips

MYSQL tips "mysql.txt" file

show tables like "h%";

select * form someTable into outfile "/tmp/tmpfile";

create temporary table tmptab select distinct id1 from sampleTab1 UNION ALL
select distinct id2 from sampleTab2;

grant ALL on homo_sapiens_core_17_33.* to hqin@localhost;

SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)

MID(str,pos,len)
#Returns a substring len characters long from string str, starting at position pos.
#The variant form that uses FROM is SQL-92 syntax:

mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'

mysqldump test name --no-data --no-create-db > tmp.dump
mysqlimport -u root -h shanghai hong_database *.txt.table


 /* try left, inner, outer join to see what's missing */
mysql>  create temporary table bader2gu
    ->  select orf, Name1
    ->  from   curagenOrf2name left join Ks_Ka_Yeast_Ca
    ->         on curagenOrf2name.orf = Ks_Ka_Yeast_Ca.Name1;
Query OK, 6268 rows affected (54.27 sec)
Records: 6268  Duplicates: 0  Warnings: 0

mysql>  select * from bader2gu where Name1 is NULL;
/* return 4313 rows */

mysql>  select * from bader2gu where Name1 is not NULL;
/* return 1955 rows.  Note, one record is missing probably
due to different annotations bw curagen and the public release from SGD
*/