SQLServer数据库技术-03数据表管理

1、济南大学 信息科学与工程学院 张坤1 数据表简介2 数据类型3 数据表创建4 数据表修改5 数据表删除6 关系图23系统表服务器的配置信息、数据表的定义信息等只读，由数据库系统维护用户表已分区表将数据水平划分为多个单元的表，这些单元可以分布到数据库中的多个文件组中临时表由于系统或用户运算的临时需要而创建的表，使用完毕后即可删除。本地临时表、全局临时表4字段名数据类型是否允许为Null是否自动增长IDNETITY属性5Northwind数据库(SQL Server 2000)SQL Server 2012上安装Northwind数据库6右击-打开方式-SQL Server Management

2、Studio-执行出错-SQL Server 2012中不再支持存储过程sp_dboption1 数据表简介2 数据类型3 数据表创建4 数据表修改5 数据表删除6 关系图72.1 精确数字型2.2 近似数字型2.3 日期时间型2.4 字符串型2.5 Unicode字符串型2.6 二进制字符串型2.7 其他数据类型2.8 用户自定义型8整数型带固定精度和小数位数的数据类型货币型9数据类型数据类型范围范围存储长度存储长度bigint-263(-9 223 372 036 854 775 808) 263-1(9 223 372 036 854 775 807)8字节int-231(-2 147

3、483 648) 231-1(2 147 483 647) 4字节smallint-215(-32 768) 215-1(32 767)2字节tinyint02551字节bit0、1或Null如果表中的列为8位或更少，则这些列作为1个字节存储。如果列为9到16位，则这些列作为2个字节存储，以此类推。10整数型数据数据类型数据类型范围范围存储长度存储长度numeric-1038+1 1038-1存储长度与精度有关19位：5字节1019位：9字节2028位：13字节2938位：17字节decimal-1038+1 1038-1存储长度与精度有关19位：5字节1019位：9字节2028位：13字节2

4、938位：17字节11带固定精度和小数位数的数据类型数据类型数据类型范围范围存储长度存储长度money-922 337 203 685 477.5808 922 337 203 685 477.58078字节smallmoney-214 748.3648 214 748.36474字节12货币型数据数据类型数据类型范围范围存储长度存储长度float-1.79E+308 -2.23E-308，0以及2.23E-3081.79E+308，最多可以表示15位数存储长度与数值的位数有关7位数时：4字节15位数时：8字节real-3.40E+38-1.18E-38，0以及1.18E-383.40E+38

5、，最多可以表示7位数4字节13近似数字型数据数据类型数据类型范围范围存储长度存储长度datetime1753年1月1日到9999年12月31日，可精确到3.33毫秒8字节smalldatetime1900年1月1日到2079年6月6日，可精确到分钟4字节14日期和时间型数据数据类型数据类型范围范围存储长度存储长度char18000个字符1个字符占1个字节，为固定长度，如果插入的数据不够定义的长度，系统会自动补上空格varcharvarchar(n): 18000个字符varchar(max): 1231-1个字符varchar(n): 1个字符占1个字节varchar(max):输入数据的实际

6、长度加2个字节Text1231-1个字符1个字符占1个字节，存储多少个字符即占多少空间，最大可存储2GB数据15字符串型数据数据类型数据类型范围范围存储长度存储长度nchar14000个字符1个字符占2个字节，为固定长度，如果插入的数据不够定义的长度，系统会自动补上空格narcharvarchar(n): 14000个字符varchar(max): 1231-1个字符varchar(n): 1个字符占2个字节varchar(max):输入数据的实际长度的两倍再加2个字节ntext1230-1个字符1个字符占2个字节，存储多少个字符即占多少空间，最大可存储2GB数据16Unicode字符串型数据

7、数据类型数据类型范围范围存储长度存储长度binary18000个字节为固定长度，如果插入的数据不够定义的长度，系统会自动补上0 x00varbinaryvarbinary(n): 18000个字节varbinary(max): 1231-1个字节varbinary (n): 可变长度，输入数据的实际长度varbinary(max):输入数据的实际长度加2个字节image1231-1个字节可变长度，输入数据的实际长度17二进制字符串型数据cursorsql_varianttimestampuniqueidentifiertableXML18sql_variant可用来存储除了text、ntext

