Shulou(Shulou.com)05/31 Report--

This article shares with you the content of a sample analysis of MySQL federated queries. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Cartesian product

Cartesian product means that in mathematics, the Cartesian product (Cartesian product) of two sets X and Y, also known as direct product, is expressed as X × Y. the first object is a member of X and the second object is one of the members of all possible ordered pairs of Y.

Suppose that set A = {a, b} and set B = {0,1,2}, then the Cartesian product of the two sets is {(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}.

Internal connection

Select field from Table 1 aliases 1 [inner] join Table 2 aliases 2 on connection conditions and other conditions; select fields from Table 1 aliases 1, Table 2 aliases 2 where connection conditions and other conditions

External connection

The outer connection is divided into the left outer connection and the right outer connection. If you join a query, the table on the left shows that it is a left outer join, and the table on the right shows that it is a right outer join.

-- left outer join, Table 1 fully shows select field name from table name 1 left join table name 2 on connection condition;-- right outer connection Table 2 fully shows select field from table name 1 right join table name 2 on connection condition

Self-connection

When it comes to row-to-row comparisons, you need to connect yourself

Example: show all the scores of "computer principles" that are higher than "Java"

-- first query the idselect id,name from course where name='Java' or name=' computer principles of "computer principles" and "Java" courses;-- then query the information in the score table that "computer principles" is better than "Java". SELECTs1.*FROMscore S1 score s2WHEREs1.student_id = s2.student_idAND s1.score < s2.scoreAND s1.course_id = 1AND s2.course_id = 3 -- you can also use the join on statement to query SELECTs1.*FROMscore s1JOIN score S2 ON s1.student_id = s2.student_idAND s1.score < s2.scoreAND s1.course_id = 1AND s2.course_id = 3.

Subquery

Subqueries are select statements embedded in other sql statements, also known as nested queries.

Single-row subquery: a subquery that returns a row of records

Case: inquire about the classmates of "Xiaobai" students:

Select * from student where classes_id= (select classes_id from student wherename=' rookie')

Multi-row subqueries: subqueries that return multiple rows of records

[NOT] IN keyword:

First execute the SQL of the subquery, and then put the results in memory, and then do the outer query, and then directly compare the constraints given with the results of the subquery, and filter it. (memory-dependent, suitable for situations where the result set of subqueries is relatively small)

[NOT] EXISTS keyword:

First execute the outer query, take the record of each outer query in turn, and bring it into the inner query

If the set of results of the inner query is not empty, the results of the outer query are retained.

If the set of results of the inner query is empty, the results of the outer query are discarded.

Does not depend on memory, and is suitable for situations where the outer query result set is small and the subquery result set is relatively large.

Merge query

To merge the execution results of multiple select, you can use the collection operator union,union all. Use UNION

And UNION ALL, the results of the query before and after the set, the fields need to be consistent.

Union

This operator is used to obtain the union of two result sets. When you use this operator, duplicate rows in the result set are automatically removed.

Example: query courses whose id is less than 3 or whose name is "English":

Select * from course where id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.