MySQL creates a data table

MySQL creates a data table

The following information is required to create a MySQL data table:

• The table name
• The name of the table field
• Define each table field

Grammar

Here's the SQL common 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 W3CSCHOOL database w3cschool_tbl:

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 )
);

Instance resolution:

• If you don't want a field to be NULL, you can set the property of the field to NOT NULL, and if you enter the field's data as NULL when you manipulate the database, you will report an error.
• AUTO_INCREMENT as a self-added property, generally used for primary keys, the value is automatically added 1.
• The PRIMARY KEY keyword is used to define the column as the primary key. Y ou can use multiple columns to define the primary key, which is separated by a comma.

Create a table with a command prompt

The mySQL data table can be created very easily through the mysql?command window. You can use the SQL statement CREATE TABLE to create a data table.

Instance

Here are some examples of creating w3cschool_tbl tables:

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

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

Create a data table using a PHP script

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

The function has two arguments that return TRUE if executed successfully, otherwise FALSE is returned.

Grammar

bool mysql_query( sql, connection );

Instance

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

<?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
';
$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('数据表创建失败: ' . mysql_error());
}
echo "数据表创建成功\n";
mysql_close($conn);
?>