8、、image、timestamp、sql_variant之外的所有的SQL Server 2008支持的数据类型，其主要用于列、参数、变量和用户定义函数的返回值中。当某个字段需要存储不同类型的数据时，可以将其设置为sql_variant。XML可以在列或变量中存储XML文档和片段XML片段是缺少单个顶级元素的XML实例存储空间不能超过2GB19timestamp8字节的16进制数据在数据库范围内提供唯一值在数据库中更新或插入数据行时，此数据类型定义的列的值会自动更新，一个计算值被自动添加到列中，而且此值在整个数据库中是唯一的每个数据表中只能有一个是timestamp类型的字段uniqueide

9、ntifier与timestamp类似16字节的16进制数据提供的是全球范围内的唯一值20cursor主要是用于变量或存储过程OUTPUT参数的一种数据类型，这些参数包含对游标的引用。Cursor主要用来存储查询结构，它是一个数据集，其内部的数据可以单条取出来进行处理table主要用于临时存储一组行，这些行是作为表值函数的结果集返回的，其用途与临时表很相似主要用于函数、存储过程和批处理中21用户可以根据需要自定义数据类型，并可以用此数据类型来声明变量或字段如可以将经常使用的char(100)数据类型自定义为一个数据类型，如char100。221 数据表简介2 数据类型3 数据表创建4 数据表修

10、改5 数据表删除6 关系图23SQL Server Management Studio定义数据表的字段和主键 主键：一个字段或多个字段的组合设置默认值设置标识列 自动增长。标识种子、标识增量建立表与表之间的关系 INSERT和UPDATE规范：不执行任何操作、级联、设置Null、设置默认值创建索引创建约束 主键约束、外键约束、Unique约束、Check约束、默认值、允许空设置数据表所在文件组24smalldatetime - getdate()25提示说不允许保存修改工具-选项-设计器-表设计器和数据库设计器-阻止保存要求重新创建表的更改26CREATE TABLE database_nam

