SQL常用命令集2

    7.4 嵌套查询

select Sno, Sname, Sdept from student where Sdept=(select Sdept from Student where Sname='刘晨');
select  Sno, Sname from student where Sno in (select Sno from sc where Cno=(select Cno from course where Cname='信息系统' ));

  上面的第二个例子和下面这个查询语句是等价的:

select Student.Sno, Sname from student, sc, course where student.Sno=sc.Sno And sc.cno=course.cno And course.cname='信息系统';

 

select Sname, Sage from student where Sage < ANY(select Sage from student where Sdept='IS') AND Sdept <> 'IS';
select Sname from student where exists(select * from sc where Cno='1' AND Sno=student.Sno);
select sname from student where not exists (select cno from course where cno in (1,2,3) AND not exists (select * from sc where cno = course.cno AND sno = student.sno));

    7.5 集合查询

select * from student where Sage<=19 UNION select * from student where Sdept='CS';

   

    8 插入子查询结果

INSERT INTO deptage(Sdept, Avgage) SELECT Sdept, AVG(Sage) from Student GROUP BY Sdept;

   

    9 修改   

 UPDATE student SET Sage=Sage+1;

 

    10 删除

delete from deptage where Sdept='CS';