SqlServer数据库-索引，备份，连接查询(第四篇)

一，索引

1.0索引与查询的区别

• 在 SQL Server中，索引和查询是两个不同的概念。索引是一种数据结构，它存储了表中某些列的值和它们在表中对应的行的位置信息，需要新建一个表，相当于存储指针。使得查找数据不需要对整表进行扫描。索引一般采用B树结构（平衡树）
• 查询：会将所有数据查一遍，一行行检索，当数据量很大时查询效率不高，它可以使用索引来优化数据检索的速度。
• 下面是索引和查询的一些区别：
  （1）索引是一种数据结构，它需要在表中创建并维护，而查询是一种 SQL 语句，它由数据库管理系统解释和执行。
  （2）索引可以提高查询的速度，因为它们允许数据库管理系统快速找到符合条件的行。查询本身不会影响数据检索的速度，但查询的效率取决于查询条件和使用的索引。
  （3）索引可以占用较大的存储空间，因为它们需要存储额外的数据结构和位置信息。查询不需要额外的存储空间，但它可以占用较大的 CPU 和内存资源，特别是对于复杂的查询。
  （4）索引可以加速特定类型的查询，如等值查询、范围查询和排序。但对于某些查询，如模糊查询和通配符查询，索引可能无法提高速度，甚至会降低查询效率。查询的优化需要根据具体情况进行，需要综合考虑查询条件、索引类型、表大小和数据分布等因素。

1.1数据页

• 在 SQL Server 中，数据页是磁盘上分配给表和索引的最小单位。每个数据页的大小通常为 8KB（可配置），用于存储表和索引中的数据行。
• 数据页由两部分组成：页头和数据区域。页头包含了页面的元数据信息，如页码、页类型、空闲空间等。数据区域包含了表和索引中的数据行。
• 当 SQL Server 读取表或索引中的数据时，它首先需要读取相应的数据页。如果数据行跨越多个数据页，则需要读取多个数据页。
• 数据页中的空闲空间用于存储新的数据行。当表或索引中的数据行被删除时，相应的空间会被标记为可用空间，以供后续插入操作使用。如果数据页中的空闲空间不足以存储新的数据行，则需要分配新的数据页。
• 在 SQL Server中，可以使用索引来加快数据访问的速度。索引是一种特殊的数据结构，用于快速定位表或索引中的数据行。索引也是由数据页组成的，但它们包含了指向表或索引中实际数据行的指针。

1.2索引的分类

1.2.1聚集索引

• 聚集索引是一种按照列的物理顺序来组织表的索引，即原表中那一行数据在哪个位置，指向他的索引块在索引表中也是在对应位置，即它决定了表中数据的物理存储顺序。每个表只能有一个聚集索引，它常常被用作主键。
  

1.2.2非聚集索引

• 非聚集索引是一种按照列的逻辑顺序来组织表的索引，即它只是记录了数据在表中的逻辑位置。每个表可以有多个非聚集索引，它可以用于优化查询条件中的列或用于排序等操作。
• 

1.2.3B树结构

• B树（B-tree）是一种自平衡的树状数据结构，广泛应用于文件系统和数据库中。它的特点是能够支持高效的插入、查找和删除操作，并且能够在磁盘上进行高效地存储和检索。
• B树的特点在于其每个节点可以包含多个关键字和对应的指针。相比于二叉搜索树，B树的每个节点可以包含更多的关键字，因此可以减少树的高度，从而减少查找、插入和删除操作所需的I/O操作。同时，B树还具有自平衡的特点，能够保证树的高度在一定范围内，从而保证了操作的时间复杂度。
• B树的节点可以分为内部节点和叶子节点。内部节点包含了若干个关键字和对应的指针，指向下一层子节点；而叶子节点则包含了若干个关键字和对应的数据项。B树的每个节点都有一个最小度数（通常记为t），表示节点中至少包含t个关键字。
  

1.3索引的创建

• 语法：

CREATE INDEX index_name
ON table_name (column1, column2, ...);

（1）index_name 是要创建的索引的名称，应该是唯一的；
（2）table_name 是要在其上创建索引的表的名称；
（3）(column1, column2, …) 是要在其上创建索引的列的列表。可以在同一个 CREATE INDEX 语句中创建多个列的索引。

• 例如，要在名为 employees 的表中为 last_name 和 first_name 列创建一个名为 name_idx 的索引，可以使用以下语句：

CREATE INDEX name_idx
ON employees (last_name, first_name);

这将在 employees 表上创建一个名为 name_idx 的索引，该索引包括 last_name 和 first_name 列。

1.3.1实例

