mysql create table statement

MySQL create table command

the tables are organized into rows and columns: and each table must have a name.

the create table statement is used to create a table in a database.the create table statement is one of the most complex statement in mysql.

mysql create table syntax

      create  table     [ if not exists]         table_name
                                                  
                  (
                     column_name1     data_type(size),
                                                    
                     column_name2     data_type(size),
                                                    
                     column_name3     data_type(size),
                                                    
                     ......
                                                   
                  );

the if not exists is an optional element of the statement.

the if not exists statement prevents you from an error of creating a new table that already exists.

the size parameter specifies the maximum length of the column of the table.

to define a column for the table in the create table statement,use the following syntax:

column_name    data_type(size)  [null | not null]   [default value]    [auto_increment]

 

  • the column_name specifies the name of the column.it associates with a specific data    type and the size.
  • the null or not null indicates that the column accepts null value or not.
  • the default value is used to specify the default value of the column.
  • the auto_increment indicates that the value of column is increased by one whenever a    new row is inserted into the table.

note - The type clause in the create table command is optional, and MySQL tables default to the MyISAM table type.


mysql   create  table  example

now we want to create a table called "user" that contains five columns: userid, firstname, lastname,address, and city.

you can use the create table statement to create the user table as follows:

create table     if not exists     user (
                                      
                     userid             int(10)              not  null    auto_increment,
                     
                     firstname         varchar(255)      default   null,
                     
                     lastname         varchar(255)      default   null,
                     
                     address           varchar(255)      default   null,
                      
                     city                varchar(255)      default   null,
                                    )

the userid column is of type int and will hold an integer.

the lastname, firstname, address, and city columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.

the empty "user" table will now look like this:

useridfirstnamelastnameaddresscity
     

note : the empty table filled with data with the insert into statement.


MySQL Create temporary table

the create   temporary  table command is used to create a temporary table.

syntax :

   create    temporary   table       table_name (

                                                                     definition, ....
                                                                      
                                                                    );

a temporary table is visible only to the current connection, and is dropped automatically when the connection is closed.