Copyright © tutorialspoint.com

# Create MySQL Tables

The table creation command requires:

- Name of the table

- Names of fields

- Definitions for each field

### Syntax:

Here is generic SQL syntax to create a MySQL table:

    CREATE TABLE table_name (column_name column_type);

Now we will create following table in TUTORIALS database.

    tutorials_tbl(
       tutorial_id INT NOT NULL AUTO_INCREMENT,
       tutorial_title VARCHAR(100) NOT NULL,
       tutorial_author VARCHAR(40) NOT NULL,
       submission_date DATE,
       PRIMARY KEY ( tutorial_id )
    );

Here few items need explanation:

- Field Attribute NOT NULL is being used because we do not want this field to be NULL. SO if user will try to create a record with NULL value then MySQL will raise an error.

- Field Attribute AUTO_INCREMENT tells to MySQL to go ahead and add the next available number to the id field.

- Keyword PRIMARY KEY is used to define a column as primary key. You can use multiple columns separated by comma to define a primary key.

## Creating Tables from Command Prompt:

This is easy to create a MySQL table from mysql> prompt. You will use SQL command CREATE TABLE to create a table.

### Example:

Here is an example which creates tutorials_tbl:

    root@host# mysql -u root -p
    Enter password:*******
    mysql> use TUTORIALS;
    Database changed
    mysql> CREATE TABLE tutorials_tbl(
       -> tutorial_id INT NOT NULL AUTO_INCREMENT,
       -> tutorial_title VARCHAR(100) NOT NULL,
       -> tutorial_author VARCHAR(40) NOT NULL,
       -> submission_date DATE,
       -> PRIMARY KEY ( tutorial_id )
       -> );
    Query OK, 0 rows affected (0.16 sec)
    mysql>

NOTE: MySQL does not terminate a command until you give a semi colon (;) at the end of SQL command.

## Creating Tables Using PHP Script:

To create new table in any existing database you would need to use PHP function mysql_query(). You will pass its second argument with proper SQL command to create a table.

### Example:

Here is an example to create a table using PHP script:

    <html>
    <head>
    <title>Creating MySQL Tables</title>
    </head>
    <body>
    <?php
    $dbhost = 'localhost:3036';
    $dbuser = 'root';
    $dbpass = 'rootpassword';
    $conn = mysql_connect($dbhost, $dbuser, $dbpass);
    if(! $conn )
    {
      die('Could not connect: ' . mysql_error());
    }
    echo 'Connected successfully<br />';
    $sql = "CREATE TABLE tutorials_tbl( ".
           "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
           "tutorial_title VARCHAR(100) NOT NULL, ".
           "tutorial_author VARCHAR(40) NOT NULL, ".
           "submission_date DATE, ".
           "PRIMARY KEY ( tutorial_id )); ";
    mysql_select_db( 'TUTORIALS' );
    $retval = mysql_query( $sql, $conn );
    if(! $retval )
    {
      die('Could not create table: ' . mysql_error());
    }
    echo "Table created successfully\n";
    mysql_close($conn);
    ?>
    </body>
    </html>

Copyright © tutorialspoint.com