chapter05-数据库与表的管理

第五章数据库与表的管理-2-本书提供一个销售管理系统的示例数据库mySales，其数据表结构如第4章所述。本书第5章后数据库技术与应用部分的绝大部分实例基于该数据库。该数据库生成安装方法如下：①打开SQLServer数据库管理系统，使用SQLServer身份验证，输入服务器名称、sa和密码。也可以使用windows身份验证，只需输入服务器名称。具体如下图所示：实例数据库mySales的安装方法-3-实例数据库mySales的安装方法-4-实例数据库mySales的安装方法②在SQLServer2008中打开本书配套资料中的mysales.sql文件，然后按F5或“执行”按钮运行该文件。稍后（大约10秒），系统提示“命令已成功完成”，表明数据库已经生成完成，这时，在左边的下拉式菜单中可以找到mysales数据库，至此数据库已经生成完毕。具体如下图所示：-5-实例数据库mySales的安装方法-6-第5章数据库与表的管理数据库的创建与维护表的创建与维护表数据的更新创建数据库第五章修改数据删除数据创建表修改表删除表表概述数据类型插入数据数据完整性约束唯一性约束检验性约束缺省约束主键约束外键约束非空约束-7-5.1.2创建数据库基本语法：CREATEDATABASEdatabase_name[ON[filespec[,...n]][LOGON{filespec[,...n]}][FORLOAD|FORATTACH]filespec::=([NAME=logical_file_name,]FILENAME='os_file_name'[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment])[,...n]主要参数：①NAME：指定数据文件的逻辑名。在创建数据库后，可以引用文件逻辑名进行修改和删除操作。逻辑名在数据库中必须唯一。-8-5.1.2创建数据库②FILENAME：指定数据文件在操作系统中的路径和物理文件名。文件所在的路径必须是在创建数据库时已经存在的一个文件目录或磁盘符号。③SIZE。指定文件的初始大小。文件大小可以使用KB、MB、GB或TB，默认值为MB。④MAXSIZE。指定文件可以增长到的最大值，该参数是一个整数。如果没有指定最大值，那么文件将增长到磁盘变满为止，相当于使用UNLIMITED选项值。⑤FILEGROWTH。指定文件增长的增量，即为数据库文件每次需要新的空间时添加空间。该值可以MB、KB、GB、TB或百分比（%）为单位。如果没有指定FILEGROWTH，则默认值为10%。-9-5.1.2创建数据库实例5-1创建简单的数据库，只包含一个数据文件和日志文件。本例使用两种方式创建数据库。第1个示例是一种最简单的数据库创建方式，由于没有指定任何参数项，因此数据文件和日志文件的名称、路径和大小都采用系统默认值（即SQLServer系统的安装路径）。USEmasterCREATEDATABASEmyDB1GO解析：很多情况下，在上机实验过程中，可以使用这种简单的方式创建数据库。-10-5.1.2创建数据库实例5-1（续）:第2个示例是一种最常用的数据库创建方式，由于指定了数据文件的名称、路径和大小，其日志文件的名称、路径和大小将根据数据文件自动生成。USEmasterCREATEDATABASEmyDB2ON(NAME=myDB2_Dat1,FILENAME='c:\myDBF\myDB2Dat1.mdf',SIZE=5,MAXSIZE=500,FILEGROWTH=1)解析：由于SQLServer不会自动创建文件路径，因此文件路径c:\myDBF\必须在创建数据库之前已经存在。由于没有具体指定事务日志文件的各项参数，其文件大小为主文件大小的25%和512KB中的较大值。虽然这2个示例都没有明确指定事务日志文件，但系统会自动创建该文件，其文件名称、路径和大小与主数据文件相关。-11-5.1.2创建数据库实例5-2创建数据库，指定多个数据文件和事务日志文件的路径和大小。本实例创建一个数据库myDB。该数据库包含多个数据文件和日志文件，不同的数据文件和日志文件之间使用西文逗号（,）分隔，数据文件与日志文件之间使用LOGON分隔。在同一个数据库中不同数据文件的逻辑名（Name）不允许重名的。实例5-2程序代码运行实例5-2.sql-12-5.1.2创建数据库实例5-2（续）USEmasterCREATEDATABASEmyDBON(NAME=myDB_dat1,FILENAME='C:\myDBF\myDBdat1.mdf',SIZE=10,MAXSIZE=500,FILEGROWTH=5),(NAME=myDB_dat2,FILENAME='C:\myDBF\myDBdat2.ndf',SIZE=5MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)LOGON(NAME='myDB_log1',FILENAME='C:\myDBF\myDBlog1.ldf',SIZE=5MB,MAXSIZE=250MB,FILEGROWTH=5MB),(NAME='myDB_log2',FILENAME='C:\myDBF\myDBlog2.ldf',SIZE=5MB,MAXSIZE=UNLIMITED,FILEGROWTH=1MB)-13-表（Table）是存储数据库中所有数据的一个对象。表定义为列的集合。与电子表格（Spreadsheet）相似，数据在表中是按行和列的格式组织排列的。每行代表唯一的一条记录，而每列代表记录中的一个域。例如，在包含公司员工数据的表中，每一行代表一名员工，各个列分别表示雇员的详细资料，如员工编号、姓名、地址、职位以及电话号码等。EmpIDEmpNameTitleAddressHomePhoneAMD154FDevonAnnSalesRepresentative8127OtterDr.(208)5558097ATF328MFullerAndrewVicePresident,Sales908W.CapitalWay(206)5559482DBT394MToniniDanielSalesRepresentative4440AlgiersDrive(800)6604366ENL442FLincolnElizabethSalesRepresentative9056MountDr5.2表的创建与维护-14-5.2.1表概述SQLServer中的表由两个主要组成部分：①列（Column）。每一列描述表所建模的对象的一些属性。列也称为字段（Field）。②行（Row）。每一行描述表所建模的对象的一个个体的存在。行也称为记录（Record）。在很多情况下，行和记录、列和字段这两对术语是可以互相替代的。创建一张表的最有效方法是一次性全部定义好表中所需要的一切内容（字段），包括它的数据限定和其他要素。当然，也可以在创建后再对表进行修改，例如，添加一些约束、索引、缺省值、规则和其它对象。-15-5.2.2数据类型SQLServer系统提供的主要数据类型见表5-2。表5-2SQLServer的常用数据类型整型数据（Integer）bit取值为0或1int取值范围从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)。存储大小是4个字节。.smallint取值范围从2^15(-32,768)到2^15-1(32,767)。存储大小是2个字节。tinyint取值范围从0到255。存储大小是1个字节。bigint取值范围从-2^63到2^63-1范围内的数字。存储大小为8个字节。小数数据（Decimal和Numeric）decimal[(p[,s])]取值范围从-10^38+1到10^38–1的固定精度和小数位的数字数据。精度p取值1到38之间。小数位数s指定小数点的最大位数，默认为0。存储大小根据精度而定。numeric功能上等同于Decimal。-16-表5-2（续1）5.2.2数据类型货币型数值（money和smallmoney）money取值范围-2^63到2^63-1之间。存储大小为8个字节。Smallmoney取值范围-214,748.3648与+214,748.3647之间。存储大小为4个字节。浮点型数值float[(n)]介于-1.79E+308与1.79E+308之间的浮点精度数字。n必须为从1到53之间的值。存储字节数根据n所在的范围和精度而定。Real介于-3.40E+38到3.40E+38的浮点精度数字。存储大小是4个字节。日期时间型（Datetime和Smalldatetime）datetime介于1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒或3.33毫秒）。例如：01/01/9823:59:59.999，2008-01-0223:59:59.995。smalldatetime从1900年1月1日到2079年6月6日的日期和时间数据，精确到分。-17-表5-2（续2）5.2.2数据类型字符型（Char和Varchar）char[(n)]固定长度的非Unicode字符数据，最大长度为8,000个字符。存储大小是n个字节。varchar[(n)]可变长度的非Unicode数据，最长为8,000个字符。存储大小为输入数据的实际长度而不是n个字节。输入数据可以是0个字符。text可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。Unicode字符型存储由Unicode标准定义的任何字符和其他字符集定义的所有字符，存储空间相当于非Unicode的两倍。nchar[(n)]固定长度的Unicode数据，最大长度为4,000个字符。nvarchar[(n)}]可变长度Unicode数据，其最大长度为4,000字符。Ntext可变长度Unicode数据，其最大长度为2^30-1个字符。-18-表5-2（续3）5.2.2数据类型二进制字符串binary[(n)]固定长度的二进制数据，其最大长度为8,000个字节。varbinary[(n)]可变长度的二进制数据，其最大长度为8,000个字节。Image可变长度的二进制数据，其最大长度为2^31-1(2,147,483,647)个字节。-19-5.2.2数据类型在SQLServer中，用户可以定义基于系统数据类型的用户自定义数据类型（User-DefinedDataTypes）。当多个表的列中要存储同样类型的数据，而且这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型。在SQLServer2000中，使用系统存储过程sp_addtype和sp_droptype分别来创建或删除用户定义数据类型。在SQLServer2008中，可以使用CREATETYPE和DROPTYPE语句来分别替代sp_addtype和sp_droptype的功能。-20-建表语句的语法如下：CREATETABLE[database_name].[schema_name.]table_name(column_namedata_type[NULL|NOTNULL][PRIMARYKEY|UNIQUE][REFERENCESref_table[(ref_column)]][DEFAULTconstant_expression][IDENTITY[(seed,increment)]][ROWGUIDCOL][CONSTRAINTconstraint_name][,...n])5.2.3创建表-21-1．创建简单的表从CREATETABLE语句的语法中可以看出，建立一个表需要考虑很多因素。下面从创建最简单的表开始，逐步讲述如何创建完整定义的数据表。实例5-7简单的表定义，建表时使用多种数据类型，但没有定义约束条件。本实例创建一张员工表myEmployees，使