销售管理数据库数据表的创建和管理课程课件

第四章销售管理数据库数据表的创建和管理技能目标学会创建数据表；学会创建和修改销售管理数据库中的数据表；学会使用约束来保证数据的完整性。SQLServer2005数据库应用技术清华大学出版社4.1.1表的基本概念表是组织和管理数据的基本单位,是数据库存储数据的主要对象。表是由行和列组成的二维结构。表中的一行称为一条记录，表中的一列称为一个字段订单号客户代号产品号单价数量订单日期10248VINET1114.00202006-07-0510248VINET429.80152006-07-0510249TOM2218.60102006-07-0610250JACK1114.00302006-07-0810250JACK4134.50252006-07-08字段/属性记录/元组SQLServer2005数据库应用技术清华大学出版社表的性质：在表中，行的顺序可以是任意的，一般按照数据插入的先后顺序存储。在使用过程中，可以使用排序语句或按照索引对表中的行进行排序。列的顺序也可以是任意的，对于每一个表，最多可以允许用户定义1024列。在同一个表中，列名必须是唯一的，即不能有名称相同的两个或两个以上的列同时存在于一个表中，但是，在同一个数据库的不同表中，可以使用相同的列名。并且在定义时为每一个列指定一种数据类型，数据类型规定了此字段数据的取值范围和存储格式。SQLServer2005数据库应用技术清华大学出版社数据类型数字数据（整型，精确数值型，浮点数值型）字符数据货币数据日期和时间数据二进制和图像型其他数据类型SQLServer2005数据库应用技术清华大学出版社4.1.2SQLServer2005中的数据类型

精度：指数值型数据可以存储的十进制数字的总位数，包括小数点左侧的整数部分和小数点右侧的小数部分。比如，1230.456的精度为7。小数位数：指数值型数据小数点右边的数字个数。比如，513.15的精度是5，小数位是2。长度：指存储数据时所占用的字节数。数据类型不同，所占用的字节数就不同。有些数据类型拥有固定的长度（例如：int，4字节；datetime，8字节），而有些数据类型则根据用户的要求来决定长度（例如：char）。SQLServer2005数据库应用技术清华大学出版社1．整数类型

（1）bigint：数据范围为－2^63～2^63－1。精度为19，长度为8个字节。（2）int（integer）：数据范围为－2^31～2^31－1。精度为10，长度4个字节。（3）smallint

：数据范围为－2^15～2^15－1。精度为5，长度2个字节。（4）tinyint

：数据范围为0～255。精度为3，长度1个字节。（5）bit：此类型数据相当于其他语言中的逻辑型数据，它只存储0、1或NULL，长度一般为1字节。提示：整数型数据的存储结构效率较高，所以在选用数据类型时，应尽量采用这种类型。SQLServer2005数据库应用技术清华大学出版社2．精确数值型：由整数部分和小数部分构成，其所有的数字都是有效位，能够以完整的精度存储十进制数。decimal和numeric：数据的范围为－10^38+1～10^38-1的固定精度和小数位数。可以提供小数所需要的实际存储空间，可以用5~17个字节来存储。使用命令创建表时将其写为decimal（p，s）或者numeric（p，s）的形式。其中p(1<=P<=38)为精度，s为小数位数。例如：decimal（8，3），表示共有8位数，其中整数5位，小数3位。注意：声明精确数值型数据时，其小数位数必须小于精度。SQLServer2005数据库应用技术清华大学出版社3．浮点数据类型：用于存储十进制小数，采用只入不舍的方式（1）float：其取值范围为－1.79×10^308～1.79×10^308的浮点精度数字。可以精确到第15位小数，默认占用8个字节的存储空间（2）real：其取值范围为－3.40×10^38～3.40×10^38的浮点精度数字，长度为4个字节。SQLServer2005数据库应用技术清华大学出版社4．货币数据类型（1）money：用于存储货币值，数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，存储范围为-922337213685377.5808~922337213685377.5808，精度为19，小数位数为4，长度为8字节。（2）smallmoney：其存储范围为-213738.3368~213738.3367，精度为10，小数位数为4，长度为4字节。由于money和smallmoney数据类型的小数位数固定为4，因此若所使用的货币数据值的小数位超过4位，请改用numeric或decimal数据类型。SQLServer2005数据库应用技术清华大学出版社5．日期和时间数据类型（1）datetime：占用8个字节。用于存储日期和时间的结合体，可以存储从公元1753年1月1日零时起~公元9999年12月31日23时59分59秒之间的所有日期和时间，其精确度可达百分之三秒，即3.33毫秒。（2）smalldatetime：存储从1900年1月1日~2079年6月6日内的日期。长度为4个字节。当存储datetime数据类型时，默认的格式是：MMDDYYYYhh:mmA.M/P.M。SQLServer2005数据库应用技术清华大学出版社输入日期部分时可以采用的格式如下：英文数字格式：Jan31999、JAN31999、January31999、Jan19993、19993Jan和1999Jan3。数字加分隔符格式：允许使用/、-和.作为不同时间单位间的分隔符，如1999-1-3或1999/1/3或

