MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
1PLs Company - #1Payday.Loans Agency - Loans online and near me $100-$2500 (Same Day)
Powered by MySQL
 
Home / Database Structure / Tables / Question No: 203

How to create table in MySQL?

Answer No: 203

MySQL provides more than one way to create tables:

  • You can create an empty table, either by specifying its definition explicitly or by using the definition of an existing table.
  • You can create a table populated from the result of a SELECT statement.
  • You can create temporary tables.

A new table can be created from an explicit definition by using a CREATE TABLE statement that includes the table name and a list of columns. Each column has its own name and definition. The table definition may also include index definitions. To create a table, give its name followed by a list of column definitions within parentheses:

CREATE TABLE table_name (column_definitions);
CREATE TABLE tbl_name (id INT NOT NULL);

If you try to create a table that already exists, an error occurs. If you simply want to ensure that the table exists, add an IF NOT EXISTS clause to the statement:

CREATE TABLE IF NOT EXISTS tbl_name (i INT);

More complex tables have multiple columns, with the column definitions separated by commas. The following table definition includes, in addition to an id column, two 30-character columns for storing last names and first names, and a column for storing date values. All columns are declared NOT NULL to indicate that they require non-NULL values.

CREATE TABLE tbl_name
(
    id         INT NOT NULL,
    first_name CHAR(30) NOT NULL,
    last_name  CHAR(30) NOT NULL,
    dob        DATE NOT NULL
);

While creating a table, you can provide index definitions in addition to the column definitions. Indexes are useful for speeding up queries by reducing record lookup time. Here's a simple table that includes two index definitions. The first creates an index on the id column and requires each id value to be unique. The second index definition creates a two-column index on the last_name and first_name columns of the table:

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

Every table is created using one of the storage engines supported by the server. To specify a storage engine when you create a table, include an ENGINE = engine_name option in the CREATE TABLE statement. The following statement creates t as an InnoDB table:

CREATE TABLE tbl_name
(
    id         INT NOT NULL,
    first_name CHAR(30) NOT NULL,
    last_name  CHAR(30) NOT NULL,
    UNIQUE (id),
    INDEX (last_name, first_name)
)  ENGINE = InnoDB; 

Related MySQL FAQs to the Above FAQ

How-to-create-table-based-on-existing-tables How to create table based on existing tables?

What-is-temporary-table-and-how-to-create-it-in-MySQL What is temporary table and how to create it in MySQL?

How-to-describe-a-table-in-MySQL How to describe a table in MySQL?

How-to-renaming-a-table-in-MySQL How to renaming a table in MySQL?

How-to-specify-multiple-alterations-for-a-table How to specify multiple alterations for a table?

How-to-get-table-metadata How to get table metadata?

How-to-add-new-column-into-an-existing-table-in-MySQL How to add new column into an existing table in MySQL?

How-to-add-a-new-AUTO_INCREMENT-field-to-an-existing-table How to add a new AUTO_INCREMENT field to an existing table?

How-to-drop-a-column-from-the-existing-table How to drop a column from the existing table?

How-to-drop-all-existing-tables-in-one-go How to drop all existing tables in one go?

How-to-get-columns-of-an-existing-table-in-MySQL How to get columns of an existing table in MySQL?

How-to-change-data-type-of-an-existing-column How to change data type of an existing column?

How-to-delete-table-records How to delete table records?

How-to-set-AUTO_INCREMENT-value-other-than-1-in-MySQL How to set AUTO_INCREMENT value other than 1 in MySQL?

How-to-get-copy-of-table-structure-in-MySQL-using-query How to get copy of table structure in MySQL using query?

How-to-get-constraints-or-keys-of-the-existing-table How to get constraints or keys of the existing table?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2023  www.mysqlfaqs.net
All rights reserved.