SQL语句SQL Server 续 博客分类： SQL Server SQLSERVER语句exists左连接
1.分组查询结果
在COMPUTE中使用BY子句时，要求必须包含ORDER BY子句，即先按分类的
字段排序，并且排序的字段与分组汇总的字段必须相同。
select s.sno,s.sname,g.cno,g.scgrade from student s,grade g
where s.sno=g.sno and s.sno=1
order by g.cno
compute avg(g.scgrade),min(g.scgrade),max(g.scgrade) by g.cno;
2.两个表的查询
select student.*,sex.*
from student,sex
where student.ssex=sex.sexno;
3.左外连接查询，显示出左边关系中所有的元组
select student.*,sex.sexname
from student left outer join sex on(student.ssex=sex.sexno);
4.右外连接查询
select student.*,sex.sexname
from student right outer join sex on(student.ssex=sex.sexno)
5.全外连接查询
select student.*,sex.sexname
from student full outer join sex on(student.ssex=sex.sexno)
insert into sex values(4,'错');
6.嵌套查询
select student.sno,student.sname,grade.cno,course.cname,grade.scgrade
from student,course,grade
where student.sno=grade.sno
and course.cno=grade.cno
and student.sno in(
select A.sno
from grade A,grade B,student
where A.sno=B.sno
and A.cno=1
and B.cno=2);
7.ALL与ANY的应用
select sno,cno,scgrade
from grade
where scgrade > all(
select scgrade from grade
where sno=2
);
8.EXISTS的应用
select sname,sbirth
from student
where not exists
(select * from grade
where grade.sno=student.sno and cno=4);
9.UNION的使用
select * from student
where ssex in(select sexno from sex where sexname='女')
union
select * from student
where sno in(select sno from grade where cno=4);
10.INTERSECT的使用
select * from student
where ssex in(select sexno from sex where sexname='女')
intersect
select * from student
where sno in(select sno from grade where cno=4);
11.EXCEPT的使用
select * from student
where ssex in(select sexno from sex where sexname='女')
except
select * from student
where sno in(select sno from grade where cno=4);
12.将查询结果直接插入表格
create table gv(
sno int primary key,
sname varchar(8),
gavg float);
insert into gv
select student.sno,sname,avg(scgrade)
from student,grade
where student.sno=grade.sno
group by student.sno,student.sname;
13.修改查询出的数据
update sex
set sexname='错'
where sexno in (select sexno from sex where sexno not in(1,2));
14.删除查询出的数据
delete
from sex
where sexno in(select sexno from sex where sexno not in('1','2'));