How to create a data table in a MySQL database? Create data table command/statement/syntax in MySQL

How to create a table in a MySQL database ? MySQL commands/statements/syntax for creating a table.

MySQL create data table

Creating a MySQL data table requires the following information:

• Table Name
• table field name
• define each table field

grammar

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

CREATE TABLE table_name (column_name column_type);

In the following example we will create the data table chenweiliang_tbl in the chenweiliang database:

CREATE TABLE IF NOT EXISTS `chenweiliang_tbl`(
   `chenweiliang_id` INT UNSIGNED AUTO_INCREMENT,
   `chenweiliang_title` VARCHAR(100) NOT NULL,
   `chenweiliang_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `chenweiliang_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Example analysis:

• If you don't want the field to be NULL The properties of the fields can be set as NOT NULL, when operating the database, if the data entered in this field isNULL , an error will be reported.
• AUTO_INCREMENT is defined as an auto-incrementing attribute, generally used for the primary key, and the value will be automatically incremented by 1.
• The PRIMARY KEY keyword is used to define a column as the primary key.You can use multiple columns to define the primary key, separated by commas.
• ENGINE sets the storage engine, and CHARSET sets the encoding.

Create table from command prompt

MySQL tables can be easily created using the mysql> command window. You can use the SQL statement CREATE TABLE to create tables.

Examples

The following is an example of creating a data table chenweiliang_tbl:

root@host# mysql -u root -p
Enter password:*******
mysql> use chenweiliang;
Database changed
mysql> CREATE TABLE chenweiliang_tbl(
   -> chenweiliang_id INT NOT NULL AUTO_INCREMENT,
   -> chenweiliang_title VARCHAR(100) NOT NULL,
   -> chenweiliang_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( chenweiliang_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql>

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

Create data table using PHP script

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

The function has two parameters and returns TRUE if the execution is successful, otherwise it returns FALSE.

grammar

mysqli_query(connection,query,resultmode);

Examples

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

<?
 php
 $dbhost = 'localhost:3306'; // mysql服务器主机地址
 $dbuser = 'root'; // mysql用户名
 $dbpass = '123456'; // mysql用户名密码
 $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
 if(! $conn )
 {
 die('连接失败: ' . mysqli_error($conn));
 }
 echo '连接成功<br />';
 $sql = "CREATE TABLE chenweiliang_tbl( ".
 "chenweiliang_id INT NOT NULL AUTO_INCREMENT, ".
 "chenweiliang_title VARCHAR(100) NOT NULL, ".
 "chenweiliang_author VARCHAR(40) NOT NULL, ".
 "submission_date DATE, ".
 "PRIMARY KEY ( chenweiliang_id ))ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
 mysqli_select_db( $conn, 'chenweiliang' );
 $retval = mysqli_query( $conn, $sql );
 if(! $retval )
 {
 die('数据表创建失败: ' . mysqli_error($conn));
 }
 echo "数据表创建成功\n";
 mysqli_close($conn);
 ?>