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）的完整表定义，其中包含所有的约束定义。
      /* ************************** jobs table ************************** */
      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
      /* ************************* employee table ************************* */
      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())
      )
       /* ***************** publishers table ******************** */
      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