How to create a data table in mysql

Mysql method to create a data table: 1. Create the table through the command prompt; 2. Use a PHP script to create the data table. The syntax is [mysqli_query(connection, query, resultmode);].

How to create a data table in mysql? The following article will introduce to you two methods of creating MySQL data tables. I hope it will be helpful to you.

SQL general syntax for creating a data table:

CREATE TABLE table_name (column_name column_type);

We can create a mysql data table in two ways.

1. Create a table through the command prompt

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

Example:

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>

Example analysis:

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

● AUTO_INCREMENT is defined as a self-increasing attribute, generally used for primary keys, and the value will automatically increase by 1.

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

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

Note: The MySQL command terminator is semicolon; . -> is a newline character, do not copy it.

2. Use PHP script to create a data table

We can use PHP’s mysqli_query() function to create a data table for an existing database.

This function has two parameters and returns TRUE when executed successfully, otherwise it returns FALSE.

Mysqli_query() Basic syntax of function:

mysqli_query(connection,query,resultmode);

Code example:

<?php
$dbhost = &#39;localhost:3306&#39;;  // mysql服务器主机地址
$dbuser = &#39;root&#39;;            // mysql用户名
$dbpass = &#39;123456&#39;;          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die(&#39;连接失败: &#39; . mysqli_error($conn));
}
echo &#39;连接成功<br />&#39;;
$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, &#39;RUNOOB&#39; );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die(&#39;数据表创建失败: &#39; . mysqli_error($conn));
}
echo "数据表创建成功\n";
mysqli_close($conn);
?>

3. View data table

Data table After it is created, you can view the structure of the table through the command prompt:

The above is the detailed content of How to create a data table in mysql. For more information, please follow other related articles on the PHP Chinese website!