明月看潮生

明月看潮生

关注
发信
关注(28)粉丝(399)

青少年编程与数学 02-002 Sql Server 数据库应用 07课题、表的操作

课题摘要:

本课题介绍了SQL Server中表的操作，包括表的基本概念、创建表、列的命名规则、数据类型选择以及表定义示例。表是数据库中存储数据的基本结构，由行和列组成，具有结构化数据、列定义、主键、外键、索引和约束等特征。创建表时使用CREATE TABLE语句，定义表名、列名、数据类型和约束。列名应遵循长度、字符和大小写规则，避免使用保留关键字。数据类型选择应考虑数值、字符、日期时间、二进制数据的特性，以及性能、存储空间、数据完整性和国际化需求。合适的数据类型有助于保证数据库性能和数据准确性。最后，通过一个包含不同数据类型的表定义示例，展示了如何应用这些概念和规则。

一、数据库表（Table）

在 SQL Server 中，数据库的表（Table）是一种基本的数据结构，用于存储数据。表由行（Row）和列（Column）组成，每行代表一个数据记录，每列代表记录的一个属性或字段。以下是数据库表的一些基本特征：

  1. 结构化数据：表中的数据是结构化的，每列都有特定的数据类型，如整数、浮点数、字符串、日期等。

  2. 列定义：表的列定义了数据的类型和属性，例如是否允许为空、是否是主键、是否唯一等。

  3. 行和记录：表中的每行代表一个记录，记录是列中数据的集合。

  4. 主键：表中的一个或多个列可以被定义为主键（Primary Key），它唯一地标识表中的每条记录。

  5. 外键：表可以包含外键（Foreign Key），它指向另一个表的主键，用于建立两个表之间的关系。

  6. 索引：为了提高数据检索的效率，可以在表的列上创建索引。

  7. 约束：表可以有各种约束（Constraints），如唯一性约束、检查约束、默认值约束等，以确保数据的完整性和准确性。

  8. 视图：视图（View）是基于 SQL 语句的结果集的可视化表现，它不存储数据，而是提供了一种方式来查询和操作表中的数据。

  9. 存储过程和触发器：存储过程（Stored Procedure）和触发器（Trigger）是数据库中的程序对象，它们可以对表中的数据执行复杂的操作。

  10. 数据操作：可以通过 SQL 语句对表中的数据进行插入（INSERT）、查询（SELECT）、更新（UPDATE）和删除（DELETE）等操作。

数据库表是关系数据库管理系统（RDBMS）中存储和管理数据的核心组件，SQL Server 作为 RDBMS 的一种，提供了强大的工具和功能来创建和管理这些表。

二、创建表

在 SQL Server 中创建一个新的数据库表，你可以使用 CREATE TABLE 语句。这个语句允许你定义表的名称、列名、数据类型以及其他约束（如主键、外键、索引等）。下面是一个基本的 CREATE TABLE 语句的示例：

CREATE TABLE [dbo].[TableName]
(
    [Column1] [DataType] NOT NULL,
    [Column2] [DataType] NULL,
    [Column3] [DataType] NULL,
    CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED 
    (
        [Column1] ASC
    )
);

在这个例子中：

  • [dbo] 是模式名，通常用来表示数据库中的一个命名空间。
  • [TableName] 是你想要创建的表的名称。
  • [Column1]、[Column2]、[Column3] 是表中的列名。
  • [DataType] 是列的数据类型，例如 INT、VARCHAR(50)、DATETIME 等。
  • NOT NULL 表示该列不能存储 NULL 值。
  • NULL 表示该列可以存储 NULL 值。
  • CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED 定义了一个主键约束，PK_TableName 是约束的名称，PRIMARY KEY 表示这是一个主键约束，CLUSTERED 表示这是一个聚集索引。

下面是一个具体的例子，创建一个名为 Employees 的表，包含 EmployeeID、FirstName、LastName 和 HireDate 四个列：

CREATE TABLE [dbo].[Employees]
(
    [EmployeeID] INT NOT NULL,
    [FirstName] NVARCHAR(50) NULL,
    [LastName] NVARCHAR(50) NULL,
    [HireDate] DATETIME NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
    (
        [EmployeeID] ASC
    )
);

在这个例子中，EmployeeID 被设置为主键，这意味着每个 EmployeeID 的值必须是唯一的，并且不能为 NULL。

创建表后，你可以使用 INSERT 语句向表中添加数据，使用 SELECT 语句查询数据，使用 UPDATE 语句更新数据，以及使用 DELETE 语句删除数据。

三、列（字段）的命名规则

