--1012课堂内容
--查询教师所有的单位即不重复的Depart列。
select distinct t. depart from teacher t;
--两种条件相加去除重复内容
select distinct t. depart ,t.prof from teacher t;
--查询Score表中成绩在60到80之间的所有记录。
select *from score where degree between 60 and 80; --小的在前,大的在后
--查询Score表中成绩为85,86或88的记录。
select *from score where degree =85 or degree ='86' or degree =88;
select *from score where degree in(85,86,88)
--查询男生的成绩
--括号select后必须是单个字段
select *from score where sno in (select sno from student where ssex='男'); --子查询
--模糊查询
--查询不姓王的同学
--字符数量不固定 %任意数量的字符
select *from student where sname not like '王%';
--字符数量固定 _单个字符
select *from student where sname like '王_';
select *from student where sname like '王_王';
--查询“95031”班的学生人数。
--内置函数
--1.聚合函数 返回单个值
--记录条数
select count (sno)from student where sclass=95031;
--查平均成绩
select sum(degree)/count (1) from score;
select avg(degree) 平均值 from score;
--合计总成绩
select sum(degree) from score;
--最高成绩
select * from score order by degree desc;
select max (degree)最大值, min (degree)最小值,avg(degree) 平均值 from score;
--日期
select min(t.sbirthday)from student t;
select max (s.degree)from score s
-- 查询Score表中的最高分的学生学号和课程号。(子查询或者排序)
select sno, cno from score where Degree in (select max (degree)from score );--子查询
select *from (select * from score order by degree desc) where rownum=1; --排序
--select sno, cno from score where Degree in (select degree from score);
--伪列
select * from score where rownum=1;
--查询每门课的平均成绩。
--按照课程编号进行分组,在计算每组的平均成绩
select cno,avg (degree) 平均值 from score group by cno;
--按照课程编号进行分组,在计算每组的平均成绩,对分组后的结果进行过滤
select cno ,avg(degree) 平均值, count (1) , sum(degree) ,(sum(degree) /count (1))平均值 from score group by cno
having avg(degree)>80; --分组
select *from (select cno ,avg(degree) a , count (1) , sum(degree) ,(sum(degree) /count (1))平均值 from score group by cno )
where a>80
select avg(degree) 平均值 from score group by cno;
--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select cno from score where cno like '3%' --以3开头的课程
select cno, avg (degree) from score where cno like '3%' group by cno
--至少有5人选修 考试
--在成绩表中课程的考试的学生数>=5个
select cno, a ,c 考试人数 from(select cno, avg(degree) a ,count(degree) c from score where cno like '3%' group by cno)
where c>=5
--查询分数大于70,小于90的Sno列。
select sno from score where degree between 70 and 90
--14、查询所有学生的Sname、Cno和Degree列。
Select cno,degree from score where
Select sname from student
--表连接,一定有主外键关系:都好隔开两个表形成笛卡尔积,再进行where筛选,通过表主外键关系筛选,where只是个筛选条件
select Sname, Cno, Degree from Student,Score where Student.Sno = Score.Sno;
--查询所有学生的Sno、Cname和Degree列。
select sname, ssex,sclass from student;
--查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher;
-- 查询Student表的所有记录。
select * from student;
--查询Score表中成绩在60到80之间的所有记录。
select *from score s where s. degree>=60 and s.degree<=80;
--查询成绩是85 86 88 的
select * from score s where s.degree=85 or s.degree=86 or s.degree=88;
--查询Student表中“95031”班或性别为“女”的同学记录。
select *from student s where s.sclass='95031'or s.ssex='女';
--以Class降序查询Student表的所有记录
select * from student order by sclass desc;
-- 以Cno升序、Degree降序查询Score表的所有记录。
--建表,并添加数据
select * from score s order by s. cno , s.degree desc;
create table grade(low number(3),upp number (3),rank char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
select * from grade;