MySQL Finishing (iii)

Simple single-table operation

(1) Simple crud　　　　　　

Insert Query Results	Insert INTO table1 (id,name,age) Select Id,name,age from table2 where id=1
Update action	Update table name set field name = ' abc ' WHERE id=xxx;
Avoid duplicate data queries-distinct	Select distinct rename field name from table name
Mathematical operations	Select id*10 from table name
string concatenation	Select concat (Name, ' salary is: ', salary)
Scope Query	Select name from table name where ID between 5 and 10
Remove range Query	Select name from table name where ID not between 5 and 10
is empty query	SELECT * from table name where field is NULL;
Non-empty query	SELECT * from table name where field is not NULL;
Query with in keyword collection	Select name from table name where ID in (three-by-one)
Exclude query with in keyword	Select name from table name where ID not in (three-by-one)
Note: For collection queries with in, when there is a null value in the collection, such as the Select name from table name where ID in (1,2,3,null) does not affect the query results.
Fuzzy matching like	Select name from table name where name like ' XXX% '
Note: Matches characters beginning with "XX" End with%, characters ending with "xx" Begin with%
Placeholder _	1. Select name from Xuanjie where name is like ' _c% '; (the second character is C) 2. Select name from Xuanjie where name is like ' __c%; (the third character is C)
Note: The "_" wildcard can match a single character, and the "%" wildcard can match any length of string. A like match "percent" means that all data records are queried. The non-matching keyword can be expressed as a not.
Ascending	SELECT * FROM table name order by field name ASC;
Descending	SELECT * FROM table name order by field name Desc;
Multi-character orderby sequence, descending	SELECT * FROM table name order BY field name 1 ASC, field name 2 desc;
Limit keyword	SELECT * FROM table name where ID < 8 ORDER BY id DESC limit 3
Limit A, b	A from the beginning of the first, show article B
Not equal to an expression	Select the From table name where not name= ' CC ';

(2) Statistical functions and Group queries

　　　　　　　

Number of bars recorded in the statistics count () function	Select count (field name) from table name where ID < xxx
Statistical mean AVG () function	Select AVG (field name) as xxx from table name
Statistical calculation sum sum () function	Select sum (field name) as xxx from table name
Statistical maximum max () function	Select max (field name), min (field name) from table name
Statistic Max min () function
Note: For MySQL-supported statistics functions, if there are no records in the table being manipulated, the count () function returns 0, and the other function returns NULL.
Simple packet Query GROUP by	SELECT * FROM table name GROUP By field name
The Group_concat () function is used to specify the display of the specified field values in each group	Select Age,group_concat (field name), Count (field name) from table name Group by Group field name
Multi-field group query	Group By Field name 1, field name 2
Limited group query for having a clause	Select ID as UID,GROUP_CONCAT (name) as Uname,count (name), AVG (age) from Xuanjie Group by Id,age have age > 20;
Note: In MySQL, if you want to implement a conditional restriction on a grouping, you cannot do so by where, because the keyword is primarily used to implement conditional throttling data logging. MySQL provides a special keyword having to implement conditional restrictions on grouping data records. At the same time, the packet query must have duplicate data in the action table, otherwise it doesn't make any sense.

Second, multi-table operation principle

MySQL supports multi-table operation by connecting queries, when you first connect two or more than two tables according to a condition, and then query the target data as required, the connection query includes both internal and external connections. However, in practical applications, the connection query is not used generally, because of the Cartesian product, the efficiency of the operation is relatively low, so there is also a sub-query suitable for multi-table query.

The following sections are organized around the following classes and student information tables.

(1) Cartesian product: The result of no connection table relationship returned. such as SELECT * from Class,student, the following results appear, the result set of the Cartesian product is the sum of the data of the previous table, the sum of the data of a table, the middle is simply the connection of two tables, and did not do data matching operations.

(2) Connection: The so-called connection, in fact, is in the Cartesian product of the table relationship, according to a certain condition generated a new relationship, the connection can be divided into internal and external connections.

(3) Inner connection (INNER join): The so-called internal connection, is in the Cartesian product of the table relationship, preserving the matching records in the table relationship, discarding the mismatched data records, according to the matching conditions can be divided into natural connections, equivalent connections and not equivalent connections.

(4) Natural connection (NATURAL join): The so-called natural connection, which is the Cartesian product of the table relationship, automatically matches the data based on the field of the same name in the table relationship, and then removes the repeating field.

SELECT * FROM class natural join student;

(5) Equivalent connection: the so-called equivalent connection, is the Cartesian product of the table relation, selects the data which matches the field value equal. As a result of the execution, it is found that the equivalent connection matches the "=" condition compared to the natural connection, and that no duplicate fields, such as class_id, are removed from the new relationship.

(6) Unequal connection: the so-called unequal connection, that is, in the Cartesian product of the table relationship, select the matching field is not equal to the condition. The result of the execution is that "! =" is not equal to the data in the Cartesian product, and the repeating field, such as class_id, is not removed.

(7) Outer join (OUTER join): In the Cartesian product of the table relationship, not only the partially mismatched records are retained, but also some of the mismatched records are retained. The outer joins include the left OUTER join (OUTER join), the right outer join (OUTER join) and the All-out connection (full OUTER join), and the following table explains the outer joins based on the two tables:

(8) Left OUTER join: The so-called Left outer connection, is the Cartesian product of the table relationship in addition to the selection of matching data records, but also contains the table in the left unmatched data records, as follows:

(9) Right outer connection (OUTER join): The so-called right outer join, is the Cartesian product of the table relationship in addition to the selection of matching data records, but also contains the data records in the right table mismatch, as follows:

(10) All-out connection (full OUTER join): The so-called all-out connection, is the Cartesian product of the table relationship, in addition to the selection of matching records, but also contains the left and right side of the table mismatch data records.

Third, sub-query

Why use subqueries: In the usual multi-table connection query, because the table will be Cartesian product operations, if more than one table data record large, or field more, then the Cartesian product will appear in the panic, for experienced SQL developers, will first through the statistical function count (*) Statistics data records of multiple tables, Then decide whether to use a multi-table query.

However, if the number of data records obtained by the statistical function is too large, it is not appropriate to use a multi-table query, it is recommended to use a subquery, the so-called subquery, that is, in a main query nested in a number of other queries, such as in the Select xxx from where xxx nested multiple Select, at this time, The outer select is called the main query, and the inner layer is called a subquery.

The following uses the student table as an example:

Example 1: Subquery is a single-row multi-column

　　　　 Example 2: Subquery is a single-row multi-column

　　　　 Example 3: Subquery as a multiline column

　　　　 The In,any,all,exists keyword is typically included when a subquery returns results that are multi-row single-column data.

(1) in keyword

(2) any keyword

=any: function is the same as in

>any: records that are larger than the smallest data returned in a subquery

<any: records that are smaller than the largest data returned in a subquery

(3) All keywords

>all: Data that is larger than the maximum number of records returned in a subquery

<all: Data that is smaller than the minimum number of records returned in a subquery

(4) Exists keywords

exists the query, it iterates through the surface, and then passes the results to the subquery.

Example 4: Multi-row multi-column subquery

MySQL Finishing (iii)