1999.1.3。输入时间部分时可以使用12小时格式或24小时格式。使用12小时格式时需要加上AM或PM说明上午还是下午。1999-1-32：30：45：20PM//12小时制1999-1-314：30：45：20//24小时制SQLServer2005数据库应用技术清华大学出版社6．字符数据类型用来存储汉字、英文字母、数字和特殊符号。在使用时需要在其前后加上英文单引号或者双引号。（1）char（定长字符型）：用来存放固定长度的字符数据，可用n来指定字符串的长度，字符串长度取值范围为1-8000，不论用户输入的字符串有多长（不大于n），长度均为n字节。当输入字符的长度大于n时，SQLServer给出错误信息拒绝接收数据。对于像身份证号码、邮政编码等为固定长度的数据，使用char类型比较合适。注意：一个中文文字占用2个字节，因此要存放三个汉字，长度应取6字节。SQLServer2005数据库应用技术清华大学出版社（2）varchar（变长字符型）：可以存储长达8000个字符的可变长度字符串，和char类型不同，varchar类型根据输入数据的实际长度而变化。当一个字段中的数值长度显著不同时，可以采用varchar。与char类型相比，它可以节省很多空间，但在处理速度上往往不及char。text：当一个字段中存储字符超过8000个时，可以选择text类型。最大长度为2^31－1个字符。在定义Text数据类型时，不需要指定数据长度，SQLServer会根据数据的长度自动为其分配空间。SQLServer2005数据库应用技术清华大学出版社7.Unicode字符型——双字节数据类型Unicode是“统一字符编码标准”，用于支持国际上非英语语种的字符数据的存储和处理。Unicode数据类型包括三种：nchar、nvarchar和ntext数据类型。nchar（Unicode定长字符型）：用于存储固定长度的Unicode数据，所存放的字符数n的取值范围为1～4000,长度为2n。nvarchar（Unicode变长字符型）：用于存储可变长度的Unicode数据。存储大小是所输入字符个数的2倍。SQLServer2005数据库应用技术清华大学出版社7.Unicode字符型——双字节数据类型ntext(Unicode文本型）:当一个字段中存储字符超过4000个时，且为可变长度的Unicode文本数据，则应该选择text类型。最多可以存放2^30-1个字符。注意：使用unicode，由于用两个字节来存放一个字符，因此不管是一个英文字符还是一个汉字都将占用2字节。SQLServer2005数据库应用技术清华大学出版社8．二进制数据类型和图像型（1）binary：其定义形式为binary（n），数据的存储长度是固定的，即n个字节。二进制数据类型的最大长度（即n的最大值）为8000，常用于存储图像等数据。（2）varbinary其定义形式为varbinary（n），数据的存储长度是变化的，它为实际所输入数据的长度。（3）image：如果字段要存储超过8000字节且为可变长度的二进制数据，应采用image数据类型。用于存储照片、目录图片或者图画。长度为2^31－1个字节。SQLServer2005数据库应用技术清华大学出版社9．其他数据类型（较少使用）（1）sql_variant：用于存储除文本、图形数据和timestamp类型数据外的其它任何合法的SQLServer数据。（2）table：用于存储对表或者视图处理后的结果集。一般只用在编程环境中，就像一个临时的表格。（3）timestamp：也称作时间戳数据类型。是一种自动记录时间的数据类型，主要用于在数据表中记录其数据的修改时间。它提供数据库范围内的惟一值。一个表中只能有一个timestamp类型的字段。（4）uniqueidentifier：也称作惟一标识符数据类型,是一个16字节长的二进制数据。SQLServer2005数据库应用技术清华大学出版社4.1.3SQLServer的主要列属性1．允许空（NULL或NOTNULL）该属性定义在输入数据时指定列是否可以为空。在SQLServer中，列的默认属性为“允许空”，例如，Student表中的Ssex列和Sage列。当某列不允许为空时，只需要在该列的“允许空”选项中取消复选框就行了，如Sno列。SQLServer2005数据库应用技术清华大学出版社很多表中使用编号列来标识表中的记录。设置成功后，插入数据时，用户并不需要指定编号列的值，每插入一条记录，系统都会根据增量值自动为该列生成新数据。定义标识列需要指定两个值：种子值和增量值。这样，表中第一行记录的IDENTITY列的值就是种子值，其他行的IDENTITY列的值是在前一行值的基础上增加一个增量值得到的。标识列必须定义为整型(bigint、int、smallint或tinyint）或精确数类型（decimal(P,0)和numeric(P,0)），但通常将标识列的类型定义为int或bigint。2．标识规范（IDENTITY）SQLServer2005数据库应用技术清华大学出版社由于IDENTITY属性列的增长是单方向的，所以一般情况下不能手工为设置了IDENTITY属性的列添加数据。而且，如果删除了这些列中的部分数据，还会造成标识符序列空缺——已删除的标识符值是不能重用的，系统不会自动补充这部分数据值。解决办法：使用SETIDENTITY_INSERT语句将标识列设置为可以插入数据的状态。该语句的基本格式为：

