In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article will explain in detail how to achieve multi-table joint query operation in mysql. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.
MySQL multi-table federated query syntax:
The copy code is as follows:
SELECT * FROM insert table LEFT JOIN main table ON t1.lvid=t2.lv_id select * from mytable,title where table name 1.name = table name 2.writer
If the mysql version is greater than 4.0, use UNION to query. The example is as follows:
SELECT `id`, `name`, `date`,'AS `type` FROM table_A WHERE conditional statement. UNIONSELECT `id`, `name`, `date`, 'incomplete' AS `type` FROM table_B WHERE conditional statement. ORDER BY `id` LIMIT num
If the mysql version is less than 4.0, you need to create a temporary table, which is divided into three steps. The example is as follows:
Step 1: create a temporary table tmp_table_name and insert related records in table_A
The copy code is as follows:
$sql = "CREATE TEMPORARY TABLE tmp_table_name SELECT `id`, `name`, `date`, 'complete' AS `type` FROM table_A WHERE conditional statement …"
Step 2: get the relevant records from table_B and insert them into the temporary table tmp_table_name
The copy code is as follows:
INSERT INTO tmp_table_name SELECT `id`, `name`, `date2` AS `date`, 'incomplete' AS `type` FROM table_B WHERE conditional statement.
Step 3: retrieve the record from the temporary table tmp_table_name
SELECT * FROM tmp_table_name ORDER BY id DESC
Analysis of the difference between union, order by and limit
Code example:
CREATE TABLE `test1` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar (20) NOT NULL, `room` varchar (100) NOT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8
1. The following query will report an error: [Err] 1221-Incorrect usage of UNION and ORDER BY
Code example:
Select * from test1 where name like'A% 'order by nameunionselect * from test1 where name like' B% 'order by name
Modified to:
Code example:
Select * from test1 where name like 'A%'unionselect * from test1 where name like' B% 'order by name
Note that in union, you can only use one order by without parentheses (think: what will happen when the column names of the order by on both sides of the union are different?) Which sorts the result set after union
Modified to:
Code example:
(select * from test1 where name like'A% 'order by name) union (select * from test1 where name like' B% 'order by name)
It is also possible that these two order by are carried out before the union.
two。 same
Code example:
Select * from test1 where name like'A% 'limit 10unionselect * from test1 where name like' B% 'limit 20
Equivalent to:
Code example:
(select * from test1 where name like'A% 'limit 10) union (select * from test1 where name like' B%') limit 20
That is, the latter limit acts on the result set after union, not on select after union.
You can also enclose it in parentheses to get the desired results:
3. The difference between UNION and UNION ALL
Union filters out duplicate rows in the select result set on both sides of the union, while union all does not filter out duplicate rows.
Code example:
(select * from test1 where name like'A% 'limit 10) union (select * from test1 where name like' B% 'limit 20)
Let's try a complex sql statement for age analysis.
(SELECT '5dates 19' AS `age`, SUM (`impression`) AS impression, SUM (`click`) AS click, sum (`cost`) AS cost FROM `adgroup_age_ report` WHERE (`age` =' 2015-11-22') AND (`date` = '2015-11-22')) AND (`date` =' 2015-11-22') AND (`date` = '2015-11-22') AND (`date` = '2015-11-22') AND (`date`
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.