数据库原理及应用课件2005版第08章和表

教学内容1、数据库基本概念2、数据库操作3、表的操作4、数据库的导入/导出教学目的与要求掌握数据的基本概念和操作方法掌握表的创建、删除等基本操作掌握数据库的导入和导出教学重点数据库和表的操作难点表和列的约束2SQLServer的数据库是有组织的数据的集合，这种数据集合具有逻辑结构并得到数据库系统的管理和维护。数据库由包含数据的基本表和对象(如视图、索引、存储过程和触发器等)组成，其主要用途是处理数据管理活动产生的信息。数据库中最重要的对象是表，表中存储了数据库的数据。对数据库和表的操作是开发人员的一项重要工作。38.1数据库基本概念数据库是SQLServer2005存放表和索引等数据库对象的逻辑实体。数据库的存储结构分为逻辑存储结构和物理存储结构两种。48.1.1物理数据库数据库的物理存储结构指的是保存数据库各种逻辑对象的物理文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，SQLServer2005将数据库映射为一组操作系统文件。5

8.1.1物理数据库1.SQLServer2005的数据库文件的类型SQLServer2005的数据库具有三种类型的文件：(1)主数据文件：主数据文件是数据库的起点，指向数据库中的其他文件。每个数据库都有且只有一个主数据文件。主数据文件的推荐文件扩展名是.mdf。(2)辅助数据文件：除主数据文件以外的所有其他数据文件都是辅助数据文件。某些数据库可能不含有任何辅助数据文件，而有些数据库则含有多个辅助数据文件。辅助数据文件的推荐文件扩展名是.ndf。(3)事务日志文件：日志文件包含着用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件，当然也可以有多个。SQLServer2005事务日志采用提前写入的方式，即对数据库的修改先写入事务日志中，然后再写入数据库。日志文件的推荐文件扩展名是.ldf。6

8.1.1物理数据库2、数据库文件组SQLServer中的数据库文件组分为主文件组和用户定义文件组。①主文件组：主文件组包含主要数据库文件和任何没有明确指派给其他文件组的其他文件。数据库的系统表都包含在主文件组中。②用户定义文件组：用户定义文件组是在CREATEDATABASE或ALTERDATABASE语句中，使用FILEGROUP关键字指定的文件组。7

8.1.1物理数据库2、数据库文件组文件组应用的规则：(1)一个文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用。(2)主文件组中包含了所有的系统表。当建立数据库时，主文件组包括主数据库文件和未指定组的其它文件。(3)在创建数据库对象时如果没有指定将其放在哪一个文件组中，就会将它放在默认文件组中。如果没有指定默认文件组，则主文件组为默认文件组。(4)事务日志文件不属于任何文件组。88.1.2逻辑数据库数据库是存储数据的容器，即数据库是一个存放数据的表和支持这些数据的存储、检索、安全性和完整性的逻辑成分所组成的集合。组成数据库的逻辑成分称为数据库对象，SQLServer2005中的逻辑对象主要包括数据表、视图、同义词、存储过程、函数、触发器、规则，以及用户、角色、架构等。每个SQLServer都包含两种类型的数据库：系统数据库和用户数据库。98.1.2逻辑数据库1．master数据库顾名思义，master数据库是SQLServer2005中的主数据库，它是最重要的系统数据库，记录系统中所有系统级的信息。它对其他的数据库实施管理和控制的功能，同时该数据库还保存了用于SQLServer管理的许多系统级信息。master数据库记录所有的登录账户和系统配置，它始终有一个可用的最新master数据库备份。108.1.2逻辑数据库2．model数据库model数据库为用户新创建的数据库提供模板，它包含了用户数据库中应该包含的所有系统表的结构。当用户创建数据库时，系统会自动地把model数据库中的内容复制到新建的用户数据库中。用户在系统中新创建的所有数据库的内容，最初都与该模板数据库具有完全相同的内容。118.1.2逻辑数据库3．msdb数据库msdb数据库供SQLServer代理程序调度警报作业以及记录操作时使用。当很多用户在使用一个数据库时，经常会出现多个用户对同一个数据的修改而造成数据不一致的现象，或是用户对某些数据和对象的非法操作等。为了防止上述现象的发生，SQLServer中有一套代理程序能够按照系统管理员的设定监控上述现象的发生，及时向系统管理员发出警报。那么当代理程序调度警报作业、记录操作时，系统要用到或实时产生许多相关信息，这些信息一般存储在msdb数据库中。128.1.2逻辑数据库4．tempdb数据库使用SQLServer系统时，经常会产生一些临时表和临时数据库对象等，如用户在数据库中修改表的某一行数据时，在修改数据库这一事务没有被提交的情况下，系统内就会有该数据的新、旧版本之分，往往修改后的数据表构成了临时表。所以系统要提供一个空间来存储这些临时对象。tempdb数据库保存所有的临时表和临时存储过程。tempdb数据库是全局资源，所有连接到系统的用户的临时表和存储过程都被存储在该数据库中。138.1.2逻辑数据库此外，SQLServer2005还提供了两个样板数据库：pubs和northwind。其中pubs数据库记录了一个出版公司的数据信息。而northwind数据库则保存了一个贸易公司的数据信息。Master、model、msdb、tempdb、pubs、northwind这4个系统数据库和两个样板数据库都是在系统安装时生成的。148.2数据库操作在SQLServer2005中，用户可以自己创建数据库即用户数据库，并且可以对数据库进行修改、删除等操作。

