MyException - 我的异常网
当前位置:我的异常网» SQL » SQL 经典5十道题

SQL 经典5十道题

www.MyException.Cn  网友分享于:2013-11-16  浏览:0次
SQL 经典五十道题

点击有惊喜

 

 

--1.学生表
Student(S,Sname,Sage,Ssex) --S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表 
Course(C,Cname,T) --C --课程编号,Cname 课程名称,T 教师编号
--3.教师表 
Teacher(T,Tname) --T 教师编号,Tname 教师姓名
--4.成绩表 
SC(S,C,score) --S 学生编号,C 课程编号,score 分数
*/
--创建测试数据

create table Student(S 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(C varchar(10),Cname,varchar(10),T varchar(10))
insert into Course values('01' , '语文' , '02')
insert into Course values('02' , '数学' , '01')
insert into Course values('03' , '英语' , '03')
create table Teacher(T varchar(10),Tname,varchar(10))
insert into Teacher values('01' , '张三')
insert into Teacher values('02' , '李四')
insert into Teacher values('03' , '王五')
create table SC(S varchar(10),C 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)

--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT a. ,c. 
from 
(select 
    a.* 
    from
(SELECT * from sc WHERE sc.C in('01'))a
left JOIN(SELECT * from sc where sc.C in('02'))b
ON a.s =b.s 
where a.score>b.score ) a ,student c
where a.s =c.s;

--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT a. ,c.
FROM
(SELECT a.*
FROM
(SELECT * FROM sc WHERE sc.C in('01'))a
LEFT JOIN(SELECT * FROM sc WHERE sc.C in('02'))b
ON a.s =b.s WHERE a.score<b.score) a,student c
WHERE a.s =c.s

--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT
b.s
,c.Sname
,b.avgscore
FROM
(SELECT 
    a.s s
    , avg(a.score) avgscore
FROM sc a
GROUP BY a.s) b
    ,student c
WHERE
b.avgscore>60 AND c.s =b.s

--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

SELECT
b.s
,c.Sname
,b.avgscore
FROM
(SELECT 
    a.s s
    , avg(a.score) avgscore
FROM sc a
GROUP BY a.s) b
    ,student c
WHERE
b.avgscore<60 AND c.s =b.s

--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT
    b.s
    ,c.Sname
    ,b.num
    ,b.sumscore
    FROM
(SELECT
    a.s s
    ,COUNT(a.c) num 
    ,SUM(a.score) sumscore
FROM 
    sc a
GROUP BY a.s) b
    ,student c
WHERE b.s =c.s

--6、查询"李"姓老师的数量 

SELECT
COUNT(a.Tname)
FROM
(SELECT
    Tname Tname
FROM
    teacher
    WHERE
    Tname LIKE '李%') a

--7、查询学过"张三"老师授课的同学的信息 

SELECT
d.*
FROM
student d
,sc e
WHERE
e.S =d.S
AND
e.C=
(SELECT
    b.C
FROM
    teacher a
    ,course b
WHERE
    a.T =b.T AND a.Tname='张三') 

--8、查询没学过"张三"老师授课的同学的信息 

SELECT
a.*
FROM
student a
WHERE
a.S NOT in 
(SELECT 
 b.S
FROM
sc a
,student b

WHERE
a.S=b.S AND
a.C =
(SELECT
    b.c 
FROM
    teacher a
    ,course b
WHERE
    a.T =b.T AND a.Tname ='张三'))

--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT
*
FROM 
sc a,
sc b,
student c
WHERE
a.S =b.S AND a.C='01' and b.C='02'  
AND c.S =a.S

--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT
*
FROM
student a
WHERE
a.S in
(
SELECT
DISTINCT(sc.s)
FROM
sc
WHERE
sc.S
NOT
in(
SELECT S
FROM sc
WHERE
 sc.C in('02')
GROUP BY
sc.S)
)

--11、查询没有学全所有课程的同学的信息 

SELECT
b.*
FROM
student b
WHERE
b.S in
(
SELECT
a.s
FROM
(SELECT
    a.S s
    ,COUNT(a.C) NUM
FROM
sc a
GROUP BY
a.s) a
WHERE
a.NUM<3
)

--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 

SELECT
e.*
FROM
student e
WHERE
e.S in 
(SELECT
DISTINCT(c.s)
FROM
student c
,sc d
WHERE
c.s=d.s
AND 
d.C in 
(SELECT 
b.c
FROM 
student a
,sc  b
WHERE
a.s =b.s AND a.s ='01'
)
)

--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 

SELECT
    a.c
FROM
    sc a    
WHERE
    a.S ='01'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

---

SELECT
*
FROM
(SELECT
a.*
,COUNT(b.c) num
FROM
student a
,sc b
WHERE
a.s=b.S 
GROUP BY
b.S
) a
WHERE
a.num=3 AND a.s <> '01'

--14、查询没学过"张三"老师讲授的任一门课程的学生姓名 

SELECT
*
FROM
student a
WHERE
a.S NOT in 
(
SELECT
b.S
FROM
(
SELECT
b.c 
,b.Cname
FROM
teacher a
,course b
WHERE
a.T=b.T
AND
a.Tname='张三'
) a
,sc b
WHERE
a.c=b.C
)

--15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩 

SELECT
b.S
,b.Sname
,a.avgscore
FROM
(SELECT
AVG(score) avgscore
,s S
FROM
sc
WHERE
sc.score<60
GROUP BY
s
HAVING COUNT(s)>=2) a
,student b
WHERE
a.S=b.S

--16、检索"01"课程分数小于60,按分数降序排列的学生信息

SELECT
b.*
FROM
(SELECT
a.s s
FROM
sc a
WHERE
a.C ='01'
AND
a.score<60
ORDER BY a.score DESC
) a
,student b
WHERE
a.s=b.S

--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT
*
FROM
(SELECT
b.S
,b.C
,b.score
,c.avgscore
FROM
sc b
LEFT JOIN
(SELECT
a.S
,AVG(a.score) avgscore
FROM
sc a
GROUP BY a.S) c
ON c.S =b.S) a
,student b
WHERE
a.S =b.S
ORDER BY avgscore DESC

--18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
--及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90

SELECT
a.C
,b.Cname
,MAX(a.score)
,MIN(a.score)
,AVG(a.score)
,(SELECT COUNT(1) FROM sc WHERE sc.C=b.C and score>60)/(SELECT COUNT(1)FROM sc WHERE sc.C=b.c ) jigelv
FROM sc a ,course b 
WHERE a.C =b.C
GROUP BY
a.C
SELECT
a.C
,a.Cname
,MAX(b.score)
,MIN(b.score)
,CAST(AVG(b.score) AS DECIMAL(18,2)) pingqunfen
,CAST((SELECT COUNT(1) FROM sc WHERE sc.C=a.C AND sc.score>60)/
(SELECT COUNT(1) FROM sc WHERE sc.C =a.C) AS DECIMAL(18,2)) jigelv
,CAST((SELECT COUNT(1) FROM sc WHERE sc.C =a.c AND sc.score>=70 AND sc.score<80)/
(SELECT COUNT(1) FROM sc WHERE sc.c =a.c) AS DECIMAL (18,2)) youlianlv
FROM
course a,sc b
WHERE
a.C=b.C
GROUP BY a.C,a.Cname
ORDER BY a.C DESC

--19、按各科成绩进行排序,并显示排名

SELECT 
c.s
,c.sname
,b.cname
,a.score
,(SELECT COUNT(1) FROM sc WHERE sc.C=a.c AND sc.score > a.score)+1 mc
FROM 
sc a ,course b,student c 
WHERE a.c='01'
AND a.c=b.c 
AND a.s=c.s
ORDER BY mc
SELECT 
c.s
,c.sname
,b.cname
,a.score
,(SELECT COUNT(1) FROM sc WHERE sc.C=a.c AND sc.score > a.score)+1 mc
FROM 
sc a ,course b,student c 
WHERE a.c='02'
AND a.c=b.c 
AND a.s=c.s
ORDER BY mc
SELECT 
c.s
,c.sname
,b.cname
,a.score
,(SELECT COUNT(1) FROM sc WHERE sc.C=a.c AND sc.score > a.score)+1 mc
FROM 
sc a ,course b,student c 
WHERE a.c='03'
AND a.c=b.c 
AND a.s=c.s
ORDER BY mc

--20、查询学生的总成绩并进行排名

SELECT 
b.s
,b.sumscore
,@rownum:=@rownum +1 AS rownum
FROM
(SELECT
a.s s
,SUM(a.score) sumscore
FROM
sc a
GROUP BY
a.S
ORDER BY sumscore DESC
) as b
,(SELECT @rownum:=0)r

--21、查询不同老师所教不同课程平均分从高到低显示 

SELECT
c.Tname
,b.Cname
,AVG(a.score) 
FROM
sc a
,course b
,teacher c
WHERE
a.C =b.C
AND b.T =c.T
GROUP BY a.C
ORDER BY AVG(a.score) DESC

--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT
a.*
,b.*
FROM
(SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='01'AND a.score>b.score)+1 mc
FROM sc b  WHERE b.c='01' HAVING mc BETWEEN 2 AND 3
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='02'AND a.score>b.score)+1 mc
FROM sc b  WHERE b.c='02' HAVING mc BETWEEN 2 AND 3
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='03'AND a.score>b.score)+1 mc
FROM sc b  WHERE b.c='03' HAVING mc BETWEEN 2 AND 3
) a
,student b
WHERE
a.s =b.s

