How to add, delete, modify and query table data in MySQL?

In mysql, you can use the SELECT statement to query table data, the INSERT statement to add table data, the UPDATE statement to modify table data, and the DELETE statement to delete table data.

Querying mysq table data

In MySQL, you can use the SELECT statement to Query data. Querying data refers to using different query methods to obtain different data from the database according to needs. It is the most frequently used and important operation.

The syntax format of SELECT is as follows:

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

The meaning of each clause is as follows:

• ##{*|b44f51d0b443c93502271d3d391e4df2 } A field list containing the asterisk wildcard character, indicating the name of the field to be queried.

##05cee8b6ad5efd33489a0be0260a7bbc, a31a6b5c4a9ac86f75409c3950db0e23…, Table 1 and Table 2 represent the source of query data, which can be single or multiple.
WHERE ffbeece48539e6983ff3249db04f27c9 is optional. If selected, the query data must meet the query conditions.
GROUP BY3b26370eed070b4e2af74808aa8f2dee, this clause tells MySQL how to display the queried data and group it according to the specified field.
[ORDER BY0f5333100010744a1571ca8552350494], this clause tells MySQL in what order to display the queried data. The possible sorting is ascending order (ASC) and descending order (DESC ), which is ascending by default.

• ##[LIMIT[ab76cfca1a1dc7ff1291dcdf873f72ec,]bc984d207842008469e14f06321b6461], this clause tells MySQL to display the number of queried data items each time.

Example: Specified fields in the query table

The syntax format of a certain field in the query table is:

SELECT < 列名 > FROM < 表名 >;

Query the names of all students in the name column of the tb_students_info table. The SQL statement and running results are shown below.

mysql> SELECT name FROM tb_students_info;
+--------+
| name   |
+--------+
| Dany   |
| Green  |
| Henry  |
| Jane   |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
+--------+
10 rows in set (0.00 sec)

The output shows all data under the name field in the tb_students_info table.

Use the SELECT statement to obtain data under multiple fields. You only need to specify the field name to be searched after the keyword SELECT. Different field names are separated by commas "," after the last field. There is no need to add commas. The syntax format is as follows:

SELECT <字段名1>,<字段名2>,…,<字段名n> FROM <表名>;

Add mysq table data

After the database and table are successfully created, you need to add Insert data into the table. In MySQL, you can use the INSERT statement to insert one or more rows of tuple data into an existing table in the database.

Basic syntax

The INSERT statement has two syntax forms, namely the INSERT…VALUES statement and the INSERT…SET statement.

1) INSERT...VALUES statement

INSERT VALUES 的语法格式为：
INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];

The syntax is explained as follows.

722e3d59fd24604761db25f00f9b264f: Specify the name of the table to be operated on.

• 895a828a5767d0ff83c19d6a93c7da7c: Specify the column name into which data needs to be inserted. If data is inserted into all columns in the table, all column names can be omitted, and INSERTccc43248daffbac9770dee47fdaff697VALUES(…) can be used directly.

• VALUES or VALUE clause: This clause contains the list of data to be inserted. The order of data in the data list should correspond to the order of columns.

• 2) INSERT...SET statement

The syntax format is:

INSERT INTO <表名>
SET <列名1> = <值1>,
        <列名2> = <值2>,
        …

This statement is used to directly specify the corresponding values ​​for certain columns in the table. The column value, that is, the column name of the data to be inserted is specified in the SET clause. col_name is the specified column name, and the equal sign is followed by the specified data. For unspecified columns, the column value will be specified as the default value of the column. .

It can be seen from the two forms of INSERT statement:

Use INSERT...VALUES statement to insert one row of data or multiple rows of data into the table;

• Use the INSERT…SET statement to specify the value of each column in the inserted row, or to specify the values ​​of some columns;

• INSERT…SELECT statement Insert data from other tables into the table.

• The INSERT…SET statement can be used to insert the values ​​of some columns into the table, which is more flexible;

• INSERT…VALUES statement Multiple pieces of data can be inserted at one time.

• In MySQL, processing multiple inserts with a single INSERT statement is faster than using multiple INSERT statements.

When using a single INSERT statement to insert multiple rows of data, you only need to enclose each row of data in parentheses.

Modification of mysq table data

In MySQL, you can use the UPDATE statement to modify and update the data of one or more tables.

Basic syntax of the UPDATE statement

Use the UPDATE statement to modify a single table. The syntax format is:

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

The syntax description is as follows:

b5715d1c5343acc275feb1c04543e303：指定要删除数据的表名。

ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。

WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。

LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。

注意：在不使用 WHERE 条件的时候，将删除所有数据。

删除表中的全部数据

实例：删除 tb_courses_new 表中的全部数据，输入的 SQL 语句和执行结果如下所示。

mysql> DELETE FROM tb_courses_new;
Query OK, 3 rows affected (0.12 sec)
mysql> SELECT * FROM tb_courses_new;
Empty set (0.00 sec)

推荐教程：mysql视频教程

The above is the detailed content of How to add, delete, modify and query table data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!