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

济南大学信息科学与工程学院张坤1数据表简介2数据类型3数据表创建4数据表修改5数据表删除6关系图23系统表服务器的配置信息、数据表的定义信息等只读，由数据库系统维护用户表已分区表将数据水平划分为多个单元的表，这些单元可以分布到数据库中的多个文件组中临时表由于系统或用户运算的临时需要而创建的表，使用完毕后即可删除。本地临时表、全局临时表4字段名数据类型是否允许为Null是否自动增长IDNETITY属性5Northwind数据库(SQLServer2000)SQLServer2012上安装Northwind数据库6右击-打开方式-SQLServerManagementStudio-执行出错-SQLServer2012中不再支持存储过程sp_dboption1数据表简介2数据类型3数据表创建4数据表修改5数据表删除6关系图72.1精确数字型2.2近似数字型2.3日期时间型2.4字符串型2.5Unicode字符串型2.6二进制字符串型2.7其他数据类型2.8用户自定义型8整数型带固定精度和小数位数的数据类型货币型9数据类型范围存储长度bigint-263(-9223372036854775808)~263-1(9223372036854775807)8字节int-231(-2147483648)~231-1(2147483647)4字节smallint-215(-32768)~215-1(32767)2字节tinyint0~2551字节bit0、1或Null如果表中的列为8位或更少，则这些列作为1个字节存储。如果列为9到16位，则这些列作为2个字节存储，以此类推。10整数型数据数据类型范围存储长度numeric-1038+1~1038-1存储长度与精度有关1~9位：5字节10~19位：9字节20~28位：13字节29~38位：17字节decimal-1038+1~1038-1存储长度与精度有关1~9位：5字节10~19位：9字节20~28位：13字节29~38位：17字节11带固定精度和小数位数的数据类型数据类型范围存储长度money-922337203685477.5808~922337203685477.58078字节smallmoney-214748.3648~214748.36474字节12货币型数据数据类型范围存储长度float-1.79E+308~-2.23E-308，0以及2.23E-308~1.79E+308，最多可以表示15位数存储长度与数值的位数有关7位数时：4字节15位数时：8字节real-3.40E+38~-1.18E-38，0以及1.18E-38~3.40E+38，最多可以表示7位数4字节13近似数字型数据数据类型范围存储长度datetime1753年1月1日到9999年12月31日，可精确到3.33毫秒8字节smalldatetime1900年1月1日到2079年6月6日，可精确到分钟4字节14日期和时间型数据数据类型范围存储长度char1~8000个字符1个字符占1个字节，为固定长度，如果插入的数据不够定义的长度，系统会自动补上空格varcharvarchar(n):1~8000个字符varchar(max):1~231-1个字符varchar(n):1个字符占1个字节varchar(max):输入数据的实际长度加2个字节Text1~231-1个字符1个字符占1个字节，存储多少个字符即占多少空间，最大可存储2GB数据15字符串型数据数据类型范围存储长度nchar1~4000个字符1个字符占2个字节，为固定长度，如果插入的数据不够定义的长度，系统会自动补上空格narcharvarchar(n):1~4000个字符varchar(max):1~231-1个字符varchar(n):1个字符占2个字节varchar(max):输入数据的实际长度的两倍再加2个字节ntext1~230-1个字符1个字符占2个字节，存储多少个字符即占多少空间，最大可存储2GB数据16Unicode字符串型数据数据类型范围存储长度binary1~8000个字节为固定长度，如果插入的数据不够定义的长度，系统会自动补上0x00varbinaryvarbinary(n):1~8000个字节varbinary(max):1~231-1个字节varbinary(n):可变长度，输入数据的实际长度varbinary(max):输入数据的实际长度加2个字节image1~231-1个字节可变长度，输入数据的实际长度17二进制字符串型数据cursorsql_varianttimestampuniqueidentifiertableXML18sql_variant可用来存储除了text、ntext、image、timestamp、sql_variant之外的所有的SQLServer2008支持的数据类型，其主要用于列、参数、变量和用户定义函数的返回值中。当某个字段需要存储不同类型的数据时，可以将其设置为sql_variant。XML可以在列或变量中存储XML文档和片段XML片段是缺少单个顶级元素的XML实例存储空间不能超过2GB19timestamp8字节的16进制数据在数据库范围内提供唯一值在数据库中更新或插入数据行时，此数据类型定义的列的值会自动更新，一个计算值被自动添加到列中，而且此值在整个数据库中是唯一的每个数据表中只能有一个是timestamp类型的字段uniqueidentifier与timestamp类似16字节的16进制数据提供的是全球范围内的唯一值20用户可以根据需要自定义数据类型，并可以用此数据类型来声明变量或字段如可以将经常使用的char(100)数据类型自定义为一个数据类型，如char100。221数据表简介2数据类型3数据表创建4数据表修改5数据表删除6关系图23SQLServerManagementStudio定义数据表的字段和主键▪主键：一个字段或多个字段的组合设置默认值设置标识列▪自动增长。标识种子、标识增量建立表与表之间的关系▪INSERT和UPDATE规范：不执行任何操作、级联、设置Null、设置默认值创建索引创建约束▪主键约束、外键约束、Unique约束、Check约束、默认值、允许空设置数据表所在文件组24smalldatetime-getdate()25提示说不允许保存修改工具-选项-设计器-表设计器和数据库设计器-阻止保存要求重新创建表的更改26CREATETABLE[database_name.[schema_name].|schema_name.]table_name--设置表名({column_definition--设置列属性|computed_column_definition}--设置计算列[table_constraint][,...n])--设置表约束[ON{partition_scheme_name(partition_column_name)|filegroup|default}]--指定存放表数据的分区架构或文件组[{TEXTIMAGE_ON--指定存放Text及Image类型字段数据的分区架构或文件组{filegroup|default}]27创建新表CREATETABLENorthwind.dbo.例一表(编号int,姓名nvarchar(50))CREATETABLENorthwind..例一表(编号int,姓名nvarchar(50))USENorthwindCREATETABLE例一表(编号int,姓名nvarchar(50))28创建标识字段CREATETABLE例二表(编号intIDENTITY,姓名nvarchar(50))创建带标识种子和标识增量的标识字段CREATETABLE例三表(编号intIDENTITY(1,2)PRIMARYKEY,姓名nvarchar(50))创建非NULL字段CREATETABLE例四表(编号intIDENTITY(1,1)PRIMARYKEY,姓名nvarchar(50)NOTNULL)29设置字段的默认值CREATETABLE例五表(编号intIDENTITY(1,1)PRIMARYKEY,姓名nvarchar(50)NOTNULL,性别bitDEFAULT1)30创建新表并将其放在文件组中CREATETABLE例六表(编号intIDENTITY(1,1)PRIMARYKEY,姓名nvarchar(50)NOTNULL,性别bitDEFAULT1)ON第二文件组31创建新表并使用Check约束CREATETABLE例七表(编号intIDENTITY(1,1)PRIMARYKEY,姓名nvarchar(50)NOTNULL,性别bitDEFAULT1,年纪tinyintCONSTRAINTCK_年纪CHECK(年纪0AND年纪101))32设置字段的排序规则CREATETABLE例八表(编号intIDENTITY(1,1)PRIMARYKEY,姓名nvarchar(50)COLLATEChinese_PRC_CI_ASNotnull,性别bitDEFAULT1,年纪tinyintCONSTRAINTCK_例八年纪CHECK(年纪0AND年纪101))33创建外键CREATETABLE例九_部门表(部门编号intIDENTITY(1,1)PRIMARYKEY,部门名称nvarchar(50)Notnull)GOCREATETABLE例九_员工表(员工编号intIDENTITY(1,1)PRIMARYKEY,所属部门intCONSTRAINTFK_员工表外键FOREIGNKEYREFERENCES例九_部门表(部门编号),员工姓名nvarchar(20)notnull)GO34设置外键的更新规则和删除规则CREATETABLE例十_部门表(部门编号intIDENTITY(1,1)PRIMARYKEY,部门名称nvarchar(50)Notnull)GOCREATETABLE例十_员工表(员工编号intIDENTITY(1,1)PRIMARYKEY,所属部门intCONSTRAINTFK_例十_员工表外键FOREIGNKEYREFERENCES例十_部门表(部门编号)ONUPDATECASCADEONDELETESETNULL,员工姓名nvarchar(20)notnull)GO35创建Unique约束CREATETABLE例十一_部门表(部门编号intIDENTITY(1,1)PRIMARYKEY,部门名称nvarchar(50)NotnullUNIQUE)36创建计算列CREATETABLE例十二(编号intIDENTITY(1,1)PRIMARYKEY,单价moneyNotnull,数量intNotnullCONSTRAINTCK_例十二CHECK(数量0),合计as单价*数量)37设置数据表的约束CREATETABLE例十三_用户注册表(编号intIDENTITY(1,1)PRIMARYKEY,用户名nvarchar(50)Notnull,密码varchar(16)Notnull,电话varchar(50),地址nvarchar(200),CHECK(电话isnotnullor地址isnotnull))381数据表简介2数据类型3数据表创建4数据表修改5数据表删除6关系图39SQLServerManagementStudio修改数据表名插入、移动和删除数据数据表的字段修改字段数据类型及属性▪数据表有记录时不能随意修改，只能修改成兼容的数据类型。▪Varchar(5)–Varchar(10),Varchar(10)-varchar(5)截断▪Bit-decimal(18,0),decimal(18,0