SQl语言(续).ppt

查询语句 － 连接(Join)操作 查计算机系的学生名单： select sno , sname from student , dep where student.dno=dep.dno and dname= ‘ 计算机系’ ; 查询语句 － 集合操作 查询同时选了c01和c02两门课的学生信息（学号） 查询语句 － 子查询 查物理课不及格的男生名单： 查询语句 － 子查询 select sno,sname from student,dep where student.dno =dep.dno and dname=`机械系` and sno in ( select sno from sc where cno in (select cno from course where cname=`物理`) and grade ( select avg(grade) from sc,course where sc.cno=course.cno and cname=`物理`) ) ; 查询语句 － 子查询 查与计算机系任意一名同学生日相同的外语系的学生. select sno,sname from student,dep where student.dno=dep.dno and dname=`外语系` and birth = any ( select birth from student, dep where student.dno=dep.dno and dname=`计算机系`) ; 查询语句 － 子查询 查询语句 － 树查询 查询职工上下级关系 select level,empno , ename from emp connect by prior empno=mgr 指定父子关系 start with ename=‘JONES’ 指定开始节点 (或start with mgr is null) * * SQL语言 Create table Insert into dept … update Delete Select DML…查询语句 SELECT命令的语法： SELECT [DISTINCT|ALL] {*|模式名.] {表名|视图名| 快照名] .*…| {表达式[列别名]…} } [, [模式名. ] {表名| 视图名|} .*…| 表达式[列别名] ]… FROM [模式名.] {表名|视图名|快照名} [@数据库链名] [表别名] [, [模式名.] {表名|视图名|快照名} [@数据库链名] [表别名] ]… [WHERE条件] [START WITH 条件 CONNECT BY 条件] [GROUP BY表达式[，表达式] …[HAVING条件] [UNION|UNION ALL |INTERSECT|MINUS]SELECT命令 [ORDER BY{表达式|位置} [ASC|DESC] [, {表达式|位置[ASC|DESC]}]…] 查物理课不及格的学生名单： select student.sno , sname from student,sc,course where sc.sno = student.sno and sc.cno = course.cno and cname=‘物理’ and grade 60 ; select sno from sc where cno = `c01` and cno = `c02` ; select sno from sc where cno=‘ c01’ intersect Intersect 交 union 并 minus 差 c01 c02 select sno from sc where cno=‘ c02’ ; select sno from sc where grade 60 and cno in ( se