第3章表与表数据操作课件.pptx

《第3章表与表数据操作课件.pptx》由会员分享，可在线阅读，更多相关《第3章表与表数据操作课件.pptx（90页珍藏版）》请在三一办公上搜索。

1、第3章 表与表数据操作,本章要点,在关系数据库中，每个关系都对应为一张表，表是数据库的最主要对象，是信息世界实体或实体间联系的数据表示，是用来存储与操作数据的逻辑结构，使用数据库时，绝大多数时间都是在与表打交道。因此掌握SQL Server表的相关知识与相关操作是非常重要的。本章围绕表对象主要介绍如下内容：表的概念、如何设计表、如何创建和修改表、表信息的交互式查询与维护、删除表等。,第3章 表与表数据操作,3.1 表结构和数据类型,3.2 界面方式操作表,3.3 命令方式操作表,3.4 界面方式操作表数据,3.5 命令方式操作表数据,3.1 表结构和数据类型,3.1.1 表和表结构每个数据库包

2、含了若干个表。表是SQL Server中最主要的数据库对象，它是用来存储数据的一种逻辑结构。表由行和列组成，因此也称之为二维表。表是在日常工作和生活中经常使用的一种表示数据及其关系的形式，表3.1就是用来表示学生情况的一个“学生”表。,表3.1 “学生”表,3.1.1 表和表结构,下面简单介绍与表有关的几个概念：（1）表结构。组成表的各列的名称及数据类型，统称为表结构。（2）记录。每个表包含了若干行数据，它们是表的“值”，表中的一行称为一个记录。因此，表是记录的有限集合。（3）字段。每个记录由若干个数据项构成，将构成记录的每个数据项称为字段。例如表3.1中，表结构为（学号, 姓名, 性别, 出

3、生时间, 专业, 总学分, 备注），包含7个字段，由5个记录组成。,3.1.1 表和表结构,下面简单介绍与表有关的几个概念：（4）空值。空值（NULL）通常表示未知、不可用或将在以后添加的数据。若一个列允许为空值，则向表中输入记录值时可不为该列给出具体值。而一个列若不允许为空值，则在输入时必须给出具体值。（5）关键字。若表中记录的某一字段或字段组合能唯一标识记录，则称该字段或字段组合为候选关键字（Candidate key）。若一个表有多个候选关键字，则选定其中一个为主关键字（Primary key），也称为主键。当一个表仅有唯一的一个候选关键字时，该候选关键字就是主关键字。,3.1.2 数据

4、类型,设计数据库表结构，除了表属性外，主要就是设计列属性。在表中创建列时，必须为其指定数据类型，列的数据类型决定了数据的取值、范围和存储格式。列的数据类型可以是SQL Server提供的系统数据类型，也可以是用户定义的数据类型。,3.1.2 数据类型,表3.2 系统数据类型表,3.1.2 数据类型,1整数型整数包括bigint、int、smallint和tinyint，从标识符的含义就可以看出，它们的表示数范围逐渐缩小。bigint：大整数，数范围为-263（-9223372036854775808）263-1（9223372036854775807），其精度为19，小数位数为0，长度为8字节

5、。int：整数，数范围为-231（-2147483648）231-1（2147483647），其精度为10，小数位数为0，长度为4字节。smallint：短整数，数范围为-215（-32768）215-1（32767），其精度为5，小数位数为0，长度为2字节。tinyint：微短整数，数范围为 0255，长度为1字节，其精度为3，小数位数为0，长度为1字节。,3.1.2 数据类型,2精确数值型decimal和numeric可存储从-1038+1到1038-1的固定精度和小数位的数字数据，它们的存储长度随精度变化而变化，最少为5字节，最多为17字节。精度为19时，存储字节长度为5。精度为1019

6、时，存储字节长度为9。精度为2028时，存储字节长度为13。精度为2938时，存储字节长度为17。例如，若有声明numeric(8,3)，则存储该类型数据需5字节；而若有声明numeric(22,5)，则存储该类型数据需13字节。,3.1.2 数据类型,3浮点型有两种近似数值数据类型：float(n)和real。两者通常都使用科学计数法表示数据，即形为：尾数E阶数，如5.6432E20、-2.98E10、1.287659E-9等。real：使用4字节存储数据，表数范围为-3.40E+383.40E+38，数据精度为7位有效数字。float：float型数据的数范围为-1.79E+3081.79

