MySQL简单查询

1、查询男生和女生的个数：

select 
    gender, sum(sid)
from
    student
group by gender

2、查询平均成绩大于60分的同学的学号和平均成绩

select 
    student_id, avg(num)
from
    score
group by student_id
having avg(num) > 60;

3、接上面同时显示学生名字

select 
    B.student_id, student.sname, B.xxx
from
    (select 
        student_id, avg(num) as xxx
    from
        score
    group by student_id
    having avg(num) > 60) as B
        left join
    student ON B.student_id = student.sid；

-- avg(num)会被认为函数，不能做列名使用，加as xxx定义别名,（）as B定义临时表

4、查询所有同学的学号、姓名、选课数、总成绩：

select 
    score.student_id,
    student.sname,
    count(score.student_id),
    sum(score.num)
from
    score
        left join
    student ON score.student_id = student.sid
group by score.student_id

5、查询姓“李”的老师的个数:

select 
    count(tid)
from
    teacher
where
    tname like '李%'

6、查询没学过“李平”老师课的同学的学号、姓名

select 
        *
    from
        student
    where
        sid not in (select 
                student_id
            from
                score
            where
                course_id in (SELECT 
                        cid
                    FROM
                        db1.course
                            left join
                        teacher ON course.teacher_id = teacher.tid
                    where
                        tname = '李平老师'))

7、查询“生物”课程比“物理”课程成绩高的所有学生的学号

select 
    A.student_id
from
    (select 
        score.student_id, course.cname, score.num
    from
        score
    left join course ON score.course_id = course.cid
    where
        cname = '生物') as A
        left join
    (select 
        score.student_id, course.cname, score.num
    from
        score
    left join course ON score.course_id = course.cid
    where
        cname = '物理') as B ON A.student_id = B.student_id
where
    A.num > B.num

8、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

select 
    A.student_id, student.sname
from
    (select 
        *
    from
        score
    left join course ON score.course_id = course.cid
    where
        course.cid = 1 or course.cid = 2) as A
        left join
    student ON A.student_id = student.sid
where
    course_id > 1

9、查询和编号“2”号的同学学习的课程完全相同的其他同学学号和姓名

？？？

10、查询学过“李平”老师所教的所有课的同学的学号、姓名

???

11、查询所有课程成绩小于60分的同学的学号、姓名

select 
    A.student_id, student.sname
from
    (select 
        student_id
    from
        score
    where
        num < 60
    group by student_id) as A    -- distinct 也可去重,效率低
        left join
    student ON A.student_id = student.sid

12、查询没有学全所有课的同学的学号、姓名

13、
14、
15、
16、17、18、19、20、21、22、23、24、25、26、27、28、29、30、31、32、33、34、35、36、37