SETIDENTITY_INSERT<表名>{ON|OFF}

其中，ON表示可以插入数据，OFF表示拒绝插入数据。

SQLServer2005数据库应用技术清华大学出版社4.2创建销售管理数据库的数据表使用ManagementStudio

【练习】在销售管理数据库中，利用ManagementStudio创建客户表。使用CREATETABLE语句【练习】在销售管理数据库中，利用CreateTable语句，创建部门表和商品表。使用模板SQLServer2005数据库应用技术清华大学出版社4.3销售管理数据库数据的完整性实体完整性：要求数据库中的每一条记录都是惟一的，即表中没有重复的记录。即要求主键属性的值不能为空值，并且必须是唯一的。。参照完整性（引用完整性）：外键属性的值必须与它所引用的主键属性的值保持一致。用户自定义完整性（域完整性）：要求存入字段中的数据必须符合特定的条件来保证数据的完整性。SQLServer2005数据库应用技术清华大学出版社SQLServer2005数据库应用技术清华大学出版社约束概述约束定义：是SQLServer提供的自动保持数据库完整性的一种方法。

约束分类空值（NULL）约束主键约束(PrimaryKeyConstraint)唯一键约束(UniqueConstraint)外键约束(ForeignKeyConstraint)检查约束(CheckConstraint)默认约束(DefaultConstraint)SQLServer2005数据库应用技术清华大学出版社主键约束一个表通常可以通过一个字段（或多个字段的组合）的数据来惟一地确定表中的每一条记录，这个字段（或字段组合）被称为表的主键。每个表都应该有主键，且只能有一个主键。主键不能取空值。主键约束可以保证实体的完整性。使用ManagementStudio创建主键约束在创建表同时主键约束在一张现有表上添加主键约束SQLServer2005数据库应用技术清华大学出版社外键约束外键约束主要用来维护两个表之间数据的一致性。如果希望一个表中的字段（或字段组合）与其他表中的主键字段相关，这个字段（或字段组合）就成为前一个表中的外键。创建外键字段与主键字段的数据类型匹配，字段长度应当相等，字段名称可以相同也可以不同，两个表必须位于同一个数据库内。具有主键约束或惟一性约束的表为主键表，另一个则为外键表。SQLServer2005数据库应用技术清华大学出版社外键约束使用ManagementStudio创建外键约束使用Transact-SQL语句定义外键语法格式：CREATETABLE<表名>(列名数据类型为空性FOREIGNKEYREFERENCESref_table(ref_column)）其中，参数说明如下。REFERENCES：参照｡ref_table：主键表名，要建立关联的被参照表的名称｡ref_column：主键列名

SQLServer2005数据库应用技术清华大学出版社唯一键约束唯一键约束用于指定一个或多个列的组合值具有唯一性，以防止在列中输入重复的值。唯一键约束与主键约束的区别。唯一键性约束指定的列可以有NULL属性。但主键约束所在的列则不允许；一个表中可以包含多个唯一键约束，而主键约束则只能有一个；创建唯一键约束创建表的同时创建UNIQUE约束在现有的表中添加唯一键约束SQLServer2005数据库应用技术清华大学出版社检查约束检查约束（CHECK约束）定义实际上是验证字段输入内容的规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。可以对每个列设置CHECK约束。创建检查约束使用Managementstudio使用Transact-SQL语句SQLServer2005数据库应用技术清华大学出版社检查约束语法结构：CONSTRAINT约束名CHECK

(logical_expression)[,…n]【练习】在销售管理数据库中的商品表中，为了保证数据的质量，确保商品的价格为大于0的数，库存量和已销售量数据为非负数。SQLServer2005数据库应用技术清华大学出版社默认值约束默认值约束（DEFAULT）用于确保域完整性，它提供了一种为数据表中任何一列提供默认值的手段。创建默认值约束：1、使用Managementstudio创建默认值2、使用Transact-SQL语句创建默认值定义语法：CONSTRAINT约束名DEFAULTconstant_expressionFOR列名【练习】在销售管理数据库中的员工表中，新员工如果不到特定部门工作的话，新员工全部到“销售部”工作SQLServer2005数据库应用技术清华大学出版社4.4用SQL命令创建数据表基本语法如下： CREATETABLE表名 ( 列名1数据类型和长度1列属性1, 列名2数据类型和长度2列属性2, ………… 列名n数据类型和长度n列属性n )在CREATETABLE语句中需要指出的元素与在表设计器中相同，包括表名、列名、列的数据类型以及列属性等。SQLServer2005数据库应用技术清华大学出版社使用默认值一个字段的默认值的建立可以通过如下两种方式实现：

①在定义表或修改表时，定义默认值约束；

②先定义默认对象，然后将该对象绑定到表的相应字段。SQLServer2005数据库应用技术清华大学出版社创建默认值1.使用T-SQL语句创建默认值

CREATEDEFAULTdefault_name

ASdefault_description其中：default_name为默认值对象的名称default_description为默认值表达式，可以是任意数据类型的常量、内置函数或数学表达式。其中字符和日期常量用单引号（’）括起来。SQLServer2005数据库应用技术清华大学出版社SQLServer2005数据库应用技术清华大学出版社SQLServer2005数据库应用技术清华大学出版社捆绑默认值将默认值捆绑到列或用户自定义数据类型上，它就可以为列和用户自定义数据类型提供默认值。默认值和表列或用户自定义数据类型的绑定可以通过sp_bindefault系统存储过程和图形工具的方法实现。

SQLServer2005数据库应用技术清华大学出版社使用系统存储过程sp_bindefault绑定sp_bindefault‘default’,’object_name’

SQLServer2005数据库应用技术清华大学出版社删除默认值1.使用系统存储过程sp_unbindefault解除默认值与用户自定义数据类型或者列的捆绑关系：Sp_unbindefault‘对象名’删除默认值

dropdefault{default}[,…]SQLServer2005数据库应用技术清华大学出版社使用规则规则是一组使用T-SQL书写的条件语句，它可以和列或者用户自定义数据类型捆绑在一起。

当用户向绑定有规则的数据列上插入数据或修改数据时，需要验证一个数据库中的数据是否遵循规则。如果违反了规则，这一操作就会失败。SQLServer2005数据库应用技术清华大学出版社创建规则CREATERULErule_name

AS

Rule_description

其中，规则表达式为一个条件表达式，可以是where子句中的有效表达式，可以包含比较运算符和算术运算符，但不能包括数据库对象名或表的列名。被绑定的列在规则表达式中使用参数表示，参数前要加“@”符号。SQLServer2005数据库应用技术清华大学出版社SQLServer2005数据库应用技术清华大学出版社SQLServer2005数据库应用技术清华大学出版社绑定规则绑定规则就是将定义好的规则绑定在数据库表的列上，使该列具有规则指定的完整性条件。绑定规则的语法为:execsp_bindrule规则名,’对象名’SQLServer2005数据库应用技术清华大学出版社SQLServer2005数据库应用技术清华大学出版社解除规则解除规则的语法为Execsp_unbindrule，‘对象名’SQLServer2005数据库应用技术清华大学出版社删除规则删除规则的语法为Droprule规则名SQLServer2005数据库应用技术清华大学出版社销售管理数据库的数据表管理查看表结构查看数据表的属性查看表结构查看表中数据修改数据表使用ManagementStudio使用AlterTable语句删除数据表重命名数据表SQLServer2005数据库应用技术清华大学出版社修改表结构使用ALTERTABLE语句修改表结构添加列修改列属性删除列修改列名和表名添加约束删除约束SQLServer2005数据库应用技术清华大学出版社添加列基本语法：

ALTERTABLE表名ADD列名数据类型和长度列属性【例4.1】在部门表中新增一列“联系电话”，列允许为空值。执行以下语句：ALTERTABLE部门ADD联系电话char(13)注意：当向表中新增一列时，最好为该列定义一个默认约束，使该列有一个默认值。这一点可以使用关键字DEFAULT来实现；如果增加的新列没有设置默认值，并且表中已经有了其他数据，那么必须指定该列允许空值，否则，系统将产生错误信息。SQLServer2005数据库应用技术清华大学出版社修改列属性基本语法：ALTERTABLE表名ALTERCOLUMN列名新数据类型和长度新列属性例如，【例4.1】中创建的员工工资列是money类型，。现在要将该列改为decimal(6,1)类型，并且允许为空。 SQL语句如下：ALTERTABLE员工ALTERCOLUMN工资decimal(6,1)SQLServer2005数据库应用技术清华大学出版社删除列基本语法： ALTERTABLE表名DROPCOLUMN列名例如，要删除部门表中的联系电话列，可以执行下面的SQL语句。

ALTERTABLE部门DROPCOLUMN联系电话SQLServer2005数据库应用技术清华大学出版社修改列名和表名可以使用sp_rename存储过程对表和表中的列进行重命名，重命名的基本语法为：sp_rename原对象名,新对象名更改表名sp_rename原表名,表名更改列名

sp_rename‘表名.原列名’,新列名

例如，如果想将Student表改名为StudentInfo，可以执行以下SQL语句：

sp_rename商品,ProductSp_rename‘客户.公司名称’,客户名称SQLServer2005数据库应用技术清华大学出版社添加约束ALTERTABLEtable_nameADD[CONSTRAINTconstraint_name]constraint_definition[,…n]SQLServer2005数据库应用技术清华大学出版社示例：1)AltertableStudentAddprimarykey(Sno)2)AltertableStudentAdddefault‘女’forSsex3)AltertableStudentaddunique(Sname)4)AltertableStudentaddconstraintCK_Sagecheck(Sagebetween15and28)SQLServer2005数据库应用技术清华大学出版社示例：4)AltertableCourseaddprimarykey(Cno)5)AltertableSCaddprimarykey(Sno,Cno)6）AltertableSCaddforeignkeySnoreferencesStudent(Sno)7)AltertableSCaddforeignkeyCnoreferencesCourse(Cno)SQLServer2005数据库应用技术清华大学出版社使用DROPCONSTRAINT子句删除约束ALTERTABLEtable_name

