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.
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
)ENGINE=InnoDB DEFAULT CHARSET=utf8
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:
1. NOT NULL - Each row must contain a value (cannot be empty), null value is not allowed.
2. DEFAULT value - Set the default value
3. UNSIGNED - Use unsigned numeric type, 0 and positive numbers
4. AUTO INCREMENT - Set the value of the MySQL field It will automatically increase by 1
each time when new records are added. 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.
6. ENGINE=InnoDB - Set the database engine to InnoDB, which is used by default starting from MySQL 5.6. Each MySQL data table can be set up with a different Storage Engine (storage engine). There are two main engines used: MyISAM and InnoDB (default).
7. DEFAULT CHARSET=utf8 - Set the default character set of the database to utf8
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 - process-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);
?>Instance (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;
?>Create table Principle
Generally speaking, there are the following precautions when creating a database table:
1. Correspondence between original record data and table
2. Table The name and field name should follow the naming syntax and should have a clear meaning
3. Specify the data type of the field
4. Specify other attributes of the field such as whether it is non-empty and whether it has a default value
5. Define the attributes of the table such as primary and foreign keys, constraints, indexes, etc.
6. Relationship with other tables
Limited by space and to control the difficulty of the tutorial, we will not discuss it here. Start too many discussions.
Use phpMyAdmin to operate
In the previous chapter, we mentioned that directly opening phpMyAdmin and entering commands to create a new database and query the database,
Here we can also use commands to perform other basic operations on database tables:
Delete database table-DROP TABLE db_name
Modify table name-ALTER TABLE db_name RENAME new_db_name
Next Section