软件测试面试题总结（二）- 数据库知识点

持续更新参加面试过程中，遇到的软件测试相关的面试题，包括：项目管理、测试理论、python基础、数据库、团队管理等等。

1. 关系型数据库和非关系型数据库

1.1 数据库分类

关系型数据库	非关系型数据库（分布式数据库）	中间件
Mysql	MongoDB（文档型数据库）	数据库中间件有以下4种：1、分表分库中间件 ；2、数据增量订阅与消费中间件； 3、数据库同步中间件；4、跨数据库（数据源）迁移中间件。
SQL Server	Redis（键值对存储数据库）	常见的数据库中间件工具有：1、Cobar；2、DRDS；3、MyCat；4、Atlas；5、OneProxy。
Oracle	HBase

1.2 数据库存储类型

关系型数据库：基于表存储数据，SQL是一门结构化语言。

非关系性数据库（分布式数据库）：使用键值对、文档或图形动态存储数据。

1.3 数据库处理业务

SQL支持复杂的业务，可以进行多表联合查询，支持复杂的多表联合查询

NoSQL数据库存储结构不固定，不支持复杂的联合查询

1.4 数据库事务

SQL遵循严格的事务一致性ACID属性（事务的特性：原子性、一致性、隔离性和持久性）

NoSQL支持分布式CAP原理（一致性、可用性和分区容错性）

事务（Transaction）：在数据库中，事务是指一组数据库操作，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单元，mysql事务应用广泛，特别是开发高并发系统时，事务可以保证数据的完整性和一致性：银行转账、订单处理、库存管理、日志记录

原子性	逻辑上是不可分割的操作单元，事务的所有操作要么全部提交成功，要么全部失败回滚（用回滚日志实现，反向执行日志中的操作）
一致性	事务的执行必须使数据库保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的
隔离性	一个事务所做的修改在最终提交以前，对其它事务是不可见的（并发执行的事务之间不能相互影响）
持久性	一旦事务提交成功，对数据的修改是永久性的

一致性	所有节点在同一时间具有相同的数据
可用性	保证每个请求不管成功或者失败都有响应
区分容错性	系统中任意信息的丢失或失败不影响系统的继续运行

2. MySql和Redis的选择

1. redis 读写效率高，性能好，成本高，所以会用在对性能要求高的场合，如：抢购、秒杀、实时排行榜

2. mysql 可以存储大量数据，持久化到硬盘，性价比高。在性能要求不那么高的场合均建议使用

3. redis 持久化：由于redis的数据都存放在内存中，如果没有配置持久化，redis重启后数据就全丢失，于是需要开启redis持久化功能，将数据保存在磁盘上。redis 重启后可以从磁盘恢复数据。

本质区别：

mysql主redis辅：加快访问读取速度，提高性能

mysql数据放在磁盘：mysql作为持久化存储的关系型数据库，相对薄弱的地方在于每次访问数据库时，都存在IO操作，如果频繁访问数据库：1. 会在反复连接数据库上花费大量时间，导致运行效率慢，2. 反复访问数据库也会导致数据库负载过高

redis数据存在内存，读取数据极快，成本高，断电数据就清零，需要做持久化

使用场景

mysql 支持复杂的联合查询，可以实现一些关联的查询和统计

redis 对内存要求极高，在有限的条件下不能把所有的数据都放在redis中

mysql 偏向于存数据，redis 偏向于快速读取数据

3. 数据库结构化查询语句

连接多个表	使用JOIN语句连接多个数据表，进行联合查询
排序数据	使用ORDER BY 语句对查询结果进行排序 DESC ASC
分组数据	使用GROUP BY语句对查询结果进行分组
过滤数据	使用WHERE 子句根据条件过滤查询结果
统计数据	使用聚合函数如COUNT、SUM、AVG等对数据进行统计
备份和恢复数据	使用mysqldump明亮备份数据库，使用mysql命令导入备份数据进行恢复
distinct	获取唯一结果

4. mysql三种删除 delete truncate drop 

delete：通过过滤条件删除数据
delete from student order by age desc limit 3;
truncate： 不能加条件表达，删除所有数据
truncate student;
drop：表结构和表数据都删除
drop table student 

5. mysql 表添加字段 alter 

在表尾添加字段；
alter table student add age int(4);
在表头添加字段
alter table student add age int(4) first;
在指定位置添加字段
alter table student add age int(4) after name;

6. mysql 多表联合查询

select t1.sid, t1.name ccount, sscore from student t1
join
(select sid, count(cid) as ccount, sum(score) as sscore from sC group by sid) sc
on t1.sid = sc.sid;

mysql distinct 用于去重

7. MySQL索引

索引是帮助MySQL高效获取数据的数据结构，数据库索引类似一本图书的目录，能加快数据库的查询速度 索引提高了查询效率，但同时也降低了更新表的效率：update、insert、delete，索引占内存

a. 单列索引：一个索引只包含单个列，但是一张表可以有多个索引

• 普通索引：mysql中基本索引类型，无限制，允许在定义的索引列插入重复值和空值，存粹是为了查询速度快一点
• 唯一索引：索引列的值必须是唯一的，但允许空值
• 主键索引：是一种特殊的唯一索引，不允许有空值

b. 组合索引

c. 全文索引

8. MySQL关键字

1. where 在分组和聚合之前筛选数据，用于表数据过滤
2. having 在分组和聚合之后筛选数据，用于组数据过滤

1. UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行，
UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同.
2. UNION ALL 不消去重复的行

1. 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
2. 右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。 

9. 查找语句

面试过程经常会问到，多表联合查询的sql语句，讲老实话可视化工具，一张表一张表也能查到...

9.1. 查找选课学生的名字和平均成绩

解释：这里需要使用左连接和子查询，需要注意的是，在做**多表查询**，或者查询的时候**产生新的表**的时候会出现这个错误：Every derived table must have its own alias（**每一个派生出来的表都必须有一个自己的别名**）

select sname, avgmark from tb_student t1 join (select sid, AVG(mark) as avgmark from tb_score group by sid) t2 on stuid=sid;

9.2. 查询每个学生的姓名和选课数量(左外连接和子查询)

select sname, countcid from tb_student t1 join (select sid, COUNT(cid) as countcid from tb_score group by sid) t2 on stuid = sid;

9.3. 查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩

select stuid, sname, avgmark from tb_student t1 join (select sid, AVG(mark) as avgmark from tb_score group by sid having avgmark>60) t2 on stuid = sid;
`select stuid, sname, avg(mark) from tb_student as stu, tb_score as s where stu.stuid=s.sid group by sid having AVG(mark) > 60;

持续更新......