Workshop
MySQL/PHP
2 MySQL Lectures (June 22nd and June 24 th) 2 PHP Lectures (June 29 th and July 1st) Each lecture builds on concepts taught and learned in the previous lecture. The first two lectures discuss the concept of a relational database such as MySQL and show you how to manipulate the data stored in the database from the command line. It is essential to learn this first because PHP makes use of the language of the database. The third and fourth lectures will introduce you to PHP, a server-side scripting language that allows you to interact with the MySQL database from a web browser and create fancy web pages to display the data. PHP is the go-between that fetches the data from the MySQL database and then spits it out dynamically as the nicelyformatted HTML page that the browser expects.
MySQL Part 1
Michael Honig Weill Cornell Medical College Computational Genomics Core Facility
Agenda
Why Relational Databases ?
Relational Databases Introduction to MySQL Entering Basic Queries Workshop: Create Database/Exercises Reading: MySQL Documentation (see course web site.)
Large collections of well-annotated data Most public databases provide cross-links to other databases NCBI GenBank:NCBI taxonomy Gene Ontology:SwissProt human, mouse, fly, FlyBase, SGD SwissProt:PFAM, SwissProt:Prosite Although cross-linking data is available, one cannot integrate all the related data in one query
Benefits of Relational Databases
The Relational Model Data and relationships are represented by a collection of tables. Each table has a certain number of columns with unique names The goal of a relational database is to provide an environment that is both convenient and efficient to use in: Retrieving information from the database (queries) Storing information into the database. Redundancy can be reduced Same information may be duplicated in several places. All copies may not be updated properly. Standards can be enforced Restrict columns to specified data-types and sizes Security restrictions can be applied Every user of the system should be able to access only the data they are permitted to see.
Excel Spreadsheet of Sample Data to be loaded into our MySQL database, ensmartdb
Ensembl Gene ID ENSG00000186891.3 ENSG00000078808.4 ENSG00000176022.1 ENSG00000160087.5 ENSG00000162572.4 ENSG00000162576.4 ENSG00000175756.3 ENSG00000131586.2 ENSG00000179403.2 ENSG00000160072.5 ENSG00000008128.5 ENSG00000169911.4 ENSG00000008130.3 ENSG00000078369.3 ENSMUSG00000041954.1 ENSMUSG00000023286.1
Symbol / Name TNFRSF18 CAB45 B3GALT6 UBE2J2 SCNN1D MGC3047 AKIP MRPL20 WARP ATAD3B CDC2L2 SLC35E2 FLJ13052 GNB1 TNFRSF18 UBE2J2
Chromosome Name Position (bp) End Position (bp) LocusLink IDTaxonomy_ID Start Common NameSpecies 1 1044947 1048147 8784 9606 human Homo sapiens 1 1058370 1073469 51150 9606 human Homo sapiens 1 1073703 1076476 126792 9606 human Homo sapiens 1 1095352 1115292 118424 9606 human Homo sapiens 1 1123634 1133467 6339 9606 human Homo sapiens 1 1194130 1199973 84308 9606 human Homo sapiens 1 1215168 1216641 54998 9606 human Homo sapiens 1 1288703 1294063 55052 9606 human Homo sapiens 1 1322311 1327547 64856 9606 human Homo sapiens 1 1358611 1396091 83858 9606 human Homo sapiens 1 1582617 1604060 985 9606 human Homo sapiens 1 1611978 1625728 9906 9606 human Homo sapiens 1 1630975 1659805 65220 9606 human Homo sapiens 1 1665027 1770792 2782 9606 human Homo sapiens 4 154139702 154142251 21936 10090 mouse Mus musculus 4 154057210 1540722964 140499 10090 mouse Mus musculus
1
Genbank Flat File
Flat Files are not Relational
Data-type is part of the data Contains multiline records There is a considerable number of repeating elements or duplicate data This wastes vast amounts of disk space and will slow down any queries we run against the table More potential for typographical errors
Normalization
Entity Relationship Diagram (ERD)
One to Many Relationship
An organism can have one to many ensembl_gene_ids associated with it, but an
ensembl_gene_id is associated with one and only one organism. The crows foot indicates the many side of the relationship. Organism is the parent table and Gene is the child table. Organism_ID in the Gene table is a foreign key (FK) reference to ID in the Organism table
Normalization is the process of adjusting a data model so that it consists of tables that conform to strict relational tenets, or norms. At its core, the process calls for removing redundancy from the database. One does not build a house without blueprints, or a database without a data model. A data model consists of an ERD (Entity Relationship Diagram) and the supporting detailed column and constraint definitions. The ERD shows the entities, or areas of interest, of the database and how they interrelate.
Gene
ID (PK) Ensembl_Gene_ID Organism_ID (FK) Name LocusLink Chromosome Chromo_Start Chromo_End Description
Organism
ID (PK) Taxonomy_ID Common_Name Species
MySQL
Introduction to MySQL
MySQL is a popular, open source database. Officially pronounced my Ess Cue Ell (not my sequel). Handles very large databases; very fast performance. Why are we using MySQL? Free (much cheaper than Oracle!) Each student can install MySQL locally. Easy to use Shell for creating tables, querying tables, etc. Easy to use with PHP, Java, Perl, etc.
2
Connecting to MySQL
Sample Session
For example:
shell> mysql -u root -p Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1966 to server version: 3.23.58 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
MySQL provides an interactive shell for creating tables, inserting data, etc. On UNIX, you connect to MySQL via the mysql command. To get help:
shell> mysql --help
To connect:
shell> mysql u <username> -p You will then be prompted to type in your password
To exit the MySQL shell, type quit;
mysql> quit
Basic Queries
Basic Queries (continued)
Once logged in, you can try some simple queries. For example:
Heres another query which demonstrates that you can use MySQL as a simple calculator:
mysql> select user(), version(), current_date(); +----------------+-----------+----------------+ | user() | version() | current_date() | +----------------+-----------+----------------+ | root@localhost | 3.23.58 | 2004-06-07 | +----------------+-----------+----------------+ 1 row in set (0.00 sec)
Note that most MySQL commands end with a semicolon (;) MySQL returns the total number of rows found, and the total time to execute the query.
mysql> select 2+2, (4+1)*5, SIN(PI()/4); +-----+---------+-------------+ | 2+2 | (4+1)*5 | SIN(PI()/4) | +-----+---------+-------------+ | 4 | 25 | 0.707107 | +-----+---------+-------------+ 1 row in set (0.02 sec)
Basic Queries (continued)
Multi-Line Commands
You can also enter multiple statements on a single line. Just end each one with a semicolon:
mysql> select version(); select now(); +-----------+ | version() | +-----------+ | 3.23.58 | +-----------+ 1 row in set (0.00 sec) +---------------------+ | now() | +---------------------+ | 2004-06-07 12:50:15 | +---------------------+ 1 row in set (0.00 sec)
MySQL determines where your statement ends by looking for the terminating not semicolon, by looking for the end of the input line. Here's a simple multiple-line statement:
mysql> select -> user() -> , -> current_date(); +----------------+----------------+ | user() | current_date() | +----------------+----------------+ | root@localhost | 2004-06-07 | +----------------+----------------+ 1 row in set (0.00 sec)
3
Canceling a Command
Using a Database
If you decide that you do not want to execute a command that you are in the process of issuing, cancel it by typing \c :
Using a database involves three steps:
mysql> select user(), -> current_date(), -> \c mysql>
Creating (initializing) the database Creating the tables within the database Interacting with the tables by inserting, retrieving, modifying, or deleting data
Creating a Database
Creating a Database (continued)
To create a new database issue the command create database.
To get started on your own database, first check which databases currently exist. Use the SHOW statement to find out which databases currently exist on the server:
mysql> create database ensmartdb; Query OK, 1 row affected (0.00 sec)
mysql> show databases; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.01 sec)
To select a database, issue the command use;
mysql> use ensmartdb; Database changed
Creating Tables
Creating Tables (continued)
Once you have selected a database, you can view all existing tables in the database:
mysql> show tables; Empty set (0.00 sec)
Lets create a table for storing data about genes. Table: gene COLUMN DATATYPE
An empty set, as you may guess, indicates that no tables have been created yet in the database.
gene_id name ensembl_gene_id organism_id locuslink chromosome chromo_start chromo_end description
INTEGER VARCHAR(35) VARCHAR(50) INTEGER VARCHAR(10) TINYINT INTEGER INTEGER VARCHAR(255)
4
Datatypes used in this database
Column Type Attributes
VARCHAR a variable length fixed string INTEGER a whole number ranging from -2147683648 to 2147483647 TINYINT a whole number ranging from -128 to 127 DATE a date value, in YYYY-MM-DD format
NULL is something of a typeless value. Generally, its used to mean no value, unknown value, missing value, out of range, none of the above, and so forth. You can insert NULL values into tables, retrieve them from tables, and test whether or not a value is NULL. You cant perform arithmetic on NULL valuesand if you try, the result will be NULL. AUTO_INCREMENT Use the AUTO_INCREMENT attribute when you wish to generate unique identifiers or values in a series. AUTO_INCREMENT values normally begin at 1 and increase by 1 per row. When you insert NULL into an AUTO_INCREMENT column, MySQL inserts a value one greater than the current maximum value in that column. You may have at most one AUTO_INCREMENT column in a table. MySQL also requires that every AUTO_INCREMENT column have a unique index.
Column Type Attributes (continued)
Creating Tables (Continued)
UNSIGNED This attribute disallows negative values. Making a column UNSIGNED does not change the size of the underlying datatypes range; it just shifts the range upward. Consider this table specification:
To create a table, use the CREATE TABLE command:
CREATE TABLE gene ( gene_id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (gene_id), name VARCHAR(35) NOT NULL, ensembl_gene_id VARCHAR(50) NOT NULL, locuslink VARCHAR(10) NULL, chromosome TINYINT NULL, chromo_start INTEGER NULL, chromo_end INTEGER NULL, description VARCHAR(255) NULL, UNIQUE(ensembl_gene_id) );
CREATE TABLE my_table (
column_tiny column_tiny_u ) TINYINT, TINYINT UNSIGNED
column_tiny and column_tiny_u are both TINYINT columns with a range of 256 values. However, the range of column_tiny is -128 to 127 whereas the range of column_tiny_u is 0 to 255.
PRIMARY KEY means that the column is indexed for fast lookups and that each value in the column must be unique. This prevents us from using the same ID twice by mistake.
mysql> -> -> -> -> -> -> -> -> -> -> ->
Query OK, 0 rows affected (0.00 sec)
Showing Tables
Describing Tables
To verify that the table gene has been created, issue the SHOW TABLES command:
To view a table structure, use the DESCRIBE command:
mysql> show tables; +---------------------+ | Tables_in_ensmart...