Relational Databases for Biologists

 

Session 1: Data Conceptualization and Database Design

 

Exercise: Accessing MySQL and viewing table content

 

Goals: Learn how to access a MySQL database and look at table structure and content. From your Hebrides.wi.mit.edu account, access a MySQL database.

 

See /education/bioinfo2005/db4bio/ for course page

See http://www.mysql.com/documentation                                         for MySQL documentation

 

3. To access My SQL on Hebrides, use the command:

                 

                  mysql –u latek –p – D db4bio

 

with the following options:

                  -u             username (replace with your MySQL username –[not necessarily your Hebrides username])

                  -p             password (you will be prompted for your password)

                  -D            database (This takes you directly to the database we’ll be using)

 

5. Find out what databases are on the system:

 

                  mysql > show databases;

 

                  Yours should look similar, but not exactly like this:

 

| Database      |

+---------------+

| bell          |

| boyer         |

| casillo       |

| cheung        |

| db4bio        |

| groop         |

 

6. To access one of these databases:

 

                  mysql > use database;

 

where “database” is replaced with the name of the one you’d like to access. In this case, enter the database “db4bio”.

 

7. To get a list of tables in this database:

 

                  mysql > show tables;

 

                  Yours should look similar to this:

 

+------------------+

| Tables_in_db4bio |

+------------------+

| Data             |

| Descriptions     |

| GO_Descr         |

| LocusDescr       |

| LocusLinks       |

| Ontologies       |

| RefSeqs          |

| Sources          |

| Targets          |

| UniDescr         |

| UniSeqs          |

| Unigenes         |

| eya              |

+------------------+

 

 

8. To get a description of data fields and types in a table:

 

                  mysql > describe table;

 

mysql> describe Data;

+--------+-------------+------+-----+---------+-------+

| Field  | Type        | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| affyId | varchar(30) |      | PRI |         |       |

| exptId | varchar(10) |      | PRI |         |       |

| level  | int(11)     |      |     | 0       |       |

+--------+-------------+------+-----+---------+-------+

3 rows in set (0.03 sec)

 

 

where “table” is replaced with the one you’d like to access.


 

9. To look at some of the data in this table, use a general “SELECT” command. Since these tables are large, you’ll want to limit the number of rows (tuples) in your query to 5 or so:

 

                  mysql > SELECT * from ________ LIMIT 5;

 

mysql> select * from Data LIMIT 5;

+-----------------+----------+-------+

| affyId          | exptId   | level |

+-----------------+----------+-------+

| AFFX-MurIL2_at  | hs-cer-1 |    20 |

| AFFX-MurIL10_at | hs-cer-1 |     8 |

| AFFX-MurIL4_at  | hs-cer-1 |    77 |

| AFFX-MurFAS_at  | hs-cer-1 |    30 |

| AFFX-BioB-5_at  | hs-cer-1 |   258 |

+-----------------+----------+-------+

5 rows in set (0.04 sec)

 

 

10. [Optional] Create a file containing the query and use Unix redirection to save the output of the query into another file.

                  a. Exit from MySQL:  quit

                  b. Create a directory (“dbclass”) and switch into it:  mkdir dbclass; cd dbclass;

                  c. Create a text file called query1.sql

                                    To do this with pico, a simple text editor, type             pico query1.sql

Type the query from step 9 in the editor and save it with the ^x (Control-x) command.

 

mysql> select * from Data LIMIT 5;

 

d. Back in the shell, to your MySQL login command, add the SQL file (query1.sql) as input and the file query1.out as output all on one line.

 

                  mysql –u username –p –D db4bio < query1.sql > query1.out

e. View the outputfile:                 more query1.out

 

affyId  exptId  level

AFFX-MurIL2_at  hs-cer-1        20

AFFX-MurIL10_at hs-cer-1        8

AFFX-MurIL4_at  hs-cer-1        77

AFFX-MurFAS_at  hs-cer-1        30

AFFX-BioB-5_at  hs-cer-1        258