知己知彼,百战不殆~本篇文章我们总结一下面试中常见的题目,针对性练习一下:
练习1:简单查询
1)查询姓“猴”的学生名单
![1a455edcc0cc2bd9c171b3793d4b7d39.png](https://img-blog.csdnimg.cn/img_convert/1a455edcc0cc2bd9c171b3793d4b7d39.png)
2) 查询姓名中最后一个字是猴的学生名单
![e73675a8d9c80d3c6d25d1b6823f183b.png](https://img-blog.csdnimg.cn/img_convert/e73675a8d9c80d3c6d25d1b6823f183b.png)
3)查询姓名中带“猴”的学生名单
![bbe66e437b56ad91faaf301477467a14.png](https://img-blog.csdnimg.cn/img_convert/bbe66e437b56ad91faaf301477467a14.png)
4) 查询姓“孟”老师的个数
![58379b866372ce00f26787ec33be95ea.png](https://img-blog.csdnimg.cn/img_convert/58379b866372ce00f26787ec33be95ea.png)
练习2 汇总分析
1)查询课程编号为“0002”的总成绩
![b2b14b03768e6ad32bec5562a408c370.png](https://img-blog.csdnimg.cn/img_convert/b2b14b03768e6ad32bec5562a408c370.png)
2)查询选了课程的学生人数
![ef11db4b32d8551b867d30995a281fe4.png](https://img-blog.csdnimg.cn/img_convert/ef11db4b32d8551b867d30995a281fe4.png)
3)查询各科成绩最高和最低的分
![9360b685c88298d0d1227654ebe42339.png](https://img-blog.csdnimg.cn/img_convert/9360b685c88298d0d1227654ebe42339.png)
4)查询每门课程被选修的学生数
![1b9ec30d1cb9af73d28150fbabc30861.png](https://img-blog.csdnimg.cn/img_convert/1b9ec30d1cb9af73d28150fbabc30861.png)
5)查询男生、女生人数
![74767b78d63ecb2e2241e15c0309f82a.png](https://img-blog.csdnimg.cn/img_convert/74767b78d63ecb2e2241e15c0309f82a.png)
分组结果的条件
6)查询平均成绩大于60分学生的学号和平均成绩
![645d7eca7ab74d96af3703e7d240c400.png](https://img-blog.csdnimg.cn/img_convert/645d7eca7ab74d96af3703e7d240c400.png)
7)查询至少选修两门课程的学生学号
![90b3775c323dbcfa28eb838638dfae65.png](https://img-blog.csdnimg.cn/img_convert/90b3775c323dbcfa28eb838638dfae65.png)
8)查询同名同姓学生名单并统计同名同姓人数
![2a419d4ac2ed0cefeccbaa860d898f21.png](https://img-blog.csdnimg.cn/img_convert/2a419d4ac2ed0cefeccbaa860d898f21.png)
9)查询不及格的课程并按课程号从大到小排列
![e389ce83f3d06a2b0d6c1b3d3426fbb7.png](https://img-blog.csdnimg.cn/img_convert/e389ce83f3d06a2b0d6c1b3d3426fbb7.png)
查询及格的课程并按课程号从大到小排列
![9257bc2f596440bdb6e0f5ed9e66a7d9.png](https://img-blog.csdnimg.cn/img_convert/9257bc2f596440bdb6e0f5ed9e66a7d9.png)
10)查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
![4aa74d76203a405bc4527e68382b8d2b.png](https://img-blog.csdnimg.cn/img_convert/4aa74d76203a405bc4527e68382b8d2b.png)
11) 查询课程编号为“0004”且分数小于60的学生学号,结果按分数降序排列
![7b0ee12f905b349b75de5232171c4557.png](https://img-blog.csdnimg.cn/img_convert/7b0ee12f905b349b75de5232171c4557.png)
查询课程编号为“0003”且分数小于90的学生学号,结果按分数降序排列
![4acda98f14fd939947d30818bc8a9948.png](https://img-blog.csdnimg.cn/img_convert/4acda98f14fd939947d30818bc8a9948.png)
12) 统计每门课程的学生选修人数(超过2人的课程才统计)要求输出课程号和选课人数,查询结果按人数降序排列,若人数相同,按课程号升序排序
![e5e0ce25995c9495e1c89ceea5a970a4.png](https://img-blog.csdnimg.cn/img_convert/e5e0ce25995c9495e1c89ceea5a970a4.png)
13) 查询两门以上不及格课程的学生学号,以及不及格课程的平均成绩
![c1b1d7eb561c3d843e2c83b9964f3bce.png](https://img-blog.csdnimg.cn/img_convert/c1b1d7eb561c3d843e2c83b9964f3bce.png)
查询两门以上成绩大于等于80的课程的学生学号,及其平均成绩
![371bcc3df1f4b1529d1233f3b63249a7.png](https://img-blog.csdnimg.cn/img_convert/371bcc3df1f4b1529d1233f3b63249a7.png)
练习3 复杂查询
1)查询所有课程成绩小于60分学生的学号、姓名
![57198653301d05799f34f67940a278f4.png](https://img-blog.csdnimg.cn/img_convert/57198653301d05799f34f67940a278f4.png)
查询所有课程成绩小于等于80分学生的学号、姓名
![c04a645d577b84fc56669d1c3a6820d9.png](https://img-blog.csdnimg.cn/img_convert/c04a645d577b84fc56669d1c3a6820d9.png)
2)查询没有学全所有课程的学生的学号、姓名
这里需要用到三张表course和student,score
![6c0aa6dfd8fd2b06e03a70c2fc3b4895.png](https://img-blog.csdnimg.cn/img_convert/6c0aa6dfd8fd2b06e03a70c2fc3b4895.png)
3)查询出只选修了两门课程的全部学生的学号和姓名
![be9a75c6771ec4e7ea2ea1ab368af496.png](https://img-blog.csdnimg.cn/img_convert/be9a75c6771ec4e7ea2ea1ab368af496.png)
4)1990年出生的学生名单
这里需要用到日期函数:
![c8fed27f831375ec103a2b590c7fa11b.png](https://img-blog.csdnimg.cn/img_convert/c8fed27f831375ec103a2b590c7fa11b.png)
试着写一下:
![e930405a1e4349e115d50dd75c5265c8.png](https://img-blog.csdnimg.cn/img_convert/e930405a1e4349e115d50dd75c5265c8.png)
5)topN问题(分组取每组最大值、最小值,每组的topN):
- 按课程号分组取成绩最大值所在行的数据
![a6bcbb05a872f6e8eaabbc2bf08abb7e.png](https://img-blog.csdnimg.cn/img_convert/a6bcbb05a872f6e8eaabbc2bf08abb7e.png)
- 按课程号分组取成绩最小值所在行的数据
![6b3fb246a9976612efc2e1e577fb08b9.png](https://img-blog.csdnimg.cn/img_convert/6b3fb246a9976612efc2e1e577fb08b9.png)
- 查询各科成绩前两名的记录
![5102a3250ddf179c0f2a29fecdf5d93b.png](https://img-blog.csdnimg.cn/img_convert/5102a3250ddf179c0f2a29fecdf5d93b.png)
练习4 多表查询
1)不是近视眼的学生都有谁?
“学生表“和”近视学生表“分别如下:
![154d590b24501e87d06c3178b349950c.png](https://img-blog.csdnimg.cn/img_convert/154d590b24501e87d06c3178b349950c.png)
![8d12dfedf4535f3edad26718aa4185f8.png](https://img-blog.csdnimg.cn/img_convert/8d12dfedf4535f3edad26718aa4185f8.png)
sql语句及运行结果如下:
![edd47aab4ac26d8ebffdfac568bdbd84.png](https://img-blog.csdnimg.cn/img_convert/edd47aab4ac26d8ebffdfac568bdbd84.png)
2)对上一题举一反三,查询从不订购的顾客信息
sql语句如下
![9ba6b383473a39dc9909e753cfcd91a6.png](https://img-blog.csdnimg.cn/img_convert/9ba6b383473a39dc9909e753cfcd91a6.png)
![ba71eac6466534babf7c313f09de550e.png](https://img-blog.csdnimg.cn/img_convert/ba71eac6466534babf7c313f09de550e.png)
运行sql语句:
![d9dfce7c8f9e571ad256a85d1029d555.png](https://img-blog.csdnimg.cn/img_convert/d9dfce7c8f9e571ad256a85d1029d555.png)
3)查询所有学生的学号、姓名、选课数、总成绩
4) 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
5)查询学生的选课情况:学号、姓名、课程号、课程名称
6) 查询出每门课程的及格人数和不及格人数
7) 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计:各分数段人数,课程号和课程名称
1)--7)练习答案可参考前一篇文章
有伊人兮天各一方:SQL--多表查询zhuanlan.zhihu.com8) 查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
![dbdd94d1c5d5d4f322a4e9573088c024.png](https://img-blog.csdnimg.cn/img_convert/dbdd94d1c5d5d4f322a4e9573088c024.png)
9) 如何实现表的行列互换
![b2ab636c41878c0e110ba3dc675b3789.png](https://img-blog.csdnimg.cn/img_convert/b2ab636c41878c0e110ba3dc675b3789.png)
TIPS : 如何提高sql查询效率
数据量大时,不同的sql语句消耗的时间相差很大,写SQL语句时需要注意以下几点以提高查询效率:
- select子句中尽量避免使用*。因为*是返回全部数据的意思,当数据量大时非常消耗时间
- where子句比较符号的左侧避免使用函数。因为这会导致数据库引擎进行全表扫描,从而增加运行时间。为了提高效率,where子句中遇到函数或者运算,将其移到比较符号的右侧。
- 避免使用in和not in。这也会导致数据库引擎进行全表扫描,增加运行时间。此类情况可以考虑用between函数
- 尽量避免使用or。这也会导致数据库引擎进行全表扫描。可以考虑用联结优化,子句有些长,但是运行起来却会很节省时间
- 尽量用limit子句限制返回的数据行数,因为返回行数多也会导致运行时间长。