c mysql demo_MYSQL_demo-CSDN博客

本文链接：https://blog.csdn.net/weixin_28748205/article/details/113377177

-- 1、查询至少有一门课与学号为"1001"的同学所学相同的同学的学号和姓名;

SELECT cid FROM sc WHERE sid=1001;

SELECT sid FROM sc WHERE cid in (SELECT cid FROM sc WHERE sid=1001);

SELECT sid,sname FROM student WHERE sid in (SELECT sid FROM sc WHERE cid in (SELECT cid FROM sc WHERE sid=1001));

-- 2、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

SELECT cid,SUM(CASE WHEN score < 60 THEN1ELSE0 END ) as `[<60]`,

SUM(CASE WHEN score >= 60 THEN1ELSE0 END ) as `[70-60]` ,

SUM(CASE WHEN score >= 70 THEN1ELSE0 END ) as `[85-70]` ,

SUM(CASE WHEN score >= 85 THEN1ELSE0 END ) as `[100-85]`

FROM sc GROUP BY cid;

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

select sid FROM sc GROUP BY sid HAVING COUNT(sid)=1;

SELECT sid,sname FROM student WHERE sid in (select sid FROM sc GROUP BY sid HAVING COUNT(sid)=1);

-- 4、查询男生、女生学生人数

SELECT COUNT(sid) as '男生人数' FROM student WHERE ssex ='男';

SELECT COUNT(sid) as '女生人数' FROM student WHERE ssex ='女';

-- 5、查询姓"张"的学生名单

SELECT * FROM student WHERE sname like '%张%';

-- 6、查询同名同性学生名单，并统计同名人数、

SELECT * FROM student

SELECT a.sname,COUNT(1) FROM (SELECT * FROM student) a,(SELECT * FROM student) b WHERE a.sname=b.sname and a.sid != b.sid GROUP BY a.sname

-- 7、查询1981年出生的学生名单(注：student表中sage列的类型是datetime)

SELECT sname FROM student WHERE sage like '%1981%';

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

SELECT cid,AVG(score) as avg FROM scGROUP BY sid ORDER BY avg,cid DESC

-- 9、查询课程名称为"数据库"，且分数低于60的学生姓名和分数

SELECT cid FROM course WHERE cname = '数据库';

SELECT sid,score FROM sc WHERE cid in (SELECT cid FROM course WHERE cname = '数据库') HAVING score <60;

SELECT a.sname, b.score FROM (SELECT * FROM student) a, (SELECT sid,score FROM sc WHERE cid in (SELECT cid FROM course WHERE cname = '数据库') HAVING score <60) b

WHERE a.sid = b.sid

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

SELECT sid,cid FROM sc WHERE score >70;

SELECT sid,sname FROM student ;

SELECT * FROM course ;

SELECT b.sname,c.cname,a.score FROM (SELECT * FROM sc WHERE score >70) a,(SELECT * FROM student ) b,(SELECT * FROM course )c WHERE b.sid =a.sid and c.cid=a.cid;