mysql经典四表查询_mysql经典问题四表查询(教师，学生，成绩，课程表)

创建数据库

1.创建一个数据库

create database work;web

2.进入数据库work

use work;数据库

3.数据库默认编码可能不支持中文,能够在这里设置下

set names gbk;svg

4.创建student表

属性有:编号：id (主键，自动增加),姓名:sname,出生年月:sage,性别:ssex(枚举)

create table student(sid int primary key auto_increment,

sname varchar(20),

sage date,

ssex enum(‘男’,‘女’));学习

5.第二个课程表中使用了外键教师标号，于是须要先创建教师表

create table teacher(tid int primary key auto_increment,

tname varchar(20));编码

6.创建课程表

create table course(cid int primary key auto_increment,

cname varchar(20),

tid int,

foreign key(tid) references teacher(tid));code

7.创建成绩表

create table sc(sid int,

cid int,

score int);xml

8.show tables; //可查看创建的四个表格blog

9.插入数据，由于里面有主键连接，表格插入数据也要有顺序(注意题目图片上都是字节引号，应该为int，不要单引号)图片

a，先给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','女');

b, 给teacher表插入数据,这里不能够先给course表插入数据,由于course表外连接到teacher的主键

insert into teacher values(1,'张三'),

(2,'李四'),

(3,'王五');

c, 给course表插入数据

insert into course values(1,'语文',2),

(2,'语文',1),

(3,'语文',3);

d, 最后给sc表插入数据(题目图片少了第1个学生成绩，在这加上 1,1,90; 1,2,80; 1,3,90)

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);

-----------------------数据库创建完成---------------------------------------ci

一、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select s.sid,s.sname,s.sage,s.ssex,sc1.score,sc2.score from student s,sc sc1,sc sc2 where sc1.cid=1 and sc2.cid=2 and sc1.score>sc2.score and sc1.sid=sc2.sid and s.sid=sc1.sid;

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

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

四、查询名字中含有"风"字的学生信息

select * from student where sname like ‘%风%’;

五、查询课程名称为"数学"，且分数低于60的学生姓名和分数

select s.sname,score from student s,sc where s.sid=sc.sid and cid=2 and score<60;

六、查询全部学生的课程及分数状况；

select cname,score from sc,course where sc.cid=course.cid;

七、查询没学过"张三"老师授课的同窗的信息

select s.* from student s where s.sid not in(select sc1.sid from sc sc1,course c,teacher t where t.tid=c.tid and sc1.cid=c.cid and t.tname=‘张三’);

8.查询学过"张三"老师授课的同窗的信息

select s.* from student s ,sc sc1,course c,teacher t where s.sid=sc1.sid and sc1.cid=c.cid and c.tid=t.tid and t.tname=‘张三’;

九、查询学过编号为"01"而且也学过编号为"02"的课程的同窗的信息

student(sid) sc(sid cid tid) sc2(sid cid tid) course(cid tid cname)

select s.* from student s,sc sc1,sc sc2 where s.sid=sc1.sid and sc1.sid=sc2.sid and sc1.cid=1 and sc2.cid=2;

十、查询学过编号为"01"可是没有学过编号为"02"的课程的同窗的信息

select distinct s.* from student s,sc sc1,sc sc2,sc sc3 where s.sid=sc1.sid and sc1.sid=sc2.sid and sc1.cid=1 and sc2.cid!=2;

十一、查询没有学全全部课程的同窗的信息

select s.* from student s where s.sid not in(select sc1.sid from 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.sid) group by s.sid;

十二、查询至少有一门课与学号为"01"的同窗所学相同的同窗的信息

select distinct s.* from student s,sc sc1 where s.sid=sc1.sid and sc1.cid in(select cid from sc where sid=1) and s.sid<> 1;

1三、查询和"01"号的同窗学习的课程彻底相同的其余同窗的信息

select s.* from student s where s.sid in(select distinct sc.sid from sc where sid<>1 and sc.cid in(select distinct cid from sc where sid=1)group by sc.sid having count(1)=(select count(1) from sc where s.sid=1));

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

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=‘张三’);

1五、查询出只有两门课程的所有学生的学号和姓名

select s.* from student s,sc group by sc.sid having count(sc.sid)=2 and s.sid=sc.sid;

1六、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

select * from student where sage>=‘1900-01-01’ and sage<=‘1900-12-31’;

select s.* from student s where s.sage like ‘1900-%’;(方法2)

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

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

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

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;

1九、查询平均成绩大于等于85的全部学生的学号、姓名和平均成绩

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

20、查询不及格的课程

select s.sname,c.cname,score from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and score<60;

2一、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

select s.sid,s.sname from student s,sc where sc.sid=s.sid and sc.cid=1 and score>80;

2二、求每门课程的学生人数

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

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

select cid,count(sid) from sc group by cid having count(sid)>5 order by count(sid),cid ASC;

2四、查询不一样课程成绩相同的学生的学生编号、课程编号、学生成绩

select s1.sid,s2.sid,sc1.cid,sc1.score,sc2.score from student s1,student s2,sc sc1,sc sc2 where s1.sid!=s2.sid and s1.sid=sc1.sid and s2.sid=sc2.sid and sc1.cid!=sc2.cid and sc1.score=sc2.score;

2五、检索至少选修两门课程的学生学号

select sid from sc group by sid having count(cid)>=2;

2六、查询选修了所有课程的学生信息

select s.* from sc,student s where s.sid=sc.sid group by sid having count

(cid)=3;

2七、查询各学生的年龄

select s.sname,(TO_DAYS(‘2017-09-07’)-TO_DAYS(s.sage))/365 as age from student s;

2八、查询本月过生日的学生

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

2九、查询下月过生日的学生

select s.sname from student s where s.sage like ‘_____08%’;

30、查询学全全部课程的同窗的信息

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;