--23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 

SELECT
a.*
FROM
(SELECT
c.Cname
,(SELECT COUNT(*)FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND  a.C='01') '85以上'
,(SELECT COUNT(*)FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C='01') '[70_85]'
,(SELECT COUNT(*)FROM sc a WHERE a.score>70 AND a.C=b.c  AND c.c=a.C AND a.score>60 AND a.C='01') '[60_70]'
,(SELECT COUNT(*)FROM sc a WHERE a.score<60 AND a.C=b.c  AND c.c=a.C AND a.C='01') '60一下'
,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c  AND c.c=a.C AND a.C='01')/(SELECT COUNT()FROM sc WHERE c='01'AND C=b.c AND c.c=c) '85以上比率'
,(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c  AND c.c=a.C AND a.score>70 AND a.C='01')/(SELECT COUNT()FROM sc WHERE c='01'AND C=b.c AND c.c=c) '[70_85]以上比率'
,(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c  AND c.c=a.C AND a.score>60 AND a.C='01')/(SELECT COUNT()FROM sc WHERE c='01'AND C=b.c AND c.c=c)'[60_70]以上比率'
,(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C  AND a.C='01')/(SELECT COUNT()FROM sc WHERE c='01'AND C=b.c AND c.c=c)'60以上比率'
FROM sc b ,course c WHERE b.c=c.c AND c.c='01'
UNION ALL
SELECT
c.Cname
,(SELECT COUNT(*)FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND  a.C='02') '85以上'
,(SELECT COUNT(*)FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C='02') '[70_85]'
,(SELECT COUNT(*)FROM sc a WHERE a.score>70 AND a.C=b.c  AND c.c=a.C AND a.score>60 AND a.C='02') '[60_70]'
,(SELECT COUNT(*)FROM sc a WHERE a.score<60 AND a.C=b.c  AND c.c=a.C AND a.C='02') '60一下'
,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c  AND c.c=a.C AND a.C='02')/(SELECT COUNT()FROM sc WHERE c='02'AND C=b.c AND c.c=c) '85以上比率'
,(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c  AND c.c=a.C AND a.score>70 AND a.C='02')/(SELECT COUNT()FROM sc WHERE c='02'AND C=b.c AND c.c=c)'[70_85]以上比率'
,(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c  AND c.c=a.C AND a.score>60 AND a.C='02')/(SELECT COUNT()FROM sc WHERE c='02'AND C=b.c AND c.c=c)'[60_70]以上比率'
,(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C  AND a.C='02')/(SELECT COUNT()FROM sc WHERE c='02'AND C=b.c AND c.c=c)'60以上比率'
FROM sc b ,course c WHERE b.c=c.c AND c.c='02'
UNION ALL
SELECT
c.Cname
,(SELECT COUNT(*)FROM sc a WHERE a.score>85 AND a.C=b.c AND c.c=a.C AND  a.C='03') '85以上'
,(SELECT COUNT(*)FROM sc a WHERE a.score<85 AND a.C=b.c AND c.c=a.C AND a.score>70 AND a.C='03') '[70_85]'
,(SELECT COUNT(*)FROM sc a WHERE a.score>70 AND a.C=b.c  AND c.c=a.C AND a.score>60 AND a.C='03') '[60_70]'
,(SELECT COUNT(*)FROM sc a WHERE a.score<60 AND a.C=b.c  AND c.c=a.C AND a.C='03') '60一下'
,(SELECT COUNT()FROM sc a WHERE a.score>85 AND a.C=b.c  AND c.c=a.C AND a.C='03')/(SELECT COUNT()FROM sc WHERE c='03'AND C=b.c AND c.c=c) '85以上比率'
,(SELECT COUNT()FROM sc a WHERE a.score<85 AND a.C=b.c  AND c.c=a.C AND a.score>70 AND a.C='03')/(SELECT COUNT()FROM sc WHERE c='03'AND C=b.c AND c.c=c)'[70_85]以上比率'
,(SELECT COUNT()FROM sc a WHERE a.score>70 AND a.C=b.c  AND c.c=a.C AND a.score>60 AND a.C='03')/(SELECT COUNT()FROM sc WHERE c='03'AND C=b.c AND c.c=c)'[60_70]以上比率'
,(SELECT COUNT()FROM sc a WHERE a.score<60 AND a.C=b.c AND c.c=a.C  AND a.C='01')/(SELECT COUNT()FROM sc WHERE c='03'AND C=b.c AND c.c=c)'60以上比率'
FROM sc b ,course c WHERE b.c=c.c AND c.c='03') a

