SQL/MySQL/Table Index/Create Table

Create Simple Tables

/*
mysql> DROP TABLE Employee;
mysql> CREATE TABLE Employee (
    ->     Name VARCHAR(50),
    ->     Phone VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.21 sec)
mysql> Show tables;
+-------------+
| Tables_in_t |
+-------------+
| employee    |
| enrollment  |
| student     |
| studentexam |
+-------------+
4 rows in set (0.07 sec)
mysql> Describe Employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Name  | varchar(50) | YES  |     | NULL    |       |
| Phone | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
mysql> INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", "666 2323");
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO Employee (Name) VALUES ("John Doe");
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO Employee (Name, Phone) VALUES ("John Doe", NULL);
Query OK, 1 row affected (0.01 sec)
mysql> Select * from Employee;
+----------+----------+
| Name     | Phone    |
+----------+----------+
| Joe Wang  | 666 2323 |
| John Doe | NULL     |
| John Doe | NULL     |
+----------+----------+
3 rows in set (0.08 sec)

*/
DROP TABLE Employee;
CREATE TABLE Employee (
    Name VARCHAR(50), 
    Phone VARCHAR(15)
);
Show tables;
Describe Employee;
INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", "666 2323");
INSERT INTO Employee (Name) VALUES ("John Doe");
INSERT INTO Employee (Name, Phone) VALUES ("John Doe", NULL);
Select * from Employee;

Create table: auto increment primary key

Drop table Product;
  
CREATE TABLE Product
(
   ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   Name VARCHAR(40) NOT NULL,
   PRIMARY KEY (ID)
);
Describe Product;

Create table: BLOB

/*
mysql> Drop table Inventory;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE Inventory
    -> (
    ->    ID SMALLINT UNSIGNED,
    ->    Name VARCHAR(40),
    ->    Photo BLOB,
    ->    Quantity INT UNSIGNED
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql> Describe Inventory;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| ID       | smallint(5) unsigned | YES  |     | NULL    |       |
| Name     | varchar(40)          | YES  |     | NULL    |       |
| Photo    | blob                 | YES  |     | NULL    |       |
| Quantity | int(10) unsigned     | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

*/
Drop table Inventory;
CREATE TABLE Inventory
(
   ID SMALLINT UNSIGNED,
   Name VARCHAR(40),
   Photo BLOB,
   Quantity INT UNSIGNED
);
Describe Inventory;

Create table: char set and language

/*
mysql> Drop table Product;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE Product
    -> (
    ->    ID SMALLINT UNSIGNED,
    ->    Name VARCHAR(40),
    ->    DescriptionDoc TEXT CHARACTER SET latin1 COLLATE latin1_bin
    -> );
Query OK, 0 rows affected (0.12 sec)
mysql> Describe Product;
+----------------+----------------------+------+-----+---------+-------+
| Field          | Type                 | Null | Key | Default | Extra |
+----------------+----------------------+------+-----+---------+-------+
| ID             | smallint(5) unsigned | YES  |     | NULL    |       |
| Name           | varchar(40)          | YES  |     | NULL    |       |
| DescriptionDoc | text                 | YES  |     | NULL    |       |
+----------------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

*/
Drop table Product;
  
CREATE TABLE Product
(
   ID SMALLINT UNSIGNED,
   Name VARCHAR(40),
   DescriptionDoc TEXT CHARACTER SET latin1 COLLATE latin1_bin
);
Describe Product;

Create table: char, varchar

Drop table Catalog;
CREATE TABLE Catalog
(
   ID SMALLINT,
   Description VARCHAR(40),
   Name CHAR(3),
   Price DECIMAL(7,2)
);
Describe Catalog;

Create table: default int value

/*
mysql> Drop table Employee;
mysql> CREATE TABLE Employee
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL,
    ->    YearBorn YEAR NOT NULL,
    ->    CityBorn VARCHAR(40) NOT NULL DEFAULT "Unknown"
    -> );
Query OK, 0 rows affected (0.11 sec)
mysql> Describe Employee;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| ID       | smallint(5) unsigned |      |     | 0       |       |
| YearBorn | year(4)              |      |     | 0000    |       |
| CityBorn | varchar(40)          |      |     | Unknown |       |
+----------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

*/
Drop table Shelf;
  
