本章要点：

本章要点： 第4章数据库中表的基本操作 • 了解表的结构特点； • 了解SQL server的数据类型； • 学会在企业管理器中创建表； • 学会使用T-SQL语句创建表； • 学会使用两种方法对表进行插入、查看操作。

4.1 SQL server表的概述 4.1.1 SQL server表的概念 1、表的概念表是相关联的行列集合。用来存储数据库中的所有数据。每一列称为一个字段，每一行称为一条记录。设计数据库时，要根据数据库逻辑结构设计的要求，确定需要什么样的表，各表中都有哪些数据，所包含的数据类型。 2、SQL server表与关系模型的对应(略)

3、表的设计 表是包含数据库中所有数据的数据库对象，用来存储各种各样的信息。在SQL Server 2005中，一个数据库中最多可以创建20亿个表，用户创建数据库表时，最多可以定义1024列。在同一数据库的不同表中，可以有相同的字段，但在同一个表中不允许有相同的字段，而且每个字段都要求数据类型相同。

表 4-1 关系模型数据库结构 关系名属性（列）学生表关系主键（码）

4.1.2 SQL server2000数据类型 建表之前需要对数据类型有所了解。Sql server 提供了26种数据类型，常用数据类型分类如下： • 1.整型数据类型 • 2.浮点数据类型 • 3.字符数据类型 • 4.日期、时间数据类型 • 5.文本、图形数据类型 • 6.货币数据类型 • 7.位数据类型 • 8.二进制数据类型 • 9.特殊数据类型 • 10.新增数据类型

1. 整型数据类型 整型数据类型是最常用的数据类型之一，它主要用来存储数值，可以直接进行数据运算，而不必使用函数转换。 • int（integer）：int（或integer）数据类型可以存储从-231(-2147483648)到231-1 (2147483647)范围之间的所有正负整数，占用 4个字节的存储空间。 • Smallint：可以存储从-215(-32768)到215-1 (32767)范围之间的所有正负整数，占用 2个字节的存储空间。 • Tinyint：可以存储从0到255范围之间的所有正整数，占用 1个字节的存储空间。

2. 浮点数据类型 (近似数值类型) 浮点数据类型用于存储十进制小数。浮点数值的数据在SQL Server中采用只入不舍的方式进行存储。 (1)近似数值型（用于浮点数） Real：可以存储正的或者负的十进制数值,最大有7位精确位数，存储大小为4字节。 Float：可以精确到第15位小数，其范围从- 1.79E-308到1.79E+308。

(2)精确小数型(用于小数点后位数确定的实 数）Decimal和numeric：Decimal数据类型和 numeric数据类型完全相同，它们可以提供小数所需要的实际存储空间，但也有一定的限制，可以用2到17个字节来存储从-1038-1到 1038-1之间的数值。

注意： (1) 近似数值型数据不能确定所输出的数值精确度。 (2) 精确数值类型的总位数不包括小数点。例如：decimal（10，5），表示共有 10位数，其中整数5位，小数5位。

3. 字符数据类型 字符数据类型可以用来存储汉字、字母、数字、标点和各种符号，输入时必须由英文单引号括起来。 • Char：按固定长度存储字符串，定义形式为char(n)， n必须从1-8000，若输入的数据不足n个字节，补足后存储。若输入的数据超过n个字节，则截断后存储。每个字符和符号占用一个字节的存储空间。 • Varchar：按变长存储字符串，其定义形式为varchar （n）， n必须介于1和8000之间，存储大小为实际长度，若输入的数据超过n个字节，则截断后存储。所输入的数据长度可为零。用char类型的字符串查询速度快，当有空值或存储长度不固定时可使用Varchar 数据类型。

4.Unicode字符数据 Unicode标准为全球商业领域中广泛使用的大部分字符定义了一个单一编码方案。 • Nchar（定长）：其定义形式为nchar（n）， n必须介于1和4000之间。 • Nvarchar（变长):其定义形式为nvarchar（n）,n必须介于1和4000之间。注意：（1）该两种数据类型每一个字符占用2个字节，用来处理来自不同字符集的字符。（2） Unicode常量使用N开头来指定：N’A Unicode string’ 。

