How to add, delete, modify and query table data in MySQL?-Mysql Tutorial-php.cn

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.

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

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>]
]

Copy after login

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.

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[,]], 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 < 表名 >;

Copy after login

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)

Copy after login

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 <表名>;

Copy after login

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) ];

Copy after login

The syntax is explained as follows.

: 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 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>,
        …

Copy after login

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 子句]

Copy after login

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)

Copy after login

mysq表数据的删除

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

删除单个表中的数据

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

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

Copy after login

语法说明如下：

<表名>：指定要删除数据的表名。
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)

Copy after login

推荐教程：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!

Related labels：

mysql

source：php.cn

Previous article：How to import data into mysql Next article：How to check if mysql is installed on my computer?

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn