Creating a table is the very basic operation in the database. Table is created using a CREATE TABLE statement. The create table command requires the following:
- Table name
- Column names
- Column data types
- Column default values (Optional)
- Indexes (if any)
- Characterset, collation and Engine (Optional).
Basic table structure:
Example of a create table command:
create table table_1( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, class VARCHAR(40) NOT NULL, DOB DATE, PRIMARY KEY ( id ) ) engine='innodb';
Fig 7.1 Example of table creation.
Now here is the little description of above example:
Here four columns are used named id, name, class, DOB with data types INT, VARCHAR, CLASS and date in creating the table table_1. Here NOT NULL is used to indicate that none of the columns allow NULL except DOB. There a PRIMARY key is used on id column which means this is unique key index and can be used for reference to other tables. Here the id is AUTO INCREMENT also which means this id is generated aiutomatically on insertion of a new row and is incremented by one by default.And at last the table engine which is INNODB here.
You can check and validate the table creation by the command show tables:
This command lists all the tables present in the current database. Here the table table_1 exists which is created via above example.
NOTE: Before creating the table you must be in some database means you must have to enter in a database by the command “use database database_name” or “\u database_name”.
If you want to check the details of the table you can use desc command means description of the table followed by table name. Here is the Example :
Command: DESC TABLE_NAME ;
If you want the output in a query format as you create it then use the command show create table as :
Command: SHOW CREATE TABLE TABLE_NAME :
Create table has an another form in which you can create a new table from the old table by using the command CREATE TABLE LIKE command:
Command: CREATE TABLE TABLE_NEW LIKE TABLE_OLD ;
Every table creation in the MySQL makes physical files in the data directory of mysql server under the directory named same as of database name. Although the physical files depend upon the engine of the table. For example as we read in previous chapter of storage Engine MyISAM creates the three files .frm, .myi and .myd wheras the Innodb creates the frm file only and data exists in the main ibdata file.