Shulou(Shulou.com)06/01 Report--

This article mainly introduces how mysql table data additions, deletions and changes, the text is very detailed, has a certain reference value, interested friends must read!

In mysql, you can query table data using SELECT statements, add table data using INSERT statements, modify table data using UPDATE statements, and delete table data using Delete statements.

Query of mysq table data

In MySQL, you can query data using SELECT statements. Querying data refers to obtaining different data from the database according to different query methods according to requirements. It is the most frequently used and most important operation.

SELECT syntax format is as follows:

SELECT{* | }[FROM , …[WHERE [GROUP BY [HAVING [{ }…]][ORDER BY ][LIMIT[,] ]]

where the clauses have the following meanings:

{*|} A list of fields containing asterisk wildcards indicating the name of the field to be queried.

Table 1 and Table 2 indicate the source of query data, which can be single or multiple.

WHERE is optional. If selected, the query data must satisfy the query criteria.

GROUP BY

This clause tells MySQL how to display the queried data grouped by the specified fields.

[ORDER BY

], this clause tells MySQL in what order to display the query data, can be sorted in ascending order (ASC) and descending order (DESC), the default is ascending order.

[LIMIT[,]], which tells MySQL how many pieces of data to display at a time.

Example: Query fields specified in a table

The syntax format of a 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 the running result are as follows.

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 the data under the name field in the tb_students_info table.

You can use SELECT declaration to obtain data under multiple fields. You only need to specify the name of the field you want to find after the keyword SELECT. Different field names are separated by commas. There is no need to add commas after the last field. The syntax format is as follows:

SELECT ,,…, FROM ;

increase in mysq table data

After the database and tables are created successfully, you need to insert data into the tables of the database. In MySQL, you can insert one or more rows of tuple data into an existing database table using INSERT statements.

basic syntax

INSERT statements have two syntax forms: INSERT…VALUES statements and INSERT…SET statements.

1)INSERT…VALUES statement

INSERT VALUES syntax format: INSERT INTO [ [ , … ] ]VALUES (value1) [… , (valuen) ];

The syntax is explained below.

: Specifies the name of the table being operated on.

: Specifies the column name for which data needs to be inserted. If you insert data into all columns in the table, all column names can be omitted and you can use INSERT VALUES (…) directly.

VALUES or VALUE clause: This clause contains the manifest of data to insert. The order of the data in the data list corresponds to the order of the columns.

2)INSERT…SET statement

The syntax format is:

INSERT INTO SET = , = , …

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

Two forms of INSERT statements can be seen:

Insert one row or more rows of data into a table using the INSERT…VALUES statement;

Use the INSERT…SET statement to specify values for each column in the inserted row or for some columns;

INSERT…SELECT statements insert data from other tables into a table.

Insert…SET statement can insert partial column values into the table, which is more flexible;

INSERT…VALUES statements can insert multiple pieces of data at once.

In MySQL, multiple inserts are processed faster with a single INSERT statement than with multiple INSERT statements.

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

Modification of mysq table data

In MySQL, you can use UPDATE statements to modify or update data in one or more tables.

Basic syntax for UPDATE statements

Modify a single table using the UPDATE statement with the syntax:

UPDATE SET Field1 = Value1 [, Field2 = Value2… ] [WHERE clause][ORDER BY clause] [LIMIT clause]

The syntax is explained as follows:

: Specifies the name of the table to update.

SET clause: Used to specify column names and column values to modify in a table. Where each specified column value can be an expression or a default value for that column. If default values are specified, column values can be represented with the keyword DEFAULT.

WHERE clause: optional. Used to restrict the rows in a table to be modified. If not specified, all rows in the table are modified.

ORDER BY clause: optional. Used to limit the order in which rows in a table are modified.

LIMIT clause: Optional. Used to limit the number of rows to be modified.

Note: When modifying multiple column values in a row of data, each value in the SET clause can be separated by commas.

Example: Modifying data in a table

In the tb_courses_new table, update the course_grade field value of all rows to 4. The SQL statement entered and the execution result are as follows:

mysql> UPDATE tb_courses_new -> SET course_grade=4;Query OK, 3 rows affected (0.11 sec)Rows matched: 4 Changed: 3 Warnings: 0mysql> 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)

Deletion of mysq table data

In MySQL, you can delete one or more rows of data from a table by using the Delete statement.

Delete data from a single table

Delete data from a single table using the Delete statement, syntax format:

Delete FROM [WHERE clause] [ORDER BY clause] [LIMIT clause]

The syntax is explained as follows:

: Specifies the name of the table from which data is to be deleted.

ORDER BY clause: optional. Indicates that when deleted, rows in the table are deleted in the order specified in the clause.

WHERE clause: optional. Indicates that the delete operation limits the delete condition. If this clause is omitted, it represents deleting all rows in the table.

LIMIT clause: Optional. Used to tell the server the maximum number of rows to delete before a control command is returned to the client.

Note: When the WHERE condition is not used, all data is deleted.

Delete all data from a table

Example: Delete all the data in tb_courses_new table, input SQL statement and execution result are shown below.

mysql> SELECT * FROM tb_courses_new; Empty set (0.00 sec)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) The above is all the contents of mysql how to add, delete and query table data. Thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.