操作数据表

一、数据表基础

1、基本数据类型

1.1、Character 字符串：

1.2、Unicode 字符串：

1.3、Binary 类型：

1.4、Number 类型：

1.5、Date 类型：

1.6、其他数据类型：

2、用户自定义数据类型

• 在SQL Server Management Studio的对象资源浏览器中，数据库->选择指定数据库->可编程性->类型->用户自定义数据类型，选中“用户自定义数据类型”右键在弹出的菜单中选中“新建用户定义的数据类型”，在打开的窗口中设置用户自定义数据类型的名称、依据的系统数据类型以及是否允许NULL值等。

• 在sql脚本中使用sp_addtype创建用户自定义数据类型，语法如下：

  sp_addtype[@typename=]type,[@phystype=]system_data_type[,[@nulltype=]'null_type'][,[@owner=]'owner_name']
  参数说明：
  [@typename=]type：指定待创建的用户自定义数据类型的名称，用户自定义数据类型名称必须遵守标识符的命名规则，而且在数据库唯一。
  [@phystype=]system_data_type：指定用户定义数据类型所依赖的系统数据类型。
  [@nulltype=]'null_type'：指定用户定义数据类型的可空属性，即用户自定义数据类型处理空值的方式，取值为NULL、NOT NULL或NONULL。
  例：
  use TEST
  EXEC sp_addtype 'postalcode','char(10)','not null','null'在sql脚本中使用sp_addtype创建用户自定义数据类型，语法如下：
  

• 根据需要，可以使用系统储存过程sp_droptype从systypes中删除别名数据类型

3、数据表完整性

• 表列中除了具有数据类型和大小属性之外，还有其他属性。其他属性是保证数据完整性和表的引用完整性的重要部分。
• 数据完整性是指列中每个事件都有正确的数据值。引用完整性指示表之间的关系得到正确维护。

3.1、空值与非空值

• 允许空值（NULL）：默认情况下，列允许空值，即允许用户在添加数据时省略该列的值。
• 不允许空值（NOT NULL）：不允许在没有指定列默认值的情况下省略该列的值。

3.2默认值

• 如果再插入行时没有指定值，那么默认值将指定列中所使用的值。默认值可以是任何取值为常量的对象。
• 在CREATE TABLE中使用DEFAULT关键字创建默认定义，将常量表达式指派为列的默认值。这是标准方法。
• 使用CREATE DEFAULT语句创建默认对象，然后使用sp_bindefault系统储存过程将它绑定到列上，这是一个向前兼容的功能。

3.3 、特定标识属性（IDENTITY)

• 数据表中如果某列被指派特定标识属性，系统将自动为表中插入的新行生成连续递增的编号，因为标识值通常唯一，因而标识列通常定义为主键。IDENTITY属性适用于INT、SMALLINT、TINYINT、DECIMAL(p,0)、UMERIC( p,0)数据类型的列。
• 一个列不能同时具有NULL属性和IDENTITY属性，二者只能选其一

3.4、约束

• 约束是用来定义Microsoft SQL Server自动强制数据库完整性的方式。

1、非空（NOT NULL）：

• 使用户必须在表中的指定列输入一个值。

2、检查（check）：

• 用来指定一个布尔操作，限制输入到表中的值。

3、唯一性（Unique）

• 使用户的应用程序必须向列中输入一个唯一的值，值不能重复但可以为空。

4、主键（Primary Key）

• 建立一列或多列的组合以唯一标识表中的每一行。主键可以保证实体的完整性，一个表只能有一个主键，同时主键中的列不接受空值。

5、外键（Foreign Key）

• 用于建立和加强两个表数据之间的链接的一列或多列，当一个表中作为主键的一列被添加到另一个表中时，链接就形成，主要目的是用于控制储存在外键表中的数据。

二、数据表创建和管理

1、以界面方式方式操作数据表

1.1、创建数据表

• 在SQL Server Management Studio的对象资源浏览器中，数据库->选择指定数据库->表，选中“表”右键，在弹出的菜单中选择“新建表”命令，进入“添加表”对话框，在列表框里填写所需要的字段名以及对应的数据类型，最后填写表名保存。

1.2、修改数据表

• 在SQL Server Management Studio的对象资源浏览器中，数据库->选择指定数据库->表，选中“表”右键，在弹出的菜单中选择“设计”命令，进入“表设计”对话框，在该对话框对数据表进行修改后单击保存。

1.3、删除数据表

