Record operation of the front-end learning Database

xTable of Contents [1] Insert record [2] update record [3] Delete record [4] query expression [5] result processing before

This article will detail MySQL on record additions and deletions

Inserting records

INSERT [into] tbl_name [(Col_name,...)] {values| VALUE} ({expr| DEFAULT},...), (...),...

First, create a simple data table

If you omit col_name, it means that all the fields are assigned sequentially in order. Because the ID field is automatically numbered, the field can be assigned a value of NULL or the default

Multiple records can be written once by commas, and values can be expressed using expressions

The database does not have a Boolean type Boolean, and if the declared type is Boolean, it is converted to the tinyint type, and true converts to 1,false to 0

Method Two

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

The difference from the first method is that this method can use a subquery (subquery), and only one record can be inserted at a time

Method Three

INSERT [into] tb1_name [(Col_name,...)] SELECT ...

This method inserts the query results into the 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 one time, and when the Where condition is omitted, the values of all records are updated

The following increases the age of all people by 5 years.

The following updates the age of everyone to the original age minus their ID value, changing the gender of all to 0

The following is the age of all people with an even number of IDs plus 10 years old

Deleting records

DELETE from Tbl_name [WHERE where_condition]

[note] After deleting a record, inserting a new record, the AutoNumber does not fill the number of the deleted record, but continues to increase based on the maximum number of the original record

query expressions

| Position} [ASC| | expo | position}  [ASC || row_count offset (offset}]]

Each expression of a query expression represents a column that you want to find and must have at least one. Multiple columns separated by commas

The order of the query expressions can be inconsistent with the order of the fields in the original table

When using a multi-table connection, you may have fields with the same name in different tables, and if you write directly, you can't tell which data table is the field. Add a data table before the field name to identify which data table belongs to

The asterisk * number indicates all columns. Tbl_name.* can represent all columns of a named table

A query expression can use [as] alias_name to give it an alias, which can be used for group by, ORDER by, and having words

[note] When you use a query expression to set up an alias query, as can be used, or it may not be used. However, if you do not use it, there may be ambiguity conditions

SELECT ID username from users;

mysql resolves the above statement to username as an alias for ID

Result processing

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

In the parameters of the query result grouping (group by), ASC is ascending and is the default; DESC is descending

Col_name represents the field name, position the position as a number, for example, 1 represents the first occurrence of a field in a SELECT statement

Grouping conditions

[Having where_condition]

When setting up a group (having), make sure that the grouping condition (where_condition) is either an aggregate function (max,min,avg,count,sum), or that the field in it must be a query field in select, or it will be an error

Grouping sort

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

Query results can be sorted by using a grouped sort (order by)

It can be sorted by several conditions, and priority is chosen in the order of input.

Limit results

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

Restrict query results (limit) By default, all found results are returned

If there is only one number behind the limit, it means that the record is returned from the first and returns the corresponding number

The SELECT statement is numbered starting with 0, and if you want to return from the third bar, you need to use the offset parameter with the Row_count parameter

Added

INSERT [into] tbl_name [(Col_name,...] SELECT ...

Unlike the method of inserting records at the beginning, this method can now store the results of a lookup to a specified data table

Record operation of the front-end learning Database