SQL Server 数据库设计和实现（三）

三、更新数据库

（一）格式：具体格式请在“联机丛书”中查找关键字“alter database”进行查询

（二）相关示例：

   1、向数据库中添加文件：下例创建数据库，并更改该数据库以添加一个 5 MB 大小的新数据文件。

      USE master

      GO

      CREATE DATABASE Test1

      ON

      (

       NAME = Test1dat1,

       FILENAME = 'c:/Program Files/Microsoft SQL Server/MSSQL/Data/t1dat1.ndf',

       SIZE = 5MB,

       MAXSIZE = 100MB,

       FILEGROWTH = 5MB

      )

      GO

      ALTER DATABASE Test1

      ADD FILE

      (

       NAME = Test1dat2,

       FILENAME = 'c:/Program Files/Microsoft SQL Server/MSSQL/Data/t1dat2.ndf',

       SIZE = 5MB,

       MAXSIZE = 100MB,

       FILEGROWTH = 5MB

      )

      GO

   2、向数据库中添加由两个文件组成的文件组：

      下例在示例 A 中所创建的 Test1 数据库中创建一个文件组，并将两个 5 MB 的文件添加到该文件组。然后下例将 Test1FG1 设置为默认文件组。

      USE master

      GO

      ALTER DATABASE Test1

      ADD FILEGROUP Test1FG1

      GO

      ALTER DATABASE Test1

      ADD FILE

      (

       NAME = test1dat3,

       FILENAME = 'c:/Program Files/Microsoft SQL Server/MSSQL/Data/t1dat3.ndf',

       SIZE = 5MB,

       MAXSIZE = 100MB,

       FILEGROWTH = 5MB

      ),

      (

       NAME = test1dat4,

       FILENAME = 'c:/Program Files/Microsoft SQL Server/MSSQL/Data/t1dat4.ndf',

       SIZE = 5MB,

       MAXSIZE = 100MB,

       FILEGROWTH = 5MB

      )

      TO FILEGROUP Test1FG1

      ALTER DATABASE Test1

      MODIFY FILEGROUP Test1FG1 DEFAULT

      GO

   3、向数据库中添加两个日志文件：下例向数据库中添加两个 5 MB 大小的日志文件。

      USE master

      GO

      ALTER DATABASE Test1

      ADD LOG FILE

      (

       NAME = test1log2,

       FILENAME = 'c:/Program Files/Microsoft SQL Server/MSSQL/Data/test2log.ldf',

       SIZE = 5MB,

       MAXSIZE = 100MB,

       FILEGROWTH = 5MB

      ),

       (

       NAME = test1log3,

       FILENAME = 'c:/Program Files/Microsoft SQL Server/MSSQL/Data/test3log.ldf',

       SIZE = 5MB,

       MAXSIZE = 100MB,

       FILEGROWTH = 5MB

      )

      GO

   4、从数据库中删除文件：下例将示例 B 中添加到数据库 Test1 中的一个文件删除。

      USE master

      GO

      ALTER DATABASE Test1

      REMOVE FILE test1dat4

      GO

   5、更改文件：下例将示例 B 中添加到数据库 Test1 中的一个文件增加大小。

      USE master

      GO

      ALTER DATABASE Test1

      MODIFY FILE

      (

       NAME = test1dat3,

       SIZE = 20MB

      )

      GO

2.3 使用SQL语句创建和删除表

一、创建表

（一）格式：

      数据表的信息一般存储于各个数据库中的sysobjects表中，在创建表格时，可以同IF EXISTS判断语句一同使用

      CREATE TABLE 表名

      (

       字段1 数据类型 列的特征,

       字段2 数据类型 列的特征,

        ... ...

      )

      go

（二）参数分析：

   1、字段：表格中的字段名称

   2、数据类型：详见SQLSERVER应用开发

   3、列的特征：即对该列中的数据的相关约束，一般包括

      1）非空约束：是否允许列中的数据为null，允许可写为NULL或不写，不允许写为NOT NULL

      2）默认值约束：当用户没有对列输入信息时，用默认值代替，可以写为DEFAULT 默认值，如果字段为字符型，应写为DEFAULT '默认值'

      3）唯一约束：对于该列的数据采用唯一性原则，即没有重复的元素，可以包括null值，但只能有一个，可写为UNIQUE

      4）主键约束：将该列的数据设为主键，和唯一约束一样，不存在重复的元素，但不能包括null，即主键不能为空

      5）外键约束：将从表中列的数据与主表中相关主键的数据相关联，可写为references 主表名(主键列名)

      6）检查约束：对列中的数据进行格式上的限制，可写为CHECK(条件表达式)

      7）标识列：对于列的数据由系统从种子基数开始，按照种子递增量自动增加，可写为IDENTITY(种子基数,种子递增量)

