第04章 创建和维护表

4. 创建和维护表

本章主题

l        表的结构

l        创建表

l        全局唯一标识符与自动编号字段

l        活用用户定义数据类型

l        创建计算字段

l        快速查看、添加、修改与删除数据记录

l        更改表的结构

l        更改表的所有者

l        删除表

l        用户表VS临时表VS系统表

 

4.1. 表的结构

表的两大组成部分：

l        结构（Structure）

l        数据记录（Record）

 

表的结构定义工作决定表将拥有哪些字段及字段的特性。

 

字段特性：字段的名称、数据类型、长度、精度、小数位数、是否允许NULL值等。

 

字段名

Ø        字段名最长128字符

Ø        可包含中文、英文、数字、下划线（_）、＃、$、＠

Ø        用一表中，字段名不可重复

 

 

长度、精度和小数位数

Ø        对字符串与Unicode数据类型，长度代表字段所能容纳字符的数目

Ø        对数值类数据类型，长度代表字段使用多少字节来存放数字

Ø        对binary、varbinary、image数据类型，长度代表字段所能容纳的字节数

 

精度：指数中数字的位数（包括小数点左侧的整数部分和小数点右侧的小数部分）

 

举例：

格式	示例	说明
binary(n)	binary(10)	表示长度为10的binary数据类型
char(n)	char(12)	表示长度为12的char数据类型
numeric(p[,s])	numeric(8,3)	表示精度为8，小数位数为3的numeric数据类型

 

数据类型

 

是否允许NULL值

NULL不等于数值0、空白、长度为零的字符串

 

Ø        如果字段设定为不能接受NULL值，用户务必在字段中输入数据，否则SQL Server将不允许存储这条记录。

 

4.2. 创建表

Ø        SQL Server2000限制每个数据库最多可以拥有2,147,483,647个对象。

Ø        每个表最多拥有1024个字段

Ø        每一条记录的最大数据量是8060字节，但text、ntext、image三种数据类型不计算在内。

 

使用企业管理器创建表

Ø        技巧：插入字段、删除字段可用右键快捷菜单。

Ø        表属性：点击工具栏中“属性”按钮

Ø        表名

Ø        所有者

Ø        表文件组

Ø        文本文件组：存放text、ntext、image类型字段内容

Ø        表描述

 

 

使用CREATE TABLE命令创建表

CREATE TABLE
    [ database_name.[ owner ] .| owner.] table_name
    ( column_name datatype [NULL | NOT NULL] [,…n])
[ ON  filegroup ]
[ TEXTIMAGE_ON  filegroup ]

 

（随着讲解，我会适当时机讲解CREATE TABLE其它参数）

 

[ database_name.[ owner ] .| owner.] table_name

Ø        database_name：数据库名称，如果未指定数据库名，则将表创建于当前活动的数据库中。

Ø        Owner：所有者名称，如未指定，创建表的用户会成为表的所有者。

Ø        table_name：表名称

 

CREATE TABLE demoDB.alex.customer(name varchar(12))

 

或

 

USE demoDB

CREATE TABLE alex.customer(name varchar(12))

 

USE demoDB

