Various clauses for database---database queries

Basic Query
Clause by
Form:
Order by Sort field 1 [Sort by], sort field 2 [Sort by], .....
Description
The previously obtained data (with the FROM clause, the WHERE clause, the group clause, all the results of the HAVING clause) specifies that the size of a field is arranged (sorted), and that there are only 2 ways of sorting:
　　　　Positive order: ASC (default), can omit
Reverse: DESC
If you specify more than one field to sort (although uncommon), it means that in the same data as in the previous field sort, the specified sort is followed by the size of the latter field.

　　　　

　　　　

Limit clause
Form:
　　　　Limit [start line number start], number of rows to be fetched num
Description
Indicates that the previously obtained data has been previously queued (if any) and that it has been given a "partially contiguous number of strips" of data.
Start line number start: The line number of the first row is 0, which can be omitted, or the default line number (0).
Number of rows to get: If the result set starts from the specified line number and does not have so many rows at the end, only the last one is taken.

This clause is useful--primarily for one of the most common requirements (phenomena) on a Web page: paging.

Paging principle:
The premise of paging: the number of bars per page specified by the person, $pageSize = 3;
Show (get) 1th page data: SELECT * from table name limit 0, $pageSize;
Show (get) 2nd page data: SELECT * from table name limit 3, $pageSize;
Show (GET) 3rd page data: SELECT * FROM table name limit 6, $pageSize;
Show (get) page $n data: SELECT * from table name limit ($n-1) * $pageSize, $pageSize;

Connection Query
Connecting means two or more tables (data sources) that are "connected to become a data source".

In fact, a complete connection of two tables is such a process:

Each row of the table on the left, with each row of the table on the right, is the result of all the data rows that are obtained after the 22 cross docking.

Note: After the connection, a new data table is not formed, but only a "memory pattern".

Syntax form: From table 1 [connection method] Join table 2 [on connection condition];

Cases:

SELECT * FROM course join teacher on Course.cno=teacher.cno

Select Student.sno,course.cname,score. degree from student join score on score.sno=student. Sno Join course on COURSE.CNO=SCORE.CNO;

Cross Connect:
In fact, a cross-join is the result of a connection that does not set any conditions on two tables.
Cross-joins are also often called "Cartesian product"-mathematically more likely.
Grammatical form:
　from table 1 [Cross] join table 2; The cross join is visible only without the on condition.
The word cross can also be omitted, and the word inner can be used instead

Internal connection: equivalent to connection query
Grammar:
　　From table 1 [inner] Join table 2 on table 1. field 1= table 2. Field 2;

Left [outer] connection:
Form:
　　From table 1 left [outer] Join table 2 on join condition.
Description
1, here, left is the keyword.
2, the connection condition is the same as the inner connection.
3, meaning: Based on the results of the inner join, plus all the data in the left table that do not meet the join conditions, the corresponding position of the table on the right should be automatically added to the "null" value.

Right [outer] connection:
Form:
　　From table 1 right [outer] join table 2 on join condition.
Description
1, here, right is the keyword.
2, the connection condition is the same as the inner connection.
3, meaning: On the basis of the results of the internal connection, plus all the data in the right table that do not meet the join conditions, the corresponding position of the table should be placed on the left side of the field will automatically fill the "Null" value.

Full [outer] connection:

Corresponds to the collection of left and right outer queries.
Form:
　　From table 1 full [outer] Join table 2 on connection condition;
Description
1, meaning: In fact, is the "associated set" (elimination of duplicates), that is, the result of the inner join, plus all the rows in the left table that do not meet the conditions (the right side of the corresponding null), plus, the right table does not satisfy the condition of all rows (left corresponding to the null).
2,mysql In fact do not know the whole [outer] connection syntax, that is, MySQL this software itself does not support the full-attached syntax.
　3, this concept exists in other databases, understanding is possible.

Common sub-queries and related keywords

Sub-query
A query, which is usually a SELECT statement (that is, the SELECT keyword appears once)
However, if the SELECT query statement appears in a SELECT query statement, the latter is called "subquery", which is the "main query"
Form:
SELELCT field or expression or (subquery 1) [as Alias] from table name or (subquery 2) where field or expression or (subquery 3) Condition judgment

Using the in sub-query
In the basic syntax form is:
　　where operand in (value 1, value 2, ...). ）
The in sub-query is:
　　where operand in (the query for a clause);
Meaning:
Indicates that the operand (field value) is equal to either of the subqueries, even if the condition is met.

Using any subquery
form of Use:
　　where operand comparison operator any (for the query of a clause);
Description
1 operands are usually still field names
The 2 comparison operator is the regular 〉〉= <= = <>
A 3-column subquery can also be a scalar subquery, which means "several data values"
Meaning:
A value that represents the operand that satisfies a given comparison operation with any one of the values of the query, even if it satisfies the condition-that is, as long as there is one achievement.

Special cases:
　　where operand = any (for the query of the reference);
It is exactly equivalent to:
　　where operand in (the query for a clause);

Using the all subquery
　　The where operand comparison operator all (for the query of a clause);
Description
1 operands are usually still field names
The 2 comparison operator is the regular 〉〉= <= = <>
A 3-column subquery can also be a scalar subquery, which means "several data values"
Meaning:
The value that represents the operand must satisfy the given comparison operation with all the values of the example query, before the condition is satisfied.

Subqueries that use some
Some is a synonym for any. Use it as well.

Subqueries that use exists
Form:
　　where exists (sub-query);
Meaning:
True if the subquery has result data (no matter what data, as long as it is greater than or equal to 1 rows), otherwise false

NOT EXISTS subquery:

　　Contrary to the exists subquery.

Union query
　　The union query keyword is: Union extends the row, two table fields are the same, the result is different
The key word for a join query is: Join to the column extension, two tables to the left and right together
Union query is the query results of two SELECT statements "Cascade" together to become a "big result". The prerequisite conditions for which the
two query results can be "federated" are:

Grammatical form:
　　SELECT statement 1union [All | Distinct]select Statement 2;
Description
1, the output segment (result field) of the two SELECT statement is the same as the number, and the usual type in the application makes sense.
2, the fields in the result set are subject to the fields of the first SELECT statement.
3, the field of the first SELECT statement can be aliased, but if an alias is made, subsequent where,group,order clauses should use that alias.
　　4, the Federated query by default is to eliminate duplicates (DISTINCT), if you want to not eliminate, you must be explicit "all."
5, if you want to sort or limit the entire Union result, you should add parentheses to the respective SELECT statement:
　　　(SELECT statement 1)
Union
(SELECT statement 2)
Order by .... Limit .... ；

Cases:

# 31. Check the name, sex and birthday of all teachers and classmates. Select Sname,ssex,sbirthday from student Union select Tname, Tsex,tbirthday from teacher; # 32, query all "female" teacher and "female" classmate's name, Sex and birthday. Select Sname,ssex,sbirthday from student where ssex= "female"Union select Tname, tsex,tbirthday from teacher where Tsex = "female";

Various clauses for database---database queries