第2章数据库和表创建

第2章数据库和表创建2.1SQLServer基本概念2.1.1数据库1.数据库

SQLServer2008数据库是存储数据的容器，是一个存放数据的表和支持这些数据的存储、检索、安全性和完整性的逻辑成分所组成的集合。

数据库对象说明表由行和列构成的集合，用来存储数据数据类型定义列或变量的数据类型，SQLServer提供了系统数据类型，并允许用户自定义数据类型视图由表或其他视图导出的虚拟表索引为数据快速检索提供支持且可以保证数据唯一性的辅助数据结构约束用于为表中的列定义完整性的规则默认值为列提供的缺省值存储过程存放于服务器的预先编译好的一组T-SQL语句触发器特殊的存储过程，当用户表中数据改变时，该存储过程被自动执行2.1.2表表是在日常工作和生活中经常使用的一种表示数据及其关系的形式，表2.2就是一个学生情况表。学

号姓

名专业名性

别出生时间总学分备注001101王林计算机男1980-02-1050

001102程明计算机男1981-02-0150

001103王燕计算机女1979-10-0650

001104韦严平计算机男1980-08-2650

001106李方方计算机男1980-11-2050

001107李明计算机男1980-05-0154提前修完《数据结构》，并获学分001108林一帆计算机男1979-08-0552已提前修完一门课001109张强民计算机男1978-08-1150

2.1.2表表结构每个数据库包含了若干个表。每个表具有一定的结构，即组成表的各列的名称及数据类型，也就是日常表格的“栏目信息”。记录。每个表包含了若干行数据，它们是表的“值”，表中的一行称为一个记录(Record)，因此，表是记录的有限集合。字段每个记录由若干个数据项构成，将构成记录的每个数据项称为字段(Field)。例如学生情况表中，表结构为（学号，姓名，专业名，性别，出生时间，总学分，备注），包含7个字段，由8个记录组成。关键字注意到，在学生情况表中，若不加以限制，每个记录的姓名、专业、性别、出生时间、总学分和备注这6个字段的值都有可能相同，但是学号字段的值对表中所有记录来说一定不同，即通过“学号”字段可以将表中的不同记录区分开来。2.2用界面方式创建数据库和表

2.2.1数据库的创建、修改和删除

1.创建数据库以创建本书的示例数据库【db_stu】为例，对这种方法进行详细介绍。具体的操作步骤如下所示。（1）从【开始】菜单中选择【程序】|MicrosoftSQLServer2008|SQLServerManagementStudio命令，打开SQLServerManagementStudio窗口，并使用Windows或SQLServer身份验证建立连接。如图2.1所示。

