软件测试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，abc，bac，bca

select * from A表 where name like 'a%' (name值以a字母开头)

查询结果：a，abc

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 scoreGROUP BY 课程号; -- 是先分组再查询每组得最高分和最低分

查询每门课程选修的学生数

SELECT 课程号,COUNT(学号) 选修学生人数FROM scoreGROUP BY 课程号;查询男生、女生人数SELECT 性别,COUNT(学号) 总人数FROM studentGROUP BY 性别;

3.分组结果的条件

查询平均成绩大于60分学生的学号和平均成绩SELECT 学号,AVG(成绩) 平均成绩FROM scoreGROUP BY 学号HAVING AVG(成绩)>60;

查询至少选修两门课程的学生学号

SELECT 学号,COUNT(课程号) 选修课程数量FROM scoreGROUP BY 学号HAVING COUNT(课程号)>=2;

查询同名同姓学生名单并统计同名人数

分析：条件：怎么算姓名相同？按姓名分组后人数大于等于2，因为同名的人数大于等于2，分析出这一点很重要

SELECT 姓名,COUNT(*)FROM studentGROUP BY 姓名HAVING COUNT(*)>=2;

查询不及格的课程并按课程号从大到小排列

SELECT 课程号FROM scoreWHERE 成绩<60ORDER BY 课程号 DESC;

查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

SELECT 课程号,AVG(成绩) 平均成绩FROM scoreGROUP BY 课程号ORDER BY 平均成绩 ASC,课程号 DESC;

检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列

from scoreWHERE 课程号='0004' AND 成绩<60ORDER BY 成绩 DESC;

统计每门课程的学生选修人数(超过2人的课程才统计)，要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

SELECT 课程号,COUNT(学号) 选修人数FROM scoreGROUP BY 课程号HAVING COUNT(学号)>2ORDER 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 scoregroup by 学号;/* 

第2步：再加上限制条件：

1）不及格课程

2）两门以上[不及格课程]

select 查询结果 [学号,平均成绩：汇总函数avg(成绩)]from 从哪张表中查找数据 [涉及到成绩：成绩表score]where 查询条件 [限制条件：不及格课程，平均成绩<60]group by 分组 [每个学生的平均：按学号分组]having 对分组结果指定条件 [限制条件：课程数目>2,汇总函数count(课程号)>2]order by 对查询结果排序[没有];*/SELECT 学号,AVG(成绩) 平均成绩 -- 这是小于60分课程的平均成绩，按题目理解是要查询这个学生全部课程的平均成绩FROM scorewhere 成绩<60GROUP BY 学号HAVING COUNT(课程号)>=2;

4.查询结构排序，分组的指定条件

查询学生的总成绩并进行排名

SELECT 学号,SUM(成绩) 总成绩from scoreGROUP BY 学号ORDER BY 总成绩;

查询平均成绩大于60分的学生的学号和平均成绩

SELECT 学号,AVG(成绩) 平均成绩FROM scoreGROUP BY 学号HAVING 平均成绩>60;

复杂查询

查询课程成绩小于60分学生的学号、姓名

【知识点】子查询

1.翻译成大白话

1）查询结果：学生学号，姓名

2）查询条件：所有课程成绩 < 60 的学生，需要从成绩表里查找，用到子查询

第1步，写子查询（所有课程成绩 < 60 的学生）

select 查询结果[学号]from 从哪张表中查找数据[成绩表：score]where 查询条件[成绩 < 60]group by 分组[没有]having 对分组结果指定条件[没有]order by 对查询结果排序[没有]limit 从查询结果中取出指定行[没有];select 学号from studentwhere 成绩 < 60;

第2步，查询结果：学生学号，姓名，条件是前面1步查到的学号

select 查询结果[学号,姓名]from 从哪张表中查找数据[学生表:student]where 查询条件[用到运算符in]group by 分组[没有]having 对分组结果指定条件[没有]order by 对查询结果排序[没有]limit 从查询结果中取出指定行[没有];*/SELECT 学号,姓名FROM studentWHERE 学号 IN(SELECT 学号 from score WHERE 成绩<60);

查询没有学全所有课的学生的学号、姓名

/*查找出学号，条件：没有学全所有课，也就是该学生选修的课程数 < 总的课程数【考察知识点】in，子查询*/SELECT 学号FROM scoreGROUP 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())/12from student ;

查询本月过生日的学生

select *from studentwhere month (出生日期 ) = month(now())+2;​top-N查询

工作中会经常遇到这样的业务问题：

如何找到每个类别下用户最喜欢的产品是哪个？

如果找到每个类别下用户点击最多的5个商品是什么？

这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（top N）记录。

分组取每组最大值，按课程号分组取成绩最大值所在行的数据

-- 可以使用关联子查询来实现：select *from score as awhere 成绩 = (select max(成绩)from score as bwhere b.课程号 = a.课程号);

分组取每组最小值，按课程号分组取成绩最小值所在行的数据

select *from score as awhere 成绩 = (select min(成绩)from score as bwhere b.课程号 = a.课程号);

每组最大的N条记录，查询各科成绩前两名的记录

第1步，查出有哪些组

我们可以按课程号分组，查询出有哪些组，对应这个问题里就是有哪些课程号

select 课程号,max(成绩) as 最大成绩from scoregroup by 课程号;

第2步：先使用order by子句按成绩降序排序（desc），然后使用limt子句返回topN（对应这个问题返回的成绩前两名）

-- 课程号'0001' 这一组里成绩前2名select *from scorewhere 课程号 = '0001'order by 成绩 desclimit 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 bon a.学号 = b.学号group by a.学号;

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select a.学号,a.姓名, avg(b.成绩) as 平均成绩from student as a left join score as bon 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自动化、接口、框架搭建学习资料！技术大牛解惑答疑，同行一起交流。