oracle 数据库基本操作三——数据查询语言

例3-1： (选择表中的若干列)  求全体学生的学号、姓名、性别和年龄。

select sno,sname,ssex,sage from student;

例3-2： (不选择重复行)  求选修了课程的学生学号。

select distinct sno from score;

例3-3： (选择表中的所有列)  求全体学生的详细信息。

select * from student;

例3-4： (使用表达式)  求全体学生的学号、姓名和出生年份。

select sno,sname,2017-sage as year_brithday from student;

例3-5： (使用列的别名)  求学生的学号和出生年份，显示时使用别名“学号”和“出生年份”。

select sno as 学号,2017-sage as 出生年份 from student;

例3-6： (比较大小条件)  求年龄大于19岁的学生的姓名和年龄。

select sname,sdept,sage from student where sage > 19;

例3-7： (比较大小条件)  求计算机系或信息系年龄大于18岁的学生的姓名、系和年龄。

select sno,sage from student where sage > 18 and sdept = 'CS';

例3-8： (确定范围条件)  求年龄在19岁与22岁(含20岁和22岁)之间的学生的学号和年龄。

select sno,sage from student where sage between 20 and 23;//或者
select sno,sage from student where sage > 19 and sage <= 22;

例3-9： (确定范围条件)  求年龄不在19岁与22岁之间的学生的学号和年龄。

select sno,sage from student where sage not between 20 and 23;

例3-10：(确定集合条件)  求在下列各系的学生信息：数学系、计算机系。

select * from student where sdept in ('CS','MA')order by sdept asc;

例3-11：(确定集合条件)  求不是数学系、计算机系的学生信息。

select * from student where sdept not in ('CS','MA') order by sdept asc;

例3-12：(匹配查询)  求姓名是以“李”打头的学生。

select * from student where sname like '李%';

例3-13：(匹配查询)  求姓名中含有“志”的学生。

select * from student where sname like '%志%';

例3-14：(匹配查询)  求姓名长度至少是三个汉字且倒数第三个汉字必须是“马”的学生。

select * from student where sname like '%马__';

出现问题，本应该显示的数据，却没有被配到?

原因:在汉字马前面会有一个空格，应该调用去空格函数rtram(sname)去掉空格就好了。

例3-15：(匹配查询)  求选修课程001或003，成绩在80至90之间，学号为96xxx的学生的学号、课程号和成绩。

 select sno,cno,score from score
 where cno in ('001','003') 
 and (score between 80 and 90)
 and sno like '96___';

例3-16：(匹配查询)  求课程名中包含 ’_’ 字符的课程号、课程名和学时数。

select cno,cname,ctime from course where cname like '%\_%' escape '\';

例3-17：(涉及空值查询)  求缺少学习成绩的学生的学号和课程号。

select sno,cno from score where score is null;

例3-18：(控制行的显示顺序)  求选修003课程或004课程的学生的学号、课程号和分数，要求按课程号升序、分数降序的顺序显示结果。

select sno, cno, score from score 
where cno in ('003','004') 
and score is not null 
order by cno asc,score desc;

例3-19：(组函数)  求学生总人数。

select count(*) from student;

例3-20：(组函数)  求选修了课程的学生人数。

select count(distinct sno) from score;

例3-21：(组函数)  求计算机系学生的平均年龄。

select avg(sage) from student where sdept = 'CS';

例3-22：(组函数)  求选修了课程001的最高、最低与平均成绩。

select max(score), min(score), avg(score) from score 
where cno = '001' 
group by cno;

例3-23：(分组查询)  求各门课程的平均成绩与总成绩。

select cno,avg(score),sum(score) from score 
group by cno order by cno;

例3-24：(分组查询)  求各系、各班级的人数和平均年龄。

select sdept,sclass,count(sno),avg(sage) from student
group by sdept,sclass
order by sdept,sclass asc;

例3-25：(分组查询)  输入以下查询语句并执行，观察出现的其结果并分析其原因。

                     SELECT SNAME，SDEPT，COUNT（*）FROM STUDENT                                         

                             WHERE SDEPT=’CS’ GROUP BY SDEPT;

报错：ORA-00979: 不是 GROUP BY 表达式

原因：select 查找的属性必须是在group by 中包含的属性，且只能少不能多；

例3-26：(分组查询)  分析以下语句为什么会出现错误。并给出正确的查询语句。                    

                      SELECT SAGE FROM STUDENT GROUP BY SNO;

报错：ORA-00979: 不是 GROUP BY 表达式

原因：同上，select 查找的属性必须是在group by 中包含的属性，且只能少不能多；

例3-27：(分组查询)  求学生人数不足3人的系及其相应的学生数。         

select sdept ,count(sno) from student group by sdept having count(sno) < 3;      

例3-28：(分组查询)  求各系中除01班之外的各班的学生人数。  

select sdept,sclass, count(sno) from student where sclass != '01' group by sdept,sclass;                  

例3-29：(涉及空值的查询)  分别观察各组函数、行的显示顺序以及分组查询与空值的关系。 

