MySQL Create Data Table

Creating a MySQL data table requires the following information:

• Table Name
• Table field name
• Define each table field

grammar
The following is the general SQL syntax for creating MySQL data tables:

CREATE TABLE table_name (column_name column_type);

In the following example, we will create the data table runoob_tbl in the RUNOOB database:

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Instance resolution:

• If you don't want the field to be NULL, you can set the field's property to NOT NULL. When you manipulate the database, you will get an error if the data entered for the field is NULL.
• The AUTO_INCREMENT definition column is a self-increasing property, typically used for a primary key, with a value automatically added by 1.
• The PRIMARY KEY keyword is used to define columns as primary keys.You can use multiple columns to define the primary key, separated by commas.
• ENGINE sets the storage engine and CHARSET sets the encoding.

Create tables from a command prompt

MySQL data tables can be easily created through the MySQL > command window.You can use the SQL statement CREATE TABLE to create a data table.
Example
The following is an example of creating a data table, runoob_tbl:

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

Note: The MySQL command terminator is a semicolon (;).

Creating data tables using PHP scripts

You can use PHP's mysqli_query() function to create data tables for existing databases.
This function has two parameters and returns TRUE on successful execution or FALSE on failure.
grammar

mysqli_query(connection,query,resultmode);

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

<?php
$dbhost = 'localhost:3306';  // mysql server host address
$dbuser = 'root';            // mysql user name
$dbpass = '123456';          // mysql username password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('connection failed: ' . mysqli_error($conn));
}
echo 'Connection Successful<br />';
$sql = "CREATE TABLE runoob_tbl( ".
        "runoob_id INT NOT NULL AUTO_INCREMENT, ".
        "runoob_title VARCHAR(100) NOT NULL, ".
        "runoob_author VARCHAR(40) NOT NULL, ".
        "submission_date DATE, ".
        "PRIMARY KEY ( runoob_id ))ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Data table creation failed: ' . mysqli_error($conn));
}
echo "Datasheet created successfully\n";
mysqli_close($conn);
?>

After successful execution, you can view the table structure from the command line:

MySQL Delete Data Table

Deleting tables in MySQL is very easy, but you should be careful when deleting tables because all data will disappear after the delete command is executed.
grammar
The following is the general syntax for deleting MySQL data tables:

DROP TABLE table_name ;

Delete Datasheet in Command Prompt Window

Delete the data table SQL statement in the mysql>command prompt window as DROP TABLE:
Example
The following example deletes the data table runoob_tbl:

root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> DROP TABLE runoob_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

Delete data tables using PHP scripts

PHP uses the mysqli_query function to delete MySQL data tables.
This function has two parameters and returns TRUE on successful execution or FALSE on failure.
H3>Syntax

mysqli_query(connection,query,resultmode);

Example
The following example uses a PHP script to delete the data table runoob_tbl:

<?php
$dbhost = 'localhost:3306';  // mysql server host address
$dbuser = 'root';            // mysql user name
$dbpass = '123456';          // mysql username password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('connection failed: ' . mysqli_error($conn));
}
echo 'Connection Successful<br />';
$sql = "DROP TABLE runoob_tbl";
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
  die('Data table deletion failed: ' . mysqli_error($conn));
}
echo "Successful data table deletion\n";
mysqli_close($conn);
?>

After successful execution, we can't see the runoob_tbl table using the following commands:

mysql> show tables;
Empty set (0.01 sec)