MySQL

CS418 - Web Programming - Spring 2015

Old Dominion University

Mat Kelly (mkelly@cs.odu.edu)
http://www.cs.odu.edu/~mkelly/cs418

What this lecture will cover

  • Review of relational databases
    • Normalization
    • Referential integrity
  • Basic MySQL commands
  • Other types of databases

Relational Databases

  • Data organized in tables
    • Can be created, retrieved, deleted, and updated
  • Uses keys for manipulation
    • Primary key - unique identifier for the table
    • Foreign key - matches primary key of another table

First Normal Form (1NF)

name real_name power 1 power 2 power 3 lair_address city state zip
Clean Freak John Smith Strength X-ray vision Flight
Invisibility
123 Poplar Ave Townsburg OH 45293
Soap Stud Efram Jones Speed 123 Poplar Ave Townsburg OH 45293
The Dustmite Dustin Huff Strength Dirtiness Laser Vision 452 Elm St. #3D Burgtown OH 45201
  1. No repeating columns containing same data
  2. All columns contain a single value
  3. Primary key to uniquely identify each row

First Normal Form (1NF)

id name real_name power lair_address city state zip
1 Clean Freak John Smith Strength 123 Poplar Ave Townsburg OH 45293
1 Clean Freak John Smith X-ray vision 123 Poplar Ave Townsburg OH 45293
1 Clean Freak John Smith Flight 123 Poplar Ave Townsburg OH 45293
1 Clean Freak John Smith Invisibility 123 Poplar Ave Townsburg OH 45293
2 Soap Stud Efram Jones Speed 123 Poplar Ave Townsburg OH 45293
3 The Dustmite Dustin Huff Strength 452 Elm St. #3D Burgtown OH 45201
3 The Dustmite Dustin Huff Dirtiness 452 Elm St. #3D Burgtown OH 45201
3 The Dustmite Dustin Huff Laser Vision 452 Elm St. #3D Burgtown OH 45201
  1. Eliminate repeating columns
  2. Add primary key to tables
  3. Each attribute is atomic

What if John Smith changes his name?

Second Normal Form (2NF)

id name real_name power lair_address city state zip
1 Clean Freak John Smith Strength 123 Poplar Ave Townsburg OH 45293
1 Clean Freak John Smith X-ray vision 123 Poplar Ave Townsburg OH 45293
1 Clean Freak John Smith Flight 123 Poplar Ave Townsburg OH 45293
1 Clean Freak John Smith Invisibility 123 Poplar Ave Townsburg OH 45293
2 Soap Stud Efram Jones Speed 123 Poplar Ave Townsburg OH 45293
3 The Dustmite Dustin Huff Strength 452 Elm St. #3D Burgtown OH 45201
3 The Dustmite Dustin Huff Dirtiness 452 Elm St. #3D Burgtown OH 45201
3 The Dustmite Dustin Huff Laser Vision 452 Elm St. #3D Burgtown OH 45201
  1. Honor First Normal Form (1NF)
  2. Create separate tables for data duplicate across rows
  3. Be aware of relationship
    • 1:1
    • 1:m
    • m:n

Second Normal Form (2NF) Result

id lair_id name real_name
1 1 Clean Freak John Smith
2 1 Soap Stud Efram Jones
3 2 The Dustmite Dustin Huff
id lair_address city state zip
1 123 Poplar Ave Townsburg OH 45293
2 452 Elm St. #3D Burgtown OH 45201
id power
1 Stength
2 X-Ray vision
3 Flight
4 Invisibility
5 Speed
6 Dirtiness
7 Laser Vision
char_id power_id
1 1
1 2
1 3
1 4
2 5
3 1
3 6
3 7
  1. Satisfies 1NF
  2. Created separate tables for data duplicated across rows

Are city and state directly related to the lairs?

Third Normal Form (3NF)

  • Honor 1st and 2nd Normal Form
  • Create separate tables for any transitive or partial dependencies
id lair_address city state zip
1 123 Poplar Ave Townsburg OH 45293
2 452 Elm St. #3D Burgtown OH 45201

