MySQL CREATE TABLE
The MySQL CREATE TABLE Statement
The CREATE TABLE statement is used to create a new table in a database.
Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
The table’s column names are specified by the column parameters.
The kind of data that a column can contain is indicated by the datatype argument (varchar, integer, date, etc.).
Tip: Visit our comprehensive Data kinds Reference for a summary of all the data kinds that are accessible.
MySQL CREATE TABLE Example
A table named “Persons” with the following five columns is created using the following example: PersonID, LastName, FirstName, Address, and City.
Example
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
An integer will be stored in the PersonID column, which is of type int.
The maximum length for the varchar columns containing the LastName, FirstName, Address, and City is 255 characters. These columns are meant to hold characters.
This is how the “Persons” table will seem after it is empty:
| PersonID | LastName | FirstName | Address | City |
|---|---|---|---|---|
Tip: The empty “Persons” table can now be filled with data with the SQL INSERT INTO statement.
Create Table Using Another Table
generate TABLE can also be used to generate a clone of an existing table.
The column definitions are the same for the new table. You can choose to pick all columns or just some of them.
The values from the old table will be transferred to the new table if you create it from an existing one.
Syntax
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;
The following SQL creates a new table called “TestTables” (which is a copy of the “Customers” table):
Example
CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;
