项目三-公司管理数据系统的表的创建和修改课件

项目三项目三 公司管理数据系统公司管理数据系统的表的创建和修改的表的创建和修改终极目标：会创建公司管理数据库终极目标：会创建公司管理数据库系统用户数据表，并能根据需要实系统用户数据表，并能根据需要实现表的修改和删除操作现表的修改和删除操作工作任务在SQLServer2000数据库管理系统下，在companyinfo数据库中完成公司管理数据库系统各数据表的创建，并能根据实际需要进行数据表的修改、删除及数据的相关操作。2模块1公司管理数据系统数据表的建立教学目标：1会用企业管理器和TransactSQL语句创建数据表2会用企业管理器和TransactSQL语句创建外键约束以实现数据表间的关联3工作任务（1）1、创建名称为“product”的产品信息表，该表结构如表3-1所示，要求利用企业管理器和TransactSQL语句两种方法实现。表3-1product表字段名称字段名称数据类型数据类型预计长度预计长度特殊限制特殊限制产品产品IDIDintint默认默认主键主键产品名产品名intint默认默认类别类别IDIDintint默认默认单价单价moneymoney默认默认库存量库存量intint默认默认4一、系统数据类型1.整型数据类型2.浮点数据类型3.字符数据类型4.日期和时间数据类型5.文本和图形数据类型6.货币数据类型货币数据类型 7.位数据类型位数据类型 8.二进制数据类型二进制数据类型 9.特殊数据类型特殊数据类型 10.新增数据类型新增数据类型数据类型就是定义每一列所能存放的数据值和数据格式。数据类型就是定义每一列所能存放的数据值和数据格式。51.整型数据类型整型数据类型是最常用的数据类型之一，它主要用来存储数值，可以直接进行数据运算，而不必使用函数转换。int（integer）：可以存储从-231231-1（-2147483648（2147483647）范围之间的所有正负整数。Smallint：可以存储从-213213-1（-3276832767）范围之间的所有正负整数。2个字节Tinyint：可以存储从0到255范围之间的所有正整数。Bigint：存放从-263到263-1范围内的整型数据。以bigint数据类型存储的每个值占用8个字节，共64位，其中63位用于存储数字，1位用于表示正负。62.浮点数据类型浮点数据包括按二进制技术系统所能提供的最大精度保留的数据。浮点数值的数据在SQLServer中采用只入不舍的方式进行存储。Real：精确到第7位精确位数，范围从-3.40E-383.40E+38，占用4个字节的存储空间。Float：可以精确到第15位小数，其范围从-1.79E-308到1.79E+308，占用8个字节的存储空间。语法：floatn,n为数据精度17定义real,815为float7小数数据类型Decimal和numeric：Decimal数据类型和numeric数据类型完全相同，它们可以提供小数所需要的实际存储空间，但也有一定的限制，可以用2到17个字节来存储从-1038-1到1038-1之间的数值。8例：存储数值1829.3455比存储5.20需要更加多字节。声明格式：numeric(p,s)|decimal(p,s)其中p为精度，s为小数位数，s的默认值为0.例：decimal(6,3)若向该列赋值65.4542345则实际存储的是65.45493.字符数据类型SQLServer提供了3类字符数据类型，分别是Char、Varchar和Text。在这3类数据类型中，最常用的Char和Varchar两类。字符数据类型可以用来存储各种字母、数字符号和特殊符号。10Char 利用Char数据类型存储数据时，每个字符占用一个字节的存储空间。Char数据类型使用固定长度来存储字符，最长可以容纳8000个字符。格式：char(n)n表示所有字符所占的空间，默认值为1。v如果实际数据的字符长度短于给定的最大长度，则多余的字节会用空格填充。v如果实际数据的字符长度超过了给定的最大长度，则超过的字符将会被截断。在使用字符型常量为字符数据类型赋值时，必须使用单引号（）将字符型常量括起来。11VarcharVarchar数据类型来存储最长可以达到8000字符的变长字符。与Char数据类型不同，Varchar数据类型的存储空间随存储在表列中的每一个数据的字符数的不同而变化。格式：Varchar(n)n表示所有字符所占的空间，18000。例:定义表列为Varchar(20)，那么存储在该列的数据最多可以长达20个字节。但是在数据没有达到20个字节时并不会在多余的字节上填充空格。当存储在列中的数据的值大小经常变化时，使用Varchar数据类型可以有效地节省空间。但是char比Varchar的处理速度快。12Text当要存储的字符型数据非常庞大以至于8000字节完全不够用时，Char和Varchar数据类型都失去了作用。这时应该选择Text数据类型。Text：用于存储大量文本数据，其容量理论上为1到231-1（21，4748，3647）个字节，但实际应用时要根据硬盘的存储空间而定。Ntext：与text数据类型类似，存储在其中的数据通常是直接能输出到显示设备上的字符，显示设备可以是显示器、窗口或者打印机。134.日期和时间数据类型代表日期和一天内的时间的日期和时间数据类型。Datetime：用于存储日期和时间的结合体。从1753年1月1日到9999年12月31日的日期和时间数据，精确度为百分之三秒（等于3.33毫秒或0.00333秒）。Smalldatetime：从1900年1月1日到2079年6月6日的日期和时间数据精确到分钟。SQLServer在用户没有指定小时以上精度的数据时，会自动设置Datetime和Smalldatetime数据的时间为00:00:00。14输入日期和时间数据常用的日期表示格式：月/日/年：5/1/2003、05/01/03月-日-年：5-1-2003、05-01-03月.日.年：5.1.2003、05.01.03常用的时间表示格式：时：分08：05时：分：秒08：05：25时：分：秒：毫秒08：05：25：23时：分AM|PM08：05AM155.货币数据类型Money：用于存储货币值，存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，存储范围为-922337213683477.3808到922337213683477.3808，精度为货币单位的万分之一。Smallmoney：与money数据类型类似，但其存储的货币值范围比money数据类型小，其存储范围为-214748.3468到214748.3467。167.位数据类型Bit数据相当于其他语言的逻辑型数据。他存储0和1,长度为1字节。178.二进制数据类型binary(n)：固定长度的n 个字节二进制数据。N 必须从1到8,000。存储空间大小为n+4字节。varbinary(n)：n个字节变长二进制数据。n 必须从1到8,000。存储空间大小为实际输入数据长度+4个字节，而不是n 个字节。输入的数据长度可能为0字节。189.特殊数据类型Timestamp：亦称时间戳数据类型，数据库范围的唯一数字，每次更新行时也进行更新。timestamp这种数据类型表现自动生成的二进制数，确保这些数在数据库中是唯一的。存储大小为8字节。一个表只能有一个timestamp列。每次插入或更新包含timestamp列的行时，timestamp列中的值均会更新。1910.新增数据类型（1）Bigint：用于存储从-263（-9，223，372，036，834，773，807）到263-1（9，223，372，036，834，773，807）之间的所有正负整数。sql_variant：用于存储除文本、图形数据和timestamp类型数据外的其它任何合法的SQLServer数据。sql_variant类型的列可以包含不同数据类型的行。例如，定义为sql_variant的列可以存储int、binary和char值。不能使用sql_variant存储的值类型只有text、ntext、image、timestamp和sql_variant。sql_variant的最大长度可达8016字节。2010.新增数据类型（2）table：用于存储对表或者视图处理后的结果集。这种新的数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。2111.用户自定义数据类型（1）用户定义的数据类型基于在MicrosoftSQLServer中提供的数据类型。当几个表中必须存储同一种数据类型时，并且为保证这些列有相同的数据类型、长度和可空性时，可以使用用户定义的数据类型。例：SQL Server 实 例 包 括 一 个 名 为 sysname的用户定义数据类型。sysname在功能上与nvarchar(128)等效。22创建用户定义的数据类型时提供参数：q名称q新数据类型所依据的系统数据类型q为空性（数据类型是否允许空值）如果为空性未明确定义，系统将依据数据库或连接的ANSINull默认设置进行指派。23创建数据类型的方法：1.使用企业管理器（EnterpriseManager）创建用户自定义数据类型。2.利用系统存储过程创建用户自定义数据类型。系统存储过程sp_addtype为用户提供了T_SQL语句创建自定义数据类型的途径。语法：sp_addtype typename=type,phystype=system_data_type,nulltype=null_type24【例1】创建一个用户定义的数据类型ssn，其基于的系统数据类型是变长为11的字符，不允许空。命令如下：Execsp_addtypessn,varchar(11),NotNull【例2】创建一个用户定义的数据类型birthday，其基于的系统数据类型是datetime，允许空。命令如下：Execsp_addtypebirthday,datetime,Null25删除用户自定义的数据类型1.使用企业管理器（EnterpriseManager）删除用户自定义数据类型。2.利用T-SQL删除用户自定义数据类型。系统存储过程sp_droptype为用户提供了T_SQL语句创建自定义数据类型的途径。语法：sp_droptype typename=type26【例3】删除自定义的生日数据类型。exec sp_droptype birthday其运行结果如下：其运行结果如下：（1 row(s)affected）（0 row(s)affected）Type has been dropped.27【例4】假定用户已定义了一个数据类型ssn，则删除该数据类型的命令为：Execsp_droptypessn注意：当表中的列还正在使用用户定义的数据类型时，或者在其上面还绑定有默认或者规则时，这种用户定义的数据类型不能删除。28(二)表的概念（1）表是数据存储的地方，在SQLServer中所有的数据存储都基于表，表是数据库中最重要的部分。表由行和列组成。表中的一行称为一条记录，表中的一列称为一个字段。29（二)表的概念（2）每个SQLServer数据库可容纳多达20亿个表，每个表中至多可以有1024列，每一行最多允许有8086个字节。行数和表的大小只受服务器中可用存储空间的限制。30（二)表的概念（3）SQLServer2000的数据表可分为用户表和系统表两种类型。用户表是数据库用户根据自己的设计创建的表，用于存放用户的数据，系统表则是SQLServer2000为实现数据库维护而创建的表，任何用户都不应该直接修改系统表，否则会导致数据库无法使用。31（三）数据表的创建1.利用企业管理器创建表步骤如下：（1）在树形目录中找到要建表的数据库，展开该数据库。（2）选择表，单击鼠标右键，在弹出的快捷菜单中选择“新建表”命令，表设计器如图所示。（3）表设计器的上半部分有一个表格，在这个表格中输入列的属性，表格的每一行对应一列。对每一列都需要进行以下设置，其中前三项是必须在建表时给出的，它们是：列名、数据类型、长度。（4）定义好所有列后，单击工具栏上的保存按扭，表就创建完成了。32表设计器3-133q数据类型：数据类型是一个下拉列表框，其中包括了所有的系统数据类型和数据库中的用户自定义数据类型。q长度：如果选择的数据类型需要长度，则指定长度。q允许空：单击鼠标，可以切换是否允许为空值的状态，勾选说明允许为空值，空白说明不允许为空值，默认状态下是允许为空值的。表设计器3-234表设计器3-3表设计器的下半部分是特定列的详细属性，包括是否是标识列、是否使用默认值等。描述：定义字段的说明信息。默认值：定义字段的默认值。如果在输入记录时没有指定该字段的值，便将该字段设置为默认值。标识：定义是否允许字段自动编号，其值可以为“否”、或“是（不用于复制）”。都表示允许字段自动编号，只是后者在通过复制方法添加数据时不自动编号。标识种子：定义字段自动编号的初值，默认值为1。标识递增量：定义字段自动编号的递增量，默认值为1。公式：定义计算字段的表达式。在定义表达式时，字段名称应使用括起来，例如，a+b+10，其中a，b分别为字段名称。排序规则：定义字段的排序规则。35工作任务（1）1、创建名称为“product”的产品信息表，该表结构如表3-1所示，要求利用企业管理器和TransactSQL语句两种方法实现。表3-1 product表字段名称数据类型预计长度特殊限制产品IDint默认主键产品名int默认类别IDint默认单价money默认库存量int默认36方法一、利用企业管理器实现步骤如下：1.打开SQLServer企业管理器。2.展开companyinfo数据库节点，用鼠标右击“表”对象，在打开的快捷菜单中执行【新建表】命令。3.在打开的“表设计器”对话框中。定义完成各字段后。说明：当创建表时，必须指定表名、列名以及数据类型。同一个表中，列名不能重复，但是，同一个数据库中不同表的列名称可以相同。并必须为每个列都指定数据类型。37方法二利用create命令创建表CREATE TABLE table_name(CREATE TABLE table_name(/*/*列的定义列的定义*/|column_name AS computed_column_expression /*|column_name AS computed_column_expression /*定义计算列定义计算列*/|/*|/*指定表的约束指定表的约束*/ON filegroup|DEFAULT /*ON filegroup|DEFAULT /*指定存储表的文件组指定存储表的文件组*/TEXTIMAGE_ON filegroup|DEFAULT TEXTIMAGE_ON filegroup|DEFAULT /*/*指定存储指定存储text,ntexttext,ntext和和imageimage类型数据的文件组类型数据的文件组*/用“CREATE TABLE表名”来创建表，表中列的定义必须有括号括起来。一个表最多有1024列。语法：38说明：:=:=column_name data_type /*column_name data_type /*指定列明、类型指定列明、类型*/DEFAULT constant_expression /*DEFAULT constant_expression /*指定默认值指定默认值*/|IDENTITY|IDENTITY(seed,increment)(seed,increment)NOT NOT FOR FOR REPLICATION REPLICATION /*/*指定默认值指定默认值*/.n .n /*/*指定列的约束指定列的约束*/39各参数说明2-1：database_name：用于指定在其中创建表的数据库名称。owner：用于指定新建表的所有者的用户名。table_name：用于指定新建的表的名称。column_name：用于指定新建表的列的名称。computed_column_expression：用于指定计算列的列值的表达式。ONfilegroup|DEFAULT：用于指定存储表的文件组名。TEXTIMAGE_ON：用于指定text、ntext和image列的数据存储的文件组。data_type：用于指定列的数据类型。DEFAULT：用于指定列的缺省值。40constant_expression：用于指定列的缺省值的常量表达式。IDENTITY：用于指定列为标识列。Seed：用于指定标识列的初始值。Increment：用于指定标识列的增量值。NOTFORREPLICATION：用于指定列的IDENTITY属性在把从其它表中复制的数据插入到表中时不发生作用，即不足的生成列值，使得复制的数据行保持原来的列值。column_constraint和table_constraint：用于指定列约束和表约束。各参数说明2-2：41方法二：利用Transact-SQL语句创建实现操作步骤：1在打开“查询分析器”窗口。2连接数据库：在“查询分析器”窗口的工具栏的下拉列表中，选中companyinfo数据库。3在查询分析器的查询窗口中输入如下：CREATETABLEproduct(产品IDintprimarykey，产品名char(10)notnull，类别IDint，单价money,库存量int)42（二）按照任务2要求创建companyinfo数据库的其它数据表。1创建表名为“p_order”的产品订单表的命令如下：CREATETABLEp_order(订单IDintprimarykey,产品IDint,数量int,雇员IDint,客户IDint,订货日期datetime)GO432创建表名为“employee”的产品信息表的命令如下：CREATE TABLE employee(雇员ID int primary key,姓名 varchar(8),性别 char(2),出生日期 datetime,雇佣日期 datetime,特长 varchar(20),薪水 money)GO443创建名为“customer”的客户表和名为“category”的类别表的命令如下：CREATE TABLE customer(CREATE TABLE customer(/*/*创建客户表创建客户表customer*/customer*/客户客户ID int primary key,ID int primary key,公司名称公司名称 varchar(30),varchar(30),联系人姓名联系人姓名 varchar(8),varchar(8),联系方式联系方式 varchar(12),varchar(12),地址地址 varchar(30),varchar(30),邮编邮编 char(6)char(6)GOGO45创建类别表CREATE TABLE category(/*创建类别表category*/类别ID int primary key,类别名 varchar(10),说明 varchar(16)GO46（三）按照任务3的要求，创建名称为DIAGRAM_p_order_product的数据库关系图，从而实现p-order表和product表间的关联。47数据约束约束是在创建表时，可以对某一列或多个列的组合设置限制条件，让SQLServer帮助检查该列的输入值是否符合要求。当用户从客户端传送数据到SQLServer服务器，系统也会检查数据是否符合要求，如果不符合要求将拒绝接受。48约束的实现：约束的实现：设置主键（PRIMARYKEY）约束、唯一（UNIQUE）约束、外键（FOREIGNKEY）约束、缺省（DEFAULT）约束和检查（CHECK）约束等。注意：对于BYTE和TEXT列不可以指定主键约束、唯一约束和外键约束，但可以用检查约束来检查其值是否为NULL。49约束的创建约束的创建约束作为表的一部分，可以在创建表的同时创建约束，也可以在建立表之后追加定义或删除定义。在一个表中可以定义多个约束，甚至可以在一个列上定义多个约束。50约束分类1、列级约束定义在一个列上的约束，是列定义的一部分，与列名、数据类型、唯一标识号、默认值和排序规则等一样，都是列的属性。严格地说列的数据类型也是一种列约束，它限定了列的取值的域。2、表级约束在列定义外单独定义的，它是多列之间的约束，用CONSTRAINT关键字定义。注意：如果要对一个表中的多个列定义约束，必须首先定义每个列，然后在末尾追加表约束。511.主关键字（PRIMARY KEY）约束 一个表中，存在这样的字段，它在每个记录中的值都是惟一的，因此可以用来标识表中的各个不同的纪录。这样的字段被称为关键字。在这些列上创建主关键字约束，确保数据表的一列或几列的组合的值在表中具有惟一性。52（1）在企业管理器中定义主键约束 “表设计器”对话框中，选择要设置为主键的列，可以选择一个列，也可以选择多个列。在所选的字段左端的选择按钮上右击鼠标，在弹出的菜单中选择“设置主键”或点击工具栏上的图标 按钮。说明：如果主关键字包含多个字段，则在定义时，首先按住【Ctrl】键或【Shift】键，再选择字段。按【Ctrl】键可选择不连续的多个字段，按【Shift】键则只能选择连续的多个字段。53（2）使用Transact-SQL语句定义主关键字约束的语法如下:CONSTRAINT constraint_nameCONSTRAINT constraint_name PRIMARY PRIMARY KEY KEY CLUSTERED|NONCLUSTERED CLUSTERED|NONCLUSTERED (column_namel,column_name2,.,(column_namel,column_name2,.,column_namel6)column_namel6)54各参数说明:vconstraint_name:指定约束的名称。约束的名称在数据库中应是惟一的。如果不指定，则系统会自动生成一个约束名。vCLUSTERED|NONCLUSTERED:指 定 索 引 类 别，CLUSTERED为缺省值。其具体信息请参见索引章节。vColumn_name:指定组成主关键字的列名。主关键字最多由16个列组成。说明：每个表只允许存在一个主键约束（PRIMARY KEY）。55【例 6】创 建 一 个 产 品 信 息 表（product_info），以产品编号和名称为主关键字。CREATETABLEproduct_info(产品IDint，产品名称char(10)notnull，单价moneydefault10，库存量intnull，总价值as单价*库存量，/*产品总价值=单价*库存量*/CONSTRAINTP_KP_IDPRIMARYKEY(产品ID,产品名称)562.外键（FOREIGN KEY）约束 外键约束定义了表之间的关系。当一个表中的一个列或多个列的组合和其它表中的主关键字属性相同时，就可以将这些列或列的组合定义为外关键字，并设定已适合哪个表中哪些列相关联。作用：q当在定义主关键字约束的表中更新列值时，其它与之相关联的外关键字约束的表中的外关键字字段的值也将被自动更新。q当向含有外关键字的表插入数据时，如果与之相关联的表的字段中无与插入的外关键字字段值相同的值时，系统会拒绝插入数据。57【例7】数据库pubs中的titles表与publishers表有链接，因为在书名和出版商之间存在逻辑联系。titles表中的pub_id列与publishers表中的主键列相对应。titles表中的pub_id列是到publishers表的外键。Primary Key58（1）在企业管理器中创建外键约束操作步骤如下：1打开companyinfo数据库2.执行【新建数据库关系图】命令.3打开创建数据库关系图向导，进入数据库关系图创建窗口。4在数据库关系图创建窗口的工具拦中，单击“在关系图上添加表”按钮。将打开“添加表”对话框。5在“添加表”对话框中，选择主键表product表，并单击“添加”按钮，再选择外键表p_oreder表，并单击“添加”按钮。然后关闭“添加表”对话框。6将鼠标移动到product表中的“产品ID”左边的选择栏，单击左键，并将鼠标指针拖动到p_oreder表中的“产品ID”字段上。59（2）使用Transact-SQL语句定义外关键字约束语法如下语法如下:CONSTRAINT CONSTRAINT constraint_name constraint_name FOREIGN FOREIGN KEY KEY(column_namel,column_name2,.,(column_namel,column_name2,.,column_namel6)column_namel6)REFERENCES REFERENCES ref_table ref_table(ref_columnl,(ref_columnl,ref_column2,.,ref_columnl6)ref_column2,.,ref_columnl6)ON DELETECASCADE|NO ACTION ON DELETECASCADE|NO ACTION ON UPDATECASCADE|NO ACTION ON UPDATECASCADE|NO ACTION NOT FOR REPLICATION NOT FOR REPLICATION60各参数说明:vREFERENCES：指定要建立关联的表的信息。vref_table：指定要建立关联的表的名称。vref_column：指定要建立关联的表中的相关列的名称。vONDELETECASCADE|NOACTION：指定在删除表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下，如果指定了值CASCADE，则在删除父表数据行时会将子表中对应的数据行删除；如果指定的是NO ACTION，则SQL Server会产生一个错误，并将父表中的删除操作回滚。NO ACTION是缺省值。vONUPDATECASCADE|NOACTION：指定在更新表中数据时，对关联表所做的相关操作。vNOTFORREPLICATION：指定列的外关键字约束在把从其它表中复制的数据插入到表中时不发生作用。61【例8】创建一个课程表，然后创建一个成绩表与前面创建的学生表和课程的关联。脚本文件3-1：usestudentsgoCREATETABLE学生(学号char(6)NOTNULL,性别char(2)NOTNULL,姓名char(10)NOTNULL,学分intconstraintpk_student_idprimarykey(学号)go62脚本文件3-2CREATETABLE课程(课程号char(12)NOTNULL,课程名char(12)NOTNULL,学时char(10)NOTNULL,学分char(10)NOTNULLconstraintpk_course_idprimarykey(课程号)Go63CREATETABLE成绩（学号char(6)NOTNULL,课程号char(12)NOTNULL,课程名char(12)NOTNULL,成绩intNOTNULL,补考成绩intNOTNULLconstraintpk_grade_idprimarykey(学号,课程号),constraintfr_student_idforeignkey(学号)references学生(学号),constraintfr_course_idforeignkey(课程号)references课程(课程号)脚本文件3-3643.唯一性(UNIQUE)约束唯一性约束指定一个或多个列的组合的值具有唯一性，以防止在列中输入重复的值。唯一性约束指定的列可以有NULL属性。由于主关键字值是具有唯一性的，因此主关键字列不能再设定唯一性约束。唯一性约束最多由16个列组成。65（1）在企业管理器中定义惟一性键【例9】将“product”表中的产品ID列设定为唯一性约束。q在“product”表上右击鼠标，在弹出的菜单上点击“设计表”，进入表的设计窗口。q在设计面板的空白处右击鼠标，在弹出的菜单中选择“属性”。选择“索引/键”选项卡q单击“新建”按钮，系统分配的名称将出现在“索引名”文本框中。66（2）使用Transact-SQL语句定义惟一性约束语法:CONSTRAINTconstraint_nameUNIQUECLUSTERED|NONCLUSTERED(column_namel,column_name2,.,column_name16)67【例10】定义一个员工信息表，其中员工的身份证号具有惟一性。CREATETABLEemployee_info(雇员IDchar(8),姓名char(10)，身份证号char(18)constraintpk_emp_idprimarykey(雇员ID)，constraintuk_emp_cardidunique(身份证号)68例：例：CREATETABLEjsy_temp3(驾照号驾照号 char(8)PRIMARYKEY,姓名姓名varchar(8)NOTNULL,身份证号身份证号char(18)NOTNULLUNIQUENONCLUSTERED）6970714.检查（CHECK）约束检查约束对输入列或整个表中的值设置检查条件，以限制输入值，保证数据库的数据完整性。可以对每个列设置符合检查。72（1）在企业管理器中设定CHECK约束的方法【例11】“product”表中的库存量列设定CHECK约束，使此列的值限制在100到1000之间。q启动企业管理器，展开数据库，在“product”表上右击鼠标，在弹出的菜单上点击“设计表”，进入表的设计窗口。q在设计面板的空白处右击鼠标，在弹出的菜单中选择“属性”。选择“CHECK约束”选项卡，将弹出对话框。q对话框中“CHECK约束”选项卡q单击“新建”按钮，“选定的约束”列表框显示了由系统分配的新约束名。系统分配的名称以CK开始，后跟表名q在“约束表达式”框中，为CHECK约束键入SQL表达式，如将“product”表中的“库存量”列的数据限制在100到1000之间，即输入：“库存量=100and库存量=10)注意：对计算列不能作除检查约束外的任何约束。755.缺省（DEFAULT）约束缺省约束通过定义列的缺省值或使用数据库的缺省值对象绑定表的列，来指定列的缺省值。SQLServer推荐使用缺省约束，而不使用定义缺省值的方式来指定列的缺省值。语法:CONSTRAINTconstraint_nameDEFAULTconstant_expressionFORcolumn_name76例：创建一个司机表脚本文件脚本文件1：Createtabledriver(姓名姓名varchar(8)NOTNULL,是否见习是否见习char(1)DEFAULT(是是),身份证号身份证号char(18)NOTNULLUNIQUE,电话电话char(8)Constraintjianxi_dedefault(是是)776、NOTNULL约束NOTNULL约束又称非空约束，表示使用该约束的列不允许使用空值。若该列是主键，则系统强制主键列为非空约束，而其他列的非空约束必须根据需要加以设置。78模块2 公司管理数据库系统数据表的修改和删除教学目标1会用企业管理器和Transact-SQL语句修改数据表的结构2会用企业管理器和Transact-SQL语句删除数据表3能采用各种约束实现数据完整性79工作任务修改companyinfo数据库中的数据表，具体要求如下：1修改前面创建的employee表，将“特长”字段的数据类型改为varchar,长度为100。2先创建一个名为order_tab2的表，然后再删除。3将employee表中的姓名字段强制非空约束。4将employee表中性别字段创建一个DEFAULT约束，默认值为男。80一、修改表 v利用企业管理器增加、删除和修改字段。在企业管理器中，打开指定的服务器中要修改表的数据库，用右键单击要进行修改的表，从弹出的快捷菜单中选择设计表选项，则会出现设计表对话框，在该对话框中，可以利用图形化工具完成增加、删除和修改字段的操作。v利用Transact-SQL语言中的altertable命令增加、删除和修改字段。通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。811、利用企业管理器方法 工作任务（一）修改前面创建的employee表，将“特长”字段的数据类型改为varchar,长度为100。（1）展开SQL Server服务器和相应的数据库。点击“表”节点，在employe 表上右击鼠标，在打开的快捷菜单中执行【设计表】命令。（2）在打开的设计表结构的表设计器中修改列的数据类型改为varchar，长度修改为100。82 2、利用Transact-SQL语句的方法ALTER TABLE命令（1）ALTER TABLEALTER TABLE命令可以添加或删除表的列、约束。命令可以添加或删除表的列、约束。语法语法:ALTER TABLE table_name ALTER TABLE table_name ALTER COLUMN column_name ALTER COLUMN column_name /*/*修改已有列的属性修改已有列的属性*/new_data_type(precision,scale)new_data_type(precision,scale)COLLATE COLLATE NULL|NOT NULL NULL|NOT NULL|ADD|DROP ROWGUIDCOL|ADD|DROP ROWGUIDCOL 83ALTER TABLE命令（2）/*/*增加新列增加新列|ADD|ADD|column_name column_name AS AS column_expression column_expression ,.n ,.n|WITH CHECK|WITH NOCHECK ADD|WITH CHECK|WITH NOCHECK ADD ,.n ,.n 84ALTER TABLE命令（3）/*/*删除列删除列|DROP|DROP CONSTRAINT constraint_name CONSTRAINT constraint_name|COLUMN column ,.n|COLUMN column ,.n|CHECK|NOCHECK CONSTRAIN|CHECK|NOCHECK CONSTRAIN ALL ALL|constraint_name constraint_name ,.n,.n|ENABLE|DISABLE TRIGGER|ENABLE|DISABLE TRIGGER ALL|trigger_name ,.n ALL|trigger_name ,.n 85 参数说明:vtable_name：指定要修改的表的名称。vcolumn_name：指定列的名称。vnew_data_type：指定要更改的列的新数据类型。vADD|DROPS ROWGUIDCOL：加或删除列的ROWGUIDCOL属性。vADD：添加一个或多个列、计算列或表约束的定义。vcomputed_columnexpression：计算列的计算表达式。vDROPCONSTRAINTconstraintname|COLUMNcolumn_name：指定要删除的约束或列的名称。vCONSTRAINT：删除数据库中的约束。vCOLUMNcolumn_name：指定删除数据库中的列。86（1）更改表以添加新列【例13】添加一个允许空值的列，各行的新列中的值将为NULL。CREATETABLEdoc_exa(column_aINT)GOALTERTABLEdoc_exaADDcolumn_bVARCHAR(20)NULLGOEXECsp_helpdoc_exaGODROPTABLEdoc_exaGO87(2)更改表以除去列【例14】修改表以删除一列。CREATETABLEdoc_exb(column_aINT,column_bVARCHAR(20)NULL)GOALTERTABLEdoc_exbDROPCOLUMNcolumn_bGOEXECsp_helpdoc_exbGODROPTABLEdoc_exbGO88(3)添加具有默认值的可为空的列【例15】添加可为空的、具有DEFAULT定义的列，并使用WITHVALUES为表中的各现有行提供值。如果没有使用WITHVALUES，那么每一行的新列中都将具有NULL值。程序清单：ALTERTABLEMyTableADDAddDatesmalldatetimeNULLCONSTRAINTAddDateDfltDEFAULTgetdate()WITHVALUES89（4）更改表以添加具有约束的列【例16】向表中添加具有UNIQUE约束的新列。CREATETABLEdoc_exc(column_aINT)GOALTERTABLEdoc_excADDcolumn_bVARCHAR(20)NULLCONSTRAINTexb_uniqueUNIQUE(column_b)GOEXECsp_helpdoc_excGODROPTABLEdoc_exc90工作任务:利用Transact-SQL语句的方法1)打开查询分析器。2)输入如下Transact-SQL语句并执行。USE COMPANYINFOGOALTER TABLE employeeALTER COLUMN 特长 varchar(100)nullGO91二、删除表删除表时，表的结构定义、数据、全文索引、约束和索引都永久地从数据库中删除，原来存放表及其索引的存储空间可用来存放其它表。1、使用企业管理器删除表2、使用T-SQL的DROPTABLE语句删除表语句格式：DROPTABLETABLE_name92A、除去当前数据库内的表【例17】从当前数据库中删除titles1表及其数据和索引。DROPTABLEtitles1B、除去另外一个数据库内的表【例18】除去pubs 数据库内的authors2 表。可以在任何数据库内执行此操作。DROPTABLEpubs.dbo.authors293三、查看表的信息在数据库中创建表之后，可能需要查看有关表属性的一些信息（如列名、列的数据类型及其索引的特性等等）。而最重要的是需要查看表中的数据。1、使用企业管理器查看表2、使用系统存储过程sp_help查看表信息语句格式：sp_helpobjname=name 参数:objname=name 是sysobjects中的任意对象的名称，或者是在systypes表中任何用户定义数据类型的名称。94【例19】显示有关publishers表的信息。USEpubsEXECsp_helppublishers9596模块3公司管理数据系统表数据的操作教学目标1.会用企业管理器和TransactSQL语句插入记录2.会用企业管理器和TransactSQL语句修改记录3.会用企业管理器和TransactSQL语句删除记录97工作任务（1）1、利用企业管理器和TransactSQL语句，在COMPANYINFO数据库中的product表中插入如下记录，如表所示。产品ID产品名类别ID单价库存量（件）1000电风扇0012402341001冰箱00118506001002电视机001456010001003牙膏0022.51001004牙刷002360098工作任务（2）2、目前，市场出现一种新产品“电动牙刷”，它的产品代号为“1005”，单价为30元，类别代号为“002”，库存量为0，请将该产品的记录插入到product表中。3、由于国家政策的调价，将所有产品的单价上调10%。4、由于市场急速变化，产品信息表中数据不能准确反映信息，现删除所有的记录。99一、插入数据q使用企业管理器方法：选择“打开表”项，单击“返回所有行”命令项。q使用Transact-SQL语句方法：INSERT语句向表中插入数据语法：INSERTINTOtable_name(column_list)Values(Values_list)100参数说明：|table_name：指定要插入数据的表名。|column_list：是任选参数。如果在表中插入部分数据时，或要插入的列以不同的顺序被定义时，可使用它。|VALUES：指出要插入的表的列应取的值。|Values_list：要作为表的行插入的列的值列表。101【例20】向类别信息表（category）中，插入数据“3”、“文具类”和“所有文具”。USECOMPANYINFOGOINSERTcategory（类别ID，类别名，说明）VALUES(3，文具类，所有文具)102需要注意的是：（1）输入的顺序和数据类型必须与表中列的顺序和数据类型一致。（2）可以不给全部列赋值，但没有赋值的列必须是可以为空的列。（3）标识列，不必输入数据。（3）字符型和日期型值插入时要用单引号扩起来。1031、插入与列顺序不同的数据【例21】使用column_list及VALUES列表显式地指定将被插入每个列的值。CREATETABLET1(readerIDint,readerNamevarchar(30)INSERTT1(readerName,readerID)VALUES(Rower,1)1042、插入值少于列个数的数据【例22】创建一个带有四个列的表。INSERT语句插入一些行，这些行只有部分列包含值。CREATETABLET1(col1int,col2varchar(30)DEFAULTcolumndefault,col3int,col4varchar(40)goINSERTINTOT1(col4)VALUES(Explicitvalue)INSERTINTOT1(col2,col4)VALUES(Explicitvalue,Explicitvalue)INSERTINTOT1(col2,col3,col4)VALUES(Explicitvalue,-44,Explicitvalue)105106二、UPDATE语句修改表中的数据在表修改数据时，可以用UPDATE语句来实现。语法：UPDATE表名SET列名=表达式WHERE条件107【例23】在产品信息表中，将所有库产量小于200的产品的库存量置为0。程序USECOMPANYINFOGOUPDATEproductSET库存量=0WHERE库存量=200GO1081、更新一列1092、更新多列还可以通过省略还可以通过省略WHERE子句或在子句或在WHERE子句中子句中包含一个可以影响多行的条件来更新表中的多行数据。包含一个可以影响多行的条件来更新表中的多行数据。110三、DELETE语句删除表中数据可以用的DELETE语句来实现。语法：DELETEFROMtable_nameWHEREcondition参数说明：qtable_name：要删除的行的表名。qtables：是用于设定删除条件所需的表名。qcondition：指定删除行的条件。111【例24】在产品信息表中，删除所有库产量小于200的产品信息。USECOMPANYINFOGODELETEproductWHERE库存量=200GO1121、删除一行数据DELETEFROMWHERE1132、删除多行数据可以通过省略可以通过省略DELETE语句中的语句中的WHERE子句或子句或在在WHERE子句中包含一个将删除多行的条件来删除多子句中包含一个将删除多行的条件来删除多行数据。行数据。114四、TRUNCATETABLE语句删除表中数据可以使用TRUNCATETABLE语句删除表中所有行。TRUNCATETABLE语句与DELETE语句类似。但是，TRUNCATETABLE语句执行更快。语法：TRUNCATETABLEtable_name注意：TRUNCATE TABLE语句删除表中的所有行，所以此语句不能包含WHERE字句。115拓展知识一、默认定义：当用户未指定时由系统自动指派的数据值、选项设置、排序规则或名称。当用户未指定某些事件发生后要采取的操作时所自动采取的操作。创建称为默认值的对象。当绑定到列或用户定义数据类型时，如果插入时没有明确提供值，默认值便指定一个值，并将其插入到对象所绑定的列中（或者，在用户定义数据类型的情况下，插入到所有列中）。116v在 CREATE TABLE 中使用 DEFAULT 关键字创建默认定义，将常量表达式指派为列的默认值。v使用 CREATE DEFAULT 语句创建默认对象，然后使用 sp_bindefault 系统存储过程将它绑定到列上。语法：CREATEDEFAULTdefault_nameAS表达式1、有两种使用默认值的方法：117【例25】创建简单的字符默认值”男”。USEpubsGOCREATEDEFAULTSexdlfAS男118系统存储过程sp_bindefault，将默认值绑定到列或用户定义的数据类型。语法：sp_bindefaultdefname=default,objname=object_name参数：vdefname=default：由CREATEDEFAULT语句创建的默认名称。vobjname=object_name：要绑定默认值的和列名称或用户定义数据类型。默认情况下，用户定义数据类型的现有列继承default，除非默认值直接绑定到列中。2、默认值绑定1191、绑定默认对象1203、使用T_sQL语句删除绑定语法：execsp_unbindefault绑定列的名称121规则是一种数据库对象，可以被绑定到一个或多个列上，还可以被绑定到用户自己定义的数据类型上。用于执行一些与CHECK约束相同的功能。CHECK约束是用来限制列值的首选标准方法。CHECK约束比规则更简明，一个列只能应用一个规则，但是却可以应用多个CHECK约束。二、规则122创建称为规则的对象。当绑定到列或用户定义数据类型时，规则将指定可以插入到列中的可接受的值。语法:CREATERULErule_nameAScondition_expression参数condition_expression：是定义规则的条件。规则分类：范围规则、列表规则、模式规则1、创建规则1231）范围规则【例26】创建一个规则，用以限制插入该规则所绑定的列中的整数范围。CREATERULErange_ruleASrange=$1000ANDrange$20000124【例27】创建一个规则，用以将输入到该规则所绑定的列中的实际值限制为只能是该规则中列出的值。CREATE RULE list_rule AS list IN(1389,0736,0877)2）列表规则1253）模式规则【例28】创建一个遵循这种模式的规则：任意两个字符的后面跟一个连字符和任意多个字符（或没有字符），并以0到9之间的整数结尾。CREATERULEpattern_ruleASvalueLIKE_-%0-9126 系统存储过程sp_bindrule将规则绑定到列或用户定义的数据类型。语法语法:sp_bindrule rulename=sp_bindrule rulename=rulerule,objname=objname=object_nameobject_name参数：参数：vrulename=rule:由由CREATERULE语句创建的语句创建的规则名称。规则名称。vobjname=object_name:绑定了规则的表和列或绑定了规则的表和列或用户定义的数据类型。如果用户定义的数据类型。如果object_name没有采取没有采取table.column 格式，则认为它属于用户定义数据类型。格式，则认为它属于用户定义数据类