理解T-SQL：数据库&表的创建、修改、删除

这一节主要介绍使用SQL语言来进行数据库，及表的创建、修改和删除功能。使用SQL语言比使用图形界面会带来更快的效率，另外，学习好T-SQL对于写相应的脚本、存储过程都带来很大的便利。
      本节讨论了以下几个方面: 创建和修改数据库,创建和修改表,主键/外键/UNIQUE/CHECK/DEFAULT约束, 创建和修改规则和默认值.

1.创建数据库
       创建数据库，默认情况下，一条命令就可以了:Create Database dbName;
       但可以指定更多的选项来创建数据库，其命令语法格式如下：

CREATE DATABASE <database name>
[ON [PRIMARY]
([NAME = <’logical file name’>,]
FILENAME = <’file name’>
[, SIZE = <size in kilobytes, megabytes, gigabytes, or terrabytes>]
[, MAXSIZE = size in kilobytes, megabytes, gigabytes, or terrabytes>]
[, FILEGROWTH = <kilobytes, megabytes, gigabytes, or terrabytes|percentage>])]
[LOG ON
([NAME = <’logical file name’>,]
FILENAME = <’file name’>
[, SIZE = <size in kilobytes, megabytes, gigabytes, or terrabytes>]
[, MAXSIZE = size in kilobytes, megabytes, gigabytes, or terrabytes>]
[, FILEGROWTH = <kilobytes, megabytes, gigabytes, or terrabytes|percentage>])]
[ COLLATE <collation name> ]
[ FOR ATTACH [WITH <service broker>]| FOR ATTACH_REBUILD_LOG| WITH DB_CHAINING {
ON|OFF } | TRUSTWORTHY { ON|OFF }]
[AS SNAPSHOT OF <source database name>]
[;]

      以上，需要介绍的是SIZE表示是创建时数据库大小，MAXSIZE是数据库最大大小，FILEGROWTH是每次增长时的大小。默认以MB为单位。可以使用KB，GB，TB等单位。
      展示一个例子：

CREATE DATABASE Accounting
ON
    (NAME = ‘Accounting’,
     FILENAME = ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\AccountingData.mdf’,
     SIZE = 10,
     MAXSIZE = 50,
     FILEGROWTH = 5)
     LOG ON
     ( NAME = ‘AccountingLog’,
       FILENAME = ‘c:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\AccountingLog.ldf’,
       SIZE = 5MB,
       MAXSIZE = 25MB,
       FILEGROWTH = 5MB)
GO

     查看数据库的信息，使用以下语句执行：
exec sp_helpdb ‘Accounting’

 

2.创建表
       创建表，其命令语法格式如下：

