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.