8.2.1创建数据库若要创建数据库，必须确定数据库的名称、所有者、大小以及存储该数据库的文件和文件组。创建数据库时，根据数据库中预期的最大数据量，应创建尽可能大的数据文件。158.2.1创建数据库1．在图形界面下创建数据库在“MicrosoftSQLServerManagementStudio”中创建数据库的过程如下。（1）启动“MicrosoftSQLServerManagementStudio“，在对象资源管理器窗口的“数据库”节点上右击，选择快捷菜单中的“新建数据库”命令。如图所示：16178.2.1创建数据库（2）弹出“新建数据库”对话框，在“常规”数据页的“数据库名称”文本框中，输入要创建的数据库的名称。其中：在“常规”数据页中数据文件“初始大小”处可以设置文件的初始大小（MB）；单击自动增长方式后的按钮可设置自动增长方式和最大文件大小；单击路径后的按钮可设置文件的存放路径；在“选项”数据页中，设置数据库的属性选项。在“文件组”数据页中，增加或删除文件组。188.2.1创建数据库在对象资源管理器窗口中展开“数据库”，可以看到新建的数据库，如图所示。198.2.1创建数据库【例8-1】创建数据库“教学库”，初始大小为5MB，数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB，按1MB增长（默认是按10%比例增长）。208.2.1创建数据库2、使用CREATEDATABASE语句创建数据库语法格式如下：CREATEDATABASEdatabase_name[ON[PRIMARY][<filespec>[,…n]][,<filegroupspec>[,…n]]][LOGON{<filespec>[,…n]}][FORLOAD|FORATTACH]21<filespec>::=([NAME=logical_file_name,]FILENAME=‘os_file_name’[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment])[,…n]22说明：在T-SQL语言的命令格式中，用[]括起来的内容表示是可选的；[，…n]表示重复前面的内容；用<>括起来的内容表示在实际编写语句时，用相应的内容替代；用{}括起来的内容表示是必选的；类似A|B的格式，表示A和B只能选择一个，不能同时都选。23①database_name：新数据库的名称。数据库名称在服务器中必须惟一，最长为128个字符，并且要符合标识符的命名规则。每个服务器管理的数据库最多为32767个。②ON：指定存放数据库的数据文件信息。该关键字后面可以包含用逗号分隔的<filespec>列表，<filespec>列表用于定义主文件组的数据文件。主文件组的文件列表后可以包含用逗号分隔的<filegroup>列表，<filegroup>列表用于定义用户文件组及其中的文件。24③PRIMARY：用于指定主文件组中的文件。主文件组不仅包含数据库系统表中的全部内容，而且还包含用户文件组中没有包含的全部对象。一个数据库只能有一个主文件，默认情况下，如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。④LOGON：指明事务日志文件的明确定义。如果没有本选项，则系统会自动产生一个文件名前缀，与数据库名相同，容量为所有数据库文件大小1/4的事务日志文件。25⑤NAME：指定数据库的逻辑名称，这是在SQLServer系统中使用的名称，是数据库在SQLServer中的标识符。⑥FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。26⑦SIZE：指定数据库的初始容量大小。如果没有指定主文件的大小，则SQLServer默认其与模板数据库中的主文件大小一致，其他数据库文件和事务日志文件则默认为1MB。指定大小的数字size可以使用KB、MB、GB和TB后缀，默认的后缀为MB。size中不能使用小数，其最小值为512KB，默认值为1MB。主文件的size不能小于模板数据库中的主文件。⑧MAXSIZE：指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增大直到充满磁盘。27⑨FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。增加量可以确定为以KB、MB作后缀的字节数或以%作后缀的被增加容量文件的百分比来表示。默认后缀为MB。如果没有指定FILEGROWTH，则默认值为10%，每次扩容的最小值为64KB。288.2.1创建数据库【例8-2】使用CREATEDATABASE创建一个新的数据库，名称为“STUDENT1”，其他所有参数均取默认值。实现的步骤如下：①打开SQLServerManagementStudio，在窗口上部的工具栏的左侧找到“新建查询”按钮。298.2.1创建数据库②单击“新建查询”，在SQLServerManagementStudio的窗口右侧会建立一个新的查询页面，默认的名称为“SQLQuery1.sql”，在这个页面中可以输入要让SQLServer执行的T-SQL语句。③这里输入下面列出的创建数据库的T-SQL语句。CREATEDATABASESTUDENT1④单击工具栏中的“执行”按钮，当系统给出的提示信息为“命令已成功完成。”时，说明此数据库创建成功。308.2.1创建数据库【例8-3】创建数据库名为“STUDENT2”的数据库，包含一个主数据文件和一个事务日志文件。主数据文件的逻辑名为“STUDENT2_DATA”，操作系统文件名为“STUDENT2_DATA.MDF”，初始容量大小为5M，最大容量为20M，文件的增长量为20%。事务日志文件的逻辑文件名为“STUDENT2_LOG”，物理文件名为“STUDENT2_LOG.LDF”，初始容量大小为5M，最大容量为10M，文件增长量为2M。数据文件与事务日志文件都放在F盘根目录。318.2.1创建数据库首先在F盘创建一个新的文件夹，名称是“DATA”。在MicrosoftSQLServerManagementStudio”窗口中单击“新建查询”按钮，打开其窗口，输入如下内容，单击执行按钮，可创建数据库。32CREATEDATABASESTUDENT2ONPRIMARY(NAME='STUDENT2_DATA',FILENAME='F:\DATA\STUDENT2_DATA.MDF',SIZE=5MB,MAXSIZE=20MB,FILEGROWTH=20%)LOGON(NAME='STUDENT2_LOG',FILENAME='F:\DATA\STUDENT2_LOG.LDF',SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=2MB)338.2.1创建数据库注意：一个数据库最多可以创建32,767个文件组，文件组不能独立于数据库文件而建立，文件组是管理数据库中一组数据文件的管理机制。348.2.1创建数据库【例8-4】创建一个指定多个数据文件和日志文件的数据库。该数据库名称为STUDENTS，有1个5MB和1个10MB的数据文件和2个5MB的事务日志文件。数据文件逻辑名称为STUDENTS1和STUDENTS2，物理文件名为STUDENTS1.mdf和STUDENTS2.ndf。主文件是STUDENTS1，由PRIMARY指定，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件的逻辑名为STUDENTSLOG1和STUDENTSLOG2，物理文件名为STUDENTSLOG1.ldf和STUDENTSLOG2.ldf，最大尺寸均为50MB，文件增长速度为1MB。要求数据库文件和日志文件的物理文件都存放在E盘的DATA文件夹下358.2.1创建数据库实现的步骤如下：（1）在E盘创建一个新的文件夹，名称是“DATA”。（2）在SQLServerManagementStudio中新建一个查询页面。（3）输入以下程序段并执行此查询：36CREATEDATABASESTUDENTSONPRIMARY(NAME=STUDENTS1,FILENAME='E:\DATA\STUDENTS1.mdf',SIZE=5,MAXSIZE=unlimited,FILEGROWTH=10%),(NAME=STUDENTS12,FILENAME='E:\DATA\STUDENTS2.ndf',SIZE=10,MAXSIZE=100,FILEGROWTH=1)37LOGON(NAME=STUDENTSLOG1,FILENAME='E:\DATA\STUDENTSLOG1.ldf',SIZE=5,图8--6MAXSIZE=50,FILEGROWTH=1),(NAME=STUDENTSLOG2,FILENAME='E:\DATA\STUDENTSLOG2.ldf',SIZE=5,MAXSIZE=50,filegrowth=1)388.2.2修改数据库建好数据库后，可以对其进行修改。修改数据库包括增减数据文件和日志文件、修改文件属性（包括更改文件名和文件大小）、修改数据库选项等。398.2.2修改数据库1、增加数据库空间（1）增加已有数据库文件的大小①在图形界面下修改数据库在“MicrosoftSQLServerManagementStudio”中，在“对象资源管理器”窗口中展开“数据库”右键单击要修改的数据库的名称，在快捷菜单中选择“属性”，打开数据库属性窗口，选择“文件”属性页，修改“初始大小”选项，“自动增长”中的“文件增长”和“最大文件大小”选项。408.2.2修改数据库②使用T-SQL语句增加已有数据库文件的大小，语法格式如下：ALTERDATABASE数据库名MODIFYFILE(NAME=逻辑文件名,SIZE=文件大小,MAXSIZE=增长限制)418.2.2修改数据库【例8-5】为STUDENT2数据库增加容量，原来数据库文件STUDENT2_DATA的初始分配空间为5M，现在将STUDENT2_DATA的分配空间增加至20M。ALTERDATABASESTUDENT2MODIFYFILE(NAME=STUDENT2_DATA,SIZE=20MB)428.2.2修改数据库（2）增加数据库文件①在图形界面下修改数据库在“MicrosoftSQLServerManagementStudio”中，选择数据库属性窗口中的“文件”属性页，单击“添加”按钮，为新的数据库文件指定逻辑文件名、初始大小、文件增长方式等属性，再单击“确定”按钮即可完成增加数据库文件数目的操作。438.2.2修改数据库②使用T-SQL语句增加数据库文件的数目，语法格式如下：ALTERDATABASE数据库名ADDFILE|ADDLOGFILE(NAME=逻辑文件名,FILE=物理文件名,SIZE=文件大小,MAXSIZE=增长限制)448.2.2修改数据库【例8-6】为数据库STUDENT2增加数据文件STUDENT2_DATA1，初始大小10M，最大50M，按照5%增长。ALTERDATABASESTUDENT2ADDFILE(NAME=‘STUDENT2_DATA1’，

FILENAME='F:\DATA\STUDENT2_DATA1.NDF’,

SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5%)458.2.2修改数据库2、缩减已有数据库文件的大小①在“对象资源管理器”窗口的数据库名上右击，选择快捷菜单中的“任务”→“收缩”→“数据库”，在出现的“收缩数据库”对话框中，保持默认设置，单击“确定”按钮，数据库收缩完毕。②如果要收缩特定的数据文件或日志文件，选择快捷菜单中的“任务”→“收缩”→“文件”。③数据库的自动收缩可以在数据库的属性中“选项”选项页面中设置，只要将选项中的“自动收缩”设为“True”即可。468.2.2修改数据库3、删除数据库文件①在“MicrosoftSQLServerManagementStudio”中，选择数据库属性窗口中的“文件”属性页，指定要删除的文件，单击“删除”按钮就可以删除对应的文件，从而缩减了数据库的空间。②使用ALTERDATABASE的REMOVEFILE子句，可以删除指定的文件。语法格式如下：ALTERDATABASE数据库名REMOVEFILE逻辑文件名478.2.2修改数据库【例8-7】将数据库STUDENT2中增加的数据文件STUDENT2_DATA1删除。ALTERDATABASESTUDENT2REMOVEFILESTUDENT2_DATA1488.2.2修改数据库4、数据库更名方法一：在SQLServer管理控制台中选中此数据库，右击鼠标，在弹出的快捷菜单中选择“重命名”。方法二：是使用系统存储过程sp_renamedb更改数据库的名称。在重命名数据库之前，应该确保没有用户正在使用该数据库。系统存储过程sp_renamedb语法如下：sp_renamedb[@dbname=]'old_name',[@newname=]'new_name'498.2.2修改数据库【例8-8】将已存在的数据库STUDENT2重命名为STUDENT_BACK。实现的步骤：①在SQLServerManagementStudio中新建一个查询页面。②输入以下程序段并执行此查询：SP_RENAMEDB'STUDENT2','STUDENT_BACK'508.2.3删除数据库（1）使用SQLServerManagementStudio删除数据库打开SQLServerManagementStudio，选择“数据库”，右击要删除的数据库，在弹出的快捷菜单中选择“删除”命令，在随后出现的“删除对象”对话框中单击“确定”按钮，即可完成对指定数据库的删除操作。51