--24、查询学生平均成绩及其名次 

SELECT
a.s
,a.avgscore
,@rownum:=@rownum +1 AS rownum
FROM 
(SELECT
s
,AVG(score) avgscore
FROM
sc 
GROUP BY s
ORDER BY avgscore DESC
) a
,(SELECT @rownum:=0)r

--25、查询各科成绩前三名的记录

SELECT
a.*
,b.*
FROM
(SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='01'AND a.score>b.score)+1 mc
FROM sc b  WHERE b.c='01' HAVING mc BETWEEN 1 AND 3
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='02'AND a.score>b.score)+1 mc
FROM sc b  WHERE b.c='02' HAVING mc BETWEEN 1 AND 3
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='03'AND a.score>b.score)+1 mc
FROM sc b  WHERE b.c='03' HAVING mc BETWEEN 1 AND 3
) a
,student b
WHERE
a.s =b.s

--26、查询每门课程被选修的学生数 

SELECT
a.Cname
,COUNT(b.c)
FROM sc b,course a 
WHERE a.c =b.c
GROUP BY b.c

--27、查询出只有两门课程的全部学生的学号和姓名  

SELECT
b.S
,b.Sname
,a.countc
FROM
(SELECT
s 
,COUNT(c) countc
FROM 
sc
GROUP BY s
HAVING countc =2) a
,student b
WHERE
a.s=b.s

