一,索引
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代价
- 在SQL Server中,利用索引进行查询可以提高查询效率,但是也存在一些代价。以下是利用索引查询的代价:
(1)索引维护开销:创建索引会占用存储空间,同时也需要维护索引数据结构。当数据表中的数据发生变化(如插入、更新或删除)时,索引也需要进行相应的维护,这可能会导致额外的开销和资源占用。
(2)索引查询的性能:虽然索引可以提高查询的性能,但是在某些情况下,使用索引可能会降低查询的性能。例如,如果查询条件包含了表中大部分的行,那么使用索引查询可能会比全表扫描更慢。
(3)索引占用的存储空间:索引需要占用存储空间,而且随着数据表的增大,索引的大小也会增加。因此,在设计索引时需要权衡索引的数量和大小,以避免过多的占用存储空间。
(4)索引对写操作的影响:索引不仅会影响查询操作,还会影响写操作。每次插入、更新或删除数据时,都需要维护相应的索引,这可能会导致写操作变慢。
二,表备份
2.1SSMS进行页面操作
- 通过选择“导出数据”向导来备份表:
(1)在 SQL Server Management Studio (SSMS) 中,右键单击需要备份的数据库,选择“任务” -> “导出数据”。
(2)在“选择数据源”对话框中,选择要备份的数据库。
(3)在“选择目标”对话框中,选择“平面文件”作为目标,然后选择保存备份数据的文件夹和文件名。
(4)在“复制数据”对话框中,选择“从表或视图复制数据”,然后选择要备份的表和视图。
(5)在“保存和执行包”对话框中,选择“运行立即”,然后单击“完成”。
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
- 在 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 将返回所有行,包括重复的行。
- 举个例子,假设有两个表 employees 和 managers,它们都有一个名为 name 的列,我们可以使用以下 SQL 语句将这两个表中的所有员工的名字合并到一起:
SELECT name FROM employees
UNION ALL
SELECT name FROM managers
这将返回一个包含所有员工名字的结果集,其中可能会包含重复的名字。
- 如果我们想要去重,可以使用 UNION 操作符:
SELECT name FROM employees
UNION
SELECT name FROM managers
这将返回一个包含所有不重复的员工名字的结果集。
- 必须保证链接的两个表格式相同,拥有相同的字段
四,连接查询
4.1左连接查询
- 在 SQL Server 中,使用左连接可以在两个或多个表之间建立关联,以便返回满足指定条件的结果集。左连接将返回左表中所有行,无论是否有匹配的行在右表中,如果在右表中没有匹配到,则会返回 NULL 值。
- 左连接语法格式:
SELECT [column1], [column2], ...
FROM [table1]
LEFT JOIN [table2]
ON [table1].[column_name] = [table2].[column_name];
在上面的语法中,table1 是左表,table2 是右表,column_name 是两个表之间用来进行匹配的列名,column1,column2 等是需要返回的列名。
- 例如,我们有两个表,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 表中没有对应行的记录也会返回空值。如果要查询所有列,可以使用通配符 *
- 实例:
(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右连接查询
- SQL Server中的右连接(right join)是一种用于连接两个表的SQL查询语句,它返回两个表中所有右表(第二个表)的记录以及匹配的左表(第一个表)中的记录。
- 右连接语法格式:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
其中,table1是左表,table2是右表,column_name是两个表共有的列名,ON关键字指定了连接的条件。
-
右连接的查询结果包括以下内容:
(1)包括右表中的所有记录,左表中没有匹配的记录用 NULL 填充
(2)仅包括左表中与右表匹配的记录 -
右连接通常用于在左表中找到与右表匹配的记录。如果需要返回左表中所有记录以及匹配的右表记录,可以使用左连接(left join)。
4.3内连接查询
- SQL Server中的内连接(inner join)是一种用于连接两个表的SQL查询语句,它返回两个表中都有匹配的记录。(公共部分)
- 内连接语法如下:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
其中,table1和table2是要连接的两个表,column_name是这两个表共有的列名,ON关键字指定了连接的条件
- 内连接的查询结果包括以下内容:
(1)仅包括两个表中都有匹配的记录
(2)内连接通常用于从两个表中获取相关的数据。如果需要返回一个表中的
五,递归查询
5.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排序。