MySQL中合并查询数据记录与子查询

合并查询数据记录

1、带有关键字union的合并操作

select * from table_name1

union select * from table_name2;

执行结果成功显示合并后的数据记录，同时去掉了重复数据记录，使新关系里没有任何重复的数据记录

2、带有关键字union all的合并操作

select * from table_name1
union all
select * from table_name2;

没有去掉重复记录，新关系里存在重复的数据记录

子查询

1、返回结果为单行单列的子查询

select filed1

​			from tbale_name

​			where field2='smith';

slelect * 

​			from table_name

​			where filed2>(

​					select filed2

​					from table_name

​					where filed2=value);

2、单行多列子查询

select field1,field2

​		from table_name

​		where field3=value;

select filed1,field2,field3

​		from table_name

​		where (field1，filed2)=(

​				select filed1,filed2

​					from table_name

​					where filed3=value);

返回结果为多行单列子查询

1、带有关键字in的子查询(或者用not in)

select filed from table_name;

select * from table_name1

​		where filed in(

​				select filed from table_name2);

2、带有关键字any的子查询

select filed

​		from table_name

​		where filed3=value;

select filed1,filed2

​		from table_name

​		where filed3>any(

​		select filed3

​			from table_name

​			where filed4=value)

3、带有关键字all的子查询

select filed from table_name

​		where field2=value;

select filed1,filed2 from table_name

​		where filed3>all(

​		select filed3

​				from table_name

​				where filed4=value);