数据库基础—SQL Server

一、概述
在 SQL Server 面试中，作为软件测试工程师，你可能会被问及与数据库测试和 SQL 查询相关的问题。以下是一些你可能需要掌握的技能：

（1）SQL 查询语言： 熟练掌握 SQL 查询语言，包括 SELECT、INSERT、UPDATE、DELETE 等基本语句。了解 SQL 查询的语法和规则。

**（2）数据库测试：**理解数据库测试的基本概念和流程。了解如何编写有效的数据库测试用例，包括对数据库表、视图、存储过程等的测试。

（3）数据一致性和完整性： 理解数据库中的数据一致性和完整性约束，了解如何测试这些约束以确保数据的准确性和完整性。

（4）性能测试： 了解数据库性能测试的基本原理和方法。知道如何编写性能测试脚本，并能够分析数据库查询的执行计划。

（5）索引和优化： 了解数据库索引的作用，知道如何优化查询语句以提高性能。理解索引的类型和选择合适的索引策略。

（6）事务管理： 了解数据库事务的概念，知道如何测试事务的正确性和一致性。

（7）备份与恢复： 知道如何执行数据库备份和恢复操作。了解不同备份策略的优缺点。

（8）安全性和权限： 理解数据库的安全性模型，包括用户权限、角色权限等。了解如何测试和验证数据库的安全性。

（9）版本控制： 了解如何在数据库中执行版本控制，以确保数据库架构的一致性。

（10）.数据库工具： 熟悉一些常见的 SQL Server 管理工具，如 SQL Server Management Studio (SSMS)。知道如何使用这些工具执行基本的数据库操作。

（11）.错误处理和日志： 知道如何处理数据库中的错误，并能够查看和分析数据库的日志信息。

二、SQL查询语句
1、SQL常用语句：
（1）SELECT 语句： 用于从一个或多个表中检索数据。

SELECT column1, column2, ...
FROM table_name
WHERE condition;

（2）ORDER BY 子句： 用于对结果进行排序。

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

（3）GROUP BY 子句： 用于将结果集按照一个或多个列进行分组。

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

（4）HAVING 子句： 类似于 WHERE 子句，但用于过滤 GROUP BY 的结果

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 1;

（5）JOIN 操作： 用于从多个表中检索相关联的数据。

SELECT orders.OrderID, customers.CustomerName
FROM orders
INNER JOIN customers ON orders.CustomerID = customers.CustomerID;

（6）DISTINCT 关键字： 用于返回唯一不同的值。

SELECT DISTINCT column1, column2, ...
FROM table_name;

（7）INSERT 语句： 用于向表中插入新的数据。

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

（8）UPDATE语句：用于更新表中的数据。

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

（9）DELETE 语句： 用于从表中删除数据。

DELETE FROM table_name
WHERE condition;

2、数据库实例

假设我们有两个简单的表格，一个是 students 表，包含学生的信息，另一个是 courses 表，包含课程的信息。以下是这两个表的结构：
students 表：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    age INT,
    grade VARCHAR(10)
);

INSERT INTO students (student_id, student_name, age, grade)
VALUES
    (1, 'Alice', 20, 'A'),
    (2, 'Bob', 22, 'B'),
    (3, 'Charlie', 21, 'A');

courses 表：

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    credits INT
);

INSERT INTO courses (course_id, course_name, credits)
VALUES
    (101, 'Math', 3),
    (102, 'English', 4),
    (103, 'History', 3);

（1）JOIN 操作：

SELECT students.student_name, courses.course_name
FROM students
INNER JOIN courses ON students.student_id = courses.course_id;

这将选择学生的姓名和他们所选课程的课程名。

（2）GROUP BY 和 HAVING 子句：

SELECT grade, COUNT(*)
FROM students
GROUP BY grade
HAVING COUNT(*) > 1;

这将统计每个成绩等级的学生人数，并筛选出人数大于 1 的成绩等级。

（3）UPDATE 语句：

UPDATE students
SET age = 23
WHERE student_name = 'Bob';

这将把名字为 ‘Bob’ 的学生的年龄更新为 23。

（4）Distinct语句

SELECT DISTINCT grade
FROM students;

DISTINCT 用于仅返回不同的年级值，排除了重复的数据

三、表、视图和存储过程

1、实例：

假设有一个学生信息的数据库，包含一个名为 students 的表：

学生信息表 (students):

+------------+--------------+-----+-------+
| student_id | student_name | age | grade |
+------------+--------------+-----+-------+
|      1     |    Alice     |  20 |   A   |
|      2     |     Bob      |  22 |   B   |
|      3     |   Charlie    |  21 |   A   |
+------------+--------------+-----+-------+

