What is the basic statement of database addition, deletion, modification and query?-1024programmer

Basic statements for addition, deletion, modification and query in the database: “INSERT INTO table name field list VALUES (value list)”, “DELETE FROM table name WHERE clause”, “UPDATE table name SET column=value WHERE clause” , “SELECT * FROM tablename”.

(Recommended tutorial: mysql video tutorial)

Database Add, delete, modify and check basic statements

Increase data in the database

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

Grammar format:

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

If the data is a character type, you must use single or double quotes, such as: “value”.

Table name: Specify the name of the table being operated.

Column name: Specify the column name that needs to insert data. If you want to insert data into all columns in the table, all column names can be omitted, just use INSERT

VALUES(…) directly.

VALUE clause: This clause contains a list of data to be inserted. The order of the data in the data list should correspond to the order of the columns.

Example: Insert a new record in the tb_courses table, the course_id value is 1, the course_name value is “Network”, the course_grade value is 3, and the info value is “Computer Network” .

Check the tb_courses table before inserting

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

The query result shows that the content of the current table is empty and there is no data. Next, the operation of inserting data will be performed. The input SQL statement and execution process are as follows.

mysql> INSERT INTO tb_courses
     -> (course_id, course_name, course_grade, course_info)
     -> VALUES(1, & # 39; Network & # 39;, 3, & # 39; Computer Network & # 39;);
 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 inserting data, all fields of the tb_courses table are specified, so new values will be inserted for each field.

Explanation:

The order of the column names after the INSERT statement may not be the order of the tb_courses table definition, That is, when inserting data, it does not need to be inserted in the order defined by the table, as long as the order of the values is the same as that of the column fields.
When using INSERT to insert data, the column name list column_list is allowed to be empty. At this time, the value list needs to specify a value for each field of the table, and the order of the values must be the same as the data The fields in the table are defined in the same order.

Note: Although the column name of the inserted data can be ignored when using INSERT to insert data, if the value does not contain the column name, the value after the VALUES keyword not only requires Complete, and the order must be the same as the order of the columns when the table was defined. If the structure of the table is modified, adding, deleting or changing the position of the column, these operations will make the order of inserting data in this way also change at the same time. If you specify column names, you will not be affected by changes in the table structure.

Delete data from the database

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

The syntax format is:

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

Delete specified data:

DELETE FROM table name WHERE column name=value

Grammar description As follows:

Table name: Specify the name of the table to delete data.
ORDER BY clause: Optional. Indicates that when deleting, each row in the table will be deleted in the order specified in the clause.
WHERE clause: Optional. Indicates that the deletion condition is limited for the deletion operation. If this clause is omitted, it means that all rows in the table are deleted.
LIMIT clause: Optional. Used to tell the server the maximum number of rows to delete before control commands are returned to the client.

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

Example 1: Delete all the data in the table

Delete all the data in the tb_courses_new table, the entered SQL statement and the execution result are as follows.

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 the table according to conditions

In the tb_courses_new table, delete the record whose course_id is 4, the input SQL statement and the execution result are as follows shown.

<pQuery conditions.

GROUP BY, this clause tells MySQL how to display the queried data and group it by the specified field.

[ORDER BY], this clause tells MySQL in what order to display the queried data, and the sorting that can be performed is ascending (ASC) and descending ( DESC), which is ascending by default.

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

Let’s introduce some simple SELECT statements first, and I won’t elaborate on restrictions such as WHERE, GROUP BY, ORDER BY, and LIMIT.

1. Query all fields in the table

Query 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 look up the data of all fields in the table, the syntax format is as follows:

SELECT * FROM table name;

When you use “*” to query, you can only arrange according to the order of the fields in the data table, and you cannot change the order of the fields.

Example: query the data of all fields from the tb_students_info table, the SQL statement and the running results are as follows.

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

The results show that when the “*” wildcard is used, all columns will be returned, and the data columns will be displayed in the order in which the table was created.

Note: In general, unless you need to use all the field data in the table, it is best not to use the wildcard “*”. While using wildcards can save time entering query statements, fetching data for columns you don’t need often slows down the efficiency of your queries and the applications you use. The advantage of using “*” is that when the names of the required columns are not known, they can be obtained by “*”.

2. The specified field in the query table

The syntax format of a certain field in the query table is:

SELECT  FROM

;

Example:

Query the names of all students in the name column in the tb_students_info table, the SQL statement and the running results are as follows shown.

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.

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 “,” and the last field There is no need to add a comma after it, and the syntax format is as follows:

SELECT ,,…, FROM

;

Example:

Get the three columns of id, name and height from the tb_students_info table, the SQL statement and the running results are as follows.

mysql> SELECT id,name,height
     -> FROM tb_students_info;
 +----+--------+--------+
 | id | name | height |
 +----+--------+--------+
 | 1 | Dany | 160 |
 | 2 | Green | 158 |
 | 3 | Henry | 185 |
 | 4 | Jane | 162 |
 | 5 | Jim | 175 |
 | 6 | John | 172 |
 | 7 | Lily | 165 |
 | 8 | Susan | 170 |
 | 9 | Thomas | 178 |
 | 10 | Tom | 165 |
 +----+--------+--------+
 10 rows in set (0.00 sec)

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

For more programming knowledge, please visit: Programming Video! !

The above is what is the basic statement of database addition, deletion, modification and query? For more details, please pay attention to other related articles on 1024programmer.com!

This article is from the internet and does not represent1024programmerPosition, please indicate the source when reprinting:https://www.1024programmer.com/what-is-the-basic-statement-of-database-addition-deletion-modification-and-query/