SQL经典查询案例

谁借莪１个温暖的怀抱￠ 2022-09-02 04:27 238阅读 0赞

# 一、表结构 #

## 1.1 学生表 ##

create table student(
        sid varchar(10) NOT NULL COMMENT '学生ID',
        username varchar(20) NOT NULL COMMENT '学生名称',
        sex enum("man","woman") NOT NULL DEFAULT 'man' COMMENT '性别',
        birthday datetime NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '出生日期',
        primary key(sid)  
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';
    
    insert into student values('01', '赵雷', '男', '1990-01-01');
    insert into student values('02', '钱电', '男', '1990-12-21');
    insert into student values('03', '孙风', '男', '1990-05-20');
    insert into student values('04', '李云', '男', '1990-08-06');
    insert into student values('05', '周梅', '女', '1991-12-01');
    insert into student values('06', '吴兰', '女', '1992-03-01');
    insert into student values('07', '郑竹', '女', '1989-07-01');
    insert into student values('08', '王菊', '女', '1990-01-20');

## 1.2 教师表 ##

create table teacher(
        tid varchar(10) NOT NULL COMMENT '教师ID',
        tname varchar(20) NOT NULL COMMENT '教师名称',
        primary key(tid)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教师表';
    
    insert into teacher values('01' , '张三'),('02' , '李四'),('03' , '王五');

## 1.3 课程表 ##

create table course(
        cid varchar(10) NOT NULL COMMENT '课程ID',
        tid varchar(10) NOT NULL COMMENT '教师ID',
        cname varchar(20) NOT NULL COMMENT '课程名称',
        primary key(cid)  
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';
    
    insert into course values('01', '02', '语文');
    insert into course values('02', '01', '数学');
    insert into course values('03', '03', '英语');

## 1.4 成绩表 ##

create table SC(
        sid varchar(10) NOT NULL COMMENT '学生ID',
        cid varchar(10) NOT NULL COMMENT '课程ID',
        score tinyint(3) NOT NULL COMMENT '分数' 
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成绩表';
    
    insert into SC values('01' , '01' , 80);
    insert into SC values('01' , '02' , 90);
    insert into SC values('01' , '03' , 99);
    insert into SC values('02' , '01' , 70);
    insert into SC values('02' , '02' , 60);
    insert into SC values('02' , '03' , 80);
    insert into SC values('03' , '01' , 80);
    insert into SC values('03' , '02' , 80);
    insert into SC values('03' , '03' , 80);
    insert into SC values('04' , '01' , 50);
    insert into SC values('04' , '02' , 30);
    insert into SC values('04' , '03' , 20);
    insert into SC values('05' , '01' , 76);
    insert into SC values('05' , '02' , 87);
    insert into SC values('06' , '01' , 31);
    insert into SC values('06' , '03' , 34);
    insert into SC values('07' , '02' , 89);
    insert into SC values('07' , '03' , 98);

# 二、SQL查询场景 #

## 2.0 \[ \* \]所有表关联查询, 使用左连接是避免学生没分数记录 ##

select * from student a left join (
    	select a.tid,a.tname,b.cid,b.score,b.sid,c.cname from teacher a,SC b,course c where a.tid=c.tid and b.cid=c.cid
    ) b on a.sid=b.sid;
    +-----+----------+-----+---------------------+------+-------+------+-------+------+-------+
    | sid | username | sex | birthday            | tid  | tname | cid  | score | sid  | cname |
    +-----+----------+-----+---------------------+------+-------+------+-------+------+-------+
    | 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 02   | 李四  | 01   |    80 | 01   | 语文  |
    | 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 01   | 张三  | 02   |    90 | 01   | 数学  |
    | 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 03   | 王五  | 03   |    99 | 01   | 英语  |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 02   | 李四  | 01   |    70 | 02   | 语文  |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 01   | 张三  | 02   |    60 | 02   | 数学  |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 03   | 王五  | 03   |    80 | 02   | 英语  |
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 02   | 李四  | 01   |    80 | 03   | 语文  |
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 01   | 张三  | 02   |    80 | 03   | 数学  |
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 03   | 王五  | 03   |    80 | 03   | 英语  |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 | 02   | 李四  | 01   |    50 | 04   | 语文  |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 | 01   | 张三  | 02   |    30 | 04   | 数学  |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 | 03   | 王五  | 03   |    20 | 04   | 英语  |
    | 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 02   | 李四  | 01   |    76 | 05   | 语文  |
    | 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 01   | 张三  | 02   |    87 | 05   | 数学  |
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 02   | 李四  | 01   |    31 | 06   | 语文  |
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 03   | 王五  | 03   |    34 | 06   | 英语  |
    | 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 01   | 张三  | 02   |    89 | 07   | 数学  |
    | 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 03   | 王五  | 03   |    98 | 07   | 英语  |
    | 08  | 王菊     | 女  | 1990-01-20 00:00:00 | NULL | NULL  | NULL | NULL  | NULL | NULL  |
    +-----+----------+-----+---------------------+------+-------+------+-------+------+-------+

## 2.1 查询各科成绩最高分及同学 ##

select a.sid,a.username,c.cname,s.score from student a,course c,SC s,(
    	select cid,max(score)as score from SC group by cid) b 
    	where c.cid=s.cid and c.cid=b.cid and s.score=b.score and a.sid=s.sid;

## 2.2 查询语文比数学成绩高的学生信息及课程分数 ##

select a.*,b.score 语文,c.score 数学 from student a,SC b,SC c where a.sid=b.sid and a.sid=c.sid and b.cid='01' and c.cid='02' and b.score>c.score;
    +-----+----------+-----+---------------------+------+------+
    | sid | username | sex | birthday            | 语文 | 数学 |
    +-----+----------+-----+---------------------+------+------+
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 |   70 |   60 |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 |   50 |   30 |
    +-----+----------+-----+---------------------+------+------+

## 2.3 查询平均成绩大于等于90分的同学的学生编号，学生姓名，平均成绩 ##

select b.sid,a.username,avg(b.score) '平均成绩' from student a,SC b where a.sid=b.sid group by b.sid having avg(b.score)>90;
    +-----+----------+----------+
    | sid | username | 平均成绩 |
    +-----+----------+----------+
    | 07  | 郑竹     | 93.5     |
    +-----+----------+----------+

## 2.4 查询没有成绩的学生信息 ##

select * from student where student.sid not in (select sid from SC group by sid);
    +-----+----------+-----+---------------------+
    | sid | username | sex | birthday            |
    +-----+----------+-----+---------------------+
    | 08  | 王菊     | 女  | 1990-01-20 00:00:00 |
    +-----+----------+-----+---------------------+

## 2.5 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩, 降序 ##

select a.sid,a.username,count(b.cid) '选课总数',sum(b.score) '总分数' from student a left join SC b on a.sid=b.sid group by a.sid order by sum(b.score) desc;
    +-----+----------+----------+--------+
    | sid | username | 选课总数 | 总分数 |
    +-----+----------+----------+--------+
    | 01  | 赵雷     |        3 | 269    |
    | 03  | 孙风     |        3 | 240    |
    | 02  | 钱电     |        3 | 210    |
    | 07  | 郑竹     |        2 | 187    |
    | 05  | 周梅     |        2 | 163    |
    | 04  | 李云     |        3 | 100    |
    | 06  | 吴兰     |        2 | 65     |
    | 08  | 王菊     |        0 | NULL   |
    +-----+----------+----------+--------+

## 2.6 查询张三老师教过的所有同学的信息 ##

select a.* from student a, teacher b,course c, SC d where a.sid=d.sid and c.cid=d.cid and b.tid=c.tid and b.tname="张三";
    +-----+----------+-----+---------------------+
    | sid | username | sex | birthday            |
    +-----+----------+-----+---------------------+
    | 01  | 赵雷     | 男  | 1990-01-01 00:00:00 |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 |
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 |
    | 05  | 周梅     | 女  | 1991-12-01 00:00:00 |
    | 07  | 郑竹     | 女  | 1989-07-01 00:00:00 |
    +-----+----------+-----+---------------------+

## 2.7 查询张三老师没教过的所有同学的信息 ##

(先把教过的学生sid找出来，然后用sid not in)

select * from student where sid not in(
    	select a.sid from SC a,course b,teacher c where a.cid=b.cid and b.tid=c.tid and c.tname="张三"
    );
    +-----+----------+-----+---------------------+
    | sid | username | sex | birthday            |
    +-----+----------+-----+---------------------+
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
    | 08  | 王菊     | 女  | 1990-01-20 00:00:00 |
    +-----+----------+-----+---------------------+

## 2.8 查询同时报了语文和数学课的同学信息 ##

把学了语文或数学的记录找出来，然后按学生id分组大于等于2即可

select a.*,count(b.cid) from student a,SC b where a.sid=b.sid and b.cid in (
    	select cid from course where cname="语文" or cname="数学"
    ) group by b.sid having count(b.cid)>=2;
    +-----+----------+-----+---------------------+--------------+
    | sid | username | sex | birthday            | count(b.cid) |
    +-----+----------+-----+---------------------+--------------+
    | 01  | 赵雷     | 男  | 1990-01-01 00:00:00 |            2 |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 |            2 |
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 |            2 |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 |            2 |
    | 05  | 周梅     | 女  | 1991-12-01 00:00:00 |            2 |
    +-----+----------+-----+---------------------+--------------+

## 2.9 查询报了语文但是没报数学课的同学的信息 ##

select c.* from student c,SC d where c.sid=d.sid and c.sid not in (
    	select sid from SC a,course b where a.cid=b.cid and b.cname="数学"
    ) and d.cid=(select cid from course where course.cname="语文");
    +-----+----------+-----+---------------------+
    | sid | username | sex | birthday            |
    +-----+----------+-----+---------------------+
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
    +-----+----------+-----+---------------------+

## 2.10 查询没有学全所有课程的同学的信息 ##

select a.* from student a,SC b where a.sid=b.sid group by b.sid having count(b.cid)<(select count(*) from course);
    +-----+----------+-----+---------------------+
    | sid | username | sex | birthday            |
    +-----+----------+-----+---------------------+
    | 05  | 周梅     | 女  | 1991-12-01 00:00:00 |
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
    | 07  | 郑竹     | 女  | 1989-07-01 00:00:00 |
    +-----+----------+-----+---------------------+

## 2.11 查询与吴兰课程有交集的同学 ##

select a.* from student a,SC b where a.sid=b.sid and b.cid in (select cid from student a,SC b where a.sid=b.sid and a.username="吴兰") group by b.sid;
    +-----+----------+-----+---------------------+
    | sid | username | sex | birthday            |
    +-----+----------+-----+---------------------+
    | 01  | 赵雷     | 男  | 1990-01-01 00:00:00 |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 |
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 |
    | 05  | 周梅     | 女  | 1991-12-01 00:00:00 |
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
    | 07  | 郑竹     | 女  | 1989-07-01 00:00:00 |
    +-----+----------+-----+---------------------+

## 2.12 查询和赵雷同学的课程完全相同的同学信息 ##

把赵雷的每个课的同学记录找出来，学生id分组后课程数=赵雷的课程数即可

select a.*,count(b.cid) from student a,SC b where a.sid=b.sid and b.cid in (
    	select cid from student a,SC b where a.sid=b.sid and a.username="赵雷"
    ) group by sid having count(cid)=(
    	select count(b.cid) from student a,SC b where a.sid=b.sid and a.username="赵雷"
    );
    +-----+----------+-----+---------------------+--------------+
    | sid | username | sex | birthday            | count(b.cid) |
    +-----+----------+-----+---------------------+--------------+
    | 01  | 赵雷     | 男  | 1990-01-01 00:00:00 |            3 |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 |            3 |
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 |            3 |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 |            3 |
    +-----+----------+-----+---------------------+--------------+

## 2.13 查询没学过"张三"老师讲授的任一门课程的学生姓名 ##

先把学过张三的学生记录找出来，再反向查询

select a.* from student a,SC b where a.sid=b.sid and a.sid not in (select b.sid from teacher a,SC b,course c where a.tid=c.tid and b.cid=c.cid and a.tname="张三") group by a.sid;
    +-----+----------+-----+---------------------+
    | sid | username | sex | birthday            |
    +-----+----------+-----+---------------------+
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
    +-----+----------+-----+---------------------+

## 2.14 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 ##

select b.sid,a.username,count(b.cid),avg(b.score)'平均成绩'
     from student a,SC b where a.sid=b.sid and b.score<60 group by sid having count(b.cid)>=2;
    +-----+----------+--------------+----------+
    | sid | username | count(b.cid) | 平均成绩 |
    +-----+----------+--------------+----------+
    | 04  | 李云     |            3 | 33.3333  |
    | 06  | 吴兰     |            2 | 32.5     |
    +-----+----------+--------------+----------+

## 2.15 检索语文分数小于60，按分数降序排列的学生信息 ##

select a.*,b.score from student a,SC b,course c where a.sid=b.sid and b.cid=c.cid and c.cname='语文' and b.score<60 order by b.score desc;
    +-----+----------+-----+---------------------+-------+
    | sid | username | sex | birthday            | score |
    +-----+----------+-----+---------------------+-------+
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 |    50 |
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |    31 |
    +-----+----------+-----+---------------------+-------+

## 2.16 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 ##

–先计算学生ID对应的平均成绩记录==>sid,avg  
–再用成绩表左连接on sid >sid,avg,cid,score  
–再用课程表连接查询>sid,cid,score,avg,cname  
–再用学生表左连接on sid, 并根据avg排序

select * from student left join (
    	select c.cname,d.sid,d.cid,d.score,d.avg from course c, (
    		select a.sid,cid,score,avg from SC a left join (
    			select b.sid,avg(score) avg from SC b group by b.sid
    		) b on a.sid=b.sid
    	) d where c.cid=d.cid
    ) b on student.sid=b.sid order by b.avg desc;
    +-----+----------+-----+---------------------+-------+------+------+-------+---------+
    | sid | username | sex | birthday            | cname | sid  | cid  | score | avg     |
    +-----+----------+-----+---------------------+-------+------+------+-------+---------+
    | 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 数学  | 07   | 02   |    89 | 93.5    |
    | 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 英语  | 07   | 03   |    98 | 93.5    |
    | 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 语文  | 01   | 01   |    80 | 89.6667 |
    | 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 数学  | 01   | 02   |    90 | 89.6667 |
    | 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 英语  | 01   | 03   |    99 | 89.6667 |
    | 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 语文  | 05   | 01   |    76 | 81.5    |
    | 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 数学  | 05   | 02   |    87 | 81.5    |
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 语文  | 03   | 01   |    80 | 80      |
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 数学  | 03   | 02   |    80 | 80      |
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 英语  | 03   | 03   |    80 | 80      |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 数学  | 02   | 02   |    60 | 70      |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 英语  | 02   | 03   |    80 | 70      |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 语文  | 02   | 01   |    70 | 70      |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 | 数学  | 04   | 02   |    30 | 33.3333 |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 | 英语  | 04   | 03   |    20 | 33.3333 |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 | 语文  | 04   | 01   |    50 | 33.3333 |
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 英语  | 06   | 03   |    34 | 32.5    |
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 语文  | 06   | 01   |    31 | 32.5    |
    | 08  | 王菊     | 女  | 1990-01-20 00:00:00 | NULL  | NULL | NULL | NULL  | NULL    |
    +-----+----------+-----+---------------------+-------+------+------+-------+---------+

## 2.17 查询学生的总成绩并进行排名 ##

select a.*,sum(b.score) from student a,SC b where a.sid=b.sid group by sid order by sum(b.score) desc; -- 使用左连接是避免空成绩
    select * from student a left join (select SC.sid,sum(SC.score) sum from SC group by SC.sid) b on a.sid=b.sid order by sum desc;
    +-----+----------+-----+---------------------+------+------+
    | sid | username | sex | birthday            | sid  | sum  |
    +-----+----------+-----+---------------------+------+------+
    | 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 01   | 269  |
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 03   | 240  |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 02   | 210  |
    | 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 07   | 187  |
    | 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 05   | 163  |
    | 04  | 李云     | 男  | 1990-08-06 00:00:00 | 04   | 100  |
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 06   | 65   |
    | 08  | 王菊     | 女  | 1990-01-20 00:00:00 | NULL | NULL |
    +-----+----------+-----+---------------------+------+------+

## 2.18 查询不同老师所教不同课程平均分从高到低显示 ##

(左连接是避免有些老师没教书)

select b.cid,c.cname,d.tname,avg(b.score) from SC b,course c,teacher d where b.cid=c.cid and c.tid=d.tid group by b.cid order by avg(b.score) desc;
    +-----+-------+-------+--------------+
    | cid | cname | tname | avg(b.score) |
    +-----+-------+-------+--------------+
    | 02  | 数学  | 张三  | 72.6667      |
    | 03  | 英语  | 王五  | 68.5         |
    | 01  | 语文  | 李四  | 64.5         |
    +-----+-------+-------+--------------+

## 2.19 查询总成绩第2名到第3名的学生信息及该课程成绩 ##

select a.*,sum(b.score) from student a, SC b where a.sid=b.sid group by b.sid order by sum(b.score) desc limit 1,2;
    +-----+----------+-----+---------------------+--------------+
    | sid | username | sex | birthday            | sum(b.score) |
    +-----+----------+-----+---------------------+--------------+
    | 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 240          |
    | 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 210          |
    +-----+----------+-----+---------------------+--------------+

## 2.20 统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比 ##

select a.cid,b.cname,sum(a.score>85) '85-100',sum(a.score>=60 and a.score<85) '60-84',sum(a.score<60) '0-59' from SC a,course b where a.cid=b.cid group by a.cid;
    +-----+-------+--------+-------+------+
    | cid | cname | 85-100 | 60-84 | 0-59 |
    +-----+-------+--------+-------+------+
    | 01  | 语文  | 0      | 4     | 2    |
    | 02  | 数学  | 3      | 2     | 1    |
    | 03  | 英语  | 2      | 2     | 2    |
    +-----+-------+--------+-------+------+

## 2.21 查询各科选修人数 ##

select a.cid,b.cname,count(a.sid) from SC a,course b where a.cid=b.cid group by a.cid;
    +-----+-------+--------------+
    | cid | cname | count(a.sid) |
    +-----+-------+--------------+
    | 01  | 语文  |            6 |
    | 02  | 数学  |            6 |
    | 03  | 英语  |            6 |
    +-----+-------+--------------+

## 2.22 查询只选修两门课的学生信息 ##

select a.*,b.sid,count(b.cid) from student a,SC b where a.sid=b.sid group by b.sid having count(b.cid)=2;
    +-----+----------+-----+---------------------+-----+--------------+
    | sid | username | sex | birthday            | sid | count(b.cid) |
    +-----+----------+-----+---------------------+-----+--------------+
    | 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 05  |            2 |
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 06  |            2 |
    | 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 07  |            2 |
    +-----+----------+-----+---------------------+-----+--------------+

## 2.23 查询男生、女生人数 ##

select sex,count(sid) from student group by sex;
    +-----+------------+
    | sex | count(sid) |
    +-----+------------+
    | 男  |          4 |
    | 女  |          4 |
    +-----+------------+

## 2.24 查询1990后出生的学生名单(注：Student表中Sage列的类型是datetime) ##

select * from student where year(birthday)>1990;
    +-----+----------+-----+---------------------+
    | sid | username | sex | birthday            |
    +-----+----------+-----+---------------------+
    | 05  | 周梅     | 女  | 1991-12-01 00:00:00 |
    | 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
    +-----+----------+-----+---------------------+

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

select a.cid,b.cname,avg(a.score) from SC a,course b where a.cid=b.cid group by a.cid order by avg(a.score) desc,a.cid asc;
    +-----+-------+--------------+
    | cid | cname | avg(a.score) |
    +-----+-------+--------------+
    | 02  | 数学  | 72.6667      |
    | 03  | 英语  | 68.5         |
    | 01  | 语文  | 64.5         |
    +-----+-------+--------------+

## 2.26 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 ##

select a.sid,b.username,avg(a.score) avg from SC a,student b where a.sid=b.sid group by a.sid having avg>85;
    +-----+----------+---------+
    | sid | username | avg     |
    +-----+----------+---------+
    | 01  | 赵雷     | 89.6667 |
    | 07  | 郑竹     | 93.5    |
    +-----+----------+---------+

## 2.27 查询数学课程分数低于60的学生姓名和分数 ##

select c.username,a.score from SC a,course b,student c where a.sid=c.sid and a.cid=b.cid and b.cname='数学' and a.score <60;
    +-----+----------+-------+
    | sid | username | score |
    +-----+----------+-------+
    | 04  | 李云     |    30 |
    +-----+----------+-------+

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

select a.username,c.cname,b.score from student a,SC b,course c where a.sid=b.sid and b.cid=c.cid and score>70;
    +----------+-------+-------+
    | username | cname | score |
    +----------+-------+-------+
    | 赵雷     | 语文  |    80 |
    | 赵雷     | 数学  |    90 |
    | 赵雷     | 英语  |    99 |
    | 钱电     | 英语  |    80 |
    | 孙风     | 语文  |    80 |
    | 孙风     | 数学  |    80 |
    | 孙风     | 英语  |    80 |
    | 周梅     | 语文  |    76 |
    | 周梅     | 数学  |    87 |
    | 郑竹     | 数学  |    89 |
    | 郑竹     | 英语  |    98 |
    +----------+-------+-------+

## 2.29 查询选修了全部课程的学生信息 ##

select a.sid,b.username,count(cid) from SC a,student b where a.sid=b.sid group by sid having count(cid)=(select count(*) from course);
    +-----+----------+------------+
    | sid | username | count(cid) |
    +-----+----------+------------+
    | 01  | 赵雷     |          3 |
    | 02  | 钱电     |          3 |
    | 03  | 孙风     |          3 |
    | 04  | 李云     |          3 |
    +-----+----------+------------+