默认男检查男或女mysql_mysql习题

最新推荐文章于 2022-09-12 14:22:33 发布

山月抚过惊蛰

最新推荐文章于 2022-09-12 14:22:33 发布

阅读量629

点赞数

文章标签：默认男检查男或女mysql

本文链接：https://blog.csdn.net/weixin_33548183/article/details/113940109

版权

如图表创建数据库。

create tableclass(

cidint auto_increment primary key,

captionchar(20)

)engine=innodb default charset=utf8;create tablestudent(

sidint auto_increment primary key,

snamechar(10),

genderchar(10),

class_idint not null,CONSTRAINT fk_cla_cid FOREIGN key (class_id) REFERENCESclass(cid)

)engine=innodb default charset=utf8;select * from student,class where student.class_id =class.cid;create tableteacher(

tidint auto_increment primary key,

tnamechar(20)

)engine=innodb default charset=utf8;create tablecourse(

cidint auto_increment primary key,

cnamechar(10),

teach_idint not null,CONSTRAINT fk_tea_tid FOREIGN key (teach_id) REFERENCESteacher(tid)

)engine=innodb default charset=utf8;select * from course,teacher where course.teach_id =teacher.tid;create tablescore(

sidint auto_increment primary key,

student_idint not null,

course_idint not null,number int,uniqueuq_stu_course (student_id,course_id),CONSTRAINT fk_score_stu FOREIGN key (student_id) REFERENCESstudent(sid),CONSTRAINT fk_score_course FOREIGN key (course_id) REFERENCEScourse(cid)

)engine=innodb default charset=utf8;

数据库创建

1.查询所有成绩大于60分的人；

select * from score where number >60;+-----+------------+-----------+--------+

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

| 3 | 2 | 2 | 100 |

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

成绩大于60

2.查询每个老师有几个学生；

mysql> select teach_id,count(cname) from course group byteach_id;+----------+--------------+

| teach_id | count(cname) |

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

| 1 | 2 |

| 2 | 1 |

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

几人选修

3.查询老师名字与课程的关联(连表)；

mysql> select * from course left join teacher on course.teach_id =teacher.tid;+-----+--------+----------+------+--------+

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

| 1 | 生物 | 1 | 1 | 波多 |

| 2 | 体育 | 1 | 1 | 波多 |

| 3 | 物理 | 2 | 2 | 苍空 |

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

老师对应课程

4.查询男女生人数；

mysql> select gender,count(sid) from student group bygender;+--------+------------+

| gender | count(sid) |

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

| 女 | 2 |

| 男 | 1 |

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

查询男女生人数

5.查看平均成绩大于50的人的信息；

mysql> select grade.student_id,student.sname,grade.av from (select student_id,avg(number)as av from score group by student_id having avg(number) > 50) asgrade-> left join student on grade.student_id =student.sid;+------------+--------+----------+

| student_id | sname | av |

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

| 1 | 钢弹 | 59.5000 |

| 2 | 铁锤 | 100.0000 |

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

平均分+连表

6.查询所有同学的学号、姓名、选课数、总成绩；

mysql> select student.sid,student.sname,count(1),sum(number) from score left join student on student.sid = score.student_id group byscore.student_id;+------+--------+----------+-------------+

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

| 1 | 钢弹 | 2 | 119 |

| 2 | 铁锤 | 1 | 100 |

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

总成绩

7.查询没学过“波多”老师课的同学的学号、姓名；

mysql> select student.sid,student.sname from (select score.student_id as iid from score where course_id not in (select course.cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname = '苍空') group by score.student_id)as i left join student on i.iid =student.sid;+------+--------+

| sid | sname |

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

| 1 | 钢弹 |

| 2 | 铁锤 |

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

#select course.cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname = '苍空'，这个老师的所有课的id，

接着是所有没选这个老师课的学生id的分组列表，最后和学生表连表得到结果

没选的人

mysql> select course.cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname = '苍空';+-----+

| cid |

+-----+

| 3 |

+-----+

这是苍空老师教的课；

mysql> select score.student_id from score where course_id in (select course.cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname = '苍空') group byscore.student_id;

Emptyset (0.00sec)这是从成绩表查询谁上了苍空老师的课，select student.sid,student.sname from student where sid not in(select score.student_id from score where course_id in (select course.cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname = '苍空') group byscore.student_id);

在学生表中排出这几个人就是没选他的课的人了。

选课

8.查询“生物”课程比“体育”课程成绩高的所有学生的学号；

mysql> select A.student_id from

-> (select * from score left join course on score.course_id = course.cid where course.cname='生物') asA-> inner join

-> (select * from score left join course on score.course_id = course.cid where course.cname='体育') asB-> on A.student_id = B.student_id where A.number>B.number;+------------+

