Latest web development tutorials

MySQL create a data table

Create MySQL data tables require the following information:

  • Table Name
  • Table Field Name
  • The definition of each table field

grammar

Following is a table of the SQL data to create MySQL general syntax: 

CREATE TABLE table_name (column_name column_type);

The following example we will create a data table in w3big w3big_tbl database: 

w3big_tbl(
   w3big_id INT NOT NULL AUTO_INCREMENT,
   w3big_title VARCHAR(100) NOT NULL,
   w3big_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( w3big_id )
);

Analysis examples:

  • If you do not want the field to NULL can set the field attribute NOT NULL, when the operation of the database if the input data field is NULL, an error.
  • AUTO_INCREMENT is defined as a self-energizing properties, generally used for the primary key value is automatically incremented.
  • PRIMARY KEY keyword is used to define a primary key column. You can use multiple columns to define a primary key columns separated by a comma between.

Create a table from the command prompt

By the mysql> command window can be very simple to create a MySQL table. You can use the SQL CREATE TABLE statement to create a data table.

Examples

The following is to create a data table w3big_tbl instance: 

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

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

Create a data table using PHP script

You can use PHP's mysql_query () function to create a database table data already exists.

This function has two parameters, in the implementation of successful returns TRUE, otherwise returns FALSE.

grammar 

bool mysql_query( sql, connection );
parameter description
sql Required. SQL query to send provisions. Note: The query string should not end with a semicolon.
connection Optional. Provisions of SQL connection identifier. If not specified, the use of an open connection.

Examples

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

<html>
<head>
<meta charset="utf-8"> 
<title>创建 MySQL 数据表</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('连接失败: ' . mysql_error());
}
echo '连接成功<br />';
$sql = "CREATE TABLE w3big_tbl( ".
       "w3big_id INT NOT NULL AUTO_INCREMENT, ".
       "w3big_title VARCHAR(100) NOT NULL, ".
       "w3big_author VARCHAR(40) NOT NULL, ".
       "submission_date DATE, ".
       "PRIMARY KEY ( w3big_id )); ";
mysql_select_db( 'w3big' );
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('数据表创建失败: ' . mysql_error());
}
echo "数据表创建成功\n";
mysql_close($conn);
?>
</body>
</html>