select * from Person,Class
where Person.ClassID = Class.ID
select * from Person p,Class c
where p.ClassID = c.ID
select *, 2012-year(p.birthday) age from Person p,Class c
where p.ClassID = c.ID
--内连接查询
select * from person
inner join class on person.classid = class.id
inner join xxxx on xxxx.xxx = person.xxxx
where sex = '男'
--左连接查询
select * from person
left join class on person.classid = class.id
--右连接查询
select * from person
right join class on person.classid = class.id
--全连接查询
select * from person
full join class on person.classid = class.id
--将查询结果作为另一个查询的条件使用
--查询结果用作查询条件进行比较运算的时候，结果必须是一行一列的
select * from person where salary = (select max(salary) from person )
--查询结果用作查询条件进行in查询的时候，结果必须是多行一列的
select * from person where classID in (select id from Class )
--可以将一个查询结果看成一个临时表，再次对它进行查询
select * from Person
inner join
(
select classid,avg(salary) avgsalary
from person
group by classid
) Personavg on person.classid = personavg.classid
where person.salary < personavg.avgsalary
select * from Person,
(
select classid,avg(salary) avgsalary
from person
group by classid
) Personavg
where person.classid = personavg.classid
and person.salary < personavg.avgsalary
3、其他查询
--any表示取查询结果的任意一条记录
select * from person
where classid = 2 and salary > any(select salary from person where classid=1)
--all表示取查询结果的所有值
select * from person
where classid = 2 and salary > all(select salary from person where classid=1)
--exists表示数据存在
select * from person
where exists(select * from class where 1<>1)
select * from person p
where exists(select * from class c where p.classid = c.id)
--union合并两个数据集，并且消除重复行
select * from person
union
select * from person1
select name,sex,salary from person
union
select name,sex,salary from person1
