2020-08-28

最新推荐文章于 2022-12-19 20:30:18 发布
最新推荐文章于 2022-12-19 20:30:18 发布
阅读量213 收藏
点赞数
分类专栏： mysql 文章标签： sql 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hexuejian97/article/details/108276706
版权

Mysql高级-50题训练

数据表介绍:

--1.学⽣生表
Student(SId,Sname,Sage,Ssex)
--SId 学⽣生编号,Sname 学⽣生姓名,Sage 出⽣生年年⽉月,Ssex 学⽣生性别
--2.课程表
Course(CId,Cname,TId)
--CId 课程编号,Cname 课程名称,TId 教师编号
--3.教师表
Teacher(TId,Tname)
--TId 教师编号,Tname 教师姓名
--4.成绩表
SC(SId,CId,score)
--SId 学⽣生编号,CId 课程编号,score 分数

数据
student.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `SId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `Sage` datetime(0) NULL DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-12-20 00:00:00', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-12-06 00:00:00', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-01-01 00:00:00', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-01-01 00:00:00', '女');
INSERT INTO `student` VALUES ('09', '张三', '2017-12-20 00:00:00', '女');
INSERT INTO `student` VALUES ('10', '李四', '2017-12-25 00:00:00', '女');
INSERT INTO `student` VALUES ('11', '李四', '2012-06-06 00:00:00', '女');
INSERT INTO `student` VALUES ('12', '赵六', '2013-06-13 00:00:00', '女');
INSERT INTO `student` VALUES ('13', '孙七', '2014-06-01 00:00:00', '女');

SET FOREIGN_KEY_CHECKS = 1;

teacher.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `TId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `Tname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

SET FOREIGN_KEY_CHECKS = 1;

sc.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `SId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `CId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `score` decimal(18, 1) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', 80.0);
INSERT INTO `sc` VALUES ('01', '02', 90.0);
INSERT INTO `sc` VALUES ('01', '03', 99.0);
INSERT INTO `sc` VALUES ('02', '01', 70.0);
INSERT INTO `sc` VALUES ('02', '02', 60.0);
INSERT INTO `sc` VALUES ('02', '03', 80.0);
INSERT INTO `sc` VALUES ('03', '01', 80.0);
INSERT INTO `sc` VALUES ('03', '02', 80.0);
INSERT INTO `sc` VALUES ('03', '03', 80.0);
INSERT INTO `sc` VALUES ('04', '01', 50.0);
INSERT INTO `sc` VALUES ('04', '02', 30.0);
INSERT INTO `sc` VALUES ('04', '03', 20.0);
INSERT INTO `sc` VALUES ('05', '01', 76.0);
INSERT INTO `sc` VALUES ('05', '02', 87.0);
INSERT INTO `sc` VALUES ('06', '01', 31.0);
INSERT INTO `sc` VALUES ('06', '03', 34.0);
INSERT INTO `sc` VALUES ('07', '02', 89.0);
INSERT INTO `sc` VALUES ('07', '03', 98.0);

SET FOREIGN_KEY_CHECKS = 1;

course.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `CId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `Cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `TId` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

SET FOREIGN_KEY_CHECKS = 1;

题目和解答

-- 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select student.sid,student.sname,sc.score
from student
join
(
   select sc1.sid,sc1.score from
    (select sid,score from sc where cid = '01') as sc1
    join
    (select sid,score from sc where cid = '02') as sc2
    on sc1.sid = sc2.sid
    where sc1.score > sc2.score
) as sc
on student.sid = sc.sid;

-- 2. 查询同时存在" 01 "课程和" 02 "课程的情况
select sc1.* from
(select sid,score from sc where cid = '01') as sc1
join
(select sid,score from sc where cid = '02') as sc2
on sc1.sid = sc2.sid

-- 3. 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select sc1.*,sc2.* from
(select sid,score from sc where cid = '01') as sc1
LEFT join
(select sid,score from sc where cid = '02') as sc2
on sc1.sid = sc2.sid

-- 4. 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from sc
where sid not in (select sid from sc where cid = '01')
and cid = '02'

-- 5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT student.SId,student.Sname,ROUND(AVG(score),2) FROM student join sc on student.SId = sc.SId GROUP BY sc.SId HAVING AVG(sc.score)>60

-- 6.查询在 SC 表存在成绩的学生信息
SELECT DISTINCT student.* FROM student JOIN sc ON sc.SId = student.SId

-- 7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT student.SId,student.Sname,COUNT(cid) AS cidsum,SUM(sc.score) AS sumsource FROM student left join sc on student.SId = sc.SId GROUP BY student.SId

-- 8.查询李姓老师的数量
SELECT COUNT(*) FROM teacher where teacher.Tname like '李%'

-- 9.查询学过「张三」老师授课的同学的信息
SELECT student.* FROM student JOIN sc on sc.SId = student.SId JOIN course ON course.CId = sc.CId JOIN teacher ON teacher.TId = course.TId where teacher.Tname='张三'

-- 10.查询没有学全所有课程的同学的信息
SELECT student.* from student JOIN sc on sc.SId = student.SId JOIN course ON course.CId = sc.CId GROUP BY sc.SId HAVING COUNT(sc.SId)<(SELECT COUNT(*) FROM course)

-- 11.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT DISTINCT student.* FROM student JOIN sc on sc.SId = student.SId WHERE sc.CId IN (SELECT CId FROM sc WHERE sc.SId = '01')

-- 12.查询和"01"号的同学学习的课程 完全相同的其他同学的信息
SELECT student.* from student JOIN sc AS sc1 on sc1.SId = student.SId JOIN sc AS sc2 ON sc1.CId = sc2.CId AND sc2.SId = '01' AND sc1.SId!='01' GROUP BY sc1.SId HAVING COUNT(sc1.CId)=(SELECT COUNT(*) FROM sc WHERE sc.SId = '01')

-- 13.查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT SId,Sname FROM student WHERE student.SId NOT IN (SELECT SId FROM sc WHERE sc.CId IN (SELECT course.CId FROM course JOIN teacher ON course.TId = teacher.TId WHERE teacher.Tname ='张三'))

-- 14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT student.SId,Sname,ROUND(AVG(score) ,2) AS avgscore FROM student JOIN sc ON sc.SId = student.SId WHERE sc.score<60 GROUP BY sc.SId HAVING COUNT(sc.SId)>=2

-- 15.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT student.* FROM student JOIN sc ON sc.SId = student.SId WHERE sc.CId = '01' AND sc.score<60 GROUP BY sc.SId ORDER BY sc.score DESC

-- 16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT sc1.*,sc2.avgcourse FROM sc as sc1 JOIN (SELECT SId,AVG(score) as avgcourse FROM sc GROUP BY sc.SId) as sc2 ON sc2.SId = sc1.SId ORDER BY sc2.avgcourse DESC,sc1.SId
select
stu.sname,
a.score as '语文',
b.score as '数学',
c.score as '英语',
avg(d.score) as '平均成绩'
from student as stu
left join sc as a on stu.sid = a.sid and a.cid = '01'
left join sc as b on stu.sid = b.sid and b.cid = '02'
left join sc as c on stu.sid = c.sid and c.cid = '03'
left join sc as d on stu.sid = d.sid
group by stu.sname,语文,数学,英语
order by 平均成绩 desc;

/*
17.查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低
分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀
为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
*/
SELECT 
sc.CId,course.Cname,
MAX(sc.score) as maxscore,
MIN(sc.score) as minscore,
AVG(sc.score) AS avgscore,
(a.pass/e.mysum)AS pass,
(b.mediuma/e.mysum) AS mediuma,
(c.excellent/e.mysum) AS excellent,
(d.excellents/e.mysum) as excellents,
e.mysum as coursesum
FROM sc JOIN course ON sc.CId = course.CId 
left join (SELECT CId,count(*) AS pass FROM sc where sc.score>=60 GROUP BY sc.CId) AS a ON a.CId = sc.CId
left JOIN (SELECT CId,count(*) AS mediuma FROM sc where sc.score>=70 AND sc.score<80 GROUP BY sc.CId) AS b ON b.CId = sc.CId
left JOIN (SELECT CId,count(*) AS excellent FROM sc where sc.score>=80 AND sc.score<90 GROUP BY sc.CId) AS c ON c.CId = sc.CId
left JOIN (SELECT CId,count(*) AS excellents FROM sc where sc.score>=90 GROUP BY sc.CId) AS d ON d.CId = sc.CId
left JOIN (SELECT CId,COUNT(*) AS mysum FROM sc GROUP BY sc.CId) AS e ON e.CId = sc.CId
GROUP BY course.CId
ORDER BY coursesum DESC,sc.CId

-- 18.按各科平均成绩进行排序，并显示排名， Score 重复时保留名次空缺
SELECT a.CId,a.SId,AVG(a.score) AS avgscore,COUNT(b.score)+1 AS ranka FROM sc a
LEFT JOIN sc b ON a.score < b.score AND a.CId = b.CId
GROUP BY a.CId, a.SId,a.score ORDER BY avgscore

-- 19.按各科平均成绩进行排序，并显示排名， Score 重复时不保留名次空缺
SELECT a.CId,a.SId,AVG(a.score) AS avgscore,COUNT(b.score)+1 AS ranka FROM sc a
JOIN sc b ON a.score < b.score AND a.CId = b.CId
GROUP BY a.CId, a.SId,a.score ORDER BY avgscore

-- 20.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT sc.SId,sumscore,row_number() OVER(ORDER BY sumscore desc) AS Rankq FROM sc JOIN (SELECT SId,SUM(sc.score) as sumscore FROM sc GROUP BY sc.SId) AS a ON a.SId = sc.SId GROUP BY sc.SId order by sumscore desc

-- 21.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT sc.SId,sumscore,RANK() OVER(ORDER BY sumscore desc) AS Rankq FROM sc JOIN (SELECT SId,SUM(sc.score) as sumscore FROM sc GROUP BY sc.SId) AS a ON a.SId = sc.SId GROUP BY sc.SId order by sumscore desc 

-- 22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比
SELECT 
	sc.CId,
	course.Cname,
	CONCAT(ROUND((a.pass/e.mysum)*100,2),'%')AS pass,
	CONCAT(ROUND((b.mediuma/e.mysum)*100,2),'%')AS mediuma,
	CONCAT(ROUND((c.excellent/e.mysum)*100,2),'%')AS excellent,
	CONCAT(ROUND((d.excellents/e.mysum)*100,2),'%')AS excellents
FROM sc
	JOIN course ON sc.CId=course.CId
	left join (SELECT CId,count(*) AS pass FROM sc where sc.score<=60 AND sc.score>0 GROUP BY sc.CId) AS a ON a.CId = sc.CId
	left JOIN (SELECT CId,count(*) AS mediuma FROM sc where sc.score>60 AND sc.score<=70 GROUP BY sc.CId) AS b ON b.CId = sc.CId
	left JOIN (SELECT CId,count(*) AS excellent FROM sc where sc.score>70 AND sc.score<=85 GROUP BY sc.CId) AS c ON c.CId = sc.CId
	left JOIN (SELECT CId,count(*) AS excellents FROM sc where sc.score>85 AND sc.score<=100 GROUP BY sc.CId) AS d ON d.CId = sc.CId
	left JOIN (SELECT CId,COUNT(*) AS mysum FROM sc GROUP BY sc.CId) AS e ON e.CId = sc.CId
GROUP BY 
	sc.CId
	
-- 	23.查询各科成绩前三名的记录
SELECT
	s1.*
FROM
	sc s1
WHERE
	(
		SELECT
			count(1)
		FROM
			sc s2
		WHERE
			s2.CId = s1.CId
		AND s2.score > s1.score
	) < 3
ORDER BY
	s1.CId,
	s1.SId
	
-- 	24.查询每门课程被选修的学生数
SELECT sc.CId,COUNT(SId) AS '人数'FROM sc GROUP BY sc.CId

-- 25.查询出只选修两门课程的学生学号和姓名
SELECT student.SId,student.Sname FROM student JOIN sc ON sc.SId = student.SId GROUP BY sc.SId HAVING COUNT(sc.CId)=2

-- 26.查询男生、女生人人数
SELECT Ssex,count(*) FROM kkb_mysqlfifty.Student group by Ssex;

-- 27.查询名字中含有「风」字的学生信息
select * from Student where Sname like '%风%';

-- 28.查询同名同性学生名单,并统计同名人人数
select stu1.Sname,stu1.Ssex,count(*) from Student as stu1 join Student as stu2 on stu1.Sname=stu2.Sname and stu1.SId<>stu2.SId and stu1.Ssex = stu2.Ssex group by stu1.Sname;

-- 29.查询 1990 年年出生的学生名单
-- select Student.* from Student where Student.Sage = TimeStampDiff(year,'1990-01-01',now())
select Student.* from Student where year(Student.Sage) = '1990';

-- 30.查询每课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select SC.CId,avg(SC.score) as avgscore from SC group by SC.CId  order by avgscore desc,SC.CId
 
-- 31.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT student.SId,student.Sname,AVG(sc.score) AS avgscore from student JOIN sc ON sc.SId = student.SId GROUP BY sc.SId HAVING AVG(sc.score)>85

-- 32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT sc.SId,student.Sname,sc.score FROM student RIGHT JOIN sc ON sc.SId = student.SId WHERE sc.CId = (SELECT course.CId FROM course WHERE course.Cname = '数学') AND sc.score<60 

-- 33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT student.SId,student.Sname,sc.CId,sc.score FROM student LEFT JOIN sc ON sc.SId = student.SId
 
-- 34.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT student.Sname,course.Cname,sc.score FROM student JOIN sc ON sc.SId = student.SId JOIN course ON course.CId = sc.CId WHERE sc.score>70

-- 35.查询不及格的课程
SELECT course.Cname,sc.score FROM sc JOIN course ON course.CId = sc.CId WHERE sc.score<60

-- 36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT student.SId,student.Sname FROM student JOIN sc ON sc.SId = student.SId WHERE sc.score>=80 AND sc.CId='01'

-- 37.求每门课程的学生人数
SELECT sc.CId,course.Cname,COUNT(*) AS countnum FROM sc JOIN course ON course.CId = sc.CId GROUP BY sc.CId

-- 38.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT student.*,MAX(sc.score) FROM student JOIN sc ON sc.SId = student.SId WHERE sc.CId = (SELECT course.CId FROM course JOIN teacher ON course.TId = teacher.TId WHERE teacher.Tname = '张三')GROUP BY sc.CId

-- 39.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT student.*,MAX(sc.score) FROM student JOIN sc ON sc.SId = student.SId WHERE sc.CId = (SELECT course.CId FROM course JOIN teacher ON course.TId = teacher.TId WHERE teacher.Tname = '张三')

-- 40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT sc.SId,sc.CId,sc.score FROM sc JOIN sc AS sc2 ON sc.score = sc2.score AND sc.CId<>sc2.CId 

-- 41.查询每门课程成绩最好的前两名
SELECT
	s1.*
FROM
	sc s1
WHERE
	(
		SELECT
			count(1)
		FROM
			sc s2
		WHERE
			s2.CId = s1.CId
		AND s2.score > s1.score
	) < 2
ORDER BY
	s1.CId,
	s1.SId
	
	
-- 42.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
SELECT sc.CId,course.Cname,COUNT(*) AS countnum FROM sc JOIN course ON course.CId = sc.CId GROUP BY sc.CId HAVING COUNT(*)>5

-- 43.检索至少选修两门课程的学生学号
SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT(*)>=2

-- 44.查询选修了全部课程的学生信息
SELECT student.* FROM student JOIN sc ON sc.SId = student.SId GROUP BY sc.SId HAVING COUNT(*) = (SELECT COUNT(*) FROM course)

-- 45.查询各学生的年龄，只按年份来算
SELECT TIMESTAMPDIFF(YEAR,student.Sage,NOW()) AS age FROM student

-- 46.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT sid,sname,
CASE 
WHEN DATE_FORMAT(NOW(),'%m')-DATE_FORMAT(sage,'%m')<0
THEN DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(sage,'%Y')-1
WHEN DATE_FORMAT(NOW(),'%m')-DATE_FORMAT(sage,'%m')>0
THEN DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(sage,'%Y')
WHEN DATE_FORMAT(NOW(),'%m')-DATE_FORMAT(sage,'%m')=0
THEN IF( DATE_FORMAT(NOW(),'%d')-DATE_FORMAT(sage,'%d')<0 , DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(sage,'%Y')-1,DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(sage,'%Y'))
END 年龄
FROM student
 
-- 47.查询本周过生日的学生

SELECT * 
FROM
(
SELECT *
,DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY) firstdayofweek
, CONCAT_WS('-',YEAR(CURDATE()),DATE_FORMAT(sage,'%m-%d')) thisyear
,CONCAT_WS('-',YEAR(CURDATE())+1,DATE_FORMAT(sage,'%m-%d'))  nextyear
,DATE_ADD(CURDATE(),INTERVAL 6-WEEKDAY(CURDATE()) DAY) lastdayofweek

FROM student
) t1
WHERE
thisyear>= firstdayofweek AND thisyear<=lastdayofweek
OR
nextyear>= firstdayofweek AND nextyear<=lastdayofweek

-- 48.查询下周过生日的学生
SELECT * 
FROM
(
SELECT *
,DATE_ADD(CURDATE(),INTERVAL 7-WEEKDAY(CURDATE()) DAY) FirstDayOfNextweek
, CONCAT_WS('-',YEAR(CURDATE()),DATE_FORMAT(sage,'%m-%d')) ThisYearBirthday
,CONCAT_WS('-',YEAR(CURDATE())+1,DATE_FORMAT(sage,'%m-%d'))  NextYearBirthday
,DATE_ADD(CURDATE(),INTERVAL 13-WEEKDAY(CURDATE()) DAY) LastDayOfNextweek

FROM student
) t1
WHERE
ThisYearBirthday>= FirstDayOfNextweek AND ThisYearBirthday<=LastDayOfNextweek
OR
NextYearBirthday>= FirstDayOfNextweek AND NextYearBirthday<=LastDayOfNextweek

-- 49.查询本月过生日的学生
SELECT sid,sname,sage FROM student WHERE MONTH(NOW())=MONTH(sage)

-- 50.查询下月过生日的学生
SELECT sid,sname,sage FROM student WHERE MONTH(NOW())+1=MONTH(sage)
hexuejian97
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库练习题.docx
07-30
需要数据库表1.学生表 Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 2.课程表 Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号 3.教师表 Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名 4.成绩表 SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数
Java期末复习题
热门推荐
唐昊
12-29 2万+
第1章Java开发入门 一、填空题 1.将.java源文件编译为.class文件的是【 】命令。 2.在Eclipse窗口中选择菜单【 】→New→Java Project创建Java项目。 3.在安装JDK的过程中，有三个功能模块可供选择，它们分别是【 】，【 】和【 】。 4.将Hello.java文件编译为class文件，需要键入【 ...
Java期末考试试题及参考答案(11)
谷哥的小弟
12-19 4836
PreparedStatement接口是Statement的子接口，用于执行预编译的SQL语句。
SQL练习和Java作业
zwyoozwz的博客
05-19 627
1、 有一个员工表dept_emp简况如下: emp_no dept_no from_date to_date 10001 d001 1986-06-26 9999-01-01 10002 d001 1996-08-03 9999-01-01 10003 d002 1995-12-03 9999-01-01 第一行表示为员工编号为10001的部门是d001部门。 有一个部门经理表dept_manager简况如下:
Java self-test questions
AntarcticPenguin
11-03 1740
Java自测题——做错及不会的题（题号顺序不固定） 抽象类和抽象方法有哪些规则？ （1）抽象类必须使用abstract修饰符来修饰，抽象方法必须使用abstract修饰符来修饰。 （2）抽象类不能被实例化，无法使用new关键字来调用抽象类的构造器创建抽象类的实例，即使抽象类里不包含抽象方法，这个抽象类也不能创建实例。 （3）抽象类可以包含属性、方法（普通方法和抽象方法）、构造器、初始化块、内
libhv libev libuv libevent 2020-08-28 最新源代码
08-31
libhv libev libuv libevent 2020-08-28 最新源代码。这是几个经典的跨平台网络开发库
[更新]强大的SPWM波形数据生成工具 spwm_tool_v1.3.5 2020-08-28新版
08-28
作者原创：功能强大的SPWM正弦表生成工具，2020-08-28已更新到V1.3.5版，本工具使用简单方便、快捷，直观， 比用Excel处理起来方便多了。 非常适合玩正弦波电源逆变器，电机驱动，步进电机细分等相关的朋友。 使用...
阿里巴巴-SW（09988）1QFY21业绩：业绩超预期，核心电商依然稳固_2020-08-28_浦银国际.pdf
09-01
阿里巴巴-SW（09988）1QFY21业绩：业绩超预期，核心电商依然稳固_2020-08-28_浦银国际.pdf
CBM209X_UMPToolV7200(2020-06-24)2199E_8T24_9T24_TOG量产工具.zip
10-07
CBM209X_UMPToolV7200(2020-06-24)2199E_8T24_9T24_TOG量产工具.zip
js代码-手写代码练习---时间戳格式化（2020-10-10 12:00:00）
07-16
js代码-手写代码练习---时间戳格式化（2020-10-10 12:00:00）
经典的SQL笔试题题目
08-30
经典sql练习，几道经典的SQL笔试题题目。sql就在于多多练习
500道SQL练习题及答案.md
09-07
高级Mysql强化训练题库
50道sql练习含答案.docx
07-10
50道sql练习含答案，面试必备，刷过之后轻松解决数据库笔试题！
java 面试题基础题，sql 面试题 带答案
07-18
java 面试题基础题，sql 面试题 带答案
SQL练习题
daimenglaoshi的博客
11-09 2625
1.3、在这个数据库中创建一个名为 [成绩表(score)] 的表，由 [学号] 、[课程编号]、 [成绩] 字段组成。insert into student values(2001001,'吴小亮','计算机及应用','0101'),1.2、在这个数据库中创建一个名为[学生表(student)]的表，由[学号]、[姓名]、[专业]、[班级]8、从学生表和成绩表中查找记录，要求以两个表中的“学号”字段作为连接字段，23、查询所有考试01号课程分数不低于所有02号课程分数的学生学号，姓名；
SQL经典练习题(上)
Gauss松鼠会
09-19 7907
openGauss数据库SQL经典练习题进行实践与测试。
sql语句练习50题(Mysql版)
沈荣荣的博客
09-10 6381
–1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_score) –学生编号,课程编号,分数 测试数据 --建表 --学生表 CREATE TABLE `Student`( `s...
SQL 45道经典练习题
sosososoon的博客
09-07 4246
数据表 学生表 Student (SId,Sname,Sage,Ssex) SId 学生编号 Sname 学生姓名 Sage 出生年月 Ssex 学生性别 课程表 Course (CId,Cname,TId) CId 课程编号 Cname 课程名称 TId 教师编号 教师表 Teacher (TId,Tname) TId 教师编号 Tname 教师姓名 成绩表 SC (SId,CId,score) SId 学生编号 CId 课程编号 score 分数 查询" 01 “课
某互联网公司针对初级Java开发的几道Sql笔试题(看完保证不亏 )
我是臭鱼阿祥
03-25 1969
实不相瞒，我就是题目中所说的初级开发，在曲折的面试道路上，被各大互联网公司的笔试题吊打。所以，我就整了一个最经典的学生成绩表sql练习，保证你看完，有所收获。 下面是三张表的创表语句，供你们快速练习： Student表： CREATE TABLE `student` ( `sno` varchar(10) NOT NULL, `sname` varchar(10) DEFAULT NULL, `ssex` varchar(10) DEFAULT NULL, `sage` int(5
INSERT INTO `借阅表` VALUES (100001,'123413','0001','2020-11-05',NULL,'借阅'), (100002,'223411','0002','2020-9-28',2020-10-13,'已还'), (100003,'321123','1001','2020-7-01',NULL,'过期'), (100004,'321124','2001','2020-10-09',2020-10-14,'已还'), (100005,'321124','0001','2020-10-15',NULL,'借阅'), (100006,'223411','2001','2020-10-16',NULL,'借阅'), (100007,'411111','1002','2020-9-01',2020-9-24,'已还'), (100008,'411111','0001','2020-9-25',NULL,'借阅'), (100009,'411111','1001','2020-10-08',NULL,'借阅');
最新发布
05-11
- 借阅编号：100002，读者编号：223411，书籍编号：0002，借阅日期：2020-9-28，归还日期：2020-10-13，借阅状态：已还 - 借阅编号：100003，读者编号：321123，书籍编号：1001，借阅日期：2020-7-01，归还日期：...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值