操作数据表
一、数据表基础
1、基本数据类型
1.1、Character 字符串：
| 数据类型 | 描述 | 存储 |
|---|---|---|
| char(n) | 固定长度的字符串。最多 8,000 个字符。 | n |
| varchar(n) | 可变长度的字符串。最多 8,000 个字符。 | |
| varchar(max) | 可变长度的字符串。最多 1,073,741,824 个字符。 | |
| text | 可变长度的字符串。最多 2GB 字符数据。 |
1.2、Unicode 字符串：
| 数据类型 | 描述 | 存储 |
|---|---|---|
| nchar(n) | 固定长度的 Unicode 数据。最多 4,000 个字符。 | |
| nvarchar(n) | 可变长度的 Unicode 数据。最多 4,000 个字符。 | |
| nvarchar(max) | 可变长度的 Unicode 数据。最多 536,870,912 个字符。 | |
| ntext | 可变长度的 Unicode 数据。最多 2GB 字符数据。 |
1.3、Binary 类型：
| 数据类型 | 描述 | 存储 |
|---|---|---|
| bit | 允许 0、1 或 NULL | |
| binary(n) | 固定长度的二进制数据。最多 8,000 字节。 | |
| varbinary(n) | 可变长度的二进制数据。最多 8,000 字节。 | |
| varbinary(max) | 可变长度的二进制数据。最多 2GB 字节。 | |
| image | 可变长度的二进制数据。最多 2GB。 |
1.4、Number 类型：
| 数据类型 | 描述 | 储存 |
|---|---|---|
| tinyint | 允许从 0 到 255 的所有数字。 | 1 字节 |
| smallint | 允许从 -32,768 到 32,767 的所有数字。 | 2 字节 |
| int | 允许从 -2,147,483,648 到 2,147,483,647 的所有数字。 | 4 字节 |
| bigint | 允许介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之间的所有数字。 | 8 字节 |
| decimal(p,s) | 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 | 5-17 字节 |
| numeric(p,s) | 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 | 5-17 字节 |
| smallmoney | 介于 -214,748.3648 和 214,748.3647 之间的货币数据。 | 4 字节 |
| money | 介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之间的货币数据。 | 8 字节 |
| float(n) | 从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 参数 n 指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。 | 4 或 8 字节 |
| real | 从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 | 4 字节 |
1.5、Date 类型：
| 数据类型 | 描述 | 存储 |
|---|---|---|
| datetime | 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。 | 8 bytes |
| datetime(2) | 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。 | 6-8 bytes |
| smalldatetime | 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。 | 4 bytes |
| date | 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 | 3 bytes |
| time | 仅存储时间。精度为 100 纳秒。 | 3-5 bytes |
| datetimeoffset | 与 datetime2 相同，外加时区偏移。 | 8-10 bytes |
| timestamp | 存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量。 |
1.6、其他数据类型：
| 数据类型 | 描述 |
|---|---|
| sql_variant | 存储最多 8,000 字节不同数据类型的数据，除了 text、ntext 以及 timestamp。 |
| uniqueidentifier | 存储全局标识符 (GUID)。 |
| xml | 存储 XML 格式化数据。最多 2GB。 |
| cursor | 存储对用于数据库操作的指针的引用。 |
| table | 存储结果集，供稍后处理。 |
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) } }
| 参数 | 描述 |
|---|---|
| database_name | 在其中创建表的数据库的名称。database_name必须指定现有数据库的名称，如果未指定，则database_name默认为当前数据库。 |
| schema_name | 新表所属架构的名称。 |
| table_name | 新表的名称，表名必须遵循标识符规则。除了本地临时表名（以单个数字符（#）为前缀的名称不能超过116个字符，table_name最多可以包含128个字符。 |
| <column_definition> | 列定义 |
| column_name | 表中列的名称，列名必须遵循标识符规则且在表中是唯一的。 |
| computer_column_expression | 定义计算列的值的表达式 |
| PERSISTED | 指定在sql server数据库引擎将在表中屋里储存计算值，而且，当计算列依赖的任何其他列发生更新时对这些计算值进行更新。 |
| ON {<partition_scheme> |filegroup|"default"} | 指定储存表的分区架构或文件组。 |
| <table_constraint> | 表约束 |
| CONSTRAINT | 可选关键字，表示PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN KEY或CHECK约束定义的开始。 |
| constraint_name | 约束的名称，约束名称必须在表所属的架构中唯一 |
| NULL | NOT NULL | 确定列中是否允许空值 |
| PRIMARY KEY | 是通过唯一索引对给定的一列或多列强制实体完整性的约束。每个表只能创建一个PRIMARY KEY约束 |
| UNIQUE | 一个约束，该约束通过唯一索引为一个或多个指定列提供实体完整性。一个表可以有多个UNIQUE约束 |
| CLUSTERED | NONCLUSTERED | 指示为PRIMARY KEY或UNIQUE约束创建聚集索引还是非聚集索引。PRIMARY KEY约束默认为CLUSTERED，UNIQUE约束默认NONCLUSTERED。 |
| column | 用括号括起来一列或多列，在表约束中表示这些列用在约束定义中 |
| [ASC | DESC] | 指定加入到表约束中的一列或多列的排序顺序，默认为ASC |
| WITH FILLFACTOR = fillfactor | 指定数据库引擎储存索引数据时每个索引页的填充程度。用户指定的fillfactor值可以在1~100之间的任意值。如果未指定值，则默认为零。 |
| partition_scheme_name | 分区架构的名称，该分区架构定义要将已分区表的分区映射到的文件组。数据库中必须存在该分区架构 |
| [partition_cloumn_name] | 指定对已分区的表进行分区所依据的列 |
| FOREIGN KEY REFERENCES | 为列中的数据提供引用完整性的约束。FOREIGN KEY约束要求列中的每个值在所引用的表中对应的被引用的列中都存在。 |
| (ref_column [,...n]) | 是FOREIGN KEY约束所引用的表中的一列或多列 |
| ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT} | 指定如果已创建表中的行具有引用关系，并且被引用行已从父表中删除，则对这些行采取的操作。默认值为NO ACTION |
| NO ACTION | 数据库引擎将引发错误，并回滚到对父表中相应行的删除操作。 |
| CASCADE | 如果从父表中删除一行，则将从引用表中删除相应行 |
| SET NULL | 如果父表对应的行被删除，则组成外键的所有值将设置为NULL，若要执行次约束，外键列必须可为空值 |
| SET DEFAULT | 如果父表对应的行被删除，则组成外键的所有值将设置为默认值，若要执行次约束，所有外键列必须有默认定义。 |
| ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT} | 指定在发生更改的表中，如果行引用关系且引用的行在父表中被更新，则对这些行采取什么操作，默认为NO ACTION |
| CHECK | 一个约束，该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。计算列上的CHECK约束也必须标记为PERSISTED |
| logical_expression | 返回TRUE或FALSE的逻辑表达式，别名数据类型不能作为表达式的一部分 |
| NOT FOR REPLICATION | 在CREATE TABLE语句中，可为IDENTITY属性、FOREGIN KEY约束和CHECK约束指定NOT FOR REPLICATION子句。 |
-
例：
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
-
网友评论