数据库概论第三章概要1.ppt

例： create cluster index ind_sname on student(Sname); create unique index ind_deposit on deposite(customer_name ASC,account_number DESC); 例4： select Sname,Ssex from Student where Sdept in(‘CS’,’MA’,’IS’) 例5： select * from Student where Sname not like ‘刘%’ 例6： select * from Student where Sname like ‘欧阳_’ 例7： select * from Course where Cname like ‘DB\_Design’ 例8： select Sno,Cno from SC where Grade is null 例9： select Sname from Student where Sdept=‘CS’ and Sge 20 例1： select * from SC where Cno=‘3’ order by Grade desc 例2： select * from Student order by Sdept,Sage desc 例2： select count(distinct(Sno)) from SC 例3： select avg(Grade) from SC where Cno=‘1’ 例4： select max(Grade) from SC where Cno=‘1’ 例1： select Cno,count(Sno) from SC group by Cno 例2： select Sno from SC group by Sno having count(*) 3 例1：查询每个学生及其选修课程的情况 select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno 2 自身连接 例1：查询每一门课的间接先修课 select first.Cno,second.Cpno from Course fitst,Course second where first.Cpno=second.Cno 3 外连接 例1：查询每个学生的基本情况及其选课情况 select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno(+) 4 复合条件连接 例1：查询选修2号课程且成绩在90分以上的所有学生 select Student.* from Student,SC where Student.Sno=SC.Sno and SC.Cno=‘2’ and SC.Grade90 1 带IN 的子查询 例1：查询与‘刘晨’在同一个系学习的学生 select Sno,Sname,Sdept from Student where Sdept in( select Sdept from Student where Sname=‘刘晨’) 例2：查询选修了课程名为‘信息系统’的学生学号和姓名 select Sno,Sname from Student where Sno in( select Sno from SC where Cno in( select Cno from Course where Cname=‘信息系统’)) 等价于：Select Sno,Sname from Student,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Course.Cname=‘信息系统’ 2 带比较运算符的子查询 例1： select * from Student where Sdept=( select Sdept from Student where Sname=‘刘晨