mysql数据库

1.查询男生、女生人数
SELECT ssex,COUNT(ssex) FROM student GROUP BY ssex;

2.查询姓“吴”的学生名单
SELECT sname FROM student WHERE sname like '吴%';

3.查询姓“张”的老师的个数
SELECT tname,COUNT(tname) FROM teacher WHERE tname LIKE '张%' 

4.查询选了课程的学生人数
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM score;
SELECT sid FROM score GROUP BY sid;

SELECT COUNT(*) FROM (SELECT sid FROM score GROUP BY sid) s;
SELECT count(distinct sid ) FROM score ;

5.查询有学生不及格的课程，并按课程号从大到小排列

SELECT distinct cid FROM score where score<60 ORDER BY cid  desc;

6.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT cid  课程ID,MAX(score)  最高分,MIN(score) 最低分 FROM score GROUP BY cid;

7.查询不同课程平均分从高到低显示，要显示授课老师 (x)

SELECT tname,sc.cid,AVG(score) FROM teacher t,score sc ,course c WHERE c.cid=sc.cid 
AND t.tid=c.tid GROUP BY cid ORDER BY AVG(score) DESC;

8.查询所有学生的选课情况

SELECT sname,cid  from student st LEFT join score sc on st.sid=sc.sid 

9.查询“c001” 课程成绩大于等于80分的学生的学号和姓名

SELECT student.sid,sname FROM student,score 
WHERE student.sid = score.sid AND cid = "c001" AND score >= 80;

10.查询“c003”课程分数小于60的同学学号和姓名，按分数降序排列
SELECT student.sid,sname,score FROM student,score 
WHERE student.sid = score.sid AND cid = "03" AND score < 60 ORDER BY score desc;

11.查询课程名称为“语文”，且分数低于60 的学生姓名和分数
SELECT student.sid,sname,score FROM student,score ,course c
WHERE student.sid = score.sid AND cname = "语文" AND score < 60 AND c.cid=score.cid ;

12.查询所有同学的学号、姓名、选课数、总成绩 (x)
SELECT st.sid,sname,COUNT(cid),SUM(score) FROM student st left join score sc on st.sid=sc.sid
GROUP BY sid

13.查询每门课程被选修的学生数
SELECT cid,COUNT(sid) FROM score GROUP BY cid;

14.查询至少选修两门课程的学生学号和姓名 (X)
SELECT st.sid,sname FROM student st,score sc WHERE st.sid=sc.sid
GROUP BY sid
HAVING COUNT(cid)>=2;

15.查询出只选修了两门课程的学生学号和姓名

SELECT st.sid,sname FROM student st,score sc WHERE st.sid=sc.sid
GROUP BY sid
HAVING COUNT(cid)=2;

16.查询平均成绩大于80 的同学的学号、姓名和平均成绩 (x)
SELECT st.sid,sname, AVG(score) FROM student st,score sc WHERE st.sid=sc.sid
GROUP BY sid
HAVING AVG(score)>80;

17.查询所有课程成绩小于60 分的同学的学号、姓名
SELECT st.sid,sname FROM student st,score sc WHERE st.sid=sc.sid
GROUP BY sid
HAVING max(score)<60;

18.查询任何一门课程成绩在70 分以上的学生的姓名、课程名称和分数
SELECT sname,cname,score FROM student st,score sc,course co
WHERE st.sid=sc.sid 
AND sc.cid=co.cid 
GROUP BY sc.sid
HAVING MIN(score) >70;

19.查询两门及以上不及格课程的同学的学号及其平均成绩
SELECT sid,AVG(score) FROM score WHERE score<60 
GROUP BY sid HAVING COUNT(cid)>=2;

20.查询1990年出生的学生名单 
SELECT sname,sbirth_dt FROM student WHERE sbirth_dt LIKE '1990%';

21.查询同名同姓学生名单，并统计同名人数
SELECT sname,count(*)  FROM student GROUP BY sname HAVING COUNT(*)>=2 

insert into Student VALUES('09' , '王菊' , '1990-01-20' , '男');

22.查询不同课程但成绩相同的学生的学号、课程号、学生成绩
SELECT distinct sc.sid,sc.cid,sc.score FROM score sc ,score sc2 WHERE sc.score = sc2.score AND 
 sc.sid=sc2.sid  AND sc.cid != sc2.cid 

23.查询没有学全所有课的同学的学号、姓名
SELECT st.sid,sname FROM student st LEFT JOIN score sc ON st.sid=sc.sid GROUP BY sid
HAVING COUNT(cid)<3

24.查询全部学生都选修的课程的课程号和课程名

SELECT sc.cid,COUNT(sc.cid) ,cname FROM score sc,course c WHERE sc.cid=c.cid
 GROUP BY sc.cid HAVING COUNT(sc.cid)=8;

insert into Score values('07' , '01' , 98);