SQL查询指导.ppt

SQL查询指导 基本语法 Select 内容列表From 表列表Where 查询条件表达式Order By 排序关键字列表Group By 分组关键字 带表达式的查询 Select sno,cno,grade-60 from sc where grade=60 Student(sno,sname,sbirth) Select * from student Select sno,sname,year(date())-year(sbirth) as age from student 选择查询WHERE 比较大小：(NOT) 比较运算符 Select * from student where sage20 Select * from student where not sage20 = ,= ,= 多重条件查询：AND / OR Select * from student where sage25 or sage20 Select * from student where sage20 and sage25 确定范围：(NOT) BETWEEN AND Select * from student where sage=20 and sage=25 Select * from student where not sage between 20 and 25 消除重复的行：DISTINCT / ALL Select DISTINCT cno,cname from course,sc where sc.cno=course.cno 确定集合：(NOT) IN Select * from student where sno=‘01’ or sno=‘02’ or sno=‘03’ Select * from student where sno not in (‘01’,’02’,’03’) 字符匹配（模糊查询）：(NOT) LIKE Select * from student where sname like ‘张%’ ‘_’：任意一个字符 ‘%’：任意字符串 Select * from student where sno like ‘____01%’ Select * from student where sname like ‘%三%’ 涉及空值的查询：IS (NOT) NULL Select * from student where sname is NULL Select * from student where len(sname)=0 连表查询 等值连接 Select student.sno,sname,course.cno,cname,grade from student,course,sc where student.sno=sc.sno and course.cno=sc.cno 复合条件连接 自身连接 Select sa.* from student sa,student sb where sa.sage=sb.sage and sa.sname=‘张三’ 子查询块嵌套查询 带有IN谓词的子查询 Select student.sno,sname from student,sc where student.sno=sc.sno and cno=‘00001’ Select sno,sname from student where sno in (select sno from sc where cno=‘00001’) 带有比较运算符的子查询 Select * from student where sage=(select sage from student where sname=‘张三’) 带有ANY或ALL谓词的子查询 Select * from student where sage=(select max(sage) from student) Select * from student where sage=ALL(select sage from student) Select * from student where sage=any(select sage from student) 带有EXISTS谓词的子查询 查询选修所有课程的学生名单 Select sno,sname from student where not exists (select * from course where not exists(select * from sc where student.sno=sc.sno and course.cno=sc.cno)) Select sno from