创建与管理数据库表.ppt

创建与管理数据库表 Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望1.表是重要数据库对象，是数据库的基本组成部分，是储存数据的逻辑载体2.关系型数据库的表是二维的，表的一列称为一个字段；表的一行称为一个记录3.SQLServer2000中，一个表中最多允许1024列，表的行数没有限制，与磁盘存储空间有关概述：概述：局部临时表：以开头以开头，仅可由创建者本人在创建后立即使用，一旦断开连接，该表被删除全局临时表以开头以开头，创建后可以由多个用户使用，当最后使用的用户断开连接最后使用的用户断开连接，该表被删除表表永久表永久表permernet：表一旦创建将一直存储在硬盘上，除非被用户删除临时表临时表：用户退出时自动被系统删除temp5.1数据类型系统提供的数据类型系统提供的数据类型创建和删除用户定义的数据类型创建和删除用户定义的数据类型选择数据类型的指导原则选择数据类型的指导原则表的内容由列属性组成，数据类型是最重要的列属性，它决定了数据的存储格式（长度、精度、小数位数等）系统提供的数据类型数值型数据整型数据：存储整数小数数据：包含存储在最小有效数上的数据bigint占占8 8个字节，值的范围为个字节，值的范围为-2-263632 26363-1-1int占占4 4个字节，值的范围为个字节，值的范围为-2-231312 23131-1-1smallint占占2 2个字节，值的范围为个字节，值的范围为-32768-3276832 76732 767tinyint占占1 1个字节，值的范围为个字节，值的范围为 0 0255255decimal(p,s)p p为精度，最大为精度，最大3838；s s为小数位数，为小数位数，0sp0spnumeric(p,s)在在SQL ServerSQL Server中，等价于中，等价于decimaldecimal近似数字数据：表示浮点数据的近似数字注意：注意：存储的非精确值而只是近似值，在使用where进行查询时，避免使用数据类型为float和real的字段。另限制float和real的字段作和的比较float(n)从从-1.79E+308 -1.79E+308 到到 1.79E+308 1.79E+308 之间的浮点数字数据；之间的浮点数字数据；n n 为用为用于存储科学记数法尾数的位数，同时指示其精度和存储大小，于存储科学记数法尾数的位数，同时指示其精度和存储大小，11nn5353real从从 3.40E+38 3.40E+38 到到 3.40E+38 3.40E+38 之间的浮点数字数据，存储大小之间的浮点数字数据，存储大小为为 4 4 字节；字节；SQL Server SQL Server 中，中，real real 的同义词为的同义词为 float(24)float(24)货币数据：表示正的或负的货币值注意：注意：货币数据使用时，不需要用单引号货币数据使用时，不需要用单引号 括起来，但货括起来，但货币数币数 值之前一定要带有适当的货币符号。如：值之前一定要带有适当的货币符号。如：$78money占占8 8个字节，个字节，值的范围为值的范围为 -922 337 203 685 477.580 8-922 337 203 685 477.580 8+922 337 203+922 337 203 685 477.580 7685 477.580 7smallmoney占占4 4个字节，值的范围为个字节，值的范围为 -214 748.3648-214 748.3648 214 748.3647 214 748.3647日期和时间数据字符数据和Unicode字符数据datetime占占8 8个字节，表示从个字节，表示从17531753年年1 1月月1 1日到日到 99999999年年1212月月3131日的日期日的日期smalldatetime占占4 4个字节，表示从个字节，表示从19001900年年1 1月月1 1日至日至20792079年年6 6月月6 6日的日期日的日期char(n)存储字符个数为存储字符个数为 0 08 0008 000varchar(n)存储字符个数为存储字符个数为 0 08 0008 000text存储字符个数为存储字符个数为 0 02GB2GBnchar(n)存储字符个数为存储字符个数为 0 04 0004 000nvarchar(n)存储字符个数为存储字符个数为 0 04 0004 000ntext存储字符个数为存储字符个数为 0 01GB1GB可变长度Unicode采用两个字节编码,可以存储日文、韩文和汉字二进制数据其他binary(n)存储字节个数存储字节个数 0 08 0008 000varbinary(n)存储字节个数存储字节个数 0 08 0008 000image存储字节个数存储字节个数 0 02 G2 Gbit存储位数据存储位数据cursor存储对游标的引用存储对游标的引用rowversion(timestamp)时间戳时间戳sql_variant可存储除可存储除 text、ntext、image、rowversion 之外的之外的其他类型其他类型table存储函数返回结果存储函数返回结果uniqueidentifier存储存储 GUID 以及以及 UUID 选择数据类型的指导原则选择数据类型的指导原则若列值的长度相差很大，那么使用变长数据类型例如某列存储的是人名，地址等谨慎使用 tinyint 数据类型虽然节省空间，但扩展性很小对于小数数据来说，一般使用 decimal 数据类型可以精确地控制精度如果行的存储量 超过8 000字节，使用 text 或者 image若不大于8 000字节，可使用char、varchar或者binary数据类型对于货币数据，使用 money 数据类型不要使用类型为 float 或者 real 的列作为主键因为它们不精确，所以不适合用于比较创建和删除用户定义的数据类型创建和删除用户定义的数据类型用户定义的数据类型（扩展SQLServer的基本数据类型）当多个表的列中要存储同样类型的数据，且想确保这些当多个表的列中要存储同样类型的数据，且想确保这些列具有完全相同的数据类型、长度和为空性时，可使用列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型用户定义数据类型注意：如果用户定义数据类型是在 modelmodel 数据库中创建的，它将作用于所有用户定义的新数据库中。如果数据类型在用户定义的数据库中创建，则该数据类型只作用于此用户定义的数据库。创建用户定义的数据类型企业管理器系统存储过程语法语法sp_addtypetypename=type,phystype=system_data_type,nulltype=null_type,owner=owner_name 删除用户定义的数据类型sp_droptype sp_droptype 类型名 企业管理器创建用户定义函数企业管理器创建用户定义函数展开服务器组，然后展开服务器。展开服务器组，然后展开服务器。展开展开 数据库数据库 文件夹，再展开要在其中创建用户定义的数据文件夹，再展开要在其中创建用户定义的数据类型的数据库。类型的数据库。右击右击 用户定义数据类型用户定义数据类型，然后单击，然后单击 新建用户定义数据类新建用户定义数据类型型 命令。命令。输入新建数据类型的名称。输入新建数据类型的名称。在在 数据类型数据类型 列表中，选择基数据类型。列表中，选择基数据类型。如如 长度长度 处于活动状态，若要更改此数据类型可存储的最大处于活动状态，若要更改此数据类型可存储的最大数据长度，请键入另外的值。长度可变的数据类型有：数据长度，请键入另外的值。长度可变的数据类型有：binarybinary、charchar、ncharnchar、nvarcharnvarchar、varbinary varbinary 和和 varchar varchar。若要允许此数据类型接受空值，请选择若要允许此数据类型接受空值，请选择 允许空值允许空值 命令。命令。在在 规则规则 和和 默认值默认值 列表中选择一个规则或默认值（若有）列表中选择一个规则或默认值（若有）以将其绑定到用户定义数据类型上以将其绑定到用户定义数据类型上用户自定义函数示例用户自定义函数示例(p350)A.创建不允许空值的用户定义数据类型创建不允许空值的用户定义数据类型下面的示例创建一个名为 ssnssn（社会保险号）的用户定义数据类型，它基于 SQL Server 提供的 varcharvarchar 数据类型。ssnssn 数据类型用于那些保存 11 位数字的社会保险号(999-99-9999)的列。该列不能为 NULL。USEmasterEXECsp_addtypessn,VARCHAR(11),NOTNULL请注意，varchar(11)varchar(11)由单引号引了起来，这是因为它包含了标点符号（圆括号）。B.创建允许空值的用户定义数据类型创建允许空值的用户定义数据类型下面的示例创建了一个名为下面的示例创建了一个名为 birthday birthday 的用户定的用户定义数据类型（基于义数据类型（基于 datetime datetime），该数据类型允），该数据类型允许空值。许空值。USEmasterEXECsp_addtypebirthday,datetime,NULLC.创建另外的用户定义的数据类型创建另外的用户定义的数据类型下面的示例为国内及国际电话和传真号码另外创下面的示例为国内及国际电话和传真号码另外创建两个用户定义的数据类型建两个用户定义的数据类型 telephone telephone 和和 fax fax。USEmasterEXECsp_addtypetelephone,varchar(24),NOTNULLEXECsp_addtypefax,varchar(24),NULL例：首先创建一个例：首先创建一个birthday用户定义数据类型，然后用户定义数据类型，然后在后面的操作中使用它，最后将它删除。在后面的操作中使用它，最后将它删除。-创建创建birthday用户定义数据类型，使用用户定义数据类型，使用datetime基本数据类型，允许基本数据类型，允许NULLuse sampleEXEC sp_addtype birthday,datetime,NULLGO-在数据表定义时使用在数据表定义时使用birthday用户定义数据类型用户定义数据类型CREATE TABLE 特殊数据特殊数据(char_data char(20),birthday_data birthday)GO-向数据表中添加几条记录向数据表中添加几条记录INSERT INTO 特殊数据特殊数据VALUES(Sarah,02/22/1976)INSERT INTO 特殊数据特殊数据VALUES(Tina,04/15/1998)GO-检索检索SELECT*FROM 特殊数据特殊数据GO-删除删除birthday用户定义数据类型，首先要删除对定义数据类型的引用用户定义数据类型，首先要删除对定义数据类型的引用 DROP table 特殊数据特殊数据EXEC sp_droptype birthdaygo5.2 5.2 创创 建建 表表创建表有三种方法：创建表有三种方法：使用向导使用企业管理器使用T-SQL语句1使用CREATETABLE命令创建表在指定的数据库中创建表的基本语法格式如下页所示：CREATETABLEdatabase_name.owner.|owner.table_name(|column_nameAScomputed_column_expression|:=CONSTRAINTconstraint_name|PRIMARYKEY|UNIQUE,.n)说明：说明：1.database_name.owner .|owner.table_name：定：定义表名义表名table_name 可以选加数据库名可以选加数据库名database_name和和表的所有者名表的所有者名owner 2.表名不得超过表名不得超过128个字符，临时表表名不得超过个字符，临时表表名不得超过116个字符个字符:=column_namedata_typeCOLLATEDEFAULTconstant_expression|IDENTITY(seed,increment)3.column_name data_type：定定义义列列 其其中中column_name为为列列名名，data_type为数据类型为数据类型 4.NULL|NOT NULL：指指定定所所定定义义的的列列是是否否可可以以取取空空值值 默默认认情情况况下下是是NULL 5.DEFAULT constant_expression：指定列的默认值约束：指定列的默认值约束 当向表中当向表中插入一条记录时，如果本列插入的数据为空，则系统自动将默认值插入一条记录时，如果本列插入的数据为空，则系统自动将默认值填充到本列填充到本列 6.IDENTITY (seed,increment)：定义标识列：定义标识列,也称自动编号列，也称自动编号列，一个表中只能定义一个标识列一个表中只能定义一个标识列 7.,.n：表示可以在表中设计：表示可以在表中设计n个列的定义，列间用逗号隔开个列的定义，列间用逗号隔开起始值T-Sql创建表示例创建表示例例一、pubs数据库中所创建的表jobs定义，其中包含所有的约束定义。CREATETABLEjobs(job_idsmallintIDENTITY(1,1)PRIMARYKEY,job_descvarchar(50)NOTNULLDEFAULTNewPosition-titlenotformalizedyet,min_lvltinyintNOTNULLCHECK(min_lvl=10),max_lvltinyintNOTNULLCHECK(max_lvl=250)例二、按如下要求在数据库例二、按如下要求在数据库companycompany中创建一个数据表中创建一个数据表(1)(1)表名：表名：project_back.project_back.(2)(2)字段：项目编号，项目名称，级别，注册时间，预计工期。字段：项目编号，项目名称，级别，注册时间，预计工期。(3)(3)将项目编号设置为标识字段，种子为将项目编号设置为标识字段，种子为1 1，增量为，增量为1 1；项目名称；项目名称的默认值为的默认值为”UNKNOWN”;”UNKNOWN”;预计工期为级别乘以预计工期为级别乘以2020。Usecompanygocreatetableproject_back(项目编号intIDENTITY(1,1),项目名称varchar(40)DEFAULTUNKNOWN,级别int,注册时间varchar(20),预计工期as级别*20)2使用企业管理器创建表返返 回回作业作业以下作业内容分别用企业管理器和以下作业内容分别用企业管理器和T-sql语言实现语言实现一、创建数据库一、创建数据库Group1Group1二、在二、在Group1Group1数据库中编写并执行创建用户自定义数据类型的语句，需数据库中编写并执行创建用户自定义数据类型的语句，需要创建的数据类型表格如下：该过程的脚本放在要创建的数据类型表格如下：该过程的脚本放在E:Microsoft SQL ServerMSSQLDatacreatyp1.sql中中数据类型数据类型数据描述数据描述CityCity最多最多1515个字节的字符数据，可以个字节的字符数据，可以nullnullTelephoneTelephone2020个字符数据，可以为个字符数据，可以为nullnullRegionRegion最多最多1515个字节的字符数据，不可以个字节的字符数据，不可以nullnull三、使用语句创建如下表格，该过程的脚本放在三、使用语句创建如下表格，该过程的脚本放在E:Microsoft SQL ServerMSSQLDatacreatab1.sql中中列名列名数据类型数据类型允许空值允许空值Identity属性属性SIDint不允许种子=1增量=1SNamenvarchar(20)不允许无Ssexnchar(5)不允许无Sageint允许无SCityCityCityCity允许无TelephoneTelephoneTelephoneTelephone允许无RegionRegionRegionRegion允许无5.3 修修 改改 表表1使用ALTERTABLE命令修改表结构命令格式：命令格式：ALTER TABLEtable_nameALTERCOLUMN column_name new_data_typeNULL|NOTNULL|ADDcolumn_name data_typeNULL|DEFAULT|DROPCOLUMNcolumn_name,.n Sql server规定：存规定：存在值约束或其他约束，在值约束或其他约束，须先删除它们须先删除它们说明：说明：1.ALTERTABLE：该关键字表示本命令将修改表的结构2.table_name：指定需要修改的表名3.ALTERCOLUMNcolumn_name：column_name指定需要修改的列名称命令关键字4.new_data_typeNULL|NOT NULL：新的：新的数据类型，可以修改其非空属性可以修改其非空属性 5.ADD column_name data_type NULL|DEFAULT：添：添加的列的名称及其数据类型，可添加加的列的名称及其数据类型，可添加空值空值NULL或或DEFAULT缺省值缺省值 6.除除DROP 外，该命令一次只允许更改表的一个属性或外，该命令一次只允许更改表的一个属性或修改一列修改一列 可删除多列举例：举例：CREATE TABLE doc_exb(column_a INT,column_b VARCHAR(20)NULL)GO ALTER TABLE doc_exb DROP COLUMN column_b GO 5.4使用企业管理器修改表结构设计表设计表重新命名表重新命名表1用系统存储过程修改表的名称用系统存储过程修改表的名称命令格式：sp_renameobjname=object_name,newname=new_name2使用企业管理器修改表的名称使用企业管理器修改表的名称自己做一下吧！返返 回回2 使用企业管理器查看表属性使用企业管理器查看表属性返返 回回5.5删除表使用使用T-SQL语句语句命令格式：DROPTABLEtable_name 对于不再需要的表，可以通过对于不再需要的表，可以通过T-SQL语句或企语句或企业管理器删除业管理器删除说明：删除表的权力仅属于表的拥有者不能使用DROPTABLE命令删除系统表SQLServer禁止这样做，不是道德上的看下页返返 回回