创建与管理数据库表.ppt

第5章 管理与使用表,5.1 数 据 类 型5.2 创 建 表5.3 修 改 表5.4 查看表属性5.5 删 除 表,表是重要数据库对象，是数据库的基本组成部分，是储存数据的逻辑载体 关系型数据库的表是二维的，表的一列称为一个字段；表的一行称为一个记录 SQL Server 2000中，一个表中最多允许1024列，表的行数没有限制，与磁盘存储空间有关,概述：,局部临时表：以开头，仅可由创建者本人在创建后立即使用，一旦断开连接，该表被删除 全局临时表 以开头，创建后可以由多个用户使用，当最后使用的用户断开连接，该表被删除,表,永久表permernet,：表一旦创建将一直存储在硬盘上，除非被用户删除,临时表：用户退出时自动被系统删除temp,5.1 数 据 类 型,系统提供的数据类型创建和删除用户定义的数据类型选择数据类型的指导原则,表的内容由列属性组成，数据类型是最重要的列属性，它决定了数据的存储格式（长度、精度、小数位数等）,系统提供的数据类型,数值型数据整型数据：存储整数小数数据：包含存储在最小有效数上的数据,近似数字数据：表示浮点数据的近似数字注意：存储的非精确值而只是近似值，在使用where进行查询时，避免使用数据类型为float和real的字段。另限制float和real的字段作和的比较,货币数据：表示正的或负的货币值注意：货币数据使用时，不需要用单引号 括起来，但货币数 值之前一定要带有适当的货币符号。如：$78,日期和时间数据字符数据和 Unicode 字符数据,可变长度,Unicode采用两个字节编码,可以存储日文、韩文和汉字,二进制数据其他,选择数据类型的指导原则,若列值的长度相差很大，那么使用变长数据类型例如某列存储的是人名，地址等谨慎使用 tinyint 数据类型虽然节省空间，但扩展性很小对于小数数据来说，一般使用 decimal 数据类型可以精确地控制精度如果行的存储量 超过8 000字节，使用 text 或者 image若不大于8 000字节，可使用char、varchar或者binary数据类型对于货币数据，使用 money 数据类型不要使用类型为 float 或者 real 的列作为主键因为它们不精确，所以不适合用于比较,创建和删除用户定义的数据类型,用户定义的数据类型（扩展SQL Server的基本数据类型）当多个表的列中要存储同样类型的数据，且想确保这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型注意：如果用户定义数据类型是在 model 数据库中创建的，它将作用于所有用户定义的新数据库中。如果数据类型在用户定义的数据库中创建，则该数据类型只作用于此用户定义的数据库。,创建用户定义的数据类型企业管理器系统存储过程语法sp_addtype typename=type,phystype=system_data_type,nulltype=null_type,owner=owner_name 删除用户定义的数据类型sp_droptype 类型名,企业管理器创建用户定义函数,展开服务器组，然后展开服务器。展开数据库文件夹，再展开要在其中创建用户定义的数据类型的数据库。右击用户定义数据类型，然后单击新建用户定义数据类型命令。输入新建数据类型的名称。在数据类型列表中，选择基数据类型。如长度处于活动状态，若要更改此数据类型可存储的最大数据长度，请键入另外的值。长度可变的数据类型有：binary、char、nchar、nvarchar、varbinary 和 varchar。若要允许此数据类型接受空值，请选择允许空值命令。在规则和默认值列表中选择一个规则或默认值（若有）以将其绑定到用户定义数据类型上,用户自定义函数示例(p350),A.创建不允许空值的用户定义数据类型下面的示例创建一个名为 ssn（社会保险号）的用户定义数据类型，它基于 SQL Server 提供的 varchar 数据类型。ssn 数据类型用于那些保存 11 位数字的社会保险号(999-99-9999)的列。该列不能为 NULL。USE master EXEC sp_addtype ssn,VARCHAR(11),NOT NULL 请注意，varchar(11)由单引号引了起来，这是因为它包含了标点符号（圆括号）。,B.创建允许空值的用户定义数据类型下面的示例创建了一个名为 birthday 的用户定义数据类型（基于 datetime），该数据类型允许空值。USE master EXEC sp_addtype birthday,datetime,NULL,C.创建另外的用户定义的数据类型下面的示例为国内及国际电话和传真号码另外创建两个用户定义的数据类型 telephone 和 fax。USE master EXEC sp_addtype telephone,varchar(24),NOT NULL EXEC sp_addtype fax,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 birthdaygo,5.2 创 建 表,创建表有三种方法：使用向导 使用企业管理器 使用T-SQL语句,1 使用CREATE TABLE命令创建表 在指定的数据库中创建表的基本语法格式如下页所示：,CREATE TABLE database_name.owner.|owner.table_name(|column_name AS computed_column_expression|:=CONSTRAINT constraint_name|PRIMARY KEY|UNIQUE,.n),说明：database_name.owner.|owner.table_name：定义表名table_name 可以选加数据库名database_name和表的所有者名owner 表名不得超过128个字符，临时表表名不得超过116个字符,:=column_name data_type COLLATE DEFAULT constant_expression|IDENTITY(seed,increment),column_name data_type：定义列 其中column_name为列名，data_type为数据类型 NULL|NOT NULL：指定所定义的列是否可以取空值 默认情况下是NULL DEFAULT constant_expression：指定列的默认值约束 当向表中插入一条记录时，如果本列插入的数据为空，则系统自动将默认值填充到本列 IDENTITY(seed,increment)：定义标识列,也称自动编号列，一个表中只能定义一个标识列,.n：表示可以在表中设计n个列的定义，列间用逗号隔开,起始值,T-Sql创建表示例,例一、pubs 数据库中所创建的表jobs定义，其中包含所有的约束定义。CREATE TABLE jobs(job_id smallint IDENTITY(1,1)PRIMARY KEY,job_desc varchar(50)NOT NULL DEFAULT New Position-title not formalized yet,min_lvl tinyint NOT NULL CHECK(min_lvl=10),max_lvl tinyint NOT NULL CHECK(max_lvl=250),例二、按如下要求在数据库company中创建一个数据表(1)表名：project_back.(2)字段：项目编号，项目名称，级别，注册时间，预计工期。(3)将项目编号设置为标识字段，种子为1，增量为1；项目名称的默认值为”UNKNOWN”;预计工期为级别乘以20。Use companygocreate table project_back(项目编号 int IDENTITY(1,1),项目名称 varchar(40)DEFAULT UNKNOWN,级别 int,注册时间 varchar(20),预计工期 as 级别*20),2 使用企业管理器创建表,返 回,作业,以下作业内容分别用企业管理器和T-sql语言实现一、创建数据库Group1二、在Group1数据库中编写并执行创建用户自定义数据类型的语句，需要创建的数据类型表格如下：该过程的脚本放在E:Microsoft SQL ServerMSSQLDatacreatyp1.sql中,三、使用语句创建如下表格，该过程的脚本放在E:Microsoft SQL ServerMSSQLDatacreatab1.sql中,5.3 修 改 表,1 使用ALTER TABLE命令修改表结构,命令格式：ALTER TABLE table_name ALTER COLUMN column_name new_data_type NULL|NOT NULL|ADD column_name data_type NULL|DEFAULT|DROP COLUMN column_name,.n,Sql server规定：存在值约束或其他约束，须先删除它们,说明：ALTER TABLE：该关键字表示本命令将修改表的结构 table_name：指定需要修改的表名ALTER COLUMN column_name：column_name指定需要修改的列名称,命令关键字,new_data_typeNULL|NOT NULL：新的数据类型，可以修改其非空属性 ADD column_name data_type NULL|DEFAULT：添加的列的名称及其数据类型，可添加空值NULL或DEFAULT缺省值 除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_rename objname=object_name,newname=new_name,2使用企业管理器修改表的名称,自己做一下吧！,返 回,2 使用企业管理器查看表属性,返 回,5.5 删 除 表,使用T-SQL语句命令格式：DROP TABLE table_name,对于不再需要的表，可以通过T-SQL语句或企业管理器删除,说明：,删除表的权力仅属于表的拥有者不能使用DROP TABLE命令删除系统表,SQL Server禁止这样做，不是道德上的,返 回,