This command creates an index on a given column in a given table.Example:
CREATE INDEX idx_cust_ID ON Customers (Customer_ID)CREATE [TEMPORARY]
TABLE [IF NOT EXISTS] table_name table_definition [table_options] [[IGNORE or
REPLACE] select_statement]
This statement will create a table. The table definition is a list of column names and types.
CREATE TABLE has the following optional parameters:
CREATE TEMPORARY TABLE Temp_Customers (Cust_ID INT) This statement
creates a temporary table that will automatically be deleted when the connection
that created it drops.
CREATE TABLE IF NOT EXISTS Customers (Cust_ID INT) This statement
will only create the table if the table does not currently exist.
TYPE = {ISAM or MYISAM or HEAP} Used to set the type of table that is
created.
The tables options can be any of the following:
o ISAM is the original table type.
o MYISAM is the newer storage type. This is used by
default.
o HEAP tables are stored in memory only. They have the
following constraints:
HEAP tables do not support AUTO_INCREMENT columns.Only = or < = > can be used with indexes.
HEAP tables uses a fixed record length format.
HEAP tables do not support an index on a NULL column.
HEAP tables do not support BLOB or TEXT columns.
You can have non-unique keys in a HEAP table (not that normal with hashed
tables).
Example: CREATE TABLE Customers (Cust_ID INT) TYPE = HEAP
AUTO_INCREMENT = X Sets the starting point of an AUTO_INCREMENT column.
Example: CREATE TABLE Customers (Cust_ID INT AUTO_INCREMENT)
AUTO_INCREMENT = 90000
AVG_ROW_LENGTH = X Rarely used. Sets the length of a table row in
variable length columns.
CHECKSUM = {0 or 1} Adds a performance hit, but allows the
myisamchk utility to spot tables with corrupted data easier.
COMMENT = "comment to be made" Allows you to add a comment to a
table. The limit is 60 characters.
MAX_ROWS = X Sets the maximum number of rows that will be stored in a
table. May increase performance.
MIN_ROWS = X Sets the minimum number of rows that will be stored in a
table. May increase performance.
PACK_KEYS = {0 or 1} When set to 1, will make your indexes smaller
and faster. Updates will take longer.
PASSWORD = "password" This will password protect the .frm file.
DELAY_KEY_WRITE = {0 or 1} When set to 1, all updates to the table
will occur when the table is not in use.
ROW_FORMAT= { default or dynamic or static or compressed
} Determines how the rows will be stored in a table.
Example of a CREATE TABLE statement:
CREATE TABLE Customers
(Customer_ID INT NOT NULL AUTO_INCRMENT PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(35) NOT NULL,
Address VARCHAR(100) NOT NULL)