CREATE TABLE

Defining Indexes at table creation time:

CREATE TABLE HeadOfState
(
    ID INT NOT NULL,
    LastName CHAR(30) NOT NULL,
    FirstName CHAR(30) NOT NULL,
    CountryCode CHAR(3) NOT NULL,
    InaugurationDate Date NOT NULL,
    INDEX (Inauguration)
);

To create a unique index, the KEY or UNIQUE keywords may be used instead of INDEX. To include multiple columns in an index (that is, to create a composite index), list all the column names within parentheses, separated by commas:

CREATE TABLE HeadOfState
(
    ID INT NOT NULL,
    LastName CHAR(30) NOT NULL,
    FirstName CHAR(30) NOT NULL,
    CountryCode CHAR(3) NOT NULL,
    InaugurationDate DATE NOT NULL,
    INDEX (LastName, FirstName)
);

A table can have multiple indexes:

CREATE TABLE HeadOfState
(
    ID INT NOT NULL,
    LastName CHAR(30) NOT NULL,
    FirstName CHAR(30) NOT NULL,
    CountryCode CHAR(3) NOT NULL,
    InaugurationDate DATE NOT NULL,
    INDEX (LastName, FirstName),
    INDEX (InaugurationDate)
);

To create a unique index, use the UNIQUE keyword instead of INDEX.

You can have a PRIMARY KEY index that span multiple columns:

CREATE TABLE people
(
    last_name CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (last_name, first_name)
);

Naming Indexes:

CREATE TABLE HeadOfState
(
    ID INT NOT NULL,
    LastName CHAR(30) NOT NULL,
    FirstName CHAR(30) NOT NULL,
    INDEX NameIndex (LastName, FirstName),
    UNIQUE IDIndex (ID)
);

Choosing an Indexing Algorithm:

CREATE TABLE table_name
(
    id INT
    INDEX USING BTREE (id)
) ENGINE = MEMORY;

CREATE TABLE [IF NOT EXISTS] customer(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name char(50) NOT NULL,
    address char(50),
    yesno ENUM('yes','no'),
    truefalse ENUM('true','false'),
    symptom SET('sneezing', 'runny nose', 'stuffy head', 'red eyes'),
    ts1 TIMESTAMP,
    ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    c CHAR(10) CHARACTER SET utf8,
    d DATE DEFAULT '1999-12-31'
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

MySQL does not perform any check on the table structure when you add the IF NOT EXISTS clause. If a table with the given name exists but has a structure different from the one you've defined in the CREATE TABLE statement, MySQL will issue no warning.

Only one AUTO_INCREMENT column is allowed per table, and it must be indexed (for example, by making it a primary key).

MySQL does not allow the use of function as default value.

Many database developers use DEFAULT values instead of NULL, especially in columns that will be used in calculation or data groupings.

CREATE TABLE seminar
(
    att_id INT UNSIGNED NOT NULL,
    sem_title ENUM('Database Design', 'Query Optimization', 'SQL Standards', 'Using Replication'),
    INDEX (att_id)
)

CREATE TABLE multisequence
(
    name CHAR(10) NOT NULL,
    name_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (name, name_id)
);

Inserting name values into the multisequence table generates separate sequences for each distinct name.

Here is a table that includes two index definitions. The first creates an index on the id column and requires each id to be unique. The second index definition creates a two-column index on the last_name, and first_name columns:

CREATE TABLE t
(
    id INT NOT NULL,
    last_name CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    UNIQUE (id),
    INDEX (last_name, first_name)
);

Create a table using SELECT:

CREATE TABLE Capital
 SELECT Country.Name AS Country, City.Name AS Capital
   FROM Country, City WHERE Country.Capital = City.ID