MySQL Basics 3

First, joint inquiry

In both cases it may be used: 1, the desired result in a statement will cause a logical conflict, can only be placed in two statements is to use the Union query

2, a table of data volume is very large, will be separated into multiple table storage, to query the use of joint query

Note: 1, if you want to use order by in a federated query, you must enclose the SELECT statement with a pair of parentheses!

2, the order by in the federated query must be paired with the Limit keyword to take effect! Because the system default federated query results tend to be more, so to limit, of course, if you want to display all the data, you can add a large number after the limit clause, for example: 999999

Keyword: union

Grammatical form

Select Statement 1

union[union Options]

Select Statement 2

union[union Options]

Select Statement 3

union[union Options]

......

Union option All : also the default value, keep all the query results!

distinct : go to weight (default), remove duplicate query results!

Second, cross-connect

Keyword: Cross join

syntax: SELECT * | Field List from Table 1 Cross Join Table 2

Meaning: It is a record from one table to connect all the records in another table, and save all the records, including all the fields of two tables! From the results, it is the Cartesian product of the two tables!

Three, internal connection syntax: Select *| Field List from Left Table INNER JOIN Right Table on Left Table . Field = Right Table . Field ; Where inner can be omitted

The nature of the inner connection is still doing cross-linking, but on the basis of the cross-linking to add a certain connection conditions! Meet the connection conditions will be displayed, non-compliant will not show! (It's possible to lose data)!

Therefore, if there is no connection when the internal connection conditions, the connection is the equivalent of cross-connect!

Four, outer connection

The outer joins are left-outer joins (left table-Main table) and right outer join (right outer join) (the table is the main table);

is to take each record of the main table according to the conditions after on to match from the table, if the match succeeds, then the two table of all records, if the match fails (that is, a record of the primary table cannot match all the records from the table), at this time, only the primary table records, from the table records are all NULL instead, So that the primary table data is not lost

Five, natural connection

Natural connections are divided into natural inner joins natural inner join, natural left/right connection natural left/right join

The nature of the connection is not much different from the inner and outer connections, but the connection conditions here are not specified by the user, but by the system.

As long as two of the table has the same field name, the system is considered a connection condition, will be active to match the two same field name value is the same, if the same field name of two tables the same value, the match is considered successful; if more than one field name is the same in both tables, Then all the same field names have the same values to match the success

Six, sub-query 1, Standard quantum query

It is often the result of the scalar quantum query as a value to use, such as to judge, participate in the operation and so on

Cases:

2. Query

That is, to return a single row of subqueries, also known as the column query!

The result of the query is often a collection of data of the same property, so we generally regard the result of the query as a set, and usually with the in and not in set operators to use!

3, row sub-query

Query results for a row of sub-query is called as a row subquery!

Row subqueries do not use many, and you must construct a row element in the process of the query to compare the results of the subquery!

The so-called construction line element, is a number of fields composed of elements, the form is to enclose multiple fields in parentheses together!

4. Table sub-query

A subquery that returns multiple rows and columns is called a table subquery!

Table sub-queries are generally from-type, that is, after the from, generally as a data source to use!

Example: using a table subquery instead of a statistical function

1), sort the entire table according to the score field first (ascending)

2), the sorted results are then group by according to the home field, because group by only takes the first record of each group!

If you want to check all the lowest points in all your hometown (there may be repeat cases):

SELECT * FROM Php_student Natural joins (select Home,min (score) as score from Php_student Group by Home) as M;

5, exists sub-query

Exists is primarily used to make judgments, and the return result is a Boolean value!

Judging by: If a subquery can return data, the result of exists is true, otherwise it is false!

The main function of the exists is to determine if the following SELECT statement has queried the data

MySQL Basics 3