图2.1连接服务器身份验证2.2.1数据库的创建、修改和删除（2）在【对象资源管理器】窗格中展开服务器，然后选择【数据库】节点。（3）在【数据库】节点上单击鼠标右键，从弹出的快捷菜单中选择【新建数据库】命令，如图2.2所示。2.2.1数据库的创建、修改和删除图2.2选择【新建数据库】命令2.2.1数据库的创建、修改和删除（4）执行上述操作后，会弹出【新建数据库】对话框，如图2.3所示。这个对话框有3个页，分别是【常规】、【选项】和【文件组】。完成这3个选项中的内容之后，就完成了数据库的创建工作。2.2.1数据库的创建、修改和删除图2.3【新建数据库】对话框2.2.1数据库的创建、修改和删除（5）在【数据库名称】文本框中输入要新建数据库的名称。（6）在【所有者】文本框中输入新建数据库的所有者，如sa。根据数据库的使用情况，选择启用或者禁用【使用全文索引】复选框。（7）【数据库文件】列表包括两行，一行是数据文件，另一行是日志文件。通过单击相应的按钮，可以添加或删除相应的数据文件。2.2.1数据库的创建、修改和删除（8）选择【选项】页，设置数据库的排序规则、恢复模式、兼容级别和其他需要设置的内容，如图2.4所示。2.2.1数据库的创建、修改和删除图2.4【选项】页2.2.1数据库的创建、修改和删除（9）单击【文件组】可以设置数据库文件所属的文件组，可以通过【添加】或者【删除】按钮更改数据库文件所属的文件组，如图2.5所示。2.2.1数据库的创建、修改和删除图2.5【文件组】页2.2.1数据库的创建、修改和删除（10）完成以上操作后，就可以单击【确定】按钮关闭【新建数据库】对话框。这时，一个数据库成功的创建了，可以通过【对象资源管理器】窗格查看新建的数据库。2.2.1数据库的创建、修改和删除（1）数据库更名通常情况下，创建好一个数据库后就不再更改其数据库名称。因为许多应用程序可能已经使用该名称，如果要更改数据库名称，所有引用其名称的应用程序都要做相应的修改。2.数据库的修改使用图形界面是修改数据库名称最简单的一种方法，只要在【对象管理器】窗格中右击一个要修改的数据库，选择【重命名】命令，即可直接改名。2.2.1数据库的创建、修改和删除利用SQLServer管理控制台修改数据库属性下面介绍如何利用SQLServer管理控制台的图形界面修改数据库的属性。①在【对象资源管理器】窗格中，右击要修改的数据库，选择【属性】命令。②在【数据库属性】对话框的【选择页】下选择【文件】选项。③在要修改的数据库的数据文件行的【初始大小】列中，输入要修改的值。同样在日志文件行的【初始大小】列中，输入要修改的值。④单击【自动增长】列中的按钮，打开【自动增长设置】对话框，可设置自动增长的方式及大小。⑤如果要添加文件，可以直接在【数据库属性】对话框中单击【添加】按钮，进行相应大小的设置。⑥完成修改后，单击【确定】按钮完成对数据库的修改。2.2.1数据库的创建、修改和删除3.数据库的删除删除数据库就是从SQLServer系统中去除掉将不再使用或需要的数据库。使用图形界面删除数据库的具体步骤如下：①在【对象资源管理器】窗格中选中要删除的数据库，右击【删除】命令。②在弹出的【删除对象】对话框中，单击【确定】按钮确认删除。删除操作完成后会自动返回SQLServerManagementStudio窗口。2.2.2表的创建、修改和删除1.SQLServer支持的数据类型

精度：指数值数据中所存储的十进制数据的总位数。小数位数：指数值数据中小数点右边可以有的数字位数的最大值。例如数值数据3890.587的精度是7，小数位数是3。长度：指存储数据所使用的字节数。数据类型符

号

标

识整数型bigint,int,smallint,tinyint精确数值型decimal,numeric浮点型float,real货币型money,smallmoney位型bit字符型char,varcharUnicode字符型nchar,nvarchar文本型text,ntext二进制型binary,varbinary日期时间类型datetime,smalldatetime时间戳型Timestamp图象型Image其他cursor,sql_variant,table,uniqueidentifier2.2.2表的创建、修改和删除

下面分别说明系统数据类型：整数型整数包括bigint、int、smallint和tinyint，从标识符的含义就可以看出，它们的表示数范围逐渐缩小。

bigint：大整数，数范围为-2^63(-8)～2^63-1(7)，其精度为19，小数位数为0，长度为8字节。

int：整数，数范围为-2^31(-2,147,483,648)～2^31-1(2,147,483,647)，其精度为10，小数位数为0，长度为4字节。

smallint：短整数，数范围为-2^15(-32768)～2^15-1(32767)，其精度为5，小数位数为0，长度为2字节。

tinyint：微短整数，数范围为0～255，长度为1字节，其精度为3，小数位数为0，长度为1字节。2.2.2表的创建、修改和删除(2)精确整数型decimal和numeric可存储从-1038+1到1038–1的固定精度和小数位的数字数据，它们的存储长度随精度变化而变化，最少为5字节，最多为17字节。精度为1～9时，存储字节长度为5；精度为10～19时，存储字节长度为9；精度为20～28时，存储字节长度为13；精度为29～38时，存储字节长度为17。(3)浮点型

real：使用4字节存储数据，表数范围为-3.40E+38到3.40E+38，数据精度为7位有效数字。

float：定义中的n取值范围是1～53，用于指示其精度和存储大小。

