Data Definition Statements

Part:   1   2  3 

This chapter describes data definition statements:

CREATE TABLE ...;
DROP TABLE ...;
CREATE INDEX ...;
DROP INDEX ...;
ALTER TABLE ...;

Create Table Statements

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 output data types and data of 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) 1999 by Dr. Herong Yang
--
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 the data types of the output of the select sub-statement, if it used in the create table statement.

(Continued on next part...)

Part:   1   2  3