DROPCONSTRAINTconstraint_name[,…n]SQLServer2005数据库应用技术清华大学出版社数据表的操作在SQLServer2005中，经过创建表确定基本结构以后，接着就是表中的数据处理：添加、修改和删除数据。方法：使用ManagementStudio操作表中数据使用ManagementStudio的可视化工具。SQLServer2005数据库应用技术清华大学出版社插入记录INSERT语句通常有两种形式：一种是插入一条记录；另一种是插入子查询的结果，一次可以插入多条记录。INSERT语句语法插入一条记录格式：INSERT[INTO]表名[(column_list)]VALUES({DEFAULT|NULL|expression}[,...n])插入多条记录INSERT[INTO]表名[(column_list)]SELECTcolumn_listFROMtable_listWHEREsearch_conditionSQLServer2005数据库应用技术清华大学出版社使用Insert语句给部门表添加记录1.给部门表除了标识列以外的所有字段赋值。insertinto部门values(‘生产部’,‘张三','主管公司商品生产’)2.给部门表的部门字段赋值。insertinto部门(部门名称,部门主管)values(‘销售部’,‘李四’)SQLServer2005数据库应用技术清华大学出版社注意：在编辑表中数据的过程中，有些读者可能会遇到这样一个问题，就是无法输入中文。这种情况出现在表的第一列为标识列、并且打开表时表中已经存在数据，此时，光标定位在第一条记录的标识列中，再将光标定位到其他的编辑框，将无法输入中文。解决方法是在定义表结构时，不要将标识列定义为表的第一列，或者使用SQL命令进行数据的插入。SQLSer