select avg(score) from score;
select sum(score) from score;
select max(score) from score;
select min(score) from score;
select count(score) from score; //count不统计空值
select score from score order by score desc; //空值作为最大值排列在第一个位置
select score from score order by score asc;  //空值作为最大值排列在最后一个位置               

例3-30：(连接查询)  求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。

select sname,cname,score from student,score,course
where score.cno = '001' and (score <70 or score > 90)
and student.sno = score.sno
and score.cno = course.cno;         

例3-31：(连接查询与表的别名)  求选修了课程的学生的学生姓名、课程号和成绩。 

select sname,cno,score from student,score where student.sno in (select sno from score)
and score is not null and student.sno = score.sno ;                 

例3-32：(自身连接查询)  求年龄大于 ’李丽’ 的所有学生的姓名、系和年龄。 

select s1.sname,s1.sdept,s1.sage from student s1,student s2
where s1.sage > s2.sage and s2.sname = '李丽';     

例3-33：(外部连接查询)  求选修了课程002或003的学生的学号、课程号、课程名和成绩，要求必须将002和003课程的相关信息显示出来。  

select sno,course.cno,cname,score from score,course where course.cno in ('002','003')
and score.cno = course.cno;            

例3-34：(子查询)  求与 ‘黎明’ 年龄相同的学生的姓名和系。

 select sname ,sdept from student where sage  in (select sage from student where sname = '黎明');

因为，有两位同学叫黎明，在子查询中返回多行，此时不能使用等号进行判断，而是要用in进行判断；                  

例3-35：(子查询)  求选修了课程名为 ’数据结构’ 的学生的学号和姓名。

select sno,sname from student 
where sno in 
　　(select sno from score where cno in
 　　　　(select cno from course where cname = '数据结构'));          

例3-36：(子查询ANY)  求比数学系中某一学生年龄大的学生的姓名和系。

select sname ,sdept from student where sage > any(select sage from student where sdept = 'MA');               

例3-37：(子查询ALL)  求比数学系中全体学生年龄大的学生的姓名和系。

select sname ,sdept from student 
where sage > all(select sage from student where sdept = 'MA');                

例3-38：(子查询EXISTS)  求选修了课程004的学生的姓名和系。

select sname, sdept from student 
where exists (select * from score where cno = '004' and sno = student.sno);               

例3-39：(返回多列的子查询)  求与 ‘黎明’ 同系且同龄的学生的姓名和系。

select sname, sdept, sage from student 
where (sdept,sage) in (select sdept ,sage from student where sname = '黎明' ) ;               

例3-40：(多个子查询)  求与 ‘‘黎明’ 同系，且年龄大于 ‘李丽’ 的学生的信息。

select * from student 
where sdept in (select sdept from student where sname = '黎明') 
and sage > all (select sage from student where sname = '李丽') 
and sname != '李丽';       

例3-41：(子查询中使用表连接)  求数学系中年龄相同的学生的姓名和年龄。

select sname, sage from student
where sno in (
    select s1.sno from student s1,student s2 
    where s1.sage = s2.sage and s1.sno != s2.sno 
    and s1.sdept = s2.sdept and s1.sdept = 'MA');                    

例3-42：(连接或嵌套查询)  检索至少选修王成刚老师所授课程中一门课程的女学生姓名。

select sname from student 
where sno in (
    select sno from score 
    where cno in (
        select cno from teach where tname = '王成刚')) 
and ssex = '女';                      

例3-43：(嵌套与分组查询)  检索选修某课程的学生人数多于3人的教师姓名。  

select distinct tname ,cno from teach 
where cno in (
    select cno from score group by cno having count(sno)>3);           

例3-44：(集合查询)  列出所有教师和同学的姓名和性别。 

select sname ,ssex from student union select tname, tsex from teach               

例3-45：(相关子查询)  求未选修课程004的学生的姓名。 

select sname from student where sno not in (select distinct sno from score where cno = '004');                   

例3-46：(相关子查询)  求选修了全部课程的学生的姓名。    

select sname from student where not exists (
　　 select * from course where not exists(
    　　 select * from score where sno = student.sno and cno = course.cno));            

例3-47：(相关子查询)  求至少选修了学生 ‘96002’ 所选修的全部课程的学生的学号。 

select sno from score sc1 where not exists(
    select * from score sc2 where sc2.sno = '96002' and not exists(
        select * from score sc3 where sno = sc1.sno and cno = sc2.cno));                

例3-48：(相关子查询)  求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。

select sno, score.cno, score from score ,(select cno ,avg(score) as av from score group by cno)avgs 
where score > av and score.cno = avgs.cno ;

例3-49：(相关子查询)  查询被一个以上的学生选修的课程号。

select cno from (select cno ,count(sno)as cou from score group by cno) count_c where cou > 1 order by cno asc;

例3-50：(相关子查询)  查询所有未选课程的学生姓名和所在系。

select sname ,sdept from student where not exists (
    select * from score where student.sno = score.sno);

欢迎大家评论，共同学习，共同进步；

转载于:https://www.cnblogs.com/a1982467767/p/7718327.html