软件测试SQL面试题

　　数据库，无外乎增删改查：
　　增：
　　insertintoA表values（value1，value2，value3。。。。。。）向A表中新增数据
　　删：
　　deletefromA表（whereid1）删除A表（删除A表id1的数据）如果删除某个测试数据，记得加where条件，否则整张表的数据都被删除，防止误操作！不过，数据可以回滚找回。
　　truncateA表：清除表数据，数据无法回滚。
　　dropA表：整表结构删除，即，这张表不存在了。
　　改：
　　updateA表setnametestwhereid1修改id为1的name值为test
　　查：
　　1：左关联和右关联的区别？
　　答：左关联（leftjoin）左表为主，左表数据全部显示，右表显示关联数据，无关联显示null，右关联（rightjoin）相反。
　　2：模糊查询？
　　selectfromA表wherenamelikea（name包含a字母）
　　查询结果：a，abc，bac，bca
　　selectfromA表wherenamelikea（name值以a字母开头）
　　查询结果：a，abc
　　selectfromA表wherenamelikea（name值以a字母结尾）
　　查询结果：a，bca
　　3：统计：count和sum
　　count统计表的记录数，sum统计某列数值总和。
　　selectcount（）fromA表
　　selectcount（1）fromA表
　　selectcount（列名）fromA表
　　区别：
　　count（）：所有记录，包括null值
　　count（1）：所有记录，包括null值
　　count（列名）：列名有值得记录，不包括null值
　　执行效率：
　　以前是count（1）比count（）快，但现在count（）底层算法优化，查询更快，所以推荐count（）
　　统计有效数据的记录，count（列名）
　　selectsum（列名）fromA表计算列名数值总和。
　　举例：
　　查询A表中，姓王的用户量
　　selectcount（）fromAwherenamelike王
　　Plus版
　　1：去重distinct，查询不重复记录的数据
　　必须放开头
　　selectdistinct列名from表名（查询所有列名数据，去掉重复数据）
　　举例：
　　表A，查询考核等级grade，有哪些值
　　selectdistinctgradefromA
　　2：分组groupby，根据某个字段分组
　　select列名from表名groupby列名，一般会配合聚合函数一起使用
　　举例：
　　表A中，查询考核等级grade字段，不同值各多少人
　　selectgrade，count（）fromAgroupbygrade
　　3：limit，查询结果返回的数量，多用于分页查询
　　selectfrom表名limiti，ni代表查询结果的索引值，默认从0开始，n返回查询的结果数。
　　举例：
　　订单表A，查询第21条到30条数据
　　selectfromAlimit20，10
　　可能会问为什么不能直接用id查询？因为id不准确，可能存在id不连续的情况。如果某条数据被物理删除了呢？
　　何为物理删除和逻辑删除？
　　物理删除，直接将某条数据，从表中删除。
　　逻辑删除，仅通过某个字段标记删除，实际表中还存在。（比如：isdelete1代表已删除，isdelete0未删除）
　　4：排序orderby列名asc（列名值升序排列）和orderby列名desc（列名值降序排列）
　　举例：
　　用户表A，按照用户id升序（select查询默认根据主键升序，所以升序，不加orderby也可以）
　　selectfromAorderbyidasc同selectfromA
　　用户表A，按照新建时间倒序
　　selectfromAorderbycreatetimedesc
　　5：in和between查询某个范围的数值
　　举例：
　　in：查询指定数值的数据
　　between：查询某个范围内的数据
　　举例：
　　查询表A中，id1和id10的数据
　　selectfromAwhereidin（1，10）
　　查询表A中，1月份新增的数据
　　selectfromAwherecreatetimebetween20220101and2022013123：59：59
　　已知有如下4张表：
　　学生表：student（学号，学生姓名，出生年月，性别）
　　成绩表：score（学号，课程号，成绩）
　　课程表：course（课程号，课程名称，教师号）
　　教师表：teacher（教师号，教师姓名）
　　准备练习数据
　　1）创建学生表（student）CREATETABLEstudent（学号varchar（255）NOTNULL，姓名varchar（50）DEFAULTNULL，出生日期dateDEFAULTNULL，性别varchar（10）DEFAULTNULL，PRIMARYKEY（学号））ENGINEInnoDBDEFAULTCHARSETutf8；
　　2）创建成绩表（score）CREATETABLEscore（学号varchar（255）NOTNULL，课程号varchar（255）NOTNULL，成绩float（3，0）DEFAULTNULL，PRIMARYKEY（学号，课程号））ENGINEInnoDBDEFAULTCHARSETutf8；
　　3）创建课程表（course）CREATETABLEcourse（课程号varchar（255）NOTNULL，课程名称varchar（255）DEFAULTNULL，教师号varchar（255）DEFAULTNULL，PRIMARYKEY（课程号））ENGINEInnoDBDEFAULTCHARSETutf8；
　　4）教师表（teacher）CREATETABLEteacher（教师号varchar（255）NOTNULL，教师姓名varchar（50）DEFAULTNULL，PRIMARYKEY（教师号））ENGINEInnoDBDEFAULTCHARSETutf8；
　　1）学生表添加数据insertintostudent（学号，姓名，出生日期，性别）values（0001，猴子，19890101，男）；insertintostudent（学号，姓名，出生日期，性别）values（0002，猴子，19901221，女）；insertintostudent（学号，姓名，出生日期，性别）values（0003，马云，19911221，男）；insertintostudent（学号，姓名，出生日期，性别）values（0004，王思聪，19900520，男）；
　　2）成绩表添加数据insertintoscore（学号，课程号，成绩）values（0001，0001，80）；insertintoscore（学号，课程号，成绩）values（0001，0002，90）；insertintoscore（学号，课程号，成绩）values（0001，0003，99）；insertintoscore（学号，课程号，成绩）values（0002，0002，60）；insertintoscore（学号，课程号，成绩）values（0002，0003，80）；insertintoscore（学号，课程号，成绩）values（0003，0001，80）；insertintoscore（学号，课程号，成绩）values（0003，0002，80）；insertintoscore（学号，课程号，成绩）values（0003，0003，80）；
　　3）课程表添加数据insertintocourse（课程号，课程名称，教师号）values（0001，语文，0002）；insertintocourse（课程号，课程名称，教师号）values（0002，数学，0001）；insertintocourse（课程号，课程名称，教师号）values（0003，英语，0003）；
　　4）教师表添加数据教师表：添加数据insertintoteacher（教师号，教师姓名）values（0001，孟扎扎）；insertintoteacher（教师号，教师姓名）values（0002，马化腾）；这里的教师姓名是空值（null）insertintoteacher（教师号，教师姓名）values（0003，null）；这里的教师姓名是空字符串（）insertintoteacher（教师号，教师姓名）values（0004，）；
　　简单查询
　　查询姓猴的学生名单SELECTFROMstudentWHERE姓名LIKE猴；
　　查询姓名中最后一个字是猴字的学生名单SELECTFROMstudentWHERE姓名LIKE猴；
　　查询姓名中带猴字的学生名单SELECTFROMstudentWHERE姓名LIKE猴；
　　查询姓孟老师的个数SELECTCOUNT（教师号）FROMteacherWHERE教师姓名LIKE孟；
　　汇总分析
　　1。汇总分析
　　查询课程编号为0002的总成绩分析思路select查询结果〔总成绩：汇总函数sum〕from从哪张表中查找数据〔成绩表score〕where查询条件〔课程号是0002〕SELECTSUM（成绩）FROMscoreWHERE课程号0002；
　　查询选了课程的学生人数这个题目翻译成大白话就是：查询有多少人选了课程select学号，成绩表里学号有重复值需要去掉from从课程表查找SELECTCOUNT（DISTINCT学号）选了课程的学生人数FROM
　　2。分组
　　查询各科成绩的最高分和最低分，以如下的形式显示：课程号，最高分，最低分分析思路select查询结果〔课程ID：是课程号的别名，最高分：max（成绩），最低分：min（成绩）〕from从哪张表中查找数据〔成绩表score〕where查询条件〔没有〕groupby分组〔各科成绩：也就是每门课程的成绩，需要按课程号分组〕having分组后过滤〔没有〕orderby排序desc、asc〔没有〕limit分页和限制数量〔没有〕；SELECT课程号，MAX（成绩）最高分，MIN（成绩）最低分FROMscoreGROUPBY课程号；是先分组再查询每组得最高分和最低分
　　查询每门课程选修的学生数SELECT课程号，COUNT（学号）选修学生人数FROMscoreGROUPBY课程号；查询男生、女生人数SELECT性别，COUNT（学号）总人数FROMstudentGROUPBY性别；
　　3。分组结果的条件查询平均成绩大于60分学生的学号和平均成绩SELECT学号，AVG（成绩）平均成绩FROMscoreGROUPBY学号HAVINGAVG（成绩）60；
　　查询至少选修两门课程的学生学号SELECT学号，COUNT（课程号）选修课程数量FROMscoreGROUPBY学号HAVINGCOUNT（课程号）2；
　　查询同名同姓学生名单并统计同名人数
　　分析：条件：怎么算姓名相同？按姓名分组后人数大于等于2，因为同名的人数大于等于2，分析出这一点很重要SELECT姓名，COUNT（）FROMstudentGROUPBY姓名HAVINGCOUNT（）2；
　　查询不及格的课程并按课程号从大到小排列SELECT课程号FROMscoreWHERE成绩60ORDERBY课程号DESC；
　　查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
　　SELECT课程号，AVG（成绩）平均成绩FROMscoreGROUPBY课程号ORDERBY平均成绩ASC，课程号DESC；
　　检索课程编号为0004且分数小于60的学生学号，结果按按分数降序排列fromscoreWHERE课程号0004AND成绩60ORDERBY成绩DESC；
　　统计每门课程的学生选修人数（超过2人的课程才统计），要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序SELECT课程号，COUNT（学号）选修人数FROMscoreGROUPBY课程号HAVINGCOUNT（学号）2ORDERBYCOUNT（学号）DESC，课程号ASC；
　　查询两门以上不及格课程的同学的学号及其平均成绩分析思路先分解题目：1）〔两门以上〕〔不及格课程〕限制条件2）〔同学的学号及其平均成绩〕，也就是每个学生的平均成绩，显示学号，平均成绩分析过程：第1步：得到每个学生的平均成绩，显示学号，平均成绩第2步：再加上限制条件：1）不及格课程2）两门以上〔不及格课程〕：课程数目2第1步：得到每个学生的平均成绩，显示学号，平均成绩select查询结果〔学号，平均成绩：汇总函数avg（成绩）〕from从哪张表中查找数据〔涉及到成绩：成绩表score〕where查询条件〔没有〕groupby分组〔每个学生的平均：按学号分组〕having对分组结果指定条件〔没有〕orderby对查询结果排序〔没有〕；select学号，avg（成绩）as平均成绩fromscoregroupby学号；
　　第2步：再加上限制条件：
　　1）不及格课程
　　2）两门以上〔不及格课程〕select查询结果〔学号，平均成绩：汇总函数avg（成绩）〕from从哪张表中查找数据〔涉及到成绩：成绩表score〕where查询条件〔限制条件：不及格课程，平均成绩60〕groupby分组〔每个学生的平均：按学号分组〕having对分组结果指定条件〔限制条件：课程数目2，汇总函数count（课程号）2〕orderby对查询结果排序〔没有〕；SELECT学号，AVG（成绩）平均成绩这是小于60分课程的平均成绩，按题目理解是要查询这个学生全部课程的平均成绩FROMscorewhere成绩60GROUPBY学号HAVINGCOUNT（课程号）2；
　　4。查询结构排序，分组的指定条件
　　查询学生的总成绩并进行排名SELECT学号，SUM（成绩）总成绩fromscoreGROUPBY学号ORDERBY总成绩；
　　查询平均成绩大于60分的学生的学号和平均成绩SELECT学号，AVG（成绩）平均成绩FROMscoreGROUPBY学号HAVING平均成绩60；
　　复杂查询
　　查询课程成绩小于60分学生的学号、姓名
　　【知识点】子查询
　　1。翻译成大白话
　　1）查询结果：学生学号，姓名
　　2）查询条件：所有课程成绩60的学生，需要从成绩表里查找，用到子查询
　　第1步，写子查询（所有课程成绩60的学生）select查询结果〔学号〕from从哪张表中查找数据〔成绩表：score〕where查询条件〔成绩60〕groupby分组〔没有〕having对分组结果指定条件〔没有〕orderby对查询结果排序〔没有〕limit从查询结果中取出指定行〔没有〕；select学号fromstudentwhere成绩60；
　　第2步，查询结果：学生学号，姓名，条件是前面1步查到的学号select查询结果〔学号，姓名〕from从哪张表中查找数据〔学生表：student〕where查询条件〔用到运算符in〕groupby分组〔没有〕having对分组结果指定条件〔没有〕orderby对查询结果排序〔没有〕limit从查询结果中取出指定行〔没有〕；SELECT学号，姓名FROMstudentWHERE学号IN（SELECT学号fromscoreWHERE成绩60）；
　　查询没有学全所有课的学生的学号、姓名查找出学号，条件：没有学全所有课，也就是该学生选修的课程数总的课程数【考察知识点】in，子查询SELECT学号FROMscoreGROUPBY学号HAVINGCOUNT（课程号）3；SELECT学号，姓名FROMstudentWHERE学号IN（SELECT学号FROMscoreGROUPBY学号HAVINGCOUNT（课程号）3）；
　　查询出只选修了两门课程的全部学生的学号和姓名SELECT学号，姓名FROMstudentWHERE学号IN（SELECT学号FROMscoreGROUPBY学号HAVINGCOUNT（课程号）2）；日期函数
　　查找1990年出生的学生名单查找1990年出生的学生名单学生表中出生日期列的类型是datetimeSELECTFROMstudentWHEREYEAR（出生日期）1990；查询各学生的年龄（精确到月份）【知识点】时间格式转化select学号，timestampdiff（month，出生日期，now（））12
　　查询本月过生日的学生selectfromstudentwheremonth（出生日期）month（now（））2；topN查询
　　工作中会经常遇到这样的业务问题：
　　如何找到每个类别下用户最喜欢的产品是哪个？
　　如果找到每个类别下用户点击最多的5个商品是什么？
　　这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（topN）记录。
　　分组取每组最大值，按课程号分组取成绩最大值所在行的数据可以使用关联子查询来实现：selectfromscoreasawhere成绩（selectmax（成绩）fromscoreasbwhereb。课程号a。课程号）；
　　分组取每组最小值，按课程号分组取成绩最小值所在行的数据selectfromscoreasawhere成绩（selectmin（成绩）fromscoreasbwhereb。课程号a。课程号）；
　　每组最大的N条记录，查询各科成绩前两名的记录
　　第1步，查出有哪些组
　　我们可以按课程号分组，查询出有哪些组，对应这个问题里就是有哪些课程号select课程号，max（成绩）as最大成绩fromscoregroupby课程号；
　　第2步：先使用orderby子句按成绩降序排序（desc），然后使用limt子句返回topN（对应这个问题返回的成绩前两名）课程号0001这一组里成绩前2名selectfromscorewhere课程号0001orderby成绩desclimit2；同样的，可以写出其他组的（其他课程号）取出成绩前2名的sql
　　第3步，使用unionall将每组选出的数据合并到一起（selectfromscorewhere课程号0001orderby成绩desclimit2）unionall（selectfromscorewhere课程号0002orderby成绩desclimit2）unionall（selectfromscorewhere课程号0003orderby成绩desclimit2）；
　　多表查询
　　查询所有学生的学号、姓名、选课数、总成绩selecta。学号，a。姓名，count（b。课程号）as选课数，sum（b。成绩）as总成绩fromstudentasaleftjoinscoreasbona。学号b。学号groupbya。学号；
　　查询平均成绩大于85的所有学生的学号、姓名和平均成绩selecta。学号，a。姓名，avg（b。成绩）as平均成绩fromstudentasaleftjoinscoreasbona。学号b。学号groupbya。学号havingavg（b。成绩）85；
　　查询学生的选课情况：学号，姓名，课程号，课程名称selecta。学号，a。姓名，c。课程号，c。课程名称fromstudentainnerjoinscorebona。学号b。学号innerjoincourseconb。课程号c。课程号；
　　希望本文对你有所帮助如果对软件测试、接口测试、自动化测试、面试经验交流感兴趣可以私聊我或关注公众号特斯汀软件测试。免费领取最新软件测试大厂面试资料和Python自动化、接口、框架搭建学习资料！技术大牛解惑答疑，同行一起交流。