5. 日期和时间数据类型 Datetime：用于存储日期和时间数据。它可以存储从公元1753年1月1日零时起到公元9999年12月31日23 时59分59秒之间的所有日期和时间。 • Smalldatetime：与datetime数据类型类似，但其日期时间范围较小，它存储从1900年1月1日到2079年 6月6日内的日期。注意：输入日期时间型数据时，需要用单引号括起来。默认的日期格式为（mdy），也可以通过使用SET DATEFORMAT语句改变日期的格式。默认宽度8个字节,(输入时不能加宽度)

6. 文本和图形数据类型 • Text：用于存储大量文本数据，其容量理论上为1到231-1（2147483647）个字节，但实际应用时要根据硬盘的存储空间而定。 • Ntext：与text数据类型类似，存储在其中的数据通常是直接能输出到显示设备上的字符，显示设备可以是显示器、窗口或者打印机。 • Image：用于存储照片、目录图片或者图画，其理论容量为231-1（2147483647）个字节。

文本类型 • Text：容量可以在1-231-1个字节。当需要在一个字段中存储容量超过8KB的大量文本数据时，可以选择该类型。在定义Text数据类型时，不需要指定数据长度，SQL Server会根据数据的长度自动为其分配空间，在Text类型字段中仅存储一个指针。 • Image:可以存储最大长度约为2GB。

7. 货币数据类型 • Money：用于存储货币值，存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，介于 -263 与263-1之间，精度为货币单位的万分之一。 • Smallmoney：与money数据类型类似，但其存储的货币值范围比money数据类型小，存储大小为4个字节。注意：在输入货币数据时须在数据前面加货币符号$, 输入负货币值时在$后面加一个减号。

8. 位数据类型 • Bit：称为位数据类型，其数据有两种取值：0、1或NULL，常用于逻辑数据的存取，长度为1字节。如果表中有9个或16个以下位类型数据字段，则会用两个字节存储。注意：位数据类型相当于逻辑型数据。定义时数据宽度取默认值（一个字节）。

9. 二进制数据类型 • Binary（定长）：其定义形式为binary(n)，数据的存储长度是固定的，即n+4字节，当输入的二进制数据度小于n时，余下部分填充0(即补足)，超过则截取。 • Varbinary（变长）：数据的存储长度是变化的，它为实际所输入数据的长度加上4字节（超过则截断）。其它含义同binary。注：在输入二进制常量时，需在该常量前面加一个前缀0x。

4.2 数据库中表的创建 1.定义表结构：给表的每一列定义以下属性：列名称、数据类型、长度、精度、小数位数、是否允许为空等。 2.设置约束：设置约束是为了限制该列输入值的取值范围，以保证数据的正确性和一致性。 3.添加数据：表结构完成后，即可以向表中输入数据了。注：创建表一般要经过定义表结构、设置约束和添加数据三个步骤。

SQL Server 2005 创建数据库表的两种方法 • 第一种方法是利用企业管理器创建表； • 第二种方法是利用Transact-SQL语句中的create table 命令创建表。

4.2.1 使用企业管理器创建表 1、创建表的步骤在企业管理器中，展开指定的服务器和数据库，打开想要创建新表的数据库，用右键单击表对象，从弹出的快捷菜单中选择“新建表”选项，或者在工具栏中选择图标，就会出现“新建表”对话框，在该对话框中，可以定义列的以下属性：列名称、数据类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。然后根据提示进行设置。

对表设计器中各关键词的解释如下 • 默认值：指定列的默认值。除定义为带 IDENTITY属性的列以外的任何列。删除表时，将删除默认值定义。只有常量值(如字符串)、SQL Server内部函数或NULL值可以用作默认值，如果不允许为空的字段，在插入或修改数据时必须输入数据，否则会出现错误。 • 精度和小数位数：精度是列的总长度，包括整数部分和小数部分的长度之和，但不包括小数点；小数位数指定小数点后面的长度。

