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

The editor will share with you how to write the basic sentences of database addition, deletion, modification and inquiry. I hope you will gain a lot after reading this article. Let's discuss it together.

Add, delete, modify and query basic statements in the database: "INSERT INTO table name field list VALUES (values list)", "DELETE FROM table name WHERE clause", "UPDATE table name SET column = value WHERE clause", "SELECT * FROM table name".

Basic statements of database addition, deletion, modification and query

Increase data in database

In MySQL, you can use the INSERT INTO statement to insert one or more rows of tuple data into an existing table in the database.

Syntax format:

INSERT INTO table name (column 1, column 2... Column name N) VALUES (value 1, value 2... Value N)

If the data is character type, you must use single or double quotation marks, such as "value".

Table name: specifies the name of the table being manipulated.

Column name: specifies the name of the column in which data needs to be inserted. If you insert data into all the columns in the table, all column names can be omitted and directly use INSERTVALUES (...) That's it.

VALUE clause: this clause contains the list of data to insert. The order of the data in the data list corresponds to the order of the columns.

Example: insert a new record in the tb_courses table with a course_id value of 1, a course_grade value of 3, a course_grade value of 3, and a value of "Computer Network".

View the tb_courses table before performing the insert operation

Mysql > SELECT * FROM tb_courses;Empty set (0.00 sec)

The query results show that the content of the current table is empty and there is no data, and then the operation of inserting data is performed. The input SQL statement and execution process are shown below.

Mysql > INSERT INTO tb_courses-> (course_id,course_name,course_grade,course_info)-> VALUES; Query OK, 1 rows affected (0.08 sec) mysql > SELECT * FROM tb_courses +-+ | course_id | course_name | course_grade | course_info | +-+ -+ | 1 | Network | 3 | Computer Network | +-+ 1 row in set (0.00 sec)

You can see that the record was inserted successfully. When you insert data, all fields of the tb_courses table are specified, so a new value is inserted for each field.

Description:

The order of column names after the INSERT statement may not be the order in which the tb_courses table is defined, that is, when inserting data, you do not need to insert data in the order defined by the table, as long as you ensure that the order of the values is the same as the order of the column fields.

When inserting data using INSERT, the column name list column_list is allowed to be empty, where values need to be specified for each field in the table, and the order of values must be the same as when the fields in the data table were defined.

Note: although the column name of the inserted data can be ignored when inserting data using INSERT, if the value does not contain a column name, the values after the VALUES keyword must not only be complete, but must also be in the same order as the columns when the table was defined. If the structure of the table is modified, columns are added, deleted, or relocated, which will change the order in which data is inserted in this way. If you specify a column name, it is not affected by changes in the table structure.

Database deletion data

In MySQL, you can use the DELETE statement to delete one or more rows of data from a table.

The syntax format is:

DELETE FROM table name [WHERE clause] [ORDER BY clause] [LIMIT clause]

Delete the specified data:

DELETE FROM table name WHERE column name = value

The syntax is as follows:

Table name: specifies the name of the table to delete the data.

ORDER BY clause: optional. When you delete, the rows in the table are deleted in the order specified in the clause.

WHERE clause: optional. Indicates that the deletion condition is qualified for the delete operation, and if the clause is omitted, all rows in the table are deleted.

LIMIT clause: optional. Used to tell the server the maximum value of the deleted row before the control command is returned to the client.

Note: when the WHERE condition is not used, all data will be deleted.

Example 1: delete all data in the table

Delete all the data in the tb_courses_new table, enter the SQL statement and the execution result is shown below.

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)

Example 2: delete data in a table according to conditions

In the tb_courses_new table, delete the record with a course_id of 4, and enter the SQL statement and the execution result as shown below.

Mysql > DELETE FROM tb_courses-> WHERE course_id=4;Query OK, 1 row affected (0.00 sec) mysql > SELECT * FROM tb_courses +-+ | course_id | course_name | course_grade | course_info | +-+ -+ | 1 | Network | 3 | Computer Network | | 2 | Database | 3 | MySQL | | 3 | Java | 4 | Java EE | +- -+-+ 3 rows in set (0.00 sec)

As can be seen from the running results, the record with a course_id of 4 has been deleted.

Database modification data

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

The syntax format is:

UPDATE table name SET field = value [WHERE clause] [ORDER BY clause] [LIMIT clause]

Example:

UPDATE table name SET column name = new value WHERE column name = a value

The syntax is as follows:

Table name: used to specify the table name to update.

