SQL server数据库从入门到精通

SQL Server 技术指南：从基础到高级实践

引言： 精通 SQL 的核心在于深入理解数据模型、表间关系，并运用结构化查询语言进行高效、精确的数据操作。本指南旨在提供一个从基础到高级的系统化学习路径，覆盖 SQL Server 的核心功能与最佳实践。

如果觉得本文对您有所帮助，点个赞和关注吧，你的支持就是我持续更新的最大动力。谢谢！！！！

语法格式说明

在本指南的语法格式模板中，您会看到一些特殊符号，它们的含义如下：

• [] (方括号): 表示方括号内的子句、关键字或参数是可选的。在实际编写 SQL 时，您可以根据需要选择是否包含这部分内容。
• | (竖线): 表示在多个选项之间必须选择一个。例如，ASC | DESC 意味着您必须选择 ASC (升序) 或 DESC (降序) 中的一个。
• ... (省略号): 表示前面的项可以重复多次。例如，[ColumnName1], [ColumnName2], ... 意味着您可以列出一个或多个列。

第一部分：数据定义语言 (DDL)

DDL (Data Definition Language) 用于定义和管理数据库对象的结构，例如数据库、表、视图等。

1. 数据库操作 (Database Operations)

1.1 创建数据库 (CREATE DATABASE)

• 功能： 创建一个用于存储数据和对象的逻辑容器。
• 语法格式（请先看第一部分前的语法格式说明，方便理解语法格式,后续都是此语法格式）：

  CREATE DATABASE database_name
  [ ON PRIMARY
      ( NAME = logical_file_name,
        FILENAME = 'os_file_name'
        [ , SIZE = size ]
        [ , MAXSIZE = { max_size | UNLIMITED } ]
        [ , FILEGROWTH = growth_increment ] )
  ]
  [ LOG ON
      ( NAME = logical_log_file_name,
        FILENAME = 'os_log_file_name'
        [ , SIZE = size ]
        [ , MAXSIZE = { max_size | UNLIMITED } ]
        [ , FILEGROWTH = growth_increment ] )
  ];
  

• 示例 (简化语法)：

  CREATE DATABASE MyBusiness;
  

• 示例 (详细语法)：

  CREATE DATABASE MyBusiness
  ON PRIMARY (
      NAME = N'MyBusiness_Data',
      FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyBusiness_Data.mdf',
      SIZE = 100MB,
      MAXSIZE = 500MB,
      FILEGROWTH = 50MB
  )
  LOG ON (
      NAME = N'MyBusiness_Log',
      FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyBusiness_Log.ldf',
      SIZE = 50MB,
      MAXSIZE = 200MB,
      FILEGROWTH = 10%
  );
  

1.2 切换与删除数据库 (USE & DROP)

• USE: 将当前会话的数据库上下文切换到指定数据库。
  • 语法格式： USE [DatabaseName];
• DROP DATABASE: 永久性删除一个或多个数据库及其所有对象和数据。此操作不可恢复。
  • 语法格式： DROP DATABASE [DatabaseName1] [, DatabaseName2, ...];
  • 示例： DROP DATABASE MyBusiness;

2. 表结构操作 (Table Structure Operations)

2.1 创建表 (CREATE TABLE)

• 功能： 定义数据实体的结构，包括列、数据类型和约束。
• 语法格式：

  CREATE TABLE [TableName] (
      [ColumnName1] [DataType] [Column_Constraints],
      [ColumnName2] [DataType] [Column_Constraints],
      ...
      [Table_Constraints]
  );
  

• 示例：

  CREATE TABLE Departments (
      DepartmentID   INT PRIMARY KEY IDENTITY(1,1),
      DepartmentName NVARCHAR(100) NOT NULL UNIQUE
  );
  

2.2 修改表 (ALTER TABLE)

• 功能： 修改已存在表的结构。
• 语法格式 (常用操作)：

  -- 添加列
  ALTER TABLE [TableName] ADD [ColumnName] [DataType] [Constraints];
  
  -- 修改列定义
  ALTER TABLE [TableName] ALTER COLUMN [ColumnName] [NewDataType] [NULL | NOT NULL];
  
  -- 删除列
  ALTER TABLE [TableName] DROP COLUMN [ColumnName];
  

• 示例：

  ALTER TABLE Employees ADD IsActive BIT NOT NULL DEFAULT 1;
  

2.3 删除与清空表 (DROP & TRUNCATE)

• DROP TABLE: 永久删除一个或多个表结构及其所有数据。
  • 语法格式： DROP TABLE [TableName1] [, TableName2, ...];