利用T-SQL语言创建表结构： create table 表名（ <列定义> [，….n] <列约束> ）注：列名：在同一表中必须是唯一的 <列定义>语法: {列名数据类型| as 列的表达式 [null | not null |default 常量表达式 |identity(初始值，步长)] [primary key|unique]}

<列约束>语法： • 主键 constraint 约束名 primary key • 惟一 constraint 约束名 unique • 外键 constraint 约束名 references 引用表名(引用列) • 检查 constraint 约束名 check (检查表达式) • 默认 constraint 约束名 default 默认值

2、create table 语句的使用 例 4.1:在student数据库中创建“专业”表，表的结构定义如下：表4.1 “专业”表的结构定义

代码如下： Use student Create table dbo.专业 /*表的拥有者*/ (专业代码 char(2) not null, /*不允许空*/ 专业名称 varchar (20) not null, 系部代码 char (2) not null） /*系部代码可作为后面专业表的外键*/

例 4.2: 在student数据库中创建“班级”表，表的结构定义如下：表4.2 “班级”表的结构定义

代码如下： Use student Create table 班级（班级代码 char (9) constraint pk-bjdm primary key, 班级名称 varchar (20) not null, 专业代码 char (2) not null, 专业名称 varchar (20) not null, 系部代码 char (2) not null 备注 varchar (50)） --“班级代码”字段为主键，主键约束名为pk-bjdm.

例 4.3:创建带有参照约束的“学生”表，“学 生”表结构定义如表4.3所示。“学号”字段为 “学生”表的主键，“班级代码”字段为“学生”表的外键，它必须参照班级表中的“班级代码”字段的值。

表4.3 “学生”表的结构定义

代码如下： Use student Create table 学生（学号 char (12) constraint pk-xh primary key, 姓名 char (8) , 性别 char (2), 出生日期 datetime, 入学时间 datetime, 班级代码 char(9) constraint fk_bjdm References 班级(班级代码）, 专业代码 char (2), 系部代码 char (2)）

说明：创建‘学生’表的代码中，班级代码 char(9) constraint fk_bjdm References 班级(班级代码）,的含义是指定‘班级代码 ’为学生表的外键，外键约束名为fk-bjdm,它引用的是‘班级’表中的‘班级代码’字段的值。注意：若在该字段中输入‘班级’表中所没有的‘班级代码’，将破坏该字段的域完整性。

4.3 表的修改与删除 一个表建立后，可以根据使用的需要对它进行修改和删除，修改的内容可以是列的属性，如数据类型、长度等，还可以添加列、删除列等。 4.3.1 修改数据库中的表 1、使用企业管理器修改表 2、使用Transact-SQL语言修改表

1. 利用企业管理器增加、删除和修改字段 (1)在企业管理器中，展开指定的服务器和数据库，用右键单击要修改的“表”，从弹出的快捷菜单中选择“设计表”选项，打开表设计器。 (2)在表设计器中修改各字段的定义，如：列名称、数据类型、长度、小数位数、是否允许为空等。

(3)添加、删除字段。如果增加字段，将光标移 动到最后一个字段的下边，输入内容即可。如果要在某一个字段前插入新字段，用鼠标右键单击该字段，在弹出的快捷菜单中选择“插入列”选项，然后输入内容即可。如果要删除列，用鼠标右键单击该字段，在弹出的快捷菜单中选择“删除列”选项，

2.利用T -SQL语言增加、删除和修改字段 1、对现有字段进行修改： ALTER TABLE 表名 { [ ALTER COLUMN 字段名新数据类型 [（精度 [ , 小数位数] ） ] [ NULL | NOT NULL ]

2、使用ADD子句添加字段： ALTER TABLE 表名 ADD {字段定义|字段名 AS 计算字段表达式}[,…n] 例：ALTER TABLE 成绩 ADD 平均分 AS （语文+数学）/2 ADD 英语 INT NULL

3、使用DROP COLUMN子句删除字段： ALTER TABLE 表名 DROP COLUMN 字段名[,…n] 例： ALTER TABLE 成绩 DROP COLUMN 语文，数学

例4.5:在“学生”表中增加“家庭住址”和 “邮政编码”，数据类型分别为char(30),char(6). 代码如下： Use student Go Alter table 学生 add 家庭住址 char (30) null , 邮政编码 char (6) null go

例4.6:在“学生”表中修改“家庭住址”字段 的属性，使该字段的数据类型为varchar(50), 允许为空。代码如下： Use student Go Alter table 学生 alter column 家庭住址 varchar (50) null go -- alter column 选项只能改变字段的属性

例4.7:删除“学生”表中“系部代码”字段。 代码如下： Use student Go Alter table 学生 Drop column 系部代码 go 注意：在删除列时，必须先删除该列的约束和索引。

例4.8修改“学生”表中的“邮政编码”字段， 将该字段的名称改为“联系方式”。代码如下： Use student Go Exec sp_rename‘学生.邮政编码’,’联系方式’ go /*修改字段名*/ Exec sp_help 学生 /*显示表结构*/ 注：请同学们考虑使用alter Table命令完成上面的操作？

 例4.9由学生完成 创建一个“成绩”表，然后在该表中增加一个计算字段（其值依赖于其它字段，并由系统自动计算，无须手工输入），其名称为“平均成绩”，其值为“实验”和“平时”两个字段的平均值。 • 提示代码： Use student Create table 成绩（学号 char (3),平时 real,实验 real) Go Alter table 成绩 add 平均成绩 as (平时+实验)/2 go

4.3.2 删除数据库中的表 1 、使用企业管理器删除表。 2 、使用Transact-SQL语句中的Drop table 命令删除表。注意：（1）一旦表被删除，将不能被恢复，所以要慎重。（2）如果一个表被其它表通过外键约束引用，那么必须先删除了设置外键约束的表。

1 、使用企业管理器删除表 （1）在企业管理器中，展开指定的服务器和数据库，用右键单击“表”的图标。（2）在明细窗格中右键单击“表名”对象，从弹出的快捷菜单中选择“删除”，在弹出的“除去对象”对话框中单击“全部除去”按钮，确认删除操作。

2 、使用T-SQL中的Drop table 命令删除表 语句的语法格式如下： DROP TABLE 表名[,…n] 说明:(1)用于删除一个或多个表。 (2)若要删除另外一个数据库中包含的表，应当在表名前面冠以数据库和拥有者的名称。

例4-10:删除当前数据库中的“班级”表。 Use student Drop table 班级 go 例4-11:删除master数据库中的goods表。 Drop table master.db0.goods go

4.4 数据的添加与表的查看 1、使用企业管理器向表中添加数据右击表----选打开表----选返回所有行---输入数据或修改和删除已有记录 2、使用查询分析器向表中添加数据插入整条记录 INSERT 表名 VALUES （列属性，列属性….. ）

在XSCJ数据库中建立表XS CREATE TABLE XS ( 学号 char(8) NOT NULL, 姓名 char(8) NOT NULL, 专业 char(10) NULL ) • 向表XS中插入: USE XSCJ INSERT XS VALUES(‘20060101’,’张三’,’计算机’) GO

使用INSERT语句: • 注意事项： 1.对已经存在的值，系统不允许输入重复值 2.对某些不允许为空的字段，不允许输入空值 3.数据类型要和所对应的字段的定义相同 4.values值列表的顺序要与表的字段顺序一致

例4-10:使用INSERT语句向“班级”表中添加新记录。例4-10:使用INSERT语句向“班级”表中添加新记录。代码如下：（方法1） use student go Insert 班级 (班级代码,班级名称,专业代码,系部代码) values (‘03033069’,’计0333班’,‘01’,‘jsj’) --添加数据时，字符型和日期型数据要用单引号括起来。数据个数、类型要相同，对应传送。

本章要点：

本章要点：

Presentation Transcript