第四模块MySQL50题作业,以及由作业引申出来的一些高端玩法

核心提示:请问各位高手,如何查询登陆时间间隔不超过5分钟的所有记录

一、表关系

3。表内容如下 —————————– ID LogTime 1 2008/10/10
10:00:00 1 2008/10/10 10:03:00 1 2008/10/10 10:09:00 2 2008/10/10
10:10:00 2 2008/10/10 10:11:00 …… —————————–

先创建如下表,并创建相关约束

请问各位高手,如何查询登陆时间间隔不超过5分钟的所有记录.

  1. 班级表class

    create table class ( cid int primary key auto_increment, caption char(10), grade_id int );

    insert into class values(1,’少一一班’,1),(2,’少二一班’,2),(3,’少三二班’,3),(4,’少四一班’,4),(5,’少五三班’,5);

几道经典的SQL笔试题目

 

表名:成绩表 姓名 课程 分数 张三 语文 81 张三 数学 75 李四 语文 56 李四
数学 90 王五 语文 81 王五 数学 100 王五 英语 49 ……

  1. 学生表student

    create table student ( sid int primary key auto_increment, sname char(10), gender enum(‘男’,’女’) not null, class_id int );

    insert into student values(1,’乔丹’,’女’,1),(2,’艾弗森’,’女’,1),(3,’科比’,’男’,2),(4,’葫芦娃’,’男’,3),(5,’张三丰’,’男’,5),(6,’洞房不败’,’男’,4),(7,’樱木花道’,’男’,2),(8,’松岛菜菜子’,’女’,3),(9,’洞房不败’,’女’,5);

给出成绩全部合格的学生信息,注:分数在60以上评为合格

 

select * from score

  1. 老师表teacher

    create table teacher ( tid int primary key auto_increment, tname char(10) );

where s_name not in

 

