Web Programming Step by Step

Lecture XX
More SQL Syntax

Except where otherwise noted, the contents of this presentation are Copyright 2010 Marty Stepp and Jessica Miller.

Entities and relationships

• an entity is a record in a table in the database
• a relationship is a connection between two or more entities
• database designers often draw ER diagrams like the above to represent the entities and relationships in their databases

The SQL INSERT statement

INSERT INTO table

VALUES (value, value, ..., value);

INSERT INTO Student 
VALUES (789, "Nelson", "muntz@fox.com");

• adds a new row to the given table
• How would we record that Nelson took CSE 190M and got a D+ in it?

The SQL UPDATE statement

UPDATE table
SET column = value,
    ...,
    column = value
WHERE column = value;

UPDATE Student
SET    email = "lisasimpson@gmail.com"
WHERE  SID = 888;

• modifies an existing row(s) in a table

The SQL CREATE TABLE statement

CREATE TABLE name (
    columnName type constraints,
    ...
    columnName type constraints

);

CREATE TABLE Student (
    SID INTEGER UNSIGNED NOT NULL PRIMARY KEY,
    name VARCHAR(20),
    email VARCHAR(32)
);

• adds/deletes an entire new table from this database
• you can add constraints such as NOT NULL for a field that cannot be blank or PRIMARY KEY for a column that must be unique for every row
• related commands: CREATE DATABASE, DROP TABLE

SQL data types

• BOOLEAN
• INTEGER
• DOUBLE
• VARCHAR : a string
• DATE, TIME, DATETIME
• BLOB : binary data
• quick reference