slide1
Download
Skip this Video
Download Presentation
CREATE DATABASE database_name [ON [PRIMARY] [( [ NAME = logical_file_name,]

Loading in 2 Seconds...

play fullscreen
1 / 20

CREATE DATABASE database_name [ON [PRIMARY] [( [ NAME = logical_file_name,] - PowerPoint PPT Presentation


  • 84 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' CREATE DATABASE database_name [ON [PRIMARY] [( [ NAME = logical_file_name,]' - cisco


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

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]

]

创建数据库

slide2

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

)

slide3

创建一个名为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)

slide4

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)

slide5

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]

)

修改数据库

slide6

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

)

slide7

在上面的数据库的基础上,向库中添加一个数据文件和一个日志文件,数据文件的逻辑名和实际文件名分别为:Temp2_dat 和Temp2_dat.ndf,日志文件的逻辑名和实际文件名分为:Temp2_log 和Temp2_log.ldf

这几个文件的初始容量,最大容量及文件大小递增量相同

slide8

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

)

slide9

将前面的数据库中的数据文件Temp1_dat的容量增加到10MB,并将其容量最大值增加到20MB,递增量增加到2MB.将前面的数据库中的数据文件Temp1_dat的容量增加到10MB,并将其容量最大值增加到20MB,递增量增加到2MB.

ALTER DATABASE Temp

MODIFY FILE

(

NAME = Temp1_dat,

SIZE = 10,

MAXSIZE = 20,

FILEGROWTH = 2

)

slide10

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

slide11

删除数据库

DROP DATABASE database_name[,…..n]

例:同时删除 产品数据库 和 员工数据库

DROP DATABASE 产品数据库,员工数据库

slide12

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 ]

slide13

CREATE TABLE project

(

项目编号 int CONSTRAINT PK_Pno PRIMARY KEY,

项目名称 varchar(40)

)

CREATE TABLE project

(

项目编号 int PRIMARY KEY,

项目名称 varchar(40)

)

slide14

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

)

slide15

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

)

slide16

创建数据表employees,包括员工编号和员工姓名两个字段,创建数据表employees,包括员工编号和员工姓名两个字段,

其中员工编号为主键,员工姓名为唯一键。

CREATE TABLE employees

(

员工编号 int PRIMARY KEY,

员工姓名 char(20) UNIQUE

)

GO

slide17

-- 创建数据表project

CREATE TABLE project

(

项目编号 int PRIMARY KEY,

项目名称 char(20),

项目负责人 char(20)

FOREIGN KEY REFERENCES temp9(员工姓名)

ON DELETE CASCADE

)

GO

slide18

创建核查约束

CREATE TABLE employees

(

员工编号 int NOT NULL PRIMARY KEY,

员工姓名 char(20) UNIQUE,

工资 int NOT NULL

CONSTRAINT CK_Es

CHECK (工资 >= 1000 AND 工资 <= 20000)

)

slide19

创建规则

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

slide20

创建默认

CREATE DEFAULT DE_salary as 1000

GO

EXEC sp_bindefault ‘DE_salary’, ‘employee.工资’

GO

ad