8.2.3删除数据库（2）使用DROPDATABASE语句删除数据库语法格式如下：DROPDATABASE数据库名【例8-9】删除已创建的数据库STUDENTS。实现的步骤如下：①在SQLServerManagementStudio中新建一个查询页面。②输入以下程序段并执行此查询：DROPDATABASESTUDENTS说明：用户只能根据自己的权限删除用户数据库；不能删除当前正在使用（正打开供用户读写）的数据库；无法删除系统数据库（msdb、model、master、tempdb）。528.3表的操作在数据库中，表是由数据按一定的顺序和格式构成的数据集合，是数据库的主要对象。每一行代表一条记录，每一列代表记录的一个字段。没有记录的表称为空表。在SQLServer2005中，每个数据库最多可包含20亿个表，每个表可包含1,024个字段。每个表通常都有一个主关键字（又称为主码），用于唯一地确定一条记录。在同一个表中不允许有相同名称的字段。538.3.1创建表创建好数据库后，数据库是空的，就像建造了一个空的房子,放入数据后，才成为真正的数据库。数据库中用于存储数据的当然是表，所以需要在其中创建表。1、设计表对于具体的某一个表，在创建之前，需要确定表的下列特征：①表要包含的数据的类型；②表中的列数，每一列中数据的类型和长度（如果必要）；哪些列允许空值；③是否要使用以及何处使用约束、默认设置和规则；⑤所需索引的类型，哪里需要索引，哪些列是主键，哪些是外键。54

