| Category | Detailed explanation |
Basic syntax | create table table name (field name 1 field type,....field name n field type n); |
| Example | create table user(username varchar(20),password varchar(32)); |
|
## Example
Create a table named user. The first field is username. The field type of the table is varchar and the length is 32 characters. The second field is password, the type is also varchar, and the length is also 32 characters. | |
Example
We will create a table named "MyGuests" with 5 columns: "id", "firstname", "lastname", "email" and "reg_date" creation code
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 on creating the table above:
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.
· DEFAULT value - Set the default value
· UNSIGNED - Use unsigned numeric type, 0 and positive numbers
· AUTO INCREMENT - Set the value of the MySQL field in the new 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.
Example
The following example shows how to create a table in PHP:
<?php
header("Content-type:text/html;charset=utf-8"); //设置编码
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test";
// 创建连接
$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);
?>The above example is We created a table named MyGuests in a database named "test", which has 5 columns, namely "id", "firstname", "lastname", "email" and "reg_date":
Program running result:
Data table MyGuests was created successfully
Introduction to Commonly Used Tools
MySQL We can use official tools and third-party tools for management, so we don’t have to remember some complicated things The SQL statement is completed without having to memorize it. For example: permissions, table creation, backup, etc. Direct use of visual tools
is more conducive to improving work efficiency.
Commonly used tools are:
phpMyAdmin (Chinese, recommended)
Navicat (Chinese, recommended)
mysql workbench (English, official product, recommended when designing E-R diagrams)
phpMyAdmin
##phpMyAdmin is a PHP-based, Web-Base is a MySQL database management tool built on the website host, allowing administrators to use the Web interface to manage MySQL databases. This web interface can be a better way to enter complex SQL syntax in a simple way, especially to handle the import and export of large amounts of data. One of the greater advantages is that phpMyAdmin is executed on the web server like other PHP programs, but you can use the HTML pages generated by these programs anywhere, which is to remotely manage the MySQL database, which is convenient Create, modify, and delete databases and information tables. You can also use phpMyAdmin to create commonly used PHP syntax to facilitate the correctness of the SQL syntax required when writing web pages. Your server directly runs the php environment. Download the installation package, unzip the access address and start using it. You can also access it by entering http://localhost/phpmyadmin/ on the web page. Enter your user name and password and enter
where we can do some related things. Database operations can greatly reduce the time we spend writing SQL statements. Explore it yourself. Go to Baidu to search for the other two tools, download and install them, I believe you can operate them
##Next Section
<?php
header("Content-type:text/html;charset=utf-8"); //设置编码
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test";
// 创建连接
$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);
?>