7、E+308。定义中的n取值范围是153，用于指示其精度和存储大小。当n在124之间时，实际上是定义了一个real型数据，存储长度为4字节，精度为7位有效数字。当n在2553之间时，存储长度为8字节，精度为15位有效数字。当缺省n时，代表n在2553之间。,3.1.2 数据类型,4货币型SQL Server提供了两个专门用于处理货币的数据类型：money和smallmoney，它们用十进制数表示货币值。money：数据的数范围为-263（-922337203685477.5808）263-1（922337203685477.5807），其精度为19，小数位数为4，长度为8字节。money的数的范

8、围与bigint相同，不同的只是money型有4位小数。实际上，money就是按照整数进行运算的，只是将小数点固定在末4位。smallmoney：数范围为-231（-214748.3648）231-1（214748.3647），其精度为10，小数位数为4，长度为4字节。可见smallmoney与int的关系就如同money与bigint的关系。,3.1.2 数据类型,5位型SQL Server中的位（bit）型数据相当于其他语言中的逻辑型数据，它只存储0和1，长度为一个字节。但要注意，SQL Server对表中bit类型列的存储做了优化：如果一个表中有不多于8个的bit列，这些列将作为一个字节

9、存储；如果表中有9到16个bit列，这些列将作为两个字节存储；更多列的情况依次类推。当为bit类型数据赋0时，其值为0，而赋非0（如100）时，其值为1。字符串值TRUE和FALSE可以转换为以下bit 值：TRUE转换为1，FALSE转换为0。,3.1.2 数据类型,6字符型字符型数据用于存储字符串，字符串中可包括字母、数字和其他特殊符号（如#、&等）。在输入字符串时，需将串中的符号用单引号或双引号括起来，如abc、AbcCde。SQL Server字符型包括两类：固定长度（char）或可变长度（varchar）字符数据类型。char(n)：定长字符数据类型，其中n定义字符型数据的长度，n在

10、1到8000之间，缺省为1。当表中的列定义为char(n)类型时，若实际要存储的串长度不足n时，则在串的尾部添加空格以达到长度n，所以char(n)的长度为n。若输入的字符个数超出了n，则超出的部分被截断。varchar(n)：变长字符数据类型，其中n的规定与定长字符型char中n完全相同，但这里n表示的是字符串可达到的最大长度。,3.1.2 数据类型,7Unicode字符型Unicode是“统一字符编码标准”，用于支持国际上非英语语种的字符数据的存储和处理。SQL Server的Unicode字符型可以存储Unicode标准字符集定义的各种字符。Unicode字符型包括nchar(n)和nv

11、archar(n)两类。nchar是固定长度Unicode数据的数据类型，nvarchar是可变长度 Unicode 数据的数据类型，二者均使用UNICODE UCS-2字符集。nchar(n)：nchar(n)为包含n个字符的固定长度 Unicode 字符型数据，n的值在1与4000之间，缺省为1，长度2n字节。若输入的字符串长度不足n，将以空白字符补足。nvarchar(n)：nvarchar(n)为最多包含n个字符的可变长度Unicode字符型数据，n的值在1与4000之间，缺省为1。长度是所输入字符个数的两倍。实际上，nchar、nvarchar与char、varchar的使用非常相似

12、，只是字符集不同（前者使用Unicode字符集，后者使用ASCII字符集）。,3.1.2 数据类型,8文本型文本型包括text和ntext两类，分别对应ASCII字符和Unicode字符。text类型可以表示最大长度为 231-1（2147483647）个字符，其数据的存储长度为实际字符数个字节。ntext类型可表示最大长度为 230-1（1073741823）个Unicode字符，其数据的存储长度是实际字符个数的两倍（以字节为单位）。,3.1.2 数据类型,9二进制型二进制数据类型表示的是位数据流，包括binary（固定长度）和varbinary（可变长度）两种。binary (n)：固定长

