mysql查询记录_MySQL 查询练习记录

1. select * from student;

2. select name,sex,class from student;

3. select distinct depart from teacher;

4. select * from score where degree between 60 and 80;

或 select * from score where degree > 60 and degree < 80;

5. select * from score where degree in (85,86,88);

6. select * from student where class="95031" or sex="女";

7. select * from student order by class desc;

8. select * from score order by cno asc,degree desc;

9. select count(*) from student where class='95031';

10. select sno, cno from score order by degree desc limit 1;

或 select sno,cno from score where degree=(select max(degree) from score);

11. select cno,avg(degree) from score group by cno;

12. select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like '3%';

13. select sno,degree from score where degree > 70 and degree < 90;

14. select name,cno,degree from student,score where student.sno=score.sno;

15. select sno,name,degree from course,score where course.cno=score.cno;

16. select student.name as sname,course.name as cname,score.degree from student,course,score where student.sno=score.sno and course.cno=score.cno;

17. select cno,avg(degree) from score where sno in (select sno from student where class='95031') group by cno;

18. select * from score where degree > (select degree from score where sno='109' and cno='3-105') and cno='3-105';

19. select * from score where degree > (select degree from score where sno='109' and cno='3-105');

20. select sno,name,birthday from student where year(birthday) in (select year(birthday) from student where sno in (101,108));

21. select * from score where cno=(select cno from course where tno=(select tno from teacher where name='张旭'));

22. select name from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(cno)>=5));

23. select * from student where class in ("95033","95031");

24. select cno,degree from score where degree > 85;

25. select * from score where cno in (select cno from course where tno in (select tno from teacher where depart="计算机系"));

26. select name,prof from teacher where depart='电子工程系' and prof not in (select prof from teacher where depart='计算机系')

union

select name,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系');

27. select * from score

where cno='3-105'

and degree>any(select degree from score where cno='3-245')

order by degree desc;

28. select * from score

where cno='3-105'

and degree>all(select degree from score where cno='3-245');

29. select name,sex,birthday from teacher

union

select name,sex,birthday from student;

30. select name,sex,birthday from teacher where sex='女'

union

select name,sex,birthday from student where sex='女';

31. select * from score a where degree < (select avg(degree) from score b where a.cno=b.cno);

32. select teacher.name as tname,course.name as cname,teacher.depart from teacher,course where teacher.tno=course.tno;

33. select class from student where sex='男' group by class having count(class)>2;

34. select * from student where name not like "王%";

35. select name,year(now())-year(birthday) as '年龄' from student;

36. select max(birthday) as max_bd,min(birthday) as min_bd from student;

37. select * from student order by class desc,birthday;

38. select * from course where tno in (select tno from teacher where sex='男');

39. select * from score where degree=(select max(degree) from score);

40. select name from student where sex=(select sex from student where name='李军');

41. select name from student

where sex=(select sex from student where name='李军')

and class=(select class from student where name='李军');

42. select * from score

where cno=(select cno from course where name='计算机导论')

and sno in (select sno from student where sex='男');

43. select sno,cno,degree,grade from score,grade where degree between low and up;