数据库查询语句详细实例解析(以mysql为例) |
您所在的位置:网站首页 › 佛学考研要求年龄在十九至二十五周岁之间为什么 › 数据库查询语句详细实例解析(以mysql为例) |
MySQL查询语句 最近做实验时写到了有关查询语句的详细使用案例。借此案例记录一下包括关联查询,子查询,嵌套查询在内的查询语句的用法。 本案例涉及的表如下: student: sc: course: 具体操作 一 单表 1 查询年龄在 19 至 21 岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。 select sno,sname,sage from student where sage between 19 and 21 and ssex=’女’ order by sage desc 2 查询姓名中第戎 2 个字为“明”字的学生学号、性别 select sname ,ssex from student where sname like ‘_明%’ 3 查询 1001 课程没有成绩的学生学号、课程号 select sno,cno from sc where grade is null and cno=’1001’ 4 查询 JSJ 、SX、WL 系的学生学号,姓名,结果按系及学号排列 select sno,sname from student where sdept in (‘JSJ’,’SX’,’WL’) order by sdept,sno 5 按 10 分制查询学生的 sno,cno,10 分制成绩 (1-10 分 为 1 ,11-20 分为 2 ,30-39 分为 3,。。。90-100 为 10) select sno , cno , grade/10.0+1 as level from sc 6 查询 student 表中的学生共分布在那几个系中。(distinct select distinct sdept from student 7 查询 0001 号学生 1001,1002 课程的成绩 Select cno from sc where sno=’0001’ and (cno=’1001’ or cno=’1002’) 二 统计 1 查询姓名中有“明”字的学生人数。 select count(*) from student where sname like ‘%明%’ 2 计算‘JSJ’系的平均年龄及最大年龄。 Select avg(sage) , max(sage) from student Where sdept=’JSJ’ 3 计算每一门课的总分、平均分,最高分、最低分,按平均分由高到低排列 select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno order by avg(grade) desc 4 计算 1001,1002 课程的平均分。 Select cno , avg(grade) from sc where cno in (‘1001’,’1002’) Group by cno 5 查询平均分大于 80 分的学生学号及平均分 select sc.sno , avg(grade) from sc group by sc.sno having avg(grade)>80 6 统计选修课程超过 2 门的学生学号 select sno from sc group by sno having count(*)>2 7 统计有 10 位成绩大于 85 分以上的课程号。 Select cno from sc where grade>85 group by cno having count(*) =10 8 统计平均分不及格的学生学号 select sno from sc group by sno having avg(grade)80 and cname=’数据库原理’ b: select sname from student where sno in ( select sno from sc where grade>80 and cno in ( select cno from course where cname=’数据库原理’) ) 5 查询平均分不及格的学生的学号,姓名,平均分。 select sno, max(sname) , avg(grade) as avggrade from sc , student where student.sno=sc.sno group by student.sno having avg(grade) 75) B: Select max(sname ) from sc,student where student.sno=sc.sno and Ssex=’女’ Group by student.sno having avg(grade)>75 7 查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出,不能 遗漏) select student.sno,sname,cno,grade from student left join sc ON student.sno=sc.sno and ssex=’男’ 四 嵌套、相关及其他 1 查询平均分不及格的学生人数 select count(*) from student where sno in ( select sno from sc group by sno having avg(grade)=all ( select avg(grade) from sc group by sno ) *4 查询没有选修 1001,1002 课程的学生姓名。 Select sname from student where not exists (Select * from course where cno in (‘1001’,’1002’) and Not exists ( select * from sc where sno=student.sno and cno=course.cno ) ) 5 查询 1002 课程第一名的学生学号 select sno from sc where cno=’1002’ and grade >=all (select grade from sc where cno=’1002’) 6 查询平均分前三名的学生学号 select top 3 sno from sc group by sno order by avg(grade) desc 7 查询 JSJ 系的学生与年龄不大于 19 岁的学生的差集 a: select * from student where sdept=’JSJ’ and sage>19 b: select * from student where sdept=’JSJ’ except select * from student where sage90 union select sno,sname from student where sno in ( select sno from sc group by sno having avg(grade)>85 ) 9 查询每门课程成绩都高于该门课程平均分的学生学号 select sno from student where sno not in ( select sno from sc X where grade ( select avg(sage) from student y where sdept=x.sdept) 以上为查询操作的所有内容,如果有错误或使用时有问题请在评论指出。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |