Mysql 练习题(二)

1. 题目概览

  1. 查询”01”课程比”02”课程成绩高的学生的信息及课程分数
  2. 查询”01”课程比”02”课程成绩低的学生的信息及课程分数
  3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
  4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
  5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
  6. 查询”李”姓老师的数量
  7. 查询学过”张三”老师授课的同学的信息
  8. 查询没学过”张三”老师授课的同学的信息
  9. 查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息
  10. 查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息
  11. 查询没有学全所有课程的同学的信息
  12. 查询至少有一门课与学号为”01”的同学所学相同的同学的信息
  13. 查询和”01”号的同学学习的课程完全相同的其他同学的信息
  14. 查询没学过”张三”老师讲授的任一门课程的学生姓名
  15. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
  16. 检索”01”课程分数小于60,按分数降序排列的学生信息
  17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
  18. 查询各科成绩最高分、最低分和平均分:
    以如下形式显示:
    课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
    及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
  19. 按各科成绩进行排序,并显示排名
  20. 查询学生的总成绩并进行排名
  21. 查询不同老师所教不同课程平均分从高到低显示
  22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
  23. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
  24. 查询学生平均成绩及其名次
  25. 查询各科成绩前三名的记录
  26. 查询每门课程被选修的学生数
  27. 查询出只有两门课程的全部学生的学号和姓名
  28. 查询男生、女生人数
  29. 查询名字中含有”风”字的学生信息
  30. 查询同名同性学生名单,并统计同名人数
  31. 查询1990年出生的学生名单(注:Student表中Sage列的类型是datetime)
  32. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号
  33. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
  34. 查询课程名称为”数学”,且分数低于60的学生姓名和分数
  35. 查询所有学生的课程及分数情况
  36. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
  37. 查询不及格的课程
  38. 查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名;
  39. 求每门课程的学生人数
  40. 查询选修”李四”老师所授课程的学生中,成绩最高的学生信息及其成绩
  41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
  42. 查询每门课程成绩最好的前两名
  43. 统计每门课程的学生选修人数(超过5人的课程才统计)
    要求输出课程号和选修人数,查询结果按人数降序排列,
    若人数相同,按课程号升序排列
  44. 检索至少选修两门课程的学生学号
  45. 查询选修了全部课程的学生信息
  46. 查询各学生的年龄
  47. 查询本周过生日的学生
  48. 查询下周过生日的学生
  49. 查询本月过生日的学生
  50. 查询下月过生日的学生

2. 创建库

/*

# 创建数据库:
drop database if exists student1;
create database if not exists Student1;

show databases;
use Student1 ;


#创建测试数据
create table Student(Sno varchar(10),Sname varchar(10),Sage datetime,Ssex nvarchar(10));

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');


create table Course(Cno varchar(10),Cname varchar(10),Tno varchar(10));

insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');


create table Teacher(Tno varchar(10),Tname varchar(10));

insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');


create table SC(Sno varchar(10),Cno varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

# 查看数据:
select * from student ;
select * from course;
select * from teacher;
select * from SC;
*/

3. 练习题


use Student1 ;

#1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:
select a.*,b.score as '01-score',c.score as '02-score'
    from student a
    inner join SC b on a.SNO=b.SNO and b.cno='01'
    inner join SC c on a.SNO=c.SNO and c.cno='02'
    where b.score>c.score ;

#2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.*,b.score as '01-score',c.score as '02-score'
    from student a
    inner join SC b on a.SNO=b.SNO and b.cno='01'
    inner join SC c on a.SNO=c.SNO and c.cno='02'
    where b.score<c.score ;

#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.sno,a.sname,avg(ifnull(b.score,0)) as '平均成绩'
    from student a
    left join SC b using(SNO)
    group by a.sno
    having avg(ifnull(b.score,0))>=60;

#4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select sno,sname,avg(ifnull(b.score,0)) as avgnum
    from student a
    left join SC as b using(sno)
    group by a.sno
    having avg(ifnull(b.score,0))<=60;

#5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select sno,
    sname,
    count(cno) as '选课总数',
    sum(score) as '总成绩'
    from student a
    left join SC b using(sno)
    group by sno
    order by sum(score) desc;

#6、查询"李"姓老师的数量 
select count(*) as '李老师人数' from teacher where TNAME like '李%';

#7、查询学过"张三"老师授课的同学的信息    
select a.* from student a
     inner join (SC b,course c,teacher d) 
     on a.sno=b.sno 
     and b.Cno=c.CNO 
     and c.TNO=d.TNO
     where d.tname='张三';

select a.* from student a
    inner join SC as b on a.sno=b.sno
    inner join course as c on b.Cno=c.CNO
    inner join teacher as d on c.TNO=d.TNO
    where d.TNAME='张三';

#8、查询没学过"张三"老师授课的同学的信息
select aa.* from student aa where not exists (
    select * from SC as b
        inner join course as c on b.Cno=c.CNO
        inner join teacher as d on c.TNO=d.TNO
        where d.TNAME='张三' and aa.sno=b.sno
);

#9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select a.* from student a
    inner join SC b on a.sno=b.sno and b.Cno='01' where exists(
        select * from SC c where c.Cno='02' and a.sno=c.sno );

select a.* from student a
    inner join SC b on a.sno=b.sno and b.cno='01'
    inner join SC c on a.sno=c.sno and c.Cno='02';

#10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select a.* from student a
    inner join SC b on a.sno=b.sno and b.cno='01' where not exists(
        select * from SC c where c.cno='02' and a.SNO=c.sno);

select a.* from student a
    left join SC b on a.sno=b.sno and b.cno='01'
    left join SC c on a.sno=c.sno and c.Cno='02' 
    where c.Cno is null and b.cno='01';

#11、查询没有学全所有课程的同学的信息 
select a.* from student a
    left join SC b using(sno) 
    group by a.sno 
    having count(cno)<(    
        select count(cno) from course);

SELECT * FROM student a
    LEFT JOIN sc b ON a.sno=b.sno
    LEFT JOIN (SELECT COUNT(1) anum FROM course) c
    ON 1=1
    GROUP BY 1,2,3,4
    HAVING MAX(c.anum)>COUNT(b.cno);

#12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select a.* from student a
    left join SC b on a.sno=b.sno where exists ( 
        select * from SC where sno='01' and b.cno=cno)
    group by a.sno;

#13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select a.* from student a
    inner join SC b using(sno)
    group by sno
    having count(cno)=(select count(*) from SC where sno='01') and Sno != '01';

SELECT aa.sno,aa.sname,aa.sage,aa.ssex
    FROM (
        SELECT a.*,COUNT(b.cno) AS snum FROM student a
        INNER JOIN sc b ON a.sno=b.sno 
        WHERE EXISTS( SELECT 1 FROM sc WHERE sno='01' AND cno=b.cno ) GROUP BY 1,2,3,4
          ) aa
    INNER JOIN (
        SELECT a.*,COUNT(b.cno) AS anum FROM student a
        INNER JOIN sc b ON a.sno=b.sno GROUP BY 1,2,3,4
        ) bb ON aa.sno=bb.sno
    INNER JOIN (
        SELECT COUNT(1) AS num1 FROM sc WHERE sno='01'
        ) cc ON 1=1 
    WHERE aa.snum=bb.anum AND aa.snum=cc.num1 and aa.sno !='01';

SELECT a.*,COUNT(b.cno) AS anum
       ,SUM(CASE WHEN EXISTS(SELECT 1 FROM sc WHERE sno='01' AND cno=b.cno)THEN 1 ELSE 0 END) AS snum
       ,MAX(c.num1) AS num1
FROM student a
INNER JOIN sc b
ON a.sno=b.sno
INNER JOIN (SELECT COUNT(1) AS num1 FROM sc WHERE sno='01') c
ON 1=1
GROUP BY 1,2,3,4
HAVING anum=snum AND anum=num1;

#14、查询没学过"张三"老师讲授的任一门课程的学生姓名 
select * from student aa where not exists (
    select * from teacher a
        inner join course b using(tno) 
        inner join SC c using(cno) 
    where a.Tname='张三' and aa.sno=c.sno
    );

select aa.* from student aa
    left join(
        select * from teacher a
        inner join course b using(tno) 
        inner join SC c using(cno) 
        where a.Tname='张三' 
    ) bb
    on aa.sno =bb.sno where bb.tno is null;

SELECT a.* FROM student a
    LEFT JOIN(
           SELECT * FROM student a
           LEFT JOIN sc b ON a.sno=b.sno
           LEFT JOIN course c ON b.cno=c.cno
           LEFT JOIN teacher d ON c.tno=d.tno AND d.tname='张三'
           WHERE d.tno IS NOT NULL
           ) b
    ON a.sno=b.sno WHERE b.sno IS NULL;

#15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩 
select  a.sno,a.sname,avg(b.score) as avgnum  
    from student a
    left join SC b on a.Sno=b.Sno and b.score<60
    group by a.sno
    having count(cno)>=2;

select a.sno,a.sname,avg(b.score) as avgnum  
    from student a
    left join SC b on a.Sno=b.Sno
    group by a.sno
    having  SUM(CASE WHEN b.score>=60 THEN 0 ELSE 1 END)>=2;

#16、检索"01"课程分数小于60,按分数降序排列的学生信息    
select a.*,b.score from student a 
    inner join SC b using(sno) 
    where b.cno='01' and b.score<60  
    order by score desc;

#17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩    
select a.*,b.sc01,c.sc02,d.sc03,avg(dd.score) as avgN 
    from student a
    left join (
        select sno,score as 'sc01' from SC where cno='01'
        ) b on a.Sno=b.sno
     left join (
        select sno,score as 'sc02' from SC where cno='02'
        ) c on a.sno=c.sno
    left join (
        select sno,score as 'sc03' from SC where cno='03'
        ) d on a.sno=d.sno
    left join SC dd on a.sno=dd.sno
    group by sno
    order by avg(dd.score) desc;

SELECT a.*
       ,SUM(CASE WHEN b.cno='01' THEN b.score ELSE 0 END) AS s01
       ,SUM(CASE WHEN b.cno='02' THEN b.score ELSE 0 END) AS s02
       ,SUM(CASE WHEN b.cno='03' THEN b.score ELSE 0 END) AS s03
       ,AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) avs
FROM student a
LEFT JOIN sc b
ON a.sno=b.sno
GROUP BY 1,2,3,4
ORDER BY avs DESC;

/*18、查询各科成绩最高分、最低分和平均分:
以如下形式显示:
课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 */
select cno as '课程ID',
    Cname as '课程name',
    max(score) as '最高分',
    min(score) as '最低分',
    avg(score) as '平均分',
    sum(case when score >=60 then 1 else 0 end)/count(cno) as '及格率',
    sum(case when score >=70 and score<80 then 1 else 0 end)/count(cno) as '中等率',
    sum(case when score >=80 and score<90 then 1 else 0 end)/count(cno) as '优良率',
    sum(case when score >=90 then 1 else 0 end)/count(cno) as '优秀率'
    from course a
    inner join SC b using(cno)
 group by cno;

#19、按各科成绩进行排序,并显示排名
SET @yw:=0;
set @sx:=0;
set @yy:=0;
SELECT aa.*,
    (case cno when '01' then @yw:=@yw+1  
              when '02' then @sx:=@sx+1 
              when '03' then @yy:=@yy+1 else 0 end) as rank 
    FROM(
    SELECT a.*,b.Sno,b.score FROM course a 
    INNER JOIN sc b ON a.cno=b.cno
    ORDER BY b.cno,score DESC,sno
)aa;

#第二种:
SELECT a.*,c.cname,count(b.cno)+1 as rank FROM sc a
    left JOIN sc b ON a.cno=b.cno and a.score<b.score
    left join course c on a.Cno=c.Cno 
    group by 1,2,3
    order by a.cno,a.score desc;

#20、查询学生的总成绩并进行排名
set @b:=0;
select bb.*,@b:=@b+1 as '名次' from (
select a.*,
    sum(case cno when '01' then score else 0 end) as '语文',
    sum(case cno when '02' then score else 0 end) as '数学',
    sum(case cno when '03' then score else 0 end) as '英语',
    sum(score) as '总成绩'
    from student a
    left  join sc b on a.sno=b.sno group by 1,2,3,4 order by sum(score) desc
) bb;

#21、查询不同老师所教不同课程平均分从高到低显示 
select b.cno,b.Cname,b.Tno,c.Tname,
    avg(score) as '平均分'
    from SC a 
    inner join course b on a.Cno=b.Cno
    inner join teacher c on b.Tno=c.Tno
    group by a.cno
    order by 平均分 desc;

#22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT a.*,b.cname,b.score
FROM student a
INNER JOIN (
       SELECT a.*
              ,c.cname
              ,COUNT(b.cno)+1 AS tp
       FROM sc a
       LEFT JOIN sc b
       ON a.cno=b.cno AND a.score<b.score
       LEFT JOIN course c
       ON a.cno=c.cno
       GROUP BY 1,2,3,4
       HAVING tp IN(2,3)
       ORDER BY a.cno,tp)b
ON a.sno=b.sno order by cname,score desc;

select aa.*,c.Cno,c.Cname,a.score
    from student aa
    inner join SC a on aa.Sno=a.Sno
    inner join course c on c.Cno=a.Cno
    where (select count(*) from SC b where b.cno=a.cno and b.score<a.score) in(2,3)
    order by c.cname,a.score desc;

#23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select 
    cno as '课程号',
    cname as '课程名',
    sum(case when score>=85 then 1 else 0 end) as '[100-85]',
    sum(case when score>=70 and score<85 then 1 else 0 end) as '[85-70]',
    sum(case when score>=60 and score<70 then 1 else 0 end) as '[70-60]',
    sum(case when score<60 then 1 else 0 end) as '[0-60]',
    concat(round(sum(case when score>=85 then 1 else 0 end)/count(*)*100,1),'%') as '[100-85]%',
    concat(round(sum(case when score>=70 and score<85 then 1 else 0 end)/count(*)*100,1),'%') as '[85-70]%',
    concat(round(sum(case when score>=60 and score<70 then 1 else 0 end)/count(*)*100,1),'%') as '[70-60]%',
    concat(round(sum(case when score<60 then 1 else 0 end)/count(*)*100,1),'%') as '[0-60]%'
    from SC a 
    inner join course b using(cno) 
    group by cno,cname;

#24、查询学生平均成绩及其名次
#First method:
select aa.*,
    @rownum := @rownum + 1 AS rank
from (
    select a.*,
        avg(ifnull(score,0)) as avgnum
        from student a
        left join SC b  using(sno)
        group by sno
        order by avgnum desc
    )aa,
    (SELECT @rownum:= 0) r;

#Second method:
SELECT a.*,COUNT(b.sno)+1 as rank
FROM (
       SELECT a.*,AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) AS ascore
       FROM student a
       LEFT JOIN sc b
       ON a.sno=b.sno
       GROUP BY 1,2,3,4
      )a
LEFT JOIN(
       SELECT a.*,AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) AS ascore
       FROM student a
       LEFT JOIN sc b
       ON a.sno=b.sno
       GROUP BY 1,2,3,4
      )b
ON a.ascore<b.ascore
GROUP BY 1,2,3,4,5 order by ascore desc;

#25、查询各科成绩前三名的记录
select a.sno,c.cno,c.cname,a.score
    from SC a
    inner join course c on c.Cno=a.Cno
    where (select count(*)+1 from SC b where b.cno=a.cno and b.score>a.score)<=3
    order by c.cname,a.score desc;

SELECT a.*,COUNT(b.cno)+1 AS ascore
FROM sc a
LEFT JOIN sc b
ON a.cno=b.cno AND a.score<b.score
GROUP BY 1,2,3
HAVING ascore<=3
ORDER BY a.cno,ascore;

#26、查询每门课程被选修的学生数 
select a.Cname as '课程',
    count(*) as '学生数'
    from course a,SC b where a.cno=b.cno
    group by a.cno;

#27、查询出只有两门课程的全部学生的学号和姓名 
select a.sno,a.Sname
    from student a
    left join SC b using(sno)
    group by sno
    having count(cno)=2;

#28、查询男生、女生人数 
select ssex,count(*) as '人数' from student group by ssex;

select
    sum(case when Ssex='男' then 1 else 0 end) as '男生数',
    sum(case when Ssex='女' then 1 else 0 end) as '女生数'
from student;

#29、查询名字中含有"风"字的学生信息
select * from student where Sname like '%风%';

#30、查询同名同性学生名单,并统计同名人数
select sname,ssex,count(*) as '同名人数' 
    from student 
    group by sname,ssex 
    having count(*)>1;

#31、查询1990年出生的学生名单(注:Student表中Sage列的类型是datetime) 
select * from student where  year(Sage)=1990;

#32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号
select cno,round(avg(score),2) as avgnum 
    from course a
    left join SC b using(cno)
    group by cno
    order by avgnum desc,cno;

#33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select sno,Sname,avg(score) as ascore 
    from student a
    left join SC b using(sno)
    group by sno
    having ascore>=85;

