MySQL creates data table

Creating a MySQL data table requires the following information:

Table name

Table field name

Define each table field

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

CREATE TABLE table_name (column_name column_type);

The following example will create the data table runoob_tbl in the RUNOOB database:

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

Example analysis:

If you don’t want the field to be NULL, you can set the field’s attribute to NOT NULL. When operating the database, if the data entered in this field is NULL, an error will be reported.

AUTO_INCREMENT is defined as an auto-incrementing attribute, generally used for primary keys, and the value will automatically increase by 1.

PRIMARY KEY keyword is used to define columns as primary keys. You can use multiple columns to define the primary key, separated by commas.

Create a table through the command prompt

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

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(
-& gt; runoob_id into null auto_increment,
-& gt; runoob_title varchar (100) not null,
-& gt; runoob_autHor Varchar (40) not null,
- & gt; su; bmission_date date,
-& gt; Primary Key (runoob_id)
-> );
Query OK, 0 rows affected (0.16 sec)
mysql>

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

Use PHP script to create a data table

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

This function has two parameters and returns true when executed successfully, otherwise it returns false.

bool mysql_query(sql, connection);

Parameter Description

sql Required. Specifies the SQL query to be sent. Note: The query string should not end with a semicolon.

connection Optional. Specifies the SQL connection identifier. If not specified, the last opened connection is used.

Example

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

$dbhost ='localhost:3036';

$dbuser ='root';

$dbpass ='rootpassword ';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn )

{

die('Connection failed: '. mysql_error());

}

echo 'Connection successful
';

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

mysql_select_db('RUNOOB');

$retval = mysql_query( $sql, $conn );

if(! $retval )

{

die('Data table creation failed: '. mysql_error());

}

echo "Data table created successfully";

mysql_close( $conn);

?>

Run results: