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:

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

##<Table 1>, <Table 2>…, Table 1 and Table 2 represent the source of query data, which can be single or multiple.
WHERE <Expression> is optional. If selected, the query data must meet the query conditions.
GROUP BY< Field >, this clause tells MySQL how to display the queried data and group it according to the specified field.
[ORDER BY< field>], 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[<offset>,]<row count>], this clause tells MySQL to display the number of queried data items each time.

SELECT < 列名 > FROM < 表名 >;

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.

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

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.

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

• <Column Name>: 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<table name>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

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

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

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

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

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!