13、度的n个字节二进制数据。n取值范围为1到8000，缺省为1。binary(n)数据的存储长度为n+4字节。若输入的数据长度小于n，则不足部分用0填充；若输入的数据长度大于n，则多余部分被截断。varbinary (n)：n个字节变长二进制数据。n取值范围为1到8000，缺省为1。varbinary(n)数据的存储长度为实际输入数据长度+4个字节。,3.1.2 数据类型,10日期时间类型日期时间类型数据用于存储日期和时间信息，包括datetime和smalldatetime两类。datetime：datetime类型可表示的日期范围从1753 年1月1日到9999年12月31日的日期和时间数据，

14、精确度为百分之三秒（3.33毫秒或0.00333秒），例如1到3毫秒的值都表示为0毫秒，4到6毫秒的值都表示为4毫秒。datetime类型数据长度为8字节，日期和时间分别使用4个字节存储。前4字节用于存储datetime类型数据中距1900年1月1日的天数。为正数表示日期在1900年1月1日之后，为负数则表示日期在1900年1月1日之前。,3.1.2 数据类型,10日期时间类型日期时间类型数据用于存储日期和时间信息，包括datetime和smalldatetime两类。用户给出datetime类型数据值时，日期部分和时间部分分别给出。日期部分的表示形式常用的格式如下：,3.1.2 数据类型,说

15、明：年可用4位或2位表示，月和日可用1位或2位表示。时间部分常用的表示格式如下：,smalldatetime：smalldatetime类型数据可表示从1900年1月1日到2079年6月6日的日期和时间，数据精确到分钟。即29.998秒或更低的值向下舍入为最接近的分钟，29.999秒或更高的值向上舍入为最接近的分钟。,3.1.2 数据类型,11时间戳型标识符是timestamp。若创建表时定义一个列的数据类型为时间戳类型，那么每当对该表加入新行或修改已有行时，都由系统自动将一个计数器值加到该列，即将原来的时间戳值加上一个增量。记录timestamp列的值实际上反映了系统对该记录修改的相对（相对

16、于其他记录）顺序。一个表只能有一个timestamp 列。timestamp类型数据的值实际上是二进制格式数据，其长度为8字节。,3.1.2 数据类型,12图像数据类型标识符是image，它用于存储图片、照片等。实际存储的是可变长度二进制数据，介于0与231-1（2147483647）字节之间。在SQL Server 2005中该类型是为了向下兼容而保留的数据类型。微软推荐用户使用varbinary(MAX)数据类型来替代image类型。,3.1.2 数据类型,13其他数据类型SQL Server 2005还提供了其他几种数据类型：cursor、sql_variant、table和unique

17、identifier。cursor：是游标数据类型，用于创建游标变量或定义存储过程的输出参数。sql_variant：是一种存储SQL Server支持的各种数据类型（除text、ntext、image、timestamp和sql_variant外）值的数据类型。sql_variant的最大长度可达8016字节。table：是用于存储结果集的数据类型，结果集可以供后续处理。uniqueidentifier：是唯一标识符类型。系统将为这种类型的数据产生唯一标识值，它是一个16字节长的二进制数据。xml：是用来在数据库中保存xml文档和片段的一种类型，但是此种类型的文件大小不能超过2GB。,3.1

18、.3 表结构设计,学生管理系统的三个表：学生表（表名为XSB）、课程表（表名为KCB）和成绩表（表名为CJB）为例介绍如何设计表的结构。例如“性别”列只有“男”、“女”两种值，所以可以使用bit型数据，值1表示“男”，值0表示“女”，默认是1；“出生时间”是日期时间类型数据，列类型定为datetime； “备注”列需要存放学生的备注信息，备注信息的内容在0到500个字之间，所以应该使用varchar类型。在XSB表中，只有“学号”列能唯一标识一个学生，所以将“学号”列设为该表的主键。,3.1.3 表结构设计,表3.3 XSB的表结构,3.1.3 表结构设计,表3.4 KCB的表结构,表3.5

