signed

QiShunwang

“诚信为本、客户至上”

【数据库安全】_SQL实验

2021/4/26 20:56:53   来源:

文章目录

    • 概述
    • 单表查询任务
        • 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

  

  

  

  

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