（1）. 表 (Table):
数据表 students 存储了学生的详细信息，包括学生编号 (student_id)、姓名 (student_name)、年龄 (age) 和成绩 (grade)。
通过查询表，可以直接获取所有学生的信息。

（2）. 视图 (View):
假设我们创建一个名为 view_students_A 的视图，用于只显示成绩为 A 的学生信息：

CREATE VIEW view_students_A AS
SELECT * FROM students WHERE grade = 'A';

查询这个视图就相当于查询了只包含成绩为 A 的学生的虚拟表。

（3）. 存储过程 (Stored Procedure):
假设我们创建一个名为 sp_increase_age 的存储过程，用于将所有学生的年龄增加 1：

CREATE PROCEDURE sp_increase_age()
BEGIN
    UPDATE students SET age = age + 1;
END;

调用存储过程 sp_increase_age 将会更新所有学生的年龄。
这样，表存储了实际的学生数据，视图提供了一个过滤或抽象的层次，而存储过程则可以执行一系列的 SQL 操作。在实际应用中，这些数据库对象可以根据需求相互结合使用，以满足不同的业务需求。

2、表、视图、存储过程的区别

（1）区别
表（Table）：
表是数据库中最基本的组成单位，用于存储数据。
表包含行和列，每一行表示表中的一条记录，每一列表示记录中的一个字段。
表定义了数据的结构，包括字段的数据类型、长度等。

视图（View）：
视图是虚拟的表，不包含实际的数据。
视图是基于一个或多个表的查询结果，它是一种虚拟的表，不存储数据，而是根据定义的查询规则动态生成结果。
视图可以简化复杂查询，提高查询的可读性，还能够限制用户对数据的访问，起到了一定的安全性和权限管理的作用。。

存储过程（Stored Procedure）：
存储过程是一段预先编译好并存储在数据库中的一组 SQL 语句。
存储过程可以接受参数，并且可以包含条件判断、循环等控制结构，实现一定的业务逻辑。
存储过程通常用于实现一些复杂的业务逻辑、数据处理或者数据计算，提高数据库的性能和安全性。

（2）存储过程和视图
存储过程具有对数据库进行直接修改的能力，而视图通常不允许直接对其进行修改。

存储过程：
存储过程可以包含一系列的 SQL 语句，包括插入（INSERT）、更新（UPDATE）、删除（DELETE）等操作，用于实现一定的业务逻辑。
存储过程允许在其中编写包含事务控制、条件判断、循环等复杂逻辑的代码。

视图：
视图是一个虚拟表，通常是基于一个或多个实际表的查询结果。
视图的目的是提供一个简化或筛选的数据集，方便用户查询，但它本身不存储实际的数据。
通常情况下，直接对视图进行修改是不被允许的，因为它只是查询结果的一个映射，对视图的修改可能涉及到多个底层表。

四、数据库性能测试

我经历的实战主要是：创建大量数据并模拟真实负载的测试。
主要测试方向：
1、考虑索引和查询复杂性： 在插入大量数据后，模拟系统中常见的查询操作，包括一些复杂的联合查询和过滤条件。通过这些查询，评估数据库在不同场景下的响应性能。

2、模拟并发用户： 使用工具模拟并发用户，模拟医疗信息系统在高并发情况下的运行。观察数据库在并发负载下的响应情况，包括事务处理能力和锁的使用情况。

3、使用 SQL Profiler 进行性能分析： 使用 SQL Profiler 工具捕获数据库的实时查询，了解每个查询的执行计划、消耗资源等情况。这有助于识别潜在的性能问题。

4、定期监测数据库性能指标： 在测试过程中，定期监测数据库的性能指标，包括 CPU 利用率、内存使用情况、磁盘 I/O 等。通过这些指标，可以及时发现潜在问题并进行调整。

五、缺陷定位（SQL Server Profiler）

SQL Profiler是一个图形界面和一组系统存储过程，其作用如下：
(1) 图形化监视SQL Server查询；
(2) 在后台收集查询信息；
(3) 分析性能；
(4) 诊断像死锁之类的问题；
(5) 调试T-SQL语句；
(6) 模拟重放SQL Server活动；
(7) 也可以使用SQL Profiler捕捉在SQL Server实例上执行的活动。这样的活动被称为Profiler跟踪。

应用场景：
在之前的项目中，前端页面通过与后端交互来实现数据的存储和获取。这包括前端页面向后端发送请求，后端处理请求并操作数据库，最后将结果返回给前端页面.
排查问题的方法： 当出现数据存储或检索方面的问题时，可以通过 SQL Server Profiler 工具来帮助定位问题。你可以在工具中启动跟踪，然后操作前端页面，观察是否可以捕获到与数据库相关的 SQL 语句。如果捕获到了 SQL 语句，可以进一步分析执行过程，检查是否有字段不匹配、数据类型错误等问题导致的数据库操作失败。
直接在数据库中执行 SQL 语句： 通过 SQL Server Profiler 捕获到的 SQL 语句，你可以直接在数据库中执行，以验证语法的正确性并观察执行结果。这样可以快速排查出数据库操作中的问题，例如字段不匹配、数据约束违反等。