19、CJB的表结构,3.1.4 表的分类,SQL Server 2005的表可分为：用户基本表、已分区表、临时表与系统表四类。 （1）用户基本表：是存放用户数据的标准表，是数据库中最基本、最主要的对象。 （2）已分区表：是将数据水平划分为多个单元的表，这些单元可以分布到数据库中的多个文件组中。在维护整个集合的完整性时，使用分区可以快速而有效地访问或管理数据子集，从而使大型表或索引更易于管理。在分区方案下，将数据从 OLTP 加载到 OLAP 系统中这样的操作只需几秒钟，而不是像在早期版本中那样需要几分钟或几小时。对数据子集执行的维护操作也将更有效，因为它们的目标只是所需的数据，而不是整个表。,3.

20、1.4 表的分类,SQL Server 2005的表可分为：用户基本表、已分区表、临时表与系统表四类。 （2）已分区表已分区表支持所有与设计和查询标准表关联的属性和功能，包括约束、默认值、标识和时间戳值、触发器和索引。分区表主要用于：1）表中包含或可能包含以不同方式使用的许多数据；2）对表的查询或更新没有按照预期的方式执行，或者维护开销超出了预定义的维护期。,（3）临时表 临时表有两种类型：本地临时表和全局临时表。在与首次创建或引用表时相同的SQL Server实例连接期间，本地临时表只对于创建者是可见的。当用户与SQL Server实例断开连接后，将删除本地临时表。全局临时表在创建后对任何用

21、户和任何连接都是可见的，当引用该表的所有用户都与SQL Server实例断开连接后，将删除全局临时表。 （4）系统表 SQL Server将定义服务器配置及其定义所有表的数据存储在一组特殊的表中，这组表称为系统表。除非通过专用的管理员连接，否则用户无法直接查询或更新系统表。也可以通过目录视图查看系统表中的信息。,3.1.4 表的分类,3.2 界面方式操作表,3.2.1 创建表以下是通过“对象资源管理器”创建表XSB的操作步骤：第1步 启动“SQL Server Management Studio”在“对象资源管理器”中展开“数据库”右击“PXSCJ”数据库菜单下的“表”选项，在弹出的快捷菜单中

22、选择“新建表”菜单项，打开如图3.1所示的“表设计器”窗口。,图3.1 “表设计器”窗口,3.2.1 创建表,第2步 在“表设计器”窗口中，根据已经设计好的XSB的表结构分别输入或选择各列的名称、数据类型、是否允许为空值等属性。根据需要，可以在列属性表格填入相应的内容。第3步 在“学号”列上右击鼠标，选择“设置主键”菜单项，选择“设为主键”选项，如图3.2所示。在列属性窗口中的“默认值和绑定”和“说明”项中分别填写各列的默认值和说明。,图3.2 设置XSB表的主键,3.2.1 创建表,学生情况表结构设计完成后的结果如图3.3所示。,图3.3 表属性编辑完成结果,3.2.1 创建表,第4步 在表

23、的各列的属性均编辑完成后，单击工具栏中的“保存”按钮，出现“选择表名”对话框。在“选择表名”对话框中输入表名“XSB”，单击“确定”按钮，这样XSB表就创建好了。在“对象资源管理器”窗口中可以找到新创建的表XSB，如图3.4所示。,图3.4 新创建的XSB表,3.2.1 创建表,第5步 使用同样的方法创建课程表，名称为KCB；创建成绩表，名称为CJB。KCB表创建后的界面如图3.5所示，CJB创建后的界面如图3.6所示。,图3.5 创建KCB表,3.2.1 创建表,图3.6 创建CJB表,说明：记住：表是属于 架构的，而架构是属于数据库的。每个表至多可定义1024列。表和列的名称必须遵守标识符

24、的规定，在特定表中必须是唯一的，但同一数据库的不同表中可使用相同的列名尽管对于每一个架构在一个数据库内表的名称必须是唯一的，但如果为每张表指定了不同的架构，则可以创建多个具有相同名称的表。可以创建名为employees的两个表并分别指定Comp1和Comp2作为其架构。当您必须使用某一employees表时，可以通过指定表的架构以及表的名称来区分这两个同名表。,3.2.1 创建表,用户架构分离,SQL Server 2005 切断数据库用户和架构之间的隐式连接。 什么是架构？架构是形成单个命名空间的数据库实体的集合。命名空间是一个集合，其中每个元素的名称都是唯一的。例如，为了避免名称冲突，同一