（三）相关示例：

   1、完整的表定义：

      下例显示 pubs 数据库中所创建的三个表（jobs、employee 和 publishers）的完整表定义，其中包含所有的约束定义。

      CREATE TABLE jobs

      (

       job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, -- 将该列定义为自动增长的聚焦索引主键

       job_desc varchar(50) NOT NULL DEFAULT 'New Position - title not formalized yet', -- 将该列定义为非空字段并且有默认值

       min_lvl tinyint NOT NULL CHECK (min_lvl >= 10), -- 限制该列数据只能为只于10的整数

       max_lvl tinyint NOT NULL CHECK (max_lvl <= 250)

      )

      go

      CREATE TABLE employee

      (

       emp_id empid CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED CONSTRAINT CK_emp_id CHECK (emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),

       fname varchar(20) NOT NULL,

       minit char(1) NULL,

       lname varchar(30) NOT NULL,

       job_id smallint NOT NULL DEFAULT 1 REFERENCES jobs(job_id),

       job_lvl tinyint DEFAULT 10,

        pub_id char(4) NOT NULL DEFAULT ('9952') REFERENCES publishers(pub_id), hire_date datetime NOT NULL DEFAULT (getdate())

      )

      CREATE TABLE publishers

      (

       pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756') OR pub_id LIKE '99[0-9][0-9]'),

       pub_name varchar(40) NULL,

        city varchar(20) NULL,

       state char(2) NULL,

       country varchar(30) NULL DEFAULT('USA')

      )

   2、在列中使用 uniqueidentifier 数据类型：

      下例创建含有 uniqueidentifier 列的表。该表使用 PRIMARY KEY 约束以确保用户不会在表中插入重复的值，并在 DEFAULT 约束中使用 NEWID() 函数为新行提供值。

      CREATE TABLE Globally_Unique_Data

      (

       guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWID(),

       Employee_Name varchar(60),

       CONSTRAINT Guid_PK PRIMARY KEY (Guid)

      )

   3、对计算列使用表达式：下例显示如何使用表达式 ((low + high)/2) 计算 myavg 计算列。

      CREATE TABLE mytable

      (

       low int,

       high int,

       myavg AS (low + high)/2

      )

   4、对计算列使用 USER_NAME 函数：下例在 myuser_name 列中使用 USER_NAME 函数。

      CREATE TABLE mylogintable

      (

       date_in datetime,

       user_id int,

       myuser_name AS USER_NAME()

      )

二、删除表

      格式：DROP TABLE 表名

2.4 使用SQL语句创建和删除约束

一、添加约束：

（一）格式：

      ALTER TABLE 表名

      ADD CONSTRAINT 约束名 约束类型 具体的约束说明

（二）相关示例：

   1、添加主键约束：

      为stuInfo表的stuNo字段添加主键约束（PRIMARY KEY），约束名为PK_stuNo

      ALTER TABLE stuInfo

      ADD CONSTRAINT PK_stuNo PRIMARY KEY (stuNo)

   2、添加唯一约束：

      为stuInfo表的stuId字段添加唯一约束（UNIQUE），约束名为UQ_stuId

      ALTER TABLE stuInfo

      ADD CONSTRAINT UQ_stuId UNIQUE (stuId)

   3、添加默认约束：

      ALTER TABLE stuInfo

      ADD CONSTRAINT DF_stuAddress DEFAULT ('地址不详') FOR stuAddress

   4、添加检查约束：年龄在15到40之间

      ALTER TABLE stuInfo

      ADD CONSTRAINT CK_stuAge CHECK (stuAge BETWEEN 15 AND 40)

   5、添加外键约束：将从表stuMarks中的stuNo列定义为外键并与主列中的stuNo列相关联

      ALTER TABLE stuMarks

      ADD CONSTRAINT FK_stuNo FOREIGN KEY (stuNo) REFERENCES stuInfo (stuNo)

二、删除约束：

（一）格式： ALTER TABLE DROP CONSTRAINT 约束名

（二）相关示例：删除名为DF_stuAddress的约束

      ALTER TABLE DROP CONSTRAINT DF_stuAddress

三、扩展：修改表格的方法

（一）格式：参见“联机丛书”，查找关键字“ALTER TABLE”

（二）相关示例：

   1、更改表以添加新列：

      下例添加一个允许空值的列，而且没有通过 DEFAULT 定义提供值。各行的新列中的值将为 NULL。

       CREATE TABLE doc_exa

      (

       column_a INT

      )

      GO

      ALTER TABLE doc_exa

       ADD column_b VARCHAR(20) NULL

      GO

      EXEC sp_help doc_exa

      GO

      DROP TABLE doc_exa

      GO

   2、更改表以除去列：下例修改表以删除一列。

      CREATE TABLE doc_exb

      (

       column_a INT,

       column_b VARCHAR(20) NULL

      )

      GO

      ALTER TABLE doc_exb

       DROP COLUMN column_b

      GO

      EXEC sp_help doc_exb

      GO

      DROP TABLE doc_exb

      GO

3、更改表以添加具有约束的列：下例向表中添加具有 UNIQUE 约束的新列。

      CREATE TABLE doc_exc

      (

       column_a INT

      )

      GO

      ALTER TABLE doc_exc

       ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE

      GO

      EXEC sp_help doc_exc

      GO

      DROP TABLE doc_exc

      GO