(select s_name from score

一、表关系请创建如下表,并创建相关约束1. 班级表class【创建表语句】create
table class(cid int primary key auto_increment,caption
char(10),grade_id int);
【插入记录语句】insert into class
values(1,’少一一班’,1),(2,’少二一班’,2),(3,’少三二班’,3),(4,’少四一班’,4),(5,’少五三班’,5); 

where score60)

  1. 学生表student【创建表语句】create table student(sid int primary key
    auto_increment,sname char(10),gender enum(‘男’,’女’) not null,class_id
    int);
    【插入记录语句】insert into student
    values(1,’乔丹’,’女’,1),(2,’艾弗森’,’女’,1),(3,’科比’,’男’,2),(4,’葫芦娃’,’男’,3),(5,’张三丰’,’男’,5),(6,’洞房不败’,’男’,4),(7,’樱木花道’,’男’,2),(8,’松岛菜菜子’,’女’,3),(9,’洞房不败’,’女’,5); 
  2. 老师表teacher【创建表语句】create table teacher(tid int primary key
    auto_increment,tname char(10));
    【插入记录语句】Insert into teacher
    values(1,’张三’),(2,’李四’),(3,’王五’),(4,’萧峰’),(5,’一休哥’),(6,’诸葛’),(7,’李四’); 
  3. 课程表course【创建表语句】create table course(cid int primary key
    auto_increment,cname char(10),teacher_id int);
    【插入记录语句】insert into course
    values(1,’生物’,1),(2,’体育’,1),(3,’物理’,2),(4,’数学’,3),(5,’语文’,4),(6,’英语’,2),(7,’土遁?沙地送葬’,5),(8,’夏日喂蚊子大法’,3),(9,’麻将牌九扑克千术’,6); 
  4. 成绩表score【创建表语句】create table score(sid int primary key
    auto_increment,student_id int,course_id int,score int);
    【插入记录语句】insert score
    values(1,1,1,60),(2,1,2,21),(3,2,2,99),(4,3,3,56),(5,4,1,56),(6,5,3,94),(7,5,4,40),(8,6,4,80),(9,7,3,37),(10,8,5,100),(11,8,6,89),(12,8,7,0),(13,3,8,45),(14,7,1,89),(15,2,7,89),(16,2,1,61); 
  5. 年级表class_grade【创建表语句】create table class_grade(gid int
    primary key auto_increment,gname char(10));
    【插入记录语句】insert class_grade
    values(1,’一年级’),(2,’二年级’),(3,’三年级’),(4,’四年级’),(5,’五年级’); 
  6. 班级任职表teach2cls【创建表语句】create table teach2cls(tcid int
    primary key auto_increment,tid int,cid int);
    【插入记录语句】insert into teach2cls
    values(1,1,1),(2,1,2),(3,2,1),(4,3,2),(5,4,5),(6,5,3),(7,5,5),(8,6,2),(9,6,4),(10,6,3),(11,4,1),(12,1,4); 
    二、操作表★注:由于样本数量有限,为了能够得到足够的查询结果,所有题目中涉及到“超过”或“以上”字样的,均默认为包含该值(例如:查询教授课程超过2门的老师的id和姓名,视作教授课程数>=2)
    1、自行创建测试数据;(创建语句见”一、表关系”)
    2、查询学生总人数;select count(*) as 学生总人数 from student;
    3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名;【查法1——子查询】select sid,
    snamefrom student where sid
    in(select student_idfrom scorewhere student_id
    in(select student_idfrom scorewhere course_id = (select cid from
    course where cname = ‘生物’) and score >= 60)and course_id = (select
    cid from course where cname = ‘物理’) and score >= 60);
    【查法2——联表】select sid, sname from studentwhere sid
    in (select t1.student_id from (select student_id from scorewhere course_id
    = (select cid from course where cname = ‘生物’) and score >= 60) as
    t1inner join (select student_id from score where course_id = (select
    cid from course where cname = ‘物理’) and score >= 60) as
    t2on t1.student_id=t2.student_id);
    4、查询每个年级的班级数,取出班级数最多的前三个年级select class.grade_id, class_grade.gname, count(class.cid)
    as 班级数from class inner join class_grade on
    class.grade_id=class_grade.gidgroup by class.grade_idorder
    by count(class.cid) desclimit 3;
    5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩select stu.sid, stu.sname, avg(score)
    as 平均成绩from student as stu inner join score as scoon stu.sid =
    sco.student_idgroup by stu.sidhaving avg(score)
    = (select avg(score) from score group by student_idorder by avg(score)
    desclimit 1) or avg(score) = (select avg(score) from score group
    by student_idorder by avg(score) asclimit 1);
    6、查询每个年级的学生人数;select t1.gname, count(s.sid) as
    学生人数from (select * from class as c inner join class_grade as g on
    c.grade_id = g.gid) as t1inner join student as s on t1.cid =
    s.class_idgroup by t1.gid;
    7、查询每位学生的学号,姓名,选课数,平均成绩;select stu.sid as
    学号,stu.sname as 姓名,count(sco.course_id) as 选课数,avg(sco.score) as
    平均成绩from student as stu left join score as sco on stu.sid =
    sco.student_idgroup by sco.student_id;
    8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数;select t1.sname
    as 姓名,t2.cname as 课程名,t1.score as 分数from (select stu.sid,
    stu.sname, sco.course_id, sco.score from student as stu inner join
    score as sco on stu.sid = sco.student_id where stu.sid=2) as t1inner
    joincourse as t2 on t1.course_id = t2.cidgroup by t2.cidhaving score in
    (max(score),min(score));
    9、查询姓“李”的老师的个数和所带班级数;select count(te.tid) as
    姓李老师个数,count(tc.cid) as 所带班级数from teacher as te inner join
    teach2cls as tcon te.tid = tc.tidwhere te.tname regexp “^李.*”group
    by te.tid;
    10、查询班级数小于5的年级id和年级名;select c.grade_id as
    年级id,g.gname as 年级名from class as c inner join class_grade as gon
    c.grade_id = g.gidgroup by c.grade_idhaving count(c.cid)<5;
    11、查询班级信息,包括班级id、班级名称、年级、年级级别(12为低年级,34为中年级,56为高年级),示例结果如下;select cid
    as 班级id,caption as 班级名称,gname as 年级,casewhen g.gid in (1,2) then
    ‘低年级’when g.gid in (3,4) then ‘中年级’when g.gid in (5,6) then
    ‘高年级’else ‘其他’ end as 年级级别from class as c inner join
    class_grade as gon c.grade_id = g.gid;
    12、查询学过“张三”老师2门课以上的同学的学号、姓名;select stu.sid as
    学号,stu.sname as 姓名from student as stu inner join score as sco on
    stu.sid = sco.student_idwhere sco.course_id
    in (select c.cidfrom teacher as t inner join course as con t.tid =
    c.teacher_idwhere t.tname = ‘张三’)group
    by stu.sidhaving count(sco.course_id) >= 2;
    13、查询教授课程超过2门的老师的id和姓名;selecttid as id,tname as
    姓名from teacher as t inner join course as c on t.tid =
    c.teacher_idgroup by c.teacher_idhaving count(c.cid) >= 2;
    14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名;select sid as
    学号,sname as 姓名from studentwhere sid
    in (select student_id from scorewhere student_id
    in (select student_id from scorewhere course_id = 1)and course_id =
    2);
    15、查询没有带过高年级的老师id和姓名;select tid as 老师id,tname as
    姓名from teacherwhere tid not in (select tc.tidfrom class as c inner
    join teach2cls as tc on c.cid = tc.cidwhere c.grade_id in (5,6));
    16、查询学过“张三”老师所教的所有课的同学的学号、姓名;select distinctstu.sid
    as 学号,stu.sname as 姓名from student as stu inner join score as sco on
    stu.sid = sco.student_idwhere sco.course_id
    in (select c.cid from teacher as t inner join course as c on t.tid =
    c.teacher_idwhere t.tname = “张三”);
    17、查询带过超过2个班级的老师的id和姓名;select tid as id,tname as
    姓名from teacherwhere tid in (select tid from teach2clsgroup
    by tidhaving count(cid) >= 2);
    18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名;select sid
    as 学号,sname as 姓名from studentwhere sid
    in(select t1.student_idfrom (select * from scorewhere course_id = 1)
    as t1inner join (select * from score where course_id = 2) as
    t2on t1.student_id = t2.student_idwhere t1.score > t2.score);
    19、查询所带班级数最多的老师id和姓名;select tid as id,tname as
    姓名from teacher where tid in (select tidfrom teach2clsgroup
    by tidhaving count(cid) = (select count(cid)from teach2clsgroup
    by tidorder by count(cid) desclimit 1));
    20、查询有课程成绩小于60分的同学的学号、姓名;select sid as 学号,sname
    as 姓名from studentwhere sid
    in (select student_idfrom score where score < 60);
    21、查询没有学全所有课的同学的学号、姓名;select sid as 学号,sname as
    姓名from studentwhere sid in (select student_idfrom scoregroup
    by student_idhaving count(course_id) != (select count(cid) from
    course));
    22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名;select sid
    as 学号,sname as 姓名from studentwhere sid
    in (select student_idfrom scorewhere course_id
    in (select course_id from scorewhere student_id = 1));
    23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名;select sid
    as 学号,sname as 姓名from studentwhere sid
    in (select student_idfrom scorewhere course_id
    in (select course_id from scorewhere student_id = 1) and student_id
    != 1);
    24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名;select sid
    as 学号,sname as 姓名from studentwhere sid
    in (select student_idfrom scorewhere student_澳门新葡亰3522平台游戏,id != 2group
    by student_idhaving group_concat(course_id order by course_id asc)
    = (select group_concat(course_id order by course_id
    asc)from scorewhere student_id = 2group by student_id));
    25、删除学习“张三”老师课的score表记录;delete from scorewhere course_id
    in (select c.cid from teacher as t inner join course as con t.tid =
    c.teacher_idwhere t.tname = ‘张三’);
    26、向score表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“2”课程的同学学号;②插入“2”号课程的平均成绩;【插入第一条】insert
    into score(student_id, course_id,
    score) values((select sidfrom studentwhere sid not
    in(select s.student_idfrom score as swhere s.course_id = 2)order
    by sid desclimit 0,1),2,(select avg(s.score)from score as
    swhere s.course_id = 2));【插入第二条】insert into score(student_id,
    course_id, score) values((select sidfrom studentwhere sid not
    in(select s.student_idfrom score as swhere s.course_id = 2)order
    by sid desclimit 1,1),2,(select avg(s.score)from score as
    swhere s.course_id = 2));
    【改limit后的第一个参数值,可继续插入第三、四、…条】
    27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示:
    学生ID,语文,数学,英语,有效课程数,有效平均分;【这里题目有歧义:有效课程数和有效平均分是仅以这3门课来统计,还是以学生所有科目来统计】【解一:仅以这3门课来统计】select t2.sid
    as 学生ID, sum(case when t1.cname = ‘语文’ then t1.score else null end)
    as 语文,sum(case when t1.cname = ‘数学’ then t1.score else null end) as
    数学,sum(case when t1.cname = ‘英语’ then t1.score else null end) as
    英语,count(case when t1.cname in (‘语文’,’数学’,’英语’) then 1 else null
    end) as 有效课程数,avg(case when t1.cname in (‘语文’,’数学’,’英语’) then
    t1.score else null end) as 有效平均分from (select * from score as s
    inner join course as con s.course_id = c.cid) as t1right join student
    as t2on t1.student_id = t2.sidgroup by t2.sidorder by avg(case when
    t1.cname in (‘语文’,’数学’,’英语’) then t1.score else null end) asc;
    【解二:以该学生所有科目来统计】select t2.sid as 学生ID, sum(case when
    t1.cname = ‘语文’ then t1.score else null end) as 语文,sum(case when
    t1.cname = ‘数学’ then t1.score else null end) as 数学,sum(case when
    t1.cname = ‘英语’ then t1.score else null end) as 英语,count(t1.score)
    as 有效课程数,avg(t1.score) as 有效平均分from (select * from score as s
    inner join course as con s.course_id = c.cid) as t1right join student
    as t2on t1.student_id = t2.sidgroup by t2.sidorder by avg(t1.score)
    asc;
    28、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;select course_id
    as 课程ID,max(score) as 最高分,min(score) as 最低分from scoregroup
    by course_id;
    29、按各科平均成绩从低到高和及格率的百分数从高到低顺序;【这里优先按平均成绩从低到高排序,若遇到平均成绩相同的则按及格率百分数从高到低排序】select course_id
    as 课程ID,avg(score) as 平均成绩,concat(100*count(case when
    score>=60 then 1 else null end)/count(score),”%”) as
    及格率from scoregroup by course_idorder by avg(score) asc, count(case
    when score>=60 then 1 else null end)/count(score) desc;
    30、课程平均分从高到低显示(显示任课老师);select t1.cname as
    课程名称,avg(t2.score) as 平均分,t1.tname as 任课老师from (select *
    from teacher as t inner join course as con t.tid = c.teacher_id) as
    t1 inner join score as t2on t1.cid = t2.course_idgroup
    by t2.course_idorder by avg(t2.score) desc;
    31、查询各科成绩前三名的记录(不考虑成绩并列情况)【本题与44题类似,不会做,于是百度了下”如何在mysql中查询每个分组的前几名”,参照其中的一个方法,写出了答案】【注:这里仍然是按照score表默认的排序,即sid的排序】select*from scorewhere(selectcount(*)from score
    as swheres.course_id = score.course_idands.score <=
    score.score)<= 3;
    32、查询每门课程被选修的学生数;select cname as
    课程名,count(s.student_id) as 选修学生数from course as c left join
    score as s on c.cid = s.course_idgroup by c.cid;
    33、查询选修了2门以上课程的全部学生的学号和姓名;select sid as
    学号,sname as 姓名from studentwhere sid
    in (select student_id from scoregroup
    by student_idhaving count(course_id) >= 2);
    34、查询男生、女生的人数,按倒序排列;select gender,
    count(sid)from studentgroup by genderorder by count(sid) desc;
    35、查询姓“张”的学生名单;【查法1——正则】select sname from studentwhere sname
    regexp “^张.*”;
    【查法2——like】select snamefrom studentwhere sname like “张%”;
    36、查询同名同姓学生名单,并统计同名人数;select sname as
    姓名,count(sid) as 同名人数 from studentgroup by snamehaving count(sid)
    > 1;
    37、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;select avg(score),course_idfrom scoregroup
    by course_idorder by avg(score) asc, course_id desc;
    38、查询课程名称为“数学”,且分数低于60的学生姓名和分数;select stu.sname
    as 学生姓名,sco.score as 分数from student as stu inner join score as
    scoon stu.sid = sco.student_idwhere sco.course_id
    = (select cid from course where cname = ‘数学’)and sco.score < 60;
    39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名;select sid
    as 学号,sname as 姓名from studentwhere sid
    in (select student_id from scorewhere course_id = 3 and score >=
    80);
    40、求选修了课程的学生人数select count(1) as
    学生人数from(select distinct student_idfrom score) as t1;
    41、查询选修“王五”老师所授课程的学生中,成绩最高和最低的学生姓名及其成绩;select stu.sname
    as 学生姓名,sco.score as 成绩from student as stu inner join score as
    scoon stu.sid = sco.student_idwhere score
    in ((select max(score)from scorewhere course_id
    in (select c.cidfrom teacher as t inner join course as con t.tid =
    c.teacher_id where t.tname =
    ‘王五’)), (select min(score)from scorewhere course_id
    in (select c.cidfrom teacher as t inner join course as con t.tid =
    c.teacher_id where t.tname = ‘王五’)));
    42、查询各个课程及相应的选修人数;select cname as
    课程名,count(s.student_id) as 选修学生数from course as c left join
    score as s on c.cid = s.course_idgroup by c.cid;
    43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;select student_id
    as 学号,course_id as 课程号,score as 学生成绩from scoregroup
    by scorehaving count(student_id) > 1;
    44、查询每门课程成绩最好的前两名学生id和姓名;【注:这里指定了前两名,所以若出现多名同分的学生也只取倒序排的默认前2名】【与31题类似…不会写,于是百度了下”如何在mysql中查询每个分组的前几名”,参照其中一种比较高端且高效的自定义变量的方法,写出了答案】
    set @num := 0, @cname := ”;selectt2.cid as 课程ID,t2.cname as
    课程名,t1.sid as 学生ID,t1.sname as 学生名,t1.score as 成绩,@num :=
    if(@cname = t2.cname, @num + 1, 1) as 排名,@cname := t2.cname as
    课程名确认from (select stu.sid, stu.sname, sco.course_id,
    sco.score from student as stu inner join score as scoon stu.sid =
    sco.student_id) as t1right joincourse as t2on t1.course_id =
    t2.cidgroup byt2.cid, t1.score, t1.snamehaving排名 <= 2;