• 在SQL Server Management Studio的对象资源浏览器中，数据库->选择指定数据库->表展开，选择将要删除的数据表右键，在弹出的菜单中选择“删除”命令，在“删除对象”对话窗口点击“确定”。

2、使用脚本操作数据表

2.1、使用CREATE TABLE语句创建数据表

• CREATE TABLE语句的基本语法如下：

  CREATE TABLE
  [database_name.[schema_name].|schema_name.].table_name
  ({<column_definition> | <computer_column_definition> | <column_set_definition>}[<table_constraint>][,...n])
  <column_definition>::=
  column_name<data_type>
  [FILESTRENM]
  [COLLATE collation_name]
  [NULL | NOT NULL]
  [
      [CONSTRAINT constrainT_name] DEFUALT constant_expression]
      | [IDENTITY [(seed,increment)][NOT FOR REPLICATION]
  ]
  [ROWGUIDCOL] [<column_constraint>[,...n]]
  [SPARSE]
  <computer_column_definition>::=
  column_name AS computer_column_expression
  [PERSISTED [NOT NULL]]
  [
      [CONSTRAINT constrainT_name]
      {PRIMARY KEY | UNIQUE}
        [CLUSTERED | NONCLUSTERED]
      [
          WITH FILLFACTOR = fillfactor
          | WITH(<index_option>[,...n])
      ]
      | [FOREIGN KEY]
        REFERENCES referenced_table_name [(ref_column)]
        [ON DELETE {NO ACTION | CASCADE}]
        [ON UPDATE {NO ACTION}]
        [NOT FOR REPLICATION]
      | CHECK [NOT FOR REPLICATION] (logical_expression)
      | ON {partition_scheme_name (partition_column_name) | filegroup | "default"}
  ]
  <column_set_definition>::=
  column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
  <table_constraint>::=
  [CONSTRAINT constraint_name]
  {
    {
        {PRIMARY KEY | UNIQUE}
            [CLUSTERED | NONCLUSTERED]
                (column [ASC | DESC][,...n])
                  [
                      WITH FILLFACTOR = fillfactor
                      | WITH (<index_option>[,...n])
                  ]
                  [ON {partition_scheme_name (partition_column_name) | filegroup | "defualt"}]
            | FOREIGN KEY
                        (column [,...n])
                REFERENCES referenced_table_name [(ref_column [,...n])]
                [ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT}]
                [ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT}]
                [NOT FOR REPLICATION]
            | CHECK [NOT FOR REPLICATION] (logical_expression)
    }
  }
  

• 例：

  USE TEST1  --打开数据库
  CREATE TABLE[dbo].[Agent](  --创建表
    [ID][int] NOT NULL,  --字段ID，int型，不能为空
    [Name][varchar](50),  --Name字段，varchar型
    [Age][int]  --Age字段int型
    )
  

  结果：

1.PNG

2.2使用ALTER TABLE语句修改数据表结构

• 其语法如下：

  ALTER TABLE[database_name.[schema_name].|schema_name.]table_name
  {
    ALTER COLUMN column_name
      {
        [type_schema_name.]type_name[({precision[,scale] | max | xml_schema_collection})]
        [COLLATE collation_name]
        [NULL | NOT NULL]
        |{ ADD | DROP}
        {ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE}
      }
      |[WITH {CHECK | NOCHECK}]
      | ADD
      {
        <column_definition> | <computer_column_definition> | <table_constraint> | <column_set_definition>
      }[,...n]
      | DROP
      {
        [CONSTRAINT]constraint_name
        [WITH(<drop_clustered_constraint_option>[,...n])]
        | COLUMN column_name
      }
  }[,...n]
  

  • ALTER TABLE函数参数说明

    参数	描述
    database_name	创建表时所在的数据库的名称
    schema_name	表所属的架构的名称
    table_name	要更改表的名称
    ALTER COLUMN	指定要更改的命名列
    column_name	要更改、添加或删除的列的名称
    [type_schema_name.]type_name	更改后的列的新数据类型或添加的列数据类型
    Precision	指定的数据类型的精度
    scale	指定的数据类型的小数位数
    max	仅应用于varchar、nvarchar和varbinary数据类型
    xml_schema_collection	仅应用于xml数据类型
    COLLATE<collation_name>	指定更改后列的新排序规则
    NULL | NOT NULL	指定列是否接受空值
    [{ADD | DROP} ROWGUIDCOL]	指定在指定列中添加或删除ROWGUIDCOL属性
    [{ADD | DROP}PERSISTED]	指定在指定列中添加或删除PERSISTED属性
    DORP NOT FOR REPLICATION	指定当复制代理执行插入操作时，标识列中的值将增加
    SPARSE	指示列为稀疏列。稀疏列已对NULL值进行了储存优化，不能将稀疏列指定为NOT NULL
    WITH CHECK | WITH NOCHECK	指定表中的数据是否用新添加的或重新启用FOREIGN KEY或CHECK约束进行验证
    ADD	指定添加一个或多个列定义、计算列定义或表约束
    DROP{[CONSTRAINT] constraint_name | COLUMN column_name}	指定从表中删除constraint_name或column_name，可以列出多个列或约束
    WITH<drop_clustered_constraint_option>	指定设置一个或多个删除聚集约束选项

    • 例子：

      USE TEST1
      ALTER TABLE Agent
      DROP COLUMN sex
      

