CREATE TABLE - Statement to Create Tables

MySQL Tutorials - Herong's Tutorial Examples

∟CREATE TABLE - Statement to Create Tables

A tutorial example is provided on how to use CREATE TABLE statements to create tables in different ways.

The create table statement allows you to create a new table in the database. It has a number of syntax formats:

1. To create a permanent table:

CREATE TABLE tbl_name (column_list);

where "column_list" defines the columns of the table with the following syntax:

col_name col_type col_options,
col_name col_type col_options,
...
col_name col_type col_options

2. To create a temporary table:

CREATE TEMPORARY TABLE tbl_name (column_list);

3. To create a permanent table if it doesn't exist:

CREATE TABLE tbl_name IF NOT EXISTS (column_list);

4. To create a permanent table with the same definition as an existing table:

CREATE TABLE tbl_name LIKE old_tbl_name;

5. To create a permanent table with data types and data generated from a select statement:

CREATE TABLE tbl_name select_statement;

To show the columns of an existing table, you can use these statements:

DESC tbl_name;
SHOW COLUMNS FROM tbl_name;
SHOW CREATE TABLE tbl_name;

To rename an existing table, you can use the rename table statement:

RENAME TABLE old_tbl_name TO new_tbl_name;

To delete an existing table, you can use the drop table statement:

DROP TABLE tbl_name;

Here is an example SQL code, CreateTable.sql, showing you how to create a table by selecting data from existing tables:

-- CreateTable.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
CREATE TABLE IF NOT EXISTS User (Login VARCHAR(8), Password CHAR(8));
INSERT INTO User VALUES ('herong','8IS3KOXW');
INSERT INTO User VALUES ('mike','PZ0JG');
SELECT 'User table:';
SHOW COLUMNS FROM User;
SELECT * FROM User;
--
CREATE TABLE IF NOT EXISTS UserCopy SELECT * FROM User;
SELECT 'UserCopy table:';
SHOW COLUMNS FROM UserCopy;
SELECT * FROM UserCopy;
--
CREATE TABLE IF NOT EXISTS UserDump
   SELECT CONCAT(Login,':') AS Login, CHAR_LENGTH(Password) AS Count
   FROM User;
SELECT 'UserCopy table:';
SHOW COLUMNS FROM UserDump;
SELECT * FROM UserDump;
--
DROP TABLE User;
DROP TABLE UserCopy;
DROP TABLE UserDump

If you run the code, you will get:

User table:
User table:
Field    Type       Null    Key     Default Extra
Login    varchar(8) YES     NULL
Password varchar(8) YES     NULL
Login   Password
herong  8IS3KOXW
mike    PZ0JG

UserCopy table:
UserCopy table:
Field    Type       Null    Key     Default Extra
Login    varchar(8) YES
Password varchar(8) YES
Login   Password
herong  8IS3KOXW
mike    PZ0JG

UserDump table:
UserDump table:
Field   Type    Null    Key     Default Extra
Login   char(9) YES
Count   int(10) YES
Login   Count
herong: 8
mike:   5

A couple of interesting notes on the output:

The show columns statement reports that MySQL sets all columns to be variable length, if one column is variable length.
The output of the UserCopy table shows that the create table statement with select sub-statement works perfectly.
The output of the UserDump table shows that the columns types are based on the data types of the output of the select sub-statement, if it used in the create table statement.