SQL语句SQL Server 续

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'));