mysql练习02-2
苏*
mysql
项目描述
mysql练习02-2
上传时间
2022.06.27
浏览人数
431人
苏*
天津市河东区
Hot:14503
-- 26、查询每门课程被选修的学生数
SELECT
c_id, COUNT(s_id)
FROM
score a
GROUP BY c_id;
-- 27、查询出只有两门课程的全部学生的学号和姓名
SELECT
s_id, s_name
FROM
student
WHERE
s_id IN (SELECT
s_id
FROM
score
GROUP BY s_id
HAVING COUNT(c_id) = 2);
-- 28、查询男生、女生人数
SELECT
s_sex, COUNT(s_sex) AS 人数
FROM
student
GROUP BY s_sex;
-- 30、查询同名同性学生名单，并统计同名人数
SELECT
s1.s_name, s1.s_sex , count(1) as 同名人数
FROM
student s1
JOIN
student s2 ON s1.s_id != s2.s_id
AND s1.s_name = s2.s_name
AND s1.s_sex = s2.s_sex
group by s1.s_name, s1.s_sex;
;
--
SELECT s1.s_name, s1.s_sex, count(1) AS '同名人数'
FROM student s1 JOIN student s2 ON s1.s_id != s2.s_id
AND s1.s_name = s2.s_name AND s1.s_sex = s2.s_sex
GROUP BY s1.s_name, s1.s_sex;
-- 31、查询1990年出生的学生名单
SELECT
s_name
FROM
student
WHERE
s_birth LIKE '1990%';
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id, avg(s_score) score_avg from score group by c_id order by score_avg desc, c_id asc;
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT
a.s_id, b.s_name, ROUND(AVG(a.s_score), 2) AS avg_score
FROM
score a
LEFT JOIN
student b ON a.s_id = b.s_id
GROUP BY s_id
HAVING avg_score >= 85;
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s.s_name, score.s_score from student s where s.s_id in (
select s_id from score s1 where s_score < 80 and c_id =
(select c_id from course where c_name = '数学') );
SELECT
s.s_name, s1.s_score
FROM
student s
LEFT JOIN
score s1 ON s.s_id = s1.s_id
WHERE s1.c_id = (select c_id from course where c_name = '数学') AND s1.s_score < 80
;
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT
s2.s_name, c1.c_name, s1.s_score
FROM
score s1
LEFT JOIN
student s2 ON s1.s_id = s2.s_id
LEFT JOIN
course c1 ON s1.c_id = c1.c_id
where s1.s_score >= 70;
-- 37、查询不及格的课程(人名字, 课程名字, 分数)
SELECT
a.s_id, a.c_id, b.c_name, a.s_score
FROM
score a
LEFT JOIN
course b ON a.c_id = b.c_id
WHERE
a.s_score < 60;
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select * from score as se left join student as st on se.s_id = st.s_id
left join course as ce on se.c_id = ce.c_id where ce.c_id = 01 and se.s_score > 80 ;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
-- t_id c_id s_id(s) max()
SELECT
a.*, b.s_score, b.c_id, c.c_name
FROM
student a
LEFT JOIN
Score b ON a.s_id = b.s_id
LEFT JOIN
course c ON b.c_id = c.c_id
WHERE
b.c_id = (SELECT
c_id
FROM
course c,
teacher d
WHERE
c.t_id = d.t_id AND d.t_name = '张三')
AND b.s_score IN (SELECT
MAX(s_score)
FROM
Score
WHERE
c_id = (SELECT
c_id
FROM
course c,
teacher d
WHERE
c.t_id = d.t_id AND d.t_name = '张三'));
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）
select se.c_id, count(1) as n from score se group by se.c_id having n>5;
-- 44、检索至少选修两门课程的学生学号
select * from student st left join score se on st.s_id = se.s_id
group by st.s_id having count(se.c_id) >= 2;
-- 45、查询选修了全部课程的学生信息
select * from student st left join score se on st.s_id = se.s_id and se.c_id = 01
left join score se1 on st.s_id = se1.s_id and se1.c_id = 02
left join score se2 on st.s_id = se2.s_id and se2.c_id = 03
where se.s_score is not null and se1.s_score is not null and se2.s_score is not null;
select * from student st left join score se on st.s_id = se.s_id
group by st.s_id having count(se.c_id) = 3;
-- –46、查询各学生的年龄
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
-- 时间格式的统一表示: %Y-%m-%d %H:%i:%s:代表: 年年年年-月月-日日 时时:分分:秒秒
-- NOW(): 获取当前时间
-- DATE_FORMAT:转化时间为指定格式
-- DATE_FORMAT(NOW(),’%Y’):获取年,其他的以此类推
-- WEEK('2019-07-11',1): 返回'2019-07-11'是2019年的第几周: 结果是28
-- YEARWEEK('2019-07-11',1);返回整数型的6位, 结果为:201928
-- MONTH('2018-06-08'): 获取月, 结果为6
-- select WEEK('2019-07-11',1) from student;
-- select YEARWEEK('2019-07-11',1) from student;
SELECT
s_birth,
(DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(s_birth, '%Y')
-
(CASE
WHEN
DATE_FORMAT(NOW(), '%m%d')
>
DATE_FORMAT(s_birth, '%m%d')
THEN 0
ELSE 1
END)
) AS age
FROM
student;
-- 47、查询本周过生日的学生
select * from student where week (concat(year(curdate()),'-',mid(s_birth,6,5)),1) = week (now(),1);
-- 48、查询下周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'),1)+1 =WEEK(s_birth);
SELECT * FROM student AS s WHERE WEEK(CONCAT(YEAR(CURDATE()),"-",MID(s_birth,6,5)),1) = WEEK(now(),1)+1;
SELECT * FROM student AS s WHERE WEEK(CONCAT(YEAR(CURDATE()),"-",MID(s_birth,6,5)),1) = WEEK(curdate(),1)+1;
-- CURDATE() 取的是年月日，CURTIME()取的是时分秒
-- 50、查询下月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth);
苏*
