mysql经典问题四表查询(教师,学生,成绩,课程表) | 您所在的位置:网站首页 › 学号姓名表格 › mysql经典问题四表查询(教师,学生,成绩,课程表) |
建立数据库
1.建立一个数据库 create database work; 2.进入数据库work use work; 3.数据库默认编码可能不支持中文,可以在这里设置下 set names gbk; 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)); 7.建立成绩表 create table sc(sid int, cid int, score int); 8.show tables; //可查看建立的四个表格 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);-----------------------数据库建立完成--------------------------------------- 1、查询"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; 4、查询名字中含有"风"字的学生信息 select * from student where sname like ‘%风%’; 5、查询课程名称为"数学",且分数低于60的学生姓名和分数 select s.sname,score from student s,sc where s.sid=sc.sid and cid=2 and score=‘1900-01-01’ and sage70; 19、查询平均成绩大于等于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 score80; 22、求每门课程的学生人数 select cid,count(sid) from sc group by sc.cid; 23、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 select cid,count(sid) from sc group by cid having count(sid)>5 order by count(sid),cid ASC; 24、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 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; 25、检索至少选修两门课程的学生学号 select sid from sc group by sid having count(cid)>=2; 26、查询选修了全部课程的学生信息 select s.* from sc,student s where s.sid=sc.sid group by sid having count (cid)=3; 27、查询各学生的年龄 select s.sname,(TO_DAYS(‘2017-09-07’)-TO_DAYS(s.sage))/365 as age from student s; 28、查询本月过生日的学生 select s.sname from student s where s.sage like ‘_____07%’; 29、查询下月过生日的学生 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; |
CopyRight 2018-2019 实验室设备网 版权所有 |