自学数据库自己练习的

--单列排序（查询所有学生，按学号降序排序）
select * from tbl_student order by snum desc;
--多惗排序（查询所有学生，按照年龄升序排序，然后在按性别降序排列）
select * from tbl_student order by age,sex desc;
--查询学生年龄，要求不重复
select distinct age from tbl_student;
--统计不同年龄的数量
select count（distinct age） from tbl_student ;
--统计每个学生的总分，平均分，最高分，最低分
select snum,sum(score),round (avg (score),1),max(score),min(score)from tbl_grade group by snum;
--多分组字段类似于排序，先按第一个分组，在对已分好的组按第二个字段分组统计不同年龄，不同性别人数
select age,sex,count(*) from tbl_student group by age,sex;
--过滤掉最低分小于60的学生
select snum,sum(score)  ,avg(score),max(score) from Tbl_Grade
Group by snum having min(score)>=60;
--检索至少学过两门课的学生
select snum from tbl_grade group by snum having count(*)>=2;
--按照学生总分降序排序
Select snum,sum(score),avg(score),max(score),min(score)from tbl_grade
Group by snum order by sum(score)desc;
--查询有一门及格的学生
select * from tbl_student s
where 60<any(select score from tbl_grade g where s.snum=g.snum);
--或
select * from tbl_student s
where 60<some(select score from tbl_grade g where s.snum=g.snum);
--单行单列子查询
--查询学生的成绩，要求显示学生的姓名
select (select sname from tbl_student s where s.snum=g.snum) as sname,
       (select cname from tbl_course c where c.cnum=g.cnum) as cname,
       score from tbl_grade g;       
select * from Tbl_Course;
select * from Tbl_Grade;
select * from Tbl_Student;
select * from course where ctime=50;
select * from course where ctime=50 and Credit=4;
select * from course where ctime=60 or credit=1;
select * from course where (ctime=60 or credit=1)and Quota=100;
select * from course order by credit,ctime,quota;
select * from course order by credit,ctime desc;
select * from course where  credit=5 and rownum <=2;
select cno as "编号",cname as "课程" from course;
select * from course where cname like '%a%';
select * from course where Cname like '%[Java]%';
select * from course where REGEXP_LIKE (Cname,'[据]+|[语]');
select distinct  S.Sname 姓名,S.Sex 性别 ,s.age 岁数, c.cname 专业,g.score 分数
from  Tbl_Course c,Tbl_Student s,Tbl_Grade g
where c.cnum=G.Cnum AND G.snum=s.snum  order by c.cname,G.Score;
--exists(返回结果集为真) not exists（不返回结果集为真）
select * from Tbl_Student s
where exists(select * from Tbl_Grade g where g.cnum=1 and s.snum=g.snum);
--查询男同学的成绩
select * from Tbl_Grade g
where exists (select * from Tbl_Student s where s.snum=g.snum and sex=1);
--并操作
select * from Tbl_Grade where score <60
Union
select * from Tbl_Grade where score >80;


select s.sname 姓名,s.sex 性别 ,s.age 岁数, c.cname 专业,g.score 分数 
from tbl_course c,tbl_grade g,tbl_student s 
where c.cnum=g.cnum and g.snum=s.snum
and G.score between 60 and 80; 
--and G.score>60 and G.score <80 order by g.score;


--多表查询
select s.sname 姓名,s.sex 性别 ,s.age 岁数, c.cname 专业,g.score 分数 
from  (tbl_course c inner join  tbl_grade g on c.cnum=g.cnum)
inner join tbl_student s on g.snum=s.snum
and G.score>60 and G.score <80 order by g.score;