• TRUNCATE TABLE: 高效删除表中的所有行，但保留表结构。
  • 语法格式： TRUNCATE TABLE [TableName];

第二部分：数据操作语言 (DML)

DML (Data Manipulation Language) 用于查询和操作数据库中的数据。

1. 插入数据 (INSERT INTO)

• 功能： 向表中添加新行。
• 语法格式：

  INSERT INTO [TableName] [ (Column1, Column2, ...) ]
  VALUES (Value1, Value2, ...);
  

• 示例：

  INSERT INTO Employees (FirstName, LastName, Email)
  VALUES (N'伟', N'张', 'wei.zhang@example.com');
  

2. 更新数据 (UPDATE)

• 功能： 修改表中的现有记录。WHERE 子句至关重要。
• 语法格式：

  UPDATE [TableName]
  SET [Column1] = [Value1], [Column2] = [Value2], ...
  [ WHERE [Condition] ];
  

• 示例：

  UPDATE Employees
  SET Salary = 16000.00
  WHERE Email = 'wei.zhang@example.com';
  

3. 删除数据 (DELETE FROM)

• 功能： 从表中移除一行或多行。WHERE 子句至关重要。
• 语法格式：

  DELETE FROM [TableName]
  [ WHERE [Condition] ];
  

• 示例：

  DELETE FROM Employees
  WHERE Email = 'qiang.li@example.com';
  

第三部分：数据查询语言 (DQL)

DQL (Data Query Language)，主要通过 SELECT 语句从数据库中检索数据。

1. SELECT 语句基本结构

• 功能： 从数据库检索数据，形成结果集。
• 语法格式 (常见子句)：

  SELECT [DISTINCT] [TOP (n) [PERCENT]] select_list
  FROM [Table1_Name]
  [ JOIN_type JOIN [Table2_Name] ON join_condition ]
  [ WHERE search_condition ]
  [ GROUP BY group_by_expression ]
  [ HAVING search_condition ]
  [ ORDER BY order_expression [ASC | DESC] ];
  

2. 连接查询 (JOINs)

• 功能： JOIN 子句是关系数据库的核心，它用于根据两个或多个表中的相关列，将这些表中的行组合起来。

• 主要类型及解释：

  • INNER JOIN (内连接)

    • 作用： 只返回两个表中联接键（ON子句中指定的列）相匹配的行。
    • 示例： 查询所有已分配部门的员工及其部门名称。

      SELECT e.FirstName, e.LastName, d.DepartmentName
      FROM Employees AS e
      INNER JOIN Departments AS d ON e.DepartmentID = d.DepartmentID;
      

  • LEFT JOIN (或 LEFT OUTER JOIN，左外连接)

    • 作用： 返回左表（FROM子句后的第一个表）的所有行，以及右表中与左表匹配的行。如果右表中没有匹配项，则结果集中的右表列将包含 NULL。
    • 示例： 查询所有员工，无论他们是否有部门。

      SELECT e.FirstName, e.LastName, d.DepartmentName
      FROM Employees AS e
      LEFT JOIN Departments AS d ON e.DepartmentID = d.DepartmentID;
      

  • RIGHT JOIN (或 RIGHT OUTER JOIN，右外连接)

    • 作用： 与 LEFT JOIN 相反。返回右表的所有行，以及左表中与右表匹配的行。如果左表中没有匹配项，则左表列为 NULL。
    • 示例： 查询所有部门，以及这些部门下的员工。没有员工的部门也会被列出。

      SELECT e.FirstName, d.DepartmentName
      FROM Employees AS e
      RIGHT JOIN Departments AS d ON e.DepartmentID = d.DepartmentID;
      

  • FULL OUTER JOIN (全外连接)

    • 作用： 返回左表和右表中的所有行。当某行在一个表中有匹配时，显示匹配结果；如果没有匹配，则缺失的另一方表的列将为 NULL。
    • 示例： 列出所有员工和所有部门，显示员工与部门的分配关系，包括没有部门的员工和没有员工的部门。

      SELECT e.FirstName, d.DepartmentName
      FROM Employees AS e
      FULL OUTER JOIN Departments AS d ON e.DepartmentID = d.DepartmentID;
      

• CROSS JOIN (交叉连接)

  • 作用： 返回两个表的笛卡尔积，即左表中的每一行与右表中的每一行进行组合。结果行数是两表行数的乘积。通常需谨慎使用，因为它可能产生巨大的结果集。

3. 聚合与分组 (Aggregate & GROUP BY)