Third Normal Form (3NF) Result

id lair_id name real_name
1 1 Clean Freak John Smith
2 1 Soap Stud Efram Jones
3 2 The Dustmite Dustin Huff
id zip_id lair_address
1 45293 123 Poplar Ave
2 45201 452 Elm St. #3D
id power
1 Stength
2 X-Ray vision
3 Flight
4 Invisibility
5 Speed
6 Dirtiness
7 Laser Vision
char_id power_id
1 1
1 2
1 3
1 4
2 5
3 1
3 6
3 7
id city state
45293 Townsburg OH
45201 Burgtown OH

Further Forms

  • Other normal forms are possible (BCNF, 4NF, 5NF)
    • e.g., Boyce-Codd (3.5) NF, 4NF, 5NF
    • More info in CS450 - Database Concepts
  • Referential Integrity
    • loss of integrity - a foreign key into another table is no longer valid
    • "404 error" are bad in databases and should not occur

What this lecture will cover

  • Review of relational databases
    • Normalization
    • Referential integrity
  • Basic MySQL commands
  • Other types of databases

Standardization

  • Table Names
    • descriptive of main function of app
    • relatively short
    • lowercase
  • Column names
    • lowercase, short, separate words by '_'
  • Primary key
    • single primary key always called 'id'
  • Foreign keys
    • end with 'id'
    • start with table descriptor

MySQL Hierarchy

server mydb.mydomain.com

database superheroes

table heroes
table lairs
table powers
table powers_mapping
table zip_mapping

database businesslogic
...

database recipes
...

Interfacing with MySQL

  • /usr/bin/mysql -p
    • -p will prompt for password

phpMyAdmin

  • Access your MySQL database through a GUI
  • Change your password
  • Create, edit, delete tables
  • Run (and test) queries
  • View and print table structures
  • Bulk backup and restore
phpmyadmin

Manipulating Tables and Data

  • CREATE - create new databases, tables
  • ALTER - modify existing tables
  • DELETE - erase data from tables
  • DESCRIBE - show structure of tables
  • INSERT INTO tablename VALUES - put data in table
  • UPDATE - modify existing data in tables
  • DROP - destroy table or database (values + structures)

SQL Query Form

SELECT [fieldnames] FROM [tablenames] WHERE [criteria] ORDER BY [fieldname to sort on] LIMIT [offset, maxrows]

Extended syntax at http://dev.mysql.com/doc/refman/5.5/en/select.html

Example SQL SELECT

id lair_id name real_name
1 1 Clean Freak John Smith
2 1 Soap Stud Efram Jones
3 2 The Dustmite Dustin Huff
SELECT name, real_name FROM heroes
name real_name
Clean Freak John Smith
Soap Stud Efram Jones
The Dustmite Dustin Huff

SQL Joins

  • Pulling in data from two different tables
id name
1 Pirate
2 Monkey
3 Ninja
4 Spaghetti
id name
1 Rutabaga
2 Pirate
3 Darth Vader
4 Ninja

From Coding Horror

INNER JOIN

  • Records that match in both tables (intersection)
id name
1 Pirate
2 Monkey
3 Ninja
4 Spaghetti
id name
1 Rutabaga
2 Pirate
3 Darth Vader
4 Ninja

SELECT * FROM TableA INNER JOIN TableB on TableA.name = TableB.name

From Coding Horror

OUTER JOIN

  • Records in both tables (union)
id name
1 Pirate
2 Monkey
3 Ninja
4 Spaghetti
null null
null null
id name
2 Pirate
null null
4 Ninja
null null
1 Rutabaga
3 Darth Vader

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name

From Coding Horror

LEFT OUTER JOIN

  • For TableA data, find matching TableB data
id name
1 Pirate
2 Monkey
3 Ninja
4 Spaghetti
id name
2 Pirate
null null
4 Ninja
null null

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name

From Coding Horror

Only Left Records

  • Only records unique to TableA
id name
2 Monkey
4 Spaghetti
id name
null null
null null

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id is null

From Coding Horror

Exclude Common Records

  • Exclude common records, keep rest