2.2.2表的创建、修改和删除(4)货币型money：数据的数范围为-263(-922337203685477.5808)～263-1(922337203685477.5807)，其精度为19，小数位数为4，长度为8字节。smallmoney：数范围为–231(-2,147,48.3648)～231-1(2,147,48.3647)，其精度为10，小数位数为4，长度为4字节。(5)位型SQLServer中的位（bit）型数据相当于其他语言中的逻辑型数据，它只存储0和1，长度为一个字节。(6)字符型字符型数据用于存储字符串，字符串中可包括字母、数字和其它特殊符号（如#、@、&等等）。SQLServer字符型包括两类：固定长度(char)或可变长度

(varchar)字符数据类型。2.2.2表的创建、修改和删除(7)Unicode字符型Unicode是“统一字符编码标准”，用于支持国际上非英语语种的字符数据的存储和处理。

nchar[(n)]：nchar[(n)]为包含n个字符的固定长度Unicode字符型数据，n

的值在1与4,000之间，缺省为1。长度为2n字节。若输入的字符串长度不足n，将以空白字符补足。

nvarchar[(n)]：nvarchar[(n)]为最多包含n个字符的可变长度Unicode字符型数据，n

的值在1与4,000之间，缺省为1。(8)文本型文本型包括text和ntext两类，分别对应ASCII字符和Unicode字符。text类型可以表示最大长度为231-1(2,147,483,647)个字符，其数据的存储长度为实际字符数个字节。ntext可表示最大长度为230-1(1,073,741,823)个Unicode字符，其数据的存储长度是实际字符个数的两倍（以字节为单位）。2.2.2表的创建、修改和删除(9)二进制型

binary[(n)]：固定长度的n个字节二进制数据。n取值范围为1到8,000，缺省为1。

varbinary

[(n)]：n个字节变长二进制数据。n取值范围为1到8,000，缺省为1。varbinary(n)数据的存储长度为实际输入数据长度+4个字节。(10)日期时间类型datetime类型数据长度为8字节，日期和时间分别使用4个字节存储。(11)时间戳型标识符是timestamp。若创建表时定义一个列的数据类型为时间戳类型，那么每当对该表加入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。2.2.2表的创建、修改和删除1.使用SQLServer管理控制台创建表

在SQLServermanagementstudio中，可在表设计器中创建数据表，具体操作如下：（1）在对象资源管理器中，右键单击数据库的“表”节点，再单击“新建表”.如图2.6所示。2.2.2表的创建、修改和删除图2.6新建表2.2.2表的创建、修改和删除（2）键入列名，选择数据类型，并选择各个列是否允许空值.如图2.7所示图2.7定义字段2.2.2表的创建、修改和删除（3）在“文件”菜单上，选择保存“tablename”.如图2.8所示。图2.8保存表2.2.2表的创建、修改和删除（4）在“选择名称”对话框中，为该表键入一个名称，再单击“确定”。如图2.9所示。图2.9【选择名称】对话框2.2.2表的创建、修改和删除2.使用SQLServer管理控制台修改表

具体操作步骤如下：（1）在对象资源管理器中，选择数据库db_stu，在表中选择表Student,并且单击右键，在弹出的快捷菜单中选择【设计】命令，打开表Student,如图2.10所示。2.2.2表的创建、修改和删除图2.10打开表Student2.2.2表的创建、修改和删除（2）直接在表设计器窗口中的“Sname”字段后添加“Sage”字段，如图2.11所示图2.11添加字段直接添加字段2.2.2表的创建、修改和删除图2.12插入字段（3）选择字段“Sage”，单击右键，在弹出的快捷菜单中选择【插入列】命令，插入一个空行，在插入的空行中的【列名】处输入“Ssex”,并设置其属性。如图2.12所示。插入字段2.2.2表的创建、修改和删除（4）选择字段“Sdept”，单击右键，在弹出的快捷菜单中选择【删除列】命令，删除“Sdept”字段，如图2.13所示。图2.13删除列2.2.2表的创建、修改和删除（5）单击字段“Sdept”左边的按钮，按住左键不放，将其拖动到新的位置，释放鼠标，即可完成位置的移动，如图2.14所示。图2.14调整字段顺序拖动该列2.2.2表的创建、修改和删除3.使用SQLServer管理控制台删除表

具体操作步骤如下:

