MySQL 经典四表查询

创建表

创建学生表

CREATE TABLE student
(
    sid VARCHAR(32),
    sname VARCHAR(32), 
    sage INT, 
    ssex VARCHAR(8)
);

创建课程表

CREATE TABLE course
(
    cid VARCHAR(32), 
    cname VARCHAR(32), 
    tid VARCHAR(32)
);

创建学生课程分数表

CREATE TABLE sc
( 
    sid VARCHAR(32), 
    cid VARCHAR(32), 
    score INT
);

创建教师表

CREATE TABLE teacher
( 
    tid VARCHAR(32), 
    tname VARCHAR(16) 
);

初始化表数据

初始化学生表数据

insert into student 
select '1', '刘一', 18, '男'
union all select '2', '钱二', 19, '女' 
union all select '3', '张三', 17, '男'
union all select '4', '李四', 18, '女' 
union all select '5', '王五', 17, '男'
union all select '6', '赵六', 19, '女';

初始化教师表数据

insert into teacher
select 1, '叶平'
union all select 2, '贺高'
union all select 3, '杨艳'
union all select 4, '周磊';

初始化课程表数据

insert into course
select '1', '语文', '1'
union all select '2', '数学', '2'
union all select '3', '英语', '3'
union all select '4', '物理', '4';

初始化学生课程成绩表数据

insert into sc 
select '1', '1', 56 
union all select '1', '2', 78
union all select '1', '3', 67
union all select '1', '4', 58
union all select '2', '1', 79
union all select '2', '2', 81
union all select '2', '3', 92
union all select '2', '4', 68
union all select '3', '1', 91
union all select '3', '2', 47
union all select '3', '3', 88
union all select '3', '4', 56
union all select '4', '2', 88
union all select '4', '3', 90
union all select '4', '4', 93
union all select '5', '1', 46
union all select '5', '3', 78
union all select '5', '4', 53
union all select '6', '1', 35
union all select '6', '2', 68
union all select '6', '4', 71;

SQL 查询问题

查询 “1” 课程比 “2” 课程成绩高的所有学生的学号

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

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

select sid, avg(score) from sc group by sid having avg(score) >  60;

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

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

select s.sid,s.sname,count(sc.cid),sum(sc.score) from student s left outer join sc on s.sid=sc.sid group by s.sid,sname;

查询姓 “李” 的老师的个数

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

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

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

select s.sid, s.sname from student s where s.sid not in (select distinct(sc.sid) from sc, course c, teacher t where sc.cid = c.cid and t.tid = c.tid and t.tname = "叶平");

查询学过 “1” 并且也学过编号 “2” 课程的同学的学号、姓名

select sid, sname from student where sid in (select sid from sc where cid = '2' and sid in (select sid from sc where cid = '1'));

select s.sid, s.sname from student s, sc where s.sid = sc.sid and sc.cid = '1' and exists (select * from sc as sc_2 where sc_2.sid = sc.sid and sc_2.cid = '2');

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

select sid, sname from student
where sid in (select sid from sc, course, teacher where sc.cid = course.cid and teacher.tid = course.tid and teacher.tname = '叶平'
group by sid having count(sc.cid) = (select count(course.cid) from course, teacher where course.tid = teacher.tid and teacher.tname = '叶平'));

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

select distinct(s.sid), s.sname from sc, student s where sc.sid = s.sid and sc.score < 60;

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

select s.sid, s.sname from student s,sc where s.sid = sc.sid group by s.sid, s.sname having count(sc.cid) < (select count(cid) from course);

查询至少有一门课与学号为 “1” 的同学所学相同的同学的学号和姓名

select distinct(s.sid), s.sname from student s, sc where s.sid = sc.sid and sc.cid in (select cid from sc where sc.sid = '1');

查询至少学过学号为 “1” 同学所有一门课的其他同学学号和姓名

select distinct s.sid, s.sname from student s, sc where s.sid = sc.sid and s.sid != '1' and sc.cid in (select cid from sc where sc.sid = '1');

删除学习 “叶平” 老师课的 SC 表记录

delete from sc where cid in (select distinct(cid) from (select sc.cid from sc, course c, teacher t where sc.cid = c.cid and c.tid = t.tid and t.tname = '叶平') a);

向 SC 表中插入一些记录，这些记录要求符合以下条件：1、没有上过编号 “2” 课程的同学学号；2、插入 “2” 号课程的平均成绩

insert into sc (select sid, '2', (select avg(score) from sc where cid = '2') from student where sid not in (select sid from sc where cid = '2'));

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

select sid, sname from student where sid in (select sid from sc where cid in (select cid from sc where sid = '2') group by sid having count(*) = (select count(*) from sc where sid = '2'));

把 “SC” 表中 “叶平” 老师教的课的成绩都更改为此课程的平均成绩

update sc set score = (select a from (select avg(sc_2.score) a from sc sc_2 where sc_2.cid = sc.cid) x) where sc.cid in (select course.cid from course, teacher where course.tid = teacher.tid and teacher.tname = '叶平');

按平均成绩从高到低显示所有学生的 “语文”、”数学”、”英语” 三门的课程成绩，按如下形式显示：学生ID，语文，数学，英语，有效课程数，有效平均分

select sid as 学生ID,(select score from sc where sc.sid = t.sid and cid = '1') as 语文,(select score from sc where sc.sid = t.sid and cid = '2') as 数学,(select score from sc where sc.sid = t.sid and cid = '3') as 英语,count(*) as 有效课程数, avg(t.score) as 有效平均成绩 from sc as tgroup by sid order by avg(t.score);

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

select cid as 课程ID, max(score) as 最高分, min(score) as 最低分 from sc group by cid;