1 / 20

CREATE DATABASE database_name [ON [PRIMARY] [( [ NAME = logical_file_name,]

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

cisco
Download Presentation

CREATE DATABASE database_name [ON [PRIMARY] [( [ NAME = logical_file_name,]

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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] ] 创建数据库

  2. CREATE DATABASE sales ON (NAME = ‘sales_dat’, FILENAME = ‘c:\program files\microsoft sql server\mssql\data\sales_dat.mdf’, SIZE = 5, MAXSIZE = 30, FILEGROWTH = 2 ) LOG ON (NAME = ‘sales_log’, FILENAME = ‘c:\program files\microsoft sql server\mssql\data\sales_log.ldf’, SIZE = 2MB, MAXSIZE = 20MB, FILEGROWTH = 2MB )

  3. 创建一个名为Report的数据库. 要求:一个主文件,两个二级文件和两个日志文件. 主数据文件的逻辑名为Rep1_dat,磁盘文件名为rep1_dat.mdf 二级文件的逻辑名分别为Rep2_dat, Rep3_dat 磁盘文件名分别为 rep2_dat.ndf rep3_dat.ndf 事务日志文件的逻辑文件名分别为Rep1_log, Rep2_log, 磁盘文件名分别为 rep2_dat.ldf rep3_dat.ldf 初始容量为5MB,最大容量为10MB,每次增长量为1MB. CREATE DATABASE Report ON PRIMARY (NAME = ‘Rep1_dat’, FILENAME = ‘c:\program files\microsoft sql server\mssql\data\rep1_dat.mdf’ SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 1MB), (NAME = ‘Rep2_dat’, FILENAME = ‘c:\program files\microsoft sql server\mssql\data\rep2_dat.ndf’ SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 1MB), (NAME = ‘Rep3_dat’, FILENAME = ‘c:\program files\microsoft sql server\mssql\data\rep3_dat.ndf’ SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 1MB)

  4. LOG ON (NAME = Rep1_log, FILENAME = ‘c:\program files\microsoft sql server\mssql\data\rep1_log.ldf’ SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 1MB), (NAME = Rep2_log, FILENAME = ‘c:\program files\microsoft sql server\mssql\data\rep2_log.ldf’ SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 1MB)

  5. 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] ) 修改数据库

  6. 例 DROP DATABASE Temp CREATE DATABASE Temp ON ( NAME = 'Temp1_dat', FILENAME = 'c:\program files\microsoft sql server\mssql\data\temp1_dat.mdf', SIZE = 5MB, MAXSIZE = 15MB, FILEGROWTH = 1 ) LOG ON ( NAME = 'Temp1_log', FILENAME = 'c:\program files\microsoft sql server\mssql\data\temp1_log.ldf', SIZE = 5MB, MAXSIZE = 15MB, FILEGROWTH = 1 )

  7. 在上面的数据库的基础上,向库中添加一个数据文件和一个日志文件,数据文件的逻辑名和实际文件名分别为:Temp2_dat 和Temp2_dat.ndf,日志文件的逻辑名和实际文件名分为:Temp2_log 和Temp2_log.ldf 这几个文件的初始容量,最大容量及文件大小递增量相同

  8. ALTER DATABASE Temp ADD FILE ( NAME = 'Temp2_dat', FILENAME = 'c:\program files\microsoft sql server\mssql\data\temp2_dat.ndf', SIZE = 5MB, MAXSIZE = 15MB, FILEGROWTH = 1 ) ALTER DATABASE Temp ADD LOG FILE ( NAME = 'Temp2_log', FILENAME = 'c:\program files\microsoft sql server\mssql\data\temp2_log.ldf', SIZE = 5MB, MAXSIZE = 15MB, FILEGROWTH = 1 )

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

  10. ALTER DATABASE Temp ADD FILEGROUP TempFileGroup ALTER DATABASE Temp ADD FILE ( NAME = 'Temp3_dat', FILENAME = 'c:\program files\microsoft sql server\mssql\data\temp3_dat.ndf', SIZE = 3, MAXSIZE = 10, FILEGROWTH = 1 ), ( NAME = 'Temp4_dat', FILENAME = 'c:\program files\microsoft sql server\mssql\data\temp4_dat.ndf', SIZE = 3, MAXSIZE = 10, FILEGROWTH = 1 ) TO FILEGROUP TempFileGroup ALTER DATABASE Temp REMOVE FILE Temp2_log

  11. 删除数据库 DROP DATABASE database_name[,…..n] 例:同时删除 产品数据库 和 员工数据库 DROP DATABASE 产品数据库,员工数据库

  12. 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 ]

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

  14. 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 )

  15. 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(项目名称,项目负责人) )

  16. 创建数据表employees,包括员工编号和员工姓名两个字段，创建数据表employees,包括员工编号和员工姓名两个字段， 其中员工编号为主键，员工姓名为唯一键。 CREATE TABLE employees ( 员工编号 int PRIMARY KEY, 员工姓名 char(20) UNIQUE ) GO

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

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

  19. 创建规则 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

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

More Related