Relational Databases for Biologists
Session 3: Building and modifying a database with SQL
Goals: Generate SQL to create tables and input data. Use SQL to query, modify, and delete data in your database.
See /education/bioinfo2005/db4bio/ for the course page.
See http://www.mysql.com/documentation/ for mySQL documentation.
See http://www.mysql.com/doc/en/Tutorial.html for an SQL tutorial.
1. Login to Hebrides and go to your home directory with the commands:
ssh hebrides.wi.mit.edu –l YOURUSERNAME
cd (cd = “change directory” in Unix)
Since you’ll be in your home directory, any data or SQL files located there can be piped/imported/loaded into MySQL.
Log in to your new MySQL database (named after your last name) on hebrides:
mysql –u username –D username_db -p
(replace username_db with your TRUE username, e.g. guest1_db)
2. Manually create a new table by typing the following at the mysql prompt:
CREATE TABLE Targets
gbId VARCHAR(20) NOT NULL,
affyId VARCHAR(20) NOT NULL,
PRIMARY KEY (affyId)
Notice the use of two attributes to represent the primary key.
3. Perform the following command from the shell prompt (not MySQL prompt) to copy an SQL script to your home directory:
cp /home/latek/create.sql .
(that’s a necessary period preceeded by a space)
Automatically create the rest of the db4bio tables by loading the create.sql script, now located in your home directory:
mysql > SOURCE create.sql
4. Check that your tables were created as you wanted:
describe table_name; (replacing table_name with each table in turn)
6. Load data into a table
INSERT INTO Data VALUES(“1000_at”,”my_experiment”,156);
7. Use SELECT on this table to check that your data was entered properly. Then modify the data:
UPDATE Data SET level=358;
SELECT * FROM DATA;
8. Remove a table
DROP TABLE Targets;
Notice the difference?
9. Remove data from your Data table:
DELETE FROM Data WHERE affyId=”1000_at”;
SELECT * FROM Data WHERE affyId=’1000_at”;