Style Cleanup Required
This article does not follow the style standards in the Wiki Guide. More info...


Content Cleanup Required
This article should be cleaned-up to follow the content standards in the Wiki Guide. More info...


A quick start Guide

  • pages under construction

please visit regularly to see the updates

Appologies for the slow generation of this page.

I'm a bit stuck at the moment, I've got a lot of projects to finish for my masters and this has now come to a halt for a little while.

Hopefully I'll be able to get back to it in a month or so.

When this page becomes live I will put in a link to a forum page that I will "inhabit"

First off, let me apologise for this page, it is my first in the wiki, so it probably doesn't meet any of the ubuntu wiki standards.

To Business

so you have come to this page as you hope to get a quick start guide to the syntax of MySQL

This is good, as that is what I intend to give!

Why did I start this page?

When I first started using MySQL I obviously needed to creates tables and all sorts of stuff, I did this by using public data information (which I will point you too). I had persistently been put off delving into MySQL as I didn't have the time, or inclination, to create tables that where going to be a catalogue of my CD / DVD / Book collection.

So as mentioned earlier, this guide will aim to do the following...

* Help you create your first tables

  • giving syntax for the tables and how to populate them with data
  • pointers for where to get a large quantity of data, all of it free Wink ;-)

* Create select statement

  • demonstrate how you can drill down into your new data.
  • cross referencing and joining tables.

Pre Requisites

So these are the things you will obviously require prior to getting started.


avilable in the repo's, there are numerous guides on installing MySQL, and the ones on the ubuntu wiki are preety hot stuff, check them out, here are the links.

MYSQL5FromSource I would currently recommend version 5 (or even 6), I have version 4 at a location and some of these commands may not work as you would expect (eg. a search with an inner sub search that contains an inner subsearch... I know it sounds confusing but I'll get to this later, so don't worry about it for now). I installed from source, and in fact it was so easy I have started to install other stuff from source now also, and it isn't as scary as you may at first expect.

Also on the wiki there are lots of other usefull informatin, try this search

MySQL Users

I am also asuming that you have set up a new MySQL admin user and new non-admin user also, this is generaly good practice, however for the majority of this tutorial due to the nature of what I will be asking you to do you will need to be logged into the MySQL monitor via an admin login.

unserstandably that is about it for the pre-requisites page, If you have an instal of MySQL and have organised yourself with admin / user / password combination you should be set to go.

Finding data for your tables.

Before we can start really understanding the MySQL syntax we need to find data to put into our tables.

Here are the links to various site. The NCBI Home page Is one of the largest collections of data that there is, what makes it so large is the nature of the data (information on all the human genetic code, mouse, ecole, various yeasts, and a whole load of other medically interesting creatures that grunt and smell!). It is also a good site to demonstrate the power of MySQL as all of their data is stored in MySQL tables.

However to be really usefull you want to get hold of some of the flat files they create, so you'll want to look at their ftp site here and download the gene_info_gz file, the other files are also of interest but this is the "biggie" - later on you will unzip it and import it into a nice shiny new MySQL table, then you will use a MySQL function to count the lines, and notice that there are over 5 million of them - all stored in a tab separated text file.

