Article directory
MySQL databaseHow to create a data table?MySQLCreate data table command/statement/syntax in
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 data tables can be easily created through the mysql> command window.You can use SQL statement CREATE TABLE to create the data table.
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 of data from 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);
参数 | Description |
---|---|
connection | Required.Specifies the MySQL connection to use. |
Query | Required, specifies the query string. |
result mode | Optional.a constant.Can be any of the following values:
|
Examples
The following example uses a PHP script to create a data table:
Create 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); ?>
After successful execution, you can view the table structure through the command line.
Hope Chen Weiliang Blog ( https://www.chenweiliang.com/ ) shared "How to create a data table in MySQL database? Create Data Table Commands/Statements/Syntax in MySQL" to help you.
Welcome to share the link of this article:https://www.chenweiliang.com/cwl-457.html
Welcome to the Telegram channel of Chen Weiliang's blog to get the latest updates!
📚 This guide contains huge value, 🌟This is a rare opportunity, don’t miss it! ⏰⌛💨
Share and like if you like!
Your sharing and likes are our continuous motivation!