Creating Tables

CREATE TABLE [IF NOT EXITS] table name
   ( create definitions)
   [table options]

A create definition consists of
- column name.
- data_type
  - There are many of these.
  - An entire chapter in the MySQL manual is dedicated to these
  - I don't see much point in discussing these after 230
  - I am sure that these vary from DBMS to DBMS somewhat.
- optional NULL, NOT NULL
  - Null is a specific value, like a null pointer.
  - Null is the default
- Optional DEFAULT default value
  - Default values must be a constant.
  - If no value is given, then the default is defined here
  - Text and varchar can not have a default value.
- Optional UNIQUE KEY | PRIMARY KEY
  - Sets the primary key field for a table.
  - Can only be used to set a simple key,
  - Compound keys later.
  - Primary keys must also be not null
  - This will be added to the field if it is marked as a primary key.
  - If you don't declare a primary key, MySQL uses the first unique not null field as the primary key.
- Optional AUTO_INCREMENT
- Optional comment 'string'
CONSTRAINT name PRIMARY KEY (field, field)
CONSTRAINT name FOREIGN KEY (field) REFERENCES table(field)
A list of table options can be given as well.
- ENGINE = INNODB,
  - several options, but INNODB is probably the engine of choice for this class..
Look at the examples we have done so far.

Insert into

A reference page.
INSERT INTO TABLE (column list) values (values)
If the column list is empty, all columns, in the order show by describe table is used.
Otherwise columns are listed and the default value is used for non-listed columns.
Values are given in as a tuple.
DEFAULT can be given as a value.
Expressions may be used for values.
A common separated list of tuples can be given
NULL can be a value.

Drop TABLE [IF EXISTS] tablename;
- Order may matter if we have referential integrity constraints.

Update

UPDATE table
   SET colname=value, [colname=value]
   WHERE condition

Load

LOAD DATA LOCAL INFILE 'filename' 
INTO TABLE table_name 
COLUMNS TERMINATED BY ',';