--创建非聚集索引表
create index index_name on Student(score1)
--创建唯一聚集索引
create unique clustered index index_sno on Student(sno)

• 总结：当建表是对于某一属性如果定义为主键，会自动建立聚集索引表。聚集索引表只能建立一个，如果需要建立新的聚集索引，需要删除之前建立的索引表。

1.4索引的使用

• 在 SQL Server 中，创建索引后，可以使用 SELECT 语句查询表并利用索引来加速查询。SQL Server 会自动选择最优的索引来执行查询，但也可以使用 WITH (INDEX(index_name)) 语句来指定使用哪个索引。
• 例如，假设我们已经在名为 employees 的表中创建了一个名为 name_idx 的索引，包括 last_name 和 first_name 列。我们可以使用以下语句查询该表：

SELECT * FROM employees
WHERE last_name = 'Smith' AND first_name = 'John';

此查询将使用 name_idx 索引来快速定位满足 last_name = ‘Smith’ 和 first_name = ‘John’ 条件的行。

• 如果想强制使用 name_idx 索引，则可以使用以下语句：

SELECT * FROM employees WITH (INDEX(name_idx))
WHERE last_name = 'Smith' AND first_name = 'John';

此查询将使用 name_idx 索引来执行查询，即使 SQL Server 认为另一个索引更适合此查询。

1.5代价

1. 在SQL Server中，利用索引进行查询可以提高查询效率，但是也存在一些代价。以下是利用索引查询的代价：
   （1）索引维护开销：创建索引会占用存储空间，同时也需要维护索引数据结构。当数据表中的数据发生变化（如插入、更新或删除）时，索引也需要进行相应的维护，这可能会导致额外的开销和资源占用。
   （2）索引查询的性能：虽然索引可以提高查询的性能，但是在某些情况下，使用索引可能会降低查询的性能。例如，如果查询条件包含了表中大部分的行，那么使用索引查询可能会比全表扫描更慢。
   （3）索引占用的存储空间：索引需要占用存储空间，而且随着数据表的增大，索引的大小也会增加。因此，在设计索引时需要权衡索引的数量和大小，以避免过多的占用存储空间。
   （4）索引对写操作的影响：索引不仅会影响查询操作，还会影响写操作。每次插入、更新或删除数据时，都需要维护相应的索引，这可能会导致写操作变慢。

二，表备份

2.1SSMS进行页面操作

1. 通过选择“导出数据”向导来备份表：
   （1）在 SQL Server Management Studio (SSMS) 中，右键单击需要备份的数据库，选择“任务” -> “导出数据”。
   （2）在“选择数据源”对话框中，选择要备份的数据库。
   （3）在“选择目标”对话框中，选择“平面文件”作为目标，然后选择保存备份数据的文件夹和文件名。
   （4）在“复制数据”对话框中，选择“从表或视图复制数据”，然后选择要备份的表和视图。
   （5）在“保存和执行包”对话框中，选择“运行立即”，然后单击“完成”。

2.2命令操作

2. 通过使用 SQL Server Management Studio (SSMS) 来执行 T-SQL 命令来备份表

SELECT *
INTO backup_table
FROM original_table

其中，“backup_table”是你要备份到的表的名称，“original_table”是你要备份的表的名称。
3. 实例：备份CourseTable表

--备份
select * INTO NewCourseTable  from CourseTable ct 

起初并没有NewCourseTable表:

三，union 和union all

1. 在 SQL Server 中，UNION 和 UNION ALL 是用于合并两个或多个 SELECT 语句的操作符。它们的区别在于：
   （1）UNION 操作符会返回所有不重复的行，即会去重。
   （2）UNION ALL 操作符会返回所有行，包括重复的行。

SELECT column1, column2, ... FROM table1
UNION [ALL]
SELECT column1, column2, ... FROM table2

其中，column1, column2, … 是要选择的列的名称，table1 和 table2 是要选择的表的名称。 如果使用 UNION 操作符，则 SQL Server 将返回所有不重复的行。如果使用 UNION ALL 操作符，则 SQL Server 将返回所有行，包括重复的行。

2. 举个例子，假设有两个表 employees 和 managers，它们都有一个名为 name 的列，我们可以使用以下 SQL 语句将这两个表中的所有员工的名字合并到一起：

SELECT name FROM employees
UNION ALL
SELECT name FROM managers

这将返回一个包含所有员工名字的结果集，其中可能会包含重复的名字。

3. 如果我们想要去重，可以使用 UNION 操作符：

SELECT name FROM employees
UNION
SELECT name FROM managers

这将返回一个包含所有不重复的员工名字的结果集。

4. 必须保证链接的两个表格式相同，拥有相同的字段

四，连接查询

4.1左连接查询

