MySQL create table query and Example

Database information is saved in a table. In order to create a table execute following query:

mysql> CREATE TABLE TABLE_NAME (
FIELD1 INT NOT NULL PRIMARY KEY,
FIELD2 CHAR2(25),
FIELD3 INT(9));

Explanation:

• The INT in the field1 shows that this field will contain only integers
• NOT NULL makes sure that this field is not left empty
• PRIMARY KEY keyword in the query makes the FIELD1 as the primary key of the table. The values in the primary key field should be unique.
• The CHAR(25) and INT(9) commands allow character type data in FIELD2 and integer type data in FIELD3 respectively. The numbers in the parenthesis are the maxium number of ints or chars in the fields.

Example:

CREATE TABLE CUSTOMER (
account_number int  NOT NULL PRIMARY KEY,
date DATE,
name VARCHAR(20) NOT NULL,
address VARCHAR(60) NOT NULL,
advance INT,
nic_number VARCHAR(20)
);

This query creates a table named "customer" with the primary key "account_number" of type int.
Other fields created in the table are:

•  'date' of data type date, 
• "name" field of datatype varchar with a size 20 characters max. It also declares that name cannot be null
• "address" field of datatype varchar with a size 60 characters max. It also declares that address cannot be null
• "advance" field of datatype int 
• "nice_number" field of datatype varchar with a size 20 characters max

In order to make sure that the table is created and has everything right, check the existing table in the database by

mysql>show tables;

mysql>explain customer;