CREATE TABLE customer(name varchar(12)  --未指定所有者

 

 

Ø        （更精确的讲法）在一个数据库中，同一位所有者的表名不能重复

 

( column_name datatype [,…n])

Ø        该参数定义字段

Ø        字段定义必须包含在一对小括号中

CREATE TABLE mytable

(

  uid char(10),

name varchar(16),

height decimal(5,2)

)

 

[NULL | NOT NULL]

Ø        用于设定是否允许为NULL

 

ON  filegroup

Ø        指定表存放在哪个文件组中，否则存放在默认文件组中（即主文件组Primary）。

 

TEXTIMAGE_ON  filegroup

Ø        指定text、ntext、image字段的数据存放在哪个文件组中，否则存放在默认文件组中（即主文件组Primary）。

 

/* 档案名称: Demo51.sql */

 

CREATE TABLE MyFriends

(

Name varchar(16) NOT NULL,

HomePhone char(11) NOT NULL,

OfficePhone char(11) NULL,

Extension char(4) NULL,

CellularPhone int NULL,

Pager int NULL,

Address varchar(60) NULL,

Birthday datetime NULL,

BloodType varchar(4) NULL,

Height decimal(5, 2) NULL,

Weight decimal(5, 2) NULL,

Income money NULL,

Photo image NULL,

Notes text NULL

)

 

4.3. 全局唯一标识符与自动编号字段

uniqueidentifier字段

Ø        全局唯一标识符（GUID）：16字节的二进制值，全世界没有任何一台计算机所产生的GUID是重复的。

Ø        GUID最重要的用途：是在一个由许多计算机所组成的网络中，确保所指派的标识符是独一的。

Ø        GUID取自网卡和CPU时钟的唯一数值

 

产生GUID的方法：

Ø        调用NEWID函数返回一个GUID（取自服务器的网卡和CPU时钟）

Ø        通过调用API函数返回（取自客户端计算机的网卡和CPU时钟）

 

 

/* 档案名称: Demo52.sql */

CREATE TABLE ComputerManager

(

ComputerID uniqueidentifier DEFAULT NEWID(),

ComputerName varchar(16)

)

 

/* 档案名称: Demo53.sql */

CREATE TABLE ComputerManager

(

ComputerID uniqueidentifier,

ComputerName varchar(16)

)

INSERT ComputerManager VALUES (NEWID(), 'MainSite1')

INSERT ComputerManager VALUES (NEWID(), 'MainSite2')

 

l        一个表可以拥有多个uniqueidentifier字段，但只能有一个字段是ROWGUID字段，即该字段的内容是不能重复的。但是设置为ROWGUID不能强制该字段内容是不能重复的。必须借助PRIMARY KEY或UNIQUE约束（以后会讲解）。

l        设置uniqueidentifier字段为ROWGUID的方法是将“是RowGuid”选项设置成“是”。

 

 

/* 档案名称: Demo54.sql */

CREATE TABLE ComputerManager

(

ComputerID uniqueidentifier ROWGUIDCOL,

ComputerName varchar(16)

)

 

/* 档案名称: Demo55.sql */

CREATE TABLE ComputerManager

(

ComputerID uniqueidentifier

DEFAULT newid() ROWGUIDCOL CONSTRAINT ComputerID UNIQUE,

ComputerName varchar(16)

)

 

 

自动编号字段

设置方法：

1.        设置字段数据类型为tinyint、smallint、int、bigint、decimal(p,0)或numeric(p,0)

2.        设置为“标识”

 

 

 

CREATE TABLE person

(

  PersonID int IDENTITY NOT NULL,

  Name varchar(16) NULL

)

 

CREATE TABLE customer

(

  CustomerID int IDENTITY (100,5) NOT NULL,  ---种子和增量

  CompanyName varchar(20) NULL

)

 

l        一个表最多只能有一个自动编号字段

l        自动编号字段内容不允许修改

l        自动编号字段不允许接受NULL

l        函数@@IDENTITY可取得最近一次添加到自动编号字段的数值

 

INSERT INTO customer(companyName) VALUES(‘温一军工作室’)

SELECT @@IDENTITY as 最后一个公司名称

 

 

4.4. 活用用户定义数据类型

Ø        用户定义数据型是数据库级别的，可以被该数据库中所有表使用。

 

创建用户定义数据类型

 

 

 

/* 档案名称: Demo57.sql */

USEDemoDb

EXEC sp_addtype Scaletype, 'decimal(5,2)', 'NULL'

EXEC sp_addtype Birthday, datetime, 'NULL'

EXEC sp_addtype Telephone, 'varchar(24)', 'NOT NULL'

EXEC sp_addtype Fax, 'varchar(24)', 'NULL'

EXEC sp_addtype Salary, money, 'NOT NULL'

 

 

如何选取用户定义数据类型

/* 档案名称: Demo58.sql */

/* 使资料库 DemoDb 成为活动资料库 */

USE DemoDb

/* 建立各个用户定义数据类型 */

EXEC sp_addtype NameType, 'varchar(16)', 'NOT NULL'

EXEC sp_addtype Birthday, datetime, 'NOT NULL'

EXEC sp_addtype ScaleType, 'decimal(5,2)', 'NULL'

EXEC sp_addtype SalaryType, money, 'NOT NULL'

EXEC sp_addtype ListType, 'char(4)', 'NOT NULL'

 

GO

 

/* 建立资料表 */

CREATE TABLE MyTable

(

Id int IDENTITY(1000,1) NOT NULL,

Name NameType,

Address varchar(60) NULL,

Height ScaleType,

Weight ScaleType,

Salary SalaryType,

Department ListType,

Photo image NULL

)

 

 

删除用户定义数据类型

l        在删除前，必须先确认是否已被表、规则或默认值引用。

l        显示相关性

 

USE DemoDb

EXEC sp_droptype ‘NameType’

 

创建计算字段

 

/* 档案名称: Demo59.sql */

CREATE TABLE MyTable

(

ProductName varchar(20) NOT NULL,

UnitPrice money NOT NULL,

Discount decimal(3,2) NOT NULL,

Quantity int NOT NULL,

Total AS UnitPrice * Discount * Quantity

)

 

 

 

 

4.5. 快速查看、添加、修改与删除数据记录

Ø        讲解使用企业管理器实现上述功能

 

4.6. 更改表的结构

 

使用企业管理器更改表的结构

 

 

使用ALTER TABLE命令更改表的结构

Ø        更改现有字段的特性

/* 档案名称: Demo510.sql */

USE DemoDb

CREATE TABLE MyTable

(

OrderID uniqueidentifier NOT NULL DEFAULT newid(),

SerialNo int IDENTITY(1, 1) NOT NULL ,

ProductID varchar(6) NULL ,

ShipDate datetime NULL ,

UnitPrice smallmoney NULL ,

Discount decimal(2, 2) NULL ,

Quantity smallint NULL

)

 

/* 档案名称: Demo511.sql */

/* 使用ADD ROWGUIDCOL引数将OrderID栏位设定成ROWGUID栏位 */

ALTER TABLE MyTable

   ALTER COLUMN OrderID ADD ROWGUIDCOL

ALTER TABLE MyTable

   ALTER COLUMN SerialNo int

 

ALTER TABLE MyTable

   ALTER COLUMN ProductID char(10) NOT NULL

 

ALTER TABLE MyTable

   ALTER COLUMN ShipDate smalldatetime NOT NULL

 

ALTER TABLE MyTable

   ALTER COLUMN UnitPrice money NOT NULL

 

ALTER TABLE MyTable

   ALTER COLUMN Discount decimal(3,2) NOT NULL

 

ALTER TABLE MyTable

   ALTER COLUMN Quantity int NOT NULL

 

/* 档案名称: Demo512.sql */

ALTER TABLE MyTable

   ALTER COLUMN OrderID DROP ROWGUIDCOL

 

Ø        向表中添加新字段

/* 档案名称: Demo513.sql */

USE DemoDb

CREATE TABLE DemoTable

(

ProductID char(20) NOT NULL

)

 

/* 档案名称: Demo514.sql */

ALTER TABLE DemoTable

  ADD SerialNo int IDENTITY(1,5) NOT NULL,

      OrderID uniqueidentifier NOT NULL

              DEFAULT NEWID() ROWGUIDCOL,

      ShipDate datetime NOT NULL DEFAULT GETDATE(),

      UnitPrice money NOT NULL DEFAULT 1,

      Discount decimal(3,2) NOT NULL DEFAULT 1,

      Quantity int NOT NULL DEFAULT 0,

      Total AS UnitPrice * Discount * Quantity

 

Ø        删除现有的字段

 

/* 档案名称: Demo515.sql */

ALTER TABLE DemoTable

   DROP COLUMN ProductID, SerialNo, Total

 

 

4.7. 删除表

使用企业管理器删除表

 

使用DROP TABLE命令删除表

/* 档案名称: Demo522.sql */

USE Trade

DROP TABLE alex.Members, sandra.Hobby

 

 

4.8. 用户表VS临时表VS系统表

临时表

l        本地临时表：创建表时，以＃作为名称的第一个字符，则该表成为存在tempdb数据库中的本地临时表。本地临时表只有创建它的联机用户能够访问它，而且一旦这位用户断开与SQL Server的连接，本地临时表会自动删除。

l        全局临时表：创建表时，表名以＃＃开头，则此表成为一个存在tempdb数据误置的全局临时表。一旦创建了全局临时表，连接到SQL Server的任何用户都能够访问它。一旦创建它的用户断开与SQL Server的连接，SQL Server会检查是否有其他用户正在使用此全局临时表，如果没有则立即删除该全局临时表，如果有则等待处理完毕，但此时不允许其它任何用户再去访问全局临时表。

 

特别提醒：在SQL Server2000中应尽可能使用table数据类型的变量来取代临时表。

 

系统表

系统表是SQL Server内建的表，存放系统使用的相关信息。

 

 

 

本章结束