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:

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

##, …, Table 1 and Table 2 represent the source of query data, which can be single or multiple.
WHERE is optional. If selected, the query data must meet the query conditions.
GROUP BY, this clause tells MySQL how to display the queried data and group it according to the specified field.
[ORDER BY], 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[,]], 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.
: Specify the name of the table to be operated on.

• : 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 INSERT
VALUES(…) 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:
SET clause: used to specify the column name and its column value to be modified in the table. Among them, each specified column value can be an expression or the default value corresponding to the column. If a default value is specified, the column value can be represented by the keyword DEFAULT.
WHERE clause: Optional. Used to limit the rows in the table to be modified. If not specified, all rows in the table will be modified.

ORDER BY 子句：可选项。用于限定表中的行被修改的次序。

LIMIT 子句：可选项。用于限定被修改的行数。

注意：修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。

实例：修改表中的数据

在 tb_courses_new 表中，更新所有行的 course_grade 字段值为 4，输入的 SQL 语句和执行结果如下所示。

mysql> UPDATE tb_courses_new
    -> SET course_grade=4;
Query OK, 3 rows affected (0.11 sec)
Rows matched: 4  Changed: 3  Warnings: 0
mysql> SELECT * FROM tb_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            4 | Computer Network |
|         2 | Database    |            4 | MySQL            |
|         3 | Java        |            4 | Java EE          |
|         4 | System      |            4 | Operating System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)

mysq表数据的删除

在 MySQL 中，可以使用 DELETE 语句来删除表的一行或者多行数据。

删除单个表中的数据

使用 DELETE 语句从单个表中删除数据，语法格式为：

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

语法说明如下：

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

• 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!