25、架构中不能有两个同名的表。两个表只有在位于不同的架构中时才可以同名。 注意：在讨论数据库工具时，“架构”还指目录信息，用于说明架构或数据库中的对象。在讨论 Analysis Services 时，“架构”指多维对象，如多维数据集和维度。,用户架构分离（续1）,架构是形成单个命名空间的数据库实体的集合。虽然 SQL Server 2000 包含 CREATE SCHEMA 语句，但实际上并不会像上面所定义的那样创建架构。在 SQL Server 2000 中，数据库用户和架构是隐式连接在一起的。每个数据库用户都是与该用户同名的架构的所有者。对象的所有者在功能上与包含它的架构所有者相同。因而，SQ

26、L Server 2000 中的完全限定名称的“架构”也是数据库中的用户。因此，从 SQL Server 2000 数据库中删除用户之前，管理员需要删除该用户所拥有的所有对象或更改这些对象的所有者。以包含此对象的SQL Server 2000数据库为例：accounting.ap.george.balance,转让对象的所有权也会更改其完全限定名称。引用accounting.ap.george.balance的任何代码必须经过更新以反映对名称所做的更改。 在 SQL Server中，架构独立于创建它们的数据库用户而存在。可以在不更改架构名称的情况下转让架构的所有权。并且可以在架构中创建具有用户

27、友好名称的对象，明确指示对象的功能。例如，除了accounting.ap.sandra.balance外，您还可以创建名为accounting.ap.invoice.balance的架构。因为“invoice”不是用户，所以从数据库中删除用户后，无需更改此名称。这就简化了数据库管理员和开发人员的工作。,用户架构分离（续2）,将架构与数据库用户分离对管理员和开发人员而言有下列好处：1）多个用户可以通过角色成员身份或 Windows 组成员身份拥有一个架构。这扩展了允许角色和组拥有对象的用户熟悉的功能。2）极大地简化了删除数据库用户的操作。3）删除数据库用户不需要重命名该用户架构所包含的对象。因而

28、，在删除创建架构所含对象的用户后，不再需要修改和测试显式引用这些对象的应用程序。4）多个用户可以共享一个默认架构以进行统一名称解析。5）开发人员通过共享默认架构可以将共享对象存储在为特定应用程序专门创建的架构中，而不是 DBO 架构中。6）可以用比早期版本中的粒度更大的粒度管理架构和架构包含的对象的权限。7）完全限定的对象名称现在包含四部分：server.database.schema.object。,用户架构分离（续3）,SQL Server 2005还引入了“默认架构”的概念，用于解析未使用其完全限定名称引用的对象的名称。在SQL Server 2005中，首先检查的是调用数据库用户所拥有

29、的架构，然后是 DBO 拥有的架构。在SQL Server 2005中，每个用户都有一个默认架构，用于指定服务器在解析对象的名称时将要搜索的第一个架构。可以使用CREATE USER和ALTER USER的DEFAULT_SCHEMA选项设置和更改默认架构。如果未定义DEFAULT_SCHEMA，则数据库用户将把 DBO 作为其默认架构。许多实体的所有权都可以转让。,用户架构分离（续4）,3.2.2 修改表结构,1更改表名【例3.1】 将XSB表的表名改为student。在“对象资源管理器”中选择需要更名的表XSB，右击鼠标，在弹出的快捷菜单上选择“重命名”菜单项，如图3.7所示，输入新的表名

30、student，按下回车键即可更改表名。,图3.7 修改表名,3.2.2 修改表结构,2增加列【例3.2】 向表XSB中添加一个“奖学金等级”列，“奖学金等级”列为“tinyint”，允许为空值。第1步 启动“SQL Server Management Studio”，在“对象资源管理器”中展开“数据库”，选择“PXSCJ”在“PXSCJ”数据库中选择表“dbo.XSB”，右击鼠标，在弹出的快捷菜单上选择“修改”菜单项，打开“表设计器”窗口。第2步 在“表设计器”窗口中选择第一个空白行，输入列名“奖学金等级”，选择数据类型“tinyint”，如图3.8所示。如果要在某列之前加入新列，可以右击该

