MySQL Commands

Shorthand Key:

file name		flnm
database name		dbnm
table name		tblnm

SQL Commands

mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A        to log on to the public mysql server
%			                                        wildcard
!=			                                        not equal to
quit			                                        takes you out of sql
use db nm		                                        changes databases
Ctrl + C		                                        cancels command in progress
show tables;	                                                list all tables in database you are in
desc tblnm;	                                                lists fields & their types
select * from tblnm limit 2;		                        selects all fields of 2 records from the table
select distinct (fld nm) from tblnm;		                displays distinct values of a particular field
select min(fld nm) from tblnm;		                displays lowest value in particular field
select max(fld nm) from tblnm;		                displays highest value in particular field
select * from tblnm where field name = ‘value’ limit 1;	displays all data from one row of the table that has the value of interest in the particular field
select count(*) from tblnm;		                        number of rows in the table
select count(*) from tblnm where fld nm = ‘value’;		number of rows in the table that contain the value of interest in the particular field
show index from tblnm;		                          checks the indexes for the primary table
select count(distinct(fld nm)) from tblnm;		        counts # of records w/ distinct value in field
select (fld nm) from tblnm limit 2;		                outputs only the values from the field specified
delete from tblnm where fld nm = “value”;		        deletes a row with specified value in the field of interest
insert into tblnm values (‘value’,’value’,’value’)		inserts a new row with values in order of the fields
select database();		                                shows which db you are currently using
DELETE from tblnm where fldnm = '_____'; 		        deletes a row from a table
UPDATE tblnm SET fld nm = “value” WHERE<which records to update>  change a field of a record
DROP table tblnm		                                drop an entire table

A little more complicated:

Outputs all the rows where the field of interest from one table is equal to the field of interest of another table.

select tblA.fldnm, tblB.fldnm from tblA, tblB where tblA.fldnm = tblB.fldnm;

Ex.
select sgdGene.name, sgdToName.name from sgdGene, sgdToName where sgdGene.name = sgdToName.name;

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

add distinct to remove duplicates and add a where clause:

Select distinct tblA.fldnm, tblB.fldnm from tblA, tblB where tblA.fldnm = tblB.fldnm where tblB.fldnm2 like ‘%__’;

Ex.
select distinct sgdToPfam.value, proteome.pfamXref.pfamAC from sgdToPfam, proteome.pfamXref where sgdToPfam.value = proteome.pfamXref.pfamAC and proteome.pfamXref.swissDisplayId like '%YEAST';

Outputs all the rows where the field of interest from one table is equal to the field of interest of another table AND those that don’t have a value and where tblB has more values than tblA:

select tblB.fldnm, tblA.fldnm from tblB left join tblA on tblB.fldnm = tblA.fldnm;

Ex.
select sgdToName.name, sgdGene.name from sgdToName left join sgdGene on sgdToName.name=sgdGene.name;

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

right join, also adding distinct to remove duplicates and a where clause:

Select distinct tblB.fldnm, tblA.fldnm from tblB right join tblA on tblB.fldnm = tblA.fldnm where tblB.fldnm2 like ‘%___’;

Ex.
select distinct sgdToPfam.value, proteome.pfamXref.pfamAC from sgdToPfam right join proteome.pfamXref on sgdToPfam.value = proteome.pfamXref.pfamAC where proteome.pfamXref.swissDisplayId like '%YEAST';

Outputs all the rows where field of interest from one table doesn’t have a corresponding value in the field of interest in the other table:

select tblB.fldnm from tblB left join tblA on tblB.fldnm=tblA.fldnm where tblA.fldnm is null;

Ex.
select sgdToName.name from sgdToName left join sgdGene on sgdToName.name=sgdGene.name where sgdGene.name is null; 

http://www.linein.org/blog/2007/02/01/compare-two-mysql-tables/

right join, also adding distinct to remove duplicates, outputting both columns (null in the one) and a where clause:

Select distinct tblB.fldnm, tblA.fldnm from tblB right join tblA on tblB.fldnm = tblA.fldnm where tblB.fldnm2 like ‘%___’ and tblB.fldnm is null;

Ex.
select distinct sgdToPfam.value, proteome.pfamXref.pfamAC from sgdToPfam right join proteome.pfamXref on sgdToPfam.value = proteome.pfamXref.pfamAC where proteome.pfamXref.swissDisplayId like '%YEAST' and sgdToPfam.value is null;

Can set shorthand for db.table:

select distinct(lsp.protId) from sp090821.extDb ed, sp090821.extDbRef edr, hg18.lsSnpPdb lsp where edr.extDb=ed.id and ed.val="PDB" and edr.acc = "P04637" and lsp.pdbId=edr.extAcc1;

• lsp is shorthand for hg18.lsSnpPdb (db.table)
• edr is shorthand for sp090821.extDbRef (db.table)
• ed is shorthand for sp090821.extDb (db.table)

useful in complicated searches...in all long hand would look like:

select distinct(hg18.lsSnpPdb.protId) from sp090821.extDb, sp090821.extDbRef, hg18.lsSnpPdb where sp090821.extDbRef.extDb=sp090821.extDb.id and sp090821.extDb.val="PDB" and sp090821.extDbRef.acc = "P04637" and hg18.lsSnpPdb.pdbId=sp090821.extDbRef.extAcc1;