MySQL Commands

Shorthand Key:

file name		flnm
database name		dbnm
table name		tblnm

SQL Commands

mysql --user=genome --host=genome-mysql.soe.ucsc.edu -A        to log on to the public mysql server
%			                                        wildcard
!=			                                        not equal to
quit			                                        takes you out of sql
use dbnm		                                        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

Create a duplicate table

If you need to edit a major table, like anything in hgcentral, and you're concerned about possibly making a catastrophic mistake, you can create an exact duplicate of the table for testing/backup purposes:

CREATE TABLE [newtable] AS SELECT * FROM [oldtable]

Just don't forget to clean up your duplicates once everything is working as expected!

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;

Checking and clearing queries gumming up the Public Mysql Server

• Use show processlist; to see the processes running on the public server:
  

Example (you should probably output to a file to keep a record):

mysql --user=genome --host=genome-mysql.soe.ucsc.edu -ANe 'show full processlist;' | less

• Check the processes that aren't "Sleep" but that are "Sending Data" and look for onese where the "Time" column, is very long, with seconds in the many thousands.
• When things are gummed up, you'll see some poor innocent processes with status "Waiting for Table Flush", they may mention the table in question that is locked by the culprit query.
• Find the culprit query, and make a note of the web address or IP and the query it is asking. Also note the ID number.
• Go to the Mysql server and put in a kill command for that ID:

Example:

mysql --user=genome --host=genome-mysql.soe.ucsc.edu -A

kill 19084487;

show processlist;

• You may have to kill several queries from the offending party, they probably sent it several times waiting for a response. Keep returning to the show processlist until the innocent "Waiting for a Table Flush" queries finally get a chance to execute.
• Now users should probably not see issues, you can run a test query on the table to check.

External MySQL Resources

There are O'Reilly books available through the UCSC Library system including one called the MySQL Cookbook.

Here are excerpts from chapters Clay Fischer found useful with commentary:

Excerpt Title: Creating a View to Simplify Using a Summary

The 'Generating Summaries' chapter starts off simple and really quickly you are building complex queries that generate some cool stats. This example would be a pain to have generated by exporting data, analyzing, and then summarizing, but it's a pretty quick SQL query which yields an immediate summary.

Excerpt Title: Using Expressions for Sorting

The linked example above shows how we can perform math on a column's rows. This could be super useful when we deal with huge numbers that human minds have trouble comprehending and comparing, and could put them into perspective. The rest of that chapter goes through all sorts of useful sorting tricks, which I have so far been relying on exporting to bash and then using the 'sort' command with keys specified.