1 / 38

Introduction to SQL and MySQL

Tables Column Types and Properties Keys, Indexes, and AUTO_INCREMENT The mysql client Creating databases and tables Inserting records Selecting data Using conditionals. Using LIKE and NOT LIKE Sorting query results Limiting query results Updating data Deleting data Using functions.

majed
Télécharger la présentation

Introduction to SQL and MySQL

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. Tables Column Types and Properties Keys, Indexes, and AUTO_INCREMENT The mysql client Creating databases and tables Inserting records Selecting data Using conditionals Using LIKE and NOT LIKE Sorting query results Limiting query results Updating data Deleting data Using functions Introduction to SQL and MySQL BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  2. First a Word About CGI Programming • You should all be setup to run CGI code on binf now • Remember that your personal directory, your public_html, and your cgi-bin must be world readable • Remember that your cgi program must be world executable • chmod 755 myprogram.cgi public_html cgi-bin prog1 prog2 BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  3. A Motivational Story • How can we track and anlyze the outputs from a complex simulation system? • Use a RDMS BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  4. Relational Databases • Databases provide persistent storage and access to large amounts of data • something like a hash that survives after a program executes • lots of convenient methods for quickly accessing the data • Data is stored in Tables (usually many tables per database) • Each row is a record • Each column is a field An Example Table BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  5. Column Types and Properties • Each field in a table can hold data of a certain type • Examples: Column Type Example VARCHAR "A string up to 255 characters long" TEXT "A string up to 64K characters long" LONGTEXT "A string up to 4G characters long" INT 65473829 FLOAT 3.1417 DATE 2007-10-29 ENUM ('M' ,'F') DEFAULT 'F' • We can define a DEFAULT value for any column • If a column is designated as "NOT NULL" then we must provide a value for it when inserting data into the table BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  6. Keys, Indexes, and AUTO_INCREMENT • Tables can have keys, like hashes; keys are also called indexes • Unlike hashes, tables can have more than one index • allows quick access with any key • Each table has one primary key • must always have a value • that value must never change • that value must be unique for each record in the table • We often give the primary key the AUTO_INCREMENT property • every time a new record is inserted, the primary key is assigned the next highest integer • example: see the ID field in the sequence table BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  7. The mysql Client • The MySQL database server is always running on binf.gmu.edu • You interact with it my running the mysql client program: % mysql -u username -p [pwd] -h hostname database • username is your MySQL login name (same as binf login name) • pwd is your MySQL password • mysql will prompt you if not supplied (recommended!) • set to usernamelast4digitsofyourGnumber • same password as you log into the system with • hostname is the computer where the MySQL server is (eg, binf.gmu.edu) • You can access the MySQL server on binf from any Internet-connected computer that has the mysql client installed • mysql will prompt with ">" • mysql command always end with ";" • Each student has a database with the same name as your login name BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  8. Logging in mysql –ujsolka –p******* mysql> use jsolka; Database changed mysql> select database(); +------------+ | database() | +------------+ | jsolka | +------------+ 1 row in set (0.00 sec) BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  9. Logging In • Can you all take a moment to ssh into binf and try logging into the Mysql server BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  10. Creating Databases and Tables A Hypothetical Table • MySQL command for creating a database: CREATE DATABASE databasename • MySQL for creating a table: CREATE TABLE tablename ( column1name description, column2name description ... ) mysql> CREATE TABLE SEQUENCES ( -> ID INT UNSIGNED NOT NULL AUTO_INCREMENT, -> GI_NUMBER VARCHAR(11), -> MOLECULE ENUM('DNA','AA') default 'DNA', -> LENGTH INT UNSIGNED, -> DATE DATE, -> SEQUENCE LONGTEXT NOT NULL, -> PRIMARY KEY (ID) ); Query OK, 0 rows affected (0.02 sec) Take a moment to try this. BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  11. The Describe Command Take a moment to try this. mysql> describe sequences; +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | GI_NUMBER | varchar(11) | YES | | NULL | | | MOLECULE | enum('DNA','AA') | YES | | DNA | | | LENGTH | int(10) unsigned | YES | | NULL | | | DATE | date | YES | | NULL | | | SEQUENCE | longtext | NO | | | | +-----------+------------------+------+-----+---------+----------------+ 6 rows in set (0.03 sec) BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  12. Inserting Data • After your database and tables have been created, there are two ways to add new records to a table: • First method: give values for all fields in same order as in table: INSERT INTO tablename VALUES (value1, value2 ...); • Second method: give a list of columns and values for these columns: INSERT INTO tablename (columni, columnj) VALUES (valuei, valuej); • Other fields are set to their default values, or to NULL BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  13. Inserting Data CREATE TABLE SEQUENCES ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT, GI_NUMBER VARCHAR(11), MOLECULE ENUM('DNA','AA') default 'DNA', LENGTH INT UNSIGNED, DATE DATE, SEQUENCE LONGTEXT NOT NULL, PRIMARY KEY (ID) ); INSERT INTO SEQUENCES (SEQUENCE,LENGTH,DATE) VALUES ('TAGGT', 5, '2000-10-03'); • The ID field will be filled automatically by AUTO_INCREMENT. • The MOLECULE field will get the default value 'DNA'. • The GI_NUMBER will by set to NULL. • NOTE: Strings are quoted. Numeric values NOT quoted. • Names of database, tables, and fields are NOT quoted. Take a moment to try this. BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  14. Selecting Data • Retrieve information from the DB using the SELECT command: SELECT which_columns FROM tablename; • To select all data: SELECT * FROM tablename; mysql> select * from sequences; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | NULL | DNA | 5 | 2000-10-03 | TAGGT | +----+-----------+----------+--------+------------+----------+ 1 row in set (0.00 sec) Take a moment to try this. BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  15. Selecting Data Take a moment to try this. mysql> select * from sequences; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | NULL | DNA | 5 | 2000-10-03 | TAGGT | | 2 | GI321456 | DNA | 6 | 2001-03-04 | ATGCGT | +----+-----------+----------+--------+------------+----------+ 2 rows in set (0.00 sec) Here is the table after I have added a little more information BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  16. Selecting Data (cont'd) • To select from certain columns: mysql> select gi_number, sequence, molecule from sequences; +-----------+----------+----------+ | gi_number | sequence | molecule | +-----------+----------+----------+ | NULL | TAGGT | DNA | | GI321456 | ATGCGT | DNA | +-----------+----------+----------+ 2 rows in set (0.01 sec) Take a moment to try this. BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  17. Using Conditionals • Use WHERE clauses to list conditions on which records to select: SELECT columns FROM tablename WHERE columnname = 'value'; mysql> select * from sequences where length = 5; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | NULL | DNA | 5 | 2000-10-03 | TAGGT | +----+-----------+----------+--------+------------+----------+ 1 row in set (0.01 sec) mysql> select * from sequences where molecule='dna'; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | NULL | DNA | 5 | 2000-10-03 | TAGGT | | 2 | GI321456 | DNA | 6 | 2001-03-04 | ATGCGT | +----+-----------+----------+--------+------------+----------+ 2 rows in set (0.00 sec) Take a moment to try these. BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  18. Using Conditionals (cont'd) • WHERE clauses can use "=", "<", ">" mysql> select * from sequences where date < '2001-03-04'; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | NULL | DNA | 5 | 2000-10-03 | TAGGT | +----+-----------+----------+--------+------------+----------+ 1 row in set (0.00 sec) • Multiple conditions: mysql> select * from sequences where date < '2001-03-04' AND (length = 5); +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | NULL | DNA | 5 | 2000-10-03 | TAGGT | +----+-----------+----------+--------+------------+----------+ 1 row in set (0.00 sec) Take a moment to try these. BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  19. Some Funny Mysql code • SELECT * FROM users WHERE clue > 0 • 0 rows returned BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  20. MySQL Operators BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  21. Using LIKE and NOT LIKE • Conditions on strings can test if the string matches a limited set of patterns • Use LIKE or NOT LIKE to test for pattern match • Special characters: • match a single character: '_' • match zero or more characters: '%' SELECT * FROM users WHERE login LIKE “jsol%" would return records for users “jsol", “jsolka", etc. SELECT * FROM users WHERE login LIKE "_jsol%" would return records for users "ajsol", "cjsolen", but not “jsolk". • To search for strings containing "_" or "%", escape with "\" SELECT * FROM users WHERE login LIKE "john\_gref%" matches "john_grefens", for example. BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  22. Sorting Query Results • Can sort output by ORDER BY clause after WHERE clauses, if any. mysql> select * from sequences order by length; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | NULL | DNA | 5 | 2000-10-03 | TAGGT | | 2 | GI321456 | DNA | 6 | 2001-03-04 | ATGCGT | +----+-----------+----------+--------+------------+----------+ 2 rows in set (0.00 sec) mysql> select * from sequences order by length DESC; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 2 | GI321456 | DNA | 6 | 2001-03-04 | ATGCGT | | 1 | NULL | DNA | 5 | 2000-10-03 | TAGGT | +----+-----------+----------+--------+------------+----------+ 2 rows in set (0.00 sec) ASC = Ascending (default) DESC = descending BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  23. Sorting Query Results • Can sort output by ORDER BY clause after WHERE clauses, if any. mysql> select * from sequences where (molecule = 'dna') order by date; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | NULL | DNA | 5 | 2000-10-03 | TAGGT | | 2 | GI321456 | DNA | 6 | 2001-03-04 | ATGCGT | +----+-----------+----------+--------+------------+----------+ 2 rows in set (0.00 sec) BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  24. Limiting Query Results • LIMIT controls how many results are returned: SELECT columns FROM tablename WHERE conditions LIMIT [skip], size • Examples: SELECT columns FROM tablename WHERE conditions LIMIT 10 SELECT columns FROM tablename WHERE conditions LIMIT 10, 20 • The first command limits output to first 10 results. • The second command returns 20 results, starting with item 11 • The following returns the earliest DNA sequence in the database: mysql> select * from sequences where molecule = 'dna' order by date limit 1; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | NULL | DNA | 5 | 2000-10-03 | TAGGT | +----+-----------+----------+--------+------------+----------+ 1 row in set (0.00 sec) BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  25. Updating Data • To make a change to existing records, use UPDATE UPDATE tablename SET column = 'value' WHERE conditions mysql> UPDATE sequences SET GI_NUMBER = 'GI99999' WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from sequences; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | GI99999 | DNA | 5 | 2000-10-03 | TAGGT | | 2 | GI321456 | DNA | 6 | 2001-03-04 | ATGCGT | +----+-----------+----------+--------+------------+----------+ 2 rows in set (0.01 sec) BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  26. Deleting Data • Remove data from a table using DELETE DELETE FROM tablename WHERE condition mysql> select * from sequences; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | GI99999 | DNA | 5 | 2000-10-03 | TAGGT | | 2 | GI321456 | DNA | 6 | 2001-03-04 | ATGCGT | +----+-----------+----------+--------+------------+----------+ 2 rows in set (0.01 sec) mysql> delete from sequences where length=6; Query OK, 1 row affected (0.00 sec) mysql> select * from sequences; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | GI99999 | DNA | 5 | 2000-10-03 | TAGGT | +----+-----------+----------+--------+------------+----------+ 1 row in set (0.00 sec) BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  27. Using Functions • MySQL provides many functions that you can use in queries. • Do not put any spaces between function name and parentheses. INSERT INTO SEQUENCES (SEQUENCE,LENGTH,DATE) VALUES ('TAGG', 4, NOW()); -- NOW() returns today's date. SELECT FUNCTION(column1) FROM tablename SELECT COUNT(*) FROM tablename WHERE conditions -- returns just the count of records that match conditions String functions: CONCAT(x,y), LENGTH(col), UPPER(col), LOWER(col), SUBSTRING(col,start,length) Numeric: ABS(x), ROUND(x,d), FORMAT(x,d), RAND(), SQRT(x) ROUND(3.1417, 2) = "3.14" FORMAT(6123456,2) = "6,123,456.00" BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  28. Functions in Action INSERT INTO SEQUENCES (SEQUENCE,LENGTH,DATE) VALUES ('TAGG', 4, NOW()); mysql> select * from sequences; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | GI99999 | DNA | 5 | 2000-10-03 | TAGGT | | 3 | NULL | DNA | 4 | 2009-11-06 | TAGG | +----+-----------+----------+--------+------------+----------+ 2 rows in set (0.00 sec) BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  29. More Functions in Action mysql> select * from sequences; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | GI99999 | DNA | 5 | 2000-10-03 | TAGGT | | 3 | NULL | DNA | 4 | 2009-11-06 | TAGG | +----+-----------+----------+--------+------------+----------+ 2 rows in set (0.00 sec) mysql> select count(*) from sequences where molecule='DNA'; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) as 'DNA COUNT' from sequences where molecule='DNA'; +-----------+ | DNA COUNT | +-----------+ | 2 | +-----------+ 1 row in set (0.04 sec) BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  30. Using Functions mysql> select * from sequences; +----+-----------+----------+--------+------------+----------+ | ID | GI_NUMBER | MOLECULE | LENGTH | DATE | SEQUENCE | +----+-----------+----------+--------+------------+----------+ | 1 | GI99999 | DNA | 5 | 2000-10-03 | TAGGT | | 3 | NULL | DNA | 4 | 2009-11-06 | TAGG | +----+-----------+----------+--------+------------+----------+ 2 rows in set (0.00 sec) mysql> select concat('>',GI_NUMBER,'|type:',MOLECULE,'|',DATE) as HEADERS, LOWER(sequence) as SEQS from sequences; mysql> select concat('>',GI_NUMBER,'|type:',MOLECULE,'|',DATE) as HEADERS ,LOWER(sequence) as SEQS from sequences; +------------------------------+-------+ | HEADERS | SEQS | +------------------------------+-------+ | >GI99999|type:DNA|2000-10-03 | taggt | | NULL | tagg | +------------------------------+-------+ 2 rows in set (0.02 sec) BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  31. Ways to interact with MySQL • Use the mysql client program interactively (as we have been doing) • Run an SQL script with the mysql client • Use Perl to interface with MySQL database • Running an SQL script • Put the MySQL commands into a text file, e.g., script.sql • Run mysql with input/output redirection: jsolka% mysql -ujsolka –p****** -hbinf.gmu.edu < script.sql > script.out -- reads in script.sql -- writes output to script.out -- omit "> script.out" to get the output on the terminal BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  32. Here is the script.sql file use jsolka; select * from sequences; Apologies the script is pretty lame Here is the script.out file jsolka% cat script.out ID GI_NUMBER MOLECULE LENGTH DATE SEQUENCE 1 GI99999 DNA 5 2000-10-03 TAGGT 3 NULL DNA 4 2009-11-06 TAGG The Details BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  33. Perl Database Interface • DBI is a module that provides an interface between Perl and MySQL. • You access the interface module with the statement: use DBI; • After that you can connect to MySQL database servers and send queries via a simple object oriented interface • Two types of objects are available: database handles and statement handles • Perl returns a database handle to the connect method like so: my $dbh = DBI->connect("DBI:mysql:database=$db;host=$host", $user, $password, {RaiseError => 1}); Once you have connected to a database, you can can execute SQL statements like this: my $query = "CREATE TABLE foo (id INTEGER, name VARCHAR(20))"; $dbh->do($query); BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  34. Creating and filling a table with Perl DBI # Create a fresh copy of the SEQUENCES table: $dbh->do("drop table sequences"); # in case it already exists my $create_table = "CREATE TABLE SEQUENCES ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT, GI_NUMBER VARCHAR(11), MOLECULE ENUM('DNA','AA') default 'DNA', LENGTH INT UNSIGNED, DATE DATE, SEQUENCE LONGTEXT NOT NULL, PRIMARY KEY (ID) )"; $dbh->do($create_table); # Create an insert query with 3 parameters my $insert = "INSERT INTO SEQUENCES (GI_NUMBER, DATE, SEQUENCE) VALUES (?, ?, ?)"; # Prepare the query (for efficiency), getting a statement handle my $sth = $dbh->prepare($insert); # Now execute the statement with different data for each record $sth->execute("GI321456", "2001-03-04", "ATGCGT"); $sth->execute("GI123456", "1998-12-03", "MILVK"); $sth->execute("GI786543", "1995-05-29", "ATGCCAGCCA"); BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  35. Getting Results from Database • Create a statement handle • Execute it. • Call fetchrow_array to retrieve each row of results # Create a statement handle for the SELECT query my $query = "SELECT gi_number, date FROM sequences WHERE date < ? "; my $query_sth = $dbh->prepare($query); # Execute the query $query_sth->execute("2000-01-01"); # get each row of results while (my @row = $query_sth->fetchrow_array()) { my ($gi, $date) = @row; print "$gi $date\n"; } # finished with statement handle $query_sth->finish(); # when you're done with the database: $dbh->disconnect(); BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  36. The Output from the Successful Execution of this “Program” jsolka% ./perdb.pl GI123456 1998-12-03 GI786543 1995-05-29 BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  37. Homework • Read Chapter 11 • Quiz next week • Work on Program 4 BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

  38. CGI Programming Writing CGI Applications with Perl (Paperback); Kevin Meltzer and Brent Michalski MySQL MySQL (4th Edition) (Paperback); Paul DuBois Additional Reading BINF634 FALL 2013 LECTURE 10 SQL AND MYSQL

More Related