--第五章——建立數(shù)據(jù)庫和數(shù)據(jù)庫表
--5.2.3  使用T-SQL命令創(chuàng)建數(shù)據(jù)庫
--使用T-SQL命令創(chuàng)建數(shù)據(jù)庫的語法如下：
CREATE DATABASE database_name 
[ ON 
    [ < filespec > [ ,...n ] ] 
    [ , < filegroup > [ ,...n ] ] 
] 
[ LOG ON { < filespec > [ ,...n ] } ] 
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ] 
< filespec > ::=
[ PRIMARY ]
( [ NAME = logical_file_name , ]
    FILENAME = 'os_file_name'
    [ , SIZE = size ]
    [ , MAXSIZE = { max_size | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment ] ) [ ,...n ]
< filegroup > ::=
FILEGROUP filegroup_name < filespec > [ ,...n ]

--創(chuàng)建簡單的數(shù)據(jù)庫
USE master
GO
CREATE DATABASE Products
ON 
( NAME = prods_dat,
   FILENAME = 'f:\program files\microsoft sql server\mssql\data\prods.mdf',
   SIZE = 4,
   MAXSIZE = 10,
   FILEGROWTH = 1 )
GO

--創(chuàng)建復雜的數(shù)據(jù)庫
USE master
GO
CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
      FILENAME = 'f:\program files\microsoft sql server\mssql\data\archdat1.mdf',
      SIZE = 100MB,
      MAXSIZE = 200,
      FILEGROWTH = 20),
( NAME = Arch2,
   FILENAME = 'f:\program files\microsoft sql server\mssql\data\archdat2.ndf',
   SIZE = 100MB,
   MAXSIZE = 200,
   FILEGROWTH = 20),
( NAME = Arch3,
   FILENAME = 'f:\program files\microsoft sql server\mssql\data\archdat3.ndf',
   SIZE = 100MB,
   MAXSIZE = 200,
   FILEGROWTH = 20)
LOG ON 
( NAME = Archlog1,
   FILENAME = 'f:\program files\microsoft sql server\mssql\data\archlog1.ldf',
   SIZE = 100MB,
   MAXSIZE = 200,
   FILEGROWTH = 20),
( NAME = Archlog2,
   FILENAME = 'f:\program files\microsoft sql server\mssql\data\archlog2.ldf',
   SIZE = 100MB,
   MAXSIZE = 200,
   FILEGROWTH = 20)
GO

--5.3.2  使用 SQL察看數(shù)據(jù)庫
osql
    [-?] |
    [-L] |
    [
        {
            {-U login_id [-P password]}
            | -E
        }
        [-S server_name[\instance_name]] [-H wksta_name] [-d db_name]
        [-l time_out] [-t time_out] [-h headers]
        [-s col_separator] [-w column_width] [-a packet_size]
        [-e] [-I] [-D data_source_name]
        [-c cmd_end] [-q "query"] [-Q "query"]
        [-n] [-m error_level] [-r {0 | 1}]
        [-i input_file] [-o output_file] [-p]
        [-b] [-u] [-R] [-O]
    ]

--5.5.1.4  創(chuàng)建employee表
--下例顯示 pubs 數(shù)據(jù)庫中所創(chuàng)建表employee的完整表定義，其中包含所有的約束定義。
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]'),
      /* Each employee ID consists of three characters that 
      represent the employee's initials, followed by a five 
      digit number ranging from 10000 through 99999 and then the 
      employee's gender (M or F). A (hyphen) - is acceptable 
      for the middle initial. */
   fname   varchar(20)     NOT NULL,
   minit   char(1) NULL,
   lname   varchar(30)     NOT NULL,
   job_id  smallint        NOT NULL
      DEFAULT 1
      /* Entry job_id for new hires. */
      REFERENCES jobs(job_id),
   job_lvl tinyint
      DEFAULT 10,
      /* Entry job_lvl for new hires. */
   pub_id  char(4) NOT NULL
      DEFAULT ('9952')
      REFERENCES publishers(pub_id),
      /* By default, the Parent Company Publisher is the company
      to whom each employee reports. */
   hire_date       datetime        NOT NULL
      DEFAULT (getdate())
      /* By default, the current system date is entered. */
)

--T-SQL命令用來創(chuàng)建用戶自定義的數(shù)據(jù)類型：
USE auser
GO
sp_addtype type_brand,'bigint','NOT NULL'
GO

--刪除用戶自定義的數(shù)據(jù)類型。
USE auser
GO
sp_droptype type_brand
GO

--使用新的數(shù)據(jù)類型創(chuàng)建表Product_Info 和Brands
USE auser
GO
DROP TABLE  product_Info
GO
CREATE TABLE product_Info
(
Product_ID   smallint,
Product_Name  char(20),
Description  vchar(30),
Price   smallmoney,
Brand_ID brand_type
)
GO
CREATE TABLE brands
(
Brand_ID brand_type,
Brand_Name  vchar(30),
Supplier_ID smallint
)
GO

--使用文件組創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri1dat.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 15% ),
( NAME = SPri2_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri2dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi1dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi2dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi1dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi2dt.ndf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
   FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )
GO

--修改數(shù)據(jù)類型的實例：
ALTER TABLE  Bicycle_Sales
ALTER COLUMN  Sale_date Smalldatetime NOT NULL
GO

--把數(shù)據(jù)類型char(30)修改成數(shù)據(jù)類型varchar(20)可以通過以下命令：
ALTER TABLE Bicycle_Sales
ALTER COLUMN Description Varchar(20) NULL
GO

--更改列屬性成為允許空值：
ALTER TABLE Bicycle_Sales
ALTER COLUMN Quantity Tinyint NULL
GO

--把列變成非空屬性。
UPDATE Bicycle_Sales SET Description "None"
WHERE Ddescription IS NULL
GO

--下面介紹如何設(shè)置成非空：
ALTER TABLE Bicycle_Sales
ALTER COLUMN Description Char(30) NOT NULL
GO

--下面的語句用來僅僅修改是否為空的屬性：
ALTER TABLE Bicycle_Sales
ALTER COLUMN Description Varchar(20) NOT NULL
GO

--增加ROWGUIDCOL權(quán)限到一列，或者從列中刪除ROWGUIDCOL權(quán)限，實例如下：
ALTER TABLE Bicycle_Sales
ALTER COLUMN Unique_id ADD ROWGUIDCOL
GO

--用戶可以刪除該權(quán)限：
ALTER TABLE Bicycle_Sales
ALTER COLUMN Unique_id DROP ROWGUIDCOL
GO

--增加列，實例如下：
ALTER TABLE Bicycle_Sales
ADD salesperson_id Tinyint NOT NULL
DEFAULT 0
GO

--下面的代碼允許列salesperson_id為空，并且有默認值：
ALTER TABLE Bicycle_Sales
ADD salesperson_id Tinyint NULL
DEFAULT 0
GO

--雖然指定了默認值，但是該列還是允許空。為了強制所有現(xiàn)存的數(shù)據(jù)由一個默認值0，而不是空（NULL），用戶可以使用WITH VALUES關(guān)鍵字：
ALTER TABLE Bicycle_Sales
ADD salesperson_id Tinyint NULL
DEFAULT 0 WITH VALUES
GO

--刪除表中的列的實例：
ALTER TABLE Bicycle_Sales
DROP COLUMN description
GO

--重命名列，使用T-SQL命令重命名列的名稱，用戶必須運行存儲過程sp_rename，實例如下：
sp_rename 'Bicycle_Sales.description','bicycle_desc','COLUMN'
GO