SET clause: used to specify the column name to be modified in the table and its column value. Where each specified column value can be an expression or a default value for that column. If the default value is specified, the DEFAULT keyword is available to indicate the column value.

WHERE clause: optional. Used to limit the rows in the 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 the table are modified.

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

Note: when you modify multiple column values for a row of data, each value of the SET clause can be separated by a comma.

Example 1: modify data in a table

In the tb_courses_new table, the course_grade field value for updating all rows is 4, and the SQL statement entered and the execution result are shown below.

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)

Example 1: modify the data in the table according to the condition

In the tb_courses table, update the record with a course_id value of 2, change the course_grade field value to 3.5, and change the course_name field value to "DB". The SQL statement entered and the execution result are shown below.

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

Note: make sure that the UPDATE ends with the WHERE clause, and specify the conditions that the updated record needs to meet through the WHERE clause. If you omit the WHERE clause, MySQL will update all rows in the table.

Database query data

In MySQL, you can use SELECT statements to query data. Query data refers to the use of different query methods to obtain different data from the database according to the requirements, which is the most frequent and important operation.

The syntax format of SELECT is as follows:

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

Among them, the meaning of each clause is as follows:

{* |} contains a list of fields with asterisk wildcards, indicating the name of the field to be queried.

,... Tables 1 and 2 represent the sources of query data, which can be single or multiple

WHERE is optional, and if selected, the query data must meet the query criteria.

GROUP BY

That tells MySQL how to display the queried data and group it by the specified field

[ORDER BY

This clause tells MySQL in what order the queried data is displayed, which can be sorted in ascending (ASC) and descending (DESC) order, which is ascending by default.

[LIMIT [,]], this clause tells MySQL to display the number of pieces of data that are queried each time.

Let's start with some simple SELECT statements, without going into details about restrictions such as WHERE, GROUP BY, ORDER BY, and LIMIT.

1. Query all fields in the table

Querying all fields refers to the data of all fields in the query table. MySQL provides the following two ways to query all fields in a table.

SELECT can use "*" to find data for all fields in the table. The syntax format is as follows:

SELECT * FROM table name

When using a "*" query, you can only arrange the fields in the order of the data table, not change the order of the fields.

Example: query the data for all fields from the tb_students_info table, and the SQL statement and run results are shown below.

Mysql > use test_db;Database changedmysql > SELECT * FROM tb_students_info +-+ | id | name | dept_id | age | sex | height | login_date | + -+-+ | 1 | Dany | 1 | 25 | F | 2015 | 09-10 | 2 | Green | 3 | 23 | F | 2016-10-22 | 3 | Henry | 2 | 23 | M | 2015-05-31 | 4 | Jane | 1 | 22 | F | 2016-12-20 | 5 | | Jim | 1 | 24 | M | 2016 | 01-15 | | 6 | John | 2 | 21 | M | 2015 | 11-11 | | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 2015-10-01 | 9 | Thomas | 3 | 22 | M | 2016-06-07 | | | 10 | Tom | 4 | 23 | M | 165,165 | 2016-08-05 | +-+ 10 rows in set (0.26sec) |

The result shows that when you use the "*" wildcard, all columns are returned, and the data columns are displayed in the order in which the table was created.

Note: in general, it is best not to use the wildcard "*" unless you need to use all the field data in the table. Although using wildcards can save time entering query statements, getting unwanted column data usually reduces the efficiency of the query and the application used. The advantage of using "*" is that when you don't know the names of the columns you want, you can get them through "*".

2. Query the fields specified in the table

The syntax format for a field in the query table is:

SELECT

FROM

Example:

Query the names of all the students in the name column in the tb_students_info table, and the SQL statement and run 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 the data under the name field in the tb_students_info table.

You can use the SELECT declaration to obtain the data under multiple fields. You only need to specify the field name to be looked up after the keyword SELECT. Different field names are separated by commas "and". There is no need to add a comma after the last field. The syntax format is as follows:

SELECT,... , FROM

Example:

Get the id, name, and height columns from the tb_students_info table, and the SQL statement and run results are shown below.

Mysql > SELECT id,name,height-> FROM tb_students_info + -. | 178 | 10 | Tom | 165 | +-+ 10 rows in set (0.00 sec)

The output shows all the data under the id, name, and height fields in the tb_students_info table.

After reading this article, I believe you have a certain understanding of how to write the basic sentences of database addition, deletion, modification and query. I would like to know more about it. Welcome to follow the industry information channel. Thank you for your reading!

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.