MySQL 10条查询语句

-- 1、查询至少有一门课与学号为"1001"的同学所学相同的同学的学号和姓名;
select sc.sid,Sname from Student,SC
where Student.Sid=SC.Sid and cid in (select cid from SC where Sid=1009)GROUP BY sid;

 

-- 2、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT SC.Cid as 课程ID, Cname as 课程名称
,SUM(case when score between 85 and 100 then 1 else 0 end) AS '[100-85] '
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '[85-70] '
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS' [70-60]'
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS '[60-]'
FROM SC,Course
where SC.Cid=Course.Cid
GROUP BY SC.Cid,Cname;

 

-- 3、查询出只选修了一门课程的全部学生的学号和姓名
select SC.Sid,Student.Sname,count(Cid) AS 选课数
from SC,Student
where SC.Sid=Student.Sid group by SC.Sid ,Student.Sname having count(Cid)=1;

 

-- 4、查询男生、女生学生人数
select count(Ssex)AS男生人数 from student WHERE ssex='男';
select count(Ssex)AS女生人数 from student WHERE ssex='女‘’;

 

 

-- 5、查询姓"张"的学生名单
SELECT Sname FROM Student WHERE Sname like '张%';

 

-- 6、查询同名同性学生名单，并统计同名人数
select Sname,count(Sname) from Student group by Sname having count(Sname)>1;

 

-- 7、查询1981年出生的学生名单(注：student表中sage列的类型是datetime)
select * from student where year(sage) = '1981';

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

Select Cid,Avg(score) from SC group by Cid order by Avg(score),Cid DESC ;

 

-- 9、查询课程名称为"数据库"，且分数低于60的学生姓名和分数
Select Sname,score
from Student,SC,Course
where SC.Sid=Student.Sid and SC.Cid=Course.Cid and Course.Cname='数据库'and score <60;

 

-- 10、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
SELECT distinct student.Sid,student.Sname,SC.Cid,SC.score
FROM student,Sc
WHERE SC.score>=70 AND SC.Sid=student.Sid;