Shulou(Shulou.com)06/03 Report--

This article mainly explains "what is Mysql federated query". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is Mysql federated query".

Catalogue

Union of federated query

1. Query the ID and the name of each province in China

two。 ID and name of all prefecture-level cities in Hunan Province

3. Merge them with union

Union all of federated query

Inner join of federated query

1. Inquire about the number of prefecture-level cities in Hubei Province

two。 Count the number of prefecture-level cities in each province, and output the number of provincial names and prefecture-level cities

3. Query cities with more than 20 districts and counties, and output city names and number of districts and counties

The three tables of federated query are federated

1. Which province and which city are the three largest cities in districts and counties? the query results include the names of provinces, cities, and the number of districts and counties.

Left join&right join of federated query

Query all provinces and their city information

Query information about all provinces and their cities and provinces without cities

Query all provinces and their city information and cities without provinces

Union of federated query

Union can merge tables queried by more than two select statements and eliminate duplicate rows in the table.

Where the select statement needs to have the same number and data type of columns.

1. Query the ID and the name of each province in China

Select ProID,ProName from T_Province2. ID and name of all prefecture-level cities in Hunan Province

Select CityID,CityName from T_Citywhere ProID = (select ProID from T_Province where ProName= "Hunan"); 3. Merge them with union

Select ProID,ProName from T_Provinceunionselect CityID,CityName from T_Citywhere ProID = (select ProID from T_Province where ProName= "Hunan Province")

In this way, we get the union of the two query results.

The column names in the UNION merged collection are always equal to the column names in the first SELECT statement in UNION.

Union all of federated query

Select ProID,ProName from T_Provinceunion allselect CityID,CityName from T_Citywhere ProID = (select ProID from T_Province where ProName= "Hunan Province")

When using union all, duplicate lines are not eliminated.

Inner join of federated query

1. Inquire about the number of prefecture-level cities in Hubei Province

No joint query is required:

Select count (CityID) from T_Citywhere ProID = (select ProID from T_Province where ProName= "Hubei Province")

Join two tables together through ProID

Select ProName,CityName from (T_City join T_Province on T_City.ProID = T_Province.ProID) where ProName= Hubei Province 2. Count the number of prefecture-level cities in each province, and output the number of provincial names and prefecture-level cities

Select titled City.ProID Reclamation ProName count (CityID) as cc from (T_City join T_Province on T_City.ProID = T_Province.ProID) group by T_City.ProIDorder by cc desc

The ProID to be output in the select statement of something should be one of T_City and T_Province, otherwise an error will be reported.

There needs to be a common "language" between the two tables (the column names are not necessarily the same) to join.

You can give the table an individual name, set the alias of the TCIT table to tc and the alias of T_Province to tp.

Select tc.ProID,ProName,count (CityID) as cc from (T_City tc join T_Province tp on T_City.ProID = T_Province.ProID) group by tc.ProIDorder by cc desc;3. Query cities with more than 20 districts and counties, output city name, number of districts and counties select CityName,count (DisName) disCount from (T_City tc join T_District td on tc.CityID = td.CityID) group by CityNamehaving disCount > 20; three tables of federated query

1. Which province and which city are the three cities with the largest number of districts and counties? the query results include the province name, city name, and the number of districts and counties select tp.ProName,tcd.CityName,tcd.ci from (select ProID,CityName,count (ID) ci from (T_City tc join T_District td on tc.CityID = td.CityID) group by tc.CityIDorder by ci desclimit 3) tcdjoin T_Province tp on tcd.ProID = tp.ProID; joint query left join&right join

The inner connection is based on the common part of the left and right tables.

The left join is based on the common part of the left and right tables plus the unique part of the left table.

The right join is based on the common part of the left and right tables plus the unique part of the right table.

Query all provinces and their city information select * from (T_Province tp join T_City tcon tp.ProID = tc.ProID); query all provinces and its city information and province information without cities select * from (T_Province tp left join T_City tcon tp.ProID = tc.ProID); query all provinces and their city information and city information without provinces select * from (T_Province tp right join T_City tcon tp.ProID = tc.ProID) Thank you for your reading, the above is the content of "what is Mysql joint query", after the study of this article, I believe you have a deeper understanding of what is Mysql joint query, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.