在 SQL Server 中，表的列名遵循以下规则：

  1. 长度限制：列名的最大长度为 128 个字符。

  2. 字符限制：列名可以包含字母、数字和一些特殊字符，但不能包含以下字符：

    • 空格 ( )
    • SQL Server 保留关键字，如 SELECT、INSERT、DELETE 等。
    • 以下特殊字符：~ ! @ # $ % ^ & * ( ) - + = { } [ ] \ | ; : ' , . / ? > <

  3. 大小写敏感性：SQL Server 默认情况下是不区分大小写的，但可以通过设置数据库的 COLLATE 属性来使其区分大小写。

  4. 保留关键字：避免使用 SQL Server 的保留关键字作为列名，如 DATE、TABLE、INDEX 等。如果必须使用这些关键字作为列名，需要用方括号 [ ] 将它们括起来，例如 [DATE]。

  5. 使用方括号：如果列名包含特殊字符或保留关键字，或者你希望列名区分大小写，可以使用方括号将列名括起来，例如 [MyColumn]。

  6. 避免使用空格：虽然可以使用空格作为列名的一部分，但通常不建议这样做，因为它会使 SQL 查询变得更加复杂，需要在列名周围使用反引号或方括号。

  7. 避免使用数字开头：列名不应该以数字开头。

  8. 避免使用保留字：虽然可以使用保留字作为列名，但最好避免这样做，以减少混淆和潜在的错误。

  9. 使用有意义的名称：列名应该清晰、简洁并能够描述列中存储的数据类型或内容。

  10. 避免使用缩写：除非缩写在上下文中非常明确，否则最好使用完整的单词作为列名。

  11. 避免使用SQL Server的函数名：避免使用SQL Server的函数名作为列名，如 DATE、ID、NAME 等，因为这些名称可能会与SQL Server的内置函数冲突。

遵循这些规则可以帮助你创建清晰、易于理解和维护的数据库表结构。

四、列的数据类型

在 SQL Server 中，列的数据类型决定了可以存储的数据类型以及数据的大小和精度。以下是一些常用的数据类型及其规则：

  1. 数值类型：

    • BIT：存储二进制值，0 或 1。
    • TINYINT：存储 8 位无符号整数，范围从 0 到 255。
    • SMALLINT：存储 16 位整数，范围从 -32,768 到 32,767。
    • INT：存储 32 位整数，范围从 -2,147,483,648 到 2,147,483,647。
    • BIGINT：存储 64 位整数，范围从 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807。
    • DECIMAL 或 NUMERIC：存储固定精度和比例的数值，范围取决于定义的精度和小数位数。
    • FLOAT：存储浮点数值，精度取决于定义的位数。
    • REAL：存储单精度浮点数值。

  2. 字符类型：

    • CHAR：固定长度的非 Unicode 字符数据，长度范围从 1 到 8,000 字符。
    • VARCHAR：可变长度的非 Unicode 字符数据，最大长度为 8,000 字符。
    • TEXT：可变长度的非 Unicode 字符数据，最大长度为 2^31-1 字节（约 2GB）。
    • NCHAR：固定长度的 Unicode 字符数据，长度范围从 1 到 4,000 字符。
    • NVARCHAR：可变长度的 Unicode 字符数据，最大长度为 4,000 字符。
    • NTEXT：可变长度的 Unicode 字符数据，最大长度为 2^30-1 字节（约 1GB）。

  3. 日期和时间类型：

    • DATE：存储日期，范围从 0001-01-01 到 9999-12-31。
    • TIME：存储时间，范围从 00:00:00.0000000 到 23:59:59.9999999，精度为 100 纳秒。
    • DATETIME：存储日期和时间，范围从 1753-01-01 到 9999-12-31 23:59:59.997，精度为 3.33 毫秒。
    • DATETIME2：存储日期和时间，精度和范围取决于定义的精度，最大精度为 100 纳秒。
    • DATETIMEOFFSET：存储日期和时间，带有时区偏移量，精度和范围取决于定义的精度，最大精度为 100 纳秒。

  4. 二进制数据类型：

    • BINARY：固定长度的二进制数据，长度范围从 1 到 8,000 字节。
    • VARBINARY：可变长度的二进制数据，最大长度为 8,000 字节。
    • IMAGE：可变长度的二进制数据，最大长度为 2^31-1 字节（约 2GB）。

  5. 其他数据类型：

    • MONEY 和 SMALLMONEY：存储货币值，精度分别为小数点后四位和两位。
    • UNIQUEIDENTIFIER：存储一个 16 字节的唯一标识符，通常用于 GUID。
    • XML：存储 XML 数据。
    • CURSOR：存储游标。
    • ROWSET：存储行集。
    • TABLE：存储表变量。

  6. 用户定义的数据类型：

    • 用户可以创建自己的数据类型，基于现有的系统数据类型。

  7. 空间数据类型：

    • GEOMETRY 和 GEOGRAPHY：存储地理空间数据。

选择正确的数据类型对于数据库性能、存储效率和数据完整性至关重要。在设计数据库时，应根据数据的特性和需求来选择最合适的数据类型。

五、选择合适的数据类型

