MySQL Create Table

To create a MySQL data table, the following information is required:

• Table name

• Table field name

• Define each table field

Syntax

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

CREATE TABLE table_name (column_name column_type);

In the following example, we will create a data table in the w3Create a data table w in the codebox database3`codebox_tbl`:

CREATE TABLE IF NOT EXISTS `w3`codebox_tbl`(
　　　`w3`codebox_id` INT UNSIGNED AUTO_INCREMENT,
　　　`w3`codebox_title` VARCHAR(100) NOT NULL,
　　　`w3`codebox_author` VARCHAR(40) NOT NULL,
　　　`submission_date` DATE,
　　　PRIMARY KEY (　`w3`codebox_id`　)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Example Analysis:

• If you don't want the field to be NULL You can set the attribute of the field to NOT NULL, When operating a database, if the input data for this field isNULL , an error will be reported.

• AUTO_INCREMENT defines the column as an auto-incrementing attribute, generally used for the primary key, with the number automatically incremented1.

• The PRIMARY KEY keyword is used to define a column as a primary key. You can use multiple columns to define a primary key, separated by commas.

• ENGINE sets the storage engine, CHARSET sets the encoding.

Create a table through the command prompt

You can easily create MySQL data tables through the mysql> command window. You can use SQL statements CREATE TABLE to create a data table.

Online example

The following is the creation of data table w3codebox_tbl example:

root@host# mysql　-u root　-p
Enter password:*******
mysql> use w3codebox;
Database changed
mysql> CREATE TABLE w3codebox_tbl(
　　　-> w3codebox_id INT NOT NULL AUTO_INCREMENT,
　　　-> w3codebox_title VARCHAR(100) NOT NULL,
　　　-> w3codebox_author VARCHAR(40) NOT NULL,
　　　-> submission_date DATE,
　　　-> )PRIMARY KEY (3codebox_id )
　　　-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16　sec)
mysql>

Note:The MySQL command terminator is the semicolon ; .

Note: -> Is a newline character identifier, do not copy.

Create data table using PHP script

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

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

Syntax

mysqli_query(connection, query, resultmode);

Online 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 username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
echo 'Connection successful<br}}　/';
$sql = "CREATE TABLE w3codebox_tbl(".
　　　　　　　　"w3codebox_id INT NOT NULL AUTO_INCREMENT, ".
　　　　　　　　"w3codebox_title VARCHAR(100) NOT NULL, ".
　　　　　　　　"w3codebox_author VARCHAR(40) NOT NULL, ".
　　　　　　　　"submission_date DATE, ".
　　　　　　　　"PRIMARY KEY ( w3codebox_id ) ENGINE=InnoDB DEFAULT CHARSET=utf8";
mysqli_select_db($conn, 'w';3codebox');
$retval = mysqli_query($conn, $sql);
if(! $retval )
{
　　　　die('Table creation failed: ' . mysqli_error($conn));
}
echo "Table created successfully\n";
mysqli_close($conn);
?>

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