2.2、使用DROP TABLE删除数据表

• 语法如下：

  DROP TABLE[database_name.[schema_name].| schema_name.]table_name[,...n][;]
  database_name:要在其中删除数据表的数据库的名称。
  schema_name：表所属架构的名称。
  table_name：要删除的表的名称。
  例：
  USE TEST1
  DROP TABLE num
  

3、管理数据

3.1、使用INSERT语句添加数据

• INSERT语句可以实现向表中添加新记录的操作，该语句可以向表中插入一条新记录或者插入一个结果集。语法如下：

  INSERT[INTO]
  table_or_view_name
  VALUES
  (expression)[,...n]
  table_or_view_name:要接收数据的表或视图的名称
  VALUES：引入要插入的数据值的列表
  expression：一个常量、变量或表达式，表达式不能包含SELECT或EXECUTE语句。
  例：
  USE TEST1
  INSERT INTO Agent(ID,Agent_Name,Sex,Age)VALUES(1,'御承扬','男',22)
  或者：
  USE TEST
  INSERT INTO Tb_student VALUES(3163,'御承扬','男','1999-9-7',22,'数学院','信息与计算科学','广东省',1,NULL,'516500')
  

  • 使用右键选择要查看的数据表，在弹出的菜单中选中“选择前100行”的命令就可以查看数据表中的情况。

3.2、使用 UPDATE语句修改数据

• 语法如下：

  UPDATE table_or_view_name
  [FROM{<table_source>}[,...n]]
  SET
  {column_name={expression | DEFAULT | NULL}}
  [WHERE <search_condition>]
  

  参数	描述
  table_or_view_name	要更新的表或视图的名称。
  FROM<table_source>	指定表、视图或派生表用于为更新操作提供条件
  expression	返回单个值的变量、文字值、表达式或嵌套select语句（加括号）
  DEFAULT	指定用为列定义的默认值替换列中的现有值
  WHERE	指定条件来限制所更新的行。根据所用的WHERE子句的形式，现有两种更新形式。（1）搜索更新指定搜索条件来限定要更新的行。（2）定位更新实用CURRENT FOR子句确定游标，更新操作发生在游标的当前的位置
  <search_condition>	为要更新的行指定要满足的条件，搜索条件也可以是连接所基于的条件。对所搜索条件中可以包含的谓词数量没有限制

  • 例：

    指定某一列进行更新：
    USE TEST
    UPDATE Tb_student
    SET 年龄=年龄+2
    指定某一行进行更新：
    UPDATE Tb_student
    SET 年龄=年龄-2
    WHERE 学生编号=3161
    
    

3.3、使用DELETE语句删除数据

• 语法如下：

  DELETE
  [FROM<table_source>[,...n]]
  [WHERE{<serach_condition>}]
  
  

  参数	描述
  FROM	指定表、视图或派生表源用于为删除操作提供条件
  WHERE	指定条件来限制删除行数的条件。如果没有提供WHERE子句，则DELETE删除表中所有的行。根据所用的WHERE子句的形式，现有两种更新形式。（1）搜索更新指定搜索条件来限定要删除的行。（2）定位更新实用CURRENT FOR子句确定游标，更新操作发生在游标的当前的位置。这比使用WHERE serach_condition子句限定要删除的行的搜索DELETE语句更为精确。如果搜索条件不唯一标识单行，则搜索DELETE语句删除多行
  <serach_condition>	指定删除行的限定条件，对搜索条件中可以包含的谓词数量没有限制。

  • 例：

    USE TEST
    DELETE FROM Tb_student WHERE 学生编号=3162