1 / 18

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.

tamira
Download Presentation

Class #2: Introduction to MySQL (Continued)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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;

  3. Where Clause (Continued) • SELECT gene.name, organism.species • FROM gene, organism • WHERE gene.organism_id=organism.organism_id • AND gene.name=‘TNFRSF18’;

  4. 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;

  5. 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.

  6. 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.

  7. Restoring your Database • From the command line: • mysql -uusername -ppassworddatabasename < filename.sql • Example: • mysql -uroot trii < triibackup.sql • Notice you must have already recreated the database in MySQL • CREATE DATABASE trii;

  8. 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,...);

  9. 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';

  10. 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;

  11. 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

  12. 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 • OR • At the command prompt: • >perl -pi -e ‘s/^M/\n/g’ gene.txt • To get the ^M: hold control + v

  13. 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 tab-delimited 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);

  14. 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!

  15. 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;

  16. 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);

  17. 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);

More Related