MySQL - for Beginners

An important MySQL web site is: http://www.mysql.com/documentation/index.html
The exercise below contains different MySQL commands which are quite simple and many variations can be used with
different options. To see all the options you can go to the MySQL web site and use the keyword search (in another browser).

Some of the files that you will need for this exercise are rather large therefore make your own directory like
/home/projects/your_user_name/mysql_tut/ and complete the following tour from that directory.

Copy some files:
cp /home/projects/tnp/mysql_tut/* .

Contents:

   Using MySQL within a mysql shell
      Create and show a table
      Describe the table definitions
      Insert data into a table
      Select data
         Where
         Between
         Group
         wildcards
      Update data
      Alter table
      Index a table
   Using MySQL commands in a shell script
   Using MySQL commands in a perl script
      Connect
      Prepare
      Execute
   Running a few perl scripts
      Exercises
         Exercise 1
         Exercise 2
         Exercise 3
         Exercise 4
   Using MySQL commands in a python script

Using MySQL within a mysql shell

Open an MySQL shell

mysql

This should work as you have a ".my.cnf" in your root directory. Otherwise you can type mysql --password and you will be prompted for the password (which can not be changed).
All MySQL commands ends with a semi-colon ";".
Reserved MySQL words are written in CAPITAL letters in this exercise, but lower case letters will just as well. You can not use variable names with a dot "." as this is also a reserved MySQL character.

USE user_private , where user is your normal user name.

Create and show a table

CREATE TABLE foo (firstname varchar(30) NOT NULL,
age INT NULL,
city TEXT)
;

SHOW tables;

There are many different column types like "INT, VARCHAR, TEXT". The different types can be found at the MySQL web
site by searching for "column types".

Describe the table definitions

DESCRIBE foo;

Insert data into a table

INSERT INTO foo (firstname, age, city) VALUES ('palle','30','Copenhagen');
INSERT INTO foo (firstname, age) VALUES ('ib','20');
INSERT INTO foo (firstname) VALUES ('per');
INSERT INTO foo (firstname, age, city) VALUES ('irene','40','London');

SELECT * FROM foo;

REPLACE INTO foo (firstname, age, city) VALUES ('irene','40','Thy');

SELECT * FROM foo;

A new firstname 'irene' has been added to the table just like the INSERT command. The effect would only have been different
if 'firstname' was indexed as unique. In that case 'London' would be replaced with 'Thy'.

Select data

SELECT * FROM foo;
(select everything)

SELECT firstname, city FROM foo;
(select a subset)

SELECT b.age, b.firstname FROM foo AS b;
(uses 'b' as a table alias)

Where

SELECT * FROM foo WHERE age > 20;

Between

SELECT * FROM foo WHERE age BETWEEN 20 AND 30;

Group

SELECT * FROM foo WHERE age BETWEEN 20 AND 30 GROUP BY city DESC;

wildcards

SELECT * FROM foo WHERE firstname LIKE 'pa%';
SELECT * FROM foo WHERE firstname LIKE '%e%';
SELECT * FROM fooWHERE firstname LIKE 'i_';

The % sign is equivalent to the unix *
The _ sign matches exactly one character

Update data

UPDATE foo SET city='Odense' WHERE firstname='ib';

SELECT * FROM foo;

Alter table

ALTER TABLE foo RENAME foo2;

ALTER TABLE foo2 ADD height float(6,2);

DESCRIBE foo2;

ALTER TABLE foo2 DROP height;

ALTER TABLE foo2 RENAME foo;

Index a table

CREATE INDEX firstname_index ON foo (firstname);

SHOW INDEX FROM foo;

DROP INDEX firstname_index ON foo;

Using MySQL commands in a shell script

The script below is also in the file "foo.csh"

#! /bin/csh -f
set user="tnp"

set db="$user"_private
set mysql="mysql --user=$user";

cat << ENDOFSQL | $mysql |
USE $db;
SELECT * FROM foo;

ENDOFSQL

Another type of shell script can be written like:

echo " USE tnp_private; select * from foo; " | mysql --user=tnp

Using MySQL commands in a perl script

A database interface module exists for perl called DBI. There are three basic mysql steps
that are used in a perl-script.

Connect

$dbname            = 'DBI:mysql:$user_private:genome.cbs.dtu.dk';
$dbusername    = '$user';
$dbpassword    = 'abcdefg123';

$dbh = DBI->connect($dbname, $dbusername, $dbpassword) or die ("Connection to database failed.\n");

Prepare

$sql = "CREATE TABLE foo (firstname VARCHAR(30));"
$sth = $dbh->prepare($sql) or die ("Error in SQL: '$sql'\n");

Execute

$sth->execute() or die ("Error in SQL\n");

Running a few perl scripts

Use the existing perl scripts to make the tables that are needed for the last exercise where you combine data from 2 tables. These
tables are rather larger eg. each table contains approx. 50.000 rows. The two tables have one column in common and this is a
MUST when you build a relational database.

You can either edit the two scripts and put in your own username and password, which can be seen in the file ".my.cnf."
in your root directory. Alternatively the user and password can be given as input to the perl scripts using the options
-u and -p.

There is a help option for the two scripts.

signalp_mysql.pl -h
blastdb_mysql.pl -h

You can use the default values for 'Query database name' and 'table name'.

signalp_mysql.pl -i human_signalp.txt
blastdb_mysql.pl -i human_vs_mouse.txt

go to a mysql environment:

DESCRIBE sigp;

The column 'query_acc' in the table 'sigp' has already been indexed.

SHOW INDEX FROM sigp;

Try to remove the index.

DROP INDEX query_acc ON sigp;

SELECT * FROM sigp WHERE query_acc='IPI00179049.1';

SELECT * FROM myblastdb WHERE query_acc='IPI00179049.1';

Combine data from 2 tables:

SELECT a.query_acc, a.target_acc, b.cleavage_site FROM myblastdb AS a, sigp AS b
WHERE a.query_acc = b.query_acc AND a.target_acc != 'NULL' AND b.cleavage_site BETWEEN 15 AND 45
LIMIT 500;

notice the time ! (approx 20 sec.)

The MySQL command LIMIT is very usefull as it limits the output to a reduced number of rows. It can always be removed
when the MySQL syntax is correct and the output is as expected.

CREATE INDEX query_acc_index ON sigp (query_acc);
CREATE INDEX query_acc_index ON myblastdb (query_acc);

SELECT a.query_acc, a.target_acc, b.cleavage_site FROM myblastdb AS a, sigp AS b
WHERE a.query_acc = b.query_acc AND a.target_acc != 'NULL' AND b.cleavage_site BETWEEN 15 AND 45
LIMIT 500;

notice the time again !

EXPLAIN is a good command that can be used in combination with SELECT. It tells you how many combinations
MySQL is trying for your SELECT statement and in what order the search in performed. The total number of combinations is the product of the 'rows' column. If this number is too big you might want to index and/or refine your select statement.

EXPLAIN SELECT a.query_acc, a.target_acc, b.cleavage_site FROM myblastdb AS a, sigp AS b
WHERE a.query_acc = b.query_acc AND a.target_acc != 'NULL' AND b.cleavage_site BETWEEN 15 AND 45
LIMIT 500;

Exercises

Exercise 1

Try to update cleavage_site in the sigp table such that cleavage_site is increased by one, except
for those where cleavage_site is zero.

Exercise 2

List the 10 highest 'y_max' values from the table sigp.

Exercise 3

Remove the column 'perc_query_len' from the table myblastdb. (ALTER command)

Exercise 4

Drop the indexes on the sigp table and make an index composed of two columns 'query_acc' and 'query_db'.
(This might be useful when an entry eg firstname becomes unique only when combined with ex. lastname
and city etc.)

Using MySQL commands in a python script

An example of a python script foo.py. Change dbusername, dbname and dbpassword.

#!/usr/local/python/bin/python2.2
import MySQLdb

dbusername = "user"
dbname = 'user_private'
dbpassword = 'some_password'

# connect to the database
db = MySQLdb.Connect(db = dbname, user = dbusername, passwd = dbpassword)

#To perform a query, you first need a cursor, and then you can execute queries on it.
cursor = db.cursor()

# create the query
query = "SELECT * FROM foo"

# execute the query
cursor.execute(query)

# retrieve the result
results = cursor.fetchall()

for firstname, age, city in results:
print firstname, age, city