Transcript Class #2: Introduction to MySQL (Continued)

Class #2: Introduction to
MySQL (Continued)
•
•
•
•
•
•
Where clause (continued)
Aggregate Functions
Creating users
Backing up your database
Indexes (if time)
Importing large datasets (if time)
• Make sure you receive
Class notes
Exercise handout
Where Clause
• How do you think we can limit the results
from the last query to just gene
“TNFRSF18”?
• Remember the query is:
SELECT gene.name, organism.species
FROM gene, organism
WHERE gene.organism_id=organism.organism_id;
Where Clause (Continued)
SELECT gene.name, organism.species
FROM gene, organism
WHERE
gene.organism_id=organism.organism_id
AND gene.name=‘TNFRSF18’;
Aggregate Functions
• What if we want to know how many genes
we have?
SELECT count(gene_id) FROM gene;
• What if we want to count the number of
records for each gene name?
SELECT name, COUNT(gene_id)
FROM gene
GROUP BY name;
Having Clause
• What if we want to know how many genes
(by name) have more than one record in our
gene table?
SELECT name,
COUNT(gene_id) AS copies
FROM gene
GROUP BY name
HAVING copies>1;
• Here we restricted the results based on
properties of the group.
Backing Up your Database
•
From the command line (not in MySQL):
mysqldump -uusername -p databasename > filename.sql
•
Example:
mysqldump -uroot trii > triibackup.sql
(our database has no password)
•
filename.sql will contain all of the SQL
statements necessary to first create all
the tables in your database and then
repopulate them.
Restoring your Database
• From the command line:
mysql -uusername -ppassword databasename < filename.sql
• Example:
mysql -uroot trii < triibackup.sql
• Notice you must have already
recreated the database in MySQL
CREATE DATABASE trii;
Inserting Very Large Datasets
into your Database
• You must have a tab-delimited text
file (each field in a row is separated
with a tab) that contains only the
rows from one table.
LOAD DATA LOCAL INFILE ‘filename’
INTO TABLE tablename (col1name,
col2name,...);
Inserting Very Large Datasets
into your Database (Continued)
• You can create a file like this with:
SELECT col1name, col2name FROM
tablename INTO OUTFILE ‘filename’;
Example:
SELECT * FROM organism INTO
OUTFILE '/tmp/organism.txt';
Creating Users/Granting
Privileges
GRANT ALL ON trii.*
TO ‘guest’@’localhost’
IDENTIFIED BY ‘guestpwd’;
•
•
•
•
•
ALL - privilege type
trii.* - all tables in trii database
guest - username
localhost - machine user can connect from
guestpwd - password
FLUSH PRIVILEGES;
Loading Data from MS Excel
• We will discuss 2
ways to do this
• In both cases
you should first
save your Excel
spreadsheet as a
tab-delimited
text file.
• Go to: File > Save
As
Check the File!!!
• Excel might have saved the end line characters
incorrectly. Check in vi. If the file has a bunch of
“^M” characters we have to replace them.
At the command prompt:
>vi gene.txt
In vi:
:0,$s/^M/^M/g
To get the ^M: hold control + v
OR
At the command prompt:
>perl -pi -e ‘s/^M/\n/g’ gene.txt
Loading Data from MS Excel
Option 1:
If your spreadsheet corresponds to a table in your
database exactly, then you can save the file as a tabdelimited text file in Excel and then use the SQL
command “LOAD DATA LOCAL INFILE…” from before.
LOAD DATA INFILE ‘gene.txt’
INTO TABLE gene (ensembl_gene_id,
organism_id, name, locuslink, chromosome,
chromo_start, chromo_end, description);
Loading Data from MS Excel
Option 2:
• Option 2 (most common)
If you need to put some Excel columns
into one table and some into another use
Perl to read in your tab-delimited text
file, process your data, and insert it into
the database.
Remember Perl is great at parsing files!
Indexes
• Add an index on a column (or columns) that
will be queried frequently (with an exact
match query). If something is a primary
key it is automatically indexed.
• To find out what indexes exist on a table:
SHOW INDEX FROM tablename;
Indexes
• Create an index on 1 column:
CREATE INDEX indexname ON
tablename (columnname);
• This will make searches on
borrower_id fast:
CREATE INDEX borrower_index ON
on_loan (borrower_id);
Indexes
• Create an index on 2 columns:
CREATE INDEX indexname ON tablename
(column1name, column2name);
• This will make queries on book_id fast, as well as
queries on book_id and copy_id but NOT copy_id
alone:
CREATE INDEX book_index ON on_loan
(book_id, copy_id);