mysql Cno降序_MySQL any表示至少一个-desc降序

27、查询编号为3-105课程成绩至少高于3-245同学的Cno sno degree 按degree 降低次序排序

select * from score where cno ='3-245';

select * from score where cno ='3-105';

至少，大于其中至少一个

select * from score

where cno ='3-245'9

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

order by degree desc;

28，查询选修编号为3-105且成绩高于3-245课程的同学的cno sno 和degree

---且？

select * from score

where cno ='3-245'9

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

order by degree desc;

29 、as取别名，union求并集

--别名？as

select tname，tsex as sex，tbirthday as birthday from teacher

union

select sname  ,sex,sbirthday from student;

30、查询所有女教师女同学的name sex 和birth

select tname，tsex as sex，tbirthday as birthday from teacher where tsex='女'

union

select sname  ,sex,sbirthday from student where tsex='女';

31、查询成绩比该课程平均成绩低的同学的成绩表

select cno ，avg(degree) from score group by cno;

select *from score;

select * from score a where degree

32、查询所有任课教师的Tname 和Depart。

---课程表钟安排了课程

select *from course ；

select tname,depart from techer where tno in

(select  tno from course )；

33、查询至少有两名男生的班号。

select * from student；

select class from student where ssex='男' group by class having count(*)>1;

34、notlike模糊查询取反 查询student中不信王的同学记录。

select *from student where sname not like '王%'；

35、year函数与now函数 查询学生表中每个学生姓名和年龄

---年龄=当前年份-出生年份

select year(now());

select year(sbirthday) from student;

select sname,year(now())-year(sbirthday) as '年龄' from student;

36、查询学生表中最大最小的birthday的日期值

SELECT sbirthday from student ORDER BY sbirthday;

--max min

select max(sbirthday) as '最大',min(sbirthday) as '最小' from student;

查询最大和最小的差值

select max(year(sbirthday))- min(year(sbirthday)) as '最大年龄差' from student;

37、以班号和年龄从大到小的顺序查询student表中的全部记录

select * from student order by class desc  , sbirthday;

38、查询男教师及所上的课程。

select * from techer where tsex ='男';

39、查询最高分学生的sno，cno和degree列。

select max(degree) from score;

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

40、查询和李军同性别的所有同学的sname。

select ssex from student where sname ='李军';

select sname from student where ssex=(select ssex from student where name='李军')；

select * from student where ssex=(select ssex from student where sname='王花')

+-----+-------+------+---------------------+-------+

| sno | sname | ssex | sbirthday | class |

+-----+-------+------+---------------------+-------+

| 107 | 王花 | 女 | 1988-09-01 00:00:00 | 95038 |

| 109 | 庐山 | 女 | 1998-09-01 00:00:00 | 95041 |

| 112 | 王倩 | 女 | 2001-09-01 00:00:00 | 95028 |

+-----+-------+------+---------------------+-------+

3 rows in set

4、查询和李军同性别并同班的学生snme。

SELECT sname FROM student WHERE ssex = (SELECT ssex FROM student WHERE sname='王花')

and class=(SELECT class FROM student WHERE sname='王花')

+-------+

| sname |

+-------+

| 李华 |

| 王花 |

| 庐山 |

+-------+

+-----+-------+------+---------------------+-------+

| sno | sname | ssex | sbirthday | class |

+-----+-------+------+---------------------+-------+

| 101 | 大花 | 男 | 1988-07-01 00:00:00 | 95138 |

| 103 | 李华 | 女 | 2020-03-10 19:51:30 | 95031 |

| 105 | 王明 | 男 | 1987-09-01 00:00:00 | 95031 |

| 107 | 王花 | 女 | 1988-09-01 00:00:00 | 95031 |

| 108 | 曾华 | 男 | 1987-09-01 00:00:00 | 95033 |

| 109 | 庐山 | 女 | 1998-09-01 00:00:00 | 95031 |

| 112 | 王倩 | 女 | 2001-09-01 00:00:00 | 95028 |

+-----+-------+------+---------------------+-------+

42、查询所有选修计算机导论课程的男同学的成绩表。

select * from student where ssex='男'；

select * from course where cname ='计算机导论'；

select *from score

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

and sno in (select sno from student where ssex='男')；