id name
2 Monkey
4 Spaghetti
null null
null null
id name
null null
null null
1 Rutabaga
3 Darth Vader

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id is null OR TableB.id is null

From Coding Horror

PHP and MySQL

  • Connect to a MySQL server
    • $mysqli = new mysqli ("hostname", "user", "pass", "db")
  • Send MySQL commands/query to server
    • $results = $mysqli->query("query")
  • Shows error message generated by the MySQL server
    • $mysqli->error
  • Release results array
    • $results->free()
    • Note: result object is only created for SELECT, SHOW, DESCRIBE or EXPLAIN queries, so for CREATE, INSERT, UPDATE there is nothing to "free".
  • Close connection
    • $mysqli->close();

Fetching Results

  • General Note
    • If two or more columns of the result have the same field (column) names, the last column will take percedence and overwrite the earlier data.
    • Can use alias in select statement (AS keyword)
    • Can use numeric array index
  • $results->fetch_array()
    • stores data in both the numeric indices of the result array AND associate indices using the field (column) name as the key

fetch_array

$query = "SELECT * FROM heroes";
$results = $mysqli->query($query)
  or die($mysql->error.__LINE__);

$row = $results->fetch_array();
print_r($row);
id lair_id name real_name
1 1 Clean Freak John Smith
2 1 Soap Stud Efram Jones
3 2 The Dustmite Dustin Huff
Array ( [0] => Clean Freak [name] => Clean Freak [1] => John Smith [real_name] => John Smith ) Array ( [0] => Soap Stud [name] => Soap Stud [1] => Efram Jones [real_name] => Efram Jones ) Array ( [0] => The Dustmite [name] => The Dustmite [1] => Dustin Huff [real_name] => Dustin Huff )

PHP ⟷ MySQL demophpMyAdmin

Try XAMPP

SQL: Adding data

INSERT INTO `lecture4`.`heroes`
(`id` ,`lair_id` ,`name` ,`real_name`) VALUES
(NULL , '4', 'General Grime', 'Phillip Grimaldi');
id lair_id name real_name
1 1 Clean Freak John Smith
2 1 Soap Stud Efram Jones
3 2 The Dustmite Dustin Huff
id zip_id lair_address
1 45293 123 Poplar Ave
2 45201 452 Elm St. #3D
id power
1 Stength
2 X-Ray vision
3 Flight
4 Invisibility
5 Speed
6 Dirtiness
7 Laser Vision
char_id power_id
1 1
1 2
1 3
1 4
2 5
3 1
3 6
3 7
id city state
45293 Townsburg OH
45201 Burgtown OH

SQL: Removing data

DELETE FROM `lecture4`.`heroes` WHERE
`id`='1'

id lair_id name real_name
1 1 Clean Freak John Smith
2 1 Soap Stud Efram Jones
3 2 The Dustmite Dustin Huff
4 4 General Grime Phillip Grimaldi
id zip_id lair_address
1 45293 123 Poplar Ave
2 45201 452 Elm St. #3D
id power
1 Stength
2 X-Ray vision
3 Flight
4 Invisibility
5 Speed
6 Dirtiness
7 Laser Vision
char_id power_id
1 1
1 2
1 3
1 4
2 5
3 1
3 6
3 7
id city state
45293 Townsburg OH
45201 Burgtown OH

SQL: Updating data

UPDATE `lecture4`.`heroes`
SET `name`='Admiral Grime', `real_name`='Phillip J. Grimaldi' WHERE `id` = '4'

id lair_id name real_name
2 1 Soap Stud Efram Jones
3 2 The Dustmite Dustin Huff
4 4 General Grime Phillip Grimaldi
id zip_id lair_address
1 45293 123 Poplar Ave
2 45201 452 Elm St. #3D
id power
1 Stength
2 X-Ray vision
3 Flight
4 Invisibility
5 Speed
6 Dirtiness
7 Laser Vision
char_id power_id
1 1
1 2
1 3
1 4
2 5
3 1
3 6
3 7
id city state
45293 Townsburg OH
45201 Burgtown OH