软件测试SQL面试题

　　数据库，无外乎增删改查：
　　增：
　　insert into  A表 values (value1，value2，value3......)向A表中新增数据
　　删：
　　delete from  A表 (where  id=1) 删除A表（删除A表id=1的数据）如果删除某个测试数据，记得加where条件，否则整张表的数据都被删除，防止误操作！不过，数据可以回滚找回。
　　truncate  A表：清除表数据，数据无法回滚。
　　drop  A表：整表结构删除，即，这张表不存在了。
　　改：
　　update  A表  set  name=＂test＂  where  id=1 修改id为1的name值为test
　　查：
　　1：左关联和右关联的区别？
　　答：左关联（left join ）左表为主，左表数据全部显示，右表显示关联数据，无关联显示null，右关联(right join )相反。
　　2：模糊查询？
　　select * from A表 where name like ＂%a% ＂ (name包含a字母)
　　查询结果：a ，a bc，ba c，bca
　　select * from A表 where name like ＂a% ＂ (name值以a字母开头)
　　查询结果：a ，a bc
　　select * from A表 where name like ＂%a ＂ (name值以a字母结尾)
　　查询结果：a ，bca
　　3：统计：count和sum
　　count统计表的记录数 ，sum统计某列数值总和 。
　　select count(*) from A表
　　select count(1) from A表
　　select count(列名) from A表
　　区别：
　　count( * )： 所有记录，包括null值
　　count( 1 )：所有记录，包括null值
　　count( 列名 )：列名有值得记录， 不包括null值
　　执行效率：
　　以前是count(1)比count(*)快，但现在count(*)底层算法优化,查询更快，所以推荐count(*)
　　统计有效数据的记录，count(列名)
　　select sum(列名) from A表 计算列名数值总和。
　　举例：
　　查询A表中，姓王的用户量
　　select count(*) from A where name like ＂王%＂
　　Plus版
　　1：去重distinct，查询不重复记录的数据
　　必须放开头
　　select distinct 列名 from 表名（查询所有列名数据，去掉重复数据）
　　举例：
　　表A，查询考核等级grade，有哪些值
　　select distinct grade from A
　　2：分组group by，根据某个字段分组
　　select 列名 from 表名 group by 列名，一般会配合聚合函数一起使用
　　举例：
　　表A中，查询考核等级grade字段，不同值各多少人
　　select grade,count(*) from A group by grade
　　3：limit，查询结果返回的数量，多用于分页查询
　　select * from 表名 limit i,n i代表查询结果的索引值，默认从0开始，n返回查询的结果数。
　　举例：
　　订单表A，查询第21条到30条数据
　　select * from A limit 20,10
　　可能会问为什么不能直接用id查询？因为id不准确，可能存在id不连续的情况。如果某条数据被物理删除了呢？
　　何为物理删除和逻辑删除？
　　物理 删除 ，直接将某条数据，从表中删除。
　　逻辑 删除，仅通过某个字段标记删除，实际表中还存在。(比如：is_delete=1代表已删除，is_delete=0未删除)
　　4：排序order by 列名 asc（列名值升序排列）和order by 列名 desc（列名值降序排列）
　　举例：
　　用户表A，按照用户id升序（select查询默认根据主键升序，所以升序，不加order by也可以）
　　select * from A order by id asc同select *from A
　　用户表A，按照新建时间倒序
　　select * from A order by create_time desc
　　5：in和between查询某个范围的数值
　　举例：
　　in：查询指定数值的数据
　　between：查询某个范围内的数据
　　举例：
　　查询表A中，id=1和id=10的数据
　　select * from A where id in(1,10)
　　查询表A中，1月份新增的数据
　　select * from A where create_time between ＂2022-01-01＂ and ＂2022-01-31 23:59:59＂
　　已知有如下4张表：
　　学生表：student(学号,学生姓名,出生年月,性别)
　　成绩表：score(学号,课程号,成绩)
　　课程表：course(课程号,课程名称,教师号)
　　教师表：teacher(教师号,教师姓名)
　　准备练习数据
　　1）创建学生表（student）CREATE TABLE `student` ( `学号` varchar(255) NOT NULL, `姓名` varchar(50) DEFAULT NULL, `出生日期` date DEFAULT NULL, `性别` varchar(10) DEFAULT NULL, PRIMARY KEY (`学号`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
　　2）创建成绩表（score）CREATE TABLE `score` ( `学号` varchar(255) NOT NULL, `课程号` varchar(255) NOT NULL, `成绩` float(3,0) DEFAULT NULL, PRIMARY KEY (`学号`,`课程号`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
　　3）创建课程表（course）CREATE TABLE `course` ( `课程号` varchar(255) NOT NULL, `课程名称` varchar(255) DEFAULT NULL, `教师号` varchar(255) DEFAULT NULL, PRIMARY KEY (`课程号`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
　　4）教师表（teacher）CREATE TABLE `teacher` ( `教师号` varchar(255) NOT NULL, `教师姓名` varchar(50) DEFAULT NULL, PRIMARY KEY (`教师号`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
　　1）学生表添加数据insert into student(学号,姓名,出生日期,性别) values(＂0001＂ , ＂猴子＂ , ＂1989-01-01＂ , ＂男＂); insert into student(学号,姓名,出生日期,性别) values(＂0002＂ , ＂猴子＂ , ＂1990-12-21＂ , ＂女＂); insert into student(学号,姓名,出生日期,性别) values(＂0003＂ , ＂马云＂ , ＂1991-12-21＂ , ＂男＂); insert into student(学号,姓名,出生日期,性别) values(＂0004＂ , ＂王思聪＂ , ＂1990-05-20＂ , ＂男＂);
　　2）成绩表添加数据insert into score(学号,课程号,成绩) values(＂0001＂ , ＂0001＂ , 80); insert into score(学号,课程号,成绩) values(＂0001＂ , ＂0002＂ , 90); insert into score(学号,课程号,成绩) values(＂0001＂ , ＂0003＂ , 99); insert into score(学号,课程号,成绩) values(＂0002＂ , ＂0002＂ , 60); insert into score(学号,课程号,成绩) values(＂0002＂ , ＂0003＂ , 80); insert into score(学号,课程号,成绩) values(＂0003＂ , ＂0001＂ , 80); insert into score(学号,课程号,成绩) values(＂0003＂ , ＂0002＂ , 80); insert into score(学号,课程号,成绩) values(＂0003＂ , ＂0003＂ , 80);
　　3）课程表添加数据insert into course(课程号,课程名称,教师号) values(＂0001＂ , ＂语文＂ , ＂0002＂); insert into course(课程号,课程名称,教师号) values(＂0002＂ , ＂数学＂ , ＂0001＂); insert into course(课程号,课程名称,教师号) values(＂0003＂ , ＂英语＂ , ＂0003＂);
　　4）教师表添加数据-- 教师表：添加数据 insert into teacher(教师号,教师姓名) values(＂0001＂ , ＂孟扎扎＂); insert into teacher(教师号,教师姓名) values(＂0002＂ , ＂马化腾＂); -- 这里的教师姓名是空值（null） insert into teacher(教师号,教师姓名) values(＂0003＂ , null); -- 这里的教师姓名是空字符串（＂＂） insert into teacher(教师号,教师姓名) values(＂0004＂ , ＂＂);
　　简单查询
　　查询姓＂猴＂的学生名单SELECT * FROM student WHERE 姓名 LIKE ＂猴%＂;
　　查询姓名中最后一个字是＂猴＂字的学生名单SELECT * FROM student WHERE 姓名 LIKE ＂%猴＂;
　　查询姓名中带＂猴＂字的学生名单SELECT * FROM student WHERE 姓名 LIKE ＂%猴%＂;
　　查询姓＂孟＂老师的个数SELECT COUNT(教师号) FROM teacher WHERE 教师姓名 LIKE ＂孟%＂;
　　汇总分析
　　1.汇总分析
　　查询课程编号为＂0002＂的总成绩* 分析思路 select 查询结果 [总成绩:汇总函数sum] from 从哪张表中查找数据[成绩表score] where 查询条件 [课程号是0002] */ SELECT SUM(成绩) FROM score WHERE 课程号=＂0002＂;
　　查询选了课程的学生人数/* 这个题目翻译成大白话就是：查询有多少人选了课程 select 学号，成绩表里学号有重复值需要去掉 from 从课程表查找score; */ SELECT COUNT(DISTINCT 学号) 选了课程的学生人数 FROM score;
　　2.分组
　　查询各科成绩的最高分和最低分, 以如下的形式显示：课程号，最高分，最低分/* 分析思路 select 查询结果 [课程ID：是课程号的别名,最高分：max(成绩) ,最低分：min(成绩)] from 从哪张表中查找数据 [成绩表score] where 查询条件 [没有] group by 分组 [各科成绩：也就是每门课程的成绩，需要按课程号分组] having 分组后过滤[没有] order by 排序desc、asc[没有] limit 分页和限制数量[没有]; */ SELECT 课程号,MAX(成绩) 最高分,MIN(成绩) 最低分 FROM score GROUP BY 课程号; -- 是先分组再查询每组得最高分和最低分
　　查询每门课程选修的学生数SELECT 课程号,COUNT(学号) 选修学生人数 FROM score GROUP BY 课程号; 查询男生、女生人数 SELECT 性别,COUNT(学号) 总人数 FROM student GROUP BY 性别;
　　3.分组结果的条件查询平均成绩大于60分学生的学号和平均成绩 SELECT 学号,AVG(成绩) 平均成绩 FROM score GROUP BY 学号 HAVING AVG(成绩)>60;
　　查询至少选修两门课程的学生学号SELECT 学号,COUNT(课程号) 选修课程数量 FROM score GROUP BY 学号 HAVING COUNT(课程号)>=2;
　　查询同名同姓学生名单并统计同名人数
　　分析：条件：怎么算姓名相同？按姓名分组后人数大于等于2，因为同名的人数大于等于2，分析出这一点很重要SELECT 姓名,COUNT(*) FROM student GROUP BY 姓名 HAVING COUNT(*)>=2;
　　查询不及格的课程并按课程号从大到小排列SELECT 课程号 FROM score WHERE 成绩<60 ORDER BY 课程号 DESC;
　　查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
　　SELECT 课程号,AVG(成绩) 平均成绩 FROM score GROUP BY 课程号 ORDER BY 平均成绩 ASC,课程号 DESC;
　　检索课程编号为＂0004＂且分数小于60的学生学号，结果按按分数降序排列from score WHERE 课程号=＂0004＂ AND 成绩<60 ORDER BY 成绩 DESC;
　　统计每门课程的学生选修人数(超过2人的课程才统计)，要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序SELECT 课程号,COUNT(学号) 选修人数 FROM score GROUP BY 课程号 HAVING COUNT(学号)>2 ORDER BY COUNT(学号) DESC,课程号 ASC;
　　查询两门以上不及格课程的同学的学号及其平均成绩/* 分析思路 先分解题目： 1）[两门以上][不及格课程]限制条件 2）[同学的学号及其平均成绩]，也就是每个学生的平均成绩，显示学号，平均成绩 分析过程： 第1步：得到每个学生的平均成绩，显示学号，平均成绩 第2步：再加上限制条件： 1）不及格课程 2）两门以上[不及格课程]：课程数目>2  /*  第1步：得到每个学生的平均成绩，显示学号，平均成绩 select 查询结果 [学号,平均成绩：汇总函数avg(成绩)] from 从哪张表中查找数据 [涉及到成绩：成绩表score] where 查询条件 [没有] group by 分组 [每个学生的平均：按学号分组] having 对分组结果指定条件 [没有] order by 对查询结果排序[没有]; */ select 学号, avg(成绩) as 平均成绩 from score group by 学号;  /*
　　第2步：再加上限制条件：
　　1）不及格课程
　　2）两门以上[不及格课程]select 查询结果 [学号,平均成绩：汇总函数avg(成绩)] from 从哪张表中查找数据 [涉及到成绩：成绩表score] where 查询条件 [限制条件：不及格课程，平均成绩<60] group by 分组 [每个学生的平均：按学号分组] having 对分组结果指定条件 [限制条件：课程数目>2,汇总函数count(课程号)>2] order by 对查询结果排序[没有]; */ SELECT 学号,AVG(成绩) 平均成绩 -- 这是小于60分课程的平均成绩，按题目理解是要查询这个学生全部课程的平均成绩 FROM score where 成绩<60 GROUP BY 学号 HAVING COUNT(课程号)>=2;
　　4.查询结构排序，分组的指定条件
　　查询学生的总成绩并进行排名SELECT 学号,SUM(成绩) 总成绩 from score GROUP BY 学号 ORDER BY 总成绩;
　　查询平均成绩大于60分的学生的学号和平均成绩SELECT 学号,AVG(成绩) 平均成绩 FROM score GROUP BY 学号 HAVING 平均成绩>60;
　　复杂查询
　　查询课程成绩小于60分学生的学号、姓名
　　【知识点】子查询
　　1.翻译成大白话
　　1）查询结果：学生学号，姓名
　　2）查询条件：所有课程成绩 < 60 的学生，需要从成绩表里查找，用到子查询
　　第1步，写子查询（所有课程成绩 < 60 的学生）select 查询结果[学号] from 从哪张表中查找数据[成绩表：score] where 查询条件[成绩 < 60] group by 分组[没有] having 对分组结果指定条件[没有] order by 对查询结果排序[没有] limit 从查询结果中取出指定行[没有]; select 学号 from student where 成绩 < 60;
　　第2步，查询结果：学生学号，姓名，条件是前面1步查到的学号select 查询结果[学号,姓名] from 从哪张表中查找数据[学生表:student] where 查询条件[用到运算符in] group by 分组[没有] having 对分组结果指定条件[没有] order by 对查询结果排序[没有] limit 从查询结果中取出指定行[没有]; */ SELECT 学号,姓名 FROM student WHERE 学号 IN(SELECT 学号 from score WHERE 成绩<60);
　　查询没有学全所有课的学生的学号、姓名/* 查找出学号，条件：没有学全所有课，也就是该学生选修的课程数 < 总的课程数 【考察知识点】in，子查询 */ SELECT 学号 FROM score GROUP BY 学号 HAVING COUNT(课程号)<3; SELECT 学号,姓名 FROM student WHERE 学号 IN(SELECT 学号 FROM score GROUP BY 学号 HAVING COUNT(课程号)<3);
　　查询出只选修了两门课程的全部学生的学号和姓名SELECT 学号,姓名 FROM student WHERE 学号 IN(SELECT 学号 FROM score GROUP BY 学号 HAVING COUNT(课程号)=2); 日期函数
　　查找1990年出生的学生名单/* 查找1990年出生的学生名单 学生表中出生日期列的类型是datetime */ SELECT * FROM student WHERE YEAR(出生日期)=＂1990＂; 查询各学生的年龄（精确到月份） /* 【知识点】时间格式转化 */ select 学号 ,timestampdiff(month ,出生日期 ,now())/12 from student ;
　　查询本月过生日的学生select * from student where month (出生日期 ) = month(now())+2;  top-N查询
　　工作中会经常遇到这样的业务问题：
　　如何找到每个类别下用户最喜欢的产品是哪个？
　　如果找到每个类别下用户点击最多的5个商品是什么？
　　这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（top N）记录。
　　分组取每组最大值，按课程号分组取成绩最大值所在行的数据-- 可以使用关联子查询来实现： select * from score as a where 成绩 = ( select max(成绩) from score as b where b.课程号 = a.课程号);
　　分组取每组最小值，按课程号分组取成绩最小值所在行的数据select * from score as a where 成绩 = ( select min(成绩) from score as b where b.课程号 = a.课程号);
　　每组最大的N条记录，查询各科成绩前两名的记录
　　第1步，查出有哪些组
　　我们可以按课程号分组，查询出有哪些组，对应这个问题里就是有哪些课程号select 课程号,max(成绩) as 最大成绩 from score group by 课程号;
　　第2步：先使用order by子句按成绩降序排序（desc），然后使用limt子句返回topN（对应这个问题返回的成绩前两名）-- 课程号＂0001＂ 这一组里成绩前2名 select * from score where 课程号 = ＂0001＂ order by 成绩 desc limit 2; 同样的，可以写出其他组的（其他课程号）取出成绩前2名的sql
　　第3步，使用union all 将每组选出的数据合并到一起(select * from score where 课程号 = ＂0001＂ order by 成绩 desc limit 2) union all (select * from score where 课程号 = ＂0002＂ order by 成绩 desc limit 2) union all (select * from score where 课程号 = ＂0003＂ order by 成绩 desc limit 2);
　　多表查询
　　查询所有学生的学号、姓名、选课数、总成绩selecta.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩 from student as a left join score as b on a.学号 = b.学号 group by a.学号;
　　查询平均成绩大于85的所有学生的学号、姓名和平均成绩select a.学号,a.姓名, avg(b.成绩) as 平均成绩 from student as a left join score as b on a.学号 = b.学号 group by a.学号 having avg(b.成绩)>85;
　　查询学生的选课情况：学号，姓名，课程号，课程名称select a.学号, a.姓名, c.课程号,c.课程名称 from student a inner join score b on a.学号=b.学号 inner join course c on b.课程号=c.课程号;
　　希望本文对你有所帮助~~如果对软件测试、接口测试、自动化测试、面试经验交流感兴趣可以私聊我或关注公众号＂特斯汀软件测试＂。免费领取最新软件测试大厂面试资料和Python自动化、接口、框架搭建学习资料！技术大牛解惑答疑，同行一起交流。