PAT数据库补题6-7

查询这周过生日的学生

查询下周过生日的学生

查询下月过生日的学生

查询各学生的年龄，只按年份来算

成绩有重复的情况下，查询不同老师所教不同课程平均分从高到低显示

查询出只选修一门课程的全部学生的学号和姓名

试卷发布当天作答人数和平均分

筛选限定昵称成就值活跃日期的用户

---

这周过生日的学生

查询本周过生日的学生，假设现在是 2020-12-22 00:00:00.000000

表结构：

CREATE TABLE `course` (
  `CId` varchar(10) DEFAULT NULL,
  `Cname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `TId` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `sc` (
  `SId` varchar(10) DEFAULT NULL,
  `CId` varchar(10) DEFAULT NULL,
  `score` decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `student` (
  `SId` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `Sage` datetime(6) DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `teacher` (
  `TId` varchar(10) DEFAULT NULL,
  `Tname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

输出样例：

select SId,Sname,Sage,Ssex

from student

where week("2020-12-22 00:00:00.000000") = week(Sage) 

 知识点：

week(date,mode)函数：返回date日期时一年中的第几周，mode是用来指定该星期从周日开始还是周一开始，没搞懂怎么用

查询下周过生日的学生

查询下周过生日的学生 假设现在是 2020-04-14 00:00:00.000000

CREATE TABLE `course` (
  `CId` varchar(10) DEFAULT NULL,
  `Cname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `TId` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `sc` (
  `SId` varchar(10) DEFAULT NULL,
  `CId` varchar(10) DEFAULT NULL,
  `score` decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `student` (
  `SId` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `Sage` datetime(6) DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `teacher` (
  `TId` varchar(10) DEFAULT NULL,
  `Tname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 输出样例：

select SId,Sname,Sage

from student

where week("2020-04-14 00:00:00.000000") + 1 = week(Sage)

周数加一就好咯

查询下月过生日的学生

查询下月过生日的学生 假设现在是 2020-04-14 00:00:00.000000

CREATE TABLE `course` (
  `CId` varchar(10) DEFAULT NULL,
  `Cname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `TId` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `sc` (
  `SId` varchar(10) DEFAULT NULL,
  `CId` varchar(10) DEFAULT NULL,
  `score` decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `student` (
  `SId` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `Sage` datetime(6) DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `teacher` (
  `TId` varchar(10) DEFAULT NULL,
  `Tname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

 输出样例：

select SId,Sname,Sage,Ssex

from student

where month("2020-04-14 00:00:00.0000") + 1 = month(age)

 知识点：

month(date)函数：返回date日期所在月份

查询各学生的年龄，只按年份来算

CREATE TABLE `student` (
  `SId` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `Sage` datetime(6) DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

输出样例：

select Sname as sname,

year(curdate()) - year(Sage)-3 as age

from student

os：感觉有bug不减3过不了。。 

知识点：

year(date)函数：返回date日期所在年份

curdate()函数：返回当前日期  curtime()函数：返回当前时间  now()函数：返回当前日期时间

成绩有重复的情况下，查询不同老师所教不同课程平均分从高到低显示

成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

CREATE TABLE `sc` (
  `SId` varchar(10) DEFAULT NULL,
  `CId` varchar(10) DEFAULT NULL,
  `score` decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `student` (
  `SId` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `Sage` datetime(6) DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `course` (
  `CId` varchar(10) DEFAULT NULL,
  `Cname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `TId` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `teacher` (
  `TId` varchar(10) DEFAULT NULL,
  `Tname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

输出样例：

SId	Sname	Sage	Ssex	score	CId
51	aaa	2017-12-25 00:00:00 000000	女	66.6	06

select sc.SId,Sname,Sage,Ssex,score,sc.CId
from student,course,sc,teacher
where student.SId = sc.SId and course.CId = sc.CId 
and teacher.TId = course.TId
and Tname = '张三' and score = (
    select max(score)
    from student,course,sc,teacher
    where student.SId = sc.SId and course.CId = sc.CId 
    and teacher.TId = course.TId
    and Tname = '张三'
)

Tips：注意子查询也要满足相应的条件

查询出只选修一门课程的全部学生的学号和姓名

CREATE TABLE `student` (
  `SId` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `Sage` datetime(6) DEFAULT NULL,
  `Ssex` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sc` (
  `SId` varchar(10) DEFAULT NULL,
  `CId` varchar(10) DEFAULT NULL,
  `score` decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

输出样例： 

这题卡了很久一直不知道怎么搞，主要有以下几个问题：

1. 关于group by语句和select语句。如果在有group by语句的操作中，select后接的结果集字段只有两种，一种是包含在gruop by语句中的；另一种是在聚合函数操作中的字段

常用聚合函数：max()、min()、sum()、avg()、count()

count(字段)：返回的行数是不包括该字段为null的字段的

count(*)：返回的是实际查询结果的总行数。

count(常量)：返回值不为null的行数，返回的也是实际查询结果的总行数。

2. 我一直是用的inner join 但实际上需要用的是left join 要用sc表里面的SId去分组和返回

select sc.SId as sid,Sname as sname,

count(CId) as '课程数'

from sc left join student on sc.SId = student.SId

group by sc.SId,Sname

having count(CId) = 1

试卷发布当天作答人数和平均分

drop table if exists examination_info,user_info,exam_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

样例输出： 

exam_id	uv	avg_score
9001	3	81.3

 分析：

• 返回：exam_id(试卷id),人数，平均分
• 试卷要求：SQL类别(在examination_info表中)
•                   作答时间是试卷当天发布的(在exam_record表中)
• 人要求：5级以上(在user_info中)且是不同的人
• 排序

所以以上就涉及到子查询：使试卷和用户满足要求

date()函数的使用（用来比较作答时间是否满足为试卷发布当天的要求）

 select exam_id,count(distinct uid) as uv,round(avg(score),1) as avg_score
from exam_record
where (exam_id,date(start_time)) in (
    select exam_id,date(release_time)
    from examination_info
    where tag = 'SQL'
)
and uid in(
    select uid 
    from user_info
    where level > 5
)
group by exam_id
order by uv desc,avg(score)

筛选限定昵称成就值活跃日期的用户

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

输出样例:

请在这里给出输出样例。例如：

1002|牛客2号|1200

---

昵称 成就值的条件可以很容易的就实现
关键的是用户的最近一次活跃日期 而这个活跃日期有两个来源
一个是试卷作答日期(exam_record表) 一个是题目练习日期(practice_record表)
所以在用户的子查询中：
我们可以再临时创建一个新表(from后面进行的子查询)
这个新表中有用户id、有试卷作答日期，有题目练习日期 这两个日期都合并名称为活跃日期
然后根据用户分组 选出最近的活跃日期即可

select uid,nick_name,achievement
from user_info
where nick_name like '牛客%号'
and achievement between 1200 and 2500
and uid in(
    select uid 
    from (
        select uid,submit_time as act_time
        from exam_record
        union 
        select uid,submit_time as act_time
        from practice_record
    ) t
    group by uid
    having max(act_time) like '2021-09%'
)