#34、查询课程名称为"数学",且分数低于60的学生姓名和分数 
select a.Sname,c.Cname,b.score
    from student a
    left join SC b on a.Sno=b.Sno
    left join course c on b.Cno=c.Cno
    where b.score<60 and c.Cname='数学';

#35、查询所有学生的课程及分数情况
select a.Sname,c.Cname,b.score
    from student a
    left join SC b on a.Sno=b.Sno
    left join course c on b.Cno=c.Cno;

#36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
select a.Sname,c.Cname,b.score
    from student a
    left join SC b on a.Sno=b.Sno
    left join course c on b.Cno=c.Cno
    where b.score>70
    order by sname,cname;

#37、查询不及格的课程
select a.Sname,c.Cname,b.score
    from student a
    left join SC b on a.Sno=b.Sno
    left join course c on b.Cno=c.Cno
    where b.score<70
    order by sname,cname;

#38、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名; 
select a.sno,a.sname,b.score from student a
    inner join SC b using(sno)
    where b.cno='01' and b.score>=80;

#39、求每门课程的学生人数 
select a.Cname,count(*) as '人数'
    from course a
    left join SC b on a.Cno=b.Cno
    group by a.cno;

#40、查询选修"李四"老师所授课程的学生中,成绩最高的学生信息及其成绩  
select a.*,score from student a   
    inner join SC b using(sno)
    inner join (
        select b.cno,max(b.score) as maxcj
        from SC b
        inner join course c on b.Cno=c.cno
        inner join teacher d on c.Tno=d.Tno
        where d.Tname='李四'
    )bb 
    on b.cno=bb.cno and b.score=bb.maxcj;

#41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.Sname,b.cno,b.score 
    from student a
    left join SC b using(sno) where exists 
(
    select * from SC cc
        where b.score=cc.score and b.cno!=cc.cno and a.sno=cc.sno
    );

SELECT a.*
FROM sc a
LEFT JOIN sc b
ON a.sno=b.sno AND a.score=b.score AND a.cno<>b.cno
GROUP BY 1,2,3
HAVING COUNT(b.sno)>0;

#42、查询每门课程成绩最好的前两名 
select a.sno,c.cno,c.cname,a.score
    from SC a
    inner join course c on c.Cno=a.Cno
    where (select count(*)+1 from SC b where b.cno=a.cno and b.score>a.score)<=2
    order by c.cname,a.score desc;

SELECT a.*,COUNT(b.cno)+1 AS ascore
FROM sc a
LEFT JOIN sc b
ON a.cno=b.cno AND a.score<b.score
GROUP BY 1,2,3
HAVING ascore<=2
ORDER BY a.cno,ascore;

/*43、统计每门课程的学生选修人数(超过5人的课程才统计)。
要求输出课程号和选修人数,查询结果按人数降序排列,
若人数相同,按课程号升序排列  
*/
select cno,count(*) as num from SC 
    group by Cno
    having num>5
    order by num desc,cno;

#44、检索至少选修两门课程的学生学号 
select sno from SC group by sno having count(*)>=2;

#45、查询选修了全部课程的学生信息 
select a.* from student a
    inner join SC b using(sno)
    group by 1,2,3,4
    having  count(*)=(select count(*) from course);

#46、查询各学生的年龄
select sno,sname,(year(curdate())-year(Sage)) as age from student;

#47、查询本周过生日的学生
select * from student where 
    date_format(sage,'%m%d') 
    between date_format(subdate(curdate(),weekday(curdate())),'%m%d') 
    and (date_format(subdate(curdate(),weekday(curdate())),'%m%d')+6);

-- 48、查询下周过生日的学生
# 关闭安全模式,修改数据,开启安全模式
set sql_safe_updates=0;
#update student set sage = '1990-01-01' where sno='01';
update student set sage = '1990-10-16' where sno='01';
set sql_safe_updates=1;

set @NextMonday:=(date_format(subdate(curdate(),weekday(curdate())),'%m%d')+7);
set @NextSunday:=(date_format(subdate(curdate(),weekday(curdate())),'%m%d')+13);
select * from student where
    date_format(sage,'%m%d') between @NextMonday and @NextSunday;    

-- 49、查询本月过生日的学生
select * from student where month(Sage)=month(curdate());

-- 50、查询下月过生日的学生
select * from student where month(Sage)=(month(curdate())+2);

【转】http://blog.csdn.net/dehu_zhou/article/details/52881587

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页