sql语句练习(Mysql)

最新推荐文章于 2024-07-15 19:44:21 发布
置顶一定有人改了我的代码
最新推荐文章于 2024-07-15 19:44:21 发布
阅读量194
点赞数 1
分类专栏： mysql 文章标签： mysql 数据库
原文链接：https://blog.csdn.net/fashion2014/article/details/78826299
版权
mysql 专栏收录该内容
2 篇文章 0 订阅
订阅专栏
转载:https://blog.csdn.net/fashion2014/article/details/78826299
本来没有想着整理出来,有的表字段可能和转载内容不太一样
如果有错误的地方还望大佬指出
如果有好的写法可以留在评论区我会查看并更新的
如果第一题就感觉有难度可以倒着来从50-1
#学生表
create table Student(
  SId VARCHAR(10),
  Sname VARCHAR(10),
  Sage VARCHAR(10),
  Ssex VARCHAR(10)
);
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');


#课程表
create table Course(
		CId varchar(10),
		Cname varchar(10),
		TId varchar(10)
);
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');


#教师表
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');


#成绩表
create table SC(SId varchar(10),
CId 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 * from Student INNER JOIN(
    select t1.SId, class1, class2 from
          (select SId, score as class1 from sc where sc.CId = '01')as t1, 
          (select SId, score as cl·ass2 from sc where sc.CId = '02')as t2
    where t1.SId = t2.SId AND t1.class1 > t2.class2
)r 
on Student.SId = r.SId;
#====================================思路
#先查询出01课程的所有信息,和02课程的所有信息
#然后通过两个结果做条件过滤,首先要是一个学生(t1.SId = t2.SId) 其次就是01课程成绩大于02课程成绩
#最后通过学生表内连接和过滤好数据的学生id匹配 最后得出结果

#写法2
select s.*,b.score as 01_score,c.score as 02_score from student s
left join sc b on s.SId = b.SId and b.CId='01'
left join sc c on s.SId = c.SId and c.CId = '02'
where b.score > c.score
#=============思路
#left join sc b后产生 01课程的数据
#left join sc c后产生 02课程的数据
#然后 where b.score > c.score  01>02
#s.*展示学生全部数据,b.score,和c.score分别显示01,02课程数据

#写法3 可以看做from 同时查询了三张表然后来做where
select s.*,b.score as 01_score,c.score as 02_score from student s,sc b,sc c
where s.SID=b.SId 
and s.SID = c.SId
and b.CId = '01'
and c.CId = '02'
and b.score>c.score


-- 2.查询同时存在" 01 "课程和" 02 "课程的情况  
select * from
	(select * from sc where sc.CId = '01')as t1, 
	(select * from sc where sc.CId = '02')as t2
where t1.SId = t2.SId

-- 方法 2查询同时考了01课程和02课程的学生
select s.*,b.CId cid_01,b.score score_01,c.CId cid_02,c.score score_02 from student s,sc b,sc c
where s.SId = b.SId
and s.SId = c.SId
and b.cid ='01'
and c.CId = '02'


-- 3查询平均成绩大于60分的同学的编号,和学生姓名和平均值
select s.SId,s.Sname,avg(score) avg_score from student s 
inner join sc b
on s.SId = b.SId
group by s.SId
having avg_score>60




-- 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
#包括没有成绩的学生
select s.SId,s.Sname,avg(b.score) as avg_score from student s
inner join sc b
on s.SId = b.SId
group by s.SId
having  avg_score<60
union all
#下面是没有成绩的学生   0 as avg_score是用来凑语法 使用union all的
#成绩表中没有学省编号的学生就是没有成绩的
select a.sid,a.sname,0 as avg_score from 
	student a 
	where a.sid not in (#not in 排除掉成绩表中成绩的学生
				select distinct sid from sc);#在所有成绩中排除掉有成绩的学生就是没有成绩的学生




-- 5查询所有同的学生编号,学生姓名,选课总数,所有课程的总成绩
select s.SId,s.Sname,count(b.CId),sum(b.score) from student s
inner join sc b
on s.SId = b.SId
group by s.SId,s.Sname



-- 6 查询李姓老师的数量
select count(tid) from teacher where tname like '李%'



-- 7 查询学过"张三"老师授课的同学的信息 
select  *  from student st
inner join sc s
on s.sid = st.sid
inner join course c
on c.cid = s.cid
inner join teacher t
on c.tid = t.tid
where t.tname='张三'



-- 8 查询没学过"张三"老师授课的同学的信息 
select * from student a
inner join sc s
on a.sid = s.sid
where s.cid not in (
	select c.cid from course c where s.cid = c.cid and c.tid =(
			select t.tid from teacher t where tname='张三' 
   )
)


#9 查询学过编号为"01并且也学过编号为"02"的课程的同学信息
select a.* from student a,sc b,sc c
where a.sid = b.sid 
and a.sid = c.sid 
and b.CId = '01' and c.cid = '02'



# 10 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select * from student s
where s.SId in (select sid from sc a where a.cid = '01')
and s.SId not in (select sid from sc a where a.cid = '02')



#11 查询没有学全所有课程的同学信息
select * from student a
inner join sc b
on a.sid = b.sid 
group by a.sid 
having count(*) <(select count(*) from course)



#12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 
select distinct  a.* from student a,sc b
where a.sid = b.sid 
and b.cid in (select cid from sc c where c.sid='01' )



#13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
#思路:
#1 先排除掉学过01没有学过的课程的学生
#2 然后找到和01学生,学过课程数量相当的学生
select  a.* from student a,sc b
where a.sid = b.SId 
and b.CId not in ( #然后排出掉01学过的课程,留下01没有学过的课程
	select a.cid from course a  
  where a.cid not in ( 
      select b.cid from sc b where b.sid = '01' #找到01学过的课程
  )
)
group by a.SId  #然后找到和03学的课程数量相当的学生
having count(*)=(select count(*) from sc where sc.SId='01')
AND a.sid NOT IN ('01') #排除01学生自己



#14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student s
where s.sid not in (
	 select sid  from sc where sc.CId in(
			  select c.cid from course c where c.tid in (
						select tid from teacher where tname='张三'
				)
   )
)



#15 查询两门及其以上不及格课程的同学的学号,姓名,及其平均成绩
select s.SId,s.Sname,avg(sc.score) score from student s
inner join sc
on s.SId = sc.SId
group by s.SId
HAVING 
count(
		case when sc.score<60 then 1 else null end)  >=2



#16、检索"01"课程分数小于60，按分数降序排列的学生信息
select * from student s
inner join sc 
on s.SId = sc.SId
where sc.cid='01' and sc.score<60
order by sc.score desc


#17按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.sid,
   (select score from sc where sid = a.sid and cid='01') 语文,
   (select score from sc where sid = a.sid and cid='02') 数学,
   (select score from sc where sid = a.sid and cid='03') 英语,
	 avg(score) as 平均分
 from sc a
group by a.sid order by 平均分  desc

#第二种方法
select sc.sid,
	MAX(case sc.CId when '01' then sc.score end) as 语文,
    MAX(case sc.CId when '02' then sc.score end) as 数学,
    MAX(case sc.CId when '03' then sc.score end) as 英语,
	avg(score) as 平均值
 from sc
group by sc.sid





#18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
#格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select c.cid 编号,c.cname 课程名字,max(score) 最大,min(score) 最小,round(avg(score),2) 平均,
			   ROUND(count(case when sc.score>=60 then 1 else null end)/count(1)*100,2) as 及格率,
				 ROUND(count(case when sc.score>=70 and sc.score<=80 then 1 else null end)/count(1)*100 ,2)as 中等率,
				 ROUND(count(case when sc.score>=80 and sc.score<=90 then 1 else null end)/count(1)*100 ,2)as 优良率,
         ROUND(count(case when sc.score>=90 then 1 else null end)/count(sc.score)*100,2) as 优秀率
 from  sc 
inner join course c
on c.cid = sc.CId
group by c.cid

#第二种办法
select a.cid,b.cname,MAX(score),MIN(score),ROUND(AVG(score),2),
	ROUND(100*(SUM(case when a.score>=60 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 及格率,
	ROUND(100*(SUM(case when a.score>=70 and a.score<=80 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 中等率,
	ROUND(100*(SUM(case when a.score>=80 and a.score<=90 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 优良率,
	ROUND(100*(SUM(case when a.score>=90 then 1 else 0 end)/SUM(case when a.score then 1 else 0 end)),2) as 优秀率
	from sc a left join course b on a.cid = b.cid GROUP BY a.cid,b.cname


#19、按各科成绩进行排序，并显示排名
select a.*,@rownum:=@rownum+1 as rownum from (
	select s.sname,c.cname,sc.score from student s
	inner join sc
	on s.sid = sc.sid
	inner join course c
	on c.cid = sc.CId
	group by s.sname,c.cname
	order by c.cname,score desc
)a,(select @rownum:=0) b

-- 20、查询学生的总成绩并进行排名
select a.*,@rownum:=@rownum+1 as rownum from (
	select s.sname,sum(sc.score) sum_score from student s
	inner join sc
	on s.sid = sc.sid
	inner join course c
	on c.cid = sc.CId
	group by s.sname
  order by sum_score desc
)a,(select @rownum:=0) b

#21、查询不同老师所教不同课程平      均分从高到低显示 
select a.*,@rownum:=@rownum+1 as rownum from (
	select t.tname,avg(sc.score) avg_score from teacher t
	inner join course c
	on t.tid = c.TId
	inner join sc
	on sc.cid = c.cid
	group by t.tname
	order by avg_score  desc
)a,(select @rownum:=0) b




-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select sname,cname,score,rownum from(
   select a.*,@Z:=@Z+1 as rownum from (
		select s.sname,c.cname,sc.score from student s
		inner join sc 
		on sc.sid = s.sid
		inner join course c 
		on sc.cid = c.cid
		where cname='语文'
		group by s.sname,c.cname
		order by c.cname,sc.score desc 
)a,(select @Z:=0) b
)c where rownum between 2 and 3
UNION ALL
select sname,cname,score,rownum from(
   select a.*,@H:=@H+1 as rownum from (
		select s.sname,c.cname,sc.score from student s
		inner join sc 
		on sc.sid = s.sid
		inner join course c 
		on sc.cid = c.cid
		where cname='数学'
		group by s.sname,c.cname
		order by c.cname,sc.score desc
)a,(select @H:=0) b
)c where rownum between 2 and 3
UNION ALL
select sname,cname,score,rownum from(
   select a.*,@M:=@M+1 as rownum from (
		select s.sname,c.cname,sc.score from student s
		inner join sc 
		on sc.sid = s.sid
		inner join course c 
		on sc.cid = c.cid
		where cname='英语'
		group by s.sname,c.cname
		order by c.cname,sc.score desc
)a,(select @M:=0) b
)c where rownum between 2 and 3

-- 第二种写法
select s.sname,cname,score,rownum from (
	select sc.*,c.cname,@Z:=@Z+1 rownum from sc,course c,(select @Z:=0) b
  where sc.cid='03'  and c.cid = sc.cid 
  order by score desc 
)a
left join student s
on a.sid = s.sid
where rownum between 2 and 3
union 
select s.sname,cname,score,rownum from (
	select sc.*,c.cname,@H:=@H+1 rownum from sc,course c,(select @H:=0) b
  where sc.cid='02'  and c.cid = sc.cid 
  order by score desc 
)a
left join student s
on a.sid = s.sid
where rownum between 2 and 3
UNION
select s.sname,cname,score,rownum from (
	select sc.*,c.cname,@M:=@M+1 rownum from sc,course c,(select @M:=0) b
  where sc.cid='01'  and c.cid = sc.cid 
  order by score desc 
)a
left join student s
on a.sid = s.sid
where rownum between 2 and 3




-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分
select 
      c.cid,
      c.cname,
			count(case when sc.score>=85 and sc.score<=100 then 1 else null end) as `85-100`,
			round(count(case when sc.score>=85 and sc.score<=100 then 1 else null end)/count(*)*100,2) as `85-100百分比`,
			count(case when sc.score>=70 and sc.score<85 then 1 else null end)  as `70-85`,
			round(count(case when sc.score>=70 and sc.score<85 then 1 else null end)/count(*)*100,2)  as `70-85百分比`,
			count(case when sc.score>=60 and sc.score<=70 then 1 else null end) as `60-70`,
			round(count(case when sc.score>=60 and sc.score<=70 then 1 else null end)/count(*)*100,2) as `60-70百分比`,
			count(case when sc.score>=0 and sc.score<=60 then 1 else null end)  as `0-60`,
			round(count(case when sc.score>=0 and sc.score<=60 then 1 else null end)/count(*)*100,2)  as `0-60百分比`
    from course c
inner join sc 
on c.cid = sc.CId
group by c.cid,c.cname


-- 第二种写法网友写的,因为大于等于,小于等于不同可能上下结果有点出入
select distinct f.cname,a.cid,b.`85-100`,b.百分比,c.`70-85`,c.百分比,d.`60-70`,d.百分比,e.`0-60`,e.百分比 from sc a
left join (select cid,SUM(case when score >85 and score <=100 then 1 else 0 end) as `85-100`,
			ROUND(100*(SUM(case when score >85 and score <=100 then 1 else 0 end)/count(*)),2) as 百分比
from sc GROUP BY cid)b on a.cid=b.cid
left join (select cid,SUM(case when score >70 and score <=85 then 1 else 0 end) as `70-85`,
			ROUND(100*(SUM(case when score >70 and score <=85 then 1 else 0 end)/count(*)),2) as 百分比
from sc GROUP BY cid)c on a.cid=c.cid
left join (select cid,SUM(case when score >60 and score <=70 then 1 else 0 end) as `60-70`,
			ROUND(100*(SUM(case when score >60 and score <=70 then 1 else 0 end)/count(*)),2) as 百分比
from sc GROUP BY cid)d on a.cid=d.cid
left join (select cid,SUM(case when score >=0 and score <=60 then 1 else 0 end) as `0-60`,
			ROUND(100*(SUM(case when score >=0 and score <=60 then 1 else 0 end)/count(*)),2) as 百分比
from sc GROUP BY cid)e on a.cid=e.cid
left join course f on a.cid = f.cid



-- 24、查询学生平均成绩及其名次 
select a.*,@i:=@i+1 rownum from (
	select s.sname,avg(sc.score) as avg_score from student s
	inner join sc 
	on s.sid = sc.sid
	group by s.sname
	order by  avg_score desc
)a,(select @i:=0) b

-- 网友写的
		select a.sid,
				@i:=@i+1 as '不保留空缺排名',
				@k:=(case when @avg_score=a.avg_s then @k else @i end) as '保留空缺排名',
				@avg_score:=avg_s as '平均分'
		from (select sid,ROUND(AVG(score),2) as avg_s from sc GROUP BY sid ORDER BY avg_s DESC)a,(select @avg_score:=0,@i:=0,@k:=0)b;




-- 25、查询各科成绩前三名的记录
-- 理解 a.score<b.score 这里过滤出最大值,所有最大的数据
-- 最后按照学生,课程,分数分组并且过滤后就是各个课程的最大数据 然后count(*)<3 控制它显示几个
		select a.sid,a.cid,a.score from sc a 
			left join sc b on a.cid = b.cid and a.score<b.score  
			group by a.sid,a.cid,a.score HAVING count(*)<3
			ORDER BY a.cid,a.score DESC

-- 倒数第三
		select a.sid,a.cid,a.score from sc a 
			left join sc b on a.cid = b.cid and a.score>b.score  
			group by a.sid,a.cid,a.score HAVING count(*)<3
			ORDER BY a.cid,a.score 




-- 26 查询每门课程被选修的次数
-- 思路,成绩表里面有几条数据就有几次考试
-- 分组后数课程数据量就知道这门课程被考试了几次
select cid ,count(*) from sc a GROUP BY cid


-- 27、查询出只有两门课程的全部学生的学号和姓名 
-- 关联成绩表,按照学生分组后,数一下有几条记录就是几门课程
select s.sid,s.sname from student s
inner join sc 
on s.sid = sc.sid
group by  s.sid,s.sname having count(*)=2

   -- 网友写法
		select sid,sname from student where sid in(
				select sid from sc GROUP BY sid HAVING COUNT(cid)=2);


-- 28 查询男生 女生人数
select Ssex, count(*) as 人数 from student
group by Ssex 


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


-- 30、查询同名同性学生名单，并统计同名人数 
 -- 查看同名同姓的名单
   select sname,ssex,count(*) 人数 from student 
   group by sname,ssex having count(*)>1
	
   -- 可以查看两个人的详细信息
   select * from student a
   inner join student b
   on a.sid !=b.sid and a.sname = b.sname and a.ssex = b.ssex

	   -- 其他写法
		select a.sname,a.ssex,count(*) from student a  JOIN 
					student b on a.sid !=b.sid and a.sname = b.sname and a.ssex = b.ssex
		GROUP BY a.sname,a.ssex


-- 31、查询1990年出生的学生名单
select * from student
where sage like '1990%'




-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 
select cid,avg(score) as avg_score from sc
group by cid  order by avg_score desc,cid asc



-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
select s.sid,s.sname,avg(sc.score) as avg_score from student s
inner join sc 
on s.sid = sc.sid
group by s.sid,s.sname
having avg_score>=85




-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 
select * from student s
inner join sc 
on s.sid = sc.sid
inner join course c
on sc.cid = c.cid 
where sc.score < 60 and c.cname='数学'

-- 子查询写法 数据多了子查询影响效率
select * from student s
inner join sc  on s.sid = sc.sid
where sc.cid in (
	 select cid from course where cname='数学' and sc.score <60
)




-- 35、查询所有学生的课程及分数情况,行转列
select 
s.sid,
s.sname,
 sum(case c.cname when '语文' then sc.score else 0 end) as '语文',
 sum(case c.cname when '数学' then sc.score else 0 end) as '数学',
 sum(case c.cname when '英语' then sc.score else 0 end) as '英语',
sum(sc.score) as '总分'
from student s
left join sc on s.sid = sc.sid
left join course c on c.cid = sc.cid
group by s.sid,s.sname 



-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；   
select 
 s.sid,s.sname,c.cname,sc.score
from student s
left join sc on s.sid = sc.sid
left join course c on c.cid = sc.cid
where sc.score >=70 order by sname



-- 37、查询不及格的课程
		select sc.sid,c.cname,sc.score from sc 
    inner join course c on sc.cid = c.cid    
		where sc.score < 60


-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名； 
    select 
          s.sid,
          s.sname
				from student s
				left join sc on s.sid = sc.sid
        where sc.cid='01' and sc.score>80


-- 39、求每门课程的学生人数 
    select count(*) from sc group by cid

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
   -- 这里分数可能有相同分数
   select s.*,c.cname,max(sc.score) score from student s
   left join sc on s.sid =sc.sid
   left join course c on c.cid = sc.cid
   left join teacher t on c.tid = t.tid
   where t.tname='张三'

-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
select distinct a.sid,a.cid,a.score from sc a,sc b
where a.cid!=b.cid and a.score=b.score 



-- 42、查询每门功成绩最好的前两名 
select a.sid,a.cid,a.score from sc a
left join sc b on a.cid = b.cid and a.score<b.score
group by a.sid,a.cid,a.score
having count(*)< 2 order by  a.sid,a.cid,a.score

-- 下面这个好理解一点  先过滤出分数高的一个所有数据然后总条数小于2
select a.sid,a.cid,a.score from sc a
		where (select COUNT(1) from sc b where b.cid=a.cid and b.score>=a.score)<=2 ORDER BY a.cid
		

-- 43 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
	select cid,count(*) as total from sc 
	group by cid having total>2
	order by total,cid asc

 -- 按学生分组然后数一下每个学生有几条记录,有几条记录就是参加了几门考试
-- 44、检索至少选修两门课程的学生学号 
	select sid,count(*) as sel from sc 
	group by sid having sel>=2
	order by sid asc


-- 45、查询选修了全部课程的学生信息 
 select * from student where sid in (
	select sid from sc group by sid having count(*)=(select count(*) from course) )


-- 46、查询各学生的年龄
	-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select sage,
				(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(sage,'%Y'))-  -- 拿当前年的年份减去字段中的年份获取到年龄多大
				-- 当前日期月日部分大于生日日期的月日部分,说明过了生日就-0 如果如果当前日期不大于数据库中的说明就还没有过生日就要减去1
				(case when DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(sage,'%m%d') then 0 else 1 end ) age
 from student 



-- 47、查询本周过生日的学生
	select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(sage)
	select * from student where YEARWEEK(s_birth)=YEARWEEK(DATE_FORMAT(NOW(),'%Y%m%d'))

-- week 返回1 - 52 代表一年中的某一周
select WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))
select WEEK(sage) from student;
-- YEARWEEK  返回年部分和周部分
select   YEARWEEK(DATE_FORMAT(NOW(),'%Y%m%d'))  from dual;
select YEARWEEK(sage) from student;


-- 48、查询下周过生日的学生  wee回某一周因为是下周所以加一然后去匹配学生
	select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =WEEK(sage)
	select DATE_FORMAT(NOW(),'%Y%m%d') from dual;


-- 49、查询本月过生日的学生
 -- 1  
 select * from student where month(Now())=month(sage)
 -- 2
 select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(sage)


-- 50、查询下月过生日的学生
 -- 1  
 select * from student where month(Now())+1=month(sage)
 -- 2
 select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(sage)
一定有人改了我的代码
关注
1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sql语句练习(Mysql)

转载:https://blog.csdn.net/fashion2014/article/details/78826299本来没有想着整理出来,有的表字段可能和转载内容不太一样如果有错误的地方还望大佬指出如果有好的写法可以留在评论区我会查看并更新的#学生表create table Student( SId VARCHAR(10), Sname VARCHAR(10), Sage VARCHAR(10), Ssex VARCHAR(10));insert into Stude.
复制链接

扫一扫