1. 在 SQL Server 中，使用左连接可以在两个或多个表之间建立关联，以便返回满足指定条件的结果集。左连接将返回左表中所有行，无论是否有匹配的行在右表中，如果在右表中没有匹配到，则会返回 NULL 值。
   
2. 左连接语法格式：

SELECT [column1], [column2], ...
FROM [table1]
LEFT JOIN [table2]
ON [table1].[column_name] = [table2].[column_name];

在上面的语法中，table1 是左表，table2 是右表，column_name 是两个表之间用来进行匹配的列名，column1，column2 等是需要返回的列名。

3. 例如，我们有两个表，TableA 和 TableB，其中 TableA 包含 ID、Column1 和 Column2，TableB 包含 ID、Column3 和 Column4，我们想要从这两个表中返回 Column1 和 Column3 的值，同时按照 TableA 的 ID 列与 TableB 的 ID 列进行匹配，则可以使用以下 SQL 语句：

SELECT TableA.Column1, TableB.Column3
FROM TableA
LEFT JOIN TableB
ON TableA.ID = TableB.ID;

这个查询将会返回 TableA 表中的所有行，并且对于那些在 TableB 表中没有对应行的记录也会返回空值。如果要查询所有列，可以使用通配符 *

4. 实例：
   （1）建表：

CREATE TABLE TableA (
  ID INT PRIMARY KEY,
  Column1 VARCHAR(50),
  Column2 INT
);
CREATE TABLE TableB (
  ID INT PRIMARY KEY,
  Column3 VARCHAR(50),
  Column4 INT
);

（2）插入数据：

INSERT INTO TableA (ID, Column1, Column2)
VALUES (1, 'Value 1', 10),
       (2, 'Value 2', 20),
       (3, 'Value 3', 30);
INSERT INTO TableB (ID, Column3, Column4)
VALUES (1, 'Value A', 100),
       (3, 'Value C', 300),
       (4, 'Value D', 400);

（3）左连接查询：

SELECT *
FROM TableA
LEFT JOIN TableB
ON TableA.ID = TableB.ID

TableA:

TableB:
左连接查询：

4.2右连接查询

1. SQL Server中的右连接（right join）是一种用于连接两个表的SQL查询语句，它返回两个表中所有右表（第二个表）的记录以及匹配的左表（第一个表）中的记录。
2. 右连接语法格式：

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

其中，table1是左表，table2是右表，column_name是两个表共有的列名，ON关键字指定了连接的条件。

3. 右连接的查询结果包括以下内容：
   （1）包括右表中的所有记录，左表中没有匹配的记录用 NULL 填充
   （2）仅包括左表中与右表匹配的记录

4. 右连接通常用于在左表中找到与右表匹配的记录。如果需要返回左表中所有记录以及匹配的右表记录，可以使用左连接（left join）。

4.3内连接查询

5. SQL Server中的内连接（inner join）是一种用于连接两个表的SQL查询语句，它返回两个表中都有匹配的记录。（公共部分）
6. 内连接语法如下：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

其中，table1和table2是要连接的两个表，column_name是这两个表共有的列名，ON关键字指定了连接的条件

7. 内连接的查询结果包括以下内容：
   （1）仅包括两个表中都有匹配的记录
   （2）内连接通常用于从两个表中获取相关的数据。如果需要返回一个表中的

五，递归查询

5.1递归实例

1. 在SqlServer中进行递归查询需要使用Common Table Expression（CTE）和递归查询语句。
   以下是一个简单的示例，展示如何使用CTE和递归查询在一个表中查找所有的上级。
   假设有一个名为Employees的表，其中包含员工的ID和其上级的ID。下面是一个简单的表格：
   

WITH EmployeeHierarchy (EmployeeID, ManagerID, Level)
AS
(
   SELECT EmployeeID, ManagerID, 0
   FROM Employees
   WHERE ManagerID IS NULL

   UNION ALL

   SELECT e.EmployeeID, e.ManagerID, Level + 1
   FROM Employees e
   JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, ManagerID, Level
FROM EmployeeHierarchy
ORDER BY Level, EmployeeID

这个查询首先使用一个SELECT语句选择根级别的员工，即ManagerID为NULL的员工，然后使用UNION ALL语句和另一个SELECT语句将查询与自身连接，从而实现递归查询。

CTE中的第一个SELECT语句选择根级别的员工，并设置初始Level为0。第二个SELECT语句将根据EmployeeHierarchy中已有的记录选择下一级员工，并将其Level加1。这个过程将继续重复，直到所有的员工都被遍历为止。

最后，查询将返回每个员工的ID、其上级的ID和它们之间的层次级别，按级别和员工ID排序。