CSSE 290 Web Programming

Lecture 22: Relational Databases and SQL

Reading: 13.1 - 13.2, 13.3

Attribution:Except where otherwise noted, the contents of this document are Copyright 2012 Marty Stepp, Jessica Miller, and Victoria Kirst. All rights reserved. Any redistribution, reproduction, transmission, or storage of part or all of the contents in any form is prohibited without the author's expressed written permission.

Otherwise noted: Claude Anderson was given permission to modify the slides for CSSE 290 at Rose-Hulman by author Jessica Miller. The authors' original slides, based on Web Programming Step by Step, can be seen at http://webstepbook.com.
Some of the examples in some days' slides are from David Fisher at Rose-Hulman, who was kind enough to allow me to use them. My intention is to mark these examples with [DSF].

Why use a database?

• powerful: can search it, filter data, combine data from multiple sources
• fast: can search/filter a database very quickly compared to a file
• big: scales up to very large data sizes
• safe: built-in mechanisms for failure recovery (e.g. transactions)
• multi-user: concurrency features let many users view/edit data at same time
• abstract: provides layer of abstraction between stored data and app(s)
  • many different database management systems understand the same SQL commands

Relational databases

• relational database: A method of structuring data as tables that can be associated with each other by shared attributes.
• a table row corresponds to a unit of data called a record;
  a column corresponds to an attribute of that record
• relational databases typically use the Structured Query Language (SQL) to define, manage, and search data
• there are other types of databases, but we won't study them here
  • (take CSSE 333 and 433)
• we will only focus on very simple database concepts
• and how to query a database from within PHP code

Relational database software

• Oracle
• Microsoft SQL Server (powerful) and Microsoft Access (simple)
• IBM DB2
• PostgreSQL (powerful/complex free open-source database system)
• SQLite (transportable, lightweight free open-source database system)
• MySQL (simple free open-source database system)
  • many servers run "LAMP" (Linux, Apache, MySQL, and PHP)
  • Also MAMP for Macs, XAMPP and WAMP for Windows
  • Wikipedia is run on PHP and MySQL
  • we will use MySQL in this course

Example: simpsons database

• Let's see how to load this database on your machine
• We'll also load a couple of others: world, imdb_small

Example: world database

Example: imdb database

• also available, imdb_small with fewer records (for testing queries)

SQL basics

SELECT name FROM cities WHERE id = 17;

INSERT INTO countries VALUES ('SLD', 'ENG', 'T', 100.0);

• Structured Query Language (SQL): a language for searching and updating a database
• a standard syntax that is used by all database software (with minor incompatiblities)
  • generally case-insensitive
• a declarative language: describes what data you are seeking, not exactly how to find it

Entering SQL commands directly in MySQL

SHOW DATABASES;
USE database;
SHOW TABLES;

• From the command line (with appropriate directory prefix, or set PATH

$ mysql -u yourusername -p
Password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> USE world;
Database changed

mysql> SHOW TABLES;
+-----------+
| cities    | 
| countries | 
| languages | 
+-----------+
3 rows in set (0.00 sec)

The SQL SELECT statement

SELECT column(s) FROM table;

SELECT name, code FROM countries;

• the SELECT statement searches a database and returns a set of results
  • the column name(s) written after SELECT filter which parts of the rows are returned
  • table and column names are case-sensitive
  • SELECT * FROM table; keeps all columns

The DISTINCT modifier

SELECT DISTINCT column(s) FROM table;

SELECT language
FROM languages;

SELECT DISTINCT language
FROM languages;

• eliminates duplicates from the result set

The WHERE clause

SELECT column(s) FROM table WHERE condition(s);

SELECT name, population FROM cities WHERE country_code = "FSM";

• WHERE clause filters out rows based on their columns' data values
• in large databases, it's critical to use a WHERE clause to reduce the result set size
• suggestion: when trying to write a query, think of the FROM part first, then the WHERE part, and lastly the SELECT part

More about the WHERE clause

WHERE column operator value(s)

SELECT code, name, gnp FROM countries WHERE gnp > 2000000;

• the WHERE portion of a SELECT statement can use the following operators:
  • =, >, >=, <, <=
  • <> : not equal
  • BETWEEN min AND max
  • LIKE pattern
  • IN (value, value, ..., value)

Multiple WHERE clauses: AND, OR

SELECT * FROM cities WHERE country_code = 'USA' AND population >= 2000000;

• multiple WHERE conditions can be combined using AND and OR

Approximate matches: LIKE

WHERE column LIKE pattern

SELECT code, name, population FROM countries WHERE name LIKE 'United%';

• LIKE 'text%' searches for text that starts with a given prefix
• LIKE '%text' searches for text that ends with a given suffix
• LIKE '%text%' searches for text that contains a given substring

Sorting by a column: ORDER BY

ORDER BY column(s)

SELECT code, name, population FROM countries
WHERE name LIKE 'United%' ORDER BY population;

• can write ASC or DESC to sort in ascending (default) or descending order:

  SELECT * FROM countries ORDER BY population DESC;
  

• can specify multiple orderings in decreasing order of significance:

  SELECT * FROM countries ORDER BY population DESC, gnp;
  

• see also: GROUP BY

Limiting rows: LIMIT

LIMIT number

SELECT name FROM cities WHERE name LIKE 'K%' LIMIT 5;

• can be used to get the top-N of a given category (ORDER BY and LIMIT)
• also useful as a sanity check to make sure your query doesn't return 10⁷ rows