--28、查询男生、女生人数 

select s.Ssex,COUNT(*)from student s where s.Ssex='男'
UNION
select s.Ssex,COUNT(*)from student s where s.Ssex='女'

--29、查询名字中含有"风"字的学生信息

SELECT
*
,count(*)
FROM student 
WHERE
Sname rLIKE '风'

--30、查询同名同性学生名单,并统计同名人数 

SELECT
s.Sname
,s.Ssex
,COUNT(*) countnum
from
student s
GROUP BY
s.Sname,s.Ssex
HAVING
countnum>=2

--31、查询1990年出生的学生名单(注:Student表中Sage列的类型是datetime) 

SELECT
*
FROM
student
WHERE
Sage LIKE '1990%'

--32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号

SELECT
a.Cname
,AVG(sc.score) avgscore
FROM sc ,course a
WHERE sc.C =a.C
GROUP BY sc.C
ORDER BY avgscore DESC

--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 

SELECT
*
FROM
(SELECT
a.Sname
,a.S
,AVG(sc.score) avgscore
FROM sc ,student a
WHERE sc.s =a.s 
GROUP BY sc.s
ORDER BY avgscore DESC
) a
WHERE
a.avgscore>85

--34、查询课程名称为"数学",且分数低于60的学生姓名和分数 

SELECT
*
FROM
sc a,course b
WHERE 
a.c =b.c AND b.Cname='数学' AND a.score>60

--35、查询所有学生的课程及分数情况;

SELECT
*
FROM
student a
,course b
,sc c
WHERE
a.S=c.S AND b.C =c.C

--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数; 

SELECT
b.Sname
,c.Cname
,a.score
FROM
sc  a, student b,course c
WHERE
a.score>70 AND a.C =c.C AND a.S =b.S

--37、查询不及格的课程

SELECT
b.Sname
,c.Cname
,a.score
FROM
sc  a, student b,course c
WHERE
a.score<60 AND a.C =c.C AND a.S =b.S

--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名; 

