MySQL Database 8-sub-query, federated query

A subquery that uses the IN keyword

Question: The query game type is the ' Chess class ' score information for the game

-Game type information is not included in the Games score table

Idea one: Using link query

Idea two: In two steps, first find so ' chess class ' game number, and then this group number for query based on the completion of the query

SELECT * from scores where Gno in (select Gno from game where gtype = ' board ')

Example: Query the player QQ without participating in number 5th game

Select User_qq from the users where user_qq not in (select User_qq from scores where gno=5)

Two sub-queries using the EXISTS keyword

Example: If there is a nickname ' Monkey King ', then query the data in the score table

SELECT * from scores where exists (SELECT * from users user_name = ' Monkey King ')

Three joint query

Select _statement Union[all] select_statement [Union[all] SELECT_STATEMENT][...N]

function and characteristic: can connect the result set of multiple query statements vertically as one

All keywords can display all the data (that is, duplicates are also displayed)

The number of columns is compatible with the type

Select user_name from Users

Union

Select Gname from Games

Example: Query the player table for all female players and birthdays are empty players

SELECT * from users where user_sex= ' women '

Union

SELECT * from users where user_birthday is null

<<=====>> SELECT * from users where user_sex= ' female ' or select * from the users where User_birthday is null

Example: Query QQ number is ' 12302 ' of the player all scores and calculate the total score and average score, and display to the same result set

Select User_qq,gno,score from scores where user_qq= ' 12302 ' union ALL select ' Total score ', ', sum (score) from scores union ALL Sele CT ' average score ', ', AVG (score) from scores where user_qq= ' 12302 '

MySQL Database 8-subquery, federated query