31、列，选择“插入列”，在空白行填写列信息即可。,图3.8 增加新列,3.2.2 修改表结构,3删除列在“表dbo.XSB设计器”窗口中选择需删除的列（例如XSB表中删除“奖学金等级”列），此时箭头指在该列上，右击鼠标，在弹出的快捷菜单上选择“删除列”菜单项，该列即被删除。4修改列表中尚未有记录值时，可以修改表结构，如更改列名、列的数据类型、长度和是否允许空值等属性。但当表中有了记录后，建议不要轻易改变表结构，特别不要改变数据类型，以免产生错误。,3.2.2 修改表结构,4修改列（1）具有以下特性的列不能被修改：具有text、ntext、image或timestamp数据类型的列。计算列。全局标识

32、符列。复制列。用于索引的列（但若用于索引的列为varchar、nvarchar或varbinary数据类型时，可以增加列的长度）。用于由“CREATE STATISTICS”生成统计的列。若需修改这样的列，必须先用“DROP STATISTICS”；语句删除统计。,3.2.2 修改表结构,用于主键或外键约束的列。用于CHECK或UNIQUE约束的列。关联有默认值的列。（2）当改变列的数据类型时，要求满足下列条件：原数据类型必须能够转换为新数据类型。新类型不能为timestamp类型。如果被修改列属性中有“标识规范”，则新数据类型必须是有效的“标识规范”数据类型。,3.2.2 修改表结构,【例3

33、.3】 在XSB表中，将“姓名”列名改为“name”，数据长度由8改为10，允许为空值。将“出生时间”列名改为“birthday”，数据类型由“datetime”改为“smalldatetime”。因尚未输入记录值，所以可以改变XSB表的结构。右击需要修改的XSB表，选择“修改”选项进入表XSB的设计窗口，单击需要修改的列，修改相应的属性。修改完后保存。,3.2.3 删除表,删除一个表时，表的定义、表中的所有数据以及表的索引、触发器、约束等均被删除。注意：不能删除系统表和有外键约束所参照的表。【例3.4】 使用界面方式删除表XSB。启动“SQL Server Management Studio

34、”，在“对象资源管理器”中展开“数据库” “PXSCJ”“表”选择要删除的表XSB，右击鼠标，在弹出的快捷菜单上选择“删除”菜单项。系统弹出“删除对象”窗口。单击“确定”按钮，即可删除选XSB表。,3.3 命令方式操作表,3.3.1 创建表创建表使用“CREATE TABLE”语句。语法格式：CREATE TABLE database_name . schema_name . | schema_name . table_name ( /*列的定义*/ | column_name AS computed_column_expression PERSISTED NOT NULL /*定义计算列*/

35、 ,.n /*指定表的约束*/) ON partition_scheme_name ( partition_column_name ) | filegroup | default /*指定分区方案和存储表的文件组*/ TEXTIMAGE_ON filegroup | default /*指定存储text、ntext和image类型数据的文件组*/ ; ,3.3.1 创建表,说明：database_name是数据库名，schema_name是新表所属架构的名称，table_name是表名，表的标识按照对象命名规则。如果省略数据库名则默认在当前数据库中创建表；如果省略架构名，则默认是“dbo”。