六、其它面试中常见的问题
1、inner join和left join区别
INNER JOIN 和 LEFT JOIN 是 SQL 中用于合并两个表的方法，它们之间有一些重要的区别：
（1）INNER JOIN 是最常用的连接方式之一。
该操作返回两个表中共有的行，即两个表中连接列的值相等的行。
如果某行在其中一个表中没有匹配到另一个表中的行，则不会包含在结果集中。
（2）LEFT JOIN：
LEFT JOIN 也称为 LEFT OUTER JOIN。
该操作返回左边表（第一个表）的所有行，以及右边表（第二个表）中与左边表匹配的行。如果右边表中没有匹配的行，则结果集中将包含 NULL 值。
举例：
假设我们有两个表：Employees 和 Departments，它们的结构如下：
Employees 表字段如下：

EmployeeID	EmployeeName	DepartmentID
  1	        John	            1
  2	        Alice	            2
  3         Bob

Departments 表：

DepartmentID	DepartmentName
 1	              IT
 2	              HR

使用 INNER JOIN：

SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

结果：

EmployeeName	DepartmentName
John	         IT
Alice	         HR

使用 LEFT JOIN：

SELECT Employees.EmployeeID, Employees.EmployeeName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

在 LEFT JOIN 中，我们会得到左表 Employees 的所有行，不管是否存在匹配的右表 Departments 的行。对于没有匹配的部分，相关的 Departments 列会显示为 NULL。

输出结果：

EmployeeID  EmployeeName  DepartmentName
1           John          IT
2           Alice         HR
3           Bob           NULL

在这个结果集中，Bob 的部门名称显示为 NULL，因为他在 Employees 表中没有部门ID。

2、索引是什么？哪些字段适合做索引？
数据库索引是一种数据结构，用于提高数据库表的检索速度和查询效率，一般在一些频繁用于检索的列、经常用于排序的列、频繁用于连接的列上创建。以下是适合创建索引字段的详细说明：
频繁用于检索的字段： 那些经常用于检索和过滤数据的字段是首选的索引候选字段。这些字段通常是经常出现在 WHERE 子句、JOIN 子句或 ORDER BY 子句中的字段。
唯一性约束的字段： 需要保证数据唯一性的字段（如主键）通常需要创建唯一性索引。这样可以确保数据的完整性。
经常用于排序的字段： 那些经常用于排序操作的字段，尤其是在涉及到 ORDER BY 子句的查询中，可以考虑创建索引。这样可以加速排序操作，提高查询效率。
经常用于连接的字段： 当某个字段经常用于连接操作（JOIN）时，可以考虑在该字段上创建索引。这样可以加速连接操作，提高连接查询的效率。
全文搜索的字段： 需要进行全文搜索的字段通常需要创建全文索引。全文索引可以加速全文搜索操作，支持模糊查询和自然语言搜索。
数据的基数（Cardinality）： 字段的基数是指字段中不同值的数量。基数越高的字段通常更适合创建索引，因为它们提供了更好的过滤效果。
数据类型和长度： 索引的大小直接影响到索引的性能和内存消耗。一般来说，较小的数据类型和长度更适合作为索引。
空值的处理： 如果某个字段允许空值，并且经常需要查询空值或者非空值的数据，可以考虑在该字段上创建索引。
举例：
假设有一个名为 Employees 的表，现在需要从该表中检索特定部门的员工信息。
首先，假设没有任何索引，我们可以执行如下的 SQL 查询：

SELECT *
FROM Employees
WHERE DepartmentID = 3;

如果没有索引，数据库引擎将需要扫描整个 Employees 表来找到所有 DepartmentID 为 3 的员工记录。这对于大型表格来说可能会很耗时，特别是当表格中包含数百万条记录时。
现在，我们在 DepartmentID 列上创建一个索引，命名为 idx_department_id，执行以下操作：

CREATE INDEX idx_department_id ON Employees (DepartmentID);

这样，数据库引擎将会在创建索引时，对 DepartmentID 列进行排序并建立索引表。这个索引表会存储 DepartmentID 的值以及对应的行指针，加快了根据 DepartmentID 进行检索的速度。

接下来，执行相同的 SQL 查询：

SELECT *
FROM Employees
WHERE DepartmentID = 3;

现在，由于有了 DepartmentID 的索引，数据库引擎可以直接定位到索引表中 DepartmentID 为 3 的行指针，然后快速访问到对应的员工记录。这样就大大加快了数据检索的速度，提高了查询效率。