Creating a MySQL Table

A table is a two dimensional matrix structure, having rows and columns, that is traditionally used in DBMSystems to store user data.

Rules for Creating Tables:

1. A table name can have maximum of 30 characters

2. Alphabets from A-Z, a-z and numbers from 0-9 are allowed

3. A table name should begin with an alphabet

4. The use of the special character like _ is allowed and also recommended

5. SQL reserved words not allowed. For Example: CREATE, SELECT and so on

Each column must have a datatype. The column should either be defined as NULL or NOT NULL. If this value is not specified, the MySQL assumes NULL as the default.

Creating Tables:

In the CREATE TABLE command each column of the table is defined uniquely. Each column has a minimum of three attributes, a name, datatype and size (i.e. column width). Each table column definition is a single clause in the CREATE TABLE syntax. Each table column definition is separated from the other by a comma. Finally, the SQL statement is terminated with a semi colon.

The following is the syntax for creating the table on the command prompt.
Syntax:
CREATE TABLE <TableName>(<ColumnName1><DataType>(<Size>), <ColumnName1><DataType>(<Size>) );

Example:
CREATE TABLE ClientMaster
(
CustomerID VARCHAR(6),
Name VARCHAR(25),
CompanyName VARCHAR(25),
Address VARCHAR(100),
Telephone INT(20)
);

NOTE: It really does not matter whether the create table command is typed on a single line or multiple lines as shown.  We’ve used multiple lines for the sake of clarity and checking if the command line is well formed.  

Output:
Query OK, 0 rows affected (0.13 sec) as shown in Diagram 1.

Diagram 1: Creating table command on command prompt

All table columns belong to a single record. Therefore all the table column definitions are enclosed within parenthesis.

Displaying a Table's Structure
To display information about the columns defined in a table use the following syntax

The following is the syntax for displaying the column names, the data types and the special attributes connected to the table.  

Syntax:
DESCRIBE <TableName>;

Example:
DESCRIBE ClientMaster;

Output:
5 rows in set (0.05 sec) as shown in Diagram 2.

Diagram 2: ‘Describe table’ command on command prompt

• < Prev
• Next >