MySQL简单例子

前言

真的是一些简单例子，我还写了好久

database.stu.sql 文件内容:

drop database if exists stu;
create database stu;
use stu;
set names gbk;
show tables;

drop table if exists student;
create table student(
    sid int primary key auto_increment not null,
    sname varchar(20) not null,
    sage int,
    ssex varchar(2)
);

insert into student (sname,sage,ssex) values ('haoge',23,'m');
insert into student (sname,sage,ssex) values ('dahan',22,'m');
insert into student (sname,sage,ssex) values ('xiaowei',23,'m');
insert into student (sname,sage,ssex) values ('limei',23,'w');
insert into student (sname,sage,ssex) values ('zhangxin',23,'w');
insert into student (sname,sage,ssex) values ('liyan',23,'w');
insert into student (sname,sage,ssex) values ('qq',23,'w');

drop table if exists teacher;
create table teacher(
    tid int primary key auto_increment not null,
    tname varchar(20) unique not null
);

insert into teacher (tname) values ('zou'),('叶平老师',('lixiao'));
insert into teacher (tname) values ('gu');

drop table if exists course;
create table course(
    cid int primary key auto_increment not null,
    cname varchar(20) default 'java',
    tid int,
    foreign key (tid) references teacher(tid) #not null
);

insert into course (tid) values (1);
insert into course (cname,tid) values ('.net',1);
insert into course (cname,tid) values ('Jsp',2);
insert into course (cname,tid) values ('html',3);
insert into course (cname) values ('ajax');

drop table if exists sc;
create table sc(
    sid int,
    cid int,
    score int,
    primary key(sid,cid)
);

delete from sc;
insert into sc (sid,cid,score) values (1,1,77);
insert into sc (sid,cid,score) values (1,2,73);
insert into sc (sid,cid,score) values (2,1,87);
insert into sc (sid,cid,score) values (2,2,77);
insert into sc (sid,cid,score) values (3,1,71);
insert into sc (sid,cid,score) values (3,2,72);
insert into sc (sid,cid,score) values (3,3,73);
insert into sc (sid,cid,score) values (4,1,70);
insert into sc (sid,cid,score) values (5,1,71);
insert into sc (sid,cid,score) values (5,2,72);

###表信息一览：
学生表：

student(sid,sname,sage,ssex)

课程表：

course(cid,cname,tid)

教师表：

teacher(tid,tname)

成绩表：

sc(sid,cid,score)

题目

1. 查询 01 课程比 02 课程成绩高的学生的信息以及课程分数
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
3. 查询所有学生的学号，学生姓名，选课总数，所有课程的总成绩
4. 查询学过 zou 授课的同学的信息
5. 查询学过编号 01 并且也学过编号 02 的课程的同学的信息
6. 查询没有学全所有课程的同学的信息
7. 查询没有学过 zou 讲授的任一一门课程的学生姓名
8. 查询两门及以上<80 分课程的同学的学号，姓名以及其平均成绩
9. 统计各科成绩各分数段的人数:课程编号，课程名称【100-85】【85-70】【70-60】【0-60】
10. 把“SC”表中 zou 教的课的成绩都更改为此课程的平均成绩
11. 不用 max()求学号最大的学生信息
12. 查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名

查询基础样式

select
from
where
group by
having
order by
limit

解答

1. 连表查询

   select sid,score from sc where cid = 1;
select sid,score from sc where cid = 2;
select a.sid,a.score,b.score from (select sid,score from sc where cid = 1) a,(select sid,score from sc where cid = 2) b where a.sid = b.sid and a.score > b.score;

   或者自连查询

   select sc1.sid,sc1.score,sc2.score from sc sc1,sc sc2 where sc1.cid = 1 and sc2.cid = 2 and sc1.sid = sc2.sid and sc1.score > sc2.score;

2. select s.sid,s.sname,avg(sc.score) from student s,sc where s.sid = sc.sid group by s.sid having avg(sc.score) >= 60;

3. select s.sid,s.sname,count(sc.cid),sum(sc.score) from student s,sc where s.sid = sc.sid group by s.sid;

4. select distinct sc.sid from teacher t,course c,sc where t.tid = c.tid and c.cid = sc.cid and t.tname = 'zou';
   —–distinct 查询结果去重 ⬆️ 为嵌套查询，⬇️ 也是，不过优先用上面的
   select distinct sc.sid from sc,(select c.cid from course c,teacher t where c.tid = t.tid and t.tname = 'zou') a where sc.cid = a.cid;

5. select sc1.sid from sc sc1,sc sc2 where sc1.sid = sc2.sid and sc1.cid = 1 and sc2.cid = 2;

6. select x.sid,a.tcnt from sc x,(select count(cid) tcnt from course) a group by x.sid having count(x.cid) < a.tcnt-3;

7. select sname from student where sid not in (select sc.sid from teacher t,course c,sc where sc.cid =c.cid and t.tid = c.tid and t.tname = 'zou');

8. select s.sname,s.sid,avg(sc.score) from student s,sc,(select sid,count(cid) cnt from sc where sc.score < 80 group by sid having count(cid) >= 2) a where s.sid = a.sid and s.sid = sc.sid group by s.sid;

9. select cid, sum(case when score >= 85 then 1 else 0 end) '【100-85】', sum(case when score >= 70 and score < 85 then 1 else 0 end) '【85-70】', sum(case when score >= 60 and score < 70 then 1 else 0 end) '【70-60】', sum(case when score <60 then 1 else 0 end) '【60-0】' from sc group by cid;

10. update sc,(select sc.cid,c.cname,avg(sc.score) ag from sc,course c,teacher t where sc.cid = c.cid and c.tid = t.tid and t.tname = 'zou' group by sc.cid) a set sc.score = a.ag where sc.cid = a.cid;

11. select * from student order by sid desc limit 0,1;

12. select sid,count(sc.cid) ant from sc group by sid having count(cid) = (select count(cid) from sc where sid = 2);
    ——-查询选课数与 id 为 2 的学生数量一致
    select sid,count(cid) bnt from sc where cid in (select cid from sc where sid = 2) group by sid;
    ——-查询选课为 id 为 2 的学生所选课相同的学生
    select a.sid from (select sid,count(sc.cid) ant from sc group by sid having count(cid) = (select count(cid) from sc where sid = 2)) a,(select sid,count(cid) bnt from sc where cid in (select cid from sc where sid = 2) group by sid) b where a.sid = b.sid and a.ant = b.bnt and a.sid != 2;