The Mouse Genome Institute A repository of the current mouse genome data (most of which is replicated within the NCBI data tables, but the MGI have it organised in a different manner, which is better for our purposes. This is their ftp site data page you'll notice that this is a regular file that is updated every week... give or take. This will be important later on!

Ensembl this is the european version of the NCBI, interestingly you can get direct access to their MySQL tables (unlike the NCBI where you will need to download their special tools for direct access), instructions to do this are here, I will come back to this later on.

So you have probably guessed that I am involved in no small way in bioinformatics research, if so you are correct.

Creating and populating your MySQL data tables

Create your first table

So you now now where to get a bunch of free data to send directly onto your MySQL server, but wait you need a table to put the data into!

so lets create a nice shiny new table.... but wait you need a database to hold the table! OK so I'm getting a little carried away Wink ;) but I'm sure you get my point!

so you will need to login with the following code.

mysql -u userName -p

You will now be connected to the MySQL server that you have installed, it has asked you for your password, and you get the standard < mysql > type prompt.

Now if as I have suggested you have downloaded the gene_data.gz file, now is the moment to unzip it to a directory of your choice, I'm going to choose the /tmp directory, and I'll name the file gene_info.txt, as that way I can do less typing! Bear in mind however that if you do the same you will loose your file when you turn off your pc. Also you need to make sure you have read access to the file, so again the /tmp folder is good to ensure that you do. As I mentioned earlier this is a monster of a flat file, but we need to ensure that our table column names in same way match up to the data that this file contains, if you aren't willing to trust me you can check out the first line(s) with the following code in a terminal..

head /tmp/gene_info.txt

Ok so after doing this you will have the first 10 lines of the file, the first line tells us what we want to know

#Format: tax_id GeneID Symbol LocusTag Synonyms dbXrefs chromosome map_location description type_of_gene Symbol_from_nomenclature_authority Full_name_from_nomenclature_authority Nomenclature_status Other_designations Modification_date (tab is used as a separator, pound sign - start of a comment)

so now we now what to call our table columns. The next code snipit will create a database, and then create a table that has the desired structure.

create database NCBI;
use NCBI;
create table NCBI (
EntrezGeneID varchar(20) default NULL,
symbol varchar(20) default NULL,
full_nameHGNC varchar(250) default NULL,
NCBI_Tax_ID varchar(20) default NULL,
UniProt_ID varchar(20) default NULL,
EMBL_ID varchar(20) default NULL);

Now if you are observant you will have noticed a few things here, firstly the column heading I've used are not in sync with the number of columns in the file - I've only actually used 6 out of the available 15. I've done this purposely to enable the demonstration of some of the clever stuff you can do with MySQL

When a line terminates with a ';' the MySQL server will interpret everything upto it as a single command. You could simply copy and paste the above lines into the monitor and you shouldn't get any errors. If you do, tell me, as I've obviously made a boob in my code, or your version of mysql may be different and I need to put in a note for other to realise this.

A quick explaination of the commands. The first 2 should be obvious, creation of a new database called NCBI and then "use" this database hooks us into that particular database. We could of course use fully qualified database.table names, but I'll come to that later also in the [#AdvancedQueries|Creating and understanding more advanced queries] section.

For now the basic syntax for creating a table in MySQL is as follows

create table tableName (columnName dataType(length) specialColumnStuff specialTableStuff;

So hopefull this is also fairly obvious. tableName - is the name you have given to your table (again this could have been a fully qualified name in which case it would have looked < databaseName.tableName >

columnName - again the name you have given to your column (from above this would be EntrezGeneID, symbol, full_nameHGNC, NCBI_Tax_ID, UniProt_ID, or EMBL_ID).

dataType - this telly the server what type of data it can accept for this column, the options are mostly self explanatory such as INT, varchar (for "variable character" I suppose?), etc, there are lots more numeric and "string" types (such as BLOB (binary long object), longText) and date types this page is the place to look in the online docs for the relevant pages.

specialStuff - I've included everything else in this note! - maybe not very sensible but here goes... the specialStuff that you can do with a column relates to things such as a column being a Key (primary or otherwise) foreignKey references, again I suggest that you have a look at the relevant pages, you should search for terms such as <Column Flags>, <Partitioning> and <Storage Engines>. It is a little beyond the scope of this "beginers guide" to delve into these things too deeply, better still the text Mysql in a nutShel is where I learnt most of my stuff from.

Now you also need to now how to add and remove columns to a table, so I'll show you very quickly.


populate the table with data

The follwing code will create a table in your newly made database.

{{{create table gene_info (

  • EntrezGeneID varchar(20) default NULL,

LocalID varchar(20) default NULL, nameHGNC varchar(250) default NULL, NCBI_Tax_ID varchar(20) default NULL, UniProt_ID varchar(20) default NULL, EMBL_ID varchar(20) default NUL)L; }}}

Creating queries to get informatin from your database

Creating and understanding more advanced queries

I guess really I am now moving past the idea of this being a beginner guide, but as I'm getting a little carried away I would kindly ask that you stay with me on this one!


sample code




web references

mysqlresources This is a page started by the guy who wrote the excelent book <Mysql in a nutShel>

The Home page Obviously I can't miss out the main MySQL home page

The Documentation pages Links to all the versions documentation pages.

Normalising your tables Read this often whenever you start a new database project, as a reminder

MySQL~QuickStartGuide (last edited 2017-09-06 21:17:42 by ckimes)