MySQL Creating Data Table

Creating a MySQL data table requires the following information:

• Table name
• Table field names
• Definition of each table field

Syntax

The following is the general SQL syntax for creating a MySQL data table:

CREATE TABLE table_name (column_name column_type);

In the example below, we will create a data table named tutorialpro_tbl in the tutorialpro database:

CREATE TABLE IF NOT EXISTS `tutorialpro_tbl`(
   `tutorialpro_id` INT UNSIGNED AUTO_INCREMENT,
   `tutorialpro_title` VARCHAR(100) NOT NULL,
   `tutorialpro_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `tutorialpro_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Example Analysis:

• If you do not want a field to be NULL, you can set the field's attribute to NOT NULL. If data for this field is NULL during database operations, an error will be thrown.
• AUTO_INCREMENT defines the column as an auto-increment attribute, typically used for primary keys, with values automatically incremented by 1.
• The PRIMARY KEY keyword is used to define a column as the primary key. You can use multiple columns to define the primary key, separated by commas.
• ENGINE sets the storage engine, and CHARSET sets the encoding.

Creating a Table via Command Prompt

Creating a MySQL data table can be easily done through the mysql> command window. You can use the SQL statement CREATE TABLE to create a data table.

Example

Below is an example of creating the tutorialpro_tbl table:

root@host# mysql -u root -p
Enter password:*******
mysql> use tutorialpro;
Database changed
mysql> CREATE TABLE tutorialpro_tbl(
   -> tutorialpro_id INT NOT NULL AUTO_INCREMENT,
   -> tutorialpro_title VARCHAR(100) NOT NULL,
   -> tutorialpro_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( tutorialpro_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql>

Note: The MySQL command terminator is a semicolon ;.

Note: -> is a newline identifier and should not be copied.

Creating a Table Using PHP Script

You can use PHP's mysqli_query() function to create a data table in an existing database.

This function takes two parameters and returns TRUE on success, otherwise FALSE.

Syntax

mysqli_query(connection, query, resultmode);

Example

The following example uses a PHP script to create a data table:

<?php
$dbhost = 'localhost';  // mysql server host address
$dbuser = 'root';       // mysql username
$dbpass = '123456';     // mysql user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Connection failed: ' . mysqli_error($conn));
}
echo 'Connected successfully<br />';
$sql = "CREATE TABLE tutorialpro_tbl( ".
        "tutorialpro_id INT NOT NULL AUTO_INCREMENT, ".
        "tutorialpro_title VARCHAR(100) NOT NULL, ".
        "tutorialpro_author VARCHAR(40) NOT NULL, ".
        "submission_date DATE, ".
        "PRIMARY KEY ( tutorialpro_id ))ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Failed to create data table: ' . mysqli_error($conn));
}
echo "Data table created successfully\n";
mysqli_close($conn);
?>

After successful execution, you can view the table structure via the command line.