Mysql查询综合练习题

前言：
注意事项：如果你是从网上直接复制别人的代码，在确定语法无问题后，sql语句仍然报错，可能有以下几种原因：
1.数据库版本
2.复制来的代码，有些空格的编码是其他方式，这个时候就要删除空格，重新输入空格===========（特别注意）
建议：数据库练习时，可以考虑多种方式进行查询，而不是说自己按照题目要求查出来就完事了，学习的过程不仅是学到基础知识，更难能可贵的是能锻炼思维。查询得到期望数据只是基础，而我们更应该考虑的是SQL语句的性能、SQL语句的优化。

1.表的建立

#建表
CREATE TABLE `course` (
  `cno` varchar(10) NOT NULL,
  `cname` varchar(20) DEFAULT NULL,
  `tno` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`cno`),
  KEY `tno` (`tno`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `sno` varchar(10) NOT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `class` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `teacher` (
  `tno` varchar(10) NOT NULL,
  `tname` varchar(20) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `level` varchar(20) DEFAULT NULL,
  `major` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sc` (
  `sno` varchar(10) NOT NULL DEFAULT '',
  `cno` varchar(10) NOT NULL DEFAULT '',
  `grade` int(11) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  KEY `cno` (`cno`),
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`),
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.数据插入

insert into student values('108','曾华','男',19,'95033');
insert into student values('105','匡明','男',20,'95031');
insert into student values('107','王丽','女',20,'95033');
insert into student values('101','李军','男',19,'95033');
insert into student values('109','王芳','女',22,'95031');
insert into student values('103','陆君','男',20,'95031');

insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');

insert into teacher values('804','李成','男',42,'副教授','计算机系');
insert into teacher values('856','张旭','男',35,'讲师','电子工程');
insert into teacher values('825','王萍','女',28,'助教','计算机系');
insert into teacher values('831','刘冰','女',25,'助教','电子工程');

insert into sc values('103','3-245',86);
insert into sc values('105','3-245',75);
insert into sc values('109','3-245',68);
insert into sc values('103','3-105',92);
insert into sc values('105','3-105',88);
insert into sc values('109','3-105',76);
insert into sc values('101','3-105',64);
insert into sc values('107','3-105',91);
insert into sc values('108','3-105',78);
insert into sc values('101','6-166',85);
insert into sc values('107','6-166',79);
insert into sc values('108','6-166',81);

3.查询练习题（连接、嵌套、分组、子查询的使用）

1.查询选修课程’3-105’且成绩在60到80之间的所有记录。

#注释:用于指定某个范围使用between and,也可以使用and连接符;
select * from sc where cno='3-105' and grade between 60 and 80;

2.查询成绩为85、86或88的记录。

#注释:用于制定某个集合使用 in 关键字,也可以使用 or 连接符;
select * from sc where grade in (85,86,88);
select * from sc where grade=85 or grade =86 or grade=88;

3.查询’95031’班的学生人数。

#注释:count(*)用于计算结果总数;
select count(*) 人数 from student where class='95031';

4.查询最低分大于70,且最高分小于90的学号列。

#注释:having后面跟聚合函数:avg,min,max,count;having语句只能跟#在:group by语句后面;
#我的代码
select sno from sc group by sno having max(grade)<90 and min(grade)>70
#给的答案
select sno,min(grade)as 最低分,max(grade)as 最高分 from sc group by sno having min(grade)>70 and max(grade) < 90;

5.查询至少有5名学生选修并以3开头的课程的平均成绩。

#注释:group by 语句置于where语句后面表示根据什么条件来分组;
#我的方式：
#步骤：先查以3开头的
select substring(cno,1,1) from sc;
#
select cno 课程号,avg(grade) 平均成绩 from sc where substring(cno,1,1)=3 group by cno  having count(grade)>=5
#给的答案：
select cno,avg(grade) as 平均成绩 from sc where cno like '3%' group by cno having count(cno) >= 5;

6.查询平均分大于80分的学生的成绩表

#注释:having后面跟聚合函数avg();
#查平均分
#我的答案：
select avg(grade) as a from sc group by sno having avg(grade)>80;
#给的答案：=====这个答案感觉有些问题？？每个学生怎么只有一个成绩？？
select * from sc group by sno having avg(grade)>80;

7.查询’95033’班每个学生所选课程的平均分。

#注释:此题是根据 #'95033'班学生的学号进行的分组,使用where语句限制group #by语句的分组条件;
#我的答案：
select student.sno,avg(sc.grade) 平均分 from student inner join sc on student.sno=sc.sno where student.class='95033'
group by student.sno;

#给的答案：
select sno,avg(grade) as 平均成绩 from sc where sno in (select sno from student where class = '95033') group by sno;

8.以选修 #'3-105’为例,查询成绩高于’109’号同学的所有同学的记录。

#注释:此题使用典型的嵌套查询,层层深入;
#我的答案：
select * from student where sno in(select sno from sc where grade >(select grade from sc where sno=109 and cno='3-105') and cno='3-105');
#给的答案：
select * from student where sno in (select sno from sc where cno='3-105' and grade >
(select grade from sc where sno='109' and cno='3-105'));

9.查询与学号为’108’的同学同岁的所有学生的学号、姓名和年龄。

#注释:当查询的结果集返回只有一个时关键字in的作用等价于'='的作#用,但是注意'='只能用与返回结果集只有一个,而 in #可以有多个结果;
#1先查出108同学的年龄
select age from student where sno=108
#2我的答案：
select sno 学号,sname 姓名,age 年龄 from student where age =(select age from student where sno=108)

#给的答案：
#法1:
select sno 学号,sname 姓名,age 年龄 from student where age = (select age 年龄 from student where sno='108');

#法2:
select sno as 学号,sname as 姓名,age as 年龄 from student where age in(select age as 年龄 from student where sno ='108');

10.查询’张旭’教师任课的课程号，选修其课程学生的学号和成绩。

#注释:此题使用了表的连接:inner #join,将多个表连接在一起组成一个新的表,标的连接的条件是必须存#在相同的列;
#查询张老师的课程号
#我的答案
select course.cno 课程号,sc.sno 学号,sc.grade 成绩 from sc inner join course on sc.cno=course.cno inner join teacher on course.tno=teacher.tno where teacher.tname='张旭';

#给的答案：
#法1:====给出的这个答案似乎有些不符合题意,这样查出来是两张表了===
select cno 课程号 from course where tno=(select tno from teacher where tname='张旭'); select sno,grade from sc where cno in(select cno from course where tno in 
(select tno from teacher where tname='张旭'));
#法2:
select teacher.tname as 教师姓名,course.cno,student.sname as 学生姓名,student.sno,grade from teacher inner join (course inner join (sc inner join student on student.sno=sc.sno) on course.cno=sc.cno) on course.tno=teacher.tno where teacher.tname='张旭';

11.查询选修其课程的学生人数多于5人的教师姓名。

#注意外:此题采用嵌套查询和连接表两种方法,表现出了同样的效果;
select teacher.tname 教师姓名 from sc inner join course on sc.cno=course.cno inner join teacher on course.tno=teacher.tno group by sc.cno having count(*) >=5
#给的答案
#法1:
select tname as 教师姓名 from teacher where tno in (select tno from course where cno in (select cno from sc group by cno having count(*) > 5));
#法2:
select 姓名 as 教师姓名 from teacher inner join (course inner join sc on course.课程号=sc.课程号) on teacher.教师号=course.教师号 group by sc.课程号 having count(*) > 5;

12.查询’计算机系’与’电子工程系’不同职称的教师的姓名和职称。

#注释:不同职称意味着分组后的返回结果集只有一个;
#我的答案：不推荐
select tname,level from teacher where level !=( select DISTINCT a.level from teacher as a inner join teacher as b on a.level = b.level and a.tname!=b.tname)
#给的答案：======有些不能理解
select tname as 教师姓名,level as 职称 from teacher group by level having count(*) = 1;

13.查询选修编号为’3-105’课程且成绩至少高于选修编号为’3-245’

#课程的同学的课程号、学号 、成绩并按成绩从高到低次序排列。
#注释:any 表示任意一个;
#1查询选修编号为'3-245'的最大值
select max(grade) from sc where cno = '3-245';
#我的答案：和给定答案一样，此处省略
select cno,sno,grade from sc where cno='3-105' and grade>(select max(grade) from sc where cno = '3-245') order by grade desc;

14.查询选修编号为’3-105’课程且成绩高于选修编号为’3-245’课程

#的同学的课程号、学号 、成绩。
#注释:all 表示所有;
#我的答案：
select cno,sno,grade from sc where cno='3-105' and grade>(select max(grade) from sc where cno = '3-245')
#给的答案：
select * from sc where cno='3-105' and grade > all (select grade from sc where cno='3-245');

15.列出所有教师和同学的姓名 、性别 、年龄。

#注释:此题将所有表连接在一起:
#我的答案
select tname 姓名,sex 性别,age 年龄 from teacher
union
select sname,sex,age from student;
#给的答案
select student.sname as 学生姓名,student.sex as 学生性别,student.age as 学生年龄,
teacher.tname as 教师姓名,teacher.sex as 教师性别,teacher.age as 教师年龄 from student inner join (sc inner join (course inner join teacher on course.tno=teacher.tno) on sc.cno=course.cno) on student.sno=sc.sno;

16.查询成绩比’3-105’课程的平均成绩低的学生的成绩表。

#1平均成绩
select * from sc where grade<( select avg(grade) from sc where sc.cno='3-105');

17.查询成绩比该课程平均成绩低的学生的成绩表。

#注释:此题是所有题中最难的,下面的方法很经典,请认真琢磨,先按照#课程进行分组,查出小于各门课程平均成绩的所有成绩;
#1查询平均成绩
#我的答案：
select * from sc as a left join (select cno,avg(grade) num from sc group by cno) as b on a.cno=b.cno having a.grade<num order by a.cno asc;
#给的答案：
select * from sc where grade < any (select avg(grade) from sc group by cno) order by cno asc;

18.列出所有任课教师的姓名和专业。

#注释:姓名和专业涉及两个表course表和teacher表,只需连接这两个
#表就能解决此题了;
#我的答案，我这里答案与给出答案结果不同，因为我对题意的理解是不管有没有考试，只要课程里有，都是算任课老师的
select course.cname,teacher.tname,teacher.major from teacher LEFT JOIN course on teacher.tno=course.tno;
#给的答案，这里是以由考试成绩的才算任课
select teacher.tname as 教师姓名,teacher.major from teacher where teacher.tno in (select tno from course where cno in (select cno from sc group by cno));

19.列出所有未讲课教师的姓名和专业。

#注释:
#我的答案：
select teacher.tname,teacher.major from teacher left join course on teacher.tno=course.tno left join sc on sc.cno=course.cno where grade is null;

#给的答案：
select teacher.tname as 教师姓名,major from teacher where tno not in 
(select tno from course where cno in (select cno from sc group by cno));

20.列出至少有2名男生的班号。

#注释:根据性别='男'这个条件来对student表进行分组就可以了;
select class 班级 from student where sex='男' group by class having count(*)>=2;

21.查询不姓’王’的学生记录。

#我的答案：
select * from student where sname not like '王%';
给的答案：
select * from student where sname not in (select sname from student where sname like '王%');

22.查询每门课最高分的学生的学号、课程号、成绩。

#注释:此题的技巧在于根据最高分来查成绩表;
#我的答案：这里需要将* 改为题目要求的字段即可
select * from sc as a left join (select cno,max(grade) as num from sc group by cno) as b on a.cno=b.cno where a.grade=b.num;

#给的答案：======
select * from sc where grade in (select max(grade) from sc group by cno);

23.查询与’李军’同性别并同班的同学名字。

#注释:嵌套查询;
#我的答案
select student.sname from student LEFT JOIN (select sex a,class b from student where sname='李军') as student2 on student.class=student2.b where student.sex=student2.a and student.class=student2.b and student.sname!='李军';

#给的答案：
select sname from student where sex=(select sex from student where sname='李军') and 
class=(select class from student where sname='李军');

24.查询’男’教师及其所上的课程。

#注释:先分析此题涉及的表;
select teacher.tname 教师,course.cname 课程 from teacher inner join course on teacher.tno=course.tno and teacher.sex='男';

25.查询选修’计算机导论’课程的’男’同学的成绩表。

#注释:此题用嵌套查询和表连接两种方法进行解答;
#我的答案：
select sc.* from sc inner join student on sc.sno=student.sno inner join course on sc.cno=course.cno where course.cname='计算机导论' and student.sex='男';

#给的答案：
#法1:
select * from sc where cno=(select cno from course where cname='计算机导论') and sno in (select sno from student where sex='男');
#法2
select sc.sno,student.sname as sname,student.sex as 学生性别,sc.cno,sc.grade,course.cname from student inner join (sc inner join course on sc.cno=course.cno) 
on student.sno=sc.sno where sex='男' and cname='计算机导论';