11、e . schema_name . | schema_name . table_name -设置表名 ( -设置列属性 | -设置计算列 ,.n ) -设置表约束 ON partition_scheme_name ( partition_column_name ) | filegroup | default -指定存放表数据的分区架构或文件组 TEXTIMAGE_ON -指定存放Text及Image类型字段数据的分区架构或文件组 filegroup | default 27创建新表CREATE TABLE Northwind.dbo.例一表(编号 int,姓名 nvarchar(50)CREA

12、TE TABLE Northwind.例一表(编号 int,姓名 nvarchar(50)USE NorthwindCREATE TABLE 例一表(编号 int,姓名 nvarchar(50)28创建标识字段CREATE TABLE 例二表(编号 int IDENTITY,姓名 nvarchar(50)创建带标识种子和标识增量的标识字段CREATE TABLE 例三表(编号 int IDENTITY(1,2) PRIMARY KEY,姓名 nvarchar(50)创建非NULL字段CREATE TABLE 例四表(编号 int IDENTITY(1,1) PRIMARY KEY,姓名 nva

13、rchar(50) NOT NULL)29设置字段的默认值CREATE TABLE 例五表(编号 int IDENTITY(1,1) PRIMARY KEY,姓名 nvarchar(50) NOT NULL,性别 bit DEFAULT 1)30创建新表并将其放在文件组中CREATE TABLE 例六表(编号 int IDENTITY(1,1) PRIMARY KEY,姓名 nvarchar(50) NOT NULL,性别 bit DEFAULT 1)ON 第二文件组31创建新表并使用Check约束CREATE TABLE 例七表(编号 int IDENTITY(1,1) PRIMARY KE

14、Y,姓名 nvarchar(50) NOT NULL,性别 bit DEFAULT 1,年纪 tinyint CONSTRAINT CK_年纪 CHECK (年纪0 AND 年纪0 AND 年纪0),合计 as 单价*数量)37设置数据表的约束CREATE TABLE 例十三_用户注册表(编号 int IDENTITY(1,1) PRIMARY KEY,用户名 nvarchar(50) Not null,密码 varchar(16) Not null,电话 varchar(50),地址 nvarchar(200),CHECK (电话 is not null or 地址 is not null)

15、381 数据表简介2 数据类型3 数据表创建4 数据表修改5 数据表删除6 关系图39SQL Server Management Studio修改数据表名插入、移动和删除数据数据表的字段修改字段数据类型及属性 数据表有记录时不能随意修改，只能修改成兼容的数据类型。 Varchar(5) Varchar(10), Varchar(10)-varchar(5)截断 Bit-decimal(18,0), decimal(18,0)-bit不一定修改主键、外键、索引和约束修改数据表的属性40ALTER TABLE database_name . schema_name . | schema_name

16、. table_name ALTER COLUMN column_name -要修改的字段名 type_schema_name. type_name ( precision , scale | max | xml_schema_collection ) -修改后的数据类型 NULL | NOT NULL -设置是否为NULL COLLATE collation_name 设置排序规则 | ADD | DROP ROWGUIDCOL | PERSISTED -添加或删除ROWGUIDCOL属性 | WITH CHECK | NOCHECK ADD -添加字段 -设置字段属性 | -设置计算列 |

17、 -设置表约束 ,.n | DROP -删除 CONSTRAINT constraint_name -删除约束 WITH ( ,.n ) -设置聚集约束选项 | COLUMN column_name 删除字段 ,.n | WITH CHECK | NOCHECK CHECK | NOCHECK CONSTRAINT -启用或禁用约束 ALL | constraint_name ,.n | ENABLE | DISABLE TRIGGER -启用或禁用触发器 ALL | trigger_name ,.n | SWITCH PARTITION source_partition_number_exp

18、ression -切换数据块 TO schema_name. target_table PARTITION target_partition_number_expression 41更改表字段的属性ALTER TABLE 例一表ALTER COLUMN 姓名 nvarchar(20) COLLATE Chinese_PRC_CI_AS not null添加字段ALTER TABLE 例一表ADD 性别 nvarchar(2)添加字段并设置字段属性ALTER TABLE 例一表ADD 密码 varchar(16) NOT Null42删除字段ALTER TABLE 例一表DROP COLUMN

19、密码添加约束ALTER TABLE 例一表ADD CONSTRAINT CK_性别 CHECK (性别=男 OR 性别=女)默认情况下，添加Check约束时会检查数据表里的数据是否符合Check约束，如果有数据不符合Check约束，添加Check约束会失败。如果希望添加约束时不检查数据库里的数据是否符合Check约束，参考如下：ALTER TABLE 例一表WITH NOCHECK ADD CONSTRAINT CK_性别 CHECK (性别=男 OR 性别=女)43禁用约束ALTER TABLE 例一表NOCHECK CONSTRAINT CK_性别一次禁用多个约束ALTER TABLE 例

20、一表NOCHECK CONSTRAINT CK_1,CK_2,CK_3禁用所有约束ALTER TABLE 例一表NOCHECK CONSTRAINT ALL44启用约束ALTER TABLE 例一表CHECK CONSTRAINT CK_性别一次启用多个约束ALTER TABLE 例一表CHECK CONSTRAINT CK_1,CK_2,CK_3启用所有约束ALTER TABLE 例一表CHECK CONSTRAINT ALL45删除约束ALTER TABLE 例一表DROP CK_性别设置主键ALTER TABLE 例一表ALTER COLUMN 编号 int not nullGOALTE

21、R TABLE 例一表ADD CONSTRAINT PK_主键 PRIMARY KEY (编号)GO46修改字段名和数据表名Alter Table没有直接修改数据表名和字段名的功能。使用存储过程sp_rename (SQL Server 2008)exec sp_rename 例一表.姓名,名称,COLUMNexec sp_rename 例一表,例二十三表sp_rename 原对象名称 , 新对象名称 , 对象类型471 数据表简介2 数据类型3 数据表创建4 数据表修改5 数据表删除6 关系图48SQL Server Management Studio当数据表有外键依赖时是不能被删除的 查看表的依赖关系一次删除多个数据表 摘要窗格中选择多个数据表49DROP TABLE database_name . schema_name . | schema_name . table_