CREATE TABLE [database_name.[owner].]table_name
(
   <column name> <data type>
   [[DEFAULT <constant expression>] | [IDENTITY [(seed, increment) [NOT FOR REPLICATION]]]]
   [ROWGUIDCOL]
   [COLLATE <collation name>]
   {[NULL|NOT NULL] | [ PRIMARY KEY | UNIQUE ]}
   [FOREIGN KEY ( column [ ,n ] ) REFERENCES ref_table [ ( ref_column [ ,n ] ) ]
   [<column constraints>] | [column_name AS computed_column_expression] | [<table_constraint>]
   [,n]
)
[ON {<filegroup>|DEFAULT}]
[TEXTIMAGE_ON {<filegroup>|DEFAULT}]

 

      一般的，创建表需要指定列名，指定列名的情况下，必须指定其类型。至于支持的数据类型，可以在联机从书中搜索“数据类型”即可得到。
      还可以制定键和约束，包括DEFAULT,INDENTITY,UNIQUE,FOREIGN KEY...
      [<column constraints>]选项表示列约束，即放置在列上的限制和规则，决定什么数据能插入到列中，例如，包含月份的字段，只允许插入1-12之间的数据
      [column_name AS computed_column_expression] 表示计算列，即列没有自己的数据，而是根据其它列推导出来的。详细语法：<列名> as <计算表达式>，例如:ExtendedPrice as Price*Quantity
      [<table_constraint>]表示表约束，即对什么数据能够插入到表中进行限制，与列约束不同的是表约束可以基于一个以上的列。
      [ON]指定创建表时把表放置在哪个文件组上。
      [TEXTIMAGE_ON]把表非常特殊的部分移到另一个不同的文件组上。当表定义中有text,ntext,image列时，子句才可用。
      一个例子：

USE Accounting
CREATE TABLE Customers
(
    CustomerNo int IDENTITY NOT NULL,
    CustomerName varchar(30) NOT NULL,
    Address1 varchar(30) NOT NULL,
    Address2 varchar(30) NOT NULL,
    City varchar(20) NOT NULL,
    State char(2) NOT NULL,
    Zip varchar(10) NOT NULL,
    Phone char(15) NOT NULL
)

     查看表的信息，使用以下语句执行：
exec sp_help Customers

 

3. 修改数据库
     语法格式如下：

ALTER DATABASE <database name>
ADD FILE
(
   [NAME = <’logical file name’>,]
   FILENAME = <’file name’>
   [, SIZE = <size in KB, MB, GB or TB>]
   [, MAXSIZE = < size in KB, MB, GB or TB >]
   [, FILEGROWTH = <No of KB, MB, GB or TB |percentage>]) [,n]
   [ TO FILEGROUP filegroup_name]
   [, OFFLINE ]
   |ADD LOG FILE
      ([NAME = <’logical file name’>,]
      FILENAME = <’file name’>
      [, SIZE = < size in KB, MB, GB or TB >]
      [, MAXSIZE = < size in KB, MB, GB or TB >]
      [, FILEGROWTH = <No KB, MB, GB or TB |percentage>]
)
|REMOVE FILE <logical file name> [WITH DELETE]
|ADD FILEGROUP <filegroup name>
|REMOVE FILEGROUP <filegroup name>
|MODIFY FILE <filespec>
|MODIFY NAME = <new dbname>
|MODIFY FILEGROUP <filegroup name> {<filegroup property>|NAME =<new filegroup name>}
|SET <optionspec> [,n ][WITH <termination>]
|COLLATE <collation name>

     具体不多讲了，查询联机从书就可以
     还是举个例子来得更直白些：

ALTER DATABASE Accounting
   MODIFY FILE
     (NAME = Accounting, SIZE = 100MB)

 

 

4. 修改表
    修改表是经常遇到的，所以语法还是要掌握的，语法也比较复杂：

ALTER TABLE table_name
{
  [ALTER COLUMN <column_name>     --【修改列属性】
        { [<新数据类型模式>].<新数据类型> [(精度[, 小数位数])] max | <xml 模式集合>
        [COLLATE <顺序名>]  --校对名，排序时用到
        [NULL|NOT NULL] | [{ADD|DROP} ROWGUIDCOL] | PERSISTED}]
  |ADD                                              --【增加列】
        <column name> <data_type>  
        [[DEFAULT <常量表达式>]              --默认值
        |[IDENTITY [(<种子>, <增量>) [NOT FOR REPLICATION]]]] 
        [ROWGUIDCOL]                         --GUID列
        [COLLATE <collation_name>]
        [NULL|NOT NULL]
        [<列约束>]
        |[<列名> AS <计算列表达式>]         --计算列
   |ADD                                             --【增加约束】
        [CONSTRAINT <约束名>]
        { [   {PRIMARY KEY|UNIQUE}                 --关键字，UNIQUE约束
               [CLUSTERED|NONCLUSTERED]        --聚集索引，非聚集索引
               {(<列名>[ ,n ])}                          --以上几个约束在哪些列名上面
               [WITH FILLFACTOR = <填充因子>]
               [ON {<文件组> | DEFAULT}]
           ]
           |FOREIGN KEY                                      --外键约束                       
               [(<column_name>[ ,n])]                 --外键约束在哪些列上面
               REFERENCES <引用表> [(<引用字段>[ ,n])]   --外键约束的引用表和字段
               [ON DELETE {CASCADE|NO ACTION}]            --当引用字段被删除时，本表外键字段所做的动作
               [ON UPDATE {CASCADE|NO ACTION}]            --当引用字段被更新时，本表外键字段所做的动作
               [NOT FOR REPLICATION]
          |DEFAULT <常量表达式>                            --Default约束
               [FOR <column_name>]                        --Default约束在哪个列上面
          |CHECK [NOT FOR REPLICATION]               --Check约束
               (<搜索条件>)
           [,n][ ,n]                                            --可以增加更多约束
               |[WITH CHECK|WITH NOCHECK]
   | { ENABLE | DISABLE } TRIGGER   { ALL | <触发器名> [ ,n ] }
   |DROP                                             --【删除列或约束】
      {  [CONSTRAINT] <约束名>               -- 删除某个约束
          |COLUMN <column_name>}[ ,n] -- 删除某列
          |{CHECK|NOCHECK} CONSTRAINT {ALL|<约束名>[ ,n]}
          |{ENABLE|DISABLE} TRIGGER  {ALL|<trigger_name>[ ,n]}
          | SWITCH [ PARTITION <source partition number expression> ] TO [ schema_name. ] target_table
                  [ PARTITION <target partition number expression> ]
}

    以上，修改表代码量很多，支持的功能也多，但无外于几点：
      第一功能是修改列的相应属性，使用alter column columnName,
      第二功能是增加列,使用add column newColumnName;
      第三功能是增加约束，使用add constraint newConstraint;
      第四功能是删除列，使用 drop column columnName;
      第五功能是删除约束，使用 drop column constraint;
    以下，举个简单的例子，更多的情况留到约束时再展现：

ALTER TABLE Customers
   ADD Contact varchar(25) NULL,
           LastRaiseDate datetime NOT NULL  DEFAULT ‘2005-11-07’

 

 

5. 删除数据库和表
    非常简单，使用Drop DatabaseName / TableName; 即可。

 

6. 关于约束
    约束的方式有很多种，但每一种都属于三个类型的约束之一：实体(entity)、域(domain,)、引用完整约束(referential integrity constraints),如图所示：

    ● 域约束涉及一个或多个列，确保某一个列或一组列满足特定的标准。当插入或更新一个行时将应用该约束。
       处理CHECK约束、规则(rule)、默认值(default)约束时，属于这种类型
    ● 实体约束是关于单独行的，这种类型的约束感兴趣的是单个的行，最具代表性的例子是要求所有的行的某个列或列的组合具有唯一性约束
       处理PRIMARY KEY、UNIQUE约束时，属于这种类型
    ● 引用完整性约束是一个列中的值必须与另一个列(可以同一个表，或者不同的表)中的值相匹配。
       外键FOREIGN KEY约束是属于这种类型

 

7. 主键和外键约束
    ● 创建表时创建主键：

CREATE TABLE Customers
(
    CustomerNo int IDENTITY NOT NULL PRIMARY KEY,
    CustomerName varchar(30) NOT NULL,
    Address1 varchar(30) NOT NULL,
    City varchar(20) NOT NULL,
    State char(2) NOT NULL,
)

    ● 在现有表上创建主键

USE Accounting
ALTER TABLE Employees ADD CONSTRAINT PK_EmployeeID
        PRIMARY KEY (EmployeeID)

    ● 创建表时创建外键：

USE Accounting
CREATE TABLE Orders
(
    OrderID int IDENTITY NOT NULL PRIMARY KEY,
    CustomerNo int NOT NULL  FOREIGN KEY REFERENCES Customers(CustomerNo),
    OrderDate smalldatetime NOT NULL,
    EmployeeID int NOT NULL
)

    ● 在现有表上创建外键

ALTER TABLE Orders
    ADD CONSTRAINT FK_EmployeeCreatesOrder
          FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)      --外键引用外部的表字段
ALTER TABLE Employees
    ADD CONSTRAINT FK_EmployeeHasManager
          FOREIGN KEY (ManagerEmpID) REFERENCES Employees(EmployeeID)  --外键引用内部表字段

    ● 外键定义中的[ON UPDATE{CASCADE|NO ACTION}] 和[ON DELETE {CASCADE|NO ACTION}] 选项
        CASCADE/NO ACTION定义了两个不同的引用完整性操作，默认的情况下，NO ACTION是默认选项。
        CASCADE选项是表示级联操作，如果删除/更新了父级的记录，相应的子级的记录行也会被删除/更新。且CASCADE操作能够影响的深度没有限制。
    ● 查看表中的主键和外键约束
      exec sp_helpconstraint Orders

 

8.UNIQUE约束
    UNIQUE约束要求表中的列（或列的组合）具有唯一性。UNIQUE约束常被称为备用键。一个表中可以有不止一个UNIQUE约束，但主键约束只能有一个。
    ● 创建UNIQUE约束：

CREATE TABLE Shippers
(
    ShipperID int IDENTITY NOT NULL
    PRIMARY KEY,
    ShipperName varchar(30) NOT NULL,
    State char(2) NOT NULL,
    Zip varchar(10) NOT NULL,
    PhoneNo varchar(14) NOT NULL UNIQUE
)

    ● 现有表中创建UNIQUE约束：

ALTER TABLE Employees
   ADD CONSTRAINT AK_EmployeeSSN  UNIQUE (SSN)

 

9. CHECK约束
     CHECK约束最妙的地方是它们不局限于一个特定的列。它可以与一个列有关。可以基于同一个表中的另一个列来检查某个列。也可以检查列值的组合是否满足某个判断标准。
    例如，以下的例子是使用CHECK约束的时机：
限制MONTH列为适宜的值         BETWEEN 1 AND 12
正确的SSN格式                      LIKE ‘[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]’
限制在特定的shippers列表中     IN (‘UPS’, ‘Fed Ex’, ‘USPS’)
价格必须为正数                      UnitPrice >= 0
参考同一行中的另一个列           ShipDate >= OrderDate
      ● 在现有表中加入CHECK约束

ALTER TABLE Customers
    ADD CONSTRAINT CN_CustomerDateInSystem CHECK
           (DateInSystem <= GETDATE ())   --保证DateInSystem字段不大于当天日期。

 

10. DEFAULT约束
     DEFAULT用于处理“默认值”，如果插入的新行在定义默认值的约束上没有给出值，将以约束的定义来进行处理。可以将默认值定义为常量值(如0,’UNKOWN’),也可以是一些系统值(如GETDATE())
     ● 在创建表中加入DEFAULT约束

CREATE TABLE Shippers
(
    ShipperID int IDENTITY NOT NULL
    PRIMARY KEY,
    ShipperName varchar(30) NOT NULL,
    DateInSystem smalldatetime NOT NULL
    DEFAULT GETDATE ()
)

     ● 在现有表中加入DEFAULT约束

ALTER TABLE Customers
     ADD CONSTRAINT CN_CustomerAddress DEFAULT ‘UNKNOWN’ FOR Address1

 

 

11. 禁用约束
     禁用不是删除约束，约束还是在表中存在的，只不过是对于值不进行约束检查，禁用可以是暂时的，也可以是永久的，视情况而定。
     另外，不能禁用PRIMARY KEY和UNIQUE约束
     ● 禁用约束的例子：
        首先，创建一个CHECK约束：

ALTER TABLE Customers
   ADD CONSTRAINT CN_CustomerPhoneNo  CHECK
     (Phone LIKE ‘([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]’)

        然后，可以禁用该约束：

ALTER TABLE Customers
   WITH NOCHECK
   ADD CONSTRAINT CN_CustomerPhoneNo  CHECK
      (Phone LIKE ‘([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]’)

        或者，更简单的方法：

ALTER TABLE Customers NOCHECK  CONSTRAINT CN_CustomerPhoneNo

        再恢复约束

ALTER TABLE Customers CHECK CONSTRAINT CN_CustomerPhoneNo

 

 

12. 创建规则(rule)和默认值(default)
     规则不同于约束，约束是在表中定义的，而规则和默认值是独立定义的，定义完后，再“绑定”到表上。
     ● 规则：与CHECK约束相似。但不同的是规则局限在一次只能作于于一个列上，但可以把一个规则多次绑定到多个列上。
                 另外，规则只对每一个单独列起作用，不会意识到有其它列，所以定义(Column1>Column2)这样子的约束不能做为规则来实现。
     ● 定义规则和查看规则

CREATE RULE SalaryRule AS @Salary > 0;
exec sp_helptext SalaryRule;

     ● 绑定规则与反绑定
        语法定义 ：sp_bindrule <’rule’>, <’object_name’>, [<’futureonly_flag’>]

EXEC sp_bindrule ‘SalaryRule’, ‘Employees.Salary’  
EXEC sp_unbindrule ‘Employees.Salary’

     ● 删除规则

DROP RULE ‘SalaryRule’

      ● 默认值：与DEFAULT约束相似，不同地方是：隶属于表的方式不同，并且，默认值支持用户定义数据类型

       ● 定义规则和查看默认值

CREATE DEFAULT SalaryDefault AS 0
exec sp_helptext SalaryDefault ;

      ● 绑定规则与反绑定
         语法定义 ：sp_bindrule <’rule’>, <’object_name’>, [<’futureonly_flag’>]

EXEC sp_bindefault ‘SalaryDefault’, ‘Employees.Salary’ 
EXEC sp_unbindefault ‘Employees.Salary’

      ● 删除规则

DROP DEFAULT SalaryDefault

      ● 如果想要删除或修改规则或默认值,首先可能要知道谁在使用这些规则,SQL Server提供了一个系统存储过程来解决这一问题:

EXEC sp_depends <对象名>

         这样就可以在删除或更新前做到不会影响到其它表或数据.