8.3.1创建表2、使用SQLServerManagementStudio创建表【例8-10】在数据库“STUDENT1”中创建“学生表（S）”，学生表的表结构定义如表8-1所示。55表8-1字段名字段数据类型长度是否为空说明学生号char7否主键姓名char8否性别char2否出生日期datetime8是入学时间datetime8是系部代号char2是专业char10是年级char9否56

8.3.1创建表①打开SQLServerManagementStudio，在对象资源管理器中，右键单击数据库的“表”节点，选择“新建表”命令。如图所示。57②出现表设计器，在其上半部分输入列的基本属性。在表设计器下半部分的列属性，指定列的详细属性。如图所示。58③定义好表中的所有列后，单击保存按钮或“文件”菜单中的“保存表名”命令。如图所示。④在弹出的“选择名称”对话框中，为该表键入一个名称，单击“确定”按钮。598.3.1创建表3、使用Transact-SQL语句来创建表语法如下：CREATETABLE[database_name.[owner].|owner.]table_name({<column_definition>|column_nameAScomputed_column_expression|<table_constraint>}[,…n])[ON{filegroup|DEFAULT}][TEXTIMAGE_ON{filegroup|DEFAULT}]608.3.1创建表参数说明：①database_name：用于指定所创建表的数据库名称。database_name必须是现有数据库的名称。如果不指定数据库，database_name默认为当前数据库。②owner：用于指定新建表的所有者的用户名，owner必须是database_name所指定的数据库中的现有用户名，owner默认为当前注册用户名。61③table_name：用于指定新建表的名称。表名必须符合标识符规则。对于数据库来说，database_name、owner_name及object_name必须是惟一的。表名最多不能超过128个字符④column_name：用于指定新建表的列名。62⑤computed_column_expression：用于指定计算列的列值表达式。表达式可以是列名、常量、变量、函数等或它们的组合，所谓计算列是一个虚拟的列它的值并不实际存储在表中而是通过对同一个表中其它列进行某种计算而得到的结果。⑥ON{filegroup|DEFAULT}：用于指定存储表的文件组名。如果指定filegroup，则表将存储在指定的文件组中。数据库中必须存在该文件组。如果使用了DEFAULT选项，或者省略了ON子句，则新建的表会存储在默认的文件组中。63⑦TEXTIMAGE_ON：用于指定text、ntext和image列的数据存储的文件组。如果表中没有text、ntext或image列，则不能使用TEXTIMAGE_ON。如果没有指定TEXTIMAGE_ON子句，则text、ntext和image列的数据将与数据表存储在相同的文件组中。64上述创建表的语法中<column_definition>和<column_constraint>包含的内容如下：<column_definition>::={column_namedata_type}[<column_constraint>][...n]<column_constraint>::=[CONSTRAINTconstraint_name]{[NULL|NOTNULL][PRIMARYKEY|UNIQUE][FOREIGNKEY[(column)]REFERENCESref_table[(ref_column)]}658.3.1创建表字段属性参数说明：①NULL和NOTNULL：如果表的某一列被指定具有NULL属性，那么就允许在插入数据时省略该列的值。反之如果表的某一列被指定具有NOTNULL属性，那么就不允许在没有指定列缺省值的情况下插入省略该列值的数据行。在SQLServer中列的缺省属性是NOTNULL。②PRIMARYKEY：设置字段为主键。③UNIQUE：设置字段具有唯一性。66④FOREIGNKEYREFERENCESref_table[(ref_column)]：与其它表建立关联，其中ref_table（表名）

ref_column（列名）注意：使用Transact-SQL语句创建表时，应先打开其所在的数据库。打开方式如下：

use数据库名称678.3.1创建表【例8-11】在数据库“STUDENT1”下创建“成绩表（SCORE）”，表结构如表8-2所示。列名数据类型长度是否允许为空值说明学生号定长字符（char）7否主键课程号定长字符（char）3否主键成绩整数型（tinyint）1是学分整数型（tinyint）1是688.3.1创建表在SQLServerManagementStudio中新建一个查询页面，在页面内输入如下代码：USESTUDENT1GOCREATETABLESCORE

(学生号char(7)NOTNULL,

课程号 char(3)NOTNULL,

成绩tinyint,

学分tinyint,PRIMARYKEY(学生号,课程号))698.3.1创建表【例8-12】创建一个学生信息表（XSS），包括姓名、学号、性别字段。其中输入性别字段值时，只能接受“男”或者“女”，而不能接受其他数据。USESTUDENT1GOCREATETABLEXSS

(学号char(12)PRIMARYKEY,

姓名 char(8),

性别char(2)CONSTRAINTchk_sexCHECK(性别in('男','女'))708.3.2修改表当数据表的结构创建完成后，用户还可以根据实际需要随时更改表结构。用户可以增加、删除和修改字段，更改数据表名称等。718.3.2修改表1.在SQLServerManagementStudio中修改表(1)在SQLServerManagementStudio的“对象资源管理器”窗口中，单击“数据库”节点前的“+”号，展开“数据库”节点；单击目标数据库前的“+”号，展开目标数据库。(2)单击“表”节点前的“+”号，展开“表”节点。在目标表上单击鼠标右键，弹出快捷菜单，选择“修改”命令。(3)用表设计器向表中添加列、修改列的数据类型、列的数据长度、列的精度、列的小数位数、列的为空性。与创建表时相同。728.3.2修改表2．使用T-SQL语句修改表ALTERTABLEtable_name[ALTERCOLUMNcolumn_name /*修改已有列的属性*/

{new_data_type[(precision[,scale])]

[NULL|NOTNULL]}

|ADD /*增加新列*/

[column_namedata_type]

[PRIMARYKEY|constrain][FOREIGNKEY[(column)]REFERENCESref_table[(ref_column)]|DROP /*删除列*/

[CONSTRAINT]constraint_name|COLUMNcolumn_name738.3.2修改表【例8-13】在学生表中修改“姓名”字段的属性，使该字段的数据类型为varchar(50)，允许为空。USESTUDENT1ALTERTABLE学生ALTERCOLUMN姓名varchar(50)NULL748.3.2修改表【例8-14】在学生信息表XSS中添加“年龄”字段，数据类型为整型，可以为空。USESTUDENT1GOALTERTABLEXSSADD年龄int758.3.2修改表【例8-15】删除学生信息表XSS中的“年龄”字段。USESTUDENT1GOALTERTABLEXSSDROPCOLUMN年龄768.3.3列约束和表约束约束是通过限制列中数据、行中数据和表之间数据来保证数据完整性的非常有效的方法。约束可以确保把有效的数据输入到列中和维护表和表之间的特定关系。其中列约束是针对表中一个列的约束，表约束是针对表中一个或多个列的约束。MicrosoftSQLServer2005系统提供了5种约束类型，即PRIMARYKEY(主键)、FOREIGNKEY(外键)、UNIQUE、CHECK、DEFAULT约束。778.3.3列约束和表约束1、创建和删除PRIMARYKEY（主键）约束主键约束在表中定义一个主键值，这是唯一确定表中每一行数据的标识符。在所有的约束类型中，主键约束是最重要的一种约束类型，也是使用最广泛的约束类型。该约束强制实体完整性。一个表中最多只能有一个主键，且主键列不允许空值。主键经常定义在一个列上，但是也可以定义在多个列的组合上。当主键定义在多个列上时，虽然某一个列中的数据可能重复，但是这些列的组合值不能重复。788.3.3列约束和表约束①定义表的同时设置主键约束【例8-16】在“教学库”数据库中创建“学生”表，包括字段有：学生号，姓名，性别、专业；学生号为主键，USE教学库GOCREATETABLE学生(学生号char(5)PRIMARYKEY,

姓名 char(8)NOTNULL,

性别char(2),专业char(8))798.3.3列约束和表约束【例8-17】在“教学库”数据库中创建“课程”表，包括字段：课程号，课程名学分，其中课程号为主键。USE教学库GOCREATETABLE课程(课程号char(4)PRIMARYKEY,课程名char(10)NOUNULL,学分int)808.3.3列约束和表约束【例8-18】在“教学库”数据库中创建“选课”表，包括字段：学生号，课程号，成绩，其中学生号和课程号的组合为主键。USE教学库GOCREATETABLE选课(学生号char(5),

课程号char(3),成绩int,CONSTRAINTpk_jsPRIMARYKEY(学生号,课程号))818.3.3列约束和表约束当创建约束时，可以指定约束的名称。否则，MicrosoftSQLServer系统将提供一个复杂的、系统自动生成的名称。对于一个数据库来说，约束名称必须是唯一的。一般来说，约束的名称应该按照这种格式：约束类型简称_表名_列名_代号。828.3.3列约束和表约束②使用ADDCONSTRAINT子句添加约束ALTERTABLEtable_nameADD

[CONSTRAINTconstraint_name]

PRIMARYKEYCLUSTERED|NONCLUSTERED(column[,...n])838.3.3列约束和表约束【例8-19】先在“教学库”数据库中创建表XS，然后通过修改表，对学号字段创建PRIMARYKEY约束，并由系统自动创建聚集索引。USE教学库CREATETABLEXS(学号char(6)NOTNULL,姓名char(8)NOTNULL,身份证号char(20),性别bitNOTNULL)ALTERTABLEXSADDCONSTRAINTph_xsPRIMARYKEYCLUSTERED(学号)848.3.3列约束和表约束③删除PRIMARYKEY约束

ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name[,…n]【例8-20】删除“教学库”数据库中表XS的PRIMARYKEY约束ph_xs。ALTERTABLEXSDROPCONSTRAINTph_xs858.3.3列约束和表约束2、创建和删除UNIQUE约束UNIQUE约束指定表中某一个列或多个列不能有相同的两行或两行以上的数据存在。这种约束通过实现唯一性索引来强制实体完整性。当表中已经有了一个主键约束时，如果需要在其他列上实现实体完整性，又因为表中不能有两个或两个以上的主键约束，所以只能通过创建UNIQUE约束来实现。一般地，把UNIQUE约束称为候选的主键约束。868.3.3列约束和表约束例如，在xs表中，主键约束创建在“学号”列上，如果这时还需要保证该表中的存储“身份证号”列的数据是唯一的，那么可以使用UNIQUE约束。878.3.3列约束和表约束①创建表时设置UNIQUE约束。【例8-21】创建XS2表，主键约束创建在“学号”列上，要求“身份证号”列的数据是唯一的。USE教学库GOCREATETABLEXS2(学号char(6)PRIMARYKEY,姓名char(8)NOTNULL,身份证号char(20)CONSTRAINTuk_xs2UNIQUE,性别bitNOTNULL)888.3.3列约束和表约束②修改表时设置UNIQUE约束。ALTERTABLEtable_name ADD

[CONSTRAINTconstraint_name]

UNIQUE CLUSTERED|NONCLUSTERED (column[,...n])898.3.3列约束和表约束【例8-22】修改XS表“身份证号”字段值唯一。ALTERTABLEXSADD

CONSTRAINTuk_xsUNIQUECLUSTERED(身份证号)908.3.3列约束和表约束(3)删除UNIQUE约束方法与删除PRIMARYKEY约束相同。【例8-23】删除XS2表中创建的UNIQUE约束。ALTERTABLEXS2DROP

CONSTRAINTuk_xs2918.3.3列约束和表约束④UNIQUE约束使用时考虑的问题。UNIQUE约束所在的列允许空值，但是主键约束所在的列不允许空值；一个表中可以有多个UNIQUE约束；可以把UNIQUE约束放在一个或者多个列上，这些列或列的组合必须有唯一的值，但是，UNIQUE约束所在的列并不是表的主键列；UNIQUE约束强制在指定的列上创建一个唯一性索引。在默认情况下，是创建唯一性的非聚集索引。但是，在定义UNIQUE约束时也可以指定所创建的索引是聚集索引。928.3.3列约束和表约束3、创建和删除FOREIGNKEY约束在图形界面下实现连接表的步骤：①在“对象资源管理器”窗口中，展开数据库，选择“数据库关系图”，右击鼠标，如图所示。938.3.3列约束和表约束②在弹出的快捷菜单中选择“新建数据库关系图”，出现“添加表”窗口，如图所示。948.3.3列约束和表约束③选择要建立关联的表，单击“添加”按钮，关闭“添加表”窗口，出现表的关联关系，如图所示。95④关联图建好后再关闭时会弹出一窗口。⑤若要保存更改，单击“是”按钮，出现“选择名称”窗口，输入名称，单击“确定”按钮。968.3.3列约束和表约束外键约束定义一个或多个列，这些列可以引用同一个表或另外一个表中的主键约束列或UNIQUE约束列。实际上，通过创建外键约束可以实现表和表之间的依赖关系。一般情况下，在MicrosoftSQLServer关系型数据库管理系统中，表和表之间经常存在着大量的关系，这些关系都是通过定义主键约束和外键约束实现的。978.3.3列约束和表约束②利用CREATETABLE命令在创建表时定义外键约束【例8-24】在数据库STUDENT1中创建一个学生成绩表（SC），包括：学号(主键)，课程代码，成绩，并为成绩表创建外键约束，该约束把“成绩”表中的“学生号”字段和学生表（S）中的“学生号”字段关联起来。USESTUDENT1GOCREATETABLESC(学生号char（12）CONSTRAINTxs_xhFOREIGNKEYREFERENCESS(学生号),

课程号char(4),

成绩int)988.3.3列约束和表约束③利用ALTER命令添加外键约束语法格式：ALTERTABLEtable_name ADD

[

CONSTRAINTconstraint_name] FOREIGNKEY

(column[,...n]) REFERENCESref_table(ref_column[,...n])

998.3.3列约束和表约束【例8-25】将“教学库”数据库中学生表、课程表和选课表进行关联，学生表和课程表为主表，其中的学生号和课程号字段为主键，选课表为从表，将选课表的学生号和课程号字段定义为外键。①USE教学库ALTERTABLE选课ADD

CONSTRAINTxs_foreignFOREIGNKEY

(学生号)

REFERENCES学生(学生号)

②USE教学库ALTERTABLE选课ADD

CONSTRAINTkc_foreign

FOREIGNKEY

(课程号)

REFERENCES课程(课程号)

1008.3.3列约束和表约束④删除外键约束【例8-26】删除例8-24创建的外键约束。USESTUDENT1 ALTERTABLESC DROPCONSTRAINTxs_xh GO1018.3.3列约束和表约束4、创建和删除CHECK约束CHECK约束用来限制用户输入某一个列的数据，即在该列中只能输入指定范围的数据。CHECK约束的作用非常类似于外键约束，两者都是限制某个列的取值范围，但是外键是通过其他表来限制列的取值范围，CHECK约束是通过指定的逻辑表达式来限制列的取值范围。1028.3.3列约束和表约束例如，在描述学生性别列中可以创建一个CHECK约束，指定其取值范围是“男”或者“女”。这样，当向该列输入数据时，要么输入数据“男”，要么输入数据“女”，而不能输入其他不相关的数据。1038.3.3列约束和表约束①利用SQL语句在创建表时创建CHECK约束创建CHECK约束的语法格式：CREATETABLEtable_name(column_namedatatypeNOTNULL|NULL[DEFAULTconstraint_expression][[check_name]CHECK

(logical_expression)] [,…n])1048.3.3列约束和表约束【例8-27】在STUDENT1数据库中创建表books，其中包含CHECK约束定义。

USESTUDENT1 CREATETABLEbooks ( book_idsmallintPRIMARYKEY,/*书号*/ book_namevarchar(50)NOTNULL,/*书名*/ max_lvltinyintNOTNULLCHECK(max_lvl<=250) /*书允许的最高价CHECK约束*/ )1058.3.3列约束和表约束②利用SQL语句在修改表时创建CHECK约束 语法格式：

ALTERTABLEtable_name ADDCONSTRAINTcheck_nameCHECK

(logical_expression)1068.3.3列约束和表约束【例8-28】通过修改STUDENT1数据库的成绩表，增加成绩字段的CHECK约束。

USESTUDENT1 ALTERTABLE成绩表

ADDCONSTRAINTcj_constraintCHECK

(成绩>=0and成绩<=100)1078.3.3列约束和表约束③利用SQL语句删除CHECK约束 语法格式：

ALTERTABLEtable_name DROPCONSTRAINTcheck_name1088.3.3列约束和表约束【例8-29】删除例8-28创建的CHECK约束。USEstudent ALTERTABLE成绩表

dropCONSTRAINTcj_constraint1098.3.3列约束和表约束一个列上可以定义多个CHECK约束；当执行INSERT语句或者UPDATE语句时，该约束验证相应的数据是否满足CHECK约束的条件。但是，执行DELETE语句时不检查CHECK约束。1108.3.3列约束和表约束5、创建和删除DEFAULT约束当使用INSERT语句插入数据时，如果没有为某一个列指定数据，那么DEFAULT约束就在该列中输入一个值。例如，在学生表的性别列中定义了一个DEFAULT约束为“男”。当向该表中输入数据时，如果没有为性别列提供数据，那么DEFAULT约束把缺省值“男”自动插入到该列中。因此，DEFAULT约束可以实现保证域完整性。1118.3.3列约束和表约束(1)在创建表时定义默认值约束一般格式：

CREATETABLEtable_name/*指定表名*/(column_namedatatypeNOTNULL|NULL [DEFAULTconstraint_expression] /*缺省值约束表达式*/[,…n]) /*定义列名、数据类型、标识列、是否空值及定义缺省值约束*/1128.3.3列约束和表约束【例8-30】先在“教学库”数据库中创建表XS1，定义一个“入学日期”字段的默认值为系统当前日期。。

USE教学库

CREATETABLEXS1 ( 学号char(6)NOTNULL,

姓名char(8)NOTNULL,

专业名char(10)NULL,

性别bitNOTNULL,

出生时间smalldatetimeNOTNULL,

总学分tinyintNULL,

备注textNULL,

入学日期datetimeDEFAULTgetdate())/*定义默认值约束*/ 1138.3.3列约束和表约束说明：没有使用“CONSTRAINT约束名”则使用系统定义的名称。(2)在修改表时定义默认值约束【例8-31】修改XS1表，添加一字段AddDate，并为其设置默认值约束，默认值为当前日期。 USE教学库ALTERTABLExs1 ADDAddDatesmalldatetimeNULL CONSTRAINTAddDateDflt/*默认值约束名*/ DEFAULTgetdate()1148.3.3列约束和表约束(3)删除默认值约束【例8-32】删除例8-31定义的默认值约束。

USE教学库

ALTERTABLEXS1 DROPCONSTRAINTAddDateDflt1158.3.3列约束和表约束(4)定义DEFAULT约束时应考虑的问题定义的常量值必须与该列的数据类型和精度是一致的；DEFAULT约束只能应用于INSERT语句；每一个列只能定义一个DEFAULT约束。DEFAULT约束不能放在有IDENTITY属性的列上或者数据类型为timestamp的列上。因为这两种列都会由系统自动提供数据。DEFAULT约束允许指定一些由系统函数提供的值，这些系统函数包括SYSTEM_USER、GETDATE、CURRENT_USER等。1168.3.4表数据操作表的基本结构建好后，表内没有数据，我们可以在SQLServer管理控制台中非常方便地对数据执行各种操作，也可以利用Transact-SQL中的命令完成相应的功能。1178.3.4表数据操作1、输入数据(1)利用SQLServer管理控制台输入数据：①在“对象资源管理器”窗口中，展开数据库和表，右键单击表名，弹出快捷菜单，选择“打开表”如图所示。1188.3.4表数据操作②出现一个空表，如左图所示。输入数据，如右图所示。1198.3.4表数据操作(2)利用Transact-SQL中的命令完成数据插入。语法格式如下：INSERT[INTO]table_name{[(column_list)]{VALUES({expression}[,...n])}}1208.3.4表数据操作语法注释：①INTO：一个可选的关键字，可以将它用在INSERT和目标表之间。②table_name：将要接收数据的表或table变量的名称。③column_list：要在其中插入数据的一列或多列的列表。必须用圆括号将column_list括起来，并且用逗号进行分隔。④VALUES：引入要插入的数据值的列表。对于column_list中或者表中的每个列，都必须有一个数据值。必须用圆括号将值列表括起来。如果VALUES列表中的值与表中列的顺序不相同，或者未包含表中所有列的值，那么必须使用column_list明确地指定存储每个传入值的列。1218.3.4表数据操作【例8-33】在STUDENT1数据库的学生信息(XSS)表中插入一行数据（学号、姓名、性别）。USESTUDENTINSERTintoxsxx(学号，姓名，性别)VALUES('112','杨丽','女')1228.3.4表数据操作2、修改表中的数据(1)使用SQLServerManagementStudio修改数据在SQLServerManagementStudio中，选择相应的表，鼠标右击后，在弹出的快捷菜单中选择“打开表”命令，出现表数据窗口，在该窗口中，可以直接对数据进行修改操作。1238.3.4表数据操作(2)利用T-SQL中的命令完成对数据的修改语法格式如下：UPDATEtable_nameSET{column_name={expression}}[,...n][where{condition_expression}]1248.3.4表数据操作语法注释：①table_name：需要更新的表的名称。②SET：指定要更新的列或变量名称的列表。③column_name：含有要更改数据的列的名称。④{expression}：列值表达式。⑤{condition_expression}：条件表达式。对条件的个数没有限制。⑥如果没有WHERE子句，则UPDATE将会修改表中的每一行数据。1258.3.4表数据操作【例8-34】在STUDENT1数据库的学生信息(XSS)表中的“性别”字段的值设为“男”。USESTUDENT1UPDATEXSSSET性别='男'1268.3.4表数据操作【例8-35】在STUDENT1数据库的学生表中添加一字段“备注”varchar(20)；“备注”字段信息为'已毕业'。①USESTUDENT1ALTERTABLESAdd备注varchar(20)②UPDATE学生SET备注='已毕业'1278.3.4表