36、:=column_name data_type/*指定列名、类型*/ COLLATE collation_name /*指定排序规则*/ NULL | NOT NULL /*指定是否为空*/ CONSTRAINT constraint_name DEFAULT constant_expression /*指定默认值*/| IDENTITY ( seed ,increment ) NOT FOR REPLICATION /*指定列为标识列*/ ROWGUIDCOL /*指定列为全局标识符列*/ .n /*指定列的约束*,3.3.1 创建表,说明：默认情况下，计算所得的列包含计算公式的定义，但在物

37、理上不存储数据。返回数据时，应用该计算公式以返回一个结果值。可通过persisted关键字，强制一个计算所得的列在物理上存储数据。该关键字使公式计算在插入或修改时发生，然后将计算结果存储在表中。,3.3.1 创建表,【例3.5】 设已经创建了数据库PXSCJ，现在该数据库中需创建学生情况表XSB，该表的结构见表3.3。USE PXSCJGOCREATE TABLE XSB( 学号 char(6) NOT NULL PRIMARY KEY,姓名 char(8) NOT NULL,性别 bit NULL DEFAULT 1,出生时间 datetime NULL,专业 char(12) NULL,总

38、学分 int NULL,备注 varchar(500) NULL)GO,3.3.1 创建表,【例3.6】 创建一个带计算列的表，表中包含课程的课程号、总成绩和学习该课程的人数以及课程的平均成绩。创建表的T-SQL语句如下：CREATE TABLE PJCJ(课程号 char(3) PRIMARY KEY,总成绩 real NOT NULL,人数 int NOT NULL,平均成绩 AS 总成绩/人数 PERSISTED)GO,3.3.2 创建分区表,当表中存储了大量数据，而且这些数据经常被不同的使用方式访问的时候，处理时必然会降低数据库的效率，这时就需要建立分区表。分区表是将数据分成多个单元的

39、表，这些单元可以分散到数据库中的多个文件组中，实现对单元数据的并行访问，从而实现了对数据库的优化，提高了查询效率。,3.3.2 创建分区表,在SQL Server 2005中创建分区表的步骤包括：创建分区函数，指定如何分区；创建分区方案，定义分区函数在文件组上的位置；使用分区方案。创建分区函数使用“CREATE PARTITION FUNCTION”命令，语法格式如下：CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )AS RANGE LEFT | RIGHT FOR VALUES ( bound

40、ary_value ,.n ) ; 注：只有企业版的SQLServer 2005才有此功能。,3.3.2 创建分区表,说明：partition_function_name 是分区函数的名称。分区函数名称在数据库内必须唯一input_parameter_type 是用于分区的列的数据类型。boundary_value 为使用 partition_function_name 的已分区表或索引的每个分区指定边界值。如果 boundary_value 为空，则分区函数使用 partition_function_name 将整个表或索引映射到单个分区。LEFT | RIGHT 指定当间隔值由 数据库引擎

41、 按升序从左到右排序时，boundary_value ,.n 属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT。,3.3.2 创建分区表,【例3.7】 针对int类型的列创建一个名为NumberPF的分区函数，该函数把int类型的列中数据分成5个区。分为小于或等于50的区、大于50且小于或等于500的区、大于500且小于或等于1000的区、大于1000且小于或等于2000的区、大于2000的区。使用如下T-SQL语句：CREATE PARTITION FUNCTION NumberPF(int)AS RANGE LEFT FOR VALUES(50,500,1000

42、,2000)GO,创建一个分区方案,分区函数创建完后可以使用CREATE PARTITION SCHEME命令创建分区方案，由于在创建分区方案时需要根据分区函数的参数定义映射分区的文件组。所以需要有文件组来容纳分区数，文件组可以由一个或多个文件构成，而每个分区必须映射到一个文件组。一个文件组可以由多个分区使用。在一般情况下，文件组数最好与分区数相同，并且这些文件组通常位于不同的磁盘上。一个分区方案只可以使用一个分区函数，而一个分区函数可以用于多个分区方案中。,3.3.2 创建分区表,【例3.8】 假设文件组Fgroup1、Fgroup2、Fgroup3、Fgroup4、Fgroup5已经在数据

43、库PXSCJ中存在。根据例3.7中定义的分区函数创建一个分区方案，将分区函数中的5个分区分别存放在这5个文件组中。使用如下T-SQL语句：CREATE PARTITION SCHEME NumberPSAS PARTITION NumberPFTO(Fgroup1, Fgroup2, Fgroup3, Fgroup4, Fgroup5)GO,3.3.2 创建分区表,使用分区方案创建分区表。分区函数和分区方案创建以后就可以创建分区表了。创建分区表使用CREATE TABLE语句，只要在ON关键字后指定分区方案和分区列即可。 【例3.9】 在数据库PXSCJ中创建分区表，表中包含编号（值可以是15

44、000）、名称两列，要求使用例3.8中的分区方案。CREATE TABLE sample(编号 int NOT NULL PRIMARY KEY,名称 char(8) NOT NULL)ON NumberPS(编号)GO,创建分区表的说明,虽然分区可以带来众多的好处，但也增加了实现对象的管理操作和复杂性。所以，没必要为较小的表或目前满足性能和维护要求的表分区。,3.3.3 修改表结构,修改表结构可以使用“ALTER TABLE”语句。,ALTER TABLE database_name . schema_name . | schema_name . table_name ALTER COLUM

45、N column_name /*修改已有列的属性*/ new_data_type ( precision , scale ) COLLATE NULL | NOT NULL | ADD | DROP ROWGUIDCOL | PERSISTED | WITH CHECK | NOCHECK ADD /*添加列*/ | column_name AS computed_column_expression PERSISTED NOT NULL| ,.n .,3.3.3 修改表结构,修改表结构可以使用“ALTER TABLE”语句。,| DROP /*删除列*/ CONSTRAINT constrai

