Frontend database record operations and frontend database records

Frontend database record operations and frontend database records
* Directory [1] Insert record [2] update record [3] Delete record [4] query expression [5] Before result Processing

This article describes how to add, delete, modify, and query records in mysql.

 

Insert record

INSERT [INTO] tbl_name [(col_name,...)] {VALUES|VALUE} ({expr|DEFAULT},...),(...),...

First, create a simple data table

If col_name is omitted, all fields are assigned values in sequence. Because the id field is automatically numbered, this field can be assigned a value of NULL or DEFAULT.

Separated by commas (,), multiple records can be written at a time. values can be expressed using expressions.

There is no BOOLEAN type in the database. If the declared type is BOOLEAN, it will be converted to TINYINT type, true to 1, false to 0

 

Method 2

INSERT [INTO] tb1_name SET col_name={expr|DEFAULT},...

The difference with the first method is that this method can use SubQuery, and only one record can be inserted at a time.

 

Method 3

INSERT [INTO] tb1_name [(col_name,...)] SELECT ...

This method can insert query results to a specified data table.

 

Update record

UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition]

Multiple records can be updated at a time. When the WHERE condition is omitted, the values of all records will be updated.

Next we will increase the age of all people to 5 years.

Next, we will change the age of all people to the original age minus the id value, and change the gender of all people to 0.

The age of all persons whose IDs are even numbers plus 10 years old

Delete record

DELETE FROM tbl_name [WHERE where_condition]

[Note] after a record is deleted, a new record is inserted. The automatic number will not be added to the number of the deleted record, but will continue to be added based on the maximum number of the original record.

Query expression

SELECT select_expr [,select_expr...][FROM tbl_references[WHERE where_condition][GROUP BY {col_name | position} [ASC | DESC],...][HAVING where_condition][ORDER BY {col_name | expo | position}  [ASC | DESC],...][LIMIT {[offset,] row_count | row_count OFFSET offset}]]

Each expression in the query expression indicates the column to be searched. Each expression must have at least one column. Separate multiple columns with commas

The order of the query expression can be different from that of the fields in the original table.

When multi-table join is used, fields with the same name may exist in different tables. If you write the fields directly, you cannot tell which data table is the field. Add a data table before the field name to identify which data table belongs.

Asterisk (*) indicates all columns. Tbl_name. * indicates all columns in the naming table.

You can use [AS] alias_name to assign an alias to a query expression. The alias can be used for group by, order by, and HAVING clauses.

[Note] when you use a query expression to set an alias for query, the AS can be used or not used. However, if you do not use it, it may cause ambiguity.

SELECT id username FROM users;

Mysql resolves the preceding statement as username as the id alias.

 

Result Processing

[GROUP BY {col_name | position} [ASC | DESC],...]

In the query result GROUP parameter, ASC is in ascending order, which is the default value; DESC is in descending order.

Col_name indicates the field name, and position indicates the position by number. For example, 1 indicates the field that appears for the first time in the SELECT statement.

Grouping Conditions

[HAVING where_condition]

When setting a group (HAVING), make sure that the grouping condition (where_condition) is either an aggregate function (max, min, avg, count, sum ), either the field must be a query field in the SELECT statement. Otherwise, an error is returned.

Group sorting

[ORDER BY {col_name | expo | position}  [ASC | DESC],...]

You can use order by to sort the query results.

At the same time, several conditions can be used for sorting and priority selection in the input order.

Limit results

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

LIMIT query results (LIMIT) by default, return all searched results

If there is only one number after LIMIT, it indicates that the first number is returned and the number of corresponding numbers is returned.

The SELECT statement starts from 0 by default. To return the number from the third entry, use the offset parameter and the row_count parameter together.

[Supplement]

INSERT [INTO] tbl_name [(col_name,...] SELECT ...

Unlike the insert record method described at the beginning, this method can now store the search results to the specified data table.