Web Programming Step by Step, 2nd Edition

Lecture 15: Database Definition and Data Manipulation

Reading: 13.2

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.

Valid HTML5 Valid CSS

Learning about databases and tables

SHOW DATABASES;
SHOW TABLES;
DESCRIBE table;
SHOW TABLES;
+-----------+
| students  | 
| courses   | 
| grades    | 
| teachers  |
+-----------+    4 rows in set

The SQL INSERT statement

INSERT INTO table
VALUES (value, value, ..., value);
INSERT INTO students
VALUES (789, "Nelson", "muntz@fox.com", "haha!");

More about INSERT

INSERT INTO table (columnName, columnName, ..., columnName)
VALUES (value, value, ..., value);
INSERT INTO students (name, email)
VALUES ("Lewis", "lewis@fox.com");

The SQL REPLACE statement

REPLACE INTO table (columnName, columnName, ..., columnName)
VALUES (value, value, ..., value);
REPLACE INTO students
VALUES (789, "Martin", "prince@fox.com");

The SQL UPDATE statement

UPDATE table
SET column = value,
    ...,
    column = value
WHERE column = value;
UPDATE students
SET email = "lisasimpson@gmail.com"
WHERE id = 888;

The SQL DELETE statement

DELETE FROM table
WHERE condition;
DELETE FROM students
WHERE id = 888;

Creating and deleting an entire database

CREATE DATABASE name;
DROP DATABASE name;
CREATE DATABASE warcraft;

Creating and deleting a table

CREATE TABLE name (
	columnName type constraints,
	...
	columnName type constraints
);
DROP TABLE name;
CREATE TABLE students (
	id INTEGER,
	name VARCHAR(20),
	email VARCHAR(32),
	password VARCHAR(16)
);

SQL data types

Column constraints

CREATE TABLE students (
	id INTEGER UNSIGNED NOT NULL PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	email VARCHAR(32),
	password VARCHAR(16) NOT NULL DEFAULT "12345"
);

Rename a table

ALTER TABLE name RENAME TO newName;
ALTER TABLE students RENAME TO children;

Add/remove/modify a column in a table

ALTER TABLE name
	ADD COLUMN columnName type constraints;

ALTER TABLE name DROP COLUMN columnName;

ALTER TABLE name
	CHANGE COLUMN oldColumnName newColumnName type constraints;

Database Design

Database design principles

First database design

student_grades
nameemailcourseteachergrade
Bartbart@fox.comComputer Science 142KrabappelB-
Bartbart@fox.comComputer Science 143HooverC
Milhousemilhouse@fox.comComputer Science 142KrabappelB+
Lisalisa@fox.comComputer Science 143HooverA+
Lisalisa@fox.comComputer Science 190MSteppA+
Ralphralph@fox.comInformatics 100KrabappelD+

Improved database design

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
courses
idnameteacher_id
10001Computer Science 1421234
10002Computer Science 1435678
10003Computer Science 190M9012
10004Informatics 1001234
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+
teachers
idname
1234Krabappel
5678Hoover
9012Stepp

Database design exercise

amazon cookie

Suppose we want to write a web store like Amazon.com. The store sells products that can be purchased by customers online. The customer can add items to their shopping cart and then order them. The customer can also check the order's status, whether it has shipped, etc.

Connecting to a server in a terminal

screenshot screenshot

Using the MySQL console

Once connected via SSH, type the following command at the prompt:

mysql -u username -p

Type in your MySQL password (should have been emailed to you). The screen will not show anything as you type the password.

Now at the mysql> prompt, you can type SQL commands. End each with a semicolon ;.

screenshot