SQL基础复习(2)

1、各种连接查询结果示意图

2、关联查询

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