Relational Databases for Biologists
Session 1: Data Conceptualization and
Database Design
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