CREATE TABLE Shelf
(
   ID SMALLINT UNSIGNED NOT NULL,
   YearBorn YEAR NOT NULL,
   NumBooks SMALLINT NOT NULL DEFAULT 1
);
Describe Shelf;

Create table: default value for column

/*
mysql> Drop table Books;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Books
    -> (
    ->    ID SMALLINT NOT NULL,
    ->    Name VARCHAR(40) NOT NULL,
    ->    PubID SMALLINT NOT NULL DEFAULT "Unknown"
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.09 sec)
mysql> Describe Books;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | smallint(6) |      |     | 0       |       |
| Name  | varchar(40) |      |     |         |       |
| PubID | smallint(6) |      |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

*/
Drop table Books;
CREATE TABLE Books
(
   ID SMALLINT NOT NULL,
   Name VARCHAR(40) NOT NULL,
   PubID SMALLINT NOT NULL DEFAULT "Unknown"
)
ENGINE=INNODB;
Describe Books;

Create table: engine type INNODB

Drop table Employee;
CREATE TABLE Employee
(
   AuthID SMALLINT UNSIGNED NOT NULL,
   YearBorn YEAR NOT NULL,
   CityBorn VARCHAR(40) NOT NULL DEFAULT "Unknown"
)
ENGINE=INNODB;
Describe Employee;

Create table: ENUM and set

/*
mysql> Drop table Bike;
mysql> CREATE TABLE Bike
    -> (
    ->    ID SMALLINT UNSIGNED,
    ->    Model VARCHAR(40),
    ->    Color ENUM("red", "blue", "green", "yellow"),
    ->    Options SET("rack", "light", "helmet", "lock")
    -> );
Query OK, 0 rows affected (0.09 sec)
mysql> Describe Bike;
+---------+-------------------------------------+------+-----+---------+-------+
| Field   | Type                                | Null | Key | Default | Extra |
+---------+-------------------------------------+------+-----+---------+-------+
| ID      | smallint(5) unsigned                | YES  |     | NULL    |       |
| Model   | varchar(40)                         | YES  |     | NULL    |       |
| Color   | enum("red","blue","green","yellow") | YES  |     | NULL    |       |
| Options | set("rack","light","helmet","lock") | YES  |     | NULL    |       |
+---------+-------------------------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

*/
Drop table Bike;
CREATE TABLE Bike
(
   ID SMALLINT UNSIGNED,
   Model VARCHAR(40),
   Color ENUM("red", "blue", "green", "yellow"),
   Options SET("rack", "light", "helmet", "lock")
);
Describe Bike;

Create table: foreign key

Drop table Orders;
CREATE TABLE Orders
(
   ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
   ModelID SMALLINT UNSIGNED NOT NULL,
   Desciption VARCHAR(40),
   FOREIGN KEY (ModelID) REFERENCES Models (ModelID)
      ON DELETE CASCADE ON UPDATE CASCADE
);
Describe Orders;

Create table: not null and default value

/*
mysql> Drop table Product;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Product
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL,
    ->    Name VARCHAR(40) NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> Describe Product;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| ID    | smallint(5) unsigned |      |     | 0       |       |
| Name  | varchar(40)          |      |     |         |       |
+-------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
*/
Drop table Employee;
CREATE TABLE Employee
(
   ID SMALLINT UNSIGNED NOT NULL,
   YearBorn YEAR NOT NULL,
   CityBorn VARCHAR(40) NOT NULL DEFAULT "Unknown"
);
Describe Employee;

Create table: null and not null

Drop table Product;
       
CREATE TABLE Product
(
   ID SMALLINT UNSIGNED NOT NULL,
   Name VARCHAR(40) NOT NULL
);
Describe Product;

Create table: primary key

