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 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 the show columns statement:

SHOW COLUMNS FROM 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) 2015, 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:

Last update: 2015.

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 Perl Programs and MySQL Servers

 PHP Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

Using DDL to Create Tables and Indexes

CREATE TABLE - Statement to Create Tables

 Column Options When Creating Tables

 CREATE INDEX - Statement to Create Indexes

 ALTER TABLE - Statement to Alter Table Structures

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 Outdated Tutorials

 References

 PDF Printing Version