（1）在对象资源管理器中选择要删除的表,然后按【delete】键，或选择【编辑】【删除】命令，或单击鼠标右键，在弹出的快捷菜单中选择【删除】命令，打开【删除对象】对话框。2.2.2表的创建、修改和删除（2）在该对话框中选择要删除的对象，单击【确定】按钮即可，如图2.15所示。图2.15删除表2.3命令方式创建数据库和表

语法格式：CREATEDATABASEdatabase_name[ON /*指定数据库文件和文件组属性*/

[<filespec>[,...n]]

[,<filegroup>[,...n]]][LOGON{<filespec>[,...n]}] /*指定日志文件属性*/[COLLATEcollation_name][FORLOAD|FORATTACH]

<filespec>::=[PRIMARY]([NAME=logical_file_name,]

FILENAME='os_file_name'

[,SIZE=size]

[,MAXSIZE={max_size|UNLIMITED}]

[,FILEGROWTH=growth_increment])[,...n]

<filegroup>::=FILEGROUPfilegroup_name<filespec>[,...n]

【例2.1】创建一个名为db_stu的数据库，其初始大小为5MB，最大大小50MB，允许数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。假设SQLServer服务已启动，并以Administrator身份登录计算机。2.3.1使用CREATEDATABASE创建数据库在T-SQL语句输入窗口中输入如下语句：CREATEDATABASEdb_stu ON ( NAME='db_stuData

', FILENAME=‘e:\sql\data\MSSQL\Data\db_stu.mdf, SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=10% ) LOGON ( NAME='XSCJ1_Log', FILENAME='e:\sql\data\MSSQL\Data\db_stu.ldf', SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB )GO2.3.1使用CREATEDATABASE创建数据库输入完毕后，单击“执行查询”按钮。如下图所示。2.3.1使用CREATEDATABASE创建数据库【例2.2】创建TEST1的数据库。CREATEDATABASETEST1ON( NAME=‘TEST1_data’, FILENAME=‘e:\sql\data\MSSQL\data\t1.mdf’)GO【例2.3】

创建一个名为TEST2的数据库，它有三个数据文件，其中主数据文件为100MB，最大大小为200MB，按20MB增长；2个辅数据文件为20MB，最大大小不限，按10%增长；有2个日志文件，大小均为50MB，最大大小均为100MB，按10MB增长。CREATEDATABASETEST2 ON PRIMARY ( NAME=‘TEST2_data2’, FILENAME=‘e:\sql\data\t2\test2_data2.ndf’, SIZE=20MB, MAXSIZE=UNLIMITED, FILEGROWTH=10% ),

( NAME=‘TEST2_data3’, FILENAME=‘e:\sql\data\t2\test2_data3.ndf’, SIZE=20MB, MAXSIZE=UNLIMITED, FILEGROWTH=10% )LOGON( NAME=‘TEST2_log1’, FILENAME=‘e:\sql\data\t2\test2_log1.ldf’, SIZE=50MB, MAXSIZE=100MB, FILEGROWTH=10MB),( NAME=‘TEST2_log2’, FILENAME=‘e:\sql\data\t2\test2_log2.ldf’, SIZE=50MB, MAXSIZE=100MB, FILEGROWTH=10MB )GO2.3.1使用CREATEDATABASE创建数据库2.3.2使用ALTERDATABASE修改数据库使用ALTERDATABASE命令对数据库可进行以下修改：增加或删除数据文件改变数据文件的大小和增长方式改变日志文件的大小和增长方式增加或删除日志文件增加或删除文件组1.ALTERDATABASE语句2.3.2使用ALTERDATABASE修改数据库语法格式：ALTERDATABASEdatabase_name{ ADDFILE<filespec>[,…n][TOFILEGROUPfilegroup_name] /*在文件组中增加数据文件*/ |ADDLOGFILE<filespec>[,…n] /*增加日志文件*/ |REMOVEFILElogical_file_name /*删除数据文件*/ |ADDFILEGROUPfilegroup_name /*增加文件组*/ |REMOVEFILEGROUPfilegroup_name /*删除文件组*/ |MODIFYFILE<filespec> /*更改文件属性*/ |MODIFYNAME=new_dbname /*数据库更名*/ |MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name} |SET<optionspec>[,...n][WITH<termination>]/*设置数据库属性*/ |COLLATE<collation_name>/*指定数据库排序规则*/}2.3.2使用ALTERDATABASE修改数据库【例2.4】设已经创建了数据库XSCJ，它只有一个主数据文件，其逻辑文件名为XSCJ_Data，物理文件名为e:\sql\data\MSSQL\data\XSCJ_Data.mdf，大小为5MB，最大大小为50MB，增长方式为按10%增长；ALTERDATABASEXSCJ MODIFYFILE ( NAME=XSCJ_Data,MAXSIZE=UNLIMITED) GO/*这是第1次，将主数据文件的最大大小改为不限制。*/ ALTERDATABASEXSCJ MODIFYFILE ( NAME=XSCJ_Data, FILEGROWTH=5MB) GO/*这是第2次，将主数据文件的增长方式改为按5MB增长。*/2.3.2使用ALTERDATABASE修改数据库【例2.5】先为数据库XSCJ增加数据文件XSCJBAK。然后删除数据文件XSCJBAK。ALTERDATABASEXSCJ ADDFILE ( NAME=XSCJBAK FILENAME=‘e:\sql\data\MSSQL\data\XSCJBAK_dat.ndf’, SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=5% )GO通过企业管理器观察数据库XSCJ是否增加数据文件XSCJBAK。ALTERDATABASEXSCJREMOVEFILEXSCJBAKGO2.3.2使用ALTERDATABASE修改数据库【例2.6】为数据库XSCJ添加文件组FGROUP，并为此文件组添加两个大小均为10MB的数据文件。ALTERDATABASEXSCJADDFILEGROUPFGROUPGOALTERDATABASEXSCJ ADDFILE ( NAME=XSCJ_DATA2, FILENAME='e:\sql\data\MSSQL\data\XSCJ_Data2.ndf', SIZE=10MB, MAXSIZE=30MB, FILEGROWTH=5MB ), ( NAME=XSCJ_DATA3, FILENAME='e:\sql\data\MSSQL\data\XSCJ_Data3.ndf', SIZE=10MB, MAXSIZE=30MB, FILEGROWTH=5MB ) TOFILEGROUPFGROUPGO2.3.2使用ALTERDATABASE修改数据库【例2.7】从数据库中删除文件组，将示例4添加到XSCJ数据库中的数据组FGROUP删除。注意被删除的文件组中的数据文件必须先删除，且不能删除主文件组。ALTERDATABASETEST REMOVEFILETEST_DATA2GOALTERDATABASETESTREMOVEFILETEST_DATA3GOALTERDATABASETEST REMOVEFILEGROUPTGROUPGO2.3.2使用ALTERDATABASE修改数据库【例2.8】为数据库XSCJ添加一个日志文件。ALTERDATABASEXSCJ ADDLOGFILE ( NAME=XSCJ_LOG2, FILENAME='e:\sql\data\MSSQL\data\XSCJ_Log2.ldf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=1MB )GO【例2.9】从数据库XSCJ中删除一个日志文件，将日志文件XSCJ_LOG2删除。注意不能删除主日志文件。ALTERDATABASETEST REMOVEFILEXSCJ_LOG2GOALTERDATABASEXSCJ MODIFYNAME=JUST_TESTGO语法格式：CREATETABLE[database_name.[owner].|owner.]table_name({<column_definition> /*列的定义*

|column_nameAScomputed_column_expression/*定义计算列*/|<table_constraint>} /*指定表的约束*/)[ON{filegroup|DEFAULT}]/*指定存储表的文件组*/[TEXTIMAGE_ON{filegroup|DEFAULT}]/*指定存储text、ntext和image类型数据的文件组*/2.3.3使用DROPDATABASE删除数据库语法格式：DROPDATABASEdatabase_name[,…n]其中database_name是要删除的数据库名。要删除数据库TEST，使用命令：DROPDATABASETESTGO2.3.4使用CREATETABLE创建表2.3.4使用CREATETABLE创建表【例2.10】设已经创建了数据库XSCJ，现在该数据库中需创建学生情况表XS，该表的结构见表2.2。创建表XS的T-SQL语句如下：USEXSCJCREATETABLEXS( 学号char(6)NOT