/*
mysql> Drop table Orders;
mysql> CREATE TABLE Orders
    -> (
    ->    OrderID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
    ->    ModelID SMALLINT UNSIGNED NOT NULL,
    ->    ModelDescrip VARCHAR(40)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> Describe Orders;
+--------------+----------------------+------+-----+---------+-------+
| Field        | Type                 | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| OrderID      | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID      | smallint(5) unsigned |      |     | 0       |       |
| ModelDescrip | varchar(40)          | YES  |     | NULL    |       |
+--------------+----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

*/
Drop table Orders;
CREATE TABLE Orders
(
   OrderID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
   ModelID SMALLINT UNSIGNED NOT NULL,
   ModelDescrip VARCHAR(40)
);
Describe Orders;

Create table: primary key 2

Drop table Orders;
CREATE TABLE Orders
(
   ID SMALLINT UNSIGNED NOT NULL,
   ModelID SMALLINT UNSIGNED NOT NULL,
   Desciption VARCHAR(40),
   PRIMARY KEY (ID)
);
Describe Orders;

Create table: REFERENCES

/*
mysql> Drop table Orders;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE Orders
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
    ->    ModelID SMALLINT UNSIGNED NOT NULL REFERENCES Models (ModelID),
    ->    ModelDescrip VARCHAR(40)
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> Describe Orders;
+--------------+----------------------+------+-----+---------+-------+
| Field        | Type                 | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| ID           | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID      | smallint(5) unsigned |      |     | 0       |       |
| ModelDescrip | varchar(40)          | YES  |     | NULL    |       |
+--------------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

*/
Drop table Orders;
CREATE TABLE Orders
(
   ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
   ModelID SMALLINT UNSIGNED NOT NULL REFERENCES Models (ModelID),
   ModelDescrip VARCHAR(40)
);
Describe Orders;

Create table: small int and unsigned int

drop table Inventory;  
CREATE TABLE Inventory
(
   ProductID SMALLINT(4) UNSIGNED ZEROFILL,
   Quantity INT UNSIGNED
);
describe Inventory;

Create table: smallint, decimal and float

/*
mysql> Drop table Product;
mysql> CREATE TABLE Product
    -> (
    ->    ID SMALLINT,
    ->    Price DECIMAL(7,2),
    ->    Weight FLOAT(8,4)
    -> );
Query OK, 0 rows affected (0.09 sec)
mysql> Describe Product;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| ID     | smallint(6)  | YES  |     | NULL    |       |
| Price  | decimal(7,2) | YES  |     | NULL    |       |
| Weight | float(8,4)   | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
*/
Drop table Product;
CREATE TABLE Product
(
   ID SMALLINT,
   Price DECIMAL(7,2),
   Weight FLOAT(8,4)
);
Describe Product;

Create table: two columns for primary key

/*
mysql> Drop table Orders;
mysql> CREATE TABLE Orders
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL,
    ->    ModelID SMALLINT UNSIGNED NOT NULL,
    ->    Descrip VARCHAR(40),
    ->    PRIMARY KEY (ID, ModelID)
    -> );
Query OK, 0 rows affected (0.06 sec)
mysql> Describe Orders;
+---------+----------------------+------+-----+---------+-------+
| Field   | Type                 | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID      | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID | smallint(5) unsigned |      | PRI | 0       |       |
| Descrip | varchar(40)          | YES  |     | NULL    |       |
+---------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
*/
Drop table Orders;
  
CREATE TABLE Orders
(
   ID SMALLINT UNSIGNED NOT NULL,
   ModelID SMALLINT UNSIGNED NOT NULL,
   Descrip VARCHAR(40),
   PRIMARY KEY (ID, ModelID)
);
Describe Orders;

Create table: YEAR and TIMESTAMP

Drop table Orders;
CREATE TABLE Orders
(
   ID SMALLINT UNSIGNED,
   BookID SMALLINT UNSIGNED,
   Copyright YEAR,
   OrderDate TIMESTAMP
);
Describe Orders;