SELECT
c.S
,c.Sname
,a.Cname
,b.score
FROM
course a ,sc b ,student c
WHERE a.C =b.C AND b.score>=80 AND c.S =b.S AND a.C='01'

--39、求每门课程的学生人数 

SELECT
b.Cname
,COUNT(a.c)
FROM
sc a ,course b
WHERE
b.c=a.C
GROUP BY
a.C

--40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩

SELECT
b.*
,MAX(c.score)
FROM
teacher a,student b ,sc c ,course d
WHERE
a.T =d.T AND d.C =c.C AND c.S =b.S AND  a.Tname ='张三'

--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 

SELECT c. FROM sc c GROUP BY c.`C`,c.`score` HAVING COUNT()>1;

--42、查询每门功成绩最好的前两名 

SELECT
a.*
,b.*
FROM
(SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='01'AND a.score>b.score)+1 mc
FROM sc b  WHERE b.c='01' HAVING mc BETWEEN 1 AND 2
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='02'AND a.score>b.score)+1 mc
FROM sc b  WHERE b.c='02' HAVING mc BETWEEN 1 AND 2
UNION ALL
SELECT b.s ,b.C ,b.score
,(SELECT COUNT(*) FROM sc a WHERE a.c='03'AND a.score>b.score)+1 mc
FROM sc b  WHERE b.c='03' HAVING mc BETWEEN 1 AND 2
) a
,student b
WHERE
a.s =b.s

--43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列  

SELECT
b.C
,b.Cname 
,COUNT(a.C) countc
FROM
sc a,course b
WHERE
a.c=b.C 
GROUP BY
a.C
HAVING countc>5
ORDER BY countc DESC

--44、检索至少选修两门课程的学生学号 

SELECT
sc.S
,COUNT(sc.C) countc
FROM
sc
GROUP BY sc.S
HAVING countc >=2

--45、查询选修了全部课程的学生信息 

SELECT
a.*
,COUNT(sc.C) countc
FROM
sc ,student  a
WHERE sc.S =a.S
GROUP BY sc.S
HAVING countc =3

--46、查询各学生的年龄

SELECT
    s.s,
    s.sname,
    EXTRACT(YEAR FROM NOW())-EXTRACT(YEAR FROM s.sage) 年龄
FROM
    student s

--47、查询本周过生日的学生

SELECT
s.S
,s.Sname
,s.Sage
FROM student s
WHERE YEARWEEK(DATE_FORMAT(s.Sage,'%Y-%m-%d')) =YEARWEEK(NOW())

--48、查询下周过生日的学生

SELECT
s.S
,s.Sname
,s.Sage
FROM student s
WHERE YEARWEEK(DATE_FORMAT(s.Sage,'%Y-%m-%d')) =YEARWEEK(NOW())+1

--49、查询本月过生日的学生

SELECT
s.S
,s.Sname
,s.Sage
FROM student s
WHERE MONTH(DATE_FORMAT(s.Sage,'%Y-%m-%d')) =MONTH(NOW())

--50、查询下月过生日的学生

SELECT
s.S
,s.Sname
,s.Sage
FROM student s
WHERE MONTH(DATE_FORMAT(s.Sage,'%Y-%m-%d')) =MONTH(NOW())+1

 

点击有惊喜

文章评论

做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
每天工作4小时的程序员
每天工作4小时的程序员
为什么程序员都是夜猫子
为什么程序员都是夜猫子
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
旅行,写作,编程
旅行,写作,编程
10个调试和排错的小建议
10个调试和排错的小建议
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
程序员必看的十大电影
程序员必看的十大电影
中美印日四国程序员比较
中美印日四国程序员比较
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
鲜为人知的编程真相
鲜为人知的编程真相
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
如何成为一名黑客
如何成为一名黑客
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
老程序员的下场
老程序员的下场
编程语言是女人
编程语言是女人
总结2014中国互联网十大段子
总结2014中国互联网十大段子
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
程序员都该阅读的书
程序员都该阅读的书
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
我的丈夫是个程序员
我的丈夫是个程序员
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
代码女神横空出世
代码女神横空出世
 程序员的样子
程序员的样子
一个程序员的时间管理
一个程序员的时间管理
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
那些争议最大的编程观点
那些争议最大的编程观点
程序员应该关注的一些事儿
程序员应该关注的一些事儿
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
程序员和编码员之间的区别
程序员和编码员之间的区别
我是如何打败拖延症的
我是如何打败拖延症的
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有