PHP creates MySQL table

PHP Create MySQL table

A data table has a unique name and consists of rows and columns.

Creating a MySQL table using MySQLi and PDO

The CREATE TABLE statement is used to create a MySQL table.

CREATE TABLE tbl_name (col_name column_definition);

Creating a table must have at least one field definition, use spaces to separate field names and types, and use commas to separate multiple fields

After the database is created, we can execute the following statement to get the table creation statement

SHOW CREATE TABLE tbl_name;

or execute the following statement to print out the table structure

DESC tbl_name;

CREATE TABLE my_tbl (
age INT,
name VARCHAR(100)
);

The above statement creates a table named my_tbl Table, the table has two fields, one is named age (age), the saved type is integer, the other field is named name (name), the type is a string with a maximum length of 100 characters

Please use SHOW or DESC to view the table creation statement or table structure

We will create a table named "MyGuests" with 5 columns: "id", "firstname", "lastname", "email" and "reg_date":

CREATE TABLE MyGuests (
 id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 firstname VARCHAR(30) NOT NULL,
 lastname VARCHAR(30) NOT NULL,
 email VARCHAR(50),
 reg_date TIMESTAMP
 )

Notes in the above table:

The data type specifies what type of data the column can store. For complete data types please refer to our Data Types Reference Manual.

After setting the data type, you can specify the attributes of other options for each column:

·        NOT NULL - Each row must contain a value (cannot be empty), the null value is not allowed.

·                                                                                                                                                           use being having numeric type out using 0 to be used to be able to be used to be needed. It will automatically increase by 1

each time it is recorded. PRIMARY KEY - Set the unique identifier of each record in the data table. Typically the column's PRIMARY KEY is set to the ID value, used with AUTO_INCREMENT.

Each table should have a primary key (this column is the "id" column), and the primary key must contain a unique value.

The following example shows how to create a table in PHP:

Example (MySQLi - object-oriented)

<?php
 $servername = "localhost";
 $username = "username";
 $password = "password";
 $dbname = "myDB";
 
 // 创建连接
 $conn = new mysqli($servername, $username, $password, $dbname);
 // 检测连接
 if ($conn->connect_error) {
     die("连接失败: " . $conn->connect_error);
 } 
 
 // 使用 sql 创建数据表
 $sql = "CREATE TABLE MyGuests (
 id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
 firstname VARCHAR(30) NOT NULL,
 lastname VARCHAR(30) NOT NULL,
 email VARCHAR(50),
 reg_date TIMESTAMP
 )";
 
 if ($conn->query($sql) === TRUE) {
     echo "Table MyGuests created successfully";
 } else {
     echo "创建数据表错误: " . $conn->error;
 }
 
 $conn->close();
 ?>

Example (MySQLi - procedure-oriented)

<?php
 $servername = "localhost";
 $username = "username";
 $password = "password";
 $dbname = "myDB";
 
 // 创建连接
 $conn = mysqli_connect($servername, $username, $password, $dbname);
 // 检测连接
 if (!$conn) {
     die("连接失败: " . mysqli_connect_error());
 }
 
 // 使用 sql 创建数据表
 $sql = "CREATE TABLE MyGuests (
 id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
 firstname VARCHAR(30) NOT NULL,
 lastname VARCHAR(30) NOT NULL,
 email VARCHAR(50),
 reg_date TIMESTAMP
 )";
 
 if (mysqli_query($conn, $sql)) {
     echo "数据表 MyGuests 创建成功";
 } else {
     echo "创建数据表错误: " . mysqli_error($conn);
 }
 
 mysqli_close($conn);
 ?>

Example (PDO)

<?php
 $servername = "localhost";
 $username = "username";
 $password = "password";
 $dbname = "myDBPDO";
 try {
     $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
     // 设置 PDO 错误模式，用于抛出异常
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
     // 使用 sql 创建数据表
     $sql = "CREATE TABLE MyGuests (
     id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
     firstname VARCHAR(30) NOT NULL,
     lastname VARCHAR(30) NOT NULL,
     email VARCHAR(50),
     reg_date TIMESTAMP
     )";
 
     // 使用 exec() ，没有结果返回 
     $conn->exec($sql);
     echo "数据表 MyGuests 创建成功";
 }
 catch(PDOException $e)
 {
     echo $sql . "<br>" . $e->getMessage();
 }
 $conn = null;
 ?>

By the way, other table-related operations:

Delete table:

DROP TABLE tbl_name;

Modify table name:

ALTER TABLE tbl_name RENAME new_tbl_name;

Add fields:

ALTER TABLE tbl_name ADD col_name column_definition;

Added fields The default is the last column at the end of all columns. If you want to change it to the first column, you can write it as follows

ALTER TABLE tbl_name ADD col_name column_definition FIRST;

If you want to specify a specific location, you can write it as follows

ALTER TABLE tbl_name ADD col_name column_definition AFTER other_col_name;

Delete fields:

ALTER TABLE tbl_name DROP col_name;

Modify field attributes:

ALTER TABLE tbl_name MODIFY col_name column_definition;

Modify field names and attributes:

ALTER TABLE tbl_name CHANGE col_name_from col_name_to column_definition;

modify can only modify the field definition, while change can modify the field name