文章目录

• • 概述
  • 单表查询任务
  • • • SQL通配符
  • 多表查询
  • 参考

---

概述

  新建数据库sc，再使用Navicat导入sc.sql文件。通过快捷键Ctrl + Q在Navicat调出查询部分，开始查询语句实验。

  数据库有三张表：学生表S，包含学号、学生姓名、学生年龄、所属系。
学生课程表SC，包含学号、课程号、分数。课程表C，包含课程号、课程名称、教师。
****

单表查询任务

  1. 查询C4号课程没有考试成绩的学生号。
学生成绩、课程号、学号在同一个表格sc中，所以查询语句如下：

SELECT sno from sc where (cno='c4' and grade='')

  2.查询C4号课程成绩在90分以上或60分以下的学生学号。使用sc表格即可。

SELECT sno from sc where (cno='c4' and (grade>90 or grade<60))

  3.查询课程名以“C”开头的所有课程号和课程名。使用c表格即可。

SELECT cno,cname from c where cname like 'C%'

SQL通配符

  值得注意的是，这里需要使用 SQL通配符 替代一个或多个字符。SQL通配符必须与Like运算符一起使用。

通配符	描述
%	替代一个或多个字符
下划线_	仅替代一个字符
[charlist]	字符清单中的任何单一字符
[^charlist]或[!charlist]	不在字符清单中的任何单一字符

  4.查询每个学生所有课程的平均成绩，输出学号和平均成绩。使用sc表即可。
  平均成绩需要各课程成绩相加再求平均。SQL语句求某列的和、平均值、最大值、最小值、行数，主要用到sum()、avg()、max()、min()、count()函数。

  查询学号和各科成绩：select sno,grade from sc
  查询平均成绩：SELECT sno,AVG(grade) from sc GROUP BY sno
  无group by则报错的语句：

SELECT sno,AVG(grade) from sc
> 1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'sc.sc.SNO'; this is incompatible with sql_mode=only_full_group_by
> 时间: 0s

  5.查询每门课程的选修人数，输出课程号和选修人数。使用sc表格即可。

计算学号有多少行/个，即选修人数：SELECT cno,count(sno) from sc GROUP BY cno
  

  7.查询每门课程都及格的学生的学号。使用sc表格即可。
查询课程及格的学生的学号：select sno from sc where grade>=60
查询所有课程都及格的学生的学号，可以换个思路，把不及格的学生排除掉。
select DISTINCT sno from sc where sno not in(select sno from sc where grade<60)

  8.查询既有课程大于90分又有课程不及格的学生的学号。使用sc表格即可。
大于90分：select sno from sc where grade>90
不及格：select sno from sc where grade<60

  但是MySQL没有交集intersect和差集minus，自连接查询：
select sno from sc where grade>90 and sno in(select sno from sc where grade<60)

  9.查询平均分不及格的学生和平均成绩。使用sc表格即可。
由于where关键字不能与合计函数一起使用，所以设计了Having子句。
查询平均成绩的语句：select sno,AVG(grade) from sc group by sno
增加条件：having AVG(grade)<60
  最终语句
select sno,AVG(grade) from sc group by sno having AVG(grade)<60

  11.查询至少选修了S2学生选修过的全部课程的学生。使用sc表格即可。
查询：select sno from sc
条件：s2学生选修的所有课程select cno from sc where sno='2'

  12.求各门课程去掉一个最高分和最低分后的平均分。
求各门课程的平均分：select cno,AVG(grade) from sc group by cno

  去掉最高分最低分，使用count()表示成绩人数：
select cno,(sum(grade)-max(grade)-min(grade))/(count(grade)-2) from sc group by cno

  14.查询有3名以上学生选修的课程号。使用sc表格即可。
返回指定列匹配的数量count(*)，
查询：select cno from sc
课程的选修数量：select count(*) from sc where cno=‘C1’

  15.查出没有考试不及格的学生的学号。跟第7题的要求相同：
select DISTINCT sno from sc where sno not in(select sno from sc where grade<60)

  17.找出各个系男女学生的平均年龄和人数。
