mysql经典四表查询_sql 经典四表查询

题 目 ：

student(sid, sname, sage, ssex) -- 学生信息表(学生编号 自增，学生姓名， 学生出生年月， 性别)；

teacher(tid, tname) -- 教师信息表(教师编号 自增， 教师姓名)

course(cid, cname, tid) -- 课程表(课程编号 自增， 课程名称， 教师编号 外键)

sc(sid, cid, score) --  分数表(学生编号，课程编号，分数)

create database work;

use work;

set names work;

# 创建表

create table student(

sid int primary key auto_increment,

sname varchar(20),

sage date,

ssex varchar(10)

)

create table teacher(

tid int primary key auto_increment,

tname varchar(20)

);

create table course(

cid int primary key auto_increment,

cname varchar(20),

tid int ,

foreign key(tid) references teacher(tid)

)

create table sc(

sid int,

cid int,

score int

)

#  插入数据

# 先给student表插入数据

insert into student values

(1,'赵雷','1990-01-01','男'),

(2,'钱电','1990-12-21','男'),

(3,'孙风','1990-05-20','男'),

(4,'李云','1990-08-06','男'),

(5,'周梅','1991-12-01','女'),

(6,'吴兰','1992-03-01','女'),

(7,'郑竹','1989-07-01','女'),

(8,'王菊','1990-01-20','女');

# 教师表

insert into teacher values

(1,'张三'),

(2,'李四'),

(3,'王五');

# 课程表

insert into course values

(1,'语文',2),

(2,'语文',1),

(3,'语文',3);

# 分数表

insert into sc values

(1,1,90),

(1,2,80),

(1,3,90),

(2,1,70),

(2,2,60),

(2,3,80),

(3,1,80),

(3,2,80),

(3,3,80),

(4,1,50),

(4,2,30),

(4,3,20),

(5,1,76),

(5,2,87),

(6,1,31),

(6,3,34),

(7,2,89),

(7,3,98);

问 题：

-- 1、查询”01”课程比”02”课程成绩高的学生的信息及课程分数

select

st.sid,

st.sname,

st.sage,

st.ssex,

sc1.score as 课程1分数,

sc2.score as 课程1分数

from sc sc1,

sc sc2,

student st

where sc1.sid = sc2.sid

and sc1.sid = st.sid

and sc1.cid = 1

and sc2.cid = 2

and sc1.score > sc2.score

-- 2、查询学生表前5名信息；

select * from student st limit 5;

-- 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select

s.sname,

sc.sid,

avg(sc.score)

from student s,

sc

where s.sid = sc.sid

group by sc.sid

having avg(sc.score) >= 60

-- 4、查询名字中含有”风”字的学生信息

select * from student st where st.sname like '%风';

-- 5、查询课程名称为”数学”,且分数低于60的学生姓名和分数

select

st.sname,

sc.score

from sc,

student st

where st.sid = sc.sid

and sc.score < 60

and sc.cid = (select

co.cid

from course co

where co.cname = '数学')

-- 6、查询所有学生的课程及分数情况;

select

st.sname,

co.cname,

sc.score

from student st,

sc,

course co

where st.sid = sc.sid

and co.cid = sc.cid;

-- 7、查询没学过”张三”老师授课的同学的信息

select *

from student st

where st.sid not in(select st1.sid

from student st1,

sc,

course co,

teacher te

where st1.sid = sc.sid

and co.cid = sc.cid

and co.tid = te.tid

and te.tname = '张三') ;

-- 8、查询学过”张三”老师授课的同学的信息

select

st1.*

from student st1,

sc,

course co,

teacher te

where st1.sid = sc.sid

and co.cid = sc.cid

and co.tid = te.tid

and te.tname = '张三'

-- 9、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息

select

st.sid,

st.sname,

st.sage,

st.ssex

from sc sc1,

sc sc2,

student st

where sc1.sid = sc2.sid

and sc1.sid = st.sid

and sc1.cid = 1

and sc2.cid = 2

-- 10、查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息

select

st.sid,

st.sname,

st.sage,

st.ssex

from student st,

sc

where st.sid = sc.sid

and sc.cid = 1

and st.sid not in(select distinct

sc1.sid

from sc sc1,

sc sc2

where sc1.cid = 2)

-- 11、查询没有学全所有课程的同学的信息

select * from student st

where st.sid not in

(select

distinct st.sid

from sc sc1,

sc sc2,

sc sc3,

student st

where sc1.sid = sc2.sid

and sc1.sid = sc3.sid

and sc1.sid = st.sid

and sc1.cid = 1

and sc2.cid = 2

and sc3.cid = 3)