或者: select * from score where s_name in

45、检索至少选修两门课程的学生学号;select sid as
学号from studentwhere sid in (select student_id from scoregroup
by student_idhaving count(course_id) >= 2);
46、查询没有学生选修的课程的课程号和课程名;select cid as 课程号,cname
as 课程名from coursewhere cid not in (select distinct
course_idfrom score);
47、查询没带过任何班级的老师id和姓名;selecttid as 老师id,tname as
姓名from teacherwhere tid not in (select distinct tidfrom teach2cls);
48、查询有两门以上课程超过80分的学生id及其平均成绩;select student_id
as 学生id,avg(score) as 平均成绩from scorewhere student_id
in (select student_idfrom scorewhere score >= 80group
by student_idhaving count(course_id) >= 2)group by student_id;
49、检索“3”课程分数小于60,按分数降序排列的同学学号;select
distinctstudent_id as 学号from scorewhere course_id = 3 and score <
60order by score desc;
50、删除编号为“2”的同学的“1”课程的成绩;delete from scorewhere student_id
= 2 and course_id = 1;
51、查询同时选修了物理课和生物课的学生id和姓名;select sid as
学生id,sname as 姓名from studentwhere sid
in (select student_idfrom scorewhere course_id = (select cid from
course where cname = ‘生物’))and sid
in (select student_idfrom scorewhere course_id
= (select cid from course where cname = ‘物理’));

(select s_name from score

group by s_name

having min(score)=60)

表名:商品表 名称 产地 进价 苹果 烟台 2.5 苹果 云南 1.9 苹果 四川 3 西瓜
江西 1.5 西瓜 北京 2.4 ……

给出平均进价在2元以下的商品名称

select 名称 from 商品表 group by 名称 having avg(进价) 2

表名:高考信息表 准考证号 科目 成绩 2006001 语文 119 2006001 数学 108
2006002 物理 142 2006001 化学 136 2006001 物理 127 2006002 数学 149
2006002 英语 110 2006002 语文 105 2006001 英语 98 2006002 化学 129 ……

给出高考总分在600以上的学生准考证号

select 准考证号 from 高考信息表 group by 准考证号 having sum(成绩) 600

表名:高考信息表 准考证号 数学 语文 英语 物理 化学 2006001 108 119 98
127 136 2006002 149 105 110 142 129 ……