sql server 2000

JAVA下的SQL_System的建立

--第五章——建立数据库和数据库表
--5.2.3  使用T-SQL命令创建数据库
--使用T-SQL命令创建数据库的语法如下：
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 ]

--创建简单的数据库
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

--创建复杂的数据库
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察看数据库
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  创建employee表
--下例显示 pubs 数据库中所创建表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命令用来创建用户自定义的数据类型：
USE auser
GO
sp_addtype type_brand,'bigint','NOT NULL'
GO

--删除用户自定义的数据类型。
USE auser
GO
sp_droptype type_brand
GO

--使用新的数据类型创建表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

--使用文件组创建数据库
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

--修改数据类型的实例：
ALTER TABLE  Bicycle_Sales
ALTER COLUMN  Sale_date Smalldatetime NOT NULL
GO

--把数据类型char(30)修改成数据类型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

--下面介绍如何设置成非空：
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权限到一列，或者从列中删除ROWGUIDCOL权限，实例如下：
ALTER TABLE Bicycle_Sales
ALTER COLUMN Unique_id ADD ROWGUIDCOL
GO

--用户可以删除该权限：
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

--虽然指定了默认值，但是该列还是允许空。为了强制所有现存的数据由一个默认值0，而不是空（NULL），用户可以使用WITH VALUES关键字：
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