| student_id |

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

| 1 |

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

View Code

9.查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

mysql> select score.student_id,student.sname from score left joinstudent-> on score.student_id=student.sid-> where score.course_id =1 or score.course_id =2 group by student_id having count(course_id)>1;+------------+--------+

| student_id | sname |

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

| 1 | 钢弹 |

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

学过两门课

10.查询学过“波多”老师所教的所有课的同学的学号；

select cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname='波多';

波多老师上课的id

mysql> select student_id from score where course_id in

-> (select cid from course left join teacher on course.teach_id = teacher.tid where teacher.tname='波多')-> group by student_id having count(course_id)=

-> (select count(cid) from course left join teacher on course.teach_id = teacher.tid where teacher.tname='波多');+------------+

| student_id |

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

| 1 |

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

成绩表里上过他的课的人都列出来，然后进行分组，学过他的课与他带的课一样多就是要查询的学生id

老师所有的课

11.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号；

mysql> select A.student_id from

-> (select * from score where course_id=1) asA-> inner join

-> (select * from score where course_id=2) asB-> on A.student_id = B.student_id where A.number>B.number;+------------+

| student_id |

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

| 1 |

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

和1差不多

12.查询有课程成绩小于61分的同学的学号、姓名；

mysql> select student.sid,student.sname from student where student.sid in

-> (select student_id from score where number<61 group bystudent_id);+-----+--------+

| sid | sname |

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

| 1 | 钢弹 |

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

低于61分

13.查询没有学全所有课的同学的学号、姓名；

mysql> select student_id,student.sname from score left join student on score.student_id =student.sid-> group by student_id having count(1)

| student_id | sname |

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

| 1 | 钢弹 |

| 2 | 铁锤 |

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

没学全的人

14.查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

mysql> select score.student_id,student.sname fromscore-> left join student on score.student_id =student.sid-> where student_id !=1 and course_id in (select course_id from score where student_id = 1)-> group bystudent_id;+------------+--------+

| student_id | sname |

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

| 2 | 铁锤 |

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

至少一门课和他一样

15.查询至少学过学号为“001”同学所有课的其他同学学号和姓名；

mysql> select score.student_id,student.sname fromscore-> left join student on score.student_id =student.sid-> where student_id !=2 and course_id in (select course_id from score where student_id = 2)-> group bystudent_id-> having count(course_id)=(select count(course_id) from score where student_id = 2);+------------+--------+

| student_id | sname |

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

| 1 | 钢弹 |

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

至少都学过

16.查询和“002”号的同学学习的课程完全相同的其他同学学号;

mysql> select student_id from score where student_id in(-> select student_id from score where student_id !=2 group by student_id having count(1) =(select count(1) from score where student_id = 2))-> and course_id in (select course_id from score where student_id = 2) group by student_id having count(1)=(select count(1) from score where student_id = 2);

Emptyset (0.00sec)

and之前找到和2上课一样所有的id，and之后这个人上的所有课的id也要和2同学一样select count(1) from score where student_id = 2;#2同学上的课个数select student_id from score where student_id !=2 group by student_id having count(1) =(select count(1) from score where student_id = 2);

#和2上课数量一样多的同学

完全一样

17.删除学习“波多”老师课的score表记录；

delete from score where course_id in(select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.name = '波多');

取出老师上的所有课的id再记录表中删除

删除表

18.向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；

insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2)from student where sid not in(select student_id from score where course_id = 2);

没上过课程的信息

19.按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩;

