CREATE DATABASE databasename ON PRIMARY NAME logicalfilename FILENAME

CREATE DATABASE database_name [ON [PRIMARY] [( [ NAME = logical_file_name, ] [ FILENAME = ‘ os_file_name’] [, SIZE = size] [, MAXSIZE = {max_size|UNLIMITED}] [, FILEGROWTH = growth_increment ] ) [1, . . . n] ] [, FILEGROUP filegroup_name [1, . . . n]] ] [LOG ON ( [ NAME = logical_file_name, ] [ FILENAME = ‘ os_file_name’] [, SIZE = size] [, MAXSIZE = {max_size|UNLIMITED}] [, FILEGROWTH = growth_increment ] )[1, . . . n] ] 创 建 数 据 库

CREATE DATABASE sales ON (NAME = ‘sales_dat’, FILENAME = ‘c: program filesmicrosoft sql servermssqldatasales_dat. mdf’, SIZE = 5, MAXSIZE = 30, FILEGROWTH = 2 ) LOG ON (NAME = ‘sales_log’, FILENAME = ‘c: program filesmicrosoft sql servermssqldatasales_log. ldf’, SIZE = 2 MB, MAXSIZE = 20 MB, FILEGROWTH = 2 MB )

创建一个名为Report的 数据库. 要求: 一个主文件, 两个二 级文件和两个日志文件. 主数据文件的逻辑名为 Rep 1_dat, 磁盘文件名为 rep 1_dat. mdf 二级文件的逻辑名分别 为Rep 2_dat, Rep 3_dat 磁盘文件名分别为 rep 2_dat. ndf rep 3_dat. ndf 事务日志文件的逻辑文 件名分别为Rep 1_log, Rep 2_log, 磁盘文件名分别为 rep 2_dat. ldf rep 3_dat. ldf 初始容量为 5 MB, 最大容 量为 10 MB, 每次增长量 为 1 MB. CREATE DATABASE Report ON PRIMARY (NAME = ‘Rep 1_dat’, FILENAME = ‘c: program filesmicrosoft sql servermssqldatarep 1_dat. mdf’ SIZE = 5 MB, MAXSIZE = 10 MB, FILEGROWTH = 1 MB), (NAME = ‘Rep 2_dat’, FILENAME = ‘c: program filesmicrosoft sql servermssqldatarep 2_dat. ndf’ SIZE = 5 MB, MAXSIZE = 10 MB, FILEGROWTH = 1 MB), (NAME = ‘Rep 3_dat’, FILENAME = ‘c: program filesmicrosoft sql servermssqldatarep 3_dat. ndf’ SIZE = 5 MB, MAXSIZE = 10 MB, FILEGROWTH = 1 MB)

LOG ON (NAME = Rep 1_log, FILENAME = ‘c: program filesmicrosoft sql servermssqldatarep 1_log. ldf’ SIZE = 5 MB, MAXSIZE = 10 MB, FILEGROWTH = 1 MB), (NAME = Rep 2_log, FILENAME = ‘c: program filesmicrosoft sql servermssqldatarep 2_log. ldf’ SIZE = 5 MB, MAXSIZE = 10 MB, FILEGROWTH = 1 MB)

ALTER DATABASE database { ADD FILE <filespec> [, . . . n] [ TO FILEGROUP filegroup_name] |ADD LOG FILE <filespec> [, . . . n] |REMOVE FILE logical_file_name 修 改 |ADD FILEGROUP filegroup_name 数 |REMOVE FILEGROUP filegroup_name 据 |MODIFY FILE <filespec> 库 |MODIFY FILEGROUP file_group_name filegroup_property } <filespec> : : = ( NAME = logical_file_name [, FILENAME = ‘os_file_name’] [, SIZE = size] [, MAXSIZE = {max_size|UNLIMITED}] [, FILEGROWTH = growth_increment] )

例 DROP DATABASE Temp CREATE DATABASE Temp ON ( NAME = 'Temp 1_dat', FILENAME = 'c: program filesmicrosoft sql servermssqldatatemp 1_dat. mdf', SIZE = 5 MB, MAXSIZE = 15 MB, FILEGROWTH = 1 ) LOG ON ( NAME = 'Temp 1_log', FILENAME = 'c: program filesmicrosoft sql servermssqldatatemp 1_log. ldf', SIZE = 5 MB, MAXSIZE = 15 MB, FILEGROWTH = 1 )

