##########################
#
# MySQL/PHP Workshop - SQL Commands
# Day 2
#
##########################

# show databases
SHOW DATABASES;

# use a database
USE trii;

# show tables
SHOW TABLES; 

# where 
SELECT gene.ensembl_gene_id, organism.species FROM gene, organism 
WHERE gene.organism_id=organism.organism_id 
AND gene.name='TNFRSF18';

# aggregate functions
SELECT count(gene_id) FROM gene;

SELECT name, COUNT(gene_id)
FROM gene
GROUP BY name;

# having
SELECT name,
	COUNT(gene_id) AS copies
FROM gene
GROUP BY name
HAVING copies>1;

# from the command line (not in MySQL):
mysqldump -uroot trii > triibackup.sql

# delete entire database (be careful!)
DROP DATABASE trii;

# recreate database
CREATE DATABASE trii;

# from the command line (not in MySQL):
mysql -uroot trii < triibackup.sql

# select table data and store in tab delimited text file
SELECT * FROM gene INTO OUTFILE '/tmp/gene.txt';

# granting privileges
GRANT ALL ON trii.* TO 'guest'@'localhost' IDENTIFIED BY 'guestpwd'; 
FLUSH PRIVILEGES;

# delete all rows in gene 
DELETE FROM gene;

# check that the end line characters are correct
# 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
# to get the ^M:
# hold control + v

# make sure you put gene.txt into your current working directory
LOAD DATA LOCAL INFILE 'gene.txt' 
INTO TABLE gene (ensembl_gene_id, organism_id, name, locuslink, chromosome, chromo_start, chromo_end, description);

# use library
USE library;

# find out what indexes exist on a table
SHOW INDEX FROM on_loan;

# create an index on 1 column
# this will make searches on borrower_id fast
CREATE INDEX borrower_index  ON on_loan (borrower_id);

# create an index on 2 columns
# 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);