在 SQL Server 中为不同类型的数据选择合适的数据类型，需要考虑数据的特性、范围、精度、存储需求和性能要求。以下是一些指导原则：

  1. 数值数据：

    • 如果数据是整数且范围在 -2,147,483,648 到 2,147,483,647 之间，使用 INT。
    • 对于较小的整数，可以使用 SMALLINT 或 TINYINT 以节省空间。
    • 对于非常大的整数，使用 BIGINT。
    • 对于精确的十进制数，使用 DECIMAL 或 NUMERIC，并指定所需的精度和小数位数。
    • 对于浮点数，使用 FLOAT 或 REAL，但要注意精度损失。

  2. 字符数据：

    • 对于固定长度的字符数据，使用 CHAR。
    • 对于可变长度的字符数据，使用 VARCHAR，并指定一个合理的最大长度。
    • 对于非常大的文本数据，使用 TEXT 或 NTEXT（Unicode）。

  3. 日期和时间数据：

    • 对于仅存储日期的数据，使用 DATE。
    • 对于需要时间精度的数据，使用 DATETIME 或 DATETIME2（后者提供更精确的时间）。
    • 对于需要时区信息的数据，使用 DATETIMEOFFSET。
    • 对于存储时间的数据，使用 TIME。

  4. 二进制数据：

    • 对于固定长度的二进制数据，使用 BINARY。
    • 对于可变长度的二进制数据，使用 VARBINARY。
    • 对于非常大的二进制数据，使用 IMAGE。

  5. 货币数据：

    • 对于货币值，使用 MONEY 或 SMALLMONEY。

  6. 布尔数据：

    • SQL Server 没有专门的布尔数据类型，但可以使用 BIT 并设置 0 和 1 来表示 FALSE 和 TRUE。

  7. 唯一标识符：

    • 对于需要唯一标识符的列，使用 UNIQUEIDENTIFIER。

  8. XML 数据：

    • 对于存储 XML 文档的列，使用 XML 类型。

  9. 空间数据：

    • 对于地理空间数据，使用 GEOMETRY 或 GEOGRAPHY 类型。

  10. 性能考虑：

    • 选择数据类型时，考虑查询性能。例如，使用 CHAR 而不是 VARCHAR 可以提高某些类型的查询性能，因为 CHAR 类型的数据是固定长度的，易于索引。
    • 避免使用过大的数据类型，如 TEXT 或 IMAGE，因为它们可能会影响性能和存储效率。

  11. 存储空间：

    • 考虑存储空间的需求。例如，使用 SMALLINT 而不是 INT 可以减少存储空间的使用。

  12. 数据完整性：

    • 使用约束（如 NOT NULL、CHECK、PRIMARY KEY、FOREIGN KEY 等）来确保数据的完整性。

  13. 国际化和本地化：

    • 如果需要支持多语言，使用 Unicode 数据类型（如 NVARCHAR 和 NTEXT）。

  14. 兼容性：

    • 考虑与其他系统或应用程序的兼容性，确保数据类型在不同系统间可以正确转换。

通过综合考虑这些因素，你可以为 SQL Server 数据库中的列选择最合适的数据类型。

六、表定义示例

下面是一个示例，展示了如何为一个包含不同数据类型的表定义其结构：

CREATE TABLE ProductCatalog (
    ProductID INT PRIMARY KEY, -- 主键，整数类型
    ProductName NVARCHAR(100) NOT NULL, -- 产品名称，可变长度的Unicode字符数据
    ProductDescription NVARCHAR(MAX), -- 产品描述，最大长度的Unicode字符数据
    Price DECIMAL(10, 2) NOT NULL, -- 价格，十进制类型，最多8位数字，2位小数
    StockQuantity INT, -- 库存数量，整数类型
    IsDiscontinued BIT, -- 是否已停产，二进制类型，0或1
    ProductImage VARBINARY(MAX), -- 产品图片，可变长度的二进制数据
    CreatedDate DATETIME2 NOT NULL DEFAULT GETDATE(), -- 创建日期，高精度日期和时间类型，默认为当前时间
    ModifiedDate DATETIME2 NOT NULL DEFAULT GETDATE(), -- 修改日期，高精度日期和时间类型，默认为当前时间
    ShelfLife MONTH -- 保质期，以月为单位，整数类型
);

在这个示例中，ProductCatalog 表包含以下字段：

  • ProductID：一个整数字段，作为表的主键。
  • ProductName：一个可变长度的 Unicode 字符字段，用于存储产品名称，不允许为空。
  • ProductDescription：一个可变长度的 Unicode 字符字段，用于存储产品描述，可以为空。
  • Price：一个十进制字段，用于存储产品价格，不允许为空，最多有8位整数和2位小数。
  • StockQuantity：一个整数字段，用于存储库存数量。
  • IsDiscontinued：一个二进制字段，用于指示产品是否已停产，0表示未停产，1表示已停产。
  • ProductImage：一个可变长度的二进制字段，用于存储产品图片。
  • CreatedDate 和 ModifiedDate：两个高精度日期和时间字段，用于存储记录的创建和修改时间，默认值为当前时间。
  • ShelfLife：一个整数字段，用于存储产品的保质期，以月为单位。

请注意，这个表的定义只是一个示例，实际应用中可能需要根据具体需求调整字段的数据类型和约束。此外，GETDATE() 是 SQL Server 中的一个函数，用于获取当前日期和时间。在创建表时，可以为默认值使用这个函数。

10-27 15:37
Powered by LMLPHP ©2024 明月看潮生 0.001337