MySQL basic use (1) DDL and DML statements

Relevant learning recommendations: php programming (video), mysql tutorial

##Part 1 In the tutorial, we introduced the installation of MySQL and how to connect and manage the MySQL database on the client. Today we will briefly review the commonly used SQL statements in daily life, using phpMyAdmin as a GUI tool as an example for demonstration.

SQL statements are generally divided into three parts:

DDL (Data Definition Language, data definition language)
• DML (Data Manipulation Language, data manipulation language )
• DCL (Data Control Language, data control language)

DDL

DDL statements are mainly aimed at object operations in the database. These objects include databases and data tables. , indexes, columns, views, etc. These operations include creation, deletion, renaming, etc.

Create a new database

To create a database, you can use the

CREATE DATABASE statement. However, writing SQL statements is too cumbersome. GUI tools provide buttons for corresponding visual operations, which is more For convenience, we take phpMyAdmin as an example. Click "New" in the left panel, then fill in the database name and encoding information in the form on the right panel, and finally click the "Create" button to create a new database:

If executed through a SQL statement, the corresponding SQL statement is:

CREATE DATABASE `test` DEFAULT CHARACTER SET = `utf8mb4`;

The encoding set here is

utf8mb4 Mainly to support the storage of Chinese characters and Emoji emoticons.

Database renaming and deletion

For the created database, you can modify the database name by renaming the database, or click the delete link to delete. These are located on the right after selecting the specified database. In the "Operations" panel of the navigation bar at the top of the panel:

Renaming actually involves deleting and creating a new database.

New data table

After creating the database, you will enter the create data table interface by default. We create a new data table named

post, click "Execute" in the lower right corner ” button to save:

Next, we enter the data table field (column) creation page. We first need to set a primary key ID field. When selecting the index as PRIMARY (primary key Index), the index creation window will pop up:

Click "Execute" to create the index and close the window, and then continue to set the field information:

Currently, four fields are created. If you want to add a new field, you can add it through the top The add function is added to the right side of the data table name. Here we need to distinguish different field types. The ID field is generally an integer number, so the type is set to INT. The article title is generally a string, so the type is set. It is VARCHAR, the article content is long text, the type is set to TEXT, and the last created_at field stores the creation time, so the type is set to DATETIME .

Set the storage engine to InnoDB in the table structure options. Before saving, you can preview the SQL statement for creating the post table through the "Preview SQL Statement" button:

Then click the save button in the lower right corner of the page to save the data table settings, and you can enter the data table structure page:

We can click on the corresponding field of each field Use the modification link to modify this field. If you want to modify the entire table, you can complete it through the "Operation" navigation at the top (deleting and clearing the table is also done here, and you can see the corresponding operation options by scrolling down to the bottom):

Of course all these operations can be completed through corresponding SQL statements, but it is more intuitive and faster through the graphical interface.

Data table index

We will introduce the type, creation and maintenance of data table index later in conjunction with actual projects, and will not expand on it here.

DML

DML statements are mainly aimed at the addition, deletion, modification, and query operations of data tables, that is, INSERT, DELETE, UPDATE, and SELECT operations on data tables.

Insert statement

After creating a new data table, you can insert data through INSERT INTO. Here we can also demonstrate through phpMyAdmin and select post# on the left panel. ## Data table, click the "Insert" top navigation on the right, fill in the field value in the form field, the ID is an auto-increment field and can be left blank, and finally click the "Execute" button to save:

After the insertion is successful, you can see the corresponding SQL insertion statement:

Then click the "Browse" navigation bar at the top to see the inserted record:

查询语句

你也可以通过「SQL」导航进入 SQL 查询面板通过 SELECT 语句进行查询：

不指定查询条件默认返回所有查询结果，你也可以通过 WHERE 子句指定查询条件返回特定结果：

SELECT * FROM `post` WHERE id = 1;

还可以指定要查询的字段：

SELECT id, title, content FROM `post`;

另外，还可以通过 ORDER BY 语句对查询结果进行排序：

SELECT * FROM `post` ORDER BY id DESC;

更新语句

我们可以通过「浏览」界面每一条记录左侧的「编辑」功能修改每条记录的字段值：

也可以在「SQL」界面通过 UPDATE 语句进行更加复杂的自定义修改，点击「UPDATE」按钮，会在输入框填充更新语句模板，按需进行填写即可：

点击「执行」按钮进行更新，就可以看到修改后的字段值了，在进行 UPDATE 更新时，需要特别关注 WHERE 子句，因为如果没有设置 WHERE 条件，会更新整张表。

删除语句

要删除单条记录，可以通过「浏览」界面记录左侧的删除按钮删除，也可以在「SQL」面板通过 DELETE 语句进行更加复杂的自定义删除操作：

和 UPDATE 一样，如果没有通过 WEHRE 子句设置删除条件，也会删除整张表记录，所以在执行删除操作前需要格外注意。

要清空整张表记录，并将自增 ID 重置为 1，需要在「操作」面板通过 TRUNCATE 操作完成：

以上就是数据表增删改查的基本操作实现。

本文来自于https://xueyuanjun.com/post/21655

想了解更多相关文章，敬请关注php mysql栏目！

The above is the detailed content of MySQL basic use (1) DDL and DML statements. For more information, please follow other related articles on the PHP Chinese website!