Mysql数据库查询练习题~~入门级别

一、本文章目标：掌握基本的查询、嵌套子查询及连接查询。

面试开发岗位，无论是什么语言都会涉及到数据库查询的题目，这里收集关于数据库查询的某些例子，建议可以在面试之前稍微看看

资料分享，如果想要手动练习，可以导入下列资料中的数据库，答案也在里面了

点击打开链接

 

二、给出下面表的关系，写出题目要求的Sql语句

题目问题：

1．投影查询：

l 查询Student表中所有记录的SName、Sex和Class列，并且改变查询结果中显示的列名（SName->姓名，Sex->性别，Class->班级）。

2．简单查询：

l 查询SC表中成绩在60到80之间的所有记录。

l 查询Student表中所有姓王的学生记录。

l 查询SC表中成绩为85、86、88或90的记录。

l 查询Student表中“95031”班或性别为“女”的学生记录。

l 查询SC表中，选修了课程但没有参加考试的学生学号。

3．对查询结果排序：

l 查询SC表中的所有记录，并且以Cno升序、Score降序显示。

4．使用集函数：

l 查询统计Student表中“95031”班学生人数。

l 查询统计SC表中，“3-105”号课程的平均分。

5．数据分组：

l 查询SC表中选修人数多于4人，并且课程号以3开头的课程的平均成绩。

l 查询至少有两门课程的成绩在80分以上学生的学号。

6．表的连接查询：

l 查询至少选修了两门以上课程的学生的学号、姓名和所在班级。

l 查询所有学生（包括未选课的学生）的学号、姓名（SName）、该学生所选课程的平均分。提示：用外连接

 

题目答案：

--投影查询
--询Student表中所有记录的SName、Sex和Class列,并且改变查询结果中显示的列名
select sname 姓名,sex 性别,class 班级 from student



--简单查询:
--查询SC表中成绩在60到80之间的所有记录
select sno,CNo ,score from SC where score between 60 and 80

--询Student表中所有姓王的学生记录
select * from student where sname like '王%'

--询SC表中成绩为85、86、88或90的记录
select sno,CNo  ,score from SC where score in(85,86,88,90)

--查询Student表中“95031”班或性别为“女”的学生记录
select * from student where sex='女' or class=95031
--或
select * from student where class=95031
union
select * from student where sex='女'

--查询SC表中，选修了课程但没有参加考试的学生学号
select sno from sc where score is null



--排序查询
--查询SC表中的所有记录，并且以Cno升序、Score降序显示
select sno,CNo ,score from SC order by CNo asc ,score desc


--使用聚集函数
--查询统计Student表中“95031”班学生人数
select count(distinct sno) as 总人数 from student where class=95031

--查询统计SC表中，“3-105”号课程的平均分
select AVG(score) as 平均成绩 from SC where CNo='3-105'


--数据分组
--查询SC表中选修人数多于4人，并且课程号以3开头的课程的平均成绩
select CNo, AVG(score) 平均成绩 from SC where CNo like '3%' 
group by CNo having count(sno)>4

--查询至少有两门课程的成绩在80分以上学生的学号
select sno from sc
	where score>80
        group by sno
	having count(cno)>=2

--表的连接
--l	查询至少选修了两门以上课程的学生的学号、姓名和所在班级
select distinct s.sno,sname,class 
	from (select sc1.sno from sc sc1,sc sc2 where sc1.sno=sc2.sno and sc1.cno<>sc2.cno) as scc,student s
	where s.sno=scc.sno

SELECT sc.SNO,s.SName,s.Class FROM sc sc 
		LEFT JOIN student s
		ON sc.`SNo` = s.SNO
		GROUP BY sc.`SNo`
		HAVING COUNT(sc.CNo) >= 2
	
#子查询
SELECT s.SNO,s.SName,s.Class FROM student s WHERE s.sno IN (
	SELECT sc.`SNo` FROM sc sc GROUP BY sc.`SNo` HAVING COUNT(sc.`SNo`) >=2
)

--查询所有学生（包括未选课的学生）的学号、姓名（SName）、该学生所选课程的平均分
select t.sno,t.sname,平均分 
from student t left  OUTER JOIN 
( select sno,AVG(score) 平均分 from SC group by sno )as scc
on t.sno=scc.sno

--或者

SELECT s.sno, s.sname, AVG(sc.`Score`) AS 平均分 
		FROM student s 
		LEFT JOIN sc sc 
		ON s.SNo = sc.`SNo` 
		GROUP BY s.Sno

7．嵌套查询：（以下部分必须用嵌套查询来完成）

l 查询Student表中与“105”号学生在同一个班级的学生的SNo，SName和BirthDate。

l 查询修选课程名为“高等数学”的学生学号、姓名。

l 查询选修学生人数多于4人的某课程的任课教师姓名。

l 查询成绩低于该门课程的平均分的学生的学号。

l 查询所有任课教师的TName和DepID。

l 查询选修了所有课程的学生的学号，姓名，年龄

l 查询没有选修李诚老师所授课程的学生的学号、姓名、年龄

8．UNION操作：

l 查询所有教师和学生的编号、姓名、性别。

--嵌套查询：
--查询Student表中与“105”号学生在同一个班级的学生的SNo，SName和BirthDate
select sno,sname,birthdate
	from student
	where class in(select class from student where sno='105')
 
--查询修选课程名为“高等数学”的学生学号、姓名。
 
  SELECT * FROM student s WHERE s.SNo IN (
	 SELECT sc.`SNo` FROM sc sc WHERE sc.`CNo` = (
	SELECT c.CNO FROM course c WHERE c.CNAME = '高等数学'
	)
  )
  
 
 
--查询选修学生人数多于4人的某课程的任课教师姓名
select tname from teacher t ,course c,
(select sc.CNo from SC sc group by sc.CNo having count(sc.sno)>=4) sc2 where t.tno=c.tno and sc2.CNo=c.CNo
 
--查询所有任课教师的TName和DepID
select distinct tname,DepID from teacher t where t.tno in(select c.tno from course c where c.cno in(select sc.cno from sc))
 
--查询成绩比该课程平均成绩低的学生成绩表
select sc1.sno ,t.sname,sc1.score,sc3.average from SC sc1,student t,(select sc2.CNo,avg(score) as average from SC sc2 group by sc2.CNo) sc3
 where sc1.CNo=sc3.CNo and sc1.sno=t.sno and sc1.score<sc3.average
 
--查询选修了所有课程的学生的学号，姓名，年龄
SELECT sno,sname,TIMESTAMPDIFF(YEAR,birthdate,NOW()) AS age 
FROM student
WHERE NOT EXISTS
	(SELECT * FROM course
	WHERE NOT EXISTS
		(SELECT * FROM sc
			WHERE student.sno=sc.sno AND course.cno=sc.cno))
 
--查询没有选修李诚老师所授课程的学生的学号、姓名、年龄
select sno,sname,TIMESTAMPDIFF(YEAR,birthdate,NOW()) as age 
from student s
where not exists
	(select * from course c,teacher t,sc where sc.sno=s.sno and c.cno=sc.cno
				and c.tno=t.tno and tname='李诚')
 
--查询结果的并、交、差
--查询所有教师和学生的编号、姓名、性别
select sno,sname,sex  from student
union
select tno,tname,sex from teacher

最后推荐几道常见的面试题~~去了两家公司都遇到了同样的题目

    

关于优秀、及格、不及格 点击打开链接

关于统计胜负