ALTER DATABASE Temp ADD FILE ( NAME = 'Temp 2_dat', FILENAME = 'c: program filesmicrosoft sql servermssqldatatemp 2_dat. ndf', SIZE = 5 MB, MAXSIZE = 15 MB, FILEGROWTH = 1 ) ALTER DATABASE Temp ADD LOG FILE ( NAME = 'Temp 2_log', FILENAME = 'c: program filesmicrosoft sql servermssqldatatemp 2_log. ldf', SIZE = 5 MB, MAXSIZE = 15 MB, FILEGROWTH = 1 )

将前面的数据库中的数据文件Temp 1_dat的容 量增加到 10 MB, 并将其容量最大值增加到 20 MB, 递增量增加到 2 MB. ALTER DATABASE Temp MODIFY FILE ( NAME = Temp 1_dat, SIZE = 10, MAXSIZE = 20, FILEGROWTH = 2 )

ALTER DATABASE Temp ADD FILEGROUP Temp. File. Group ALTER DATABASE Temp ADD FILE ( NAME = 'Temp 3_dat', FILENAME = 'c: program filesmicrosoft sql servermssqldatatemp 3_dat. ndf', SIZE = 3, MAXSIZE = 10, FILEGROWTH = 1 ), ( NAME = 'Temp 4_dat', FILENAME = 'c: program filesmicrosoft sql servermssqldatatemp 4_dat. ndf', SIZE = 3, MAXSIZE = 10, FILEGROWTH = 1 ) TO FILEGROUP Temp. File. Group ALTER DATABASE Temp REMOVE FILE Temp 2_log

CREATE TABLE语句创表的语法如下： CREATE TABLE [ database_name. [ owner ]. | owner. ] table_name ( { column_name data_type | column_name AS computed_column_expression | < table_constraint > } [ , . . . n ] ) [ ON { filegroup | DEFAULT } ] [ [ DEFAULT constant_expression ] | [ IDENTITY [ ( seed , increment ) ] ] ] [ < column_constraint > ] [. . . n ]

CREATE TABLE project ( 项目编号 int CONSTRAINT PK_Pno PRIMARY KEY, 项目名称 varchar(40) ) CREATE TABLE project ( 项目编号 int PRIMARY KEY, 项目名称 varchar(40) )

CREATE TABLE project ( 项目编号 int, 项目名称 varchar(40), CONSTRAINT PK_Pno_Pname PRIMARY KEY(项目编号, 项目名称) ) CREATE TABLE project ( 项目编号 int CONSTRAINT PK_Pno PRIMARY KEY, 项目名称 char(20) CONSTRAINT UN_Pname UNIQUE )

CREATE TABLE project ( 项目编号 int PRIMARY KEY, 项目名称 char(20) UNIQUE ) CREATE TABLE project ( 项目编号 int CONSTRAINT PK_Pno PRIMARY KEY, 项目名称 char(20), 项目负责人 char(20), CONSTRAINT UN_Pname_Pm UNIQUE(项目名称, 项目负责人) )

-- 创建数据表project CREATE TABLE project ( 项目编号 int PRIMARY KEY, 项目名称 char(20), 项目负责人 char(20) FOREIGN KEY REFERENCES temp 9(员 姓名) ON DELETE CASCADE ) GO

创建核查约束 CREATE TABLE employees ( 员 编号 int NOT NULL PRIMARY KEY, 员 姓名 char(20) UNIQUE, 资 int NOT NULL CONSTRAINT CK_Es CHECK ( 资 >= 1000 AND 资 <= 20000) )

创建规则 CREATE TABLE employees ( 员 编号 int NOT NULL PRIMARY KEY, 员 姓名 char(20) , 资 int NOT NULL ） CREATE RULE RU_salary AS @salary>=1000 AND @salary <=20000 GO EXEC sp_bindrule ‘RU_salary’, ‘employee. 资’ GO

创建默认 CREATE DEFAULT DE_salary as 1000 GO EXEC sp_bindefault ‘DE_salary’, ‘employee. 资’ GO