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

Home

Database

Mysql Tutorial

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

青灯夜游

Oct 05, 2020 pm 12:16 PM

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.

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!

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

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Assassin's Creed Shadows: Seashell Riddle Solution

1 months ago By DDD

What's New in Windows 11 KB5054979 & How to Fix Update Issues

3 weeks ago By DDD

Where to find the Crane Control Keycard in Atomfall

1 months ago By DDD

How to fix KB5055523 fails to install in Windows 11?

2 weeks ago By DDD

InZoi: How To Apply To School And University

3 weeks ago By DDD

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Where is the login entrance for gmail email?

7756

Java Tutorial

1643

CakePHP Tutorial

1399

Laravel Tutorial

1293

PHP Tutorial

1234

Related knowledge

MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

How to connect to the database of apache Apr 13, 2025 pm 01:03 PM

Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.

Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

How to start mysql by docker Apr 15, 2025 pm 12:09 PM

The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

How to install mysql in centos7 Apr 14, 2025 pm 08:30 PM

The key to installing MySQL elegantly is to add the official MySQL repository. The specific steps are as follows: Download the MySQL official GPG key to prevent phishing attacks. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting

See all articles