MySQL查询语句练习2

最新推荐文章于 2024-07-07 13:53:42 发布

Long_UP

最新推荐文章于 2024-07-07 13:53:42 发布

阅读量586

点赞数 5

分类专栏： MySQL数据库应用技术文章标签：数据库 mysql

本文链接：https://blog.csdn.net/Long_UP/article/details/106147538

版权

MySQL数据库应用技术专栏收录该内容

14 篇文章 3 订阅

订阅专栏

没有数据库先执行50q数据库

查询所有学生的Sname、Cno和Degree列.
Select sname,cno ,degree from student inner join score on student.sno=scire.sno;
查询“95033”班所选课程的平均分
Select cno,avg(degree) as avg from score where sno in(select sno from student where class=’95033’)group by cno;
查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录
Select degree from score where sno=’109’ and cno=’3-105’;
查询score中选学一门以上课程的同学中分数为非最高分成绩的记录
Select sno from score group by sno having count(*)>1;
Select * from score where sno in (Select sno from score group by sno havingcount(*)>1)
查询所有教师和同学的name、sex和birthday
select sname as name,ssex as sex,sbirthday as birthday from student union select tname as name,tsex as sex,tbirthday as birthday from teacher;
查询所有“女”教师和“女”同学的name、sex和birthday
select sname as name,ssex as sex,sbirthday as birthday from student where ssex='女' unio
查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
select * from score where DEGREE>(select DEGREE from score where sno='109'and CNO='3-105');
查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列
Select sno,sname,sbirthday from student where year(sbirthday)=(select year(sbirthday) from student where sno=108);
查询Student表中不姓“王”的同学记录
Select * from student where sname not like '王%'
查询“张旭“教师任课的学生成绩
Select degree from score,teacher,course where score.cno=course.cno and course.tno=teacher.tno and teacher.tname='张旭';