查询男生的平均年龄和人数：select avg(sage),count(*) from s where ssex=‘1’
查询各系的人数：select count(*) from s group by sdept
查询各系男生的平均年龄和人数，使用group by进行分类：
select avg(sage),count(*) from s where ssex='1' group by sdept
  查询各系男女学生的平均年龄和人数：
select sdept,ssex,avg(sage),count(*) from s group by sdept,ssex

  20.查询每门课程的及格率
查询某课程的及格人数：select count(*) from sc where grade>=60 and cno='C1'
查询所有课程的及格人数：select count(*) from sc where grade>=60
查询各课程的及格人数：select count(*) from sc where grade>=60 group by cno

---

多表查询

  6.查询选修“C4”课程学生的学号、姓名和性别。需要使用表格s和sc。
  引用两个表，使用逗号：
SELECT DISTINCT s.sno,s.sname,s.ssex from s,sc where sc.cno='C4'
  内连接inner join：
SELECT DISTINCT s.sno,s.sname,s.ssex from s inner join sc where sc.cno='C4'

  上面的两个多表查询，会出现20*20条数据，重复数据19*20条。在进行多表查询时，可以使用distinct关键字对重复数据进行去重：select distinct。

  最终语句：
SELECT DISTINCT s.sno,s.sname,s.ssex from s,sc where sc.cno='C4'

  10.查询平均分大于70的课程号和课程名。需要使用表格c和sc。
  查询：select c.cno,c.cname。条件：avg(grade)>70，和avg捆绑使用的group by。
使用自连接查询，where x in()。
select c.cno,c.cname from c where c.cno in(select cno from sc group by sc.cno having avg(sc.grade)>70)

  13.查询选修7号课程的学生的平均年龄。使用表格s和sc。
两张表合并：select * from s,sc
无论是使用逗号，还是inner join，都是两张表的笛卡尔积。所以考虑使用where xx in。
select avg(s.sage) from s where sno in(select sno from sc where sc.cno='C5')

  16.找出C1课程成绩不低于该门平均分的学生姓名。多表查询。
平均分：select avg(grade) from sc where cno=‘C1’
查询不低于平均分的学生学号：select sno from sc where cno='C1' and grade>(select avg(grade) from sc where cno='C1')
  使用select sname from s where sno in()进行嵌套：
select sname from s where sno in(select sno from sc where cno='C1' and grade>(select avg(grade) from sc where cno='C1'))

  18.找出“dept1”平均分最高的学生的学号和姓名。group by是分组，order by是排序。
查询：select sno,sname from s
查询各个学生的平均分：select avg(grade),count(*) from sc group by sno
查询最高平均分的学生：
select sno,avg(grade) from sc group by sno order by avg(grade) desc limit 1
  使用select sno,sname from s where sno=()嵌套：
select sno,sname from s where sno=(select sno from sc group by sno order by avg(grade) desc limit 1)

  19.查找平均分不及格的学生的学号、姓名、平均分
查询平均分不及格的学生：
select sno,avg(grade) from sc group by sno HAVING avg(grade)<60
  难点在于姓名和平均分不在一个表中，必须进行表的连接。

---

参考

  《使用navicat导入sql文件》，2018-02
https://blog.csdn.net/qq_33699659/article/details/79261661

  《navicat导入sql文件后没有导入表问题》，2019-03
https://blog.csdn.net/xzl21184/article/details/88389737

  《SQL判断字段是否为空,为NULL》
https://www.cnblogs.com/kingboy-xin/p/10825085.html

  《SQL 通配符》
https://www.w3school.com.cn/sql/sql_wildcards.asp

  《SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据》
https://www.w3school.com.cn/sql/sql_join.asp

  《最全的SQL练习题，做完你不是高手我不信》，2021-01
https://blog.csdn.net/weixin_54696666/article/details/112967454

  《SQL having语句》
https://www.jianshu.com/p/44b189254f83

  

  

  

  

  建立索引。使用一些查询语句。看索引有没有提升。