mysql> selectsc.student_id,-> (select number from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as '生物',-> (select number from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as '物理',-> (select number from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as '体育',-> avg(sc.number)-> from score assc-> group by student_id asc;+------------+--------+--------+--------+----------------+

| student_id | 生物 | 物理 | 体育 | avg(sc.number) |

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

| 1 | 60 | NULL | 59 | 59.5000 |

| 2 | NULL | NULL | 100 | 100.0000 |

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

数据库的循环操作

20.查询各科成绩最高和最低的分；

mysql> select student_id,max(number),min(number) from score group bystudent_id;+------------+-------------+-------------+

| student_id | max(number) | min(number) |

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

| 1 | 60 | 59 |

| 2 | 100 | 100 |

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

max，min

在此基础上如果我想要让低于60分的都为0显示，需要用到sql的判断语句；

mysql> select student_id,max(number),min(number),min(number)+1,case when min(number)<60 then 0 else min(number) end as 成绩 from score group bystudent_id;+------------+-------------+-------------+---------------+--------+

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

| 1 | 60 | 59 | 60 | 0 |

| 2 | 100 | 100 | 101 | 100 |

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

case when。。。

21.按各科平均成绩从低到高和及格率的百分数从高到低顺序；

mysql> select course_id, avg(number) as avgnum,sum(case when score.number > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc;+-----------+---------+---------+

| course_id | avgnum | percent |

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

| 1 | 60.0000 | 0.0000 |

| 2 | 79.5000 | 50.0000 |

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

#大于60分标记成1否则为0，count(1)或者用sum(1)这是单独摆成一列为这科考试的总人数，相当于但都有一列全都为1，然后除左边的合格人数1除以右边总人数就是及格率了。

及格率

22.课程平均分从高到低显示；

mysql> select avg(if(isnull(score.number),0,score.number)),teacher.tname fromscore-> left join course on score.course_id =course.cid-> left join teacher on teacher.tid =course.teach_id-> group byscore.course_id;+----------------------------------------------+--------+

| avg(if(isnull(score.number),0,score.number)) | tname |

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

| 60.0000 | 波多 |

| 79.5000 | 波多 |

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

if(isnull(A)B,C)就是sql的三目运算A为空则结果为B，否则为C

View Code

23.查询各科成绩前三名的记录;

mysql> select * from

->(-> select

->student_id,->course_id,-> number,-> 1,-> (select number from score as s2 where s2.course_id = s1.course_id group by s2.number order by s2.number desc limit 0,1) asfirst_num,-> (select number from score as s2 where s2.course_id = s1.course_id group by s2.number order by s2.number desc limit 1,1) asthird_num-> from

-> score ass1-> ) asT-> where T.number >T.third_num;+------------+-----------+--------+---+-----------+-----------+

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

| 2 | 2 | 100 | 1 | 100 | 59 |

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

select number from score as s2 where s2.course_id = s1.course_id group by s2.number order by s2.number desc limit 0,1#第一名并列的成绩并排序

各科前三

24.查询每门课程被选修的学生数;

select course_id, count(1) from score group bycourse_id;+-----------+----------+

| course_id | count(1) |

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

| 1 | 1 |

| 2 | 2 |

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

课程人数

查询出只选修了一门课程的全部学生的学号和姓名;

mysql> select student.sid, student.sname,count(1) fromscore-> left join student on score.student_id =student.sid-> group by student_id having count(1) = 1;+------+--------+----------+

| sid | sname | count(1) |

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

| 2 | 铁锤 | 1 |

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

25.查询男生、女生的人数；

mysql> select gender,count(1) from student group bystudent.gender;+--------+----------+

| gender | count(1) |

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

| 女 | 2 |

| 男 | 1 |

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

男女人数

26.查询姓“钢”的学生名单；

mysql> select sname from student where sname like '钢%';+--------+

| sname |

+--------+

| 钢弹 |

+--------+

27.查询同名同姓学生名单，并统计同名人数；

mysql> select sname,count(1) from student group by sname having count(1)>1;

Emptyset (0.00 sec)

28.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列;

select course_id,avg(if(isnull(number), 0 ,number)) as avg from score group by course_id order by avg asc,course_id desc;

29.查询平均成绩大于85的所有学生的学号、姓名和平均成绩;

mysql> select student_id,sname,avg(if(isnull(number), 0 ,number)) as avg from score left join student on score.student_id = student.sid group by student_id having avg >85;+------------+--------+----------+

| student_id | sname | avg |

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

| 2 | 铁锤 | 100.0000 |

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

先列出所有的平均值，再判断取出

avg大于85

30.查询课程名称为“体育”，且分数低于60的学生姓名和分数;

mysql> select student.sname,score.number fromscore-> left join course on score.course_id =course.cid-> left join student on score.student_id =student.sid-> where score.number < 60 and course.cname = '体育';+--------+--------+

| sname | number |

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

| 钢弹 | 59 |

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

固定课程的成绩不及格

31.查询选修“波多”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

select course.cid from course left join teacher on course.teach_id = teacher.tid where tname='波多';+-----+

| cid |

+-----+

| 1 |

| 2 |

+-----+

先找出波多老师教授的课程

mysql> select sname,number fromscore-> left join student on score.student_id =student.sid-> where score.course_id in (select course.cid from course left join teacher on course.teach_id = teacher.tid where tname='波多') order by number desc limit 1;+--------+--------+

| sname | number |

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

| 铁锤 | 100 |

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

View Code

32.查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

select DISTINCT s1.course_id,s2.course_id,s1.number,s2.number from score as s1, score as s2 where s1.number = s2.number and s1.course_id != s2.course_id;

山月抚过惊蛰

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
默认男检查男或女mysql_mysql习题

如图表创建数据库。create tableclass(cidint auto_increment primary key,captionchar(20))engine=innodb default charset=utf8;create tablestudent(sidint auto_increment primary key,snamechar(10),genderchar(10),class...
复制链接

扫一扫