46、nt_name WITH ( ,.n ) | COLUMN column_name ,.n | WITH CHECK | NOCHECK CHECK | NOCHECK CONSTRAINT ALL | constraint_name ,.n | ENABLE | DISABLE TRIGGER ALL | trigger_name ,.n | SWITCH PARTITION source_partition_number_expression TO schema_name. target_table PARTITION target_partition_number_expression

47、,3.3.3 修改表结构,【例3.10】 设已经在数据库PXSCJ中创建了表XSB。先在表XSB中增加1个新列奖学金等级。然后在表XSB中删除名为“奖学金等级”的列。在“SQL Server Management Studio”中新建一个查询，并输入脚本如下：USE PXSCJGOALTER TABLE XSBADD 奖学金等级 tinyint NULLGO输入完成后执行该脚本，然后可以在“对象资源管理器”展开“PXSCJ”中表dbo.XSB的结构查看运行结果。,3.3.3 修改表结构,【例3.11】 修改表XSB中已有列的属性：将名为“姓名”的列长度由原来的8改为10；将名为“出生时间”的列

48、的数据类型由原来的datetime改为smalldatetime。新建一个查询，在“查询分析器”中输入并执行如下脚本：USE PXSCJGOALTER TABLE XSBALTER COLUMN 姓名 char(10)GOALTER TABLE XSBALTER COLUMN 出生时间smalldatetime,2022/12/11,Qzhicheng 第2章 数据库和表创建,66,2.3.5 使用ALTER TABLE修改表,例:修改表XS中已有列的属性：将名为“姓名”的列更名为“学生姓名”格式：Sp_rename 表名.原列名,新列名，COLUMN例： SP_rename XS.姓名 ,

49、学生姓名 , COLUMN,3.3.4 删除表,语法格式：DROP TABLE table_name其中，table_name是要被删除的表名。例如，要删除表XSB，使用的T-SQL语句为：USE PXSCJGODROP TABLE XSBGO,3.4 界面方式操作表数据,通过“SQL Server Management Studio”操作表数据的方法如下：启动“SQL Server Management Studio”在“对象资源管理器”中展开“数据库 PXSCJ”选择要进行操作的表XSB，右击鼠标，在弹出的快捷菜单上选择“打开表”菜单项，打开如图3.9所示的表数据窗口。,图3.9 操作表数

50、据窗口,3.4.1 插入记录,用户可以自己根据需要向表中插入数据，插入的数据要符合列的约束条件，例如，不可以向非空的列插入NULL值。也可参考本书实习0中的数据样本表，本书后面内容中用到的数据就是实习0中的样本数据。如图3.10所示是插入数据后的XSB表。,图3.10 向表中插入记录,3.4.2 删除记录,当表中的某些记录不再需要时，要将其删除。在“对象资源管理器”中删除记录的方法是：在表数据窗口中定位需被删除的记录行，单击该行最前面的黑色箭头处选择全行，右击鼠标，选择“删除”菜单项，如图3.11所示。,图3.11 删除记录,3.5 命令方式操作表数据,3.5.1 插入记录-INSERT语句