• 功能： GROUP BY 将具有相同值的行分为若干组，聚合函数（如 COUNT, AVG）对每个分组执行计算。HAVING 子句用于筛选分组后的结果。
• 示例：

  -- 计算各部门的员工总数和平均薪水，并只显示平均薪水超过10000的部门
  SELECT
      d.DepartmentName,
      COUNT(e.EmployeeID) AS NumberOfEmployees,
      AVG(e.Salary) AS AverageSalary
  FROM
      Employees AS e
  INNER JOIN
      Departments AS d ON e.DepartmentID = d.DepartmentID
  GROUP BY
      d.DepartmentName
  HAVING
      AVG(e.Salary) > 10000;
  

第四部分：高级主题与数据库对象

1. 公用表表达式 (CTEs)

• 功能： 使用 WITH 关键字定义的临时命名结果集，提高复杂查询的可读性。
• 语法格式：

  WITH [CTE_Name] [ (Column1, Column2, ...) ]
  AS (
      -- CTE 定义查询
      SELECT ...
  )
  -- 使用 CTE 的主查询
  SELECT ... FROM [CTE_Name] ...;
  

• 示例：

  WITH AvgSalaryCTE AS (
      SELECT AVG(Salary) AS GlobalAvgSalary FROM Employees
  )
  SELECT e.FirstName, e.Salary
  FROM Employees AS e, AvgSalaryCTE
  WHERE e.Salary > AvgSalaryCTE.GlobalAvgSalary;
  

2. 窗口函数 (Window Functions)

• 功能： 对与当前行相关的一组行（窗口）执行计算，而不折叠行。
• 语法格式：

  <Window_Function>() OVER (
      [ PARTITION BY partition_expression, ... ]
      [ ORDER BY sort_expression [ASC|DESC], ... ]
  )
  

• 示例：

  SELECT
      FirstName,
      Salary,
      d.DepartmentName,
      RANK() OVER (PARTITION BY e.DepartmentID ORDER BY Salary DESC) AS SalaryRankInDept
  FROM
      Employees AS e
  JOIN
      Departments AS d ON e.DepartmentID = d.DepartmentID;
  

3. 视图 (Views)

• 功能： 基于 SELECT 查询结果集的虚拟表。
• 语法格式：

  CREATE VIEW [ViewName] AS
  SELECT [Column1], [Column2], ...
  FROM [TableName]
  [ WHERE [Condition] ];
  

• 示例：

  CREATE VIEW V_EmployeeDetails AS
  SELECT e.FirstName, d.DepartmentName
  FROM Employees AS e LEFT JOIN Departments AS d ON e.DepartmentID = d.DepartmentID;
  

4. 存储过程 (Stored Procedures)

• 功能： 预编译并存储在数据库中的一组 T-SQL 语句。
• 语法格式：

  CREATE PROCEDURE [ProcedureName]
      @[Parameter1] [DataType] [= DefaultValue],
      ...
  AS
  BEGIN
      SET NOCOUNT ON;
      -- SQL 语句逻辑
  END;
  GO
  

• 示例：

  CREATE PROCEDURE usp_GetEmployeesByDepartment @DeptName NVARCHAR(100)
  AS
  BEGIN
      SELECT e.EmployeeID, e.FirstName, e.Salary
      FROM Employees AS e
      JOIN Departments AS d ON e.DepartmentID = d.DepartmentID
      WHERE d.DepartmentName = @DeptName;
  END;
  GO
  

5. 事务控制 (Transaction Control)

• 功能： 将一系列操作捆绑为一个原子单元，确保数据一致性。
• 语法结构 (结合错误处理)：

  BEGIN TRANSACTION;
  BEGIN TRY
      -- DML 操作 1
      -- DML 操作 2
      COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
      IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION;
  END CATCH;
  

• 示例：

  BEGIN TRANSACTION;
  BEGIN TRY
      DELETE FROM Employees WHERE EmployeeID = 3;
      UPDATE Employees SET Salary = Salary + 5000 WHERE EmployeeID = 1;
      COMMIT TRANSACTION;
      PRINT '事务成功提交。';
  END TRY
  BEGIN CATCH
      ROLLBACK TRANSACTION;
      PRINT '事务已回滚，操作失败。';
  END CATCH;
  

结语： 本文系统地介绍了 SQL Server 的各项核心技术，并为每个命令提供了标准的语法格式及约定说明。理论知识是基础，而解决实际业务场景中的数据问题是提升技能的关键。建议将这些概念应用于实际项目中，通过不断实践、调试和优化，深化理解并最终熟练掌握 SQL Server。
如果觉得本文对您有所帮助，点个赞和关注吧，你的支持就是我持续更新的最大动力。谢谢！！！！