-- 12、查询至少有一门课与学号为”01”的同学所学相同的同学的信息

select distinct

st.*

from student st,

sc

where st.sid = sc.sid

and sc.cid in(select

sc.cid

from sc

where sc.sid = 1)

and st.sid != 1

-- 13、查询和”01”号的同学学习的课程完全相同的其他同学的信息

select st.sid, st.sname, st.sage, st.ssex

from student st,

sc

where st.sid = sc.sid

and st.sid != 1

and sc.cid in(select

sc.cid

from sc

where sc.sid = 1)

group by st.sid

having count( * ) = (select

count(*)

from sc

where sc.sid = 1)

-- 14、查询没学过”张三”老师讲授的任一门课程的学生姓名

#方法1：

select *

from student st

where st.sid not in(select

st.sid

from student st,

sc

where sc.sid = st.sid

and sc.cid = (select

co.cid

from course co

where co.tid = (select

te.tid

from teacher te

where te.tname = '张三')))

#方法2：

select

s.*

from student s

where s.sid not in(select

sc1.sid

from sc sc1,

course c,

teacher t

where sc1.cid = c.cid

and c.tid = t.tid

and t.tname = '张三');

-- 15、查询出只有两门课程的全部学生的学号和姓名

select

st.sid,

st.sname,

st.sage,

st.ssex

from student st,

sc

where st.sid = sc.sid

group by sc.sid

having count( * ) = 2

-- 16、查询1990年出生的学生名单(注:Student表中Sage列的类型是datetime)

select * from student st where st.sage >= '1990-01-01' and st.sage <= '1990-12-31';

select s.* from student s where s.sage like '1990-%';

select * from student st where st.sage between '1990-01-01' and '1990-12-31';

-- 17、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列

select sc.cid, avg(sc.score) from sc group by sc.cid order by avg(sc.score) desc, sc.cid

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

#方法1：

select

s.sname,

c.cname,

score

from student s,

sc,

course c

where s.sid = sc.sid

and sc.cid = c.cid

and score > 70;

#方法2：

select st.sname, co.cname, sc.score

from student st,

course co,

sc

where st.sid = sc.sid

and sc.cid = co.cid

and st.sid in(select

sc.sid

from sc

group by sc.sid

having min(sc.score) >= 70)

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

select

st.sid,

st.sname,

avg(sc.score) avgscore

from student st,

sc

where st.sid = sc.sid

group by sc.sid

having avg(sc.score) >= 85

-- 20、查询不及格的课程

select

st.sname,

co.cname,

sc.score

from student st,

course co,

sc

where st.sid = sc.sid

and sc.cid = co.tid

and sc.score < 60

-- 21、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名;

select

st.sid,

st.sname

from student st,

sc

where st.sid = sc.sid

and sc.cid = 1

and sc.score < 60

-- 22、求每门课程的学生人数

select sc.cid, count(*) from sc group by sc.cid

select cid,count(sid) from sc group by sc.cid;

-- 23、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

select

sc.cid,

count(*) sumstudent

from sc

group by sc.cid

having count( * ) > 5

order by sumstudent desc, sc.cid

-- 24、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select

sc1.sid, sc1.cid,sc2.sid, sc2.cid,sc1.score

from sc sc1,

sc sc2

where sc1.sid != sc2.sid

and sc1.cid != sc2.cid

and sc1.score = sc2.score

-- 25、检索至少选修两门课程的学生学号

select

sc.sid

from sc

group by sc.sid

having count( * ) >= 2

-- 26、查询选修了全部课程的学生信息

select

st.*

from sc,

student st

where st.sid = sc.sid

group by sid

having count(sc.cid) = 3;

-- 27、查询各学生的年龄

select s.sname,(to_days('2018-06-23')-to_days(s.sage))/365 as age from student s;

-- 28、查询本月过生日的学生

select s.sname from student s where s.sage like '_____07%';

-- 39、查询学全所有课程的同学的信息

select

s.*

from student s,

sc sc1,

sc sc2,

sc sc3

where sc1.cid = 1

and sc2.cid = 2

and sc3.cid = 3

and sc1.sid = sc2.sid

and sc1.sid = sc3.cid

and s.sid = sc1.sid

group by s.sid;

-- 30、查询课程2 第2名到第5名的分数，降序排列

select * from course co, sc where co.cid = sc.cid and co.cid=2 order by sc.score desc limit 1,4